※注：記事内でVLOOKUP関数はワイルドカードが使えませんとしており、その考えのまま記事を作成しましたので、VLOOKUP関数の代用にCOUNTIF関数とデータテーブル機能を使っていますが、勘違いをしておりまして、VLOOKUP関数はワイルドカード可能でした。
この記事では、複数セルを使わなければ求まらないものもデータテーブル機能を使えばシンプルに求まるということをお伝えできればと思います。
VLOOKUP関数は、「検索値を、範囲の一番左の列から探し、見つかった行のその範囲の中の何列目を出すか、検索の方法が一致か近似値か」を求める関数です。
しかし、このような表のB列に入る計算式の例では、検索値を、範囲の一番左の列の列の一部から探し、という検索をします。
この「一部」というのはVLOOKUP関数ではできません。
でも、やらなければいけない場合もあるでしょう。
そんな時に使えるCOUNTIF関数とデータテーブル機能を使った計算方法を紹介します。
COUNTIF関数はMATCH関数の代わりに使える
COUNTIF関数は、その範囲に検索したいセルがいくつかるかを求める関数ですが、この関数を使えば、検索したい値のセルの位置を求めるMATCH関数が実現できます。また、VLOOKUP関数同様、MACTH関数も検索条件の一部を検索条件に設定できませんが、COUNTIF関数を使う方法でなら、ワイルドカード文字を使うことで一部を検索対象にできます。
セルB1に金額を求めるために、D、E列の計算式に中間計算式を仕込みます。
セルF2には次の計算式を入力します。
=IF(COUNTIF(D2,$A$2&”*”)=1,ROW(),””)
そして、その計算式を下にコピーします。
すると、A01なんとかが見つかった行番号が記録されます。
何個か見つかった場合は、はじめに見つかったセルを対象としたいので、今、コピーした範囲の最小値をインデックス番号とします。
ここまでの答えとして、セルB2に次の単価を引っ張ってくる計算式を入力します。
=INDEX($E$1:$E$4,MIN(F2:F4))
これでセルB2に単価が求まりました。
データテーブルで計算式を作る
セルF2の計算式はセルA2の値に対して、単価を求めていましたが、下にコピーしても、計算はF列でセルA2として行っているので、セルA2の値しか求めません。
そこで、いろいろな値を入れられるようにセルB2の計算式を変更します。
=IF(COUNTIF(D2,$F$1&”*”)=1,ROW(),””)
これでセルF1に求めたいものの値を入れれば結果が出せます。
ここに「A01」「A02」「A03」と入力していって、出てきた答えをB列にどんどん貼り付ければ表が完成します。
しかし、この方法、とても効率的とは言えません。
そのような時は、データテーブル機能を使います。
今、計算式はセルB2に入っています。
そして動かす値はA列に入っています。
計算式を最も上の行になるように、また、動かす値をすべて選択します。
A2からB10の範囲を選択し、データタブの中のWhat-IF分析の中のデータテーブルをクリックします。
今回、値を動かすのはA「列」でそれがどんどん入れ替えてほしいのはセルF1です。
ということで、列の代入セルにセルF1を設定しOKすると結果が表示されます。
繰り返し操作しなければならない計算をExcelのデータテーブル機能で求めることができました。
コメント