Excel: как динамически находить строку с помощью формулы сопоставления индексов

#excel #excel-formula

#excel #excel-формула

Вопрос:

Есть две таблицы:

Содержит фактические значения
Содержит ранжирование значений по столбцам в первой таблице

Пожалуйста, смотрите Изображение ниже для примера:

Как показано на рисунке выше, я хочу найти строку в таблице ранжирования справа, которая имеет значение 3 , в столбце Tone_Rank .

Затем я хотел бы использовать эту строку, чтобы найти фактическое значение, соответствующее этой строке, для столбца, начинающегося с того же имени в таблице справа. Вот что у меня есть до сих пор:

=INDEX(B2:E6,MATCH(???),MATCH(M4,G1:J1,0))

1. Почему ответ должен быть 22 , а не 23 ?

2. @ScottCraner Извините, вы правы, я обновлю

3. И основаны ли ранги на порядке ранжирования значений?

4. Да, порядок значений — это то, как работает таблица рангов: 1 — наименьшее значение, а 5 — наибольшее. Но моя реальная таблица содержит около 500 строк и 30 столбцов, просто разместив меньший образец в качестве примера

Ответ №1:

Пропустите таблицу ранжирования:

 =SMALL(INDEX($B$2:$E$6,0,MATCH(LEFT(M4,FIND("_",M4)-1),$B$1:$E$1,0)),M2)

1. У меня есть к вам еще один вопрос, выходящий за рамки исходного вопроса, если у вас есть минутка. Представьте, что в ячейке M1 у вас есть либо значение «выше», либо «ниже». Если значение «выше», вы должны ранжировать значения в порядке, который мы показали выше. Но если значение «ниже», вы бы оценили их в противоположном направлении. Как вы могли бы настроить приведенные выше формулы для учета этого критерия?

2. Если это выходит за рамки, пожалуйста, задайте новый вопрос.

3. Возможно, я смогу понять это с помощью небольшой подсказки. Если бы я просто использовал IF оператор and, а затем использовал Large вместо Small того, чтобы менять ряды, это сработало бы?

Ответ №2:

Как насчет следующего: =INDEX($B$1:$E$6,MATCH($M$2,INDEX($G1:$J6,,MATCH($M$4,$G$1:$J$1,0)),0),MATCH($M$4,$G$1:$J$1,0))

Для получения правильного столбца для поиска номера строки вашего значения требуется дополнительное сопоставление индексов.

Вы могли бы даже сделать все это сразу без вспомогательной таблицы _Rank: =INDEX($A$1:$E$6,MATCH(LARGE(INDEX($A$1:$E$6,,MATCH($M$4,$A$1:$E$1,0)),$M$2),INDEX($A$1:$E$6,,MATCH($M$4,$A$1:$E$1,0)),0),MATCH($M$4,$A$1:$E$1,0)) обратите внимание, что значение в M4 then должно быть «Tone», а не «Tone_Rank»

Я надеюсь, что это поможет (и да, вы можете переключиться с большого на маленький, чтобы изменить рейтинг (высокий-низкий против Низкий-высокий).

Лол, я пропустил, что Скотт уже опубликовал лучшую версию этого