Sut i wneud rhestr gwympo yn Excel

Mae'r gwymplen yn offeryn hynod ddefnyddiol a all helpu i wneud gweithio gyda gwybodaeth yn fwy cyfforddus. Mae'n ei gwneud hi'n bosibl cynnwys sawl gwerth mewn cell ar unwaith, y gallwch chi weithio gyda nhw, fel gydag unrhyw rai eraill. I ddewis yr un sydd ei angen arnoch, cliciwch ar yr eicon saeth, ac ar ôl hynny dangosir rhestr o werthoedd uXNUMXbuXNUMXbis. Ar ôl dewis un penodol, caiff y gell ei llenwi'n awtomatig ag ef, ac mae'r fformiwlâu yn cael eu hailgyfrifo yn seiliedig arno.

Mae Excel yn darparu llawer o wahanol ddulliau ar gyfer cynhyrchu cwymplen, ac yn ogystal, mae'n caniatáu ichi eu haddasu'n hyblyg. Gadewch i ni ddadansoddi'r dulliau hyn yn fwy manwl.

Proses creu rhestr

I gynhyrchu naidlen, cliciwch ar yr eitemau dewislen ar hyd y llwybr “Data” – “Dilysu Data”. Bydd blwch deialog yn agor lle mae angen i chi ddod o hyd i'r tab “Paramedrau” a chlicio arno os nad yw wedi'i agor o'r blaen. Mae ganddo lawer o osodiadau, ond mae'r eitem “Math o Ddata” yn bwysig i ni. O'r holl ystyron, “Rhestr” yw'r un iawn.

Mae nifer y dulliau a ddefnyddir i roi gwybodaeth ar y rhestr naid yn eithaf mawr.

1. Arwydd annibynnol o elfennau rhestr wedi'u gwahanu gan hanner colon yn y maes “Ffynhonnell” sydd wedi'i leoli ar yr un tab o'r un blwch deialog.
   

   

2. Arwydd rhagarweiniol o werthoedd. Mae'r maes Ffynhonnell yn cynnwys yr ystod lle mae'r wybodaeth ofynnol ar gael.
   

   

3. Yn nodi ystod a enwir. Dull sy'n ailadrodd yr un blaenorol, ond nid oes ond angen enwi'r amrediad yn rhagarweiniol.
   

   

Bydd unrhyw un o'r dulliau hyn yn cynhyrchu'r canlyniad a ddymunir. Gadewch i ni edrych ar ddulliau ar gyfer cynhyrchu cwymplenni mewn sefyllfaoedd bywyd go iawn.

Yn seiliedig ar ddata o'r rhestr

Gadewch i ni ddweud bod gennym dabl sy'n disgrifio'r mathau o wahanol ffrwythau.

I greu rhestr mewn cwymplen yn seiliedig ar y set hon o wybodaeth, mae angen i chi wneud y canlynol:

1. Dewiswch y gell a gadwyd yn ôl ar gyfer y rhestr yn y dyfodol.
2. Dewch o hyd i'r tab Data ar y rhuban. Yno, rydym yn clicio ar "Gwirio data".
   

   

3. Dewch o hyd i'r eitem “Math o Ddata” a newid y gwerth i “Rhestr”.
   

   

4. Yn y maes sy'n dynodi'r opsiwn "Ffynhonnell", nodwch yr ystod a ddymunir. Sylwch fod yn rhaid nodi cyfeiriadau absoliwt fel nad yw'r wybodaeth yn newid wrth gopïo'r rhestr.
   

Yn ogystal, mae yna swyddogaeth i gynhyrchu rhestrau ar unwaith mewn mwy nag un gell. I gyflawni hyn, dylech eu dewis i gyd, a pherfformio'r un camau ag a ddisgrifiwyd yn gynharach. Unwaith eto, mae angen ichi wneud yn siŵr bod tystlythyrau absoliwt yn cael eu hysgrifennu. Os nad oes gan y cyfeiriad arwydd doler wrth ymyl yr enwau colofn a rhes, yna mae angen i chi eu hychwanegu trwy wasgu'r allwedd F4 nes bod yr arwydd $ wrth ymyl y golofn a'r enwau rhes.

Gyda chofnodi data â llaw

Yn y sefyllfa uchod, ysgrifennwyd y rhestr trwy amlygu'r amrediad gofynnol. Mae hwn yn ddull cyfleus, ond weithiau mae angen cofnodi'r data â llaw. Bydd hyn yn ei gwneud hi'n bosibl osgoi dyblygu gwybodaeth yn y llyfr gwaith.

Tybiwch ein bod yn wynebu'r dasg o greu rhestr sy'n cynnwys dau ddewis posibl: ie a na. I gyflawni'r dasg, mae angen:

1. Cliciwch ar y gell am y rhestr.
2. Agorwch “Data” ac yno dewch o hyd i'r adran “Gwirio Data” yn gyfarwydd i ni.
   

   

3. Unwaith eto, dewiswch y math "Rhestr".
   

   

4. Yma mae angen i chi nodi “Ie; Na" fel y ffynhonnell. Gwelwn fod gwybodaeth yn cael ei mewnbynnu â llaw gan ddefnyddio hanner colon ar gyfer cyfrifo.

Ar ôl clicio OK, mae gennym y canlyniad canlynol.

Nesaf, bydd y rhaglen yn creu cwymplen yn awtomatig yn y gell briodol. Yr holl wybodaeth y mae'r defnyddiwr wedi'i nodi fel eitemau yn y rhestr naid. Mae'r rheolau ar gyfer creu rhestr mewn sawl cell yn debyg i'r rhai blaenorol, a'r unig eithriad yw bod yn rhaid i chi nodi'r wybodaeth â llaw gan ddefnyddio hanner colon.

Creu cwymplen gan ddefnyddio'r swyddogaeth OFFSET

Yn ychwanegol at y dull clasurol, mae'n bosibl defnyddio'r swyddogaeth GWAREDUi gynhyrchu dewislenni cwymplen.

Gadewch i ni agor y daflen.

I ddefnyddio'r swyddogaeth ar gyfer y gwymplen, mae angen i chi wneud y canlynol:

1. Dewiswch y gell o ddiddordeb lle rydych chi am osod rhestr y dyfodol.
2. Agorwch y tab “Data” a'r ffenestr “Dilysu Data” yn eu trefn.
   

   

3. Gosodwch “Rhestr”. Gwneir hyn yn yr un modd â'r enghreifftiau blaenorol. Yn olaf, defnyddir y fformiwla ganlynol: =GOFFSET(A$2$;0;0;5). Rydyn ni'n ei nodi lle mae'r celloedd a fydd yn cael eu defnyddio fel dadl wedi'u nodi.

Yna bydd y rhaglen yn creu bwydlen gyda rhestr o ffrwythau.

Y gystrawen ar gyfer hyn yw:

= OFFSET (cyfeirnod, llinell_offset, colofn_offset, [uchder], [lled])

Gwelwn fod gan y swyddogaeth hon 5 dadl. Yn gyntaf, rhoddir cyfeiriad y gell gyntaf i'w wrthbwyso. Mae'r ddwy ddadl nesaf yn nodi faint o resi a cholofnau i'w gwrthbwyso. Wrth siarad amdanom, y ddadl Uchder yw 5 oherwydd ei bod yn cynrychioli uchder y rhestr. 

Rhestr gwympo yn Excel gydag amnewid data (+ gan ddefnyddio'r swyddogaeth OFFSET)

Yn yr achos a roddwyd GWAREDU caniatáu i greu dewislen pop-up lleoli mewn ystod sefydlog. Anfantais y dull hwn yw y bydd yn rhaid i chi olygu'r fformiwla eich hun ar ôl ychwanegu'r eitem.

I greu rhestr ddeinamig gyda chefnogaeth ar gyfer mewnbynnu gwybodaeth newydd, rhaid i chi:

1. Dewiswch y gell o ddiddordeb.
2. Ehangwch y tab “Data” a chliciwch ar “Data Validation”.
3. Yn y ffenestr sy'n agor, dewiswch yr eitem "Rhestr" eto a nodwch y fformiwla ganlynol fel ffynhonnell ddata: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Cliciwch OK.

Mae hwn yn cynnwys swyddogaeth COUNTIF, i benderfynu ar unwaith faint o gelloedd sy'n cael eu llenwi (er bod ganddo nifer llawer mwy o ddefnyddiau, rydyn ni'n ei ysgrifennu yma at ddiben penodol).

Er mwyn i'r fformiwla weithredu'n normal, mae angen olrhain a oes celloedd gwag ar lwybr y fformiwla. Ddylen nhw ddim bod.

Rhestr gwympo gyda data o ddalen arall neu ffeil Excel

Nid yw'r dull clasurol yn gweithio os oes angen i chi gael gwybodaeth o ddogfen arall neu hyd yn oed ddalen sydd wedi'i chynnwys yn yr un ffeil. Ar gyfer hyn, defnyddir y swyddogaeth INDIRECT, sy'n eich galluogi i nodi yn y fformat cywir ddolen i gell sydd wedi'i lleoli mewn dalen arall neu'n gyffredinol - ffeil. Mae angen i chi wneud y canlynol:

1. Gweithredwch y gell lle rydyn ni'n gosod y rhestr.
2. Rydyn ni'n agor y ffenestr rydyn ni'n ei hadnabod yn barod. Yn yr un man ag y gwnaethom nodi ffynonellau ar gyfer ystodau eraill yn flaenorol, nodir fformiwla yn y fformat =INDIRECT("[Rhestr1.xlsx]Taflen1!$A$1:$A$9"). Yn naturiol, yn lle List1 a Sheet1, gallwch fewnosod enwau eich llyfr a thaflen, yn y drefn honno. 

Sylw! Rhoddir enw'r ffeil mewn cromfachau sgwâr. Yn yr achos hwn, ni fydd Excel yn gallu defnyddio'r ffeil sydd ar gau ar hyn o bryd fel ffynhonnell gwybodaeth.

Dylid nodi hefyd bod enw'r ffeil ei hun yn gwneud synnwyr dim ond os yw'r ddogfen ofynnol wedi'i lleoli yn yr un ffolder â'r un lle bydd y rhestr yn cael ei gosod. Os na, yna rhaid i chi nodi cyfeiriad y ddogfen hon yn llawn.

Creu Cwympiadau Dibynnol

Rhestr ddibynyddion yw un y mae dewis y defnyddiwr mewn rhestr arall yn effeithio ar ei gynnwys. Tybiwch fod gennym fwrdd ar agor o'n blaenau sy'n cynnwys tair ystod, a phob un ohonynt wedi cael enw.

Mae angen i chi ddilyn y camau hyn i gynhyrchu rhestrau y mae'r opsiwn a ddewiswyd mewn rhestr arall yn effeithio ar eu canlyniad.

1. Creu rhestr 1af gydag enwau amrediad.
   

   

2. Ar bwynt mynediad y ffynhonnell, mae'r dangosyddion gofynnol yn cael eu hamlygu fesul un.
   

   

3. Creu 2il restr yn dibynnu ar y math o blanhigyn y mae'r person wedi'i ddewis. Fel arall, os byddwch yn nodi coed yn y rhestr gyntaf, yna'r wybodaeth yn yr ail restr fydd "derw, oestrwydd, castanwydd" a thu hwnt. Mae angen ysgrifennu'r fformiwla yn lle mewnbwn y ffynhonnell ddata =INDIRECT(E3). E3 – cell yn cynnwys enw'r amrediad 1.=INDIRECT(E3). E3 – cell gydag enw'r rhestr 1.

Nawr mae popeth yn barod.

Sut i ddewis gwerthoedd lluosog o gwymplen?

Weithiau nid yw'n bosibl rhoi blaenoriaeth i un gwerth yn unig, felly rhaid dewis mwy nag un. Yna mae angen ichi ychwanegu macro at god y dudalen. Gan ddefnyddio'r cyfuniad allweddol Alt + F11 mae'n agor y Golygydd Sylfaenol Gweledol. Ac mae'r cod wedi'i fewnosod yno.

Is-daflen waith breifat_Newid (Targed ByVal Fel Ystod)

    Ar Ail-ddechrau Gwall Nesaf

    Os Ddim yn Croestorri(Targed, Ystod(«Е2:Е9»))) Yn Dim Ac yn Targed.Cells.Count = 1 Yna

        Application.EnableEvents = Gau

        Os yw Len (Target.Offset (0, 1)) = 0 Yna

            Target.Offset (0, 1) = Targed

        arall

            Target.End (xlToRight).Offset (0, 1) = Targed

        Gorffennwch Os

        Targed.ClearContents

        Application.EnableEvents = Gwir

    Gorffennwch Os

Is-End 

Er mwyn i gynnwys y celloedd gael ei ddangos isod, rydyn ni'n mewnosod y cod canlynol yn y golygydd.

Is-daflen waith breifat_Newid (Targed ByVal Fel Ystod)

    Ar Ail-ddechrau Gwall Nesaf

    Os Ddim yn Croestorri(Targed, Ystod («Н2:К2»))) Yn Dim Ac yn Targed.Cells.Count = 1 Yna

        Application.EnableEvents = Gau

        Os yw Len (Target.Offset (1, 0)) = 0 Yna

            Target.Offset (1, 0) = Targed

        arall

            Target.End (xlDown).Offset (1, 0) = Targed

        Gorffennwch Os

        Targed.ClearContents

        Application.EnableEvents = Gwir

    Gorffennwch Os

Is-End

Ac yn olaf, defnyddir y cod hwn i ysgrifennu mewn un gell.

Is-daflen waith breifat_Newid (Targed ByVal Fel Ystod)

    Ar Ail-ddechrau Gwall Nesaf

    Os Ddim yn Croestorri(Targed, Ystod(«C2:C5»))) Yn Dim Ac yn Targed.Cells.Count = 1 Yna

        Application.EnableEvents = Gau

        newVal = Targed

        Cais.Dadwneud

        oldval = Targed

        If Len (oldval) <> 0 A oldval <> newVal Yna

            Targed = Targed &«,» & newVal

        arall

            Targed = newVal

        Gorffennwch Os

        Os Len (newVal) = 0 Yna Target.ClearContents

        Application.EnableEvents = Gwir

    Gorffennwch Os

Is-End

Gellir golygu'r amrediadau.

Sut i wneud cwymplen gyda chwiliad?

Yn yr achos hwn, rhaid i chi ddefnyddio math gwahanol o restr i ddechrau. Mae'r tab "Datblygwr" yn agor, ac ar ôl hynny mae angen i chi glicio neu dapio (os yw'r sgrin yn gyffwrdd) ar yr elfen "Insert" - "ActiveX". Mae ganddo focs combo. Fe'ch anogir i lunio'r rhestr hon, ac ar ôl hynny bydd yn cael ei hychwanegu at y ddogfen.

Ymhellach, mae wedi'i ffurfweddu trwy eiddo, lle mae ystod wedi'i nodi yn yr opsiwn ListFillRange. Mae'r gell lle mae'r gwerth a ddiffinnir gan y defnyddiwr yn cael ei arddangos wedi'i ffurfweddu gan ddefnyddio'r opsiwn LinkedCell. Nesaf, does ond angen i chi ysgrifennu'r nodau cyntaf, oherwydd bydd y rhaglen yn awgrymu gwerthoedd posibl yn awtomatig.

Rhestr gwympo gydag amnewid data awtomatig

Mae yna hefyd swyddogaeth y caiff y data ei ddisodli'n awtomatig ar ôl iddynt gael eu hychwanegu at yr ystod. Mae'n hawdd gwneud hyn:

1. Creu set o gelloedd ar gyfer rhestr y dyfodol. Yn ein hachos ni, mae hwn yn set o liwiau. Rydyn ni'n ei ddewis.
   

   

2. Nesaf, mae angen ei fformatio fel tabl. Mae angen i chi glicio ar y botwm o'r un enw a dewis arddull y tabl.
   

   

   

Nesaf, mae angen i chi gadarnhau'r ystod hon trwy wasgu'r botwm "OK".

Rydyn ni'n dewis y tabl canlyniadol ac yn rhoi enw iddo trwy'r maes mewnbwn sydd wedi'i leoli ar ben colofn A.

Dyna ni, mae yna dabl, a gellir ei ddefnyddio fel sail i gwymplen, y mae angen y canlynol arnoch chi ar ei chyfer:

1. Dewiswch y gell lle mae'r rhestr wedi'i lleoli.
2. Agorwch y deialog Dilysu Data.
   

   

3. Rydyn ni'n gosod y math o ddata i "Rhestr", ac fel gwerthoedd rydyn ni'n rhoi enw'r tabl trwy'r arwydd =.
   

   

   

Popeth, mae'r gell yn barod, ac mae enwau'r lliwiau yn cael eu dangos ynddo, fel yr oedd ei angen arnom yn wreiddiol. Nawr gallwch chi ychwanegu swyddi newydd yn syml trwy eu hysgrifennu mewn cell sydd ychydig yn is yn syth ar ôl yr un olaf.

Dyma fantais y tabl, bod yr ystod yn cynyddu'n awtomatig pan ychwanegir data newydd. Yn unol â hynny, dyma'r ffordd fwyaf cyfleus i ychwanegu rhestr.

Sut i gopïo'r gwymplen?

I gopïo, mae'n ddigon defnyddio'r cyfuniad allweddol Ctrl + C a Ctrl + V. Felly bydd y gwymplen yn cael ei chopïo ynghyd â'r fformatio. I gael gwared ar fformatio, mae angen i chi ddefnyddio past arbennig (yn y ddewislen cyd-destun, mae'r opsiwn hwn yn ymddangos ar ôl copïo'r rhestr), lle mae'r opsiwn "amodau ar werthoedd" wedi'i osod.

Dewiswch bob cell sy'n cynnwys rhestr ostwng

I gyflawni'r dasg hon, rhaid i chi ddefnyddio'r swyddogaeth "Dewis grŵp o gelloedd" yn y grŵp "Dod o hyd i a Dewis".

Ar ôl hynny, bydd blwch deialog yn agor, lle dylech ddewis yr eitemau “Pawb” a “Yr un peth” yn y ddewislen “Dilysu Data”. Mae'r eitem gyntaf yn dewis pob rhestr, ac mae'r ail yn dewis y rhai sy'n debyg i rai penodol yn unig.