Поиск ближайшего соответствия с помощью функции ИНДЕКС и ПОИСКПОЗ

Posted on: 2020年10月22日 2020年11月12日
Tags: lookup-formulas

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

Сценарий:

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

Как решить проблему?

Чтобы формула сначала поняла, нам нужно немного пересмотреть следующие функции. link: / lookup-formulas-excel-index-function [INDEX function]

link: / lookup-formulas-excel-match-function [MATCH function]

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

Общая формула:

{ = INDEX ( data , MATCH ( MIN ( ABS ( data - value ) ) , ABS ( data - value ) , 0 ) ) }

data: массив значений даты value: заданное значение даты, где результат наиболее близок к match_type: 1 (точное или следующее наименьшее) или 0 (точное совпадение) или -1 (точное или следующее наибольшее значение)

НЕ используйте фигурные скобки вручную. Фигурные скобки применялись с помощью Ctrl + Shift + Enter вместо Enter.

Пример:

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

Именованный диапазон, используемый для данных (D4: D12).

Здесь мы использовали именованный диапазон вместо массива ссылок на ячейки, поскольку это легко понять. Нам нужно искать дату, которая ближе всего к заданной дате в ячейке G6.

Используйте приведенную выше формулу, чтобы получить ближайшую дату.

Используйте формулу:

{ = INDEX ( data , MATCH ( MIN ( ABS ( data - G6 ) ) , ABS ( data - G6 ) , 0 ) ) }

НЕ используйте фигурные скобки вручную. Фигурные скобки накладываются с помощью Ctrl Shift + Enter вместо Enter.

Пояснение:

link: / Mathemat-functions-abs-the-absolute-value-function [ABS]

функция возвращает положительное целое число после вычитания, выполненного для значений даты (данные — G6). * link: / Mathemat-functions-abs-the-absolute-value-function [ABS]

(data — G6) создает массив, вычитая все значения даты из заданного значения даты в ячейке G6. Возвращенный массив.

\ {160; 49; 105; 1048; 600; 6; 702; 822; 673} * link: / statistics-excel-min-function [MIN] (link: / Mathemat-functions-abs-the-absolute-value-function [ABS]

(данные — G6) ) возвращает ближайшее значение из массива, полученного на предыдущем шаге.

ИНДЕКС (данные, ПОИСКПОЗ (6, \ {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))

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

именованный диапазон данных, используемый для массива дат. Теперь используйте Ctrl + Shift вместо Enter, чтобы получить результат, так как это формула массива. Формула массива — это формула, в которой массив значений переходит от результата одной функции в качестве аргумента к внешней функции.

Как видно из приведенного выше снимка, мы получили наиболее близкое соответствие с указанной датой в таблице. Извлеките детали из таблицы, используя указанную выше формулу.

Вот некоторые наблюдательные замечания при использовании приведенной выше формулы.

Заметки: . Функция возвращает ошибку #NA, если аргумент массива поиска функции ПОИСКПОЗ не имеет той же длины, что и массив таблицы.

Формула возвращает ошибку, если lookup_value не соответствует значению в таблице lookup_array.
Функция соответствует точному значению, поскольку аргумент типа соответствия функции ПОИСКПОЗ равен 0.
Значения поиска могут быть заданы как ссылка на ячейку или напрямую с использованием символа кавычки («) в формуле в качестве аргументов.
НЕ используйте фигурные скобки вручную. Фигурные скобки применяются с помощью Ctrl + Shift + Enter вместо простого ввода в противном случае возвращается ошибка.

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

Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам по адресу [email protected]

Статьи по теме

`link: / формулы-поиска-использование-индекса-и-сопоставления-значения-поиска [Использовать ИНДЕКС и ПОИСКПОЗ для поиска значения] `: ИНДЕКС & M Функция ATCH для поиска необходимого значения.

link: / summing-sum-range-with-index-in-excel [SUM range with INDEX in Excel]: Используйте функцию INDEX, чтобы найти СУММУ значений по мере необходимости.

link: / lookup-formulas-excel-index-function [Как использовать функцию ИНДЕКС в Excel]: Найдите ИНДЕКС массива с помощью функции ИНДЕКС, объясненной в примере.

link: / lookup-formulas-excel-match-function [Как использовать функцию ПОИСКПОЗ в Excel]: Найдите ПОИСКПОЗ в массиве, используя значение ИНДЕКС внутри функции ПОИСКПОЗ, объясненное в примере.

link: / lookup-formulas-excel-lookup-function [Как использовать функцию ПРОСМОТР в Excel]: Найдите значение поиска в массиве с помощью функции ПРОСМОТР, объясненной в примере.

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Найдите значение поиска в массиве с помощью функции ВПР, объясненной в примере.