fbpx
Minuty czytania: 3

POWER QUERY: Zamień kolumny na wiersze

Po co zamieniać kolumny na wiersze? Najczęściej po to, żeby przygotować dane do tabeli przestawnej. Z taką sytuacją masz do czynienia, kiedy masz tabelę z danymi w kolejnych kolumnach. Natomiast w tabeli przestawnej najlepiej mieć dane w postaci bazodanowej.

Zanim pojawił się POWER QUERY, trzeba było kombinować z różnymi, najczęściej czasochłonnymi metodami (np. z wykorzystaniem funkcji TRANSPONUJ) Teraz jest na to znacznie prostszy sposób i co ważne – pozwalający na automatyczną aktualizację. Zainteresowany?

Krajobraz ze słowem z chmur "Change"

Przeczytaj również:

Kolumny na wiersze: w czym problem?

Wyobraź sobie, że masz stworzyć budżet wynagrodzeń dla kilku działów. Najwygodniej będzie działać, dodając wartości w kolejnych kolumnach reprezentujących miesiące. Szczególnie, jeżeli będziesz używał formuł obliczających potrzebne wartości.

Zamiana kolumn na wiersze - dane podstawowe

Zatem w czym problem?

Po stworzeniu budżetu i niezbędnych akceptacjach, trzeba będzie przenieść dane do bazy i raportów. Dzięki temu będziesz w stanie porównywać plan z wykonaniem. Najczęściej i najłatwiej robi się to za pomocą tabeli przestawnej. A powyższy format nie jest najlepszy dla tabel. Dużo lepiej sprawdza się układ bazodanowy, tak jak poniżej.

Kolumny na wiersze - układ bazodanowy

Kolumny na wiersze, czyli zrób UNPIVOT

Doprowadzenie do takiego układu danych, szczególnie gdy masz setki czy tysiące wierszy wymagało sporo zachodu. Wymagało, bo na szczęście mamy POWER QUERY, aka GET & TRANSFORM (od wersji 2016)

Poniżej znajdziesz kolejne kroki jakie powinieneś zrealizować. W nawiasie zaznaczyłem, jeżeli krok jest opcjonalny i wynika z tego jak wyglądają moje dane. Oczywiście możesz pobrać plik startowy i przećwiczyć kolejne etapy.

Potrzebny czas: 3 min..

Jak zamienić kolumny na wiersze w POWER PIVOT

  1. Zamień dane do postaci tabeli.

    Kliknij w dowolnym miejscu z danymi i przekształć je do postaci tabeli. NARZĘDZIA GŁÓWNE > FORMATUJ JAKO TABELĘ lub skrót CTRL + L

  2. Dodaj tabelę do POWER QUERY.

    Przejdź na kartę POWER QUERY i wybierz polecenie Z TABELI / ZAKRESU. W nowszych wersjach odpowiednie polecenie znajdziesz na karcie DANE, w sekcji POBIERANIE I PRZEKSZTAŁCANIE DANYCH.

  3. Usuń niepotrzebne kolumny (opcjonalnie).

    Usuń ostatnią kolumnę RAZEM. Podsumowanie nie będzie potrzebne. Kliknij prawym przyciskiem myszy i wybierz USUŃ.

  4. Zamień „null” na zera (opcjonalnie).

    Zamieniając wartości null na 0, spowodujesz że dane dla tych kategorii pojawią się w docelowym układzie danych. W przeciwnym razie zostaną pominięte.
    Zaznacz kolumny z miesiącami. Przejdź na wstążkę PRZEKSZTAŁĆ, wybierz ZAMIENIANIE WARTOŚCI i w pierwszym okienku wpisz null, a w drugim 0.

  5. Anuluj przestawianie innych kolumn.

    Teraz zasadnicza część całej operacji. Zaznacz kolumny „Dział” i „Grupa Kosztów”. Te kolumny pozostaną niezmienione. Kliknij prawym przyciskiem myszy i z menu kontekstowego wybierz polecenie ANULUJ PRZESTAWIANIE INNYCH KOLUMN.

  6. Zmień nazwę kolumn (opcjonalnie).

    Kliknij dwukrotnie w nagłówek kolumny „Atrybut” i zmień nazwę na „Miesiąc”

  7. Zapisz i załaduj do arkusza.

    Przejdź na wstążkę NARZĘDZIA GŁÓWNE i wybierz ZAMKNIJ I ZAŁADUJ. Wybierając miejsce w nowym arkuszu, Twoje dane zostaną załadowane w przekształconej formie do wskazanego miejsca.

Na filmie poniżej zobaczysz wszystkie opisane kroki. Dodatkowo przekształcam dane z kolumny „Grupa kosztów”, aby pozbyć się niepotrzebnych liczb z opisów kosztów.

Subskrybenci newslettera, otrzymują gotowe rozwiązania z omawianymi w artykule poradami. Jeżeli chciałbyś skorzystać z nich w przyszłości – poniżej znajdziesz formularz zapisu.


Chcesz lepiej poznać Excela? Zapisz się na Excelness News


Podsumowanie

Zamiana kolumn na wiersze, dzięki POWER QUERY jest naprawdę prosta. Przekształcanie danych do takiej postaci jest wręcz koniecznością, jeżeli chcesz efektywnie pracować na tabelach przestawnych.

Jest jeszcze jedna zaleta opisanego mechanizmu. Jeśli dodasz kolejne dane w tabeli źródłowej, wystarczy że później klikniesz ODŚWIEŻ, a przekształcona tabela docelowa zostanie automatycznie zaktualizowana.


Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.


0 Komentarzy

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *