CDF (VLOOKUP) - una dintre funcțiile cele mai utile Excel, precum și unul dintre cele mai puțin familiare utilizatorilor. În acest articol, vom ridica vălul secretului de la funcția CDF folosind exemple din viața reală. Vom crea un cont care are șablon de valoare practică pentru o companie fictivă.
Un pic despre VLOOKUP
Deci, ce este CDF. Cred că ați ghicit deja că aceasta este una dintre mai multe funcții Excel.
În conformitate cu scopul său principal, CDF - este o funcție a bazei de date, și anume funcționează cu mese sau, mai simplu, o listă de obiecte în foi de calcul Excel. Asta ar putea fi pentru obiecte? Da, nimic! Tabelul dvs. poate include o listă de angajați, produse, clienți, CD-drive, sau stelele de pe cer. De fapt, nu contează.
Aici este un exemplu al unei liste sau baze de date. În acest caz, o listă de bunuri care vinde companii fictive:
De obicei, în asemenea liste, fiecare element are propriul identificator unic. În acest caz, identificatorul unic este conținut în coloana Cod articol.
Această listă ar trebui să aibă o coloană care conține un identificator unic (numit tasta sau ID) la funcția de CDF poate lucra cu o listă, și ar trebui să fie prima coloană a tabelului. Tabelul din exemplul de mai sus, îndeplinește pe deplin această cerință.
Cel mai dificil de a lucra cu funcția CDF - este de a intelege de ce este necesar, în general. Să încercăm să se ocupe de ea, în primul rând.
VLOOKUP preia din informațiile de bază de date pe baza unui identificator unic.
Cu alte cuvinte, dacă introduceți într-o funcție VPR celulă și trece-l ca un argument pentru unul dintre identificatorii unici ai bazei de date, rezultatul în celula va fi o bucată de informații asociate cu identificatorul unic. Cu referire la exemplul de mai sus: dacă am intrat într-un argument valoarea codului coloana Item. atunci rezultatul ar obține descrierea corespunzătoare produsului (Descriere), prețul său (Price) sau prezența (în stoc). Ce informații ar trebui să returneze o formulă, puteți decide în procesul de creare a acesteia.
Dacă tot ce ai nevoie este un timp pentru a găsi unele informații în baza de date, apoi a crea o formulă de dragul folosind funcția CDF - este o cale prea complicată. Aceste funcții sunt de obicei utilizate în tabele pentru mai multe utilizări, cum ar fi în șabloane. De fiecare dată când cineva va introduce un anumit cod, sistemul va prelua toate informațiile necesare în poziția foaie corespunzătoare.
a crea un șablon
Să creați șablonul de cont pe care îl vom putea folosi de multe ori în compania noastră fictive.
Pentru a începe, lansarea Excel ...
... și de a crea un cont gol.
Așa ar trebui să funcționeze: șablon de utilizator va intra codurile mărfurilor (Articol Cod) în coloana A. Apoi, sistemul va prelua pentru fiecare descrierea produsului și prețul, și apoi se calculează totalul pentru fiecare linie. Trebuie să specificați numărul.
Pentru un exemplu simplu, punem baza de date în datele de bunuri în același registru de lucru Excel, dar pe o foaie separată:
În viața reală, baza de date de multe ori este stocată într-un fișier separat. Este un pic ingrijorat cu privire la funcția de CDF. din moment ce nu face nici o diferență în cazul în care baza de date - pe aceeași pagină de pe o altă pagină a cărții sau chiar într-un fișier separat.
Introduceți VLOOKUP
Pentru a testa funcția de CDF. vom scrie, mai întâi să introducă codul mărfurilor corect în celula A11:
În continuare, vom face celula activă, în care informațiile ar trebui să apară extras funcția CDF a bazei de date. Este curios faptul că, în acest stadiu, mulți sunt confuzi. Permiteți-mi să explic ce vom face în continuare: vom crea o formulă care va extrage din descrierea produsului de bază de date, codul este introdus în celula A11. Unde vrem să această descriere? Desigur, în celula B11. Prin urmare, formula ne scrie acolo.
Deci, selectați celula B11:
Avem nevoie de o listă cu toate funcțiile Excel existente, pentru a găsi în ea CDF și a obține ajutor în completarea formulei. Pentru a face acest lucru, mergeți la fila Formule (Formula) și comanda de funcții selectați Inserare (Insert Function).
O casetă de dialog apare în cazul în care puteți alege orice funcție existentă în Excel. Pentru a găsi ceea ce avem nevoie, putem intra în căutarea unei funcții (funcția de căutare) cuvânt de căutare (sau căutarea în versiunea rusă), pentru că aveți nevoie de noi să funcționeze - este funcția de căutare. Sistemul afișează o listă a tuturor conceptului legate de funcții Excel. Găsiți lista VLOOKUP (CDF), selectați-l cu mouse-ul și apăsați OK.
Va funcție cu argumente caseta de dialog (argumentul funcției), solicitându-vă pentru a introduce toate argumentele necesare pentru VLOOKUP. Imaginați-vă că acesta este funcția în sine te pune următoarele întrebări:
- Ce căutați un identificator unic în baza de date?
- În cazul în care este baza de date?
- Ce informații doriți să preluați din baza de date?
Primele trei argumente sunt în caractere aldine pentru a ne reaminti că acestea sunt obligatorii (funcția CDF, fără nici una dintre ele nu este completă și nu va fi în măsură să se întoarcă valoarea corectă). Al patrulea argument este îndrăzneț pentru că este opțională, și este utilizată în funcție de necesități.
Umple argumente VLOOKUP
Primul argument că este necesar să se precizeze acest Lookup_value (lookup_value). Funcția ne cere să se specifice în cazul în care să caute valoarea codul unic de produs, descrierea care ar trebui să fie eliminate. În cazul nostru, această valoare în coloana de cod articol. pe care am introdus mai devreme în celula A11.
Dați clic pe pictograma de selecție din dreapta primului argument șir de intrare.
Apoi, faceți clic o dată pe celula care conține codul de produs și apăsați Enter.
Valoarea celulei A11 este luată ca primul argument.
Acum trebuie să setați valoarea argumentului Table_array (tabel). Cu alte cuvinte, este necesar să se explice funcția de CDF, care este o bază de date în care doriți să căutați. Dați clic pe pictograma de lângă alegerea celui de al doilea argument:
Acum localizați baza de date și selectați întreaga gamă, fără linii de antet. Deoarece baza de date este stocată pe o foaie separată, selectați mai întâi foaia dorită, făcând clic pe foaia de tab:
În continuare, vom selecta toate celulele din tabel, cu excepția antete rând ...
... și apăsați Enter. Linia pentru a introduce al doilea argument automat este afișat în intervalul de celule care conține întreaga bază de date. În cazul nostru, este „baza de date de produse“ A2 D7 :!.
Acum zaymomsya treilea argument Col_index_num (column_num). Cu acest argument, precizam VLOOKUP. ce fel de bucată de informații din baza de date dorim să extragem. În acest caz, avem nevoie pentru a extrage descrierea produsului (Descriere). Dacă te uiți la baza de date, veți vedea că coloana Descriere este a doua coloană din tabel. Acest lucru înseamnă că, pentru argumentul Col_index_num (column_num) introducem o valoare de 2:
Este important să rețineți că vom specifica o valoare de 2, nu pentru că Descrierea coloanei stocate în coloana a doua banca de la început foaie Excel, și pentru că este al doilea într-un rând în intervalul care este specificat ca argument Table_array (Tabel) funcția CDF (primul este o coloană cu un identificator unic). În cazul în care baza noastră de date va începe undeva în coloana K foaie Excel, încă specificați o valoare de 2 în acest domeniu.
- În cazul în care coloana prima bază de date (conține valorile unice) sortate în ordine crescătoare (alfabetic sau numeric), în acest domeniu, puteți introduce este adevărat (TRUE) sau lăsați câmpul necompletat.
- În cazul în care prima coloană a bazei de date nu este sortate sau sortate în ordine descrescătoare, atunci acest argument trebuie să fie setat la FALSE (FALSE).
Deoarece prima coloană a bazei de date nu este sortată, vom introduce pentru acest argument este FALSE (FALS)
Final atingere ...
Asta este! Am introdus toate informațiile cerute de funcția de CDF. pentru a ne da sensul care ne interesează. Faceți clic pe OK, și rețineți că descrierea mărfurilor corespunzătoare codului R99245. apare în celula B11:
Creați o formulă arată astfel:
Dacă vom introduce un alt cod în celula A11, putem vedea acțiunea funcției CDF. în câmpul Descriere va fi o descriere care corespunde noului cod de produs.
Putem urma aceiași pași pentru a obține prețul valorii bunurilor (Preț) în celula E11. Rețineți că o nouă formulă trebuie să fie creată în celula E11. Rezultatul va arata astfel:
... iar formula ar arata astfel:
Rețineți că cele două formule diferă doar prin valoarea celui de al treilea argument, care este schimbat 2 - 3, pentru că acum avem nevoie pentru a extrage valoarea deja în a treia coloană a tabelului.
Dacă vom decide să cumpere 2 unități de produs, atunci vom scrie 2 în celula D11. În continuare vom introduce o formulă simplă în celula F11 pentru a conta total pentru această linie:
... care arată astfel:
Finalizarea crearea unui șablon
Șablonul nostru nu este încă pe deplin finalizat. Pentru a finaliza crearea sa, procedați în felul următor:
- Se elimină valoarea codului produs al celulei A11 și valoarea 2 a celulelor D11. Ca rezultat, am creat o formulă va raporta o eroare.
Putem rezolva această problemă, luați în considerare funcția IF (IF) și ISBLANK (ISBLANK). Ne schimba formula noastră cu tipul următor:
= VLOOKUP (A11, 'Baza de date de produse' A2 D7,2 :!, FALSE)
= VLOOKUP (A11; 'Baza de date de produse' A2 D7 :!; 2, FALSE)
= IF (ISBLANK (A11), "", VLOOKUP (A11, 'Baza de date de produse' A2 D7,2 :!, FALSE))
= IF (ISBLANK (A11); ""; CDF (A11; 'Baza de date de produse' A2 D7 :!; 2, FALSE))
Avem nevoie pentru a copia formula din celula B11, E11 si F11 pe liniile noastre rămase șablon. Vă rugăm să rețineți că, dacă vom copia doar cu formula creată, noua formulă nu va funcționa corect cu baza noastră de date. Acest lucru poate fi corectat prin scris ca referințe de celule absolută. Un alt mod, mai avansat, este de a crea un interval de nume pentru toate celulele, anexând baza noastra de date (numim Produse) și de a folosi un nume de zona în loc de referințe la celulă. Formula se va dovedi ca aceasta:
= IF (ISBLANK (A11), "", VLOOKUP (A11, 'Baza de date de produse' A2 D7,2 :!, FALSE))
= IF (ISBLANK (A11); ""; CDF (A11; 'Baza de date de produse' A2 D7 :!; 2, FALSE))
... acum aveți posibilitatea să copiați în condiții de siguranță cu formula în liniile celulare ale restului șablonul nostru.
Dacă veniți să lucrați cu cea mai mare responsabilitate, este posibil să se creeze o bază de date a tuturor clienților noștri chiar și pe o singură coală de hârtie. Apoi introduceți codul de client în celula F5 pentru a completa automat în celula B6, B7 și B8 datelor clientului.
