Ladda ner exempelarbetsboken
Denna Excel -handledning visar hur du använder Excel MOD -funktion för att beräkna resten efter delning.
MOD Funktionsöversikt
MOD -funktionen returnerar resten, eller modulen, av ett tal efter att ha utfört division. MOD -funktionen är dock inte enbart för att hjälpa oss med våra delningsproblem. Det blir ännu mer kraftfullt för när vi vill leta efter varje n: e artikel i en lista, eller när vi behöver generera ett upprepande mönster.
Om du vill använda funktionen MOD Excel -kalkylblad väljer du cell och skriver:
MOD -funktion Syntax och ingångar:
| 1 | = MOD (nummer, delare) |
siffra - Ett nummer.
divisor - Ett nummer att dela med.
Vad är MOD -funktionen?
MOD -funktionen returnerar resten, eller modulen, av ett tal efter att ha utfört division. MOD -funktionen är dock inte enbart för att hjälpa oss med våra delningsproblem. Det blir ännu mer kraftfullt för när vi vill leta efter varje n: e artikel i en lista, eller när vi behöver generera ett upprepande mönster.
MOD Grundläggande matematik
När du försöker dela 13 med 4 kan du säga att svaret är 3 återstoden 1. "1" i detta fall är specifikt känt som modulen (därav MOD -funktionsnamnet). I en formel då kunde vi skriva
| 1 | = MOD (13, 4) |
Och utgången skulle vara 1.
Om du tittar på den här tabellen får du några fler illustrationer av hur in/utgången av MOD kommer att fungera.
| 1 | = MOD (A2,3) |
Observera att när ingången var 3 fanns det ingen återstod och därmed var utgången från formeln 0. I vår tabell använde vi ROW -funktionen för att generera våra värden. Mycket av MOD: s kraft kommer från att använda funktionen RAD (eller KOLUMN) som vi kommer att se i följande exempel.
MOD Summa varannan rad
Tänk på den här tabellen:
För illustrationändamål har den andra kolumnen formeln
| 1 | = MOD (A2, 2) |
För att lägga till alla de jämna raderna kan du skriva en SUMIF -formel och låta kriterierna leta efter 0 -värden i kol B. Eller, för att lägga till alla udda rader, har kriterierna att leta efter 1 värden.
Vi behöver dock inte skapa hjälpkolumnen alls. Du kan kombinera kraften i MOD inom SUMPRODUCT för att göra allt i ett steg. Vår formel för detta skulle vara
| 1 | = SUMPRODUCT (A2: A5, -(MOD (B2: B2, 2) = 0) |
Eftersom det är inom SUMPRODUCT kommer MOD -funktionen att kunna hantera vår matrisinmatning. Vi har sett utmatningen redan i hjälparkolumnen, men matrisen från vår MOD i denna formel kommer att vara {0, 1, 0, 1}. Efter att ha sökt efter värden som är lika med 0 när dubbelunary tillämpas kommer matrisen att vara {1, 0, 1, 0}. SUMPRODUKTEN gör sedan sin magi eller multiplicerar matriserna för att producera {2, 0, 4, 0} och summerar sedan för att få önskad effekt på 6.
MOD Summa varje n: e rad
Eftersom en formel för MOD (x, N) kommer att mata ut ett 0 vid varje N: e värde, kan vi använda detta för att hjälpa formlerna att välja och välja vilka värden som ska användas i andra funktioner. Titta på det här bordet.
Vårt mål är att ta tag i värdena från varje rad märkt "Totalt". Observera att summan visas var tredjerd rad, men börjar från rad 4. Vår MOD -funktion kommer alltså att använda 3 som 2nd argument, och vi måste subtrahera 1 från det första argumentet (eftersom 4 -1 = 3). På så sätt kommer de önskade raderna vi vill ha (4, 7, 10) att vara multiplar av 3 (3, 6, 9). Vår formel för att summera de önskade värdena blir
| 1 | = SUMPRODUCT (C2: C10, -(MOD (RAD (A2: A10) +2, 3) = 0)) |
Arrayen som produceras kommer att transformeras så här:
| 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} |
Vår SUMPRODUCTs kriterieserie är nu konfigurerad hur vi behöver ta var tredjerd värde, och vi får vårt önskade resultat på $ 90.
MOD Summa på kolumner
Vi har hittills använt exempel som går vertikalt och använder ROW, men du kan också gå horisontellt med COLUMN -funktionen. Tänk på denna layout:
Vi vill sammanfatta alla artiklar. Vår formel för detta kan vara
| 1 | = SUMPRODUCT (B2: E2*(MOD (COLUMN (B2: E2), 2) = 0) |
I det här fallet är vi inställda på att ta varannannd kolumn inom vårt intervall, så SUMPRODUCT kommer bara att behålla värden som inte är noll för kolumnerna B & D. För referens är här en tabell som visar kolumnnummer och deras motsvarande värde efter att ha tagit MOD 2.
Markera varje n: e rad
En annan vanlig plats att använda MOD -funktionen är när du vill att en markerad rad ska visas varje n: e rad. Den allmänna formen för detta kommer att vara
| 1 | = MOD (RAD () ± Förskjutning, N) = 0 |
Var N är antalet rader mellan varje markerad rad (dvs för att markera var 3: erd rad, N = 3) och Offset är valfritt det tal vi behöver lägga till eller subtrahera för att få vår första markerade rad att anpassa sig till N (dvs för att markera var tredjerd rad men börja på rad 5, vi skulle behöva subtrahera 2 eftersom 5 -2 = 3). Observera att med ROW -funktionen, genom att utelämna några argument, returnerar det radnumret från cellen som formeln är i.
Låt oss använda vårt bord från tidigare:
För att tillämpa en markering på alla rader totalt skapar vi en ny villkorlig formateringsregel med en formel på
| 1 | = MOD (RAD ()-1, 3) = 0 |
När den villkorliga formateringen tillämpar denna formel visas rad 2
| 1234 | = MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Falskt |
Rad 3 kommer att uppleva en liknande utmatning, men sedan ser rad 4
| 1234 | = MOD (4-1, 3) = 0= MOD (3, 3) = 0= 0 = 0= Sant |
Vi har alltså vår regel som fungerar korrekt, som visas här:
Markera heltal eller jämna tal
I stället för att markera specifika rader kan du också kontrollera de faktiska värdena i cellerna. Detta kan vara användbart för när du vill hitta siffror som är multiplar av N. Till exempel, för att hitta multiplar av 3 skulle din villkorliga formatformel vara
| 1 | = MOD (A2, 3) = 0 |
Hittills har vi haft att göra med hela tal. Du kan dock ha en inmatning på en decimal (t.ex. 1,234) och sedan dividera med 1 för att bara få decimaldelen (t.ex. 0,234). Denna formel ser ut som
| 1 | = MOD (A2, 1) |
Att veta att för att markera endast heltal skulle formelformatet för villkorat format vara
| 1 | = MOD (A2, 1) = 0 |
Koppla samman alla N -celler
Vi har tidigare använt MOD för att berätta för datorn när vi ska ta värdet på varje N: e objekt. Du kan också få den att användas för att utlösa en större formel som ska köras. Tänk på denna layout:
Vi vill sammanfoga namnen tillsammans, men bara var tredjerd rad som börjar på rad 2. Formeln som används för detta är
| 1 | = IF (MOD (ROW ()+1, 3) = 0, CONCATENATE (A2, "", A3, "", A4), "") |
Vår MOD -funktion är det som fungerar som kriterier för den övergripande IF -funktionen. I det här exemplet behövde vi lägga till 1 till vår RAD, eftersom vi börjar på rad 2 (2 + 1 = 3). När MOD: s utgång är 0, gör formeln sammankopplingen. Annars returneras det bara tomt.
Räkna jämna/udda värden
Om du någonsin har behövt räkna hur många jämna eller udda värden som finns i ett intervall, vet du att COUNTIF inte har möjlighet att göra detta. Vi kan dock göra det med MOD och SUMPRODUCT. Låt oss titta på den här tabellen:
Formeln vi använder för att hitta de udda värdena kommer att vara
| 1 | = SUMPRODUCT (1*(MOD (A2: A7, 2) = 1)) |
Istället för att ladda in några radnummer kommer vår MOD att ladda in de faktiska cellernas värden i arrayen. Den övergripande omvandlingen kommer då att fortskrida så här:
| 1234 | {5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Tog mod 2{Sant, sant, sant, sant, falskt, sant} <- Kontrollerat om värdet var 0{1, 1, 1, 1, 0, 1} <- multiplicerat med 1 för att konvertera från sant/falskt till 1/0 |
SUMPRODUCT lägger sedan ihop värdena i vår array och ger önskat svar på: 5.
Upprepande mönster
Alla föregående exempel har kontrollerat utgången från MOD för ett värde. Du kan också använda MOD för att generera ett upprepande mönster av siffror, vilket i sin tur kan vara till stor hjälp.
Låt oss först säga att vi hade en lista med artiklar som vi vill upprepa.
Du kan försöka kopiera och klistra in manuellt hur många gånger du än behöver, men det skulle bli tråkigt. Istället vill vi använda INDEX -funktionen för att hämta våra värden. För att INDEX ska fungera behöver vi radargumentet vara en sekvens av tal som går {1, 2, 3, 1, 2, 3, 1, etc.}. Vi kan åstadkomma detta med MOD.
Först börjar vi med bara ROW -funktionen. Om du börjar med
| 1 | = RAD (A1) |
Och sedan kopiera detta nedåt, du får den grundläggande nummersekvensen för {1, 2, 3, 4, 5, 6,…}. Om vi använde MOD -funktionen med 3 som delare,
| 1 | = MOD (RAD (A1), 3) |
vi skulle få {1, 2, 0, 1, 2, 0,…}. Vi ser att vi har ett upprepande mönster på "0, 1, 2", men den första serien saknar initialen 0. För att åtgärda detta, säkerhetskopiera ett steg och subtrahera 1 från radnumret. Detta kommer att ändra vår startsekvens till {0, 1, 2, 3, 4, 5, …}
| 1 | = MOD (RAD (A1) -1, 3) |
Och när det kommer ut ur MOD har vi {0, 1, 2, 0, 1, 2,…}. Detta närmar sig det vi behöver. Det sista steget är att lägga till 1 i matrisen.
| 1 | = MOD (RAD (A1) -1, 3) +1 |
Som nu producerar en nummersekvens av {1, 2, 3, 1, 2, 3,…}. Detta är vår önskade sekvens! Genom att ansluta den till en INDEX -funktion får vi vår formel för
| 1 | = INDEX (MyList, MOD (RAD (A1) -1, 3) +1) |
Utmatningen kommer nu att se ut så här:
MOD Exempel i VBA
Du kan också använda LINEST -funktionen i VBA.
Inom VBA är MOD en operatör (precis som plus, minus, multiplikation och divisionsoperatorer). Så kör följande VBA -uttalanden
| 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") |
kommer att ge följande resultat
För funktionsargumenten (kända_y, etc.) kan du antingen skriva in dem direkt i funktionen eller definiera variabler som ska användas istället.
Återgå till listan över alla funktioner i Excel
Google Sheets MOD -funktion
MOD -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
