MODUŁ.LICZBY w Excelu: wartość bezwzględna i tolerancja różnic

Masz dwie wartości i chcesz sprawdzić, jak bardzo się różnią. Gdy liczy się sama wielkość odchylenia, używam MODUŁ.LICZBY, czyli angielskiego ABS. Funkcja zwraca wartość bezwzględną. Dla -7 jest to 7, dla 7 także 7, a dla 0 pozostaje 0.

Od razu poprawiam nazwę ze starej wersji strony. W polskim Excelu właściwy zapis to MODUŁ.LICZBY, potwierdzony w dokumentacji Microsoft. WARTOŚĆ.BEZWZGLĘDNA jest opisem pojęcia, ale nie nazwą tej funkcji. Zachowuję dawny adres hasła, żeby nie przerywać istniejących linków.

Składnia i trzy podstawowe przypadki

Funkcja przyjmuje jeden argument liczbowy:

=MODUŁ.LICZBY(liczba)

Argument może być liczbą, odwołaniem lub wynikiem obliczenia. Wpisanie =MODUŁ.LICZBY(A2) tworzy wynik na podstawie A2, ale nie zmienia źródła. Ja zachowuję oryginał, ponieważ znak bywa potrzebny w dalszej analizie.

Wszystkie liczby w tym haśle są syntetyczne. Najprostszy zestaw obejmuje wartość ujemną, dodatnią i zero:

Wynik jest nieujemny, co obejmuje również zero. Poprzednia wersja EN obiecywała zawsze wynik dodatni. Ta drobna różnica w opisie ma znaczenie, gdy zero oznacza idealne dopasowanie i powinno zostać rozpoznane osobno.

Przykłady PL używają przecinka dziesiętnego i średników między argumentami funkcji zagnieżdżonych. Wersja EN używa kropki, przecinków oraz nazw ABS i IF. Separatory zależą od konfiguracji regionalnej Excela, więc przy kopiowaniu formuły sprawdzam podpowiedź w programie.

Odległość między dwiema wartościami

Załóżmy, że A2 zawiera wynik 93, a B2 wartość odniesienia 100. Różnica =A2-B2 wynosi -7. Informuje zarówno o wielkości, jak i kierunku odchylenia: wynik jest niższy od odniesienia.

Aby dostać samą odległość, zapisuję:

=MODUŁ.LICZBY(A2-B2)

Najpierw odejmowanie daje -7, potem MODUŁ.LICZBY zwraca 7. Jeśli zamienię wartości miejscami, odejmowanie da 7, a moduł nadal 7. To właściwość przydatna przy porównywaniu pomiarów lub uzgadnianiu dwóch zestawień.

Uważam na pozornie podobny zapis =MODUŁ.LICZBY(A2)-MODUŁ.LICZBY(B2). Dla 93 i 100 daje -7, więc nie jest odległością. Kolejność działań ma znaczenie: gdy potrzebuję wielkości różnicy, obejmuję funkcją całe odejmowanie.

Tolerancja: czy różnica mieści się w granicy

Przyjmijmy umowną tolerancję 10 jednostek. To założenie ćwiczenia, bez związku z zasadami konkretnego raportu. Dla liczbowych wejść mogę sprawdzić:

=MODUŁ.LICZBY(A2-B2)<=10

Wynikiem porównania jest PRAWDA albo FAŁSZ. Operator <= zalicza także granicę: różnica dokładnie 10 nadal mieści się w tolerancji. Gdy reguła wymaga wartości ściśle mniejszej niż 10, potrzebny jest operator <.

Jeśli chcę czytelne etykiety, dokładam funkcję JEŻELI:

=JEŻELI(MODUŁ.LICZBY(A2-B2)<=10;"OK";"Poza tolerancją")

Wartość graniczną sprawdzam celowo. Sam przykład z różnicą 7 nie pokaże, czy autor reguły uwzględnił równość. Jeśli próg będzie się zmieniał, umieszczam go w podpisanej komórce, a przy kopiowaniu ustalam jej odwołanie. Szczegóły wyjaśniam w artykule o dolarach w Excelu.

Suma modułów a moduł sumy

Załóżmy dwie różnice: -7 i 7. Ich suma wynosi 0. Moduł sumy również daje 0, bo przeciwne kierunki się znoszą. Suma osobnych modułów wynosi jednak 14: odchylenia mają wielkości 7 oraz 7.

Obie liczby mogą być poprawne, ale odpowiadają na inne pytania. Bilans odchyleń mówi o wspólnym kierunku po zsumowaniu. Suma ich wielkości pokazuje, ile odchylenia wystąpiło łącznie w poszczególnych rekordach. Ja najpierw nazywam pytanie, a dopiero potem układam formułę.

Dla A2 równego -7 i A3 równego 7 wpisuję do B2:

=MODUŁ.LICZBY(A2)

Kopiuję do B3, a następnie sumuję zwykłą formułą:

=SUMA(B2:B3)

Wynik to 14. Kolumna pomocnicza pokazuje oba przekształcenia, a SUMA zbiera gotowe liczby. Taki układ jest czytelny również w starszych wersjach Excela i nie wymaga uczenia się formuł tablicowych. Zastępuję nim dawny przykład z zakresem wewnątrz MODUŁ.LICZBY, który pomijał różnice między wersjami obsługującymi tablice dynamiczne a starszymi.

Kiedy usunięcie znaku szkodzi

Minus może oznaczać zwrot, spadek, niedobór albo ruch w przeciwną stronę. Bez znajomości znaczenia danych nie uznaję go za błąd. Przekształcenie wszystkich wartości na nieujemne może zmienić sens raportu, nawet jeśli formuła technicznie działa.

Jeżeli -7 i 7 opisują zmiany stanu, ich bilans wynosi 0. Po usunięciu znaków suma 14 opisuje wielkość ruchu, a nie końcową zmianę stanu. Dlatego zostawiam osobno różnicę ze znakiem oraz jej moduł i podpisuję obie kolumny.

Podobnie przy kontroli tolerancji zakładam dwa poprawne wejścia liczbowe. Brak pomiaru wymaga osobnej decyzji. Nie chcę, żeby etykieta „OK” została odczytana jako dowód kompletności rekordu. Przed oceną różnicy sprawdzam, czy obie strony porównania faktycznie dostarczono.

Nazwy, błędy i następny krok

Jeśli Excel nie rozpoznaje formuły, sprawdzam nazwę w języku instalacji. Microsoft wskazuje literówkę lub nierozpoznaną nazwę jako przyczynę błędu #NAME?, wyświetlanego w polskiej wersji jako #NAZWA?. Zmiana samego nagłówka artykułu nie naprawia błędnej nazwy we wklejonej formule.

Nie myl też MODUŁ.LICZBY z funkcją MOD. Pierwsza zwraca wartość bezwzględną, druga dotyczy reszty z dzielenia. Podobny fragment nazwy nie oznacza podobnego działania.

Na ćwiczenie przygotuj pary 93 i 100, 90 i 100 oraz 111 i 100. Policz różnicę ze znakiem, jej moduł i etykietę tolerancji. Następnie zmień próg z 10 na 7 i przewidź nowe etykiety. Na końcu dopisz parę równych liczb: różnica oraz moduł powinny wynieść 0. To mały przypadek, którego opis „zawsze dodatni” nie potrafił poprawnie wyjaśnić.