VLOOKUP pe două sau mai multe coloane de criterii – Excel University

Dacă ați încercat vreodată să utilizați o funcție VLOOKUP cu două sau mai multe coloane de criterii, ați descoperit rapid că pur și simplu nu a fost construit în acest scop. Din fericire, există o altă funcție care poate funcționa ca o alternativă la VLOOKUP, în funcție de ceea ce doriți să returnați.

Obiectiv de căutare în mai multe coloane

Mai întâi, să confirmăm obiectivul nostru, analizând un exemplu de carte de lucru. Am exportat unele informații din sistemul nostru de contabilitate și, în principiu, rezumă totalele tranzacțiilor pentru lună, pe clase și pe cont. Un eșantion al exportului este prezentat mai jos:

Din aceste date exportate, am dori să preluăm sumele selectate pe baza coloane de clasă și cont. Vrem să recuperăm sumele și să le plasăm în micul nostru raport, în imaginea de mai jos:

Dacă sunteți familiarizat cu VLOOKUP funcție, este natural să încercați să creați raportul cu această funcție, deoarece, la urma urmei, aceasta este o sarcină de căutare. Și sarcinile de căutare sunt cel mai bine rezolvate cu funcții tradiționale de căutare … nu? Pai depinde. Depinde de ceea ce încercați să recuperați.

Sumarea condiționată pentru căutări

Dacă încercați să recuperați o valoare numerică, cum ar fi o sumă, este posibil ca o funcție de căutare tradițională să nu fii cel mai bun pariu al tău. Iata de ce. Începând cu Excel 2007, Microsoft a inclus funcția de sumare condiționată SUMIFS. Această funcție de sumare a condițiilor multiple este concepută pentru a adăuga o coloană de numere și include numai rânduri care îndeplinesc una sau mai multe condiții. Punctele încep să se conecteze încă?

Dacă aplicăm această idee sarcinii noastre la îndemână, ne-am da seama rapid că am putea folosi această funcție de sumare condiționată pentru a recupera valorile raportului nostru.

Primul argument al funcției SUMIFS este intervalul sumelor, adică coloana numerelor de adăugat. În cazul nostru, coloana care are valoarea pe care dorim să o returnăm. Argumentele rămase apar în perechi: gama criteriilor și valoarea criteriilor.

Este util să ne gândim la funcția din acești termeni: adăugați această coloană (argumentul 1), includeți doar acele rânduri în care această coloană (argumentul 2) este egal cu această valoare (argumentul 3) și unde această coloană (argumentul 4) este egală cu această valoare (argumentul 5) și unde … și așa mai departe, până la 127 de perechi.

Astfel, pentru a completa raportul nostru, vom prelua valorile sumelor din export și vom potrivi coloanele clasei și contului, așa cum se arată mai jos.

Dacă se întâmplă să existe mai multe rânduri cu aceeași clasă și conturi, atunci funcția SUMIFS va returna suma tuturor articolelor potrivite.

După cum puteți vedea, dacă valoarea Încercați să returnați este un număr, apoi funcția SUMIFS simplifică efectuarea căutărilor în mai multe coloane. Dar, dacă valoarea pe care încercați să o întoarceți nu este un număr? Ei bine, va trebui să utilizați o funcție de căutare tradițională, așa cum este discutat mai jos.

Utilizarea VLOOKUP cu metoda SUMIFS

O metodă este utilizarea VLOOKUP și SUMIFS într-o singură formulă. În esență, utilizați SUMIFS ca primul argument al VLOOKUP. Această metodă este explorată complet în această postare a Universității Excel:

Utilizarea VLOOKUP cu metoda CONCATENATE

Dacă încercați să returnați un șir de text mai degrabă decât un număr, sau utilizați o versiune de Excel care nu are SUMIFS, atunci probabil că sunteți blocat cu utilizarea unei funcții de căutare tradiționale, cum ar fi VLOOKUP, împreună cu funcția CONCATENATE pentru a genera o singură coloană de căutare unică. Această abordare este destul de bine documentată, dar ideea de bază merge astfel: creați mai întâi o singură coloană de căutare și apoi utilizați VLOOKUP.

Exemplul nostru va fi o listă de angajați, așa cum se ilustrează mai jos:

Trebuie să recuperăm starea din lista angajaților pentru micul nostru raport prezentat mai jos:

Deoarece valoarea pe care încercăm să o returnăm, starea, este un șir de text și nu un număr, nu avem voie să folosim funcția SUMIFS. Astfel, va trebui să mergem la școală veche cu VLOOKUP și CONCATENATE.

Începem prin a construi o coloană de ajutor care creează practic valorile de căutare combinate. Acest lucru poate fi realizat cu ușurință cu funcția CONCATENATE sau cu operatorul de concatenare (&). Această nouă coloană de căutare este ilustrată în coloana B de mai jos:

Acum avem o singură coloană de căutare care poate fi utilizată cu un funcție tradițională de căutare, cum ar fi VLOOKUP. Raportul poate fi completat căutând numele combinate din noul interval de căutare, după cum se arată mai jos:

Aceeași abordare poate fi utilizat atunci când trebuie luate în considerare două, trei sau mai multe coloane de căutare.

Concluzie

În plus față de posibilitatea de a efectua căutări în mai multe coloane atunci când valoarea returnată este numerică, funcția SUMIFS are avantaje suplimentare în comparație cu funcțiile tradiționale de căutare. De exemplu, returnează zero atunci când nu se găsește nicio valoare de potrivire, returnează suma tuturor potrivirilor, acceptă operatori de comparație și nu se va sparge când se introduce o nouă coloană între coloanele de căutare și returnare.

Deci, când sunteți pe cale să eliminați funcția VLOOKUP pentru a efectua o sarcină de căutare, luați în considerare utilizarea SUMIFS în loc. Credeți sau nu, funcția SUMIFS face o funcție de căutare minunată.

Dacă aveți alte abordări preferate pentru căutările cu mai multe coloane, ne-ar plăcea să aflăm mai multe … vă rugăm să postați un comentariu mai jos.

Fișier eșantion

Dacă doriți să jucați cu registrul de lucru folosit pentru a genera capturile de ecran de mai sus, vă rugăm să nu ezitați să descărcați fișierul eșantion:

MultColumnLookup