Bien, publiqué este Desafío de Gráficos de Excel el viernes. ¿Cómo te fue? Estoy seguro de que tu solución es mejor que la mía. Este fue el desafío de una pregunta del Foro de Excel:

«Cómo eliminar valores atípicos en el gráfico

Al ver algunos gráficos, a veces necesito ignorar algunos valores atípicos. Me gustaría saber si es posible hacer clic en los datos atípicos o en la x correspondiente en el eje x y el gráfico se actualizará sin estos datos atípicos. Sé que puedo ir al conjunto de datos y eliminar los valores atípicos, pero quiero simplificar haciéndolo en el gráfico. Muchas gracias.»

Aquí está la imagen que publicó este usuario:

El gráfico tiene 2 puntos de datos periféricos. 1 el 4 de enero y 1 el 7 de enero. El usuario de Excel eliminó manualmente los puntos de datos y luego verá el gráfico final en la parte inferior de la imagen.

También necesitamos agregar una línea de tendencia con una fórmula de línea de tendencia y un valor R, pero esto puede ser un paso manual. Los datos van del 1 al 10 de enero del año 2013. Así que vamos con estas suposiciones.

Entonces, ¿cómo podemos crear el gráfico final eliminando los puntos de datos del 4 y el 7 de enero sin eliminar manualmente esos puntos de datos? ¡Manos a la obra! A continuación encontrará un desglose rápido de cómo creé mi solución. A continuación se muestra un tutorial detallado paso a paso de esta solución de Excel. A continuación, verá un video de demostración de este consejo de Excel. Por último, a continuación encontrará una copia de mi hoja de cálculo que puede descargar para planificar con la técnica de datos y gráficos.

En primer lugar, permítanme mostrarles cómo otro usuario abordó este problema. Pete tenía una manera ingeniosa de solucionar este problema. Toma los puntos de datos y crea una fórmula basada en la pendiente de la línea. Aquí está su solución en sus propias palabras:

De Pete: «Estaba jugando con su nuevo desafío del viernes, y se me ocurrió un giro diferente en los resultados. Mi gráfico muestra los datos originales, y los nuevos datos con los periféricos eliminados. Tendrá que desbloquear la hoja para ver las fórmulas.

Básicamente, tomé los datos originales y usé la función de PENDIENTE para encontrar la pendiente de los datos, y luego la comparé con una línea teórica basada en el valor X utilizando la fórmula algebraica para líneas Y=MX+B. Con esta fórmula y la pendiente calculada, pude resolver para B. Ahora utilicé la fórmula y IF para devolver un error #N/A si el valor real y el valor teórico diferían en un % dado elegido por la celda desplegable de validación de datos. De esta manera, el usuario puede elegir en qué porcentaje eliminar los outlyers. Tuve que incluir 500% y 100% para permitir al usuario mostrar los datos originales, ya que el punto de datos «2» está 710% lejos del valor teórico calculado.»

Echa un vistazo a su solución aquí: Petes Outlyer Solution

Ahora aquí está mi solución básica para crear una línea de Excel que no muestre valores atípicos. Vuelve mañana para ver mi solución avanzada.

El Desglose

1) Crear Rango de Datos de Gráfico

2) Designar una Celda como el Nivel de Tolerancia de Valor Atípico

3) Crear Fórmula de Valor Atípico para el Rango de Datos de gráfico

4) Crear Gráfico de Valor Atípico en Excel

5) Agregar Línea de Tendencia al Gráfico, Fórmula de Línea de Tendencia y R-Cuadrado Valor

6) Suavice la línea y limpie la basura del gráfico

Paso a paso

1) Cree un Rango de datos del gráfico

Bien, vamos a crear nuestro rango de datos original en las celdas A1:B11. Ahora vamos a crear un gráfico de líneas rápidas en Excel con marcadores para ver cómo se ve. Se ve casi exactamente como el primer gráfico que vio de la pregunta original.

La única diferencia es que nuestra línea no está suavizada. El último paso de este tutorial le mostrará cómo realizar este cambio. Pero así es como se vería el gráfico original con Líneas Suavizadas:

2) Designar una celda como el Nivel de Tolerancia de valor atípico

Bien, mi solución utiliza una celda de hoja de cálculo para designar cuál es la tolerancia para un valor atípico. Así que he designado la celda D3 en la hoja de cálculo como mi nivel de tolerancia. Al observar los datos, parece que un punto de datos se designa como un valor atípico en el ejemplo del usuario de Excel cuando los puntos difieren aproximadamente 7 unidades.

Así que he puesto un valor de 7 en la celda D3:

Usaremos la celda D3 en el siguiente paso cuando creemos nuestra fórmula de valor atípico.

3) Cree una fórmula de valor atípico para el Rango de datos del gráfico

Primero, pongamos nuestras fechas en la celda E2. Esto es tan simple como poner un =A2 en la celda E2.

Bien, este es el paso que hace que todo funcione. Lo que necesitamos hacer es comparar el punto de datos actual con los puntos de datos anteriores y inferiores al actual. La comparación verá si el punto de datos actual está fuera del nivel de tolerancia establecido en la celda D3. Esencialmente, vamos a restar el punto de datos actual del punto de datos una celda arriba y ver si la diferencia es mayor que la tolerancia en la celda D3. Luego repita este paso para la celda debajo del punto de datos actual. Si ambos puntos de datos son mayores que el nivel de tolerancia, pondremos un NA() en la celda actual. Si ambos valores no están fuera de tolerancia, pondremos el valor del punto de datos actual en esta celda. Ahora, debido a que la diferencia podría ser negativa, necesitamos envolver esta resta en una función de valor absoluto. Así que en la celda F2 pongamos esta fórmula:

=IF(Y(ABS(B2-B1)>D D 3 3,ABS(B2-B3)>D D 3 3),NA(),B2)

Aquí hay un desglose de la fórmula:

Primero vamos a comenzar con una fórmula IF, así que ve a la celda F2 y escribe esto:

=IF(

porque necesitamos comparar un valor y luego, en función de ese valor, pondremos uno de los 2 valores en esta celda

Ahora, como describí anteriormente, necesita comparar los datos actuales apunte con la celda de arriba y la de abajo. Y, si ambas comparaciones están fuera de tolerancia, entonces no queremos mostrar el punto de datos. Por lo tanto, ya que estamos tratando con una comparación de «AMBOS», deberíamos usar la función AND, así que escribamos lo siguiente:

=IF(Y(

Ahora, como dije anteriormente, a veces restar los puntos de datos hará que el valor final sea Negativo. Y un valor negativo SIEMPRE estará por debajo de nuestra tolerancia, por lo que debemos asegurarnos de que nuestra diferencia esté siempre en unidades positivas, ya que nuestro nivel de tolerancia está en unidades positivas. Para hacer algo siempre positivo, necesitamos envolver nuestra comparación en una función de Valor absoluto, así que escribamos la función ABS siguiente:

=IF(Y(ABS(

Ahora hagamos nuestro 1er valor de comparación. Hacemos esto simplemente restando el valor actual con el valor por encima del punto de datos actual y vemos si es mayor que nuestro nivel de tolerancia en la celda D3. Por lo que escriba en:

=SI(Y(ABS(B2-B1)>$D$3

tenga en cuenta que hice la D3 y la referencia absoluta. Si no sabes lo que es una Referencia Absoluta, echa un vistazo a esta publicación:

Refiriéndose a Rangos en Fórmulas para sus Plantillas de Panel de Excel

Que es la primera parte de nuestra función AND y la función and está separada por una coma, luego colocarás la siguiente comparación. Así que vamos a escribir la siguiente comparación después de una coma. La siguiente comparación es comparar nuestro punto de datos actual con el siguiente punto de datos. Así que vamos a escribir eso:

=IF(AND(ABS(B2-B1)>D D 3 3,ABS(B2-B3)>D D 3 3

Ya que esta es nuestra última comparación de la que debemos preocuparnos, puede finalizar la función AND con un paréntesis derecho. Ahora podemos poner una coma y determinar qué hacer si ambos criterios son verdaderos. En nuestro caso, si ambas diferencias de los puntos de datos son mayores que la tolerancia, entonces necesitamos poner una función NA() allí. Así que vamos a escribir ese valor SI ES VERDADERO:

=IF(Y(ABS(B2-B1)>D D 3 3,ABS(B2-B3)>D D 3 3),NA()

Si desea obtener más información sobre la función NA (), consulte esta publicación:

Cómo ocultar un segmento de gráfico circular cero o Sección de gráfico de columnas apiladas

Ahora estamos listos para el valor final si la función AND NO es VERDADERA. Así que pon una coma y escribe el valor IF FALSE. En este caso, queremos poner el valor de punto de datos real de nuestra celda de datos B2. Por lo tanto,escriba una coma y luego B2)

=IF(Y(ABS(B2-B1)>D D 3 3,ABS(B2-B3)>D D 3 3), NA (), B2)

Ahora presione entrar y su valor en la celda F2 debería tener este aspecto:

Ahora que tenemos nuestras fórmulas en su lugar, podemos copiarlas hasta nuestro último punto de datos en las columnas A y B. Copie esta fórmula y se verá así:

Observe que los puntos de datos para 1/4 y 1/7 ahora están mostrando un valor de #N/A. Y los otros valores son los mismos que se ven en nuestro conjunto de datos original en la Columna B. ¡Eso es MARAVILLOSO! Qué buen consejo de excel para deshacerse de los valores atípicos de nuestra tendencia.

Ahora estamos listos para construir nuestro gráfico de valores atípicos de Excel.

4) Cree un gráfico de valores atípicos en Excel

Así que cree nuestro gráfico resaltando el rango de celdas de E1: F11. Luego vaya a la Cinta de Inserción y seleccione un Gráfico de líneas con marcadores desde el botón de línea:

Su gráfico ahora debería tener este aspecto:

Se ve muy diferente a la que tiene los valores atípicos:

5) Agregue Línea de tendencia al Gráfico, Fórmula de Línea de tendencia y Valor R Cuadrado

Ahora que tenemos nuestro gráfico, solo necesitamos algunas últimas cosas. Agreguemos una línea de tendencia a nuestro gráfico. Para hacer esto, haga clic en el gráfico y luego haga clic en la línea del gráfico. Desde allí, vaya a la Cinta de diseño y elija el botón Línea de tendencia y, a continuación, elija Línea de tendencia lineal:

A continuación, elija «Lineal» y «Mostrar ecuación en el gráfico» y «Mostrar valor R cuadrado en el gráfico» en el cuadro de diálogo Formato de línea de tendencia:

Su gráfico ahora se verá así:

6) Suavice la línea y limpie la basura del gráfico

Ahora todo lo que necesitamos hacer es limpiar el gráfico y estamos listos! Primero seleccione su gráfico y luego seleccione la leyenda y presione la tecla eliminar. Su gráfico ahora debería tener este aspecto:

Ahora necesitamos crear un gráfico de líneas suavizado. Para ello, seleccione el gráfico y, a continuación, la línea del gráfico. Desde allí, presione CTRL + 1 para abrir el cuadro de diálogo Formato de serie. Desde allí, seleccione los Estilos de línea en el menú de la izquierda y elija Línea suavizada de las opciones de estilo de línea:

Su gráfico final ahora debería tener este aspecto:

No hay mucha diferencia, pero cuando sus datos tienen cambios mayores, se verán aún más suaves

.

Ahora vamos a compararlo con la solicitud original. Se ve casi exactamente como lo que queríamos. Sin embargo, verá que los puntos de datos del 3 al 5 de enero están unidos y no rotos como la muestra original. Sam para puntos de datos entre 1/6 y 1/8. No estoy seguro de lo crítico que es NO tener estas líneas conectadas, pero no es posible con la forma en que Excel crea gráficos de líneas cuando tiene un espacio de datos que usa una fórmula.

Sin embargo, vuelva mañana, donde le mostraré cómo hacer que un gráfico de líneas muestre un espacio usando fórmulas.

Tutorial en vídeo

Echa un vistazo a esta demostración en vídeo de cómo crear un Gráfico de líneas de Excel que ignora los valores atípicos: