エクセル関数を教えてください

質問

はじめまして。
エクセルの関数で下記内容を処理したいのですが、どんな関数を使ったらよいかわからず困っています。

--------------------------
A列　　　　B列
a-111　　red
a-333　　orage
b-222　　white
a-111　　pink
b-333　　green
a-111　　black
--------------------------

このような場合、
「a-111」と入力すると隣のセルに
「redpinkblack」というように、検索時にA列で該当したB列の文字を連結して表示したいです。

このようなことは可能でしょうか。
Excelの関数でできればと思っています。
よろしくお願いいたします。

回答No.2

kagakusuki

作業列を1列用いるだけで、複数の検索も行う事が出来る方法です。
　今仮に、

--------------------------
　A列　　　B列
a-111　　red
a-333　　orage
b-222　　white
a-111　　pink
b-333　　green
a-111　　black
b-222　　blue
--------------------------

という具合に元データが並べられているシートがSheet1であるものとします。
　そして、Sheet3のA2セルに「a-111」、A3セルに「b-222」などと入力すると、Sheet3のB2セルに「redpinkblack」、B3セルに「whiteblue」と表示させる様にするものとします。

--------------------------
Sheet3
　A列　　　B列
a-111　　redpinkblack
b-222　　whiteblue
--------------------------

　又、Sheet2のA列を作業列として使用するものとします。

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

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&INDEX(Sheet1!$B:$B,ROW())&IFERROR(REPLACE(VLOOKUP(INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&"*?",A2:INDEX(A:A,ROWS(A:A)),1,FALSE),1,LEN(INDEX(Sheet1!$A:$A,ROW()))+1,),""))

　或いは

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&INDEX(Sheet1!$B:$B,ROW())&IF(COUNTIF(C2:INDEX(C:C,ROWS(C:C)),INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&"*?"),REPLACE(VLOOKUP(INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&"*?",C2:INDEX(C:C,ROWS(C:C)),1,FALSE),1,LEN(INDEX(Sheet1!$A:$A,ROW()))+1,),""))

　次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。(Sheet1の表の行数を上回るのに十分な行数となるまで)

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

=IF(INDEX($A:$A,ROW())="","",IFERROR(REPLACE(VLOOKUP(INDEX($A:$A,ROW())&CHAR(6)&"*",Sheet2!$A:$A,1,FALSE),1,LEN(INDEX($A:$A,ROW()))+1,),"(該当無し)"))

　或いは

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF(Sheet2!$A:$A,INDEX($A:$A,ROW())&CHAR(6)&"*?"),REPLACE(VLOOKUP(INDEX($A:$A,ROW())&CHAR(6)&"*",Sheet2!$A:$A,1,FALSE),1,LEN(INDEX($A:$A,ROW()))+1,),"(該当無し)"))

　次に、Sheet3のB2セルをコピーして、Sheet3のB3以下に貼り付けて下さい。(Sheet3のA列にある「検索値の入力欄」の行数と同じ行数となるまで)

　以上で準備は完了で、後はSheet3のA2以下に「a-111」や「b-222」などといった検索値を入力しますと、隣のB列のセルに「redpinkblack」や「whiteblue」などといった結合された文字列が表示されます。

　尚、もしSheet1の表に重複しているデータが複数行存在している場合には、重複を除外した結果を表示させたいという場合には、Sheet2のA1セルに入力する関数を次の様なものとして下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&INDEX(Sheet1!$B:$B,ROW())&IFERROR(SUBSTITUTE(REPLACE(VLOOKUP(INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&"*?",A2:INDEX(A:A,ROWS(A:A)),1,FALSE),1,LEN(INDEX(Sheet1!$A:$A,ROW()))+1,),INDEX(Sheet1!$B:$B,ROW()),),""))

　或いは

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&INDEX(Sheet1!$B:$B,ROW())&IF(COUNTIF(C2:INDEX(C:C,ROWS(C:C)),INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&"*?"),SUBSTITUTE(REPLACE(VLOOKUP(INDEX(Sheet1!$A:$A,ROW())&CHAR(6)&"*?",C2:INDEX(C:C,ROWS(C:C)),1,FALSE),1,LEN(INDEX(Sheet1!$A:$A,ROW()))+1,),INDEX(Sheet1!$B:$B,ROW()),),""))

回答No.1

bunjii

>このようなことは可能でしょうか。
作業用のセルを使えば可能です。
貼付画像はE2からJ2セルを作業用に使用してD2セルへ目的の文字列を抽出したものです。
D2=IF(COLUMNS($D2:D2)<ROW(D$7),INDEX($B:$B,SMALL(INDEX(($A$2:$A$7<>$D$1)*ROW($A$8)+ROW(A$2:A$7),0),COLUMNS($D2:D2)),1)&"","")&E2
D2セルをオートフィルでJ2セルまでコピーし、E列からJ列を非表示にしました。
数式の行番号と作業用の列数は元データの数によって異なりますので現状に合わせて修正してください。