S054｜Excel関数による条件を満たすデータを「・」「、」「／」で結合して抽出｜TEXTJOIN、FILTER、REPT、IF、VLOOKUP

条件に該当するデータを［、］［・］［／］などの区切り文字で結合して抽出するなら、抽出したデータと区切り文字をTEXTJOIN関数で結合して求めましょう。TEXTJOIN関数がないExcel2016では［&］で結合して求めましょう。

例題１｜

1. 「氏名」を求めるセル（B3セル）を選択し、「=TEXTJOIN(」と入力する。
2. ［区切り記号］…区切り文字「”、”」を入力する。
3. ［空のセルは無視］…省略する。
4. ［テキスト１］…FILTER関数で、「都道府県」がA3セルの「埼玉県」の条件を満たす「氏名」を抽出する数式を入力して、「Enter」キーで数式を確定する。
5. 数式を必要な分だけ複写する。

【数式】＝●●【数式】 =TEXTJOIN(“、”,,FILTER($A$7:$A$9,$B$7:$B$9=A3))

数式の解説

「=TEXTJOIN(“、”,,FILTER($A$7:$A$9,$B$7:$B$9=A3))」の数式は、B7セル～B9セルの「都道府県」がA3セルの都道府県「埼玉県」である場合の条件を満たす、 A7セル～A9セル「氏名」を「、」で結合して抽出する。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx  

例題２｜

1. 「氏名」を求めるセル（B3セル）を選択し、「=TEXTJOIN(」と入力する。
2. ［区切り記号］…区切り文字「”、”」を入力する。
3. ［空のセルは無視］…省略する。
4. ［テキスト１］…REPT関数で、「都道府県」がA3セルの「埼玉県」の条件を満たす「氏名」を繰り返して求める数式を入力して、「Ctrl」+「Shift」+「Enter」キーで数式を確定する。
5. 数式を必要な分だけ複写する。

【数式】 =TEXTJOIN(“、”,,REPT($A$7:$A$9,$B$7:$B$9=A3))

数式の解説

「REPT($A$7:$A$9,$B$7:$B$9=A3)」の数式は、「都道府県」がA3セルの「埼玉県」である場合の条件を満たす、 A7セル～A9セルの「氏名」を求める。TEXTJOIN関数の引数［テキスト１］に組み合わせることで、都道府県が「埼玉県」の「氏名」を「、」で結合して抽出できる。配列数式で求める必要があるため、「Ctrl」+「Shift」+「Enter」キーで数式を確定する。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx  

例題３｜

1. 条件を含む「都道府県」の列（B列）を並べ替えておく。
2. D列にIF関数で、1つ下の「都道府県」と同じ場合は「氏名」を「、」で結合し、違う場合はそのまま「氏名」を求める数式を入力する。
3. 数式を必要な分だけ複写する。
4. 「氏名」を求めるセル（B3セル）を選択し、VLOOKUP関数で、A3セルの「都道府県」を検索値として、作成したD列の氏名を抽出する数式を入力する。
5. 数式を必要な分だけ複写する。

【数式】=VLOOKUP(A3,$B$7:$D$9,3,0)

数式の解説

「=IF(B7=B8,A7&“、”&A8,A7)」の数式は、同じ「都道府県」の「氏名」を［、］で結合して求める。求めた「氏名」の列をVLOOKUP関数の引数［範囲］に組み合わせて数式を作成することで、都道府県が「埼玉県」の「氏名」を「、」で結合して抽出できる。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample3.xlsx