☰

Resalte las diferencias entre dos hojas de cálculo de Excel

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting
Resalte las diferencias entre dos hojas de cálculo de Excel

Los datos de la hoja de cálculo A tienen varias columnas, pero solo necesito comparar:

Column A - order #
Column C - count
Column D - date

La hoja de cálculo B también tiene muchas columnas, pero necesito comparar:

Column A - order #
Column B - count
Column C - date

Necesito resaltar la columna A en la fila de ambas hojas de cálculo cuando los datos en la fila son idénticos solo para esas 3 columnas. En este ejemplo, las filas 6 y 14 tienen fechas diferentes y 7 y 15 tienen cantidades diferentes, por lo que no se resaltarían.

haga clic por ejemplo

Respuesta1

Este método se basa en algunas columnas auxiliares en su primera hoja de cálculo o, si lo prefiere, en una hoja separada.

MATCH() devolverá el número de fila a un valor dado en una matriz.

INDEX() busca este valor en otra matriz especificada

Entonces, en la fila 1 de la columna auxiliar (digamos la columna X), desea. =MATCH(A1,SheetName!A:A,0) Esto devolverá la fila en la otra hoja que contiene el número de pedido en A1.

Luego, en las siguientes 2 columnas, desea =INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1). Esto devolverá el recuento y la fecha de la otra hoja de esa fila.

Luego, en la última columna que quieras =And(Y1=C1,Z1=D1). Esto devolverá VERDADERO si todo sigue igual.

Luego use el formato condicional para resaltar si así lo desea.

Todo esto se puede condensar en una sola fórmula y usarse como condición de formato condicional si lo desea; además, vlookup funcionaría tan bien aquí como la coincidencia de índice. Te lo he dado todo separado porque creo que es más fácil seguir lo que está pasando y por qué funciona, pero te recomiendo que intentes condensarlo, como práctica.

Espero que esto haya ayudado.

Tenga en cuenta que esto no funcionará si el número de pedido está duplicado en la lista de búsqueda, ya que solo busca la primera aparición en la tabla.

Respuesta2

Puedes hacerlo, con formato condicional, usando "Usar una fórmula para determinar qué celdas formatear". Y en la versión corta, debe usar la siguiente fórmula en su primera tabla que se encuentra en la Hoja1 y la otra en la Hoja2, ambas comenzando desde A1:

=AND(MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)>0; INDEX(Sheet1!$A:$D;ROW();3)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2); INDEX(Sheet1!$A:$D;ROW();4)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);3))

Esta fórmula supone lo siguiente:

  • Ambas tablas están en dos hojas separadas, llamadas Hoja1 y Hoja2.
  • La Hoja1 contiene su primera tabla, donde las columnas A, C y D se comparan con su segunda tabla en las columnas A, B y C de la Hoja2.
  • en la hoja uno como área de formato condicional, seleccione $A:$A o $A2:$AXX (donde XX es un número lo suficientemente alto como para contener todos los valores); esto solo importa para tener los colores si la ecuación anterior es verdadera

Cuando no funciona como "esperado":

  • Si tiene varios números de pedido iguales en la segunda tabla, solo se buscará y comparará la primera coincidencia.
  • Al aplicar a la tabla Sheet2 no se devolverá el color correcto, es necesario cambiar algunos valores de referencia

Funciones utilizadas:

  • AND()- sólo devuelve verdadero si todos los parámetros son verdaderos
  • MATCH()- encuentra un valor en un rango seleccionado (columna en nuestro caso)
  • INDEX()- devuelve una referencia de celda de una matriz/rango, diciendo explícitamente los números de fila y columna
  • ROW()- sin ningún parámetro devuelve el número de fila actual

Ahora veamos algunos componentes:

  • INDEX(Sheet1!$A:$A;ROW();1)esto devuelve la referencia de celda comparada actual cuando se ejecuta el formato condicional
  • MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)Aquí buscamos una celda en la segunda hoja que coincida con nuestra celda actual. Si hay una coincidencia, el valor devuelto es superior a 0; de lo contrario, se proporciona una advertencia/error.
  • INDEX(Sheet1!$A:$D;ROW();3)- buscamos aquí el valor de la tercera columna (alias columna C) de la primera tabla (Hoja1).
  • INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2)- esto recupera la segunda columna de la tabla Hoja2. Ya teníamos la fila, donde se coloca el ID del pedido, esa ecuación está en el medio de esta función, como determinando la fila.
  • el tercer AND()parámetro es el mismo que los dos puntos anteriores, solo cambiando los números de columna.

Nota:

  • para encontrar la fila correcta, solo uso columnas: Sheet1!$A:$AySheet2!$A:$A
  • cuando necesito obtener otra columna, necesito extender la columna a una matriz/tabla cuya última columna es al menos la última columna a la que me referiré, en el caso de la hoja 1, la cuarta, alias columna DSheet1!$A:$D

Al saber lo anterior, deberá poder modificar la ecuación para que funcione también en la tabla Hoja2.

Respuesta3

Éste es agradable y fácil de hacer con el formato condicional de la siguiente manera.

Configuración

Para la fórmula de ejemplo siguiente, los datos están en las celdas Hoja1!A1:C3 y Hoja2!A1:C3.

Fórmula de formato condicional

Resalte el rango de celdas enHoja2, agregue formato condicional por fórmula e ingrese esta fórmula: =AND(Hoja1!$A1=$A1,Hoja1!$B1=$B1,Hoja1!$C1=$C1) ...y elija un color para resaltar sus resultados .

Es importante bloquear las columnas y no las filas (es decir, tener los signos de dólar antes de las letras pero no los números).

Con respecto a las referencias utilizadas en la fórmula de formato condicional, las referencias de celda deben ser las celdas superiores de las columnas de datos correspondientes. Entonces, si ambas columnas de números de pedido comienzan en A1, déjelo como A1 según lo anterior. Pero si en la primera pestaña el primer número de orden a considerar está en la fila 4 pero en la segunda pestaña está en la fila 7, modifica la fórmula a AND(Hoja1!$A4=A7...), etc.

¡Déjame saber si hay algo que necesite aclaración!

información relacionada