☰

Функция ВПР не интерполирует все ячейки?

tag-icon • tag-icon microsoft-excel worksheet-function 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. Насколько мне известно, он должен просто искать ближайшую дату и выводить значение в матрице, но этого не происходит.

Есть идеи?

решение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чтобы она работала правильно. Она появится в строке формул, заключенная в фигурные скобки, если это сделано правильно.

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

Связанный контент