Formula Excel: extrageți toate potrivirile cu coloana de ajutor -

Cuprins

Formula generică

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

rezumat

O modalitate de a extrage mai multe potriviri în Excel este de a utiliza INDEX și MATCH cu o coloană de ajutor care semnalizează datele care se potrivesc. Acest lucru evită complexitatea unei formule matrice mai avansate. În exemplul prezentat, formula din H6 este:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

unde ct (G3), date (B3: E52) și helper (E3: E52) sunt denumite intervale.

Explicaţie

Provocarea cu formule de căutare care recuperează mai multe potriviri este gestionarea duplicatelor (adică mai multe potriviri). Formulele de căutare precum VLOOKUP și INDEX + MATCH pot găsi cu ușurință prima potrivire, dar este mult mai greu să căutați „toate potrivirile” atunci când criteriile găsesc mai multe potriviri.

Această formulă abordează această provocare utilizând o coloană de ajutor care returnează o valoare numerică care poate fi utilizată pentru a extrage cu ușurință mai multe potriviri. Formula din coloana de asistență arată astfel:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Coloana de ajutor testează fiecare rând din date pentru a vedea dacă Departamentul din coloana C se potrivește cu valoarea din I3 și Clădirea din coloana D se potrivește cu valoarea din J3. Ambele teste logice trebuie să returneze TRUE pentru ca AND să returneze TRUE.

Pentru fiecare rând, rezultatul din funcția ȘI este adăugat la „valoarea de mai sus” din coloana de ajutor pentru a genera un număr. Efectul practic al acestei formule este un contor incremental care se modifică numai atunci când se găsește o potrivire (nouă). Apoi valoarea rămâne aceeași până când se găsește următoarea potrivire. Acest lucru funcționează deoarece rezultatele TRUE / FALSE revin cu AND sunt forțate la valorile 1/0 ca parte a operației de sumă. Rezultatele FALSE nu adaugă nimic, iar rezultatele ADEVĂRATE adaugă 1.

Înapoi în zona de extracție, formula de căutare pentru Nume din coloana H arată astfel:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Funcționând din interior spre exterior, partea INDEX + MATCH a formulei caută numele pentru prima potrivire găsită, folosind numărul rândului din coloana G ca valoare a potrivirii:

INDEX(data,MATCH($G6,helper,0),1)

INDEX primește toate cele 3 coloane de date ca matrice (intervalul numit „date”), iar MATCH este configurat pentru a se potrivi cu numărul rândului din coloana helper (intervalul numit „helper”) în modul de potrivire exactă (al treilea argument setat la zero) .

Aici devine evidentă priceperea formulei. Coloana de ajutor conține în mod evident duplicate, dar nu contează, deoarece MATCH va coincide doar cu prima valoare. Prin proiectare, fiecare „prima valoare” corespunde rândului corect din tabelul de date.

Formulele din coloanele I și J sunt aceleași cu H, cu excepția numărului coloanei, care este mărit în fiecare caz cu unul.

Instrucțiunea IF care înfășoară formula INDEX / MATCH îndeplinește o funcție simplă - verifică fiecare număr de rând din zona de extracție pentru a vedea dacă numărul rândului este mai mic sau egal cu valoarea din G3 (intervalul numit „ct”), care este numărul total al tuturor înregistrărilor potrivite. Dacă da, se execută logica INDEX / MATCH. Dacă nu, IF generează un șir gol ("").

Formula din G3 (denumită interval "ct") este simplă:

=MAX(helper)

Deoarece valoarea maximă din coloana de ajutor este aceeași cu numărul total de potriviri, funcția MAX este tot ceea ce avem nevoie.

Notă: zona de extracție trebuie configurată manual pentru a gestiona cât de multe date este necesar (adică 5 rânduri, 10 rânduri, 20 rânduri etc.). În acest exemplu, este limitat la 5 rânduri doar pentru a menține foaia de lucru compactă.

Am învățat această tehnică în cartea lui Mike Girvin Control + Shift + Enter.