În această lecție veți găsi câteva exemple interesante care demonstrează modul de utilizare a funcției CDF (VLOOKUP), împreună cu suma (sumă) sau SUMIF (SUMIF) în Excel, pentru a căuta și însumarea valorilor într-una sau mai multe dintre criteriile.
Sarcinile pot diferi, însă semnificația lor este aceeași - trebuie să găsiți și să sumați valorile după unul sau mai multe criterii din Excel. Care sunt aceste semnificații? Orice număr numeric. Care sunt aceste criterii? Orice ... Începând cu un număr sau o referință la o celulă care conține valoarea dorită și terminând cu operatorii logici și cu rezultatele formulelor Excel.
VPR și SUM în Excel - calculați suma valorilor potrivite găsite
Dacă lucrați cu date numerice în Excel, atunci adesea nu trebuie doar să extrageți date conexe dintr-un alt tabel, ci și să rezumați mai multe coloane sau rânduri. Pentru aceasta puteți combina funcțiile SUM și VL. după cum se arată mai jos.
Să presupunem că avem o listă de produse cu date de vânzări pentru câteva luni, cu o coloană separată pentru fiecare lună. Sursa de date - Vânzări lunare:
Acum trebuie să facem un tabel de totaluri cu sumele de vânzare pentru fiecare produs.
Soluția la această problemă constă în folosirea unei serii de constante în argumentul col_index_num (numărul_catului) al funcției VLOOKUP. Iată un exemplu de formula:
= SUM (VLOOKUP (valoare de căutare, interval de căutare, FALSE))
= SUM (VRN (valoare_valoare; tabel;; LIE))
După cum puteți vedea, am folosit o matrice pentru al treilea argument pentru a efectua căutarea de mai multe ori într-o singură funcție VLOOKUP. și obțineți suma valorilor din coloanele 2. 3 și 4.
Acum, să aplicăm această combinație de VLOOK și SUMM la datele din tabelul nostru pentru a găsi valoarea totală a vânzărilor în coloanele de la B la M:
Important! Dacă introduceți o formulă de matrice, asigurați-vă că apăsați combinația Ctrl + Shift + Enter în locul tastei Enter obișnuite. Microsoft Excel va împleti formula ta în bretele:
Dacă apăsați pe Enter. calculul va fi efectuat numai pe prima valoare a matricei, ceea ce va duce la un rezultat greșit.
Poate că ați fost curios de ce formula din figura de mai sus arată [@Product]. ca valoare dorită. Aceasta se datorează faptului că datele mele au fost transformate într-o tabelă utilizând comanda Tabel din fila Inserare. Este mai convenabil pentru mine să lucrez cu foi de calcul Excel excelente decât cu intervale simple. De exemplu, când introduceți o formulă într-una din celule, Excel o copiază automat în întreaga coloană, salvând câteva secunde prețioase.
După cum puteți vedea, este destul de ușor să utilizați funcțiile VLOOK și SUMM în Excel. Totuși, aceasta nu este o soluție ideală, mai ales dacă trebuie să lucrați cu mese mari. Faptul este că utilizarea formulelor de matrice poate încetini aplicarea, deoarece fiecare valoare din matrice face un apel separat la funcția VLOOK. Se pare că mai multe valori din matrice, mai multe formule ale matricei din registrul de lucru și mai lent lucrarea Excel.
Această problemă poate fi depășită prin utilizarea unei combinații de funcții INDEX și MATCH în locul VLOOKUP și SUM. Mai departe, în acest articol veți vedea câteva exemple de astfel de formule.
Executăm alte calcule utilizând funcția VLOOKUP din Excel
Tocmai am analizat un exemplu despre modul de extragere a valorilor din mai multe coloane ale unui tabel și calcularea sumei acestora. În același mod, puteți efectua alte operații matematice cu rezultatele returnate de funcția VLOOKUP. Iată câteva exemple de formule:
Calculați media:
Formula se uită la o valoare din celula A2 din tabelul de căutare și calculează media aritmetică a valorilor care se găsesc la intersecția rândului și coloanelor B, C și D găsite.
Gasim maximul:
Formula urmărește o valoare din celula A2 din tabela de căutare și returnează valorile maxime care se găsesc la intersecția rândului și coloanelor B, C și D găsite.
Găsim minimul:
Formula căutată pentru o valoare din celula A2 din tabelul Lookup și returnează minimul valorilor care se găsesc la intersecția rândului și coloanelor găsite B, C și D.
Calculați procentajul din suma:
Formula se uită la o valoare din celula A2 din tabelul Lookup. apoi rezumă valorile care se află la intersecția rândului și coloanelor B, C și D găsite și numai atunci calculează 30% din sumă.
Dacă adăugăm formulele de mai sus la tabel din exemplul anterior. rezultatul va arata astfel:
Să luăm un exemplu pentru a vă face să înțelegeți ce este vorba despre conversație. Să presupunem că avem un tabel care enumeră numele clienților, produsele achiziționate și cantitatea lor (tabelul principal). În plus, există un al doilea tabel care conține prețurile mărfurilor (tabelul de căutări în tabel). Sarcina noastră este să scriem o formulă care să găsească suma tuturor ordinelor clientului dat.
Deoarece aceasta este o formulă de matrice, nu uitați să apăsați pe Ctrl + Shift + Enter la sfârșitul intrării.
Câmpul de căutare este numele foii în care se află zona.
Să analizăm componentele formulei astfel încât să înțelegeți cum funcționează și puteți să o personalizați pentru a se potrivi nevoilor dumneavoastră. Funcția SUMM este deocamdată abandonată, deoarece scopul său este evident.
Întrucât formula noastră este o formulă de matrice, ea repetă acțiunile de mai sus pentru fiecare valoare din matricea de căutare. În cele din urmă, funcția SUMM calculează suma valorilor rezultate din înmulțire. Nu este deloc dificil, nu ești de acord?
VPR și SUMMER - găsim și rezumăm valori care satisfac un anumit criteriu
Funcția SUMIF din Excel este similară cu SUM, pe care tocmai l-am analizat, deoarece rezumă și valorile. Singura diferență este că REZUMAT însumează numai acele valori care satisfac criteriul pe care l-ați setat. De exemplu, cea mai simplă formulă cu SUMMER:
- suma tuturor valorilor celulei din domeniul A2: A10. care sunt mai mult de 10.
Foarte simplu, nu? Acum, să examinăm un exemplu ușor mai complicat. Să presupunem că avem un tabel care listează numele furnizorilor și numerele lor de identificare (tabelul de căutare). În plus, există un alt tabel în care același ID este asociat cu datele despre vânzări (tabelul principal). Sarcina noastră este să găsim valoarea vânzărilor pentru un vânzător dat. Există 2 circumstanțe agravante:
- Tabelul principal conține mai multe înregistrări pentru un singur ID în ordine aleatorie.
- Nu puteți adăuga o coloană cu numele furnizorilor în tabelul principal.
Să scriem o formulă care să găsească toate vânzările efectuate de vânzătorul specificat și să însumeze și valorile găsite.
Înainte de a începe, permiteți-mi să vă reamintesc sintaxa funcției SUMIF:
- interval - argumentul vorbește de la sine. Acesta este pur și simplu gama de celule pe care doriți să o evaluați cu un anumit criteriu.
- criterii este o condiție care spune formula care valorile sumă. Acesta poate fi un număr, o referință de celule, o expresie sau o altă funcție Excel.
- sum_range este un argument opțional, dar foarte important pentru noi. Acesta definește gama de celule conexe care vor fi însumate. Dacă nu este specificat, Excel rezumă valorile celulei din primul argument al funcției.
După ce am adunat toate împreună, să definim al treilea argument pentru funcția noastră SUMMER. După cum vă amintiți, vrem să rezumăm toate vânzările efectuate de un anumit vânzător al cărui nume este dat în celula F2 (a se vedea figura de mai sus).
- interval - deoarece căutăm identificatorul vânzătorului, valorile acestui argument sunt valori din coloana B a tabelului principal. Puteți specifica o gamă de B: B (întreaga coloană) sau, transformând datele într-o tabelă, utilizați numele coloanei Main_table [ID].
criterii (criteriu) - pentru că numele vânzătorilor sunt scrise în tabelul de căutare (tabelul de căutare), utilizați funcția VLOOKUP pentru a găsi ID-ul. corespunzătoare vânzătorului specificat. Numele este scris în celula F2, deci folosim formula pentru a căuta:
Desigur, ați putea introduce numele drept valoare dorită direct în funcția VLOOKUP. dar este mai bine să folosiți o referință absolută la o celulă, pentru că astfel vom crea o formulă universală care va funcționa pentru orice valoare introdusă în această celulă.
Tot ce trebuie să faceți este să puneți piesele împreună, iar formula SUMMER + VPR va fi gata:
Trimiteți-le prietenilor: