Kuidas Excelis ripploendit luua (ainus vajalik juhend)

Ripploend on suurepärane võimalus anda kasutajale võimalus valida eelnevalt määratletud loendist.

Seda saab kasutada, kui pannakse kasutaja vormi täitma või luuakse interaktiivseid Exceli armatuurlaudu.

Ripploendid on veebisaitidel/rakendustes üsna tavalised ja on kasutajale väga intuitiivsed.

Vaadake videot - Excelis ripploendi loomine

Selles õpetuses saate teada, kuidas Excelis ripploendit koostada (selleks kulub vaid mõni sekund) koos kõigi suurepäraste asjadega, mida saate sellega teha.

Kuidas Excelis ripploendit luua

Sellest jaotisest saate teada Exceli ripploendi loomise juhised.

1. Rakkude andmete kasutamine.
2. Andmete käsitsi sisestamine.
3. OFFSET valemi kasutamine.

#1 Rakkude andmete kasutamine

Oletame, et teil on alltoodud üksuste loend:

Exceli ripploendi loomiseks toimige järgmiselt.

1. Valige lahter, kuhu soovite ripploendi luua.
2. Avage Andmed -> Andmetööriistad -> Andmete valideerimine.
3. Valige dialoogiboksis Andmete valideerimine vahekaardil Seaded valideerimiskriteeriumiks loend.
   • Niipea kui valite Loendi, kuvatakse allika väli.
4. Sisestage allikaväljale = $ A $ 2: $ A $ 6 või klõpsake lihtsalt väljal Allikas ja valige hiire abil lahtrid ning klõpsake nuppu OK. See lisab lahtrisse C2 ripploendi.
   • Veenduge, et lahtrisisene rippmenüü oleks märgitud (mis on vaikimisi märgitud). Kui see suvand on märkimata, ei kuvata lahtris rippmenüüd, kuid saate väärtused käsitsi loendisse sisestada.

Märge: Kui soovite ripploendeid korraga luua mitmes lahtris, valige kõik lahtrid, kuhu soovite selle luua, ja järgige ülaltoodud samme. Veenduge, et lahtri viited on absoluutsed (nt $ A $ 2) ja mitte suhtelised (nt A2, A $ 2 või $ A2).

#2 Andmete käsitsi sisestamisega

Ülaltoodud näites kasutatakse lahtri viiteid väljal Allikas. Samuti saate üksusi otse lisada, sisestades need käsitsi allikaväljale.

Oletame näiteks, et soovite lahtri rippmenüüs kuvada kahte valikut Jah ja Ei. Saate selle otse andmete valideerimise allika väljale sisestada järgmiselt.

• Valige lahter, kuhu soovite ripploendi luua (selles näites lahter C2).
• Avage Andmed -> Andmetööriistad -> Andmete valideerimine.
• Valige dialoogiboksis Andmete valideerimine vahekaardil Seaded valideerimiskriteeriumiks loend.
  • Niipea kui valite Loendi, kuvatakse allika väli.
• Sisestage allika väljale Jah, Ei
  • Veenduge, et lahtrisisene rippmenüü oleks märgitud.
• Klõpsake nuppu OK.

See loob valitud lahtrisse ripploendi. Kõik allikaväljal loetletud üksused, mis on eraldatud komaga, on rippmenüü erinevates ridades loetletud.

Kõik allikaväljale sisestatud üksused, komaga eraldatud, kuvatakse ripploendis erinevatel ridadel.

Märge: Kui soovite ripploendeid korraga luua mitmes lahtris, valige kõik lahtrid, kuhu soovite selle luua, ja järgige ülaltoodud samme.

#3 Exceli valemite kasutamine

Lisaks lahtritest valimisele ja andmete käsitsi sisestamisele saate Exceli ripploendi loomiseks kasutada ka lähtevälja valemit.

Excelis ripploendi loomiseks saab kasutada mis tahes valemit, mis tagastab väärtuste loendi.

Oletame näiteks, et teil on allpool näidatud andmekogum.

Siin on sammud Exceli ripploendi loomiseks funktsiooni OFFSET abil:

• Valige lahter, kuhu soovite ripploendi luua (selles näites lahter C2).
• Avage Andmed -> Andmetööriistad -> Andmete valideerimine.
• Valige dialoogiboksis Andmete valideerimine vahekaardil Seaded valideerimiskriteeriumiks loend.
  • Niipea kui valite Loendi, kuvatakse allika väli.
• Sisestage väljale Allikas järgmine valem: = OFFSET ($ A $ 2,0,0,5)
  • Veenduge, et lahtrisisene rippmenüü oleks märgitud.
• Klõpsake nuppu OK.

See loob ripploendi, mis loetleb kõik puuviljade nimed (nagu allpool näidatud).

Märge: Kui soovite ripploendi luua korraga mitmesse lahtrisse, valige kõik lahtrid, kuhu soovite selle luua, ja järgige ülaltoodud samme. Veenduge, et lahtri viited on absoluutsed (nt $ A $ 2) ja mitte suhtelised (nt A2, A $ 2 või $ A2).

Kuidas see valem töötab ??

Ülaltoodud juhul kasutasime ripploendi koostamiseks funktsiooni OFFSET. See tagastab ra esemete loendi

Tagastab üksuste loendi vahemikust A2: A6.

Siin on funktsiooni OFFSET süntaks: = OFFSET (viide, read, veerud, [kõrgus], [laius])

See võtab viis argumenti, kus määrasime viiteks A2 (loendi lähtepunkt). Ridad/veerud on määratud väärtuseks 0, kuna me ei soovi võrdluslahtrit nihutada. Kõrgus on määratud 5 -ks, kuna loendis on viis elementi.

Nüüd, kui kasutate seda valemit, tagastab see massiivi, millel on A2: A6 viie vilja loend. Pange tähele, et kui sisestate valemi lahtrisse, valige see ja vajutage klahvi F9, näete, et see tagastab massiivi puuviljanimedest.

Dünaamilise ripploendi loomine Excelis (kasutades OFFSET -i)

Ülaltoodud tehnikat ripploendi loomiseks valemi abil saab laiendada ka dünaamilise ripploendi loomiseks. Kui kasutate funktsiooni OFFSET, nagu ülal näidatud, ei uuendata rippmenüüd automaatselt, isegi kui lisate loendisse rohkem üksusi. Iga kord, kui loendit muudate, peate seda käsitsi värskendama.

Siin on viis selle dünaamiliseks muutmiseks (ja see pole midagi muud kui valemi väike muutus):

• Valige lahter, kuhu soovite ripploendi luua (selles näites lahter C2).
• Avage Andmed -> Andmetööriistad -> Andmete valideerimine.
• Valige dialoogiboksis Andmete valideerimine vahekaardil Seaded valideerimiskriteeriumiks loend. Niipea kui valite Loendi, kuvatakse allika väli.
• Sisestage allika väljale järgmine valem: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Veenduge, et lahtrisisene rippmenüü oleks märgitud.
• Klõpsake nuppu OK.

Selles valemis asendasin argumendi 5 COUNTIF -iga ($ A $ 2: $ A $ 100, ””).

Funktsioon COUNTIF loendab tühjad lahtrid vahemikus A2: A100. Seega kohandub funktsioon OFFSET, et see hõlmaks kõiki tühje lahtreid.

Märge:

• Selle toimimiseks ei tohi täidetud lahtrite vahel olla tühje lahtreid.
• Kui soovite luua ripploendi mitmest lahtrist korraga, valige kõik lahtrid, kuhu soovite selle luua, ja järgige ülaltoodud samme. Veenduge, et lahtri viited on absoluutsed (nt $ A $ 2) ja mitte suhtelised (nt A2, A $ 2 või $ A2).

Kopeerige Excelis kleepimise ripploendid

Saate andmete valideerimisega lahtrid kopeerida teistesse lahtritesse ja see kopeerib ka andmete valideerimise.

Näiteks kui teil on lahtris C2 ripploend ja soovite seda rakendada ka C3: C6-le, kopeerige lihtsalt lahter C2 ja kleepige see lahtrisse C3: C6. See kopeerib ripploendi ja teeb selle kättesaadavaks vormingus C3: C6 (koos rippmenüüga kopeerib see ka vormingu).

Kui soovite kopeerida ainult rippmenüüd, mitte vormindamist, toimige järgmiselt.

• Kopeerige lahtrit, millel on rippmenüü.
• Valige lahtrid, kuhu soovite rippmenüü kopeerida.
• Minge avalehele -> Kleebi -> Paste Special.
• Valige dialoogiboksis Eriline kleepimine suvand Kleebi suvandid Valideerimine.
• Klõpsake nuppu OK.

See kopeerib ainult rippmenüü, mitte kopeeritud lahtri vormindust.

Ettevaatust Exceli ripploendiga töötamisel

Excelis ripploenditega töötades peate olema ettevaatlik.

Kui kopeerite lahtri (mis ei sisalda ripploendit) üle lahtri, mis sisaldab ripploendit, läheb ripploend kaduma.

Selle halvim osa on see, et Excel ei näita ühtegi hoiatust ega viipa, mis annaks kasutajale teada, et rippmenüü kirjutatakse üle.

Kuidas valida kõik lahtrid, millel on ripploend

Mõnikord on raske teada, millised lahtrid sisaldavad ripploendit.

Seega on mõttekas need lahtrid märgistada, andes neile selge piiri või taustavärvi.

Kõigi lahtrite käsitsi kontrollimise asemel on kiire viis kõigi lahtrite valimiseks, millel on ripploendid (või mis tahes andmete valideerimise reegel).

• Minge avalehele -> Otsi ja vali -> Ava eri.
• Valige dialoogiboksis Mine eriliiki Andmete valideerimine
  • Andmete valideerimisel on kaks võimalust: kõik ja sama. Kõik valiksid kõik lahtrid, millele on rakendatud andmete valideerimise reegel. Sama valiks ainult need lahtrid, millel on sama andmete valideerimise reegel kui aktiivsel lahtril.
• Klõpsake nuppu OK.

See valiks koheselt kõik lahtrid, millele on rakendatud andmete valideerimise reegel (see hõlmab ka ripploendeid).

Nüüd saate lahtreid lihtsalt vormindada (anda ääris või taustavärv) nii, et need oleksid visuaalselt nähtavad ja te ei kopeeriks sinna kogemata teist lahtrit.

Siin on veel üks Jon Acampora tehnika, mille abil saate rippmenüü ikooni alati nähtaval hoida. Selles hr Exceli videos näete ka mõningaid viise, kuidas seda teha.

Sõltuva / tingimusliku Exceli ripploendi loomine

Siin on video sellest, kuidas Excelis sõltuvat ripploendit luua.

Kui eelistate video vaatamise asemel lugemist, jätkake lugemist.

Mõnikord võib teil olla mitu ripploendit ja soovite, et teises rippmenüüs kuvatavad üksused sõltuksid sellest, mida kasutaja esimeses rippmenüüs valis.

Neid nimetatakse sõltuvateks või tingimuslikeks ripploenditeks.

Allpool on näide tingimuslikust/sõltuvast ripploendist:

Ülaltoodud näites, kui jaotises „Rippmenüü 2” loetletud üksused sõltuvad jaotises „Rippmenüü 1” tehtud valikust.

Nüüd vaatame, kuidas seda luua.

Siin on sammud Excelis sõltuva / tingimusliku ripploendi loomiseks.

• Valige lahter, kuhu soovite esimese (peamise) ripploendi.
• Avage Andmed -> Andmete valideerimine. See avab andmete valideerimise dialoogiboksi.
• Valige andmete valimise dialoogiboksis seadete vahekaardil Loend.
• Väljal Allikas määrake vahemik, mis sisaldab esimeses ripploendis kuvatavaid üksusi.
• Klõpsake nuppu OK. See loob rippmenüü 1.
• Valige kogu andmekogum (selles näites A1: B6).
• Avage Valemid -> Määratud nimed -> Loo valikust (või võite kasutada kiirklahvi Control + Shift + F3).
• Märkige dialoogiboksis „Loo nimega valikust” ülemise rea valik ja tühjendage kõik muud. Seda tehes luuakse kaks nimevahemikku ("Puuviljad" ja "Köögiviljad"). Puuviljad nimega vahemik viitab kõigile loendis olevatele puuviljadele ja köögiviljad nimega vahemik viitab kõigile loendis olevatele köögiviljadele.
• Klõpsake nuppu OK.
• Valige lahter, kuhu soovite rippmenüüst Sõltuv/tingimus (selles näites E3).
• Avage Andmed -> Andmete valideerimine.
• Veenduge, et dialoogiboksi Andmete valideerimine seadete vahekaardil oleks valitud Loend sisse.
• Sisestage väljale Allikas valem = KAUDNE (D3). Siin on D3 lahter, mis sisaldab peamist rippmenüüd.
• Klõpsake nuppu OK.

Nüüd, kui valite rippmenüüst 1, värskendatakse automaatselt ripploendis 2 loetletud valikuid.

Laadige alla näidisfail

Kuidas see töötab? - Tingimuslik ripploend (lahtris E3) viitab = KAUDNE (D3). See tähendab, et kui valite lahtris D3 „Puuviljad”, viitab E3 ripploend nimega vahemikule „Puuviljad” (funktsiooni INDIRECT kaudu) ja loetleb seega kõik selle kategooria üksused.

Oluline märkus Excelis tingimuslike ripploenditega töötades:

• Kui olete valiku teinud ja seejärel muudate rippmenüüd, ei muutu sõltuv rippmenüü ja on seega vale kirje. Näiteks kui valite riigiks USA ja valite osariigiks Florida ning lähete tagasi ja muudate riigi Indiaks, jääb osariik Floridaks. Siin on Debra suurepärane õpetus sõltuvate (tingimuslike) ripploendite kustutamiseks Excelis, kui valikut muudetakse.
• Kui põhikategooria on rohkem kui üks sõna (näiteks „Puuviljad” asemel „Hooajalised puuviljad”), peate selle asemel kasutama valemit = KAUDNE (ASENDUS (D3, ”“, ”_”)). lihtne ülaltoodud kaudne funktsioon. Selle põhjuseks on asjaolu, et Excel ei luba tühikuid nimivahemikes. Nii et kui loote nimevahemiku rohkem kui ühe sõna abil, lisab Excel sõnade vahele automaatselt alajoone. Seega oleks hooajalised puuviljad nimega „Seasonal_Fruits”. Funktsiooni SUBSTITUTE kasutamine INDIRECT funktsioonis tagab tühikute olemasolu on teisendatakse alakriipsudeks.