Из функции ПРОСМОТР в ВПР
Я пытаюсь использовать функцию Excel VLOOKUP вместо LOOKUP. Я читал, что функция ВПР хорошо оптимизирована, но я не могу ее настроить. Любая помощь, пожалуйста?
Следующий возврат функции:
- 1, если первое условие выполнено>
- -1, если первое условие выполнено <
=LOOKUP(2,1/((E1:E9>E10)-(E1:E9<E10)))
Объем функции: просмотр значения E10, поиск в диапазоне E1: E9, начиная снизу вверх, если сначала появляется число> или <E10, возвращение +1 или -1
Пример 1
E1 = 6
E2 = 5
E3 = 5
E4 = 4
E5 = 6
E6 = 6
E7 = 5
E8 = 8
E9 = 8
If E10 = 8
Результат - -1, потому что первое найденное значение меньше E10 (начиная с E9 и выше).
If E10 = 7
Результат - 1, потому что первое найденное значение больше E10 (начиная с E9 и выше).
??
=VLOOKUP(2,1/((E1:E9>E10)-(E1:E9<E10));1)
или любую другую функцию вместо ПРОСМОТРА?
Ответы 2
Предыдущее предполагаемое решение работает правильно
=VLOOKUP(2,1/((E1:E9>E10)-(E1:E9<E10));1)
но формулу нужно вводить с:
CTRL+SHIFT+ENTER
Я пробовал использовать ПОИСКПОЗ, но мне кажется, что результат работает некорректно.
Есть другие идеи?
Возвращаясь к моей первой функции:
=LOOKUP(2,1/((E1:E9>E10)-(E1:E9<E10)))
Я придумал это, чтобы понять, как это работает, чтобы найти другие возможные решения.
- Сначала Excel преобразовал мое условие в ПРАВДА и ЛОЖНЫЙ
IF E10=8
=LOOKUP(2,1/((FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE)-(TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE)))
- Затем он преобразует ПРАВДА и ЛОЖНЫЙ в 1 и 0
=LOOKUP(2,1/((0,0,0,0,0,0,0,0,0)-(1,1,1,1,1,1,1,0,0)))
- Далее выполняет операцию "-", получая:
=LOOKUP(2,1/(-1,-1,-1,-1,-1,-1,-1,0,0))
- Затем он преобразуется в массивы:
=LOOKUP(2,{-1,-1,-1,-1,-1,-1,-1,#Div/0!,#Div/0!},ROW(E1:E9))
- Далее также конвертирует
ROW(E1:E9)в массивы, получая:
=LOOKUP(2,{-1,-1,-1,-1,-1,-1,-1,#Div/0!,#Div/0!},{1,2,3,4,5,6,7,8,9})
- В заключение просматривает от Нижний до Топ диапазона, ищет значение, близкое к
2, возвращает-1, потому чтоE7=5и меньше, чемE10=8
Итак, теперь, понимая, как работает Excel, можно использовать следующую функцию для получения массивов:
=INDEX(1/((E1:E9>E10)-(E1:E9<E10)),0)
в результате получается то же самое:
{-1,-1,-1,-1,-1,-1,-1,#Div/0!,#Div/0!}
и используя предыдущий результат в сочетании с ВПР, чтобы получить тот же результат функции ПОГЛЯДИ, но принять его без CTRL + SHIFT + ВВОД, что было моей целью
=VLOOKUP(2,INDEX(1/((E1:E9>E10)-(E1:E9<E10)),0),1)
Можете ли вы смоделировать некоторые данные и ожидаемый результат?