Вы задали три вопроса.
Один из них: «Может ли VLOOKUP() использоваться для этого?«Miroxlav опубликовал отличный ответ, показывающий, как это сделать с помощью вспомогательной колонки.
Ваш второй вопрос спрашивает, как получить желаемый результат, а третий спрашивает, что может быть причиной того, что ваша текущая формула возвращает неправильный ответ. Я постараюсь ответить на последние два.
Ваша формула очень близка к правильной работе. Давайте сделаем некоторые неполадки, чтобы выяснить, что не так.
Во-первых, в вашей формуле есть несколько опечаток - знак умножения отсутствует в двух местах между скобками. Excel предложил исправить это, и я удалил ссылки на листы данных («Данные L5»!) для простоты:
=INDEX(M5:M15,SUMPRODUCT(--(A5:A15="2018")*(B5:B15="2")*(F5:F15="Filler")*ROW(5:15)))
В Excel есть встроенная функция устранения неполадок, в которой вы можете выделить часть формулы в строке формул и набрать F9, чтобы увидеть значение выделенной части формулы.
Нажмите на ячейку, содержащую вашу формулу, а затем выделите выражение (A5:A15 = "2018") в строке формул и нажмите F9 (функциональная клавиша 9). Вы увидите массив, содержащий все значения FALSE . Это говорит о том, что нет ячейки в A5:A15 = "2018". Это потому что у вас 2018 в кавычках. Если вы удалите кавычки и попробуете снова, вы получите массив {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} то есть то, что вы ожидаете - первые 5 ячеек в диапазон = 2018.
Та же проблема существует во втором выражении - удалите кавычки вокруг 2 в (B5:B15="2") . Третье выражение (F5:F15="Filler") действительно нуждается в кавычках, потому что "Наполнитель" представляет собой текст , а не число.
Теперь выделите (A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler") и нажмите F9. Вы увидите результат:{0;0;0;1;0;0;0;0;0;0;0} . Что здесь произошло? Умножение массивов логических значений преобразует значения True/False в 1 и 0, а также выполняет эквивалент логического И (). Таким образом, этот результат говорит о том, что существует только одна позиция в диапазоне строк 5:15, где столбец A равен 2018, столбец B равен 2, а столбец F равен "Заполнитель". И эта позиция четвертая.
Затем ваша формула умножает этот массив на массив строк 5:15. Используя F9, вы можете увидеть этот результат - {0;0;0;8;0;0;0;0;0;0;0} . Теперь SUMPRODUCT() складывает элементы этого массива и возвращает результат 8, который является номером строки, где все три условия выполняются. Опять же, вы можете увидеть этот результат, используя F9.
А теперь вот главная проблема - после этих промежуточных вычислений ваша формула: =INDEX(M5:M15,8) , которая возвращает восьмой элемент из диапазона M5: M15. Но вам не нужен 8-й элемент, вам нужен 4-й, потому что диапазон M5: M15 начинается в строке 5, а 100% находится в 4-й ячейке в диапазоне M5: M15.
Таким образом, решение состоит в том, чтобы вычесть 4 из SUMPRODUCT():
=INDEX(M5:M15,SUMPRODUCT((A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler")*ROW(5:15))-4)
Это возвращает правильный результат - 100%.
Заметки:
- Я не мог заставить вашу формулу вернуть 81,8%, как бы я ни старался.
- Двойной унарный минус (-) в вашей формуле не нужен. Он часто используется для преобразования логических массивов в 1 и 0 ', но умножение массивов выполняет это.
Надеюсь, это поможет. Изменения, которые я изложил, позволят вашей формуле работать правильно. Используйте ответ Miroxlav, если вы хотите использовать VLOOKUP(). Удачи.
