Dynamiczne usuwanie ostatnich kolumn w Power Query pozwala na elastyczne przetwarzanie danych bez względu na zmieniające się nazwy kolumn. W tym artykule pokazuję, jak skutecznie przekształcić raporty z Excela poprzez usunięcie dwóch ostatnich kolumn niezależnie od ich nazw, co zapewnia stabilność rozwiązania nawet przy zmiennej strukturze danych.
Metoda ta wykorzystuje funkcje Table.ColumnNames i operacje na listach, co znacząco zwiększa użyteczność zapytań Power Query.
Problem z usuwaniem kolumn o zmiennych nazwach
Często podczas pracy z danymi w Power Query napotykamy na problem, gdy musimy usunąć określone kolumny, ale ich nazwy nie są stałe. W moim przypadku chciałem zawsze usunąć dwie ostatnie kolumny z raportu Excel, niezależnie od ich nazw. Standardowe podejście polegające na usuwaniu kolumn po nazwach nie zadziała w takiej sytuacji, ponieważ nazwy tych kolumn mogą się zmieniać w różnych raportach.
Pracując z wieloma raportami, zauważyłem, że mimo zmienności danych, struktura pozostaje podobna — zawsze chcemy usunąć ostatnie dwie kolumny. To wymaga stworzenia dynamicznego rozwiązania, które będzie działało niezależnie od tego, jak nazywają się te kolumny w konkretnym raporcie.
Standardowe podejścia do usuwania kolumn w Power Query
Power Query oferuje kilka standardowych metod usuwania kolumn, jednak nie wszystkie są odpowiednie dla naszego przypadku. Przyjrzyjmy się im bliżej:
Usunięcie kolumn po nazwie — metoda prosta, ale działa tylko wtedy, gdy znamy dokładne nazwy kolumn, które chcemy usunąć. W naszym przypadku nie możemy jej zastosować, ponieważ nazwy kolumn będą się zmieniać.
Wybieranie kolumn do zachowania — możemy zaznaczyć kolumny, które chcemy zachować i użyć opcji "Usuń inne kolumny". To podejście również wymaga znajomości konkretnych nazw.
W tej sytuacji potrzebujemy bardziej zaawansowanego rozwiązania, które będzie działać dynamicznie i zawsze usunie dwie ostatnie kolumny, niezależnie od ich nazw.
Dynamiczne rozwiązanie krok po kroku
Aby stworzyć dynamiczne rozwiązanie, które zawsze usuwa dwie ostatnie kolumny, potrzebujemy zastosować kilka przekształceń z wykorzystaniem funkcji Power Query. Oto jak to zrobić:
Krok 1: Pobranie danych z pliku Excel
Zaczynamy od pobrania danych z pliku Excel. W zakładce "Dane" wybieramy opcję "Pobierz dane z pliku arkusza" i wskazujemy lokalizację naszego pliku z raportem. W tym przykładzie pracujemy z jednym arkuszem na raz, więc wybieramy arkusz "Raport 1" i klikamy "Przekształć dane".
Krok 2: Usunięcie automatycznego wykrywania typów
Po załadowaniu danych zauważamy, że Power Query automatycznie dodaje krok "Zmieniono typ", który wykrywa typy danych dla poszczególnych kolumn. Ten krok odwołuje się do kolumn po ich nazwach, co w naszym przypadku stanowi problem — gdy zmienimy raport, nazwy kolumn mogą być inne, więc ten krok będzie generował błąd. Musimy go usunąć.
Krok 3: Tworzenie dynamicznej listy nazw kolumn
Teraz tworzymy dynamiczną listę nazw kolumn, z których będziemy usuwać ostatnie dwie:
Klikamy obok paska formuły i wybieramy "fx Dodaj krok"
Odwołujemy się do kroku, który zawiera wszystkie nagłówki (np. "Nagłówki o podwyższonym poziomie")
Używamy funkcji Table.ColumnNames aby wyciągnąć listę wszystkich nazw kolumn
W karcie "Narzędzia do obsługi listy" wybieramy "Usuń elementy" → "Usuń końcowe elementy" i ustawiamy liczbę elementów na 2
Po wykonaniu tych kroków otrzymujemy listę nazw kolumn bez dwóch ostatnich elementów. To będzie nasza dynamiczna lista kolumn, które chcemy zachować.
Krok 4: Uporządkowanie kroków i odwołań
Teraz musimy upewnić się, że nasze kroki są w odpowiedniej kolejności i prawidłowo się do siebie odwołują:
Przeciągamy utworzony krok z listą na koniec
Sprawdzamy odwołania — krok z listą powinien odwoływać się do kroku zawierającego wszystkie nagłówki
Zmieniamy nazwę kroku na bardziej opisową (np. "Usunięto dwie kolumny") używając klawisza F2 lub opcji "Zmień nazwę" z menu kontekstowego
W tym momencie mamy poprawnie skonfigurowaną dynamiczną listę nazw kolumn, które chcemy zachować.
Krok 5: Zastosowanie dynamicznej listy do wyboru kolumn
Ostatnim krokiem jest zastosowanie naszej dynamicznej listy do funkcji Table.SelectColumns:
Modyfikujemy krok, który zawiera funkcję Table.SelectColumns
Zamiast zahardkodowanej listy kolumn, używamy odwołania do naszego wcześniejszego kroku z listą
Używamy składni #"nazwa kroku" (np. #"Usunięto dwie kolumny")
Po zatwierdzeniu tych zmian, nasze zapytanie będzie zawsze dynamicznie wybierać wszystkie kolumny oprócz dwóch ostatnich, niezależnie od ich nazw.
Dodatkowe przekształcenia danych
W moim przykładzie chciałem również wykonać dodatkowe przekształcenie — umieścić nazwy miesięcy w jednej kolumnie, a wartości liczbowe w drugiej. Aby to zrobić:
Zaznaczamy dwie pierwsze kolumny
Klikamy prawym przyciskiem myszy i wybieramy opcję "Anuluj i przedstawienie innych kolumn"
W pasku formuły zmieniamy domyślne nazwy kolumn "Atrybut" na "Miesiąc" i "Wartość" na "Sprzedaż"
To przekształcenie pozwala nam uzyskać bardziej przejrzystą strukturę danych, gdzie miesiące i wartości sprzedaży są wyraźnie oddzielone.
Testowanie rozwiązania na różnych raportach
Aby upewnić się, że nasze rozwiązanie działa poprawnie, przetestowałem je na różnych raportach, zmieniając źródło danych w kroku nawigacji:
Raport 1: zawierał różne nazwy miesięcy
Raport 2: zawierał dodatkowy miesiąc (kwiecień)
Raport 3: miał mniej kolumn, ale rozwiązanie nadal działało prawidłowo
We wszystkich przypadkach nasze dynamiczne rozwiązanie prawidłowo usuwało dwie ostatnie kolumny, niezależnie od ich nazw i struktury raportu. To potwierdza, że stworzyliśmy uniwersalne rozwiązanie, które będzie działać z różnymi zestawami danych.
W tym artykule pokażę, jak efektywnie mierzyć czas wykonania zapytań Power Query w Excelu przy użyciu prostego kodu VBA. Dokładne monitorowanie czasu zapytań pozwala na optymalizację wydajności arkuszy i zapewnia lepszą kontrolę nad procesami przetwarzania danych.
Odpowiednie techniki pomiaru czasu są kluczowe dla deweloperów tworzących zaawansowane rozwiązania w Excelu.
W moim wideo demonstruję dwie metody pomiaru czasu wykonania zapytania Power Query:
Pomiar czasu wykonania zapytania Power Query za pomocą VBA
Monitorowanie czasu wykonywania zapytań Power Query jest niezbędnym elementem optymalizacji wydajności arkuszy Excel, szczególnie przy pracy z dużymi zbiorami danych. W tym artykule przedstawiam praktyczne metody pomiaru czasu wykonania zapytań przy użyciu prostego kodu VBA, który pozwala na precyzyjne określenie, jak długo trwa przetwarzanie danych.
Podstawowa idea polega na zapisaniu czasu początkowego przed uruchomieniem zapytania, a następnie obliczeniu różnicy po jego zakończeniu. Takie podejście daje dokładny wynik i pozwala na świadome zarządzanie wydajnością naszych rozwiązań w Excelu.
Pierwsza metoda: Odświeżanie zapytania na podstawie zaznaczonej komórki
Pierwsza metoda, którą prezentuję, wykorzystuje odświeżanie zapytania na podstawie zaznaczonej komórki. Oto kod VBA, który realizuje to zadanie:
Sub MierzCzasZapytania()
Dim t0 As Double
t0 = Timer
ActiveCell.ListObject.QueryTable.Refresh BackgroundQuery:=False
MsgBox "Czas wykonania: " & Timer - t0 & " sekund"
End Sub
W powyższym kodzie definiujemy zmienną t0, której przypisujemy aktualny czas systemowy przy użyciu funkcji Timer. Następnie odświeżamy zapytanie powiązane z tabelą w aktywnej komórce. Kluczowym elementem jest ustawienie parametru BackgroundQuery na False, co wyłącza odświeżanie w tle i pozwala na dokładny pomiar czasu.
Po zakończeniu odświeżania, kod wyświetla okno dialogowe z informacją o czasie wykonania zapytania. Aby uzyskać średni czas wykonania, możemy uruchomić makro kilkukrotnie (np. używając klawisza F5) i obliczyć średnią z otrzymanych wyników.
Druga metoda: Odświeżanie zapytania przez jego nazwę
Druga metoda polega na odświeżaniu zapytania poprzez bezpośrednie odwołanie do jego nazwy. Jest to szczególnie przydatne, gdy chcemy zmierzyć czas zapytania, które nie jest bezpośrednio związane z tabelą w aktywnej komórce. Kod VBA dla tej metody wygląda następująco:
Sub MierzCzasZapytaniaNazwa()
Dim t0 As Double
t0 = Timer
ThisWorkbook.Queries("Query1").Refresh
MsgBox "Czas wykonania: " & Timer - t0 & " sekund"
End Sub
W tym przypadku odświeżamy konkretne zapytanie o nazwie "Query1". Należy jednak pamiętać o ważnej kwestii — domyślnie zapytania Power Query są odświeżane w tle, co uniemożliwia dokładny pomiar czasu wykonania.
Problem odświeżania w tle
Podczas testów wykazałem, że metoda odświeżania przez nazwę zapytania nie daje dokładnych wyników, jeśli zapytanie ma włączoną opcję odświeżania w tle. Jest to szczególnie problematyczne dla zapytań, które są połączeniami (connections) i nie są ładowane bezpośrednio do arkusza Excel.
Aby rozwiązać ten problem, musimy wyłączyć odświeżanie w tle dla danego zapytania. Możemy to zrobić na dwa sposoby:
Ręcznie poprzez interfejs Power Query — klikamy prawym przyciskiem myszy na zapytanie, wybieramy "Właściwości" i odznaczamy opcję "Włącz odświeżanie w tle".
Programowo w kodzie VBA — w pierwszej metodzie używamy parametru BackgroundQuery:=False, ale niestety w przypadku odświeżania przez nazwę zapytania taka opcja nie jest dostępna.
Nazewnictwo zapytań w VBA
Warto zauważyć, że nazwy zapytań Power Query widoczne w edytorze i nazwy używane w VBA mogą się różnić. W kodzie VBA zapytania często mają dodatkowy przedrostek przed nazwą widoczną w interfejsie Power Query. Jest to istotna informacja, o której należy pamiętać podczas tworzenia kodu do pomiaru czasu zapytań.
Optymalizacja wydajności zapytań Power Query
Posiadając narzędzie do dokładnego pomiaru czasu wykonania zapytań Power Query, możemy przystąpić do optymalizacji ich wydajności. Oto kilka wskazówek, które mogą pomóc w przyspieszeniu zapytań:
Ograniczanie danych źródłowych — filtruj dane jak najwcześniej w procesie przetwarzania
Usuwanie niepotrzebnych kolumn — mniej danych to szybsze przetwarzanie
Minimalizacja transformacji — każda operacja wymaga czasu, staraj się łączyć podobne operacje
Używanie funkcji buforowania — funkcja Table.Buffer może przyspieszyć niektóre operacje
Monitorowanie używania pamięci — niektóre zapytania mogą wymagać dużych ilości pamięci, co wpływa na wydajność
Praktyczne zastosowania pomiaru czasu zapytań
Pomiar czasu wykonania zapytań Power Query ma wiele praktycznych zastosowań w codziennej pracy z Excelem:
Pozwala na identyfikację "wąskich gardeł" w naszych rozwiązaniach, czyli zapytań, które zajmują najwięcej czasu i wymagają optymalizacji. Umożliwia porównanie różnych podejść do tego samego problemu i wybór najwydajniejszego rozwiązania. Jest niezbędny podczas tworzenia rozwiązań dla użytkowników końcowych, aby zapewnić im komfortową pracę bez długiego oczekiwania na przetworzenie danych.
Regularne monitorowanie czasu wykonania zapytań pomaga również w identyfikacji potencjalnych problemów, które mogą pojawić się wraz ze wzrostem ilości przetwarzanych danych. Dzięki temu możemy odpowiednio wcześnie zareagować i zoptymalizować nasze rozwiązania.
Funkcja zawijania wierszy i kolumn w Excelu to nieocenione narzędzie, gdy mamy dane uszeregowane w jednej kolumnie lub wierszu, które powinny być uporządkowane w formie tabeli. Ta funkcja automatycznie reorganizuje dane zgodnie z określoną przez użytkownika liczbą kolumn lub wierszy, co pozwala na szybkie przekształcenie jednowymiarowego zakresu danych w czytelny układ dwuwymiarowy.
W tym artykule pokażę, jak używać funkcji ZAWIJAJ.WIERSZE i ZAWIJAJ.KOLUMNY, aby poprawić organizację danych.
Kiedy potrzebujemy zawijania danych?
Często zdarza się, że nasze dane są zapisane w pojedynczej kolumnie lub pojedynczym wierszu, a powinny być podzielone na kilka kolumn lub wierszy. Jest to typowa sytuacja, gdy importujemy dane z zewnętrznych źródeł lub otrzymujemy informacje w niewygodnym formacie. Zamiast ręcznie przenosić każdą wartość do odpowiedniej komórki, możemy wykorzystać nowe funkcje Excela, które zautomatyzują ten proces.
W moim wideo pokazuję, jak każda informacja powinna trafić do właściwej kolumny, zgodnie z logicznym uporządkowaniem. Przy zawijaniu wierszy numerujemy dane od lewej do prawej, a następnie od góry do dołu — tak jak zwykle czytamy tekst. Z kolei przy zawijaniu kolumn dane są układane najpierw z góry na dół, a dopiero potem od lewej do prawej.
Funkcja ZAWIJAJ.WIERSZE
Pierwszą omawianą funkcją jest ZAWIJAJ.WIERSZE, która przekształca jednowymiarowy zakres danych w układ wierszy o określonej szerokości. Jej składnia jest prosta, ale warto zrozumieć każdy z argumentów:
Wektor — jednowymiarowa tablica danych (jedna kolumna lub jeden wiersz)
Szerokość wiersza — liczba kolumn w każdym wierszu
Wartość wypełnienia (opcjonalnie) — wartość, która ma być użyta, gdy brakuje danych
W moim przykładzie zaczynam od pojedynczej kolumny i chcę ją przekształcić w układ, gdzie każdy wiersz zawiera 5 wartości. Specjalnie wybrałem przykład, w którym liczba danych nie jest wielokrotnością 5, aby pokazać, jak Excel radzi sobie z niepełnymi wierszami.
Po zastosowaniu funkcji ZAWIJAJ.WIERSZE z argumentami wskazującymi na nasz zakres danych i szerokością wiersza równą 5, Excel automatycznie reorganizuje dane. Każda kolumna zawiera odpowiednie informacje zgodnie z nagłówkami, ale w ostatnim wierszu brakuje wartości. W takim przypadku Excel domyślnie wyświetla błąd #N/D.
Radzenie sobie z brakującymi danymi
Aby uniknąć błędów #N/D, możemy wykorzystać trzeci, opcjonalny argument funkcji ZAWIJAJ.WIERSZE. Ten argument określa, jaką wartość Excel ma wstawić w miejsca, gdzie brakuje danych. W moim przykładzie pokazuję, jak można użyć pustego ciągu tekstowego ("") lub innego oznaczenia, na przykład kilku myślników ("—-").
Warto pamiętać o jednym istotnym szczególe: w wielu sytuacjach puste komórki są interpretowane jako 0. Aby sobie z tym poradzić, można dodatkowo zastosować funkcję JEŻELI, która sprawdza, czy wartość jest pustym ciągiem tekstowym, i odpowiednio ją obsługuje:
=JEŻELI(dane=""; ""; dane)
Dzięki temu puste komórki są poprawnie obsługiwane, a my unikamy niechcianych zer w naszych danych. Nowoczesne funkcje tablicowe w Excelu automatycznie radzą sobie z takimi operacjami, więc nie musimy już stosować formuł tablicowych z kombinacją klawiszy Ctrl+Shift+Enter.
Zawijanie danych z wiersza
Funkcja ZAWIJAJ.WIERSZE działa tak samo efektywnie, gdy zamiast kolumny chcemy przekształcić pojedynczy wiersz. W tym przypadku również podajemy zakres danych i liczbę elementów w wierszu, a Excel automatycznie reorganizuje dane.
Kolejność odczytu danych pozostaje taka sama: od lewej do prawej, a następnie z góry na dół. Nie ma znaczenia, czy zawijamy pojedynczą kolumnę, czy pojedynczy wiersz – kluczowe jest tylko to, aby pamiętać o właściwej kolejności danych.
Funkcja ZAWIJAJ.KOLUMNY
Drugim ważnym narzędziem jest funkcja ZAWIJAJ.KOLUMNY, która działa podobnie do ZAWIJAJ.WIERSZE, ale z inną organizacją danych wynikowych. Ta funkcja przyjmuje następujące argumenty:
Wektor — jednowymiarowa tablica danych
Wysokość kolumny — liczba elementów w każdej kolumnie
Wartość wypełnienia (opcjonalnie) — wartość dla brakujących danych
W moim przykładzie pokazuję, jak użyć tej funkcji dla pojedynczej kolumny danych, określając liczbę elementów w kolumnie na 4. Po zastosowaniu funkcji dane są organizowane najpierw w kolumnach (z góry na dół), a dopiero potem przechodzą do kolejnych kolumn (od lewej do prawej).
Ta różnica w organizacji danych jest kluczowa do zrozumienia — przy zawijaniu wierszy dane są najpierw organizowane w wierszach, a przy zawijaniu kolumn najpierw w kolumnach. Warto to zapamiętać, aby uniknąć pomyłek przy reorganizacji danych.
Zastosowanie dla danych w wierszu
Funkcja ZAWIJAJ.KOLUMNY może być również użyta do przekształcenia pojedynczego wiersza danych. W moim przykładzie pokazuję, jak przy użyciu tej funkcji z argumentem wysokości kolumny równym 2, dane z wiersza zostają zorganizowane w układ kolumnowy.
Choć ta funkcja może wydawać się mniej intuicyjna i rzadziej używana niż ZAWIJAJ.WIERSZE, w niektórych sytuacjach jest dokładnie tym, czego potrzebujemy do poprawnego uporządkowania danych.
Praktyczne zastosowania
Funkcje zawijania wierszy i kolumn są szczególnie przydatne w następujących sytuacjach:
Reorganizacja danych importowanych z zewnętrznych źródeł
Przekształcanie list danych w formaty tabelaryczne
Automatyzacja układu danych w raportach
Przygotowanie danych do analizy lub wizualizacji
Uporządkowanie danych po operacjach łączenia lub transpozycji
Te funkcje tablicowe znacznie ułatwiają pracę z danymi, eliminując potrzebę ręcznego kopiowania i wklejania wartości. Dzięki nim możemy szybko przekształcać jednowymiarowe zakresy danych w czytelne, dwuwymiarowe układy, które lepiej odpowiadają naszym potrzebom analitycznym.
Pamiętajmy jednak, że kluczem do efektywnego korzystania z tych funkcji jest zrozumienie kolejności, w jakiej dane są organizowane — przy ZAWIJAJ.WIERSZE od lewej do prawej, a następnie z góry na dół, a przy ZAWIJAJ.KOLUMNY najpierw z góry na dół, a potem od lewej do prawej. Znając te zasady, możemy szybko i efektywnie reorganizować nasze dane w Excelu.
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.
