Czy zdarzyła Ci się kiedyś potrzeba przeliczenia czegoś za pomocą aktualnego kursu wybranej waluty? Może ten kurs trzeba od razu uwzględnić w jakichś obliczeniach, tak aby np. przeliczyć sobie wartość pensji czy faktur do zapłaty po przewalutowaniu?
Jakiego przypadku byśmy nie mieli, do szczęścia potrzebny jest nam aktualny kurs obcej waluty. Taki można pobrać z Internetu, z wielu stron, my skorzystamy ze strony Narodowego Banku Polskiego.
Celem naszego zadania będzie zaimportowanie aktualnych kursów walut do arkusza, uwzględniając możliwość automatycznego odświeżenia danych w arkuszu jeśli zmienią się one na stronie, dokonanie niezbędnych przekształceń jeśli będą takie potrzebne, a finalnie zwrócenie wybranego kursu waluty na arkuszu, z przygotowaną formatką. Wszystko to w zaledwie paru krokach. 😉
Pierwszym krokiem jest znalezienie w Internecie strony, na której publikowane są aktualne kursy walut, a następnie zaimportowanie ich do Excela. Strona, którą się posłużymy znajduje się pod adresem https://www.nbp.pl/home.aspx?f=/kursy/kursya.html, a narzędzie znajdziemy na karcie Dane, w sekcji Pobieranie i przekształcanie danych.
Klikamy w przycisk Z sieci Web,a do okienka, które nam się otworzy wprowadzamy adres strony internetowej i klikamy OK.
Po chwili otworzy nam się okienko Nawigatora, w którym z lewej strony możemy zobaczyć strukturę strony internetowej, a z prawej podgląd tych elementów. Nas interesuje element Table 0, czyli tabela zawierająca wszystkie kursy walut. Teraz mamy wybór, możemy kliknąć:
Nas interesuje ta druga opcja, ponieważ jeśli się przyjrzymy, to zobaczymy, że dane w tabeli nie są jednolite, to znaczy czasem kurs średni jest pokazany w odniesieniu dla 1 jednostki waluty, a czasem dla 100 jednostek. W Excelu chcielibyśmy mieć tylko jedno odniesienie dlatego klikamy w Przekształć dane.
Ujednolicenie danych będzie polegało na przedstawieniu każdego kursu dla jednej jednostki waluty, więc tam gdzie mamy np. 100 forintów chcemy przedstawić kurs dla 1 forinta. W tym celu najpierw rozdzielimy sobie kolumnę z kodem waluty na dwie, tak aby zobaczyć oddzielnie liczby i oddzielnie teksty (aktualnie cała kolumna jest traktowana jako tekst).
Aby wykonać ten krok musimy zaznaczyć kolumnę Kod waluty klikając w jej nagłówek, następnie z karty Narzędzia główne, z sekcji Przekształć wybieramy narzędzie Podziel kolumny -> Według ogranicznika.
W nowym oknie mamy możliwość wyboru ogranicznika, czyli znaku jakim rozdzielony jest nasz tekst w kolumnie. Narzędzie prawidłowo wykryło, że tym znakiem jest Spacja (pozycja na liście rozwijanej) dlatego klikamy w przycisk OK.
W efekcie dostajemy dwie kolumny, w jednej samą wartość liczbową, w drugiej sam kod waluty.
Następnym krokiem jest podzielenie kursu średniego przez wartość liczbową wyciągniętą z kursu. Aby zrobić to precyzyjnie należy najpierw zaznaczyć kolumnę z kursem średnim, a dopiero potem doznaczyć kolumnę z wartością liczbową. Kolejność zaznaczenia ma kluczowe znaczenie, ponieważ w takiej kolejności w jakiej kolumny zaznaczymy odbędzie się dzielenie.
Po zaznaczeniu dwóch kolumn przechodzimy na kartę Dodaj kolumnę i z sekcji Z liczby wybieramy przycisk Standardowy -> Dzielenie. W efekcie otrzymujemy kolejną kolumnę z wartością kursu podzieloną przez wartość z kolumny Kod waluty.1.
Dane są już przekształcone, natomiast dobrze byłoby zwrócić do Excela tylko potrzebne nam kolumny, czyli nazwę waluty i kurs jednostkowy.
Najpierw możemy zmienić nazwę nowej kolumny z Dzielenie na Średni kurs – aby to zrobić wystarczy dwukrotnie kliknąć na nagłówku kolumny i wpisać interesującą nas nazwę.
Kolejno zajmiemy się usunięciem zbędnych kolumn. Możemy to zrobić poprzez zaznaczenie tylko tych kolumn, które nas interesują, czyli Nazwa waluty i Średni kurs, a następnie kliknąć PPM na zaznaczeniu i wybrać opcję Usuń inne kolumny.
Po wykonaniu tej operacji zostaną nam tylko dwie kolumny. Zanim zwrócimy je do Excela warto jeszcze w jakiś sposób nazwać nasze nowe dane – można zrobić to nazywając połączenie w Power Query – służy do tego pole Nazwa, które znajduje się w panelu Ustawienia zapytania po prawej stronie programu.
Dane zwracamy przyciskiem Zamknij i załaduj, który znajduje się na karcie Narzędzia główne w sekcji Zamknij. Dane zostają umieszczone w nowym arkuszu Excela w tabeli o nazwie Kursy walut NBP.
Dane wylądowały w nowym arkuszu w Tabeli. Dzięki temu, że zostały zaimportowane mieliśmy możliwość ich przekształcenia przed wrzuceniem do arkusza, ale dodatkowo stworzone zostało połączenie ze stroną internetową, które możemy odświeżyć.
Aby to wykonać wystarczy na takich danych kliknąć PPM i z rozwijanej listy wybrać przycisk Odśwież.
Dzięki temu, po chwili naszym oczom ukażą się nowe kursy walut (jeśli oczywiście uległy one zmianie na stronie i mamy dostęp do internetu 😊). W pasku stanu Excela możemy zaobserwować komunikat o uruchamianiu kwerendy w tle, czyli łączeniu się ze stroną, pobieraniu z niej nowych danych, przekształcaniu w Power Query i finalnie podmienienie danych w Excelu.
Działa to naprawdę fajnie, ale jeszcze fajniej byłoby, gdyby te odświeżanie wykonywało się samo, najlepiej przy każdorazowym włączeniu tego konkretnego pliku Excela.
Tak na szczęście też da się zrobić. Wystarczy, że aktywną komórką stoimy w tabeli i przejdziemy na kartę Zapytanie. W niej trzeba odnaleźć przycisk Właściwości, który otworzy nam okno z możliwością zaznaczenia opcji Odśwież dane podczas otwierania pliku. Wystarczy ją zaznaczyć i gotowe!
Od teraz dane będą się odświeżać za każdym otwarciem pliku jeśli tylko będziemy podłączeni do Internetu i strona z kursami nie zmieni swojego adresu.
Gdy dane mamy już zaimportowane, przekształcone i dynamicznie odświeżane czas opakować je w ładną formę, czyli zajmiemy się uzupełnieniem naszej formatki. Jest ona bardzo prosta, ale będzie wykonywać kilka rzeczy na raz.
W polu o nazwie:
Rozpoczynamy od stworzenia listy rozwijanej w komórce B2. W tym celu ustawiamy się na tej komórce, przechodzimy na kartę Dane i z sekcji Narzędzia danych wybieramy Poprawność danych. W narzędziu, z rozwijanej listy wybieramy pozycję Lista, a jako Źródło podajemy zakres komórek zawierających nasze nazwy walut znajdujące się w nowym arkuszu.
Teraz możemy wybrać z listy dowolną nazwę waluty.
Kolejnym krokiem jest stworzenie formuły w komórce A2. Jak wspomnieliśmy, będzie to dzielenie wartości wyrażonej w polskich złotych przez wartość pobieraną z arkusza z walutami. Samo pobranie wartości zostanie wykonane za pomocą formuły WYSZUKAJ.PIONOWO, której budowa przedstawia się następująco:
Finalna funkcja w komórce A2 wygląda następująco: =A1/WYSZUKAJ.PIONOWO(B2;Kursy_walut_NBP;2;0)
Dbając o wygląd naszej formatki możemy ukryć arkusz z zaimportowanymi walutami oraz na komórkach z wartościami liczbowymi ustawić format liczbowy z dwoma miejscami po przecinku.
Ostatni krok to podanie w komórce A1 wartości, którą chcemy przewalutować. Teraz możemy już w pełni korzystać z naszej formatki, której działanie przedstawia poniższa grafika:
Power Query to narzędzie, które dostępne jest w Excelu już od kilkunastu lat. Niemniej dopiero ostatnie zyskuje coraz większą popularność, a użytkownicy zaczynają dostrzegać praktyczne zastosowanie Power Query w codziennej pracy. Niektórzy określają to narzędzie jako rewolucyjne w pracy z Excelem. Jeśli chcesz pogłębić swoją wiedzę z tematu Power Query sprawdź nasze szkolenia – szkolenie Power Query oraz inne kursy Excel.
