Jimmy en Huntsville quiere trazar una curva de campana que muestre los puntajes promedio de varias personas. Cuando Jimmy hizo la pregunta durante mi Seminario de Power Excel, recordé uno de mis videos más populares en YouTube.
En Podcast 1665 - Crear una curva de campana en Excel, explico que para crear una curva de campana, debe calcular la desviación media y estándar. Luego genero 30 puntos a lo largo del eje x que abarcan una población hipotética de personas. En ese video, generé que abarcaba desde -3 desviaciones estándar hasta + 3 desviaciones estándar alrededor de una media.
Por ejemplo, si tiene una media de 50 y una desviación estándar de 10, crearía un eje x que va de 70 a 130. La altura de cada punto se calcula usando =NORM.DIST(x,mean,standard deviation,False).
En la imagen de arriba, los números en A10: A40 son esencialmente "puntos de datos falsos". Genero 31 números para crear una bonita curva suave. Si hubiera usado solo 7 puntos de datos, la curva se vería así:
Para el conjunto de datos de Jimmy, los puntajes promedio reales de sus empleados son esencialmente puntos a lo largo de un eje x. Para ajustarlos en una curva de campana, debe calcular la altura o el valor Y de cada empleado.
Sigue estos pasos:
-
Ordene los datos para que las puntuaciones aparezcan de menor a mayor.
Ordenar los datos - Calcule una media usando la función PROMEDIO.
- Calcule una desviación estándar usando la función STDEV.
-
Calcule el valor de Y a la derecha de las puntuaciones usando
=NORM.DIST(L2,$H$2,$H$3,FALSE). El valor Y generará una altura del punto de cada persona a lo largo de la curva de campana. La función DISTR.NORM se encargará de trazar personas cerca de la media en una ubicación más alta que las personas cerca de la parte superior o inferior.
Genere una serie de valores Y - Seleccione sus datos en L1: M15
-
Recientemente comenzó a aparecer un error extraño en Excel, por lo que para garantizar el éxito, elija Todos los gráficos en la pestaña Insertar.
El lanzador de diálogo lo lleva a todos los tipos de gráficos En el cuadro de diálogo Insertar gráfico, haga clic en la pestaña Todos los gráficos. Haga clic en XY (Dispersión) a la izquierda. Elija el segundo icono en la parte superior. Elija la vista previa a la derecha.
Cuatro clics para elegir el gráfico Su curva de campana inicial se verá así:
La curva de la campana
Para limpiar la curva de campana, siga estos pasos:
- Haga clic en el título y presione la tecla Suprimir.
- Haga doble clic en cualquier número a lo largo del eje Y en la parte inferior del gráfico. Aparecerá el panel Formato de eje.
-
Escriba nuevos valores para Mínimo y Máximo. El rango aquí debe ser lo suficientemente amplio para mostrar a todos en el gráfico. Usé 50 a 90.
Cambiar el mínimo y el máximo - Amplíe el gráfico arrastrando el borde del gráfico.
- Haga clic en el icono + a la derecha del gráfico y seleccione Etiquetas de datos. No se preocupe que las etiquetas aún no tengan sentido.
- Haga doble clic en una etiqueta para abrir el panel Formato de etiquetas.
- Hay cuatro iconos en la parte superior del panel. Elija el icono que muestra un gráfico de columnas.
- Haga clic en la flecha junto a Opciones de etiqueta para expandir esa parte del panel.
- Elija Valor de celdas. Aparecerá un cuadro de diálogo solicitando la ubicación de las etiquetas. Elija los nombres en K2: K15.
-
Aún en el panel Formato de etiqueta de datos, anule la selección de los valores de Y. Es importante finalizar el Paso 15 antes de realizar el Paso 16 o quitará las etiquetas sin darse cuenta.
Obtenga las etiquetas de las celdas que contienen nombres.
Nota
La capacidad de obtener etiquetas de las celdas se agregó en Excel 2013. Si está utilizando Excel 2010 o una versión anterior, descargue el complemento XY Chart Labeler de Rob Bovey. (Google para encontrarlo).
En este punto, vea si tiene alguna etiqueta de gráfico que se bloquee entre sí. Para solucionarlos, siga cuidadosamente estos pasos.
- Haga clic en una etiqueta de gráfico. Esto selecciona todas las etiquetas.
- Haga clic en una de las etiquetas que está encima de otra etiqueta para seleccionar solo esa etiqueta.
- Desplácese sobre varias partes de la etiqueta hasta que vea una flecha de cuatro puntas. Haga clic y arrastre la etiqueta a una nueva posición.
-
Una vez que haya seleccionado una sola etiqueta, puede hacer clic en cualquier otra etiqueta para seleccionarla. Repita para cualquier otra etiqueta que deba moverse.
La tabla final
Ver video
Transcripción del video
Aprenda Excel del Podcast, Episodio 2217: Coloque a las personas en una curva de campana.
Oye, bienvenido de nuevo al netcast, soy Bill Jelen. La pregunta de hoy, de Jimmy en mi seminario en Huntsville, Alabama. Jimmy tiene datos, quiere resumir estos datos y luego trazar los resultados en una curva de campana.
¿Bien? Ahora, uno de mis videos más populares en YouTube es este: el número 1663, Crea una curva de campana en Excel. Y dada una media y una desviación estándar, calculé la baja, que es 3 veces la desviación estándar menos que la media, y la alta, 3 veces la desviación estándar más que la media, donde está la brecha, y una serie de valores X aquí, y para calcular la altura, use esta función: = NORM.DIST del valor X, la media y la desviación estándar, coma falsa (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSO)).
Y si lo piensas, este video en realidad solo usa una serie de valores X falsos aquí para obtener una curva de aspecto agradable. Y usaremos el mismo concepto aquí, pero en lugar de valores X falsos, en realidad vamos a tener a la gente aquí y luego la altura será exactamente la misma fórmula. Bien.
Entonces, ahora, Jimmy quería crear una tabla dinámica. Entonces, insertaremos, tabla dinámica, lo colocaremos aquí en esta hoja, haga clic en Aceptar. Personas en el lado izquierdo y luego su Puntaje promedio. Muy bien, comienza con Suma de puntaje, haré doble clic allí y lo cambiaré a un promedio. Excelente. Ahora, en la parte inferior, no quiero un gran total, haga clic con el botón derecho y Eliminar el gran total, y queremos organizar estas personas de arriba a abajo y esto es fácil de hacer en una tabla dinámica. Datos, de la A a la Z: excelente. Bien. Ahora, vamos a hacer exactamente lo mismo que hicimos en Podcast 1663, y eso es calcular una media y una desviación estándar. Entonces, la media es un promedio de estos puntajes y luego es igual a la desviación estándar de esos puntajes. Bien. Ahora que lo sé, puedo crear mi valor y.
Muy bien, vamos a hacer un par de cosas aquí. En primer lugar, no puede crear una tabla dinámica, un gráfico de dispersión, a partir de una tabla dinámica. Así que voy a copiar todos estos datos y lo haré con = D2. Observe que tengo cuidado de no usar el mouse o las teclas de flecha para señalarlos. Y aquí tenemos nuestros valores. Estos se convertirán en valores X, el valor Y se convertirá en = NORM.DIST, aquí está el valor x, coma, para la media, ese número, presionaré F4 para bloquearlo; para la desviación estándar es este número, nuevamente, presione F4 para bloquearlo, y FALSO acumulativo. (= DISTR.NORM (K2, $ H $ 2, $ H $ 3, FALSO)) Y haremos doble clic para copiarlo. Bien. Y luego, no elija las etiquetas,simplemente elija XY e insertaremos un gráfico de dispersión con líneas; puede elegir el que tiene líneas curvas o un poco de líneas rectas. Aquí, iré con líneas curvas como esta. Y ahora tenemos a toda nuestra Gente colocada en una curva de campana.
Bien. Ahora, algunas cosas, algunas cosas de tipo de formato, vamos a hacer aquí: Primero, haga doble clic aquí a lo largo de la escala, y parece que nuestro número más bajo probablemente sea alrededor de 50 establezca un mínimo de 50, y nuestro número más grande, nuestro número más grande, es 88, así que estableceré un máximo de 90. Muy bien. Y ahora, tenemos que etiquetar estos puntos. Si está en Excel 2013 o más reciente, esto es fácil de hacer; pero si está en una versión anterior de Excel, tendrá que volver atrás y usar el complemento Chart Labeler de Rob Bovey para que estas etiquetas de puntos provengan de algún lugar que no está en el gráfico. Muy bien, empezamos aquí. Vamos a agregar etiquetas de datos, y agrega números y se ven horribles. Vendré aquí y diré que quiero más opciones, opciones de etiqueta,y quiero obtener el Valor de las celdas - Valor de las celdas. ¿Bien? Entonces, el rango de celdas está allí, haga clic en Aceptar. Es muy importante usar Valor de las celdas antes de desmarcar el valor de Y. Empieza a verse bien. Me deshaceré de esto. Ahora, la clave aquí, porque hay algunas personas que se sobrescriben entre sí, es intentar hacer que el gráfico sea lo más grande posible. No necesitamos un encabezado allí. ¿Por qué? Solo borra eso. Y todavía veo, como, Kelly y Lou y Andy y Flo están casi en el mismo lugar; Jared y … Está bien. Así que ahora, esto va a ser frustrante, estos que se superponen. Pero cuando hacemos clic en una etiqueta, seleccionamos todas las etiquetas, y luego hacemos clic en una etiqueta nuevamente, y seleccionamos una sola etiqueta. ¿Bien? Y ahora. muy cuidadosamente. intente hacer clic en Andy, y simplemente arrastre a Andy hacia la izquierda.Parece que Jared e Ike están juntos, así que ahora que estoy en el modo de selección de una sola etiqueta, es más fácil. Y luego Kelly y Lou, los arrastran así. Tal vez haya un lugar mejor que no sobrepase a Lou, o incluso, aquí puedo arrastrarlo a ambos lados. Muy bien, entonces, ¿qué tenemos? Comenzamos con un montón de datos, creamos una tabla dinámica, calculamos la media y la desviación estándar, lo que solo nos permite calcular la altura, la posición Y para cada una de esas puntuaciones y la altura de esas, con suerte, llevaremos a la gente a una bonita curva de campana en forma de parábola, así.Muy bien, entonces, ¿qué tenemos? Comenzamos con un montón de datos, creamos una tabla dinámica, calculamos la media y la desviación estándar, lo que solo nos permite calcular la altura, la posición Y para cada una de esas puntuaciones y la altura de esas, con suerte, llevaremos a la gente a una bonita curva de campana en forma de parábola, así.Muy bien, entonces, ¿qué tenemos? Comenzamos con un montón de datos, creamos una tabla dinámica, calculamos la media y la desviación estándar, lo que solo nos permite calcular la altura, la posición Y para cada una de esas puntuaciones y la altura de esas, con suerte, llevaremos a la gente a una bonita curva de campana en forma de parábola, así.
Me encanta esta pregunta de Jimmy, esta pregunta no está en este libro, pero estará en la próxima vez que escriba este libro. Tendré que agregar esto: es una solicitud genial y un pequeño truco genial. Las curvas de campana son muy populares en Excel.
Pero echa un vistazo a mi libro, LIVe, Los 54 mejores consejos de Excel de todos los tiempos.
Muy bien, resumen de este episodio: Jimmy de Huntsville quiere organizar a la gente en una curva de campana. Entonces usamos una tabla dinámica para calcular el puntaje promedio, ordenamos las tablas dinámicas según los puntajes, ordenados de mayor a menor, nos deshacemos del total general en la parte inferior, estos serán esencialmente los valores X y luego a un lado, calcule el promedio y la desviación estándar de esos puntajes y use fórmulas para copiar los datos de la tabla dinámica a un nuevo rango, porque no puede tener un gráfico XY que se cruce con una tabla dinámica. Calcule un valor de y para cada persona con = NORM.DIST de su valor x, la media, la desviación estándar, coma FALSO; cree un gráfico de dispersión XY con líneas suaves: si tiene Excel 2010 o una versión anterior, utilizará el complemento Chart Labeler de Ron Bovey. Voy a pedirle a Google eso porque,en caso de que Rob cambie su URL, no quiero la URL incorrecta aquí. En Excel 2013, tenía Etiquetas de datos, Desde celdas, especificaba los nombres y luego algunos ajustes: cambio la escala en la parte inferior, las cambio a Max y luego muevo las etiquetas que se superponen entre sí.
Para descargar el libro de trabajo del video de hoy, use la URL en la descripción de YouTube. Quiero agradecer a Jimmy por esta maravillosa pregunta en Huntsville, y quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo de Excel
Para descargar el archivo de Excel: place-people-on-bell-curve.xlsx
¡Gracias a Jimmy en Huntsville por la pregunta de hoy!
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"Si ha puesto Excel en modo de recálculo manual en el último mes, es hora de pivotar de potencia (nunca más necesitará el modo manual)"
Rob Collie
