一つの計算結果を変化する値ごとに答えを出すデータテーブル

Excelにはデータテーブル機能というものがあります。
テーブル機能ではありません。データテーブル機能です。
この機能を使うととんでもなく複雑な計算表が一瞬でできてしまします。と言ってもこの機能のスゴイ所は伝わりにくいので、使いこなしは若干難しいですけど、できるだけ簡単にどう使うか説明します。Excelの機能の中でも結構スゴ技で知ってれば活用ケースも結構出てきます。

データテーブルを設定する

今回は、1から10までの数値に対して、ある計算方法を試して、その結果をそれぞれ求めるということをします。
A列に１から１０までの数値が入っています。B列の先頭に参照するセルがあります。この値をどんどんB列に表示していきます。

セルD2には仮に1と入力されています。この値を元にし、1足したものがセルE2に計算され、このセルE2がセルB1で参照されています。セルB1には計算結果のセルが指定されているということが重要です。なんか普通のことですけど重要です。
この状態で、変化させる値の範囲、答えを出したい範囲、答えを出したい範囲の上の計算式のセル、ここまでを範囲選択します。
今回は、A1からB11の範囲です。

データタブのWhat-If分析の中のデータテーブルをクリックします。

今回は変化するのがA列の１列の中で変化するので、列の代入セルに、値をどんどん変えてほしいセル、つまりセルD2をセットします。

OKします。

全部の範囲にA列＋１の計算結果が求まりました。
これでA列の数値をセルD2に入力していって、セルB1が参照しているセルE2の計算結果をB列に求めるという仕組みが出来上がりました。
仕組み、計算構造ができたというのがポイントです。
いわば計算式のピタゴラスイッチができたと言えるでしょう。
でも、そこまで大げさなことなのか、なんか普通のことというか、計算式を入れてオートフィルした方がぜんぜん早いですよね。

計算プロセスの入れ替え

では、ちょっと実験。
セルE2にREPT関数で指定された数字の分だけ■を表示してみます。
セルE2に「=REPT(“■”,D2)」と入力します。
すると、B列に■がA列の数字の分、表示されるようになりました。

計算の法則が変わったことで、B列の結果も変わったのです。A列の数字を入れ替えていってその対応するものをB列に求めるというピタゴラスイッチの仕組みはわかっていないのです。
ではセルA11を15に変更してみましょう。
これでも、セルB11の■の数が変わりました。

ピタゴラスイッチの仕組みは変わらずに代入する値を変えたので変わったのです。

実用的なデータテーブルの使い方

例えば、次のようにA列の1～10のデータを書き換えます。

もちろんこの時点ではエラーになります。

■を123abc個繰り返すなんていう関数になっていますから。

では、今度は、D5からD7の範囲に１，２，３と入力します。

そして、セルE4にセルD2を参照する=D2という計算式を入力します。

セルE5に「=SUBSTITUTE(E4,D5,””)」という関数を入れます。

これで上のセルから左のセルの文字を省くことができるようになりました。

この計算式をセルE7までにコピーします。

そうすると、D2の文字の中から、１と２と３が抜けたものがセルE7に求められます。
次は、セルB1の参照先をセルE7とします。すると。

B列にすべて１と２と３が抜けた文字が求まりました。
A列のデータを、代入セルであるセルD2に次々と代入し、その結果が出てくるセルE2の値を表示する構造をデータテーブル機能で作成し、あとはその計算プロセス通りに計算するという構造ができています。
今回は文字を１つずつ削除する計算を3文字分に対し行った計算をしました。
もしも削除する文字数が多い時は、もっと多くの計算プロセスが必要ですが、それを1行1行のデータに対して計算するのはとても面倒です。
そのような時にデータテーブルで計算構造が設定できることを覚えておけば、一つだけ計算式を作るだけで範囲に値を求めてくれます。
今回はわかりやすい実例として文字列の置換で紹介しましたが、この機能の一番面白いのは一覧表範囲の該当したデータの数を数えたり合計を求めるデータベース関数まど、複雑な書式、計算をする関数との相性がとてもいいです。