Возможно, есть более элегантный способ сделать это, но здесь есть, по крайней мере, не элегантное решение. Формула становится большой, поэтому я объясню ее по частям.
Я хотел проиллюстрировать, как это работает, поэтому держал диапазон под контролем. Масштабируйте это к своим потребностям.
Мой массив данных D1: F10. Я просто заполнил его последовательными числами, потому что это было легко, и проиллюстрировал иллюстрацию. Формула не полагается на числа, являющиеся целыми числами или в определенном порядке. Я заменил несколько значений дубликатами некоторых совпадающих значений, чтобы убедиться, что формула работает.
В столбце A приведены несколько целевых значений: одно, где округляется до ближайшего совпадения, точное совпадение, другое, где округляется до ближайшего совпадения, и другое, где цель находится на равном расстоянии между двумя ближайшими совпадениями. Я выбрал целевые значения для совпадения в каждом столбце.
Столбец B содержит наиболее близкое совпадение в массиве для каждого целевого значения (решения).
Формула построена из компонентов, поэтому я объясню компоненты, которые показаны в этой таблице:
Целевые значения реплицируются в столбце A. В столбце B определяется минимальная разница между целевым значением и ближайшим значением в таблице. Формула в B16:
{=MIN(ABS($D$1:$F$10-A1))}
Это формула массива. Это компонент окончательной формулы, поэтому окончательную формулу необходимо вводить с помощью Ctrl Shift Enter.
Мы не знаем, в каком направлении находится различие, нужно ли нам прибавлять или вычитать, чтобы соответствовать ближайшему значению в таблице, поэтому мы пробуем оба. Чтобы найти соответствующее значение путем «округления вниз», он использует формулу в столбце C. Чтобы найти соответствующее значение путем «округления вверх», он использует формулу в столбце D. Один вычитает, а другой добавляет разницу:
=SUMPRODUCT(($D$1:$F$10=A1-B16)*$D$1:$F$10) =SUMPRODUCT(($D$1:$F$10=A1+B16)*$D$1:$F$10)
Если первые круглые скобки верны, они будут оценены 1, в противном случае это будет 0. SUMPRODUCT умножает это на соответствующее значение ячейки и суммирует результаты.
Но обратите внимание на некоторые странные результаты.
- Несколько целей возвращают ноль или не соответствуют. Это те случаи, когда совпадение найдено путем округления в другом направлении.
- Цель, находившаяся посередине между двумя ближайшими значениями, возвращает нижнее совпадающее значение, округленное вниз, и более высокое совпадающее значение, округленное вверх. Если вам важно, какой из них выбран, упорядочите компоненты формулы, чтобы было выбрано округление в предпочтительном направлении.
- Несколько значений в два раза больше, чем они должны быть. Это случаи, когда я продублировал ближайшее значение; формула добавляет в каждом.
Чтобы разобраться с этими случаями, мы посчитаем, сколько совпадений найдено. Это делается в столбцах E и F:
{=SUM($D$1:$F$10=A1-B16)} {=SUM($D$1:$F$10=A1+B16)}
Эти компоненты также являются формулами массива. Формула в столбце E оценивает, равно ли значение каждой ячейки цели минус разница, которая равна Истине или Ложь ( 1или 0), и суммирует эти значения. Формула в столбце F та же самая, за исключением того, что она сравнивает с целью плюс разница.
Мы можем использовать эти результаты двумя способами: для проверки отсутствия совпадений (нулевого результата), чтобы выбрать, какой результат использовать, и в качестве количества совпадений для корректировки завышенного значения нескольких совпадений на предыдущем шаге.
Обратите внимание, что, поскольку мы находим наиболее близкое совпадение, всегда будет «самое близкое», поэтому округление в большую или меньшую сторону не может оба вернуть ноль. Нам нужно только проверить один из результатов. Если это ноль, мы используем другой. Если оно не равно нулю, мы используем это значение, поскольку другое значение будет либо нулевым, тем же, либо другим действительным результатом, который можно выбрать в порядке выполнения теста.
Для выбранного действительного результата счет используется как делитель. Итак, формула для результата:
=IF(E16=0,D16/F16,C16/E16)
Так вот как это работает. Чтобы превратить это в единую консолидированную формулу, необходимо заменить ссылки на ячейки формулой в указанной ячейке. Вот как выглядит консолидированная формула (я добавлю интервалы и переводы строк, чтобы сделать ее более читабельной; вы захотите удалить их, если хотите скопировать и вставить формулу):
{=IF(SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))=0, SUMPRODUCT(($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1))), SUMPRODUCT(($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1))))}