Как сделать выражение IF остается верным во всем INDEX MATCH в Excel 2010+?

Вопрос:

Я пытаюсь использовать INDEX MATCH, чтобы вернуть оптимальный выбор из списка элементов, связанных со значениями, которые меняются в зависимости от разных критериев. Моя текущая настройка и формула листа такова:

A     B    C
Item1 Bad  27
Item2 Good 15
Item3 Good 27
Item4 Bad  44

Столбец A — это именованный Item диапазона.
Столбец B является именованным диапазоном ItemType.
Столбец C — это именованный диапазон ItemValue

=INDEX(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")),MATCH(MAX(IF(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Type")="Good",INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Value"))),INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Value"),0))

(вводится как формула массива)
В текущей итерации это вернет Item1. Я бы хотел, чтобы он вернул Item3.

В этом примере 'Sheet'!B3 содержит категорию для поиска (в этом примере Item). Я использую множество именованных диапазонов на основе данных в этой ячейке и суффиксов с использованием INDIRECT/SUBSTITUTE. Моя текущая формула находит наивысшее значение, которое также является Good, но затем возвращает первое значение, которое соответствует ему, независимо от того, является ли оно Good или Bad. Я попытался добавить дополнительный оператор IF в разные точки формулы, но ячейка либо вернет FALSE либо синтаксическую ошибку формулы. Как я могу поддерживать my ="Good" оператор IF по всей формуле?

Спасибо!

Лучший ответ:

Вам нужно будет удвоить хорошее состояние. Один раз для определения максимального числа, а затем снова с максимальным числом для определения элемента.

Этот эквивалент без массива является синтаксисом, который я предпочитаю по методу массива.

=INDEX(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")), MAX(INDEX(ROW(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")))*(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Type")="Good")*(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Value")=MAX(INDEX(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Value")*(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&"Type")="Good"), , ))), , )))

Это ваш метод массива.

=INDEX(INDIRECT('Sheet'!B3), MATCH(MAX(IF(INDIRECT('Sheet'!B3&"Type")="Good",INDIRECT('Sheet'!B3&"Value"))), IF(INDIRECT('Sheet'!B3&"Type")="Good", INDIRECT('Sheet'!B3&"Value")), 0))

Если ___Type и ___Value всегда находятся в одной позиции относительно первичной, вы можете отказаться от трех именованных диапазонов и просто использовать один и тот же диапазон с OFFSET.

=INDEX(INDIRECT('Sheet'!B3), MATCH(MAX(IF(OFFSET(INDIRECT('Sheet'!B3), 0, 1)="Good",OFFSET(INDIRECT('Sheet'!B3), 0, 2))), IF(OFFSET(INDIRECT('Sheet'!B3), 0, 1)="Good", OFFSET(INDIRECT('Sheet'!B3), 0, 2)), 0))

Функция OFFSET считается изменчивой функцией. Они пересчитываются всякий раз, когда что-либо в книге изменяется, и по этой причине следует избегать. Однако вы уже используете функцию INDIRECT, которая также считается изменчивой.

Оцените статью
TechArks.Ru
Добавить комментарий