Estou tendo alguma dificuldade em determinar como produzir um cálculo de médias que podem ser plotadas em um PivotChart. Especificamente, eu gostaria de comparar o desempenho dos Representantes de Vendas (lucro bruto por mês) com todos os outros representantes (usando uma média) que estão em um papel comparável (o mesmo grupo de trabalho) para um determinado período. Digamos que a estrutura de dados é a seguinte: No modelo de dados do Excel, adicionei campos calculados (que usam uma tabela de pesquisa) para a data de venda, de modo que as vendas possam ser plotadas por Mês ou Ano (por exemplo, ANO (Data de Venda) e MÊS SaleDate)) Como um exemplo, vamos dizer que eu quero traçar GP someones (lucro bruto) por um período de tempo: Minha pergunta é esta. Como posso calcular um lucro bruto médio que posso plotar no PivotChart Este lucro bruto médio deve ser a média de todas as vendas para o mesmo período para o mesmo grupo de trabalho. No exemplo acima, no PivotChart estou querendo plotar uma série média que traça o GP médio por mês para todos os SalesReps que estão no mesmo grupo de trabalho que John A (Sales). Se o meu pedido isnt claro o suficiente por favor me avise e eu vou fazer o meu melhor para expandir. Zam, isso deve ser bastante fácil. Você só precisa criar um novo campo calculado que calcula a média para ALL representante de vendas. Deixe-me levá-lo através dele: Eu usei sua tabela de dados e, em seguida, adicionou-o ao meu PowerPivot (Excel 2017). Então eu criei essas medidas calculadas: 2. Vendas Média ALL - isto calculará a média para TODAS as linhas na tabela e será usado em outros cálculos. Observe que usei o primeiro campo calculado como o primeiro parâmetro para torná-lo flexível: 3. Comparação de Vendas com a Média. Eu não tinha certeza qual é o seu objetivo, mas eu fiz este um pouco mais complexo como eu queria exibir o desempenho em percentagem: Basicamente, o que isso faz é que primeiro ele verifica se o seu cálculo existente para um representante de vendas, se assim for Ele divide Média de Vendas para um determinado representante de vendas pelo valor de venda médio para TODOS os representantes de vendas. E então eu subtrair 1 para que o desempenho pode ser facilmente apreendido apenas por olhar para as percentagens. Para torná-lo fácil de entender, eu decidi usar gráficos de barras em formatação condicional em vez de stand-alone pivotchart - Acredito que ele faz exatamente o que você precisa: Na imagem acima, a primeira tabela representa seus dados. A segunda tabela é a tabela powerpivot real e mostra o que eu descrevi. Espero que isso ajude eu não queria fazer as coisas mais complicadas, mas se você quiser remover a porcentagem total da linha de Totais Grande, use este cálculo em vez disso para a comparação percentual: EDITAR - COMPARAÇÃO MÉDIA ADICIONADA PARA GRUPOS DE TRABALHO Para calcular o desempenho por grupo de trabalho em vez De TODOS os representantes de vendas, adicione essas duas medidas: 4. Média de Vendas por Grupo de Trabalho Isso calculará a venda média por grupo de trabalho (não fique confuso com o uso de SalesRep em função ALL, relacionado ao contexto do filtro). 5. Diferença de vendas para a média por grupo de trabalho Isso simplesmente calcula a diferença entre a venda média de determinado representante de vendas e a venda média por grupo de trabalho. Há duas semanas prometi falar sobre como gerar uma média móvel no PowerPivot, mas na semana passada fiquei desviado ao falar sobre uma maneira legal de exibir o YouTube Vídeos em suas páginas do SharePoint usando uma parte da Web encontrada no CodePlex que alguns dos membros da minha equipe de trabalho encontraram. Foi tão fácil de implementar, eu só tinha que compartilhá-lo com todos vocês. No entanto, voltando ao tema de calcular uma média móvel, a primeira pergunta pode ser o que é uma média móvel e, em seguida, por que você iria querer usar um. Uma média móvel é simplesmente a soma de dois ou mais valores dependentes do tempo em que a soma é então dividida pelo número de valores utilizados. Por exemplo, se eu estivesse falando sobre os preços das ações, talvez eu queira usar algo como uma média móvel de 7 dias para atenuar o efeito de picos de dias individuais ou quedas no preço das ações que não são indicativos da tendência geral do estoque. (Alguns investidores de longo prazo usam médias de movimento de período ainda mais longo.) Isso não significa que se uma ação cai ou sobe que eu me sentaria até que a média móvel me diga para agir. Qualquer investidor bom estoque irá dizer-lhe há muitos outros fatores internos e externos a uma empresa que poderia forçar a sua mão para vender ou comprar qualquer estoque em particular. Mas o ponto é, e esta é a resposta para a segunda pergunta, uma média móvel amortece aleatoriedade para que eu possa ver mais facilmente o padrão geral dos números que eu estou acompanhando. Ok, então suponha que eu trabalho para Contoso e queria saber se as vendas estão subindo, caindo ou geralmente plana. Se eu olhar para as vendas diárias, os números são susceptíveis de flutuar para cima e para baixo em nenhum padrão particular impedindo-me de detectar uma tendência geral. A figura a seguir mostra as vendas diárias da Contoso diariamente durante um período de 3 meses durante o verão de 2008. Eu escolhi mostrar os dados como um gráfico para ajudar a mostrar como as vendas flutuam ao longo do dia revelando informações que eu poderia não ser capaz de ver tão facilmente se eu Criou uma tabela com os mesmos valores. Claro, eu poderia traçar um ano inteiro ou mais, mas para ver os dias individuais, eu teria que ampliar o gráfico substancialmente. No entanto, mesmo com este menor período de tempo, posso ver que as vendas flutuam bastante bem. Mas eu poderia perguntar são as vendas aumentando, diminuindo ou permanecendo o mesmo. Se eu tiver um bom olho, eu poderia dizer que as vendas pico para o final de julho e depois cair para trás um pouco como o gráfico se move em agosto. Mas isso não é tão óbvio quanto o fato de que há uma grande quantidade de flutuação diária. Então, como posso exibir visualmente as tendências com a Moving Average Sales. Agora, para o propósito desta ilustração, estou indo para criar uma média móvel de quatro dias, mas honestamente, não há um certo número de períodos em uma média móvel. Na verdade, eu deveria experimentar períodos de tempo diferentes para ver qual período de tempo me permite detectar não só as tendências gerais, mas também neste caso em que estou exibindo as vendas da loja, em mudanças sazonais. Eu já sei que se eu exibir dados por dia, eu posso usar a seguinte fórmula para calcular as vendas diárias de apenas o nosso canal de loja. (Sim, eu poderia simplesmente usar SalesAmount e aplicar um slicer de canal para usar apenas as vendas da loja, mas vamos ficar com o exemplo.) Posso então usar essa medida calculada para calcular as vendas dias anteriores para qualquer dia, criando a seguinte medida. StoreSales1DayAgo: CALCULATE (StoreSales2DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -1, dia)) Você pode ser capaz de adivinhar que a fórmula para calcular as vendas há dois dias e há três dias respectivamente são: Com estes quatro valores calculados para cada dia, eu posso calcular a soma destes valores e dividir por 4 para obter uma média móvel de 4 dias usando um valor de 4 dias. O seguinte valor calculado: FourDayAverage: (StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo) 4.0 Agora, se eu voltar para a minha página de gráfico, eu deveria ver que o Excel atualiza a lista de campo para incluir as novas medidas calculadas. Se eu adiciono o campo FourDayAverage à caixa Values criando uma segunda série no gráfico, agora tenho tanto as vendas diárias reais como a média móvel de quatro dias exibida no mesmo gráfico. O único problema é que também gostaria de alterar o formato do gráfico para exibir as vendas diárias (minhas primeiras séries de dados) como colunas e minha média móvel (minha segunda série de dados) como uma linha. Quando eu clique com o botão direito do mouse no gráfico e selecione Alterar tipo de gráfico, eu posso selecionar Combo como o tipo de gráfico como mostrado na figura a seguir. Nesse caso, o gráfico da Linha de Colunas em Cluster é exatamente o que eu quero. Como adicionei a série de média móvel à área de Valores em último lugar, ela torna-se, por padrão, a linha e todas as outras séries de dados aparecem como colunas agrupadas. Como eu só tenho um valor para cada dia, o gráfico mostra uma coluna individual por dia. Se eu tivesse entrado minha série de dados na área Valores na ordem errada, eu poderia simplesmente usar essa caixa de diálogo para selecionar o tipo de gráfico para cada série. Quando clico em OK nesta caixa de diálogo, meu gráfico agora se parece com o seguinte, que mostra mais claramente a maior tendência geral e menos flutuação diária. Mas espere, existe uma maneira mais fácil de fazer isso Por que sim, existe. Mas para aprender como fazer isso, você terá que esperar até a próxima semana. Post navigation Meus Arquivos E-mail Assinatura Tópicos Eu falo sobre Como calcular métricas móveis no Excel Análise de dados do Excel para Dummies, 2nd Edition O comando Análise de dados fornece uma ferramenta para calcular movimentação e médias exponencialmente suavizadas no Excel. Suponha, por uma questão de ilustração, que você tenha coletado informações diárias sobre temperatura. Você quer calcular a média móvel de três dias 8212 a média dos últimos três dias 8212 como parte de algumas previsões meteorológicas simples. Para calcular médias móveis para este conjunto de dados, execute as seguintes etapas. Para calcular uma média móvel, clique primeiro no botão de comando Dados da análise de dados tab8217s. Quando o Excel exibe a caixa de diálogo Análise de dados, selecione o item Média móvel da lista e clique em OK. O Excel exibe a caixa de diálogo Média móvel. Identifique os dados que você deseja usar para calcular a média móvel. Clique na caixa de texto Intervalo de entrada da caixa de diálogo Média móvel. Em seguida, identifique o intervalo de entrada, digitando um endereço de intervalo de planilha ou usando o mouse para selecionar o intervalo de planilha. Sua referência de intervalo deve usar endereços de célula absolutos. Um endereço de célula absoluto precede a letra da coluna eo número da linha com sinais, como em A1: A10. Se a primeira célula do seu intervalo de entrada incluir uma etiqueta de texto para identificar ou descrever os dados, marque a caixa de seleção Etiquetas na primeira linha. Na caixa de texto Intervalo, informe ao Excel quantos valores devem ser incluídos no cálculo da média móvel. Você pode calcular uma média móvel usando qualquer número de valores. Por padrão, o Excel usa os três valores mais recentes para calcular a média móvel. Para especificar que algum outro número de valores seja usado para calcular a média móvel, insira esse valor na caixa de texto Intervalo. Diga ao Excel onde colocar os dados da média móvel. Use a caixa de texto Range de saída para identificar o intervalo de planilha no qual você deseja colocar os dados de média móvel. No exemplo da folha de cálculo, os dados da média móvel foram colocados na gama de folhas de cálculo B2: B10. (Opcional) Especifique se deseja um gráfico. Se você quiser um gráfico que traça a informação da média móvel, marque a caixa de seleção Saída do gráfico. (Opcional) Indique se você deseja que as informações de erro padrão sejam calculadas. Se você deseja calcular erros padrão para os dados, marque a caixa de seleção Erros Padrão. O Excel coloca valores de erro padrão ao lado dos valores da média móvel. (As informações de erro padrão passam para C2: C10.) Depois de concluir especificando quais informações de média móvel você deseja calcular e onde deseja colocá-las, clique em OK. O Excel calcula as informações da média móvel. Nota: Se o Excel não possui informações suficientes para calcular uma média móvel para um erro padrão, ele coloca a mensagem de erro na célula. Você pode ver várias células que mostram esta mensagem de erro como um value. Add uma tendência ou linha média móvel para um gráfico Aplica-se a: Excel 2017 Word 2017 PowerPoint 2017 Excel 2017 Word 2017 Outlook 2017 PowerPoint 2017 Mais. Menos Para mostrar tendências de dados ou médias móveis em um gráfico que você criou. Você pode adicionar uma linha de tendência. Você também pode estender uma linha de tendência além de seus dados reais para ajudar a prever valores futuros. Por exemplo, a seguinte linha de tendência linear prevê dois trimestres à frente e mostra claramente uma tendência ascendente que parece promissora para as vendas futuras. Você pode adicionar uma linha de tendência a um gráfico 2-D que não está empilhado, incluindo área, barra, coluna, linha, estoque, dispersão e bolha. Você não pode adicionar uma linha de tendência a um gráfico empilhado, 3D, de torta, de superfície ou de rosquinha. Adicionar uma linha de tendência No gráfico, clique na série de dados à qual pretende adicionar uma linha de tendência ou uma média móvel. A linha de tendência começará no primeiro ponto de dados da série de dados que você escolher. Marque a caixa Trendline. Para escolher um tipo diferente de linha de tendência, clique na seta ao lado de Trendline. E clique em Exponencial. Previsão Linear. Ou média móvel de dois períodos. Para linhas de tendência adicionais, clique em Mais opções. Se escolher Mais opções. Clique na opção desejada no painel Formato da linha de tendência em Opções da linha de tendência. Se você selecionar Polynomial. Digite a potência mais alta para a variável independente na caixa Ordem. Se você selecionar Média Móvel. Digite o número de períodos a serem usados para calcular a média móvel na caixa Período. Dica: Uma linha de tendência é mais precisa quando seu valor R-quadrado (um número de 0 a 1 que revela quão próximos os valores estimados para a linha de tendência correspondem aos seus dados reais) é igual ou próximo de 1. Quando você adiciona uma linha de tendência aos seus dados , O Excel calcula automaticamente o seu valor R-quadrado. Você pode exibir esse valor em seu gráfico, marcando a caixa Mostrar o valor R-quadrado na caixa de gráfico (painel Formato da linha de tendência, Opções da linha de tendência). Você pode aprender mais sobre todas as opções de linha de tendência nas seções abaixo. Linhas de tendência linear Use este tipo de linha de tendência para criar uma linha reta com o melhor ajuste para conjuntos de dados lineares simples. Seus dados são lineares se o padrão em seus pontos de dados se parecer com uma linha. Uma linha de tendência linear geralmente mostra que algo está aumentando ou diminuindo a uma taxa constante. Uma linha de tendência linear usa esta equação para calcular o ajuste de mínimos quadrados para uma linha: onde m é a inclinação eb é a interceptação. A seguinte linha de tendência linear mostra que as vendas de geladeiras têm aumentado consistentemente ao longo de um período de 8 anos. Observe que o valor R-squared (um número de 0 a 1 que revela quão próximos os valores estimados para a linha de tendência correspondem aos dados reais) é 0.9792, que é um bom ajuste da linha aos dados. Mostrando uma linha curva melhor ajustada, esta linha de tendência é útil quando a taxa de alteração nos dados aumenta ou diminui rapidamente e, em seguida, nivela para fora. Uma linha de tendência logarítmica pode usar valores negativos e positivos. Uma linha de tendência logarítmica usa esta equação para calcular o ajuste de mínimos quadrados através de pontos: onde c e b são constantes e ln é a função de logaritmo natural. A seguinte linha de tendência logarítmica mostra predito crescimento populacional de animais em uma área de espaço fixo, onde a população nivelada como espaço para os animais diminuiu. Observe que o valor R-quadrado é 0.933, que é um ajuste relativamente bom da linha para os dados. Essa linha de tendência é útil quando os dados flutuam. Por exemplo, quando você analisa ganhos e perdas em um grande conjunto de dados. A ordem do polinômio pode ser determinada pelo número de flutuações nos dados ou por quantas curvas (colinas e vales) aparecem na curva. Tipicamente, uma linha de tendência polinomial da Ordem 2 tem apenas uma colina ou vale, uma Ordem 3 tem uma ou duas colinas ou vales, e uma Ordem 4 tem até três colinas ou vales. Uma linha de tendência polinomial ou curvilínea usa esta equação para calcular o ajuste de mínimos quadrados através de pontos: onde b e são constantes. A seguinte linha de tendência polinomial da Ordem 2 (uma colina) mostra a relação entre a velocidade de condução eo consumo de combustível. Observe que o valor R-quadrado é 0,979, que é próximo a 1, portanto, as linhas um bom ajuste para os dados. Mostrando uma linha curva, esta linha de tendência é útil para conjuntos de dados que comparam medidas que aumentam a uma taxa específica. Por exemplo, a aceleração de um carro de corrida em intervalos de 1 segundo. Você não pode criar uma linha de tendência de energia se seus dados contiverem valores zero ou negativos. Uma linha de tendência de energia usa essa equação para calcular o ajuste de mínimos quadrados através de pontos: onde c e b são constantes. Nota: Esta opção não está disponível quando os dados incluem valores negativos ou zero. O gráfico de medição de distância a seguir mostra a distância em metros por segundos. A linha de tendência de energia demonstra claramente a crescente aceleração. Observe que o valor R-quadrado é 0.986, que é um ajuste quase perfeito da linha para os dados. Mostrando uma linha curva, esta linha de tendência é útil quando os valores de dados sobem ou descem em taxas constantemente crescentes. Você não pode criar uma linha de tendência exponencial se seus dados contiverem valores zero ou negativos. Uma linha de tendência exponencial usa esta equação para calcular o ajuste de mínimos quadrados através de pontos: onde c e b são constantes e e é a base do logaritmo natural. A linha de tendência exponencial seguinte mostra a quantidade decrescente de carbono 14 num objecto à medida que envelhece. Note que o valor R-quadrado é 0,990, o que significa que a linha se encaixa os dados quase perfeitamente. Moving Average trendline Esta linha de tendência evens out flutuações em dados para mostrar um padrão ou tendência mais claramente. Uma média móvel usa um número específico de pontos de dados (definido pela opção Período), os calcula em média e usa o valor médio como um ponto na linha. Por exemplo, se Período é definido como 2, a média dos dois primeiros pontos de dados é usada como o primeiro ponto na linha de tendência de média móvel. A média do segundo e terceiro pontos de dados é usada como o segundo ponto da linha de tendência, etc. Uma linha de tendência de média móvel usa esta equação: O número de pontos em uma linha de tendência de média móvel é igual ao número total de pontos na série, Número que você especificar para o período. Em um gráfico de dispersão, a linha de tendência é baseada na ordem dos valores x no gráfico. Para obter um resultado melhor, classifique os valores x antes de adicionar uma média móvel. A seguinte linha de tendência de média móvel mostra um padrão no número de casas vendidas ao longo de um período de 26 semanas.
No comments:
Post a Comment