0
0 Produkty w koszyku

No products in the cart.

W dzisiejszym poście nauczymy się jak załadować ponad milion wierszy do tabeli przestawnej, czyli ponad 1048576 wierszy, które mieszczą się w arkuszu Excela. Będziemy używać do tego zadania dodatku do Excela – Power Query.

Dla ułatwienia dane pobierzemy sobie z pliku tekstowego, czyli wybieramy polecenie Z pliku tekstowego /CSV z karty Dane (rys. nr 1).

Pobieranie danych z pliku tekstowego/ CSV
Pobieranie danych z pliku tekstowego/ CSV

Otworzy nam się okno, w którym musimy znaleźć lokalizację pliku (danych bazowych), jaki chcemy zaimportować do Power Query. Wybieramy plik a następnie nasz wybór zatwierdzamy przyciskiem Importuj (rys. nr 2).

rys. nr 2 — Wybór źródła danych

Otworzy nam się okno z danymi Sprzedaż zagranicznak.csv z danymi, które będziemy chcieli edytować w Power Query, więc klikamy przycisk Edytuj (rys. nr 3).

rys. nr 3 — Edycja pliku CSV

Otworzy nam się Edytor zapytań Power Query, w którym chcemy sprawdzić czy wszystkie dane zostały załadowane poprawnie. Kolumna Kod pocztowy jest błędna, ponieważ Power Query źle rozpoznaje dane (np. dla kodu z wartością 0 z przodu Power Query pomija tą wartość 0 i otrzymujemy kod 4- cyfrowy). Wystarczy zmienić formatowanie tej kolumny. Klikamy na ikonkę 123 przy nazwie kolumny Kod pocztowy, a następnie z podręcznego menu wybrać formatowanie Tekst (rys. nr 4).

rys. nr 4 — Zmiana formatu danych

Pojawi się komunikat, w którym musimy zatwierdzić zmianę typu kolumny za pomocą przycisku Zamień bieżącą (rys. nr 5).

rys. nr 5 — Zmień typ kolumny

W kolejnym etapie wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 6).

rys. nr 6 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych. Jeśli posiadasz subskrypcję Excela 365 w tym okienku znajduje się bezpośrednia opcja, aby załadować dane jako Raport w formie tabeli przestawnej (rys. nr 7).

rys. nr 7 — Parametry importowania danych

Jeśli posiadasz starszą wersję pakietu Office to wybieramy polecenie Utwórz tylko połączenie, następnie nasz wybór zatwierdzamy przyciskiem OK (rys. nr 8).

rys. nr 8 — Utwórz tylko połączenie

W Excelu w bocznym panelu stworzyło się zapytanie Power Query, tylko w formie połączenia (rys. nr 9).

rys. nr 9 — Zapytanie Power Query w panelu bocznym

Na tym etapie musimy ręcznie wstawić sobie tabelę przestawną. Klikamy polecenie Tabela przestawna z karty Wstawianie (rys. nr 10).

rys. nr 10 — Tabela przestawna

Otworzy nam się okno Tworzenia tabeli przestawnej. W polu wybierz dane, które chcesz analizować, zaznaczamy opcję Użyj zewnętrznego źródła danych, następnie klikamy przycisk Wybierz połączenie (rys. nr 11).

rys. nr 11 — Okno tworzenia tabeli przestawnej

Otworzy nam się okno z Istniejącymi połączeniami. Zaznaczamy połączenie w tym arkuszu Zapytanie – Sprzedaż Zagranicznak, a następnie klikamy przycisk Otwórz (rys. nr 12).

rys. nr 12 — Istniejące połączenia

Następnie w oknie Tworzenie tabeli przestawnej z rysunku nr 11, wybieramy miejsce wstawienia naszej tabeli (Istniejący arkusz i konkretna komórka $A$1) i tak ustawione parametry zatwierdzamy przyciskiem OK.

Mamy dużą ilość danych, więc musimy trochę poczekać, ponadto Excel robi sobie kopię zapytania, co dodatkowo wydłuża proces przetwarzania danych.

Otrzymamy w Excelu pustą tabelę, przestawną którą stworzymy przeciągając odpowiednie dane do pól etykiet wierszy i kolumn (rys. nr 13).

rys. nr 13 — Pusta tabela przestawna

Przeciągamy pola Miasto i Kraj do obszaru etykiet wierszy, a następnie przeciągamy pole Przychód do obszaru Sumy wartości (rys. nr 14).

rys. nr 14 — Przeciąganie danych do obszaru etykiet 

Otrzymamy tabelę przestawną przedstawioną na rysunku nr 15.

rys. nr 15 — Tabla przestawna

Możemy sobie podejrzeć ilość załadowanych wierszy w panelu Zapytania i połączenia. W tym arkuszu zostało stworzonych 2 501 580 wierszy (rys. nr 16).

rys. nr 16 — ilość wierszy

Klikamy skrót klawiszowy Ctrl+S, aby zapisać nasze dane. Skopiowane zapytanie Sprzedaż zagraniczna (2) zostało stworzone tylko pod tą tabelę przestawną. Połączenie źródłowe możemy dalej wykorzystywać do innych zadań.

Ciekawostką jest, że plik w Excelu dotyczący tego odcinka zajmuje niecałe 30 Mb, w porównaniu do 130 Mb danych w pliku źródłowym csv.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama