☰

Excel の複数のテーブルの検索、よりエレガントなソリューションを見つけられますか?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function vlookup lookup
Excel の複数のテーブルの検索、よりエレガントなソリューションを見つけられますか?

これは部分的には楽しみのためであり、主観的な理由で閉じられないことを願っています。なぜなら、「どのソリューションが最もエレガントであるか」は主観的ですが、「現在のソリューションは複雑で醜い」ことは誰の目にも明らかだからです。

とにかく、sheet1 に次のようなリストがあります:

*Thing*            *type of thing*
Apple
Orange
Larceny
Cake
Banana
Murder

シート2では次のようになります:

              fruit    confectionary      crime
Apple           x                                
Orange          x                                
Larceny                                     x    
Cake                         x                   
Banana          x                                
Murder                                      x

そして、シート1の列Bを次のように返したいのです

*Thing*               *type of thing*                                   
Apple                 fruit                        
Orange                fruit                        
Larceny               crime                        
Cake                  confectinary               
Banana                fruit                        
Murder                crime

現在の解決策を回答として投稿します。うまくいきました。それは認めます...

この問題にどう取り組みますか?

答え1

これが私の解決策です。Index+match を使用すると、テーブル内の値を両方向で検索できます。これにより、通常のソリューションが調整され、x が検索されて列ヘッダーが返されます。

=INDEX($E$1:$G$1,MATCH("x",OFFSET($E$1:$G$1,MATCH(A2,$D$2:$D$7,0),),0))

ここに画像の説明を入力してください

すべてを 1 つのシートにまとめたので、見やすくなり、関数も少し短くなりました。

以下の Barry のコメントによれば、より良い選択肢は、別のインデックス関数を使用してオフセットを回避し、揮発性にならないようにすることです。

=INDEX($E$1:$G$1,MATCH("x",INDEX($E$2:$G$7,MATCH(A2,$D$2:$D$7,0),0),0))

答え2

私の現在の解決策としては

=MATCH(A1,Sheet1!A1:A10,0)

列 B で、シート 2 の項目を含む行を返します。ここまでは順調です。しかし、シート 2 のその行内の x の位置を返し、正しい単語を返すには、間接関数内で R1C1 参照の連結文字列を使用して行番号を実際に使用できる参照に変換するという、この怪物のようなコードが必要です。

=IF(INDIRECT("sheet2!R"&B1&"C2",0)="X","fruit",IF(INDIRECT("sheet2!R"&B1&"C3",0)="X","confectionary",IF(INDIRECT("sheet2!R"&B1&"C4",0)="X","crime","ERROR")))

次に、文字列は「sheet2R[行番号]C[列]」と評価され、間接的に（通常の参照に変換されて）入力され、if文に渡されてxの存在が関連する単語に変換されます。

答え3

これは私が好む解決策です。gtwebbのコンセプトに似ていますが（データがスクリーンショットのように整列していると仮定）、使用しませんOFFSET（これは他に選択肢がない場合にのみ使用すべきだと思います。不安定な）。

=INDEX($E$1:$G$1,MATCH("x",$E2:$G2,0))

関連情報