Формула Excel: Что в простом английском языке содержится в этом массиве в INDEX(MATCH())?

Question

Я пытаюсь помочь другу с индексом Excel INDEX(MATCH()) она унаследовала на работе, но это поставило меня в тупик. Могут ли люди помочь мне лучше понять, что происходит внутри? Вот формат:

=INDEX(
    '[Spreadsheet2.xlsx]TabOfInterest'!$B$B
    ,
    MATCH(
        1
        ,
        (
            (D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
            *
            (F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
            *
            (K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)
        )
        ,
        0
    )
    ,
    1
)

Я могу видеть, что если число "1" (обозначающее "ИСТИНА", кажется, здесь) появляется где-то в каком-то массиве ИСТИН /1 и ЛОЖЕЙ /0, который был построен путем объединения и других заполненных BOOLEAN массивов (однако это должно работать в Excel ...), тогда INDEX выберет "номер строки" того , что было в результате, что привело к "1", и INDEX вернет значение Ячейка электронной таблицы 2 на пересечении этой строки и столбца B.

Но я понятия не имею, что происходит во втором параметре MATCH.

Я пытался играть с пустой таблицы и не могли даже вывести , что =(SingleCell=RangeOfCells) делает - или на то пошло, то , что =RangeOfCells делает - возвращаемое значение , как представляется, зависит от которой клетка набирается формула в (смещение строки от проверяемых данных), и добавление $ перед номерами строк в этих формулах не имеет значения.

Какие основные принципы, относящиеся к ссылкам на диапазоны и выполнению сравнений "равенство" между ячейками и диапазонами, мне нужно знать, чтобы лучше понять, что происходит в этой формуле, и, в конечном итоге, что пытается найти INDEX(MATCH()) ?

Что на земле в этом массиве?

(D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
*
(F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
*
(K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)

Кроме того, если бы мне пришлось угадывать, я бы сказал, что общая функция делает это, но меня беспокоит, что я не могу объяснить, почему:

Если есть одна строка Spreadsheet2, где ВСЕ три из этих условий ИСТИНА:

• ячейка D34 любой электронной таблицы, внутри которой находится этот INDEX-MATCH, появляется в столбце электронной таблицы 2 "M" И
• ячейка F34 любой электронной таблицы, внутри которой находится этот INDEX-MATCH, появится в столбце Spreadsheet2 "P" AND
• ячейка K34 любой таблицы, внутри которой находится этот INDEX-MATCH, появится в столбце таблицы Y "Y"

Затем верните значение ячейки в Spreadsheet2, столбец B, независимо от того, для какой строки в Spreadsheet2 это составное условие было истинным.

Answer 1

Давайте немного упростим это и посмотрим, сможем ли мы выяснить, что происходит. На рисунке ниже я настроил D1, E1 и F1 для имитации D34, F34 и K34 в вашей формуле и заполнил их значениями. Для ухмылок я добавил еще две строки значений ниже первых трех ячеек. Подробнее об этом позже.

Затем в столбцы H, I и J я добавил массивы, эквивалентные вашим столбцам M, P и Y. Я также немного сократил их, главным образом потому, что приведенный ниже трюк отладки не сработает, если они представляют собой целые столбцы.

В G7 я ввел только часть MATCH() вашей формулы выше. Как видно из фигурных скобок, заключающих его, это формула массива. Это введено, нажимая CTRL-Shift Enter, и Excel добавляет фигурные скобки. Как только это будет сделано, вы можете заполнить еще две строки. Понятно, что функция MATCH() предлагает несколько ответов.

Вы правы, что умножение логических значений является эквивалентом операции AND(), за исключением того, что она дает 1 и 0 в качестве результатов вместо True и False. Вы можете проверить это, например, набрав =True*True или =True*False в ячейке.

Вы спросили, что может быть в довольно странном массиве (да, это массив). Итак, давайте посмотрим на то, что Excel считает его значение.

Щелкните внутри одной из формул, а затем нажмите на элемент формулы, значение которой вы хотите увидеть - в данном случае это "lookup_array". Это выделяет массив lookup_array, как показано на следующем рисунке.

Теперь нажмите F9. Это позволяет Excel отображать значение выделенного элемента.

И Excel говорит нам, что значением выделенной части формулы является массив {1; 0; 0; 0; 0}. Если вы сделаете это для G8 и G9, вы увидите значения {0; 0; 0; 0; 1} и {0; 0; 0; 1; 0} соответственно. Таким образом, функция MATCH() находит позицию 1 в этих массивах, то есть 1, 5 и 4.

Вы можете думать об этом так: первая часть lookup_array проверяет, где D1 равен H1: H5 - эта часть возвращает {1; 1; 0; 0; 0}. Затем проверка E1 в I1: I5 дает {1; 0; 1; 0; 0}. Проверка F1 в J1: J5 дает {1; 0; 1; 0; 0}. AND, используя все три из них, дает {1; 0; 0; 0; 0}, что является результатом, который Excel показывает для всего (по общему признанию странного) массива. Аналогично, формула в G8 находит {0; 0; 0; 0; 1}, {0; 1; 0; 0; 1} и {0; 0; 0; 0; 1), а затем ANDs их, чтобы получить {0; 0; 0; 0; 1}.

Ваше предположение о том, что происходит, верно: MATCH() находит первую строку (Match type = 0) в H, I и J, которая имеет значения, соответствующие ячейкам в D, E & F, а затем INDEX () возвращает эту позицию в колонке Б.

Вы можете поиграть с вышеуказанными значениями и убедиться в этом сами. Не забудьте использовать CTRL-Shift Enter для ввода формулы массива - вы будете делать это много, так как в Excel есть ошибка / функция, при которой нажатие на формулу, а затем нажатие клавиши return или tab дает значение #VALUE! ошибка. CTRL-Z отменит это.

Надеюсь, что это помогает и удачи.

Answer 2

Это написано как функция массива? То есть фигурные скобки с обеих сторон и введенные с помощью ctrl/enter? Если это так, то в средней части сравнивается значение в массиве, соответствующее строке, в которой он находится. Учитывая умножение, я бы сказал, что вы правы, что он ищет что-то, где все 3 значения верны.

Я подозреваю, что, не видя вашей электронной таблицы (и, конечно, не исчерпав подробных подробностей вашего поста), есть диапазон, который имеет эту функцию, и вы случайно показали нам одну из них. Я подозреваю, что строка до и строка после (и многие другие тоже) имеют одну и ту же формулу с соответствующим изменением номеров строк. Если это так, то я удваиваю ожидание того, что это функция массива, написанная один раз и примененная к ряду ячеек, примененная с помощью ctrl/enter, чтобы она повторялась во всех ячейках во время выполнения.

И если это так, редактирование формулы и использование ввода для ее сохранения (даже если вы не вносили никаких изменений или изменений) изменят поведение и, следовательно, результаты.

Если ничего из этого не имеет смысла, не стесняйтесь прислать мне таблицу, и я посмотрю.