Să ne gândim cum să combinați rapid două tabele mari împreună cu coloane și rânduri, adică să faceți o selecție nu după un parametru (ca funcțiile VLOOKUP sau HLOOKUP), ci cu două simultan (folosind funcțiile INDEX și MATCH).
Dacă sunteți familiarizat cu funcția VLOOKUP sau cu analogul său orizontal HLOOKUP, atunci ar trebui să vă amintiți că aceste funcții minunate caută informații doar printr-un singur parametru, adică într-o matrice unidimensională - după rând sau după coloană. Și dacă trebuie să selectăm date dintr-un tabel bidimensional prin coincidența a doi parametri simultan - atât rând, cât și coloană în același timp? Să luăm în considerare mai multe opțiuni pentru combinarea tabelelor.
Să presupunem că trebuie să combinăm două tabele care afișează portofoliul de credite și portofoliul de garanții:
Putem începe să folosim funcția VLOOKUP pentru a combina fiecare coloană individuală, dar dacă tabelele noastre au un număr extrem de mare de rânduri și coloane, acest exercițiu se poate transforma într-o adevărată durere. Cu toate acestea, există o cale de ieșire destul de simplă din această situație, deoarece Excel are două funcții excelente, INDEX și MATCH din categorie Referințe și matrice
Bine, deci ce trebuie să facem pentru a uni rapid cele două mese? Să începem prin a copia antetul celui de-al doilea tabel (cel la care ne vom alătura) și să-l lipim lângă antetul primului tabel. După numele capacului, funcția MATCH ne va oferi numărul de serie al coloanei, iar în funcție de numărul tranzacției - numărul de serie al liniei de care avem nevoie.
De fapt, dorim să găsim valoarea unei celule de la intersecția unui anumit rând și coloană dintr-un tabel. Pentru claritate, să împărțim sarcina în trei etape:
Deci, combinând toate cele de mai sus într-o singură formulă, obținem celula F14 urmatoarea formula:
=INDEX(J2:M18; MATCH(A14; K2:K18; 0); MATCH(F1; J1:M1; 0))
În acest fel, extinzând formula noastră pe întregul interval, vom atașa corect câmpurile suplimentare. De asemenea, trebuie să acordați atenție faptului că atunci când întindem formula, trebuie să fixăm intervalele cu semnul dolarului ($) (pentru fixare, puteți folosi tasta F4), pentru căutarea după cap, fixați doar linia ( F$1 ), pentru căutarea după numerele tranzacțiilor - numai coloana ( 14 USD ).
Să modificăm puțin exemplul anterior și să presupunem că avem tabele similare, dar anteturile lor sunt combinate, așa că funcția MATCH nu ne va ajuta să stabilim corect numărul de serie al coloanei.
În acest caz, putem crea un câmp tehnic deasupra tabelului și putem introduce manual numerele coloanelor. Apoi, putem folosi funcția deja familiară VLOOKUP.
Deci, formula pentru a doua opțiune a noastră va fi următoarea:
=VLOOKUP($A3; $J$3:$M$19; E$1; 0)
Din nou, nu uitați să marcați intervalul și câmpurile de căutare corespunzătoare cu un semn $ , astfel încât formula să funcționeze corect și să nu dea o eroare la deplasarea intervalelor.