Excel 該当セルが複数ある場合にまとめて取得

質問

お世話になります。

Excelでvlookupなどて検索した時に、複数の該当セルがあった場合、
一列の文字列にして取得できるワークシート関数はないでしょうか。

例えば、

A| B
1 | AAA
2 | BBB
1 | CCC
4 | DDD
1 | EEE
などで、「1」の条件に引っかかったものを取得する場合、
「AAA,CCC,EEE」などのような結果が得られるワークシート関数です。

VBAで力技で行う方法はできるのですが、できる限りスマートな方法で
行いたいので、ワークシート関数で探しています。

以上、ご指導、何卒よろしくお願いいたします。

回答No.1

mu2011 · ベストアンサー

>VBAで力技で行う方法はできるのですが、できる限りスマートな方法
　⇒残念ながらそのような関数は無いと思います。
　　ワークシート関数でもＶＢＡと同様、配列型の数式になると想定できるので難解な数式になると思います。
　　ＶＢＡで実現できているのでしたらそのマクロをユーザ定義関数にしては駄目なんでしょうか。

お礼

うーん、やっぱりないのですかねぇ…。

回答No.4

kagakusuki

今仮に、元データが存在しているシートはSheet1であり、A列に入力されている条件は全て数値であり、条件や文字列は2行目以下に入力されているものとします。
　又、結果はSheet2に表示し、Sheet2のA列には全ての条件が順番に並べられた状態で、自動的に表示され、Sheet2のB列から右方向に向かって、該当する文字列が自動的に表示される様にするものとします。

　まず適当な列（ここでは仮にSheet3のA列とB列とします）を作業列として使用し、Sheet3のA1セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))=1,ROW(),"")

　次に、Sheet3のB1セルに次の数式を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",INDEX(Sheet1!$A:$A,ROW())&"゛"&COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW())))

　次に、Sheet3のA1～B1セルの範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL(Sheet3!$A:$A,ROWS($1:1))))

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(OR(Sheet2!$A2="",COLUMNS($B:B)>COUNTIF(Sheet1!$A:$A,Sheet2!$A2)),"",INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2&"゛"&COLUMNS($B:B),Sheet3!$B:$B,0)))

　次に、Sheet2のA1セルをコピーして、Sheet2のA2セルに貼り付けて下さい。
　次に、Sheet2のA2～B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、Sheet2のB列全体をコピーして、C列から右方向に向かって貼り付けて下さい。

　これで、Sheet1のA列に入力されている全ての条件が、重複無しに自動的に並べられると共に、該当る文字列が、その右側に並べて表示されます。

補足

回答、ありがとうございます。
いただいた方法ですと、やはりセルひとつごとに
関数を書かないといけないみたいで、一つのセルの中に
連結されて求める方法ではないみたいです。
せっかくいただいた方法ですが、ちょっと違っています。
申し訳ありません。
引き続き、ご指導をお願いいたします。

もし、関数としてはないというなら、ないということでも
結構です。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

力技と言うかスマートというか、判断任せます
C2セル =IF(A2=$C$1,B2,"")
C3セル =IF(A3=$C$1,IF(C2="",B3,C2&","&B3),C2)
下へオートフィル

別のセルへ取り出すなら =lookup("ー",C:C)

補足

回答、ありがとうございます。
一つのセルの中に結果を求める方法はないのでしょうか…。

回答No.2

MackyNo1

>、「1」の条件に引っかかったものを取得する場合、
「AAA,CCC,EEE」などのような結果が得られるワークシート関数です。

関数で対応する場合は、例えば最大重複数が３つまでと限定されているなら、ご希望の表示も可能ですが、一般的に1つのセルに結果を表示する条件だと、関数では実用的なものはありません。

例えば横方向に３つのセルに分けて表示してもよいなら、比較的簡単な配列数式で表示できます。

ただし、配列数式を多用すると、再計算に時間がかかりシートの動きが重くなるなどのデメリットもありますので、このような表示をしたい目的や数式で表示したいデータ数（関数を入力するセルの数）なども具体的に提示されたほうが良いと思います。

補足

回答、ありがとうございます。
説明不足で済みませんでした。
おっしゃる通り、説明し忘れていた点があります。
結果はすべて、一つのセルの中に、一つの
連結された文字列として表示したいのです。
よって、三つ該当する項目があった場合、
セル三つ分に並んで表示されるのではなく、
一つのセルの中に、『"AAA,BBB,CCC"』
などのような形で求めたいのです。

皆さんの回答を見させていただくと、
セルを並べての処理はできそうな感じですが、
一つのセルに収めて…というのは、やはり
関数としてはないのでしょうか…。