Engadindo formato condicional en Excel permítelle aplicar diferentes opcións de formato a unha cela ou a un intervalo de celas que cumpran as condicións específicas que configurou.
As opcións de formato só se aplican cando as celas seleccionadas cumpren estas condicións.
As opcións de formato que se poden aplicar inclúen os cambios de tipo de letra e fondo, estilos de letra, bordos das celas e engadindo formato de número a datos.
Desde Excel 2007, Excel ten varias opcións integradas para condicións comúnmente usadas, como atopar números que sexan maiores ou menores que un valor determinado ou que atopen números que están por enriba ou por baixo do valor medio .
Ademais destas opcións predefinidas, tamén é posible crear regras de formato condicional personalizadas usando fórmulas de Excel para probar as condicións especificadas polo usuario.
Aplicando varias regras
Pode aplicarse máis dunha regra aos mesmos datos para probar diferentes condicións. Por exemplo, os datos do orzamento poden ter as condicións establecidas que apliquen os cambios de formato cando se gastan determinados niveis (como o 50%, o 75% eo 100%) do orzamento total.
Nestas circunstancias, Excel determina primeiro se as diferentes regras conflúen e, se é así, o programa segue un conxunto de precedencia para determinar que regra de formato condicional se aplica aos datos.
Exemplo: atopar datos que superan o 25% e 50% aumenta co formato condicional
No seguinte exemplo, aplicaranse dúas regras de formato condicional personalizadas ao rango de celas B2 a B5.
- a primeira regra comproba se os datos das celas A2 a A5 son maiores que o valor correspondente en B2 a B5 en máis dun 25%.
- a segunda regra verifica se os mesmos datos en A2: A5 supera o valor correspondente en B2: B5 en máis do 50%.
Como se pode ver na imaxe de arriba, se algunha das anteriores condicións é verdadeira, cambiará a cor de fondo da cela ou as celas do rango B1: B4.
- para os datos onde a diferenza é superior ao 25%, a cor do fondo das celas cambiará a azul.
- se a diferenza é superior ao 50%, a cor do fondo das celas cambiará a vermello.
As regras utilizadas para realizar esta tarefa,
= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%ingresaranse usando a caixa de diálogo Regra de formateo de formato condicional.
Introducir os datos do titorial
- Introduza os datos nas celas A1 a C5 como se ve na imaxe de arriba
Nota: o Paso 3 do tutorial engadirá fórmulas ás celas C2: C4 que mostran a diferencia de porcentaxe exacta entre os valores das celas A2: A5 e B2: B5 para comprobar a precisión das regras de formato condicional.
Configurar as regras de formatación de Condtional
Como se mencionou, as regras de formato condicional que comproban as dúas condicións ingresaranse usando a caixa de diálogo da regra de Formato de condicional.
Configurando o formato condicional para atopar un aumento superior ao 25%
- Destaque as celas B2 a B5 na folla de cálculo.
- Fai clic na pestana Inicio da cinta.
- Fai clic na icona de formatado condicional na cinta para abrir o menú despregable.
- Escolla Nova regra para abrir a caixa de diálogo Regra de formato novo como se ve na imaxe de arriba.
- Na parte superior da caixa de diálogo, prema na última opción: use unha fórmula para determinar que celas formatar.
- Na parte inferior da caixa de diálogo, prema nos valores de Formato onde esta fórmula é verdadeira: liña.
- Escriba a fórmula : = (A2-B2) / A2> 25% no espazo proporcionado
- Fai clic no botón Formato para abrir o diálogo Formatar celas.
- Neste cadro de diálogo, prema na pestana Explique e elixa unha cor de recheo azul.
- Fai clic en Aceptar dúas veces para pechar os cadros de diálogo e volver á folla de cálculo.
- Neste punto, a cor de fondo das celas B3 e B5 debería ser azul.
Configurando o formato condicional para atopar un aumento superior ao 50%
- Con celas B2 a B5 aínda seleccionadas, repita os pasos 1 a 6 anteriores.
- Escriba a fórmula: = (A2-B2) / A2> 50% no espazo proporcionado.
- Fai clic no botón Formato para abrir o diálogo Formatar celas.
- Fai clic na pestana Expl. E escolle unha cor de enchido.
- Fai clic en Aceptar dúas veces para pechar os cadros de diálogo e volver á folla de cálculo .
- A cor de fondo da cela B3 tamén debe ser azul indicando que a porcentaxe de diferenza entre os números nas celas A3 e B3 é superior ao 25% pero inferior ou igual ao 50%.
- A cor de fondo da cela B5 debería cambiar a vermello indicando que a porcentaxe de diferenza entre os números das celas A5 e B5 é superior ao 50%.
Comprobando as regras do formato condicional
Calculando% diferenza
Para comprobar que as regras de formato condicional introducidas son correctas, podemos introducir fórmulas nas celas C2: C5 que calcularán a porcentaxe exacta entre os números nos intervalos A2: A5 e B2: B5.
- Faga clic na cela C2 para que sexa a célula activa.
- Escriba a fórmula = (A2-B2) / A2 e prema a tecla Enter no teclado.
- A resposta do 10% debería aparecer na cela C2, indicando que o número da cela A2 é un 10% maior que o número da cela B2.
- Pode ser necesario cambiar o formato na cela C2 para mostrar a resposta como porcentaxe.
- Utilice o identificador de recheo para copiar a fórmula da cela C2 ás celas C3 a C5.
- As respostas para as celas C3 a C5 deberían ser: 30%, 25% e 60%.
- As respostas nestas celas mostran que as regras de formato condicional creadas son correctas xa que a diferenza entre as celas A3 e B3 é superior ao 25% ea diferenza entre as celas A5 e B5 é superior ao 50%.
- A célula B4 non cambiou de cor porque a diferenza entre as celas A4 e B4 equivale ao 25% ea nosa regra de formato condicional especificou que se necesitaba unha porcentaxe superior ao 25% para que a cor de fondo cambie a azul.
Orde de precedencia para as regras de formato condicional
Aplicando regras de formato condicional conflitivas
Cando se aplican varias regras ao mesmo rango de datos, Excel primeiro determina se as regras conflúen.
As regras conflitivas son aquelas en que as opcións de formato escollidas para cada regra non se poden aplicar aos mesmos datos .
No exemplo empregado neste tutorial, o conflito de regras xa que ambas regras usan a mesma opción de formato: a de cambiar a cor da cela de fondo.
Na situación en que a segunda regra é verdadeira (a diferenza de valor é superior ao 50% entre dúas celas), a primeira regra (a diferencia de valor superior ao 25%) tamén é verdadeira.
Orde de precedencia de Excel
Dado que unha cela non pode ter tanto un fondo vermello como azul ao mesmo tempo, o Excel necesita saber que regra de formato condicional debería aplicar.
A regra que se aplica está determinada pola orde de precedência de Excel, que indica que a regra superior na lista no cadro de diálogo do Xestor de Normas de Formalización Condicional ten precedencia.
Como se mostra na imaxe de arriba, a segunda regra utilizada neste tutorial (= (A2-B2) / A2> 50%) é maior na lista e, polo tanto, ten precedencia sobre a primeira regra.
Como resultado, a cor de fondo da cela B5 cambia a vermello.
De xeito predeterminado, engádense novas regras á parte superior da lista e, polo tanto, teñen unha maior prioridade.
Para cambiar a orde de precedencia use os botóns de frecha arriba e abaixo no cadro de diálogo identificados na imaxe de arriba.
Aplicación de regras non conflitivas
Se dúas ou máis regras de formato condicional non entran en conflito ambos se aplican cando a condición que cada regra proba é verdadeira.
Se a primeira regra de formato condicional no noso exemplo (= (A2-B2) / A2> 25%) formateaba o intervalo de celas B2: B5 cun bordo azul en vez dunha cor de fondo azul, as dúas regras de formato condicional non entrarían en conflito ambos formatos poden ser aplicados sen interferir co outro.
Como resultado, a célula B5 tería un borde azul e unha cor de fondo vermello, xa que a diferenza entre os números nas celas A5 e B5 é maior que 25 e 50 por cento.
Formato condicional vs formateo regular
No caso de conflitos entre as regras de formato condicional e as opcións de formato aplicadas manualmente, a regra de formato condicional sempre ten prioridade e aplicarase en lugar das opcións de formato engadidas manualmente.
Se se aplicou inicialmente unha cor de fondo amarela ás celas B2 a B5 no exemplo, unha vez que se engadiron as regras de formato condicional, só as celas B2 e B4 quedarían amarelas.
Debido a que as regras de formato condicional aplicadas ás celas B3 e B5, as súas cores de fondo cambiarían de amarelo a azul e vermello respectivamente.