Zastanówmy się, jak szybko połączyć dwie duże tabele wraz z kolumnami i wierszami, czyli dokonać selekcji nie jednym parametrem (jak funkcje VLOOKUP lub HLOOKUP), ale dwoma naraz (przy użyciu funkcji INDEX i MATCH).
Jeśli znasz funkcję VLOOKUP lub jej poziomy odpowiednik HLOOKUP, to powinieneś pamiętać, że te wspaniałe funkcje wyszukują informacje tylko według jednego parametru, czyli w tablicy jednowymiarowej - według wiersza lub kolumny. A jeśli mamy wybierać dane z dwuwymiarowej tabeli poprzez zbieżność dwóch parametrów na raz - jednocześnie wiersza i kolumny? Rozważmy kilka opcji łączenia tabel.
Załóżmy, że musimy połączyć dwie tabele przedstawiające portfel pożyczek i portfel zabezpieczeń:
Możemy zacząć używać funkcji VLOOKUP do łączenia poszczególnych kolumn, ale jeśli nasze tabele mają wyjątkowo dużą liczbę wierszy i kolumn, to ćwiczenie może okazać się naprawdę uciążliwe. Istnieje jednak dość proste wyjście z tej sytuacji, ponieważ Excel ma dwie doskonałe funkcje, INDEX i MATCH z kategorii Referencje i tablice
OK, więc co musimy zrobić, aby szybko połączyć oba stoły? Zacznijmy od skopiowania nagłówka drugiej tabeli (tej, do której będziemy dołączać) i wklejenia go obok nagłówka pierwszej tabeli. Zgodnie z nazwą nasadki funkcja MATCH poda nam numer seryjny kolumny, a zgodnie z numerem umowy - numer seryjny potrzebnej nam linii.
W rzeczywistości chcemy znaleźć wartość komórki na przecięciu określonego wiersza i kolumny w tabeli. Dla przejrzystości podzielmy zadanie na trzy etapy:
Łącząc wszystkie powyższe w jedną formułę, otrzymujemy następującą formułę dla komórki F14:
W ten sposób, rozciągając naszą formułę na cały zakres, poprawnie dołączymy dodatkowe pola. Należy również zwrócić uwagę, że przy rozciąganiu formuły musimy ustalić zakresy znakiem dolara ($) (w celu ustalenia można użyć klawisza F4), w przypadku wyszukiwania po czapce naprawiamy tylko linię (1 F$ ), do wyszukiwania po numerach transakcji - tylko kolumna ($A14 ).
Zmodyfikujmy nieco poprzedni przykład i załóżmy, że mamy podobne tabele, ale ich nagłówki są połączone, więc funkcja MATCH nie pomoże nam poprawnie określić numeru seryjnego kolumny.
W takim przypadku możemy utworzyć nad tabelą pole techniczne i ręcznie wstawić numery kolumn. Następnie możemy skorzystać ze znanej już funkcji VLOOKUP.
Zatem wzór dla naszej drugiej opcji będzie następujący:
Ponownie nie zapomnij o naprawieniu zakresu i odpowiednich pól wyszukiwania znakiem $, aby formuła działała poprawnie i nie powodowała błędu podczas przesuwania zakresów.