Ok, I posted this Excel challenge on Friday. Como te saíste? Tenho a certeza que a tua solução é melhor que a minha. This was the challenge from an Excel Forum question:

” How to eliminate outliers in graph

When viewing some graphs, sometimes I need to ignore some outliers. Como saber se é possível clicar nos dados externos ou o X correspondente no eixo x e o grafo será atualizado sem estes dados externos. Sei que posso ir ao conjunto de dados e remover os valores anómalos, mas quero simplificar ao fazê-lo no gráfico. Obrigado.”

Aqui está a imagem que este utilizador postou:

o gráfico tem 2 pontos de dados anómalos. 1 em 4 de Janeiro e 1 em 7 de Janeiro. O Usuário Excel apagou manualmente os pontos de dados e então você verá o gráfico final na parte inferior da imagem.

precisamos também adicionar uma linha de tendência com uma fórmula de linha de tendência e Valor R, mas este pode ser um passo manual. Os dados vão de 1 de Janeiro a 10 de janeiro de 2013. Então vamos com essas suposições.como podemos criar o gráfico final removendo os pontos de dados de 4 e 7 de Janeiro sem apagar manualmente esses pontos de dados? Vamos a isso! Abaixo você vai encontrar um colapso rápido sobre como eu criei a minha solução. Abaixo está um tutorial detalhado passo a passo desta solução Excel. Abaixo, você verá uma demonstração de vídeo desta dica do Excel. Finalmente, abaixo que você vai encontrar uma cópia da minha planilha que você pode baixar para planejar com os dados e técnica de mapeamento.em primeiro lugar, deixe-me mostrar-lhe como outro usuário abordou este problema. O Pete tinha uma maneira engenhosa de resolver este problema. Ele pega os pontos de dados e cria uma fórmula baseada na inclinação da linha. Aqui está sua solução em suas próprias palavras:

de Pete: “eu estava brincando com o seu novo desafio de sexta-feira, e eu vim com uma rotação diferente sobre os resultados. A minha ficha mostra os dados originais e os novos dados com os outlyers removidos. Você terá que desbloquear a folha para ver as fórmulas.

Basicamente, eu peguei os dados originais e utilizada a função de INCLINAÇÃO para encontrar a inclinação dos dados e, em seguida, comparou-a a uma linha teórica com base no valor de X, usando a fórmula algébrica para linhas Y=MX+B. Com esta fórmula e o cálculo de inclinação, eu poderia resolver para B. Agora eu usei e SE a fórmula retornar um erro #N/d se o valor real e o valor teórico, diferem por uma dada % escolhidos pelo validação de dados suspensa célula. Desta forma, o usuário pode escolher em que percentagem para remover os outlyers. Eu tive que incluir 500% e 100% para permitir que o usuário mostrasse os dados originais como o ponto de dados “2” está a 710% do valor teórico calculado.”

Check out his solution here: Petes Outlyer Solution

Now here is my basic solution for creating an Excel Line that does not display Outliers. Volte amanhã para ver a minha solução avançada.

A distribuição

1) Crie o Intervalo de Dados do Gráfico

2) Designar uma Célula, como o Outlier Nível de Tolerância

3) Crie o Outlier Fórmula para o Intervalo de Dados do Gráfico

4) Criar Máscaras de Gráfico no Excel

5) Adicionar linha de Tendência do Gráfico, a linha de Tendência de Fórmula e o Valor de R-Quadrado

6) Suave a linha e limpar Gráfico de Lixo

passo-a-Passo

1) Crie o Intervalo de Dados do Gráfico

Ok, vamos criar o nosso original do intervalo de dados em células A1:B11. Agora vamos criar um gráfico de linha rápida no Excel com marcadores para ver como ele se parece. Parece quase exatamente como o primeiro gráfico que você viu da pergunta original.

a única diferença é que a nossa linha não está suavizada. O último passo deste tutorial irá mostrar-lhe como fazer esta mudança. Mas aqui está como o gráfico original seria com linhas suavizadas:

2) Designar uma Célula, como o Outlier Nível de Tolerância

tudo Bem, a minha solução usa uma célula de folha de cálculo para designar o que a tolerância é um ponto fora da curva. Então eu designei a célula D3 na planilha como meu nível de tolerância. Olhando para os dados, parece que um ponto de dados é designado como um outlier no exemplo do usuário do Excel quando os pontos diferem cerca de 7 unidades.por isso coloquei um valor de 7 na célula D3:

usaremos a célula D3 no próximo passo quando criarmos a nossa fórmula.

3) Crie a fórmula mais estranha para o intervalo de dados de gráficos

primeiro, vamos colocar as nossas datas na célula E2. Isto é tão simples como colocar an =A2 na célula E2.

Ok, este é o passo que faz tudo funcionar. O que precisamos fazer é comparar o ponto de dados atual com os pontos de dados acima e abaixo do atual. A comparação irá ver se o ponto de dados atual está fora do nível de tolerância que definimos na célula D3. Essencialmente, vamos subtrair o ponto de dados atual do ponto de dados uma célula acima e ver se a diferença é maior do que a tolerância na célula D3. Em seguida, repita este passo para a célula abaixo do ponto de dados atual. Se ambos os pontos de dados são maiores que o nível de tolerância, então vamos colocar um NA() na célula atual. Se ambos os valores não estão fora de tolerância, então vamos colocar o valor do ponto de dados atual nesta célula. Agora, como a diferença pode ser negativa, precisamos envolver esta subtração em uma função de valor absoluto. Então na célula F2 vamos colocar esta fórmula:

=SE(E(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Aqui está uma análise da fórmula:

Primeiro vamos começar com uma fórmula SE, então vá para a célula F2 e escreva isto:

=SE(

porque nós precisamos comparar um valor e, em seguida, com base nesse valor, vamos colocar um dos 2 valores em que esta célula

Agora, como descrito acima, você precisa comparar o atual ponto de dados com a célula acima e a célula abaixo. E, se ambas as comparações estão fora de tolerância, então não queremos mostrar o ponto de dados. Assim, uma vez que estamos lidando com uma comparação “ambos”, devemos usar a e função, então vamos digitar que em seguida:

=IF(E(

agora como eu disse anteriormente, às vezes subtraindo os pontos de dados fará o valor final negativo. E um valor negativo estará sempre abaixo de nossa tolerância, então precisamos ter certeza de que nossa diferença está sempre em unidades positivas, uma vez que nosso nível de tolerância está em unidades positivas. Para fazer algo sempre positivo, precisamos envolver nossa comparação em uma função de valor absoluto, então vamos digitar a função ABS seguinte:

=IF (E (ABS (

agora vamos realmente fazer o nosso primeiro valor de comparação. Nós fazemos isso simplesmente subtraindo o valor atual com o valor acima do ponto de dados atual e vemos se ele é maior do que o nosso nível de tolerância na célula D3. Por isso, digite o que:

=SE(E(ABS(B2-B1)>$D$3

Note que eu fiz o D3 e referência absoluta. Se você não sabe o que é uma referência absoluta, confira este post:

referindo-se a gamas em fórmulas para os seus modelos de painel do Excel

que é a primeira parte de nossa e função e a função é separada por uma vírgula, Então você vai colocar a próxima comparação. Então vamos digitar a próxima comparação depois de uma vírgula. A próxima comparação é comparar nosso ponto de dados atual com o próximo ponto de dados. Então vamos digitar isso.:

=SE(E(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3

uma vez que é a nossa última comparação que temos necessidade de se preocupar, você pode acabar com a função E com um parêntese direito. Agora podemos colocar uma vírgula e determinar o que fazer se ambos os critérios e são verdadeiros. No nosso caso, se ambas as diferenças dos pontos de dados são maiores do que a tolerância, então precisamos colocar uma função NA() lá. Então vamos digitar isso se o valor verdadeiro:

=SE(E(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA()

Se você deseja aprender mais sobre a função ND (), confira este post:

Como-Esconder-se um Zero Gráfico de Pizza Slice ou de um Gráfico de Colunas Empilhadas Seção

Agora nós estamos prontos para o valor final se o E função NÃO é VERDADE. Então coloque uma vírgula e digite o valor falso IF. Neste caso, queremos colocar o valor real do ponto de dados da nossa célula de dados B2. Então, digite uma vírgula e, em seguida, B2)

=SE(E(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Agora pressione enter e o seu valor na Célula F2 deve olhar como este:

Agora que temos nossas fórmulas no lugar, podemos copiá-los para baixo para o nosso último ponto de dados nas colunas A e B. Então copie esta fórmula para baixo e ele será parecido com este:

Observe que os pontos de dados para 1/4 e 1/7 estão agora mostrando o valor #N/A. E os outros valores são os mesmos que você vê em nosso conjunto de dados originais na coluna B. Isso é tão maravilhoso! Que bela dica do excel para nos livrarmos dos anómalos da nossa tendência.

Agora estamos todos prontos para construir o nosso gráfico outlier do Excel.

4) Crie um gráfico externo no Excel

portanto, vamos criar o nosso gráfico, destacando a gama de células de E1:F11. Em seguida, vá para a fita de inserção e seleccione um gráfico de linhas com marcadores do botão de linha:

o seu gráfico deverá agora ficar parecido com este:

It looks a lot different then the one with the outliers:

5) Add Trendline to Chart, Trendline Formula and R-Squared Value

Agora que temos o nosso chart, só precisamos de algumas últimas coisas. Vamos adicionar uma tendência ao nosso gráfico. Para fazer isso, clique no gráfico e, em seguida, clique na linha do gráfico. A partir daí, vá para a fita de Layout e escolha o botão Trendline e, em seguida, escolher a linha de Trendline Linear:

em Seguida, escolha “Linear” e “Exibir Equação no gráfico” e “Exibir valor de R-quadrado no gráfico”, o Formato de linha de Tendência caixa de diálogo:

O gráfico será agora olhar como este:

6) Suavizar a linha e limpar Gráfico de Lixo

Agora, tudo o que precisamos fazer é limpar o gráfico e, está tudo pronto! Primeiro selecione o seu gráfico e, em seguida, selecione a legenda e pressione a sua tecla delete. O seu gráfico deverá agora ficar assim:

Agora precisamos de criar um gráfico de linhas suavizadas. Para fazer isso, selecione o gráfico e, em seguida, a linha no gráfico. A partir daí, carregue na sua tecla CTRL+1 para mostrar a janela de Séries de formatos. A partir daí, seleccione os estilos de linha do menu à esquerda e escolha a linha suavizada das opções de estilo de linha:

o seu gráfico final deverá agora ficar assim:

Não há muita diferença, mas quando os dados tem maior mudança, ele vai olhar ainda mais suave

.

Agora vamos compará-lo com o pedido original. Parece quase exactamente o que queríamos. No entanto, você verá que os pontos de dados de 3 de Janeiro para o 5 ° é Unido e não quebrado como a amostra original. Sam para pontos de dados entre 1/6 e 1/8. Não tenho certeza de quão crítico é não ter essas linhas conectadas, mas não é possível com a forma como o Excel cria gráficos de linha quando você tem uma lacuna de dados que usa uma fórmula.

no entanto, volte amanhã, onde lhe mostrarei como fazer um gráfico de linhas mostrar uma lacuna usando fórmulas.

Tutorial Vídeo

Check out this video demonstration of building an Excel Line Chart that Ignores Outliers: