Hoja1

Question

Tengo una hoja de cálculo de Excel con 2 hojas de trabajo. La primera es sólo una fila de encabezado y una única columna de nombres de elementos. La segunda es una lista de grupos de elementos, con una fila de encabezado y un título en la columna más a la izquierda, siendo cada fila posterior un elemento u otro de la otra hoja:

Sheet1:            Sheet2:
+-------+--+--+    +-------+-------+-------+-------+-------+
| Item  |  |  |    | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+    +-------+-------+-------+-------+-------+
| Shirt |  |  |    | A     | Shirt | Hat   | Tie   |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Hat   |  |  |    | B     | Socks | Shirt | SHOES |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Socks |  |  |    | C     | Hat   | Socks |       |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Tie   |  |  |    | D     | Tie   | Tie   | Socks |
+-------+--+--+    +-------+-------+-------+-------+-------+
| ...   |  |  |
+-------+--+--+

Me gustaría formatear condicionalmente todas las celdas de "Hoja2" de modo que cualquier valor que no coincida con un valor en la primera columna de "Hoja1" esté marcado con un fondo rojo; los que sí lo hacen están marcados con un fondo verde. Entonces, todas las celdas de este ejemplo que comienzan en B2 serían verdes excepto el valor "ZAPATOS". El valor debajo no tiene nada ingresado, por lo que no tendrá ningún formato.

La regla de formato para el verde que he probado es:

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)

Para el rojo, más o menos lo mismo:

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)

Ambas reglas se "aplican" a un rango algo arbitrario (me gustaría que se aplicara a toda la hoja, menos a la fila/columna superior e izquierda):

=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35

Esto funciona parcialmente, pero los resultados son impredecibles. Algunos valores se resaltan como esperaba, pero solo en unas pocas filas, y otros no. Probablemente mis rangos estén fuera de control de alguna manera, pero ya no uso Excel tanto como antes. Alguien puede prestar ayuda?

¡Gracias!

Answer 1

Como afirma el Doktoro Reichard, usted desea utilizarFormato condicionalpara hacer esto. En este caso específico desea tener tres reglas:

1. Si la celda está en blanco, no cambies el fondo.
2. Si la celda coincide, haga que el fondo sea verde.
3. Si la celda no coincide, haga que el fondo sea rojo.

_{Disculpas, mi Excel es japonés. Es el día multilingüe.}

Para hacer esto, necesitamos 3 fórmulas que devolverán TRUEo FALSEpara cada una de estas condiciones. Asumiré que sus datos son los siguientes:

Hoja1

Hoja2

Regla 1

La siguiente fórmula devolverá independientemente de que la celda esté en blanco o no. he seleccionado

=ISBLANK(B2)

Tenga en cuenta que he seleccionado celdas B2:D5con referencias relativas. Esto aplicará la misma fórmula cambiando la referencia de celda para cada celda en el rango seleccionado. Establezca el color de fondo en blanco (o cualquiera que sea su preferencia) cuando esta condición sea verdadera.

Regla #2

La siguiente fórmula arrojará resultados si existe o no una coincidencia perfecta en la lista de la hoja 1:

=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))

Regla #3

La siguiente fórmula arrojará resultados independientemente de que no haya una coincidencia perfecta en la lista de la hoja 1:

=ISERROR(MATCH(B2,Sheet1!$A:$A,0))

Orden

La regla de arriba se ejecutará primero. Entonces, dado que todas las celdas en blanco no coincidirán, primero debe colocar la regla en blanco. El orden de los números 2 y 3 no importa (nunca se superpondrán).

Answer 2

Parece que lo que necesitas es formato condicional de celda.

Aquí hay unenlacedescribiendo qué es esto.

Mis fotografías son de una versión portuguesa de Excel 2003, pero la funcionalidad también debería estar disponible en Office 2010. De hecho, Office 2010 permite que exista formato condicional entre hojas, algo que 2003 no puede y, como tal, estoy haciendo todo. en una hoja.

Primero de la tabla:

Lo que quiere hacer es comparar un elemento de la segunda tabla con todos los elementos de la primera. Entonces, necesitas escribir una función como esta:

=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))

Lo que EXACT()hace es comparar dos cadenas de texto. Lo OR()que hace es convertirse Truesi alguna condición lógica en su interior lo es True.

Dicho esto, luego selecciona todas las celdas de la segunda tabla y luego presiona Formato condicional, como se muestra.

Conociendo Office 2010 y su nueva interfaz Ribbon, debería buscar esto en el panel Formato. Si mal no recuerdo, es un icono.

Al hacer clic en ese icono aparecerá una ventana similar a esta:

Allí, primero debes seleccionar que quieres una fórmula y luego pegar la fórmula que mencioné antes. Para que todas las celdas que verifican la condición sean verdes, simplemente modifique el formato. Para hacer el formato rojo, simplemente use NOT(OR(...)); esto devolverá la condición inversa a la que estableciste.

Para asegurarse de que no formatee las celdas que no contienen nada, cree una tercera condición en la que la fórmula sea ESBLANCO(E3) (siendo E3 la esquina superior izquierda).

Algo que verifiqué después de escribir el borrador inicial es la prioridad. Al menos en mi versión, la Condición 1 se verifica antes que la Condición 2 y así sucesivamente. Por lo tanto, debe ordenar las condiciones de manera que no interfieran entre sí. Entonces:

1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))

Por lo tanto, deberías tener una ventana como esta:

Intenta adaptar esto a tu situación. Si mal no recuerdo, no es tan diferente de lo que estoy mostrando. El resultado debería ser algo como esto: