Excel: Construire un diagramme de Levey Jennings

Mon beau-frère est un scientifique. Il travaille souvent avec Excel pour analyser des résultats de laboratoire. Lorsqu’il ne parvient pas à ses fins, il arrive qu’il me demande de venir à sa rescousse. Récemment, il m’a demandé un coup demain pour construire un diagramme de Levey Jennings. Pour être honnête avec vous, je n’avais jamais entendu parler de ce type de graphique auparavant! C’est pourtant un graphique plutôt simple et plutôt intéressant. Cet article vous permettra de le découvrir.

Qu’est-ce qu’un diagramme de Levey Jennings?

Le diagramme de Levey-Jennings est un graphique sur lequel des données de contrôle de qualité sont tracées pour donner une indication visuelle du bon fonctionnement d’un test de laboratoire.

• La valeur moyenne est la valeur attendue pour le contrôle
• La distance par rapport à la moyenne est mesurée en écarts-types (ou SD pour standard deviation)

On trace donc des lignes qui traversent le graphique à la moyenne, ainsi qu’à un, deux et/ou trois écarts-types de chaque côté de la moyenne. Cela permet de constater à quel point chaque résultat se rapproche ou s’éloigne du résultat attendu.

Dans le cas présenté dans cet article, nous avons choisi de présenter des lignes représentants 2 écarts-types au-dessus et en-dessous de la moyenne. À noter que ce type de graphique rappelle les graphiques XmR décrits dans notre article 3 critères à respecter pour développer des indicateurs de performance utiles.

Exemple de diagramme de Levey Jennings

Dans l’exemple ci-dessous, on peut voir les résultats (concentrations) de différents tests effectués à différentes dates, par différents laboratoires et à partir de différents lots. Les points représentent les résultats obtenus, la ligne pointillée grise la moyenne et les deux lignes bleues pointillées la moyenne +/- 2 écarts-types. On voit, par exemple, que le résultat obtenu le 9 janvier 2018 semble particulièrement loin du résultat attendu.

Les segments permettent de sélectionner un laboratoire, pour voir les résultats de ce laboratoire.

Ils permettent également de choisir un lot pour voir les résultats pour ce lot.

De même, ils permettent également de voir les résultats selon n’importe quelle combinaison de dates, lots et laboratoires.

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

Base de données

La base de données qui sert à alimenter ce graphique est une plage de données mise sous forme de tableau dans Excel. Dans les 4 premières colonnes, on retrouve les données d’entrée: date, lot, concentration (résultat) et laboratoire.

Dans les 3 dernières colonnes, on retrouve les données de moyenne et de moyenne +/- 2 fois l’écart-type. Ces données viennent tout simplement des cellules K26, K28 et K29 (voir image ci-dessous).

Dans la cellule K26, on ne peut pas tout simplement faire une moyenne sur la colonne Concentration puisque lorsqu’on va filtrer les données à l’aide d’un segment, la moyenne continuera d’être calculée sur l’ensemble des lignes du tableau (et non seulement sur les lignes filtrées). Ce faisant, nous devons utiliser la fonction AGREGAT. Cette fonction est expliquée en détails dans l’article suivant: Excel 2010: La fonction Aggregate (Agregat).

De la même façon, nous calculons en K27 l’écart-type. Puis, en K28 et K29, on additionne/soustrait 2 fois l’écart-type à la moyenne.

Idée complémentaire pour notre diagramme de Levey Jennings

De façon à pouvoir identifier plus rapidement les résultats dissidents, nous recommandons d’ajouter des étiquettes personnalisées, qui permettraient par exemple d’identifier le laboratoire et le lot sous analyse. Pour savoir comment intégrer ces étiquettes personnalisées, vous pouvez vous référer à l’article suivant: Excel – Personnalisez vos étiquettes de données.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Afin d’approfondir vos connaissances avec les tableaux Excel, nous vous invitons à vous inscrire à notre formation Excel – Tableaux de bord (niveau 2).

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.