Kā saistīt nolaižamo sarakstu programmā Excel

Vai vēlaties uzzināt, kā saistiet nolaižamo sarakstu programmā Excel? Excel nolaižamais saraksts ir noderīga funkcija, kad veidojat datu ievades veidlapas vai Excel informācijas paneļus.

Vienumu saraksts tiek parādīts kā nolaižamā izvēlne šūnā, un lietotājs var veikt atlasi nolaižamajā izvēlnē. Tas var būt noderīgi, ja jums ir nosaukumu, produktu vai reģionu saraksts, kas bieži jāievada šūnu kopā.

Excel nolaižamā saraksta piemērs

Šeit ir Excel nolaižamā saraksta piemērs:

Šeit varat lasīt par: Kā kopēt Excel lapu citā darbgrāmatā — apmācība

Attēlā redzamajā piemērā elementi A2: A6 ir izmantoti, lai izveidotu C3 nolaižamo izvēlni. Tomēr dažreiz programmā Excel varat izmantot vairāk nekā vienu nolaižamo sarakstu, lai otrajā nolaižamajā sarakstā pieejamie vienumi būtu atkarīgi no pirmajā nolaižamajā sarakstā veiktās atlases.

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

Tālāk ir sniegts piemērs tam, ko mēs vēlamies izskaidrot ar atkarīgo nolaižamo sarakstu programmā Excel:

Varat redzēt, ka 2. nolaižamās izvēlnes opcijas ir atkarīgas no 1. nolaižamajā izvēlnē veiktās atlases.

Ja izvēlaties "augļi" 1. nolaižamajā izvēlnē tiek parādīti augļu nosaukumi, bet, ja atlasāt Dārzeņi 1. nolaižamajā izvēlnē, tad dārzeņu nosaukumi tiek parādīti 2. nolaižamajā izvēlnē. Programmā Excel to sauc par nosacītu vai atkarīgu nolaižamo sarakstu.

Izveidojiet atkarīgu nolaižamo sarakstu programmā Excel

Tālāk ir norādītas darbības, lai programmā Excel izveidotu atkarīgu nolaižamo sarakstu.

• soli 1: atlasiet šūnu, kurā vēlaties pirmo (galveno) nolaižamo sarakstu.
• soli 2: iet uz Dati -> Datu validācija. Tiks atvērts datu validācijas dialoglodziņš.

• soli 3: datu validācijas dialoglodziņā konfigurācijas cilnē atlasiet opciju saraksts.

• soli 4: Laukos Fuente, norāda diapazonu, kas satur vienumus, kas jāparāda pirmajā nolaižamajā sarakstā.

• soli 5: Klikšķis akceptēt. Tiks izveidota nolaižamā izvēlne 1.

• soli 6: atlasiet visu datu kopu (šajā piemērā A1:B6).

• soli 7: Iet uz Formulas -> Definētie nosaukumi -> Izveidot no atlases (vai arī varat izmantot īsinājumtaustiņu Control+Shift+F3).

• soli 8: dialoglodziņā 'Izveidot nosaukumu no atlases', atzīmējiet opciju Augšējā rinda un noņemiet atzīmi no visiem pārējiem. To darot, tiek izveidoti 2 nosaukumu diapazoni ("augļi" un "dārzeņi"). Diapazons Nosaukti augļi attiecas uz visiem sarakstā iekļautajiem augļiem, savukārt diapazons Nosauktie dārzeņi attiecas uz visiem sarakstā iekļautajiem dārzeņiem.

• soli 9: Klikšķis akceptēt.
• soli 10: atlasiet šūnu, kurā vēlaties izveidot nolaižamo sarakstu Atkarīgais/nosacījums (šajā piemērā E3).
• soli 11: Iet uz Dati -> Datu validācija.

• soli 12: dialoglodziņā Datu validācija, cilnes iekšpusē Konfigurācijas, Pārliecinies ka saraksts ir atlasīts.

• soli 13: Plkst Avota lauks, ievadiet formulu = NETIEŠAIS (D3). Šeit D3 ir šūna, kurā ir galvenā nolaižamā izvēlne.

• soli 14: Klikšķis akceptēt.

Tagad, kad veicat atlasi 1. nolaižamajā izvēlnē, 2. nolaižamajā izvēlnē norādītās opcijas tiks automātiski atjauninātas.

Kā tas strādā?

Kā tas darbojas? – Excel nosacījuma nolaižamais saraksts (šūnā E3) attiecas uz =NETIEŠA(D3). Tas nozīmē, ka, atlasotAugļiŠūnā D3 nolaižamais saraksts E3 attiecas uz nosaukto diapazonu "augļi" (caur NETIEŠĀ funkcija) un tāpēc ir uzskaitīti visi elementi šajā kategorijā.

• Svarīga piezīme:ja vecākkategorijā ir vairāk nekā viens vārds (piem. 'Sezonas augļi'drīzāk'Augļi'), tad jums ir jāizmanto formula = NETIEŠA (AIZSTĀTĀJS (D3,"","_"), nevis vienkāršā NETIEŠĀ funkcija, kas parādīta iepriekš.

Iemesls tam ir tas, ka programma Excel neatļauj atstarpes nosauktajos diapazonos. Tātad, veidojot nosauktu diapazonu, izmantojot vairāk nekā vienu vārdu, programma Excel automātiski ievieto pasvītrojumu starp vārdiem.

Piem: veidojot nosauktu diapazonu ar "Sezonas augļi", Sauks sezona_Augļi kas aizmugure. Izmantošana REPLACE funkcija ietvaros NETIEŠĀ funkcija nodrošina, ka atstarpes kļūst par pasvītrojumiem.

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

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

• Piem: ja atlasāt "augļi" patīk kategorijai un pēc tam atlasiet Manzana kā vienumu un pēc tam atgriezieties un mainiet kategoriju uz "Dārzeņi", joprojām tiks rādīta atkarīga nolaižamā izvēlne Manzana kā elements.

Varat izmantot VBA, lai nodrošinātu, ka atkarīgā nolaižamā saraksta saturs tiek atiestatīts ikreiz, kad tiek mainīts vecāku nolaižamais saraksts. Šeit ir VBA kods, lai notīrītu atkarīgā nolaižamā saraksta saturu:

Private Sub Worksheet_Change (byVal Target As Range)

Kļūdas gadījumā atsāciet nākamo

Ja Target.Column = 4 Tad

Ja Target.Validation.Type = 3 Tad

Application.EnableEvents = False

Target.Offset(0, 1).ClearContents

Tas beigsies, ja

Tas beigsies, ja

exitHandler:

Application.EnableEvents = True

Iziet no apakšnodaļas

End Sub

Kā tas strādā?

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

• soli 1: kopējiet VBA kodu.
• soli 2: Excel darbgrāmatā, kurā ir atkarīgais nolaižamais saraksts, dodieties uz Cilne Izstrādātājs, un grupas ietvaros "Kods"Noklikšķiniet uz Visual Basic (varat izmantot arī īsinājumtaustiņu - ALT + F11).

• soli 3: VB redaktora logā, projektu pētnieka kreisajā pusē, jūs redzēsit visus darblapu nosaukumus. Veiciet dubultklikšķi uz tā, kurā ir nolaižamais saraksts.

• soli 4: ielīmējiet kodu koda logā labajā pusē.

• soli 5: aizveriet VB redaktoru.

Tagad katru reizi, kad maināt vecāku nolaižamo sarakstu, tiks aktivizēts VBA kods un tiks notīrīts atkarīgā nolaižamā saraksta saturs (kā parādīts tālāk).

Ja neesat VBA eksperts, varat izmantot arī vienkāršu nosacītā formatēšanas triku, kas izcels šūnu ikreiz, kad būs neatbilstība. Tas var palīdzēt jums redzēt un vizuāli labot neatbilstību (kā parādīts tālāk).

Tālāk ir norādītas darbības, lai izceltu neatbilstības atkarīgajos nolaižamajos sarakstos.

• soli 1: atlasiet šūnu, kurā ir atkarīgie nolaižamie saraksti.
• soli 2: Iet uz Sākums -> Nosacījuma formatējums -> Jauns noteikums.

• soli 3: dialoglodziņā Jauns noteikums formātā, izvēlieties 'Izmantojiet formulu, lai noteiktu, kuras šūnas formatēt".

• soli 4: Formulas laukā ievadiet šādu formulu:=ESERROR(VLOOKUP(E3,INDEKSS($A$2:$B$6,,MATCH(D3,$A$1:$B$1)), 1,0))

• soli 5: iestatiet formātu.
• soli 6: noklikšķiniet uz Labi.

Jūs varētu arī interesēt uzzināt par: Kā programmā Excel grupēt rakurstabulu pa mēnešiem

Formulā tiek izmantots VLOOKUP funkcija lai pārbaudītu, vai atkarīgais nolaižamā saraksta vienums ir no vecākkategorijas vai nē. Ja nē, formula atgriež kļūdu. To izmanto Funkcija ESERROR lai atgrieztos REĀLS kas liek nosacījumformatēšanai izcelt šūnu.

Kā redzat, tas ir pareizais veids Saistiet ar nolaižamo sarakstu programmā Excel. Kad vien iespējams, izmantojiet šo mazo praktisko pamācību, lai varētu lietot šo noderīgo funkciju. Mēs ceram, ka esam jums palīdzējuši.