Zaawansowane formuły Excela: obliczenia, wyszukiwanie i kontrola błędów

Zaawansowany arkusz rozpoznaję po tym, że da się zrozumieć jego wynik i bezpiecznie zmienić dane. Sama długość formuły niewiele mi mówi. Pięć zagnieżdżonych funkcji może rozwiązywać realny problem, ale może też ukrywać założenie, którego nikt już nie pamięta. Wolę zacząć od małej próbki, obliczyć oczekiwany wynik i dopiero wtedy składać kolejne elementy.

W tym artykule użyję sześciu syntetycznych pozycji zamówień. Obliczę ich wartość, wybiorę region, pobiorę kategorię produktu oraz rozdzielę kontrolowany kod tekstowy. Przy każdym kroku pokażę również sytuację, w której poprawna składnia nie wystarcza. Dzięki temu możesz przećwiczyć cały proces na pustym arkuszu, bez firmowego pliku i bez zgadywania, co powinno wyjść.

Formuły podaję z polskimi nazwami i średnikami. Angielska wersja artykułu zawiera odpowiadające im nazwy i przecinki. Separator zależy od konfiguracji programu oraz systemu. Obliczenia wykonano w Excelu dla Windows, przez angielski interfejs formuł; zgodność obu zapisów sprawdzono po mapowaniu nazw. Nie jest to deklaracja testu każdej wersji i platformy Excela.

Przygotuj dane i zapisz reguły

Wpisz nagłówki do A1:E1, a rekordy do A2:E7. Nazwy i wartości tekstowe pozostaw dokładnie takie jak w tabeli. D i E muszą zawierać liczby. Jeden wiersz oznacza pozycję zamówienia, więc ten sam produkt może pojawiać się kilka razy. To ważne: powtarzający się kod produktu w transakcjach jest oczekiwany, podczas gdy w słowniku kategorii będzie wymagał kontroli.

Ujemna ilość w O05 oznacza zwrot jednej sztuki w naszym ćwiczeniu. Zero w O04 jest świadomym zapisem zerowej ilości. Wszystkie ceny dotyczą tej samej umownej jednostki wartości. Nie ma tu podatków, rabatów ani kursów walut. Te uproszczenia zapisuję jawnie, bo formuła mnożąca ilość przez cenę nie rozstrzygnie za mnie, czy taki model pasuje do rzeczywistej sprzedaży.

W H1:I4 przygotuj osobny słownik. Nagłówki to Product i Category, a pary to P-A / Components, P-B / Accessories oraz P-C / Equipment. Każdy kod ma jedną kategorię. Zostaw kolumnę F na wartość pozycji, G na wynik wyszukiwania, a dalsze kolumny na kontrole. Oddzielenie wejścia, słownika i obliczeń ułatwia zauważenie przypadkowego nadpisania formuły.

Najpierw policz pojedynczy wiersz

W F1 wpisz Amount. Poniższą formułę umieść w F2 i skopiuj do F7. Odwołania względne powinny zmieniać numer wiersza: kolejna komórka korzysta z D3 i E3. Sprawdź pierwszy i ostatni wynik, zamiast zakładać, że przeciągnięcie zawsze objęło cały zakres. W większym pliku łatwo zatrzymać kopiowanie o jeden rekord za wcześnie.

=D2*E2

Oczekiwane wartości to kolejno 100, 60, 50, 0, -20 oraz 320. Ich suma wynosi 510. Region North daje 470, a South 40. Te liczby można policzyć ręcznie przed uruchomieniem bardziej złożonej formuły. Zachowuję kolumnę pomocniczą, ponieważ pozwala szybko znaleźć wiersz odpowiedzialny za różnicę. Kilka dodatkowych komórek bywa znacznie czytelniejsze od jednej ogromnej konstrukcji.

Wolne pole kontrolne, na przykład K2, może zawierać sumę F2:F7. To będzie punkt odniesienia dla następnego obliczenia. Kontrola ma sens wtedy, gdy rozumiesz jej zależności: dwie formuły korzystające z tego samego błędnego zakresu mogą zgodnie zwrócić zły wynik. Dlatego obok uzgodnienia sumy sprawdzam także sześć pozycji i ich oczekiwane wartości.

=SUMA(F2:F7)

SUMA.ILOCZYNÓW łączy mnożenie z sumowaniem

W K3 wpisz poniższy zapis. Funkcja mnoży odpowiadające sobie elementy dwóch zakresów i dodaje wyniki. W naszej próbce powinna zwrócić 510, tyle samo co suma kolumny pomocniczej. Dokumentacja SUMPRODUCT opisuje wymaganie jednakowych wymiarów argumentów. D2:D7 i E2:E7 obejmują te same sześć pozycji.

=SUMA.ILOCZYNÓW(D2:D7;E2:E7)

Nie zamieniam tych zakresów na całe kolumny. Utrudniłoby to kontrolę danych wejściowych i niepotrzebnie rozszerzyło pracę funkcji. Jeżeli tabela ma rosnąć, warto użyć tabeli Excela i odwołań strukturalnych, a następnie sprawdzić dodanie nowego wiersza. Tutaj celowo pozostawiam krótki, stały zakres, żeby granice obliczenia były widoczne w każdym przykładzie.

Zwróć uwagę na tekst w kolumnie liczbowej. W tym wariancie z osobnymi argumentami wartość tekstowa w tablicy może zostać potraktowana jak zero. Jeżeli E2 stanie się tekstem „50”, wynik może spaść do 410, mimo że komórka wygląda podobnie. Inny zapis, wykorzystujący operator mnożenia wewnątrz argumentu, może zachowywać się inaczej. Nie traktuj różnych składni jako zamiennych bez sprawdzenia typów.

Dodaj warunek i sprawdź jego zakres

Chcę obliczyć wartość wyłącznie dla North. Porównanie w pierwszym argumencie tworzy wartości logiczne, a podwójny minus zamienia je na jedynki i zera. Pozycje z South otrzymują zerową wagę. Wynik w K4 powinien wynosić 470. Warunek nie zmienia cen ani ilości, tylko określa, które wiersze uczestniczą w tej konkretnej sumie.

=SUMA.ILOCZYNÓW(--(C2:C7="North");D2:D7;E2:E7)

Zmień North na South i sprawdź 40, a następnie na East, którego nie ma w danych, i sprawdź zero. Taki wynik mówi, że warunek nie wybrał wartości do zsumowania. Nie dowodzi, że firma na pewno nie działa w tym regionie. Być może plik nie obejmuje jego transakcji. Znaczenie pustego wyboru ustalam na podstawie zakresu źródła.

Sprawdź też, co stanie się po przypadkowej zmianie jednego zakresu na D2:D6. Różne rozmiary powinny ujawnić błąd, który trzeba poprawić. Nie otaczam całej formuły obsługą błędów zwracającą zero, bo zamieniłbym wadliwy rachunek w wiarygodnie wyglądającą liczbę. Przy kilku warunkach najpierw oglądam każdy osobno, a potem sprawdzam wynik ich połączenia.

Rozdziel znalezienie pozycji od pobrania wartości

Kod produktu w B2 ma znaleźć się w słowniku H2:H4. W K5 możesz najpierw uruchomić samo PODAJ.POZYCJĘ. Wynikiem będzie 1, ponieważ P-A jest pierwszą pozycją wskazanego zakresu. Nie jest to numer wiersza arkusza. Opis MATCH rozróżnia pozycję względną i tryby dopasowania; ostatni argument zero wybiera dopasowanie dokładne.

=PODAJ.POZYCJĘ(B2;$H$2:$H$4;0)

Teraz w G2 połącz tę pozycję z INDEKS i skopiuj formułę do G7. INDEKS pobiera wartość ze wskazanego miejsca zakresu I2:I4. Dolary blokują słownik podczas kopiowania, a B2 pozostaje względne. Oczekuję kategorii Components, Accessories, Components, Equipment, Accessories i Equipment. Sprawdzenie całej szóstki pokazuje więcej niż samo potwierdzenie pierwszego wiersza.

=INDEKS($I$2:$I$4;PODAJ.POZYCJĘ(B2;$H$2:$H$4;0))

To połączenie pozwala niezależnie wskazać kolumnę klucza i wyniku. Jeżeli korzystasz z wersji obsługującej X.WYSZUKAJ, możesz wybrać krótszą konstrukcję opisaną w porównaniu funkcji wyszukiwania. W obu przypadkach nadal potrzebujesz poprawnego słownika. Wybór nowszej funkcji nie rozwiązuje problemu niejednoznacznych kategorii ani różnego znaczenia identycznego kodu w dwóch systemach.

Pierwsze dopasowanie nie potwierdza unikalności

W K6 policz wystąpienia szukanego produktu w słowniku. Nasze kody nie zawierają znaków wieloznacznych, więc poniższa kontrola ma prostą interpretację: zero oznacza brak kodu, jeden oczekiwaną parę, a więcej niż jeden wymaga wyjaśnienia. Dla kluczy zawierających gwiazdkę lub pytajnik trzeba osobno uwzględnić sposób interpretacji kryterium. Regułę zawsze dopasowuję do formatu identyfikatora.

=LICZ.JEŻELI($H$2:$H$4;B2)

W kopii ćwiczenia zmień H4 z P-C na P-A, pozostawiając kategorię Equipment. Wyszukiwanie P-A nadal zwróci Components, bo wybierze pierwsze dopasowanie. Kontrola pokaże jednak dwa wystąpienia. Taki słownik wymaga decyzji o właściwym kluczu lub poprawnej kategorii. Nie usuwam drugiego wiersza tylko po to, żeby kontrolka stała się zielona. Najpierw sprawdzam, skąd wzięła się rozbieżność.

Przywróć H4 do P-C przed następnym krokiem. Ten drobny nawyk pomaga w ćwiczeniach i w pracy: każdą celową zmianę testową odwracam albo opisuję w osobnej kopii. W przeciwnym razie kolejny błąd może wynikać z wcześniejszego eksperymentu. Słownik z jedną kategorią na produkt to założenie tego przykładu, a nie uniwersalna własność wszystkich danych produktowych.

Obsłuż brak kodu, zachowując diagnostykę

Zastąp B2 przez P-X. Surowe wyszukiwanie powinno zwrócić brak dopasowania. W wersji przeznaczonej do czytania można użyć JEŻELI.ND i opisać ten oczekiwany przypadek. Poniższą formułę wstaw tymczasowo do G2. IFNA reaguje na błąd niedostępnej wartości, pozostawiając inne rodzaje błędów widoczne. Po ćwiczeniu przywróć produkt P-A.

=JEŻELI.ND(INDEKS($I$2:$I$4;PODAJ.POZYCJĘ(B2;$H$2:$H$4;0));"Brak kodu")

Komunikat „Brak kodu” też wymaga uwagi. Jeśli sam słownik zawiera błąd niedostępnej wartości w kategorii, otaczająca funkcja może go również przechwycić. W naszym czystym słowniku opis odpowiada brakowi dopasowania; w większym procesie sprawdzam jakość słownika osobno. Obsługa błędu jest regułą prezentacji wyniku i nie zastępuje analizy jego pochodzenia.

Nie zamieniam brakującej kategorii na zero, bo zero nie jest kategorią produktu. Podobnie uszkodzone odwołanie do komórki nie oznacza sprzedaży równej zero. Szerokie JEŻELI.BŁĄD ma swoje zastosowania, lecz wymaga świadomego określenia, jakie problemy można ukryć i gdzie je później zobaczyć. Podczas budowania arkusza najpierw uruchamiam środkową formułę bez osłony.

Przetwarzaj tekst według znanego formatu

Do L2 wpisz tekst CAT - 017 , zachowując zwykłe spacje. W M2 usuń nadmiarowe odstępy. Wynikiem ma być CAT - 017. TRIM dotyczy określonych spacji i nie jest uniwersalnym narzędziem usuwającym każdy niewidoczny znak. Tekst skopiowany ze strony internetowej może wymagać rozpoznania innego znaku, zanim zdecydujesz o jego zamianie.

=USUŃ.ZBĘDNE.ODSTĘPY(L2)

W N2 pobierz prefiks przed pierwszym myślnikiem i usuń pozostałą spację. ZNAJDŹ wskazuje pozycję separatora, a LEWY pobiera znaki przed nim. Wynik to CAT. Dzielę pracę na M2 i N2, dzięki czemu widać etap czyszczenia. Gdy rezultat jest nieoczekiwany, łatwiej wskazać krok wymagający poprawienia.

=USUŃ.ZBĘDNE.ODSTĘPY(LEWY(M2;ZNAJDŹ("-";M2)-1))

Zamień tekst na CAT017 i sprawdź błąd braku separatora. Potem użyj CAT-017-PL: wynikiem nadal będzie CAT, bo szukamy pierwszego myślnika. To świadoma reguła dla określonego kodu. Nie przenoszę jej automatycznie na imiona i nazwiska. Pierwsza spacja nie jest wiarygodną definicją granicy imienia, nazwiska lub nazwy organizacji. Najlepiej otrzymać te informacje w oddzielnych polach źródłowych.

Formuły tablicowe zależą od programu i wersji

ARRAYFORMULA jest funkcją Google Sheets, więc nie wpisujemy jej jako funkcji Excela. Excel ma własne mechanizmy tablicowe. Nowe wersje obsługują wyniki rozlewające się na sąsiednie komórki, a starsze konstrukcje mogą wymagać zatwierdzenia Ctrl+Shift+Enter. Szczegóły i przykłady znajdziesz w artykule o formułach tablicowych.

Pokazane tutaj formuły zwracają pojedynczy wynik i nie wymagają ręcznego wpisywania klamer. Nie dodaję nawiasów klamrowych skopiowanych ze zrzutu starszego arkusza jako sposobu naprawy błędu. Najpierw sprawdzam nazwę funkcji, język, separator i obsługę przez daną wersję. Informacja, że przykład działał w innym programie, nie jest dowodem zgodności składni.

Przy przekazywaniu pliku proszę odbiorcę o przeliczenie krótkiej próbki w jego środowisku. Oglądam zarówno wyświetlany wynik, jak i formułę. Zapisana wcześniej wartość może wyglądać poprawnie, mimo że aktualny program nie potrafi odtworzyć obliczenia. Szczególnie istotne są nowsze funkcje i zależności od zewnętrznych skoroszytów, których odbiorca może nie mieć.

Ustal, jaki wynik ma powstać z tych liczb

Suma 510 ma w naszym modelu jasną interpretację: wartość pozycji po uwzględnieniu zapisanego zwrotu. Gdyby ktoś poprosił o „średnią cenę”, zadałbym dodatkowe pytanie. Średnia sześciu cen z kolumny E, średnia cen trzech produktów i iloraz wartości przez ilość odpowiadają na różne pytania. W zbiorze z zerem i zwrotem nie wybieram mianownika na podstawie tego, który wzór najłatwiej napisać.

Podobnie liczba wierszy nie jest tutaj liczbą klientów. Nie mamy identyfikatora klienta ani informacji o jego kolejnych zakupach. Nie obliczę więc wiarygodnej średniej na klienta przez podzielenie sumy przez sześć. Rozpoznanie brakującej informacji jest częścią pracy z formułami. Czasem poprawnym kolejnym krokiem jest uzyskanie dodatkowej kolumny, zanim powstanie następne obliczenie.

Jeżeli potrzebuję tylko sumy istniejącej już kolumny Amount według regionu, mogę wybrać funkcję sumowania warunkowego. SUMA.ILOCZYNÓW jest tu przydatna dlatego, że pokazuje także mnożenie dwóch zakresów. Nie stosuję jej automatycznie do każdego podsumowania. Czytelny arkusz korzysta z konstrukcji odpowiadającej zadaniu, a osoba przejmująca pracę powinna rozumieć, dlaczego właśnie tę wybrałem.

Zachowaj powiązanie między kolumnami

Załóżmy, że sortujesz produkty alfabetycznie. Zaznaczenie wyłącznie kolumny B może odłączyć kod od jego ilości i ceny, jeżeli pozwolisz na niezależne przestawienie tej kolumny. Same formuły nadal będą działały, a suma może nawet pozostać taka sama. Przestawiaj całe rekordy i sprawdzaj, czy O01 nadal opisuje P-A z ilością dwa oraz ceną pięćdziesiąt.

W podobny sposób kontroluję kopiowanie słownika. Zakres kluczy i zakres kategorii muszą zachować wspólny porządek. Jeżeli przesuniesz jeden z nich o wiersz, poprawnie odnaleziona pozycja może wskazać opis innego produktu. Dlatego testuję kilka znanych par, w tym początek i koniec zakresu. Kontrola unikalności wykrywa inny problem i nie zastąpi tego sprawdzenia.

Przed większą zmianą zapisuję kopię roboczą z rozpoznawalnym opisem. Po zmianie porównuję wartości, liczbę rekordów i kilka konkretnych identyfikatorów. Nie polegam wyłącznie na kolorze komórek ani na tym, że zniknął komunikat błędu. Dobre kontrole powinny ujawnić także sytuację, w której arkusz oblicza płynnie, lecz odpowiada na inne pytanie niż planowane.

Sprawdź zmianę danych przed oddaniem arkusza

Dodaj na końcu siódmą pozycję O07, P-A, North, ilość 1 i cenę 50. Dotychczasowe stałe zakresy kończą się na wierszu 7, więc nie obejmą nowego rekordu w wierszu 8. Najpierw zauważ ten problem, potem rozszerz wszystkie potrzebne zakresy i skopiuj formułę wiersza. Pełna suma powinna wynieść 560, a North 520.

Następnie wróć do sześciu pozycji i sprawdź oddzielnie tekstową cenę, brakujący produkt, zduplikowany klucz oraz kod bez separatora. Nie zmieniaj wszystkich rzeczy naraz. Każdy eksperyment powinien mieć nazwę, oczekiwany rezultat i przywrócony stan początkowy. Dzięki temu kontrolujesz przyczynę zmiany wyniku, zamiast szukać jej w kilku nakładających się modyfikacjach.

Na koniec zapisz obok arkusza jego założenia: jednostkę wartości, znaczenie zwrotu, granice zakresów i regułę unikalności słownika. Osoba przejmująca plik powinna umieć odtworzyć 510 bez pytania autora o każdy szczegół. Kiedy potrafisz wyjaśnić te zależności i wykryć celowo wprowadzony błąd, masz solidną podstawę do rozwijania formuł na większych danych.