Kako narediti spustni seznam v Excelu

Spustni seznam je izjemno uporabno orodje, ki vam lahko pomaga narediti delo z informacijami udobnejše. Omogoča, da v celici vsebuje več vrednosti naenkrat, s katerimi lahko delate, kot z drugimi. Če želite izbrati tisto, ki jo potrebujete, samo kliknite ikono puščice, po kateri se prikaže seznam vrednosti uXNUMXbuXNUMXbis. Ko izberete določeno, se celica samodejno zapolni z njo in na podlagi nje se preračunajo formule.

Excel ponuja veliko različnih metod za ustvarjanje spustnega menija, poleg tega pa vam omogoča, da jih prilagodljivo prilagodite. Analizirajmo te metode podrobneje.

Postopek ustvarjanja seznama

Če želite ustvariti pojavni meni, kliknite elemente menija na poti »Podatki« – »Preverjanje podatkov«. Odpre se pogovorno okno, kjer morate najti zavihek »Parametri« in ga klikniti, če še ni bil odprt. Ima veliko nastavitev, vendar je za nas pomemben element »Vrsta podatkov«. Od vseh pomenov je "seznam" pravi.

Število metod, s katerimi se informacije vnašajo v pojavni seznam, je precej veliko.

1. Neodvisna navedba elementov seznama, ločenih s podpičjem v polju »Vir«, ki se nahaja na istem zavihku istega pogovornega okna.
   

   

2. Predhodna navedba vrednosti. Polje Izvor vsebuje obseg, kjer so na voljo zahtevane informacije.
   

   

3. Določanje imenovanega obsega. Metoda, ki ponavlja prejšnjo, vendar je potrebno le predhodno poimenovati obseg.
   

   

Katera koli od teh metod bo dala želeni rezultat. Oglejmo si metode za ustvarjanje spustnih seznamov v resničnih situacijah.

Na podlagi podatkov s seznama

Recimo, da imamo tabelo, ki opisuje vrste različnega sadja.

Če želite ustvariti seznam v spustnem meniju na podlagi tega nabora informacij, morate narediti naslednje:

1. Izberite celico, rezervirano za prihodnji seznam.
2. Poiščite zavihek Podatki na traku. Tam kliknemo »Preveri podatke«.
   

   

3. Poiščite postavko »Vrsta podatkov« in spremenite vrednost na »Seznam«.
   

   

4. V polje, ki označuje možnost »Vir«, vnesite želeni obseg. Upoštevajte, da je treba navesti absolutne reference, da se pri kopiranju seznama informacije ne premaknejo.
   

Poleg tega obstaja funkcija za ustvarjanje seznamov hkrati v več kot eni celici. Če želite to doseči, jih morate izbrati vse in izvesti enake korake, kot je opisano prej. Spet se morate prepričati, da so napisane absolutne reference. Če naslov nima znaka za dolar ob imenih stolpcev in vrstic, jih morate dodati tako, da pritiskate tipko F4, dokler ni znak $ poleg imen stolpcev in vrstic.

Z ročnim zapisom podatkov

V zgornji situaciji je bil seznam napisan z označevanjem zahtevanega obsega. To je priročna metoda, vendar je včasih potrebno ročno zabeležiti podatke. Tako se bo mogoče izogniti podvajanju informacij v delovnem zvezku.

Recimo, da smo soočeni z nalogo, da ustvarimo seznam, ki vsebuje dve možni izbiri: da in ne. Za izvedbo naloge je potrebno:

1. Kliknite na celico za seznam.
2. Odprite »Podatki« in tam poiščite razdelek »Preverjanje podatkov«, ki nam je znan.
   

   

3. Ponovno izberite vrsto »Seznam«.
   

   

4. Tukaj morate vnesti »Da; Ne« kot vir. Vidimo, da so informacije vnesene ročno s podpičjem za naštevanje.

Po kliku V redu imamo naslednji rezultat.

Nato bo program samodejno ustvaril spustni meni v ustrezni celici. Vse informacije, ki jih je uporabnik določil kot elemente na pojavnem seznamu. Pravila za ustvarjanje seznama v več celicah so podobna prejšnjim, z edino izjemo, da morate podatke vnesti ročno s podpičjem.

Ustvarjanje spustnega seznama s funkcijo OFFSET

Poleg klasične metode je možna uporaba funkcije ODSTRANJEVANJEza ustvarjanje spustnih menijev.

Odprimo list.

Če želite uporabiti funkcijo za spustni seznam, morate narediti naslednje:

1. Izberite celico, ki vas zanima, kamor želite postaviti prihodnji seznam.
2. Zaporedoma odprite zavihek »Podatki« in okno »Preverjanje podatkov«.
   

   

3. Nastavite "Seznam". To se naredi na enak način kot v prejšnjih primerih. Na koncu se uporabi naslednja formula: =ODOMIK(A$2$;0;0;5). Vpišemo ga tam, kjer so določene celice, ki bodo uporabljene kot argument.

Nato bo program ustvaril meni s seznamom sadja.

Sintaksa za to je:

=OFFSET(referenca,odmik_vrstice,odmik_stolpca,[višina],[širina])

Vidimo, da ima ta funkcija 5 argumentov. Najprej je podan prvi naslov celice, ki bo zamaknjen. Naslednja dva argumenta določata, koliko vrstic in stolpcev je treba zamakniti. Ko smo že pri nas, je argument Višina 5, ker predstavlja višino seznama. 

Spustni seznam v Excelu z zamenjavo podatkov (+ uporaba funkcije OFFSET)

V danem primeru ODSTRANJEVANJE dovoljeno ustvariti pojavni meni v fiksnem obsegu. Pomanjkljivost te metode je, da boste morali po dodajanju artikla sami urediti formulo.

Če želite ustvariti dinamični seznam s podporo za vnos novih informacij, morate:

1. Izberite celico, ki vas zanima.
2. Razširite zavihek »Podatki« in kliknite »Preverjanje podatkov«.
3. V oknu, ki se odpre, znova izberite postavko »Seznam« in kot vir podatkov določite naslednjo formulo: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Kliknite OK.

To vsebuje funkcijo COUNTIF, da takoj ugotovimo, koliko celic je zapolnjenih (čeprav ima veliko večje število uporab, ga tukaj samo zapišemo za določen namen).

Za normalno delovanje formule je potrebno spremljati, ali so na poti formule prazne celice. Ne bi smeli biti.

Spustni seznam s podatki iz drugega lista ali Excelove datoteke

Klasična metoda ne deluje, če potrebujete podatke iz drugega dokumenta ali celo lista v isti datoteki. Za to se uporablja funkcija INDIRECT, ki vam omogoča, da v pravilni obliki vnesete povezavo do celice, ki se nahaja na drugem listu ali na splošno – datoteko. Narediti morate naslednje:

1. Aktiviramo celico, kamor postavimo seznam.
2. Odpremo okno, ki ga že poznamo. Na istem mestu, kjer smo prej navedli vire za druge obsege, je v formatu navedena formula =INDIRECT(“[Seznam1.xlsx]List1!$A$1:$A$9”). Seveda lahko namesto List1 in Sheet1 vstavite imena svoje knjige oziroma listov. 

Pozor! Ime datoteke je navedeno v oglatih oklepajih. V tem primeru Excel ne bo mogel uporabiti datoteke, ki je trenutno zaprta, kot vir informacij.

Upoštevati je treba tudi, da je samo ime datoteke smiselno le, če se zahtevani dokument nahaja v isti mapi kot tista, v katero bo vstavljen seznam. Če ne, potem morate v celoti navesti naslov tega dokumenta.

Ustvarjanje odvisnih spustnih menijev

Odvisni seznam je tisti, na katerega vsebino vpliva uporabnikova izbira na drugem seznamu. Recimo, da imamo pred seboj odprto tabelo, ki vsebuje tri obsege, od katerih je vsak dobil ime.

Za ustvarjanje seznamov, na rezultat katerih vpliva možnost, izbrana na drugem seznamu, morate slediti tem korakom.

1. Ustvarite 1. seznam z imeni obsegov.
   

   

2. Na vstopni točki vira so zahtevani indikatorji označeni drug za drugim.
   

   

3. Ustvarite 2. seznam glede na vrsto rastline, ki jo je oseba izbrala. Če na prvem seznamu določite drevesa, bodo informacije na drugem seznamu "hrast, gaber, kostanj" in več. Formulo je treba zapisati na mesto vnosa vira podatkov =POSREDNO(E3). E3 – celica, ki vsebuje ime obsega 1.=INDIRECT(E3). E3 – celica z imenom seznama 1.

Zdaj je vse pripravljeno.

Kako izbrati več vrednosti s spustnega seznama?

Včasih ni mogoče dati prednosti samo eni vrednosti, zato je treba izbrati več kot eno. Nato morate kodi strani dodati makro. S kombinacijo tipk Alt + F11 odprete urejevalnik Visual Basic. In koda je vstavljena tam.

Zasebni pod delovni list_Change (ByVal Target As Range)

    On Error Resume Next

    Če Not Intersect(Target, Range(«Е2:Е9»)) ni nič in Target.Cells.Count = 1 potem

        Application.EnableEvents = False

        Če je Len (Target.Offset (0, 1)) = 0 Potem

            Target.Offset (0, 1) = Target

        ostalo

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

        Konec Če

        Target.ClearContents

        Aplikacija EnableEvents = True

    Konec Če

End Sub 

Da bo vsebina celic prikazana spodaj, v urejevalnik vstavimo naslednjo kodo.

Zasebni pod delovni list_Change (ByVal Target As Range)

    On Error Resume Next

    Če Not Intersect(Target, Range(«Н2:К2»)) ni nič in Target.Cells.Count = 1 potem

        Application.EnableEvents = False

        Če je Len (Target.Offset (1, 0)) = 0 Potem

            Target.Offset (1, 0) = Target

        ostalo

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

        Konec Če

        Target.ClearContents

        Aplikacija EnableEvents = True

    Konec Če

End Sub

In končno, ta koda se uporablja za pisanje v eno celico.

Zasebni pod delovni list_Change (ByVal Target As Range)

    On Error Resume Next

    Če Not Intersect(Target, Range(«C2:C5»)) ni nič in Target.Cells.Count = 1 potem

        Application.EnableEvents = False

        newVal = Cilj

        Aplikacija. Razveljavi

        oldval = cilj

        Če je Len (oldval) <> 0 In oldval <> newVal Potem

            Target = Target & «,» & newVal

        ostalo

            Cilj = novaVal

        Konec Če

        Če je Len (newVal) = 0, potem Target.ClearContents

        Aplikacija EnableEvents = True

    Konec Če

End Sub

Razpone je mogoče urejati.

Kako narediti spustni seznam z iskanjem?

V tem primeru morate najprej uporabiti drugo vrsto seznama. Odpre se zavihek »Razvijalec«, po katerem morate klikniti ali tapniti (če je zaslon na dotik) element »Vstavi« – »ActiveX«. Ima kombinirano polje. Pozvani boste, da narišete ta seznam, nato pa bo dodan v dokument.

Nadalje se konfigurira prek lastnosti, kjer je obseg določen v možnosti ListFillRange. Celica, kjer je prikazana uporabniško določena vrednost, je konfigurirana z možnostjo LinkedCell. Nato morate samo zapisati prve znake, saj bo program samodejno predlagal možne vrednosti.

Spustni seznam s samodejno zamenjavo podatkov

Obstaja tudi funkcija, da se podatki samodejno nadomestijo, ko so dodani v obseg. To je enostavno narediti:

1. Ustvarite niz celic za prihodnji seznam. V našem primeru je to nabor barv. Izberemo ga.
   

   

2. Nato ga je treba oblikovati kot tabelo. Klikniti morate istoimenski gumb in izbrati slog tabele.
   

   

   

Nato morate ta obseg potrditi s pritiskom na gumb »V redu«.

Izberemo nastalo tabelo in ji damo ime prek vnosnega polja, ki se nahaja na vrhu stolpca A.

To je to, tabela je in jo lahko uporabite kot osnovo za spustni seznam, za katerega potrebujete:

1. Izberite celico, v kateri se nahaja seznam.
2. Odprite pogovorno okno Preverjanje podatkov.
   

   

3. Podatkovni tip nastavimo na "Seznam", kot vrednosti pa damo ime tabele skozi znak =.
   

   

   

Vse, celica je pripravljena in v njej so prikazana imena barv, kot smo jih prvotno potrebovali. Zdaj lahko dodate nove položaje preprosto tako, da jih zapišete v celico, ki se nahaja malo nižje takoj za zadnjim.

To je prednost tabele, da se ob dodajanju novih podatkov obseg samodejno poveča. Zato je to najprimernejši način dodajanja seznama.

Kako kopirati spustni seznam?

Za kopiranje je dovolj, da uporabite kombinacijo tipk Ctrl + C in Ctrl + V. Tako bo spustni seznam kopiran skupaj z oblikovanjem. Če želite odstraniti oblikovanje, morate uporabiti posebno pasto (v kontekstnem meniju se ta možnost prikaže po kopiranju seznama), kjer je nastavljena možnost "pogoji za vrednosti".

Izberite vse celice, ki vsebujejo spustni seznam

Če želite opraviti to nalogo, morate uporabiti funkcijo »Izberi skupino celic« v skupini »Najdi in izberi«.

Po tem se odpre pogovorno okno, kjer morate v meniju »Preverjanje podatkov« izbrati postavke »Vsi« in »Ti isti«. Prvi element izbere vse sezname, drugi pa samo tiste, ki so podobni določenim.