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).
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).
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).
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).
Pojawi się komunikat, w którym musimy zatwierdzić zmianę typu kolumny za pomocą przycisku Zamień bieżącą (rys. nr 5).
W kolejnym etapie wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 6).
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).
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).
W Excelu w bocznym panelu stworzyło się zapytanie Power Query, tylko w formie połączenia (rys. nr 9).
Na tym etapie musimy ręcznie wstawić sobie tabelę przestawną. Klikamy polecenie Tabela przestawna z karty Wstawianie (rys. nr 10).
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).
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).
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).
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).
Otrzymamy tabelę przestawną przedstawioną na rysunku nr 15.
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).
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
