=MOD(A2,B2)はA2をB2で割った余りを返します。=MOD(17,5)は2です。17の中に5は3回入り、2が余るからです。=ABS(A2)は符号を取った数を返すので、-42は42になります。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Number | Divisor | MOD | Times it fits |
| 2 | 17 | 5 | 2 | 3 |
| 3 | 20 | 5 | 0 | 4 |
| 4 | 7 | 2 | 1 | 3 |
| 5 | 100 | 7 | 2 | 14 |
| 6 | -3 | 2 | 1 | -1 |
| 7 | 3 | -2 | -1 | -1 |
D列は割り算のもう半分を示しています。QUOTIENTは除数が何回入るかを整数で返すので、17は5の3倍と2です。B2を0に変えると、0で割ったときと同じようにMODは#DIV/0!を返します。
MOD関数の構文
=MOD(number, divisor)
MODは小数にも使えます。=MOD(7.5,2)は1.5で、=MOD(A2,1)は3.75から0.75のように数の小数部分だけを返します。時刻付きの日付なら、=MOD(A2,1)は日付を捨てて時刻を残します。
マイナスの数のMOD
上のシートの6行目と7行目がExcelの規則を示しています。結果は除数の符号になります。=MOD(-3,2)は-1ではなく1で、=MOD(3,-2)は-1です。ExcelはMODをnumber - divisor * INT(number / divisor)として計算し、INTは常に切り下げます。
ここでスプレッドシートとコードの結果が食い違います。JavaScript、Java、C、C#では-3 % 2は-1で、Pythonの-3 % 2はExcelと同じく1です。GoogleスプレッドシートはExcelと同じです。被除数の符号にしたいなら、=A2-B2*TRUNC(A2/B2)を使います。
偶数か奇数か、1行おき
MOD(number,2)が0なら偶数です。これで「Even」か「Odd」のラベルが作れ、ROWと組み合わせれば1行おきに色を付けるルールになります。ExcelにはTRUEかFALSEを直接返すISEVENとISODDもあります。
| A | B | C | |
|---|---|---|---|
| 1 | Order | Qty | Even or odd |
| 2 | 1001 | 4 | Even |
| 3 | 1002 | 7 | Odd |
| 4 | 1003 | 10 | Even |
| 5 | 1004 | 3 | Odd |
| 6 | 1005 | 8 | Even |
| 7 | 1006 | 5 | Odd |
条件付き書式のルール=MOD(ROW($A2),2)=0は、偶数の行番号である2、4、6行目を強調します。Excelでは、ホーム > 条件付き書式 > 新しいルール >「数式を使用して、書式設定するセルを決定」で設定し、そこでは短い=MOD(ROW(),2)=0でも動きます。2を3に変えると、2行おきに色が付きます。
n行ごと: 3行ごとの値を合計する
MOD(ROW()-ROW(first),n)=0は、範囲の最初の行と、そこからn行ごとの行でTRUEになります。SUMPRODUCTの中で使えば、列から3つおきの値、たとえば各四半期の最後の月を取り出せます。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | Every 3rd | ||
| 2 | Jan | 120 | 310 | ||
| 3 | Feb | 135 | |||
| 4 | Mar | 150 | |||
| 5 | Apr | 110 | |||
| 6 | May | 125 | |||
| 7 | Jun | 160 |
行のずれは0から5で、=2はずれ2と5、つまり3月と6月を残し、150と160を足します。=2を=0に変えると、代わりに1月と4月を取ります。
分を時間と分に変換する
MODと整数の割り算で、合計を単位に分けられます。時間と残りの分、週と残りの日、箱とばらの品物です。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Minutes | Hours | Minutes left | Label |
| 2 | 135 | 2 | 15 | 2 h 15 min |
| 3 | 59 | 0 | 59 | 0 h 59 min |
| 4 | 240 | 4 | 0 | 4 h 0 min |
| 5 | 1000 | 16 | 40 | 16 h 40 min |
1000分は16時間40分です。本物の時刻の値(8:30、17:45)や、午前0時をまたぐ勤務には=MOD(end-start,1)が定番の数式です。時間の計算を参照してください。
ABS: 2つの数の差
=ABS(number)はマイナス記号を取ります。おもな使い道は、どちらの値が大きいかを気にしない差の大きさです。各予測が実績からどれだけ外れたか、測定値が許容範囲内かなどです。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Week | Forecast | Actual | Off by | Within 10? |
| 2 | W1 | 120 | 112 | 8 | TRUE |
| 3 | W2 | 95 | 109 | 14 | FALSE |
| 4 | W3 | 140 | 138 | 2 | TRUE |
| 5 | W4 | 80 | 93 | 13 | FALSE |
| 6 | W5 | 110 | 104 | 6 | TRUE |
B2-C2はW1では8、W2では-14です。ABSはどちらも距離に変えます。距離を合計するには、どのバージョンのExcelでも使える=SUMPRODUCT(ABS(B2:B6-C2:C6))を使います。ここではD列の合計の43になります。それを件数で割ると平均絶対誤差になります。
やってみよう: MODとABS
| A | B | C | |
|---|---|---|---|
| 1 | Eggs | Per carton | Left over |
| 2 | 350 | 12 |
やってみよう: C2に、A2の卵をB2の大きさのパックに詰められるだけ詰めた後、余る卵の数を計算してください。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | City | Morning | Evening | Change |
| 2 | Oslo | 14 | 6 |
やってみよう: D2に、B2とC2の間で気温が何度変わったかを、どちらの値が高くてもプラスの数で表示してください。
MODとQUOTIENTとINTの違い
| 目的 | 数式 | 17と5の結果 | -17と5の結果 |
|---|---|---|---|
| 余り | =MOD(A2,B2) | 2 | 3 |
| 入る回数の整数、0に向かって | =QUOTIENT(A2,B2) | 3 | -3 |
| 入る回数の整数、切り下げ | =INT(A2/B2) | 3 | -4 |
| ふつうの割り算 | =A2/B2 | 3.4 | -3.4 |
プラスの数ではQUOTIENTとINT(A2/B2)は一致し、B2*INT(A2/B2)+MOD(A2,B2)で必ず元の数に戻ります。マイナスの数で元に戻るのはINTの組だけです。-4に5を掛けて3を足すと-17です。マイナスの数でQUOTIENTとMODを組み合わせるのが、スケジュールや分割で1ずれるよくある原因です。
よくある質問
ExcelのMODは何をしますか？
割り算の余りを返します。=MOD(17,5)は2です。17の中に5は3回入り、2が余るからです。割り切れるとMODは0を返します。
Excelで数が偶数かどうかを調べるには？
偶数ならTRUEになる=MOD(A2,2)=0か、=ISEVEN(A2)を使います。ラベルにするなら=IF(MOD(A2,2)=0,"Even","Odd")です。
マイナスの値でMODがプラスの数を返すのはなぜですか？
ExcelのMODは除数の符号を取ります。=MOD(-3,2)は1、=MOD(3,-2)は-1です。多くのプログラミング言語では-3 % 2が-1になるので、コードと結果が違うことがあります。
Excelで絶対値を求めるには？
=ABS(A2)を使います。-42は42になり、42は42のままです。差の大きさを符号なしで求めるには=ABS(B2-C2)を使います。
Excelで絶対値を合計するには？
どのバージョンでも使える=SUMPRODUCT(ABS(A2:A7))を使います。Excel 365と2021では=SUM(ABS(A2:A7))でも動きます。
