Ceci est une ancienne révision du document !

---

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 :

1. Quel médicament a été produit en plus grande quantité ?
2. A quelle température ?
3. A quelle pression ?
4. Quel médicament a été produit le moins souvent ?
5. En quelle quantité ?
6. A quelle température moyenne ?
7. A quelle pression moyenne ?

Aide sur les fonctions à utiliser :

• MIN
• MAX
• EQUIV
• INDEX
• NB.SI
• NB.SI.ENS
• MOYENNE.SI.ENS

Mise en situation

Les données ont été copier sur Excel et sont positionner selon cet exemple

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

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 :
  • En copiant l'ensemble de la colonne G, puis en utilisant 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

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”.
  Cette réponse sera utilisé dans les questions ⑥ et ⑦.
• 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 : “Stockage”.
  Cette réponse 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