Приближенное и точное совпадение с несколькими критериями

Для того, чтобы найти приближенное соответствие на основе более одного критерия, вы можете использовать формулу массива, основанную на ИНДЕКС и ПОИСКПОЗ, с помощью функции ЕСЛИ.

Пример формулы в G7:

Целью данной формулы является найти размер кошки, если известен ее вес.

По сути, это просто формула ИНДЕКС/ПОИСКПОЗ. Проблемой в данном случае является то, что нам нужно "отсеивать" посторонние записи в таблице.

Это делается с помощью простой функции ЕСЛИ:

Она входит в функцию ПОИСКПОЗ как массив. Значение поиска для совпадений происходит от G6, который содержит вес (7 кг в примере).

Обратите внимание, что совпадение настроено для приблизительного совпадения, установив тип_сопоставления = 1, это нужно сортировки С5:С10.

ПОИСКПОЗ возвращает позицию веса в массиве, и передается ИНДЕКСУ как номер строки. Поисковый_массив для ИНДЕКСА размеры в D5:D10, так ИНДЕКС получает размер, соответствующий положению генерируемого совпадением (номер 6 в показанном примере).

Этот пример показывает, как использовать ИНДЕКС и ПОИСКПОЗ для получения класса из таблицы на основе заданного балла. Для этого требуется "приближенное соответствие", так как маловероятно , что реальная оценка существует в таблице.

Обратите внимание , что последний аргумент 1 (эквивалент ИСТИНЫ), что позволяет ПОИСКПОЗ выполнить приблизительное совпадение на значения , перечисленные в порядке возрастания. В этой конфигурации, ПОИСКПОЗ возвращает позицию первого значения, которое меньше или равно значению перекодировки.

Эта формула использует ПОИСКПОЗ, чтобы получить позицию строки "Истории Игрушек" в таблице, и ИНДЕКС для извлечения значения в этой строке в колонке 2.

Обратите внимание, что последний аргумент имеет значение 0, что заставляет ПОИСКПОЗ найти точное совпадение.

ИНДЕКС с одним столбцом

В приведенном выше примере, ИНДЕКС получает массив, который содержит все данные в таблице. Тем не менее, вы можете легко переписать формулы для работы только с одним столбцом, что избавляет от необходимости указывать номер столбца:

В каждом случае ИНДЕКС принимает массив одного столбца, который соответствует данным его извлечений, и ПОИСКПОЗ поставляет номер строки.

Для выполнения чувствительного к регистру совпадения, вы можете использовать функцию СОВПАД вместе с ПОИСКПОЗ в формуле массива.

Сама по себе функция ПОИСКПОЗ не чувствительна к регистру, поэтому следующая формула получает 1:

Чтобы добавить чувствительность к регистру, мы используем функцию СОВПАД:

Которая получает массив истина/ложь значения:

Этот массив переходит в функции ПОИСКПОЗ как массив. Для поиска, мы используем значение ИСТИНА с ПОИСКПОЗ, установленным в режим точного соответствия путем установки тип_сопоставления к нулю.

ПОИСКПОЗ затем получает позицию первого найденного истинного значения: 4.

Если вам нужно сделать, чувствительный к регистру поиск, вы можете сделать это с помощью формулы массива, которая использует ИНДЕКС, ПОИСКПОЗ и СОВПАД функции.

В примере, мы используем следующую формулу

Эта формула будет извлекать текст и числовые значения. Если вы хотите получить только числа, вы можете использовать формулу, основанную на СУММПРОИЗВ.