Îmbinare rapidă a meselor mari (VLOOKUP2D)

Descriere

Să luăm în considerare 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.

1. Combinarea tabelelor folosind INDEX și MATCH

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 (Lookup and Reference), care într-o pereche funcționează ca VLOOKUP 2D.

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. În funcție de numele capacului, funcția MATCH ne va oferi numărul de serie al coloanei, iar în funcție de numărul de acord - 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:

În primul rând, trebuie să determinăm numărul rândului corespunzător contractului de credit selectat. Acest lucru va ajuta la crearea funcției MATCH din categorie Referințe și matrice (Lookup and Reference). În special, formula MATCH(A14; K2:K18; 0) ne va oferi rezultatul dorit (pentru tranzacție „№5398” va fi numărul ordinal 13). Primul argument al acestei funcții este valoarea căutată (acord „№5398” din celula A14), al doilea este intervalul de celule în care căutăm un acord (coloana cu numerele acordului din tabel - K2:K18), al treilea argument specifică tipul de căutare (0 - potrivire exactă a numelui, căutarea aproximativă este interzisă).
În al doilea rând, într-un mod complet similar, trebuie să stabilim numărul de serie al coloanei din tabelul cu conținutul de care avem nevoie. Funcţie MATCH(F1; J1:M1; 0) va face acest lucru și va emite, de exemplu, valoarea 3 pentru Collateralname selectată de utilizator în celula F1.
Și, în sfârșit, în al treilea rând, avem nevoie de o funcție care poate scoate conținutul unei celule dintr-un tabel după numărul de rând și coloană - funcția INDEX din aceeași categorie Referințe și matrice (Lookup and Reference). Primul argument al acestei funcții este intervalul de celule (în cazul nostru, este întregul tabel, adică J2:M18), al doilea este numărul rândului, al treilea este numărul coloanei (și le vom determina folosind MATCH funcții).

Deci, combinând toate cele de mai sus într-o singură formulă, obținem următoarea formulă pentru celula F14:

=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, este necesar să acordăm atenție faptului că atunci când întindem formula, trebuie să reparăm intervalele cu un semn dolar ($) (pentru fixare, puteți folosi tasta F4), pentru căutarea după capac, fixăm doar linia (F$1 ), pentru căutarea după numerele tranzacțiilor - numai coloana (14 USD ).

2. Combinarea tabelelor folosind VLOOKUP

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ă remediați intervalul și câmpurile de căutare corespunzătoare cu semnul $, astfel încât formula să funcționeze corect și să nu dea o eroare la deplasarea intervalelor.

Articole pe tema: