Kuidas Excelis ripploendit koostada

Rippmenüü on uskumatult kasulik tööriist, mis aitab teabega töötamist mugavamaks muuta. See võimaldab lahtris korraga sisaldada mitut väärtust, millega saate töötada nagu kõigi teistega. Vajaliku valimiseks klõpsake lihtsalt nooleikoonil, mille järel kuvatakse väärtuste loend uXNUMXbuXNUMXbis. Pärast konkreetse valimist täidetakse lahter sellega automaatselt ja selle alusel arvutatakse valemid ümber.

Excel pakub rippmenüü loomiseks palju erinevaid meetodeid ning lisaks võimaldab see neid paindlikult kohandada. Analüüsime neid meetodeid üksikasjalikumalt.

Nimekirja loomise protsess

Hüpikmenüü loomiseks klõpsake menüükäskudel tee "Andmed" – "Andmete kinnitamine" ääres. Avaneb dialoogiboks, kus peate leidma vahekaardi "Parameetrid" ja klõpsama sellel, kui seda pole varem avatud. Sellel on palju seadeid, kuid üksus "Andmetüüp" on meie jaoks oluline. Kõigist tähendustest on "loend" õige.

Meetodite arv, mille abil teave hüpikloendisse sisestatakse, on üsna suur.

1. Semikooloniga eraldatud loendi elementide sõltumatu tähis väljal „Allikas”, mis asub sama dialoogiboksi samal vahekaardil.
   

   

2. Väärtuste esialgne näitamine. Väli Allikas sisaldab vahemikku, kus nõutav teave on saadaval.
   

   

3. Nimega vahemiku määramine. Meetod, mis kordab eelmist, kuid vahemikku on vaja ainult esialgselt nimetada.
   

   

Ükskõik milline neist meetoditest annab soovitud tulemuse. Vaatame meetodeid rippmenüüde loomiseks reaalsetes olukordades.

Põhineb loendi andmetel

Oletame, et meil on tabel, mis kirjeldab erinevate puuviljade liike.

Selle teabekogumi põhjal rippmenüüs loendi loomiseks peate tegema järgmist.

1. Valige tulevase loendi jaoks reserveeritud lahter.
2. Leidke lindilt vahekaart Andmed. Seal klõpsame nuppu "Kinnita andmed".
   

   

3. Leidke üksus "Andmetüüp" ja lülitage väärtuseks "Loend".
   

   

4. Sisestage soovitud vahemik suvandit „Allikas” tähistavale väljale. Pange tähele, et absoluutsed viited tuleb täpsustada, et loendi kopeerimisel teave ei nihkuks.
   

Lisaks on funktsioon loendite loomiseks korraga rohkem kui ühes lahtris. Selle saavutamiseks peaksite need kõik valima ja tegema samad toimingud, nagu eespool kirjeldatud. Jällegi peate veenduma, et absoluutsed viited on kirjutatud. Kui aadressi veergude ja ridade nimede kõrval pole dollarimärki, peate need lisama, vajutades klahvi F4, kuni $ märk on veergude ja ridade nimede kõrval.

Käsitsi andmete salvestamisega

Ülaltoodud olukorras kirjutati nimekiri vajaliku vahemiku esiletõstmisega. See on mugav meetod, kuid mõnikord on vaja andmeid käsitsi salvestada. See võimaldab vältida töövihiku teabe dubleerimist.

Oletame, et seisame silmitsi ülesandega luua loend, mis sisaldab kahte võimalikku valikut: jah ja ei. Ülesande täitmiseks on vaja:

1. Loendi kuvamiseks klõpsake lahtril.
2. Ava "Andmed" ja sealt leiate meile tuttava jaotise "Andmete kontroll".
   

   

3. Valige uuesti loendi tüüp.
   

   

4. Siin peate sisestama "Jah; Ei” allikana. Näeme, et teave sisestatakse käsitsi, kasutades loendamiseks semikoolonit.

Pärast OK klõpsamist saame järgmise tulemuse.

Järgmisena loob programm vastavasse lahtrisse automaatselt rippmenüü. Kogu teave, mille kasutaja on hüpikloendis üksustena määranud. Loendi loomise reeglid mitmes lahtris on sarnased eelmiste reeglitega, ainsa erandiga tuleb teave käsitsi semikooloniga määrata.

Rippmenüü loomine funktsiooni OFFSET abil

Lisaks klassikalisele meetodile on võimalik kasutada funktsiooni KÕRVALDAMINErippmenüüde loomiseks.

Avame lehe.

Funktsiooni kasutamiseks rippmenüü jaoks peate tegema järgmist.

1. Valige huvipakkuv lahter, kuhu soovite tulevase loendi paigutada.
2. Avage järjestikku vahekaart "Andmed" ja aken "Andmete kinnitamine".
   

   

3. Määrake "Loend". Seda tehakse samamoodi nagu eelmistes näidetes. Lõpuks kasutatakse järgmist valemit: =OFFSET(A$2$;0;0;5). Sisestame selle, kus on määratud lahtrid, mida argumendina kasutatakse.

Seejärel loob programm menüü koos puuviljade loendiga.

Selle süntaks on:

=OFFSET(viide,joone_nihe,veeru_nihe,[kõrgus],[laius])

Näeme, et sellel funktsioonil on 5 argumenti. Esiteks antakse esimene nihutatav lahtri aadress. Järgmised kaks argumenti määravad, mitu rida ja veerge nihutada. Meist rääkides on kõrguse argument 5, kuna see tähistab loendi kõrgust. 

Exceli rippmenüü koos andmete asendamisega (+ kasutades funktsiooni OFFSET)

Antud juhul KÕRVALDAMINE lubatud luua kindlas vahemikus asuva hüpikmenüü. Selle meetodi puuduseks on see, et pärast üksuse lisamist peate valemit ise redigeerima.

Uue teabe sisestamist toetava dünaamilise loendi loomiseks peate:

1. Valige huvipakkuv lahter.
2. Laiendage vahekaarti "Andmed" ja klõpsake "Andmete kinnitamine".
3. Avanevas aknas valige uuesti üksus "Loend" ja määrake andmeallikaks järgmine valem: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Vajuta OK.

See sisaldab funktsiooni COUNTIF, et kohe kindlaks teha, kui palju lahtreid täidetakse (kuigi sellel on palju rohkem kasutusalasid, kirjutame selle siia lihtsalt konkreetse eesmärgi jaoks).

Valemi normaalseks toimimiseks on vaja jälgida, kas valemi teel on tühje lahtreid. Nad ei tohiks olla.

Rippmenüü andmetega teisest lehe või Exceli failist

Klassikaline meetod ei tööta, kui teil on vaja teavet hankida teisest dokumendist või isegi samas failis sisalduvalt lehelt. Selleks kasutatakse funktsiooni KAUDSEID, mis võimaldab sisestada õiges vormingus lingi teisel lehel asuvale lahtrile või üldiselt – failile. Peate tegema järgmist.

1. Aktiveerige lahter, kuhu loendi paigutame.
2. Avame juba tuttava akna. Samas kohas, kus varem märkisime teiste vahemike allikad, on vormingus näidatud valem =INDIRECT("[List1.xlsx]Sheet1!$A$1:$A$9"). Loomulikult saate loendi1 ja lehe1 asemel sisestada vastavalt oma raamatu ja lehe nimed. 

Tähelepanu! Faili nimi on antud nurksulgudes. Sel juhul ei saa Excel praegu suletud faili kasutada teabeallikana.

Samuti tuleb tähele panna, et failinimi ise on mõttekas ainult siis, kui vajalik dokument asub samas kaustas, kuhu nimekiri lisatakse. Kui ei, siis peate täpsustama selle dokumendi täieliku aadressi.

Sõltuvate rippmenüüde loomine

Sõltuv loend on loend, mille sisu mõjutab kasutaja valik teises loendis. Oletame, et meie ees on avatud tabel, mis sisaldab kolme vahemikku, millest igaühele on antud nimi.

Peate järgima neid samme, et luua loendeid, mille tulemust mõjutab mõnes teises loendis valitud suvand.

1. Looge esimene loend vahemiku nimedega.
   

   

2. Allika sisenemispunktis tõstetakse vajalikud näitajad ükshaaval esile.
   

   

3. Looge teine ​​​​loend sõltuvalt inimese valitud taime tüübist. Teise võimalusena, kui määrate puud esimeses loendis, on teises loendis teave "tamm, sarvepukk, kastan" ja rohkem. Valem on vaja üles kirjutada andmeallika sisestuskohta =KAUDNE(E3). E3 – vahemiku nime sisaldav lahter 1.=INDIRECT(E3). E3 – lahter loendi nimega 1.

Nüüd on kõik valmis.

Kuidas valida ripploendist mitu väärtust?

Mõnikord ei ole võimalik eelistada ainult ühte väärtust, seega tuleb valida rohkem kui üks. Seejärel peate lehe koodile lisama makro. Klahvikombinatsiooni Alt + F11 kasutamine avab Visual Basicu redaktori. Ja kood sisestatakse sinna.

Private Sub Worksheet_Change (ByVal Target as Range)

    On Error Resume Next

    Kui ei lõiku(sihtmärk, vahemik(«Е2:Е9»)) on mitte midagi ja sihtmärk.lahtrid.arv = 1, siis

        Application.EnableEvents = Vale

        Kui Len (Target.Offset (0, 1)) = 0 Siis

            Target.Offset (0, 1) = sihtmärk

        teine

            Target.End (xlToRight) .Offset (0, 1) = Sihtmärk

        End Kui

        Target.ClearContents

        Application.EnableEvents = Tõene

    End Kui

End Sub 

Lahtrite sisu allpool kuvamiseks sisestame redaktorisse järgmise koodi.

Private Sub Worksheet_Change (ByVal Target as Range)

    On Error Resume Next

    Kui ei lõiku(sihtmärk, vahemik(«Н2:К2»)) on mitte midagi ja sihtmärk.lahtrid.arv = 1, siis

        Application.EnableEvents = Vale

        Kui Len (Target.Offset (1, 0)) = 0 Siis

            Target.Offset (1, 0) = sihtmärk

        teine

            Target.End (xlDown) .Offset (1, 0) = Sihtmärk

        End Kui

        Target.ClearContents

        Application.EnableEvents = Tõene

    End Kui

End Sub

Ja lõpuks kasutatakse seda koodi ühte lahtrisse kirjutamiseks.

Private Sub Worksheet_Change (ByVal Target as Range)

    On Error Resume Next

    Kui ei lõiku(sihtmärk, vahemik(«C2:C5»)) on mitte midagi ja sihtmärk.lahtrid.arv = 1, siis

        Application.EnableEvents = Vale

        newVal = Sihtmärk

        Rakendus. Võta tagasi

        oldval = Sihtmärk

        Kui Len (vanaval) <> 0 Ja oldval <> newVal Siis

            Sihtmärk = Sihtmärk & «,» & uusVal

        teine

            Sihtmärk = uusVal

        End Kui

        Kui Len (newVal) = 0, siis Target.ClearContents

        Application.EnableEvents = Tõene

    End Kui

End Sub

Vahemikud on redigeeritavad.

Kuidas luua otsinguga rippmenüüd?

Sel juhul peate esialgu kasutama teist tüüpi loendit. Avaneb vahekaart "Arendaja", mille järel peate klõpsama või puudutama (kui ekraan on puutetundlik) elementi "Insert" - "ActiveX". Sellel on liitkast. Teil palutakse see loend koostada, misjärel see lisatakse dokumenti.

Lisaks konfigureeritakse seda atribuutide kaudu, kus suvandis ListFillRange on määratud vahemik. Lahter, kus kuvatakse kasutaja määratud väärtus, on konfigureeritud suvandi LinkedCell abil. Järgmiseks peate lihtsalt esimesed märgid üles kirjutama, kuna programm soovitab automaatselt võimalikke väärtusi.

Rippmenüü koos andmete automaatse asendamisega

Samuti on funktsioon, mille kohaselt asendatakse andmed automaatselt pärast nende lisamist vahemikku. Seda on lihtne teha:

1. Looge tulevase loendi jaoks lahtrite komplekt. Meie puhul on see värvide komplekt. Me valime selle.
   

   

2. Järgmisena tuleb see vormindada tabelina. Peate klõpsama samanimelist nuppu ja valima tabeli stiili.
   

   

   

Järgmisena peate selle vahemiku kinnitama, vajutades nuppu "OK".

Valime saadud tabeli ja anname sellele veeru A ülaosas asuva sisestusvälja kaudu nime.

See on kõik, tabel on olemas ja seda saab kasutada ripploendi alusena, mille jaoks vajate:

1. Valige lahter, kus loend asub.
2. Avage andmete valideerimise dialoog.
   

   

3. Määrame andmetüübiks “Loend” ja väärtustena anname tabeli nime läbi = märgi.
   

   

   

Kõik, lahter on valmis ja selles on näidatud värvide nimed, nagu meil algselt vaja oli. Nüüd saate lisada uusi positsioone lihtsalt kirjutades need lahtrisse, mis asub vahetult pärast viimast.

See on tabeli eelis, et uute andmete lisamisel suureneb vahemik automaatselt. Seetõttu on see loendi lisamiseks kõige mugavam viis.

Kuidas kopeerida rippmenüüd?

Kopeerimiseks piisab klahvikombinatsiooni Ctrl + C ja Ctrl + V kasutamisest. Nii kopeeritakse ripploend koos vorminguga. Vormindamise eemaldamiseks peate kasutama spetsiaalset kleepimist (kontekstimenüüs ilmub see valik pärast loendi kopeerimist), kus on seatud valik "Väärtuste tingimused".

Valige kõik lahtrid, mis sisaldavad ripploendit

Selle ülesande täitmiseks peate kasutama rühmas "Otsi ja valige" funktsiooni "Vali lahtrite rühm".

Pärast seda avaneb dialoogiboks, kus tuleb valida menüüst "Andmete kinnitamine" üksused "Kõik" ja "Need samad". Esimene üksus valib kõik loendid ja teine ​​valib ainult need, mis on teatud loenditega sarnased.