Excel na pomoc czyli jak z pomocą Power Query szybko i automatycznie uzgadniać konta, porządkować bałagan w danych, łączyć dane z wielu plików

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.

• Omówimy bazy danych, z których będziemy łączyli dane do jednego pliku.
• Pobieramy dane z pliku z danymi z konta sprzedaży i dokładamy oznaczenie, skąd pochodzą dane.
• Pobieramy dane z pliku z danymi z zestawienia faktur i dokładamy oznaczenie, skąd pochodzą dane.
• Ujednolicamy nazwy kolumn z obu zapytań – bardzo ważna czynności.
• Łączymy oba zestawienia.
• Przestawiamy dane z jednej kolumny do dwóch kolumn – oddzielnie konto, oddzielnie zestawienie, żeby móc policzyć różnice.
• Obliczamy różnice, czyli wyłapujemy niezgodnie dokumenty.
• Omawiamy błędy, które powstały przy obliczeniu różnic i naprawiamy błędy.
• Ł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.

• Omówimy bazy danych, z których będziemy łączyli dane do jednego pliku.
• Pobieramy dane z pliku z danymi z konta VAT i dokładamy oznaczenie, skąd pochodzą dane.
• Pobieramy dane z pliku z danymi z rejestru VAT i dokładamy oznaczenie, skąd pochodzą dane. Pozbywamy się zbędnych kolumn.
• Ujednolicamy nazwy kolumn z obu zapytań – bardzo ważna czynności.
• Łączymy oba zestawienia.
• 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ą.
• Przestawiamy dane z jednej kolumny do dwóch kolumn – oddzielnie konto, oddzielnie rejestr, żeby móc policzyć różnice.
• Obliczamy różnice, czyli wyłapujemy niezgodnie dokumenty.
• Omawiamy błędy, które powstały przy obliczeniu różnic i naprawiamy błędy.
• Ładujemy dane do Excela.
• 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

• 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).
• 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.
• Z nazwy pliku wydobędziemy datę, na jaki dzień zostało zrobione zestawienie.
• Obliczymy opóźnienie płatności i omówimy, jakie problemy napotkasz przy działaniach na datach.
• Podzielimy przeterminowania na przedziały przeterminowań wykorzystując kolumnę warunkową.
• Zastanowimy się, jak najlepiej załadować te dane do Excela.
• 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

• Zaczytamy dane z 11 plików z miesięcznymi kosztami i przychodami i zrobimy z nich jedną bazę danych.
• 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.
• Z numeru konta wyodrębnimy oznaczenie MPK (miejsce powstania kosztu) i dołożymy jego nazwę z innego pliku, co pozwoli prezentować czytelniejsze dane.
• Zastanowimy się, jak najlepiej załadować te dane do Excela.
• 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.

• Łą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. 
• 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ń?

• Pobieramy do Power Query dane z pliku z ewidencją środków trwałych.
• Usuwamy hurtowo wiersze z niepotrzebnymi danymi lub puste.
• Dodajemy kolumnę indeksu, żeby ponumerować wiersze amortyzacji podatkowej i bilansowej.
• 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.
• Duplikujemy zapytanie, żeby móc zrobić oddzielne dane dla amortyzacji podatkowej i bilansowej.
• Usuwamy niepotrzebne kolumny i porządkujemy nagłówki.
• Wykorzystujemy kolumnę indeksu, żeby łatwo wyodrębnić dane podatkowe i bilansowe.
• Rozdzielamy rodzaj amortyzacji i stawkę amortyzacyjną do oddzielnych kolumn.
• Dodajemy kolumnę z informacją o kategorii amortyzacji – podatkowa / bilansowa.
• Analogicznie przygotowujemy dane o amortyzacji podatkowej, uzupełniając brakujące dane.
• Łączymy dane z dwóch zapytań w jedno, żeby obie ewidencje były w jednej bazie, co ułatwi analizowanie i porównywanie danych.