Zwracanie całych wierszy lub kolumn

Do tej pory XLOOKUP zwracał pojedynczą wartość. Może jednak zwrócić jednocześnie cały wiersz lub kolumnę danych.

Gdy formuła zwraca wiele wartości, są one automatycznie rozlewane do sąsiednich komórek. Dzięki temu jeden XLOOKUP może wypełnić cały niewielki rekord.

Trik polega na tym, aby tablica zwracana obejmowała kilka kolumn. Zamiast zwracać tylko B2:B20, należy zwrócić B2:E20.

XLOOKUP znajduje pasujący wiersz, a następnie zwraca każdą kolumnę z tego wiersza tablicy zwracanej. Jedna formuła, cztery wyniki.

Należy wpisać formułę w jednej komórce, na przykład F2, i nacisnąć Enter. Wartości pojawią się w komórkach F2, G2, H2 i I2.

Jasnoniebieska ramka obrysuje zakres rozlania. Edytuje się tylko komórkę w lewym górnym rogu; pozostałe są wypełniane przez rozlanie i nie można ich zmieniać bezpośrednio.

Tabela pracowników zawiera identyfikator w kolumnie A, a imię i nazwisko, dział, stanowisko oraz wynagrodzenie w kolumnach od B do E.

Należy wpisać identyfikator do komórki D2, aby jeden XLOOKUP zwrócił cały rekord. Po zmianie identyfikatora cały wiersz natychmiast się aktualizuje — to niewielkie narzędzie do wyszukiwania zapisane w jednej formule.

Ta sama idea działa pionowo. Jeśli wyszukiwanie odbywa się w wierszu nagłówków, można zwrócić całą kolumnę wyników.

W tym przypadku XLOOKUP przeszukuje wiersz nagłówków B1:E1 w poszukiwaniu etykiety z komórki D2, a następnie rozlewa w dół pełną kolumnę B2:E50 znajdującą się pod pasującym nagłówkiem.

Po rozlaniu formuły można odwołać się do całego rozlanego zakresu, zapisując adres komórki ze znakiem hash, na przykład F2#.

Jest to bardzo przydatne: funkcja SUM zastosowana do rozlanego wiersza nadal działa prawidłowo, nawet jeśli zmieni się jego szerokość, ponieważ F2# zawsze oznacza „całe rozlanie z komórki F2”.

Ponieważ wynik jest tablicą, można przekazać go bezpośrednio do funkcji, które przyjmują zakresy.

Można na przykład opakować wyszukiwanie w funkcję SUM, aby zsumować zwrócony wiersz miesięcznych wartości — wszystko w jednej formule, bez komórek pomocniczych.

Formuła rozlewająca potrzebuje pustych komórek, które może wypełnić. Jeśli jakakolwiek komórka na drodze rozlania zawiera już dane, XLOOKUP zwróci błąd #SPILL!.

Rozwiązanie jest proste: należy wyczyścić blokujące komórki albo przenieść formułę do pustego obszaru. Zakres rozlania musi być całkowicie pusty.

W dopracowanej karcie wyszukiwania można również rozlewać nagłówki pól.

Należy umieścić jeden XLOOKUP zwracający wiersz danych, a nad nim odwołać się do zakresu nagłówków. Gdy rozlanie się poszerzy lub zwęzi, etykiety nadal będą wyrównane ze zwracanymi kolumnami.

Można nawet zagnieździć jeden XLOOKUP w drugim. Wewnętrzny XLOOKUP zwraca całą kolumnę, a zewnętrzny wybiera z niej pojedynczą komórkę.

W ten sposób powstaje prawdziwe wyszukiwanie dwukierunkowe — dopasowujące jednocześnie wiersz i kolumnę — wykonane w całości za pomocą XLOOKUP. Jest to ciekawa alternatywa dla INDEX-MATCH-MATCH.

Wiedzą już Państwo, że XLOOKUP może zwracać więcej niż jedną wartość:

• Tablica zwracana obejmująca wiele kolumn rozlewa cały wiersz
• Tablica zwracana obejmująca wiele wierszy rozlewa całą kolumnę
• Do rozlania można odwołać się za pomocą przyrostka #
• Aby uniknąć błędu #SPILL!, należy wyczyścić blokujące komórki

Jedna formuła może dzięki temu służyć jako kompletny podgląd rekordu.

Ukończyli już Państwo kurs XLOOKUP, ucząc się rozlewania wyników:

• Poszerz tablicę zwracaną, aby rozlać cały wiersz lub kolumnę
• Rozlane wyniki wypełniają puste sąsiednie komórki i są oznaczone niebieską ramką
• Do rozlania można odwołać się za pomocą przyrostka #, na przykład F2#
• Aby uniknąć błędu #SPILL!, należy pozostawić obszar rozlania pusty

Po opanowaniu składni, wartości zastępczych, kierunku wyszukiwania i rozlewania XLOOKUP może zastąpić niemal każdą używaną przez Państwa starszą funkcję wyszukiwania.