Zaokrąglanie w Power Query daje inny wynik niż w Excelu - Number.Round i 2,5

Power Query w Excelu, Power BI i Fabric

Zaokrąglanie w Power Query daje inny wynik niż w Excelu - Number.Round i 2,5

W skrócie

Kwota brutto w zestawieniu z Power Query różni się o grosz od tej samej kwoty policzonej formułą w Excelu, a 2,5 po zaokrągleniu daje 2. To nie usterka danych: w Power Query zaokrąglanie funkcją Number.Round domyślnie działa inaczej niż ZAOKR w Excelu. Pokazujemy, kiedy wyniki się rozjeżdżają, jak uzyskać zgodność z Excelem i jak sprawdzić sumy.

Jak to wygląda w praktyce

Rozjazd dotyczy tylko wartości dokładnie w połowie między dwoma wynikami. Sprawdziliśmy w polskim Excelu te same liczby funkcją Number.Round i formułą ZAOKR:

• Number.Round(2.5) zwraca 2, a ZAOKR zwraca 3,
• Number.Round(-2.5) zwraca -2, a ZAOKR zwraca -3,
• Number.Round(3.5) zwraca 4, tak samo jak ZAOKR,
• Number.Round(0.125, 2) zwraca 0,12, a ZAOKR z dwoma miejscami po przecinku 0,13,
• cena 24,25 zł z rabatem 50% to 12,125 zł: Power Query daje 12,12, a ZAOKR 12,13,
• 1,50 zł netto razy 1,23 to 1,845 zł brutto: Number.Round(1.5 * 1.23, 2) daje 1,84, a ZAOKR 1,85.

Różnice wychodzą w kolumnach wyliczanych, na przykład w kwocie brutto albo cenie po rabacie, a potem w sumach i w porównaniu z fakturami. W kursie ten sam zapis bez trzeciego argumentu ma kolumna Wartość brutto, Number.Round([Wartość netto] * (1 + [Stawka VAT]), 2), i przeliczenie zamówień na złote kursem NBP.

Dlaczego tak się dzieje

Funkcja Number.Round przyjmuje liczbę, liczbę miejsc po przecinku i tryb zaokrąglania. Opis funkcji w Power Query mówi wprost, że domyślnie remis jest rozstrzygany przez zaokrąglenie do najbliższej liczby parzystej trybem RoundingMode.ToEven, nazywanym zaokrągleniem bankowym. Remis to wartość dokładnie w połowie: 2,5 leży tak samo daleko od 2 jak od 3, więc wygrywa parzyste 2. Przy 3,5 parzysta jest 4, dlatego tu Power Query i Excel się zgadzają.

Funkcja ZAOKR w Excelu rozstrzyga remis od zera: 2,5 daje 3, a -2,5 daje -3. W Power Query to samo zachowanie daje tryb RoundingMode.AwayFromZero w trzecim argumencie. Tryb ma znaczenie wyłącznie przy remisie, wartości poza połową oba narzędzia zaokrąglają do najbliższej liczby. Dlatego różnice dotyczą tylko części wierszy i łatwo je przeoczyć.

Jak to rozwiązać krok po kroku

1. Znajdź kroki, które zaokrąglają. Na karcie Strona główna otwórz Edytor zaawansowany i poszukaj w kodzie Number.Round. Zwróć uwagę na wywołania z dwoma argumentami, bo to one używają trybu domyślnego.
2. Kliknij ikonę koła zębatego przy kroku kolumny niestandardowej, żeby otworzyć okno Kolumna niestandardowa, i dopisz trzeci argument. Formuła z kursu przyjmie postać Number.Round([Wartość netto] * (1 + [Stawka VAT]), 2, RoundingMode.AwayFromZero). Z tym trybem 2,5 daje 3, -2,5 daje -3, a 1,845 daje 1,85, tak samo jak ZAOKR.
3. Kolumnę liczb możesz też zaokrąglić w miejscu jednym krokiem, na przykład = Table.TransformColumns(Poprzedni, {{"Kwota", each Number.Round(_, 2, RoundingMode.AwayFromZero), type number}}). W naszym teście wartości 2,5, 3,5 i -2,5 zaokrąglone do całości dały 3, 4 i -3.
4. Jeśli zaokrąglasz poleceniem z grupy Zaokrąglenie na karcie Przekształć, sprawdź w pasku formuły, czy powstały krok używa Number.Round bez trzeciego argumentu. Jeśli tak, dopisz w nim tryb tak samo jak wyżej.
5. Wybierz tryb świadomie. RoundingMode.AwayFromZero odpowiada ZAOKR, RoundingMode.Up rozstrzyga remis w górę (-2,5 daje -2), RoundingMode.Down w dół (-2,5 daje -3), RoundingMode.TowardZero w stronę zera, a RoundingMode.ToEven to domyślne zaokrąglenie bankowe.
6. Nie myl trybów z funkcjami Number.RoundUp i Number.RoundDown. One zawsze idą w górę albo w dół, także poza remisem: Number.RoundUp(2.1) zwraca 3.

Jak sprawdzić, że zadziałało

Dodaj tymczasową kolumnę niestandardową z różnicą obu trybów: Number.Round([Kwota], 2, RoundingMode.AwayFromZero) - Number.Round([Kwota], 2). Wiersze z wynikiem różnym od zera to remisy, w których tryb zmienia wynik. Porównaj kilka z nich z formułą ZAOKR w arkuszu: po dopisaniu trybu kwota 1,845 ma dawać 1,85 w obu miejscach.

Sprawdź też sumę kontrolną, bo tryb wpływa na nią przy wielu pozycjach. W naszym teście wartości 0,5, 1,5, 2,5 i 3,5 po zaokrągleniu bankowym sumują się do 8, czyli tak jak przed zaokrągleniem, a po zaokrągleniu od zera do 10. Zestawienie ma używać tego samego trybu co dokumenty, z którymi je porównujesz. Żeby problem nie wrócił, wpisuj tryb w każdym Number.Round, także RoundingMode.ToEven, gdy wybierasz zaokrąglenie bankowe: zapisany wprost pokazuje, że to świadoma decyzja.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5