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

ワードとエクセルの練習問題の動画を「Youtube」で無料公開しています

【Excel】オートフィルは累積的に同じ計算を繰り返すときにも使う

オートフィルはコピーの一種であり、同じデータまたは連続データを入力するときに使うのが一般的です。

しかし、1つ前のセル、または2つ前のセルを参照する式をオートフィルすることによって、同じ計算を繰り返すという手法があります。

f:id:waenavi:20181027130756j:plain 

 

Excelの画面に向かって同じ作業を繰り返して、忙しそうに仕事をしている人をたまに見かけますが、そんな面倒な作業はExcelに任せたほうが、仕事の効率化につながります。

f:id:waenavi:20181027125957g:plain

そこで、今回は、オートフィルを用いてひたすら同じ計算を繰り返して、「文字を連結する」「逆算する」「収束値を求める」といった計算方法について解説します。

 

目次

1.規則的な数列の基本パターン

問題

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

f:id:waenavi:20181026213533j:plain

 

解説

1行目を変えると、連動して2行目以降も変わります。 

f:id:waenavi:20181026213542j:plain

 

隣り合う2つの数に何らかの規則がある場合、オートフィルによって計算式を繰り返します。

f:id:waenavi:20181026214054j:plain

 

基本パターン(1)同じ数の連続

例えば、同じ数が連続して並ぶような場合、最初の数が決まれば、あとはそれを参照すればよいです。

f:id:waenavi:20181026214058j:plain

 

最初に、適当な数値を入れます。イコールで参照します。

f:id:waenavi:20181026214104j:plain


オートフィルをします。

f:id:waenavi:20181026214107j:plain

 

相対参照によって、「=A2」「=A3」「=A4」「=A5」・・・と上のセルを参照する数式が入りますから、すべて同じ数になります。

f:id:waenavi:20181026214112j:plain

 

最初のセルを変えるだけですべてが変わります。

f:id:waenavi:20181026214115j:plain

 

基本パターン(2)等差数列、等比数列

等差数列や等比数列のように、同じ数を足したりかけたりしてできる数列は、足し算や掛け算を繰り返します。

f:id:waenavi:20181026214119j:plain

 

最初に、適当な数値を入れます。上のセルに1を足します。

f:id:waenavi:20181027105517j:plain 


オートフィルをすると相対参照によって、「=B2+1」「=B3+1」「=B4+1」「=B5+1」・・・と上のセルに1を足す数式が入りますから、1ずつ増える等差数列になります。

f:id:waenavi:20181027105520j:plain

 

最初に、適当な数値を入れます。上のセルに2をかけます。

f:id:waenavi:20181027105523j:plain


オートフィルをすると「=C2*2」「=C3*2」「=C4*2」「=C5*2」・・・と上のセルを2倍する数式が入りますから、2倍の等比数列になります。

f:id:waenavi:20181027105526j:plain

 

基本パターン(3)同じ計算(漸化式)による繰り返し

「3、7、15、31、63・・・」も規則性があります。

3を2倍して1をたすと7です。7を2倍して1をたすと15です。2倍して1をたす計算を繰り返しています。数学では「an+1=2an+1」という式になります(漸化式)。このように出てきた答えに対して同じ計算を繰り返す場合も、オートフィルを使えばすぐにできます。

f:id:waenavi:20181027105530j:plain

 

最初に、適当な数値を入れます。上のセルを2倍して1をたします。

f:id:waenavi:20181027105533j:plain


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

f:id:waenavi:20181027105536j:plain

 

基本パターン(4)文字列の繰り返し

「ちょ」の後に、wが繰り返されています。文字列連結の計算を繰り返します。(文字列連結演算について、くわしくはファンダメンタルExcel Program3-2の動画参照)

f:id:waenavi:20181027105539j:plain

 

最初に「ちょ」と入力します。上のセルに「w」を右側に連結する計算式「=E1&"w"」を入れます。

f:id:waenavi:20181027105542j:plain

 

オートフィルをすると、文字列の繰り返しになり、右側にwwwが増えます。

f:id:waenavi:20181027105554j:plain

 

最初に不等号を入力します。上のセルにハイフンを左側に連結する計算式「="-"&F1」を入力します。

f:id:waenavi:20181027105557j:plain


オートフィルをすると、ハイフンが繰り返して連結され、左側にハイフンが増えます。

f:id:waenavi:20181027105600j:plain

 

2.自分自身を繰り返す

問題

計算式を用いて入力しなさい。

f:id:waenavi:20181027112649j:plain

 

解説

5と入力すると5ずつ増えます。

f:id:waenavi:20181027112847j:plain

 

9と入力すると9ずつ増えます。

f:id:waenavi:20181027112850j:plain

 

最初の数を足し続ける計算は、最初の数に、同じ数を足します(=A1+A1)。そのうち1つだけ絶対参照にします(=$A$1+A1)。

f:id:waenavi:20181027113004j:plain


これで完成です。

f:id:waenavi:20181027113508j:plain

 

上の数に、最初の数を足す計算を繰り返しています。

f:id:waenavi:20181027113511j:plain

 

自分自身を足し続けると、掛け算九九のように2倍、3倍、4倍となります。この計算をするには、絶対参照と相対参照を足します。

f:id:waenavi:20181027113517j:plain

 

これと同じように、絶対参照と相対参照を掛け算します。

f:id:waenavi:20181027113522j:plain


3倍を繰り返して、結局、3の累乗を計算したことになります。

f:id:waenavi:20181027113526j:plain


累乗を求めるには2回同じセルを掛け算して、1つだけ絶対参照にします。

f:id:waenavi:20181027113529j:plain

 

同じ文字列を繰り返すのも、&を使って同じような計算をすればよいです。

f:id:waenavi:20181027113532j:plain

f:id:waenavi:20181027113535j:plain


何文字でも繰り返しになります。

f:id:waenavi:20181027113541j:plain

 

絶対参照と相対参照を連結することによって、同じ文字列の繰り返しになります。

f:id:waenavi:20181027113547j:plain

 

3.2つの計算を交互に繰り返す

問題

算数の授業で「7000からスタートして、4で割るのと、2を足すのを繰り返す」という課題を与えた。そして、答えが1になるまで計算するよう伝えた。この課題は適切か、検討しなさい。

f:id:waenavi:20181027121240j:plain

 

解説

7000を入力します。4で割ります。

f:id:waenavi:20181027121057j:plain

 

この答えに、2をたします。

f:id:waenavi:20181027121107j:plain

 

2つの計算を交互に繰り返すときは、2つを選択してオートフィルをすればよいです。
f:id:waenavi:20181027121110j:plain


4で割って2を足しています。徐々に数が減っていきます。

f:id:waenavi:20181027121143j:plain

 

しかし、これをさらに繰り返すと小数になります。

f:id:waenavi:20181027121617j:plain

 

さらに続けると、2.666と0.666の繰り返しになります。決して答えが1になることはありません。

f:id:waenavi:20181027121623j:plain

 

4.逆算する

問題

最初の数を1として、次の計算を繰り返しなさい。

f:id:waenavi:20181027122459j:plain

 

解説

初めに1を入力します。4をかけます。

f:id:waenavi:20181027122756j:plain


2をひきます。

f:id:waenavi:20181027122758j:plain


2つを選んでオートフィルをします。

f:id:waenavi:20181027122801j:plain


これで完成です。この計算を繰りかえして、最後に4をかけた式で終わります。

f:id:waenavi:20181027122804j:plain

 

例えば、「5464」は「=B13*4」です。

f:id:waenavi:20181027122809j:plain

 

さきほどの問題の「課題」は7000からスタートするのではなく、例えば、5464からスタートすれば、答えが1になっていたはずです。このように初めの数をいくつにすればよいかを逆算することもできます。

f:id:waenavi:20181027122812j:plain

 

5.収束値を求める

問題

セルA1の値を、最初の値として「逆数にして1を加える」という計算を繰り返すと、最終的にどのような値になるか。

f:id:waenavi:20181027123251j:plain

 

解説

逆数は「かけて1になる数」で、xの逆数はx分の1です。Excelでは「1/x」です。

f:id:waenavi:20181027123530j:plain

 

1を割ります。さらに1を加えます。これで逆数にして1を加えたことになります。

f:id:waenavi:20181027123744j:plain


2の逆数は0.5で1を加えると1.5です。オートフィルでこれを繰り返します。約1.618を逆数にして1を加えると、約1.618になるため、これの繰り返しになります。

f:id:waenavi:20181027123747j:plain

 

この約1.618とは、いわゆる黄金比のことです。

f:id:waenavi:20181027123751j:plain

 

6.カンマ区切り

問題

A列に7個の単語がある。

f:id:waenavi:20181027115842j:plain

 

これらをカンマで連結しなさい。

f:id:waenavi:20181027115845j:plain

 

解説

B1にA1を参照する数式(=A1)を入れます。

f:id:waenavi:20181027120111j:plain

 

上のセルにA2を連結する数式(=B1&","&A2)を入れます。

f:id:waenavi:20181027120118j:plain

 

オートフィルによって、上のセルと左のセルの連結が繰り返され、最終的にはすべての値がカンマで連結されます。

f:id:waenavi:20181027120125j:plain

 

7.累計や残高の計算

以上のような繰り返しの計算を利用することで、累計や残高の計算もできます。この続きは、こちらの記事をご覧ください。

www.waenavi.com 


解説は以上です。


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

この記事は、わえなびファンダメンタルExcel Program8-11、8-13、8-14 の動画の内容を書き起こし、加筆修正したものです。

www.youtube.com

www.youtube.com

 

「繰り返しの計算」完全版はYoutubeにすべて無料で公開しております。ぜひ、ご覧ください。


Youtube わえなび公式チャンネル
http://www.youtube.com/c/waenavijp

チャンネル登録をすると最新の動画をチェックできます。

 

 

 

 

 

Copyright(C)2018 waenavi, All rights reserved. [www.waenavi.com 定礎 平成30年8月]