GWARANTOWANE TERMINY
Jesteś tutaj: strona głównastrefa wiedzySumowanie danych w Excel (dodatek Power Query)
MENU

Sumowanie danych w Excel (dodatek Power Query)


Kategoria: Kurs Excel, Samouczek Excel
26Sep

Dodatek Power Query daje możliwość wyszukiwania źródeł danych, tworzenia połączeń oraz kształtowania uzyskanych danych (np. usuwanie kolumn, zmienianie typów danych lub scalanie tabel) odpowiednio do potrzeb. Po zakończeniu kształtowania danych możesz udostępnić uzyskane wyniki lub użyć swojego zapytania do tworzenia raportów. Poniżej przedstawiono praktyczne zastosowanie Power Query w celu obliczenia wartości ze sprzedaży danych produktów.

Plik do pobrania, aby wykonać poniższe ćwiczenie:

Nota korygująca w Excelu pobierz

Cel

Jaką kwotę uzyskano ze sprzedaży według poszczególnych produktów?

Rozwiązanie

W tym zbiorze danych można poradzić sobie bez problemu z wykorzystaniem funkcji WYSZUKAJ.PIONOWO, jednak kiedy importujemy dane z liczbą wierszy powyżej liczby wierszy arkusza programu MS Excel, to zadanie można wykonać dzieląc w odpowiedni sposób bazę danych lub… wykorzystując dodatek Power Query. Tylko czy Power Query jest na pewno dodatkiem?

W Excelu 2013 – Power Query jest jak najbardziej dodatkiem, który należało włączyć z poziomu dodatków COM. W tym artykule wykorzystano Excela 2016, czyli najstarszą wersję, w której Power Query stał się integralną częścią Excela. W różnych wersjach Excela inaczej korzysta się z tej funkcjonalności (w artykule przyjęto, że jest to dodatek). Oczywiście opcja jest dostępna na karcie Dane z wyłączeniem Excela 2013, gdzie Power Query istnieje jako osobna karta.

Poniżej przedstawiono uruchomienie edytora dodatku Power Query dla wersji programu MS Excel, gdzie dodatek stanowi jego część (Rys. 1). Dalsze funkcjonalności będą przedstawiane dla aplikacji Excel Professional Plus 2016.

Rys. 1. Uruchomienie edytora dodatku Power Query

 

Pierwszym etapem jest zaimportowanie danych. Przejdź do karty Dane > Nowe zapytanie > Z pliku > Ze skoroszytu (Rys. 2).

Rys. 2. Import danych z Excela

Po wskazaniu plików w oknie Nawigatora zaznacz opcję Wybierz wiele elementów oraz zaznacz odpowiednie arkusze (Rys. 3).

Rys. 3. Wskazanie arkuszy do importu

Po kliknięciu Załaduj po prawej stronie Excela zostanie wyświetlone okno Zapytania dotyczące skoroszytów. Kliknij prawym przyciskiem myszy na tabelę np. Produkty i wybierz polecenie Edytuj (Rys. 4).

 

Rys. 4. Przejście do Power Query

Zostanie wyświetlony edytor Power Query. Kliknij na opcję Zapytania (Rys. 5).

 

Rys. 5. Edytor Power Query

W tym miejscu można zobaczyć jakie tabele zostały zaimportowane (Rys. 6).

Rys. 6. Rozwinięta lista z tabelami

Na początku należy odpowiedzieć sobie na pytanie w jaki sposób połączyć tabele. W tym przypadku będzie to pole Produkt ID z tabeli Produkty oraz pole Produkt ID z tabeli Sprzedaz. Proszę zauważyć, że nazwa tabel jest identyczna jak nazwa arkusza, z którego pobrane są dane w przypadku, gdy pobieramy dane z Excela ze zwykłego zakresu danych. Przyszedł czas na połączenie tabel. W tym celu z karty Strona główna z grupy Połącz rozwiń opcję Scal zapytania przy pomocy trójkąta i wybierz polecenie Scal zapytanie jako nowe (Rys. 7).

Rys. 7. Polecenie łączenia tabel jako nowa tabela

Po wybraniu tej opcji powinno pojawić się okno Scalanie. W związku z tym, że tabela Produkty była przed wybraniem opcji zaznaczona to w oknie Scalanie jest ona udostępniona na pierwszej liście rozwijanej (Rys. 8).

Rys. 8. Wybrana tabela Produkty

Wybierz z drugiej listy tabelę Sprzedaz. Zauważ, że przycisk OK jest nieaktywny (Rys. 9).

 Rys. 9. Wybranie z listy tabeli Sprzedaz

Kolejnym krokiem jest kliknięcie na pole Produkt ID z tabeli Produkty oraz Sprzedaz. Zauważ, że po zaznaczeniu pól przycisk OK jest aktywny (Rys. 10).

Rys. 10. Zaznaczenie pól, które będą łączone

Celem jest połączenie danych w jedną tabelę, której pole Produkt ID jest określone w obydwu tabelach, dlatego z listy Rodzaj sprzężenia wybierz Wewnętrznie (tylko pasujące wiersze) oraz kliknij przycisk OK (Rys. 11).

Rys. 11. Końcowe ustawienia scalania

Po zaakceptowaniu scalania na liście tabel pojawiła się nowa pozycja (Rys. 12).

Rys. 12. Nowa tabela

Kliknij na nią dwukrotnie lub kliknij prawym przyciskiem myszy na tą tabelę i wybierz opcję Zmień nazwę (Rys. 13).

Rys. 13. Zmiana nazwy tabeli

Wstaw słowo Dane oraz zaakceptuj klawiszem Enter (Rys. 14).

Rys. 14. Zmieniona nazwa tabeli

Po scaleniu danych tabela Dane ma obiekt Table (Rys. 15).

Rys. 15. Obiekt Table w tabeli Dane

W tym obiekcie znajdują się dane z tabeli Sprzedaz. Aby je odkryć należy kliknąć na dwie charakterystyczne strzałki obok nazwy Sprzedaz (Rys. 16).

 Rys. 16. Miejsce rozwinięcia danych

Gdy przycisk zostanie kliknięty to pojawią się pola, które potencjalnie chcemy pobrać. Kliknij przycisk OK, pomimo faktu, że nie potrzebujemy pola Produkt ID (Rys. 17).

 

Rys. 17. Kolumny do importu

Teraz brakuje kolumny z wartością sprzedanych produktów. W celu dodania nowej kolumny przejdź do karty Dodaj kolumnę i wybierz opcję Kolumna niestandardowa (Rys. 18).

Rys. 18. Wstawienie nowej kolumny niestandardowej

W oknie Kolumna niestandardowa wstaw iloczyn ceny [Sprzedaz.Cena] oraz ilości [Sprzedaz.Ilość] (Rys. 19).

Rys. 19. Formuła kolumny niestandardowej

Zmień typ danych nowej kolumny na liczbowy. Kliknij na ikonę ABC 123 znajdującą się po lewej stronie od nazwy Niestandardowe i wybierz Liczba dziesiętna (Rys. 20).

Rys. 20. Zmiana typu danych

Po przygotowaniu danych do raportu przyszedł czas na zgrupowanie danych. Z karty Strona główna wybierz Grupowanie według (Rys. 21).

Rys. 21. Opcja grupowania danych

Z okna Grupowanie według wybierz pole Nazwa produktu z pierwszej listy rozwijalnej. Do nazwy kolumny wprowadź tekst Wartość, bo tak będzie nazywać się nowa kolumna która powstanie na skutek sumowania (czyli z listy rozwijalnej Operacja wybierz Suma) wartości w polu o nazwie Niestandardowe, dlatego to pole wybierz z listy rozwijalnej Kolumna (Rys. 22).

Rys. 22. Ustawienia grupowania

Po zaakceptowaniu przyciskiem OK mamy rozwiązanie zawierające nieuporządkowane produkty (Rys. 23).

Rys. 23. Wygląd po grupowaniu danych

Zaznacz kolumnę Nazwa produktu i wybierz opcję sortowania z karty Strona główna (Rys. 24).

  

Rys. 24. Sortowanie listy wg nazwy produktu

Po wybraniu opcji Zamknij i załaduj (która jest na Stronie głównej) zostanie utworzony dodatkowy arkusz z tabelą Dane. Można usunąć arkusze Produkty oraz Sprzedaz, a arkuszowi o nazwie Arkusz1 nadać nazwę Dane (Rys. 25).

Rys. 25. Końcowy wygląd rozwiązania biznesowego

Rozwiązanie jest zawarte w poniższym pliku:

Nota korygująca w Excelu pobierz

Autor: Grzegorz Plak – trener Cognity

Power Query jest funkcjonalnym narzędziem – służy do łączenia danych, daje możliwość odnajdowania, łączenia i uściślania źródeł danych, potrzebnych do sporządzenia analiz. Funkcje technologii Power Query można używać w programach Power BI Desktop oraz Microsoft Excel.

Jeśli chcesz poznać możliwości oraz skuteczne rozwiązania jakie daje praca z tym dodatkiem MS Excel to zapraszamy na nasze szkolenia:

 

Wybrani Klienci:

Ten serwis używa plików cookies. Brak zmiany ustawień przeglądarki oznacza zgodę na ich używanie.
Więcej informacji o plikach cookies znajdziesz tutaj
Kliknij ZGADZAM SIĘ, aby ta informacja nie wyświetlała się ponownie
Cognity.pl