2

У меня есть массив таблиц $E$14:$G$851 . Столбец E просто содержит уникальные идентификаторы, скажем, a/b/c/d и т.д., Столбец F содержит сотни чисел, все меньше или равны 20, а столбец G содержит значения, которые мне нужно вернуть.

Мне нужна формула, которая будет искать уникальный идентификатор в столбце E, а затем искать в столбце ВВЕРХ F и находить мне ПЕРВОЕ значение, которое МЕНЬШЕ, чем результат столбца F, и использовать этот результат для возврата соседнего значения в столбце G.

Так что, если бы я искал "g", формула тогда искала бы в столбце F UP первое число МЕНЬШЕ 16. Это будет "d" с 12. формула будет затем возвращать 25010 из столбца G.

пример

Я искал ответ на этот вопрос в течение нескольких недель и не нашел примеров превосходства в поиске массива снизу вверх. Я бы предпочел не менять макет данных (например, перелистывать его).

|источник

2 ответа2

1

Вы можете получить его с помощью следующей формулы массива (нажмите CTRL+SHIFT+ENTER после ввода в ячейку:

=INDEX(C:C,MAX(IF(OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(B1,0,0,MATCH(E1,A:A,0)-1)),"")))

Как это устроено:

  • OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1) - получает ссылку на столбец B по поисковому идентификатору
  • IF(OFFSET(...)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(...),"") - возвращает массив, содержащий номер строки для строк, значение которых в B меньше строки искал и пуст для других
  • MAX(IF(...)) - выбрать максимальное значение (последняя строка со значением в критериях полного заполнения B)
  • =INDEX(C:C,MAX(...)) - выбрать ячейку в столбце C

|источник
0

В этом примере столбец с результатом - G, но какая строка правильная? Если мы сможем найти строку, то INDIRECT("G"&row) получит результат.

рабочий лист

Решение состоит в том, чтобы добавить столбец указателей в качестве столбца H. Каждый указатель является строкой для INDIRECT и VLOOKUP может найти его. Ячейка C2 имеет формулу.

=INDIRECT("G"&VLOOKUP(B2,$E$1:$H$9,4,FALSE))

Так что теперь поиск в обратном направлении сводится к VLOOKUP для указателя на результат. Как создать столбец H? Я еще не нашел способ с помощью формул Excel, но вот несколько идей.

Если столбец F редко изменяется, то все, что нужно, - это несколько строк perl или python с копией и вставкой. Я использовал Perl. Если столбец F обновляется чаще, VBA может работать. В качестве альтернативы, Perl может читать данные из основного файла Excel и генерировать вторую рабочую книгу для использования в качестве внешнего источника.

Указатели избегают необходимости переворачивать данные или изменять расположение данных. Сложный обратный поиск выполняется одноразово с помощью Perl или VBA, а VLOOKUP быстро, просто и легко тестируется.

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .