1

Итак, у меня есть файл Excel с несколькими таблицами, такими как на картинке и по ссылке ниже.

Скриншот таблицы

https://www.dropbox.com/s/967glpkyfjfb6iy/Test.xlsx?dl=0

Я пытаюсь ввести значение, находящееся в диапазоне D2:N21, и вернуть ему соответствующее значение ячейки в столбцах B & D и дату из строки 1 и отобразить их в ячейках B26, C26. И D26 соответственно.

Я пытался использовать формулы INDEX MATCH, но я не могу заставить его работать. Я предполагаю, что что-то упустил. Любая помощь будет принята с благодарностью.

2 ответа2

2

Это может быть сделано без использования VBA. Однако для этого требуются не только функции поиска / ссылки, поскольку они работают только в одном измерении (как и большинство других функций).

SUMPRODUCT() - это одна функция, которая работает с двумерными массивами (которые могут быть сгенерированы с помощью простого сравнения).


Решение требует только две следующие формулы:

Формула 1 Введена в B26 и введена ctrl / заполнена / скопирована в B26:C26:

=IF(ISERROR($D26),NA(),INDEX(B:B,SUMPRODUCT(MAX(($D$2:$N$21=$A26)*(ROW($D$2:$N$21))))))

Формула 2, введенная в D26:

=INDEX($1:$1,COLUMN($D$2:$N$21)-1+MATCH($A26,INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),1):INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),COLUMNS($D$2:$N$21)),0))

Предварительно подтвержденный вариант формулы 2:

=
INDEX(
  ($1:$1),
  COLUMN($D$2:$N$21)-1
  +MATCH(
    $A26,
    INDEX(
      $D$2:$N$21,
      MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
      1
    )
    :INDEX(
      $D$2:$N$21,
      MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
      COLUMNS($D$2:$N$21)
    ),
    0
  )
)

Обратите внимание, что если бы суммы в долларах в таблице были уникальными, можно было бы использовать менее сложную формулу. Поскольку вы не указали, что вы хотели бы делать при обнаружении дубликатов, я написал простейшую формулу, которая все еще работает, когда они обнаруживаются.

Так получается, что эта формула извлекает самое левое совпадающее значение в самой нижней строке, содержащей совпадение. Его можно изменить, чтобы он возвращал определенные конкретные альтернативные значения.

Следующая формула может использоваться для определения наличия дубликатов в таблице для значения, введенного в A26 , если требуется какое-либо уведомление / действие:

=SUMPRODUCT(MAX((D2:N21=A26)*(ROW(D2:N21))))<>SUMPRODUCT(SUM((D2:N21=A26)*(ROW(D2:N21))))


Для случая уникальных значений, функция MAX() в формуле 1, конечно, больше не требуется, и упрощенная формула 2 будет иметь вид:

=INDEX($1:$1,IFERROR(1/(1/(SUMPRODUCT(($D$2:$N$21=A26)*(COLUMN($D$2:$N$21))))),NA()))


Заметки:

  • Предварительно подтвержденная формула действительно работает, если введена.
  • Квадратные скобки ($1:$1) в предварительно проверенной версии необходимы для того, чтобы заставить $1:$1 оставаться на своей собственной строке.
  • Хотя я выбрал отображение ошибки #N/A если введенная сумма в долларах не может быть найдена, ее можно изменить на что-либо еще.
-1

Я думаю, что все функции поиска / ссылки поиска по столбцам или строкам. Я хотел бы использовать "пользовательские функции", которые являются функциями VBA, которые идут в модуле. Вам понадобится 3, один для B26, C26 и D26. Вот один для D26 (Дата). В D26 вы бы имели "= zDate (A26)". Модуль:

Option Explicit

Function zDate$(param$)
  Dim icol&, searchRange As Range, cellRange As Range
  Set searchRange = Range("D2:N21")
  Set cellRange = searchRange.Find(param, , xlValues, xlWhole)
  icol = cellRange.Column ' column of found cell
  zDate = Cells(1, icol) ' returns date
End Function

РЕДАКТИРОВАТЬ: Какой удивительный ответ от @robinCTS. Я собираюсь купить сопроводительную книгу. Я был тронут, чтобы создать

=INDEX(A1:N21,1,MAX(SUMPRODUCT((D2:N21=A26)*(COLUMN(D2:N21)) )))

но он не проверяет наличие ошибок, и я не понимаю формат "$ bbbnn.nn". В моем, A26 должен был бы включать "$" и пробелы. Спасибо

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