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

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

【Excel】関数の前に「変数」を理解して表計算の本質を知れ!

 

Excelの計算式(または関数)を扱うときの概念として「変数」というものがあります。

ご存知でしょうか?

変数の考え方もわからずに、難しい関数を習っても実際に使いこなすことはできません。

変数は、プログラミングで本格的に習いますが、「箱」に例える人もいれば、「ラベル(名札)」に例える人もいます。ということは、ラベルのついた箱(セル)がたくさん並んでいるExcelを使えば変数が理解できるのではないでしょうか?

f:id:waenavi:20180812231258j:plain 

そこで、今回は、Excelの計算式や関数が使いこなせていない初心者のために、簡単な算数の問題を使って「変数」の考え方を説明します。改めて「表計算作った人すげぇ」と感じていただければ幸いです。

 

目次

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

 

 

1.表計算ソフトが「表」の形をしている理由

問題

長さ200mのまっすぐな道に20m間隔で木を植える。木は何本必要か、Excelを用いて答えなさい。また、10m間隔の場合はどうか。

 

解説

Excelを用いて答える方法は何通りかあります。

回答例(1)

まずは、Excelのシートに200mの道と木の模式図をかいて、数える方法です。

f:id:waenavi:20180812203423j:plain


20m間隔で区切ると、10個のブロックに分かれるので、両端に木を植えた場合(スタートとゴールがわかるように木を植える場合)は11本になり、両端に木を植えない場合(20mの区切りとして木を植える場合)は9本となります。

間隔を10mとするには、列を増やして「絵」を描きなおして数えます。

f:id:waenavi:20180812201852j:plain

 

しかし、Excelは計算するためのソフトであり、お絵かきソフトではありません。
計算をしなければExcelを使っている意味がありません。

f:id:waenavi:20180812203433j:plain

 

回答例(2)

そこで、割り算を使って計算します。
両端に木を植えた場合は200mを20mで割って、1をたせばよいです。
両端に木を植えない場合は200mを20mで割って、1をひけばよいです。

f:id:waenavi:20180812203623j:plain

 

表計算ソフトも、電卓と同じように計算をするソフトなので、単に計算をするだけであれば直接計算式を入力すればよいです。

f:id:waenavi:20180812203700j:plain

 

間隔を10mとして計算しなおすには、1つずつ計算式を直す必要があります。
これは不便です。

f:id:waenavi:20180812203934j:plain

 

回答例(3) 

ところで、西暦1970年代(手書きで計算していた時代)、データが変わるごとに計算式を消してやり直し、もう一度計算式を書きなおすということをしていました。大学や大学院でも難しい計算式を書いて、消して、また書いて、計算しなおして・・・という授業をしていました。

f:id:waenavi:20180812204441j:plain

f:id:waenavi:20180812204648j:plain


表計算ソフトは、この煩わしさを解消するため、変わる可能性のある数(元のデータ)を「変数」として別の場所に置き、それを使って計算できるように開発されました。

f:id:waenavi:20180812204739j:plain

 

変数の値を指示するだけで、同じ計算を自動で繰り返しますので、計算式を入力しなおす必要がなくなります。 

f:id:waenavi:20180812205025j:plain


表計算ソフトが表の形をしているのはなぜでしょうか。

きれいな表を書きたかったからではありません。また、集計やデータベースをしたかったわけでもありません。

f:id:waenavi:20180812205656j:plain

 

変数は、かならずx、y、z、Aなどとアルファベットで名前を付けます(変数名といいます)。名前を付けなければ、他の計算式に利用することができないからです。

f:id:waenavi:20180812205659j:plain


変数が100個あったら、100個の名前を考えなければなりません。しかし、表の形にしておけばそれを考える手間がなくなります。

例えば、セル C1に「20」と入力すると、数式バーは C1が「20」であると表示されます。これは、C1という変数が20であることを表しています。データを入力するだけで、変数が設定できるのです。そして、変数に名前を付けるということを意識することなく変数が使えるのです。

f:id:waenavi:20180812205812j:plain


表計算ソフトが表の形をしているのは、「アルファベット+数字」の名前の付いた変数を大量に設定することができるからです。

f:id:waenavi:20180812210020j:plain

 

以上のことを踏まえて表を作り直します。

木の間隔が20mだったとします。

f:id:waenavi:20180812211130j:plain

 

両端に木を植えた場合は、20をC1で割って1をたします。

f:id:waenavi:20180812211636j:plain


両端に木を植えない場合は、20をC1で割って1をひきます。 

f:id:waenavi:20180812211658j:plain

 

木の間隔を10mにすると、木の本数が連動して計算されます。

f:id:waenavi:20180812211655j:plain


間隔をいろいろ変えると、自動で計算しなおします。

f:id:waenavi:20180812211853j:plain

 

Excelのような表計算ソフトは、既に入力されている計算式を直接修正して使うものではありません。できるだけ計算式を修正しないように、変数と計算式を分離して入力します

f:id:waenavi:20180812211857j:plain

 

 

2.パラメータの設定(変数の宣言)

問題

長さ200mのロープを20mずつに切ると、何本のロープができるか。Excelを用いて回答しなさい

 

解説

単に、200を20で割るだけであれば、直接計算式を入力すれば終わりです。計算機として(電卓の代わりとして)使用するだけであればこれで構いません。

f:id:waenavi:20180812212805j:plain

 

しかし、20mずつ切るところを、10mにしたり、12.5mにしたり、いろいろ変化させる場合、計算式を修正しなければいけません。これは、Excelの使い方として不適切です。

f:id:waenavi:20180812212911j:plain

 

計算をするときの前提条件となる変数のことを、特に「パラメータ(parameter)」ということがあります。この場合、計算式に使われている数値である200や20がパラメータです。

f:id:waenavi:20180812213347j:plain


元のロープの長さと短いロープの長さを初めに入力します。このように、計算する前にパラメータが何かを決めて初めに入力することを、「パラメータを設定する」または「変数を宣言する」といいます。

f:id:waenavi:20180812213635j:plain


これらを参照しながら本数を求めます。

f:id:waenavi:20180812213722j:plain

 

これによって、例えば300mのロープを12mずつに切るといった計算も自動でできます。

f:id:waenavi:20180812214005j:plain


パラメータと計算式を分けて入力することで、計算のやり直しが簡単にできます。

f:id:waenavi:20180812214050j:plain

 

3.再計算を前提とする表を作る練習問題

問題

次の各設問について、いろいろとパラメータを変えて計算し直すことを前提として、Excelを用いて回答しなさい。

(1)2000円の商品を15%値引きしたときの値引き額と値引き後の金額を求めなさい。

(2)6面のサイコロを、1回投げて1を出すのと、5回連続で1を出さないのとでは、確率的にどちらが難しいか。

 

解説

(1)

元の値段が2000円で、それに15%をかけることによって値引き額、85%をかけることによって値引きした後の金額になりますが、このように、それぞれ独立してパーセンテージを入力するのは間違いです。割引率が変わった場合、両方の計算式を直す必要があるからです。

f:id:waenavi:20180812220210j:plain

 

値引き後の金額は、元の値段から割引額を引いて求めます。

f:id:waenavi:20180812220449j:plain


さらに、15%もパラメータとして独立させるべきです。

f:id:waenavi:20180812220854j:plain


このように、商品価格と割引率をパラメータとすることによって、例えば、3000円の24%引きの金額も、パラメータを変えるだけで計算できます。 

f:id:waenavi:20180812220728j:plain

 

(2)

1が出る確率は、6分の1です。
f:id:waenavi:20180812215005j:plain
 
逆に1が出ない確率は、6分の5です。5回連続で1が出ない確率は、6分の5を5乗すればよいです。

f:id:waenavi:20180812215008j:plain

 
1回投げて1を出すほうが確率が低いので難しいということになります。5回連続で1を出さないほうが簡単ということです。

f:id:waenavi:20180812215236j:plain

 
それでは、10回連続だったらどうか、と考えたとします。

この場合のパラメータは、サイコロを投げる回数です。1が出る確率は、6分の1です。

f:id:waenavi:20180812215410j:plain

 

1が出ない確率は、6分の5を回数分だけ累乗します。

f:id:waenavi:20180812215634j:plain

 

10回連続で1を出さないのと、1回で1を出すのとは、ほぼ同じ確率であることがわかります。

f:id:waenavi:20180812215726j:plain

 

さらに、6面のサイコロではなく、20面のサイコロだった場合はどうか、と考えたとします。この場合は、さいころの面の数もパラメータになります。

1を面の数で割ります

f:id:waenavi:20180812215945j:plain


これで20面のサイコロでも計算できます。

f:id:waenavi:20180812220046j:plain

 

 

4.共通部分があればパラメータにする

問題

次の図で、B列は、末尾に「.jpg」を付加する計算する式が入力されている。
「.pdf」として計算しなおしなさい。

f:id:waenavi:20180812221252j:plain

 

解説

pdfに修正して、オートフィルですべての数式を直します。

f:id:waenavi:20180812221517j:plain

 
さらに、別の拡張子に変える可能性があれば、パラメータにすることを考えます。

f:id:waenavi:20180812221605j:plain

 

ここで、すべての計算式を表示します。共通部分があります。オートフィルをした時に、計算式の中に共通部分があればパラメータとして分離すべきです。

f:id:waenavi:20180812221717j:plain


まず、拡張子を入れる場所を用意します。

f:id:waenavi:20180812221953j:plain

 

次に、拡張子にドットを連結します。

f:id:waenavi:20180812221958j:plain

 

共通部分をパラメータにしたときは、それを参照するときには絶対参照になります。

f:id:waenavi:20180812222002j:plain


拡張子をいろいろ変えることができます。

f:id:waenavi:20180812222240j:plain

 

 

5.パラメータを1か所にするか1列に並べるか

問題

模擬店でフランクフルトを1本150円で販売しようとしている。このフランクフルトを作るのに材料費として1本70円がかかる。また、販売本数にかかわらず12000円程度の固定費がかかることが予想されている。

(1)販売で得た収益は、販売員5人で分けるものとするとき、販売本数を0~450本として、30本刻みで1人あたりの収益を求めなさい。

(2)1本当たりの販売価格140円、材料費65円、販売員4人として再計算しなさい。

(3)販売員の人数が4人、5人、6人だった場合の、1人あたりの収益を比較しなさい。

 

解説

(1)

販売本数を0~450本として、30本刻みで入力をします。
(模擬店でフランクフルトを何本ぐらい販売すればよいかが分からないという状況なので、とりあえず本数を入力してみる)

f:id:waenavi:20180812223159j:plain


売上高は、1本150円に本数をかけます。

f:id:waenavi:20180812223540j:plain

 

材料費は、1本70円に本数をかけます。

f:id:waenavi:20180812223545j:plain

 

さらに固定費が、販売本数にかかわらず12000円かかります。
(調理器具を借りる費用、ポスターを作成する費用、出店申請費用、・・・)

f:id:waenavi:20180812223548j:plain


売り上げから、かかった材料費と固定費をひくと収益になります。

f:id:waenavi:20180812223645j:plain


さらに、販売で得た収益を5人で割ると、1人あたりの収益が求められます。

f:id:waenavi:20180812223649j:plain

 

150本が損益分岐点となり、450本のフランクフルトを用意すれば、最大で1人あたり4800円の収入が得られる計算になります。

f:id:waenavi:20180812223736j:plain

 

(2)

計算式の中に登場する数値は、変動することが考えられます。例えば、1本70円の材料費が65円で済んだので、1本150円のところを140円で売ったらどうなるかとか、販売員が1人減って4人になったとか、いろいろな状況が考えられます。

f:id:waenavi:20180812223859j:plain


これらをパラメータとして分離してみましょう。 

f:id:waenavi:20180812224552j:plain

 

売上高は、1本の価格に本数をかけます。1本の価格は絶対参照です。

f:id:waenavi:20180812225029j:plain


材料費は、1本の材料費に本数をかけます。

f:id:waenavi:20180812225033j:plain


固定費は、参照するだけです。

f:id:waenavi:20180812225037j:plain

 

収益は売り上げから費用を引きます。

f:id:waenavi:20180812225040j:plain


1人当たりの収益は、販売で得た収益を人数で割ります。パラメータはすべて絶対参照になります。

f:id:waenavi:20180812225044j:plain

 

販売価格140円、材料費65円、販売員4人の場合はこのような表になります。パラメータがいろいろ変わることを前提として、初めからこのような表を作成するべきです。

f:id:waenavi:20180812225115j:plain


(3)

販売員の数が4人~6人の間であることがわかっている場合、一覧にして見比べたほうが見やすくなります。

f:id:waenavi:20180812225907j:plain

f:id:waenavi:20180812225911j:plain


販売員の数が変わると1人当たりの収益が変わります。複合参照にします。収益はE列固定、販売員数は8行目固定となります。

f:id:waenavi:20180812225914j:plain

 

オートフィルをします。

f:id:waenavi:20180812225918j:plain

 

この表で、縦に販売本数、横に販売員の数を並べています。
これらもパラメータの1つです。

f:id:waenavi:20180812225923j:plain

 

変化するデータをパラメータとするときに、1か所に入力するか、縦または横に1列に並べます。

パラメータを1か所にすると、これを入力しなおすことによって無限に計算をやり直すことができますが、見比べることができません。

f:id:waenavi:20180812225926j:plain

 

パラメータを、縦または横に1列に並べると比較することができますが、たくさん並べて計算すると表が大きくなりすぎるというデメリットがあります。

f:id:waenavi:20180812225930j:plain

 

 


解説は以上です。

*補足意見*
この説明におけるパラメータは、数学の媒介変数とは関係ありません。ローカル変数や仮引数の考え方に近いです。また、循環参照になるとか、書式を代入できないとか、プログラミングの変数とは異なるところがありますが、表計算の範疇を超えるのでその説明は割愛させていただきます。

 



 

付録1.サンプルファイルはこちら

わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお使いください。 

http://waenavi.jp/htm/fx5.html

waenavi.jp

 

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

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

 

 


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

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