VLOOKUP не интерполирует все ячейки?

Question

Моя формула VLOOKUP не интерполирует все ячейки

    AD           AE   AF
11-6-2014 15:20 90,4 #N/A
11-6-2014 15:21 89,1 #N/A
11-6-2014 15:22 90,4 Good
11-6-2014 15:23 89,1 Good

Колонка AF - это моя формула: (на голландском) = AE4- (VERT.ZOEKEN (AD4; $ AL $ 3: $ AM $ 39714; 2; WAAR) -1000) (пробный перевод на английском языке) = AE4- (VLOOKUP (AD4; $ AL $ 3: $ AM $ 39714; 2; True) -1000)

Это часть столбца матрицы:

        AL        AM
11-6-2014 15:22 1026,9
11-6-2014 15:23 1027,3
11-6-2014 15:24 1027,2
11-6-2014 15:25 1026,9

Мои данные (1026.9) в 11-6-2014 15:22 найдены. Но я хочу, чтобы это значение также было интерполировано для 11-6-2014 15:20 и 11-6-2014 15:21. Afaik он должен просто найти ближайшую дату и вывести значение в матрице, но это не так.

Есть идеи?

Answer 1

Именно так VLOOKUP предназначен для работы, когда для параметра Range Lookup установлено значение TRUE . Он вернет значение для ближайшего значения, которое меньше или равно значению поиска. Если никакое значение не соответствует этому критерию, возвращается ошибка.

Пара вариантов для вас:

1. Установите исключение для значений ниже нижней границы и позвольте VLOOKUP обрабатывать все остальное. Для этого просто оберните свою функцию в функцию IFERROR .

   =IFERROR(AE4-(VLOOKUP(AD4;$AL$3:$AM$39714;2;True)-1000);AE4-$AM$3)
   

   Это просто по умолчанию для всех производящих ошибку поисков первое значение в столбце AM .

2. Если вместо этого вы хотите вернуть данные за ближайшую дату, а не только за ближайшую, которая меньше или равна вашему значению поиска, вы можете использовать что-то отличное от VLOOKUP . Приведенная ниже формула массива вернет значение из AM для ближайшей даты. Середины между датами отображаются на более позднюю дату.

   =AE4-INDEX($AM$3:$AM$39714,MAX(IF(ABS($AL$3:$AL$39714-AD4)=MIN(ABS($AL$3:$AL$39714-AD4)),ROW($AL$3:$AL$39714)-2,-1)))
   

   Это формула массива, поэтому ее необходимо ввести, нажав Ctrl+Shift+Enter, чтобы работать правильно. Он будет отображаться в строке формул в фигурных скобках, если это сделано правильно.

   При использовании длинных массивов, подобных вашему (n = 39 712), формулы массивов могут быть медленными в вычислительном отношении и могут затормозить вашу электронную таблицу. Так что примите это во внимание при принятии решения, какой метод использовать.