Formuły tablicowe w Excelu: od jednej komórki do całej listy

Mam sześć wierszy zamówień. Chcę policzyć ich wartości, wybrać jeden kanał sprzedaży i przygotować listę kanałów do sprawdzenia. Mogę kopiować formuły w dół, ręcznie włączać filtr i przepisywać nazwy. Mogę też zapisać kilka reguł, które zwrócą gotowe wyniki. Właśnie w takich zadaniach przydają mi się formuły tablicowe.

Zanim zacznę je skracać i zagnieżdżać, chcę rozumieć kształt danych. Ile wartości wchodzi do obliczenia? Ile ma wyjść? Czy oczekuję jednej liczby, kolumny czy całej tabeli? Taka chwila namysłu oszczędza mi później patrzenia na formułę, która wygląda imponująco, ale robi coś zupełnie innego.

Przejdę przez jeden syntetyczny przykład. Liczby nie pochodzą z żadnej firmy. Zostawiam mały zbiór celowo: możesz samodzielnie policzyć każdą pozycję i zauważyć, czy Excel zwrócił właściwe wiersze. Na tysiącu rekordów błąd też istnieje, tylko trudniej go zobaczyć.

Najpierw sprawdzam wersję i język Excela

Przykłady dotyczą Excela z obsługą tablic dynamicznych. Dokumentacja funkcji FILTRUJ wymienia Microsoft 365, Excel 2021 i Excel 2024. Podział na „365” i „wszystkie starsze wersje” jest więc mylący. Numer wersji oraz dostępność konkretnej funkcji sprawdzam także u odbiorcy skoroszytu.

W polskich formułach używam średników między argumentami. W angielskich odpowiednikach zwykle zobaczysz przecinki. Ustawienia regionalne mogą to zmienić. Jeżeli Excel odrzuca składnię od razu, najpierw sprawdzam nazwę funkcji i separator. Przepisywanie danych z kropkami zamiast przecinków na chybił trafił potrafi dołożyć drugi problem do pierwszego.

Najważniejsze nazwy to FILTRUJ, UNIKATOWE i SORTUJ, czyli FILTER, UNIQUE i SORT. SUMA to SUM, DŁ to LEN, a SUMA.ILOCZYNÓW to SUMPRODUCT. W tym artykule nie zapisuję ręcznie stałych tablicowych w klamrach, ponieważ ich separatory również zależą od lokalizacji. Zakres komórek pozwala spokojnie zobaczyć mechanizm bez dodatkowej zagadki ze znakami.

Gdy przejmuję stary plik, sprawdzam jego działanie przed zmianą formuł. Sam fakt, że nowy Excel otwiera skoroszyt, nie oznacza, że warto przebudować wszystkie obliczenia. Potrzebuję kopii do pracy, znanych wyników kontrolnych i informacji, kto będzie później utrzymywał raport.

Co właściwie nazywam tablicą

Tablica jest uporządkowanym układem wartości. Może mieć jedną kolumnę, jeden wiersz albo kilka wierszy i kolumn. Zakres C2:C7 dostarcza sześć wartości ustawionych pionowo. Zakres A2:D7 dostarcza prostokąt o sześciu wierszach i czterech kolumnach. Formuła może przetwarzać taki układ bez tworzenia osobnego obliczenia wpisanego ręcznie w każdej komórce.

Rozróżniam przy tym formułę i funkcję. Funkcja ma nazwę oraz argumenty, na przykład SUMA. Formuła może składać się z funkcji, operatorów i odwołań. Mnożenie dwóch zakresów jest obliczeniem tablicowym, chociaż nie występuje w nim żadna nazwana funkcja. Określenie „funkcje tablicowe” bywa wygodnym skrótem, ale zasada działania dotyczy całego obliczenia.

Wynik pośredni i wynik końcowy też mogą mieć różny kształt. Najpierw obliczam sześć wartości zamówień, później je sumuję. W środku mam tablicę sześciu liczb, a na końcu jedną liczbę. Brak rozlania na arkuszu nie dowodzi więc, że formuła przetwarzała tylko jedną wartość.

Ja wyobrażam sobie taki proces jako przechodzenie danych przez kolejne etapy. Na każdym etapie pytam o liczbę wierszy i znaczenie pojedynczej pozycji. To samo pytanie przydaje się później przy SQL-u, łączeniu tabel i budowie raportów. Zmienia się zapis, lecz przypadkowe pomieszanie poziomów szczegółowości nadal psuje wynik.

Przygotowuję sześć zamówień

Wpisz dane do A1:D7. Nagłówki zajmują pierwszy wiersz, dane zaczynają się w drugim. Nazwy kanałów pozostawiam po angielsku, aby oba warianty językowe ćwiczenia miały identyczne wartości. Kolumny C i D zawierają liczby. Cena oznacza umowną wartość jednej sztuki, bez podatków, rabatów i przeliczania walut.

Zerowa liczba sztuk jest celowa. Chcę pokazać, że zero jest konkretną wartością, a nie automatycznym dowodem błędu. W rzeczywistych danych sprawdziłbym znaczenie takiego rekordu: anulowana pozycja, rezerwacja albo pomyłka wymagają różnych decyzji. W ćwiczeniu przyjmuję po prostu, że taka pozycja ma wartość zero.

Zostaw wolne miejsce w kolumnach F:O. Poszczególne przykłady możesz wykonywać kolejno, usuwając poprzednią formułę z jej komórki początkowej. Nie wpisuj następnego przykładu na obszar już zajęty przez rozlany wynik. Excel nie potraktuje tego jako uprzejmej prośby o przesunięcie sąsiadów.

Jedno obliczenie, sześć wyników albo jedna suma

W F2 wpisuję poniższą formułę i zatwierdzam zwykłym Enterem:

=C2:C7*D2:D7

Otrzymuję pionowo 100, 60, 80, 100, 0 i 120. Excel mnoży odpowiadające sobie pozycje: C2 przez D2, C3 przez D3 i tak dalej. Właśnie dlatego kolejność danych ma znaczenie. Niezależne posortowanie samej kolumny cen połączyłoby sztuki z niewłaściwymi wartościami, choć formuła nadal wyglądałaby poprawnie.

W F2 znajduje się właściwa formuła. Pozostałe komórki pokazują jej rozlany wynik. Gdy chcę zmienić regułę, edytuję F2. Gdy potrzebuję ręcznie poprawić jeden rekord, poprawiam dane źródłowe, jeśli mam do tego podstawę. Nie próbuję zastępować pojedynczej komórki wewnątrz wyniku inną liczbą.

W H10 mogę od razu zsumować iloczyny:

=SUMA(C2:C7*D2:D7)

Wynik to 460. Sześć wartości pośrednich zostaje sprowadzonych do jednej sumy. Kontroluję ją ręcznie: 100 plus 60 plus 80 plus 100 plus zero plus 120. Ta sama logika ma też zapis przez funkcję, która od dawna obsługuje tablice:

=SUMA.ILOCZYNÓW(C2:C7;D2:D7)

SUMA.ILOCZYNÓW nie wymaga dawnego zatwierdzania CSE. W tym przykładzie wynik również wynosi 460. Nie zakładam jednak, że dwa zapisy zachowają się identycznie dla dowolnych uszkodzonych danych. Tekst, błędy i niezgodne wymiary zakresów wymagają osobnego sprawdzenia. Najpierw ustalam, jakie typy danych są dozwolone.

FILTRUJ zwraca rekordy spełniające warunek

W H2 umieszczam filtr wybierający kanał North:

=FILTRUJ(A2:D7;B2:B7="North";"Brak wyników")

Wynik zajmuje trzy wiersze i cztery kolumny. Obejmuje A101, A103 oraz A105. Warunek B2:B7=“North” tworzy po jednej odpowiedzi logicznej dla każdego wiersza. FILTRUJ wykorzystuje ten układ odpowiedzi do wyboru rekordów z A2:D7. Zerowa liczba sztuk w A105 niczego tu nie wyklucza, ponieważ warunek dotyczy wyłącznie kanału.

Argument z warunkiem musi pasować wymiarem do filtrowanych danych. Jeśli wybieram wiersze z sześciu rekordów, potrzebuję sześciu odpowiadających im rozstrzygnięć. Zakres B2:B6 pomija jeden rekord. To błąd konstrukcji formuły, a nie informacja, że ostatnie zamówienie należy odrzucić.

Trzeci argument określa wynik, kiedy żaden rekord nie pasuje. Zamień North na East, a zobaczysz tekst „Brak wyników”. Taki komunikat pomaga człowiekowi, ale jest już innym typem wyniku niż tabela zamówień. Jeśli kolejne obliczenie ma liczyć rekordy, musi uwzględniać ten przypadek. Komórka z komunikatem nie jest jednym znalezionym zamówieniem.

W dokumentacji FILTRUJ Microsoft opisuje zarówno brak dopasowań, jak i błędy wewnątrz warunku. Argument „jeśli puste” nie jest ogólnym sposobem ukrywania wszystkich problemów. Gdy w kanale pojawia się błąd arkusza, chcę go wykryć i wyjaśnić, zanim opublikuję wynik.

Kilka warunków bez zgadywania

Teraz potrzebuję kanału North i dodatniej liczby sztuk. Zmieniam formułę w H2:

=FILTRUJ(A2:D7;(B2:B7="North")*(C2:C7>0);"Brak wyników")

Mnożenie łączy warunki w każdym wierszu. Oba muszą być spełnione, więc pozostają A101 i A103. A105 odpada przez zero w kolumnie C. Najpierw sprawdzam oba porównania osobno, a dopiero potem ich połączenie. To prostsze niż szukanie literówki w długiej formule z kilkoma poziomami nawiasów.

Dla alternatywy mogę użyć dodawania. Poniższy przykład wybiera kanał North lub co najmniej trzy sztuki:

=FILTRUJ(A2:D7;((B2:B7="North")+(C2:C7>=3))>0;"Brak wyników")

Otrzymuję pięć rekordów, od A101 do A105. Ostatni A106 nie spełnia żadnego warunku. Porównanie sumy z zerem pokazuje wprost, że interesuje mnie spełnienie co najmniej jednego warunku. Rekord spełniający oba warunki nadal pojawiłby się tylko raz, bo wybieram istniejące wiersze, zamiast doklejać dwie osobne listy.

Nie zastępuję tej konstrukcji bezrefleksyjnie funkcją LUB. Funkcja logiczna może sprowadzić cały zestaw odpowiedzi do jednego wyniku, podczas gdy tutaj potrzebuję osobnej decyzji dla każdego rekordu. Zawsze wracam więc do pytania o kształt: sześć wierszy wymaga sześciu decyzji.

UNIKATOWE i SORTUJ budują listę kategorii

W M2 przygotowuję uporządkowaną listę kanałów:

=SORTUJ(UNIKATOWE(B2:B7))

Wynik to North, South i Web. Najpierw usuwam powtórzenia z listy nazw, potem sortuję pozostałe wartości. Kolejność tych operacji jest czytelna także dla osoby, która dopiero poznaje funkcje. UNIKATOWE zwraca domyślnie odrębne wartości, a SORTUJ porządkuje wynik.

Warto rozróżnić odrębną nazwę i nazwę występującą dokładnie raz. North występuje trzy razy, ale na liście odrębnych kanałów nadal ma swoje miejsce. Jeśli chcę tylko wartości z pojedynczym wystąpieniem, korzystam z trzeciego argumentu UNIKATOWE:

=UNIKATOWE(B2:B7;FAŁSZ;PRAWDA)

Tutaj dostaję wyłącznie Web. Drugi argument mówi, że porównuję wiersze, trzeci włącza wybór wartości występujących dokładnie raz. To przydatne rozróżnienie przy kontroli danych, lecz samo znalezienie powtórzeń nie rozstrzyga, czy mamy błąd. Trzy zamówienia z jednego kanału są całkiem normalne.

Nie oczekuję od UNIKATOWE pełnego czyszczenia nazw. Dodatkowe spacje, alternatywne etykiety i błędnie zapisane kategorie wymagają ustalonej reguły porządkowania. Zanim nazwę wynik słownikiem kanałów, porównuję go z dozwolonymi wartościami i wyjaśniam rozbieżności. Automatyczna lista bardzo sprawnie porządkuje również błędne etykiety.

Rozlany zakres i dane, które rosną

Jeżeli w M2 pozostaje formuła sortująca odrębne kanały, zapis M2# oznacza cały jej aktualny rozlany wynik. W O2 mogę policzyć jego wiersze:

=WIERSZE(M2#)

Otrzymuję trzy. Znak # pozwala odwołać się do wyniku bez ręcznego wpisywania jego ostatniego wiersza. Przydaje się, gdy liczba kategorii zmienia się po aktualizacji danych. Trzeba jednak pamiętać, że odwołanie dotyczy wyniku konkretnej formuły, wraz z jej ograniczeniami i ewentualnym błędem.

Sama dynamiczność wyniku nie rozszerza dowolnego zakresu źródłowego. Dopisanie zamówienia w ósmym wierszu nie sprawi, że zapis B2:B7 nagle zacznie obejmować B8. W przykładzie musisz zmienić zakres. W utrzymywanym raporcie rozważyłbym Tabelę Excela i odwołania strukturalne, które dostosowują się do jej nowych wierszy.

Rozlewającą się formułę umieszczam poza samą Tabelą Excela. Dane wejściowe mogą być w Tabeli, lecz obszar wyniku potrzebuje miejsca w zwykłej siatce arkusza. To rozróżnienie opisuje Microsoft w zasadach rozlania. Zostawiam też przestrzeń na wzrost wyniku, zamiast ciasno obudowywać go innymi raportami.

Jak diagnozuję błędy i starsze formuły

Jeżeli pojawia się #ROZLANIE!, sprawdzam miejsce docelowe. W angielskim interfejsie ten błąd nazywa się #SPILL!. Przeszkodą może być wpisana wartość, formuła wyglądająca na pustą, scalenie komórek lub granica arkusza. Zamiast od razu czyścić cały obszar, ustalam, co go zajmuje i czy można bezpiecznie przenieść wynik.

Druga grupa problemów dotyczy samych danych. Pusta komórka w ilości może w prostym mnożeniu dać zero, przez co wygląda jak poprawnie rozliczone zamówienie. Tymczasem brak ilości i świadomie wpisane zero mają inne znaczenie. Przy kontroli wejścia osobno sprawdzam brakujące wartości, typ liczbowy oraz dopuszczalny zakres. Poprawny wynik działania operatora jeszcze nie potwierdza poprawności danych.

W starszych plikach mogę spotkać formuły zatwierdzane Ctrl+Shift+Enter, czyli CSE. Excel pokazuje wtedy klamry wokół formuły; nie dopisuję ich ręcznie. Taki sposób pracy miał inne zasady dotyczące obszaru wyniku. Porównanie Microsoft wyjaśnia różnice. Do nowych przykładów z dynamicznymi tablicami używam zwykłego Entera.

Z dawnego artykułu zostawiam też użyteczny pomysł z liczeniem znaków, ale na konkretnych komórkach. Wszystkie sześć identyfikatorów ma po cztery znaki:

=SUMA(DŁ(A2:A7))

Wynik wynosi 24. DŁ oblicza długość tekstu, a SUMA zbiera wyniki. Takie sprawdzenie może pomóc zauważyć zmianę formatu identyfikatora. Sama suma długości nie wykryje jednak każdej pomyłki: jeden za krótki i jeden za długi zapis mogą się wzajemnie zamaskować.

Ćwiczenie przed oddaniem raportu

Przed ćwiczeniem zapisuję jeszcze krótką instrukcję odświeżenia. Skąd pochodzą dane, kiedy zostały pobrane i jaki okres obejmują? Które kolumny można nadpisywać, a które zawierają obliczenia? Takie informacje umieszczam obok raportu, ponieważ kolejna osoba może mieć poprawną formułę i jednocześnie analizować nieaktualny plik. Data ostatniego otwarcia skoroszytu nie zastępuje daty danych źródłowych.

Rozdzielam też filtrowanie widoku od usuwania rekordów. FILTRUJ buduje nowy wynik, zachowując dane wejściowe w pierwotnym miejscu. Dzięki temu mogę porównać wybraną grupę z całością i wrócić do odrzuconych pozycji. Jeżeli kopiuję wynik jako wartości do innego pliku, tracę tę automatyczną zależność. Taki eksport jest migawką i powinien mieć jasno opisany moment wykonania.

Przy przekazywaniu arkusza ustalam, czy odbiorca potrzebuje żywego obliczenia, czy właśnie zamkniętej migawki. Oba warianty bywają użyteczne. Problem pojawia się wtedy, gdy ktoś oczekuje aktualizacji, a dostał statyczną kopię. Najlepsza formuła nie naprawi nieporozumienia dotyczącego sposobu korzystania z raportu.

Zacznij od odtworzenia wyników: suma 460, trzy zamówienia North, dwa dodatnie zamówienia North oraz trzy odrębne kanały. Potem usuń formułę z F2, wpisz dowolny tekst w F4 i ponownie wprowadź mnożenie w F2. Zobacz, jak Excel zgłasza przeszkodę. Usuń wyłącznie tę testową wartość i sprawdź powrót prawidłowego wyniku.

Następnie zmień kanał w B7 na Web. Łączna wartość powinna pozostać taka sama, a rozkład kanałów się zmieni. Przywróć dane i dopisz siódmy rekord poniżej zakresu. Sprawdź, które formuły go pomijają. Ten test dobrze pokazuje różnicę między automatyczną zmianą wyniku a poprawnie zaprojektowanym źródłem danych.

Na koniec poproś drugą osobę o wskazanie komórek wejściowych, miejsca edycji formuły i znaczenia komunikatu o braku wyników. Jeśli bez Twojego komentarza trudno to ustalić, dopisz instrukcję w skoroszycie. Czytelność przekazania jest częścią poprawności rozwiązania, szczególnie gdy raport ma żyć dłużej niż jedno popołudnie.

Jeśli następnym krokiem jest pobieranie opisu produktu po identyfikatorze, przejdź do funkcji wyszukiwania w Excelu. Tam wraca podobne pytanie: czy chcę jeden wynik, wiele dopasowań, czy informację o braku rekordu? Dobrze postawione pytanie zwykle upraszcza wybór formuły bardziej niż kolejna lista skrótów klawiaturowych.