わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel・MOD関数】余りの周期性や規則性を利用して、同じ数字の繰り返しを考える


割り算で、元の数(割られる数)が1つずつ増えると、余りも1つずつ増えますが、割り切れると余りは0になります。4で割ったときの余りは「1,2,3,0」の繰り返しとなります。このような繰り返す性質のことを「周期性」といいます。

日常生活にはこの周期性を利用したものがたくさんあります。曜日は7つの曜日が周期的に繰り返されます。当番や勤務シフトは、平等かつ等間隔に担当となるように割り振るものです。オリンピックは4年に1回、世界陸上は2年に1回です。このような周期性のある事柄や計算にはMOD関数が使われます。

そこで、今回は、MOD関数を用いて繰り返しの数列を作ること、繰り返しの連番を振ること、MOD関数を利用した文章題を出題します。

目次

1.連番を利用して繰り返しの数列を作る

問題

1から始まる連番を入力した。これを利用して、0,1,2,3を繰り返す数列を作りなさい。また、1,2,3,4を繰り返す数列を作りなさい。

f:id:waenavi:20201128104416j:plain

 

解説

A列には数値が入力されています。MOD関数を挿入します(参考:【Excel】MOD関数の使い方、INT関数との関係、マイナスの場合の余り)。4で割ったときの余りを求めます。1,2,3,0の順で繰り返す数列になります。

f:id:waenavi:20201128105924j:plain

 

MOD関数を用いて、4で割ったときの余りを求めると、0から3までの4種類の整数になり、4つの数字が繰り返されます。

f:id:waenavi:20201128115137j:plain

 

これは、すべての数を4つのグループに分けるのと同じです。

f:id:waenavi:20201128152559j:plain

 

この繰り返しを1つずらすには、元の数を1つ減らします。

f:id:waenavi:20201128152951j:plain

 

これで完成です。0,1,2,3の順で繰り返す数列になります。

  • =MOD(A1-1,4)

f:id:waenavi:20201128153235j:plain

 

4種類の数字を繰り返す数列を作るには、MOD関数を用いて4で割ったときの余りを求めます。X個の種類の数字を繰り返すときには、Xで割ったときの余りを求めます。

f:id:waenavi:20201128153733j:plain

 

さらに1を足します。これで1から始まって、1,2,3,4の順で繰り返す数列になります。

  • =MOD(A1-1,4)+1

f:id:waenavi:20201128153843j:plain

 

1から始まる連番を、1~4を繰り返す数列にするには、元の数から1を引いた数を4で割り、その余りに1を足します。1から始まる連番を、1~Xを繰り返す数列にするには、元の数から1を引いた数をXで割り、その余りに1を足します。

f:id:waenavi:20201130094900j:plain

 

2.複数列に並べたときの列番号

問題

A~F列に、1001から始まる連番を横向きに入力した。セルH1に入力した値が左から何列目に入力されているかを調べたい。セルH2に数式を入力して列数を求めなさい。

f:id:waenavi:20201130095529j:plain

 

解説

6列に入力されているのは1ずつ増える連番であり、6列に並べたときの列番号は1,2,3,4,5,6の繰り返しになりますので、連番を繰り返しの数列に変換していることから、MOD関数が使えることが分かります。

f:id:waenavi:20201130095931j:plain

 

1001から始まる連番を入力していますが、開始番号である1001を引くと0から始まる連番になります。

f:id:waenavi:20201130100129j:plain

 

数値を6列で入力するのは、連番を6つのグループに分けているのと同じです。6で割ったときの余りを求めると0~5になります。これに1を足せば列番号になります。

f:id:waenavi:20201130100232j:plain

 

MOD関数を挿入します。元の数から先頭の数(開始番号)を引きます。

f:id:waenavi:20201130100312j:plain

 

6で割ったときの余りを求めます。1を足します。これで完成です。

  • =MOD(H1-A2,6)+1

f:id:waenavi:20201130100350j:plain

 

3.降順の数列

問題

セルB1に西暦年4桁を入力した。次回の夏季オリンピックまであと何年かを求めなさい。ただし、4の倍数の場合は4年とする。

f:id:waenavi:20201130100628j:plain

 

解説

夏季オリンピックは通常、西暦が4の倍数の年に開催されます。オリンピックが開催された年は次のオリンピックまであと4年です。その翌年はあと3年です。

f:id:waenavi:20201130101234j:plain

 

西暦年は1ずつ増える連番になっているのに対して、次回のオリンピックまでの年数は、4,3,2,1の繰り返しになりますので、MOD関数が使えることが分かります。

f:id:waenavi:20201130101413j:plain

 

ところで、西暦年を4で割ったときの余りは、0,1,2,3の繰り返しになります。元の数が昇順であれば、余りも昇順になります。

f:id:waenavi:20201130110051j:plain

 

これを降順にするには引き算を使います。例えば、3からこれらの数を引くと、3,2,1,0となります。また、4からこれらの数を引くと、4,3,2,1の繰り返しになります。

f:id:waenavi:20201130110339j:plain

 

MOD関数を挿入します。西暦年を4で割ります。

f:id:waenavi:20201130112447j:plain

 

これを4から引きます。これで完成です。

  • =4-MOD(B1,4)

f:id:waenavi:20201130112543j:plain

 

4.等差数列を作る

問題

1から始まる連番を入力した。1,3,5,7,…のように1から始まる奇数の数列を作りなさい。また、1,3,5,7,1,3,5,7,…と繰り返す数列を作りなさい。

f:id:waenavi:20201130112642j:plain

 

解説

はじめの数に、一定の数だけ増え続ける、もしくは減り続ける数列のことを「等差数列」といいます。そして、隣りあう2つの数の差を「公差」といいます。

f:id:waenavi:20201202124235j:plain

 

あらかじめ連番が入力されている場合、公差を掛けることによって、等差数列を作ることができます。奇数は2つずつ増えていますから、2を掛けます。

f:id:waenavi:20201202124537j:plain

 

2を掛けると偶数の列になります。

f:id:waenavi:20201202124631j:plain

 

ここから1を引くことで奇数の列になります。

f:id:waenavi:20201202124704j:plain

 

4つずつ繰り返す数列は、とりあえずMOD関数を用いて4で割ったときの余りを求めます。

f:id:waenavi:20201202125017j:plain

 

MOD関数を挿入します。4で割ったときの余りを求ますが、このとき元の連番が1から始まっていますので、元の数から1を引きます。

  • =MOD(A1-1,4)

f:id:waenavi:20201202125302j:plain

 

これで0,1,2,3の順になります。

f:id:waenavi:20201202125344j:plain

 

1ずつ増えているものを、2ずつ増える数列にするには2倍します。2倍すると、偶数の列になります。

  • =MOD(A1-1,4)*2

f:id:waenavi:20201202125417j:plain

 

これに1を足せば、1から始まる奇数の列になります。

f:id:waenavi:20201202125519j:plain

 

5.INTとMODの組み合わせ

問題

「1-1」「1-2」「1-3」「1-4」「2-1」・・・という文字列を作りなさい。

f:id:waenavi:20201202132716j:plain

 

解説

この文字列のハイフンの前の部分だけを取り出すと、同じ数を4回繰り返す数列になっています。これはINT関数を使います(参考:【Excel】INT関数を使って同じ数を複数個繰り返す数列を作る方法)。

f:id:waenavi:20201202133152j:plain

 

ハイフンの後の部分だけを取り出すと、1,2,3,4を繰り返す数列になっています。これはMOD関数を使いますので、分けて考えましょう。

f:id:waenavi:20201202135125j:plain

 

まずは、INT関数を挿入します。

f:id:waenavi:20201202135222j:plain

 

このとき、元の数から先頭の数を引くことを考えます。元の数が1から始まる連番の場合は、元の数から1を引きます。元の数から1を引いて4で割ります。

  • =INT((A1-1)/4)

f:id:waenavi:20201202135325j:plain

 

つぎに、MOD関数を挿入します。元の数から1を引いて4で割ります。

  • =MOD(A1-1,4)

f:id:waenavi:20201202135416j:plain

 

これらに1を足せば求める文字列になります。それでは、もう一度、先頭から入力してみましょう。まずは、INT関数を挿入します。

f:id:waenavi:20201202135222j:plain

 

元の数から1を引いて4で割ります。1を足します。

f:id:waenavi:20201202135526j:plain

 

ハイフンを連結します。

f:id:waenavi:20201202135553j:plain

 

つぎに、MOD関数を挿入します。元の数から1を引いて4で割ります。1を足します。これで完成です。

  • =INT((A1-1)/4)+1&"-"&MOD(A1-1,4)+1

f:id:waenavi:20201202135630j:plain

 

6.一部の行だけ連番を振る

(1)累計を利用する

問題

B列に数値の「1」を入力した。SUM関数を用いて累計を求めなさい。また、これを利用して、1~5の数値を繰り返し入力しなさい。

f:id:waenavi:20201202154012j:plain

 

解説

SUM関数を用いて累計を求めるには、範囲の始まりを絶対参照、終わりを相対参照にして同じセルを参照します(参考:【Excel】SUM関数の合計する範囲の始点や終点を固定するとどうなるか?)。

  • =SUM($B$1:B1)

f:id:waenavi:20201202154155j:plain

 

これで累計を求めることができます。

f:id:waenavi:20201202154215j:plain

 

1から始まる連番を、1~5の繰り返しの数列にするには、元の数から1を引き、5で割った余りに1を足します。

f:id:waenavi:20201202154442j:plain

 

累計を求めたSUM関数の外側に、MOD関数を挿入します。1を引きます。

f:id:waenavi:20201202154608j:plain

 

5で割ったときの余りを求めます。最後に1を足します。これで、1~5の繰り返しになります。

  • =MOD(SUM($B$1:B1)-1,5)+1

f:id:waenavi:20201202154645j:plain

 

外側に、IF関数を挿入します。

f:id:waenavi:20201202154707j:plain

 

空白の場合は空白を返します(参考:【Excel】空白セルの判定をして、IF関数で「空白文字列」を返す方法と注意点)。

  • =IF(B1="","",MOD(SUM($B$1:B1)-1,5)+1)

f:id:waenavi:20201202154939j:plain

 

これで完成です。

f:id:waenavi:20201202155000j:plain

 

(2)個数を利用する

問題

A列に文字列が入力されている場合は空白とし、それ以外は1~4の数値を繰り返し入力しなさい。

f:id:waenavi:20201202155053j:plain

 

解説

空白のセルを数える関数はCOUNTBLANK関数です(参考:IF関数の条件に「等号や不等号が必要」と思っているなら、IF関数を勉強し直してほしい)。累計と同じように、範囲の始まりだけを固定します。

  • =COUNTBLANK($B$1:B1)

f:id:waenavi:20201202155236j:plain

 

これで空白セルを数えることができます。

f:id:waenavi:20201202155349j:plain

 

設問(1)と同じように、MOD関数を挿入します。1を引きます。4で割ったときの余りに1を足します。

  • =MOD(COUNTBLANK($B$1:B1)-1,4)+1

f:id:waenavi:20201202155501j:plain

 

さらに、外側に、IF関数を挿入します。空白ではない場合に空白を返します。

  • =IF(B1="",MOD(COUNTBLANK($B$1:B1)-1,4)+1,"")

f:id:waenavi:20201202160315j:plain

 

これで完成です。

f:id:waenavi:20201202160339j:plain

 

7.マイナスの余り

問題

A列に、-1から始まる連番を入力した。これを利用して、4,3,2,1を繰り返す数列を作りなさい。

f:id:waenavi:20201202160459j:plain

 

解説

MOD関数を挿入します。4で割ったときの余りを求めます。3,2,1,0を繰り返す数列になります。

f:id:waenavi:20201202160546j:plain

 

マイナスの数も4で割ると、余りは0から3までの数になります。

f:id:waenavi:20201202160737j:plain

 

1を足せば、1から4までの繰り返しになります。

  • =MOD(A1,4)+1

f:id:waenavi:20201202160839j:plain

 

8.マイナスの余りを利用する

(1)マイナスを付けると逆順になる

問題

A列に、1から始まる連番を入力した。これを利用して、3,2,1,3,2,1,…と繰り返す数列を作りなさい。

f:id:waenavi:20201202161158j:plain

 

解説

3個ずつ繰り返しているので、MOD関数を用いて、3で割ったときの余りを求めます。1ずつ増える数列を3で割ると、その余りも1ずつ増えます。

f:id:waenavi:20201202161258j:plain

 

ここで、元の数にマイナスをつけます。2,1,0の繰り返しになります。

  • =MOD(-A1,3)

f:id:waenavi:20201202161341j:plain

 

マイナスを付けると1ずつ減る数列になります。3で割ると、その余りも1ずつ減ります。

f:id:waenavi:20201202164313j:plain

 

1を足せば、3から1までの繰り返しになります。

  • =MOD(-A1,3)+1

f:id:waenavi:20201202164418j:plain

 

(2)オリンピックまでの年数

問題

A列に西暦年4桁を入力した。冬季オリンピックまであと何年かを求めなさい。ただし、開催年は0とする。

f:id:waenavi:20201202164458j:plain

 

解説

冬季オリンピックは通常、西暦が4の倍数の年の2年後に開催されます。開催された年を0とします。その翌年はオリンピックまで3年あります。次回のオリンピックまでの年数は、3,2,1,0の繰り返しになります。このような数列はMOD関数を用いて作ります。

f:id:waenavi:20201202164729j:plain

 

MOD関数を挿入します。

f:id:waenavi:20201202164805j:plain

 

西暦年にマイナスをつけて、4で割ります。

  • =MOD(-A2,4)

f:id:waenavi:20201202164843j:plain

 

2年ずれているので、元の数から2を引きます。これで完成です。

  • =MOD(-A2-2,4)

f:id:waenavi:20201202164947j:plain

 


解説は以上です。


 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ