Gwnewch Restr Deinamig Dibynnol yn Excel (Cam wrth Gam)
Yma yn y tiwtorial hwn, byddwn gam wrth gam yn cyflwyno sut i greu cwymprestr ddibynnol sy'n dangos dewisiadau yn dibynnu ar y gwerthoedd a ddewiswyd yn y gwymplen gyntaf. Mewn geiriau eraill, byddwn yn gwneud rhestr dilysu data Excel yn seiliedig ar werth rhestr arall.
Gwnewch gwymplen deinamig dibynnol
10s i wneud cwymplen ddibynnol gyda theclyn defnyddiol
Gwnewch restr ddeinamig ddibynnol yn Excel 2021 neu Excel 365
Rhai cwestiynau y gallech eu gofyn am y tiwtorial hwn
Dadlwythwch y ffeil sampl am ddim 
Fideo: Gwnewch restr sy'n dibynnu ar Excel
Gwnewch gwymplen deinamig dibynnol
Cam 1: Teipiwch y Cofrestriadau Ar gyfer y Rhestrau Gollwng
1. Yn gyntaf, teipiwch y cofnodion rydych chi am eu gweld yn y cwymplenni, pob rhestr mewn colofn ar wahân.
Hysbysiad y bydd yr eitemau yn y golofn gyntaf (Cynnyrch) fel enwau Excel ar gyfer y rhestrau dibynnol yn ddiweddarach. Er enghraifft, yma Ffrwythau a Llysiau fydd yr Enwau ar gyfer colofnau B2:B5 a C2:C6 ar wahân.
Gweler y screenshot:
2. Yna creu tablau ar gyfer pob rhestr data.
Dewiswch ystod colofn A1: A3, cliciwch Mewnosod > Tabl, yna yn y Creu Tabl deialog, ticiwch Mae penawdau ar fy mwrdd blwch gwirio. Cliciwch OK.
Yna ailadroddwch y cam hwn i greu tablau ar gyfer y ddwy restr arall.
Gallwch weld yr holl dablau a'r cyfeiriad at ystodau yn Enw Rheolwr (pwyswch Ctrl + F3 i'w agor).
Cam 2: Creu Ystod Enwau
Yn y cam hwn, mae angen i chi greu enwau ar gyfer y brif restr a phob rhestr ddibynyddion.
1. Dewiswch yr eitemau sy'n ymddangos yn y brif restr (A2: A3).
2. Yna ewch i'r Blwch enw sydd wrth ymyl Bar fformiwla.
3. Teipiwch yr enw i mewn iddo, dyma ei enwi fel Dewisiwch eich eitem.
4. Gwasgwch Rhowch allwedd i'w chwblhau.
Yna ailadroddwch y camau uchod i greu Enwau ar wahân ar gyfer pob rhestr ddibynnol.
Yma enwir yr ail golofn (B2:B5) fel Ffrwythau, a'r drydedd golofn (C2:C6) fel Llysiau.
Gallwch weld yr holl enwau ystod yn Rheolwr Enw (pwyswch Ctrl + F3 i'w agor).
Cam 3: Ychwanegu Y Brif Rhestr Gollwng
Nesaf, ychwanegwch y brif gwymplen (Cynnyrch), sy'n gwymplen dilysu data arferol, nid yn gwymplen ddibynnol.
1. Yn gyntaf, creu tabl.
Dewiswch gell (E1), a theipiwch bennawd y golofn gyntaf (Dewisiwch eich eitem), a symud i'r gell golofn nesaf (F1), teipiwch bennawd yr ail golofn (Eitem). Byddwch yn ychwanegu'r gwymplen i'r tabl hwn.
Yna dewiswch y ddau bennawd yma (E1 ac F1), cliciwch Mewnosod tab, a dewis Tabl yn y grŵp Tablau.
Yn y Creu Tabl deialog, ticiwch Mae penawdau ar fy mwrdd blwch, a chliciwch OK.
2. Dewiswch gell E2 yr ydych am fewnosod y brif gwymplen, cliciwch Dyddiad tab a mynd i Offer Data grŵp i glicio Dilysu Data > Dilysu Data.
3. Yn yr ymgom Dilysu Data,
- Dewiswch rhestr yn y Caniatáu adran,
- Teipiwch isod y fformiwla i mewn ffynhonnell bar, Cynnyrch yw Enw'r brif restr,
- Cliciwch OK.
=Product
Gallwch weld bod y brif gwymplen wedi'i chreu.
Cam 4: Ychwanegu Rhestr Gollwng Dibynnol
1. Dewiswch gell F2 yr ydych am ychwanegu'r gwymplen ddibynnol ati, cliciwch Dyddiad tab, ac ewch i grŵp Data Tools i glicio Dilysu Data > Dilysu Data.
2. Yn yr ymgom Dilysu Data,
- Dewiswch rhestr yn y Caniatáu adran,
- Teipiwch y fformiwla isod i mewn ffynhonnell bar, E2 yw'r gell sy'n cynnwys y brif gwymplen.
- Cliciwch OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Os yw'r E2 yn wag (nid ydych yn dewis unrhyw un eitem yn y brif gwymplen), fe welwch neges yn ymddangos fel isod, cliciwch Ydy i barhau.
Nawr mae'r gwymplen ddibynyddion wedi'i gwneud.
Cam 5: Profwch y Rhestr Gollwng Dibynnol.
1. Dewiswch ffrwythau yn y brif gwymplen (E2), yna ewch i'r gwymplen ddibynnol (F2) i glicio ar yr eicon saeth, gweld a yw'r eitemau ffrwythau yn y rhestr, yna dewiswch un eitem o'r gwymplen ddibynnol.
2. Gwasgwch Tab allwedd i ddechrau rhes newydd yn y tabl mewnbynnu data, dewiswch Llysiau, a symudwch i'r gell nesaf i'r dde, gweld a yw'r eitemau llysiau yn y rhestr, yna dewiswch un eitem o'r gwymplen ddibynnol.
- Os nad oes eitem wedi'i dewis yn y brif gwymplen (Colofn Cynnyrch), ni fydd y gwymplen ddibynnol (Colofn Eitem) yn gweithio.
- Os ydych chi am ailosod neu glirio cynnwys y gwymplen ddibynyddion ar ôl dewis newid, ewch i'r erthygl hon Sut i glirio cell gwymplen ddibynnol ar ôl dewis newid yn Excel?, mae'n cyflwyno cod VBA i'ch helpu chi.
- Os ydych chi am greu rhestr ostwng 3 lefel, yr erthygl hon Sut i greu rhestr ostwng dibynnol aml-lefel yn Excel? Bydd yn eich helpu.
10s i wneud cwymplen ddibynnol gyda theclyn defnyddiol
Kutools ar gyfer Excel yn darparu offeryn pwerus i wneud cwymprestr ddibynnol yn haws ac yn gyflymach, gadewch i ni weld:
Cyn dilyn y camau isod, os gwelwch yn dda cliciwch i lawrlwytho Kutools ar gyfer Excel ar gyfer treial am ddim 30 diwrnod yn gyntaf.
Cam 1: Teipiwch y Cofrestriadau Ar Gyfer Y Rhestr Gollwng
Yn gyntaf, trefnwch eich data fel y sgrinlun isod:
Cam 2: Cymhwyso offeryn Kutools
1. Dewiswch y data rydych chi wedi'i greu, cliciwch Kutools tab, a chlicio Rhestr ostwng i arddangos yr is-ddewislen, cliciwch Rhestr Gollwng Dynamig.
2. Yn y Rhestr Drop-Lawr Dibynnol
- Gwiriwch y Modd B. sy'n cyfateb i'ch modd data,
- dewiswch y ystod allbwn, rhaid i'r golofn amrediad allbwn fod yn hafal i'r golofn amrediad data,
- Cliciwch Ok.
Nawr mae'r gwymplen ddibynnol wedi'i chreu.
- Mae Modd B yn cefnogi creu rhestr gwympo trydedd lefel neu fwy:
- Os yw'ch data wedi'i drefnu fel y mae'r sgrinlun isod yn ei ddangos, mae angen i chi ddefnyddio Modd A, mae Modd A yn ei gefnogi yn unig i greu cwymprestr ddibynnol 2 lefel.
- Mwy o fanylion am sut i ddefnyddio Kutools i greu rhestr ostwng dibynnol, ewch i tiwtorial hwn .
Gwnewch restr ddeinamig ddibynnol yn Excel 2021 neu Excel 365
Os ydych chi yn Excel 2021 neu Excel 365, mae ffordd arall o greu cwymprestr ddeinamig yn gyflym trwy ddefnyddio swyddogaethau newydd UNIGRYW ac Hidlo.
Gan dybio bod eich data ffynhonnell wedi'i drefnu fel y sgrinlun a ddangosir, dilynwch y camau isod i greu'r gwymplen deinamig.
Cam 1: Defnyddio fformiwla i gael eitemau ar gyfer y brif gwymplen
Dewiswch gell, er enghraifft, cell G3, a defnyddio ffwythiannau UNIGRYW a FILTER i echdynnu'r gwerthoedd unigryw o'r Dewisiwch eich eitem rhestr a fydd yn ffynhonnell y brif gwymplen, a gwasgwch Rhowch allweddol.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Cam 2: Creu'r brif gwymplen
1. Dewiswch gell yr ydych am osod y brif gwymplen, er enghraifft, cell D3, Cliciwch Dyddiad tab, ac ewch i Offer Data grŵp i glicio Dilysu Data > Dilysu Data.
2. Yn yr ymgom Dilysu Data,
- Dewiswch rhestr yn y Caniatáu adran,
- Teipiwch isod y fformiwla i mewn ffynhonnell bar,
- Cliciwch OK.
=$G$3#
Nawr mae'r brif gwymplen wedi'i chreu.
Cam 3: Defnyddio fformiwla i gael eitemau ar gyfer y gwymplen ddibynnol
Dewiswch gell, er enghraifft, cell H3, gan ddefnyddio swyddogaeth FILTER i hidlo'r eitemau yn seiliedig ar y gwerth yn y gell D3 (yr eitem a ddewiswyd yn y brif gwymplen), pwyswch Rhowch allweddol.
=FILTER(B3:B20, A3:A20=D3)
Cam 4: Creu'r gwymplen ddibynnol
1. Dewiswch gell a fydd yn gosod y gwymplen ddibynnol, er enghraifft, cell E3, Cliciwch Dyddiad tab, ac ewch i Offer Data grŵp i glicio Dilysu Data > Dilysu Data.
2. Yn yr ymgom Dilysu Data,
- Dewiswch rhestr yn y Caniatáu adran,
- Teipiwch isod y fformiwla i mewn ffynhonnell bar,
- Cliciwch OK.
=$H$3#
Nawr mae'r gwymplen ddibynnol yn cael ei chreu'n llwyddiannus.
Pan fyddwch chi'n ychwanegu eitemau newydd neu'n gwneud rhai newidiadau yn A3: A20, bydd y gwymplen yn cael ei diweddaru'n awtomatig.
Trefnu'r gwymplen yn nhrefn yr wyddor
Os ydych chi am drefnu'r eitemau yn y gwymplen yn nhrefn yr wyddor, gallwch ddefnyddio'r fformiwla isod i'r tabl paratoi.Ar gyfer y prif gwymplen (y fformiwla yng nghell G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))Ar gyfer y gwymplen dibynnol (y fformiwla yng nghell H3):
=SORT(FILTER(B3:B20, A3:A20=D3))Nawr mae'r ddwy gwymplen yn cael eu didoli yn nhrefn yr wyddor A i Z.
I gael trefn yn nhrefn yr wyddor Z i A, defnyddiwch y fformiwla isod:
Ar gyfer y prif gwymplen (y fformiwla yng nghell G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)Ar gyfer y gwymplen dibynnol (y fformiwla yng nghell H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)Rhai cwestiynau y gallech eu gofyn:
1. Pam mewnosod tabl ar gyfer pob rhestr ddata?
Bydd mewnosod tabl ar gyfer y rhestr ddata yn eich helpu i ddiweddaru'r gwymplen yn awtomatig yn seiliedig ar y newidiadau yn y rhestr ddata. Er enghraifft, gan ychwanegu 'Eraill' yn y rhestr ddata gyntaf, yna bydd y brif gwymplen yn cael ei hychwanegu'n awtomatig gyda 'Eraill'.
2. Pam defnyddio tabl i osod cwymplenni?
Pan fyddwch chi'n pwyso'r allwedd Tab i ychwanegu llinell newydd at y tabl, bydd y cwymplenni'n cael eu hychwanegu'n awtomatig yn y llinell newydd hefyd.
3. Sut mae swyddogaeth INDIRECT yn gweithio?
INDIRECT ffwythiant yn cael ei ddefnyddio i drosi llinyn testun i gyfeiriad dilys.
4. Sut mae fformiwla INDIRECT(SUBSTITUTE(E2&F2," ","")) yn gweithio?
Yn gyntaf, TANYSGRIFIAD swyddogaeth yn disodli testun gyda thestun arall. Yma roedd yn arfer tynnu'r bylchau o'r enwau cyfun (E2 a F2). Yna INDIRECT ffwythiant yn trosi'r llinyn testun (y cynnwys cyfunol gan E2 a F2) i gyfeiriad dilys.
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!
