Kompendium Excela: Praktyczne zastosowania funkcji Lookup i Odniesienia

Funkcje VLOOKUP, HLOOKUP, INDEX i MATCH w Excelu mogą znacznie ułatwić życie księgowym. Ten krótki poradnik pokazuje, jak one działają i przedstawia kilka sytuacji, w których można je zastosować.

Wprowadzenie do funkcji Lookup i Odniesienie

Praktyczne zastosowania: Wprowadzanie miesięcznych danych budżetowych do raportu finansowego lub mapowanie wartości z różnych źródeł do skonsolidowanego raportu.

Rozwiązania: Użyj jednej z funkcji z kategorii Lookup i Reference, aby wybrać wartość z listy lub tabeli

• VLOOKUP() znajduje wartość w kolumnie, a następnie zwraca wartość komórki w tym samym wierszu, co pasująca wartość z wyznaczonej kolumny (stąd V jak Vertical lookup)
• HLOOKUP() znajduje wartość w wierszu, a następnie zwraca wartość komórki w tej samej kolumnie, co pasująca wartość z wyznaczonego wiersza (stąd H jak Horizontal lookup)
• INDEX() zwraca wartość na przecięciu danego wiersza i kolumny. Wiersz i kolumna są oznaczone jako względne pozycje - np. wiersz 2, kolumna 3 to drugi wiersz w dół od pierwszej komórki w zakresie, trzecia kolumna w prawo od pierwszej komórki w zakresie
• Funkcja MATCH() zwraca względną pozycję elementu na liście. Funkcja MATCH() może być użyta z jedną z innych formuł wyszukujących, aby znaleźć kolumnę lub wiersz, z którego ma zostać zwrócona wartość. Alternatywnie, funkcja MATCH() może być użyta z INDEX(), aby zwrócić pozycję na przecięciu określonego wiersza i kolumny bez użycia żadnej z funkcji wyszukujących.

Użycie kreatora odnośników
Kreator odnośników może być bardzo przydatny przy tworzeniu niektórych bardziej złożonych odnośników - przykład INDEX() w komórce B21 poniżej został utworzony przez kreator odnośników. Aby skorzystać z Kreatora odnośników, najpierw upewnij się, że jest on zainstalowany - przejdź do Narzędzia - Dodatki i upewnij się, że zaznaczona jest opcja Kreator odnośników. Kreator powinien wtedy pojawić się jako opcja podrzędna pozycji Kreator w dolnej części menu Narzędzia. Excel 2007: Wstążka Formuły - sekcja Rozwiązania - Lookup.

Funkcja VLOOKUP() do przypisywania wartości nominalnych do kategorii
Funkcje VLOOKUP() i HLOOKUP() używają czwartego argumentu "Prawda/Fałsz", aby określić, czy funkcja wyszukująca powinna znaleźć najbliższe dopasowanie czy dokładne dopasowanie. Aby użyć domyślnego 'True' dla najbliższego dopasowania, pierwsza kolumna musi być posortowana w porządku rosnącym. Jeśli nie ma dokładnego dopasowania, lookup znajdzie następną największą wartość, która jest mniejsza niż wartość lookup. Tak więc na przykład C906 znajdzie wartość C000 w pierwszej kolumnie, która jest największą "liczbą" mniejszą niż C906. Jeśli użyjesz opcji "False", wtedy pierwsza kolumna nie musi być sortowana, a funkcja lookup zwróci pierwsze znalezione dokładne dopasowanie. Jeśli nie ma dopasowania, zwrócony zostanie błąd "#N/A error":

W naszym przykładzie utworzyliśmy tabelę zakresów kodów kont z dopasowanymi kategoriami. Tak więc kody kont od A000 do AZZZ będą skategoryzowane jako sprzedaż, od B000 do BZZZ jako koszt sprzedaży itd.

Użyj funkcji MATCH() dla bardziej elastycznych argumentów referencji
Chociaż VLOOKUP() i HLOOKUP() mają tylko dwie opcje dla tego argumentu, powiązana funkcja MATCH() jest bardziej elastyczna i ma 3 możliwe typy dopasowania:

• 1 - znajduje największą wartość, która jest mniejsza lub równa od wartości odnośnika (jak dla odnośników powyżej), lista musi być rosnąca
• 0 - znajduje dokładne dopasowanie, kolejność nie jest wymagana
• -1 - znajduje najmniejszą wartość, która jest większa lub równa wartości szukanej, lista musi być w porządku malejącym.