Kā izveidot nolaižamo sarakstu programmā Excel (vienīgais nepieciešamais ceļvedis)

Nolaižamais saraksts ir lielisks veids, kā lietotājam dot iespēju izvēlēties no iepriekš definēta saraksta.

To var izmantot, liekot lietotājam aizpildīt veidlapu vai veidojot interaktīvus Excel informācijas paneļus.

Nolaižamie saraksti ir diezgan izplatīti vietnēs/lietotnēs un ir ļoti intuitīvi lietotājam.

Skatieties video - nolaižamā saraksta izveide programmā Excel

Šajā apmācībā jūs uzzināsit, kā programmā Excel izveidot nolaižamo sarakstu (tas prasa tikai dažas sekundes), kā arī visas lieliskās lietas, ko varat ar to darīt.

Kā izveidot nolaižamo sarakstu programmā Excel

Šajā sadaļā jūs uzzināsit precīzas darbības, lai izveidotu Excel nolaižamo sarakstu:

1. Izmantojot datus no šūnām.
2. Manuāla datu ievadīšana.
3. Izmantojot formulu OFFSET.

#1 Šūnu datu izmantošana

Pieņemsim, ka jums ir vienumu saraksts, kā parādīts zemāk:

Lai izveidotu Excel nolaižamo sarakstu, veiciet tālāk norādītās darbības.

1. Atlasiet šūnu, kurā vēlaties izveidot nolaižamo sarakstu.
2. Dodieties uz Dati -> Datu rīki -> Datu validācija.
3. Dialoglodziņa Datu validācija cilnē Iestatījumi atlasiet Saraksts kā validācijas kritēriji.
   • Tiklīdz izvēlaties sarakstu, parādās avota lauks.
4. Avota laukā ievadiet = $ A $ 2: $ A $ 6 vai vienkārši noklikšķiniet uz lauka Avots un ar peli atlasiet šūnas un noklikšķiniet uz Labi. Šādā veidā šūnā C2 tiks ievietots nolaižamais saraksts.
   • Pārliecinieties, vai ir atzīmēta nolaižamā opcija šūnā (kas ir atzīmēta pēc noklusējuma). Ja šī opcija nav atzīmēta, šūna neparāda nolaižamo izvēlni, tomēr jūs varat manuāli ievadīt vērtības sarakstā.

Piezīme: Ja vēlaties vienā reizē izveidot nolaižamos sarakstus vairākās šūnās, atlasiet visas šūnas, kurās vēlaties to izveidot, un pēc tam izpildiet iepriekš norādītās darbības. Pārliecinieties, vai šūnu atsauces ir absolūtas (piemēram, $ A $ 2) un nav relatīvas (piemēram, A2, A $ 2 vai $ A2).

#2, ievadot datus manuāli

Iepriekš minētajā piemērā šūnu avoti tiek izmantoti laukā Avots. Varat arī pievienot vienumus tieši, ievadot tos manuāli avota laukā.

Piemēram, pieņemsim, ka šūnas nolaižamajā izvēlnē vēlaties parādīt divas iespējas - Jā un Nē. Lūk, kā to tieši ievadīt datu validācijas avota laukā:

• Atlasiet šūnu, kurā vēlaties izveidot nolaižamo sarakstu (šādā piemērā šūna C2).
• Dodieties uz Dati -> Datu rīki -> Datu validācija.
• Dialoglodziņa Datu validācija cilnē Iestatījumi atlasiet Saraksts kā validācijas kritēriji.
  • Tiklīdz izvēlaties sarakstu, parādās avota lauks.
• Avota laukā ievadiet Jā, Nē
  • Pārliecinieties, vai ir atzīmēta nolaižamā opcija šūnā.
• Noklikšķiniet uz Labi.

Tādējādi atlasītajā šūnā tiks izveidots nolaižamais saraksts. Visi avota laukā uzskaitītie vienumi, atdalīti ar komatu, nolaižamajā izvēlnē ir norādīti dažādās rindās.

Visi avota laukā ievadītie vienumi, atdalīti ar komatu, tiek parādīti dažādās rindās nolaižamajā sarakstā.

Piezīme: Ja vēlaties vienā reizē izveidot nolaižamos sarakstus vairākās šūnās, atlasiet visas šūnas, kurās vēlaties to izveidot, un pēc tam izpildiet iepriekš norādītās darbības.

#3 Izmantojot Excel formulas

Papildus atlasīšanai no šūnām un manuālai datu ievadīšanai varat izmantot arī formulu avota laukā, lai izveidotu Excel nolaižamo sarakstu.

Jebkuru formulu, kas atgriež vērtību sarakstu, var izmantot, lai programmā Excel izveidotu nolaižamo sarakstu.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:

Šeit ir norādītas darbības, lai izveidotu Excel nolaižamo sarakstu, izmantojot funkciju OFFSET:

• Atlasiet šūnu, kurā vēlaties izveidot nolaižamo sarakstu (šādā piemērā šūna C2).
• Dodieties uz Dati -> Datu rīki -> Datu validācija.
• Dialoglodziņa Datu validācija cilnē Iestatījumi atlasiet Saraksts kā validācijas kritēriji.
  • Tiklīdz izvēlaties sarakstu, parādās avota lauks.
• Laukā Avots ievadiet šādu formulu: = OFFSET ($ A $ 2,0,0,5)
  • Pārliecinieties, vai ir atzīmēta nolaižamā opcija šūnā.
• Noklikšķiniet uz Labi.

Tādējādi tiks izveidots nolaižamais saraksts, kurā uzskaitīti visi augļu nosaukumi (kā parādīts zemāk).

Piezīme: Ja vēlaties izveidot nolaižamo sarakstu vairākās šūnās vienā reizē, atlasiet visas šūnas, kurās vēlaties to izveidot, un pēc tam veiciet iepriekš norādītās darbības. Pārliecinieties, vai šūnu atsauces ir absolūtas (piemēram, $ A $ 2) un nav relatīvas (piemēram, A2, A $ 2 vai $ A2).

Kā šī formula darbojas ??

Iepriekš minētajā gadījumā mēs izmantojām funkciju OFFSET, lai izveidotu nolaižamo sarakstu. Tas atgriež vienumu sarakstu no ra

Tas atgriež vienumu sarakstu no diapazona A2: A6.

Šeit ir funkcijas OFFSET sintakse: = OFFSET (atsauce, rindas, kolonnas, [augstums], [platums])

Tam nepieciešami pieci argumenti, kur mēs norādījām atsauci kā A2 (saraksta sākumpunkts). Rindas/atdalījumi ir norādīti kā 0, jo mēs nevēlamies kompensēt atsauces šūnu. Augstums ir norādīts kā 5, jo sarakstā ir pieci elementi.

Tagad, kad izmantojat šo formulu, tas atgriež masīvu, kurā ir piecu augļu saraksts A2: A6. Ņemiet vērā: ja ievadāt formulu šūnā, atlasiet to un nospiediet F9, jūs redzēsit, ka tā atgriež augļu nosaukumu masīvu.

Dinamiska nolaižamā saraksta izveide programmā Excel (izmantojot OFFSET)

Iepriekš minēto metodi, kā izmantot formulu, lai izveidotu nolaižamo sarakstu, var paplašināt, lai izveidotu arī dinamisku nolaižamo sarakstu. Ja izmantojat funkciju OFFSET, kā parādīts iepriekš, pat ja pievienojat sarakstam vairāk vienumu, nolaižamā izvēlne netiks automātiski atjaunināta. Katru reizi, mainot sarakstu, tas būs manuāli jāatjaunina.

Šeit ir veids, kā padarīt to dinamisku (un tas ir nekas cits kā neliela formulu korekcija):

• Atlasiet šūnu, kurā vēlaties izveidot nolaižamo sarakstu (šādā piemērā šūna C2).
• Dodieties uz Dati -> Datu rīki -> Datu validācija.
• Dialoglodziņa Datu validācija cilnē Iestatījumi atlasiet Saraksts kā validācijas kritēriji. Tiklīdz izvēlaties sarakstu, parādās avota lauks.
• Avota laukā ievadiet šādu formulu: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Pārliecinieties, vai ir atzīmēta nolaižamā opcija šūnā.
• Noklikšķiniet uz Labi.

Šajā formulā es aizstāju argumentu 5 ar COUNTIF ($ A $ 2: $ A $ 100, ””).

Funkcija COUNTIF saskaita tukšās šūnas diapazonā A2: A100. Tādējādi funkcija OFFSET pielāgojas, lai iekļautu visas tukšās šūnas.

Piezīme:

• Lai tas darbotos, starp aizpildītajām šūnām NAV jābūt tukšām šūnām.
• Ja vēlaties izveidot nolaižamo sarakstu vairākās šūnās vienlaikus, atlasiet visas šūnas, kurās vēlaties to izveidot, un pēc tam veiciet iepriekš norādītās darbības. Pārliecinieties, vai šūnu atsauces ir absolūtas (piemēram, $ A $ 2) un nav relatīvas (piemēram, A2, A $ 2 vai $ A2).

Kopējiet ielīmēšanas nolaižamos sarakstus programmā Excel

Varat kopēt un ielīmēt šūnas ar datu validāciju uz citām šūnām, un tā kopēs arī datu validāciju.

Piemēram, ja šūnā C2 ir nolaižamais saraksts un vēlaties to piemērot arī C3: C6, vienkārši nokopējiet šūnu C2 un ielīmējiet to C3: C6. Tas nokopēs nolaižamo sarakstu un padarīs to pieejamu C3: C6 (kopā ar nolaižamo izvēlni tiks kopēts arī formatējums).

Ja vēlaties kopēt tikai nolaižamo izvēlni, nevis formatējumu, veiciet tālāk norādītās darbības.

• Kopējiet šūnu, kurā ir nolaižamā izvēlne.
• Atlasiet šūnas, kurās vēlaties kopēt nolaižamo izvēlni.
• Dodieties uz Sākums -> Ielīmēt -> Īpašā ielīmēšana.
• Dialoglodziņā Īpašā ielīmēšana ielīmēšanas opcijās atlasiet Apstiprināšana.
• Noklikšķiniet uz Labi.

Tādējādi tiks nokopēta tikai nolaižamā izvēlne, nevis kopētās šūnas formatējums.

Uzmanību, strādājot ar Excel nolaižamo sarakstu

Strādājot ar nolaižamajiem sarakstiem programmā Excel, jums jābūt uzmanīgam.

Kopējot šūnu (kurā nav nolaižamā saraksta) pār šūnu, kurā ir nolaižamais saraksts, nolaižamais saraksts tiek zaudēts.

Vissliktākais ir tas, ka programmā Excel netiks parādīts brīdinājums vai uzvedne, lai lietotājs zinātu, ka nolaižamā izvēlne tiks pārrakstīta.

Kā atlasīt visas šūnas, kurās ir nolaižamais saraksts

Dažreiz ir grūti zināt, kuras šūnas satur nolaižamo sarakstu.

Līdz ar to ir lietderīgi atzīmēt šīs šūnas, piešķirot tai atšķirīgu apmali vai fona krāsu.

Tā vietā, lai manuāli pārbaudītu visas šūnas, ir ātrs veids, kā atlasīt visas šūnas, kurās ir nolaižamie saraksti (vai jebkurš datu validācijas noteikums).

• Dodieties uz Sākums -> Atrast un atlasīt -> Atvērt īpašo.
• Dialoglodziņā Pāriet uz īpašu atlasiet Datu validācija
  • Datu validācijai ir divas iespējas: viss un tas pats. Visi atlasītu visas šūnas, kurām ir piemērots datu validācijas noteikums. Tas pats atlasītu tikai tās šūnas, kurām ir tāds pats datu validācijas noteikums kā aktīvajai šūnai.
• Noklikšķiniet uz Labi.

Tas uzreiz atlasītu visas šūnas, kurām ir piemērots datu validācijas noteikums (tas ietver arī nolaižamos sarakstus).

Tagad jūs varat vienkārši formatēt šūnas (norādīt apmali vai fona krāsu), lai tās būtu vizuāli redzamas un nejauši nekopētu citu šūnu.

Šeit ir vēl viena Jon Acampora tehnika, kuru varat izmantot, lai vienmēr būtu redzama nolaižamās bultiņas ikona. Jūs varat arī redzēt dažus veidus, kā to izdarīt šajā Excel kunga videoklipā.

Atkarīga / nosacīta Excel nolaižamā saraksta izveide

Šeit ir video par to, kā programmā Excel izveidot nolaižamo sarakstu.

Ja vēlaties lasīt nevis skatīties videoklipu, turpiniet lasīt.

Dažreiz jums var būt vairāk nekā viens nolaižamais saraksts un vēlaties, lai otrajā nolaižamajā izvēlnē redzamie vienumi būtu atkarīgi no tā, ko lietotājs izvēlējās pirmajā nolaižamajā izvēlnē.

Tos sauc par atkarīgiem vai nosacītiem nolaižamajiem sarakstiem.

Tālāk ir sniegts nosacītu/atkarīgu nolaižamā saraksta piemērs:

Iepriekš minētajā piemērā, ja vienumi, kas uzskaitīti “Nolaižamajā 2”, ir atkarīgi no atlases, kas veikta sadaļā “Nolaižamais 1”.

Tagad redzēsim, kā to izveidot.

Tālāk ir norādītas darbības, lai programmā Excel izveidotu nolaižamo atkarīgo / nosacīto sarakstu:

• Atlasiet šūnu, kurā vēlaties pirmo (galveno) nolaižamo sarakstu.
• Dodieties uz Dati -> Datu validācija. Tiks atvērts datu validācijas dialoglodziņš.
• Datu apstiprināšanas dialoglodziņa cilnē Iestatījumi atlasiet Saraksts.
• Laukā Avots norādiet diapazonu, kurā ir vienumi, kas jāparāda pirmajā nolaižamajā sarakstā.
• Noklikšķiniet uz Labi. Tādējādi tiks izveidots nolaižamais saraksts 1.
• Atlasiet visu datu kopu (šajā piemērā A1: B6).
• Dodieties uz Formulas -> Definētie vārdi -> Izveidot no atlases (vai arī varat izmantot īsinājumtaustiņu Control + Shift + F3).
• Dialoglodziņā “Izveidot nosaukumu no atlases” atzīmējiet opciju Augšējā rinda un noņemiet atzīmi no visām pārējām. To darot, tiek izveidoti 2 nosaukumu diapazoni (“Augļi” un “Dārzeņi”). Augļu nosauktais diapazons attiecas uz visiem augļiem sarakstā, un dārzeņu nosauktais diapazons attiecas uz visiem sarakstā iekļautajiem dārzeņiem.
• Noklikšķiniet uz Labi.
• Atlasiet šūnu, kurā vēlaties nolaižamo sarakstu Atkarīgs/nosacīts (šajā piemērā E3).
• Dodieties uz Dati -> Datu validācija.
• Dialoglodziņa Datu validācija cilnē Iestatījumi pārliecinieties, vai ir atlasīts Saraksts.
• Laukā Avots ievadiet formulu = NETIEŠA (D3). Šeit D3 ir šūna, kurā ir galvenā nolaižamā izvēlne.
• Noklikšķiniet uz Labi.

Tagad, veicot izvēli 1. nolaižamajā sarakstā, 2. nolaižamajā sarakstā uzskaitītās opcijas tiks automātiski atjauninātas.

Lejupielādējiet parauga failu

Kā tas darbojas? - Nosacītais nolaižamais saraksts (šūnā E3) attiecas uz = NETIEŠS (D3). Tas nozīmē, ka, atlasot “Augļi” šūnā D3, E3 nolaižamais saraksts attiecas uz nosaukto diapazonu “Augļi” (izmantojot funkciju NETIEŠA) un tādējādi uzskaita visus šīs kategorijas vienumus.

Svarīga piezīme, strādājot ar nosacījuma nolaižamajiem sarakstiem programmā Excel:

• Kad esat izdarījis atlasi un pēc tam maināt nolaižamo vecāku sarakstu, atkarīgais nolaižamais saraksts nemainīsies un līdz ar to būs nepareizs ieraksts. Piemēram, ja kā valsti izvēlaties ASV un pēc tam kā štatu atlasāt Floridu un pēc tam atgriežaties un maināt valsti uz Indiju, štats paliek kā Florida. Šeit ir lieliska Debra apmācība par atkarīgo (nosacīto) nolaižamo sarakstu dzēšanu programmā Excel, kad tiek mainīta atlase.
• Ja galvenā kategorija ir vairāk nekā viens vārds (piemēram, “Sezonas augļi”, nevis “Augļi”), tad jums ir jāizmanto formula = NETIEŠA (AIZSTĀDE (D3, ”“, ”_”)), nevis vienkārša netieša funkcija, kas parādīta iepriekš. Iemesls tam ir tas, ka programmā Excel nav atļautas atstarpes nosauktajos diapazonos. Tātad, veidojot nosauktu diapazonu, izmantojot vairākus vārdus, programma Excel starp vārdiem automātiski ievieto pasvītrojumu. Tātad “Sezonas augļi” nosauktais diapazons būtu “Seasonal_Fruits”. Izmantojot funkciju SUBSTITUTE funkcijas INDIRECT ietvaros, pārliecinieties, ka atstarpes ir pārvērsts pasvītros.