✅ Funkce MOD v aplikaci Excel - Výpočet zbytku divize

Obsah

Přehled funkcí MOD
Příklady MOD ve VBA
Funkce MOD Tabulek Google

Stáhněte si ukázkový sešit

Tento výukový program Excel ukazuje, jak používat Funkce Excel MOD pro výpočet zbytku po rozdělení.

Přehled funkcí MOD

Funkce MOD vrací zbytek nebo modul čísla po provedení dělení. Funkce MOD však není výhradně pro to, aby nám pomohla s problémy s dělením. Je ještě výkonnější, když chceme hledat každou N -tu položku v seznamu, nebo když potřebujeme generovat opakující se vzor.

Chcete -li použít funkci sešitu MOD Excel, vyberte buňku a zadejte:

Syntaxe a vstupy funkcí MOD:

1	= MOD (číslo, dělitel)

číslo - Číslo.

dělitel - Číslo k dělení.

Co je to funkce MOD?

MOD Základní matematika

Když se pokusíte rozdělit 13 na 4, můžete říci, že odpověď je 3 zbytek 1. „1“ je v tomto případě konkrétně znám jako modul (odtud název funkce MOD). Ve vzorci bychom tedy mohli psát

1	= MOD (13, 4)

A výstup by byl 1.

Pohled na tuto tabulku poskytuje další ilustrace, jak bude fungovat vstup/výstup MOD.

1	= MOD (A2,3)

Všimněte si, že když byl vstup 3, nebyl žádný zbytek, a proto byl výstup vzorce 0. V naší tabulce jsme také pro generování našich hodnot použili funkci ROW. Velká část výkonu MOD bude z používání funkce ROW (nebo COLUMN), jak uvidíme v následujících příkladech.

MOD Součet každý druhý řádek

Zvažte tuto tabulku:

Druhý sloupec má pro ilustraci vzorec

1	= MOD (A2, 2)

Chcete -li přidat všechny sudé řádky, můžete napsat vzorec SUMIF a nechat kritéria hledat 0 hodnot ve sloupci B. Nebo chcete -li přidat všechny liché řádky, musíte mít kritéria hledat 1 hodnoty.

Vůbec nepotřebujeme vytvářet pomocný sloupec. Můžete kombinovat sílu MOD v rámci SUMPRODUCT, abyste to všechno udělali v jednom kroku. Náš vzorec pro to by byl

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

Protože je to v rámci SUMPRODUCT, funkce MOD bude schopna zpracovat náš vstup do pole. Viděli jsme výstup již ve pomocném sloupci, ale pole z našeho MOD v tomto vzorci bude {0, 1, 0, 1}. Po kontrole hodnot, které se rovnají 0 při použití dvojité unární hodnoty, bude pole {1, 0, 1, 0}. SUMPRODUCT pak kouzlí nebo vynásobením polí vytvoří {2, 0, 4, 0} a sečtením získá požadovaný výstup 6.

MOD Součet každý N -tý řádek

Protože vzorec MOD (x, N) vygeneruje 0 při každé N -té hodnotě, můžeme to použít k tomu, abychom vzorcům pomohli vybrat a vybrat hodnoty, které se mají použít v jiných funkcích. Podívejte se na tuto tabulku.

Naším cílem je získat hodnoty z každého řádku označeného „Celkem“. Všimněte si, že součet se zobrazuje každé 3^rd řádek, ale začíná na řádku 4. Naše funkce MOD tedy použije 3 jako 2^nd argument, a musíme odečíst 1 od prvního argumentu (od 4 -1 = 3). Tímto způsobem požadované řádky, které chceme (4, 7, 10), budou násobky 3 (3, 6, 9). Náš vzorec pro součet požadovaných hodnot bude

1	= SUMPRODUKT (C2: C10, -(MOD (ŘADA (A2: A10) +2, 3) = 0))

Vytvořené pole se 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}

Pole kritérií našeho SUMPRODUCT nyní nastavuje, jak musíme chytit každé 3^rd hodnotu, a dostaneme požadovaný výsledek 90 $.

Součet MOD na sloupcích

Doposud jsme používali příklady, které jdou svisle a používají ROW, ale můžete také jít vodorovně s funkcí COLUMN. Zvažte toto rozložení:

Chceme shrnout všechny položky. Náš vzorec pro to by mohl být

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

V tomto případě jsme připraveni chytit každé 2^nd sloupec v našem rozsahu, takže SUMPRODUCT zachová nenulové hodnoty pouze pro sloupce B & D. Pro referenci je zde tabulka ukazující čísla sloupců a jejich odpovídající hodnoty po převzetí MOD 2.

Zvýrazněte každý N -tý řádek

Dalším běžným místem pro použití funkce MOD je, když chcete, aby se zvýrazněný řádek objevil v každém N -tom řádku. Obecná forma bude

1	= MOD (ŘÁDEK () ± Ofset, N) = 0

Kde N. je počet řádků mezi každým zvýrazněným řádkem (tj. pro zvýraznění každé 3^rd řádek, N = 3), a Ofset je volitelně číslo, které potřebujeme přidat nebo odečíst, aby se náš první zvýrazněný řádek zarovnal s N (tj. zvýraznit každé 3^rd řádek, ale začínáme na řádku 5, potřebovali bychom odečíst 2, protože 5 -2 = 3). Všimněte si, že s funkcí ROW, vynecháním jakýchkoli argumentů, vrátí číslo řádku z buňky, ve které je vzorec.

Použijme naši tabulku dříve:

Chcete -li použít zvýraznění na všechny řádky Celkem, vytvoříme nové pravidlo podmíněného formátování se vzorcem

1	= MOD (ŘÁDEK ()-1, 3) = 0

Když podmíněné formátování použije tento vzorec, zobrazí se řádek 2

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

Řádek 3 bude mít podobný výstup, ale pak řádek 4 uvidí

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

Naše pravidlo tedy funguje správně, jak je ukázáno zde:

Zvýrazněte celá čísla nebo sudá čísla

Namísto zvýraznění konkrétních řádků můžete také zkontrolovat skutečné hodnoty v buňkách. To může být užitečné, když chcete najít čísla, která jsou násobky N. Například pro nalezení násobků 3 bude váš vzorec podmíněného formátu

1	= MOD (A2, 3) = 0

Až do tohoto bodu jsme se zabývali celými čísly. Můžete však zadat desetinnou čárku (např. 1,234) a poté vydělit 1, abyste získali pouze desetinnou část (např. 0,234). Tento vzorec vypadá

1	= MOD (A2, 1)

Vědět, že pro zvýraznění pouze celých čísel by byl vzorec podmíněného formátu

1	= MOD (A2, 1) = 0

Zřetězte každých N buněk

Dříve jsme pomocí MOD informovali počítač, kdy má získat hodnotu u každé N -té položky. Můžete také použít ke spuštění většího vzorce k provedení. Zvažte toto rozložení:

Chceme spojit jména společně, ale pouze na každé 3^rd řádek začínající na řádku 2. Vzorec použitý pro toto je

1	= IF (MOD (ŘÁDEK ()+1, 3) = 0, SPOJENÍ (A2, "", A3, "", A4), "")

Naše funkce MOD slouží jako kritéria pro celkovou funkci IF. V tomto případě jsme potřebovali přidat 1 do ŘADY, protože začínáme na řádku 2 (2 + 1 = 3). Když je výstup MOD 0, vzorec provede zřetězení. V opačném případě se vrátí prázdné.

Počítejte sudé/liché hodnoty

Pokud jste někdy potřebovali spočítat, kolik sudých nebo lichých hodnot je v rozsahu, budete vědět, že COUNTIF to neumí. Můžeme to však udělat pomocí MOD a SUMPRODUCT. Podívejme se na tuto tabulku:

Vzorec, který použijeme k nalezení lichých hodnot, bude

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

Spíše než načíst některá čísla řádků, náš MOD načte hodnoty aktuálních buněk do pole. Celková transformace pak bude probíhat takto:

1234	{5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Vzal režim 2{True, True, True, True, False, True} <- Zaškrtnuto, pokud byla hodnota 0{1, 1, 1, 1, 0, 1} <- vynásobeno 1 pro převod z True/False na 1/0

SUMPRODUCT pak sečte hodnoty v našem poli a poskytne požadovanou odpověď: 5.

Opakující se vzor

Všechny předchozí příklady zkontrolovaly výstup MOD pro hodnotu. MOD můžete také použít ke generování opakujícího se vzoru čísel, což může být zase velmi užitečné.

Nejprve řekněme, že jsme měli seznam položek, které chceme opakovat.

Můžete to zkusit ručně zkopírovat a vložit, kolikrát potřebujete, ale bylo by to únavné. Místo toho budeme chtít k načtení našich hodnot použít funkci INDEX. Aby INDEX fungoval, potřebujeme, aby argument řádku byl posloupnost čísel, která jde {1, 2, 3, 1, 2, 3, 1 atd.}. Toho můžeme dosáhnout pomocí MOD.

Nejprve začneme funkcí ROW. Pokud začnete s

1	= ŘADA (A1)

A pak to zkopírujte dolů, získáte základní číselnou posloupnost {1, 2, 3, 4, 5, 6, …}. Pokud bychom použili funkci MOD s dělitelem 3,

1	= MOD (ŘADA (A1), 3)

dostali bychom {1, 2, 0, 1, 2, 0, …}. Vidíme, že máme opakující se vzor „0, 1, 2“, ale první sérii chybí počáteční 0. Chcete -li to napravit, zazálohujte krok a odečtěte 1 od čísla řádku. Tím se změní naše počáteční sekvence na {0, 1, 2, 3, 4, 5,…}

1	= MOD (ŘADA (A1) -1, 3)

A poté, co vyjde z MOD, máme {0, 1, 2, 0, 1, 2, …}. To se blíží tomu, co potřebujeme. Posledním krokem je přidání 1 do pole.

1	= MOD (ŘADA (A1) -1, 3) +1

Který nyní vytváří číselnou posloupnost {1, 2, 3, 1, 2, 3, …}. Toto je naše požadovaná sekvence! Připojením do funkce INDEX získáme vzorec

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

Výstup bude nyní vypadat takto:

Příklady MOD ve VBA

Můžete také použít funkci LINEST ve VBA.

V rámci VBA je MOD operátorem (stejně jako operátory plus, mínus, násobení a dělení). Provedení následujících příkazů VBA

123456

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

přinese následující výsledky