Wykres skrzynka i wąsy to potężne narzędzie statystyczne, które pozwala na wizualizację rozkładu danych w przystępny sposób. Dzięki temu wykresowi można łatwo identyfikować wartości maksymalne, minimalne, mediany oraz kwartylu, co pozwala na szybką analizę zmienności parametrów mierzonych w czasie.
Tworzenie takiego wykresu w Excelu jest wyjątkowo proste, wymaga jedynie odpowiedniego przygotowania danych.
Czym jest wykres skrzynka i wąsy?
Wykres skrzynka i wąsy (ang. box and whisker plot) to graficzna metoda przedstawiania rozkładu danych statystycznych. Jest szczególnie użyteczny, gdy chcemy podsumować parametr zmieniający się w czasie, na przykład z dnia na dzień. W moim wideo pokazuję, jak taki wykres może służyć do wizualizacji pomiarów wykonywanych codziennie, niezależnie od pory dnia, w której zostały zarejestrowane.
Ten typ wykresu zawiera wiele informacji statystycznych w jednym kompaktowym obrazie. Główne elementy to:
Skrzynka — reprezentuje zakres między pierwszym (25%) a trzecim (75%) kwartylem
Linia wewnątrz skrzynki — zazwyczaj reprezentuje medianę (wartość środkową)
Wąsy — linie wychodzące ze skrzynki do wartości minimalnych i maksymalnych (z pewnymi ograniczeniami)
Punkty odstające — pojedyncze punkty poza głównym zakresem danych
Jak stworzyć wykres skrzynka i wąsy w Excelu
Tworzenie wykresu skrzynka i wąsy w Excelu jest niezwykle proste. Pierwszym krokiem jest odpowiednie przygotowanie danych. W moim przypadku miałem kolumnę z datami oraz kolumnę z wartościami pomiarów. Nie ma znaczenia, o której godzinie dnia wykonano pomiar — istotne jest tylko to, że mamy konkretną wartość przypisaną do konkretnego dnia.
Po przygotowaniu danych, proces tworzenia wykresu wygląda następująco:
Zaznaczamy pojedynczą komórkę w naszych danych
Przechodzimy do karty Wstawianie
Wybieramy opcję wykresu "Skrzynka i wąsy"
Po wykonaniu tych prostych kroków, Excel automatycznie generuje wykres na podstawie naszych danych. Możemy teraz obserwować statystyczny rozkład pomiarów w postaci wizualnej, co znacznie ułatwia analizę.
Interpretacja wykresu skrzynka i wąsy
Za tym wykresem kryje się mnóstwo informacji statystycznych. W większości przypadków możemy przyjąć następującą interpretację:
Górny punkt (koniec górnego wąsa) — to wartość maksymalna z naszych danych (z pewnymi wyjątkami)
Dolny punkt (koniec dolnego wąsa) — to wartość minimalna
Linia w środku skrzynki — reprezentuje medianę (wartość środkową)
Górna krawędź skrzynki — oznacza trzeci kwartyl (75% danych ma wartość niższą)
Dolna krawędź skrzynki — oznacza pierwszy kwartyl (25% danych ma wartość niższą)
Wysokość skrzynki pokazuje rozrzut środkowych 50% danych, co jest bardzo wartościową informacją przy analizie zmienności parametrów. Im wyższa skrzynka, tym większe zróżnicowanie w środkowej części danych.
Punkty odstające
Na wykresie skrzynka i wąsy mogą pojawiać się punkty odstające (outliers), które są reprezentowane jako pojedyncze kropki. Są to wartości, które znacząco odbiegają od reszty danych i nie pasują do głównej części wykresu. W zależności od potrzeb analizy, możemy zdecydować, czy chcemy uwzględniać te punkty czy nie.
Jeśli nie interesują nas przyczyny tych odchyleń (czy to błędy pomiarowe, czy naturalne odstępstwa), możemy je łatwo wyłączyć z wykresu. W Excelu wystarczy odznaczyć opcję wyświetlania punktów odstających, a wykres automatycznie dopasuje swój rozmiar do pozostałych danych.
Grupowanie danych na wykresie
W moim wideo pokazuję również, jak można tworzyć wykresy skrzynka i wąsy dla pogrupowanych danych. Jeśli nasze dane mają nagłówki (np. różne kategorie pomiarów), Excel może utworzyć osobne skrzynki dla każdej grupy.
Proces jest podobny jak wcześniej:
Zaznaczamy pojedynczą komórkę w danych z nagłówkami
Przechodzimy do karty Wstawianie
Wybieramy wykres "Skrzynka i wąsy"
W tym przypadku Excel traktuje każdą kolumnę jako osobną serię danych. Każda seria ma swoją własną skrzynkę i własne punkty odstające. Możemy włączać lub wyłączać punkty odstające niezależnie dla każdej serii, co daje nam większą elastyczność w prezentacji danych.
Nagłówki kolumn zostają automatycznie użyte jako etykiety w legendzie, co pomaga w identyfikacji, która skrzynka reprezentuje którą grupę danych. Mogą to być daty, nazwy kategorii lub inne identyfikatory — wszystko zależy od charakteru analizowanych danych.
Zastosowania wykresów skrzynka i wąsy
Wykresy skrzynka i wąsy mają szerokie zastosowanie w różnych dziedzinach, gdzie ważna jest analiza statystyczna:
Analiza pomiarów wykonywanych w regularnych odstępach czasu
Porównywanie rozkładu danych między różnymi grupami
Identyfikacja wartości odstających i anomalii w danych
Wizualizacja zmienności parametrów
Szybka ocena symetrii rozkładu danych
Ten typ wykresu jest szczególnie przydatny, gdy chcemy uzyskać syntetyczny obraz dużej ilości pomiarów bez zagłębiania się w szczegóły poszczególnych wartości. Pozwala na szybką identyfikację trendów, anomalii i charakterystyk rozkładu, co może być kluczowe w procesie decyzyjnym.
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:
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:
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:
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.
W świecie analizy danych, prezentacja sumy wartości na wykresie kolumnowym skumulowanym może znacznie zwiększyć czytelność i wartość informacyjną naszych wizualizacji. Umieszczenie wartości zbiorczej na szczycie wykresu pozwala odbiorcom na szybkie uchwycenie całościowego obrazu prezentowanych danych, co jest szczególnie przydatne podczas prezentacji biznesowych lub raportowania.
Technika ta wymaga kilku prostych, ale precyzyjnych kroków w programie Excel, które znacząco podniosą jakość twojego wykresu.
Dlaczego warto pokazywać sumę na wykresie kolumnowym skumulowanym?
Wykresy kolumnowe skumulowane są doskonałym narzędziem do przedstawiania zależności między częściami a całością. Jednak standardowo Excel nie pokazuje sumy wszystkich wartości, co czasami jest kluczową informacją dla odbiorcy. Dodanie wartości sumy na szczycie każdej kolumny pozwala na natychmiastowe zrozumienie wielkości całkowitej, bez konieczności sumowania poszczególnych segmentów "w głowie".
W moim wideo pokazuję, jak można elegancko rozwiązać ten problem, tworząc profesjonalnie wyglądające wykresy, które zawierają zarówno szczegółowe dane w postaci segmentów kolumn, jak i sumy całkowite widoczne na pierwszy rzut oka.
Krok po kroku: Tworzenie wykresu z wartościami sumy
Przygotowanie danych i stworzenie podstawowego wykresu
Pierwszym krokiem jest odpowiednie przygotowanie danych. Oprócz wartości dla poszczególnych kategorii, które chcemy przedstawić w kolumnach skumulowanych, musimy obliczyć sumę tych wartości. Ta suma będzie później widoczna na szczycie każdej kolumny.
Aby stworzyć podstawowy wykres:
Zaznacz dane, wraz z obliczonymi sumami
Przejdź do karty "Wstawianie"
Wybierz opcję "Wykres kolumnowy skumulowany"
Po wykonaniu tych czynności, suma pojawi się jako ostatni element w każdej kolumnie skumulowanej. Jest to jednak dopiero początek procesu, ponieważ chcemy, aby suma była zapisana na wykresie, a nie stanowiła po prostu kolejny element kolumny.
Zmiana typu wykresu dla serii sumy
Kluczowym etapem jest zmiana sposobu prezentacji sumy. Zamiast być częścią kolumny skumulowanej, suma powinna być przedstawiona jako oddzielny element. W tym celu należy:
Kliknąć prawym przyciskiem myszy na serię danych reprezentującą sumę
Wybrać opcję "Zmień typ wykresu seryjnego"
W otwartym oknie dialogowym wybrać inny typ wykresu dla serii sumy
W moim wideo pokazuję, że najlepszym wyborem jest wykres punktowy (bez osi pomocniczej). Wykres punktowy umieszcza wartości dokładnie w punktach odpowiadających poszczególnym kolumnom (wartości 1, 2, 3 na osi X), co idealnie pasuje do naszego celu. Można również rozważyć wykres liniowy, ale wykres punktowy daje nam większą kontrolę nad formatowaniem.
Dostosowywanie wyglądu i czytelności wykresu
Dodawanie i formatowanie etykiet danych
Po zmianie typu wykresu dla serii sumy, kolejnym krokiem jest dodanie etykiet danych, które będą pokazywać wartości sumy. W tym celu:
Kliknij na serię punktową reprezentującą sumy
Użyj ikony "plus" obok wykresu i wybierz "Etykiety danych"
Dostosuj pozycję etykiet wybierając opcję "Powyżej"
Etykiety danych są kluczowym elementem, ponieważ to właśnie one pokazują wartości sum, które chcemy wyeksponować. Warto zadbać o ich odpowiednie sformatowanie, aby wyróżniały się na wykresie.
Usuwanie zbędnych elementów i poprawianie wyglądu
Aby nasz wykres był czytelny i profesjonalny, musimy usunąć zbędne elementy i odpowiednio sformatować pozostałe:
Usuń znaczniki punktowe z serii sumy (zaznacz serię, użyj Ctrl+1, przejdź do opcji znaczników i wybierz "Brak")
Rozważ usunięcie poziomych linii siatki dla lepszej czytelności
Dodaj etykiety danych również do pozostałych serii, jeśli jest to potrzebne
Dostosuj formatowanie etykiet sumy, na przykład dodając delikatne obramowanie w innym kolorze (np. pomarańczowym), aby podkreślić ich odrębność
Szczególnie istotne jest usunięcie znaczników punktowych, które mogą niepotrzebnie zaśmiecać wykres. Chcemy, aby widoczne były tylko etykiety z wartościami sum, a nie punkty, które je reprezentują.
Poprawianie legendy i tytułu wykresu
Ostatnim etapem jest dostosowanie legendy i dodanie odpowiedniego tytułu:
Kliknij raz na legendę, aby zaznaczyć całą legendę
Kliknij na element legendy reprezentujący sumę
Naciśnij klawisz Delete, aby usunąć ten element z legendy
Dodaj odpowiedni tytuł wykresu, który można powiązać z komórką lub skopiować z arkusza
Usunięcie sumy z legendy jest ważnym krokiem, ponieważ wartości te są już widoczne jako etykiety na wykresie, a ich obecność w legendzie mogłaby wprowadzać zamieszanie. Pamiętaj, że dobra wizualizacja danych powinna być jak najbardziej czytelna i pozbawiona redundantnych elementów.
Praktyczne zastosowania techniki
Technika pokazywania sum na wykresach kolumnowych skumulowanych jest szczególnie przydatna w wielu sytuacjach biznesowych i analitycznych. Może być wykorzystana do prezentacji:
Sprzedaży produktów w podziale na kategorie z łączną wartością sprzedaży
Budżetów departamentów z całkowitym budżetem firmy
Wyników finansowych w podziale na kwartały z rocznym podsumowaniem
Statystyk demograficznych z całkowitą liczbą badanej populacji
Zastosowanie tej techniki wizualizacji znacząco podnosi wartość informacyjną wykresu, pozwalając odbiorcom na szybkie uchwycenie zarówno struktury danych, jak i ich całkowitej wartości. Jest to szczególnie cenne podczas prezentacji, gdy czas na analizę danych przez odbiorców jest ograniczony.
Zalety i potencjalne problemy
Główną zaletą przedstawionej techniki jest zwiększenie czytelności danych i umożliwienie odbiorcom szybszego zrozumienia prezentowanych informacji. Suma widoczna na pierwszy rzut oka pozwala na natychmiastowe uchwycenie skali prezentowanego zjawiska.
Należy jednak pamiętać o potencjalnych pułapkach. Jeśli mamy wiele kolumn o znacznie różniących się wysokościach, etykiety sum mogą być trudne do odpowiedniego rozmieszczenia. W takich przypadkach warto rozważyć dodatkowe dostosowanie pozycji etykiet lub wykorzystanie innych technik wizualizacji.
W przypadku bardziej złożonych danych, gdzie mamy wiele kategorii i podkategorii, warto również zastanowić się nad zastosowaniem innych typów wykresów, takich jak wykresy hierarchiczne lub wykresy kaskadowe, które mogą lepiej oddawać złożone zależności między danymi.
W tym artykule pokażę, jak stworzyć profesjonalny wykres kolumnowy z różnymi kolorami dla wartości ujemnych oraz jak dodać niestandardowe etykiety danych, które znacznie poprawiają czytelność prezentowanych informacji. Te techniki są niezbędne podczas tworzenia raportów finansowych lub analiz, gdzie precyzyjne rozróżnienie między wartościami dodatnimi i ujemnymi ma kluczowe znaczenie.
Nauczysz się, jak wykorzystać wbudowane funkcje Excela do stworzenia bardziej informatywnych i estetycznych wizualizacji danych.
Tworzenie wykresu kolumnowego z różnymi kolorami dla wartości ujemnych
Podstawowym elementem naszego projektu jest stworzenie wykresu kolumnowego, który będzie wizualnie rozróżniał wartości dodatnie od ujemnych. Zaczynamy od przygotowania prostego zestawu danych przedstawiającego bilans wartości. Możemy mieć w arkuszu zarówno wartości dodatnie jak i ujemne, które będziemy chcieli odpowiednio wyróżnić.
Aby stworzyć podstawowy wykres, przechodzimy na kartę "Wstawianie" w menu Excela i wybieramy opcję "Wykres kolumnowy" a następnie "Zwykły". Excel automatycznie generuje wykres na podstawie zaznaczonych danych, jednak domyślnie wszystkie kolumny mają taki sam kolor — niezależnie od tego, czy reprezentują wartości dodatnie czy ujemne.
Teraz przechodzimy do najważniejszej części, czyli zmiany koloru dla wartości ujemnych. Klikamy prawym przyciskiem myszy na kolumny wykresu i wybieramy "Formatuj serię danych" lub używamy skrótu CTRL+1. W oknie formatowania szukamy opcji związanych z wypełnieniem. Znajdziemy tam bardzo ważny checkbox o nazwie "Odwróć, jeśli ujemne", który należy zaznaczyć.
Po zaznaczeniu tej opcji, Excel nie od razu pokazuje drugi kolor — musimy najpierw zdefiniować podstawowy kolor dla wartości dodatnich. Po wybraniu koloru (na przykład zielonego) pojawi się drugi kubełek z kolorem, gdzie możemy określić kolor dla wartości ujemnych (na przykład czerwony). Ta prosta zmiana znacząco poprawia czytelność wykresu, ponieważ od razu widać, które wartości są dodatnie, a które ujemne.
Dopracowanie wyglądu wykresu
Aby nasz wykres był jeszcze bardziej przejrzysty, warto dopracować jego wygląd. Możemy usunąć linie obramowania kolumn, jeśli nie są potrzebne. Dodatkowo, aby etykiety osi X nie nakładały się na kolumny, możemy zmienić ich położenie używając CTRL+1 i przechodząc do ustawień osi. Warto wybrać opcję, aby etykiety znajdowały się na dole wykresu, co poprawi jego czytelność.
Te proste modyfikacje sprawiają, że wykres jest bardziej estetyczny i profesjonalny, a dzięki różnym kolorom dla wartości dodatnich i ujemnych — znacznie bardziej informatywny na pierwszy rzut oka.
Tworzenie niestandardowych etykiet danych
Kolejnym krokiem w udoskonalaniu naszego wykresu jest dodanie niestandardowych etykiet danych. W podstawowej wersji wykresu brakuje etykiet, które pomogłyby odbiorcy szybko zrozumieć konkretne wartości. Aby je dodać, klikamy na plusik znajdujący się obok wykresu i zaznaczamy opcję "Etykiety danych".
Domyślnie Excel pokazuje wartości liczbowe, ale możemy zmodyfikować to, co wyświetlają etykiety. Klikamy prawym przyciskiem myszy na etykiety i wybieramy "Formatuj etykiety danych" (lub używamy CTRL+1). W oknie formatowania zobaczymy różne opcje, takie jak:
Wartość (domyślna opcja)
Nazwa kategorii
Nazwa serii
Wartość z komórek (najciekawsza opcja)
Opcja "Wartość z komórek" jest dostępna od wersji Excel 2013 i pozwala na stworzenie w pełni niestandardowych etykiet. Po zaznaczeniu tej opcji, otworzy się okienko, w którym musimy wskazać zakres komórek z naszymi własnymi etykietami. Najważniejsze jest, aby liczba komórek w tym zakresie odpowiadała dokładnie liczbie punktów danych na wykresie — dzięki temu odpowiednie etykiety zostaną przypisane do odpowiednich kolumn.
Problemy z wartościami ND w etykietach
Podczas tworzenia niestandardowych etykiet możemy napotkać pewne problemy. Jeśli w naszych danych znajduje się wartość ND (brak danych) lub pusta komórka, Excel może wyświetlić ją w sposób, który nie wygląda estetycznie. Wartość #N/D jest widoczna na etykiecie, a pusta komórka może powodować, że etykieta będzie zawierała średnik i liczbę, co wygląda nieprofesjonalnie.
Możemy próbować modyfikować separator etykiet (na przykład na nową linię lub spację), ale nadal nie daje to pełnej kontroli nad wyglądem i zawartością etykiet. Na szczęście istnieje lepsze rozwiązanie.
Tworzenie w pełni niestandardowych etykiet z formułami
Najlepszym sposobem na stworzenie idealnych etykiet danych jest przygotowanie ich w osobnych komórkach arkusza przy użyciu formuł. Możemy na przykład użyć funkcji JEŻELI, aby sprawdzić, czy dana komórka zawiera wartość, a następnie stworzyć odpowiedni tekst etykiety.
W moim przykładzie stworzyłem formuły, które sprawdzają, czy komórka zawiera wartość, i na tej podstawie generują odpowiedni tekst. Dla pierwszej wartości mogę wyświetlić po prostu "Obecne wartości", a dla kolejnych mogę pokazać bardziej złożoną informację, na przykład "Obecne: X, Zmiana: Y".
Po przygotowaniu takich niestandardowych etykiet w komórkach arkusza, wracamy do formatowania etykiet danych na wykresie (CTRL+1), wybieramy opcję "Wartość z komórek" i wskazujemy nowy zakres zawierający nasze formuły. Po zatwierdzeniu, Excel zastosuje te niestandardowe etykiety do naszego wykresu.
Teraz, gdy mamy już własne etykiety, możemy wyłączyć domyślne wyświetlanie wartości w etykietach danych, ponieważ te informacje są już uwzględnione w naszych niestandardowych etykietach. Dzięki temu wykres będzie czytelniejszy.
Finalizacja wyglądu wykresu
Na koniec warto dopracować wygląd całego wykresu, aby był jak najbardziej profesjonalny. Możemy dostosować rozmiar czcionki etykiet danych, aby lepiej komponowały się z całym wykresem. Jeśli etykiety są zbyt duże lub zbyt małe, możemy je łatwo zmienić w opcjach formatowania (CTRL+1), wybierając odpowiednią wielkość czcionki.
Możemy również dostosować położenie etykiet, aby nie nakładały się na siebie i były dobrze widoczne. W zależności od danych i wyglądu wykresu, czasami lepiej umieścić etykiety wewnątrz kolumn, a czasami na zewnątrz.
Dzięki wszystkim tym modyfikacjom stworzyliśmy profesjonalny wykres kolumnowy, który nie tylko rozróżnia wartości dodatnie i ujemne za pomocą kolorów, ale również dostarcza dokładnych informacji poprzez niestandardowe etykiety danych. Taki wykres jest znacznie bardziej wartościowy dla odbiorcy i lepiej komunikuje przedstawiane dane.
W tym artykule pokażę, jak stworzyć dynamiczny wykres TOP N w Excelu, który automatycznie wyróżnia największe wartości w zbiorze danych. Ta technika pozwala na szybkie identyfikowanie najważniejszych elementów w analizie danych, jednocześnie umożliwiając elastyczne dostosowanie liczby wyświetlanych wartości za pomocą prostego parametru.
Wykorzystanie wykresów TOP N jest niezwykle przydatne w raportowaniu biznesowym, gdy chcemy skupić uwagę odbiorcy na najważniejszych danych.
Podstawy tworzenia wykresu TOP N
Tworzenie wykresu TOP N w Excelu wymaga kilku kluczowych kroków, które pozwolą na dynamiczne wyświetlanie określonej liczby największych wartości w naszym zbiorze danych. W moim wideo pokazuję, jak osiągnąć ten efekt przy użyciu odpowiednich funkcji i formatowania. Celem jest stworzenie wykresu, który automatycznie wyróżni największe wartości i umożliwi łatwą zmianę parametru N, określającego, ile z tych największych wartości chcemy pokazać.
Pierwszym i najważniejszym krokiem jest wykorzystanie odpowiedniej formuły, która będzie filtrować nasze dane. Potrzebujemy funkcji warunkowej, która sprawdzi, czy dana wartość kwalifikuje się do grupy TOP N. Wykorzystamy do tego funkcję JEŻELI w połączeniu z funkcją MAX.K (lub analogiczną w zależności od wersji Excela), która pozwala na znalezienie k‑tej największej wartości w zbiorze.
Tworzenie formuły dla wartości TOP N
Formuła, którą tworzymy, musi sprawdzać dla każdej wartości w naszym zakresie danych, czy jest ona większa lub równa k‑tej największej wartości. Jeśli dana wartość spełnia ten warunek, zostaje zwrócona w wyniku; w przeciwnym razie zwracany jest błąd #N/D. Taki błąd jest preferowany w przypadku wykresów, ponieważ Excel automatycznie pomija te punkty podczas tworzenia wizualizacji.
Nasza formuła wygląda następująco:
=JEŻELI(wartość>=MAX.K(zakres;N);wartość;BRAK())
W tej formule:
wartość — to komórka, którą testujemy
zakres — to wszystkie wartości w naszym zbiorze danych
N — to parametr określający, którą największą wartość chcemy użyć jako próg
BRAK() — funkcja zwracająca błąd #N/D
Używając tej formuły, tylko wartości, które znajdują się w TOP N zostaną wyświetlone na wykresie, podczas gdy pozostałe będą pomijane. Ważne jest, aby podczas kopiowania formuły zablokować zakres danych za pomocą F4 (dodanie $ przed adresami komórek), by referencje do zakresu się nie przesuwały.
Tworzenie wykresu z wartościami TOP N
Po przygotowaniu danych z formułą filtrującą TOP N, możemy przejść do tworzenia wykresu. W moim przypadku wybieram wykres kolumnowy grupowany, który jest jednym z najczęściej używanych typów wykresów do prezentacji porównawczych.
Po wstawieniu wykresu będziemy mieli dwie serie danych:
Serię oryginalnych wartości (np. "Sprzedaż")
Serię filtrowanych wartości TOP N (np. "TOP 3")
Aby uzyskać efekt wyróżnienia tylko wartości TOP N, musimy dostosować nakładanie serii. W tym celu zaznaczamy wykres, naciskamy CTRL+1 (lub klikamy prawym przyciskiem myszy i wybieramy "Formatuj serię danych"), a następnie ustawiamy nakładanie serii na 100%. Dzięki temu nasza seria TOP N zasłoni oryginalną serię danych, pokazując tylko wybrane wartości.
Dostosowanie wyglądu wykresu
Po stworzeniu podstawowego wykresu możemy go dostosować, aby lepiej prezentował nasze dane:
Zmniejszenie szerokości odstępu między kolumnami, aby kolumny były szersze
Usunięcie niepotrzebnych elementów (np. tytułu wykresu czy linii siatki)
Usunięcie z legendy oryginalnej serii danych (zaznaczamy legendę, klikamy na niepotrzebną serię i naciskamy Delete)
Dodanie etykiet danych do serii TOP N, aby pokazać wartości
Dostosowanie obszaru kreślenia wykresu, aby optymalnie wykorzystać dostępną przestrzeń
Dynamiczna zmiana parametru N
Najważniejszą zaletą naszego rozwiązania jest możliwość dynamicznej zmiany liczby wyświetlanych wartości TOP N. Zamiast sztywno definiować tę wartość w formule, możemy utworzyć komórkę parametryczną, którą będziemy odwoływać się w naszej formule.
Aby ułatwić interpretację, warto odpowiednio sformatować tę komórkę, aby pokazywała np. "TOP 3" zamiast samej liczby 3. W tym celu:
Zaznaczamy komórkę z parametrem N
Naciskamy CTRL+1, aby otworzyć okno formatowania komórek
Przechodzimy do zakładki "Niestandardowe"
Wpisujemy format: "TOP "0
Dzięki temu gdy wpiszemy w tej komórce np. liczbę 4, wyświetli się jako "TOP 4". To nie tylko poprawia czytelność wykresu, ale również automatycznie aktualizuje nazwę serii danych w legendzie.
Następnie musimy zmodyfikować naszą formułę filtrującą, aby odwoływała się do tej komórki parametrycznej zamiast sztywnej wartości. W formule zamiast konkretnej liczby (np. 3) używamy referencji do naszej komórki parametrycznej. Ważne jest, aby zablokować tę referencję za pomocą F4, żeby nie zmieniała się podczas kopiowania formuły.
Testowanie działania dynamicznego parametru
Po wprowadzeniu wszystkich zmian możemy przetestować nasz wykres, zmieniając wartość w komórce parametrycznej. Jeśli wszystko zostało prawidłowo skonfigurowane, wykres powinien automatycznie się aktualizować, pokazując odpowiednią liczbę największych wartości.
Na przykład, zmieniając parametr z 3 na 4, wykres powinien pokazać 4 największe wartości. Zmieniając na 5, powinno być widocznych 5 największych wartości, itd. Jednocześnie nazwa serii w legendzie powinna się aktualizować, pokazując aktualną wartość parametru (np. "TOP 4", "TOP 5").
