Celica z izbiro s seznama v excelu. Ustvarite spustni seznam. Primer oblikovanja in postavitve tipk

Spustni seznam v celici omogoča uporabniku, da izbere samo določene vrednosti za vnos. To je še posebej uporabno pri delu z datotekami, strukturiranimi kot zbirka podatkov, kjer lahko vnos neustrezne vrednosti v polje povzroči neželene rezultate.

Če želite ustvariti spustni seznam, potrebujete:

1. Ustvarite seznam vrednosti, med katerimi bo uporabnik lahko izbiral (v našem primeru je to obseg M1:M3), nato izberite celico, v kateri bo spustni seznam (v našem primeru je to celica K1), nato pojdite na zavihek » podatki", skupina" Delo s podatki", gumb" Preverjanje podatkov"

2. Izberite " Vrsta podatkov" -"Seznam" in navedite obseg seznama

3. Če želite uporabnika pozvati o njegovih dejanjih, pojdite na zavihek " Sporočilo, ki ga je treba vnesti« ter izpolnite naslov in besedilo sporočila

ki se prikaže, ko izberete celico s spustnega seznama

4. Po želji lahko ustvarite tudi sporočilo, ki se bo prikazalo ob poskusu vnosa napačnih podatkov

Če ne naredite korakov 3 in 4, potem preverjanje podatkov bo delovalo, vendar ko je celica aktivirana, se sporočilo uporabniku o njegovih nameravanih dejanjih ne bo prikazalo in namesto sporočila o napaki z vašim besedilom se bo pojavilo standardno sporočilo.

5. Če je seznam vrednosti na drugem listu, potem ne boste mogli ustvariti spustnega seznama z zgoraj opisano metodo (do Excel 2010). Če želite to narediti, boste morali seznamu dati ime. To lahko naredimo na več načinov. najprej: izberite seznam in z desno miškino tipko kliknite kontekstni meni izberite " Dodelite ime"

Za različice Excela pod 2007 so enaki koraki videti takole:

drugič: uporaba Upravitelj imen(Excel različice nad 2003 - zavihek " Formule" - skupina " Določena imena«), ki se v kateri koli različici Excela pokliče z bližnjico na tipkovnici Ctrl+F3.
Ne glede na način, ki ga izberete, boste morali na koncu vnesti ime (razpon sem poimenoval s seznamom seznam) in naslov samega obsega (v našem primeru je to "2"!$A$1:$A$3)

6. Zdaj v celico s spustnim seznamom vnesite ime obsega v polje "Vir".

7. pripravljena!

Za dokončanje slike bom dodal, da je seznam vrednosti mogoče vnesti neposredno v preverjanje podatkov, ne da bi se zatekli k dajanju vrednosti na list (to vam bo omogočilo tudi delo s seznamom na katerem koli listu ). To se naredi takole:

Se pravi ročno, skozi ; (podpičje) vnesite seznam v polje " Vir", v vrstnem redu, v katerem ga želimo videti (vrednosti, vnesene od leve proti desni, bodo prikazane v celici od zgoraj navzdol).

Z vsemi svojimi prednostmi ima spustni seznam, ustvarjen na zgoraj opisani način, eno, a zelo »krepko« pomanjkljivost: preverjanje podatkov deluje le, ko vrednosti neposredno vnašate s tipkovnice. Če poskušate prilepiti v celico z preverjanje podatkov vrednosti iz odložišča, tj. predhodno na kakršen koli način kopirane, potem vam bo uspelo. Poleg tega bo prilepljena vrednost iz vmesnega pomnilnika ODSTRANILA SEZNAM ZA PREVERJANJE PODATKOV IN IZPUŠČANJE IZ CELICE, v katero je bila prilepljena predhodno kopirana vrednost. Izogibajte se temu redna sredstva Excel ni mogoč.

Pri delu v programu Microsoft Excel v tabelah s ponavljajočimi se podatki je zelo priročno uporabiti spustni seznam. Z njim lahko preprosto izberete želene parametre iz ustvarjenega menija. Ugotovimo, kako narediti spustni seznam na različne načine.

Najbolj priročen in hkrati najbolj funkcionalen način ustvarjanja spustnega seznama je metoda, ki temelji na izdelavi ločenega seznama podatkov.

Najprej izdelamo predlogo tabele, kjer bomo uporabili spustni meni, ter naredimo ločen seznam podatkov, ki jih bomo v prihodnje vključili v ta meni. Te podatke lahko postavite na isti list dokumenta ali na drugega, če ne želite, da sta obe tabeli vizualno nameščeni skupaj.

V spustnem seznamu izberemo podatke, ki jih nameravamo vnesti. Z desno miškino tipko kliknite in v kontekstnem meniju izberite »Dodeli ime ...«.

Odpre se obrazec za ustvarjanje imena. V polje »Ime« vnesite poljubno priročno ime, po katerem ga bomo prepoznali ta seznam. Toda to ime se mora začeti s črko. Vnesete lahko tudi opombo, vendar to ni obvezno. Kliknite na gumb "V redu".

Pojdite na zavihek »Podatki«. Microsoft programi Excel. Izberite območje tabele, kjer bomo uporabili spustni seznam. Kliknite gumb »Preverjanje podatkov«, ki se nahaja na traku.

Odpre se okno za preverjanje vnesenih vrednosti. V zavihku “Parametri” v polju “Vrsta podatkov” izberite možnost “Seznam”. V polje »Vir« postavimo znak enakosti in takoj brez presledkov napišemo ime seznama, ki smo mu ga dodelili zgoraj. Kliknite na gumb "V redu".

Spustni seznam je pripravljen. Zdaj, ko kliknete na gumb, se prikaže seznam parametrov za vsako celico podanega obsega, iz katerega lahko izberete katerega koli, ki ga želite dodati v celico.

Ustvarjanje spustnega menija z orodji za razvijalce

Druga metoda vključuje ustvarjanje spustnega seznama z orodji za razvijalce, in sicer z uporabo ActiveX. Funkcije orodij za razvijalce privzeto manjkajo, zato jih bomo morali najprej omogočiti. Če želite to narediti, pojdite na zavihek »Datoteka« v Excelu in kliknite »Možnosti«.

V oknu, ki se odpre, pojdite na pododdelek »Prilagodi trak« in potrdite polje poleg vrednosti »Razvijalec«. Kliknite na gumb "V redu".

Po tem se na traku pojavi zavihek »Razvijalec«, kamor se premaknemo. Pogovorimo se v Microsoftu Excel seznam, ki bi moral postati spustni meni. Nato kliknite ikono »Vstavi« na traku in med elementi, ki se prikažejo v skupini »ActiveX Element«, izberite »Combo Box«.

Kliknite na mesto, kjer naj bo celica s seznamom. Kot lahko vidite, se je pojavil obrazec s seznamom.

Nato se premaknemo v "Način oblikovanja". Kliknite na gumb "Lastnosti nadzora".

Odpre se okno z lastnostmi kontrolnika. V stolpec »ListFillRange« ročno vnesite obseg celic tabele, ločenih z dvopičjem, katerih podatki bodo tvorili elemente spustnega seznama.

Spustni seznam v programu Microsoft Excel je pripravljen.

Če želite narediti druge celice s spustnim seznamom, se preprosto postavite na spodnji desni rob končane celice, pritisnite gumb miške in povlecite navzdol.

Sorodni seznami

Tudi v program Excel Ustvarite lahko povezane spustne sezname. To so seznami, kjer, ko izberete eno vrednost s seznama, ste v drugem stolpcu pozvani, da izberete ustrezne parametre. Na primer, ko na seznamu izdelkov izberete krompir, morate kot mere izbrati kilograme in grame, pri izbiri rastlinskega olja pa litre in mililitre.

Najprej pripravimo tabelo, kjer se bodo nahajali spustni seznami, posebej pa naredimo sezname z imeni izdelkov in mer.

Vsakemu od seznamov dodelimo imenovani obseg, kot smo to storili prej z navadnimi spustnimi seznami.

V prvi celici ustvarimo seznam na povsem enak način kot prej, s preverjanjem podatkov.

V drugi celici prav tako zaženemo okno za preverjanje podatkov, vendar v stolpec “Vir” vpišemo funkcijo “=POSREDNO” in naslov prve celice. Na primer =INDIRECT($B3).

Kot lahko vidite, je seznam ustvarjen.

Zdaj, tako da spodnje celice pridobijo enake lastnosti kot prejšnjič, izberite zgornje celice in jih, medtem ko držite tipko miške, povlecite navzdol.

To je to, tabela je ustvarjena.

Ugotovili smo, kako narediti spustni seznam v Excelu. V programu lahko ustvarite tako preproste spustne sezname kot odvisne. V tem primeru lahko uporabite različne načine ustvarjanja. Izbira je odvisna od posebnega namena seznama, namena njegovega ustvarjanja, obsega uporabe itd.

Pri polnjenju celic s podatki je pogosto treba omejiti vnos na določen seznam vrednosti. Na primer, obstaja celica, v katero mora uporabnik vnesti ime oddelka, pri čemer navede, kje dela. Logično je, da najprej ustvarite seznam oddelkov organizacije in uporabniku omogočite, da samo izbere vrednosti s tega seznama. Ta pristop bo pomagal pospešiti postopek vnosa in zmanjšati število tipkarskih napak.

Spustni seznam lahko ustvarite z uporabo

V tem članku bomo ustvarili Spustni seznam z uporabo () s podatkovnim tipom Seznam.

Spustni seznam se lahko oblikuje na različne načine.

A. Najenostavnejši spustni seznam - vnos elementov seznama neposredno v polje Vir

Recimo v celici B 1 treba ustvariti spustni seznam za vnos merskih enot. Izberite celico B 1 in pokliči Preverjanje podatkov.

Če na terenu Vir označujejo merske enote, ločene s podpičji kos; kg; m2; kub, potem bo izbira omejena na te štiri vrednosti.

Zdaj pa poglejmo, kaj se je zgodilo. Izberite celico B 1 . Ko izberete celico, se na desni strani celice prikaže kvadratni puščični gumb, iz katerega lahko izberete elemente spustni seznam.

Napake ta pristop: elemente seznama je enostavno izgubiti (na primer z brisanjem vrstice ali stolpca, ki vsebuje celico B 1 ); Vnos velikega števila elementov ni primeren. Pristop je primeren za majhne (3-5 vrednosti) nespremenljive sezname.
Prednost: Hitro ustvarite seznam.

B. Vnos elementov seznama v obseg (na isti list kot spustni seznam)

Elemente za spustni seznam lahko postavite v obseg EXCEL list in nato na polje Vir orodje za določanje povezave do tega obsega.

Recimo, da so elementi seznama kos; kg; m2; kub vnesene v celice obsega A 1: A 4 , nato polje Vir bo vseboval =list1!$A$1:$A$4

Prednost: jasnost seznama elementov in enostavnost spreminjanja. Pristop je primeren za sezname, ki se redko spreminjajo.
Napake: Če so dodani novi elementi, morate ročno spremeniti referenco obsega. Res je, kot vir je mogoče takoj prepoznati širši obseg, npr. A 1: A 100 . Vendar pa lahko spustni seznam vsebuje prazne vrstice (če so bili na primer nekateri elementi izbrisani ali je bil seznam pravkar ustvarjen). Da prazne vrstice izginejo, morate datoteko shraniti.

Druga pomanjkljivost: izvorni obseg mora biti na istem listu kot spustni seznam, Ker pravila ne morejo uporabljati povezav do drugih listov ali delovnih zvezkov (to velja za EXCEL 2007 in starejše).

Najprej se znebimo druge pomanjkljivosti - objavili bomo seznam elementov spustni seznam na drugem listu.

B. Vnos elementov seznama v obseg (na kateri koli delovni list)

Vnos elementov seznama v obseg celic v drugem delovnem zvezku

Če morate premakniti obseg z elementi spustnega seznama v drug delovni zvezek (na primer v delovni zvezek Vir.xlsx), potem morate narediti naslednje:

• v knjigi Vir.xlsx ustvarite potreben seznam elementov;
• v knjigi Vir.xlsx dodelite na primer obsegu celic, ki vsebujejo seznam elementov ListExt;
• odprite delovni zvezek, v katerega nameravate postaviti celice s spustnim seznamom;
• izberite želeni obseg celic, pokličite orodje , na terenu Vir označi = INDIRECT("[Source.xlsx]sheet1!ListExt");

Ko delate s seznamom elementov, ki se nahajajo v drugem delovnem zvezku, datoteka Vir.xlsx mora biti odprt in v isti mapi, sicer morate določiti polna pot v datoteko. Na splošno se je bolje izogibati sklicevanju na druge liste ali uporabi Osebna makro knjiga Osebno.xlsx oz Dodatki.

Če ne želite dodeliti imena obsegu v datoteki Vir.xlsx, potem je treba formulo spremeniti v = INDIRECT("[Vir.xlsx]list1!$A$1:$A$4")

NASVET:
Če je na listu veliko celic s pravili Preverjanje podatkov, potem lahko uporabite orodje ( Domov/ Najdi in izberi/ Izbira skupine celic). Možnost Preverjanje podatkov To orodje vam omogoča, da izberete celice, ki so predmet preverjanja podatkov (določeno z ukazom Podatki/Delo s podatki/Preverjanje podatkov). Pri izbiri stikala Vsi vse take celice bodo izbrane. Pri izbiri možnosti te enako Označene so le tiste celice, ki imajo enaka pravila za preverjanje podatkov kot aktivna celica.

Opomba:
če spustni seznam vsebuje več kot 25-30 vrednosti, potem postane delo z njim neprijetno. Spustni seznam prikaže le 8 elementov naenkrat, za ogled ostalih pa morate uporabiti drsni trak, kar ni vedno priročno.

EXCEL ne omogoča prilagajanja velikosti pisave Spustni seznam. Pri velikem številu elementov je smiselno našteti elemente in uporabiti dodatno klasifikacijo elementov (t.j. en spustni seznam razdeliti na 2 ali več).

Če želite na primer učinkovito delati s seznamom zaposlenih z več kot 300 zaposlenimi, ga morate najprej razvrstiti po abecedi. Nato ustvarite spustni seznam ki vsebuje črke abecede. drugič spustni seznam naj vsebuje samo tiste priimke, ki se začnejo na črko, izbrano v prvem seznamu. Za rešitev takega problema je mogoče uporabiti strukturo ali .

Dober dan, dragi bralec!

V tem članku bi rad govoril o tem, kaj je spustni seznam v celici, kako ga narediti in čemu služi?

To je seznam fiksnih vrednosti, ki so na voljo samo v določenem obsegu vrednosti. To pomeni, da lahko celica, ki jo določite, vsebuje samo podatke, ki ustrezajo vrednostim navedenega obsega; podatki, ki ne ustrezajo, ne bodo vneseni. V celici lahko izberete vrednosti, ki jih ponuja fiksni seznam v celici.

No, poglejmo ustvarjanje spustnih seznamov in zakaj je to potrebno:

Osebno uporabljam spustni seznam ves čas iz vseh treh razlogov. In močno poenostavi moje delo s podatki; namenoma zmanjšam možnost vnosa primarnih podatkov na 0%.

No, tukaj sta 2 vprašanji, kaj in zakaj, sem vam povedal, a o tem, kako to storiti, bomo govorili spodaj.

Ustvarili bomo spustni seznam v celici v več fazah:

1. Določimo obseg celic, v katerih bomo ustvarili fiksni seznam.

2. Izberite obseg, ki ga potrebujemo, in izberite element v meniju "Podatki" - "Preverjanje podatkov", v kontekstnem oknu, ki se prikaže, izberite element iz navedene izbire "Seznam".

3. V odklenjeni vrstici spodaj označite obseg podatkov, ki naj bodo na našem spustnem seznamu. Kliknite "V REDU" in delo je opravljeno.

V starejših različicah Excela ni mogoče ustvariti spustnega seznama v celici s podatki iz drugih listov, zato je smiselno ustvariti sezname na istem listu in jih skriti. Po potrebi lahko ustvarite tudi navpični seznam - vodoravnega s funkcijo.

In to je zame vse! Resnično upam, da vam je vse našteto jasno. Zelo bi bil hvaležen za vaše komentarje, saj je to pokazatelj branosti in me navdihuje pri pisanju novih člankov! Delite prebrano s prijatelji in všečkajte!

Napredek človeštva temelji na želji vsakega človeka, da živi nad svojimi zmožnostmi
Samuel Butler, filozof

Spustni seznam se nanaša na vsebino več vrednosti v eni celici. Ko uporabnik klikne puščico na desni, se prikaže določen seznam. Izberete lahko določeno.

Zelo priročno orodje Excel za preverjanje vnesenih podatkov. Zmogljivosti spustnih seznamov vam omogočajo, da povečate udobje dela s podatki: zamenjava podatkov, prikaz podatkov iz drugega lista ali datoteke, prisotnost funkcije iskanja in odvisnosti.

Ustvarjanje spustnega seznama

Pot: meni Podatki - Orodje za preverjanje podatkov - zavihek Možnosti. Tip podatkov – “Seznam”.

Vrednosti, iz katerih bo sestavljen spustni seznam, lahko vnesete na različne načine:

Katera koli od možnosti bo dala enak rezultat.



Spustni seznam v Excelu z zamenjavo podatkov

Narediti morate spustni seznam z vrednostmi iz dinamično območje. Če se spremeni obstoječi obseg (podatki se dodajo ali odstranijo), se samodejno prikažejo na spustnem seznamu.

Preizkusimo ga. Tukaj je naša tabela s seznamom na enem listu:

V tabelo dodamo novo vrednost "božično drevo".

Zdaj pa odstranimo vrednost "breza".

Pri uresničitvi naših načrtov nam je pomagala »pametna miza«, ki se enostavno »širi« in spreminja.

Zdaj pa omogočimo vnos novih vrednosti neposredno v celico s tem seznamom. In podatki so bili samodejno dodani v obseg.

Ko v prazno celico spustnega seznama vnesemo novo ime, se prikaže sporočilo: »Dodajte vneseno ime baobab na spustni seznam?«

Kliknite »Da« in dodajte drugo vrstico z vrednostjo »baobab«.

Spustni seznam v Excelu s podatki iz drugega lista/datoteke

Ko so vrednosti za spustni seznam na drugem listu ali v drugem delovnem zvezku, standardni način ne deluje. Težavo lahko rešite s funkcijo INDIRECT: ustvarila bo pravilno povezavo do zunanji vir informacije.

1. Celico, kamor želimo postaviti spustni seznam, naredimo aktivno.
2. Odprite možnosti preverjanja podatkov. V polje »Vir« vnesite formulo: =INDIRECT(»[List1.xlsx]Sheet1!$A$1:$A$9«).

Priloženo je ime datoteke, iz katere so vzeti podatki za seznam oglati oklepaji. Ta datoteka mora biti odprta. Če se knjiga z zahtevanimi vrednostmi nahaja v drugi mapi, morate določiti celotno pot.

Kako narediti odvisne spustne sezname

Vzemimo tri poimenovane obsege:

To je nujno. Zgoraj je opisano, kako iz navadnega seznama narediti poimenovan obseg (z uporabo »Upravitelja imen«). Ne pozabite, da ime ne sme vsebovati presledkov ali ločil.

1. Ustvarimo prvi spustni seznam, ki bo vključeval imena obsegov.

2. Ko postavite kazalec v polje »Vir«, pojdite na list in eno za drugo izberite želene celice.


3. Zdaj pa ustvarimo drugi spustni seznam. Odražati mora tiste besede, ki ustrezajo imenu, izbranemu na prvem seznamu. Če "Drevesa", potem "gaber", "hrast" itd. V polje "Vir" vnesite funkcijo v obliki =INDIRECT(E3). E3 – celica z imenom prvega obsega.


Izbira več vrednosti s spustnega seznama Excel

To se zgodi, ko morate na spustnem seznamu izbrati več elementov hkrati. Razmislimo o načinih za izvedbo naloge.

1. Mi ustvarjamo standardni seznam z uporabo orodja za preverjanje podatkov. Dodati k vir list pripravljen makro. Kako to storiti, je opisano zgoraj. Z njegovo pomočjo bodo izbrane vrednosti dodane na desno od spustnega seznama.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E2:E9")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset (0, 1)) = 0 Potem Target.Offset(0, 1) = Target Else Target.End (xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
2. Da se izbrane vrednosti prikažejo spodaj, vstavimo drugo kodo za obravnavo.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("H2:K2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset (1, 0)) = 0 Potem Target.Offset(1, 0) = Target Else Target.End (xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
3. Za prikaz izbranih vrednosti v eni celici, ločenih s poljubnim ločilom, uporabite naslednji modul.


Private Sub Worksheet_Change(ByVal Target As Range)
Ob napaki Nadaljuj naprej
Č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 Len(oldval)<>0 In oldval<>newValThen
Target = Target & "," & newVal
drugače
Target = newVal
Konec Če
Če je Len(newVal) = 0, potem Target.ClearContents
Application.EnableEvents = True
Konec Če
End Sub


Ne pozabite spremeniti obsegov v "svoje". Sezname sestavljamo na klasičen način. In makri bodo opravili ostalo delo.

Spustni seznam z iskanjem



Ko vnesete prve črke na tipkovnici, so ujemajoči se elementi označeni. In to niso vse prijetne strani tega orodja. Tukaj lahko prilagodite vizualno predstavitev informacij in določite dva stolpca kot vir hkrati.