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

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

【Excel】難しい数式を考えるヒマがあったら作業用の列を挿入しなさい

 

複雑な計算をしようとしてネットで「Excel 関数 ~の計算方法」などと検索して解決策を探そうとしている人は多いです。しかし、横着をしてそのような近道を探そうとしてもゴールには到達できません。それは、世の中にある数学的な計算がすべて関数で解決できるわけではないからです。

例えば、自動販売機に千円札を入れて130円のジュースを買う場合、おつりとして出てくる硬貨は全部で7枚です。もし、自販機の中に500円玉が無かったら、出てくる硬貨は11枚になります。おつりの合計枚数を一発で求める関数は存在しません。

f:id:waenavi:20190313112023j:plain

 

長い数式を入力するのも良い方法ではありません。直接求めようとすること自体が間違いなのです。

f:id:waenavi:20190312181344j:plain

 

「急がば回れ」ということわざがありますが、複雑な計算結果を求めようと考える前に、簡単な問題に分けて考えたほうが早く解決できることもあります。そこで、今回は、作業用のセルまたは列を用意して、途中の計算式を残すことのメリットについて出題します。

 

目次

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

1.作業用変数を使ったほうが修正が楽になる

問題

次のデータを入力しなさい。

f:id:waenavi:20190312213800j:plain

 

解説

「1(日)」と入力してオートフィルをします。

f:id:waenavi:20190312215036j:plain

 

日曜日のままです。

f:id:waenavi:20190312215041j:plain

 

このように1つの列に2つ以上の要素が含まれる場合は、列を分けて、それぞれオートフィルで作ってから連結します。

f:id:waenavi:20190312215045j:plain

 

文字列連結の計算式を入力します。

f:id:waenavi:20190312215051j:plain

 

これで完成です。これをコピーします。

f:id:waenavi:20190312215055j:plain

 

これを別の場所で利用するには、値の貼り付けをします。

f:id:waenavi:20190312215103j:plain

 

Excelでは、データを1列で入力するよりも、2列以上に分けて入力してから、計算によって1列にまとめたほうが速いことがあります。

f:id:waenavi:20190312215109j:plain

 

最終的に必要なデータを得るために、その途中で一時的に入力するデータのことを「作業用変数」または「テンポラリ」(temporary=一時的な)といいます。作業用変数を設けることで作業の効率が良くなります。

f:id:waenavi:20190312215115j:plain

 

例えば、日曜日ではなく水曜日から始まる場合は、このテンポラリの部分を修正すればよいです。

f:id:waenavi:20190312215122j:plain

 

この部分は一時的に入力しているものなので、普段は非表示にしておきます。

f:id:waenavi:20190312215126j:plain

 

2.共通部分は先に計算しておく

問題

財布の中に100円玉5個と1000円札2枚がある。1本130円のジュースを21本購入するにはあといくら必要か、Excelを用いて答えなさい。また、1本140円のジュースを18本買う場合はどうか。

解説

計算に必要なパラメータを入力しながら、問題を整理します。100円玉5個と1000円札2枚があって、130円のジュースを21本購入しようとしています。

f:id:waenavi:20190312220914j:plain

 

パラメータについては次の記事をご覧ください。

 

このとき財布の中身の金額を計算して、そこから130円x21本をひけば、230円の不足であることがわかります。

f:id:waenavi:20190312220917j:plain

 

140円のジュース18本を計算するには、絶対参照をして計算をします。

f:id:waenavi:20190312220921j:plain

f:id:waenavi:20190312220926j:plain

 

この2つの計算式で、財布の中身を計算する式が2回出てきています。同じ計算を2回することは無駄なことです。

f:id:waenavi:20190312220929j:plain

 

また、仮に、500円玉が1個あったらこれらの計算式を直す必要があり、もっと長い式になります。

f:id:waenavi:20190312221922j:plain

 

このように同じ計算を2回以上繰り返している場合、その部分のことを共通部分式Common subexpression )といいます。共通部分式はとても無駄な計算です。

f:id:waenavi:20190312221926j:plain

 

共通部分式は、あらかじめ別のセルに計算しておきます。これは一時的に計算しているだけであり、作業用変数です。

f:id:waenavi:20190312221933j:plain

 

財布の中身の合計を計算しておきます。

f:id:waenavi:20190312221948j:plain

 

できれば購入金額も別に計算して、引き算をするだけで過不足を求めるとわかりやすくなります。

f:id:waenavi:20190312222001j:plain

f:id:waenavi:20190312222004j:plain

 

仮に、500円玉が1個あったら、財布の中身の計算式を直すだけで済みます。

f:id:waenavi:20190312222015j:plain

 

3.計算結果の補正は別の列で行うこと

問題

A列の価格から2割引きした価格をB列に求めた。

f:id:waenavi:20190312233407j:plain

 

さらに、売れている商品は少し価格を上げ、売れていない商品は少し価格を下げるため、B列の計算式を修正しようとしている。この問題点を指摘したうえで正しい表に直しなさい。

f:id:waenavi:20190312233606j:plain

 

解説

まず、この式で割引率を直接計算式に入力していますが、割引率は、この計算の前提となるパラメータですから、別のセルに割引率を入力するべきです。1から割引率を引いて絶対参照にします。

f:id:waenavi:20190312233941j:plain

 

オートフィルによって計算式を入れるべき列について、計算結果を手動で調整しようとしていますが、これも誤りです。

f:id:waenavi:20190312233946j:plain

 

オートフィルによって入力した計算式は同じ計算式が入っているものと推定されるので、途中で違う計算式が入っているのは迷惑です。

f:id:waenavi:20190312233950j:plain

 

1列増やします。調整額を入れます。

f:id:waenavi:20190312233953j:plain

 

これを加算した式を入れます。

f:id:waenavi:20190312233957j:plain

 

これで完成です。

f:id:waenavi:20190313001140j:plain

 

計算した結果を手動で調整する場合は、別の列で調整します。ちなみに、この列も作業用変数です。

f:id:waenavi:20190312234001j:plain

 

4.スモールステップに分けたほうが再利用しやすい

問題

地上18階から15階まで階段で下りるのにかかる時間を計測したら92秒であった。このペースで30階から下りたとき、15分以内に1階まで下りられるか計算しなさい。また、20階から40階までの人が1階に下りるまでの時間をそれぞれ計算しなさい。なお、Excelを用いて解き、計算過程と結果が分かるようにしなさい。

解説

問題を整理するため、与えられたパラメータを入力します。18階から15階まで92秒で階段を降りる人が、30階から1階まで下りる時間を計算します。

f:id:waenavi:20190313003344j:plain

 

計算式は92/(18-15)*(30-1)/60で、15分以内であることがわかりますが、・・・式が長すぎます。

f:id:waenavi:20190313003347j:plain

f:id:waenavi:20190313003351j:plain

 

計算式が長くなるのはよくあることですが、後で解説を付けなければわからないような計算式は、計算式として不適切です。

f:id:waenavi:20190313003354j:plain

 

長い計算式は、できるだけ細かく分けて計算過程が分かるようにします。

f:id:waenavi:20190313003357j:plain

 

1列挿入します。まず、引き算をします。

f:id:waenavi:20190313003402j:plain

 

3階分下りるのに92秒で、29階分下りるのに何秒かかるかという問題になります。

f:id:waenavi:20190313003405j:plain

 

このように少しずつ問題を簡単にしていきます。92/3で1階下りるのにかかる時間が出ます。

f:id:waenavi:20190313003409j:plain

 

これを29倍すれば答えになります。

f:id:waenavi:20190313003414j:plain

 

最後に60秒で割れば、秒を分に直すことができます。

f:id:waenavi:20190313003419j:plain

 

15分以内に下りられることがわかります。

f:id:waenavi:20190313003424j:plain

 

出発を20階から40階までとして同じ計算をするときは、1階分降りるのにかかる時間を絶対参照にします。

f:id:waenavi:20190313003428j:plain

f:id:waenavi:20190313003433j:plain

 

3つの計算式をそのままコピーするだけで再利用できます。

f:id:waenavi:20190313003436j:plain

 

このように計算式を分割して、計算過程を書いておくことで、あとで、表を修正するときに楽になります。

f:id:waenavi:20190313003440j:plain

 

この時に用いたセルは作業用変数です。

f:id:waenavi:20190313003443j:plain

 

5.難しい関数を使おうとしてはいけない

問題

AとBの2つの預金通帳があり、それぞれ残高が100万円と30万円である。「Aの残高の20%をBに移す」という作業を繰り返すとき、Bの残高が100万円を超えるのは何回目の作業の時か。Excelを用いて求め、計算過程と結果が分かるようにしなさい。

解説

数学的に考えると対数関数で「log0.30.8」を求めたらよいので、Excelでは「=LOG(0.3,0.8)」と入力して、答えは6回となります。しかし、解説の必要な計算式は無意味です。後日、その数式を見たときに何を求めているのか分からなくなります。

f:id:waenavi:20190313013215j:plain

 

Aの残高が100万円で、Bの残高が30万円です。そして、Aの残高のうちの20%をBに移します。

f:id:waenavi:20190313013219j:plain

 

Aの20%で、移動する金額を出します。

f:id:waenavi:20190313013222j:plain

 

20万円です。これをAからBに移しますから、Aは80万円(=A2-D2)、Bは50万円(=B2+D2)となります。

f:id:waenavi:20190313013227j:plain

 

さらにこの20%を計算して、さらにAとBの残高を出します。これを繰り返していきます。

f:id:waenavi:20190313013231j:plain

 

繰り返す計算をするときは、繰り返している部分を次の行に書いて、できるだけ縦長の表になるようにします。

f:id:waenavi:20190313013234j:plain

 

あらかじめ縦に回数を入力しておきます。

f:id:waenavi:20190313013237j:plain

 

20%を求めます。

f:id:waenavi:20190313013242j:plain

 

1回目のAの残高は100万-20万で80万、Bの残高は50万です。

f:id:waenavi:20190313013245j:plain

f:id:waenavi:20190313013248j:plain

 

この3つをオートフィルすることで残高が求められます。Bの残高が100万円を超えるのは6回目です。

f:id:waenavi:20190313013251j:plain

 


解説は以上です。


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

この記事は、わえなび実力養成講座「ファンダメンタルExcel Program5」5-14、5-15 の2本のYoutube動画を書き起こして、加筆修正したものです。動画もぜひご覧ください。

 


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

チャンネル登録(無料)をすると最新の動画をチェックできます。
初心者向けの動画が大量にあります。

 

 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]