О классификации задач Поиска в EXCEL

В этой статье изложены попытки классификации задач по поиску значений в MS EXCEL. Статья может быть интересна для продвинутых пользователей EXCEL.

В этой обзорной статье рассмотрены 4 типа задач поиска значений в списках, даны ссылки на статьи-описания решения типовых примеров.

Совет . Так же на эту тему можно посмотреть статью Функция ВПР() в MS EXCEL .

1. Поиск в списке неповторяющихся значений

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

Следствием того, что поиск производится в списке неповторяющихся значений, является тот факт, что условиям поиска может соответствовать только одно значение.

По сути, пользователя интересует адрес искомого значения (номер строки или позиция значения в списке). Типичная задача : поиск позиции значения с выводом соответствующего значения из соседнего столбца (см. статью Справочник ).

Наиболее часто используемые функции для этого вида поиска: ВПР() с аргументом ЛОЖЬ, ИНДЕКС() , ПОИСКПОЗ() , ПРОСМОТР() .

Списки могут содержать значения любых типов : числа, текстовые значения и даты.

2. Поиск ближайшего числового значения

Критерием поиска служит число, как правило, но не обязательно принадлежащее интервалу значений списка, по которому осуществляется поиск.

Понятно, что результатом поиска является одно значение из списка ( ближайшее к критерию). Несмотря на то, что ближайшими могут быть несколько значений (например, 295 и 305 одинаково близки к 300), выводится, как правило, только одно.

Пользователя может интересовать как само значение из списка (ближайшее), так и его позиция в списке (в этом случае выводится соответствующее значение из соседнего столбца). Типичная задача : Поиск ближайшего события . Если пользователя интересует само значение из списка (ближайшее) , то список может содержать повторяющиеся значения (например, если в списке два значения 295 ближайших к 300, то совершенно не важно, какое из них будет выведено). Поиск существенно упрощается если список предварительно отсортирован . Если пользователя интересует позиция числа, ближайшего к критерию (для вывода соответствующего значения из соседнего столбца), то список должен содержать неповторяющиеся значения, т.к. при наличии повторов непонятно какому значению должен отдаваться приоритет (если требуется вывести все значения соответствующие ближайшим, то см. тип поиска 4).

Наиболее часто используемые функции для этого вида поиска: ВПР() с аргументом ИСТИНА, ИНДЕКС() , ПОИСКПОЗ() , ПРОСМОТР() .

Списки могут содержать числа и даты .

3. Поиск характеристики списка

Под характеристикой списка здесь понимается: минимальное/ максимальное значение списка, среднее, первое/ последнее слово по алфавиту. Список может содержать повторяющиеся значения. Хотя условиям поиска могут соответствовать несколько значений (например, два числа равны максимальному), но выводится только одно (максимальное).

Строго говоря, этот тип поиска не относится к "поиску значений в списке", т.к. пользователя не интересует какое-то конкретное значение из списка, а ему нужно найти определенную харастику списка , например, среднее значение, которое в общем случае может и не совпадать ни с одним значением списка. Типичная задача : поиск максимального значения по условию . К этому типу задач можно отнести сложение и подсчет с условиями (например, сумма всех положительных значений списка или количество значений в списке могут считаться такими же важными характеристиками списка как и минимальное/ максимальное значение).

Наиболее часто используемые функции для этого типа задач: МАКС() , МИН() , НАИБОЛЬШИЙ() , СРЗНАЧ() , СУММЕСЛИ() , СЧЁТ() и др.

Списки могут содержать значения любых типов : числа, текстовые значения и даты.

4. Поиск нескольких значений

Поиск осуществляется в списках с повторяющимися значениями.

Критерием поиска может быть:

• одно значение из списка;
• набор (интервал) значений, не обязательно принадлежащих списку;
• позиция значений в списке.

Т.к. условиям поиска могут соответствовать несколько значений , то их как правило требуется вывести в отдельный диапазон ячеек. Для этого используются как обычные формулы (например, на основе функций ИНДЕКС() , НАИМЕНЬШИЙ() , ЕСЛИ() , СТРОКА() ), так и формулы массива . Также для вывода найденных значений может применяться Расширенный фильтр с опцией «Поместить результат в диапазон » или Сводные таблицы . Если выводить значения в отдельный диапазон не требуется, то можно воспользоваться стандартным Фильтром ( CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ).

Списки могут содержать значения любых типов : числа, текстовые значения и даты. При решении задач этого типа имеет смысл использовать подстановочные знаки (*, ?) (если диапазон поиска список текстовых значений).

Типичные задачи:

• Критерий - одно значение из списка;

В статьях Запрос на выборку Элементом управления формы и Поиск текстовых значений с выводом их в отдельный диапазон приведены примеры задач, когда из исходной таблицы отбираются несколько записей, удовлетворяющих критерию (как показано на рисунке ниже (исходная таблица находится на другом листе)).

• Критерий - набор (интервал) значений, не обязательно принадлежащих списку;

В статьях Запрос формулами , Расширенный фильтр приведены примеры задач, когда из исходной таблицы отбираются несколько записей, удовлетворяющих нескольким критериям (как показано на рисунке ниже).

• Критерий - позиция значений в списке.

В статьях Последнее значение в диапазоне , Третий повтор , Последние пять значений в столбце приведены примеры вывода значений исходной таблицы в зависимости от их позиции в списке.