Download eksemplet på projektmappe
Denne Excel -tutorial viser, hvordan du bruger Excel MOD -funktion at beregne resten efter opdeling.
MOD Funktionsoversigt
MOD -funktionen returnerer resten eller modulet af et tal efter at have udført division. MOD -funktionen er dog ikke udelukkende til at hjælpe os med vores delingsproblemer. Det bliver endnu mere kraftfuldt, når vi vil lede efter hvert niende element på en liste, eller når vi skal generere et gentaget mønster.
Hvis du vil bruge funktionen MOD Excel -regneark, skal du markere celle og skrive:
MOD -funktion Syntaks og input:
| 1 | = MOD (nummer, divisor) |
nummer - Et nummer.
deler - Et tal at dele med.
Hvad er MOD -funktionen?
MOD -funktionen returnerer resten eller modulet af et tal efter at have udført division. MOD -funktionen er dog ikke udelukkende til at hjælpe os med vores delingsproblemer. Det bliver endnu mere kraftfuldt, når vi vil lede efter hvert niende element på en liste, eller når vi skal generere et gentaget mønster.
MOD Grundlæggende matematik
Når du forsøger at dividere 13 med 4, kan du sige, at svaret er 3 rest 1. "1" i dette tilfælde er specifikt kendt som modulet (deraf navnet på MOD -funktionen). I en formel kunne vi så skrive
| 1 | = MOD (13, 4) |
Og output ville være 1.
Når man ser på denne tabel, får man nogle flere illustrationer af, hvordan input/output af MOD vil fungere.
| 1 | = MOD (A2,3) |
Bemærk, at når input var 3, var der ingen rest, og dermed var output fra formlen 0. Også i vores tabel brugte vi ROW -funktionen til at generere vores værdier. Meget af styrken ved MOD vil være ved at bruge funktionen RÆKKE (eller KOLONNE), som vi ser i de følgende eksempler.
MOD Sum hver anden række
Overvej denne tabel:
Til illustration har den anden kolonne formlen
| 1 | = MOD (A2, 2) |
Hvis du vil tilføje alle de lige rækker, kan du skrive en SUMIF -formel og få kriterierne til at lede efter 0 -værdier i kolonne B. Eller for at tilføje alle de ulige rækker skal kriterierne være at lede efter 1 værdier.
Vi behøver dog slet ikke at oprette hjælperkolonnen. Du kan kombinere kraften i MOD inden for SUMPRODUCT for at gøre det hele i et trin. Vores formel for dette ville være
| 1 | = SUMPRODUCT (A2: A5, -(MOD (B2: B2, 2) = 0) |
Da det er inden for SUMPRODUCT, vil MOD -funktionen kunne håndtere vores array -input. Vi har allerede set output i hjælperkolonnen, men arrayet fra vores MOD i denne formel vil være {0, 1, 0, 1}. Efter at have kontrolleret for værdier, der svarer til 0 ved anvendelse af den dobbelte unary, vil arrayet være {1, 0, 1, 0}. SUMPRODUCT gør derefter sin magi eller multiplicerer arraysne for at producere {2, 0, 4, 0} og derefter summering for at få det ønskede output på 6.
MOD Sum hver n. Række
Da en formel for MOD (x, N) udsender et 0 for hver Nth -værdi, kan vi bruge dette til at hjælpe formler med at vælge og vælge, hvilke værdier der skal bruges i andre funktioner. Se på denne tabel.
Vores mål er at få fat i værdierne fra hver række markeret "Total". Bemærk, at Total vises hver 3.rd række, men starter ved række 4. Vores MOD -funktion vil således bruge 3 som 2nd argument, og vi skal trække 1 fra det første argument (siden 4 -1 = 3). På denne måde vil de ønskede rækker, vi ønsker (4, 7, 10) være multipler af 3 (3, 6, 9). Vores formel til at summere de ønskede værdier vil være
| 1 | = SUMPRODUCT (C2: C10, -(MOD (ROW (A2: A10) +2, 3) = 0)) |
Den producerede array vil transformere sådan:
| 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}{Falsk, falsk, sand, falsk, falsk, sand, falsk, falsk, sand}{0, 0, 1, 0, 0, 1, 0, 0, 1} |
Vores SUMPRODUCTs kriterierray er nu opsat, hvordan vi skal gribe hver 3.rd værdi, og vi får det ønskede resultat på $ 90.
MOD Sum på kolonner
Vi har hidtil brugt eksempler, der går lodret og bruger ROW, men du kan også gå vandret med COLUMN -funktionen. Overvej dette layout:
Vi vil opsummere alle emnerne. Vores formel for dette kunne være
| 1 | = SUMPRODUCT (B2: E2*(MOD (COLUMN (B2: E2), 2) = 0) |
I dette tilfælde er vi indstillet til at få fat i hver 2nd kolonne inden for vores område, så SUMPRODUCT kun bevarer værdier uden nul for kolonner B & D. Til reference er her en tabel, der viser kolonnetal og deres tilsvarende værdi efter at have taget MOD 2.
Fremhæv hver n. Række
Et andet almindeligt sted at bruge MOD -funktionen er, når du vil have en fremhævet række vist hver niende række. Den generelle form for dette vil være
| 1 | = MOD (Række () ± forskydning, N) = 0 |
Hvor N er antallet af rækker mellem hver fremhævet række (dvs. for at markere hver 3rd række, N = 3) og Forskydning er valgfrit det tal, vi skal tilføje eller trække fra for at få vores første fremhævede række til at flugte med N (dvs. at markere hver 3rd række, men start ved række 5, vi skulle trække 2 fra 5 -2 = 3). Bemærk, at med ROW -funktionen, ved at udelade argumenter, returnerer det rækkenummeret fra cellen, formlen er i.
Lad os bruge vores bord fra før:
For at anvende en fremhævning på alle de samlede rækker opretter vi en ny betinget formateringsregel med en formel på
| 1 | = MOD (Række ()-1, 3) = 0 |
Når den betingede formatering anvender denne formel, vises række 2
| 1234 | = MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Falsk |
Række 3 vil opleve et lignende output, men derefter vil række 4 se
| 1234 | = MOD (4-1, 3) = 0= MOD (3, 3) = 0= 0 = 0= Sandt |
Vi har således vores regel fungerer korrekt, som vist her:
Fremhæv heltal eller lige tal
I stedet for at fremhæve bestemte rækker, kan du også kontrollere de faktiske værdier i cellerne. Dette kan være nyttigt for, når du vil finde tal, der er multipler af N. F.eks. For at finde multipler af 3, ville din betingede formel være
| 1 | = MOD (A2, 3) = 0 |
Indtil dette tidspunkt har vi beskæftiget os med hele tal. Du kan dog have et input på en decimal (f.eks. 1,234) og derefter dividere med 1 for kun at få decimaldelen (f.eks. 0,234). Denne formel ligner
| 1 | = MOD (A2, 1) |
Vel vidende, at for at markere kun heltal ville den betingede formatformel være
| 1 | = MOD (A2, 1) = 0 |
Sammenkæd alle N -celler
Vi har tidligere brugt MOD til at fortælle computeren, hvornår den skal have værdi for hver Nth -vare. Du kan også få den til at udløse en større formel, der skal udføres. Overvej dette layout:
Vi vil sammenkæde navnene sammen, men kun på hver 3rd række, der starter på række 2. Formlen, der bruges til dette, er
| 1 | = HVIS (MOD (Række ()+1, 3) = 0, KONKATENERE (A2, "", A3, "", A4), "") |
Vores MOD -funktion er det, der fungerer som kriterier for den samlede IF -funktion. I dette eksempel skulle vi tilføje 1 til vores RÆKKE, fordi vi starter ved række 2 (2 + 1 = 3). Når MODs output er 0, udfører formlen sammenkædningen. Ellers returnerer det bare tomt.
Tæl lige/ulige værdier
Hvis du nogensinde har haft brug for at tælle, hvor mange lige eller ulige værdier der er i et område, ved du, at COUNTIF ikke har mulighed for dette. Vi kan dog gøre det med MOD og SUMPRODUCT. Lad os se på denne tabel:
Formlen, vi vil bruge til at finde de ulige værdier, er
| 1 | = SUMPRODUCT (1*(MOD (A2: A7, 2) = 1)) |
I stedet for at indlæse nogle rækkenumre, vil vores MOD indlæse de faktiske cellers værdier i arrayet. Den overordnede transformation vil derefter skride frem således:
| 1234 | {5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Tog mod 2{True, True, True, True, False, True} <- Kontrolleret, om værdien var 0{1, 1, 1, 1, 0, 1} <- Multipliceret med 1 for at konvertere fra sandt/falsk til 1/0 |
SUMPRODUCT tilføjer derefter værdierne i vores array og giver det ønskede svar på: 5.
Gentaget mønster
Alle de tidligere eksempler har kontrolleret output for MOD for en værdi. Du kan også bruge MOD til at generere et gentagende mønster af tal, hvilket igen kan være meget nyttigt.
Lad os først sige, at vi havde en liste over emner, vi ønsker gentaget.
Du kan prøve at kopiere og indsætte manuelt, men mange gange du har brug for det, men det ville blive kedeligt. I stedet vil vi bruge INDEX -funktionen til at hente vores værdier. For at INDEX'en fungerer, har vi brug for rækkeargumentet til at være en række med tal, der går {1, 2, 3, 1, 2, 3, 1 osv.}. Vi kan opnå dette ved hjælp af MOD.
Først starter vi med bare ROW -funktionen. Hvis du starter med
| 1 | = RÆKKE (A1) |
Og kopier dette derefter nedad, du får den grundlæggende nummersekvens på {1, 2, 3, 4, 5, 6,…}. Hvis vi anvendte MOD -funktionen med 3 som divisor,
| 1 | = MOD (RÆKKE (A1), 3) |
vi ville få {1, 2, 0, 1, 2, 0,…}. Vi ser, at vi har et gentaget mønster på "0, 1, 2", men den første serie mangler initialen 0. For at løse dette skal du sikkerhedskopiere et trin og trække 1 fra rækkenummeret. Dette ændrer vores startsekvens til {0, 1, 2, 3, 4, 5,…}
| 1 | = MOD (RÆKKE (A1) -1, 3) |
Og efter at det kommer ud af MOD, har vi {0, 1, 2, 0, 1, 2,…}. Det er tæt på, hvad vi har brug for. Det sidste trin er at tilføje 1 til arrayet.
| 1 | = MOD (RÆKKE (A1) -1, 3) +1 |
Som nu producerer en nummersekvens på {1, 2, 3, 1, 2, 3,…}. Dette er vores ønskede sekvens! Ved at tilslutte den til en INDEX -funktion får vi vores formel
| 1 | = INDEX (MyList, MOD (Række (A1) -1, 3) +1) |
Outputtet ser nu sådan ud:
MOD Eksempler i VBA
Du kan også bruge funktionen LINEST i VBA.
Inden for VBA er MOD en operator (ligesom plus, minus, multiplikation og divisionsoperatorer). Så udførelse af følgende VBA -udsagn
| 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") |
vil frembringe følgende resultater
For funktionsargumenterne (kendt_y’er osv.) Kan du enten indtaste dem direkte i funktionen eller definere variabler, der skal bruges i stedet.
Tilbage til listen over alle funktioner i Excel
Google Sheets MOD -funktion
MOD -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
