Un colega me preguntó si había una manera de crear un gráfico de barras dinámico en Excel para tres ventas de productos.
Como suele ser el caso, había miles de filas de datos. No hay manera de que pudiera haber usado los gráficos incorporados de Excel, ya que le habría llevado años crear gráficos para cada conjunto de puntos de datos.
Esto me hizo pensar, y afortunadamente, el formato condicional vino al rescate. Fui capaz de crear rápidamente algo limpio que se ajustaba a las necesidades.
Glosario
Creación de un gráfico de barras apilado al 100% en Excel
Suponga que tiene datos de ventas de 12 meses para tres productos (P1, P2 y P3). Ahora quieres crear un gráfico de barras apilado al 100% en Excel para cada mes, con cada producto resaltado en un color diferente.
Algo como lo que se muestra a continuación:
Descargar el archivo de ejemplo
Cómo crear esto:
Primero, tienes que calcular el porcentaje de ruptura de cada producto para cada mes (¡Estaba tratando de hacer un gráfico 100% apilado, recuerda!).
Para ello, primero cree tres columnas auxiliares (cada una para P1, P2 y P3) para los 12 meses. Ahora simplemente calcula el valor % para cada producto. He usado la siguiente fórmula:
=(C4/SUMA($C4:$E4))*100)
Una vez que tengas estos datos en su lugar, vamos a sumergirnos de inmediato para hacer el gráfico apilado
- Seleccione 100 columnas y establezca su ancho de columna a 0.1.
- Selecciona estas 100 celdas en la primera fila de datos (K4:DF4) en este caso.
- En el cuadro de diálogo de la nueva regla de formato, haz clic en la opción ‘Usar una fórmula para determinar qué celdas formatear’.
- En la «Edición de la descripción de la regla» ponga la siguiente fórmula y establezca el formato en Azul (en la pestaña «Llenar»)
=COLUMNAS($K$4:K4)<=$G4
- Ahora de nuevo selecciona el mismo conjunto de celdas y ve a Inicio – Formato condicional – Administrar reglas . Haz clic en la pestaña Nueva regla y vuelve a la opción ‘Usar una fórmula para determinar qué celdas formatear’. Ahora pon la fórmula que se menciona a continuación y establece el formato en color verde.
=Y(COLUMNAS($K$4:K4);$G4,COLUMNAS($K$4:K4)=($G4+$H4))
- Y finalmente repita el mismo proceso y añada una tercera condición con la siguiente fórmula y establezca el formato en color naranja.
=Y(COLUMNAS($K$4:K4)>($G4+$H4),COLUMNAS($K$4:K4)<=100)
- Haz clic en Ok y obtendrás algo como se muestra a continuación:
Oculta las columnas de ayuda, y tendrás tu gráfico de barras dinámico 100% apilado listo a tu servicio.
Ahora es el momento de disfrutar de la gloria y sacar algo de tiempo para presumir de ello
Pruébalo tú mismo… Descarga el archivo de ejemplo de aquí 656q0-1.92 0.672-3.616t1.856-2.976 2.72-2.208 3.296-1.408 3. 616-0.448q2.816 0 5.248 1.184t3.936 3.456 1.504 5.12q0 1.728-0.32 3.36t-1.088 3.168-1.792 2.656-2.56 1.856-3.392 0.672q-1.216 0-2.4-0.576