W tym artykule przedstawię najważniejsze opcje drukowania w Excelu, które pozwolą Ci na profesjonalne i efektywne przygotowanie dokumentów do wydruku. Poznanie tych funkcji umożliwi Ci dostosowanie wielkości, orientacji i zawartości arkuszy zgodnie z Twoimi potrzebami, co jest kluczowe dla przedstawiania danych w czytelnej formie.
Właściwe wykorzystanie opcji drukowania pozwala zaoszczędzić czas i materiały eksploatacyjne, a także zaprezentować dane w sposób przejrzysty i profesjonalny.
W moim wideo dokładnie omawiam wszystkie istotne ustawienia związane z drukowaniem w Excelu:
Podstawowe opcje drukowania w Excelu
Rozpoczynając pracę z opcjami drukowania, warto przejść na kartę Układ strony, która zawiera najważniejsze narzędzia do konfiguracji wydruku. W tej sekcji znajdują się opcje takie jak marginesy, orientacja, rozmiar oraz motywy, które wpływają na wygląd i kolorystykę drukowanych dokumentów. Każda z tych opcji ma istotny wpływ na to, jak finalnie będzie wyglądał wydrukowany arkusz.
Aby uzyskać dostęp do wszystkich ustawień drukowania, możemy nacisnąć kombinację klawiszy CTRL+P lub wybrać opcję "Drukuj" z menu głównego. Od wersji Excel 2010 interfejs drukowania prezentuje się w formie podzielonego ekranu — po prawej stronie widzimy podgląd wydruku, a po lewej najważniejsze opcje konfiguracyjne. Ta intuicyjna organizacja pozwala na szybkie dostosowanie parametrów i natychmiastowy podgląd rezultatów.
Pierwszą ważną decyzją jest określenie zakresu drukowania. Excel oferuje nam kilka opcji:
Oprócz standardowych ustawień drukowania, Excel oferuje szereg specjalistycznych opcji dostosowanych do specyfiki arkuszy kalkulacyjnych. Te funkcje pozwalają na precyzyjne dostosowanie wyglądu i zawartości drukowanych dokumentów.
Dopasowanie rozmiaru wydruku
Jedną z najczęściej używanych opcji w Excelu jest możliwość dopasowania rozmiaru wydruku. Domyślnie program proponuje rozmiar rzeczywisty, czyli taki, jaki według algorytmów Microsoftu będzie najlepiej prezentował się na kartce. Jednak przy dużej ilości danych może to powodować, że część informacji nie zmieści się na jednej stronie.
Excel oferuje kilka opcji dopasowania, które możemy wybrać:
Rozmiar rzeczywisty (domyślny)
Dopasowanie do jednej strony (zarówno szerokość jak i wysokość)
Dopasowanie wszystkich kolumn do jednej strony (tylko szerokość)
Dopasowanie wszystkich wierszy do jednej strony (tylko wysokość)
W praktyce szczególnie przydatna jest opcja dopasowania wszystkich kolumn do jednej strony. Dzięki temu wszystkie kolumny danych będą widoczne na jednym wydruku, co znacząco poprawia czytelność i eliminuje potrzebę sklejania wielu stron. Na przykład, gdy mamy tabele z wieloma kolumnami (jak w przykładzie: id sprzedaży, ilość, cena, wartość), ta opcja pozwala zobaczyć wszystkie te dane obok siebie.
Szczegółowe ustawienia strony
Aby uzyskać dostęp do bardziej zaawansowanych opcji, możemy skorzystać z ustawień strony dostępnych zarówno z poziomu ekranu drukowania, jak i z karty Układ strony. W oknie tym znajdziemy kilka zakładek z różnymi grupami ustawień.
Pierwsza zakładka "Strona" zawiera podstawowe opcje takie jak orientacja (pionowa lub pozioma) oraz opcje dopasowania. Szczególnie istotne jest pole "Dopasuj do", które pozwala określić ile stron ma zająć nasz wydruk w pionie i poziomie. Przykładowo, ustawiając wartość 1 w polu poziomym (H — horizontal) i pozostawiając puste pole pionowe (V — vertical), dopasujemy szerokość arkusza do jednej strony, a wysokość będzie zajmować tyle stron, ile będzie potrzebne do wydrukowania wszystkich danych.
W zakładce Marginesy możemy precyzyjnie określić wielkość marginesów górnego, dolnego, lewego i prawego. Dodatkowo mamy możliwość ustawienia wyśrodkowania arkusza zarówno w poziomie, jak i w pionie. Z mojego doświadczenia wynika, że wyśrodkowanie w poziomie jest znacznie częściej używane i poprawia estetykę wydruku.
Nagłówki i stopki
Excel umożliwia dodawanie nagłówków i stopek do drukowanych dokumentów, choć szczegółowe omówienie tej funkcji jest tematem na osobne wideo. Warto jednak pamiętać, że w ustawieniach strony możemy określić ich rozmiary i położenie.
Powtarzanie wierszy i kolumn
Jedną z najbardziej przydatnych funkcji podczas drukowania wielostronicowych arkuszy jest możliwość powtarzania wybranych wierszy lub kolumn na każdej stronie. Ta opcja jest nieoceniona, gdy mamy długie tabele danych, które zajmują kilka stron wydruku.
Aby skonfigurować powtarzanie wierszy, należy przejść do zakładki Arkusz w ustawieniach strony. Tam możemy wskazać, które wiersze mają być powtarzane na każdej stronie wydruku. Najczęściej wybiera się wiersze zawierające nagłówki kolumn, co znacząco poprawia czytelność wielostronicowych wydruków.
W przykładzie z wideo powtarzam pierwsze pięć wierszy arkusza, które zawierają nagłówki danych. Dzięki temu, nawet jeśli tabela zajmuje kilkanaście stron, na każdej z nich widoczne będą opisy kolumn, co ułatwia interpretację danych.
Drukowanie linii siatki i komentarzy
W ustawieniach drukowania możemy również określić, czy chcemy drukować linie siatki arkusza. W większości przypadków linie te nie są drukowane, gdyż mogą zaciemniać obraz danych, szczególnie gdy używamy obramowania komórek. Jednak w niektórych sytuacjach, gdy chcemy zachować dokładny wygląd arkusza, ta opcja może być przydatna.
Excel umożliwia także określenie sposobu drukowania komentarzy i notatek. W nowszych wersjach programu to, co wcześniej nazywano komentarzami, teraz funkcjonuje jako notatki. Możemy wybrać, czy mają być one drukowane bezpośrednio na arkuszu, na końcu dokumentu, czy w ogóle nie powinny być uwzględniane w wydruku.
Kolejność drukowania stron
Ostatnim ważnym elementem konfiguracji drukowania jest określenie kolejności drukowania stron w przypadku dokumentów wielostronicowych. Możemy wybrać, czy strony mają być drukowane najpierw w dół, a potem w prawo (opcja domyślna), czy najpierw w prawo, a potem w dół. Wybór odpowiedniej opcji zależy od charakteru danych i preferencji użytkownika.
Po skonfigurowaniu wszystkich ustawień możemy wrócić do podglądu wydruku, aby upewnić się, że dokument wygląda zgodnie z naszymi oczekiwaniami. Jeśli wszystko jest w porządku, możemy przystąpić do drukowania, klikając przycisk "Drukuj" lub naciskając kombinację klawiszy CTRL+P, a następnie potwierdzając operację.
Dzięki zastosowaniu odpowiednich ustawień drukowania, takich jak powtarzanie nagłówków czy dopasowanie szerokości kolumn, możemy uzyskać profesjonalne i czytelne wydruki, które będą skutecznie prezentować nasze dane. Właściwe wykorzystanie tych opcji pozwala zaoszczędzić czas i materiały eksploatacyjne, a także zapewnia estetyczny wygląd drukowanych dokumentów.
Funkcja SEKWENCJA w Excelu pozwala na efektywne tworzenie ciągów liczbowych i dat bez konieczności ręcznego wprowadzania każdej wartości. Dzięki tej funkcji możemy generować sekwencje wartości o określonym kroku, liczbie wierszy i kolumn, co znacząco przyspiesza pracę przy tworzeniu rozbudowanych arkuszy i wykresów.
W tym artykule przedstawiam dokładne objaśnienie działania funkcji SEKWENCJA wraz z praktycznymi przykładami zastosowań.
Czym jest funkcja SEKWENCJA w Excelu?
Funkcja SEKWENCJA to jedna z nowszych funkcji wprowadzonych przez Microsoft do Excela, która umożliwia generowanie sekwencji liczbowych w postaci tablicy. Jest to niezwykle użyteczne narzędzie, które pozwala na szybkie tworzenie ciągów wartości bez konieczności ręcznego wprowadzania każdej liczby. Funkcja ta jest szczególnie pomocna przy tworzeniu wykresów, tabel danych czy przy wykonywaniu obliczeń wymagających regularnych odstępów między wartościami.
Składnia funkcji SEKWENCJA jest prosta i intuicyjna. Wymaga ona podania kilku parametrów, które determinują, jak będzie wyglądać wygenerowana sekwencja. Podstawowe parametry to:
Liczba wierszy — określa, ile wierszy będzie zawierać wygenerowana tablica
Liczba kolumn — określa, ile kolumn będzie zawierać wygenerowana tablica
Wartość początkowa — liczba, od której rozpocznie się sekwencja (domyślnie 1)
Krok — różnica między kolejnymi wartościami w sekwencji (domyślnie 1)
Proste przykłady użycia funkcji SEKWENCJA
Zacznijmy od najprostszego przykładu użycia funkcji SEKWENCJA. Gdy wpiszemy formułę =SEKWENCJA(5), Excel wygeneruje pięciowierszową sekwencję liczb, zaczynając od 1 i zwiększając wartość o 1 w każdym kolejnym wierszu. Otrzymamy więc wartości: 1, 2, 3, 4, 5.
Możemy jednak budować znacznie bardziej złożone sekwencje, definiując wszystkie parametry funkcji. Na przykład, formuła =SEKWENCJA(4;3;-2;-,5) wygeneruje tablicę składającą się z 4 wierszy i 3 kolumn, rozpoczynającą się od wartości ‑2 i zmniejszającą się o 0,5 przy każdej kolejnej komórce. Warto zauważyć, że Excel pozwala na pominięcie zera przed przecinkiem przy liczbach dziesiętnych, więc zamiast ‑0,5 możemy napisać -,5.
Ta funkcja staje się niezwykle użyteczna, gdy potrzebujemy utworzyć sekwencję z małym krokiem, na przykład do generowania danych do wykresów. Przykładowo, formuła =SEKWENCJA(31;1;-3;,1) utworzy ciąg 31 liczb, zaczynający od ‑3 i zwiększający się o 0,1 z każdą kolejną wartością. Taka sekwencja może służyć jako oś x dla wykresu przedstawiającego funkcję matematyczną.
Problem dokładności liczb zmiennoprzecinkowych
Podczas pracy z funkcją SEKWENCJA należy pamiętać o pewnym ważnym aspekcie związanym z reprezentacją liczb zmiennoprzecinkowych w komputerach. Ze względu na sposób, w jaki komputery przechowują liczby dziesiętne (w systemie binarnym), mogą pojawić się drobne niedokładności w odległych miejscach po przecinku.
Ten problem można zaobserwować, gdy tworzymy sekwencję przechodzącą przez zero z niewielkim krokiem. Na przykład, gdy generujemy sekwencję od ‑3 do 3 z krokiem 0,1, wartość, która powinna być dokładnie równa 0, może być reprezentowana jako bardzo mała liczba, np. ‑1,5e-15 (co oznacza ‑1,5 × 10^(-15)).
Takie drobne różnice mogą mieć wpływ na wykresy, ponieważ Excel traktuje te wartości jako różne od zera. Aby rozwiązać ten problem, możemy zastosować funkcję zaokrąglającą do całej sekwencji. Na przykład, formuła =ZAOKR(SEKWENCJA(31;1;-3;,1);1) zaokrągli wszystkie wartości w sekwencji do jednego miejsca po przecinku, eliminując niepożądane niedokładności.
Wykorzystanie funkcji SEKWENCJA do tworzenia wykresów
Funkcja SEKWENCJA jest niezwykle przydatna przy tworzeniu wykresów, zwłaszcza tych przedstawiających funkcje matematyczne lub rozkłady statystyczne. Gdy mamy już utworzoną sekwencję liczb (po zaokrągleniu, jeśli to konieczne), możemy wykorzystać ją jako dane wejściowe do innych funkcji.
Na przykład, aby utworzyć wykres rozkładu normalnego, możemy użyć funkcji ROZK.NORMALNY w połączeniu z funkcją SEKWENCJA. Formuła =ROZK.NORMALNY(SEKWENCJA(31;1;-3;,1);;1;FAŁSZ) oblicza wartości funkcji gęstości prawdopodobieństwa rozkładu normalnego dla każdej wartości w naszej sekwencji.
Następnie, wybierając obie sekwencje (wartości x i odpowiadające im wartości y) i tworząc wykres punktowy, otrzymujemy elegancką wizualizację rozkładu normalnego. Jest to znacznie bardziej efektywne niż ręczne wprowadzanie wszystkich tych wartości.
Sekwencje dat za pomocą funkcji SEKWENCJA
Funkcja SEKWENCJA może być również użyta do generowania sekwencji dat, co jest niezwykle przydatne przy tworzeniu harmonogramów, raportów miesięcznych czy rocznych. Możemy to osiągnąć, łącząc funkcję SEKWENCJA z funkcją DATA.
Na przykład, aby utworzyć sekwencję dat, które wszystkie przypadają na 15. dzień każdego miesiąca w roku 2023, możemy użyć formuły =DATA(2023;SEKWENCJA(12);15). Ta formuła generuje 12 dat, od 15 stycznia 2023 do 15 grudnia 2023. Jest to znacznie szybsze niż ręczne wprowadzanie każdej daty osobno.
Możemy również tworzyć bardziej złożone sekwencje dat, manipulując parametrami funkcji SEKWENCJA. Na przykład, aby utworzyć sekwencję dat w odstępach tygodniowych, moglibyśmy użyć funkcji SEKWENCJA do generowania liczb, które następnie posłużyłyby jako offset dni od daty początkowej.
Inne praktyczne zastosowania funkcji SEKWENCJA
Funkcja SEKWENCJA ma wiele innych praktycznych zastosowań w codziennej pracy z Excelem. Może być wykorzystana do:
Tworzenia numerowanych list lub indeksów
Generowania tablic do obliczeń macierzowych
Tworzenia sekwencji wartości jako podstawy do funkcji warunkowych
Generowania wartości do testowania formularzy czy makr
Szczególnie przydatne jest połączenie funkcji SEKWENCJA z innymi funkcjami tablicowymi, co pozwala na wykonywanie złożonych operacji na całych zakresach danych jednocześnie, bez konieczności tworzenia pośrednich kolumn lub wierszy.
Porady dotyczące efektywnego korzystania z funkcji SEKWENCJA
Aby w pełni wykorzystać możliwości funkcji SEKWENCJA, warto pamiętać o kilku praktycznych poradach:
Zawsze sprawdzaj, czy nie potrzebujesz zaokrąglić wartości, zwłaszcza gdy pracujesz z małymi krokami
Pamiętaj, że funkcja SEKWENCJA zawsze wypełnia komórki w kolejności od lewej do prawej, od góry do dołu
Jeśli potrzebujesz tylko części sekwencji, możesz użyć funkcji WYBIERZ.PRZEDZIAŁ, aby wyodrębnić potrzebne wartości
Dla bardzo dużych sekwencji uważaj na wydajność — Excel ma ograniczenia co do rozmiaru tablic
Funkcja SEKWENCJA to potężne narzędzie, które znacznie upraszcza wiele zadań w Excelu. Dzięki jej zrozumieniu i umiejętnemu stosowaniu możesz zaoszczędzić dużo czasu i zwiększyć swoją produktywność podczas pracy z arkuszami kalkulacyjnymi.
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.
