Preuzmite primjer radne knjige
Ovaj Excel vodič prikazuje kako se koristi Excel MOD funkcija za izračunavanje ostatka nakon dijeljenja.
Pregled MOD funkcije
Funkcija MOD vraća ostatak ili modul broja nakon izvođenja podjele. Međutim, funkcija MOD -a ne služi nam samo za rješavanje problema podjele. Postaje još snažniji kada želimo potražiti svaku N -tu stavku na popisu ili kada moramo generirati ponavljajući uzorak.
Da biste koristili funkciju MODEL Excel radnog lista, odaberite ćeliju i upišite:
Sintaksa i ulazi MOD funkcije:
| 1 | = MOD (broj, djelitelj) |
broj - Broj.
djelitelj - Broj za dijeljenje.
Što je MOD funkcija?
Funkcija MOD vraća ostatak ili modul broja nakon izvođenja podjele. Međutim, funkcija MOD -a ne služi nam samo za rješavanje problema podjele. Postaje još snažniji kada želimo potražiti svaku N -tu stavku na popisu ili kada moramo generirati ponavljajući uzorak.
MOD Osnovna matematika
Kada pokušate podijeliti 13 sa 4, možete reći da je odgovor 3 ostatak 1. "1" je u ovom slučaju posebno poznato kao modul (otuda naziv funkcije MOD). Tada bismo u formulu mogli zapisati
| 1 | = MOD (13, 4) |
I izlaz bi bio 1.
Pogled u ovu tablicu daje još nekoliko ilustracija kako će ulaz/izlaz MOD -a funkcionirati.
| 1 | = MOD (A2,3) |
Imajte na umu da kada je ulaz bio 3, nije bilo ostatka, pa je izlaz formule bio 0. Također, u našoj smo tablici koristili funkciju ROW za generiranje naših vrijednosti. Veći dio moći MOD -a bit će rezultat korištenja funkcije ROW (ili COLUMN) kao što ćemo vidjeti u sljedećim primjerima.
MOD Zbroji svaki drugi redak
Razmotrite ovu tablicu:
Radi ilustracije, drugi stupac ima formulu
| 1 | = MOD (A2, 2) |
Da biste dodali sve parne retke, mogli biste napisati formulu SUMIF i zatražiti da kriteriji traže 0 vrijednosti u stupcu B. Ili, za dodavanje svih neparnih redaka, kriterij treba tražiti 1 vrijednost.
Međutim, uopće ne moramo stvarati pomoćni stupac. Možete kombinirati snagu MOD -a unutar SUMPRODUCT -a kako biste sve učinili u jednom koraku. Naša formula za to bi bila
| 1 | = PODIZVOD (A2: A5, -(MOD (B2: B2, 2) = 0) |
Budući da je unutar SUMPRODUCT -a, funkcija MOD moći će rukovati našim unosom niza. Već smo vidjeli izlaz u pomoćnom stupcu, ali niz iz našeg MOD -a u ovoj formuli bit će {0, 1, 0, 1}. Nakon provjere vrijednosti jednakih 0 primjenom dvostruke unarne, niz će biti {1, 0, 1, 0}. SUMPRODUCT tada čini magiju ili množi nizove kako bi proizveo {2, 0, 4, 0}, a zatim zbrajanjem dobije željeni rezultat od 6.
MOD Zbroji svaki N -ti red
Budući da će formula MOD (x, N) ispisati 0 pri svakoj N -oj vrijednosti, to možemo upotrijebiti za pomoć formulama pri odabiru vrijednosti koje će se koristiti u drugim funkcijama. Pogledajte ovu tablicu.
Naš cilj je prikupiti vrijednosti iz svakog retka s oznakom "Ukupno". Imajte na umu da se ukupni zbroj pojavljuje svaka 3rd redak, ali počevši od reda 4. Naša MOD funkcija će tako koristiti 3 kao 2nd argument, a od prvog argumenta moramo oduzeti 1 (budući da je 4 -1 = 3). Na ovaj način željeni redovi koje želimo (4, 7, 10) bit će višekratnici 3 (3, 6, 9). Naša formula za zbrajanje željenih vrijednosti bit će
| 1 | = PODIZVOD (C2: C10, -(MOD (RED (A2: A10) +2, 3) = 0)) |
Proizvedeni niz će se transformirati ovako:
| 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}{Netačno, lažno, istinito, lažno, lažno, istinito, lažno, lažno, istina}{0, 0, 1, 0, 0, 1, 0, 0, 1} |
Naš niz kriterija našeg SUMPRODUCT -a sada je postavljen tako da moramo ugrabiti svaka 3rd vrijednosti i dobit ćemo željeni rezultat od 90 USD.
Zbir MOD -a za stupce
Do sada smo koristili primjere koji idu okomito i koriste ROW, ali možete ići i vodoravno s funkcijom COLUMN. Razmotrite ovaj raspored:
Želimo sažeti sve stavke. Naša formula za to bi mogla biti
| 1 | = PODIZVOD (B2: E2*(MOD (KOLONA (B2: E2), 2) = 0) |
U ovom slučaju postavljeni smo da preuzimamo svaka 2nd stupca unutar našeg raspona, pa će SUMPRODUCT zadržati samo vrijednosti koje se razlikuju od nule za stupce B & D. Za referencu ovdje je tablica koja prikazuje brojeve stupaca i njihovu odgovarajuću vrijednost nakon uzimanja MOD 2.
Označite svaki N -ti red
Još jedno uobičajeno mjesto za korištenje funkcije MOD je kada želite da se istaknuti redak pojavi svaki N -ti redak. Opći oblik za to će biti
| 1 | = MOD (ROW () ± Odstupanje, N) = 0 |
Gdje N je broj redaka između svakog istaknutog retka (tj. za označavanje svaka 3rd redak, N = 3) i Pomak je izborno broj koji moramo dodati ili oduzeti kako bi se naš prvi istaknuti red poravnao s N (tj. za označavanje svake 3rd redak, ali počnite u retku 5, morali bismo oduzeti 2 budući da je 5 -2 = 3). Imajte na umu da će s funkcijom ROW, izostavljanjem bilo kojih argumenata, vratiti broj retka iz ćelije u kojoj se nalazi formula.
Upotrijebimo našu tablicu od prije:
Da bismo primijenili isticanje na sve retke Ukupno, izradit ćemo novo pravilo uvjetnog oblikovanja s formulom
| 1 | = MOD (ROW ()-1, 3) = 0 |
Kad uvjetno oblikovanje primijeni ovu formulu, vidjet će se redak 2
| 1234 | = MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Netočno |
Red 3 će imati sličan izlaz, ali tada će se vidjeti red 4
| 1234 | = MOD (4-1, 3) = 0= MOD (3, 3) = 0= 0 = 0= Istina |
Dakle, naše pravilo ispravno funkcionira, kao što je prikazano ovdje:
Označite cijele ili parne brojeve
Umjesto isticanja određenih redaka, mogli biste provjeriti i stvarne vrijednosti unutar ćelija. To bi moglo biti korisno kada želite pronaći brojeve koji su višekratnici N. Na primjer, da biste pronašli višekratnike 3, vaša formula uvjetnog formata bila bi
| 1 | = MOD (A2, 3) = 0 |
Do sada smo se bavili cijelim brojevima. Možete unijeti decimalni broj (npr. 1.234), a zatim podijeliti s 1 da biste dobili samo decimalni dio (npr. 0,234). Ova formula izgleda ovako
| 1 | = MOD (A2, 1) |
Znajući to, formula za uvjetni format bila bi za isticanje samo cijelih brojeva
| 1 | = MOD (A2, 1) = 0 |
Spojite svaku N ćeliju
Ranije smo koristili MOD da bismo rekli računalu kada treba uzeti vrijednost za svaku N -tu stavku. Također biste ga mogli upotrijebiti za pokretanje veće formule za izvođenje. Razmotrite ovaj raspored:
Želimo spojiti imena zajedno, ali samo na svaka 3rd redak koji počinje od reda 2. Formula koja se koristi za to je
| 1 | = IF (MOD (ROW ()+1, 3) = 0, CONCATENATE (A2, "", A3, "", A4), "") |
Naša MOD funkcija služi kao kriterij za ukupnu IF funkciju. U ovom primjeru morali smo dodati 1 u naš red, jer počinjemo u 2. retku (2 + 1 = 3). Kada je izlaz MOD -a 0, formula vrši povezivanje. U suprotnom, vraća se samo prazno.
Broji parne/neparne vrijednosti
Ako ste ikada trebali brojati koliko je parnih ili neparnih vrijednosti u rasponu, znat ćete da COUNTIF nema mogućnost to učiniti. To možemo učiniti s MOD -om i SUMPRODUCT -om. Pogledajmo ovu tablicu:
Formula koju ćemo koristiti za pronalaženje neparnih vrijednosti bit će
| 1 | = SUMPRODUCT (1*(MOD (A2: A7, 2) = 1)) |
Umjesto učitavanja u neke brojeve redaka, naš MOD će učitati stvarne vrijednosti ćelija u niz. Cjelokupna transformacija tada će napredovati ovako:
| 1234 | {5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Uzeo način rada 2{True, True, True, True, False, True} <- Provjereno je li vrijednost 0{1, 1, 1, 1, 0, 1} <- pomnoženo s 1 za pretvaranje iz True/False u 1/0 |
SUMPRODUCT zatim zbraja vrijednosti u našem nizu, dajući željeni odgovor od: 5.
Uzorak koji se ponavlja
Svi prethodni primjeri provjeravaju vrijednost MOD -a na vrijednosti. Također možete koristiti MOD za generiranje ponavljajućeg uzorka brojeva, što opet može biti od velike pomoći.
Prvo, recimo da smo imali popis stavki koje želimo ponoviti.
Možete pokušati ručno kopirati i zalijepiti koliko god vam je potrebno, ali to bi postalo zamorno. Umjesto toga, htjet ćemo upotrijebiti funkciju INDEX za dohvaćanje naših vrijednosti. Da bi INDEX radio, potreban nam je argument reda kao niz brojeva koji ide {1, 2, 3, 1, 2, 3, 1 itd.}. To možemo postići pomoću MOD -a.
Prvo ćemo početi samo s funkcijom ROW. Ako počnete s
| 1 | = RED (A1) |
A zatim kopirajte ovo prema dolje, dobit ćete osnovni niz brojeva od {1, 2, 3, 4, 5, 6,…}. Ako smo primijenili funkciju MOD s 3 kao djeliteljem,
| 1 | = MOD (RED (A1), 3) |
dobili bismo {1, 2, 0, 1, 2, 0,…}. Vidimo da imamo ponavljajući uzorak "0, 1, 2", ali prvoj seriji nedostaje početna 0. Da biste to popravili, napravite sigurnosnu kopiju koraka i oduzmite 1 od broja retka. Ovo će promijeniti naš početni niz u {0, 1, 2, 3, 4, 5,…}
| 1 | = MOD (RED (A1) -1, 3) |
A nakon što izađe iz MOD -a, imamo {0, 1, 2, 0, 1, 2,…}. Ovo je sve bliže onome što nam treba. Posljednji korak je dodavanje 1 u polje.
| 1 | = MOD (RED (A1) -1, 3) +1 |
Što sada proizvodi niz brojeva od {1, 2, 3, 1, 2, 3,…}. Ovo je naš željeni slijed! Uključivanjem u funkciju INDEX dobivamo našu formulu
| 1 | = INDEKS (MyList, MOD (ROW (A1) -1, 3) +1) |
Izlaz će sada izgledati ovako:
Primjeri MOD -a u VBA -i
Također možete koristiti funkciju LINEST u VBA.
Unutar VBA -e MOD je operator (baš kao i plus, minus, operatori množenja i dijeljenja). Dakle, izvršavanje sljedećih VBA naredbi
| 123456 | Raspon ("C2") = Raspon ("A2") Mod Raspon ("B2")Raspon ("C3") = Raspon ("A3") Mod Raspon ("B3")Raspon ("C4") = Raspon ("A4") Mod Raspon ("B4")Raspon ("C5") = Raspon ("A5") Mod Raspon ("B5")Raspon ("C6") = Raspon ("A6") Mod Raspon ("B6")Raspon ("C7") = Raspon ("A7") Mod Raspon ("B7") |
će proizvesti sljedeće rezultate
Za argumente funkcije (poznati_y, itd.) Možete ih unijeti izravno u funkciju ili definirati varijable koje ćete umjesto toga koristiti.
Povratak na popis svih funkcija u Excelu
Funkcija MOD -a Google tablica
Funkcija MOD radi potpuno isto u Google tablicama kao i u Excelu:
