Note: The other languages of the website are Google-translated. Back to English

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

Tab Swyddfa Galluogi Golygu a Pori Tabbed yn y Swyddfa, a Gwneud Eich Gwaith yn Haws Orau ...
Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%
  • Ailddefnyddio Unrhyw beth: Ychwanegwch y fformwlâu, siartiau ac unrhyw beth arall a ddefnyddir fwyaf cymhleth i'ch ffefrynnau, a'u hailddefnyddio'n gyflym yn y dyfodol.
  • Mwy nag 20 o nodweddion testun: Rhif Detholiad o Llinyn Testun; Tynnu neu Dynnu Rhan o Testunau; Trosi Rhifau ac Arian Cyfred yn Eiriau Saesneg.
  • Uno Offer: Llyfrau Gwaith a Thaflenni Lluosog yn Un; Uno Celloedd Lluosog / Rhesi / Colofnau Heb Golli Data; Uno Rhesi a Swm Dyblyg.
  • Hollti Offer: Rhannwch Ddata yn Daflenni Lluosog Yn Seiliedig ar Werth; Un Llyfr Gwaith i Ffeiliau Lluosog Excel, PDF neu CSV; Un Golofn i Golofnau Lluosog.
  • Gludo Sgipio Rhesi Cudd / Hidlo; Cyfrif A Swm yn ôl Lliw Cefndir; Anfon E-byst wedi'u Personoli at Dderbynwyr Lluosog mewn Swmp.
  • Hidlo Gwych: Creu cynlluniau hidlo datblygedig a'u cymhwyso i unrhyw daflenni; Trefnu yn yn ôl wythnos, dydd, amlder a mwy; Hidlo gan feiddgar, fformwlâu, sylw ...
  • Mwy na 300 o nodweddion pwerus; Yn gweithio gyda'r Swyddfa 2007-2019 a 365; Yn cefnogi pob iaith; Defnydd hawdd yn eich menter neu sefydliad.

swigen dde glas saeth 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.

doc-ddeinamig-ystod1

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:

doc-ddeinamig-ystod2

2. Dewiswch yr ystod a chlicio Mewnosod > Tabl, gweler y screenshot:

doc-ddeinamig-ystod3

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:

doc-ddeinamig-ystod4 -2 doc-ddeinamig-ystod5

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:

doc-ddeinamig-ystod6 -2 doc-ddeinamig-ystod7

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.


swigen dde glas saeth 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:

doc-ddeinamig-ystod2

Gallwch newid yr enwau amrediad i enwau amrediad deinamig gyda'r camau canlynol:

1. Ewch i glicio Fformiwlâu > Rheolwr Enw, gweler y screenshot:

doc-ddeinamig-ystod8

2. Yn y Rheolwr Enw blwch deialog, dewiswch yr eitem rydych chi am ei defnyddio, a chliciwch golygu botwm.

doc-ddeinamig-ystod9

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:

doc-ddeinamig-ystod10

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:

doc-ddeinamig-ystod6 -2 doc-ddeinamig-ystod7

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])
  • -1
  • = 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.


swigen dde glas saeth 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:

doc-ddeinamig-ystod12
-1
doc-ddeinamig-ystod13

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?


Yr Offer Cynhyrchedd Swyddfa Gorau

Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%

  • Ailddefnyddio: Mewnosod yn gyflym fformwlâu cymhleth, siartiau ac unrhyw beth rydych chi wedi'i ddefnyddio o'r blaen; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau heb golli Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi / Colofnau Dyblyg... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Mwy na 300 o nodweddion pwerus. Yn cefnogi Swyddfa / Excel 2007-2019 a 365. Yn cefnogi pob iaith. Defnydd hawdd yn eich menter neu sefydliad. Nodweddion llawn treial am ddim 30 diwrnod. Gwarant arian yn ôl 60 diwrnod.
tab kte 201905

Mae Tab Office yn Dod â rhyngwyneb Tabbed i'r Swyddfa, a 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!
gwaelod officetab
sylwadau (4)
Dim sgôr eto. Byddwch y cyntaf i sgorio!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Diolch am erthygl dda
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Rydych chi'n athro da iawn: 1) dull cam wrth gam; 2) nad ydych yn diflasu'r myfyriwr â deunydd neu gasgliadau amlwg; 3) ac eto rydych chi'n cynnwys yr holl ddeunydd angenrheidiol. Edrychaf ymlaen at fwy o sesiynau tiwtorial gennych chi.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
helpwch yr wyf yn ceisio creu ystod a enwir deinamig ar excel 2016 teipio "= offset(DATAENTRY! $B$6,,,counta(DATAENTRY!$B$6:$B$13)))" ond yn dal i roi gwall i mi yn ei ddweud nid yw'n fformiwla.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
mewn gwirionedd, mewn gwirionedd ddim yn ddefnyddiol
Nid oes unrhyw sylwadau wedi'u postio yma eto
Gadewch eich sylwadau
Postio fel Gwestai
×
Graddiwch y swydd hon:
0   Cymeriadau
Lleoliadau a Awgrymir