Резюме
Чтобы выполнить двусторонний поиск приближенного соответствия с несколькими критериями, вы можете использовать формулу массива на основе ИНДЕКС и ПОИСКПОЗ с помощью функции ЕСЛИ для применения критериев. В показанном примере формула в K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
где данные (D6: H16), диаметр (D5: H5), материал (B6: B16) и твердость (C6: C16) - это названные диапазоны, используемые только для удобства.
Примечание: это формула массива, которую необходимо вводить с помощью Control + Shift + Enter.
Объяснение
Цель состоит в том, чтобы найти скорость подачи на основе материала, твердости и диаметра сверла. Значения скорости подачи находятся в данных названного диапазона (D6: H16).
Это можно сделать с помощью двухсторонней формулы ИНДЕКС и ПОИСКПОЗ. Одна функция ПОИСКПОЗ вычисляет номер строки (материал и твердость), а другая функция ПОИСКПОЗ находит номер столбца (диаметр). Функция ИНДЕКС возвращает окончательный результат.
В показанном примере формула в K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Разрывы строк добавлены только для удобства чтения).
Сложность заключается в том, что материал и твердость нужно обрабатывать вместе. Нам нужно ограничить ПОИСКПОЗ значениями твердости для данного материала (низкоуглеродистая сталь в показанном примере).
Мы можем сделать это с помощью функции ЕСЛИ. По сути, мы используем IF, чтобы «отбросить» нерелевантные значения, прежде чем искать совпадение.
подробности
Функция ИНДЕКС дается именованный диапазон данных (D6: H16) , как для массива. Первая функция ПОИСКПОЗ вычисляет номер строки:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Чтобы найти правильный ряд, нам нужно точно сопоставить материал и приблизить его твердость. Мы делаем это, используя функцию ЕСЛИ, чтобы сначала отфильтровать нерелевантную жесткость:
IF(material=K5,hardness) // filter
Мы проверяем все значения материала (B6: B16), чтобы убедиться, что они соответствуют значению в K5 («Низкоуглеродистая сталь»). Если это так, значение твердости пропускается. Если нет, IF возвращает FALSE. Результатом является такой массив:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Обратите внимание, что единственные сохранившиеся значения связаны с низкоуглеродистой сталью. Остальные значения теперь ЛОЖЬ. Этот массив возвращается непосредственно в функцию ПОИСКПОЗ как lookup_array.
Значение поиска для совпадения поступает из K6, который содержит заданную твердость, 176. MATCH настроен на приблизительное совпадение, задав для match_type значение 1. С этими настройками ПОИСКПОЗ игнорирует значения FALSE и возвращает положение точного совпадения или следующего наименьшего значения. .
Примечание: значения твердости должны быть отсортированы в порядке возрастания для каждого материала.
Если задана твердость 176, ПОИСКПОЗ возвращает 6, передаваемое непосредственно в ИНДЕКС как номер строки. Теперь мы можем переписать исходную формулу следующим образом:
=INDEX(data,6,MATCH(K7,diameter,1))
Вторая формула MATCH находит правильный номер столбца путем приблизительного сопоставления диаметра:
MATCH(K7,diameter,1) // get column num
Примечание: значения диаметра D5: H5 должны быть отсортированы в порядке возрастания.
Значение поиска поступает из K7 (0,75), а массив lookup_array - это диаметр именованного диапазона (D5: H5).
Как и раньше, MATCH устанавливается на приблизительное совпадение, устанавливая match_type равным 1.
Если диаметр равен 0,75, ПОИСКПОЗ возвращает 3, передаваемое непосредственно в функцию ИНДЕКС в качестве номера столбца. Исходная формула теперь принимает следующий вид:
=INDEX(data,6,3) // returns 0.015
ИНДЕКС возвращает окончательный результат 0,015, значение из F11.
