✅ Funcția MOD în Excel - Calculează restul de diviziune

Cuprins

Prezentare generală a funcției MOD
Exemple MOD în VBA
Funcția MOD Foi de calcul Google

Descărcați Exemplu de registru de lucru

Descărcați exemplul de registru de lucru

Acest tutorial Excel arată cum să utilizați Funcția MOD Excel pentru a calcula restul după împărțire.

Prezentare generală a funcției MOD

Funcția MOD returnează restul sau modulul unui număr după efectuarea împărțirii. Cu toate acestea, funcția MOD nu este strict pentru a ne ajuta cu problemele noastre de diviziune. Devine și mai puternic atunci când vrem să căutăm fiecare al N-lea element dintr-o listă sau când trebuie să generăm un model care se repetă.

Pentru a utiliza funcția Foaie de lucru MOD Excel, selectați celula și tastați:

Sintaxa și intrările funcției MOD:

1	= MOD (număr, divizor)

număr - Un număr.

divizor - Un număr de împărțit la.

Ce este funcția MOD?

MOD Matematică de bază

Când încercați să împărțiți 13 la 4, puteți spune că răspunsul este 3 restul 1. „1” în acest caz este cunoscut în mod specific ca modulul (de aici și numele funcției MOD). Într-o formulă, am putea scrie

1	= MOD (13, 4)

Iar rezultatul ar fi 1.

Privind la acest tabel sunt oferite câteva ilustrații despre modul în care funcționează intrarea / ieșirea MOD.

1	= MOD (A2,3)

Rețineți că atunci când intrarea a fost 3, nu a existat rest și, astfel, ieșirea formulei a fost 0. De asemenea, în tabelul nostru am folosit funcția ROW pentru a genera valorile noastre. O mare parte din puterea MOD va fi folosirea funcției ROW (sau COLUMN), așa cum vom vedea în exemplele următoare.

MOD Sumați la fiecare rând

Luați în considerare acest tabel:

În scop ilustrativ, a doua coloană are formula

1	= MOD (A2, 2)

Pentru a adăuga toate rândurile pare, ați putea scrie o formulă SUMIF și a avea criteriile să caute 0 valori în col B. Sau, pentru a adăuga toate rândurile impare, criteriile trebuie să fie să căutați 1 valori.

Cu toate acestea, nu este nevoie să creăm deloc coloana asistentă. Puteți combina puterea MOD în SUMPRODUCT pentru a face totul într-un singur pas. Formula noastră pentru acest lucru ar fi

1	= SUMPRODUCT (A2: A5, - (MOD (B2: B2, 2) = 0)

Deoarece se află în SUMPRODUCT, funcția MOD va putea gestiona intrarea matricei noastre. Am văzut ieșirea deja în coloana de asistență, dar matricea din MOD-ul nostru din această formulă va fi {0, 1, 0, 1}. După verificarea valorilor egale cu 0 aplicând unarul dublu, matricea va fi {1, 0, 1, 0}. SUMPRODUCT face apoi magie sau înmulțind matricele pentru a produce {2, 0, 4, 0} și apoi însumând pentru a obține rezultatul dorit de 6.

Suma MOD fiecare rând al N-lea

Deoarece o formulă MOD (x, N) va produce un 0 la fiecare valoare N, putem folosi acest lucru pentru a ajuta formulele să aleagă și să aleagă ce valori să utilizeze în alte funcții. Uită-te la acest tabel.

Scopul nostru este să obținem valorile din fiecare rând marcat „Total”. Rețineți că Totalul apare la fiecare 3^rd rând, dar începând cu rândul 4. Funcția noastră MOD va folosi astfel 3 ca 2^nd argument și trebuie să scădem 1 din primul argument (deoarece 4 -1 = 3). În acest fel rândurile dorite pe care le dorim (4, 7, 10) vor fi multipli de 3 (3, 6, 9). Formula noastră pentru a însuma valorile dorite va fi

1	= SUMPRODUCT (C2: C10, - (MOD (ROW (A2: A10) +2, 3) = 0))

Matricea produsă se va transforma astfel:

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}{Fals, Fals, Adevărat, Fals, Fals, Adevărat, Fals, Fals, Adevărat}{0, 0, 1, 0, 0, 1, 0, 0, 1}

Matricea de criterii SUMPRODUCT este acum configurată cum trebuie să luăm fiecare 3^rd valoare și vom obține rezultatul dorit de 90 USD.

Suma MOD pe coloane

Până acum am folosit exemple care merg pe verticală și folosesc ROW, dar puteți merge și pe orizontală cu funcția COLUMN. Luați în considerare acest aspect:

Vrem să rezumăm toate articolele. Formula noastră pentru acest lucru ar putea fi

1	= SUMPRODUCT (B2: E2 * (MOD (COLUMN (B2: E2), 2) = 0)

În acest caz, suntem pregătiți să apucăm la fiecare 2^nd coloană din intervalul nostru, deci SUMPRODUCT va păstra valori diferite de zero pentru coloanele B și D. Pentru referință, iată un tabel care arată numerele coloanelor și valoarea lor corespunzătoare după ce ați luat MOD 2.

Evidențiați fiecare rând al N-lea

Un alt loc obișnuit pentru a utiliza funcția MOD este atunci când doriți să apară un rând evidențiat la fiecare al N-lea rând. Forma generală pentru aceasta va fi

1	= MOD (ROW () ± Offset, N) = 0

Unde N este numărul de rânduri dintre fiecare rând evidențiat (adică, pentru a evidenția la fiecare 3^rd rând, N = 3) și Decalaj este opțional numărul pe care trebuie să-l adăugăm sau să scădem pentru a obține primul rând evidențiat care să se alinieze cu N (adică, pentru a evidenția fiecare 3)^rd rând, dar începem de la rândul 5, ar trebui să scădem 2 deoarece 5 -2 = 3). Rețineți că, cu funcția ROW, omițând orice argumente, va returna numărul rândului din celula în care se află formula.

Să folosim tabelul nostru dinainte:

Pentru a aplica o evidențiere tuturor rândurilor totale, vom crea o nouă regulă de formatare condiționată cu o formulă de

1	= MOD (ROW () - 1, 3) = 0

Când formatarea condițională aplică această formulă, rândul 2 va vedea

1234	= MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Fals

Rândul 3 va avea o ieșire similară, dar apoi rândul 4 va vedea

1234	= MOD (4-1, 3) = 0= MOD (3, 3) = 0= 0 = 0= Adevărat

Astfel, regula noastră funcționează corect, după cum se arată aici:

Evidențiați numerele întregi sau numerele pare

În loc să evidențiați anumite rânduri, puteți verifica, de asemenea, valorile reale din celule. Acest lucru ar putea fi util atunci când doriți să găsiți numere care sunt multipli de N. De exemplu, pentru a găsi multipli de 3

1	= MOD (A2, 3) = 0

Până în prezent, am avut de-a face cu numere întregi. Cu toate acestea, puteți avea o intrare a unei zecimale (de exemplu, 1,234) și apoi împărțiți la 1 pentru a obține doar porțiunea zecimală (de exemplu, 0,234). Această formulă arată

1	= MOD (A2, 1)

Știind că, pentru a evidenția doar numere întregi, ar fi formula formatului condițional

1	= MOD (A2, 1) = 0

Concatenează fiecare N celulă

Am folosit anterior MOD pentru a spune computerului când trebuie să obțină valoarea la fiecare al N-lea articol. Ați putea fi, de asemenea, folosit pentru a declanșa o formulă mai mare de executat. Luați în considerare acest aspect:

Vrem să concatenăm numele împreună, dar numai la fiecare 3^rd rând care începe pe rândul 2. Formula utilizată pentru aceasta este

1	= IF (MOD (ROW () + 1, 3) = 0, CONCATENATE (A2, "", A3, "", A4), "")

Funcția noastră MOD este ceea ce acționează ca criterii pentru funcția IF generală. În acest exemplu, a trebuit să adăugăm 1 la ROW, deoarece începem de la rândul 2 (2 + 1 = 3). Când ieșirea MOD este 0, formula face concatenarea. În caz contrar, revine doar gol.

Numărați valorile pare / impare

Dacă ați avut vreodată nevoie să numărați câte valori pare sau impare sunt într-un interval, veți ști că COUNTIF nu are capacitatea de a face acest lucru. O putem face cu MOD și SUMPRODUCT totuși. Să vedem acest tabel:

Formula pe care o vom folosi pentru a găsi valorile impare va fi

1	= SUMPRODUCT (1 * (MOD (A2: A7, 2) = 1))

Mai degrabă decât încărcarea în unele numere de rând, MOD-ul nostru va încărca valorile celulelor reale în matrice. Transformarea generală va progresa astfel:

1234	{5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- A luat modul 2{Adevărat, adevărat, adevărat, adevărat, fals, adevărat} <- Verificat dacă valoarea a fost 0{1, 1, 1, 1, 0, 1} <- Înmulțit cu 1 pentru a converti de la Adevărat / Fals la 1/0

SUMPRODUCT adaugă apoi valorile din matricea noastră, oferind răspunsul dorit de: 5.

Tipar de repetare

Toate exemplele anterioare au verificat ieșirea MOD pentru o valoare. De asemenea, puteți utiliza MOD pentru a genera un model de numere repetate, care la rândul său poate fi foarte util.

În primul rând, să spunem că am avut o listă de articole pe care le dorim repetate.

Puteți încerca să copiați și să inserați manual oricâte ori aveți nevoie, dar asta ar deveni plictisitor. În schimb, vom dori să folosim funcția INDEX pentru a ne recupera valorile. Pentru ca INDEX să funcționeze, avem nevoie ca argumentul rând să fie o succesiune de numere care merge {1, 2, 3, 1, 2, 3, 1 etc.}. Putem realiza acest lucru folosind MOD.

În primul rând, vom începe doar cu funcția ROW. Dacă începi cu

1	= ROW (A1)

Și apoi copiați acest lucru în jos, veți obține secvența numerică de bază a {1, 2, 3, 4, 5, 6, …}. Dacă am aplicat funcția MOD cu 3 ca divizor,

1	= MOD (ROW (A1), 3)

am primi {1, 2, 0, 1, 2, 0, …}. Vedem că avem un tipar care se repetă „0, 1, 2”, dar din prima serie lipsește inițialul 0. Pentru a remedia acest lucru, faceți o copie de rezervă și scădeți 1 din numărul rândului. Aceasta va schimba secvența de pornire la {0, 1, 2, 3, 4, 5, …}

1	= MOD (ROW (A1) -1, 3)

Și după ce iese din MOD, avem {0, 1, 2, 0, 1, 2, …}. Acest lucru se apropie de ceea ce avem nevoie. Ultimul pas este să adăugați 1 la matrice.

1	= MOD (ROW (A1) -1, 3) +1

Care produce acum o secvență numerică de {1, 2, 3, 1, 2, 3, …}. Aceasta este secvența noastră dorită! Conectându-l la o funcție INDEX, obținem formula noastră de

1	= INDEX (MyList, MOD (ROW (A1) -1, 3) +1)

Ieșirea va arăta astfel:

Exemple MOD în VBA

De asemenea, puteți utiliza funcția LINEST în VBA.

În cadrul VBA, MOD este un operator (la fel ca operatorii de plus, minus, multiplicare și divizare). Deci, executând următoarele instrucțiuni VBA

123456

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

va produce următoarele rezultate