Comparar dos hojas en excel y extraer lo que es igual

Autor: Oscar Cronquist Artículo actualizado en Junio ​​01, 2021

Este artículo describe una fórmula de matriz que compara valores de dos columnas diferentes en dos hojas de trabajo dos veces y devuelve un valor si se cumplen los criterios.

sissey pregunta:

Hola Oscar,
Hay varias columnas en dos hojas de trabajo diferentes, una tiene más columnas que otra. Necesito comparar la columna F de la hoja de trabajo 1 y la columna E de la hoja de trabajo 2; si el valor coincide, compare la columna G de la hoja de trabajo 1 y la columna F de la hoja de trabajo 2; si el valor coincide, registre el valor en la columna N de la hoja de trabajo 1 de la columna M de la hoja de trabajo 2. Consulte el ejemplo a continuación. Hoja de trabajo 1:

Columna F Columna G Columna N

Código de artículo Código de artículo Código bancario +1 0 +2 0 +4 0 +8 0 +28 0 Hoja de trabajo2: Columna E Columna F Columna M Código de artículo Código de artículo Código bancario 1 0 60 2 0 165 4 0 60 8 0 250 8 2 33 28 0 15 28 1 16.5

¡Aprecio su ayuda de antemano!

Fórmula matricial en la celda N2, hoja de trabajo 1:

=MIN(SI((F2=Sheet2!$F$2:$F$8)*(Sheet1!G2=Sheet2!$G$2:$G$8), Sheet2!$N$2:$N$8, ""))

Volver

1.1 Cómo ingresar una fórmula de matriz

1. Copie y pegue la fórmula anterior en la celda N2.
2. Mantenga presionado CTRL + MAYÚS simultáneamente.
3. Presione Enter una vez.
4. Suelte todas las teclas.

La fórmula ahora está rodeada de llaves, como esta {= fórmula} si lo hiciste bien. Compruebe su barra de fórmulas y asegúrese de que tiene los corchetes.

Luego copia la celda N2 y péguelo en las celdas de abajo.

Volver

1.2 Explicando la fórmula matricial en la celda N2

Paso 1: compare la celda F2 (sheet1) con la columna F (sheet2)

El signo igual es un operador lógico que le permite comparar valores. También le permite comparar un valor con varios valores, esto devuelve una matriz de valores.

F2 = Hoja2! $ F $ 2: $ F $ 8

se convierte en

1={1;2;4;8;8;28;28}

y devoluciones

{VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}

VERDADERO y FALSO son valores booleanos.

Paso 2: compare la celda G2 (hoja1) con la columna G (hoja2)

G2 = Hoja2! $ G $ 2: $ G $ 8

se convierte en

0={0;0;0;0;2;0;1}

y devoluciones

{VERDADERO; VERDADERO; VERDADERO; VERDADERO; FALSO; VERDADERO; FALSO}

Paso 3: multiplica las matrices

Este paso multiplica ambas matrices para aplicar Y-logic significa que ambos valores booleanos deben ser VERDADERO para devolver VERDADERO. Ver todas las combinaciones posibles a continuación.

VERDADERO * VERDADERO = VERDADERO (1)

VERDADERO * FALSO = FALSO (0)

FALSO * VERDADERO = FALSO (0)

FALSO * FALSO = FALSO (0)

(F2=Sheet2!$F$2:$F$8)*(Sheet1!G2=Sheet2!$G$2:$G$8)

se convierte en

({VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}) * ({VERDADERO; VERDADERO; VERDADERO; VERDADERO; FALSO; VERDADERO; FALSO})

y devuelve {1; 0; 0; 0; 0; 0; 0}.

El cálculo devuelve el equivalente numérico a los valores booleanos, VERDADERO devuelve 1 y FALSO devuelve 0 (cero).

Paso 4 - Uso SI función para reemplazar los valores calculados con los valores de la hoja 2 de la columna N, Si VERDADERO (1)

Los Función IF devuelve un valor si la prueba lógica es VERDADERO y otro valor si la prueba lógica es FALSA.

SI(prueba lógica, [valor_si_verdadero], [valor_si_falso ])

SI((F2=Sheet2!$F$2:$F$8)*(Sheet1!G2=Sheet2!$G$2:$G$8), Sheet2!$N$2:$N$8, "")

se convierte en

SI({1;0;0;0;0;0;0}, Sheet2!$N$2:$N$8, "")

se convierte en

SI({1; 0; 0; 0; 0; 0; 0}, {60; 165; 60; 250; 33; 15; 16.5}, "")

y devuelve {60; ""; ""; ""; ""; ""; ""}.

Paso 5: calcula el número más pequeño de la matriz

Los Función MIN devuelve el número más pequeño en una matriz o rango de celdas, ignora los valores lógicos y de texto

MIN(SI((F2=Sheet2!$F$2:$F$8)*(Sheet1!G2=Sheet2!$G$2:$G$8), Sheet2!$N$2:$N$8, ""))

se convierte en

MIN({60;"";"";"";"";"";""})

y devuelve 60.

Volver

2. Compare dos columnas en diferentes hojas de trabajo (Excel 2016)

fórmula de excel 2016 en la celda N2:

=MIN.SI.CONJUNTO(Sheet2!$N$2:$N$8, Sheet2!$F$2:$F$8, F2, Sheet2!$G$2:$G$8, G2)

Volver

Explicando formula en celula N2

Paso 1 - Configuración MIN.SI.CONJUNTO función

Los Función MINIFS calcula el valor más pequeño en función de un conjunto de criterios determinado.

MIN.SI.CONJUNTO(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

MIN.SI.CONJUNTO(Hoja2!$N$2:$N$8, Sheet2!$F$2:$F$8, F2, Sheet2!$G$2:$G$8, G2)

Paso 2 - Evaluar MIN.SI.CONJUNTO función

MIN.SI.CONJUNTO(Hoja2!$N$2:$N$8, Sheet2!$F$2:$F$8, F2, Sheet2!$G$2:$G$8, G2)

se convierte en

MIN.SI.CONJUNTO({60;165;60;250;33;15;16.5},{1;2;4;8;8;28;28},1,{0;0;0;0; 2;0; 1},0)

y devuelve 60. La única posición relativa que cumple con ambos criterios es la primera, contiene el número 60.

Volver

Volver

Últimos artículos actualizados.

Más de 300 funciones de Excel con información detallada que incluye sintaxis, argumentos, valores devueltos y ejemplos para la mayoría de las funciones utilizadas en las fórmulas de Excel.

Más de 1300 fórmulas organizadas en subcategorías.

Tablas de Excel simplifica su trabajo con datos, agregando o eliminando datos, filtrando, totalizando, ordenando, mejorando la legibilidad usando formato de celda, referencias de celda, fórmulas y más.

Le permite filtrar datos según el valor seleccionado, un texto determinado u otros criterios. También le permite filtrar datos existentes o mover valores filtrados a una nueva ubicación.

Le permite controlar lo que un usuario puede escribir en una celda. Le permite especificar condiciones y mostrar un mensaje personalizado si los datos ingresados ​​no son válidos.

Permite al usuario trabajar de manera más eficiente al mostrar una lista de la que el usuario puede seleccionar un valor. Esto le permite controlar lo que se muestra en la lista y es más rápido que escribir en una celda.

Le permite nombrar una o más celdas, esto hace que sea más fácil encontrar celdas usando el cuadro Nombre, leer y comprender fórmulas que contienen nombres en lugar de referencias de celdas.

Excel Solver es un complemento gratuito que utiliza celdas objetivas, restricciones basadas en fórmulas en una hoja de trabajo para realizar análisis hipotéticos y otros problemas de decisión como permutaciones y combinaciones.

Una función de Excel que le permite visualizar datos en un gráfico.

Formatee las celdas o los valores de las celdas en función de una condición o criterio, hay varias herramientas de formato condicional integradas que puede usar o use una fórmula de formato condicional personalizada.

Le permite resumir rápidamente grandes cantidades de datos de una manera muy fácil de usar. Esta poderosa función de Excel le permite analizar, organizar y categorizar datos importantes de manera eficiente.

VBA significa Visual Basic para aplicaciones y es un lenguaje de programación de computadoras desarrollado por Microsoft, le permite automatizar tareas que consumen mucho tiempo y crear funciones personalizadas.

Un programa o subrutina integrado en VBA que cualquiera puede crear. Utilice la grabadora de macros para crear rápidamente sus propias macros de VBA.

UDF significa Funciones definidas por el usuario y son funciones personalizadas que cualquiera puede crear.

Una lista de todos los artículos publicados.