☰

Excel — Поиск для соответствия различным критериям

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 lookup
Excel — Поиск для соответствия различным критериям

У меня есть список полюсов, некоторые с более чем одним результатом теста. Я хочу найти последний результат теста для каждого полюса, а затем вернуть результат. Как мы знаем, VLOOKUPнаходит только первый. Я не хочу сортировать и пересортировать данные каждый раз.

Итак: три ключевых значения: col a = даты тестов, col b = номер опроса, col fa = результат теста.

У меня растущий список из более чем 5000 столбов!

Спасибо!

решение1

Для простоты я предположу, что ваша сводная таблица находится на том же листе, начиная с E1. Вам нужно будет обновить все следующие ссылки на фактическую таблицу данных. Я настроил ее следующим образом:

введите описание изображения здесь

  1. Первый столбец представляет опрос. Поместите название или номер опроса в этот столбец. Это должно совпадать с названием или номером, указанным в таблице данных, и это должно совпадать точно. Если это не точно, это не сработает.

  2. Второй столбец представляет самую позднюю дату. Используйте следующую формулу массива и обязательно удерживайте Ctrlи Shiftпри нажатии Enter. Удерживание этих клавиш создает формулу массива.

    =MAX(IF(B:B = E2, A:A, 0))

    После нажатия Ctrl+ Shift+ Enterформула должна быть заключена в фигурные скобки, например: {=MAX(IF(B:B = F2, A:A, 0))}. Фигурные скобки идентифицируют формулу как формулу массива. Если их нет, повторно введите ячейку и попробуйте снова нажать последовательность клавиш. Обратите внимание, что вам не нужно набирать фигурные скобки, они появляются автоматически.

  3. Наконец, третий столбец представляет последний результат. Это также будет формула массива, поэтому обязательно удерживайте Ctrlи Shiftпри нажатии Enter.

    =INDEX(C:C, MATCH(F2 & " " & E2, A:A & " " & B:B, 0))

Поскольку Excel обрабатывает даты как числа, мы можем использовать MAXформулу для поиска последней. Чтобы ограничить это определенным опросом, мы используем формулу массива, чтобы указать Excel перебрать даты и, если это не тот опрос, который нам нужен, использовать число 0 вместо даты. Это фактически исключает несовпадающие опросы. Как только у нас есть последняя дата, нам просто нужно извлечь результат, и поскольку мы ссылаемся на два столбца, нам нужно объединить их каким-то образом, что мы делаем, объединяя дату и опрос вместе с пробелом между ними. Формула MATCHищет точное совпадение и возвращает позицию первого найденного совпадения, что соответствует номеру строки в этом случае. INDEXЗатем формула используется для извлечения результата.

Обратите внимание, что первый результат будет возвращен, если имеется более одной совпадающей даты и опроса.

Связанный контент