Поиск на основе трех критериев
У меня есть этот лист.
Я хочу посмотреть значение выполнения (%) по трем критериям: год, неделя и машина. Итак, я хочу сказать поиск для поиска для года: 2018, неделя: 2, машина: заполнитель, я должен получить число 100%. Я хочу сделать то же самое для даты CILT, продолжительности (%) и счета.
VLOOKUP кажется очень ограниченным для такого рода вещей.
Информация о INDEX и MATCH, которую я нашел, не очень хорошо объяснена.
Может ли VLOOKUP сделать это? Если нет, как можно получить нужные значения?
У меня есть эта формула
= ИНДЕКС ("Данные L5"!M5:M15,SUMPRODUCT(- ("Данные L5"! A5: A15 = "2018") ("Данные L5"! B5: B15 = "2") ("Данные L5"!F5:F15="Наполнитель")*ROW("Данные L5"!5:15)))
Но он возвращает значение на одну строку вниз. Другими словами, для Года: 2018, Неделя: 2, Машина: Наполнитель, он возвращает 81,8% вместо 100%. Что может быть причиной?
3 ответа
Вы задали три вопроса.
Один из них: "Может ли 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(). Удачи.
Да, VLOOKUP() может сделать это, предоставляя вам единственное необходимое вам значение (если вам нужна сумма, используйте SUMIF()).
В этих случаях я обычно создаю и использую составной ключ для VLOOKUP().
В вашем случае такой ключ просто содержит значения из всех трех столбцов.
Это позволяет вам указать критерии поиска, используя три столбца.
Подробности:
Вставьте столбец A с заголовком Год | Месяц | Машина и формула (для A5)
=B5 & "|" & C5 & "|" & D5
Так что для A5 это будет оценивать
2018|1|Filler
,Затем распространите формулу вниз.
Везде, где вы хотите выполнить поиск, соберите один и тот же ключ и выполните VLOOKUP(), например:
=VLOOKUP("2018|2|Filler", A5:R9999, 8, FALSE)
- Отрегулируйте искомое выражение по вашему желанию, например
2018|1|Labeler
или собрать его из значений в других ячейках. - Отрегулируйте диапазон данных
A5:R9999
к реальному. - Отрегулируйте значение
8
сопоставить столбецExecution (%)
считая от левого столбца вышеуказанного диапазона.
- Отрегулируйте искомое выражение по вашему желанию, например
Как только вы это заработаете, вы также можете создать аналогичный механизм для поиска на основе трех других столбцов. Просто добавьте еще один столбец слева от тех же данных. (Причина: VLOOKUP() ожидает, что поисковый столбец будет самым левым.)
Примечание: как правило, важно иметь разделитель (в приведенном выше примере я использовал |
) чтобы ключи были уникальными. Иначе, 2018
,10
,Filler
а также 2018
,1
,0Filler
создаст тот же ключ, что явно неверно. Для разделителя используйте символ, отсутствующий в значениях.
ОБНОВЛЕНИЕ: Это решение работает. В комментариях ниже были упомянуты некоторые проблемы, и теперь они исправлены или уточнены. Спасибо Раджешу за корректуру.
Ваша формула должна понравиться,
{=INDEX(K$96:K$100,SUMPRODUCT(($D$96:$D$100="2018")*($E$96:$E$100=2)*($I$96:$I$100="Filler")*ROW($96:$100)))}
NB: только вам нужно ставить знак " * " между каждым диапазоном данных и не использовать " - - " после SUMPRODUCT.
Обратите внимание, что это формула CSE, поэтому закончите с помощью Ctrl + Shift + Enter.
Кроме того, используйте ваш диапазон данных вместо моего.
Надеюсь, это поможет вам.