W dzisiejszym artykule pokażę Ci potężną technikę, która pozwoli Ci dynamicznie usuwać wiersze z początku i końca danych w Power Query. Ta zaawansowana metoda jest niezwykle przydatna podczas pracy z plikami CSV, które często zawierają niepotrzebne informacje na początku lub końcu dokumentu, utrudniające analizę właściwych danych.
Zamiast ręcznie dostosowywać liczbę usuwanych wierszy przy każdej aktualizacji danych, możesz ustawić dynamiczne warunki, które automatycznie wykryją właściwe miejsce rozpoczęcia i zakończenia twoich danych.
W moim wideo pokazuję krok po kroku, jak wykorzystać ukryte możliwości funkcji Power Query do automatycznego usuwania wierszy:
Dlaczego dynamiczne usuwanie wierszy jest ważne?
Podczas pracy z danymi pochodzącymi z zewnętrznych źródeł, szczególnie z plików CSV, często napotykamy na problem niepotrzebnych informacji umieszczonych na początku lub końcu pliku. Mogą to być różnego rodzaju nagłówki, metryki, podsumowania lub po prostu puste wiersze. Standardowe podejście polegające na usunięciu konkretnej liczby wierszy jest mało elastyczne — jeśli format danych źródłowych się zmieni (np. przybędzie więcej wierszy nagłówkowych), nasze przekształcenie przestanie działać prawidłowo.
Dzięki metodzie, którą prezentuję, możemy zdefiniować warunki logiczne, które automatycznie rozpoznają, gdzie zaczynają się i kończą właściwe dane. Dzięki temu nasze przekształcenia będą działać niezawodnie, nawet jeśli struktura danych źródłowych ulegnie zmianie.
Usuwanie wierszy z początku danych
Pierwszym krokiem jest zaimportowanie pliku CSV do Power Query. Po zaimportowaniu możemy zauważyć, że na początku pliku znajdują się wiersze, które nie są częścią naszych właściwych danych — mogą to być różne informacje nagłówkowe lub metadane.
Standardowo moglibyśmy użyć funkcji "Usuń wiersze" z zakładki "Narzędzia główne" i wybrać opcję usunięcia określonej liczby pierwszych wierszy. Jednak to podejście nie jest elastyczne — musimy znać dokładną liczbę wierszy do usunięcia, a ta może się zmienić w przyszłych wersjach pliku.
Zamiast tego możemy wykorzystać ukrytą funkcjonalność funkcji Table.Skip. Oprócz usuwania określonej liczby wierszy, funkcja ta może również usuwać wiersze spełniające określony warunek, aż do napotkania pierwszego wiersza, który tego warunku nie spełnia.
W oknie dialogowym skasuj domyślną wartość liczbową
Wprowadź formułę warunkową wykorzystującą słowo kluczowe "each"
Na przykład, jeśli wiemy, że nasze właściwe dane zaczynają się od wiersza zawierającego słowo "miasto" w pierwszej kolumnie, możemy użyć następującej formuły:
each [Column1] <> "miasto"
Ta formuła mówi Power Query, aby usuwał wiersze dopóki wartość w kolumnie Column1 jest różna od "miasto". Gdy natrafi na wiersz zawierający "miasto", zatrzyma proces usuwania. W ten sposób, niezależnie od liczby wierszy na początku pliku, Power Query automatycznie zidentyfikuje właściwy punkt startowy naszych danych.
Usuwanie wierszy z końca danych
Podobnie możemy zastosować dynamiczne usuwanie wierszy z końca naszych danych. W plikach CSV często na końcu znajdują się podsumowania, stopki lub puste wiersze, które chcielibyśmy usunąć.
Tutaj wykorzystujemy funkcję Table.RemoveLastN, która podobnie jak Table.Skip, może przyjmować warunek zamiast konkretnej liczby wierszy do usunięcia. W tym przypadku Power Query będzie usuwał wiersze od końca dopóki spełniają określony warunek.
Na przykład, jeśli chcemy usunąć wszystkie wiersze od końca, które mają pustą wartość w kolumnie "województwo", możemy użyć następującej formuły:
each [województwo] = ""
Ta formuła instruuje Power Query, aby usuwał wiersze od końca dopóki wartość w kolumnie "województwo" jest pustym ciągiem znaków. Jak tylko natrafi na wiersz z niepustą wartością, zatrzyma proces usuwania.
Obsługa wartości null
Warto zauważyć, że jest różnica między pustym ciągiem znaków ("") a wartością null. W niektórych przypadkach możemy chcieć usunąć wiersze zawierające null zamiast pustych ciągów znaków. W takiej sytuacji należy odpowiednio zmodyfikować formułę:
each [województwo] = null
Różnica między takim dynamicznym usuwaniem a zwykłym filtrowaniem jest istotna. Dynamiczne usuwanie przestaje działać po napotkaniu pierwszego wiersza niespełniającego warunku, podczas gdy filtrowanie usunęłoby wszystkie wiersze spełniające określony warunek, niezależnie od ich położenia w tabeli.
Praktyczne zastosowanie
Ta technika jest szczególnie przydatna w scenariuszach, gdzie regularnie importujemy dane z tych samych źródeł, ale struktura plików może się nieznacznie zmieniać. Dzięki dynamicznemu usuwaniu wierszy nie musimy za każdym razem dostosowywać naszych przekształceń — Power Query automatycznie dopasuje się do zmian w strukturze danych.
Typowe przypadki użycia:
Importowanie raportów eksportowanych z innych systemów, które zawierają nagłówki i stopki
Przetwarzanie plików CSV z metadanymi na początku i podsumowaniami na końcu
Automatyzacja przekształceń dla plików o zmiennej strukturze
Tworzenie odpornych przepływów pracy, które nie wymagają ręcznych dostosowań przy każdej aktualizacji danych
Dzięki tej metodzie możemy znacznie zwiększyć automatyzację naszych procesów przetwarzania danych, minimalizując potrzebę ręcznych interwencji i eliminując potencjalne błędy związane z manualnym dostosowywaniem przekształceń.
Podsumowanie kluczowych punktów
Dynamiczne usuwanie wierszy w Power Query pozwala nam:
Automatycznie identyfikować początek właściwych danych na podstawie zawartości komórek
Dynamicznie określać koniec zestawu danych bez konieczności znania dokładnej liczby wierszy
Tworzyć przekształcenia odporne na zmiany w strukturze plików źródłowych
Rozróżniać między pustymi ciągami znaków a wartościami null podczas usuwania wierszy
Zwiększyć poziom automatyzacji przetwarzania danych w Excelu
Ta technika, którą poznałem na spotkaniu Excel London Meetup, znacząco usprawniła moje przepływy pracy z danymi i pozwoliła mi tworzyć bardziej elastyczne i niezawodne rozwiązania analityczne. Zachęcam do wypróbowania jej w swoich projektach i dzielenia się swoimi doświadczeniami.
Obliczanie poziomu w hierarchii pracowników może znacząco usprawnić zarządzanie strukturą organizacyjną firmy i analizę zależności między pracownikami. W tym artykule pokażę, jak wykorzystać Power Query i rekurencję do automatycznego określania, na którym poziomie hierarchii znajduje się każdy pracownik, co pozwoli na lepszą wizualizację struktury organizacyjnej i efektywniejsze podejmowanie decyzji kadrowych.
Dzięki tej metodzie unikniesz czasochłonnego ręcznego liczenia poziomów hierarchii, szczególnie w większych organizacjach o złożonych strukturach.
W moim wideo pokazuję krok po kroku, jak stworzyć funkcję rekurencyjną w Power Query, która automatycznie obliczy poziom hierarchiczny każdego pracownika:
Zrozumienie problemu hierarchii pracowników
Zanim przejdziemy do technicznego rozwiązania, warto dokładnie zrozumieć problem, który chcemy rozwiązać. W wielu organizacjach istnieje struktura hierarchiczna oparta na poleceniach lub mentorstwie, gdzie każdy pracownik (poza najwyższym szczeblem) ma osobę, która go poleciła lub jest jego przełożonym. Określenie, na którym poziomie hierarchii znajduje się dana osoba, jest kluczowe dla zrozumienia struktury organizacyjnej.
W naszym przykładzie mamy tabelę z danymi pracowników, gdzie każdy pracownik ma swoje ID oraz ID osoby polecającej. Osoba na samym szczycie hierarchii nie ma osoby polecającej (wartość null). Przyjmujemy, że ta osoba jest na poziomie 0 hierarchii. Każda kolejna osoba w dół hierarchii ma poziom o 1 wyższy niż osoba, która ją poleciła.
Na przykład, jeśli Jan (ID 1) jest na poziomie 0, a Anna (ID 2) została polecona przez Jana, to Anna jest na poziomie 1. Jeśli Tomasz (ID 5) został polecony przez Annę, to jest on na poziomie 2, itd. Ręczne liczenie tych poziomów byłoby pracochłonne, szczególnie w dużych organizacjach, dlatego zastosujemy automatyzację z użyciem Power Query.
Tworzenie funkcji rekurencyjnej w Power Query
Aby rozwiązać nasz problem, stworzymy funkcję rekurencyjną, która będzie wywoływać samą siebie, aż dojdzie do najwyższego poziomu hierarchii. Rekurencja to technika, w której funkcja wywołuje samą siebie z różnymi parametrami, aż do osiągnięcia warunku końcowego.
Rozpoczynamy od pobrania naszej tabeli z danymi pracowników do Power Query. Następnie tworzymy nowe puste zapytanie, które przekształcimy w naszą funkcję:
Z karty "Dane" wybieramy "Z tabeli/zakresu", aby pobrać naszą tabelę do Power Query
W Power Query, z karty "Narzędzia główne", rozwijamy "Nowe źródło", wybieramy "Inne źródła", a następnie "Puste zapytanie"
Przechodzimy do Edytora zaawansowanego, aby wpisać kod naszej funkcji
Kod naszej funkcji rekurencyjnej będzie wyglądał następująco:
Implementacja funkcji poziom hierarchii
Funkcja, którą tworzymy, przyjmuje dwa parametry: ID osoby, dla której chcemy obliczyć poziom hierarchii, oraz tabelę z danymi wszystkich pracowników. Oto jak działa:
Wyszukujemy wiersz z danymi osoby o podanym ID
Sprawdzamy, kto jest osobą polecającą (ID osoby powyżej w hierarchii)
Jeśli nie ma osoby polecającej (wartość null), zwracamy 0 (najwyższy poziom hierarchii)
W przeciwnym razie wywołujemy tę samą funkcję dla osoby polecającej i dodajemy 1 do wyniku
Ta funkcja będzie się wywoływać rekurencyjnie, przesuwając się w górę hierarchii, aż dojdzie do osoby bez przełożonego (najwyższy szczebel), a następnie zacznie zwracać wyniki, dodając 1 na każdym poziomie zejścia w hierarchii.
Po utworzeniu funkcji musimy zmienić jej nazwę z domyślnej "Zapytanie 1" na bardziej opisową, np. "poziom_hierarchii", co pozwoli nam łatwiej się do niej odwoływać.
Zastosowanie funkcji do obliczenia poziomów hierarchii
Teraz, gdy mamy już zdefiniowaną funkcję, możemy ją zastosować do naszej tabeli z pracownikami, aby obliczyć poziom hierarchii dla każdego z nich. W tym celu:
Przechodzimy do zapytania z naszą tabelą pracowników (w przykładzie nazwane "lista osób") i dodajemy nową kolumnę, używając opcji "Wywołanie funkcji niestandardowej". Wybieramy naszą funkcję poziom_hierarchii i podajemy odpowiednie parametry:
Jako pierwszy parametr podajemy kolumnę ID z naszej tabeli
Jako drugi parametr chcemy podać całą tabelę, ale nie możemy jej bezpośrednio wybrać z listy
Tymczasowo wybieramy dowolną kolumnę i zatwierdzamy
Następnie edytujemy formułę w pasku formuł, zastępując odwołanie do kolumny odwołaniem do wcześniejszego kroku (czyli do całej tabeli)
Po zatwierdzeniu formuły, Power Query obliczy poziom hierarchii dla każdego pracownika w tabeli. Możemy zweryfikować poprawność wyników, sprawdzając kilka przykładów:
Jan (ID 1, bez osoby polecającej) — poziom 0
Anna (ID 2, polecona przez Jana) — poziom 1
Tomasz (ID 5, polecony przez Annę) — poziom 2
Paweł (polecony przez Tomasza) — poziom 3
Testowanie i weryfikacja funkcji
Po zaimplementowaniu naszej funkcji i zastosowaniu jej do tabeli pracowników, ważne jest, aby przetestować jej działanie i upewnić się, że zwraca prawidłowe wyniki. Możemy to zrobić, zmieniając dane w naszej tabeli źródłowej i sprawdzając, czy poziomy hierarchii zostaną odpowiednio zaktualizowane.
Na przykład, jeśli zmienimy osobę polecającą dla Pawła z ID 5 (Tomasz, poziom 2) na ID 8 (inny pracownik na poziomie 3), to poziom hierarchii Pawła powinien zmienić się na 4. Po zapisaniu zmian i odświeżeniu zapytania, możemy sprawdzić, czy nasz obliczony poziom hierarchii został prawidłowo zaktualizowany.
Takie dynamiczne obliczanie poziomów hierarchii jest szczególnie przydatne w organizacjach, gdzie struktura często się zmienia. Dzięki naszej funkcji rekurencyjnej w Power Query, nie musimy ręcznie aktualizować poziomów za każdym razem, gdy ktoś zmienia przełożonego lub gdy dochodzą nowi pracownicy.
Korzyści z automatycznego obliczania poziomów hierarchii
Automatyczne obliczanie poziomów hierarchii za pomocą Power Query przynosi wiele korzyści dla organizacji:
Oszczędność czasu — nie trzeba ręcznie liczyć poziomów hierarchii
Eliminacja błędów ludzkich — obliczenia są zawsze poprawne
Łatwa aktualizacja — wystarczy odświeżyć zapytanie po zmianie danych
Skalowalność — działa równie dobrze dla małych i dużych organizacji
Możliwość wykorzystania w dalszych analizach — np. do tworzenia wizualizacji struktury organizacyjnej
Znając poziom hierarchii każdego pracownika, możemy łatwo tworzyć raporty i analizy uwzględniające strukturę organizacyjną, co jest niezwykle przydatne dla działów HR i kadry zarządzającej.
Podsumowanie
W tym artykule pokazałem, jak wykorzystać Power Query i rekurencję do automatycznego obliczania poziomu hierarchii pracowników w organizacji. Dzięki stworzonej funkcji możemy szybko i bezbłędnie określić, na którym poziomie w strukturze organizacyjnej znajduje się każdy pracownik, co znacznie usprawnia zarządzanie i analizę danych kadrowych.
Ta technika jest szczególnie przydatna w większych organizacjach o złożonych strukturach hierarchicznych, gdzie ręczne określanie poziomów byłoby czasochłonne i podatne na błędy. Pamiętajmy, że Power Query oferuje wiele możliwości automatyzacji i optymalizacji pracy z danymi, a rekurencja jest jednym z potężnych narzędzi, które możemy wykorzystać do rozwiązywania złożonych problemów.
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.
