Kaip sukurti išskleidžiamąjį sąrašą „Excel“ (vienintelis jums reikalingas vadovas)

Išskleidžiamasis sąrašas yra puikus būdas suteikti vartotojui galimybę pasirinkti iš iš anksto apibrėžto sąrašo.

Jis gali būti naudojamas, kai vartotojas turi užpildyti formą arba kuriant interaktyvias „Excel“ informacijos suvestines.

Išskleidžiamieji sąrašai yra gana paplitę svetainėse/programose ir yra labai intuityvūs vartotojui.

Žiūrėkite vaizdo įrašą - „Excel“ išskleidžiamojo sąrašo kūrimas

Šioje pamokoje sužinosite, kaip „Excel“ sukurti išskleidžiamąjį sąrašą (tai užtrunka tik kelias sekundes) ir visus nuostabius dalykus, kuriuos galite padaryti su juo.

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

Šiame skyriuje sužinosite, kaip tiksliai sukurti „Excel“ išskleidžiamąjį sąrašą:

1. Ląstelių duomenų naudojimas.
2. Duomenų įvedimas rankiniu būdu.
3. Naudojant OFFSET formulę.

#1 Ląstelių duomenų naudojimas

Tarkime, kad turite elementų sąrašą, kaip parodyta žemiau:

Štai „Excel“ išskleidžiamojo sąrašo kūrimo veiksmai:

1. Pasirinkite langelį, kuriame norite sukurti išskleidžiamąjį sąrašą.
2. Eikite į Duomenys -> Duomenų įrankiai -> Duomenų patvirtinimas.
3. Dialogo lange Duomenų tikrinimas skirtuke Nustatymai pasirinkite Sąrašas kaip patvirtinimo kriterijus.
   • Kai tik pasirinksite Sąrašas, pasirodys šaltinio laukas.
4. Šaltinio lauke įveskite = $ A $ 2: $ A $ 6 arba tiesiog spustelėkite lauke Šaltinis ir pasirinkite langelius naudodami pelę ir spustelėkite Gerai. Tai įterps išskleidžiamąjį sąrašą į langelį C2.
   • Įsitikinkite, kad pažymėta langelio išskleidžiamoji parinktis (kuri pažymėta pagal numatytuosius nustatymus). Jei ši parinktis nepažymėta, langelis nerodo išskleidžiamojo meniu, tačiau galite rankiniu būdu įvesti sąrašo vertes.

Pastaba: Jei norite vienu metu sukurti išskleidžiamuosius sąrašus iš kelių langelių, pasirinkite visas ląsteles, kuriose norite jį sukurti, ir atlikite aukščiau nurodytus veiksmus. Įsitikinkite, kad langelių nuorodos yra absoliučios (pvz., $ 2 USD), o ne santykinės (pvz., A2, 2 USD arba 2 USD).

#2 Įvesdami duomenis rankiniu būdu

Pirmiau pateiktame pavyzdyje lauke Šaltinis naudojamos langelių nuorodos. Taip pat galite tiesiogiai pridėti elementų, įvesdami juos rankiniu būdu šaltinio lauke.

Pvz., Tarkime, kad langelio išskleidžiamajame meniu norite parodyti dvi parinktis - Taip ir Ne. Štai kaip galite jį tiesiogiai įvesti į duomenų patvirtinimo šaltinio lauką:

• Pasirinkite langelį, kuriame norite sukurti išskleidžiamąjį sąrašą (šiame pavyzdyje C2 langelis).
• Eikite į Duomenys -> Duomenų įrankiai -> Duomenų patvirtinimas.
• Dialogo lange Duomenų tikrinimas skirtuke Nustatymai pasirinkite Sąrašas kaip patvirtinimo kriterijus.
  • Kai tik pasirinksite Sąrašas, pasirodys šaltinio laukas.
• Šaltinio lauke įveskite Taip, Ne
  • Įsitikinkite, kad pažymėta langelio išskleidžiamoji parinktis.
• Spustelėkite Gerai.

Pasirinktame langelyje bus sukurtas išskleidžiamasis sąrašas. Visi šaltinio lauke išvardyti elementai, atskirti kableliais, išskleidžiamajame meniu pateikiami skirtingose ​​eilutėse.

Visi šaltinio lauke įvesti elementai, atskirti kableliais, išskleidžiamajame sąraše rodomi skirtingomis eilutėmis.

Pastaba: Jei norite vienu metu sukurti išskleidžiamuosius sąrašus iš kelių langelių, pasirinkite visas ląsteles, kuriose norite jį sukurti, ir atlikite aukščiau nurodytus veiksmus.

#3 „Excel“ formulių naudojimas

Be langelių pasirinkimo ir duomenų įvedimo rankiniu būdu, taip pat galite naudoti formulę šaltinio lauke, kad sukurtumėte išskleidžiamąjį „Excel“ sąrašą.

Bet kuri formulė, kuri pateikia reikšmių sąrašą, gali būti naudojama norint sukurti išskleidžiamąjį sąrašą „Excel“.

Pavyzdžiui, tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:

Štai kaip sukurti išskleidžiamąjį „Excel“ sąrašą naudojant funkciją OFFSET:

• Pasirinkite langelį, kuriame norite sukurti išskleidžiamąjį sąrašą (šiame pavyzdyje C2 langelis).
• Eikite į Duomenys -> Duomenų įrankiai -> Duomenų patvirtinimas.
• Dialogo lange Duomenų tikrinimas skirtuke Nustatymai pasirinkite Sąrašas kaip patvirtinimo kriterijus.
  • Kai tik pasirinksite Sąrašas, pasirodys šaltinio laukas.
• Lauke Šaltinis įveskite šią formulę: = OFFSET ($ A $ 2,0,0,5)
  • Įsitikinkite, kad pažymėta langelio išskleidžiamoji parinktis.
• Spustelėkite Gerai.

Bus sukurtas išskleidžiamasis sąrašas, kuriame bus išvardyti visi vaisių pavadinimai (kaip parodyta žemiau).

Pastaba: Jei norite vienu metu sukurti išskleidžiamąjį sąrašą iš kelių langelių, pasirinkite visas ląsteles, kuriose norite jį sukurti, ir atlikite aukščiau nurodytus veiksmus. Įsitikinkite, kad langelių nuorodos yra absoliučios (pvz., $ 2 USD) ir nėra santykinės (pvz., A2, 2 USD arba 2 USD).

Kaip veikia ši formulė ??

Pirmiau nurodytu atveju mes naudojome funkciją OFFSET, kad sukurtume išskleidžiamąjį sąrašą. Tai grąžina elementų sąrašą iš ra

Pateikiamas sąrašas elementų iš diapazono A2: A6.

Čia yra funkcijos OFFSET sintaksė: = OFFSET (nuoroda, eilutės, stulpeliai, [aukštis], [plotis])

Tam reikalingi penki argumentai, kur nuorodą nurodėme kaip A2 (pradinį sąrašo tašką). Eilutės/eilutės nurodytos kaip 0, nes nenorime atsverti atskaitos langelio. Aukštis nurodytas kaip 5, nes sąraše yra penki elementai.

Dabar, kai naudojate šią formulę, ji pateikia masyvą, kuriame yra penkių vaisių sąrašas A2: A6. Atminkite, kad įvedę formulę į langelį, pasirinkite ją ir paspauskite F9, pamatysite, kad ji pateikia vaisių pavadinimų masyvą.

Dinaminio išskleidžiamojo sąrašo kūrimas „Excel“ (naudojant OFFSET)

Pirmiau pateiktą formulės naudojimo būdą išskleidžiamajam sąrašui sukurti galima išplėsti ir sukurti dinaminį išskleidžiamąjį sąrašą. Jei naudojate OFFSET funkciją, kaip parodyta aukščiau, net jei į sąrašą įtraukiate daugiau elementų, išskleidžiamasis meniu nebus automatiškai atnaujinamas. Kiekvieną kartą keisdami sąrašą turėsite jį atnaujinti rankiniu būdu.

Čia yra būdas padaryti jį dinamišką (ir tai yra tik nedidelis formulės pakeitimas):

• Pasirinkite langelį, kuriame norite sukurti išskleidžiamąjį sąrašą (šiame pavyzdyje C2 langelis).
• Eikite į Duomenys -> Duomenų įrankiai -> Duomenų patvirtinimas.
• Dialogo lange Duomenų tikrinimas skirtuke Nustatymai pasirinkite Sąrašas kaip patvirtinimo kriterijus. Kai tik pasirinksite Sąrašas, pasirodys šaltinio laukas.
• Šaltinio lauke įveskite šią formulę: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Įsitikinkite, kad pažymėta išskleidžiamoji langelio parinktis.
• Spustelėkite Gerai.

Šioje formulėje 5 argumentą pakeičiau COUNTIF ($ A $ 2: $ A $ 100 “).

Funkcija COUNTIF skaičiuoja ne tuščius langelius diapazone A2: A100. Taigi funkcija OFFSET prisitaiko, kad apimtų visas tuščias ląsteles.

Pastaba:

• Kad tai veiktų, tarp užpildytų langelių NĖRA tuščių langelių.
• Jei norite vienu metu sukurti išskleidžiamąjį sąrašą iš kelių langelių, pasirinkite visas ląsteles, kuriose norite jį sukurti, ir atlikite aukščiau nurodytus veiksmus. Įsitikinkite, kad langelių nuorodos yra absoliučios (pvz., $ 2 USD) ir nėra santykinės (pvz., A2, 2 USD arba 2 USD).

Kopijuokite įklijavimo išskleidžiamuosius sąrašus „Excel“

Galite nukopijuoti įklijuoti langelius su duomenų patvirtinimu į kitas ląsteles, taip pat nukopijuoti duomenų patvirtinimą.

Pvz., Jei langelyje C2 turite išskleidžiamąjį sąrašą ir norite jį taikyti ir C3: C6, tiesiog nukopijuokite langelį C2 ir įklijuokite jį į C3: C6. Tai nukopijuos išskleidžiamąjį sąrašą ir padarys jį pasiekiamą C3: C6 (kartu su išskleidžiamuoju meniu jis nukopijuos ir formatavimą).

Jei norite kopijuoti tik išskleidžiamąjį meniu, o ne formatavimą, atlikite šiuos veiksmus:

• Nukopijuokite langelį, kuriame yra išskleidžiamasis meniu.
• Pasirinkite langelius, į kuriuos norite nukopijuoti išskleidžiamąjį meniu.
• Eikite į Pradžia -> Įklijuoti -> Įklijuoti specialiai.
• Dialogo lange Specialusis įklijavimas įklijavimo parinktyse pasirinkite Patvirtinimas.
• Spustelėkite Gerai.

Tai nukopijuos tik išskleidžiamąjį meniu, o ne nukopijuoto langelio formatavimą.

Atsargiai dirbdami su „Excel“ išskleidžiamuoju sąrašu

Dirbdami su „Excel“ išskleidžiamaisiais sąrašais, turite būti atsargūs.

Kai nukopijuojate langelį (kuriame nėra išskleidžiamojo sąrašo) per langelį, kuriame yra išskleidžiamasis sąrašas, išskleidžiamasis sąrašas prarandamas.

Blogiausia yra tai, kad „Excel“ nerodys jokio įspėjimo ar raginimo pranešti vartotojui, kad išskleidžiamasis meniu bus perrašytas.

Kaip pasirinkti visas ląsteles, kuriose yra išskleidžiamasis sąrašas

Kartais sunku žinoti, kuriose ląstelėse yra išskleidžiamasis sąrašas.

Taigi tikslinga pažymėti šias ląsteles, suteikiant joms atskirą kraštą arba fono spalvą.

Užuot rankiniu būdu tikrinę visus langelius, galite greitai pasirinkti visas langelius, kuriuose yra išskleidžiamieji sąrašai (arba bet kokia duomenų patvirtinimo taisyklė).

• Eikite į Pradžia -> Rasti ir pasirinkti -> Eiti į specialųjį.
• Dialogo lange Eiti į specialųjį pasirinkite Duomenų patvirtinimas
  • Duomenų tikrinimas turi dvi parinktis: visas ir tas pats. Visi pasirinktų visus langelius, kuriems taikoma duomenų patvirtinimo taisyklė. Tas pats pasirinktų tik tas ląsteles, kuriose yra tokia pati duomenų patvirtinimo taisyklė kaip ir aktyvioje ląstelėje.
• Spustelėkite Gerai.

Taip akimirksniu būtų pasirinktos visos ląstelės, kurioms taikoma duomenų patvirtinimo taisyklė (taip pat ir išskleidžiamieji sąrašai).

Dabar galite tiesiog formatuoti langelius (suteikti kraštinę arba fono spalvą), kad jie būtų matomi ir netyčia nekopijuotumėte kitos ląstelės.

Štai dar viena Jon Acampora technika, kurią galite naudoti norėdami visada matyti išskleidžiamojo rodyklės piktogramą. Šiame p. Excel vaizdo įraše taip pat galite pamatyti keletą būdų, kaip tai padaryti.

Priklausomo / sąlyginio „Excel“ išskleidžiamojo sąrašo kūrimas

Čia yra vaizdo įrašas, kaip sukurti priklausomą išskleidžiamąjį sąrašą „Excel“.

Jei norite skaityti, o ne žiūrėti vaizdo įrašą, skaitykite toliau.

Kartais galite turėti daugiau nei vieną išskleidžiamąjį sąrašą ir norite, kad antrame išskleidžiamajame meniu rodomi elementai priklausytų nuo to, ką vartotojas pasirinko pirmajame išskleidžiamajame meniu.

Jie vadinami priklausomais arba sąlyginiais išskleidžiamaisiais sąrašais.

Žemiau pateikiamas sąlyginio/priklausomo išskleidžiamojo sąrašo pavyzdys:

Pirmiau pateiktame pavyzdyje, kai elementai, išvardyti „2 išskleidžiamajame meniu“, priklauso nuo pasirinkimo, atlikto skiltyje „1 išskleidžiamasis meniu“.

Dabar pažiūrėkime, kaip tai sukurti.

Štai „Excel“ priklausomo / sąlyginio išskleidžiamojo sąrašo sukūrimo veiksmai:

• Pasirinkite langelį, kuriame norite pirmą (pagrindinį) išskleidžiamąjį sąrašą.
• Eikite į Duomenys -> Duomenų patvirtinimas. Bus atidarytas duomenų patvirtinimo dialogo langas.
• Duomenų tikrinimo dialogo lango nustatymų skirtuke pasirinkite Sąrašas.
• Lauke Šaltinis nurodykite diapazoną, kuriame yra elementai, kurie turi būti rodomi pirmajame išskleidžiamajame sąraše.
• Spustelėkite Gerai. Tai sukurs išskleidžiamąjį meniu 1.
• Pasirinkite visą duomenų rinkinį (šiame pavyzdyje A1: B6).
• Eikite į Formulės -> Apibrėžti vardai -> Sukurti iš pasirinkimo (arba galite naudoti sparčiuosius klavišus „Control“ + „Shift“ + F3).
• Dialogo lange „Sukurti pavadinimą iš pasirinkimo“ pažymėkite viršutinės eilutės parinktį ir panaikinkite visų kitų žymėjimą. Tai padarius sukuriami 2 pavadinimų diapazonai („Vaisiai“ ir „Daržovės“). Vaisiai, pavadinti asortimentu, reiškia visus sąraše esančius vaisius, o daržovių pavadintas asortimentas - visas sąraše esančias daržoves.
• Spustelėkite Gerai.
• Pasirinkite langelį, kuriame norite turėti priklausomą/sąlyginį išskleidžiamąjį sąrašą (šiame pavyzdyje E3).
• Eikite į Duomenys -> Duomenų patvirtinimas.
• Dialogo lange Duomenų tikrinimas nustatymų skirtuke įsitikinkite, kad pasirinktas sąrašas.
• Lauke Šaltinis įveskite formulę = NETIESIOGINĖ (D3). Čia D3 yra langelis, kuriame yra pagrindinis išskleidžiamasis meniu.
• Spustelėkite Gerai.

Dabar, kai pasirenkate 1 išskleidžiamajame meniu, 2 išskleidžiamajame sąraše pateiktos parinktys bus automatiškai atnaujintos.

Atsisiųskite pavyzdinį failą

Kaip tai veikia? - Sąlyginis išskleidžiamasis sąrašas (E3 langelyje) reiškia = NETIESIOGINIS (D3). Tai reiškia, kad kai D3 langelyje pasirenkate „Vaisiai“, E3 išskleidžiamasis sąrašas nurodo pavadintą diapazoną „Vaisiai“ (naudojant funkciją „NETIESIOGINĖ“) ir todėl išvardijami visi tos kategorijos elementai.

Svarbi pastaba dirbant su sąlyginiais išskleidžiamaisiais „Excel“ sąrašais:

• Kai pasirinksite ir pakeisite pirminį išskleidžiamąjį meniu, priklausomas išskleidžiamasis meniu nesikeis ir todėl bus neteisingas įrašas. Pvz., Jei kaip šalį pasirinksite JAV, o valstiją - Floridą, o tada grįšite ir pakeisite šalį į Indiją, valstija išliks kaip Florida. Čia yra puiki Debra pamoka, kaip išvalyti priklausomus (sąlyginius) išskleidžiamuosius sąrašus „Excel“, kai pasirinkimas pakeičiamas.
• Jei pagrindinė kategorija yra daugiau nei vienas žodis (pvz., „Sezoniniai vaisiai“, o ne „Vaisiai“), tada vietoj paprasta INDIRECT funkcija, parodyta aukščiau. Taip yra todėl, kad „Excel“ neleidžia tarpų pavadintuose diapazonuose. Taigi, kai kuriate pavadintą diapazoną naudodami daugiau nei vieną žodį, „Excel“ automatiškai įterpia pabraukimą tarp žodžių. Taigi „Sezoninių vaisių“ pavadintas asortimentas būtų „Seasonal_Fruits“. Funkcija PAKEITIMAS naudojant INDIRECT funkciją užtikrina, kad tarpai būtų laisvi yra paversta pabraukomis.