「複数」に対応した検索
関数を使った検索を行う際、「複数の結果を返す」方法や、「複数の条件」で検索するための方法について紹介します。これらは、以前にVlookup関数に関する投稿の際に紹介した「４つのVlookup関数の欠点」の3番目と4番目に対応するものです。
Vlookup関数やIndex関数では、これらの「複数の条件・返り値」に対応することは難しくなります。どうしてもこれらの関数で処理を行いたい場合は、元のデータテーブルに補足列を追加したり、またはデータをある条件にそって並び替えたりしている必要があります。場合によっては、それらの方が簡単で理解しやすいこともあると思いますが、ここではこれらの作業なしに、直接上記の二つの目的を達成することを試みてみます。
「複数の返り値」と「複数の条件」は一見異なる問題に聞こえますが、実は事の本質は全く同じです。それは「上から何番目にあるかを調べる」ということです。
例えば、「複数の返り値」の場合は、まず必要な情報は「ある条件のもと、△回目にヒットするのは、上から数えて〇番目のセルにある」ということです。同様に「複数の条件」の場合、知りたいのは、「条件が△個あり、△個の条件全てにヒットするのは、上から数えて〇番目にある」ということが知りたいのです。つまり、この〇に入る数、すなわち「上から数えて何番目か」をまず取得します。
この〇に入る数字を取得するために、簡単な配列関数を使用します。配列関数についてのオフィシャルサイトでの紹介は以下の通りです。
多くの場合、配列関数はその内容が複雑になりがちです。しかし、今回のケースはそこまで複雑なものにはなりません。
仮に、データテーブルに、1から始まる連番が記載されている列がある、と仮定しましょう。もちろん、そのような状況は考えにくいのですが、話を簡単にするため、最初はこの仮定を置きます。
まず「複数の返り値」についてです。例えば、上から数えて2番目に条件に合うセルが、上から何番目のセルなのかを調べてみます。式は以下の通りです。
=SMALL(IF(検索列１=条件１, 連番列), 2)
Small関数は、下から数えて△番目の値を返す関数です。
IF関数で指定している条件に合う場合のみ、対応している連番(1,,,4,,,7,,,10,,,13,,,16,,,19,)の数字が配列として返り、更にその中から2番目に小さい数字(4)を最終的に返します。これで「上から数えて何番目か」が分かったことになります。
最後に、返ってきた４という数字をIndex関数の中で使うことにより、2番目に条件に合った行の値を返すことができます。
下のシートでは、検索列の中に検索値である「あ」があるか見ており、セルF6では、2番目に「あ」が見つかった行から参照値を返しています。式は以下の通りです。
=INDEX($C$2:$C$21,SMALL(IF($B$2:$B$21=$F$2,$A$2:$A$21),E6))
SMALL関数で、"４"という数字を返していることになります。
複数の条件を扱う場合は、IF関数の中の条件式（検索列=条件）を＊で掛けて繋げます。何故このようなことをするのか、という疑問があるかもしれませんが、詳しい説明は割愛します。ちなみに、ANDの場合は式を掛けて（＊）、ORの場合は式を足す（＋）という処理をします。
では最後に、「１から始まる連番がデータテーブルに存在している」という不自然な仮定を外した場合の処理を考えます。上記の式では、IF関数がTRUEの時に返す値として、この連番を指定していました。
ここに、ROW関数を使って取得できるテーブルの行番号の連番の値を入れます。データテーブルが始まる行番号はいつも同じというわけではないので、ヘダーの行番号で引き算すると、１から始まる連番が取得できたことになります。
では実際の処理を見てみましょう。セルF6には、２つの条件「あ」と「ア」で、2番目に両方の条件が合う行の参照値を返しています。式は以下の通りです。
=INDEX($C$2:$C$21,SMALL(IF( ($A$2:$A$21=$F$1)*($B$2:$B$21=$F$2),ROW($A$2:$A$21)-ROW($A$1)),E6))
最初の Row関数で｛2,3,4,5,...,21}という配列を指定し、二つ目のRow関数でヘダー行(1)を引くことで、1から始まる連番｛1,2,3,4,...,20}を取得しています。上の例と同様に、SMALL関数で「上から何番目の行か」を取得しています。
式が少し長くなってしまうのですが、行っていることはそこまで複雑なことではありません。また、配列関数ですので、式を入力した後はCtrl + Shift + Enterで配列関数にします。
複数の条件や複数の返り値を扱うケースというのは、実務上どこまで必要とされるのか分かりませんが、いざという時に使えるように備えておきたいものです。
