Coloque as pessoas na curva de Bell - dicas do Excel

Jimmy, em Huntsville, deseja traçar uma curva em forma de sino mostrando a pontuação média de várias pessoas. Quando Jimmy fez a pergunta durante meu Seminário Power Excel, pensei em um dos meus vídeos mais populares no YouTube.

No Podcast 1665 - Criar uma curva em sino no Excel, explico que, para criar uma curva em sino, você precisa calcular a média e o desvio padrão. Em seguida, gero 30 pontos ao longo do eixo x que abrangem uma população hipotética de pessoas. Nesse vídeo, eu gerei um intervalo de -3 desvios padrão a + 3 desvios padrão em torno de uma média.

Por exemplo, se você tiver uma média de 50 e um desvio padrão de 10, eu criaria um eixo x que ia de 70 a 130. A altura de cada ponto é calculada usando =NORM.DIST(x,mean,standard deviation,False).

Gerar uma curva de sino

Na imagem acima, os números em A10: A40 são essencialmente "pontos de dados falsos". Eu gero 31 números para criar uma bela curva suave. Se eu tivesse usado apenas 7 pontos de dados, a curva ficaria assim:

Usando menos pontos de dados, a curva do sino ainda funciona

Para o conjunto de dados de Jimmy, as pontuações médias reais de seus funcionários são essencialmente pontos ao longo de um eixo x. Para encaixá-los em uma curva em forma de sino, você precisa descobrir a altura ou o valor Y de cada funcionário.

Siga esses passos:

  1. Classifique os dados para que as pontuações apareçam da menor para a maior.

    Classifique os dados
  2. Calcule uma média usando a função AVERAGE.
  3. Calcule um desvio padrão usando a função STDEV.

  4. Calcule o valor Y à direita das pontuações usando =NORM.DIST(L2,$H$2,$H$3,FALSE). O valor Y irá gerar uma altura do ponto de cada pessoa ao longo da curva do sino. A função DIST.NORM cuidará de plotar as pessoas próximas à média em um local mais alto do que as pessoas próximas ao topo ou à base.

    Gere uma série de valores Y.
  5. Selecione seus dados em L1: M15

  6. Um bug estranho começou a aparecer recentemente no Excel, portanto, para garantir o sucesso, escolha Todos os gráficos na guia Inserir.

    O iniciador de diálogo leva você a todos os tipos de gráficos

    Na caixa de diálogo Inserir gráfico, clique na guia Todos os gráficos. Clique em XY (dispersão) à esquerda. Escolha o segundo ícone na parte superior. Escolha a visualização à direita.

    Quatro cliques para escolher o gráfico

    Sua curva em forma de sino inicial ficará assim:

    A curva do sino

Para limpar a curva do sino, siga estas etapas:

  1. Clique no título e pressione a tecla Delete.
  2. Clique duas vezes em qualquer número ao longo do eixo Y na parte inferior do gráfico. O painel Format Axis aparecerá.

  3. Digite novos valores para Mínimo e Máximo. O intervalo aqui deve ser amplo o suficiente para mostrar a todos no gráfico. Usei 50 a 90.

    Altere o mínimo e o máximo
  4. Aumente o gráfico arrastando a borda do gráfico.
  5. Clique no ícone + à direita do gráfico e selecione Rótulos de dados. Não se preocupe que os rótulos ainda não façam sentido.
  6. Clique duas vezes em um rótulo para abrir o painel Formatar rótulos.
  7. Existem quatro ícones na parte superior do painel. Escolha o ícone que mostra um gráfico de colunas.
  8. Clique na seta ao lado de Opções de rótulo para expandir essa parte do painel.
  9. Escolha o valor das células. Uma caixa de diálogo aparecerá perguntando sobre a localização dos rótulos. Escolha os nomes em K2: K15.

  10. Ainda no painel Format Data Label, desmarque os valores de Y. É importante terminar a Etapa 15 antes de executar a Etapa 16 ou você removerá inadvertidamente as etiquetas.

    Obtenha os rótulos das células que contêm nomes.

Nota

A capacidade de obter rótulos de células foi adicionada ao Excel 2013. Se você estiver usando o Excel 2010 ou anterior, baixe o suplemento XY Chart Labeler de Rob Bovey. (Google para encontrá-lo).

Neste ponto, veja se você tem rótulos de gráfico que estão colidindo uns com os outros. Para corrigi-los, siga cuidadosamente estas etapas.

  1. Clique uma vez em um rótulo de gráfico. Isso seleciona todos os rótulos.
  2. Clique uma vez em um dos rótulos que está em cima de outro rótulo para selecionar apenas esse rótulo.
  3. Passe o mouse sobre várias partes do rótulo até ver uma seta de quatro pontas. Clique e arraste o rótulo para uma nova posição.

  4. Depois de selecionar apenas um rótulo, você pode clicar uma vez em qualquer outro rótulo para selecioná-lo. Repita para todas as outras etiquetas que precisam ser movidas.

    O gráfico final

Assistir vídeo

Transcrição de vídeo

Aprenda Excel com o Podcast, Episódio 2217: Coloque as pessoas em uma curva de sino.

Ei, bem-vindo de volta ao netcast, sou Bill Jelen. A pergunta de hoje, de Jimmy em meu seminário em Huntsville, Alabama. Jimmy tem dados, ele deseja resumir esses dados e, em seguida, plotar os resultados em uma curva em forma de sino.

Tudo bem? Agora, um dos meus vídeos mais populares no YouTube é este: número 1663, Criar uma curva em sino no Excel. E dada uma média e um desvio padrão, descobri o mínimo, que é 3 vezes o desvio padrão menor que a média, e o alto - 3 vezes o desvio padrão a mais do que a média - onde a lacuna é - e uma série de valores X aqui, e para descobrir a altura, use esta função: = DIST.NORM do valor X, a média e o desvio padrão, vírgula falsa (= DIST.NORM (A10, $ B $ 2, $ B $ 3, FALSO)).

E se você pensar sobre isso, este vídeo está realmente usando uma série de valores X falsos aqui para obter uma curva bonita. E vamos usar o mesmo conceito aqui, mas em vez de valores X falsos, na verdade teremos as pessoas aqui embaixo e a altura será exatamente a mesma fórmula. Tudo bem.

Então, agora, Jimmy queria criar uma tabela dinâmica. Então vamos Inserir, Tabela Dinâmica, colocar aqui nesta planilha, clicar em OK. As pessoas descem pelo lado esquerdo e depois sua pontuação média. Tudo bem, então começa com Soma de Pontuação, vou clicar duas vezes lá e mudar para uma média. Ótimo. Agora, bem no fundo, não quero um total geral - clique com o botão direito e remova o total geral - e queremos organizar essas pessoas de cima para baixo, e isso é fácil de fazer em uma tabela dinâmica. Dados, A a Z - excelente. Tudo bem. Agora, vamos fazer exatamente a mesma coisa que fizemos no Podcast 1663, e isso é calcular uma média e um desvio padrão. Portanto, a média é uma média dessas pontuações e, em seguida, é igual ao desvio padrão dessas pontuações. Tudo bem. Agora que sei disso, posso criar meu valor y.

Tudo bem, então algumas coisas que vamos fazer aqui. Primeiro, você não pode criar uma tabela dinâmica - um gráfico de dispersão - a partir de uma tabela dinâmica. Então, copiarei todos esses dados e farei isso com = D2. Observe que tenho o cuidado de não usar o mouse ou as teclas de seta para apontar para eles. E então temos nossos valores aqui. Esses valores se tornarão X, o valor Y se tornará = DIST.NORM, aqui está o valor x, vírgula, para a média, esse número, pressionarei F4 para bloquear; para o desvio padrão é este número, novamente, pressione F4 para bloqueá-lo e FALSO cumulativo. (= DIST.NORM (K2, $ H $ 2, $ H $ 3, FALSO)) E clicaremos duas vezes para copiar isso. Tudo bem. E então, não escolha os rótulos,basta escolher o XY e inseriremos um gráfico de dispersão com linhas - você pode escolher aquele com linhas curvas ou retas. Aqui, vou com linhas curvas como esta. E agora temos todo o nosso pessoal colocado em uma curva em forma de sino.

Tudo bem. Agora, algumas coisas - algumas coisas de tipo de formatação - vamos fazer aqui: primeiro, clique duas vezes aqui ao longo da escala, e parece que nosso número mais baixo está provavelmente em torno de 50 - então vou defina um mínimo de 50 - e nosso maior número - nosso maior número - é 88 - então irei definir um máximo de 90. Tudo bem. E agora, temos que rotular esses pontos. Se você estiver no Excel 2013 ou mais recente, isso é fácil de fazer; mas se você estiver em uma versão mais antiga do Excel, terá que voltar e usar o suplemento Chart Labeler de Rob Bovey para que esses rótulos de pontos venham de algum lugar que não esteja no gráfico. Tudo bem, então começamos aqui. Vamos adicionar rótulos de dados, e isso adiciona números e eles ficam horríveis. Vou chegar aqui e dizer que quero mais opções, opções de etiqueta,e eu quero obter o valor das células - valor das células. Tudo bem? Portanto, o intervalo de células está bem ali, clique em OK. Muito importante usar o valor das células antes de desmarcar o valor Y. Começa a ficar bem. Eu vou me livrar disso. Agora, toda a chave aqui - porque você tem algumas pessoas que estão substituindo umas às outras - é tentar fazer o gráfico o maior possível. Não precisamos de um título lá em cima. Por quê? Apenas exclua isso. E eu ainda vejo, tipo, Kelly e Lou e Andy e Flo estão quase no mesmo lugar; Jared e … Tudo bem. Então agora, isso vai ser frustrante - esses que se sobrepõem. Mas quando clicamos em um rótulo, selecionamos todos os rótulos e, em seguida, clicamos em um rótulo novamente e selecionamos apenas um único rótulo. Tudo bem? Então agora. muito cuidado. tente clicar em Andy e arraste Andy para a esquerda.Parece que Jared e Ike estão juntos, então agora que estou no modo de seleção de rótulo único, é mais fácil. E então Kelly e Lou, arraste-os assim. Talvez haja um lugar melhor que não atropele Lou, ou até, tipo, aqui eu posso arrastá-lo para os dois lados. Tudo bem, então, o que temos? Começamos com um monte de dados, criamos uma tabela dinâmica, descobrimos a média e o desvio padrão, o que apenas nos permite descobrir a altura - a posição Y para cada uma dessas pontuações e a altura delas, com sorte, vamos colocar as pessoas em uma bela curva de sino em forma de parábola, assim.Tudo bem, então, o que temos? Começamos com um monte de dados, criamos uma tabela dinâmica, descobrimos a média e o desvio padrão, o que apenas nos permite descobrir a altura - a posição Y para cada uma dessas pontuações e a altura delas, com sorte, vamos colocar as pessoas em uma bela curva de sino em forma de parábola, assim.Tudo bem, então, o que temos? Começamos com um monte de dados, criamos uma tabela dinâmica, descobrimos a média e o desvio padrão, o que apenas nos permite descobrir a altura - a posição Y para cada uma dessas pontuações e a altura delas, com sorte, vamos colocar as pessoas em uma bela curva de sino em forma de parábola, assim.

Adoro essa pergunta de Jimmy, essa pergunta não está neste livro, mas estará na próxima vez que eu escrever este livro. Vou ter que adicionar isso - é um pedido legal e um pequeno truque legal. As curvas de sino são muito populares no Excel.

Mas verifique meu livro, LIVe, The 54 Greatest Excel Tips of All Time.

Tudo bem, encerrando este episódio: Jimmy de Huntsville, quer organizar as pessoas em uma curva de sino. Então, usamos uma tabela dinâmica para descobrir a pontuação média, classificamos as tabelas dinâmicas de acordo com as pontuações - organizadas de cima para baixo - eliminando o total geral na parte inferior - estes serão essencialmente os valores X - e, ao lado, calcule a média e o desvio padrão dessas pontuações e use fórmulas para copiar os dados da tabela dinâmica para um novo intervalo, porque você não pode ter um gráfico XY que cruze com uma tabela dinâmica. Calcule um valor y para cada pessoa com = DIST.NORM de seu valor x, a média, o desvio padrão, vírgula FALSO; crie um gráfico de dispersão XY com linhas suaves - se você for um Excel 2010 ou anterior, usará o suplemento Chart Labeler de Ron Bovey. Vou colocar você no Google porque,no caso de Rob mudar seu URL, não quero o URL errado aqui. No Excel 2013, tinha Data Labels, From Cells, especifica os nomes, e então alguns ajustes-- mude a escala ao longo da parte inferior, eu mudo eles em e Max e então mova os rótulos que se sobrepõem uns aos outros.

Para baixar a pasta de trabalho do vídeo de hoje, use o URL na descrição do YouTube. Quero agradecer a Jimmy por essa pergunta incrível em Huntsville e quero agradecer a você por passar por aqui. Vejo você na próxima vez para outro netcast de.

Baixar arquivo Excel

Para baixar o arquivo excel: place-people-on-bell-curve.xlsx

Obrigado a Jimmy em Huntsville pela pergunta de hoje!

Excel Pensamento do Dia

Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:

"Se você colocou o Excel no modo de recálculo manual no mês passado, é hora do power pivot (você nunca mais precisará do modo manual)"

Rob Collie

Artigos interessantes...