☰

Поиск по нескольким таблицам в Excel. Можете ли вы найти более элегантное решение?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function vlookup lookup
Поиск по нескольким таблицам в Excel. Можете ли вы найти более элегантное решение?

Это отчасти ради развлечения, и я надеюсь, что дискуссия не будет закрыта по причине субъективности, поскольку, хотя вопрос «какое решение наиболее элегантно» является субъективным, то «текущее решение запутанное и уродливое» очевидно для всех.

В любом случае, у меня есть список на листе 1, вот такой:

*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

и я хочу вернуть столбец B на листе 1 вот так

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

Я опубликую свое текущее решение в качестве ответа. Оно работает, я так и сделаю...

Как бы вы подошли к решению этой проблемы?

решение1

Вот мое решение. Индекс+соответствие можно использовать для поиска значений в таблице в обоих направлениях. Это подстраивает обычное решение так, что оно найдет x и вернет заголовок столбца.

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

введите описание изображения здесь

Я разместил все на одном листе, чтобы было легче смотреть, а также чтобы функция была немного короче.

Согласно комментарию Барри ниже, лучшим вариантом было бы избежать смещения с помощью другой индексной функции, чтобы она не была изменчивой.

=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, возвращая строку, содержащую вещь в sheet2. Пока все хорошо. Но чтобы затем вернуть позицию x в этой строке в sheet2 и дать правильное слово, у меня есть это чудовище, использующее ссылку 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[rownumber]C[column]», которая передается в косвенную ссылку (преобразуется в обычную ссылку), которая затем переходит в оператор if, чтобы преобразовать присутствие x в соответствующее слово.

решение3

Вот мое предпочтительное решение, концепция которого похожа на концепцию gtwebb (и предполагается, что ваши данные выровнены, как на его снимке экрана), но оно не использует OFFSET(что, как я считаю, следует использовать только тогда, когда нет других вариантов, потому что этоизменчивый).

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

Связанный контент