fbpx

Kursy walut w Excelu

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. 😉

Import danych za pomocą Power Query

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.

Kursy walut Excel

Klikamy w przycisk Z sieci Web,a do okienka, które nam się otworzy wprowadzamy adres strony internetowej i klikamy OK.

Kursy walut Excel

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ąć:

  • Załaduj – wtedy tabela zostanie zaimportowana tak jak ją widać, bez żadnych zmian, wprost do nowego arkusza Excela,
  • Przekształć dane – wtedy przejdziemy do Edytora Power Query, w którym będziemy mogli dokonać zmian w danych jeszcze zaimportowaniem ich do Excela.

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.

Kursy walut Excel

Przekształcenie danych w Power Query

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.

Kursy walut Excel

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.

Kursy walut Excel Power Query

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.

Kursy walut Excel Power Query

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.

Kursy walut Excel Power Query

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.

Kursy walut Excel Power Query

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.

Kursy walut Excel Power Query

Dynamizacja, czyli odświeżanie danych

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.

Kursy walut Excel Power Query

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.

Kursy walut Excel Power Query

Końcowe obliczenia

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.

Kursy walut Excel Power Query

W polu o nazwie:

  • wartość (A1) – będziemy wpisywać kwotę wyrażoną w PLN jaką chcemy przewalutować,
  • wynik (A2) – będziemy za pomocą formuły dzielić wartość wyrażoną w PLN przez wartość, która będzie pobierana z arkusza z walutami obcymi,
  • waluta obca (B2) – będziemy mieli listę rozwijaną z nazwami walut pobieranymi z arkusza z tabelą.

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.

Kursy walut Excel Power Query

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:

  • szukana_wartość – to komórka B2, czyli wybrana z listy nazwa waluty,
  • tabela_tablica – to cała nasza tabela z kursami, zamiast adresu konkretnych komórek wpisujemy nazwę obiektu (czyli naszej tabeli),
  • nr_indeksu_kolumny – to liczba 2, czyli informacja, z której kolumny naszej tabeli_tablicy chcemy zwracać wartości,
  • [przeszukiwany_zakres] – to liczba 0, ponieważ używamy dopasowania dokładnego.

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:

Kursy walut Excel Power Query

Zobacz również:

Szkolenia:

Gratulacje!
Wniosek został wysłany prawidłowo.
Dziękujemy za przesłanie zapytania. Skontaktujemy się z Tobą najszybciej jak to możliwe.