Cours Excel complet PDF

Excel avancé Ahmed Laatabi ENSAM – Meknès 2024 - 2025 Objectifs Maîtriser les notions avancées de Microsoft Excel Organiser des données et analyser une problématique professionnelle pour la transposer systèmatiquement sous Excel. Créer des indicateurs clés, analyser des données,...

Excel avancé Ahmed Laatabi ENSAM – Meknès 2024 - 2025 Objectifs Maîtriser les notions avancées de Microsoft Excel Organiser des données et analyser une problématique professionnelle pour la transposer systèmatiquement sous Excel. Créer des indicateurs clés, analyser des données, bien présenter ses résultats. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 2 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menues multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 3 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menues multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 4 0 – Excel ? Excel fait partie de la suite Office (bureautique de Microsoft). Excel est un tableur (spreedsheet) : intersection de lignes et de colonnes. Permet d’effectuer des calculs, analyser des données et organiser des informations sous forme de tableaux, de graphiques. Excel est le tableur le plus utilisé dans le monde (Excel n’est pas un SGBD !). Excel est payant. Alternatives gratuites : Google Sheets (usage personnel), LibreOffice Calc, … Ahmed Laatabi | ENSAM - Meknès | 2024-2025 5 Donnée  Décision X Ahmed Laatabi | ENSAM - Meknès | 2024-2025 6 0 – Données ? IRIS est un jeu de données multivariées présenté en 1936 par Ronald Fisher. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 7 1 - Rappel des fonctions de base d’Excel Ahmed Laatabi | ENSAM - Meknès | 2024-2025 8 1 - Rappel des fonctions de base d’Excel + Addition | - Soustraction | * Multiplication | / Division Puissance ^  Comparaison = Egalité = Différence 2 + 1 = 3 renvoie TRUE 3 – 1 2 renvoie FALSE Concaténation & Excel est multi-langues. Faire attention aux différences !!! Exemple : «;» en français correspond à «,» en anglais. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 9 1 - Rappel des fonctions de base d’Excel Formule : = B5 + C4 Plage de cellules : (A5:A20) Groupe de cellules : (A5,A20,B33) Référence relative : les références de cellules ou de plages de cellules sont basées sur leur position par rapport à la cellule qui contient la formule :  Si on copie la formule = B5 + C4 de A5 dans C4 elle devient = D4 + E3 Référence absolue : éviter l’ajustement lorsqu'on copie la formule: = $B$5 + $C$4  Référence mixte : = B$5 + $C4 On peut changer le type de référence (absolue, relative, mixte) en utilisant la touche F4 dans la barre de la formule. On peut renommer une cellule et utiliser son nom au lieu de la référence absolue. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 10 1 - Rappel des fonctions de base d’Excel Les erreurs fréquentes dans Excel : #DIV/0! (Division par zéro) : diviser un nombre par zéro ou une cellule vide. #N/A (Valeur non disponible) : la formule ne trouve pas la valeur recherchée. #VALUE! (Valeur incorrecte) : le type de données utilisé dans une formule est incorrect. #REF! (Référence invalide) : la référence à une cellule est supprimée ou déplacée. #NAME? (Nom non reconnu) : nom de fonction ou de plage incorrect (souvent à cause d’une faute de frappe). #NUM! (Erreur numérique) : utilisation dans une formule de valeurs numériques non valides. #NULL! (Intersection vide) : référence à l'intersection de deux plages de cellules qui ne se croisent pas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 11 1 - Rappel des fonctions de base d’Excel SOMME / SUM (plage ou groupe) : la somme des cellules MOYENNE / AVERAGE MEDIANE / MEDIAN MIN et MAX ECARTYPE.STANDARD / STDEV.S SOMME.SI / SUMIF (plage ou groupe, condition) : la somme des cellules qui vérifient la condition SUMIF(E2:E151,"Iris-setosa",C2:C151) MOYENNE.SI / AVERAGEIF : même logique Ahmed Laatabi | ENSAM - Meknès | 2024-2025 12 1 - Rappel des fonctions de base d’Excel SI / IF : exécuter une action sous condition. IF (condition, valeur si vrai, valeur si faux) Refaire SUMIF(E2:E151,"Iris-setosa",C2:C151) avec IF : =SUM(IF(E2:E151="Iris-setosa",C2:C151,0)) Dans les anciennes versions d’Excel, il faut valider la formule avec Ctrl + Shift + Entrée pour qu'elle fonctionne correctement. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 13 1 - Rappel des fonctions de base d’Excel ALEA() / RAND() : génère un nombre aléatoire entre 0 et 1 (F9 sur la cellule pour regénérer). ENT / INT : retourne la valeur entière d’un numérique. ARRONDI / ROUND : arrondir un chiffre à la décimale selon le paramètre ROUND (3.14, 1)  3.1 | ROUND (3.16, 1)  3.2 TRONQUE / TRUNC : tronque un nombre à un certain nombre de décimales sans arrondir ROUND (3.16, 1)  3.1 NB / COUNT : le nombre de cellules contenant des nombres. NB.SI / COUNTIF : le nombre de cellules qui répondent à un critère : COUNTIF(E2:E151,"Iris-setosa") Ahmed Laatabi | ENSAM - Meknès | 2024-2025 14 1 - Rappel des fonctions de base d’Excel NBVAL / COUNTA : le nombre de cellules ayant un contenu (non vides). NB.VIDE / COUNTBLANK : le nombre de cellules vides. ESTVIDE / ISBLANK: vérifie si une cellule est vide NBCAR / LEN : renvoie le nombre de caractères dans une chaine de texte TEXTE / TEXT : convertit un nombre en texte, dans un format spécifié TEXT(0.285,"0.0%")  28.5% CONCATENER / CONCATENATE : combine plusieurs chaînes de texte en une seule (& ?). Ahmed Laatabi | ENSAM - Meknès | 2024-2025 15 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menues multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 16 2 - Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés a – Filtres Les filtres dans Excel permettent de trier, masquer ou afficher les données répondant à certaines conditions. Dans l’interface Excel :  Sélectionner la plage de cellules ou la table contenant les données.  Accéder à l'onglet Données.  Cliquer sur le bouton Filtrer / Filter (représenté par un entonnoir).  Le filtre Top 10 renvoie les N meilleurs ou pires valeurs. Il peut renvoyer plus de N lignes si plusieurs lignes ont une même valeur. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 17 2 - Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés b – Tris avancés Le tri avancé permet de trier les données en fonction de plusieurs colonnes, dans un ordre spécifié. Dans l’interface Excel :  Sélectionner la plage de cellules ou la table contenant les données.  Accéder à l'onglet Données.  Cliquer sur le bouton Trier / Sort. GRANDE.VALEUR / LARGE : renvoie la k-ième plus grande valeur d'un ensemble de données. LARGE (D2:D51,1)  MAX(D2:D51) LARGE(IF(E2:E151=«Iris-setosa»,D2:D151,0), 1) (valider la formule avec Ctrl + Shift + Entrée) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 18 2 - Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés c – Mises en forme conditionnelles Les mises en forme conditionnelles permettent de formater des cellules en fonction de conditions spécifiques  Appliquer des couleurs, des bordures ou des styles de texte sur les données.  Sélectionner les cellules en question.  Aller dans l'onglet Accueil.  Cliquer sur Mise en forme conditionnelle dans le groupe Styles. Barres de données / Nuances de couleurs / Jeux d’icones : concerne les valeurs numériques. Des styles personnalisés conditionnelles peuvent être créés et appliqués sur les cellules selectionnées. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 19 Excel avancé 2 Ahmed Laatabi ENSAM – Meknès 2024 - 2025 Objectifs Maîtriser les notions avancées de Microsoft Excel Organiser des données et analyser une problématique professionnelle pour la transposer systématiquement sous Excel. Créer des indicateurs clés, analyser des données, bien présenter ses résultats. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 2 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menues multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 3 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menues multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 4 Formules complexes Les formules complexes combinent plusieurs fonctions, opérateurs et références de cellules pour effectuer (automatiser) des calculs avancés ou extraire (analyser) des données spécifiques d'un tableau. Les opérateurs logiques : ET (AND), OU (OR), NON (NOT) =NOT(ISBLANK(E2)) =AND(NOT(ISNUMBER(E2)),NOT(ISBLANK(E2))) =AVERAGEIF(A2:A51,">5") ou =SUMIF(A2:A51,">5")/COUNTIF(A2:A51,">5") Ahmed Laatabi | ENSAM - Meknès | 2024-2025 5 Formules complexes La fonction SOMMEPRODUIT (SUMPRODUCT) calcule la somme de produits de deux (ou plus) plages de cellules =SUMPRODUCT(A2:A51,B2:B51) Pour calculer une moyenne pondérée, par exemple : =SUMPRODUCT(A2:A51,B2:B51) / SUM(B2:B51) // les B2:B51 étant les poids Les formules complexes deviennent « complexes » à lire et comprendre. Utiliser les parenthèses pour contrôler l'ordre des opérations. Nommer les plages de cellules pour rendre les formules plus lisibles. Car oui, on peut nommer des plages aussi de la même manière que les cellules (ou Formules  Gestionnaire de noms) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 6 Formules multicritères SUMIFS Les formules multicritères permettent d'effectuer des calculs en tenant compte de plusieurs conditions simultanément. SOMME.SI.ENS (SUMIFS) : SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],...) =SUMIF(A2:A51,">5") (On sélectionne directement les Iris-setosa) ou =SUMIFS(A2:A151,A2:A151,">5",E2:E151,"=Iris-setosa") Ahmed Laatabi | ENSAM - Meknès | 2024-2025 7 Formules multicritères COUNTIFS et AVERAGEIFS NB.SI.ENS (COUNTIFS) : compte le nombre de cellules qui répondent à plusieurs critères. COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) =COUNTIFS(A2:A151,">5",E2:E151,"=Iris-setosa")  22 MOYENNE.SI.ENS (AVERAGEIFS) : calcule la moyenne d'une plage en fonction de plusieurs critères. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],...) Exercice: comparer un résultat de AVERAGEIFS avec une combinaison de SUMIFS et COUNTIFS. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 8 Imbrications Imbrication : utilisation d’une fonction à l'intérieur d'une autre. Faire des calculs plus complexes ou répondre à des conditions multiples. Imbrications de plusieurs SI, ou avec des opérateurs logiques. Imbrications d’autres fonctions : SUMPRODUCT avec une condition =SUMPRODUCT(A2:A51,B2:B51)  =SUMPRODUCT((E2:E151="Iris-setosa") * (A2:A151) * (B2:B151)) =SUMPRODUCT((E2:E151="Iris-setosa") * (A2:A151))  =SUMIF(E2:E151,"=Iris-setosa",A2:A151) =SUMPRODUCT(((E2:E151="Iris-setosa") + (E2:E151="Iris-virginica")) * (A2:A151)) Notez bien : SUMIF(range, criteria, [sum_range]) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 9 Imbrications de SI SI / IF : exécuter une action sous condition. IF (condition, valeur si vrai, valeur si faux) La fonction SI peut être utilisée de manière imbriquée. C.-à-d. SI dans SI. Créer un nouvelle colonne représentant une classe de fleur selon la longueur des sépales : =IF(A2>7,"BIG",IF(A2>5,"MEDIUM","SMALL")) SI peut être combinée avec d'autres fonctions pour gérer plusieurs conditions : =IF(AND(A2>5, B2>1, C2>1.5),"BIG","NOT BIG") Attention à l’ordre de l’imbrication des conditions pour optimiser les instructions. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 10 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menues multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 11 Recherche V Les fonctions de recherche renvoient une valeur provenant d'une plage de cellules. RECHERCHEV (VLOOKUP) : recherche verticale d’une ligne (Excel lit itérativement les lignes du tableau du haut vers le bas). Une des fonctions les plus utilisées dans Excel. Elle recherche une valeur dans la première colonne d'un tableau ou d'une plage de données, puis retourne une valeur se trouvant dans la même ligne d'une autre colonne. Recherche uniquement de gauche à droite : la valeur recherchée doit toujours être dans la première colonne de la plage. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 12 Recherche V RECHERCHEV(valeur_recherchée;plage_tableau;num_colonne; [valeur_proche]) valeur : valeur à chercher (référence à une cellule ou une valeur constante) plage : la plage du tableau (tout le tableau) où s’effectue la recherche. La recherche se fait toujours dans la première colonne de ce tableau. num_colonne : le numéro de la colonne dont la valeur doit être renvoyée (résultat de la recherche). La numérotation commence (de 1) à partir de la gauche de la plage sélectionnée. valeur_proche : TRUE par défaut pour chercher une valeur proche. FALSE pour chercher la valeur exacte. A mettre à TRUE uniquement si la colonne ou s’effectue la recherche est triée par ordre croissant, dans ce cas Excel cherche la plus grande valeur inférieure ou égale à la valeur recherchée. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 13 Recherche V Si la première colonne de la table n’est pas triée : =VLOOKUP(5.9,A2:E151,5,FALSE)  Iris-setosa (le premier 5.9 trouvé) =VLOOKUP(5.9,A2:E151,5,TRUE)  résultat incohérent Après avoir trié le tableau par sepal_length en ordre croissant : =VLOOKUP(8,A2:E151,5,FALSE)  #N/A (valeur non trouvée) =VLOOKUP(8,A2:E151,5,TRUE)  Iris-virginica (celle ayant sepal_length maximal = 7.9, donc la plus proche de 8) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 14 Recherche H La fonction RECHERCHEH (HLOOKUP) recherche une valeur dans la première ligne d'une plage de données, puis renvoie une valeur d'une ligne spécifiée dans la même colonne. Fonctionne de manière similaire à RECHERCHEV, mais recherche la valeur dans une ligne plutôt que dans une colonne. RECHERCHEH(valeur_recherchée;plage_tableau;num_ligne; [valeur_proche]) Recherche uniquement de haut en bas : la valeur recherchée doit être dans la première ligne de la plage spécifiée. Donc HLOOKUP est utile uniquement pour les tableaux disposés horizontalement. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 15 INDEX Renvoie la valeur d'une cellule à l'intersection d'une ligne et d'une colonne d’une plage. INDEX(plage_tableau, numéro_ligne, numéro_colonne) Dans la table iris d’origine : INDEX(A2:E151,1,5)  Iris-setosa INDEX(A2:E151,51,5)  Iris-versicolor INDEX(A2:E151,101,5)  Iris-virginica Ahmed Laatabi | ENSAM - Meknès | 2024-2025 16 EQUIV EQUIV (MATCH) : recherche une valeur dans une plage de cellules (de la même ligne ou de la même colonne) et renvoie sa position relative (par rapport à la plage). EQUIV(valeur_recherchée, plage_cellules, [type_recherche]) Le type de recherche : 0 : correspondance exacte (le plus utilisé). 1 : correspondance approximative et avec les valeurs triées par ordre croissant (par défaut). -1 : correspondance approximative et avec les valeurs triées par ordre décroissant. MATCH(1.4,A2:E2,0)  3 (la position 3 de la deuxième ligne) MATCH("Iris-versicolor",E2:E151,0)  51 (la position 51 de la colonne E) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 17 INDEX + EQUIV INDEX et EQUIV peuvent être utilisées pour des recherches plus flexibles. Elles offrent des recherches plus flexibles que RECHERCHEV: EQUIV et INDEX peuvent rechercher à la fois verticalement et horizontalement. N’importe quelle colonne et pas forcément la première de la plage. Recherche plus rapide. =VLOOKUP(5.9,A2:E151,5,FALSE) ou  =INDEX(A2:E151,MATCH(5.9,A2:A151,0),5) Le petal_length de la première Iris-versicolor : = INDEX(A2:E151,MATCH("Iris-versicolor",E2:E151,0),3) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 18 INDEX + EQUIV La combinaison de INDEX et EQUIV permet de rechercher des valeurs en fonction de plusieurs critères : Le type de la première fleur ayant sepal_length = 5 et sepal_width = 2 : {=INDEX(A2:E151,MATCH(1,(A2:A151=5)*(B2:B151=2),0),5)} {=INDEX(A2:E151,MATCH(1,(A2:A151=5)+(B2:B151=2),0),5)} {=INDEX(A2:E151,MATCH(TRUE,(A2:A151=5)+(B2:B151=2)>0,0),5)} MATCH renvoie le premier numéro de ligne vérifiant exactement les deux critères INDEX renvoie le species utilisant le numéro de ligne et la colonne 5 Attention : formule matricielle, à valider avec Ctrl + Alt + Entrée Ahmed Laatabi | ENSAM - Meknès | 2024-2025 19 MATCH avec plusieurs conditions MATCH(1,(A2:A151=5)*(B2:B151=2),0) 1 : est la valeur recherchée. (A2:A151=5)*(B2:B151=2) : renvoie une table de 0 ou de 1 selon la condition. Donc on cherche la première occurrence de 1 dans un ensemble de valeurs {0,1} Ça équivaut à dire qu’on cherche la première valeur qui vérifie les conditions. 0 : on cherche la valeur exacte (1), et ça n’aura pas de sens de mettre -1 ou 1. Faire attention à ce que renvoie la combinaison de conditions ! Ahmed Laatabi | ENSAM - Meknès | 2024-2025 20 Excel avancé 3 Ahmed Laatabi ENSAM – Meknès 2024 - 2025 Objectifs Maîtriser les notions avancées de Microsoft Excel Organiser des données et analyser une problématique professionnelle pour la transposer systématiquement sous Excel. Créer des indicateurs clés, analyser des données, bien présenter ses résultats. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 2 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 3 Conditions et références =COUNTIF(A2:A151,">5") =COUNTIF(A2:A151,">"&A2) =COUNTIF(A3:A152,">"&A3) =COUNTIF(A2:A151,">"&$A$2) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 4 Arguments de fonctions Dans la signature d’une fonction, les arguments facultatifs sont entre crochets []. La fonction fonctionne sans ces arguments. S’ils sont omis, Excel prend les valeurs par défaut. On peut se passer d’un argument facultatif en laissant sa place vide. INDEX(array, row_num, [column_num]) Les trois écritures sont valides, et renvoient le même résultat car la plage passée à la fonction est sur une seule ligne. =INDEX(A2:D2,3) =INDEX(A2:D2,1,3) =INDEX(A2:D2,,3) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 5 PEMDAS et Modulo Dans la majorité des langages de programmation, Modulo (%) est situé dans le même rang que la multiplication et la division. Dans Excel, l’opérateur (%) est utilisé pour les pourcentages : = 2 % //  0.02 La fonction MOD permet de trouver le modulo : MOD(number, divisor) =3 + MOD(3,2) //  4 Ahmed Laatabi | ENSAM - Meknès | 2024-2025 6 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 7 Opérations sur les matrices MMULT : multiplier deux matrices. Dans le choix des plages, il faut respecter les conditions du calcul matriciel, si non la formule renvoie une erreur. Il faut également sélectionner une plage appropriée pour recevoir l’ensemble du résultat. // multiplie une 3x2 par une 2x4 donc elle donne une 3x4 {=MMULT(A3:B5,B8:D9)} TRANSPOSE : transposer une matrice. // une 3x2 donne une 2x3 Fonctionne aussi sur des cellules avec du texte (utile pour V  H) ! MINVERSE : inverser une matrice. // une 2x2 donne une 2x2 MDETERM : calculer le déterminant d'une matrice. // valeur unique Ahmed Laatabi | ENSAM - Meknès | 2024-2025 8 Formules matricielles Les formules matricielles permettent d'effectuer des calculs sur plusieurs valeurs/lignes en même temps, plutôt que de calculer une valeur/ligne unique. Utiles pour les opérations sur les ensembles de données, notamment des vecteurs (tableaux) ou des matrices. Formule matricielle (array formula) : traite des tableaux, pas uniquement des matrices. Dans les anciennes versions d’Excel, une formule matricielle doit être validée avec Ctrl + Shift + Enter. Après validation, Excel l'encadre automatiquement de crochets {}. Faire attention aux formules qui renvoient un résultat sur une plage au lieu d’une seule cellule. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 9 Formule normale vs matricielle Formule normale : renvoie le résultat sur une seule cellule : =SUM({1,2,3} * 4)  une seule cellule : 24 = 4+8+12 Formule normale : renvoie le résultat sur plusieurs cellules : {={1,2,3} * 4} //  une matrice 1x3 : {4,8,12} On ne peut pas modifier le contenu de cellules résultats d’une formule matricielle : "You cannot change part of an array".  Suppression complète de la matrice. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 10 Formules matricielles La somme des produits de sepal_length (A2:A151) x sepal_width (B2:B151) : Fonction SOMMEPRODUIT, formule normale : =SUMPRODUCT(A2:A151,B2:B151) Fonction SOMME, formule matricielle : chaque élément de la plage A2:A151 est multiplié par l'élément correspondant dans la plage B2:B151 (même ligne), puis la somme des produits est calculée. Attention, si la formule n’est pas validée matriciellement {}, elle donne comme résultat le produit des deux cellules qui correspondent à la cellule de la formule. ={SUM(A2:A151*B2:B151)} // plus lisible avec les parenthèses ={SUM((A2:A151)*(B2:B151))} Ahmed Laatabi | ENSAM - Meknès | 2024-2025 11 Formules matricielles Les formules matricielles peuvent être unidimensionnelles ou bidimensionnelles. Unidimensionnelles : prennent comme arguments des vecteurs, c-à-d. des plages d’une même colonne ou d’une même ligne. La somme des produits des colonnes A et B pour les Iris-setosa uniquement : {=SUM((A2:A151)*(B2:B151)*(E2:E151="Iris-setosa"))} Bidimensionnelles : permettent d'effectuer des calculs sur des matrices complètes (plages sur plusieurs lignes ou colonnes). La somme des produits de plages multi-colonnes et multi-lignes. SOMMEPRODUIT ne fait pas une multiplication matricielle, mais multiplie les éléments correspondants (dans les deux plages) et fait la somme. Les deux plages doivent avoir les mêmes dimensions. =SUMPRODUCT(A2:B3,C2:D3) // =A2*C2+B2*D2+A3*C3+B3*D3 Ahmed Laatabi | ENSAM - Meknès | 2024-2025 12 En pratique Les formules matricielles permettent de faire des calculs complexes sans avoir besoin de créer des colonnes intermédiaires. Maximum de différence entre A et B sans passer par une colonne intermédiaire {=MAX((A2:A151) - (B2:B151))} SOMMEPRODUIT avec des conditions… La somme des carrées d’une plage de cellules : {=SUM(A2:A151^2)} ou {=SUM(A2:A151*A2:A151)} {=SQRT(SUM((A2:A151-AVERAGE($A$2:$A$151))^2)/150)} ou =STDEV.P(A2:A151) // =STDEV.S(A2:A151) ? Ahmed Laatabi | ENSAM - Meknès | 2024-2025 13 FREQUENCE FREQUENCY est une fonction matricielle calcule la distribution de fréquence d'un ensemble de données en fonction d'intervalles donnés (renvoie le nombre de valeurs qui appartient à chaque intervalle). FREQUENCY(data_array, bins_array) data_array : une plage de colonnes contenant les données (ex : A2:A151). bins_array : la plage contenant les bornes (limites) des intervalles, triées obligatoirement en ordre croissant. Le nombre d’intervalles est le nombre de bornes + 1. Comme toutes les formules matricielles dans Excel 2016, il faut sélectionner une plage appropriée pour pouvoir l’ensemble du résultat. À partir de Excel 365, les formules se propagent automatiquement. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 14 FREQUENCE Compter le nombre de fleurs dont le sepal_length appartient aux intervalles limités par 3,5 et 7 ? Remplir la plage I2:I4 par les limites : 3,5, 7. Sélectionner une plage de 4 cellules dans le même mode que la plage des limites (ici, une même colonne à l’instar de I2:I4). {=FREQUENCY(A2:A151,I2:I4)} // 0 : nombre de sepal_length 3 et 5 et 7 Vérifier le résultat par des COUNTIF et COUNTIFS.. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 15 UNIQUE UNIQUE : renvoie une liste d’éléments uniques à partir d'une plage de cellules. Peut être utilisée, par exemple, pour nettoyer les doublons. Elle est disponible à partir d'Excel 365 et Excel 2021. UNIQUE(plage, [par_colonne], [exactement_une_fois]) par_colonne: extraire les valeurs uniques par ligne (FALSE, par défaut) ou par colonne (TRUE). exactement_une_fois : si FALSE (par défaut), elle extrait des valeurs uniques de la plage (annule les duplicates). Si TRUE, elle ne renvoie que les éléments qui apparaissent exactement une seule fois dans la plage. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 16 TROUVE et CHERCHE TROUVE (FIND) : cherche un texte dans une chaîne de texte plus grande. Elle renvoie la position de la première occurrence du texte recherché (indice du premier caractère), en commençant la recherche à partir du caractère start_num (0 par défaut). Elle est sensible à la casse. FIND(find_text, within_text, [start_num]) CHERCHE (SEARCH) a le même fonctionnement (et la même syntaxe) que TROUVE, mais elle n’est pas sensible à la casse. =FIND("setosa",E2) //  6 =FIND("Setosa",E2) // #VALUE! =SEARCH("Setosa",E2) //  6 Ahmed Laatabi | ENSAM - Meknès | 2024-2025 17 Imbrications … FIND renvoie un nombre (la position) qu’on peut convertir en un booléen qui est à TRUE si la chaine contient le texte recherché : =ISNUMBER(FIND("setosa",E2)) //  TRUE Ou voir si la formule génère une erreur : =IFERROR(FIND(“Setosa",E2),"Texte introuvable") // #N/A  Texte introuvable La fonction SIERREUR (IFERROR) renvoie une valeur spécifique si une formule génère une erreur. Sinon, elle renvoie le résultat normal de la formule. =IFERROR(formule, valeur_si_erreur) =IFERROR(4/0, "Erreur de division") //  Erreur de division =IFERROR(4/2, "Erreur de division") //  2 Ahmed Laatabi | ENSAM - Meknès | 2024-2025 18 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 19 Menus multi-déroulants de saisie Appelés aussi listes déroulantes, elles permettent à l'utilisateur de choisir une valeur parmi une sélection prédéfinie. Ça permet d’éviter les erreurs de saisie. Sélectionner une première cellule où la liste déroulante va apparaître. Données  Validation des données  Autoriser : Liste. Sélectionner la plage contenant les valeurs des catégories. Par exemple, une plage contenant les noms des IRIS (Setosa, Versicolor, Virginica). Pour des sous-catégories: Dans le Name Manager, nommer une plage de valeurs (ex: A2:A51) avec l’un des noms des IRIS (ex: Setosa). Sélectionner une autre cellule, dans Data Validation, saisir =INDIRECT(1ère cellule) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 20 Verrouillage de cellules / feuilles / classeur Permet de protéger des cellules pour éviter qu’elles ne soient modifiées par les utilisateurs. Sélectionner les cellules à protéger. Review  Allow Users to Edit Ranges. Sélectionner les plages qui doivent rester modifiables. Protect Sheet. On peut choisir des actions à autoriser (formatage, suppression de colonnes, …). Saisir un mot de passe pour protéger la déverrouillage de la feuille. On peut appliquer une protection sur tout le classeur : Protect Workbook. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 21 Excel avancé 4 Ahmed Laatabi ENSAM – Meknès 2024 - 2025 Objectifs Maîtriser les notions avancées de Microsoft Excel Organiser des données et analyser une problématique professionnelle pour la transposer systématiquement sous Excel. Créer des indicateurs clés, analyser des données, bien présenter ses résultats. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 2 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 3 Formules multicritères avec OR Par défaut, les fonctions multicritères relient les conditions avec des ET logiques. Pour exécuter ces fonctions avec un OU (exemple avec COUNTIFS) : Addition : diviser les critères et faire des additions =COUNTIF(A2:A151,"6") + COUNTIF(A2:A151,"5") Tableau de critères : faire une liste de conditions {=COUNTIF(A2:A151,{"6","5"})} // le résultat (matrice) de comparaison avec chaque critère =SUM(COUNTIF(A2:A151,{"6","5"})) // avec des constantes, SUM agrège la matrice résultat {=SUM(COUNTIF(A2:A151, I2:I3))} // avec des cellules I2:I3 contenant {"6","5"} {=COUNTIFS(A2:A151,{5,4.4},E2:E151,{"Iris-setosa","Iris-virginica"})} // matrice 1x2 : les setosas avec 5, et les virginica avec 4.4 N.B : la formule SUM avec les constantes est gérée automatiquement par Excel, sans la valider par Ctrl + Shift + Entrer. Celles avec la plage de cellules a besoin d’être validé matriciellement. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 4 Tableaux de constantes Un tableaux de constantes est un ensemble de valeurs (numériques ou textuelles) entourés de {}, et défini directement dans une formule. Excel traite ces valeurs comme un tableau : Unidimensionnel : {6, 5, 4} Bidimensionnel : {1, 2, 3 ; 4, 5, 6} // matrice 2x3 Pour distribuer les valeurs d’un tableau sur plusieurs cellules : Sélectionner, par exemple, 3 cellules de la même ligne. Valider matriciellement la formule {={11,22,33}}. Sur la même colonne, il faut faire: {={11;22;33}}. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 5 Tableaux de constantes Les tableaux de constantes sont utilisés dans les fonctions qui acceptent des tableaux en argument (formules matricielles) : =SUM({1, 2, 3}) // 6 {=COUNTIF(A2:A151, {"6" , "5"})} // renvoie matrice 1x2 {=COUNTIF(A2:A151, {"6" ; "5"})} // renvoie matrice 2x1 Astuce: Sélectionner l’intérieur (entre ()) d’une formule matricielle. Cliquer sur F9 pour voir son calcul interne. Cliquer sur ESC pour quitter la formule. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 6 Formules avec tableaux de constantes Les tableaux de constants permettent de passer plusieurs arguments aux formules matricielles : {=SUM(COUNTIFS(A2:A151, {"5.1", "4.8"}, E2:E151, "Iris-setosa"))} // details avec F9 ? Calculer la moyenne des 3 plus grandes valeurs : {=AVERAGE(LARGE(A2:A151, {1,2,3}))} Calculer la moyenne des 3 plus petites valeurs : {=AVERAGE(SMALL(A2:A151, {1,2,3}))} La fonction SMALL renvoie la k-ième plus petite valeur. LARGE renvoie la k- ième plus grande valeur. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 7 Références dynamiques ROW : renvoie le numéro de ligne d'une cellule ou d'une plage (première cellule de la plage). Si utilisée sans argument, elle renvoie le numéro de ligne de la cellule où la formule est écrite. ROW (A2)  COLUMN : même utilisation pour le numéro de colonne. OFFSET : renvoie une référence de cellule ou d’une plage qui est décalée par rapport à une cellule donnée. Elle permet de créer des références dynamiques en fonction d’un certain nombre de lignes et de colonnes. =OFFSET(reference, rows, cols, [height], [width]) Ahmed Laatabi | ENSAM - Meknès | 2024-2025 8 Références dynamiques référence : la cellule ou la plage de départ. lignes : le nombre de lignes à décaler (positif pour descendre, négatif pour monter). colonnes : le nombre de colonnes à décaler (positif à droite, négatif à gauche). hauteur : le nombre de lignes que la plage à renvoyer. largeur : Le nombre de colonnes de la plage renvoyer. =OFFSET(A2, 1, 2)  renvoie C3 (son contenu) =OFFSET(A2, 3, 4)  E5 // matricielle : sélectionner une plage 2x2 pour recevoir le résultat {=OFFSET(A2, 1,1,2,2)}  B3:C4  Dans F2, mettre la valeur A2, puis dans F3 : =A3+OFFSET(F3,-1,0)  appliquer à toute la colonne pour avoir un cumul des valeurs A2:A151. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 9 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec des formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 10 Fonctions temporelles L'utilisation des dates, années, jours, mois, et heures dans Excel est courante pour les calculs et l'analyse des données, notamment les temporelles. Les fonctions et leurs imbrications permet de créer des formules plus complexes. Excel traite les dates comme des nombres séquentiels : le 1 représente le 01/01/1900. Ceci permet de faire des calculs de dates. La fonction AUJOURDHUI() / TODAY() renvoie la date actuelle (à l’ouverture du classeur / fichier Excel). La fonction MAINTENANT() / NOW() renvoie la date et l'heure actuelles. Le format du résultat dépend du format de la cellule (date, nombre, …). Ahmed Laatabi | ENSAM - Meknès | 2024-2025 11 Dates La date «10/11/2024» devient «45576.00» si on formate la cellule en numérique. =TODAY()-1 // hier = DAY(TODAY()) // le jour actuel de même : MONTH(), YEAR() = YEAR(TODAY()) - 1988 // l’âge de qqn né en 1988 DATE(année, mois, jour) : crée une date en fonction des arguments. =TODAY()-DATE(2023,3,21) // nombre de jours depuis 21/03/2023 ou =DAYS(TODAY(),DATE(2023,3,21)) =DAY(DATE(2023,3,21)) // 21 Ahmed Laatabi | ENSAM - Meknès | 2024-2025 12 Dates DATEVALUE() convertit une date d’un format textuel vers le format date reconnu par Excel. Le résultat de la fonction dépend du : Format de la cellule (date, nombre,...). Les paramètres du système d’exploitation et d’Excel : langue, réglages régionaux, … =DATEVALUE("1990-10-8") //  10/8/1990 =DATEVALUE("11/9/99") //  11/9/1999 =DATEVALUE("20 september") //  9/20/2024 DAYS360() renvoie la différence en nombre de jours entre deux dates en se basant sur une année de 360 jours (mois de 30 jours). =DAYS360(TODAY()-100,NOW()) //  98 =DAYS(NOW(), TODAY()-100) //  100 Ahmed Laatabi | ENSAM - Meknès | 2024-2025 13 Heures Les heures sont représentées en tant que fraction d’un jour : 12:00  0,5. =HOUR(NOW()) // heure actuelle de même : MINUTE(), SECOND() TEMPS(heure, minute, seconde) : crée une valeur de temps à partir des arguments. =HOUR(NOW()-TIME(12,30,0)) // nombre d’heures depuis 12:30 PM =DAY(NOW() + TIME(6,0,0)) // le jour après 06 heures TIMEVALUE renvoie un format de temps reconnu par Excel, à partir d’un texte qui contient une expression temporelle. =TIMEVALUE("12:53") //  12:53:00 PM Ahmed Laatabi | ENSAM - Meknès | 2024-2025 14 Imbrication On peut imbriquer les fonctions pour des calculs plus complexes. YEARFRAC calcule la fraction d’année représenté par le nombre de jours entre deux dates. =YEARFRAC(TODAY(),DATEVALUE("1988-01-07")) //  36.76 CONVERTIR (numéro, unité_d'origine, unité_de_conversion) : permet de convertir des unités de temps. Elle supporte les années ("yr"), les jours ("d“), les heures ("hr"), les minutes ("min“), et les secondes ("sec "). =CONVERT(1000,"d","yr") //  2.738 =CONVERT(3600,"sec","hr") //  1 =CONVERT(DAYS(TODAY(), DATEVALUE("1988-01-07")),"d","yr") //  36.76 Ahmed Laatabi | ENSAM - Meknès | 2024-2025 15 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec des formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 16 Graphiques Les graphiques dans Excel permettent de visualiser des informations complexes et aider à leur interprétation. Le type de graphique à choisir doit correspondre à la nature des données et à l’objectif. Les graphiques combinés permettent de superposer différents types de graphiques pour mieux comparer différents types de données. Les graphes multi-échelles permettent de visualiser des données avec des unités ou des ordres de grandeur différents, sur un même graphique, grâce à une échelle secondaire. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 17 Graphique en courbes Un graphique en courbes (Line Chart) connecte des différents points de données, pour par exemple, représenter des tendances dans le temps, ou montrer une relation entre deux variables quantitatives. Il est déterminé par deux axes : Les abscisses (x) : représente souvent le temps. Les ordonnées (y) : affiche la valeur quantitative correspondante à la valeur x. Utile pour visualiser des données chronologiques ou toute série continue.  L’espérance de vie à la naissance Ahmed Laatabi | ENSAM - Meknès | 2024-2025 18 Graphique en secteurs (camembert) Le camembert (Pie Chart) montre les proportions de la répartition d'un ensemble de données, et permet d’illustrer la part de chaque catégorie dans un tout. Chaque secteur représente un pourcentage du total. Utile pour visualiser des parts relatives. Créer un camembert pour les types de fleurs : Créer un tableau de fréquences de chaque type. Avec COUNTIF ! Sélectionner la table des fréquences (noms et valeurs). Insérer le graphique. Le graphique peut être personnalisé. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 19 Graphique en barres / colonnes Un graphique en barres (Bar Chart) utilise des barres horizontales (barres) ou verticales (colonnes) pour comparer des valeurs correspondantes aux différentes catégories. Les barres peuvent être groupées ou empilées. Remplie généralement la même fonction que le camembert. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 20 Histogramme L’histogramme est similaire au diagramme en barre, mais est utilisé pour des données continues sur des plages (classes) de valeurs. Il n’y a pas d’espace entre les barres (pour exprimer la continuité). Montre la répartition de fréquences. Le bar chart compare des catégories. Généralement l’histogramme est toujours groupé (pas empilé). Ahmed Laatabi | ENSAM - Meknès | 2024-2025 21 Graphique en barres vs Histogramme Source : BioRender Ahmed Laatabi | ENSAM - Meknès | 2024-2025 22 Graphique de dispersion Le diagramme de dispersion (Scatter Plot) permet de visualiser la relation entre deux variables continues, à travers un nuage de points. D’autres variables peuvent être rajoutées à travers la couleur et la forme des points. Permet d’analyser la relation (sens de corrélation) et détecter les valeurs aberrantes. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 23 Corrélation ? Le coefficient de corrélation r est une valeur sans unité comprise entre -1 et 1. Une mesure de liaison (ou d’indépendance). C’est le quotient de leur covariance par le produit de leurs écarts types. La corrélation n’est pas la causalité. Source : Data Lab Ahmed Laatabi | ENSAM - Meknès | 2024-2025 24 Corrélation ? L’analyse de corrélation permet de déterminer : La force de la corrélation (forte, faible). Le sens de la corrélation (positive, négative). Source : Dan Shiebler Source : Medium Ahmed Laatabi | ENSAM - Meknès | 2024-2025 25 Corrélation ≠ Causalité https://www.tylervigen.com/spurious-correlations Ahmed Laatabi | ENSAM - Meknès | 2024-2025 26 Graphique à bulles Le graphique à bulles est une variante du graphique de dispersion. Chaque point est représenté par une bulle, dont la taille reflète une valeur supplémentaire. Utile donc pour visualiser trois dimensions de données : taille de la bulle = une 3ème variable numérique. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 27 Mise en forme de données La mise en forme permet de rendre un graphique plus clair et plus attrayant, ce qui aide à mieux comprendre et interpréter les données. Personnaliser les éléments du graphique Titre et légende (position, police, couleur,..) Axes. Etiquettes (labels). Styles de graphiques : affichage et couleurs. Ajout de données Filtres (données, catégories, …). Ahmed Laatabi | ENSAM - Meknès | 2024-2025 28 Analyse et interprétation Chercher des patterns : tendances, relations, covariance, … Découvrir la connaissance cachée dans les données. Repérer des anomalies : Les nuages de points ou les diagrammes à barres pour identifier des outliers. Comparer des séries, des nuages de points, et d’autres formes de visualisation. Les graphiques combinés ou les multi-échelles permet de montrer comment une variable affecte l'autre, et compare leurs comportements respectifs sur une même période. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 29 Excel avancé 5 Ahmed Laatabi ENSAM – Meknès 2024 - 2025 Objectifs Maîtriser les notions avancées de Microsoft Excel Organiser des données et analyser une problématique professionnelle pour la transposer systématiquement sous Excel. Créer des indicateurs clés, analyser des données, bien présenter ses résultats. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 2 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec ces formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 3 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec des formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 4  Graphique : un angle de vision, un point de vue Ahmed Laatabi | ENSAM - Meknès | 2024-2025 5 Graphiques évolués Les graphiques évolués permettent d’aller au-delà des graphiques classiques (barres, points, …) pour une analyse plus sophistiquée. Plus de complexité pour mieux comprendre les tendances, relations, et patterns. Pour faire un graphique avancé, un traitement de données peut être nécessaire auparavant pour adapter la structure de données à l’objectif du graphique. Par exemple : calculer la moyenne de chaque variable par classe, et organiser les données sous format d’une table  Ahmed Laatabi | ENSAM - Meknès | 2024-2025 6 Graphiques évolués : Radar Le graphique radar (spider chart) permet de visualiser et comparer plusieurs variables réparties autour d'un axe central, sous format d’une toile d'araignée. Utile pour afficher des données multidimensionnelles, où chaque axe représente une catégorie. Il peut prendre plusieurs formats selon les données projetées (triangle, losange, cercle, …). Les différences et les similitudes des 4 dimensions de l’IRIS dans chaque classe de fleurs. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 7 Radar Source : Datanovia Ahmed Laatabi | ENSAM - Meknès | 2024-2025 8 Graphiques évolués : Boîte à moustaches Le graphique boîte à moustaches (box plot) permet de comparer la distribution de variables continues (dispersion), visualiser les médianes, et aussi détecter les outliers. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 9 Box plot Ahmed Laatabi | ENSAM - Meknès | 2024-2025 10 Graphiques combinés Les graphiques combinés permettent de superposer différents types de graphiques pour mieux visualiser et comparer des données. On peut superposer des barres et des courbes … Les types de séries à combiner dépendent de types de données, et de l’objectif. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 11 Graphiques combinés Dans un graphique combiné, on peut modifier le type de chaque série (ligne, barre, surface, …). Lignes et surfaces : Ahmed Laatabi | ENSAM - Meknès | 2024-2025 12 Graphiques multi-échelles Les graphiques multi-échelles permettent de visualiser des données avec des unités ou des ordres de grandeur différents, sur un même graphique, grâce à une échelle secondaire (à droite du graphique). Utile lorsque les plages de valeurs sont très différentes, et une peut cacher les variations de l’autre. Ou lorsque les deux variables ont des unités différentes. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 13 Graphiques multi-échelles L’axe principale (à gauche) est souvent utilisée pour les valeurs les plus grandes. L’axe secondaire (à droite) : est souvent utilisée pour les valeurs avec une plage plus petite. Pour éviter la confusion, il est préférable de nommer les axes. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 14 Graphiques multi-échelles Source : Microsoft Support Ahmed Laatabi | ENSAM - Meknès | 2024-2025 15 Programme 1. Rappel des fonctions de base d’Excel (références absolues, moyennes, médianes, sommes conditionnelles, SI, …) 2. Filtres et tris avancés, mises en forme conditionnelles, styles personnalisés. 3. Formules complexes et multicritères, imbrications de SI, autres imbrications. 4. Formules conditionnelles, recherche H et V. 5. Formules matricielles, UNIQUE, TROUVE, … 6. Menus multi-déroulants de saisie, verrouillage de cellules, organisation des données, protection des feuilles classeurs. 7. Utilisation des dates, années, jours, mois, heures, calculs avec des formules imbriquées. 8. Graphiques évolués, superposition de graphes, graphes multi-échelles, choix et interprétation de graphiques, analyse, mise en forme de données. 9. Tableaux croisés dynamiques (TCD), graphiques croisés dynamiques (GCD). 10. Macros enregistrés. 11. Initiation aux macros programmés en VBA. 12. Raporting, création d’indicateurs clés, tableaux de bords, méthodologie, analyse de données, aide à la décision, études de cas. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 16 Tableaux Croisés Dynamiques (TCD) Un TCD (Pivot table) permet de synthétiser un grand volume de données en regroupant et résumant les informations, par catégories (sommes, moyennes, comptages, …). C’est un tableau de valeurs groupées par une ou plusieurs catégories. C’est une autre manière de visualiser et présenter les données  vue d’ensemble. Dans Excel : Sélectionner les données. Insertion. Tableau Croisé Dynamique (Pivot Table). Ahmed Laatabi | ENSAM - Meknès | 2024-2025 17 Tableaux Croisés Dynamiques (TCD) L’onglet Analyze (cliquer sur la tableau auparavant) permet d’accéder au paramétrage du TCD. On peut glisser les variables, un ou plusieurs fois, dans les colonnes, lignes, ou filtres … En cliquant sur une variables dans Values, on peut modifier le type de résumé : Moyenne Min Max … Les segments permettent de filtrer les données dynamiquement : Analyze  Insert Slicer (segment). Ahmed Laatabi | ENSAM - Meknès | 2024-2025 18 Tableaux Croisés Dynamiques (TCD) La moyenne des quatre variables par classe Les statistiques de la variable sepal_length par classe Ahmed Laatabi | ENSAM - Meknès | 2024-2025 19 Graphiques Croisés Dynamiques (GCD) Un GCD est un graphique basé sur un TCD. Il se met à jour automatiquement en fonction des modifications dans le TCD. Ahmed Laatabi | ENSAM - Meknès | 2024-2025 20 Graphiques Croisés Dynamiques (GCD) Sélectionner le TCD. Insert  PivotChart. Les slices (segments) permettent d’animer le graphique qui s’adapte au filtres. Dans l’exemple, on crée une nouvelle classe sepaLSize : =IF(A2