「Excel データテーブル(What-If 分析ツール)」の最後で、データテーブルを使うとこんなこともできるかもね?という例として、今年の売上をベースに、売上や利益が指定した率で伸びていったら x 年後にどのくらいの営業利益が見込めるのか、というのをまとめた表を作ってみました。
こんなやつです↓。
これ、表にしなくても、売上達成率 (セル B2) と営業利益率 (セル B3) を指定して、セル B5 に知りたいのは何年後なのかを指定すれば、セル B4 に営業利益が表示されるように数式を作成してあるのですが、逆に言うと、売上達成率と営業利益率を手作業で編集しないといけないので、一覧で表して交差する位置で値を確認するには表にします。
セル B4 の数式は、「=今年の売上* (1+売上達成率)^年数*営業利益率」という数式です。
今回はこの表を 3 つの方法で作成してみました。どれがいいとか悪いとかじゃなくて、どれか 1 つが分かればそのやり方との比較でほかの 2 つを理解しやすいかな、と思いまして。
複合参照の場合
売上達成率を表の上のほうに、営業利益率を表の左のほうに配置し、これらを「=今年の売上* (1+売上達成率)^年数*営業利益率」の数式の中で参照するようにします。
- 1 つ目に作成するセル B10 の数式は、「=$B$1*(1+B$9)^$B$5*$A10」です。この数式は、あとで表のほかのセルにコピーしなければならないので、今年の売上と年数のセルは絶対参照で固定し、売上達成率は行を固定したまま 1 列、営業利益率は列を固定したまま 1 行ずつ参照をずらすために複合参照にしています。
-
作成した数式をすべてのセルにコピーすると、右下のセルの数式はこんな感じ。
数式の入っているセルをコピーすることを想定して、適切なセル参照になるように工夫した、というところがポイントです。
データ テーブルの場合
データ テーブルについては、「Excel データテーブル(What-If 分析ツール)」で書いているので詳細はそちらでご覧いただくとして、ここでは行方向と列方向を使った複入力テーブルを作ります。
- データ テーブルの場合は、下図の赤枠の部分に基準となる数式を作成して配置する必要があります。数式自体は、セル B4 に入力してある数式と同じです。
- 表の上と左に変化させる値の一覧を配置し、数式を準備したセル範囲 (数式のセルから表の右下) を選択して、リボンの [データ] タブの [予測] グループの [What-IF 分析] をクリックし、[データ テーブル] をクリックします。
-
[データ テーブル] ダイアログ ボックスの [行の代入セル] と [列の代入セル] に数式で参照しているセルを指定します。
今回の場合は、行方向 (横) に並べた値が売上達成率です。売上達成率はセル B2 に入るべき値なので [行の代入セル] に指定します。列方向 (縦) に並べた値が営業利益率です。営業利益率はセル B3 に入るべき値なので [列の代入セル] に指定して [OK] をクリックします。 -
データ テーブルの機能によって数式が作成され、結果が表示されます。
表のどの部分の値を代入して処理をさせるのかを設定すれば、絶対参照だの複合参照だのを考える必要がなくすべてのセルに結果を表示できる、というところがポイントです。
スピルの場合
スピルについては「Office 365 ProPlus の Excel で使えるスピルという仕組み 1」で確認していただくとして、ここまで複合参照やデータ テーブルで作ってきた表を、スピルを活用して作ったらどうなるかを最後に見てみましょう。
- セル B10 に基準となる動的配列数式を作成します。「=B1*(1+B9:G9)^B5*A10:A15」のように、変化させていくべき値の一覧のセル範囲を指定します。
-
数式を確定するとゴーストが作成され、スピル領域に結果が表示されます。
絶対参照や複合参照にして数式をコピーする、値を代入するセルや基準となる数式を表の左上に用意してデータテーブルを作るなんてことをしなくても、スピルによって表全体に必要な結果が表示されていることがポイントです。
基本は複合参照を使った数式でしょうか。データ テーブルは使い方を知っているかどうかと適切な基準となる数式を準備できるかどうかですね。スピルは、“スピルが使える” バージョンでスピルが使える前提の数式を作ることができるかどうかです。
見てわかる通り、どれでやってももちろん結果は同じ。手法が違うのです。あなたならどれでこの表を作りますか?
この表を作った後、どんな人がどんなふうにメンテナンスするのか / 利用するのかによって選択できるとよいですね。
石田 かのこ