このブログの記事は、使い方はもちろん、どのように使えば考えやすくてどんな風に役立つかをお伝えしているのですが、今回は、そんななかでも、よくExcelの作業でするであろう、複雑な計算式のまとめ方をできるだけ簡単にできる方法を紹介します。
次の表を見てください。
セルC2に入っている計算式は、次の通り複雑です。
=VLOOKUP(A2,$H$2:$J$4,MATCH(VLOOKUP(B2,$E$2:$F$4,2,FALSE),$I$1:$J$1,0)+1,FALSE)
商品ごとの金額を求めるのであれば完全一致のVLOOKUP関数なのですが、取引先によって通常価格か特別価格かを選択しなければなりません。
そのために、取引先をE列からの表で調べ、その価格帯がどっちかを調べなければなりません。
そのうえで商品の価格一覧表で調べるということになります。
価格帯は通常価格と特別価格の2通りですが、もしかしたらもっと細かく５種類以上分かれているかもしれません。
そんな中でこの計算式を組み立てる方法を考えていきましょう。
まず今回は何を求めるのかを考える
今回は、日本語にすると、「商品ごとの価格を調べる。ただし取引先ごとに価格帯が違うので価格帯一覧表から調べ、その結果でどの価格帯を使うか考える」になります。
これを全部網羅する計算式をはじめから考えるのは苦労します。
そこで、「ただし」の部分は向いて、「何を」「どうするか」の主語と動詞だけの部分だけに注目します。
今回は、「商品の価格を調べる」なので、次の計算式にすればいいことになります。
=VLOOKUP(A2,$H$2:$J$4,2,FALSE)
これで価格帯は通常価格固定になりますが、価格を求めることはできますね。
これをセルC2にとりあえず入力します。
どこが足りないかを考える
次はどこが足りないかを考えていきます。
通常価格固定になっているのがまずいので、これをなんとかします。
ということは列番号の2を価格帯によって変えていきます。
価格帯の種類が2つなので、次のような計算式が考えられます。
=IF(価格帯=”通常価格”,2,3)
しかし、価格帯が多くなればIF関数では管理しにくくなるので、一致するセルの位置番号を求めるMATCH関数を使います。
また、セルC2のこの時点では、価格帯は不明なので、仮の値として価格帯は「通常価格」にしておきます。
=MATCH(“通常価格”,$I$1:$J$1,0)
これでI1からJ1の中で何番目に通常価格があるかが求まります。
ここで注意したいのは、この求める値は1から始まるということ、VLOOKUP関数でほしい列番号は2列目からです。
そこで、この計算式に1を足します。
=MATCH(“通常価格”,$I$1:$J$1,0)+1
これをセルD1に入力し、動作が合っているか確認します。
それでよければ、数式バー上でセルD1の計算式の=より後ろを選択して、コピーします。
ESCキーでセルの入力状態をキャンセルします。
そして、セルC2の計算式の2となっているところを範囲選択し、貼り付けします。
すると2だったところが、MATCH関数に置き換わります。
=VLOOKUP(A2,$H$2:$J$4,MATCH(“通常価格”,$I$1:$J$1,0)+1,FALSE)
今度はMATCH関数の通常価格になっている部分をVLOOKUP関数でE列からの表の取引先を調べて求めます。
=VLOOKUP(B2,$E$2:$F$4,2,FALSE)
この計算式をセルD2に入力し、動作チェックをします。
これでよければ、さっきのMATCH関数をコピーしたように、数式バー上で、セルD2の計算式をセルC2の”通常価格”と置き換えます。
=VLOOKUP(A2,$H$2:$J$4,MATCH(VLOOKUP(B2,$E$2:$F$4,2,FALSE),$I$1:$J$1,0)+1,FALSE)
となり、完成します。
あとは下にコピーして問題がなければこれでOKです。
これ以外の方法
1つのセルに1つの計算式として入力していれば、余計なセルを使わずに綺麗なシートにできますが、後で見直した時にどんな計算式なのかがわかりにくいです。
それで、計算のプロセスごとに次のような構造にしてもいいのかもしれません。
セルに分けた方がいいかというのは、どちらともいえないと私は思っています。
あとでもう使わない、数式を変更しないというのであれば解読しにくくてもいいのかもしれないですし、レイアウトの関係でどうしても1セルにまとめたいというケースもあるでしょう。
対して、余計な列を作っても非表示列などで対応すれば、本質的なデメリットはあまり考えにくいのでそのようなケースを選ぶことがあってもいいと思います。
まとめ
今回は複雑な計算式をできるだけ簡単に考える方法を紹介してきました。
どうしてもExcelは関数を一つ一つ覚えるよりも、このような複数の計算を用いてまとめなければならないケースもありますので、今回のテクニックが役立てばと思います。
特に初心者のうちは、計算式が何をどうするかという「主語」「動詞」の基本のみに注目することがとても大事です。
「主語」「動詞」というと文系な感じですが、まさにExcel計算式は文系の解読力と理数系の計算式の組み立て方の両方の知識がバランスよく揃えばできる話なので、「文系だからExcel苦手」みたいな話は間違いです。
コメント