あえてVLOOKUP関数で複数該当する２番目以降を抽出する２

このページは「VLOOKUP関数で２番目、３番目以降の値を抽出する数式」を開設する3ページ目です。

ここでは、前のページで紹介した数式で値が求められるまでを解説します。

まずは、前のページで紹介した数式をおさらいします。

上の表は社員の基本情報をまとめたものです。

この表から「上から２番目の正社員」のデータを抜き出したいとします。

言い換えると「A列に〇の付いている２番目の人」のデータを抜き出したいということです。

この時値を抽出するためにセルG5に入れた数式は、次の通りです。

{=VLOOKUP("〇",INDIRECT("$A$"&MATCH(LARGE(($A$5:$A$14="〇")*1/ROW($A$5:$A$14),2),1/ROW($A$5:$A$14),0)+4&":$E$14"),2,0)}

このページでは、この数式を使って解説していきます。

数式は内側から計算されるので、内側からみてみましょう。

２－１．まずは配列数式を理解する

数式の中身を見る前に、まずは数式が「配列数式にになっていること」を理解しましょう。

配列数式とは「同じ計算を一度に複数セル（配列）に対して行える数式」のことです。

通常計算式は「一つの値」に対して計算をします。

例えばセルA1に「３」という値があった時、「=A1=3」は「TRUE」となります。

これに対して配列数式は、複数のセルの結果を一時的に保持し、その結果をもとにさらに計算することができます。

例えば、「セルA1～A4で3がいくつあるか？」は、次のステップで計算されます。

　① セルごとに「3」かを判定（TRUE orFALSE）

　② 各セルの判定結果を保持（配列に格納）

　③ 判定結果に1を掛けて数値にする（1 or0）

　④ 結果の集計

実際にやってみた例が上の図です。

これが「VLOOKUP関数で２番目の値を抽出する」のに役立つので、大前提として覚えておきましょう。

２－２．①の部分

①の部分の数式は、

($A$5:$A$14="〇")

となっています。

配列数式であることから、この部分の計算結果は次のようになります。

このように、①の部分ではセルごとに条件に合うかを判定します。

結果、「TRUE」、「FALSE」のどちらかとなります。

２－３．②の部分

次に②の部分です。

数式は、

($A$5:$A$14="〇")*1

と、先ほどに比べ「*1」が追加されました。

TUREやFALSEは、1を掛けると「1」か「0」に変化します。

つまり、配列に格納された①の結果に1を掛けることによって、数値に変換しているのです。

このように、②の部分では「TRUE」か「FALSE」を「1」か「0」に変換しています。

２－４．③の部分

続いて③の部分です。

数式は、

/ROW($A$5:$A$14)

が追加されました。

ROW関数は「行番号を返す」関数で、例えば「ROW(A5)」は「5」が、「ROW(A14)」は「14」が返ってきます。

そして、「ROW(A5:A14)」だと次のように「それぞれの行番号」が返ってきます。

よって、

($A$5:$A$14="〇")*1/ROW($A$5:$A$14)

の結果は次の通りです。

なぜ行番号で割るかというと、それは「条件に当てはまる行にかぶりのない規則性を持った値を振るため」です。

まず、条件に当てはまらない行は全て「0」になります。

そして、条件に当てはまる行の分子は必ず「1」になるので、分母をかぶりのない値にしてやれば、返ってくる値も「かぶりのない値」になります。

そのため、「それぞれの行番号で割る」のです。

そして、行番号で割ることのもう一つのメリットは、「下の行に行くほど返ってくる値が小さくなるという規則性を持たせることができる」ことです。

先ほどの例でいうと、５行目は0.2、７行目は0.143、８行目は0.125と、徐々に値が小さくなっていっているのが分かります。

これは、下の行ほど大きな値で割っているからです。

このように「かぶりのない」、「規則性を持った」値を条件に当てはまる行に振るため、行番号で割るということをしています。

２－５．④の部分

続いて④の部分です。

LARGE(～,2)

が追加されました。

LARGE関数の引数は、

=LARGE(①配列,②順位)

で、「範囲内（①）で指定した順位番目（②）に大きな値を返す」関数です。

例の数式では、①が「($A$5:$A$14="〇")*1/ROW($A$5:$A$14)」、②が「2」 なので、「前項で求めた各値の中で『2番目』に大きな値を返す」ということになります。

そして、前項の結果を見てみると、2番目に大きな値はセルK7の「0.143」です。

ですから、④の部分は「0.143」が返ります。

これが、後で説明するMATCH関数の引数「検査値」になります。

２－６．⑤の部分

次に、⑤の

1/ROW($A$5:$A$14)

の部分です。

「ROW$A$5:$A$14」は、2-4と同様で「それぞれの行番号」が返ってくるので、「1/ROW$A$5:$A$14」は次のようになります。

これが、MATCH関数の引数「検査範囲」になります。

２－７．⑥の部分

続いて⑥の部分です。

MATCH(～)

が追加されました。

MATCH関数は「検査値と一致するセルの位置を返す」関数です。

そして、引数「検査値」は2-5から「0.143」、引数「検査範囲」は2-6から次の通りです。

結果、⑥の部分は検査値「0.143」が検査範囲の3行目にあることから「3」が返ってきます。

２－８．⑦の部分

次に⑦の部分です。

+4

が追加されました。

前項の結果が「3」でしたので、ここは「3+4」で「7」となります。

２－９．⑧の部分

続いて⑧の部分です。

INDTRECT(～)

が追加されました。

INDIREECT関数は「計算結果を他の関数の引数の一部として使うことができる」関数です。

「$A$」とMATCH関数の結果+4、そして「:$E$14」がつながるので、、⑧の部分は「$A$7:$E$14」となります。

２－１０．⑨の部分

最後に、数式全体となる⑨の部分です。

これまでの結果から数式は、

=VLOOKUP("〇",$A$7:$A$14,2,0)

となります。

VLOOKUP関数は「指定した範囲の左端で検索値を検索し、該当した行の指定した列の値を返す」関数で、引数は

=VLOOKUP（①検索値,②範囲,③列番号,④検索方法）

です。

ここでもう一度、元の表を見てみましょう。

範囲「$A$7:$A$14」の中で、一番始めに検査値「"〇"」がヒットするのは7行目です。

そして、列番号が「2」なので、返る値は「3」となるのです。

２－１１．まとめ

以上、例をもとに数式を解説しました。

まとめると、次のようになります。

ちょっと複雑ですが、慣れると悩まずに使いこなせるようになります。

以上で計算式の解説を終わります。

例では条件式を一つにしましたが、複数条件にすることもできます。

また、条件値を数値にして「以上」や「以下」などの条件式にしたり、「条件値を含む」、「条件値を含まない」などといった複雑な条件式にしたりすることも可能です。

これらは後ほどページを作成します。

以上参考になれば幸いです。

→「あえてVLOOKUP関数で2番目以降～」の数式の作り方のページに戻るにはこちら

VLOOKUP関数のその他の記事はこちらです。

• VLOOKUP関数その他の記事はこちら

• ◎基本的な使い方
• ◎「以上/未満」の条件指定の仕方
• ◎〇番台の条件にする方法
• ◎部分一致の条件指定の仕方
• ◎別シートから値を抜き出す方法
• ◎別ブックから値を抜き出す方法
• ◎数式コピーで横方向の値を簡単に取得する方法
• ◎２番目、３番目以降の値を抽出する方法
• ◎＃N/Aエラーになる３つの原因と対処方法
• ◎＃REF!エラーになる原因と対処方法
• ◎＃VALUE!エラーになる原因と対処方法
• ◎空白を０ではなく空白で表示する２つの方法
• ◎「IF(ISERROR(VLOOKUP」の意味と使い方
• ◎IFERROR関数とVLOOKUPの組み合わせ方
• ◎空白でもエラーでも空白で表示する方法
• ◎複数条件にする方法
• ◎セルや範囲を固定しコピーのズレを防ぐ方法
• ◎文字列を条件にする方法
• ◎列番号の意味と指定の仕方

関数の解説記事一覧へはこちら