====== Exercice final - Formation Excel Faculté de Pharmacie ====== Suite aux exercices sur les tableaux croisés dynamiques à partir des [[:pub:excel|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 : * Cellule **X28** : médicament * Cellule **X29** : étape * 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 la formule qui suit : * Cellule **X28** : médicament * Cellule **X29** : étape * 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**.