MS Excel Avance - Cours 2024 PDF

Semestre 3 - année 2024 MS EXCEL AVANCE Mme Belattar Sara [email protected] Prof universitaire à [email protected] L’ENCG Settat Sara belattar “Pourquoi apprendre Excel Semestre 3 - ann...

Semestre 3 - année 2024 MS EXCEL AVANCE Mme Belattar Sara [email protected] Prof universitaire à [email protected] L’ENCG Settat Sara belattar “Pourquoi apprendre Excel Semestre 3 - année 2024 est indispensable dans le monde professionnel” 01 Analyse et Traitement des Données Manipulation de grandes quantités de données : Excel permet de traiter rapidement et efficacement de grands volumes de données, facilitant l’organisation et l’analyse. Analyse des tendances : Avec des fonctions comme les tableaux croisés dynamiques et les graphiques, Excel permet de repérer facilement des tendances et des modèles dans les données. “Pourquoi apprendre Excel Semestre 3 - année 2024 est indispensable dans le monde professionnel” 02 Automatisation et Gain de Temps Macros et formules : Grâce à la programmation de macros ou à l’utilisation de formules complexes, il est possible d’automatiser des tâches répétitives, réduisant ainsi le temps nécessaire pour accomplir certaines tâches. Optimisation des processus : Excel permet de simplifier les processus manuels, comme la comptabilité, l'inventaire ou la gestion de projet, grâce à des feuilles de calcul intelligentes. “Pourquoi apprendre Excel Semestre 3 - année 2024 est indispensable dans le monde professionnel” 03 Compétence Demandée par les Employeurs Compétence valorisée sur le marché du travail : De nombreuses offres d'emploi exigent une bonne maîtrise d’Excel. C'est une compétence clé recherchée pour des postes administratifs, financiers, en gestion de projet, etc. Outil de productivité : Une connaissance approfondie d’Excel peut augmenter l'efficacité personnelle et collective dans une entreprise, améliorant ainsi les performances. “Pourquoi apprendre Excel Semestre 3 - année 2024 est indispensable dans le monde professionnel” 04 Visualisation et Présentation des Données Création de graphiques et tableaux : Excel permet de transformer des données brutes en graphiques et tableaux clairs et visuels, facilitant leur présentation lors de réunions ou Élément 5 20% Élément 1 20% de rapports. Élément 4 Élément 2 20% 20% Élément 3 Tableaux de bord interactifs : Avec des compétences 20% avancées, il est possible de créer des tableaux de bord dynamiques pour suivre des indicateurs clés en temps réel. “Pourquoi apprendre Excel Semestre 3 - année 2024 est indispensable dans le monde professionnel” 05 Polyvalence dans de Nombreux Domaines Utilisable dans différents secteurs : Que ce soit dans la finance, le marketing, la gestion de projet, les ressources humaines, ou la logistique, Excel est un outil commun qui s'adapte à presque toutes les disciplines. Standard professionnel : Excel est reconnu et utilisé à travers le monde, ce qui en fait une compétence essentielle pour n'importe quel poste lié à la gestion de données ou d'informations.  Semestre 3 - année 2024 05 Polyvalence dans de Nombreux Domaines 1 Analyse des séries temporelles : Excel permet de suivre et analyser les tendances économiques à travers des données historiques. Cela aide à la prévision des taux de croissance, de l'inflation ou du PIB. Modèles économiques : Les économistes peuvent créer des modèles pour simuler des scénarios économiques et estimer l’impact de certaines politiques ou événements mondiaux. Économie  Semestre 3 - année 2024 05 Polyvalence dans de Nombreux Domaines Modélisation financière : Excel est largement utilisé pour 2 construire des modèles financiers complexes, comme l’évaluation d’entreprises, la gestion de portefeuilles ou l’analyse des flux de trésorerie. Calcul des indicateurs financiers : Des fonctions comme NPV (Valeur actuelle nette) ou IRR (Taux de rentabilité interne) sont disponibles pour évaluer des projets d'investissement. Analyse des risques : Excel permet la création de tableaux de simulation (Monte Carlo, par exemple) pour évaluer les risques Finance financiers.  Semestre 3 - année 2024 05 Polyvalence dans de Nombreux Domaines Analyse des ventes : Excel aide à suivre les performances des 3 campagnes marketing en analysant les données de vente, la part de marché et le comportement des clients. Segmentation de marché : Grâce à la fonction de tableau croisé dynamique, les spécialistes du marketing peuvent facilement segmenter leur audience en fonction de critères comme le lieu, l’âge ou les comportements d’achat. Marketing “Pourquoi apprendre Excel Semestre 3 - année 2024 est indispensable dans le monde professionnel” 06 Solution Abordable Coût d’utilisation faible : Excel est souvent inclus dans la suite Microsoft Office, ce qui en fait une solution abordable pour les entreprises ou les indépendants. De plus, il existe des versions en ligne gratuites pour ceux qui n’ont pas accès à la version complète.  Semestre 3 - année 2024 Ms EXCEL Microsoft Excel est un logiciel de tableur développé par Microsoft, largement utilisé dans le monde professionnel pour le traitement et l’analyse de données. Depuis sa création en 1985, Excel est devenu l’un des outils les plus indispensables dans divers secteurs, tels que la finance, le marketing, la comptabilité, et bien d'autres domaines qui nécessitent la gestion efficace de données. Ce logiciel permet aux utilisateurs de créer des feuilles de calcul, d’automatiser des calculs avec des formules, de visualiser des informations à travers des graphiques et des tableaux, et de manipuler de grandes quantités de données avec des fonctions avancées.  Semestre 3 - année 2024 Objectifs pédagogiques Être capable de faire des calculs Maitriser la mise en forme et la avec différents formats des mise en page des classeurs Excel nombres Objectif 1 Objectif 2 Maîtriser les fonctions avancées Réaliser des tableaux de bord à d’Excel l’aide des TCD et des fonctions statistiques Faire de l’analyse des données Objectif 3 Objectif 4 VBA (Visual Basic for Applications)  Semestre 3 - année 2024 Chapitre I : Rappel 05 Gestionnaire de noms 01 Faire des calculs avec différents format des nombres 02 Les Raccourcis clavier 06 Gestion des tableaux Excel 03 Mise forme des données et mise en Mise en page forme conditionnelle 07 04 Validation des données / Listes de Références relatives et absolues choix 08  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Dans Excel, vous pouvez effectuer des calculs en utilisant différents formats de nombres (entiers, décimaux, pourcentages, devises, etc.). Formats de nombres dans Excel Excel propose plusieurs formats de nombres que vous pouvez appliquer aux cellules : Général : C’est le format par défaut sans mise en forme particulière. Nombre : Format pour les nombres décimaux. Monétaire : Utilisé pour les devises avec des symboles comme €, $, etc. Pourcentage : Pour représenter les pourcentages. Date : Pour les dates (jj/mm/aaaa). Fraction : Pour afficher les nombres sous forme de fraction. Scientifique : Représentation des nombres en notation scientifique.  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Appliquer un format de nombre Pour appliquer un format à un nombre dans une cellule, procédez ainsi : -Sélectionnez la cellule ou la plage de cellules. -Allez dans l'onglet Accueil. -Dans le groupe Nombre, choisissez le format souhaité dans le menu déroulant. Par exemple, vous pouvez choisir "Monétaire", "Pourcentage", "Nombre", etc.  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Calculs avec des nombres décimaux Si vous avez des nombres décimaux dans vos cellules, Excel les traite directement sans besoin de conversion particulière. Par exemple : Formule : =A1 + B1 pour additionner les nombres dans les cellules A1 et B1, peu importe le nombre de décimales.  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Calculs avec des pourcentages Les pourcentages dans Excel sont traités comme des fractions décimales (par exemple, 50 % est équivalent à 0,50 dans les calculs). Si vous avez des valeurs en pourcentage, Excel effectue les calculs correctement. Formule : =A1 * B1 (si A1 contient 100 et B1 contient 50%, le résultat sera 50, car 100 * 50% = 50).  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Calculs avec des montants en devise Si vous appliquez un format de devise à une cellule, Excel effectue les calculs normalement, mais affiche simplement le résultat avec le symbole monétaire. Formule : =A1 + B1 (si A1 contient 50 € et B1 contient 100 €, le résultat sera 150 €, avec le symbole monétaire appliqué automatiquement).  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Calculs avec des fractions Si vous avez des fractions dans vos cellules, Excel peut également les traiter correctement dans les calculs. Formule : =A1 + B1 (si A1 contient 1/2 et B1 contient 1/4, le résultat sera 3/4).  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Changer le format des résultats Parfois, après avoir effectué un calcul, vous souhaiterez que le résultat apparaisse dans un format spécifique (pourcentage, devise, etc.). Vous pouvez simplement : Sélectionner la cellule contenant le résultat. Appliquer le format souhaité en allant dans l'onglet Accueil, puis Nombre, et sélectionner le format approprié.  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Exemple de calcul avec différents formats dans un tableau  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Exercice 1 Sélectionnez la première feuille d'Excel, renommez-la "Exercice 1" et ajoutez-y trois cas ensuite. CAS_01 NB: Entrer dans la cellule B3 (VH) : 30 heures VH :Volume horaire Entrer dans la cellule B5 (TH) : 100 DH/heure TH : Taux Horaire Dans la cellule B7, calculer le salaire : VH * TH  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres CAS_02 Exercice 1 1. Entrer les données suivantes : a. Dans B17 : 15 MARS 1970 : Date de naissance b. Dans B20 : 20 MARS 1995 : Date d'embauche c. Dans B23 : La date actuelle d. Dans B32 : 60 (L’âge légal pour le départ en retraite) : Age 2. Calculer les éléments suivants : a. Dans B26 : L’âge de l’employé (en années) b. Dans B29 : L’ancienneté (en années) c. Dans B35 : La date de départ en retraite  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Exercice 1 CAS_03 1. Dans la cellule J5, calculer le nombre d’heures réalisées (OUT – IN) 2. Copier-glisser la formule jusqu’à la cellule J19 3. Dans la cellule J20, calculer la somme des heures réalisées. 4. Appliquer le format approprié pour avoir le bon affichage du résultat  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Exercice 2 Contexte : Vous travaillez sur un rapport économique et devez organiser et formater les données suivantes dans Excel. Ces données comprennent des montants en devises, des pourcentages de croissance, et des chiffres avec des décimales.  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Exercice 2 Formatez les colonnes en fonction des types de données : a. Colonne "PIB (en milliards)" : Sélectionnez les cellules de la colonne B (B2 à B6). Appliquez un format Nombre avec 2 décimales. b. Colonne "Croissance (décimal)" : Sélectionnez les cellules de la colonne C (C2 à C6). Appliquez un format Pourcentage avec 2 décimales pour convertir les valeurs décimales en pourcentages (0,025 devient 2,50%).  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Exercice 2 c. Colonne "Inflation (décimal)" : Sélectionnez les cellules de la colonne D (D2 à D6). Appliquez un format Pourcentage avec 2 décimales pour convertir les valeurs décimales en pourcentages (0,018 devient 1,80%). d. Colonne "Dette Publique (€)" : Sélectionnez les cellules de la colonne E (E2 à E6). Appliquez un format Monétaire en euros (€), avec séparateurs de milliers.  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Exercice 2 Effectuez des calculs : a. Somme de la dette publique : Dans la cellule E7, calculez la somme totale de la dette publique pour tous les pays. b. PIB moyen : Dans la cellule B7, calculez le PIB moyen des 5 pays. c. Croissance moyenne : Dans la cellule C7, calculez la croissance moyenne des pays.  Chapitre I : Rappel Semestre 3 - année 2024 01 Faire des calculs avec différents format des nombres Calculs avec différents formats Exercice 2 Formatez le résultat : a. Formatez le résultat du PIB moyen (B7) avec 2 décimales. b. Formatez le résultat de la croissance moyenne (C7) en pourcentage avec 2 décimales. c. Formatez la somme de la dette publique (E7) en euros.  Chapitre I : Rappel Semestre 3 - année 2024 1.Raccourcis de base : Ctrl + N : Créer un nouveau classeur. Ctrl + O : Ouvrir un classeur existant. Ctrl + S : Enregistrer le classeur. F12 : Enregistrer sous. Ctrl + P : Imprimer. Les Raccourcis clavier Ctrl + Z : Annuler la dernière action. Ctrl + Y : Rétablir une action annulée. Ctrl + C : Copier. Ctrl + X : Couper. Ctrl + V : Coller. Ctrl + A : Sélectionner tout. Ctrl + F : Rechercher. Ctrl + H : Remplacer. Alt + F4 : Fermer Excel. Ctrl + F4 : Fermer le classeur actuel.  Chapitre I : Rappel Semestre 3 - année 2024 2. Raccourcis pour la gestion des feuilles : Ctrl + Page Up : Aller à la feuille précédente. Ctrl + Page Down : Aller à la feuille suivante. Les Raccourcis clavier Alt + Shift + F1 : Insérer une nouvelle feuille. Ctrl + F9 : Réduire la fenêtre. Ctrl + F10 : Restaurer la taille de la fenêtre.  Chapitre I : Rappel Semestre 3 - année 2024 3. Raccourcis de sélection : Shift + Flèche : Sélectionner une cellule en se déplaçant dans une direction. Les Raccourcis clavier Ctrl + Shift + Flèche : Sélectionner jusqu’à la dernière cellule remplie dans une direction. Ctrl + Espace : Sélectionner la colonne entière. Shift + Espace : Sélectionner la ligne entière. Ctrl + Shift + Espace : Sélectionner tout le tableau.  Chapitre I : Rappel Semestre 3 - année 2024 4. Raccourcis de formatage : Ctrl + 1 : Ouvrir la boîte de dialogue « Format de cellule ». Ctrl + B : Mettre le texte en gras. Ctrl + I : Mettre le texte en italique. Les Raccourcis clavier Ctrl + U : Souligner le texte. Ctrl + Shift + $ : Appliquer le format monétaire. Ctrl + Shift + % : Appliquer le format pourcentage. Ctrl + Shift + # : Appliquer le format date (JJ/MM/AAAA). Ctrl + Shift + @ : Appliquer le format heure. Ctrl + Shift + ^ : Appliquer le format exposant (scientifique). Alt + E : Ouvrir la boîte de dialogue des formats de nombre. Ctrl + T : Convertir les données en tableau.  Chapitre I : Rappel Semestre 3 - année 2024 5. Raccourcis de gestion des lignes/colonnes : Ctrl + Shift + + : Insérer des cellules, des lignes ou des colonnes. Ctrl + - : Supprimer des cellules, des lignes ou des colonnes. Les Raccourcis clavier Ctrl + 9 : Masquer les lignes sélectionnées. Ctrl + 0 : Masquer les colonnes sélectionnées. Ctrl + Shift + ( ) : Afficher les lignes ou colonnes masquées.  Chapitre I : Rappel Semestre 3 - année 2024 02 Les Raccourcis clavier Feuille : Exercice 3 1. Placer le curseur à l’intérieur du tableau 2. Sélectionner tout le tableau (CTRL + A ou CTRL + * ) 3. Pour calculer les totaux de la ligne 8 et de la colonne E en même temps, il suffit d’utiliser le raccourci suivant : a. Pour Windows : Alt + = b. Pour MAC : cmd + shift + T  Chapitre I : Rappel Semestre 3 - année 2024 02 Les Raccourcis clavier Feuille : Exercice 4  Chapitre I : Rappel Semestre 3 - année 2024 02 Les Raccourcis clavier Feuille : Exercice 4 1. Placer le curseur à l’intérieur du tableau 2. Sélectionner tout le tableau (CTRL + A ou CTRL + * ) 3. Appliquer la mise en forme suivante : a. Accueil -> Cellules -> Format -> Hauteur de ligne :24 b. Accueil -> Cellules -> Format -> Ajuster la largeur des colonnes c. Accueil -> Polices -> Bordures -> Autres bordures : i. Style double pour le contour ii. Style simple pour l’intérieur 4. Sélectionner la plage A1:G1 5. Appliquer une couleur de votre choix  Chapitre I : Rappel Semestre 3 - année 2024 02 Les Raccourcis clavier Feuille : Exercice 5 Feuille : Exercice 5 1. Placer le curseur à l’intérieur du tableau 2. Sélectionner tout le tableau (CTRL + A ou CTRL + * ) 3. Appliquer la mise en forme suivante : a. Accueil -> Styles -> Mettre sous forme de tableau b. Choisir l’un des styles proposés c. Cocher (si ce n’est pas le cas) : Mon tableau comporte des en-têtes d. Confirmer la plage : $A$1:$G$475 e. Cliquer sur OK 4. Renommer votre tableau : a. Création de tableau -> Propriétés -> Nom du tableau b. Remplacer Tableau1 par BDD_EMPLOYES  Chapitre I : Rappel Semestre 3 - année 2024 03 Mise forme des données et mise en forme conditionnelle Mise en forme des données dans Excel La mise en forme des données consiste à appliquer des styles visuels aux cellules et aux données pour les rendre plus claires et plus attrayantes. Cela comprend : Police : Modification de la taille, du style (gras, italique, souligné) et de la couleur du texte. Couleur des cellules : Modification de la couleur de fond des cellules pour mieux identifier des groupes de données. Bordures : Ajout de lignes pour encadrer ou séparer les cellules. Alignement : Ajustement de l'alignement horizontal et vertical du texte dans les cellules. Format numérique : Application de formats pour les nombres, les dates, les devises, les pourcentages, etc. Exemple : Mettre les titres de colonnes en gras, ajuster les colonnes pour afficher les données numériques sous forme monétaire, ou encore colorier les lignes en alternance.  Chapitre I : Rappel Semestre 3 - année 2024 03 Mise forme des données et mise en forme conditionnelle Exemple : Tableau des Ventes Quotidiennes et Total des Recettes Sans Mise forme des données Avec Mise forme des données  Chapitre I : Rappel Semestre 3 - année 2024 03 Mise forme des données et mise en forme conditionnelle Mise en forme conditionnelle dans Excel La mise en forme conditionnelle modifie l'apparence d'une cellule ou d'une plage de cellules en fonction de conditions spécifiques. Elle permet de mettre en évidence des valeurs particulières, des tendances ou des anomalies, selon les critères que vous définissez. Exemple : Si la valeur d'une cellule dépasse 1000, la cellule devient verte. Si une date est dépassée, la cellule devient rouge. Utilisation typique : Repérer rapidement les données qui répondent à certaines conditions (chiffres élevés, valeurs négatives, etc.). Identifier des écarts, des erreurs ou des tendances dans un tableau.  Chapitre I : Rappel Semestre 3 - année 2024 03 Mise forme des données et mise en forme conditionnelle Mise en forme conditionnelle dans Excel Fonctionnement : Vous appliquez des règles sur les cellules, et si les données dans ces cellules remplissent les conditions, Excel change leur apparence (couleur de fond, couleur du texte, bordures, etc.). Exemple en Excel : Mettre en évidence les cellules où les ventes sont supérieures à un seuil. Appliquer un dégradé de couleurs pour visualiser les écarts entre des valeurs.  Chapitre I : Rappel Semestre 3 - année 2024 03 Mise forme des données et mise en forme conditionnelle Mise en forme conditionnelle dans Excel Sans Mise en forme conditionnelle Avec Mise en forme conditionnelle Exemple : Les cellules dont les ventes dépassent 5000 sont mises en surbrillance avec une couleur jaune foncé.  Chapitre I : Rappel Semestre 3 - année 2024 03 Mise forme des données et mise en forme conditionnelle Résumé des différences :  Chapitre I : Rappel Semestre 3 - année 2024 04 Validation des données / Listes de choix La validation de données dans Excel est une fonctionnalité qui permet de contrôler les types de données que les utilisateurs peuvent saisir dans une cellule ou une plage de cellules. Elle garantit que seules les valeurs respectant certaines règles ou conditions spécifiées peuvent être entrées, ce qui réduit les erreurs et garantit l'intégrité des données. les principales utilisations de la validation de données : Limiter les types de données : Vous pouvez restreindre les entrées à certains types comme les nombres, les dates, les heures, ou des textes spécifiques. Imposer des plages de valeurs : Par exemple, vous pouvez limiter les saisies à des valeurs comprises entre 1 et 100.  Chapitre I : Rappel Semestre 3 - année 2024 04 Validation des données / Listes de choix les principales utilisations de la validation de données : Listes déroulantes : Vous pouvez créer une liste déroulante dans une cellule, permettant aux utilisateurs de sélectionner des valeurs prédéfinies. Validation basée sur des formules : Vous pouvez créer des règles de validation personnalisées à l'aide de formules, par exemple pour restreindre les valeurs en fonction d'une condition spécifique. Messages d’erreur : Si une entrée non valide est saisie, Excel peut afficher un message d'erreur pour guider l'utilisateur à corriger sa saisie.  Chapitre I : Rappel Semestre 3 - année 2024 04 Validation des données / Listes de choix Scénario : Gestion des commandes d'une entreprise 1. Limiter les types de données (nombres uniquement) Cellule B2 : Quantité commandée Vous voulez vous assurer que seules des valeurs numériques positives soient saisies dans la colonne "Quantité commandée". Validation : Vous définissez la règle de validation pour n'autoriser que des nombres entiers supérieurs à 0.  Chapitre I : Rappel Semestre 3 - année 2024 04 Validation des données / Listes de choix Scénario : Gestion des commandes d'une entreprise 2. Imposer des plages de valeurs (plage de dates) Cellule C2 : Date de livraison prévue Vous souhaitez que les utilisateurs ne puissent entrer que des dates situées dans une certaine plage, par exemple, entre aujourd'hui et 30 jours à venir. Validation : Utilisez la règle de validation pour n'accepter que les dates comprises entre AUJOURDHUI() et AUJOURDHUI()+30.  Chapitre I : Rappel Semestre 3 - année 2024 04 Validation des données / Listes de choix Scénario : Gestion des commandes d'une entreprise 3. Listes déroulantes Cellule D2 : Statut de la commande Vous voulez permettre à l'utilisateur de choisir parmi plusieurs options prédéfinies pour le statut d'une commande, par exemple "En cours", "Expédiée", "Livrée", "Annulée". Validation : Créez une liste déroulante en saisissant ces options comme liste de validation de données.  Chapitre I : Rappel Semestre 3 - année 2024 04 Validation des données / Listes de choix Scénario : Gestion des commandes d'une entreprise 4. Validation basée sur des formules Cellule E2 : Remise appliquée Remise de 15 % si la quantité est inférieure à 50 et de 25 % sinon Validation : Utilisez la formule suivante pour la validation : =SI(B2