Excel: przeliczenie walut. Jak przeliczyć walutę po kursie NBP

Masz listę transakcji z zagranicznego rachunku maklerskiego: kilkadziesiąt pozycji, cztery różne waluty i każda z innego dnia. Do rozliczenia potrzebujesz złotówek po kursie NBP z konkretnej daty. Ręczne wyszukiwanie kursu dla każdego wiersza to godzina pracy i pewność, że gdzieś wkradnie się pomyłka.

W tym poradniku przechodzimy przewalutowanie po kolei: najpierw kurs z dnia transakcji, potem kurs z dnia poprzedzającego, czyli to słynne D-1 z rozliczenia podatku od zysków kapitałowych. Na końcu zaokrąglenie do złotówek. Każdy krok na tej samej tabeli transakcji, ze zrzutem z Excela i formułą do skopiowania.

Cała rzecz opiera się na jednym pomyśle: tabela kursów NBP ma daty w wierszach i waluty w kolumnach, więc kurs leży na ich przecięciu. Wystarczy podać numer wiersza i numer kolumny, a zrobią to dwie funkcje PODAJ.POZYCJĘ (MATCH).

Przeliczenie walut po kursie NBP z dnia transakcji

Na podstawie zakresu pokazującego zakup i sprzedaż akcji zagranicznych z 2021 r. chcemy zamienić wartości wyrażone w dolarach, euro, dolarze hongkońskim, dolarze kanadyjskim na złotówki, po średnim kursie nbp z dnia dokonania transakcji.

Wcześniej zaimportowane zostały kursy wszystkich walut ze strony Narodowego Banku Polskiego. Kursy trafiły do tabeli Excela o nazwie Kursy, leżącej w osobnym arkuszu. Nazwa tabeli jest tu istotna: to po niej odwołujemy się do danych w formule, zamiast wskazywać zakres komórek.

Jak wciągnąć tabelę kursów NBP do arkusza, opisałem osobno: Jak zaimportować kursy walut NBP i wykonać przewalutowanie.

W arkuszu wprowadzamy kolumnę „kursy” oraz „wartość w złotówkach”. W kolumnie „kursy” wprowadzimy wartości kursów walut dla konkretnego dnia i waluty. Do tego wykorzystamy funkcję INDEKS (INDEX) - funkcja ta zwraca wartość komórki na przecięciu określonego wiersza i kolumny.

• Pierwszym argumentem funkcji INDEKS jest cała tablica o nazwie Kursy.
• Drugim argumentem funkcji jest podanie numeru wiersza, do tego wykorzystamy funkcję PODAJ.POZYCJĘ - ta funkcja wyszukuje określony element, a następnie zwraca pozycję tego elementu w zakresie.
  • Pierwszym argumentem funkcji PODAJ.POZYCJĘ jest szukana wartość, czyli data - komórka A2,
  • Drugim argumentem jest przeszukiwana tabela, tutaj wprowadzamy: Kursy[Data]
  • Trzecim argumentem funkcji jest podanie typu porównania - podajemy wartość 0 tj. dopasowanie dokładne.
• Trzecim argumentem funkcji INDEKS jest wskazanie numeru kolumny, do tego też wykorzystamy funkcję PODAJ.POZYCJĘ:
  • jako szukana wartość podajemy walutę - komórka G2,
  • przeszukiwana tablica to nagłówki tabeli tj. Kursy[#Nagłówki],
  • typ porównania wprowadzamy 0 - dopasowanie dokładne.

Budowę obu funkcji, argument po argumencie, rozkładam w osobnym poradniku: funkcja INDEKS i PODAJ.POZYCJĘ w Excelu.

Formuła w całości wygląda tak:

=INDEKS(Kursy;PODAJ.POZYCJĘ(A2;Kursy[Data];0);PODAJ.POZYCJĘ(G2;Kursy[#Nagłówki];0))

Excel sam dopisze nazwę arkusza przed adresami komórek, jeśli tabela z kursami leży w innym arkuszu niż transakcje. To normalne i nic nie trzeba poprawiać.

W tym momencie mamy już kursy dla wskazanych dni, przeciągamy tę formułę na pozostałe komórki. Przykładowy kurs dolara kanadyjskiego z 13.12.2021 r. wynosi 3,2161 zł.

Pozostaje przeliczenie wartości na złotówki. Wprowadzamy funkcję ZAOKR:

• jako pierwszy argument tej funkcji wymnażamy wartość * kurs
• drugi argument funkcji podajemy 2 - zaokrąglenie do dwóch miejsc po przecinku

=ZAOKR(F2*H2;2)

🚀 Następny poziom

Ta formuła stoi na trzech rzeczach z kursu Excel Średniozaawansowany

✓Lekcje wideo: 40

✓Ćwiczenia z plikami: 17

✓Dostęp bez terminu

✓Gwarancja zwrotu: 30 dni

Zobacz program kursu

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy

Przewalutowanie po kursie NBP z dnia poprzedzającego (D-1)

Teraz pokażę wam jak przerobić powyższą formułę aby zwracała kursy z dnia poprzedzającego zawarcie transakcji tzw. D-1 (należy pamiętać że kursy NBP podawane są tylko dla dni roboczych).

Dla tego samego zakresu co w poprzednim przykładzie zamieniamy dane na tabelę Excela: zaznaczamy zakres, przechodzimy do karty Wstawianie i wybieramy Tabela (albo wciskamy Ctrl + T).

Wykonujemy kolejno:

• wprowadzamy funkcję INDEKS
• odwołujemy się do naszej tabeli o nazwie kursy
• następnie wykorzystamy funkcję PODAJ.POZYCJĘ aby wyszukać numer wiersza:
  • podajemy datę z tego wiersza pomniejszoną o jeden dzień: [@Data]-1
  • przeszukiwany zakres Kursy[Data]
  • typ porównania 1, czyli „największa data mniejsza lub równa szukanej”. To dlatego kurs sam cofa się do piątku, gdy dzień poprzedzający wypada w weekend
• przechodzimy do podania kolumny, wykorzystamy funkcję PODAJ.POZYCJĘ, a wartości przyjmujemy jak w poprzednim przykładzie:
  • jako szukana wartość podajemy walutę - [@Waluta],
  • przeszukiwana tablica to nagłówki tabeli tj. Kursy[#Nagłówki],
  • typ porównania wprowadzamy 0 - dopasowanie dokładne.

Formuła w całości wygląda tak:

=INDEKS(Kursy;PODAJ.POZYCJĘ([@Data]-1;Kursy[Data];1);PODAJ.POZYCJĘ([@Waluta];Kursy[#Nagłówki];0))

Aby kursy były prawidłowo pokazane to wartości muszą być ułożone w kolejności rosnącej czyli przechodzimy do arkusza z kursami i zmieniamy kolejność ułożenia wierszy według dat - od najstarszych do najnowszych.

Teraz powinniśmy mieć już prawidłowe wartości sprawdźmy. Przykładowo dla transakcji wykonanej w euro 15 grudnia 2021 r. powinniśmy otrzymać kurs z 14 grudnia 2021 r. równy 4,6436 zł

Na koniec wymnażamy wartość transakcji przez kurs i zaokrąglamy funkcją ZAOKR. W tabeli Excela zamiast adresów komórek wpisują się nazwy kolumn:

=ZAOKR([@Wartość]*[@Kursy];2)

Tym sposobem przeliczysz całą listę transakcji po średnim kursie NBP. Poniżej dołączyłem plik Excela z obydwoma przykładami.

Cztery rzeczy, na których najłatwiej się tu pomylić

• Tabela kursów musi być posortowana rosnąco po dacie. Przy wariancie D-1 trzeci argument PODAJ.POZYCJĘ to jedynka, a ta wymaga uporządkowanych danych. Na nieposortowanej tabeli dostaniesz #N/D albo, co gorsza, kurs z przypadkowego dnia bez żadnego ostrzeżenia.
• NBP nie publikuje kursów w weekendy i święta. Dlatego nie sięgamy po kurs z konkretnej daty, tylko po „ostatni nie późniejszy niż”. Przy transakcji z poniedziałku formuła sama cofnie się do piątku.
• Nagłówki w tabeli kursów muszą brzmieć dokładnie tak jak symbole walut w transakcjach. PODAJ.POZYCJĘ z zerem szuka dopasowania dokładnego, więc „USD ” ze spacją na końcu to dla Excela inna waluta niż „USD”.
• Zaokrąglaj dopiero wynik w złotówkach, nie kurs. Kurs NBP ma cztery miejsca po przecinku i taki ma zostać. Zaokrąglenie kursu do dwóch miejsc rozjeżdża kwoty przy większych transakcjach.

⚠ To jest poradnik o Excelu, nie o podatkach. Który kurs stosuje się do konkretnego zdarzenia, rozstrzygają przepisy i Twój urząd skarbowy - arkusz tylko liczy.

⚡ Power Query Zaawansowany

Następny krok: kursy NBP, które wchodzą do Excela same

✓Pobieranie danych z API po adresie z parametrem

✓Język M i własne funkcje

✓Odświeżanie całego raportu jednym przyciskiem

✓Gwarancja zwrotu: 30 dni

Zobacz kurs Power Query Zaawansowany

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy