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

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

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

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

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

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

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

OKします。

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

計算プロセスの入れ替え

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

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


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

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

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

123abc

abc123

a123bc

a123123

b123b123

a123cds1

a121121b

a12a2a3b

123a123b

123aaa1

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

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

では、今度は、D5からD7の範囲に1,2,3と入力します。

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

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

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

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

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

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

サトウヨシヒロ

Excelを中心としたMicrosoft Officeの魔法使い。
仙台市在住のフリーランスのパソコンインストラクターです。
1969年1月生まれ。
Excelを黎明期から30年近く使っています。Wordも使えます。
出版社様と一緒にExcelの情報発信もしています。
最近は羊に夢中です。
2019年はRPAと業務効率化を勉強しています。

Twitterでも業務で使える便利なOffice技をめっちゃつぶやくツイッタラーです。@yosatonet

コメントを残す

メールアドレスが公開されることはありません。