Sut i greu ystod ddynodedig ddeinamig yn Excel?
Fel arfer, Meysydd a Enwyd yn ddefnyddiol iawn i ddefnyddwyr Excel, gallwch ddiffinio cyfres o werthoedd mewn colofn, rhoi enw i'r golofn honno, ac yna gallwch gyfeirio at yr ystod honno yn ôl enw yn lle ei chyfeiriadau celloedd. Ond y rhan fwyaf o amser, mae angen ichi ychwanegu data newydd i ehangu gwerthoedd data eich ystod y cyfeiriwyd ati yn y dyfodol. Yn yr achos hwn, mae'n rhaid ichi fynd yn ôl at Fformiwlâu > Rheolwr Enw ac ailddiffinio'r ystod i gynnwys y gwerth newydd. Er mwyn osgoi hyn, gallwch greu ystod ddynodedig ddeinamig sy'n golygu nad oes angen i chi addasu cyfeiriadau celloedd bob tro pan fyddwch chi'n ychwanegu rhes neu golofn newydd at y rhestr.
Creu ystod ddynamig ddynodedig yn Excel trwy greu tabl
Creu ystod ddynamig ddynodedig yn Excel gyda Swyddogaeth
Creu ystod ddynamig ddynodedig yn Excel gyda chod VBA
Creu ystod ddynamig ddynodedig yn Excel trwy greu tabl
Os ydych chi'n defnyddio Excel 2007 neu fersiynau diweddarach, y ffordd hawsaf o greu ystod ddynodedig ddeinamig yw creu tabl Excel a enwir.
Gadewch i ni ddweud, mae gennych chi ystod o ddata canlynol y mae angen iddynt ddod yn ystod ddynodedig ddeinamig.
1. Yn gyntaf, byddaf yn diffinio enwau amrediad ar gyfer yr ystod hon. Dewiswch yr ystod A1: A6 a nodi'r enw dyddiad i mewn i'r Blwch Enw, yna pwyswch Rhowch allwedd. Diffinio enw ar gyfer ystod B1: B6 fel Saleprice gyda'r un ffordd. Ar yr un pryd, rwy'n creu fformiwla = swm (Saleprice) mewn cell wag, gweler y screenshot:
2. Dewiswch yr ystod a chlicio Mewnosod > Tabl, gweler y screenshot:
3. Yn y Creu Tabl blwch prydlon, gwirio Mae penawdau ar fy mwrdd (os nad oes penawdau yn yr ystod, dad-diciwch hi), cliciwch OK botwm, ac mae'r data amrediad wedi'i drosi i dabl. Gweler sgrinluniau:
 |
 |
 |
4. A phan fyddwch chi'n nodi gwerthoedd newydd ar ôl y data, bydd yr ystod a enwir yn addasu'n awtomatig a bydd y fformiwla a grëir hefyd yn cael ei newid hefyd. Gweler y sgrinluniau canlynol:
 |
|
 |
Nodiadau:
1. Rhaid i'ch data mewnbynnu newydd fod yn gyfagos i'r data uchod, mae'n golygu nad oes rhesi na cholofnau gwag rhwng y data newydd a'r data presennol.
2. Yn y tabl, gallwch fewnosod data rhwng y gwerthoedd presennol.
Creu ystod ddynamig ddynodedig yn Excel gyda Swyddogaeth
Yn Excel 2003 neu fersiwn gynharach, ni fydd y dull cyntaf ar gael, felly dyma ffordd arall i chi. Y canlynol OFFSET () gall swyddogaeth wneud y ffafr hon i chi, ond mae'n drafferthus braidd. Gan dybio bod gen i ystod o ddata sy'n cynnwys yr enwau amrediad yr wyf wedi'u diffinio, er enghraifft, A1: A6 enw'r amrediad yw dyddiad, a B1: B6 enw amrediad yw Saleprice, ar yr un pryd, rwy'n creu fformiwla ar gyfer y Saleprice. Gweler y screenshot:
Gallwch newid yr enwau amrediad i enwau amrediad deinamig gyda'r camau canlynol:
1. Ewch i glicio Fformiwlâu > Rheolwr Enw, gweler y screenshot:
2. Yn y Rheolwr Enw blwch deialog, dewiswch yr eitem rydych chi am ei defnyddio, a chliciwch golygu botwm.
3. Yn y popped allan Golygu Enw deialog, nodwch y fformiwla hon = OFFSET (Taflen1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) i mewn i'r Yn cyfeirio at blwch testun, gweler y screenshot:
4. Yna cliciwch OK, ac yna ailadroddwch y cam2 a'r cam3 i gopïo'r fformiwla hon = OFFSET (Taflen1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) i mewn i'r Yn cyfeirio at blwch testun ar gyfer y Saleprice enw amrediad.
5. Ac mae'r ystodau deinamig a enwir wedi'u creu. Pan fyddwch yn nodi gwerthoedd newydd ar ôl y data, bydd yr ystod a enwir yn addasu'n awtomatig a bydd y fformiwla a grëir hefyd yn cael ei newid hefyd. Gweler sgrinluniau:
|
|
|
Nodyn: Os oes celloedd gwag yng nghanol eich amrediad, bydd canlyniad eich fformiwla yn anghywir. Mae hynny oherwydd nad yw'r celloedd nad ydynt yn wag yn cael eu cyfrif, felly bydd eich amrediad yn fyrrach nag y dylai, a bydd y celloedd olaf yn yr ystod yn cael eu gadael i ffwrdd.
Awgrym: esboniad am y fformiwla hon:
- = OFFSET (cyfeirnod, rhesi, cols, [uchder], [lled])
- = OFFSET (Taflen1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
- cyfeirio yn cyfateb i safle cychwynnol y gell, yn yr enghraifft hon Taflen1! $ A $ 1;
- rhes yn cyfeirio at nifer y rhesi rydych chi'n mynd i symud tuag i lawr, mewn perthynas â'r gell gychwyn (neu i fyny, os ydych chi'n defnyddio gwerth negyddol.), yn yr enghraifft hon, mae 0 yn nodi y bydd y rhestr yn cychwyn o'r rhes gyntaf i lawr
- colofn yn cyfateb i nifer y colofnau y byddwch yn symud i'r dde, mewn perthynas â'r gell gychwyn (neu i'r chwith, gan ddefnyddio gwerth negyddol.), yn y fformiwla enghreifftiol uchod, mae 0 yn nodi ehangu 0 colofn i'r dde.
- [uchder] yn cyfateb i uchder (neu nifer y rhesi) yr ystod gan ddechrau yn y safle wedi'i addasu. $ A: $ A, bydd yn cyfrif yr holl eitemau a gofnodir yng ngholofn A.
- [lled] yn cyfateb i led (neu nifer y colofnau) yr ystod sy'n cychwyn yn y safle wedi'i addasu. Yn y fformiwla uchod, bydd y rhestr yn 1 golofn o led.
Gallwch chi newid y dadleuon hyn i'ch angen.
Creu ystod ddynamig ddynodedig yn Excel gyda chod VBA
Os oes gennych sawl colofn, fe allech chi ailadrodd a nodi fformiwla unigol ar gyfer yr holl golofnau sy'n weddill, ond byddai honno'n broses hir, ailadroddus. Ar gyfer gwneud pethau'n haws, gallwch ddefnyddio cod i greu'r ystod ddynamig a enwir yn awtomatig.
1. Gweithredwch eich taflen waith.
2. Daliwch i lawr y ALT + F11 allweddi, ac mae'n agor y Ffenestr Microsoft Visual Basic for Applications.
3. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Ffenestr Modiwl.
Cod Vba: creu ystod ddynodedig ddeinamig
Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName <> "" Then
wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
End If
Next
End Sub
4. Yna pwyswch F5 yn allweddol i redeg y cod, a chynhyrchir rhai ystodau deinamig a enwir sydd wedi'u henwi â gwerthoedd y rhes gyntaf ac mae hefyd yn creu ystod ddeinamig o'r enw FyData sy'n cwmpasu'r data cyfan.
5. Pan fyddwch chi'n nodi gwerthoedd newydd ar ôl y rhesi neu'r colofnau, bydd yr ystod yn cael ei hehangu hefyd. Gweler sgrinluniau:
 |
|
 |
Nodiadau:
1. Gyda'r cod hwn, nid yw'r enwau amrediad yn cael eu harddangos yn y Blwch Enw, er mwyn gweld a defnyddio'r enwau amrediad yn gyfleus, rwyf wedi gosod Kutools ar gyfer Excel, Gyda'i Panelau Navigation, rhestrir yr enwau ystod ddeinamig a grëwyd.
2. Gyda'r cod hwn, gellir ehangu ystod gyfan y data yn fertigol neu'n llorweddol, ond i gofio ni ddylai fod rhesi na cholofnau gwag rhwng y data pan fyddwch chi'n nodi gwerthoedd newydd.
3. Pan ddefnyddiwch y cod hwn, dylai eich ystod ddata fod yn cychwyn yng nghell A1.
Erthygl gysylltiedig:
Sut i ddiweddaru siart ar ôl mewnbynnu data newydd yn Excel?
Offer Cynhyrchiant Swyddfa Gorau
Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser. Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...
Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer
- Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
- Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
- Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
