Exercice final - Formation Excel Faculté de Pharmacie

Suite aux exercices sur les tableaux croisés dynamiques à partir des données à analyser

Le dernier exercice comporte 7 questions :

① Quel médicament a été produit en plus grande quantité ?
② A quelle température ?
③ A quelle pression ?
④ Quel médicament a été produit le moins souvent ?
⑤ En quelle quantité ?
⑥ A quelle température moyenne ?
⑦ A quelle pression moyenne ?

Aide sur les fonctions à utiliser :

MIN

MAX

EQUIV

INDEX

NB.SI

NB.SI.ENS

SOMME.SI.ENS

MOYENNE.SI.ENS

Mise en situation

Les données ont été copier sur Excel et sont positionnées selon cet exemple (jusqu'à la ligne 17680) :

A	B	C	D	E	F	G	H
1	étapes	début	durée h	Lot n°	température	pression	Médicament	Production (litres)
2	Prétraitement	07/01/2024 09:17	03:07:12	1	119,9	5,1	Advil	146
3	Prétraitement	07/01/2024 09:17	03:07:12	1	119,9	5,1	Kardegic	146
4	Osmose	07/01/2024 12:25	18:57:36	1	107	5,7	Advil	153
Avant de pouvoir créer les formules, il faut placer le contexte. Il se peut qu'on soit obliger de passer par plusieurs formules/fonctions. Il y aura plusieurs étapes avant d'obtenir la réponse à la question.

Question ① Quel médicament a été produit en plus grande quantité ?

On recherche la plus grande valeur de la colonne H.

Formule en cellule L3 : =MAX(H:H) ⇒ 16029

On recherche sur quelle ligne ce trouve l'information précédente.

Formule en cellule L4 : =EQUIV(L3;HH;0) ⇒ 6281

On récupère l'information de la colonne “Médicament” n° 7 (lettre G) de la ligne (n°6281) qui est inscrite en cellule L4

Formule en cellule L5 : =INDEX(A1:H17680;L4;7) ⇒ Kardegic

Ma réponse à la question ① est : Kardegic

Afin de mieux comprendre la fonction INDEX, on pourrait également obtenir d'autres informations : étape =INDEX(A1:H17680;L4;1) début =INDEX(A1:H17680;L4;2) Lot n° =INDEX(A1:H17680;L4;4)

Question ② A quelle température ?

On a obtenu a la question ① certaines informations.
La température se trouve dans une autre colonne, la n° 5.

Température =INDEX(A1:H17680;L4;5) ⇒ 90,6

Ma réponse à la question ② est : 90,6°C

Question ③ A quelle pression ?

On a obtenu à la question ① certaines informations.
La température se trouve dans une autre colonne, la n° 6.

Pression =INDEX(A1:H17680;L4;6) ⇒ 6,8

Ma réponse à la question ③ est : 6,8 bars

Question ④ Quel médicament a été produit le moins souvent ?

Obtenir la liste unique et trié des médicaments (info en colonne G, n°7).
2 solutions possibles :

En copiant ailleurs l'ensemble de la colonne G, puis en sélectionnant ces données, utilisez l'outil

Dans le Bandeau, onglet “Données”, groupe “outils de données”, icône supprimer les doublons

Résultat : “17659 valeurs dupliquées trouvées et supprimées : 20 valeurs uniques sont conservées.”

Il nous reste plus qu'à trier ces nouvelles données.

Avec une formule et avec la propagation automatique : =UNIQUE(G2:G17680)

Afin de les trier, modifier la formule =TRIER(UNIQUE(G2:G17680))

On obtient la liste suivante que je place dans la plage de cellule “W2:W21”.

Advil

Aldactone

Aspirine

Augmentin

Bactrim

Clamoxyl

Coversyl

Dafalgan

Doliprane

Efferalgan

Flagyl

Imodium

Kardegic

Lasilix

Nurofen

Orelox

Renitec

Spifen

Suprax

Zithromax

Il nous faut maintenant compter combien de fois je trouve chaque médicament dans l'ensemble des données.

Formule en X2 : =NB.SI(G:G;W2)

Recopier cette formule jusqu'à la ligne 21. Formule en X21 : =NB.SI(G:G;W21)

J'obtient les valeurs de nombre de fois que je trouve chaque médicament.

J'ai besoin d'avoir la valeur la plus faible.

En cellule X25, je saisie la formule =MIN(X2:X21) ⇒ 848

En cellule X26, je saisie la formule =EQUIV(X25;X2:X21;0) ⇒ 1.
Le nom du médicament recherché est donc situé en position 1 dans la plage “X2:X21”.

En cellule X28, je saisie la formule =INDEX(W2:W21;X26) ⇒ Advil
Cette réponse sera utilisé dans les questions ⑥ et ⑦.

Ma réponse à la question ④ est : Advil

Question ⑤ En quelle quantité ?

Il faut que je parcours l'ensemble de mes données (plage A1:H17680) à la recherche du médicament “Advil” et que j'additionne tous les étapes “Stockage”.

Dans la cellule X30, je saisie la formule =SOMME.SI.ENS(H:H;G:G;X28) ⇒ 1405806.
Ce résultat effectue la somme de la colonne H:H si l'info de la colonne G:G contient le médicament inscrit en cellule X28 “Advil”.

La formule SOMME.SI.ENS permet d'avoir plusieurs critères pour effectuer une somme.

Dans la cellule X29, je saisie le nom de l'étape souhaitée : Stockage
Cette information sera utilisé dans les questions ⑥ et ⑦.

Dans la cellule X31, je saisie la formule =SOMME.SI.ENS(H:H;G:G;X28;A:A;X29) ⇒ 1274635

Ma réponse à la question ⑤ est : 1274635

Conseil : Pensez à utiliser l'assistant fonction fx

Question ⑥ A quelle température moyenne ?

Rappel : La colonne E contient les informations de température. On utilise les valeurs obtenues précédemment dans la formule qui suit : étape médicament

Dans la cellule X33, je saisie la formule =MOYENNE.SI.ENS(E:E;G:G;X28;A:A;X29) ⇒ 79,19

Ma réponse à la question ⑥ est : 79,19°C

Question ⑦ A quelle pression moyenne ?

Rappel : La colonne F contient les informations de pression. On utilise les valeurs obtenues précédemment dans le formule qui suit : étape médicament

Dans la cellule X35, je saisie la formule =MOYENNE.SI.ENS(F:F;G:G;X28;A:A;X29) ⇒ 6,69

Ma réponse à la question ⑥ est : 6.69 bars