Si të krijoni një listë rënëse në Excel

Lista rënëse është një mjet tepër i dobishëm që mund të ndihmojë për ta bërë më të rehatshme punën me informacionin. Kjo bën të mundur që të përmbahen disa vlera në një qelizë në të njëjtën kohë, me të cilat mund të punoni, si me çdo tjetër. Për të zgjedhur atë që ju nevojitet, thjesht klikoni në ikonën e shigjetës, pas së cilës shfaqet një listë e vlerave uXNUMXbuXNUMXbis. Pas zgjedhjes së një të caktuar, qeliza mbushet automatikisht me të dhe formulat rillogariten në bazë të saj.

Excel ofron shumë metoda të ndryshme për gjenerimin e një menuje rënëse, dhe përveç kësaj, ju lejon t'i personalizoni ato në mënyrë fleksibël. Le t'i analizojmë këto metoda në më shumë detaje.

Procesi i krijimit të listës

Për të gjeneruar një menu pop-up, klikoni në artikujt e menysë përgjatë shtegut "Të dhënat" - "Vleresimi i të dhënave". Do të hapet një kuti dialogu ku duhet të gjeni skedën "Parametrat" ​​dhe klikoni mbi të nëse nuk është hapur më parë. Ka shumë cilësime, por artikulli "Lloji i të dhënave" është i rëndësishëm për ne. Nga të gjitha kuptimet, "Lista" është ajo e duhura.

Numri i metodave me të cilat futet informacioni në listën pop-up është mjaft i madh.

1. Tregim i pavarur i elementeve të listës të ndara me një pikëpresje në fushën "Burimi" që ndodhet në të njëjtën skedë të së njëjtës kuti dialogu.
   

   

2. Tregimi paraprak i vlerave. Fusha Burimi përmban gamën ku disponohet informacioni i kërkuar.
   

   

3. Specifikimi i një diapazoni të emërtuar. Një metodë që përsërit atë të mëparshmen, por është e nevojshme vetëm të emërtoni paraprakisht diapazonin.
   

   

Secila prej këtyre metodave do të japë rezultatin e dëshiruar. Le të shohim metodat për gjenerimin e listave rënëse në situata të jetës reale.

Bazuar në të dhënat nga lista

Le të themi se kemi një tabelë që përshkruan llojet e frutave të ndryshme.

Për të krijuar një listë në një menu drop-down bazuar në këtë grup informacioni, duhet të bëni sa më poshtë:

1. Zgjidhni qelizën e rezervuar për listën e ardhshme.
2. Gjeni skedën e të dhënave në shirit. Atje klikojmë në "Verifiko të dhënat".
   

   

3. Gjeni artikullin "Lloji i të dhënave" dhe kaloni vlerën në "Lista".
   

   

4. Në fushën që tregon opsionin "Burimi", vendosni gamën e dëshiruar. Ju lutemi vini re se referencat absolute duhet të specifikohen në mënyrë që kur kopjoni listën, informacioni të mos zhvendoset.
   

Përveç kësaj, ekziston një funksion për të gjeneruar lista menjëherë në më shumë se një qelizë. Për ta arritur këtë, duhet t'i zgjidhni të gjitha dhe të kryeni të njëjtat hapa siç përshkruhen më parë. Përsëri, duhet të siguroheni që janë shkruar referencat absolute. Nëse adresa nuk ka një shenjë dollari pranë emrave të kolonave dhe rreshtave, atëherë duhet t'i shtoni ato duke shtypur tastin F4 derisa shenja $ të jetë pranë emrave të kolonave dhe rreshtave.

Me regjistrim manual të të dhënave

Në situatën e mësipërme, lista u shkrua duke theksuar diapazonin e kërkuar. Kjo është një metodë e përshtatshme, por ndonjëherë është e nevojshme të regjistrohen manualisht të dhënat. Kjo do të bëjë të mundur shmangien e dyfishimit të informacionit në librin e punës.

Supozoni se jemi përballur me detyrën për të krijuar një listë që përmban dy zgjedhje të mundshme: po dhe jo. Për të realizuar detyrën është e nevojshme:

1. Klikoni në qelizë për listën.
2. Hapni "Të dhënat" dhe atje gjeni seksionin "Kontrolli i të dhënave" të njohur për ne.
   

   

3. Përsëri, zgjidhni llojin "Lista".
   

   

4. Këtu ju duhet të shkruani "Po; Jo” si burim. Ne shohim që informacioni futet manualisht duke përdorur një pikëpresje për numërim.

Pasi klikojmë OK, kemi rezultatin e mëposhtëm.

Më pas, programi do të krijojë automatikisht një meny rënëse në qelizën e duhur. Të gjitha informacionet që përdoruesi ka specifikuar si artikuj në listën pop-up. Rregullat për krijimin e një liste në disa qeliza janë të ngjashme me ato të mëparshme, me përjashtimin e vetëm që duhet të specifikoni manualisht informacionin duke përdorur një pikëpresje.

Krijimi i një liste rënëse duke përdorur funksionin OFFSET

Përveç metodës klasike, është e mundur të përdoret funksioni SHPENZIMIpër të gjeneruar menutë rënëse.

Le të hapim fletën.

Për të përdorur funksionin për listën rënëse, duhet të bëni sa më poshtë:

1. Zgjidhni qelizën e interesit ku dëshironi të vendosni listën e ardhshme.
2. Hapni skedën "Të dhënat" dhe dritaren "Vleresimi i të dhënave" në sekuencë.
   

   

3. Vendosni "Lista". Kjo është bërë në të njëjtën mënyrë si shembujt e mëparshëm. Në fund, përdoret formula e mëposhtme: =OFFSET (A$2$;0;0;5). E fusim aty ku specifikohen qelizat që do të përdoren si argument.

Më pas programi do të krijojë një menu me një listë frutash.

Sintaksa për këtë është:

=OFFSET (referenca, zhvendosja e linjës, zhvendosja e kolonës, [lartësia], [gjerësia])

Shohim që ky funksion ka 5 argumente. Së pari, jepet adresa e parë e qelizës që do të kompensohet. Dy argumentet e ardhshme përcaktojnë sa rreshta dhe kolona duhet të kompensohen. Duke folur për ne, argumenti Height është 5 sepse përfaqëson lartësinë e listës. 

Lista rënëse në Excel me zëvendësimin e të dhënave (+ duke përdorur funksionin OFFSET)

Në rastin e dhënë SHPENZIMI lejohet të krijojë një menu pop-up të vendosur në një gamë fikse. Disavantazhi i kësaj metode është se pas shtimit të artikullit, do të duhet ta modifikoni vetë formulën.

Për të krijuar një listë dinamike me mbështetje për futjen e informacionit të ri, duhet:

1. Zgjidhni qelizën e interesit.
2. Zgjeroni skedën "Të dhënat" dhe klikoni në "Vleresimi i të dhënave".
3. Në dritaren që hapet, zgjidhni përsëri artikullin "Lista" dhe specifikoni formulën e mëposhtme si burimin e të dhënave: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klikoni OK.

Kjo përmban një funksion countif, për të përcaktuar menjëherë sa qeliza janë mbushur (edhe pse ka një numër shumë më të madh përdorimesh, ne thjesht e shkruajmë këtu për një qëllim të caktuar).

Në mënyrë që formula të funksionojë normalisht, është e nevojshme të gjurmohet nëse ka qeliza boshe në rrugën e formulës. Ata nuk duhet të jenë.

Lista rënëse me të dhëna nga një fletë tjetër ose skedar Excel

Metoda klasike nuk funksionon nëse keni nevojë të merrni informacion nga një dokument tjetër apo edhe nga një fletë që gjendet në të njëjtin skedar. Për këtë përdoret funksioni INDIREKT, e cila ju lejon të vendosni në formatin e duhur një lidhje me një qelizë të vendosur në një fletë tjetër ose në përgjithësi - një skedar. Ju duhet të bëni sa më poshtë:

1. Aktivizoni qelizën ku vendosim listën.
2. Ne hapim dritaren që tashmë e dimë. Në të njëjtin vend ku kemi treguar më parë burime për vargjet e tjera, një formulë tregohet në format =INDIRECT(“[Lista1.xlsx]Fleta1!$A$1:$A$9”). Natyrisht, në vend të List1 dhe Sheet1, mund të futni përkatësisht emrat e librit dhe të fletës. 

Kujdes! Emri i skedarit jepet në kllapa katrore. Në këtë rast, Excel nuk do të jetë në gjendje të përdorë skedarin që aktualisht është i mbyllur si burim informacioni.

Duhet të theksohet gjithashtu se vetë emri i skedarit ka kuptim vetëm nëse dokumenti i kërkuar ndodhet në të njëjtën dosje me atë ku do të futet lista. Nëse jo, atëherë duhet të specifikoni adresën e këtij dokumenti plotësisht.

Krijimi i Dropdowns-ve të Varur

Një listë e varur është ajo përmbajtja e së cilës ndikohet nga zgjedhja e përdoruesit në një listë tjetër. Supozoni se kemi një tabelë të hapur para nesh që përmban tre vargje, secilës prej të cilave i është dhënë një emër.

Duhet të ndiqni këto hapa për të gjeneruar lista, rezultati i të cilave ndikohet nga opsioni i zgjedhur në një listë tjetër.

1. Krijo listën e parë me emrat e gamës.
   

   

2. Në pikën hyrëse të burimit, treguesit e kërkuar theksohen një nga një.
   

   

3. Krijo një listë të dytë në varësi të llojit të bimës që personi ka zgjedhur. Përndryshe, nëse specifikoni pemë në listën e parë, atëherë informacioni në listën e dytë do të jetë "lisi, shkoza, gështenja" dhe më gjerë. Është e nevojshme të shkruani formulën në vendin e hyrjes së burimit të të dhënave =INDIREKT(E3). E3 – qeliza që përmban emrin e rangut 1.=INDIRECT(E3). E3 – qeliza me emrin e listës 1.

Tani gjithçka është gati.

Si të zgjidhni vlera të shumta nga një listë rënëse?

Ndonjëherë nuk është e mundur t'i jepet përparësi vetëm një vlere, kështu që duhet të zgjidhen më shumë se një. Pastaj duhet të shtoni një makro në kodin e faqes. Duke përdorur kombinimin e tasteve Alt + F11 hapet Redaktori Visual Basic. Dhe kodi është futur atje.

Nën-Fletë pune private_Ndrysho (ByVal Target As Range)

    Në Error Resume Next

    Nëse nuk kryqëzohet (Synimi, diapazoni («Е2:Е9»)) Nuk është asgjë dhe objektiv. Qelizat. Numërimi = 1 Pastaj

        Aplikimi.EnableEvents = False

        Nëse Len (Target.Offset (0, 1)) = 0 Pastaj

            Target.Offset (0, 1) = Target

        tjetër

            Target.End (xlToDjathtas) .Offset (0, 1) = Target

        Nëse End

        Synimi.ClearContents

        Aplikimi.EnableEvents = E vërtetë

    Nëse End

Sub End 

Në mënyrë që përmbajtja e qelizave të tregohet më poshtë, ne futim kodin e mëposhtëm në redaktues.

Nën-Fletë pune private_Ndrysho (ByVal Target As Range)

    Në Error Resume Next

    Nëse nuk kryqëzohet (Synimi, diapazoni («Н2:К2»)) Nuk është asgjë dhe objektiv. Qelizat. Numërimi = 1 Pastaj

        Aplikimi.EnableEvents = False

        Nëse Len (Target.Offset (1, 0)) = 0 Pastaj

            Target.Offset (1, 0) = Target

        tjetër

            Target.End (xlPoshtë) .Offset (1, 0) = Target

        Nëse End

        Synimi.ClearContents

        Aplikimi.EnableEvents = E vërtetë

    Nëse End

Sub End

Dhe së fundi, ky kod përdoret për të shkruar në një qelizë.

Nën-Fletë pune private_Ndrysho (ByVal Target As Range)

    Në Error Resume Next

    Nëse nuk ndërpritet (Synimi, diapazoni («C2:C5»)) Nuk është asgjë dhe objektivi. Qelizat. Numërimi = 1 Pastaj

        Aplikimi.EnableEvents = False

        newVal = Synimi

        Aplikimi. Zhb

        oldval = Synim

        Nëse Len (oldval) <> 0 Dhe oldval <> newVal Pastaj

            Target = Target & «,» & newVal

        tjetër

            Synimi = newVal

        Nëse End

        Nëse Len (newVal) = 0 Pastaj Target.ClearContents

        Aplikimi.EnableEvents = E vërtetë

    Nëse End

Sub End

Gama janë të modifikueshme.

Si të krijoni një listë rënëse me një kërkim?

Në këtë rast, fillimisht duhet të përdorni një lloj tjetër liste. Hapet skeda "Zhvilluesi", pas së cilës duhet të klikoni ose trokitni (nëse ekrani është me prekje) në elementin "Fut" - "ActiveX". Ka një kuti të kombinuar. Do t'ju kërkohet të vizatoni këtë listë, pas së cilës ajo do të shtohet në dokument.

Më tej, ai konfigurohet përmes pronave, ku një varg specifikohet në opsionin ListFillRange. Qeliza ku shfaqet vlera e përcaktuar nga përdoruesi konfigurohet duke përdorur opsionin LinkedCell. Më pas, ju vetëm duhet të shkruani karakteret e para, pasi programi do të sugjerojë automatikisht vlerat e mundshme.

Lista rënëse me zëvendësimin automatik të të dhënave

Ekziston gjithashtu një funksion që të dhënat zëvendësohen automatikisht pasi ato të shtohen në interval. Është e lehtë ta bësh këtë:

1. Krijo një grup qelizash për listën e ardhshme. Në rastin tonë, ky është një grup ngjyrash. Ne e zgjedhim atë.
   

   

2. Tjetra, ajo duhet të formatohet si një tabelë. Duhet të klikoni butonin me të njëjtin emër dhe të zgjidhni stilin e tabelës.
   

   

   

Tjetra, duhet të konfirmoni këtë interval duke shtypur butonin "OK".

Ne zgjedhim tabelën që rezulton dhe i japim një emër përmes fushës së hyrjes që ndodhet në krye të kolonës A.

Kjo është e gjitha, ekziston një tabelë dhe mund të përdoret si bazë për një listë rënëse, për të cilën ju nevojitet:

1. Zgjidhni qelizën ku ndodhet lista.
2. Hapni dialogun e Validimit të të Dhënave.
   

   

3. Llojin e të dhënave e vendosim në “List”, dhe si vlera japim emrin e tabelës përmes shenjës =.
   

   

   

Gjithçka, qeliza është gati, dhe emrat e ngjyrave tregohen në të, siç na duheshin fillimisht. Tani mund të shtoni pozicione të reja thjesht duke i shkruar ato në një qelizë të vendosur pak më poshtë menjëherë pas asaj të fundit.

Ky është avantazhi i tabelës, se diapazoni rritet automatikisht kur shtohen të dhëna të reja. Prandaj, kjo është mënyra më e përshtatshme për të shtuar një listë.

Si të kopjoni listën rënëse?

Për të kopjuar, mjafton të përdorni kombinimin e tastit Ctrl + C dhe Ctrl + V. Pra, lista rënëse do të kopjohet së bashku me formatimin. Për të hequr formatimin, duhet të përdorni një ngjitje të veçantë (në menunë e kontekstit, ky opsion shfaqet pas kopjimit të listës), ku është vendosur opsioni "kushtet mbi vlerat".

Zgjidhni të gjitha qelizat që përmbajnë një listë rënëse

Për të kryer këtë detyrë, duhet të përdorni funksionin "Zgjidh një grup qelizash" në grupin "Gjej dhe Zgjidh".

Pas kësaj, do të hapet një kuti dialogu, ku duhet të zgjidhni artikujt "Të gjitha" dhe "Të njëjtat" në menynë "Vleresimi i të dhënave". Artikulli i parë zgjedh të gjitha listat dhe i dyti zgjedh vetëm ato që janë të ngjashme me ato të caktuara.