Este tutorial demostrará cómo crear gráficos de Excel con formato condicional en todas las versiones de Excel: 2007, 2010, 2013, 2016 y 2022.
El formato condicional es la práctica de asignar formato personalizado a las celdas de Excel (color, fuente, etc.) según los criterios (condiciones) especificados. La función ayuda a analizar datos, encontrar valores estadísticamente significativos e identificar patrones dentro de un conjunto de datos determinado.
Pero, lamentablemente, Excel no ofrece ninguna herramienta integrada para aplicar formato condicional a los gráficos de Excel.
Sin embargo, con una gota de magia de hoja de cálculo, ningún problema es insuperable. En este tutorial, aprenderá a crear un gráfico dinámico con formato condicional en el que los puntos de datos trazados se formatean automáticamente en función de sus respectivos valores reales.
Como ejemplo, eche un vistazo a este gráfico de columnas, que desglosa el desempeño de un gerente de ventas durante un período de ocho meses. Observe cómo el color de la columna se ajusta automáticamente según su valor subyacente:
Empezando
Considere este conjunto de datos de muestra que contiene datos de rendimiento de ventas:
Antes de comenzar, mueva su conjunto de datos para que tenga tres filas vacías sobre la tabla con sus datos sin procesar. Las filas se utilizarán para establecer reglas de formato condicional.
Paso # 1: Preparar los datos del gráfico.
Para que las cosas funcionen, debe agregar algunos datos de gráficos adicionales a la ecuación. Para su comodidad, eche un vistazo a cómo deberían verse los datos de su gráfico al final de la fase de preparación:
Nuestro primer paso es definir los valores límite (condiciones) que deben dividir los valores reales en varias categorías, lo que hace posible representarlos como series de datos independientes.
En la captura de pantalla anterior, por ejemplo, puede ver que las cifras de ventas para un mes determinado deben estar dentro del rango de $ 90,000 a $ 150,000 para ser consideradas satisfactorias.
Para crear las reglas, use las tres filas vacías sobre el conjunto de datos:
- Fila 1 (Etiqueta): Utilice estas celdas para etiquetar las reglas de formato.
- Fila 2 (valor mínimo): Los valores de esta fila definen el límite inferior para cada condición.
- Fila 3 (valor máximo): Asimismo, estos valores establecen los límites superiores.
Con este método, puede crear tantas reglas de formato condicional como desee, solo asegúrese de que los rangos de valores no se superpongan.
A medida que avanza poco a poco en la configuración de su gráfico, diseñe elementos de leyenda de gráfico personalizados que proporcionen contexto para el gráfico. Ingrese esta fórmula en C4 y copiarlo en D4 arrastrando el controlador de relleno:
| 1 | = C1 & ": desde" & TEXT (C2, "$ #, ##") & "a" & TEXT (C3, "$ #, ##") |
La fórmula crea una etiqueta dinámica de aspecto prolijo basada en las reglas de formato condicional establecidas anteriormente. La función TEXTO da formato a los valores como moneda. Pero si su tipo de datos difiere, aplique esta fórmula en su lugar:
| 1 | = C1 & ": desde" & TEXT (C2, "#, ##") & "a" & TEXT (C3, "#, ##") |
O este cuando trabajas con porcentajes:
| 1 | = C1 & ": desde" & TEXT (C2, "#%") & "a" & TEXT (C3, "#%") |
Finalmente, encuentre los valores del gráfico. Escriba la siguiente fórmula en C5, cópialo en C12 y a través de las celdas coincidentes en columna D:
| 1 | = SI (Y (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
La fórmula compara un valor real dado en columna B contra los valores límite especificados con la ayuda de las funciones IF y AND. Si el valor cae dentro del rango, se copia en esta columna. De lo contrario, la fórmula devuelve el error # N / A para evitar que se grafique el mismo valor varias veces.
Paso 2: configura un gráfico de columnas.
Una vez recopilados todos los datos del gráfico, configure un gráfico de columnas simple o un gráfico de barras como alternativa:
- Resalte todos los datos del gráfico, excepto las columnas que contienen los valores reales y las reglas, manteniendo presionada la tecla control llave (A4: A12 y C4: D12).
- Ve a la Insertar pestaña.
- Seleccione "Insertar columna o gráfico de barras.”
- Escoger "Columna agrupada / Barra agrupada.”
Como resultado, terminará con un gráfico con puntos de datos trazados según las reglas de formato condicional, tal como se prometió:
Paso # 3: Modifique los valores de Superposición y Ancho de espacio.
Antes de cerrar este tutorial, solucione el problema de las columnas fuera de lugar causado por los valores vacíos invisibles (los errores # N / A) trazados en el gráfico.
Haga clic con el botón derecho en cualquiera de las columnas y seleccione "Dar formato a la serie de datos”En el menú contextual que aparece.
En el panel de tareas, cambie la posición y el ancho de las columnas:
- Cambie al Opciones de serie pestaña.
- Cambio "Superposición de series" para "100%.”
- Selecciona el Anchura de rendija para "60%.”
Paso # 4: Ajusta el esquema de color.
Por fin, dale los toques finales. Sin cerrar el Dar formato a la serie de datos panel de tareas, modifique el esquema de color del gráfico:
- Ve a la Rellenar línea pestaña.
- Debajo "Llenar," escoger "Relleno sólido.”
- Haga clic en el "Color de relleno"Icono y elegir verde de la paleta de colores.
Una vez allí, cambie el color de la otra serie de datos, cambie el título del gráfico y su gráfico completamente dinámico con formato condicional estará listo para funcionar.
