MOD関数で求めた余りが0であれば割り切れる数、つまり「倍数」であると言えます。例えば、小学校の算数で5の倍数は、5, 10, 15, 20,・・・とプラスの数だけであると習いますが、Excelでは0やマイナスの数も倍数として扱います。したがって、倍数はプラス・マイナスともに無限にあります。
・・・ −15, −10, −5, 0, 5, 10, 15, ・・・
倍数は、数直線上で等間隔に並びますから、等間隔に印をつけたり、色を変えたりする場合にもMOD関数が使えます。なお、偶数・奇数についてはそれぞれISEVEN・ISODD関数が用意されています。
そこで、今回は、MOD関数を用いて割り切れること(倍数)の判定をする方法、余りが一致することの判定、偶奇一致判定の方法について出題します。
目次
1.割り切れることの判定
問題
A列に連番を入力した。7の倍数に「*」をつけなさい。
解説
7の倍数は、7で割り切れる数のことです。つまり、7で割ったときの余りが0になります。いっぱんに、Xの倍数であることを判定するには、MOD関数を用いてXで割ったときの余りを求め、それが0であることを判定します。
MOD関数を挿入します(参考:【Excel】MOD関数の使い方、INT関数との関係、マイナスの場合の余り)。
7で割ったときの余りを求めます。余りが0であれば7の倍数です。
IF関数を挿入します。MOD関数が0であれば7の倍数なので「*」を表示し、それ以外は空白を返します。これで完成です。
- =IF(MOD(A2,7)=0,"*","")
別解
ところで、0であることを判定するのに比較演算をする必要はありません(参考:IF関数の条件に「等号や不等号が必要」と思っているなら、IF関数を勉強し直してほしい)。IF関数の論理式をMOD関数にすると、Xの倍数でない場合は余りが1以上なのでTRUE、Xの倍数の場合は余りが0なのでFALSEとなります。
IF関数を挿入します。さらに、MOD関数を挿入します。
7で割ったときの余りを求めます。
TRUEの場合は空白を返し、FALSEの場合は「*」を表示します。これで完成です。
- =IF(MOD(A2,7),"","*")
2.下1桁で判定
問題
A列に連番を入力した。1の位が0または5の場合に「ポイント5倍」と表示しなさい。
解説
下1桁を取り出すには、10で割ったときの余りを求めます(参考:MOD関数で小数部分や下位の桁を取り出す、下の位の一致判定)。MOD関数を挿入します。10で割ったときの余りを求めます。
これで下1桁を取り出すことができます。1の位が0または5の場合に「ポイント5倍」と表示します。IF関数を挿入します。さらに、OR関数を挿入します(参考:【Excel】AND関数やOR関数を使うなら、まず「論理値を返す関数」であることを理解せよ)。
MOD関数が0または5であることを判定します。
- OR(MOD(A2,10)=0,MOD(A2,10)=5)
TRUEの場合は「ポイント5倍」とし、FALSEの場合は空白を返します。
- =IF(OR(MOD(A2,10)=0,MOD(A2,10)=5),"ポイント5倍","")
これで完成です。
別解
ところで、下1桁が0または5である数は、5の倍数です。
MOD関数を挿入して、A列の数値を5で割ったときの余りを求めます。5の倍数の場合、5で割ったときの余りは0です。
IF関数を挿入します。TRUEの場合は空白を返し、FALSEの場合は「ポイント5倍」とします。これで完成です。
- =IF(MOD(A2,5),"","ポイント5倍")
3.下位の桁を取り出して倍数の判定をする
問題
A列に連番を入力した。下2桁が3の倍数のものを抽出しなさい。
解説
下2桁を求めるには100で割ったときの余りを求めます。MOD関数を挿入します。100で割ったときの余りを求めます。
これで下2桁を取り出すことができます。
これが3の倍数であることを判定するには、3で割ったときの余りを求めますから、外側にMOD関数を挿入します。
3で割ったときの余りを求めます。
- =MOD(MOD(A2,100),3)
オートフィルタを設定します(参考:【Excel】オートフィルタの設定と解除、条件にあう行を抽出する方法の総復習)。
余りが0のものを抽出します。
12個抽出されたことが分かります。
4.等間隔であればMODが使える
問題
A列に連番を入力した。1の位が2または7の場合に「ポイント2倍」と表示しなさい。
解説
MOD関数を挿入します。
5で割ったときの余りを求めます。
1の位が0または5であれば5の倍数ですが、1の位が2または7であれば、5で割ったときの余りは2です。
IF関数を挿入します。MOD関数が2であることを判定します。TRUEの場合は「ポイント2倍」とし、FALSEの場合は空白を返します。
これで完成です。
文字列を表示する間隔が等間隔の場合、その間隔で割ったときの余りを求めて判定します。
別解
1の位が2または7の場合、元の数から2を引くと5の倍数になります。
このことを利用しても良いです。
- =IF(MOD(A2-2,5),"","ポイント2倍")
5.余りが一致することの判定
問題
セルA1とセルA2に入力した数値を7で割ったときの余りが一致すればTRUE、一致しなければFALSEと表示しなさい。また、セルA1がセルA2より大きくても、同じ数式を使ってもよいか。
解説
MOD関数を挿入します。7で割ったときの余りを求めます。両方とも2なので、7で割ったときの余りが一致します。これを判定しようとしています。
- =MOD(A1,7)
ところで、765を7で割ると109余り2、345を7で割ると49余り2です。引き算をすると、余っている部分が打ち消されるので、余りが無くなります。
つまり、引き算をした値が7の倍数であればよいです。
Xで割ったときの余りが一致するには、引き算をしたときにXの倍数になればよいです。
MOD関数を挿入します。
引き算をして7で割ったときの余りが0であれば、2つの数の余りが一致していることが分かります。TRUEになります。
- =MOD(A1-A2,7)=0
一致しなければFALSEになります。
このことは、小さい数から大きい数を引いても同じです。マイナスになりますが、引き算した数は7の倍数です。
したがって、引き算の順序が逆でも構いません。
6.偶奇の判定
問題
セルA1とセルA2に入力した数値の偶奇が一致すれば黒、一致しなければ白と表示しなさい。
解説
2つの数が両方とも偶数であるか、または、両方とも奇数であることを「偶奇が一致する」と言います。
これに対して、偶数と奇数の場合は「偶奇が異なる」といいます。
偶数は2で割り切れる数であり、奇数は2で割ったときの余りが1になる数です。したがって、偶奇が一致するかどうかは、2で割ったときの余りが一致するかどうかと同じです。
IF関数を挿入します。さらに、MOD関数を挿入します。
引き算をして、2で割ったときの余りを考えます。余りが1であれば偶奇が一致しないので白、余りが0であれば偶奇が一致するので黒とします。
- =IF(MOD(A1-A2,2),"白","黒")
別解その1
引き算をして偶数であれば偶奇が一致していると言えます。偶数であることを判定するにはISEVEN関数を使います(参考:IF関数の条件に「等号や不等号が必要」と思っているなら、IF関数を勉強し直してほしい)。
IF関数を挿入します。さらに、ISEVEN関数を挿入します。
引き算をします。偶数であれば偶奇が一致するので黒、奇数であれば偶奇が一致しないので白とします。
- =IF(ISEVEN(A1-A2),"黒","白")
別解その2
2の倍数の場合、足し算をしても良いです。偶奇が一致している場合、足し算をすると偶数になります。
引き算の代わりに、足し算をして判定しても良いです。
- =IF(ISEVEN(A1+A2),"黒","白")
<補足>足し算が使えるのは2の倍数(偶数・奇数)だけです。他の倍数は必ず引き算をしなければなりません。
解説は以上です。