(本記事は、土屋和人氏の著書『Excel実践ビジネス入門講座【完全版】』＝SBクリエイティブ出版、2019年6月9日刊=の中から一部を抜粋・編集しています)
【本書からの関連記事】
（1）エクセル操作を一瞬で完了するショートカット 時短に効率的なセルの移動方法は？
（2）エクセル集計を超高速化する4つのテクニック 予測シート、ゴールシークなど（本記事）
Excelに搭載されているデータ集計・分析機能
Excelで「ビッグデータ」の分析は可能か
「ビッグデータ」という言葉が使われはじめてから数年が経ちました。PCの性能は飛躍的に向上し、それに伴ってExcelの機能もますますパワーアップしているため、現在では、ある程度大きなデータでも、Excelで処理したり、分析したりすることが可能になっています（とはいえ、大企業が扱うような膨大な顧客データなどをExcelで処理するのは、やはり厳しいことも事実です）。
データ分析の際のポイントは「分析の対象として、同じ基準で比較できるデータを2種類以上用意すること」です。1つのデータだけでは、例えグラフ化しても、そのデータの持つ意味を読み取るのは困難です。2種類以上のデータを並べて比較したり、差分を取ったり、相関関係を求めたりすることで、それらのデータの意味がより明確になり、今後の意思決定の指針となります。
本格的なデータ分析には、やはり統計などに関する専門的な知識がある程度必要になります。しかし、Excelに搭載されているさまざまな機能を活用することで、基本的なデータ分析は可能です。ぜひ、みなさんの業務に活かしてください。
これらの機能を利用すれば、専門知識を学ぶことなく、大量データの分析やシミュレーション、将来予測などを行うことができます。ぜひ一度、本書の解説を読み進めて、実際にこれらの機能を試してみてください。かなり強力な分析ツールであることを体感していただけると思います。
過去のデータから将来を予測する
予測シートを利用する
蓄積した実績データに基づいて、将来の値を予測したい場合は、「予測シート」を利用します。この機能を利用すると、精度の高い予測を手軽に実行できます。表示される「将来の予測値を表す折れ線グラフ」は3本に分かれていますが、上下2本に挟まれた範囲が、信頼度95％で予測される範囲を表しています。
①実績データが記録された表の中の1つのセルを選択する。
②［データ］タブ→［予測シート］をクリックする。
HINT
この機能の処理対象になるのは、日付などの時系列とその日の売上金額のように、対応する2種類のデータが並んだ表です。
③［予測ワークシートの作成］ダイアログで［作成］ボタンをクリックすると、新規ワークシートに、将来の予測値とそれに基づくグラフが作成される。
データのセットを瞬時に切り替える
シナリオにデータを登録する
セル範囲に入力するデータのセットを複数パターン用意して、必要に応じて切り替えたいときは、「シナリオ」の機能を活用すると便利です。
ここでは、持ち帰り弁当の価格設定に関するシミュレーションを行うために、シナリオ機能を使って、2種類の原価設定を瞬時に切り替える方法を紹介します。
①切り替えたりデータが入力されたセル範囲を選択する（ここではセル範囲C4:C7を選択）。
②［データ］タブ→［What-If分析］→［シナリオ］をクリックする。
③［追加］ボタンをクリックする。
④［ シナリオの追加］ダイアログが表示されるので、［シナリオ名］に「原価設定A」と入力する。
⑤［OK］ボタンをクリックする。
⑥［シナリオの値］ダイアログには選択範囲のデータが入力されていることを確認する。
⑦［追加］ボタンをクリックして、このデータをシナリオ「原価設定A」として登録する。
Memo
上記の手順⑦で［追加］ボタンを押した時点で1つめのシナリオが登録されます。画面遷移としては［シナリオの追加］ダイアログに戻るので、追加処理が成功したのか否か、わかりづらい面もありますが、そのまま手順を進めて大丈夫です。
⑧［シナリオの追加］ダイアログに戻るので、［シナリオ名］に「原価設定B」と入力する。
⑨［OK］ボタンをクリックする。
目標値を逆算する
ソルバーとは
「ゴールシーク」（p.255）は、目標値を逆算できる便利な機能ですが、その計算要因には1つのセルしか指定できない、比較的シンプルな機能です。目標値以外の条件も設定できません。
より詳細にいくつかの条件を設定して、売上高や利益などをシミュレーションしたい場合は「ソルバー」の機能を利用します。ソルバーを利用すると、複数の条件を元にして目標値から必要な数値を逆算することができます。
ソルバーを有効にする
ソルバーはExcelの標準機能ではありません。そのため、ソルバーを利用するには事前に次の手順を実行して、この機能を有効にする必要があります。
①ファイル］タブ→［オプション］をクリックする。
②⑤セルB9の原価率が自動的に変化し、それぞれの場合のセルB10の計算結果が検証される。
③⑥セルB10が「50000」になったら「解答が見つかりました。」と表示される。
④⑦［OK］ボタンをクリックする。
［OK］ボタンをクリックした場合、セルB9の値が変更された状態で、［ゴールシーク］ダイアログが閉じます。一方、解答が見つかった場合でも、その値に変更したくない場合は、［キャンセル］ボタンをクリックします。
なお、セルB9の原価率は「54％」と丸めて表示されていますが、実際にはもっと細かい数値です。
また、計算の内容によっては目標値になる値が見つからないケースもあります。その場合は、「解答が見つかりませんでした。」と表示されるので⑧、やはり［キャンセル］ボタンをクリックしてこのダイアログボックスを閉じます。
計算式を変えて結果を試算する
データテーブルを利用する
シミュレーションの内容によっては、「計算の要素となる値」ではなく、「数式の計算方法」をいろいろと変更して、その結果を確認したい場合もあるでしょう。
例えば、各商品の原価から一定の計算方法で販売価格を求めているとき、原価を調整するのではなく、計算方法を変更することで、売上や原価構造がどのように変化するのかをシミュレーションしたいようなケースです。このような場合に便利なのが「データテーブル」の機能です。データテーブルを利用すれば、1つのセルの変更で、その数式の複数の計算結果を同時に求めることができます。
ここでは、下図の「持ち帰り弁当価格設定シミュレーション」の表を使って、データテーブルの使い方を解説します。セルC6 には、セルB6 の原価から販売価格を求める数式「=ROUND(B6/C3,0)」が入力されています。この数式の「B6」の部分を、セル範囲B7:B9の各セルに置き換えた計算の結果を表示します。
①セル範囲B6:C9を選択する。
=ROUND(B6/C3,0)
Memo
ROUND関数は、引数に指定された数値を四捨五入して、指定された桁数にする関数です。
書式 ROUND関数
ROUND(数値, 桁数)
「=ROUND(B6/C3,0)」の数式では、セルB6（原価）をセルC3（原価率）で除算することで販売価格を求めたうえで、その計算結果を整数（小数点桁数0）に四捨五入しています。
②［データ］タブ→［What-If分析］→［データテーブル］をクリックする。
③［列の代入セル］にセルB6を設定して、［OK ］ボタンをクリックする。
④セル範囲C7:C9にデータテーブルが設定され、セルC6の数式の「B6」を行ごとに「B7」～「B9」に置き換えた計算結果が表示される。
⑤セルC6の数式を変更すると、セル範囲C7:C9の各セルの値もすべて変更される。
=ROUND(B6/C3,-1)
使えるプロ技！ データテーブル設定後のセルの値
データテーブルを設定したセル範囲C7:C9 の内容を確認すると、「{=TABLE(,B6)}」という一種の数式が入力されています（実際に確認してみてください）。これは、いわゆる「配列数式」と同様、セル範囲全体に1 つの数式が入力されているような状態です。そのため、全体をまとめて削除することは可能ですが、個別のセルの内容を変更することはできません。
土屋和人
フリーランスのライター・編集者。ExcelやVBA関連の著書多数。「日経パソコン」「日経PC21」（日経BP社）などでExcel関連の記事を多数執筆。著書に『Excelでできる！Web データの自動収集&分析実践入門』『今すぐ使えるかんたんEx Excelマクロ＆VBAプロ技セレクション』『最速攻略Wordマクロ／VBA徹底入門』（技術評論社）、『Excel VBAパーフェクトマスター』（秀和システム）ほかがある。
【関連記事 MONEY TIMES】
仕事のストレス解消方法ランキング1位は？2位は美食、3位は旅行……
就職ランキングで人気の「三菱UFJ銀行」の平均年収はいくら？
職場で他人を一番イラつかせる行動トップ3
35歳以上で転職した人の52％が年収アップ！うち4割は100万円以上も増加
【初心者向け】ネット証券おすすめランキング（PR）
