Last ned eksemplet på arbeidsbok
Denne Excel -opplæringen viser hvordan du bruker Excel MOD -funksjon å beregne resten etter dividering.
MOD Funksjon Oversikt
MOD -funksjonen returnerer resten, eller modulen, av et tall etter å ha utført divisjon. MOD -funksjonen er imidlertid ikke strengt tatt for å hjelpe oss med divisjonsproblemene våre. Det blir enda kraftigere for når vi vil lete etter hvert niende element i en liste, eller når vi trenger å generere et gjentakende mønster.
For å bruke MOD Excel -regnearkfunksjonen, velg celle og skriv:
MOD -funksjon Syntaks og innganger:
| 1 | = MOD (nummer, divisor) |
Nummer - Et tall.
deler - Et tall å dele på.
Hva er MOD -funksjonen?
MOD -funksjonen returnerer resten, eller modulen, av et tall etter å ha utført divisjon. MOD -funksjonen er imidlertid ikke strengt tatt for å hjelpe oss med divisjonsproblemene våre. Det blir enda mer kraftfullt for når vi vil lete etter hvert niende element i en liste, eller når vi må generere et gjentagende mønster.
MOD Grunnleggende matematikk
Når du prøver å dele 13 med 4, kan du si at svaret er 3 resten 1. “1” i dette tilfellet er spesielt kjent som modulen (derav MOD -funksjonsnavnet). I en formel da kunne vi skrive
| 1 | = MOD (13, 4) |
Og utgangen ville være 1.
Når du ser på denne tabellen, får du flere illustrasjoner av hvordan input/output av MOD vil fungere.
| 1 | = MOD (A2,3) |
Vær oppmerksom på at når inngangen var 3, var det ingen rest, og dermed var utgangen til formelen 0. I tabellen vår brukte vi ROW -funksjonen til å generere våre verdier. Mye av kraften til MOD vil være å bruke funksjonen RAD (eller KOLONNE) som vi ser i eksemplene nedenfor.
MOD Sum annenhver rad
Vurder denne tabellen:
For illustrasjonsformål har den andre kolonnen formelen
| 1 | = MOD (A2, 2) |
For å legge til alle de jevne radene, kan du skrive en SUMIF -formel og la kriteriene se etter 0 -verdier i kolonne B. Eller for å legge til alle de merkelige radene, må kriteriene være å se etter 1 verdier.
Imidlertid trenger vi ikke å lage hjelperkolonnen i det hele tatt. Du kan kombinere kraften til MOD i SUMPRODUCT for å gjøre alt i ett trinn. Vår formel for dette ville være
| 1 | = SUMPRODUCT (A2: A5, -(MOD (B2: B2, 2) = 0) |
Siden det er innenfor SUMPRODUCT, vil MOD -funksjonen kunne håndtere matrisinngangene våre. Vi har sett utgangen allerede i hjelperkolonnen, men matrisen fra vår MOD i denne formelen vil være {0, 1, 0, 1}. Etter å ha sjekket for verdier som er lik 0 ved bruk av den dobbelte unaryen, vil matrisen være {1, 0, 1, 0}. SUMPRODUKTET gjør deretter det magiske eller multipliserer matrisene for å produsere {2, 0, 4, 0} og deretter summerer for å få ønsket utgang på 6.
MOD Sum hver niende rad
Siden en formel for MOD (x, N) gir en 0 for hver Nth -verdi, kan vi bruke denne til å hjelpe formler med å velge og velge hvilke verdier som skal brukes i andre funksjoner. Se på dette bordet.
Målet vårt er å få tak i verdiene fra hver rad merket "Total". Vær oppmerksom på at totalen vises hver 3.rd rad, men starter på rad 4. Vår MOD -funksjon vil dermed bruke 3 som 2nd argument, og vi må trekke 1 fra det første argumentet (siden 4 -1 = 3). På denne måten vil de ønskede radene vi ønsker (4, 7, 10) være multipler av 3 (3, 6, 9). Vår formel for å summere de ønskede verdiene vil være
| 1 | = SUMPRODUCT (C2: C10, -(MOD (RAD (A2: A10) +2, 3) = 0)) |
Matrisen som produseres vil transformere slik:
| 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, True, False, False, True, False, False, True}{0, 0, 1, 0, 0, 1, 0, 0, 1} |
Vårt SUMPRODUCTs kriteriemateriale er nå satt opp hvordan vi må ta hver tredjerd verdi, og vi får ønsket resultat på $ 90.
MOD Sum på kolonner
Vi har brukt eksempler så langt som går vertikalt og bruker RAD, men du kan også gå horisontalt med COLUMN -funksjonen. Vurder denne oppsettet:
Vi ønsker å oppsummere alle elementene. Vår formel for dette kan være
| 1 | = SUMPRODUCT (B2: E2*(MOD (COLUMN (B2: E2), 2) = 0) |
I dette tilfellet skal vi ta hver 2nd kolonne i vårt område, så SUMPRODUCT vil bare beholde verdier som ikke er null for kolonner B & D. For referanser er her en tabell som viser kolonnummer og tilhørende verdi etter å ha tatt MOD 2.
Merk hver niende rad
Et annet vanlig sted å bruke MOD -funksjonen er når du vil at en markert rad skal vises hver niende rad. Den generelle formen for dette vil være
| 1 | = MOD (RAD () ± Forskyvning, N) = 0 |
Hvor N er antall rader mellom hver markert rad (dvs. for å markere hver 3rd rad, N = 3), og Offset er eventuelt tallet vi trenger å legge til eller trekke fra for å få vår første markerte rad til å samkjøre med N (dvs. for å markere hver tredjerd rad, men start på rad 5, vi må trekke fra 2 siden 5 -2 = 3). Vær oppmerksom på at med ROW -funksjonen, ved å utelate noen argumenter, vil den returnere radnummeret fra cellen formelen er i.
La oss bruke bordet vårt fra før:
For å bruke et høydepunkt på alle rader totalt, oppretter vi en ny regel for betinget formatering med en formel på
| 1 | = MOD (RAD ()-1, 3) = 0 |
Når den betingede formateringen gjelder denne formelen, ser rad 2
| 1234 | = MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Feil |
Rad 3 vil oppleve en lignende utgang, men så vil rad 4 se
| 1234 | = MOD (4-1, 3) = 0= MOD (3, 3) = 0= 0 = 0= Sant |
Vi har dermed vår regel som fungerer korrekt, som vist her:
Fremhev heltal eller partall
I stedet for å markere spesifikke rader, kan du også sjekke de faktiske verdiene i cellene. Dette kan være nyttig for når du vil finne tall som er multipler av N. For eksempel, for å finne multipler av 3 vil formelen for betinget format være
| 1 | = MOD (A2, 3) = 0 |
Hittil har vi hatt å gjøre med hele tall. Du kan imidlertid ha en inndata på en desimal (f.eks. 1,234) og deretter dele med 1 for å få bare desimaldelen (f.eks. 0,234). Denne formelen ser ut som
| 1 | = MOD (A2, 1) |
Å vite det, for å markere bare heltall vil den betingede formatformelen være
| 1 | = MOD (A2, 1) = 0 |
Koble sammen alle N -celler
Vi har tidligere brukt MOD for å fortelle datamaskinen når den skal hente verdi for hvert N -element. Du kan også få den til å utløse en større formel som skal kjøres. Vurder dette oppsettet:
Vi ønsker å sammenkoble navnene sammen, men bare hver tredjerd rad som starter på rad 2. Formelen som brukes for dette er
| 1 | = HVIS (MOD (RAD ()+1, 3) = 0, KONKATENERE (A2, "", A3, "", A4), "") |
Vår MOD -funksjon er det som fungerer som kriterier for den generelle IF -funksjonen. I dette eksemplet måtte vi legge til 1 i RADEN vår, fordi vi begynner på rad 2 (2 + 1 = 3). Når MODs utgang er 0, gjør formelen sammenkoblingen. Ellers returnerer det bare tomt.
Teller partall/oddetall
Hvis du noen gang har trengt å telle hvor mange like eller oddetall som er i et område, vet du at COUNTIF ikke har muligheten til å gjøre dette. Vi kan gjøre det med MOD og SUMPRODUCT skjønt. La oss se på denne tabellen:
Formelen vi skal bruke for å finne de merkelige verdiene vil være
| 1 | = SUMPRODUCT (1*(MOD (A2: A7, 2) = 1)) |
I stedet for å laste inn noen radnumre, vil vår MOD laste inn de faktiske cellens verdier i matrisen. Den generelle transformasjonen vil da gå slik:
| 1234 | {5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Tok moden 2{True, True, True, True, False, True} <- Sjekket om verdien var 0{1, 1, 1, 1, 0, 1} <- Multiplisert med 1 for å konvertere fra True/False til 1/0 |
SUMPRODUCT legger deretter til verdiene i vårt utvalg, og gir ønsket svar på: 5.
Gjentatt mønster
Alle de foregående eksemplene har sjekket utgangen til MOD for en verdi. Du kan også bruke MOD til å generere et repeterende tallmønster, noe som igjen kan være veldig nyttig.
La oss først si at vi hadde en liste over ting vi vil gjenta.
Du kan prøve å kopiere og lime inn manuelt hvor mange ganger du trenger, men det ville bli kjedelig. I stedet vil vi bruke INDEX -funksjonen for å hente verdiene våre. For at INDEKSEN skal fungere, trenger vi radargumentet til å være en tallrekke som går {1, 2, 3, 1, 2, 3, 1, etc.}. Vi kan oppnå dette ved å bruke MOD.
Først starter vi med bare ROW -funksjonen. Hvis du starter med
| 1 | = RAD (A1) |
Og kopier dette nedover, du får den grunnleggende tallsekvensen til {1, 2, 3, 4, 5, 6,…}. Hvis vi brukte MOD -funksjonen med 3 som deler
| 1 | = MOD (RAD (A1), 3) |
vi får {1, 2, 0, 1, 2, 0,…}. Vi ser at vi har et repeterende mønster på "0, 1, 2", men den første serien mangler initialen 0. For å fikse dette, sikkerhetskopier et trinn og trekk 1 fra radnummeret. Dette vil endre startsekvensen til {0, 1, 2, 3, 4, 5,…}
| 1 | = MOD (RAD (A1) -1, 3) |
Og etter at den kommer ut av MOD, har vi {0, 1, 2, 0, 1, 2,…}. Dette nærmer seg det vi trenger. Det siste trinnet er å legge til 1 i matrisen.
| 1 | = MOD (RAD (A1) -1, 3) +1 |
Som nå gir en tallrekke på {1, 2, 3, 1, 2, 3,…}. Dette er vår ønskede sekvens! Ved å koble den til en INDEX -funksjon, får vi formelen vår
| 1 | = INDEKS (MyList, MOD (RAD (A1) -1, 3) +1) |
Utgangen vil nå se slik ut:
MOD Eksempler i VBA
Du kan også bruke LINEST -funksjonen i VBA.
Innen VBA er MOD en operatør (akkurat som pluss, minus, multiplikasjon og divisjonsoperatorer). Så utfører følgende VBA -utsagn
| 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 gi følgende resultater
For funksjonsargumentene (kjente_y’er, etc.) kan du enten skrive dem inn direkte i funksjonen, eller definere variabler som skal brukes i stedet.
Gå tilbake til listen over alle funksjoner i Excel
Google Sheets MOD -funksjon
MOD -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel:
