MOD w Excelu: znak reszty, podzielność i pozycja w cyklu

MOD oblicza resztę z dzielenia. Dla dodatnich liczb łatwo zobaczyć sens wyniku: z 17 sztuk można ułożyć cztery pełne grupy po 4 i zostaje 1. Przy ujemnych argumentach potrzebna jest jednak dokładniejsza reguła. Niezerowa reszta ma znak dzielnika, a nie zawsze znak liczby dzielonej.

Ja używam małych przykładów, żeby najpierw wyjaśnić resztę, a dopiero potem budować cykle czy formatowanie wierszy. Wszystkie dane są syntetyczne. To pozwala odróżnić poprawne działanie funkcji od opisu, który przypadkiem pasuje tylko do liczb dodatnich.

Składnia i reguła znaku

Dokumentacja Microsoft opisuje dwa argumenty:

=MOD(liczba;dzielnik)

Nazwa MOD jest taka sama w polskim i angielskim Excelu. W polskich przykładach stosuję średniki, a w angielskich odpowiednikach przecinki między argumentami. Separatory zależą od ustawień regionalnych. Dzielnik musi być różny od zera; dla zera funkcja zwraca #DZIEL/0!.

Ostatni przykład jest szczególnie przydatny do kontroli rozumienia. Ujemny dzielnik nie zachowuje się jak jego dodatni odpowiednik. Dla 17 i -4 wynikiem jest -3, a nie 1. Gdy dzielenie jest dokładne, reszta wynosi zero.

Skąd bierze się -3

Microsoft podaje zależność MOD(n;d) = n - d*ZAOKR.DO.CAŁK(n/d). ZAOKR.DO.CAŁK / INT zaokrągla do liczby całkowitej w dół, również dla wartości ujemnych. Ta część reguły wyjaśnia pozornie zaskakujący wynik.

Dla 17 podzielonego przez -4 iloraz wynosi -4,25. Zaokrąglenie w dół daje -5. Iloczyn -4 i -5 to 20, a 17 minus 20 daje -3. Każdy etap można prześledzić ręcznie, bez traktowania ujemnej reszty jako wyjątku wymagającego osobnej sztuczki.

Dla -10 i 3 iloraz wynosi -3 i jedną trzecią. W dół do całkowitej otrzymujemy -4, po pomnożeniu przez 3 mamy -12, więc reszta wynosi 2. Oba przykłady wynikają z tej samej reguły. Nie opisuję jej jako dążenia Excela do dodatniego wyniku.

Parzystość i podzielność

Dla całkowitej liczby w A2 warunek =MOD(A2;2)=0 sprawdza parzystość. Liczby 6 i -6 spełniają warunek, a 7 oraz -7 nie. Przy innym dodatnim dzielniku zero oznacza, że liczba jest jego wielokrotnością.

Najpierw ustal, że wejście rzeczywiście reprezentuje liczbę całkowitą. Kontrola parzystości numeru sztuki i operacja na czasie zapisanym jako ułamek doby to inne zadania. Nie stosuj reguły tylko dlatego, że w komórce widać cyfrę bez miejsc dziesiętnych. Wyświetlany format może ukrywać część wartości.

Powtarzalna numeracja od 1 do 3

W A2 umieść poniższą formułę i skopiuj w dół:

=MOD(WIERSZ(A2)-WIERSZ($A$2);3)+1

WIERSZ / ROW zwraca numer wiersza wskazanego odwołania. Różnica względem początku wynosi kolejno 0, 1, 2, 3, 4 i 5. Reszty z dzielenia przez 3 to 0, 1, 2, 0, 1, 2. Dodanie jedynki daje 1, 2, 3, 1, 2, 3.

To pozycja w cyklu. Nie jest to numer kolejnej trzywierszowej grupy: taki numer miałby postać 1, 1, 1, 2, 2, 2. Ustal, której informacji potrzebuje odbiorca, zanim nazwiesz kolumnę „grupa”. Mylny nagłówek potrafi popsuć interpretację poprawnie obliczonych liczb.

Numeracja zależna od wiersza opisuje układ arkusza. Nie traktuj jej jako trwałego identyfikatora rekordu. Po sortowaniu albo zmianie położenia wierszy relacja między numerem i konkretną obserwacją wymaga ponownego sprawdzenia. Do stałego podziału potrzebujesz reguły opartej na znaczeniu danych.

Naprzemienne tło wierszy

Dla zakresu zaczynającego się w A2 możesz użyć reguły formatowania warunkowego:

=MOD(WIERSZ()-WIERSZ($A$2);2)=0

Różnica dla pierwszego wiersza to zero, więc A2 spełnia warunek; kolejny wiersz go nie spełnia. Wybierz zakres całych wierszy tabeli, które chcesz wyróżniać, i sprawdź początek. Przesunięcie punktu odniesienia zmienia to, która połowa wierszy otrzyma kolor.

Taki kolor jest pomocą w czytaniu tabeli. Nie oznacza wspólnego klienta ani kategorii biznesowej i nie wyznacza kolejności pracy. Jeżeli wiersze mają być wyróżniane według danych, reguła powinna odwoływać się do tych danych, zamiast jedynie do pozycji na arkuszu.

Granice zastosowania i ćwiczenie

W zadaniach o sztukach zaczynam od liczb całkowitych oraz jawnego dodatniego dzielnika. Dla ułamków i obliczeń wymagających określonej precyzji trzeba dodatkowo ustalić sposób reprezentacji i tolerancję. Ten poradnik nie proponuje jednej reguły porównania dla każdego takiego procesu. Przy samym zaokrąglaniu zobacz ZAOKR.W.DÓŁ.

Prześledź na papierze MOD(10;3), MOD(-10;3) i MOD(10;-3). Wyniki to 1, 2 oraz -2. Potem zmień długość cyklu z 3 na 4 i wypisz pierwszych sześć pozycji: 1, 2, 3, 4, 1, 2. Podpisz kolumnę „pozycja w cyklu”, żeby zachować znaczenie operacji.