Atkarīga nolaižamā saraksta izveide programmā Excel (soli pa solim apmācība)

Skatieties video - atkarīgā nolaižamā saraksta izveide programmā Excel

Excel nolaižamais saraksts ir noderīga funkcija, veidojot datu ievades veidlapas vai Excel informācijas paneļus.

Šūnā tiek parādīts vienumu saraksts kā nolaižamā izvēlne, un lietotājs var veikt izvēli nolaižamajā izvēlnē. Tas varētu būt noderīgi, ja jums ir saraksts ar nosaukumiem, produktiem vai reģioniem, kas bieži jāievada šūnu komplektā.

Zemāk ir Excel nolaižamā saraksta piemērs:

Iepriekš minētajā piemērā esmu izmantojis vienumus A2: A6, lai izveidotu nolaižamo izvēlni C3.

Lasīt: Šeit ir detalizēts ceļvedis par to, kā izveidot Excel nolaižamo sarakstu.

Tomēr dažreiz programmā Excel varat izmantot vairāk nekā vienu nolaižamo sarakstu tā, ka otrajā nolaižamajā sarakstā pieejamie vienumi ir atkarīgi no pirmajā nolaižamajā sarakstā veiktās izvēles.

Programmā Excel tos sauc par atkarīgiem nolaižamajiem sarakstiem.

Zemāk ir piemērs tam, ko es domāju ar atkarīgu nolaižamo sarakstu programmā Excel:

Jūs varat redzēt, ka 2. nolaižamās iespējas ir atkarīgas no 1. nolaižamās izvēlnes. Ja 1. nolaižamajā izvēlnē atlasu “Augļi”, man tiek parādīti augļu nosaukumi, bet, ja 1. nolaižamajā izvēlnē atlasu Dārzeņi, tad es Man ir parādīti dārzeņu nosaukumi nolaižamajā 2.

Programmā Excel to sauc par nosacītu vai atkarīgu nolaižamo sarakstu.

Atkarīga nolaižamā saraksta izveide programmā Excel

Tālāk ir norādītas darbības, lai programmā Excel izveidotu nolaižamo 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: 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. Piemēram, izveidojot nosauktu diapazonu ar “Seasonal Fruits”, tā aizmugurē tiks nosaukts Season_Fruits. Izmantojot funkciju SUBSTITUTE funkcijas INDIRECT ietvaros, pārliecinieties, ka atstarpes ir pārvērsts pasvītros.

Automātiski atiestatīt/notīrīt atkarīgā nolaižamā saraksta saturu

Kad esat izdarījis izvēli un pēc tam maināt vecāku nolaižamo izvēlni, atkarīgais nolaižamais saraksts nemainīsies un tādējādi būs nepareizs ieraksts.

Piemēram, ja kā kategoriju izvēlaties “Augļi” un pēc tam kā vienumu izvēlaties Apple un pēc tam atgriežaties un maināt kategoriju uz “Dārzeņi”, atkarībā nolaižamajā izvēlnē joprojām tiks parādīts Apple kā vienums.

Varat izmantot VBA, lai pārliecinātos, ka atkarīgā nolaižamā saraksta saturs tiek atiestatīts ikreiz, kad tiek mainīts galvenais nolaižamais saraksts.

Šeit ir VBA kods, lai notīrītu atkarīgā nolaižamā saraksta saturu:

Privāta apakšdarblapa_maiņa (ByVal mērķis kā diapazons) Kļūdā Atsākt nākamo, ja ir mērķis.Column = 4 Tad If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset (0, 1). Application.EnableEvents = True Exit Sub End Sub

Šis kods tiek piešķirts Debra apmācībai par atkarīgo nolaižamo sarakstu dzēšanu programmā Excel, kad tiek mainīta atlase.

Lūk, kā panākt, lai šis kods darbotos:

• Kopējiet VBA kodu.
• Excel darbgrāmatā, kurā ir atkarīgs nolaižamais saraksts, dodieties uz cilni Izstrādātājs un grupā “Kods” noklikšķiniet uz Visual Basic (varat izmantot arī īsinājumtaustiņu - ALT + F11).
• VB redaktora logā projekta izpētītāja kreisajā pusē redzēsit visus darblapu nosaukumus. Veiciet dubultklikšķi uz tā, kuram ir nolaižamais saraksts.
• Ielīmējiet kodu koda logā labajā pusē.
• Aizveriet VB redaktoru.

Tagad, kad maināt galveno nolaižamo sarakstu, VBA kods tiks aktivizēts un tas izdzēsīs atkarīgā nolaižamā saraksta saturu (kā parādīts zemāk).

Ja neesat VBA ventilators, varat izmantot arī vienkāršu nosacījuma formatēšanas triku, kas izceļ šūnu, ja rodas neatbilstība. Tas var palīdzēt vizuāli redzēt un labot neatbilstību (kā parādīts zemāk).

Tālāk ir norādītas darbības, kas t0 izceļ neatbilstības atkarīgajos nolaižamajos sarakstos:

• Atlasiet šūnu, kurai ir atkarīgs nolaižamais saraksts.
• Dodieties uz Sākums -> Nosacījuma formatēšana -> Jauns noteikums.
• Dialoglodziņā Jauns formatēšanas noteikums atlasiet “Izmantojiet formulu, lai noteiktu, kuras šūnas formatēt”.
• Formulas laukā ievadiet šādu formulu: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Iestatiet formātu.
• Noklikšķiniet uz Labi.

Formula izmanto funkciju VLOOKUP, lai pārbaudītu, vai atkarīgā nolaižamā saraksta vienums ir viens no galvenās kategorijas. Ja tā nav, formula atgriež kļūdu. Funkcija ISERROR to izmanto, lai atgrieztu TRUE, kas norāda nosacīto formatējumu, lai izceltu šūnu.

Jums varētu patikt arī šādas Excel apmācības:

• Izvelciet datus, pamatojoties uz nolaižamo sarakstu.
• Izveidojiet nolaižamo sarakstu ar meklēšanas ieteikumiem.
• Nolaižamajā sarakstā atlasiet vairākus vienumus.
• Izveidojiet vairākus nolaižamos sarakstus bez atkārtošanās.
• Ietaupiet laiku, izmantojot datu ievades veidlapas programmā Excel.