Stiahnite si ukážkový zošit
Tento výukový program Excel ukazuje, ako používať Funkcia Excel MOD na výpočet zvyšku po rozdelení.
Prehľad funkcií MOD
Funkcia MOD vráti zvyšok alebo modul čísla po vykonaní delenia. Funkcia MOD však nie je výhradne na to, aby nám pomohla s našimi problémami s delením. Je to ešte výkonnejšie, keď chceme hľadať každú N -tú položku v zozname, alebo keď potrebujeme vygenerovať opakujúci sa vzor.
Ak chcete použiť funkciu pracovného hárka MOD Excel, vyberte bunku a zadajte:
Syntax a vstupy funkcií MOD:
| 1 | = MOD (číslo, deliteľ) |
číslo - Číslo.
deliteľ - Číslo na delenie.
Čo je to funkcia MOD?
Funkcia MOD vráti zvyšok alebo modul čísla po vykonaní delenia. Funkcia MOD však nie je výhradne na to, aby nám pomohla s našimi problémami s delením. Je to ešte výkonnejšie, keď chceme hľadať každú N -tú položku v zozname, alebo keď potrebujeme vygenerovať opakujúci sa vzor.
MOD Základná matematika
Keď sa pokúsite rozdeliť 13 na 4, môžete povedať, že odpoveď je 3 zvyšok 1. „1“ je v tomto prípade konkrétne známy ako modul (odtiaľ názov funkcie MOD). Vo vzorci by sme teda mohli písať
| 1 | = MOD (13, 4) |
A výstup by bol 1.
Pohľad na túto tabuľku poskytuje ďalšie ilustrácie, ako bude fungovať vstup/výstup MOD.
| 1 | = MOD (A2,3) |
Všimnite si toho, že keď bol vstup 3, nezostal žiaden zvyšok, a teda výstup vzorca bol 0. V našej tabuľke sme na generovanie našich hodnôt použili aj funkciu ROW. Veľká časť výkonu MOD bude z používania funkcie ROW (alebo COLUMN), ako uvidíme v nasledujúcich príkladoch.
MOD Sčítajte každý druhý riadok
Zvážte túto tabuľku:
Na ilustračné účely má druhý stĺpec vzorec
| 1 | = MOD (A2, 2) |
Ak chcete pridať všetky párne riadky, môžete napísať vzorec SUMIF a nechať kritériá vyhľadať 0 hodnôt v stĺpci B. Alebo ak chcete pridať všetky nepárne riadky, musíte zadať kritériá na vyhľadanie 1 hodnôt.
Vôbec však nepotrebujeme vytvárať pomocný stĺpec. Môžete kombinovať silu MOD v rámci SUMPRODUCT a urobiť to všetko v jednom kroku. Náš vzorec na to by bol
| 1 | = SUMPRODUCT (A2: A5, -(MOD (B2: B2, 2) = 0) |
Pretože je to v rámci SUMPRODUCT, funkcia MOD bude schopná zvládnuť náš vstup do poľa. Výstup sme už videli v pomocnom stĺpci, ale pole z nášho MOD v tomto vzorci bude {0, 1, 0, 1}. Po kontrole hodnôt, ktoré sa rovnajú 0, použitím dvojitého unárneho poľa bude pole {1, 0, 1, 0}. SUMPRODUCT potom urobí kúzlo alebo vynásobením polí vytvorí {2, 0, 4, 0} a potom sčítaním získa požadovaný výstup 6.
MOD Súčet každý N -tý riadok
Pretože vzorec MOD (x, N) bude produkovať 0 pri každej N -tej hodnote, môžeme to použiť na pomoc vzorcom pri výbere a výbere hodnôt, ktoré sa majú použiť v iných funkciách. Pozrite sa na túto tabuľku.
Našim cieľom je získať hodnoty z každého riadka označeného ako „Celkom“. Upozorňujeme, že súčet sa zobrazuje každé 3rd riadok, ale začína v riadku 4. Naša funkcia MOD bude teda používať 3 ako 2nd argument, a musíme od prvého argumentu odpočítať 1 (od 4 -1 = 3). Takto požadované riadky (4, 7, 10) budú násobkami 3 (3, 6, 9). Náš vzorec na súčet požadovaných hodnôt bude
| 1 | = SUMPRODUCT (C2: C10, -(MOD (RIADOK (A2: A10) +2, 3) = 0)) |
Vytvorené pole sa transformuje takto:
| 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} |
Naše pole kritérií nášho SUMPRODUCT je teraz nastavené tak, ako musíme chytiť každé 3rd hodnotu a získame požadovaný výsledok 90 dolárov.
Súčet MOD na stĺpcoch
Doteraz používame príklady, ktoré idú zvisle a používajú ROW, ale pomocou funkcie COLUMN môžete ísť aj vodorovne. Zvážte toto rozloženie:
Chceme zhrnúť všetky položky. Náš vzorec na to môže byť
| 1 | = SUMPRODUCT (B2: E2*(MOD (STĹPEC (B2: E2), 2) = 0) |
V tomto prípade sme pripravení chytiť každé 2nd stĺpec v našom rozsahu, takže SUMPRODUCT zachová nenulové hodnoty iba pre stĺpce B a D. Na porovnanie je tu tabuľka zobrazujúca čísla stĺpcov a ich zodpovedajúce hodnoty po použití MOD 2.
Zvýraznite každý N -tý riadok
Ďalším bežným miestom použitia funkcie MOD je, keď chcete, aby sa zvýraznený riadok zobrazil v každom N -tom riadku. Obecný formulár bude
| 1 | = MOD (ROW () ± offset, N) = 0 |
Kde N. je počet riadkov medzi každým zvýrazneným riadkom (tj. aby sa zvýraznili každé 3rd riadok, N = 3), a Ofset je voliteľne číslo, ktoré musíme sčítať alebo odčítať, aby sa náš prvý zvýraznený riadok zarovnal s N (tj. aby sa zvýraznili každé 3rd riadok, ale začínajúc v riadku 5, museli by sme odčítať 2, pretože 5 -2 = 3). Všimnite si toho, že s funkciou ROW, vynechaním akýchkoľvek argumentov, vráti číslo riadka z bunky, v ktorej je vzorec.
Použime našu tabuľku predtým:
Aby sme zvýraznili všetky riadky súčtu, vytvoríme nové pravidlo podmieneného formátovania so vzorcom
| 1 | = MOD (RIADOK ()-1, 3) = 0 |
Keď podmienené formátovanie použije tento vzorec, zobrazí sa riadok 2
| 1234 | = MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Nepravda |
Riadok 3 zažije podobný výstup, ale potom riadok 4 uvidí
| 1234 | = MOD (4-1, 3) = 0= MOD (3, 3) = 0= 0 = 0= Pravda |
Naše pravidlo teda funguje správne, ako je uvedené tu:
Zvýraznite celé čísla alebo párne čísla
Namiesto zvýraznenia konkrétnych riadkov by ste mohli skontrolovať aj skutočné hodnoty v bunkách. To môže byť užitočné vtedy, ak chcete nájsť čísla, ktoré sú násobkami N. Napríklad na nájdenie násobkov 3 bude váš vzorec podmieneného formátu
| 1 | = MOD (A2, 3) = 0 |
Až do tohto bodu sme sa zaoberali celými číslami. Môžete však zadať desatinné miesto (napr. 1,234) a potom deliť 1, aby ste získali iba desatinnú časť (napr. 0,234). Tento vzorec vyzerá
| 1 | = MOD (A2, 1) |
Vedieť, že na zvýraznenie iba celých čísel by bol vzorec podmieneného formátu
| 1 | = MOD (A2, 1) = 0 |
Spojte každých N buniek
Predtým sme pomocou MOD informovali počítač, kedy má získať hodnotu pri každej N -tej položke. Môžete ho tiež použiť na spustenie väčšieho vzorca na vykonanie. Zvážte toto rozloženie:
Chceme spojiť mená spoločne, ale iba každé 3rd riadok začínajúci riadkom 2. Vzorec, ktorý sa na to používa, je
| 1 | = IF (MOD (ROW ()+1, 3) = 0, CONCATENATE (A2, "", A3, "", A4), "") |
Naša funkcia MOD slúži ako kritérium pre celkovú funkciu IF. V tomto prípade sme do riadku potrebovali pridať 1, pretože začíname v riadku 2 (2 + 1 = 3). Keď je výkon MOD 0, vzorec vykoná zreťazenie. V opačnom prípade sa vráti iba prázdne.
Spočítajte párne/nepárne hodnoty
Ak ste niekedy potrebovali spočítať, koľko párnych alebo nepárnych hodnôt je v rozsahu, budete vedieť, že COUNTIF to nedokáže. Môžeme to však urobiť s MOD a SUMPRODUCT. Pozrime sa na túto tabuľku:
Vzorec, ktorý použijeme na nájdenie nepárnych hodnôt, bude
| 1 | = SUMPRODUCT (1*(MOD (A2: A7, 2) = 1)) |
Namiesto načítania niektorých čísel riadkov náš MOD načítava skutočné hodnoty buniek do poľa. Celková transformácia potom bude prebiehať takto:
| 1234 | {5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Použil režim 2{True, True, True, True, False, True} <- Skontrolovalo sa, či bola hodnota 0{1, 1, 1, 1, 0, 1} <- vynásobené 1 na konverziu z True/False na 1/0 |
SUMPRODUCT potom sčíta hodnoty v našom poli a poskytne požadovanú odpoveď: 5.
Opakujúci sa vzor
Všetky predchádzajúce príklady kontrolovali výstup MOD na hodnotu. MOD môžete použiť aj na generovanie opakujúceho sa vzoru čísel, čo môže byť zase veľmi užitočné.
Najprv si povedzme, že sme mali zoznam položiek, ktoré chceme zopakovať.
Môžete to skúsiť manuálne kopírovať a prilepiť, koľkokrát potrebujete, ale bolo by to únavné. Namiesto toho budeme chcieť použiť funkciu INDEX na získanie našich hodnôt. Na to, aby INDEX fungoval, potrebujeme, aby argumentom riadka bola postupnosť čísel, ktoré idú {1, 2, 3, 1, 2, 3, 1 atď.}. Môžeme to dosiahnuť pomocou MOD.
Najprv začneme iba s funkciou ROW. Ak začnete s
| 1 | = RAD (A1) |
A potom to skopírujte nadol, získate základnú postupnosť čísel {1, 2, 3, 4, 5, 6, …}. Ak by sme použili funkciu MOD s deliteľom 3,
| 1 | = MOD (RIADOK (A1), 3) |
dostali by sme {1, 2, 0, 1, 2, 0, …}. Vidíme, že máme opakujúci sa vzor „0, 1, 2“, ale v prvej sérii chýba počiatočná 0. Na vyriešenie tohto problému urobte krok späť a od čísla riadku odčítajte 1. Tým sa zmení naša počiatočná sekvencia na {0, 1, 2, 3, 4, 5, …}
| 1 | = MOD (RIADOK (A1) -1, 3) |
A potom, čo vyjde z MOD, máme {0, 1, 2, 0, 1, 2, …}. Toto sa blíži k tomu, čo potrebujeme. Posledným krokom je pridanie 1 do poľa.
| 1 | = MOD (RIADOK (A1) -1, 3) +1 |
Teraz sa vytvorí číselná postupnosť {1, 2, 3, 1, 2, 3, …}. Toto je naša požadovaná sekvencia! Po zapojení do funkcie INDEX získame vzorec
| 1 | = INDEX (MyList, MOD (RIADOK (A1) -1, 3) +1) |
Výstup bude teraz vyzerať takto:
Príklady MOD vo VBA
Vo VBA môžete použiť aj funkciu LINEST.
V rámci VBA je MOD operátorom (rovnako ako operátori plus, mínus, násobenie a delenie). Vykonaním nasledujúcich vyhlásení VBA
| 123456 | Rozsah ("C2") = Rozsah ("A2") Mod Range ("B2")Rozsah ("C3") = Rozsah ("A3") Rozsah rozsahu ("B3")Rozsah („C4“) = Rozsah („A4“) Rozsah rozsahu („B4“)Range ("C5") = Range ("A5") Mod Range ("B5")Rozsah ("C6") = Rozsah ("A6") Mod Range ("B6")Rozsah („C7“) = Rozsah („A7“) Rozsah rozsahu („B7“) |
prinesie nasledujúce výsledky
Pokiaľ ide o argumenty funkcie (známe_roky atď.), Môžete ich buď zadať priamo do funkcie, alebo definovať premenné, ktoré sa namiesto toho použijú.
Vráťte sa do zoznamu všetkých funkcií v programe Excel
Funkcia MOD v Tabuľkách Google
Funkcia MOD funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
