Я использую Microsoft Excel 2016. У меня есть две таблицы на разных листах.
sheet1.tbl1 {
key1,
key2,
val1,
fun1,
fun2
}
sheet2.tbl2 {
key1,
key2,
val1,
val2
}
Я хочу перевести значения в из sheet1.tbl1с sheet2.tbl2помощью формулы или других методов. В частности, join by key1, key2и bring val1, val2from sheet2.tbl2в fun1, fun2из sheet1.tbl1соответственно. Ключи оба уникальны в обеих таблицах, поэтому первое совпадение подойдет.
Я пробовал формулу
=INDEX(rls, MATCH(tbl1[[key1]:[key2]], tbl2[key1]:[key2]], 0),3)
Но это не работает.
Я также не хочу использовать расширения, я хочу добиться этого с помощью встроенной функциональности. И желательно с помощью синтаксиса столбцов таблицы ( tbl1[[key1]:[key2]]).
решение1
VLOOKUP()Трудность заключается в комбинации клавиш во второй таблице. Например, это затрудняет использование . Но это не является особой проблемой для INDEX/MATCHили XLOOKUP().
Например:
=XLOOKUP($A1:$A9&"|"&$B1:$B9, $H$20:$H$28&"|"&$I$20:$I$28, J20:J28,,0)
Вы объединяете ключи с помощью оператора union &, разделяя их неожиданным символом, который существует в существующих данных, например |. (Вы можете выбрать символ, а затем выполнить его поиск в ключах, чтобы убедиться, что он не используется.) Это делается для того, чтобы не возникало неожиданных дубликатов. Пример: одна пара ключей — это Addи ress, а другая — Adи dress. С символом между ними получается Add|ressи , Ad|dressа не два экземпляра Address.
Объединение просто для XLOOKUP()and MATCH()(половина, INDEX/MATCHв которой вы это делаете). Both также могут легко обрабатывать массивы для этих частей, так что вы можете создать одну формулу.
В текущих версиях они будут, SPILLчто отлично работает. В более старых версиях (вы отметили 2016 год) их не будет, поэтому вам нужно будет использовать либо {CSE}ввод, либо копировать и вставлять вниз по столбцу по мере необходимости.
Для новых версий XLOOKUP()имеет простую и очевидную (хорошую для обслуживания) формулу. Для старых версий INDEX/MATCHжертвует немного:
=INDEX($J$20:$J$28, MATCH($A1:$A9&"|"&$B1:$B9, $H$20:$H$28&"|"&$I$20:$I$28,,0))
Учитывая, что вам нужно справиться с обоими вариантами, используйте именно этот.
Ни одна из формул не поддерживает целевой диапазон из двух столбцов, поэтому (в этих формулах) J20:K28 недоступен.
(Но, естественно, кто-то может знать хороший способ сделать его доступным!)
Есть хороший метод FILTER/FILTER, но вы не можете использовать его с пользователями Excel-2016, поэтому он вам сегодня не поможет. Хотя может быть полезным методом в будущих рабочих книгах:
=FILTER(FILTER(H20:K28,SORT(A1:A9&"|"&B1:B9)=SORT(H20:H28&"|"&I20:I28)),{0,0,1,1})
Внутренний FILTER()выбирает данные. Конечно, списки ключей почти наверняка не в том же порядке (или "=F12, =F13, =F14 и т. д. было бы близко к рабочему, а?). Поэтому используйте SORT()на каждом массиве объединения ключей, чтобы расположить их в том же порядке. Если во второй таблице есть пары ключей, которых нет в первой, вам нужно будет либо удалить их, либо выбрать другой путь.
Затем внешний метод FILTERработает во многом так же, как INDEX()при использовании константы массива для выбора столбцов (или строк) для вывода (и INDEX()вместо этого вы могли бы использовать, но не так легко). FILTER()использует простую константу массива как функцию «показывать столбец/не показывать столбец». Таким образом, вы выводите только два столбца, которые хотите вывести.
Он хорош тем, что обеспечивает многомерный вывод, включая SPILL-ing, поэтому одна формула в одной ячейке, и вы получаете весь вывод.
Вы даже можете использовать FILTERXML()то, что было бы у пользователей 2016 года, но хотя это очень хорошее и умное использование (особенно потому, что метод создаст оба массива столбцов в одном XPATH) usually, в этом случае это будет просто очень простое упражнение по низкоуровневому перемалыванию чисел. Туповато... "по максимуму". И могут возникнуть проблемы с внутренней длиной строки массива.
И это не говоря уже о вспомогательном маршруте столбцов (для комбинированных ключей) или его аналоге, помещающем комбинированные ключи в именованные диапазоны, чтобы к ним было легко обращаться.
Я бы рекомендовал этот INDEX/MATCHметод для вашего состава пользователей.