Power Query i VBA – przyjaciele czy wrogowie?

Excel jest niewątpliwie jednym z najczęściej wykorzystywanych narzędzi do pracy z danymi. Praca z dużą ilością danych, np. w dużych firmach, wiąże się z rosnącą potrzebą automatyzacji procesów, w celu zwiększenia efektywności pracy i zaoszczędzenia czasu. W odpowiedzi na te zapotrzebowanie użytkownicy sięgają po dwa najczęściej wykorzystywane w tym zakresie narzędzia – Power Query i VBA (Visual Basic for Applications). Choć kierują się innymi zasadami, a sposób korzystania z nich jest kompletnie odmienny, gdy z nich korzystamy, okazuje się, że często przeznacza się je do tych samych problemów. Stąd pojawia się pytanie – czy VBA i Power Query to konkurencyjne, czy może jednak wzajemnie wspierające się narzędzia? W tym materiale postaramy się odpowiedzieć na to pytanie.

Visual Basic for Applications (VBA) to wbudowany w Excelu język programowania, który umożliwia nam tworzenie makr i skryptów, używanych do automatyzacji różnych procesów. Narzędzie to pozwala na pełną kontrolę nad arkuszem, generowanie raportów oraz może reagować na zdarzenia wywołane przez użytkownika (np. wpisanie tekstu z klawiatury). Choć wymaga znajomości programowania, VBA jest niezastąpione przy tworzeniu złożonych procesów i dynamicznych raportów. Zaletami VBA są m.in.:

• możliwość tworzenia niestandardowych funkcji
• automatyzacja bardziej skomplikowanych procesów
• pełna kontrola nad automatyzacją
• możliwość interakcji z użytkownikiem
• możliwość integracji z innymi aplikacjami Microsoft Office (generowanie dokumentów Word z Excela, automatyczna wysyłka e-maili, eksport danych do pliku PDF itp.)

Power Query to narzędzie, pozwalające w szybki i skuteczny sposób importować, łączyć i przekształcać dane z różnych źródeł, takich jak bazy danych, strony internetowe, czy skoroszyty Excel. Dzięki graficznemu interfejsowi, użytkownik może krok po kroku przygotowywać dane do dalszej pracy, nawet jeśli nie ma wiedzy z programowania. Do największych zalet Power Query można zaliczyć:

• automatyzacja importowania i przekształcania danych
• możliwość importowania danych z wielu różnych źródeł naraz
• proste odświeżanie danych jednym kliknięciem
• możliwość kontrolowania historii kroków przekształceń
• wbudowane narzędzia nie wymagają od nas programowania

Choć na pierwszy rzut oka wydaje się, że te narzędzia są wykorzystywane w jednym celu – automatyzacji pracy w Excelu. W rzeczywistości posiadają jednak wiele kluczowych różnic i są wykorzystywane w różnych sytuacjach.

Kiedy użyć Power Query?

• Gdy zależy ci na prostocie i szybkości
• Gdy nie masz doświadczenia programistycznego
• Gdy potrzebujesz zaimportować dane z wielu źródeł
• Gdy twoje dane wymagają wielu przekształceń przed analizą

Kiedy lepszym wyborem będzie VBA?

• Gdy chcesz zautomatyzować cały proces – od importu, przez analizę, aż do raportu
• Gdy proces jest skomplikowany i wymaga logiki sterującej
• Gdy potrzebna jest interakcja z użytkownikiem (np. wyliczając wartość brutto, spytamy użytkownika o stawkę VAT)
• Gdy Excel ma reagować na zdarzenia (np. kliknięcie przycisku, zmiana komórki, uruchomienie drukowania)

Power Query i VBA mogą jednak czasem konkurować ze sobą o to samo zadanie. Oba narzędzia potrafią importować i przekształcać dane, więc, np. w sytuacji, gdy chcemy przygotować dane do analizy, pojawia się pytanie: „które z nich wybrać?”. Od tej decyzji zależy nie tylko szybkość, ale i wygoda wykonywania danego zadania. To właśnie w takich sytuacjach Power Query i VBA zaczynają rywalizować – stając się „wrogami”.

Mamy przykładowe dane:

Chcemy przygotować te dane do analizy, by następnie trafiły do tabeli przestawnej. Naszym zadaniem jest poprawić format dat, usunąć puste wiersze oraz dodać dwie nowe kolumny: VAT oraz Wartość Brutto. Taki typ zadań można wykonać, używając zarówno Power Query, jak i VBA.

Sposób wykonania w Power Query:

Załadujemy nasze dane do Power Query. Aby to zrobić zaznaczamy dane, następnie wybieramy kartę Dane i klikamy Z tabeli/zakresu:

Jeśli nasze dane nie są w tabeli, Excel będzie chciał ją utworzyć. Upewniamy się, że mamy zaznaczone wszystkie potrzebne nam dane i klikamy OK.

Gdy znajdujemy się już w edytorze Power Query, chcielibyśmy poprawić nasze dane przed wstawieniem nowych kolumn. Pierw usuniemy puste wiersze. Zauważ, że przy zmianie typu danych w kolumnach, PQ oznaczyło puste wiersze jako „null”. Klikamy strzałkę filtrującą w dowolnej kolumnie (np. w kolumnie Data):

Następnie odznaczamy opcję „(null)” i klikamy OK – to usunie puste wiersze.

Zauważ, że Power Query automatycznie dodał ten krok do listy po prawej stronie, w sekcji „Zastosowane kroki”. Każda operacja, jaką wykonujemy w tym narzędziu, jest zapisywana jako oddzielny krok. Możemy dzięki temu do niej wrócić, zmodyfikować ją lub usunąć. Dzięki tej funkcji Power Query daje nam pełną kontrolę nad historią przekształceń.

Naszym następnym krokiem będzie poprawienie formatu dat. Zauważ, że Power Query zmienił nam format daty na poprawny, lecz nie usunął części godzinowej „00:00:00”. Aby to usunąć, należy kliknąć małą ikonę typu danych obok nagłówka kolumny(np. kolumny Data), a następnie wybrać typ danych Data.

Gdy nasze dane są już poprawione, chcielibyśmy dodać potrzebną nam kolumnę VAT. Aby to zrobić, przechodzimy do zakładki Dodaj Kolumnę, a następnie wybieramy Kolumna niestandardowa.

Nazywamy kolumnę „VAT”, i uzupełniamy pole formuły potrzebnym obliczeniem, a następnie klikamy OK:

Następnie stworzymy kolumnę Wartość brutto. Ponownie wybieramy Dodaj kolumnę i Kolumna niestandardowa, jak powyżej, uzupełniamy pola w kreatorze kolumny i klikamy OK:

Gdy nasze dane są już gotowe, załadujemy je do Excela. Aby to zrobić, w Narzędziach głównych klikamy Zamknij i załaduj:

Sposób wykonania w VBA:

Aby wykonać kod w VBA, musimy otworzyć edytor Visual Basic i stworzyć nowe makro. Aby to zrobić, należy przejść do karty „Deweloper”. Jeśli jej nie masz, kliknij kartę Plik:

Następnie w lewym dolnym rogu wybieramy Opcje:

Następnie klikamy Dostosowywanie Wstążki i zaznaczamy Deweloper:

Gdy włączyliśmy już kartę Deweloper, możemy przejść do edytora VBA. Przechodzimy do karty Deweloper i klikamy Edytor Visual Basic (lub naciśnij skrót Alt + F11):

Następnie tworzymy nowy moduł, by zacząć pisanie kodu:

Przykładowy kod w VBA:

Następnie zaznaczamy nasze dane, przechodzimy do zakładki Deweloper, wybieramy Makra, a następnie uruchamiamy utworzone makro:

Mamy już dane przygotowane do dalszej analizy. Jak widać, zarówno Power Query, jak i VBA, świetnie sprawdziło się do tego zadania. To, które wybierzemy w takiej sytuacji, będzie zależeć od naszych preferencji i doświadczenia.

Pamiętaj!

Zawsze gdy tworzysz w swoim pliku makra, przy zapisywaniu pamiętaj o zmianie rozszerzenia na .xlsm, .xlsb lub .xls. Pliki z podstawowym rozszerzeniem .xlsx nie przechowują makr!

W rzeczywistości, Power Query i VBA najlepiej sprawdzają się, gdy są używane razem. Ich zróżnicowane możliwości sprawiają, że uzupełniają się wzajemnie i pozwalają na tworzenie w pełni zautomatyzowanych rozwiązań. Power Query jest lepszym rozwiązaniem, jeśli chodzi o pobieranie i przekształcanie danych z wielu różnych źródeł, zwłaszcza gdy, np. importujemy wiele plików na raz. Z kolei VBA jest niezastąpione, gdy potrzebujemy np. podzielić gotowy raport na wiele osobnych plików dla różnych odbiorców, zapisać je w wybranych miejscach czy rozesłać automatyczne e-maile z załącznikami.

Mamy następujące dane:

W tej sytuacji, podobnie jak w Przykładzie 1, chcielibyśmy przekształcić nasze dane w Power Query, by nadawały się do analizy. Usuniemy puste wiersze, poprawimy format dat i dodamy nową kolumnę „Kwota całkowita”, która wyświetli nam, ile każdy pracownik otrzyma wynagrodzenia całkowicie. Następnie za pomocą VBA stworzymy makro, które z poprawionych danych stworzy raport w formacie PDF.

W edytorze Power Query, wykonując czynności, jak w poprzednim przykładzie, usuwamy puste wiersze, poprawiamy format dat i dodajemy nową kolumnę obliczeniową:

Po załadowaniu dane powinny wyglądać następująco:

Następnie otwieramy VBA i tworzymy makro, które odświeży poprawione już dane (by upewnić się czy nie zaszły żadne zmiany), stworzy z nich tabelę przestawną i na jej podstawie zbuduje wykres kolumnowy. Przed przygotowaniem kodu zmieniamy nazwę tabeli na „Dane_poprawione”, by łatwiej było nam się do niej odwoływać:

Przygotowana procedura (makro) stworzy nowy arkusz, przygotuje raport oraz wykres, zapisze całość w pliku PDF i usunie przygotowany arkusz.

Aby jeszcze bardziej ułatwić korzystanie z makra, możemy dodać w arkuszu „przycisk”, który jednym kliknięciem uruchomi stworzony przez nas skrypt(makro). Wybieramy Wstawianie, kształty a następnie wybieramy dowolny kształt (np. prostokąt), wpisujemy w nim tekst (np. „Wygeneruj raport”) i przypisujemy do niego makro, klikając prawym przyciskiem myszy, wybierając polecenie „Przypisz makro”:

Wygenerowany raport PDF:

Powyższe przykłady pokazują, że w niektórych sytuacjach Power Query i VBA mogą konkurować, lecz w praktyce znacznie bardziej się uzupełniają. Ich różnice w zastosowaniu powodują, że są zdecydowanie bardziej  „przyjaciółmi”, niż „wrogami”.