データ整理をする際に結構多いシチュエーションだと思います。
けどサッと探してピッタリな記事がなかったので、備忘録もかねて書きます。
完成イメージはこんな感じです。
鈴木1郎～鈴木100郎の中から複数の検索条件に合致するものを複数返してくれていますね。
サッと調べるとこういった記事が出てきますが、INDEX関数やVLOOKUP関数は複数条件を設定しても単一の答えしか返してくれません。
Excel2010： 複数列の条件に合致した値を取り出す（条件エリア不要）★SUMPRODUCT関数、INDEX関数 - 教えて！HELPDESK
しかも関数が複雑でわかりにくい。
今回紹介する方法は結構ゴリ押しです。
その分だけ分かりやすいと信じています。
[目次]
1.検索条件に一致しているかを判定
各項目で個別に判定します。複数組み合わせると複雑になってしまうので。
ひとまずこんな感じのやつが目標です。
完全一致の場合の処理
まずは一番簡単なH列の処理について。
H列の処理はこのようになっています。
=IF(H$3=D4,1,0)
IF(条件, TRUE の処理, FALSE の処理)
条件を満たすときはTRUE の処理を返します。満たさないときは FALSEの処理 を返します。
IF関数のルールはこのような処理なのでH列は
=IF(H$3=D4,1,0)
もしH3とD4が一致しているなら,1を返す,一致していないなら0を返す
つまり血液型がO型の人は1,そうでない人は0と返すだけの簡単な処理です。
部分一致の場合の処理
次にF列とG列の処理について。
=だと完全一致のみを判定するので、趣味欄で散歩 を検索しても 散歩,読書の人は除外されてしまいます。
そこでワイルドカードである「*」を使います。
これを使うと1文字以上の任意の文字列として機能してくれます。
つまり「*阿部*」で検索すると阿部寛と阿部サダヲのどちらかが含まれているものが判定されます。
しかし悲しいことにIF関数を使うと文字列「*阿部*」として検索されてしまうので、別の関数を使います。
というわけでF列の処理はこちら。
=COUNTIF(B4,"*"&F$3&"*")
COUNTIF(範囲, 条件)
範囲の中で条件を満たすセルの数を数えます。
=COUNTIF(B4,"*"&F$3&"*")
B4のセルが,F3が部分一致するならばB4のセルのみカウント（1を返す）
範囲の部分は相対参照、条件の部分は絶対参照なので、常に条件と一致しているか1セルだけ判定して一致していたらそのセルをカウント(1を返す)というわけです。
複数の検索条件に一致していたら該当と表示する
これまでに各条件に一致しているかどうかを0or1で判定してきました。
という事で3つの検索条件に全て合致していたら和が3になっているはずですので
=IF(SUM(F4:H4)=3,"該当","")
という処理をします。
各条件判定の和が3（すべての条件に一致）しているなら,「該当」と表示,そうでないなら「」と表示（空白のセル）
これだけです。
2.該当しているものを番号順に列挙
ここから先はこの記事を参考にしています。
関数で条件を満たす複数のデータを表から取り出す方法 [エクセル（Excel）の使い方] All About
ここでの目標はこんな感じです。
該当者に連番を振る
=IF(I4="","",COUNTIF(I$4:I4,"該当"))
合わせ技です。
もし空白のセルなら,何もしない,そうでないならI4（該当判定列の一番上のセル）から処理している場所までにある「該当」の数を数える
これで該当している人の横に連番が振られていきます。
連番付けした列に対してMAX関数を使うことで該当人数も分かります。
MAX(数値1, 数値2 …)
数値1から数値2の中で最大値を返す。
=MAX(J:J)
とすればJ列（連番付けの列）の最大値を返してくれるので該当人数が分かります。
該当者を連番に従って並び替え
下準備として並び替えたい場所に、1からオートフィルで連番を打っておいてください。
最期の処理はこちらです。
=INDEX(A$4:D$103,MATCH($L3,J$4:J$102,0),1)
INDEX(対象範囲, 行番号, 列番号)
対象範囲の中から行番号と列番号の交差するセルの参照を取得します。
説明の為に再掲。これはM列の処理になります。
=INDEX(A$4:D$103,MATCH($L3,J$4:J$102,0),1)
まず対象範囲はA$4:D$103 処理したいデータが入っているところです。
次に行番号
MATCH(検索値, 検索範囲, 照合の型)
照合の型で検索範囲にある検索値と一致するセルの位置を取得するように指定できます。
を使います。
MATCH($L3,J$4:J$102,0) というのは
J4～J102（連番付けの範囲）にある数字とL3（並び替え後の連番1）が完全一致していたらそのセルの位置を取得 ということです。
そしてINDEXの処理に戻ると
=INDEX(A$4:D$103,MATCH($L3,J$4:J$102,0),1)
列番号のところは氏名の所がもらえればいいので,A$4:D$103の範囲の1番目の列なので１を指定します。
つまり連番と一致している人の氏名を引っ張ってきているだけの処理です。
これで完成です。
3.もっとスマートにするために
赤枠で囲った部分がイケてない
連番も手入力だし、枠が余ると参照エラーになってしまうのでここの部分をスマートにしていきます。
該当人数を越えたら非表示にする処理
= IF(ROW()-2<=K$3,ROW()-2,"")
ROW()はそのセルの行位置を返してくれる関数ですので、-2とか良い感じの調整をして
該当人数を越えたら非表示 というゴリ押しです。
氏名の方も同様に
=IF(K$3>=L3,INDEX(A$4:D$103,MATCH($L3,J$4:J$102,0),1),"")
該当人数を越えたら非表示になるような処理をします。
最後にワークスペースを見えないところに移動して体裁を整えれば完成です。
各関数の処理については以下の記事を参考にしました。
