複雑な計算をしようとしてネットで「Excel 関数 ～の計算方法」などと検索して解決策を探そうとしている人は多いです。しかし、横着をしてそのような近道を探そうとしてもゴールには到達できません。それは、世の中にある数学的な計算をすべてExcel関数で解決できるわけではないからです。
例えば、自動販売機に千円札を入れて130円のジュースを買う場合、おつりとして出てくる硬貨は全部で7枚です。もし、自販機の中に500円玉が無かったら、出てくる硬貨は11枚になります。おつりの合計枚数を一発で求めるExcel関数は存在しません。
長い数式を入力するのも良い方法ではありません。直接求めようとすること自体が間違いなのです。「急がば回れ」ということわざがありますが、複雑な計算結果を求めようと考える前に、簡単な問題に分けて考えたほうが早く解決できることもあります。そこで、今回は、作業用のセルまたは列を用意して、途中の計算式を残すことのメリットについて出題します。
目次
問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。
- １．作業用変数を使ったほうが修正が楽になる
- ２．共通部分は先に計算しておく
- ３．計算結果の補正は別の列で行うこと
- ４．スモールステップに分けたほうが再利用しやすい
- ５．難しい関数を使おうとしてはいけない
- ６．動画版はこちら（無料）
１．作業用変数を使ったほうが修正が楽になる
問題
次のデータを入力しなさい。
解説
「１（日）」と入力してオートフィルをします。
日曜日のままです。
このように1つの列に2つ以上の要素が含まれる場合は、列を分けて、それぞれオートフィルで作ってから連結します。
文字列連結の計算式を入力します。
これで完成です。これをコピーします。
これを別の場所で利用するには、値の貼り付けをします。
Excelでは、データを1列で入力するよりも、2列以上に分けて入力してから、計算によって1列にまとめたほうが速いことがあります。
最終的に必要なデータを得るために、その途中で一時的に入力するデータのことを「作業用変数」または「テンポラリ」（temporary＝一時的な）といいます。作業用変数を設けることで作業の効率が良くなります。
例えば、日曜日ではなく水曜日から始まる場合は、このテンポラリの部分を修正すればよいです。
この部分は一時的に入力しているものなので、普段は非表示にしておきます。
２．共通部分は先に計算しておく
問題
財布の中に100円玉５個と1000円札２枚がある。１本130円のジュースを21本購入するにはあといくら必要か、Excelを用いて答えなさい。また、１本140円のジュースを18本買う場合はどうか。
解説
計算に必要なパラメータを入力しながら、問題を整理します。100円玉５個と1000円札２枚があって、130円のジュースを21本購入しようとしています。
パラメータについては次の記事をご覧ください。
このとき財布の中身の金額を計算して、そこから130円ｘ21本をひけば、230円の不足であることがわかります。
140円のジュース18本を計算するには、絶対参照をして計算をします。
この2つの計算式で、財布の中身を計算する式が2回出てきています。同じ計算を2回することは無駄なことです。
また、仮に、500円玉が1個あったらこれらの計算式を直す必要があり、もっと長い式になります。
このように同じ計算を2回以上繰り返している場合、その部分のことを共通部分式（ Common subexpression ）といいます。共通部分式はとても無駄な計算です。
共通部分式は、あらかじめ別のセルに計算しておきます。これは一時的に計算しているだけであり、作業用変数です。
財布の中身の合計を計算しておきます。
できれば購入金額も別に計算して、引き算をするだけで過不足を求めるとわかりやすくなります。
仮に、500円玉が1個あったら、財布の中身の計算式を直すだけで済みます。
３．計算結果の補正は別の列で行うこと
問題
A列の価格から２割引きした価格をB列に求めた。
さらに、売れている商品は少し価格を上げ、売れていない商品は少し価格を下げるため、B列の計算式を修正しようとしている。この問題点を指摘したうえで正しい表に直しなさい。
解説
まず、この式で割引率を直接計算式に入力していますが、割引率は、この計算の前提となるパラメータですから、別のセルに割引率を入力するべきです。１から割引率を引いて絶対参照にします。
オートフィルによって計算式を入れるべき列について、計算結果を手動で調整しようとしていますが、これも誤りです。
オートフィルによって入力した計算式は同じ計算式が入っているものと推定されるので、途中で違う計算式が入っているのは迷惑です。
1列増やします。調整額を入れます。
これを加算した式を入れます。
これで完成です。
計算した結果を手動で調整する場合は、別の列で調整します。ちなみに、この列も作業用変数です。
４．スモールステップに分けたほうが再利用しやすい
問題
地上18階から15階まで階段で下りるのにかかる時間を計測したら92秒であった。このペースで30階から下りたとき、15分以内に１階まで下りられるか計算しなさい。また、20階から40階までの人が１階に下りるまでの時間をそれぞれ計算しなさい。なお、Excelを用いて解き、計算過程と結果が分かるようにしなさい。
解説
問題を整理するため、与えられたパラメータを入力します。18階から15階まで92秒で階段を降りる人が、30階から1階まで下りる時間を計算します。
計算式は92/(18-15)*(30-1)/60で、15分以内であることがわかりますが、・・・式が長すぎます。
計算式が長くなるのはよくあることですが、後で解説を付けなければわからないような計算式は、計算式として不適切です。
長い計算式は、できるだけ細かく分けて計算過程が分かるようにします。
1列挿入します。まず、引き算をします。
3階分下りるのに92秒で、29階分下りるのに何秒かかるかという問題になります。
このように少しずつ問題を簡単にしていきます。92/3で1階下りるのにかかる時間が出ます。
これを29倍すれば答えになります。
最後に60秒で割れば、秒を分に直すことができます。
15分以内に下りられることがわかります。
出発を20階から40階までとして同じ計算をするときは、1階分降りるのにかかる時間を絶対参照にします。
3つの計算式をそのままコピーするだけで再利用できます。
このように計算式を分割して、計算過程を書いておくことで、あとで、表を修正するときに楽になります。
この時に用いたセルは作業用変数です。
５．難しい関数を使おうとしてはいけない
問題
AとBの２つの預金通帳があり、それぞれ残高が100万円と30万円である。「Aの残高の20％をBに移す」という作業を繰り返すとき、Bの残高が100万円を超えるのは何回目の作業の時か。Excelを用いて求め、計算過程と結果が分かるようにしなさい。
解説
数学的に考えると対数関数で「log0.30.8」を求めたらよいので、Excelでは「=LOG(0.3,0.8)」と入力して、答えは6回となります。しかし、解説の必要な計算式は無意味です。後日、その数式を見たときに何を求めているのか分からなくなります。
Aの残高が100万円で、Bの残高が30万円です。そして、Aの残高のうちの20％をBに移します。
Aの20％で、移動する金額を出します。
20万円です。これをAからBに移しますから、Aは80万円（=A2-D2）、Bは50万円（=B2+D2）となります。
さらにこの２０％を計算して、さらにAとBの残高を出します。これを繰り返していきます。
繰り返す計算をするときは、繰り返している部分を次の行に書いて、できるだけ縦長の表になるようにします。
あらかじめ縦に回数を入力しておきます。
20%を求めます。
1回目のAの残高は100万-20万で80万、Bの残高は50万です。
この3つをオートフィルすることで残高が求められます。Bの残高が100万円を超えるのは6回目です。
解説は以上です。
６．動画版はこちら（無料）
この記事は、わえなび実力養成講座「ファンダメンタルExcel Program5」5-14、5-15 の２本のYoutube動画を書き起こして、加筆修正したものです。動画もぜひご覧ください。
- ファンダメンタルExcel 5-14 作業用変数【わえなび】 （ファンダメンタルExcel Program5 計算式の入力） - YouTube
- ファンダメンタルExcel 5-15 スモールステップに分ける【わえなび】 （ファンダメンタルExcel Program5 計算式の入力） - YouTube
