microsoft excel

A列の値とB、C、D列の値をマッチングさせ、マッチングが成立した列の名前を返す方法

こんな感じのテーブルがあります(ただし数千行)。

A B C D
a1 a3 a1 a4
a2 a2 a5
a3
a4
a5

この列は、Aの各要素がどの「カテゴリー」(B、C、Dは異なるカテゴリー)であるかを教えてくれる。

私の考えでは、B:DでAのすべての値を検索し、一致するものが見つかったら、一致した列の名前を返すようにしたいと思います。

残念ながら、私はExcelを使うのに慣れておらず(普段はRを使っています)、ちょっと勝手が違うのです。何かお手伝いいただけると幸いです。

望ましい出力は次のようなものだ。

A B C D E
a1 a3 a1 a4 "C"
a2 a2 a5 "C"
a3 "B"
a4 "D"
a5 "D"

この解決策を試してみてください。

以下のスクリーンショットをご参照ください。

E1では、数式は

=SUBSTITUTE(ADDRESS(1,MAX(IF($B$1:$D$5=A1,COLUMN($B$1:$D$5),0)),4),1,"")

この数式を作成した後、これを配列数式に変換する必要があることに注意してください。CTRL+SHIFT+ENTERを数式バーから選択します。数式は自動的に中括弧で囲まれ、配列数式であることが示されます。

この手順を踏まないと、正しく動作しません。

目的の行までドラッグダウンする。

また、IFERRORを使用して、#VALUEや#N/Aエラーを排除し、値が見つからない場合は空白に置き換えることができます。

リストの区切り文字(地域によっては,の代わりに;)によっては、念のため、数式中のカンマをセミコロンに置き換える必要があります。

列の文字をダブルクォーテーションで囲む場合は、そのようにします。

=""""&SUBSTITUTE(ADDRESS(1,MAX(IF($B$1:$D$5=A1,COLUMN($B$1:$D$5),0)),4),1,"")&""""



複数回発生する可能性がある場合は、以下のような方法を試してみてください。

=TEXTJOIN(",",,SUBSTITUTE(IFERROR(SORT(ADDRESS(1,($A2=B2:D6)*COLUMN(B2:D6),4)),""),"1",""))

で、これも列をソートします。

Excel の列ラベルと一致するリテラル文字ラベル以外のものがある場合は、次のようにすべてを収集します。 SORT を使用して、列の順序で並べ替えるか、実際のヘッダー結果をアルファベット順に並べ替えることができます。

=TEXTJOIN(",",,IFERROR(SWITCH(SUBSTITUTE(IFERROR(SORT(ADDRESS(1,($A2=B2:D6)*COLUMN(B2:D6),4)),""),"1",""),"B",B1,"C",C1,"D",D1),""))