Funkcje tablicowe Excel: Jak znaleźć najczęstszą wartość tekstową | Poradnik

W tym artykule pokażę Ci, jak znaleźć najczęściej występującą wartość tekstową spełniającą określone warunki w Excelu, używając zaawansowanych funkcji tablicowych. Nauczysz się, jak zidentyfikować na przykład najczęściej sprzedawany produkt w poszczególnych miastach, nawet w przypadku remisów, co znacząco usprawni analizę danych sprzedażowych.

Technika ta jest szczególnie przydatna przy pracy z dużymi zbiorami danych, gdzie ręczna analiza byłaby czasochłonna.

W moim filmie demonstruję krok po kroku, jak osiągnąć ten cel:

Problem najczęściej występującej wartości tekstowej

Często w analizie danych musimy znaleźć najczęściej występującą wartość tekstową, która spełnia określone warunki. W moim przykładzie chcemy określić, który produkt jest najczęściej sprzedawany w każdym mieście. Zadanie komplikuje się, gdy mamy do czynienia z remisami, czyli sytuacjami, gdy kilka produktów występuje z tą samą częstotliwością.

Problem szczególnie uwidacznia się w dwóch przypadkach:

• Gdy mamy remis na poziomie wartości większych niż 1 (np. kilka produktów sprzedanych po 2 razy)
• Gdy mamy remis na poziomie wartości równych 1 (każdy produkt występuje tylko raz)

Standardowe funkcje Excela mają trudności z obsługą remisów, szczególnie tych na poziomie pojedynczych wystąpień. Dlatego potrzebujemy bardziej zaawansowanego rozwiązania wykorzystującego funkcje tablicowe.

Rozwiązanie z wykorzystaniem funkcji LET

Aby rozwiązanie było bardziej czytelne i łatwiejsze do modyfikacji, wykorzystamy funkcję LET, która pozwala nam nazywać poszczególne formuły i używać tych nazw wielokrotnie w jednym wyrażeniu.

Pierwszym krokiem jest stworzenie nazwanej formuły do filtrowania produktów według miasta:

Krok 1: Filtrowanie produktów dla danego miasta

Zaczniemy od stworzenia nazwanej formuły "produkty", która będzie zawierać odfiltrowaną listę wszystkich produktów dla konkretnego miasta:

Używamy funkcji FILTRUJ, aby uzyskać listę produktów spełniających nasze kryterium:

LET(produkty, FILTRUJ(kolumna_produkt, kolumna_miasto=konkretne_miasto), ...)

Dzięki tej formule mamy listę produktów, ale są to wartości tekstowe, a funkcja znajdująca najczęściej występujące wartości działa lepiej z liczbami.

Krok 2: Konwersja tekstów na pozycje liczbowe

Kolejnym krokiem jest zamiana nazw produktów na ich pozycje liczbowe w naszej liście. Do tego użyjemy funkcji PODAJ.POZYCJĘ:

LET(produkty, FILTRUJ(...), 
    pozycje, PODAJ.POZYCJĘ(produkty, produkty, 0), ...)

Ta transformacja pozwala nam pracować z liczbami zamiast tekstów, co ułatwia znajdowanie najczęściej występujących wartości.

Krok 3: Znalezienie najczęściej występujących wartości

Teraz możemy użyć funkcji WYSTĘP.NAJCZĘŚCIEJ.TABLICA do znalezienia najczęściej występujących pozycji liczbowych:

LET(produkty, FILTRUJ(...),
    pozycje, PODAJ.POZYCJĘ(produkty, produkty, 0),
    bez_bledu, WYSTĘP.NAJCZĘŚCIEJ.TABLICA(pozycje), ...)

Funkcja ta zwraca tablicę z najczęściej występującymi wartościami. W przypadku remisów (gdy kilka wartości występuje z tą samą, najwyższą częstotliwością), zwróci wszystkie te wartości.

Krok 4: Konwersja pozycji z powrotem na nazwy produktów

Następnym krokiem jest konwersja pozycji liczbowych z powrotem na nazwy produktów przy użyciu funkcji INDEX:

LET(produkty, FILTRUJ(...),
    pozycje, PODAJ.POZYCJĘ(produkty, produkty, 0),
    bez_bledu, INDEX(produkty, WYSTĘP.NAJCZĘŚCIEJ.TABLICA(pozycje)), ...)

Krok 5: Połączenie wyników w jeden ciąg tekstowy

Ostatnim krokiem jest połączenie uzyskanych nazw produktów w jeden ciąg tekstowy, używając funkcji POŁĄCZ.TEKSTY:

LET(produkty, FILTRUJ(...),
    pozycje, PODAJ.POZYCJĘ(produkty, produkty, 0),
    bez_bledu, POŁĄCZ.TEKSTY(", ", INDEX(produkty, WYSTĘP.NAJCZĘŚCIEJ.TABLICA(pozycje))), ...)

Jako ogranicznik używamy przecinka ze spacją, co daje czytelny rezultat w przypadku remisów.

Obsługa błędów dla szczególnych przypadków

Funkcja WYSTĘP.NAJCZĘŚCIEJ.TABLICA nie radzi sobie z remisami na poziomie pojedynczych wystąpień. W przypadku gdy każdy produkt występuje tylko raz, funkcja zwraca błąd. Musimy więc dodać obsługę błędu do naszej formuły.

Użyjemy funkcji JEŻELI.BŁĄD, która w przypadku błędu zwróci alternatywny wynik:

LET(produkty, FILTRUJ(...),
    pozycje, PODAJ.POZYCJĘ(produkty, produkty, 0),
    bez_bledu, JEŻELI.BŁĄD(POŁĄCZ.TEKSTY(", ", INDEX(produkty, WYSTĘP.NAJCZĘŚCIEJ.TABLICA(pozycje))), POŁĄCZ.TEKSTY(", ", produkty)), ...)

W przypadku błędu, zwracamy wszystkie produkty połączone przecinkiem i spacją, ponieważ każdy z nich występuje dokładnie raz, więc wszystkie są "najczęstsze".

Praktyczne zastosowanie i rozszerzenie rozwiązania

Przedstawione rozwiązanie można łatwo rozszerzyć o dodatkowe kryteria, modyfikując początkową definicję "produkty". Na przykład, możemy znaleźć najczęściej sprzedawane produkty według kupca i miasta, dodając do filtra dodatkowy warunek.

Gdy mamy więcej danych, szansa na jednoznaczne wyłonienie najczęściej występującej wartości wzrasta. W tabeli z większą liczbą rekordów (np. 26 wierszy) rzadziej występują remisy, co ułatwia identyfikację dominującego produktu.

Funkcje tablicowe dają nam więc potężne narzędzie do analizy danych i znajdują zastosowanie w wielu scenariuszach biznesowych:

• Analiza najlepiej sprzedających się produktów
• Identyfikacja najaktywniejszych klientów
• Wyznaczanie najpopularniejszych kategorii
• Analiza trendów sprzedażowych w różnych lokalizacjach

Dzięki zastosowaniu funkcji LET nasze rozwiązanie jest czytelne i łatwe do modyfikacji, co jest kluczowe przy bardziej złożonych analizach. Pamiętaj, że im więcej danych masz do analizy, tym bardziej przydatne stają się tego typu zaawansowane funkcje tablicowe, pozwalające na szybkie i precyzyjne znajdowanie wzorców w danych.