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) :

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

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 :
  1. 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.
  2. 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

Question ⑥ A quelle température moyenne ?

• 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 ?

• 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

Fin de l'exercice

Aller voir en cellule W37.