Până acum, în această serie de validare a datelor, am învățat să creăm o listă verticală verticală și o listă verticală dinamică utilizând diferite tehnici cu validarea datelor în Excel.
Și, astăzi, în acest capitol, vă vom arăta cum să creați o listă verticală dependentă în Microsoft Excel, folosind diferite metode.
Lista verticală dependentă este, de asemenea, cunoscută sub numele de validare a datelor în cascadă și limitează opțiunile dintr-o listă verticală, în funcție de valoarea selectată în cealaltă celulă care conține validarea datelor. Cu alte cuvinte, depinde de valoarea selectată în prima listă derulantă care determină valorile care trebuie afișate în a doua listă derulantă.
Acesta este un scenariu foarte obișnuit de lucru cu date mari sau cu unele rapoarte dinamice, unde doriți să aveți 2nd celula afișează o listă care depinde de elementul de listă ales în primul meniu derulant.
După cum știm că în Excel, există o mulțime de modalități de a realiza o anumită sarcină și în mod similar, există multe modalități de a crea validarea datelor dependente în Excel. Și, astăzi, vom demonstra 5 tehnici diferite pentru a crea o listă dependentă de validare a datelor.
Datele brute pot fi în orice ordine sau format și de fiecare dată când nu puteți schimba datele sau formatul pentru a obține ceea ce căutați.
Deci, am luat un set de date, dar în 3 formate diferite pentru a obține lista verticală dependentă. Și, după cum puteți vedea, datele noastre se află pe partea stângă, care este de la coloana A la coloana E, și vom avea rezultatul așteptat pe partea dreaptă, care se află în coloana J și K. Coloana J va avea validarea primară lista în timp ce coloana K va fi dependentă și va afișa valorile în funcție de valoarea selectată în coloana J.
1Sf Exemplu:-
2nd Exemplu:-
3rd Exemplu:-
1Sf Exemplu:-
Avem listă de produse pentru fiecare cod de produs din coloana A8 până la E13. Și dorim să selectăm codul produsului în J10, apoi în funcție de codul produsului selectat, un nume de produs în celula K10.
Prima metodă:-
Prima metodă este foarte simplă și scurtă și necesită doar 3 pași pentru a obține lista verticală în funcție. Cu toate acestea, funcționează cu succes până când nu efectuați modificări în gama dvs. Odată ce vă modificați datele, va trebui să modificați mai întâi intervalul numit pentru a obține validarea actualizată a datelor în cascadă.
Urmați pașii de mai jos:-
- Selectați întregul tabel de la A8 la E13
- Apoi accesați fila „Formule”, apoi în categoria „Nume definite”, faceți clic pe „Creați din selecție”
- De asemenea, puteți utiliza comanda rapidă de la tastatură CTRL + SHIFT + F3
- Va apărea caseta de dialog Creare nume din selecții
- Solicită să confirme ce rânduri și coloane să fie utilizate pentru a crea numele pentru alte rânduri și coloane. Confirmăm să folosiți „rândul de sus” pentru a crea numele și debifați 2nd și apoi facem clic pe OK
Notă: - Spațiile și alte caractere speciale, cu excepția subliniilor și punctelor, nu sunt permise ca nume. În mod implicit, va fi convertit în subliniere. Deci, utilizați caracterul de subliniere și punctul pentru a separa cuvintele. De asemenea, prima literă nu poate fi un număr; trebuie să fie o literă, o subliniere sau o bară inversă.
- Acum, pentru a confirma că fiecare zonă are un nume, mergem la „Manager de nume” (apăsați CTRL + F3)
- Acolo putem vedea toate cele 5 game numite disponibile
- Și, de asemenea, putem vedea că fiecare nume al gamei are subliniere în loc de gol în mijlocul șirului
Acum, vom crea o listă derulantă:-
- Selectați celula J10 și apăsați ALT ++ D + L pentru a deschide caseta de dialog Validare date
- Selectați Listă> apoi introduceți intervalul A8: E8 în fila Sursă
- Faceți clic pe OK
- Acum vom crea o listă dependentă în celula K10
- Deschideți caseta de dialog Validare date apăsând tasta ALT + D + L
- Selectați Listă, în sursă introduceți această funcție: - = INDIRECT (SUBSTITUT ($ J $ 10, "", "_"))
În validarea datelor, pentru a crea lista dependentă, am folosit funcția INDIRECT pentru a returna valoarea pe baza listei principale de validare a datelor. Și, pentru a înlocui sublinierea cu spațiul, vom folosi funcția SUBSTITUTE din funcția INDIRECT.
- Faceți clic pe OK
Când selectăm orice cod de produs în celula J10, atunci lista de produse cu codul de produs selectat va apărea în celula K10. De exemplu: - Am selectat ETV 501, acum puteți vedea că lista de produse dependente apare în celula K10
Notă: - Ori de câte ori veți adăuga numele produsului și codul produsului care nu vor apărea în listă.
De exemplu: - Am adăugat produsul 26 sub codul produsului ETV 505, dar când selectăm produsul ETV 505, produsul adăugat nu apare în lista derulantă.
Deci, iată cum puteți crea o listă verticală dependentă utilizând o tehnică simplă în doar 3 pași simpli.
2nd Exemplu:-
În acest exemplu, vom vedea cum să obțineți o listă verticală dependentă când aveți datele dvs. așa cum sunt prezentate în acest tabel vertical.
Vom folosi două metode diferite pentru a crea o listă verticală dependentă. Ambele sunt tehnici aproape similare. Cu toate acestea, unul este fără intervalul numit, iar celălalt va avea intervalul numit.
1Sf Metodă:-
Pentru a face același lucru, vom folosi împreună funcțiile OFFSET, MATCH & COUNTIF.
Deoarece știm că funcția OFFSET este utilizată pentru a crea gama dinamică, prin urmare, pentru a crea lista „Validare dinamică a datelor”, folosim funcția OFFSET pentru a returna gama dinamică.
MATCH este folosit pentru a returna poziția relativă a unui element dintr-o listă în Excel. Și aici ne va ajuta să potrivim categoria selectată în lista verticală primară din gama noastră de pe foaie și va returna un număr.
Și, COUNTIF este utilizat pentru a obține numărul de celule care corespunde criteriilor. Și, aici vom folosi acest lucru pentru a număra numărul de rânduri care vor apărea utilizând funcția COUNTIF.
Urmați pașii de mai jos:-
- Selectați celula J21, în care vom crea lista noastră principală de validare a datelor
- Apăsați tasta ALT + D + L pentru a deschide caseta de dialog Validare date
- Selectați lista din categoria de permisiuni
- Faceți clic în fila Sursă și selectați intervalul din B20: B24
- Și faceți clic pe OK
- Mergeți la celula K21 și deschideți din nou caseta de dialog pentru validarea datelor
- Apoi selectăm Listă și, în sursă, introducem mai jos funcția:
- = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Faceți clic pe OK
- În celula K21, putem vedea toate valorile corespunzătoare codului produsului selectat: -
Deci, așa puteți obține lista dependentă luând referințe de celule în funcție.
2nd Metodă:-
În următoarea metodă, vom folosi intervalul denumit în aceeași funcție pentru a obține validarea datelor în cascadă. Mai întâi trebuie să creăm o listă dinamică pentru codul produsului. În cazul în care orice produs nou se adaugă la date, meniul derulant trebuie actualizat pentru a afișa același lucru.
Pentru a face același lucru, urmați pașii de mai jos:-
- Selectați B19, apoi apăsați CTRL + F3 pentru a deschide fereastra „Manager de nume”
- Acum, facem clic pe „Nou” și apare caseta de dialog „Define Name”
- Putem vedea, numele apare deja în caseta de nume - asta pentru că am selectat B9 înainte de a deschide fereastra „Manager de nume”. Și, deoarece B19 are text în el, dacă vrem, îl putem schimba cu un alt nume.
- Introduceți mai jos formula de mențiune: -
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Faceți clic pe OK
Deoarece am creat o listă dinamică pentru produse unice, acum vom crea o gamă dinamică pentru gama de coduri de produse care se află în coloana D.
Urmați aceiași pași pe care i-am urmat pentru produsul unic: -
- Selectați celula D19, deschideți caseta de dialog Definire nume
- Veți găsi numele este deja acolo
- În referințe, introduceți formula de mai jos: -
= OFFSET („Listă drop-down dependentă”! $ D 20,0,0 $, COUNTA („Listă drop-down dependentă!! $ D $ 20: $ D $ 35))
- Faceți clic pe OK
- Acum ambele game dinamice sunt gata. Deci, mergem la J22 și apăsăm „ALT + D + L” și selectăm „Listă”
- În sursă, vom avea intervalul numit pe care l-am definit pentru „Cod produs unic”, așa că apăsăm F3 pentru a vedea toate intervalele numite disponibile
- Putem vedea „Codul produsului unic” denumit interval, așa că facem clic pe el, apoi facem clic pe OK și apăsăm Enter
- În momentul în care apăsăm Enter, primim săgeata derulantă în celula J22, care deține lista codurilor de produs unice
- Selectați celula K22 și deschideți caseta de dialog „Validare date”
- Vom folosi aceeași funcție pe care am folosit-o în ultima metodă, dar cu interval numit
- Selectați lista și apoi în sursă, introduceți mai jos formula: -
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Cod_produs, 0), 0, COUNTIF (Cod_produs, J22))
- Faceți clic pe OK
- Acum, avem lista derulantă principală, precum și lista de produse pentru copii
- Selectați produsul „ETV-101” din J22, iar în K22, putem vedea doar numele care se încadrează în acest produs „ETV-101”. Și, când schimbăm orice produs („ETV-103) în J22, K22 afișează valorile corespunzătoare pentru codul respectiv
Acum vom vedea ce se întâmplă când adăugăm un cod de produs nou în listă? Aceste liste derulante vor fi actualizate?
Să adăugăm produse noi în listă; Urmați pașii de mai jos: -
- Adăugați codul produsului în lista Unique_Prod_Code
- Adăugați, de asemenea, Product_Code și Product_Name în date: -
- Acum verificați lista derulantă - apare codul produsului și numele adăugat
3rd Exemplu:-
Avem titlurile dinamice direct din tabel și vom adăuga produse noi în gamă. Tabelul este în același format pe care l-am folosit pentru 1Sf metodă.
4a Metodă:-
Urmați pașii de mai jos:-
- Selectați titlul A40: E40
- Creați mai întâi intervalul dinamic pentru titluri, deschideți caseta de dialog „Define Name”
- Scrieți „Titlu” în locul numelui, apoi în „se referă la”, introduceți formula menționată mai jos: -
- Introduceți funcția de mai jos: -
- = OFFSET („Listă descendentă dependentă”! $ 40 USD ,,,, COUNTA („Listă verticală dependentă!! 40 USD: 40 USD))
- Faceți clic pe OK
- Gama dinamică „Heading” este gata acum
Și, acum vom crea intervalul numit pentru fiecare rubrică, urmați pașii de mai jos: -
- Selectați tabelul de la A40 la E50
- CTRL + SHIFT + F3 comandă rapidă de la tastatură
- Debifăm 2nd opțiune
- Și, înainte de a da clic pe OK, asigurați-vă că 1Sf este selectată opțiunea „Rândul de sus”
- Acum suntem gata cu ambele game
Acum, vom pregăti lista derulantă părinte
- Selectați celula J42
- Deschideți caseta de dialog Validare date
- Apoi, după selectarea „Listă”, apăsăm F3 în sursă pentru a obține intervalul numit pentru titluri. Faceți clic pe „Heading” și apoi faceți clic pe OK și apăsați Enter. Acum avem lista părinților în J42
- Pentru a crea lista Detaliilor articolului, selectați celula K42
- Deschideți caseta de dialog Validare date apăsând tasta ALT + D + L
- Selectați Listă, apoi introduceți funcția de mai jos în fila Sursă: -
- = OFFSET (INDIRECT (SUBSTITUT ($ J $ 42, "", "_")) ,,, COUNTA (INDIRECT (SUBSTITUT ($ J $ 42, "", "_"))))
- Faceți clic pe OK
Acum selectați un element din J42, să spunem că selectăm „Item 01” și căutați în lista derulantă K42. Și, ca și cele 3 metode anterioare, avem o listă dependentă și aici.
Deci ce este nou? În primul exemplu, nu ați putut adăuga niciun produs pe listă, dar aici puteți adăuga orice produs nou. Deci, să spunem că adăugăm un produs nou la acest articol. Mergem la A45 și tastăm „ETV-501 Prod 05” și apoi revenim la K42 și iată-te. Puteți vedea, noul produs a fost adăugat.
- Acum adăugați câteva produse sub noul articol
Când selectăm „Item 06”, mergem la K42 și facem clic pe lista derulantă. În mod surprinzător, nimic nu se întâmplă atunci când facem clic pe săgeata drop-down. Asta pentru că am creat totul dinamic și am uitat să creăm o gamă dinamică pentru tabel, motiv pentru care produsele nu sunt afișate în lista de copii.
Pentru a face acest lucru, trebuie să folosim diferite tehnici. Există două metode de a face acest lucru. Puteți crea tabelul sau pur și simplu utilizați doar funcția OFFSET. Și, în următoarea metodă, vom folosi funcția OFFSET și vom vedea trucul pentru a extinde și gama de tabele.
- Deci, mergem mai întâi la J43 și apăsăm „ALT + D + L”
- Selectăm „Listă” și, apoi în sursă, apăsăm F3 și selectăm „Heading” și facem clic pe OK și apoi apăsăm Enter
- Acum, mergem la K43 și, după ce selectăm „Listă”, mergem la „Sursă” și intrăm mai jos funcția menționată
= OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1,1000 , 1)))
- Faceți clic pe OK
Acum, ne întoarcem și selectăm „Elementul 06 din celula J43 și ne întoarcem la K43 și facem clic pe săgeata drop-down. Dar, această listă de timp arată produsele pe care le-am adăugat pentru un articol nou. Și selectăm primul produs „ETV-506 Prod 01”.
Acesta este modul în care puteți crea lista verticală dependentă utilizând diferite metode pe orice tip de format de date.
Video: Cum se creează o listă verticală dependentă (în cascadă) în Excel folosind 5 tehnici diferite în Microsoft Excel
Faceți clic pe linkul video pentru a face referire rapidă la utilizarea acestuia. Abonați-vă la noul nostru canal și continuați să învățați cu noi!
Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook.
Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail