Dynamiczne funkcje tablicowe w Excelu 365

Wpisujesz formułę w jednej komórce, a wynik sam rozlewa się na kilkanaście wierszy i kolumn. Lista się zmienia, wynik też. Tak działają dynamiczne funkcje tablicowe w Excelu: zamiast kopiować formułę w dół albo co chwilę klikać filtr, piszesz jedną formułę, która zwraca całą tabelę.

W poradniku przechodzimy sześć funkcji na jednej tabeli sprzedaży kwiatów: FILTRUJ, SORTUJ, SORTUJ.WEDŁUG, UNIKATOWE, SEKWENCJA i LOSOWA.TABLICA. Do tego to, w których wersjach Excela działają, czym jest zakres rozlania i dlaczego pojawia się błąd #ROZLANIE!.

Największe wrażenie robi połączenie kilku funkcji w jedną formułę. Ranking trzech najlepszych sprzedaży, który sam się aktualizuje, to jedna linijka.

W tym poradniku:

1. Czym są dynamiczne funkcje tablicowe
2. Funkcja FILTRUJ
3. Funkcje SORTUJ i SORTUJ.WEDŁUG
4. Funkcja UNIKATOWE
5. Funkcje SEKWENCJA i LOSOWA.TABLICA
6. Łączenie funkcji: ranking trzech najlepszych
7. Błąd #ROZLANIE! i inne pułapki

Czym są dynamiczne funkcje tablicowe

Zwykła formuła zwraca jedną wartość do jednej komórki. Funkcja tablicowa zwraca cały zakres wyników, który Excel sam rozkłada na sąsiednie komórki. Ten obszar to zakres rozlania: po kliknięciu w dowolną jego komórkę widać niebieską ramkę, a formuła siedzi tylko w pierwszej komórce. W pozostałych jest wyszarzona i nie da się jej edytować.

Funkcje opisane niżej działają w Excelu z Microsoft 365, w Excelu 2021 i 2024 oraz w Excelu w przeglądarce. W Excelu 2019 i starszych ich nie ma. Tam istniały tylko stare formuły tablicowe, zatwierdzane kombinacją Ctrl + Shift + Enter i oznaczane klamrami. W nowych wersjach tej kombinacji nie potrzebujesz, wystarczy Enter.

Wszystkie przykłady są na tabeli Excela o nazwie dane (zakres B3:F61) z kolumnami Imię, Nazwisko, Produkt, Ilość i Wartość. Odwołania w rodzaju dane[Produkt] to odwołania strukturalne do kolumn tej tabeli. Dzięki nim formuła sama obejmuje wiersze dopisane do tabeli.

Funkcja FILTRUJ

FILTRUJ zwraca tylko te wiersze, które spełniają warunek. Działa jak filtr na nagłówkach, ale wynik stoi obok danych i zmienia się razem z nimi. Wszystkie zakupy Anny:

=FILTRUJ(B4:F61;B4:B61="Anna")

Pierwszy argument to dane do zwrócenia, drugi to warunek dla każdego wiersza. Nie musisz zwracać wszystkich kolumn. Produkt i ilość dla samych tulipanów:

=FILTRUJ(dane[[Produkt]:[Ilość]];dane[Produkt]="Tulipany")

Kilka warunków łączysz mnożeniem, które działa jak „i”. Zakupy Adama Kamińskiego, czyli wiersze z tym imieniem i tym nazwiskiem:

=FILTRUJ(dane;(dane[Imię]="Adam")*(dane[Nazwisko]="Kamiński"))

Dodawanie zamiast mnożenia działa jak „lub”: (dane[Produkt]="Róże")+(dane[Produkt]="Lilie") zwróci wiersze z jednym albo drugim produktem. Gdy nic nie spełnia warunku, FILTRUJ zwraca błąd #OBLICZ!. Żeby zamiast niego pokazać komunikat, dopisz trzeci argument, na przykład "brak".

Funkcje SORTUJ i SORTUJ.WEDŁUG

SORTUJ zwraca dane posortowane, nie ruszając oryginału. Bez dodatkowych argumentów sortuje rosnąco według pierwszej kolumny:

=SORTUJ(dane)

Drugi argument to numer kolumny, trzeci kierunek: 1 rosnąco, -1 malejąco. Cała tabela od największej wartości sprzedaży (piąta kolumna):

=SORTUJ(dane;5;-1)

SORTUJ.WEDŁUG sortuje według kolumn wskazanych wprost, także takich, których w wyniku nie ma. Można podać kilka par „kolumna; kierunek”. Produkty alfabetycznie, a w obrębie produktu od największej ilości:

=SORTUJ.WEDŁUG(dane[[Produkt]:[Wartość]];dane[Produkt];1;dane[Ilość];-1)

Funkcja UNIKATOWE

UNIKATOWE zwraca listę bez powtórzeń. Każde imię klienta raz:

=UNIKATOWE(dane[Imię])

Obok takiej listy łatwo policzyć sumę sprzedaży dla każdego imienia. Jeśli lista zaczyna się w komórce AN4, napisz:

=SUMA.JEŻELI(dane[Imię];AN4#;dane[Wartość])

Znak # za adresem to odwołanie do całego zakresu rozlania. Gdy przybędzie nowy klient, lista imion wydłuży się o jeden wiersz, a sumy razem z nią.

UNIKATOWE sprawdza też kilka kolumn naraz. =UNIKATOWE(dane[[Imię]:[Nazwisko]]) zwraca niepowtarzalne pary imię i nazwisko w dwóch kolumnach, a =UNIKATOWE(dane[Imię]&" "&dane[Nazwisko]) te same pary złączone w jedną kolumnę. Jak zaznaczać i usuwać powtórki w samych danych, opisuję w poradniku o duplikatach w Excelu.

Funkcje SEKWENCJA i LOSOWA.TABLICA

SEKWENCJA tworzy ciąg kolejnych liczb. =SEKWENCJA(10) daje liczby od 1 do 10 w kolumnie, a =SEKWENCJA(3;5) tablicę z trzema wierszami i pięcioma kolumnami, wypełnioną od 1 do 15. Przydaje się do numerowania, które nie rozjeżdża się po usunięciu wiersza. Więcej o tym w poradniku o automatycznym numerowaniu.

LOSOWA.TABLICA zwraca tablicę liczb losowych. =LOSOWA.TABLICA(10) to dziesięć ułamków od 0 do 1, a =LOSOWA.TABLICA(4;4;1;100;PRAWDA) tablica cztery na cztery z liczbami całkowitymi od 1 do 100. Dane testowe do ćwiczeń masz od ręki. Połączona z SORTUJ.WEDŁUG układa tabelę w losowej kolejności, na przykład do losowania kolejności w grafiku:

=SORTUJ.WEDŁUG(dane;LOSOWA.TABLICA(ILE.WIERSZY(dane)))

Pamiętaj, że liczby losowe zmieniają się przy każdym przeliczeniu arkusza, czyli po każdej edycji. Jeśli wynik ma zostać, skopiuj go i wklej jako wartości.

Łączenie funkcji: ranking trzech najlepszych

Siła funkcji tablicowych wychodzi dopiero wtedy, gdy wynik jednej trafia do drugiej. Trzy największe sprzedaże w tabeli:

=FILTRUJ(SORTUJ(dane;5;-1);SEKWENCJA(ILE.WIERSZY(dane))<=3)

Czytając od środka: SORTUJ ustawia tabelę od największej wartości, SEKWENCJA numeruje jej wiersze od 1, a FILTRUJ zostawia te z numerem do 3. Zmień trójkę na dziesiątkę, a dostaniesz pierwszą dziesiątkę. Każda nowa sprzedaż w tabeli od razu przelicza ranking.

Błąd #ROZLANIE! i inne pułapki

• #ROZLANIE! pojawia się, gdy w miejscu, w które wynik ma się rozlać, coś już stoi: liczba, tekst, a nawet spacja. Kliknij komórkę z błędem, a Excel obrysuje przerywaną linią obszar, którego potrzebuje. Wyczyść go i wynik pojawi się sam. Więcej o tym i innych błędach w poradniku o błędach w Excelu.
• Formuła tablicowa wewnątrz tabeli Excela nie zadziała. Tabela nie pozwala na rozlanie, więc wyniki funkcji tablicowych umieszczaj obok tabeli, w zwykłych komórkach.
• Plik otwarty w starszym Excelu. Excel 2019 pokaże zapisane wyniki, ale formuły zamieni na nazwy z przedrostkiem _xlfn i nie przeliczy ich po zmianie danych.
• Nie edytuj pojedynczych komórek wyniku. Zmiana jest możliwa tylko w pierwszej komórce, tej z formułą. Jeśli wynik ma być punktem wyjścia do ręcznych poprawek, wklej go najpierw jako wartości.

W pliku do pobrania jest tabela z przykładu i wszystkie formuły z tego poradnika, każda nad swoim wynikiem.

🚀 Następny poziom

Formuły, które oszczędzają godziny przy raportach

✓INDEKS i PODAJ.POZYCJĘ, WYSZUKAJ.POZIOMO

✓SUMA.WARUNKÓW, LICZ.WARUNKI i funkcja WARUNKI

✓Tabele przestawne i formatowanie warunkowe na własnej formule

✓Ćwiczenia z rozwiązaniami: 17

Zobacz program kursu

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy