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

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

【Excel】計算式で数列を求めることによって規則的にデータを並べる練習


数や文字を規則的に並べるにはExcelを用いると便利です。このブログでは、Excelで「繰り返しの計算」を練習することの重要性について何度も述べてきました(オートフィルは累積的に同じ計算を繰り返すときにも使う)。

例えば、「春夏秋冬」はこの順番で訪れ、この順序が変わることはありません。春夏秋冬春夏秋冬春夏秋冬春夏秋冬・・・と連続して並べるような場合、Excelを使うと便利です。コピーまたはオートフィルを使います。

 

さらに、先頭の4文字が「東南西北」に変わった場合、自動的に「東南西北東南西北東南西北・・・」となるようにすることもできます。4つのセルに「喜怒哀楽」と入力すると、自動的に「喜怒哀楽喜怒哀楽喜怒哀楽・・・」となります。このように、最初のセルを入力するだけで、連動して、それ以降のセルも変わるようにするには計算式を用いる必要があります。

そこで、今回は計算式を用いて数や文字を並べる練習をするとともに、Excelで数列を作る問題を出題します。

 

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.繰り返しに規則性がある数列

問題

できるだけ計算式を用いて、次の図のように規則的に並べなさい。

f:id:waenavi:20190302204712j:plain

 

解説

(1)2つを交互に繰り返し

何個かの文字列を繰り返す配列は参照するだけです。

f:id:waenavi:20190302204911j:plain

 

最初に2つは直接入力して、その後は2つ前のセルを参照します。

f:id:waenavi:20190302204915j:plain

f:id:waenavi:20190302204921j:plain

 

AとBを入力します。

f:id:waenavi:20190302205156j:plain

 

Aを参照します(参考:【Excel数式】セルの「参照」が分からなければ相対参照は理解できない)。

f:id:waenavi:20190302205253j:plain

 

これでオートフィルをすれば2つ上のセルを参照しますから、ABの繰り返しになります。

f:id:waenavi:20190302205305j:plain

 

(2)4つの繰り返し

次に4つを繰り返します。同じパターンを繰り返しているものは参照するだけです。

f:id:waenavi:20190302205359j:plain

 

4つを入力します。4つ上を参照します。

f:id:waenavi:20190302205515j:plain

 

これでオートフィルをすれば4つ上のセルを参照しますから、これで完成です。

f:id:waenavi:20190302205520j:plain

 

最初の4つは直接入力してもよいですが、最初の3つだけ同じ文字にするときは、このなかで参照します。

f:id:waenavi:20190302205704j:plain

 

このように計算式を入れておけば連動します。

f:id:waenavi:20190302205707j:plain

f:id:waenavi:20190302205710j:plain

f:id:waenavi:20190302205735j:plain

 

(3)3つ前のセルを参照して加算

これは、3つずつ同じ数字になっています。3つずつ規則性を考えます。2ずつ加算しています。

f:id:waenavi:20190302210153j:plain

 

1、1、1と入力します。2を足します。

f:id:waenavi:20190302210157j:plain

 

これでオートフィルをします。3,3,3,5,5,5となります。

f:id:waenavi:20190302210205j:plain

 

最初の3つの数は直接入力することも可能ですが、計算式を入れておけば同じ数値で連動させることもできます。

f:id:waenavi:20190302210209j:plain

f:id:waenavi:20190302210212j:plain

 

(4)4つ前のセルを参照して加算

これは4つずつに区切って考えます。10ずつ増えているように見えます。10ずつ加算していけばよいことが分かります。

f:id:waenavi:20190302210315j:plain

 

1,2,3,4を入力します。10を足します。

f:id:waenavi:20190302210320j:plain

 

オートフィルをします。

f:id:waenavi:20190302210323j:plain

 

最初の4つの数は「+1」の計算式で連動させることもできます。

f:id:waenavi:20190302210327j:plain

f:id:waenavi:20190302210331j:plain

 

(5)フィボナッチ数列

5+8=13です。8+13=21です。このように2つの隣り合う数を足すと、その次の数になる数列で、フィボナッチ数列と呼ばれるものです。

f:id:waenavi:20190302210401j:plain

 

上の2つの数を足します。

f:id:waenavi:20190302210405j:plain

 

これをオートフィルします。これで完成です。

f:id:waenavi:20190302210437j:plain

 

2.サイコロの目の組み合わせ

問題

1行目から6行目まで数を入力して、7行目以降を計算式で入力したい。7行目にどのような計算式を入れればよいか。

f:id:waenavi:20190302204743j:plain

 

解説

A列は、6つの同じ数が並び、1ずつ増えるパターンです。6つ上のセルに1を加算します。

f:id:waenavi:20190302211349j:plain

 

B列は1から6の繰り返しなので単なる参照です。

f:id:waenavi:20190302211352j:plain

 

これをオートフィルすれば完成です。これは、2つのサイコロA、Bの目のパターンで、全部で36通りです。

f:id:waenavi:20190302211356j:plain

 

3.等差数列の作り方

問題

セルB1に初期値、セルB2に増分を入力した。これを利用して、図のように4行目以降に計算式を入力しなさい。

f:id:waenavi:20190302211549j:plain

 

解説

このように一定の間隔で増える等差数列は、初期値といくつずつ増やすかが分かれば計算できます。

f:id:waenavi:20190302211844j:plain

 

最初のセルは初期値を参照します。

f:id:waenavi:20190302211847j:plain

 

次に上のセルに増分を足しますが、増分は絶対参照です(参考:【Excel】絶対参照は表の形とコピーの有無で判断できるように練習せよ)。

f:id:waenavi:20190302211851j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190302211855j:plain

 

初期値や増分を変えることによっていろいろな数列を作ることができます。また増分をマイナスにすると減少する数列になります。

f:id:waenavi:20190302212308j:plain

 

4.縦横ともに等差数列

問題

できるだけ計算式を用いて、次の図のように10列の数列を作りなさい。

f:id:waenavi:20190302212556j:plain

 

解説

横方向は1ずつ加算しています。縦方向は10加算します。

f:id:waenavi:20190302213404j:plain

 

1行目に1を入力して1ずつ加算します。

f:id:waenavi:20190302213408j:plain

f:id:waenavi:20190302213413j:plain

 

次に1行あけて10を加算します。

f:id:waenavi:20190302213417j:plain

 

その下にある空白のセルと2つのセルを選択します。

f:id:waenavi:20190302213420j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190302213424j:plain

 

5.規則的な数列が混ざっている場合は分離して考える

問題

(1)次の数列を小数に直しなさい。

f:id:waenavi:20190302212609j:plain


(2)次の文字を入力しなさい。

f:id:waenavi:20190302212631j:plain

 

解説

分子は1ずつ増え、分母は2ずつ増えています。この場合は、分母と分子を分けて考えます。

f:id:waenavi:20190302214916j:plain

 

1を入力して、1ずつ増やします。

f:id:waenavi:20190302214920j:plain

 

1を入力して、2ずつ増やします。

f:id:waenavi:20190302214923j:plain

f:id:waenavi:20190302214926j:plain

 

これを用いて割り算します。

f:id:waenavi:20190302214930j:plain

 

これで完成です。

f:id:waenavi:20190302214934j:plain

 

文字列連結をします(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。

f:id:waenavi:20190302214938j:plain

f:id:waenavi:20190302214944j:plain

 

同じように1を4つ入力するとともにA,B,C,Dと入力します。

f:id:waenavi:20190302214947j:plain

 

数字は4つ上のセルに1を足します。

f:id:waenavi:20190302214951j:plain

 

アルファベットは4つ上のセルを参照するだけです。

f:id:waenavi:20190302214954j:plain

 

文字列連結をします。

f:id:waenavi:20190302214959j:plain

 

オートフィルをします。

f:id:waenavi:20190302215003j:plain

 

6.繰り返しの部分と固定の部分を切り離す

問題

次の図のように入力しなさい。

f:id:waenavi:20190302212640j:plain

 

解説

この図は繰り返しによって増えている部分と変わらない固定の部分があります。この場合は切り離して考えます。

f:id:waenavi:20190302215201j:plain

 

まず繰り返しの部分だけ作ります。ハイフンを入力します。ハイフンを、絶対参照と相対参照を連結することによって繰り返します(参考:オートフィルは累積的に同じ計算を繰り返すときにも使う)。

f:id:waenavi:20190302215207j:plain

 

これに左端の文字と右端の文字を連結します。

f:id:waenavi:20190302215214j:plain

 

オートフィルします。中央揃えにします。これで完成です。

f:id:waenavi:20190302215223j:plain

 

使用しない列は非表示にすればよいです。

f:id:waenavi:20190302215227j:plain

 

7.掛け算九九の表

 

(1)複合参照

問題

掛け算九九の表を作ろうとしている。A列と1行目のセルを参照して作成しなさい。

f:id:waenavi:20190302212724j:plain

 

解説

掛け算九九の表のように1行目と1列目に数値があり、これを用いて数を並べる場合、2つの方法があります。1つは複合参照による方法です(参考:【Excel複合参照】行固定や列固定のドルマークの意味と練習問題)。

f:id:waenavi:20190302220439j:plain

 

まず1行目を作ります。つぎにA列を作ります。これを掛け算します。1行目とA列を固定して複合参照にします。

f:id:waenavi:20190302220444j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190302220448j:plain

 

(2)繰り返しの計算

問題

掛け算九九の表を作ろうとしている。A列の値を、2倍、3倍、・・・、9倍して作成しなさい。

f:id:waenavi:20190302212724j:plain

 

解説

A列を基準にして数を増やしていく方法があります。

f:id:waenavi:20190302220451j:plain

 

まずA列を作ります。次に絶対参照と相対参照を加算することによって、2倍、3倍とします。

f:id:waenavi:20190302220455j:plain

 

これで1行目ができます。これを複数行にするには絶対参照ではなく複合参照にします。A列だけ固定にします。

f:id:waenavi:20190302220458j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190302220502j:plain

 

8.スモールステップで考えよう

問題

A~Fの6人で4か所の掃除を交代で行うことになった。1→2→休→3→4→休→1→・・・のようなローテーションにしたい。次の表を完成させなさい。

f:id:waenavi:20190302221145j:plain

 

解説

いきなり複雑な表を作ろうとせず、簡単な表を利用する方法を考えます。たとえば、列の順番を変えれば比較的簡単に作れます。

f:id:waenavi:20190302221213j:plain

 

A~Fを入力します。左上のセルを参照します。

f:id:waenavi:20190302221440j:plain

 

右端のセルを参照します。

f:id:waenavi:20190302221445j:plain

 

これで第1段階は完成です。

f:id:waenavi:20190302221448j:plain

 

ここから別の列に参照します。

f:id:waenavi:20190302221452j:plain

f:id:waenavi:20190302221456j:plain

 

必要のない列は非表示にします。これで完成です。

f:id:waenavi:20190302221500j:plain

 

9.階差数列

問題

数列 3,5,9,15,23,33,45,59,… の100番目の数をExcelで求めなさい。

解説

まずはExcelでこの数列を入力します。

f:id:waenavi:20190302221755j:plain

 

規則性が分からないときは引き算をしてみます。5-3をします。

f:id:waenavi:20190302222013j:plain

 

オートフィルをします。2ずつ増えています。

f:id:waenavi:20190302222017j:plain

 

このように隣り合う2つの項の差を、階差といい、階差の数列を階差数列といいます。

f:id:waenavi:20190302222026j:plain

 

階差数列に規則性があるときは、まずはこれを作ってから累計を求めます。

f:id:waenavi:20190302222030j:plain

 

階差数列は2から始まって2ずつ増える数列です。

f:id:waenavi:20190302222038j:plain

 

元の数列は3から始まっています。この3に左の2を加えます。

f:id:waenavi:20190302222045j:plain

 

オートフィルをします。これで元の数列を求めることができます。100番目は9903となります。

f:id:waenavi:20190302222054j:plain

 


解説は以上です。


10.動画版はこちら(無料)

この記事は、わえなび実力養成講座「Excel新演習2」Program 2-3、2-4、2-6-2の3本のYoutube動画を書き起こして、加筆修正したものです。

 

 


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