Kaip sukurti išskleidžiamąjį sąrašą „Excel“.

Išskleidžiamasis sąrašas yra nepaprastai naudingas įrankis, galintis padėti patogiau dirbti su informacija. Tai leidžia langelyje vienu metu turėti kelias reikšmes, su kuriomis galite dirbti, kaip ir su bet kuriomis kitomis. Norėdami pasirinkti reikiamą, tiesiog spustelėkite rodyklės piktogramą, po kurios bus rodomas uXNUMXbuXNUMXbis verčių sąrašas. Pasirinkus konkretų, langelis juo automatiškai užpildomas, pagal jį perskaičiuojamos formulės.

„Excel“ suteikia daug skirtingų metodų, kaip generuoti išskleidžiamąjį meniu, be to, leidžia juos lanksčiai tinkinti. Panagrinėkime šiuos metodus išsamiau.

Sąrašo sudarymo procesas

Norėdami sukurti iššokantįjį meniu, spustelėkite meniu elementus, esančius kelyje „Duomenys“ – „Duomenų patvirtinimas“. Atsidarys dialogo langas, kuriame turėsite rasti skirtuką „Parametrai“ ir spustelėti jį, jei jis anksčiau nebuvo atidarytas. Jame yra daug nustatymų, tačiau mums svarbus elementas „Duomenų tipas“. Iš visų reikšmių „sąrašas“ yra teisingas.

Metodų, kuriais informacija įvedama į iššokantįjį sąrašą, skaičius yra gana didelis.

1. Nepriklausomas sąrašo elementų, atskirtų kabliataškiu, rodymas lauke „Šaltinis“, esančiame tame pačiame to paties dialogo lango skirtuke.
   

   

2. Preliminarus verčių nurodymas. Lauke Šaltinis yra diapazonas, kuriame yra reikalinga informacija.
   

   

3. Nurodykite pavadintą diapazoną. Metodas, kuris pakartoja ankstesnį, tačiau reikia tik preliminariai pavadinti diapazoną.
   

   

Bet kuris iš šių metodų duos norimą rezultatą. Pažvelkime į išskleidžiamųjų sąrašų generavimo būdus realiose situacijose.

Remiantis sąrašo duomenimis

Tarkime, kad turime lentelę, kurioje aprašomos skirtingų vaisių rūšys.

Norėdami sukurti sąrašą išskleidžiamajame meniu pagal šį informacijos rinkinį, turite atlikti šiuos veiksmus:

1. Pasirinkite langelį, rezervuotą būsimam sąrašui.
2. Juostelėje raskite skirtuką Duomenys. Ten paspaudžiame „Patikrinti duomenis“.
   

   

3. Raskite elementą „Duomenų tipas“ ir perjunkite reikšmę į „Sąrašas“.
   

   

4. Lauke, žyminčiame parinktį „Šaltinis“, įveskite norimą diapazoną. Atkreipkite dėmesį, kad turi būti nurodytos absoliučios nuorodos, kad kopijuojant sąrašą informacija nepasislinktų.
   

Be to, yra funkcija generuoti sąrašus vienu metu daugiau nei viename langelyje. Norėdami tai padaryti, turėtumėte pasirinkti juos visus ir atlikti tuos pačius veiksmus, kaip aprašyta anksčiau. Vėlgi, turite įsitikinti, kad yra parašytos absoliučios nuorodos. Jei adresas neturi dolerio ženklo šalia stulpelių ir eilučių pavadinimų, juos reikia pridėti paspausdami klavišą F4, kol šalia stulpelių ir eilučių pavadinimų atsiras $ ženklas.

Su rankiniu duomenų įrašymu

Aukščiau pateiktoje situacijoje sąrašas buvo parašytas paryškinant reikiamą diapazoną. Tai patogus būdas, tačiau kartais reikia duomenis įrašyti rankiniu būdu. Tai leis išvengti informacijos dubliavimo darbaknygėje.

Tarkime, kad mes susiduriame su užduotimi sukurti sąrašą, kuriame būtų du galimi pasirinkimai: taip ir ne. Norint atlikti užduotį, būtina:

1. Spustelėkite langelį, kad pamatytumėte sąrašą.
2. Atidarykite „Duomenys“ ir ten raskite mums pažįstamą skyrių „Duomenų patikra“.
   

   

3. Vėlgi, pasirinkite „Sąrašo“ tipą.
   

   

4. Čia reikia įvesti „Taip; Ne“, kaip šaltinis. Matome, kad informacija įvedama rankiniu būdu naudojant kabliataškį.

Paspaudę Gerai, gauname tokį rezultatą.

Tada programa automatiškai sukurs išskleidžiamąjį meniu atitinkamoje langelyje. Visa informacija, kurią vartotojas nurodė kaip elementus iššokančiame sąraše. Sąrašo kūrimo keliuose langeliuose taisyklės yra panašios į ankstesnes, su vienintele išimtimi, kad informaciją turite nurodyti rankiniu būdu, naudodami kabliataškį.

Išskleidžiamojo sąrašo kūrimas naudojant OFFSET funkciją

Be klasikinio metodo, galima naudoti funkciją NAIKINIMASNorėdami sukurti išskleidžiamuosius meniu.

Atidarykime lapą.

Norėdami naudoti išskleidžiamojo sąrašo funkciją, turite atlikti šiuos veiksmus:

1. Pasirinkite dominantį langelį, kuriame norite įdėti būsimą sąrašą.
2. Iš eilės atidarykite skirtuką „Duomenys“ ir „Duomenų patvirtinimo“ langą.
   

   

3. Paruošti sąrašą". Tai daroma taip pat, kaip ir ankstesniuose pavyzdžiuose. Galiausiai naudojama ši formulė: =OFFSET(A$2$;0;0;5). Įvedame jį ten, kur nurodomi langeliai, kurie bus naudojami kaip argumentas.

Tada programa sukurs meniu su vaisių sąrašu.

Tam skirta sintaksė:

=OFFSET(nuoroda,linijos_poslinkis,stulpelio_poslinkis,[aukštis],[plotis])

Matome, kad ši funkcija turi 5 argumentus. Pirmiausia pateikiamas pirmasis poslinkio langelio adresas. Kiti du argumentai nurodo, kiek eilučių ir stulpelių reikia kompensuoti. Kalbant apie mus, aukščio argumentas yra 5, nes jis reiškia sąrašo aukštį. 

Išskleidžiamasis sąrašas programoje „Excel“ su duomenų pakeitimu (+ naudojant funkciją OFFSET)

Pateiktu atveju NAIKINIMAS leidžiama sukurti iššokantįjį meniu, esantį fiksuotame diapazone. Šio metodo trūkumas yra tas, kad pridėję elementą turėsite patys redaguoti formulę.

Norėdami sukurti dinaminį sąrašą su naujos informacijos įvedimo palaikymu, turite:

1. Pasirinkite dominančią langelį.
2. Išplėskite skirtuką „Duomenys“ ir spustelėkite „Duomenų patvirtinimas“.
3. Atsidariusiame lange dar kartą pasirinkite elementą „Sąrašas“ ir nurodykite šią formulę kaip duomenų šaltinį: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Spustelėkite OK (gerai).

Jame yra funkcija COUNTIF, norėdami iš karto nustatyti, kiek langelių užpildyta (nors jis turi daug didesnį panaudojimo skaičių, mes tiesiog rašome jį čia tam tikram tikslui).

Kad formulė veiktų normaliai, reikia atsekti, ar formulės kelyje nėra tuščių langelių. Jų neturėtų būti.

Išskleidžiamasis sąrašas su duomenimis iš kito lapo arba Excel failo

Klasikinis metodas neveikia, jei reikia gauti informaciją iš kito dokumento ar net lapo, esančio tame pačiame faile. Tam naudojama funkcija NETIESIOGINĖS, kuri leidžia teisingu formatu įvesti nuorodą į langelį, esantį kitame lape arba apskritai – failą. Turite atlikti šiuos veiksmus:

1. Suaktyvinkite langelį, kuriame pateikiame sąrašą.
2. Atsidarome jau pažįstamą langą. Toje pačioje vietoje, kur anksčiau nurodėme kitų diapazonų šaltinius, formate nurodoma formulė =INDIRECT("[List1.xlsx]Sheet1!$A$1:$A$9"). Natūralu, kad vietoj 1 sąrašo ir lapo 1 galite atitinkamai įterpti savo knygų ir lapų pavadinimus. 

Dėmesio! Failo pavadinimas pateikiamas laužtiniuose skliaustuose. Tokiu atveju „Excel“ negalės naudoti šiuo metu uždaryto failo kaip informacijos šaltinio.

Taip pat reikėtų pažymėti, kad pats failo pavadinimas yra prasmingas tik tuo atveju, jei reikalingas dokumentas yra tame pačiame aplanke, kuriame bus įterptas sąrašas. Jei ne, tuomet turite nurodyti visą šio dokumento adresą.

Priklausomų išskleidžiamųjų meniu kūrimas

Priklausomas sąrašas yra tas, kurio turiniui įtakos turi vartotojo pasirinkimas kitame sąraše. Tarkime, kad priešais mus yra atidaryta lentelė, kurioje yra trys diapazonai, kurių kiekvienas gavo pavadinimą.

Norėdami sukurti sąrašus, kurių rezultatui įtakos turi kitame sąraše pasirinkta parinktis, turite atlikti šiuos veiksmus.

1. Sukurkite pirmąjį sąrašą su diapazonų pavadinimais.
   

   

2. Šaltinio įėjimo taške reikalingi rodikliai paryškinami po vieną.
   

   

3. Sukurkite 2 sąrašą, atsižvelgdami į asmens pasirinktą augalo tipą. Arba, jei pirmajame sąraše nurodysite medžius, antrojo sąrašo informacija bus „ąžuolas, skroblas, kaštonas“ ir daugiau. Duomenų šaltinio įvesties vietoje būtina užrašyti formulę =NETIESIOGINĖ(E3). E3 – langelis, kuriame yra diapazono pavadinimas 1.=NETIESIOGINIS(E3). E3 – langelis su sąrašo pavadinimu 1.

Dabar viskas paruošta.

Kaip iš išskleidžiamojo sąrašo pasirinkti kelias reikšmes?

Kartais negalima teikti pirmenybės tik vienai reikšmei, todėl reikia pasirinkti daugiau nei vieną. Tada prie puslapio kodo turite pridėti makrokomandą. Naudojant klavišų kombinaciją Alt + F11 atidaromas Visual Basic redaktorius. Ir kodas ten įdėtas.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Toliau

    Jei nesikerta (tikslas, diapazonas («Е2:Е9»)) yra niekas ir taikinys. langeliai. skaičius = 1 tada

        Application.EnableEvents = Netiesa

        Jei Len (Target.Offset (0, 1)) = 0 Tada

            Target.Offset (0, 1) = Tikslas

        kitas

            Tikslas.Pabaiga (xlToRight) .Offset (0, 1) = Tikslas

        End If

        Target.ClearContents

        Application.EnableEvents = Tiesa

    End If

End Sub 

Kad langelių turinys būtų rodomas žemiau, į redaktorių įterpiame šį kodą.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Toliau

    Jei nesikerta (tikslas, diapazonas («Н2:К2»)) yra niekas ir taikinys. langeliai. skaičius = 1 tada

        Application.EnableEvents = Netiesa

        Jei Len (Target.Offset (1, 0)) = 0 Tada

            Target.Offset (1, 0) = Tikslas

        kitas

            Tikslas.Pabaiga (xlDown) .Poslinkis (1, 0) = Tikslas

        End If

        Target.ClearContents

        Application.EnableEvents = Tiesa

    End If

End Sub

Ir galiausiai šis kodas naudojamas rašyti vienoje ląstelėje.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Error Resume Toliau

    Jei nesikerta (tikslas, diapazonas («C2:C5»)) yra niekas ir taikinys. langeliai. skaičius = 1 tada

        Application.EnableEvents = Netiesa

        newVal = Tikslas

        Taikymas.Atšaukti

        oldval = Tikslas

        Jei Len (oldval) <> 0 Ir oldval <> newVal Tada

            Tikslas = Tikslas & «,» & naujasVal

        kitas

            Tikslas = naujasVal

        End If

        Jei Len (newVal) = 0 Tada Target.ClearContents

        Application.EnableEvents = Tiesa

    End If

End Sub

Diapazonas galima redaguoti.

Kaip su paieška sudaryti išskleidžiamąjį sąrašą?

Tokiu atveju iš pradžių turite naudoti kitokio tipo sąrašą. Atsidaro skirtukas „Kūrėjas“, po kurio reikia spustelėti arba bakstelėti (jei ekranas liečiamas) elementą „Įterpti“ – „ActiveX“. Turi kombinuotąjį langelį. Būsite paraginti sudaryti šį sąrašą, po kurio jis bus įtrauktas į dokumentą.

Be to, jis sukonfigūruojamas per ypatybes, kur diapazonas nurodomas parinktyje ListFillRange. Langelis, kuriame rodoma vartotojo nustatyta reikšmė, sukonfigūruojamas naudojant parinktį LinkedCell. Toliau tereikia užsirašyti pirmuosius simbolius, nes programa automatiškai pasiūlys galimas reikšmes.

Išskleidžiamasis sąrašas su automatiniu duomenų pakeitimu

Taip pat yra funkcija, kad duomenys pakeičiami automatiškai, kai jie įtraukiami į diapazoną. Tai lengva padaryti:

1. Sukurkite būsimo sąrašo langelių rinkinį. Mūsų atveju tai yra spalvų rinkinys. Mes jį pasirenkame.
   

   

2. Tada jis turi būti suformatuotas kaip lentelė. Turite spustelėti to paties pavadinimo mygtuką ir pasirinkti lentelės stilių.
   

   

   

Tada turite patvirtinti šį diapazoną paspausdami mygtuką „Gerai“.

Mes pasirenkame gautą lentelę ir suteikiame jai pavadinimą per įvesties lauką, esantį A stulpelio viršuje.

Tai viskas, yra lentelė ir ji gali būti naudojama kaip išskleidžiamojo sąrašo pagrindas, kuriam jums reikia:

1. Pasirinkite langelį, kuriame yra sąrašas.
2. Atidarykite duomenų patvirtinimo dialogo langą.
   

   

3. Duomenų tipą nustatome į „Sąrašas“, o kaip reikšmes pateikiame lentelės pavadinimą per ženklą =.
   

   

   

Viskas, langelis paruoštas, o joje rodomi spalvų pavadinimai, kaip mums iš pradžių reikėjo. Dabar galite pridėti naujų pozicijų tiesiog įrašydami jas langelyje, esančiame šiek tiek žemiau iškart po paskutinės.

Tai yra lentelės pranašumas, kad diapazonas automatiškai didėja, kai pridedami nauji duomenys. Atitinkamai, tai yra patogiausias būdas įtraukti sąrašą.

Kaip nukopijuoti išskleidžiamąjį sąrašą?

Norėdami nukopijuoti, pakanka naudoti klavišų kombinaciją Ctrl + C ir Ctrl + V. Taigi išskleidžiamasis sąrašas bus nukopijuotas kartu su formatavimu. Norėdami pašalinti formatavimą, turite naudoti specialų įklijavimą (kontekstiniame meniu ši parinktis pasirodo nukopijavus sąrašą), kur nustatyta parinktis „sąlygos dėl verčių“.

Pasirinkite visus langelius, kuriuose yra išskleidžiamasis sąrašas

Norėdami atlikti šią užduotį, turite naudoti grupės „Rasti ir pasirinkti“ funkciją „Pasirinkti langelių grupę“.

Po to atsidarys dialogo langas, kuriame meniu „Duomenų patvirtinimas“ turėtumėte pasirinkti elementus „Visi“ ir „Tie patys“. Pirmasis elementas pasirenka visus sąrašus, o antrasis – tik tuos, kurie yra panašūs į tam tikrus.