У меня есть две таблицы Excel, таблица A и таблица B. Таблица A содержит столбцы «Идентификатор клиента», «Почтовый индекс», «Имя клиента» и несколько других, которые имеют те же имена столбцов, что и столбцы в таблице B. Я хочу создать формулу, в идеале с использованием структурированных ссылок, чтобы порядок столбцов в таблице B не имел значения, которая ищет значение в таблице B, совпадающее с идентификатором клиента строки, в которой я нахожусь, в таблице A и значением столбца, в котором я нахожусь.
Например, если моя формула находится в третьем столбце таблицы A и в строке с идентификатором клиента «123», я хочу, чтобы она проверяла свое собственное имя столбца (Имя клиента) и искала значение для Имени клиента, где идентификатор клиента = «123» в таблице B.
Следующая формула отлично подходит для столбца «Имя клиента»:
=INDEX(TableB[Customer Name], MATCH([@[Customer Number]], TableB[Customer Number], 0))
но я хочу иметь возможность создать одну формулу, которая динамически заменяет часть [Имя клиента] на имя столбца, в котором я нахожусь, чтобы я мог просто скопировать ее во все столбцы. Я пробовал создать ссылку с помощью #Headers и с помощью indirect, но получаю ошибку Ref:
=INDIRECT("INDEX(TableB["&[#Headers]&"], MATCH([@[Customer Number]], TableB[Customer Number], 0))")
решение1
INDEX MATCHэто правильный подход, вам просто нужно быть осторожным в том, как вы его структурируете.
Таблица A слева. Таблица B справа. Мы будем использовать [Номер клиента] для поиска [Почтовый индекс].
Вот формула, которую нужно записать D2:
=INDEX(TableB,MATCH([@[Customer Number]],TableB[Customer Number],0),MATCH(D$1,TableB[#Headers],0))
INDEX, как вы знаете, возвращает значение ячейки на пересечении строки и столбца. MATCHвозвращает относительное положение значения в массиве.
Итак, для двух входных данных для INDEX, сначала мы находим номер строки в исходной таблице, который соответствует искомому значению, которое мы используем (номер клиента) (это традиционная первая половина INDEX MATCH), передавая MATCHвертикальный массив для поиска, затем мы находим номер столбца, который соответствует имени столбца, в котором мы находимся, передавая MATCHгоризонтальный массив, содержащий строку заголовка исходного столбца.
Вы заметите, что если вы добавите поле в исходную таблицу и измените заголовок в таблице формул, вы получите новые результаты, не меняя формулу.
..и что эта формула копирует как по горизонтали, так и по вертикали.
Два ключа здесь:
- зная, что MATCH будет учитываться как по горизонтали, так и по вертикали.
- вручную изменить структурированную ссылку, которую Excel предоставляет вам, на фактическую ссылку на ячейку в стиле R1C1, так что вы можете сделать индекс столбца этой ссылки на ячейку динамическим, а не фиксированным (т. е. C$1 в отличие от
TableA[[#Headers],[Post Code]], который жестко кодирует значение для поля, в котором вы находитесь, и, как таковой, не будет копироваться, хотя это будет работать, если вы вручную измените имя столбца, в котором находится формула поиска, и вам нужно будет иметь только одно поле поиска в целевой таблице).
Примечание.Я знаю, что этому вопросу уже больше трех лет, но это хороший вопрос и прекрасная демонстрация универсальности этой INDEX MATCHтехники.