Excel: Mostrar la desviación de los valores en una lista: en forma de barra o por colores

Con listas largas de valores de datos, a menudo se quiere reconocer los valores atípicos. En Excel puede determinar fácilmente las desviaciones y visualizarlas como contenido.

Así puede reconocer rápidamente los valores que se desvían de la media en una lista

  1. Supongamos que ha introducido una lista de valores en las celdas B2 a B11, como el consumo diario de electricidad .

    Ejemplo de lista de valores

  2. Escriba la siguiente fórmula en el campo C2=B2-MEDIA(B$2:B$11). La celda muestra ahora cuánto se desvía el valor en B2 del valor medio de todos los datos.
  3. Copie la fórmula arrastrando el pequeño cuadrado de la parte inferior derecha de C2 hacia los campos C3 a C11. La lista ahora se parece a esto:

    Lista con desviaciones de la media

  4. Seleccione las celdas C2 a C11
  5. Ir a la cinta Inicie pulse sobre el símbolo Formato condicional, elegir en él Barra de datos y, por ejemplo, la variante Barras de datos azules.
  6. Ahora puedes ver cuánto se desvía el valor de la columna B mirando las barras de la columna C, lo que te ayuda a encontrar los valores atípicos en los datos.

    La columna C muestra ahora visualmente las desviaciones del valor medio.

Para el rango de celdas en MEDIA() los signos de dólar se utilizan porque, de lo contrario, Excel movería automáticamente el rango de valores hacia abajo al copiar la fórmula, pero no queremos que eso ocurra. Lea más sobre este tema aquí: Direccionamiento relativo y absoluto de las células: ¿cuál es la diferencia?.

¿Quieres saber cómo insertar unidades, como el «kWh» en la última captura de pantalla, en tus propias celdas? Para ello, utilice el formato de celda definido por el usuario con 0,0 "kWh" como contenido. Puede encontrar más información sobre esto en el consejo Cómo utilizar unidades en las celdas de Excel.

Publicidad

Así es como puede optimizar la visualización de las desviaciones para su caso

  • Con el procedimiento anterior, verá las desviaciones hacia abajo y hacia arriba en diferentes colores. Si no quiere hacer esto porque sólo le interesa la desviación en sí misma -como en el caso de las tolerancias de producción- entonces simplemente amplíe la fórmula de 2) por la cantidad de la desviación: =ABS(B2-MEDIA(B$2:B$11)). La pantalla tiene entonces el siguiente aspecto:

    De este modo, Excel sólo muestra los valores absolutos de la desviación.

  • Tal vez prefiera utilizar colores en lugar de barras para mostrar dónde se producen las mayores desviaciones. A continuación, utilice el Formato condicional del paso 5) no las bases de datos sino, por ejemplo escalas de color – escala de color rojo-amarillo-verde. Esto muestra desviaciones mínimas en verde y cambios en amarillo o rojo. Rojo, cuanto mayor sea la desviación.

    Este es el aspecto de la variante utilizando la escala de colores.

  • Si no desea relacionar la desviación con el valor medio de todos los valores, sino con un valor de referencia fijo, utilice este valor numérico en la fórmula de la columna C en lugar de la parte MEDIA(B$2:B$11).
  • A veces AVERAGE() no es la función que se ajusta al problema. Continúe leyendo aquí: Esta es la diferencia entre AVERAGE() y MEDIAN()
  • Si no le interesa tanto el valor numérico de la columna C, sino la anchura de la barra, basta con dar a las celdas C2 a C11 un color gris claro como fuente.