準備問題(1)データファイル（task03.xlsx）を右ボタンクリックしてダウンロードして保存し、ブロックを解除しておきなさい。
(2)task03.xlsxの万引き対策シートに含まれている「正しい重量」列に、入店重量＋購入重量を計算する計算式を入れなさい。
準備問題本部から「万引き犯を見つけろ、だけど金はない」というありがちな指示がきたので、とりあえず倉庫に入っていたはかりを持ち出してきた。このはかりを店の出入り口に仕掛ければ、「店に入ってきた時の重量（入店重量）」「店から出るときの重量（退店重量）」が計測できる。さらに、レジで購入した商品の重さは商品データベースに入っているので「購入した商品の合計重量（購入重量）」はレジを通した段階で計算できる。
正しくレジを通して買い物をした人なら、
入店重量＋購入重量＝退店重量
| 入店重量 | 購入重量（レジで計測） | ＝ | 退店重量 |
 |
 |
 |
|
| 60kg | 1.5kg | 61.5kg |
になっているはず。万引きしていれば
入店重量＋購入重量＜退店重量
になっていると考えていいだろう。この式の左辺は準備問題で「正しい重量」として計算済みなので、とりあえずこの考え方でやってみよう。
| 入店重量 | 万引き重量 | 退店重量 | |
 |
万引きなので計測不能 | ＜ |  |
| 60kg | □kg | 60kg＋□kg |
例題1. 退店重量＞正しい重量 の人は万引き犯として処理してみる
1つの条件によって真偽を判断するには？ → Excel編 Q21
このような条件判断をExcelで行う場合は、IF関数を用いる
Step 1. セルJ2 にIF関数を用いた計算式を入力する
=IF(G2>I2,"万引き","OK")
IF関数は3つの引数をとる関数で、IF(論理式, 真の場合, 偽の場合)という構造になっている。
最初の引数、「論理式」は、上の例では G2>I2 になる。これは、G2の値がI2の値より大きければ真（TRUE）、等しいか小さければ偽（FALSE）となる式である。IF関数では、この引数の値が真ならば2番目の引数を、偽ならば3番目の引数を評価した値をIF関数の値として返す。
ここの例では、G2>I2、つまり退店重量が正しい重量より大きい（万引き疑い）なら2番目の引数が評価されるが、2番目の引数は文字列"万引き"なので、評価結果としては文字列の"万引き"がIF関数の返す値になる。
Step 2 残りのセルにもセルJ2 に入力した計算式をコピーする
できあがりはこんな感じ。こんなに万引きがいて大丈夫かこの店。
練習問題1 ブック task03.xlsx の軽減税率シートを使って以下の処理を行いなさい
※ヒント Excelの数値比較では、「以上」は >= 「未満」は < と書く
できあがりイメージ
練習問題2 練習問題1では計算式の中に1000円という数値を埋め込んでいたが、制度が変更される度に計算式を書き替えるのは面倒。セルB1 に入っている数値を絶対参照する計算式を使って消費税額を計算しなさい。
計算式が作れたら、セルB1 の内容を300に書き替えて、消費税額が再計算されるかどうか確かめること。
練習問題3 ブックtask03.xlsxのAKB総選挙 シートに入っているデータから第6回総選挙の平均得票数を求め、平均得票数以上取った人の横に「平均以上」と表示する計算式を入れなさい
例題2 退店重量と正しい重量の差を計算して、分布をグラフ化してチェックする
データは task03.xlsx の 万引き対策 シートを使う。
Step 1 退店重量-正しい重量 を計算する
Step 2 万引き量の分布をヒストグラムで確認する
ヒストグラムを作成するには → Excel編 Q44
(1）データタブ-分析グループの
データ分析ボタンをクリックすると、データ分析ダイアログボックスが表示される。
Tips 分析グループが表示されないときには？
分析ツールはExcelの本体機能ではなく、アドインと呼ばれる追加機能で実装されているため、デフォルトではロードされていない。分析グループがデータタブに存在しない場合は、ファイル-オプション-[アドイン]の一番下のほうにある設定ボタンから設定する。
（2）データ分析ダイアログボックスから[ヒストグラム]を選択肢、OKボタンをクリックする
（3）ヒストグラムダイアログボックスの中で[入力範囲]に、ヒストグラムを作成したい対象データを入れる。この場合は万引き量の列からデータを選択する。
（4）OKをクリックすると、新規のワークシートに度数分布表とグラフが表示される
こんな感じ
Step 3 商品の重さの最小値と比較する
求めてみると、60gが最小値らしい。Step 2の結果とも合わせて、-20g〜＋30gの範囲ははかりの誤差ということで処理することにしよう
練習問題4 ブック task03.xlsx の 商品重量 シートに入っている商品の重さデータから、商品の重さ分布を表すヒストグラムを作成しなさい
練習問題5 ブック task03.xlsx の AKB総選挙 シートに入っている第5回の結果から、得票数の分布を表すヒストグラムを作成しなさい
IF関数を使って2つ以上の条件を使うには→ Excel編 Q22
例題3 IF関数を使って、軽い、正常、万引き の3つの区間に正しい重量との差（万引き量）を分割してみる
Step 1 万引き量が-20g以下かどうかで区分する
IF関数では、条件式が真か偽かの2通りにしか分類できないので、まず「なぜか軽くなっている」と「正常」および「万引き疑い」の2つに区分するIF関数を使った計算式を作ってみる。下図の網掛けした部分と、それ以外を区別すると思えばよい。
セルL1 には「判別結果」というタイトルをとりあえず入れてから作業を始めよう。実際の計算式はセルL2 に入力する。
万引き量のデータはK列に入っているので、計算式は
=IF(K2<=-20,"軽い","それ以外”）
となる。これで、「軽い」部分は判別できるようになったので、一応全体にコピーしておくとこんな感じ。大部分が「それ以外」になってしまっているが、これは当然そうなるので問題はない。
Step 2 Step 1で判別した「それ以外」の部分を、万引き量が30gを超えているかどうかで「正常」と「万引き疑い」に区分する
またセルL2 に戻って作業を行う。Step 1が終わった段階で、セルL2 には
=IF(K2<=-20,"軽い","それ以外")
という計算式が入っているはず。ここでは、"それ以外"の部分を、万引き量が30g以下かどうかで2つに分けたい。
30g以下かどうかを判断するIF関数はこんな感じになる。
IF(K2<=30,"正常","万引き疑い")
これを、セルL2 に入っている計算式の"それ以外"の部分に入れてやれば、希望する計算式ができあがるはず。こんな感じ。
=IF(K2<=-20,"軽い",IF(K2<=30,"正常","万引き疑い))
何も考えずに、"それ以外"の部分を書き替えただけ。これをセルL2 の内容を書き換えて入れよう。
Tips セルのデータを編集したい
Step 3 セルL2 の計算式をコピーする
できあがりはこんな感じ。大部分は正常な取引で、中に「軽い」が結構いるのはなぜだろう。
練習問題5 例題3で「軽い」になっている人はどのような人だと思うか。もっともらしい理由を考えなさい
※誤差とかデータの取り間違えではないと考えてよい。
（ヒント）店に滞在している時間の分布も見てみよう。
練習問題6 消費税がまた複雑になった。task03.xlsxの軽減税率2シートを使って以下の計算をしなさい
練習問題7（*） 基準価格や適用税率が変更されたときに備えて、軽減税率2シートの上の方にある基準価格や税率の表を使って計算するようにしなさい
©2017, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.