✅ Funkcja MOD w Excelu - Oblicz resztę z dzielenia

Spisie treści

Przegląd funkcji MOD
Przykłady MOD w VBA
Funkcja MOD Arkuszy Google

Pobierz przykładowy skoroszyt

Ten samouczek programu Excel pokazuje, jak używać Funkcja Excel MOD aby obliczyć resztę po dzieleniu.

Przegląd funkcji MOD

Funkcja MOD zwraca resztę lub moduł liczby po wykonaniu dzielenia. Jednak funkcja MOD nie służy wyłącznie do pomocy w naszych problemach z podziałem. Staje się jeszcze potężniejszy, gdy chcemy wyszukać każdy N-ty element na liście lub gdy musimy wygenerować powtarzający się wzorzec.

Aby użyć funkcji arkusza kalkulacyjnego MOD Excel, wybierz komórkę i wpisz:

Składnia i wejścia funkcji MOD:

1	=MOD(liczba;dzielnik)

numer - Numer.

dzielnik - Liczba do dzielenia.

Co to jest funkcja MOD?

Podstawowa matematyka MOD

Kiedy próbujesz podzielić 13 przez 4, możesz powiedzieć, że odpowiedź to 3 pozostałe 1. W tym przypadku „1” jest konkretnie znane jako moduł (stąd nazwa funkcji MOD). W formule moglibyśmy wtedy napisać

1	=MOD(13, 4)

A wynik byłby 1.

Spojrzenie na tę tabelę daje więcej ilustracji pokazujących, jak będzie działać wejście/wyjście MOD.

1	=MOD(A2,3)

Zauważ, że gdy na wejściu było 3, nie było reszty, a zatem wynik formuły wynosił 0. Ponadto w naszej tabeli użyliśmy funkcji WIERSZ do wygenerowania naszych wartości. Duża część mocy MOD będzie pochodzić z funkcji WIERSZ (lub KOLUMNA), jak zobaczymy w poniższych przykładach.

MOD Suma co drugi wiersz

Rozważ tę tabelę:

Dla celów ilustracyjnych, druga kolumna zawiera formułę

1	=MOD(A2, 2)

Aby dodać wszystkie parzyste wiersze, możesz napisać formułę SUMA.JEŻELI i poprosić, aby kryteria szukały wartości 0 w kolumnie B. Lub, aby dodać wszystkie nieparzyste wiersze, należy ustawić kryteria wyszukiwania 1 wartości.

Jednak w ogóle nie musimy tworzyć kolumny pomocniczej. Możesz połączyć moc MOD w SUMPRODUCT, aby zrobić to wszystko w jednym kroku. Nasz wzór na to byłby

1	=PROD.SUMA(A2:A5; --(MOD(B2:B2, 2)=0)

Ponieważ znajduje się w SUMPRODUCT, funkcja MOD będzie w stanie obsłużyć nasze dane wejściowe tablicy. Wyniki widzieliśmy już w kolumnie pomocniczej, ale tablica z naszego MOD w tej formule będzie miała postać {0, 1, 0, 1}. Po sprawdzeniu wartości równych 0 stosując podwójną liczbę jednoargumentową, tablica będzie miała postać {1, 0, 1, 0}. SUMPRODUCT następnie robi to magicznie lub mnoży tablice, aby uzyskać {2, 0, 4, 0}, a następnie sumuje, aby uzyskać żądane wyjście 6.

MOD Suma co N-ty wiersz

Ponieważ formuła MOD(x, N) wygeneruje 0 przy każdej N-tej wartości, możemy to wykorzystać, aby pomóc formułom wybrać i wybrać wartości, które mają być używane w innych funkcjach. Spójrz na ten stół.

Naszym celem jest pobranie wartości z każdego wiersza oznaczonego „Total”. Zwróć uwagę, że suma pojawia się co 3^{r & D} wiersz, ale zaczynając od wiersza 4. Nasza funkcja MOD użyje zatem 3 jako 2^NS argument i musimy odjąć 1 od pierwszego argumentu (ponieważ 4 -1 = 3). W ten sposób żądane wiersze (4, 7, 10) będą wielokrotnością 3 (3, 6, 9). Nasz wzór na zsumowanie pożądanych wartości będzie

1	=PROD.SUMA(C2:C10; --(MOD(WIERSZ(A2:A10)+2;3)=0))

Wytworzona tablica przekształci się w ten sposób:

12345

{2, 3, 4, 5, 6, 7, 8, 9, 10}{1, 2, 3, 4, 5, 6, 7, 8, 9}{1, 2, 0, 1, 2, 0, 1, 2, 0}{Fałsz, fałsz, prawda, fałsz, fałsz, prawda, fałsz, fałsz, prawda}{0, 0, 1, 0, 0, 1, 0, 0, 1}

Tablica kryteriów naszego SUMPRODUCT jest teraz skonfigurowana tak, jak musimy chwytać co 3^{r & D} wartość, a otrzymamy pożądany wynik w wysokości 90 USD.

Suma MOD w kolumnach

Do tej pory używaliśmy przykładów, które poruszają się pionowo i używają WIERSZ, ale możesz także przejść poziomo za pomocą funkcji KOLUMNA. Rozważ ten układ:

Chcemy podsumować wszystkie pozycje. Nasz wzór na to może być

1	=PROD.SUMA(B2:E2*(MOD(KOLUMNA(B2:E2); 2)=0)

W tym przypadku łapiemy co 2^NS kolumna w naszym zakresie, więc SUMPRODUCT zachowa tylko wartości niezerowe dla kolumn B i D. Dla odniesienia znajduje się tutaj tabela przedstawiająca numery kolumn i odpowiadające im wartości po pobraniu MOD 2.

Zaznacz każdy N-ty wiersz

Innym powszechnym miejscem użycia funkcji MOD jest sytuacja, w której podświetlony wiersz pojawia się w każdym N-tym wierszu. Ogólna forma tego będzie

1	=MOD(WIERSZ() ± Przesunięcie, N)=0

Gdzie n to liczba rzędów między każdym podświetlonym rzędem (tj. podświetlenie co 3^{r & D} wiersz, N = 3), oraz Zrównoważyć jest opcjonalnie liczbą, którą musimy dodać lub odjąć, aby nasz pierwszy podświetlony wiersz wyrównał się z N (tj. podświetlić co 3^{r & D} wiersz, ale zacznij od wiersza 5, musielibyśmy odjąć 2, ponieważ 5 -2 = 3). Zauważ, że w przypadku funkcji WIERSZ, pomijając dowolne argumenty, zwróci ona numer wiersza z komórki, w której znajduje się formuła.

Skorzystajmy z naszego stołu sprzed lat:

Aby zastosować podświetlenie do wszystkich wierszy Suma, utworzymy nową regułę formatowania warunkowego z formułą

1	=MOD(WIERSZ()-1; 3)=0

Gdy formatowanie warunkowe zastosuje tę formułę, wiersz 2 zobaczy

1234	=MOD(2-1, 3)=0=MOD(1, 3) = 0=1=0=Fałsz

Wiersz 3 otrzyma podobny wynik, ale w wierszu 4 pojawi się

1234	=MOD(4-1, 3)=0=MOD(3, 3) = 0=0=0=Prawda

W ten sposób nasza reguła działa poprawnie, jak pokazano tutaj:

Zaznacz liczby całkowite lub parzyste

Zamiast wyróżniać określone wiersze, możesz również sprawdzić rzeczywiste wartości w komórkach. Może to być przydatne, gdy chcesz znaleźć liczby będące wielokrotnościami N. Na przykład, aby znaleźć wielokrotności 3, formuła formatu warunkowego powinna wyglądać tak:

1	=MOD(A2, 3)=0

Do tego momentu mieliśmy do czynienia z liczbami całkowitymi. Możesz jednak wprowadzić liczbę dziesiętną (np. 1,234), a następnie podzielić przez 1, aby uzyskać tylko część dziesiętną (np. 0,234). Ta formuła wygląda jak

1	=MOD(A2, 1)

Wiedząc o tym, aby wyróżnić tylko liczby całkowite, formuła formatu warunkowego byłaby

1	=MOD(A2, 1)=0

Połącz wszystkie komórki N

Wcześniej korzystaliśmy z MOD, aby informować komputer, kiedy chwycić wartość każdego N-tego przedmiotu. Możesz również użyć go do wywołania większej formuły do wykonania. Rozważ ten układ:

Chcemy łączyć nazwy razem, ale tylko co 3^{r & D} wiersz zaczynający się od wiersza 2. Formuła używana do tego to

1	=JEŻELI(MOD(WIERSZ()+1;3)=0;ZŁĄCZ(A2;"";A3;"";A4);"")

Nasza funkcja MOD działa jako kryterium ogólnej funkcji JEŻELI. W tym przykładzie musieliśmy dodać 1 do naszego ROW, ponieważ zaczynamy od wiersza 2 (2 + 1 = 3). Gdy wyjście MOD ma wartość 0, formuła wykonuje konkatenację. W przeciwnym razie po prostu zwraca puste.

Policz wartości parzyste/nieparzyste

Jeśli kiedykolwiek musiałeś policzyć, ile parzystych lub nieparzystych wartości znajduje się w zakresie, wiesz, że LICZ.JEŻELI nie ma takiej możliwości. Możemy to jednak zrobić za pomocą MOD i SUMPRODUCT. Spójrzmy na tę tabelę:

Formuła, której użyjemy do znalezienia wartości nieparzystych, będzie

1	=PROD.SUMA(1*(MOD(A2:A7;2)=1))

Zamiast ładować niektóre numery wierszy, nasz MOD załaduje rzeczywiste wartości komórek do tablicy. Ogólna transformacja będzie wtedy postępować w następujący sposób:

1234	{5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Wziął mod 2{ Prawda, Prawda, Prawda, Prawda, Fałsz, Prawda } <- Zaznaczone, jeśli wartość wynosiła 0{1, 1, 1, 1, 0, 1} <- pomnożone przez 1, aby przekonwertować z Prawda/Fałsz na 1/0

SUMPRODUCT następnie sumuje wartości w naszej tablicy, dając żądaną odpowiedź: 5.

Powtarzający się wzór

Wszystkie poprzednie przykłady sprawdzały dane wyjściowe MOD pod kątem wartości. Możesz również użyć MOD do wygenerowania powtarzającego się wzoru liczb, co z kolei może być bardzo pomocne.

Najpierw załóżmy, że mieliśmy listę elementów, które chcemy powtórzyć.

Możesz próbować ręcznie kopiować i wklejać tyle razy, ile potrzebujesz, ale byłoby to nużące. Zamiast tego będziemy chcieli użyć funkcji INDEX do pobrania naszych wartości. Aby INDEKS działał, musimy argumentem wiersza być sekwencją liczb {1, 2, 3, 1, 2, 3, 1 itd.}. Możemy to osiągnąć za pomocą MOD.

Najpierw zaczniemy tylko od funkcji WIERSZ. Jeśli zaczniesz od

1	=WIERSZ(A1)

A potem skopiuj to w dół, otrzymasz podstawową sekwencję liczb {1, 2, 3, 4, 5, 6,… }. Jeśli zastosujemy funkcję MOD z 3 jako dzielnikiem,

1	=MOD(WIERSZ(A1); 3)

otrzymalibyśmy {1, 2, 0, 1, 2, 0,… }. Widzimy, że mamy powtarzający się wzór „0, 1, 2”, ale w pierwszej serii brakuje początkowego 0. Aby to naprawić, wykonaj kopię zapasową kroku i odejmij 1 od numeru wiersza. To zmieni naszą sekwencję startową na {0, 1, 2, 3, 4, 5,… }

1	=MOD(WIERSZ(A1)-1; 3)

A po wyjściu z MODu mamy {0, 1, 2, 0, 1, 2,… }. To zbliża się do tego, czego potrzebujemy. Ostatnim krokiem jest dodanie 1 do tablicy.

1	=MOD(WIERSZ(A1)-1; 3)+1

Co teraz daje ciąg liczb {1, 2, 3, 1, 2, 3,… }. To jest nasza pożądana sekwencja! Podłączając go do funkcji INDEX, otrzymujemy naszą formułę

1	=INDEKS(MojaLista; MOD(WIERSZ(A1)-1; 3)+1)

Dane wyjściowe będą teraz wyglądać tak:

Przykłady MOD w VBA

Możesz także użyć funkcji REGLINP w VBA.

W VBA MOD jest operatorem (podobnie jak operatory plus, minus, mnożenia i dzielenia). Tak więc, wykonując następujące instrukcje VBA

123456

Zakres("C2") = Zakres("A2") Mod Zakres("B2")Zakres („C3”) = Zakres („A3”) Zakres modyfikacji („B3”)Zakres("C4") = Zakres("A4") Zakres modyfikacji("B4")Zakres („C5”) = Zakres („A5”) Zakres modyfikacji („B5”)Zakres("C6") = Zakres("A6") Mod Zakres("B6")Zakres („C7”) = Zakres („A7”) Zakres modyfikacji („B7”)

przyniesie następujące wyniki