【連載】
作業を効率化させる Excelテクニック集（7）
第7回 Excelを活用してデータ分析
掲載日：2022/05/31
データ集計の際、Excelを活用している企業は多いだろう。集計したデータをさらにExcel上で活用するときに覚えておきたいのが、分析機能だ。Excelの分析機能では利益・収益などのシミュレーション、目標値の割り出しなどにも活用できる。ここでは、ピボットテーブルとWhat-If分析を紹介しよう。
ピボットテーブルとは？
関数が分からないレベルでも簡単に「クロス集計」ができるのが、ピボットテーブルだ。クロス集計とは、2つの項目の縦軸と横軸に異なる項目を設定し、その交わる値にデータを入力する集計法をいう。
クロス集計の例（縦軸は月、横軸は販売数を設定 ）
| 販売数 | ||||
|---|---|---|---|---|
| 製品A | 製品B | 製品C | 合計 | |
| 4月 | 300 | 300 | 300 | 900 |
| 5月 | 200 | 500 | 200 | 900 |
| 6月 | 500 | 100 | 300 | 900 |
数式でこの集計を行う場合はSUMIF関数を用いることになるが、条件の組み合わせを考えるのは面倒なうえに、間違いが起こる可能性もある。しかし、ピボットテーブルならマウス操作だけで集計できて、数式の入力誤りのような人的ミスを防げるのだ。
ピボットテーブルの使い方
1. 元データの表を選択し、「挿入」タブ＞「テーブル」＞「ピボットテーブル」をクリックし、「テーブルまたは範囲から」を選択する。
2. 範囲が正しいことを確認し、「OK」ボタンをクリックする。
3. 「ピボットテーブルのフィールド」に表示されている項目を、下の段の「列」「行」「値」にそれぞれドラッグする。この例では、1日ごとにどの製品がいくつ売れて、製品ごとの売上はいくらになるのかを集計した。
What-If分析の使い方
ビジネスでは、さまざまな条件の下に予測を立てたり、目標達成のために必要な数字を確認したりする場面が多い。このようなシミュレーションなどを行えるのが、What-If分析ツールだ。
ここでは、What-If分析の「データ テーブル」と「ゴール シーク」「シナリオ」について、簡単な例を基に使い方を確認してみよう。
データ テーブル
データ テーブルは、異なる条件に応じた計算結果が表示できるものだ。例えば、借入金額を何回で返済したときの1回当たりの返済額はいくらになるのか。また、借入金額を増やした場合はどうなるのか。これを一括して計算し、表示する。
例として、1千万円、1.2千万円、1.5千万円を12カ月、24カ月、36カ月返済でシミュレーションしてみよう。
1. まず基準となる表を作成する。ここでは1千万円を24カ月での完済としている（一定利率の場合のローン返済額を求める「PMT」関数を用いた）。この場合、必要なデータは金利、返済期間（回数）、借入金額となる。
=PMT(利率、期間、現在価値、将来価値、支払期日)
なので、利率はB3、期間はB4、現在価値（＝借入金額）、将来価値（完済を目指すため0）、支払期日（各期末の場合は0または省略、期首の場合は1）を入力する。
2. データ テーブル用の表を用意する。下の例では6～9行目。
3. データ テーブルの見出し行の左端のセルに、基準の表の数式が入力されているセルを参照させる。ここでは、B6セルに「=B2」と入力した。この例では、データ テーブルの表のA列はデータ テーブルに含まれないため、B6セルが「見出し行の左端のセル」となる。
4. データ テーブルの範囲を選択する
5. 「データ」タブ＞「予測」＞「What-If分析」＞「データ テーブル」をクリックする。
6. 「データ テーブル」ダイアログに、行と列の代入セルを入力する。行は借入金額なので、元の表の借入金額のセル（B2）、列は返済期間なので元の表の返済期間（B4）を入力する。このとき絶対参照にすること。
入力したら、「OK」ボタンをクリックする。
そうすると、データ テーブルが作成される。
ゴール シーク
目標の利益額が決まっていて、それを達成するにはどれだけの売上が必要なのか。飲食店なら回転率をどれくらい上げなければならないのか。このような計算をするのが「ゴール シーク」だ。
通常の計算では、売上×利益率＝X（利益額）となるが、ゴール シークでは、利益率＝Y（売上）×利益率と考える。ここの例では、経費は固定と仮定し、目標の利益を得るには売上をどれだけ増やせばいいのかを確認する。
1. 基準となる表を作る。B6セルには数式が入力されている。
2. 「データ」タブ＞「予測」＞「What-If分析」＞「ゴール シーク」をクリックする。
3. 「数式入力セル」に、基準の表で数式が入ったセル（ここでは利益目標）、「変化させるセル」にはゴール シークで解答を求めたいセル（ここでは売上目標）を入力し、「OK」ボタンをクリックする。
そうすると、解答が表示される。
シナリオ
複数の条件を組み合わせたいときは、「What-If分析」＞「シナリオの登録と管理」から設定する。
1. 「データ」タブ＞「予測」＞「What-If分析」＞「シナリオの登録と管理」をクリックする。
2. 「追加」ボタンをクリックする。
3. 「シナリオ名」「変化させるセル」を入力し、「OK」ボタンをクリックする。
4. 指定したセルの値を入力し、「OK」ボタンをクリックする。
5. シナリオを選択し、「表示」ボタンをクリックする。
シナリオを複数登録しておき、それぞれのシナリオを表示させれば、複数条件におけるそれぞれの解答を比較可能だ。
このように、初心者でも扱いやすいピボットテーブル、そしてデータ テーブル・ゴール シーク・シナリオ機能があるWhat-If分析を活用すると、さまざまなシミュレーションを手軽に実行できる。ぜひ、日頃の業務にお役立ていただきたい。