FORMATO CONDICIONAL Y REFERENCIAS EXCEL

Cómo usar el formato condicional en Excel

El formato condicional nos permite dar formato a las celdas cuyo contenido cumple con las reglas que hayamos definido. En esta ocasión mostraré cómo a través de múltiples reglas podemos identificar visualmente la información de nuestra hoja de Excel.

Supongamos una lista de vendedores donde cada uno de ellos tiene asociado el monto de ventas alcanzado en el último mes.

Figura 1

La Dirección General desea tener un reporte donde visualmente puedan identificar a aquellos vendedores que no alcanzaron la cuota mínima de ventas ($5,000) y también desean ver a los mejores 3 vendedores resaltados en un color diferente.

Adicionalmente a este par de reglas será necesario identificar a los vendedores que estén por arriba del promedio de ventas. De esta manera las reglas de formato condicional que serán implementadas serán las siguientes:
Color rojo: Cuota de venta menor a $5,000
Color azul: Vendedores por arriba del promedio
Color verde: Los mejores 3 vendedores

Todo comienza por seleccionar el rango de celdas que contiene los valores a ser evaluados que para este ejemplo es el rango B2:B21. Posteriormente podemos iniciar con la creación de las reglas de formato condicional.
Implementar las reglas de formato condicional

La primera regla de formato condicional la creamos pulsando el botón Formato condicional de la ficha Inicio, posteriormente la opción Resaltar reglas de celdas y finalmente la opción Es menor que.

Figura 2

La segunda regla la podemos implementar seleccionando de nuevo el botón Formato condicional y dentro de la opciónReglas superiores o inferiores debemos seleccionar la opción Por encima del promedio.

Figura 3

Ya que dentro de la lista de opciones de formato disponibles no hay una opción para el color de fondo azul, es necesario seleccionar la opción Formato personalizado para establecer el color de fondo adecuado.

Finalmente para crear la regla que especifique los 3 mejores vendedores vuelvo a pulsar el botón Formato condicional y dentro de la opción Reglas superiores o inferiores selecciono la opción 10 superiores, solo que en lugar de dejar la configuración para los 10 superiores la modifico para que sean solo los 3 superiores:

Figura 4

Finalmente elijo el color de relleno adecuado para que Excel aplique la regla de formato. Después de haber aplicado las tres reglas de formato condicional, la hoja de Excel se verá de la siguiente manera:

Figura 5

El formato condicional nos da las herramientas necesarias para aplicar un formato especial a un grupo de celdas de manera que podamos identificarlas fácilmente. Las celdas tomarán su formato de acuerdo a las reglas que hayan sido definidas.

 Referencias relativas y absolutas 

Las fórmulas de excel utilizan referencias de celda para incluir los valores de dichas celdas en los cálculos. Existen dos tipos principales de referencias: relativas y absolutas las cuales se comportan de una manera muy peculiar dentro de nuestras hojas de Excel y es importante aprender a distinguir entre ambos tipos.

De manera predetermina Excel utiliza referencias relativas al momento de insertar una referencia de celda en una fórmula. Las referencias relativas son actualizadas automáticamente por Excel al momento de copiar la fórmula a otra ubicación.

En el siguiente ejemplo tenemos una fórmula en la celda E6 que realiza la suma de varias celdas. La imagen tiene habilitado el rastreo de precedentes para poder visualizar las celdas involucradas en el cálculo.

Figura 6

Al momento de copiar la fórmula una celda hacia abajo, descubriremos que Excel modifica todas las referencias agregando una fila a todas ellas. Observa el cambio en las referencias después de haber copiado la fórmula:

Figura 7

Si en lugar de copiar la fórmula hacia abajo, la copiamos hacia la derecha, entonces Excel modificará las referencias de celda agregando una columna a cada una de ellas de la siguiente manera:

Figura 8

De esta manera comprobamos que Excel actualiza las referencias de celdas al momento de copiar una fórmula. Estas referencias se conocen como relativas porque serán modificadas por Excel en relación con la celda a donde se copie la fórmula.
Referencias absolutas en Excel

A diferencias de las referencias relativas, las referencias absolutas no se modifican aunque copiemos la fórmula a otras celdas. Para hacer que una referencia se convierta en absoluta utilizamos el símbolo de moneda ($) anteponiéndolo a la columna y fila de la referencia.

Tomando el mismo ejemplo anterior, voy a convertir la referencia a la celda A1 en una referencia absoluta. En la siguiente imagen puedes ver en la barra de fórmulas que de todas las referencias incluidas en la fórmula solo la referencia a la celda A1 es absoluta:

Figura 9

Puedes notar que es una referencia absoluta porque está escrita como $A$1. Hasta este momento no podemos notar diferencia alguna con el ejemplo anterior ya que la suma sigue siendo la misma, pero veremos una diferencia al momento de copiar la fórmula. Observa lo que sucede cuando copio la fórmula hacia abajo:

Figura 10

Toda las referencias has sido modificadas agregando una fila, pero la referencia a la celda A1 se mantenido fija porque es la única referencia absoluta en nuestra fórmula. Ahora observa lo que sucede al copiar la fórmula de la celda E6 a la derecha:

Figura 11

Todas las referencias se desplazan una columna a la derecha, pero la referencia a la celda A1 permanece constante aún después de haber copiado la fórmula. Las referencias absolutas serán de mucha ayuda cuando necesitamos indicar a Excel que considere siempre el valor de una misma celda sin importar que la fórmula sea copiada a otra ubicación.

Un atajo para convertir una referencia relativa en una referencia absoluta es colocar el cursor de la barra de fórmulas sobre la referencia que deseamos transformar y entonces pulsar la tecla F4 para que Excel introduzca los símbolos de moneda a un lado de la columna y de la fila.

Video 1.- Fuente Youtube VideoTutolandia