XLOOKUP関数では無理！ エクセルで条件に該当する複数データを1つのセルに列挙する技

FILTER関数とTEXTJOIN関数の組み合わせで抽出可能

　1つの情報が複数行に分けて入力されている表を見かけることがありませんか？　例えば、資料に添付することを目的とした場合などです。資料添付用としては見やすい形式ですが、データ分析には適していません。

例えば、受注ID「R-1001」には明細が2行。2行目の受注ID、受注日、顧客名は同一のため、入力されていない

　この表を元に集計したり、必要な情報に絞り込んだりする場合、空白になっている受注ID、受注日、顧客名を補完する必要があります。

受注ID、受注日、顧客名を入力した状態

　では、受注IDが「R-1001」に含まれる製品を「、」区切りで取り出したい場合はどうしましょう？　検索値である受注IDに対して複数の結果が存在するため、VLOOKUP関数やXLOOKUP関数では最初の1件しか取得できません。また、フィルターした結果をコピー＆ペーストして直接編集するのは手間がかかります。

　今回は、該当項目が複数ある場合に結果を任意の区切り文字で連結して取り出すテクニックを紹介します。

FILTER関数で条件に一致するデータを取り出す

　検索値に該当する複数のデータをすべて取り出したい場合は、FILTER関数が便利です。引数［配列］に抽出対象の列、［含む］に条件を指定するだけです。

FILTER関数の構文。引数［配列］には対象のセル範囲を指定する。［含む］には［配列］から抽出する条件を指定する。［空の場合］は条件に一致するデータがない場合に表示する値を指定する（省略可能）

　今回は製品をすべて取り出したいので、引数［配列］に「製品」列、［含む］に「指定した受注IDと一致する」という条件式を指定します。

セルJ2には条件式から参照する受注ID（R-1001）が入力してある。セルJ3に「=FILTER(E3:E19,B3:B19=J2)」と入力する

受注ID（R-1001）に一致する製品が取り出された

TEXTJOIN関数で任意の区切り文字を挟み込む

　受注IDに該当する製品をすべて取り出せましたが、縦方向に並んでしまいました。これを解決しつつ、区切り文字の「、」を挟み込むためにTEXTJOIN関数を使います。

　TEXTJOIN関数は、任意の文字を挟んで指定した文字列を連結できる関数です。なお、引数［区切り文字］に「""」と指定すると、CONCAT関数と同じく単純に文字列を連結します。

TEXTJOIN関数の構文。引数［区切り文字］に［文字列］の間に挟む区切り文字を指定する。［空のセルは無視］には「TRUE」「FALSE」を指定。空白の［文字列］がある場合、「TRUE」では区切り文字を挿入せず、「FALSE」では必ず区切り文字を挿入する。引数［文字列］には文字列かセル、セル範囲を252個まで指定可能

　ここでは、FILTER関数で取り出した製品を「、」を挟んで連結するので、数式は以下のようになります。入力済みのFILTER関数の数式を修正すると効率的です。

=TEXTJOIN("、",TRUE,FILTER(E3:E19,B3:B19=J2))

FILTER関数の数式の先頭に「TEXTJOIN("、",」と入力して［Enter］キーを押す

数式を自動修正してもいいかどうかの確認メッセージが表示される。末尾に「)」が不足しているという意味。［はい］をクリックする

受注ID（R-1001）に一致する製品が「、」区切りで表示された

　帳票形式のデータでも発想を変えれば、データを自在に抽出できるようになります。実務でのデータ活用の幅を広げる一手として、ぜひ試してみてください。