✅ Funkcija MOD v Excelu - izračunajte preostanek delitve

Kazalo

Pregled funkcije MOD
Primeri MOD v VBA
Funkcija MOD Google Preglednic

Prenesite primer delovnega zvezka

Ta vadnica za Excel prikazuje, kako uporabljati Excel MOD funkcija izračunati preostanek po deljenju.

Pregled funkcije MOD

Funkcija MOD vrne preostanek ali modul števila po izvedbi delitve. Vendar funkcija MOD ni namenjena izključno temu, da bi nam pomagala pri težavah z delitvijo. Postane še močnejša, ko želimo poiskati vsak N -ti element na seznamu ali ko moramo ustvariti ponavljajoč se vzorec.

Če želite uporabiti funkcijo delovnega lista MOD Excel, izberite celico in vnesite:

Sintaksa in vnosi funkcij MOD:

1	= MOD (število, delitelj)

številko - Številka.

delitelj - Številka za deljenje.

Kaj je funkcija MOD?

MOD Osnovna matematika

Ko poskušate 13 deliti s 4, lahko rečete, da je odgovor 3 ostanek 1. "1" v tem primeru je posebej znan kot modul (zato ime funkcije MOD). V formulo bi torej lahko zapisali

1	= MOD (13, 4)

In rezultat bi bil 1.

Če pogledamo to tabelo, je še nekaj ponazoritev, kako bo deloval vhod/izhod MOD.

1	= MOD (A2,3)

Upoštevajte, da ko je bil vnos 3, ni bilo ostanka, zato je bil izhod formule 0. Prav tako smo v naši tabeli uporabili funkcijo ROW za generiranje naših vrednosti. Velik del moči MOD bo posledica uporabe funkcije ROW (ali COLUMN), kot bomo videli v naslednjih primerih.

MOD Sešteje vsako drugo vrstico

Razmislite o tej tabeli:

Za ponazoritev ima drugi stolpec formulo

1	= MOD (A2, 2)

Če želite dodati vse parne vrstice, lahko napišete formulo SUMIF in merila poiščejo vrednosti 0 v stolpcu B. Ali pa za dodajanje vseh lihih vrstic merila poiščejo 1 vrednost.

Ni nam pa treba ustvariti stolpca za pomoč. Zmogljivost MOD lahko združite v SUMPRODUCT -u, da naredite vse v enem koraku. Naša formula za to bi bila

1	= PODROČJE (A2: A5, -(MOD (B2: B2, 2) = 0)

Ker je v SUMPRODUCT -u, bo funkcija MOD upravljala z vnosom matrike. Izhod smo že videli v pomožnem stolpcu, vendar bo matrika iz našega MOD v tej formuli {0, 1, 0, 1}. Po preverjanju vrednosti, ki so enake 0, z uporabo dvojnega onarja bo matrika {1, 0, 1, 0}. SUMPRODUCT nato naredi magijo ali pomnoži matrike, da ustvari {2, 0, 4, 0} in nato sešteje, da dobi želeni rezultat 6.

MOD Vsota vsaka N -ta vrstica

Ker bo formula MOD (x, N) pri vsaki N -ti vrednosti izpisala 0, lahko to uporabimo za pomoč formulam pri izbiri, katere vrednosti uporabiti v drugih funkcijah. Poglej to tabelo.

Naš cilj je zajeti vrednosti iz vsake vrstice z oznako »Skupaj«. Upoštevajte, da se vsota prikaže vsake 3^rd vrstici, vendar se začne pri vrstici 4. Naša funkcija MOD bo tako uporabila 3 kot 2^nd argument, od prvega argumenta pa moramo odšteti 1 (saj je 4 -1 = 3). Tako bodo želene vrstice, ki jih želimo (4, 7, 10), večkratniki 3 (3, 6, 9). Naša formula za seštevanje želenih vrednosti bo

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

Proizvedeno polje se bo spremenilo tako:

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}{False, False, True, False, False, True, False, False, True}{0, 0, 1, 0, 0, 1, 0, 0, 1}

Niz meril našega izdelka SUMPRODUCT je zdaj nastavljen tako, da ga moramo vzeti vsake 3^rd vrednost in dobili bomo želeni rezultat 90 USD.

MOD Vsota o stolpcih

Doslej smo uporabljali primere, ki gredo navpično in uporabljajo ROW, lahko pa tudi vodoravno s funkcijo COLUMN. Razmislite o tej postavitvi:

Želimo povzeti vse postavke. Naša formula za to bi lahko bila

1	= PODROČJE (B2: E2*(MOD (STOLPEC (B2: E2), 2) = 0)

V tem primeru smo nastavljeni na vsaka 2^nd stolpec v našem obsegu, zato bo SUMPRODUCT ohranil samo vrednosti, ki niso ničelne, za stolpca B & D. Za referenco je tukaj tabela, ki prikazuje številke stolpcev in njihovo ustrezno vrednost po prevzemu MOD 2.

Označite vsako N -to vrstico

Drug pogost kraj uporabe funkcije MOD je, če želite, da se označena vrstica pojavi v vsaki N -ti vrstici. Splošna oblika tega bo

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

Kje N je število vrstic med vsako označeno vrstico (tj. označiti vsake 3^rd vrstica, N = 3) in Odmik je po izbiri število, ki ga moramo dodati ali odšteti, da se prva označena vrstica poravna z N (tj., da označi vsake 3^rd vrstici, vendar začnite pri vrstici 5, morali bi odšteti 2, ker je 5 -2 = 3). Upoštevajte, da bo s funkcijo ROW izpustitev argumentov vrnila številko vrstice iz celice, v kateri je formula.

Uporabimo našo tabelo od prej:

Če želite označiti vse vrstice Skupaj, bomo ustvarili novo pravilo pogojnega oblikovanja s formulo

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

Ko pogojno oblikovanje uporabi to formulo, se prikaže vrstica 2

1234	= MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Napačno

Vrstica 3 bo imela podoben rezultat, nato pa bo prikazana vrstica 4

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

Tako naše pravilo deluje pravilno, kot je prikazano tukaj:

Označite cela ali parna števila

Namesto označevanja določenih vrstic lahko preverite tudi dejanske vrednosti v celicah. To je lahko koristno, če želite najti številke, ki so večkratniki N. Na primer, če želite poiskati večkratnike 3, bi bila vaša formula pogojne oblike

1	= MOD (A2, 3) = 0

Do tega trenutka smo se ukvarjali s celimi številkami. Lahko pa vnesete decimalko (npr. 1.234) in jo nato delite z 1, da dobite le decimalni del (npr. 0,234). Ta formula izgleda tako

1	= MOD (A2, 1)

Če vemo, da bi bila za označevanje samo celih številk formula pogojne oblike

1	= MOD (A2, 1) = 0

Združite vsakih N celic

Prej smo uporabljali MOD, da smo računalniku povedali, kdaj naj pri vsaki N -ti postavki zgrabi vrednost. Lahko bi ga uporabili tudi za sprožitev večje formule. Razmislite o tej postavitvi:

Imena želimo združiti skupaj, vendar le na vsake 3^rd vrstica, ki se začne v 2. vrstici. Za to je uporabljena formula

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

Naša funkcija MOD je merilo za celotno funkcijo IF. V tem primeru smo morali v svoj vrstni red dodati 1, ker začenjamo pri 2. vrstici (2 + 1 = 3). Ko je izhod MOD -a 0, formula poveže. V nasprotnem primeru se vrne samo prazno.

Štejte parne/lihe vrednosti

Če ste kdaj morali šteti, koliko je parnih ali lihih vrednosti v razponu, boste vedeli, da COUNTIF tega ne zmore. To lahko storimo z MOD in SUMPRODUCT. Poglejmo to tabelo:

Formula, ki jo bomo uporabili za iskanje lihih vrednosti, bo

1	= PODROČJE (1*(MOD (A2: A7, 2) = 1))

Namesto nalaganja nekaterih številk vrstic bo naš MOD naložil dejanske vrednosti celic v matriko. Celotna preobrazba bo nato napredovala takole:

1234	{5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- sprejel način 2{True, True, True, True, False, True} <- Preverjeno, če je bila vrednost 0{1, 1, 1, 1, 0, 1} <- pomnoženo z 1 za pretvorbo iz True/False v 1/0

SUMPRODUCT nato sešteje vrednosti v našem nizu in daje želeni odgovor: 5.

Ponavljajoči se vzorec

Vsi prejšnji primeri so preverjali vrednost izhoda MOD. MOD lahko uporabite tudi za ustvarjanje ponavljajočega se vzorca številk, kar je lahko v veliko pomoč.

Najprej recimo, da smo imeli seznam elementov, ki jih želimo ponoviti.

Poskusite lahko ročno kopirati in prilepiti, kolikorkrat potrebujete, vendar bo to postalo dolgočasno. Namesto tega bomo želeli uporabiti funkcijo INDEX za pridobivanje naših vrednosti. Če želimo, da indeks deluje, potrebujemo argument vrstice kot zaporedje števil, ki gre za {1, 2, 3, 1, 2, 3, 1 itd.}. To lahko dosežemo z uporabo MOD.

Najprej bomo začeli samo s funkcijo ROW. Če začnete s

1	= ROW (A1)

Kopirajte to navzdol in dobite osnovno zaporedje številk {1, 2, 3, 4, 5, 6,…}. Če bi uporabili funkcijo MOD s 3 kot deliteljem,

1	= MOD (ROW (A1), 3)

dobili bi {1, 2, 0, 1, 2, 0,…}. Vidimo, da imamo ponavljajoč se vzorec »0, 1, 2«, vendar v prvi seriji manjka začetnih 0. Če želite to popraviti, naredite varnostno kopijo koraka in odštejte 1 od številke vrstice. To bo spremenilo naše začetno zaporedje v {0, 1, 2, 3, 4, 5,…}

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

In ko pride iz MOD, imamo {0, 1, 2, 0, 1, 2,…}. To se približuje temu, kar potrebujemo. Zadnji korak je, da v polje dodate 1.

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

Kar zdaj proizvaja številsko zaporedje {1, 2, 3, 1, 2, 3,…}. To je naše želeno zaporedje! Če ga vključimo v funkcijo INDEX, dobimo našo formulo

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

Izhod bo zdaj videti tako:

Primeri MOD v VBA

V VBA lahko uporabite tudi funkcijo LINEST.

Znotraj VBA je MOD operator (tako kot operatorji plus, minus, množenje in deljenje). Torej, izvajanje naslednjih stavkov VBA

123456

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

bo prinesel naslednje rezultate