Excel na pomoc - Power Query część 1

OPIS WARSZTATU

Power Query to narzędzie, które odmieni Twoją codzienną pracę. Pozwala łączyć dane z wielu plików Excela, txt, csv, pdf, z Internetu, wprost z programu finansowo-księgowego, na którym pracujesz. Ułatwia przekształcanie danych tak, żeby nie trzeba było robić pracy „ręcznie” i można było szybko zrobić gotowe raporty. Najfajniejsze jest to, że gdy w plikach, z których pobierasz dane coś się zmieni – zaktualizujesz je, dodasz nowe dane, nowy okres, nowe konto – to po odświeżeniu Twoje dane w bazie danych lub raporcie zostaną automatycznie zaktualizowane.

Dla kogo: 

Szkolenie jest kierowane do księgowych, finansistów, kontrolerów, którzy do swojej pracy muszą łączyć dane z wielu plików, programów, źródeł i po odpowiedniej obróbce przygotowywać z nich raporty, analizy i zestawienia.

Korzyści ze szkolenia:

• Skrócisz czas łączenia danych
• Uprościsz obróbkę i przygotowanie danych
• Zautomatyzujesz masę pracy, którą teraz wykonujesz ręcznie
• Będziesz budować narzędzia do wielokrotnego wykorzystania
• Wyeliminujesz błędy, które pojawiają się często przez pośpiech i ręczną robotę

Do szkolenia wymagane jest posiadanie Excela w wersji 2016 lub nowszej albo Excela 2010 lub 2013 z doinstalowanym dodatkiem Power Query. Można go pobrać bezpłatnie ze strony Microsoft.

Na tym szkoleniu bardzo ułatwia pracę korzystanie z dwóch monitorów. Na jednym obserwujesz, co pokazuje trener, na drugim wykonujesz swoje zadanie.

PROGRAM

1. ZAPISY Z KONT PRZYCHODÓW ZE SPRZEDAŻY I ZESTAWIENIA FAKTUR - Jak połączyć dane z wielu zupełnie różnych plików, z jednym arkuszem o takich samych kolumnach.
   1. Omówimy bazy danych, z których będziemy łączyli dane do jednego pliku.
   2. Pobieramy dane z pliku z danymi z konta sprzedaży i dokładamy oznaczenie, skąd pochodzą dane.
   3. Pobieramy dane z pliku z danymi z zestawienia faktur i dokładamy oznaczenie, skąd pochodzą dane.
   4. Ujednolicamy nazwy kolumn z obu zapytań – bardzo ważna czynności.
   5. Łączymy oba zestawienia.
   6. Przestawiamy dane z jednej kolumny do dwóch kolumn – oddzielnie konto, oddzielnie zestawienie, żeby móc policzyć różnice.
   7. Obliczamy różnice, czyli wyłapujemy niezgodnie dokumenty.
   8. Omawiamy błędy, które powstały przy obliczeniu różnic i naprawiamy błędy.
   9. Ładujemy dane do Excela i omawiamy różne sposoby ładowania i przechowywania danych z Power Query.
2. UZGADNIANIE KONT – NA PRZYKŁADZIE KONTA VAT I REJESTRU VAT - Jak połączyć dane z wielu zupełnie różnych plików, z wybranych arkuszy o kompletnie innej strukturze.
   1. Omówimy bazy danych, z których będziemy łączyli dane do jednego pliku.
   2. Pobieramy dane z pliku z danymi z konta VAT i dokładamy oznaczenie, skąd pochodzą dane.
   3. Pobieramy dane z pliku z danymi z rejestru VAT i dokładamy oznaczenie, skąd pochodzą dane. Pozbywamy się zbędnych kolumn.
   4. Ujednolicamy nazwy kolumn z obu zapytań – bardzo ważna czynności.
   5. Łączymy oba zestawienia.
   6. Ponieważ ta sama faktura pojawia się na zestawieniach wiele razy, grupujemy dane tak, aby każda faktura pokazywała się w jednej pozycji – jedną kwotą.
   7. Przestawiamy dane z jednej kolumny do dwóch kolumn – oddzielnie konto, oddzielnie rejestr, żeby móc policzyć różnice.
   8. Obliczamy różnice, czyli wyłapujemy niezgodnie dokumenty.
   9. Omawiamy błędy, które powstały przy obliczeniu różnic i naprawiamy błędy.
   10. Ładujemy dane do Excela.
   11. Podstawiamy nowe pliki z danymi za kolejny miesiąc i sprawdzamy, czy nasze narzędzie zadziała do kolejnych uzgodnień.
3. DANE Z NALEŻNOŚCIAMI - Zaczytanie danych z jednego folderu, z wielu plików o jednym arkuszu i takiej samej strukturze danych
   1. WAŻNE - Omawiamy funkcję Excel.Workbook - co musisz o niej wiedzieć, żeby nie popsuć swoich danych. (funkcja do ładowania wielu plików z jednego folderu do Power Query).
   2. Zaczytamy dane z wielu plików z płatnościami za poszczególne tygodnie, z tylko jednym arkuszem i identycznie nazwanymi kolumnami do jednego pliku. Po dołożeniu pliku za kolejny tydzień, dane będą go zaczytywały automatycznie.
   3. Z nazwy pliku wydobędziemy datę, na jaki dzień zostało zrobione zestawienie.
   4. Obliczymy opóźnienie płatności i omówimy, jakie problemy napotkasz przy działaniach na datach.
   5. Podzielimy przeterminowania na przedziały przeterminowań wykorzystując kolumnę warunkową.
   6. Zastanowimy się, jak najlepiej załadować te dane do Excela.
   7. Wyciągniemy i porównamy uwagi z poszczególnych zestawień z poprzednich okresów zrobione do poszczególnych faktur.
4. ZAPISY Z KONT PRZYCHODÓW I KOSZTÓW Jak połączyć dane z wielu plików oraz dołożyć dodatkowe informacje z zupełnie innych plików
   1. Zaczytamy dane z 11 plików z miesięcznymi kosztami i przychodami i zrobimy z nich jedną bazę danych.
   2. Z dodatkowego pliku dołożymy kategorie kont, na które były dokonywane księgowania, żeby można było łatwiej zrobić analizy kosztów i przychodów oraz przedstawić wyniki finansowe.
   3. Z numeru konta wyodrębnimy oznaczenie MPK (miejsce powstania kosztu) i dołożymy jego nazwę z innego pliku, co pozwoli prezentować czytelniejsze dane.
   4. Zastanowimy się, jak najlepiej załadować te dane do Excela.
   5. Dołożymy dane z kolejnego miesiąca i sprawdzimy, czy one zostaną zaczytane po odświeżeniu danych.
5. ZESTAWIENIE OBROTÓW I SALD - Jak zaczytać dane z jednego pliku z kilku arkuszy (obrotówki z kilku miesięcy / lat), ale gdy dane z każdego arkusza mają różne kolumny albo różnie poukładane kolumny.
   1. Łączymy dane z jednego pliku z wielu arkuszy, które mają różne kolumny w różnych miejscach. Aktualizujemy dane tak, aby właściwe dane były umieszczone we odpowiedniej kolumnie. 
   2. Dokładamy obrotówkę z kolejnego roku i sprawdzamy, czy pojawi się w bazie.
6. AMORTYZACJA PODATKOWA I BILANSOWA - Jak rozdzielić dane z ewidencji środków trwałych zawierającej dane o amortyzacji podatkowej i bilansowej (dwa różne wiersze, a ten sam środek trwały), żeby nadawały się do analiz, porównań i zestawień?
   1. Pobieramy do Power Query dane z pliku z ewidencją środków trwałych.
   2. Usuwamy hurtowo wiersze z niepotrzebnymi danymi lub puste.
   3. Dodajemy kolumnę indeksu, żeby ponumerować wiersze amortyzacji podatkowej i bilansowej.
   4. Za pomocą kolumny warunkowej uzupełnimy dane zawarte w co drugim wierszu do każdego wiersza i wypełniamy puste komórki brakującymi danymi.
   5. Duplikujemy zapytanie, żeby móc zrobić oddzielne dane dla amortyzacji podatkowej i bilansowej.
   6. Usuwamy niepotrzebne kolumny i porządkujemy nagłówki.
   7. Wykorzystujemy kolumnę indeksu, żeby łatwo wyodrębnić dane podatkowe i bilansowe.
   8. Rozdzielamy rodzaj amortyzacji i stawkę amortyzacyjną do oddzielnych kolumn.
   9. Dodajemy kolumnę z informacją o kategorii amortyzacji – podatkowa / bilansowa.
   10. Analogicznie przygotowujemy dane o amortyzacji podatkowej, uzupełniając brakujące dane.
   11. Łączymy dane z dwóch zapytań w jedno, żeby obie ewidencje były w jednej bazie, co ułatwi analizowanie i porównywanie danych.