D’accord, j’ai posté ce défi de graphique Excel vendredi. Comment avez-vous fait? Je suis sûr que votre solution est meilleure que la mienne. C’était le défi d’une question du forum Excel:

« Comment éliminer les valeurs aberrantes dans le graphique

Lors de l’affichage de certains graphiques, j’ai parfois besoin d’ignorer certaines valeurs aberrantes. J’aime savoir s’il est possible de cliquer sur les données aberrantes ou sur le x correspondant sur l’axe des x et le graphique sera mis à jour sans ces données aberrantes. Je sais que je peux aller à l’ensemble de données et supprimer les valeurs aberrantes, mais je veux simplifier en le faisant sur le graphique. Mille mercis. »

Voici l’image que cet utilisateur a publiée:

Le graphique comporte 2 points de données périphériques. 1 le 4 janvier et 1 le 7 janvier. L’utilisateur Excel a supprimé manuellement les points de données, puis vous verrez le graphique final au bas de l’image.

Nous devons également ajouter une ligne de tendance avec une formule de ligne de tendance et une valeur R, mais cela peut être une étape manuelle. Les données vont du 1er janvier au 10 janvier de l’année 2013. Alors, allons-y avec ces hypothèses.

Alors, comment créer le graphique final en supprimant les points de données des 4 et 7 janvier sans supprimer manuellement ces points de données? Allons-y! Vous trouverez ci-dessous une ventilation rapide de la façon dont j’ai créé ma solution. Ci-dessous, vous trouverez un tutoriel détaillé étape par étape de cette solution Excel. Ci-dessous, vous verrez une démonstration vidéo de cette astuce Excel. Enfin, ci-dessous, vous trouverez une copie de ma feuille de calcul que vous pouvez télécharger pour planifier avec la technique de données et de cartographie.

Tout d’abord, permettez-moi de vous montrer comment un autre utilisateur a abordé ce problème. Pete avait un moyen ingénieux de contourner ce problème. Il prend les points de données et crée une formule basée sur la pente de la ligne. Voici sa solution dans ses propres mots:

De Pete: « Je jouais avec votre nouveau défi du vendredi, et j’ai trouvé un tour différent sur les résultats. Mon graphique montre les données d’origine et les nouvelles données avec les périphériques supprimés. Vous devrez déverrouiller la feuille pour afficher les formules.

Fondamentalement, j’ai pris les données d’origine et utilisé la fonction de PENTE pour trouver la pente des données, puis je l’ai comparée à une ligne théorique basée sur la valeur X en utilisant la formule algébrique pour les lignes Y = MX + B. Avec cette formule et la pente calculée, je pouvais résoudre pour B. Maintenant, j’ai utilisé la formule and IF pour renvoyer une erreur # N / A si la valeur réelle et la valeur théorique différaient d’un % donné choisi par la cellule déroulante de validation des données. De cette façon, l’utilisateur peut choisir à quel pourcentage supprimer les périphériques. J’ai dû inclure 500% et 100% pour permettre à l’utilisateur d’afficher les données d’origine car le point de données « 2” est à 710% de la valeur théorique calculée. »

Découvrez sa solution ici: Solution Petes Outlyer

Voici maintenant ma solution de base pour créer une ligne Excel qui n’affiche pas de valeurs aberrantes. Revenez demain pour voir ma solution avancée.

La Répartition

1) Créer une Plage de Données de Graphique

2) Désigner une Cellule comme Niveau de Tolérance des Valeurs Aberrantes

3) Créer une Formule Aberrante pour la Plage de Données de Graphique

4) Créer un Graphique Aberrant dans Excel

5) Ajouter une Ligne de tendance au Graphique, une Formule de Ligne de tendance et une Valeur R au Carré

/p>

6) Lissez la ligne et nettoyez les fichiers inutiles du graphique

Étape par étape

1) Créez une plage de données de graphique

D’accord, créons notre plage de données d’origine dans les cellules A1:B11. Permet maintenant de créer un graphique linéaire rapide dans Excel avec des marqueurs pour voir à quoi il ressemble. Ressemble presque exactement au premier graphique que vous avez vu de la question originale.

La seule différence est que notre ligne n’est pas lissée. La dernière étape de ce tutoriel vous montrera comment effectuer ce changement. Mais voici à quoi ressemblerait le graphique original avec des lignes lissées:

2) Désignez une cellule comme Niveau de tolérance des valeurs aberrantes

D’accord, ma solution utilise une cellule de feuille de calcul pour désigner la tolérance pour une valeur aberrante. J’ai donc désigné la cellule D3 dans la feuille de calcul comme mon niveau de tolérance. En regardant les données, il semble qu’un point de données soit désigné comme une valeur aberrante dans l’exemple de l’utilisateur Excel lorsque les points diffèrent d’environ 7 unités.

J’ai donc mis une valeur de 7 dans la cellule D3:

Nous utiliserons la cellule D3 à l’étape suivante lorsque nous créerons notre formule aberrante.

3) Créez une formule aberrante pour la plage de données du graphique

Tout d’abord, mettons nos dates dans la cellule E2. C’est aussi simple que de mettre an = A2 dans la cellule E2.

D’accord, c’est l’étape qui fait que tout fonctionne. Ce que nous devons faire est de comparer le point de données actuel avec les points de données au-dessus et au-dessous du point de données actuel. La comparaison verra si le point de données actuel est en dehors du niveau de tolérance que nous avons défini dans la cellule D3. Essentiellement, nous allons soustraire le point de données actuel du point de données une cellule ci-dessus et voir si la différence est supérieure à la tolérance dans la cellule D3. Répétez ensuite cette étape pour la cellule située sous le point de données actuel. Si les deux points de données sont supérieurs au niveau de tolérance, nous placerons un NA() dans la cellule actuelle. Si les deux valeurs ne sont pas hors tolérance, nous placerons la valeur du point de données actuel dans cette cellule. Maintenant, parce que la différence pourrait être négative, nous devons envelopper cette soustraction dans une fonction de valeur absolue. Donc, dans la cellule F2, mettons cette formule:

= IF(ET (ABS(B2-B1) >DD$3, ABS(B2-B3) >DD33), NA(), B2)

Voici une ventilation de la formule:

Nous allons d’abord commencer par une formule IF, alors allez dans la cellule F2 et tapez ceci:

= IF (

parce que nous devons comparer une valeur, puis sur la base de cette valeur, nous allons mettre l’une des 2 valeurs dans cette cellule

Maintenant, comme je l’ai décrit ci-dessus, vous devez comparer le point de données actuel avec la cellule ci-dessus et la cellule ci-dessous. Et, si les deux comparaisons sont hors tolérance, nous ne voulons pas montrer le point de données. Donc, puisque nous avons affaire à une comparaison des « DEUX”, nous devrions utiliser la fonction ET, alors tapons-la ensuite:

= IF(AND(

Maintenant, comme je l’ai dit plus tôt, parfois en soustrayant les points de données, la valeur finale sera négative. Et une valeur négative sera TOUJOURS inférieure à notre tolérance, nous devons donc nous assurer que notre différence est toujours en unités positives puisque notre niveau de tolérance est en unités positives. Pour que quelque chose soit toujours positif, nous devons envelopper notre comparaison dans une fonction de valeur absolue, alors tapons ensuite la fonction ABS:

= IF(AND(ABS(

Maintenant, faisons notre 1ère valeur de comparaison. Nous le faisons simplement en soustrayant la valeur actuelle avec la valeur au-dessus du point de données actuel et voyons si elle est supérieure à notre niveau de tolérance dans la cellule D3. Tapez donc cela dans:

= IF(AND(ABS(B2-B1) >DD$3

Notez que j’ai fait la référence D3 et absolue. Si vous ne savez pas ce qu’est une Référence absolue, consultez cet article:

Se référant aux plages dans les formules de vos modèles de tableau de bord Excel

C’est la première partie de notre fonction ET et la fonction et est séparée par une virgule, puis vous mettrez la comparaison suivante. Permet donc de taper la comparaison suivante après une virgule. La comparaison suivante consiste à comparer notre point de données actuel avec le point de données suivant. Permet donc de taper cela:

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

Puisque c’est notre dernière comparaison dont nous devons nous soucier, vous pouvez terminer la fonction ET par une parenthèse droite. Maintenant, nous pouvons mettre une virgule et déterminer quoi faire si les deux critères ET sont vrais. Dans notre cas, si les deux différences des points de données sont supérieures à la tolérance, nous devons y mettre une fonction NA(). Permet donc de taper cela SI la valeur VRAIE:

= IF(AND(ABS(B2-B1) >DD33, ABS(B2-B3) >DD33), NA()

Si vous souhaitez en savoir plus sur la fonction NA(), consultez cet article:

Comment masquer une tranche de graphique circulaire nulle ou Section de graphique à colonnes empilées

Maintenant, nous sommes prêts pour la valeur finale si la fonction ET n’est PAS VRAIE. Mettez donc une virgule et tapez la valeur IF FALSE. Dans ce cas, nous voulons mettre la valeur réelle du point de données de notre cellule de données B2. Tapez donc une virgule, puis B2)

= IF(AND(ABS(B2-B1) >DD$3, ABS(B2-B3) >DD33), NA(), B2)

Maintenant, appuyez sur entrée et votre valeur dans la cellule F2 devrait ressembler à ceci:

Maintenant que nous avons nos formules en place, nous pouvons les copier jusqu’à notre dernier point de données dans les colonnes A et B. Copiez donc cette formule et elle ressemblera à ceci:

Notez que les points de données pour 1/4 et 1/7 affichent maintenant une valeur de #N/A. Et les autres valeurs sont les mêmes que celles que vous voyez dans notre ensemble de données d’origine dans la colonne B. C’est TELLEMENT MERVEILLEUX! Quel bon conseil excel pour se débarrasser des valeurs aberrantes de notre tendance.

Maintenant, nous sommes tous prêts à construire notre graphique des valeurs aberrantes Excel.

4) Créer un graphique aberrant dans Excel

Permet donc de créer notre graphique en mettant en surbrillance la plage de cellules de E1: F11. Ensuite, allez dans le ruban Insérer et sélectionnez un graphique Linéaire avec des marqueurs à partir du bouton Ligne:

Votre graphique devrait maintenant ressembler à ceci:

Cela semble très différent de celui avec les valeurs aberrantes:

5)Ajoutez une ligne de tendance au graphique, une formule de ligne de tendance et une valeur au carré R

Maintenant que nous avons notre graphique, nous avons juste besoin de quelques dernières choses. Ajoutons une ligne de tendance à notre graphique. Pour ce faire, cliquez sur le graphique, puis cliquez sur la ligne du graphique. De là, allez dans le ruban de mise en page et choisissez le bouton Ligne de tendance, puis choisissez Ligne de tendance linéaire:

Choisissez ensuite « Linéaire” et « Afficher l’équation sur le graphique” et « Afficher la valeur au carré R sur le graphique” dans la boîte de dialogue Format Trendline:

Votre graphique ressemblera maintenant à ceci:

6) Lissez la ligne et nettoyez les fichiers inutiles du graphique

Maintenant, tout ce que nous devons faire est de nettoyer le graphique et nous sommes tous prêts! Sélectionnez d’abord votre graphique, puis sélectionnez la légende et appuyez sur la touche supprimer. Votre graphique devrait maintenant ressembler à ceci:

Maintenant, nous devons créer un graphique linéaire lissé. Pour ce faire, sélectionnez le graphique, puis la ligne dans le graphique. À partir de là, appuyez sur la touche CTRL + 1 pour afficher la boîte de dialogue Format Série. À partir de là, sélectionnez les Styles de ligne dans le menu de gauche et choisissez Ligne lissée parmi les choix de style de ligne:

Votre graphique final devrait maintenant ressembler à ceci:

Pas beaucoup de différence, mais lorsque vos données ont des changements plus importants, elles seront encore plus lisses

.

Permet maintenant de le comparer à la demande d’origine. Cela ressemble presque exactement à ce que nous voulions. Cependant, vous verrez que les points de données du 3 au 5 janvier sont joints et non cassés comme l’échantillon d’origine. Sam pour les points de données entre 1/6 et 1/8. Je ne sais pas à quel point il est essentiel de NE PAS connecter ces lignes, mais ce n’est pas possible avec la façon dont Excel crée des graphiques linéaires lorsque vous avez un espace de données qui utilise une formule.

Cependant, revenez demain où je vais vous montrer comment faire en sorte qu’un graphique linéaire montre un écart en utilisant des formules.

Tutoriel vidéo

Découvrez cette démonstration vidéo de la construction d’un graphique linéaire Excel qui Ignore les valeurs aberrantes: