Estoy usando Microsoft Excel 2016. Y tengo 2 tablas en hojas diferentes.
sheet1.tbl1 {
key1,
key2,
val1,
fun1,
fun2
}
sheet2.tbl2 {
key1,
key2,
val1,
val2
}
Quiero aportar valores sheet1.tbl1mediante sheet2.tbl2el uso de fórmulas u otros métodos. Específicamente, unirse por key1, key2y traer val1, val2de sheet2.tbl2a fun1, fun2desde sheet1.tbl1respectivamente. Ambas claves son únicas en ambas tablas, por lo que la primera coincidencia será suficiente.
He probado la fórmula
=INDEX(rls, MATCH(tbl1[[key1]:[key2]], tbl2[key1]:[key2]], 0),3)
Pero no funciona.
Tampoco quiero usar extensiones, quiero lograrlo usando la funcionalidad integrada. Y preferiblemente usando la sintaxis de columnas de la tabla ( tbl1[[key1]:[key2]]).
Respuesta1
La dificultad está en la combinación de teclas en la segunda mesa. VLOOKUP()Por ejemplo, dificulta su uso . Pero esto no supone ningún problema particular para INDEX/MATCHo XLOOKUP().
Por ejemplo:
=XLOOKUP($A1:$A9&"|"&$B1:$B9, $H$20:$H$28&"|"&$I$20:$I$28, J20:J28,,0)
Combina las claves usando el operador de unión &, separándolas con un carácter inesperado que existe en los datos existentes como |. (Puede elegir un carácter y luego buscarlo en las claves para asegurarse de que no se utilice). Esto es para que no se produzcan duplicaciones inesperadas. Ejemplo: un par de claves es Addy ressmientras que otro es Ady dress. Con un carácter intermedio se obtiene Add|ressy Ad|dressen lugar de dos instancias de Address.
Hacer la combinación es sencillo para XLOOKUP()y MATCH()(la mitad INDEX/MATCHen la que lo haces). Ambos también pueden manejar fácilmente el uso de matrices para esas partes, por lo que puede crear una fórmula única.
En las versiones actuales, lo harán, SPILLlo que funciona muy bien. En versiones anteriores (que etiquetó con las de 2016), no lo harán, por lo que deberá usar cualquiera de {CSE}las entradas o copiar y pegar la columna según sea necesario.
Para las versiones más nuevas, XLOOKUP()tiene una fórmula simple y obvia (agradable para el mantenimiento). Para las versiones anteriores, INDEX/MATCHse sacrifica poco:
=INDEX($J$20:$J$28, MATCH($A1:$A9&"|"&$B1:$B9, $H$20:$H$28&"|"&$I$20:$I$28,,0))
Dado que necesita manejar ambos, ese es el que debe usar.
A ninguna fórmula le gusta un rango objetivo de dos columnas, por lo que (en estas fórmulas) J20:K28 no está disponible.
(Pero, naturalmente, ¡alguien puede conocer un buen método para ponerlo a disposición!)
Existe un buen método FILTER/FILTER, pero no puede usarlo con usuarios de Excel-2016, por lo que no le ayudará hoy. Sin embargo, podría ser una técnica útil en futuros libros de trabajo:
=FILTER(FILTER(H20:K28,SORT(A1:A9&"|"&B1:B9)=SORT(H20:H28&"|"&I20:I28)),{0,0,1,1})
El interior FILTER()elige los datos. Por supuesto, es casi seguro que las listas de claves no están en el mismo orden (o "=F12, =F13, =F14, etc. estaría cerca de funcionar, ¿eh?). Así que use SORT()en cada clave una matriz de combinación para colocarlas en el mismo orden. Si hay pares de claves en la segunda tabla que no están en la primera, deberá eliminarlos o elegir una ruta diferente.
El exterior FILTERentonces funciona de manera muy similar INDEX()a cuando se usa una constante de matriz para elegir las columnas (o filas) para generar (y podría usarlas INDEX()en su lugar, pero no tan fácilmente). FILTER()Usa la constante de matriz simple como "mostrar columna/no mostrar". "columna". Por lo tanto, solo generará las dos columnas que desea generar.
Tiene la ventaja de proporcionar una salida multidimensional que incluye SPILL-ingresar una fórmula en una celda y tendrá la salida completa.
Incluso podría usar FILTERXML()lo que tendrían los usuarios de 2016, pero aunque es un uso muy inteligente (especialmente porque el método construiría matrices de ambas columnas en un único XPATH) usually, en este caso sería simplemente un nivel muy básico de bajo nivel. Ejercicio de cálculo de números. Kludgy... "al máximo". Y podría tener problemas con la longitud de la cadena de la matriz interna.
Esto sin mencionar la ruta de la columna auxiliar (para las claves combinadas), o su prima, colocar las claves combinadas en rangos con nombre para que sean fácilmente accesibles.
Recomendaría el INDEX/MATCHmétodo para su combinación de usuarios.