Kā programmā Excel izveidot nolaižamo sarakstu

Nolaižamais saraksts ir neticami noderīgs rīks, kas var palīdzēt padarīt darbu ar informāciju ērtāku. Tas ļauj šūnā vienlaikus saturēt vairākas vērtības, ar kurām var strādāt, tāpat kā ar citām. Lai atlasītu vajadzīgo, vienkārši noklikšķiniet uz bultiņas ikonas, pēc kuras tiek parādīts vērtību saraksts uXNUMXbuXNUMXbis. Pēc konkrētas izvēles šūna tiek automātiski aizpildīta ar to, un, pamatojoties uz to, tiek pārrēķinātas formulas.

Programma Excel nodrošina daudzas dažādas metodes nolaižamās izvēlnes ģenerēšanai, kā arī ļauj tās elastīgi pielāgot. Analizēsim šīs metodes sīkāk.

Saraksta izveides process

Lai ģenerētu uznirstošo izvēlni, noklikšķiniet uz izvēlnes vienumiem ceļā “Dati” – “Datu validācija”. Tiks atvērts dialoglodziņš, kurā jāatrod cilne “Parametri” un jānoklikšķina uz tās, ja tā iepriekš nav atvērta. Tam ir daudz iestatījumu, taču mums ir svarīgs vienums “Datu tips”. No visām nozīmēm “saraksts” ir īstā.

To metožu skaits, ar kurām informācija tiek ievadīta uznirstošajā sarakstā, ir diezgan liels.

1. Neatkarīga norāde uz saraksta elementiem, kas atdalīti ar semikolu laukā “Avots”, kas atrodas tajā pašā dialoglodziņa cilnē.
   

   

2. Iepriekšēja vērtību norāde. Laukā Avots ir norādīts diapazons, kurā ir pieejama nepieciešamā informācija.
   

   

3. Nosaukta diapazona norādīšana. Metode, kas atkārto iepriekšējo, taču ir nepieciešams tikai provizoriski nosaukt diapazonu.
   

   

Jebkura no šīm metodēm dos vēlamo rezultātu. Apskatīsim metodes nolaižamo sarakstu ģenerēšanai reālās dzīves situācijās.

Pamatojoties uz datiem no saraksta

Pieņemsim, ka mums ir tabula, kurā aprakstīti dažādu augļu veidi.

Lai izveidotu sarakstu nolaižamajā izvēlnē, pamatojoties uz šo informācijas kopu, veiciet tālāk norādītās darbības.

1. Atlasiet šūnu, kas rezervēta turpmākajam sarakstam.
2. Lentē atrodiet cilni Dati. Tur mēs noklikšķinām uz "Pārbaudīt datus".
   

   

3. Atrodiet vienumu “Datu tips” un pārslēdziet vērtību uz “Saraksts”.
   

   

4. Laukā, kas apzīmē opciju “Avots”, ievadiet vajadzīgo diapazonu. Lūdzu, ņemiet vērā, ka ir jānorāda absolūtās atsauces, lai, kopējot sarakstu, informācija netiktu pārvietota.
   

Turklāt ir funkcija sarakstu ģenerēšanai vienlaikus vairākās šūnās. Lai to panāktu, atlasiet tos visus un veiciet tās pašas darbības, kas aprakstītas iepriekš. Atkal, jums ir jāpārliecinās, ka ir rakstītas absolūtas atsauces. Ja adresei blakus kolonnu un rindu nosaukumiem nav dolāra zīmes, tad tie jāpievieno, nospiežot taustiņu F4, līdz zīme $ atrodas blakus kolonnu un rindu nosaukumiem.

Ar manuālu datu ierakstīšanu

Iepriekš minētajā situācijā saraksts tika uzrakstīts, izceļot vajadzīgo diapazonu. Šī ir ērta metode, taču dažreiz ir nepieciešams manuāli ierakstīt datus. Tas ļaus izvairīties no informācijas dublēšanās darbgrāmatā.

Pieņemsim, ka mēs saskaramies ar uzdevumu izveidot sarakstu, kurā būtu divas iespējamās izvēles: jā un nē. Lai izpildītu uzdevumu, ir nepieciešams:

1. Noklikšķiniet uz šūnas, lai atvērtu sarakstu.
2. Atveriet “Dati” un tur atrodiet mums pazīstamo sadaļu “Datu pārbaude”.
   

   

3. Atkal atlasiet “Saraksta” veidu.
   

   

4. Šeit jums jāievada “Jā; Nē” kā avotu. Mēs redzam, ka informācija tiek ievadīta manuāli, uzskaitīšanai izmantojot semikolu.

Pēc noklikšķināšanas uz Labi mums ir šāds rezultāts.

Pēc tam programma automātiski izveidos nolaižamo izvēlni attiecīgajā šūnā. Visa informācija, ko lietotājs ir norādījis kā vienumus uznirstošajā sarakstā. Noteikumi saraksta izveidei vairākās šūnās ir līdzīgi iepriekšējiem, ar vienīgo izņēmumu, ka informācija jānorāda manuāli, izmantojot semikolu.

Nolaižamā saraksta izveide, izmantojot funkciju OFFSET

Papildus klasiskajai metodei ir iespējams izmantot funkciju APGLABĀŠANAlai ģenerētu nolaižamās izvēlnes.

Atveram lapu.

Lai izmantotu funkciju nolaižamajā sarakstā, jums ir jāveic šādas darbības:

1. Atlasiet interesējošo šūnu, kurā vēlaties ievietot turpmāko sarakstu.
2. Pēc kārtas atveriet cilni “Dati” un logu “Datu validācija”.
   

   

3. Iestatiet “Saraksts”. Tas tiek darīts tāpat kā iepriekšējos piemēros. Visbeidzot, tiek izmantota šāda formula: =OFFSET(A$2$;0;0;5). Mēs to ievadām, kur ir norādītas šūnas, kas tiks izmantotas kā arguments.

Pēc tam programma izveidos ēdienkarti ar augļu sarakstu.

Sintakse tam ir:

=OFFSET(atsauce,līnijas_nobīde,kolonnas_nobīde,[augstums],[platums])

Mēs redzam, ka šai funkcijai ir 5 argumenti. Pirmkārt, tiek dota pirmā nobīdāmās šūnas adrese. Nākamie divi argumenti norāda, cik rindu un kolonnu nobīdīt. Runājot par mums, Augstuma arguments ir 5, jo tas atspoguļo saraksta augstumu. 

Programmā Excel nolaižamais saraksts ar datu aizstāšanu (+ izmantojot funkciju OFFSET)

Dotajā gadījumā APGLABĀŠANA atļauts izveidot uznirstošo izvēlni, kas atrodas noteiktā diapazonā. Šīs metodes trūkums ir tāds, ka pēc vienuma pievienošanas formula būs jārediģē pašam.

Lai izveidotu dinamisku sarakstu ar atbalstu jaunas informācijas ievadīšanai, jums ir:

1. Atlasiet interesējošo šūnu.
2. Paplašiniet cilni "Dati" un noklikšķiniet uz "Datu validācija".
3. Atvērtajā logā vēlreiz atlasiet vienumu “Saraksts” un kā datu avotu norādiet šādu formulu: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Noklikšķiniet uz Labi.

Tas satur funkciju COUNTIF, lai uzreiz noteiktu, cik šūnu ir aizpildītas (lai gan tam ir daudz lielāks lietojumu skaits, mēs to vienkārši rakstām šeit konkrētam mērķim).

Lai formula darbotos normāli, ir nepieciešams izsekot, vai formulas ceļā nav tukšas šūnas. Tām nevajadzētu būt.

Nolaižamais saraksts ar datiem no citas lapas vai Excel faila

Klasiskā metode nedarbojas, ja jums ir nepieciešams iegūt informāciju no cita dokumenta vai pat lapas, kas atrodas tajā pašā failā. Šim nolūkam tiek izmantota funkcija NETIEŠAIS, kas ļauj pareizā formātā ievadīt saiti uz šūnu, kas atrodas citā lapā vai vispār – failu. Jums ir jāveic šādas darbības:

1. Aktivizējiet šūnu, kurā ievietojam sarakstu.
2. Mēs atveram logu, ko jau zinām. Tajā pašā vietā, kur mēs iepriekš norādījām avotus citiem diapazoniem, formātā ir norādīta formula =NEDIREKTS("[List1.xlsx]Sheet1!$A$1:$A$9"). Protams, List1 un Sheet1 vietā varat ievietot attiecīgi savu grāmatu un lapu nosaukumus. 

Uzmanību! Faila nosaukums ir norādīts kvadrātiekavās. Šādā gadījumā programma Excel nevarēs izmantot pašlaik aizvērto failu kā informācijas avotu.

Jāņem vērā arī tas, ka pašam faila nosaukumam ir jēga tikai tad, ja nepieciešamais dokuments atrodas tajā pašā mapē, kurā tiks ievietots saraksts. Ja nē, tad pilnībā jānorāda šī dokumenta adrese.

Atkarīgu nolaižamo izvēlņu izveide

Atkarīgais saraksts ir saraksts, kura saturu ietekmē lietotāja izvēle citā sarakstā. Pieņemsim, ka mūsu priekšā ir atvērta tabula, kurā ir trīs diapazoni, no kuriem katram ir dots nosaukums.

Lai ģenerētu sarakstus, kuru rezultātu ietekmē citā sarakstā atlasītā opcija, ir jāveic šīs darbības.

1. Izveidojiet pirmo sarakstu ar diapazonu nosaukumiem.
   

   

2. Avota ievades punktā nepieciešamie rādītāji tiek izcelti pa vienam.
   

   

3. Izveidojiet 2. sarakstu atkarībā no personas izvēlētā auga veida. Alternatīvi, ja pirmajā sarakstā norādāt kokus, tad otrajā sarakstā informācija būs “ozols, skābardis, kastaņs” un tālāk. Ir nepieciešams pierakstīt formulu datu avota ievades vietā =NETIEŠAIS(E3). E3 – šūna, kurā ir diapazona nosaukums 1.=INDIRECT(E3). E3 – šūna ar saraksta nosaukumu 1.

Tagad viss ir gatavs.

Kā nolaižamajā sarakstā atlasīt vairākas vērtības?

Dažreiz nav iespējams dot priekšroku tikai vienai vērtībai, tāpēc ir jāizvēlas vairāk nekā viena. Pēc tam lapas kodam jāpievieno makro. Izmantojot taustiņu kombināciju Alt + F11, tiek atvērts Visual Basic redaktors. Un tur ir ievietots kods.

Private Sub Worksheet_Change (byVal Target As Range)

    Par kļūdu Resume Next

    Ja nekrustojas (mērķis, diapazons («Е2:Е9»)) ir nekas un mērķis. šūnas. skaits = 1, tad

        Application.EnableEvents = False

        Ja Len (Mērķa nobīde (0, 1)) = 0 Tad

            Mērķis.Nobīde (0, 1) = Mērķis

        cits

            Mērķis.Beigas (xlToRight) .Offset (0, 1) = Mērķis

        galu Ja

        Target.ClearContents

        Application.EnableEvents = True

    galu Ja

End Sub 

Lai tālāk tiktu parādīts šūnu saturs, redaktorā ievietojam šādu kodu.

Private Sub Worksheet_Change (byVal Target As Range)

    Par kļūdu Resume Next

    Ja nekrustojas (mērķis, diapazons («Н2:К2»)) ir nekas un mērķis. šūnas. skaits = 1, tad

        Application.EnableEvents = False

        Ja Len (Mērķa nobīde (1, 0)) = 0 Tad

            Mērķis.Nobīde (1, 0) = Mērķis

        cits

            Mērķis.Beigas (xlDown) .Nobīde (1, 0) = Mērķis

        galu Ja

        Target.ClearContents

        Application.EnableEvents = True

    galu Ja

End Sub

Un visbeidzot, šis kods tiek izmantots, lai rakstītu vienā šūnā.

Private Sub Worksheet_Change (byVal Target As Range)

    Par kļūdu Resume Next

    Ja nekrustojas (mērķis, diapazons («C2:C5»)) ir nekas un mērķis. šūnas. skaits = 1, tad

        Application.EnableEvents = False

        newVal = Mērķis

        Pieteikums.Atsaukt

        oldval = Mērķis

        Ja Len (oldval) <> 0 Un oldval <> newVal Tad

            Mērķis = Target & «,» & newVal

        cits

            Mērķis = jaunsVal

        galu Ja

        Ja Len (newVal) = 0, tad Target.ClearContents

        Application.EnableEvents = True

    galu Ja

End Sub

Diapazoni ir rediģējami.

Kā izveidot nolaižamo sarakstu ar meklēšanu?

Šajā gadījumā sākotnēji ir jāizmanto cita veida saraksts. Tiek atvērta cilne “Izstrādātājs”, pēc kuras jums jānoklikšķina vai jāpieskaras (ja ekrāns ir pieskāriens) elementam “Ievietot” – “ActiveX”. Tam ir kombinētais lodziņš. Jums tiks piedāvāts izveidot šo sarakstu, pēc kura tas tiks pievienots dokumentam.

Turklāt tas tiek konfigurēts, izmantojot rekvizītus, kur diapazons ir norādīts opcijā ListFillRange. Šūna, kurā tiek parādīta lietotāja definētā vērtība, ir konfigurēta, izmantojot opciju LinkedCell. Tālāk jums vienkārši jāpieraksta pirmās rakstzīmes, jo programma automātiski ieteiks iespējamās vērtības.

Nolaižamais saraksts ar automātisku datu aizstāšanu

Ir arī funkcija, ka dati tiek automātiski aizstāti pēc to pievienošanas diapazonam. To ir viegli izdarīt:

1. Izveidojiet šūnu kopu nākotnes sarakstam. Mūsu gadījumā tas ir krāsu komplekts. Mēs to izvēlamies.
   

   

2. Tālāk tas ir jāformatē kā tabula. Jums jānoklikšķina uz tāda paša nosaukuma pogas un jāizvēlas tabulas stils.
   

   

   

Tālāk jums ir jāapstiprina šis diapazons, nospiežot pogu “OK”.

Mēs atlasām iegūto tabulu un piešķiram tai nosaukumu ievades laukā, kas atrodas kolonnas A augšpusē.

Tas ir viss, ir tabula, un to var izmantot kā pamatu nolaižamajam sarakstam, kuram jums ir nepieciešams:

1. Atlasiet šūnu, kurā atrodas saraksts.
2. Atveriet dialoglodziņu Datu validācija.
   

   

3. Mēs iestatām datu tipu uz “Saraksts”, un kā vērtības mēs piešķiram tabulas nosaukumu, izmantojot zīmi =.
   

   

   

Viss, šūna ir gatava, un tajā ir parādīti krāsu nosaukumi, kā mums sākotnēji vajadzēja. Tagad varat pievienot jaunas pozīcijas, vienkārši ierakstot tās šūnā, kas atrodas nedaudz zemāk uzreiz pēc pēdējās.

Tā ir tabulas priekšrocība, ka diapazons automātiski palielinās, pievienojot jaunus datus. Attiecīgi tas ir ērtākais veids, kā pievienot sarakstu.

Kā kopēt nolaižamo sarakstu?

Lai kopētu, pietiek ar taustiņu kombināciju Ctrl + C un Ctrl + V. Tātad nolaižamais saraksts tiks kopēts kopā ar formatējumu. Lai noņemtu formatējumu, ir jāizmanto īpaša ielīmēšana (konteksta izvēlnē šī opcija parādās pēc saraksta kopēšanas), kurā ir iestatīta opcija “nosacījumi par vērtībām”.

Atlasiet visas šūnas, kurās ir nolaižamais saraksts

Lai veiktu šo uzdevumu, grupā "Atrast un atlasīt" ir jāizmanto funkcija "Atlasīt šūnu grupu".

Pēc tam tiks atvērts dialoglodziņš, kurā izvēlnē "Datu validācija" atlasiet vienumus "Visi" un "Tie paši". Pirmajā vienumā tiek atlasīti visi saraksti, bet otrajā tiek atlasīti tikai tie, kas ir līdzīgi noteiktiem sarakstiem.