PRATIQUE MICROSOFT EXCEL (partie 3/3)

## Objectifs et prérequis **Objectifs** : À la fin de ce chapitre, l’élève sera capable d’: - Utiliser les fonctions avancées de recherche et de référence (RECHERCHEV, RECHERCHEH, INDEX, EQUIV, SI.CONDITIONS) pour extraire et manipuler des données dans un tableau. - Construire, modifier et interpréter un tableau croisé dynamique (TCD) afin de synthétiser des informations provenant d’une base de données. - Enregistrer, exécuter et modifier une macro VBA simple pour automatiser une tâche répétitive, tout en appliquant les règles de protection des classeurs et de sécurité informatique. **Prérequis** : Maîtrise de l’interface Excel (ruban, feuilles, cellules, mise en forme de base), connaissance des fonctions de base (SOMME, MOYENNE, SI) et capacité à saisir des formules simples. ## Introduction contextualisée Dans les entreprises camerounaises, qu’il s’agisse d’un cabinet comptable à Douala, d’une agence d’assurance à Yaoundé ou d’un service logistique d’une usine agroalimentaire, l’exploitation rapide et fiable des données numériques est devenue incontournable. Excel demeure l’outil privilégié pour traiter les factures, suivre les stocks, analyser les ventes ou préparer les budgets. La troisième partie de la pratique Excel vise à doter l’élève de compétences avancées qui répondent aux exigences du monde professionnel : recherche ciblée d’informations, synthèse multidimensionnelle et automatisation de tâches récurrentes. ## I. Utilisation des fonctions avancées de recherche et de référence ### A. Fonctions RECHERCHEV et RECHERCHEH > **RECHERCHEV** recherche une valeur dans la première colonne d’un tableau et renvoie la valeur située dans une colonne spécifiée de la même ligne. > **RECHERCHEH** effectue la même opération mais sur une ligne (recherche horizontale). Syntaxe : `RECHERCHEV(valeur_cherchée; matrice; indice_colonne; [valeur_proche])` - `valeur_cherchée` : ce que l’on cherche (ex. : numéro de facture). - `matrice` : plage contenant la colonne de recherche et les colonnes de retour. - `indice_colonne` : numéro de la colonne à renvoyer (première colonne de la matrice = 1). - `valeur_proche` : VRAI pour recherche approximative, FAUX pour recherche exacte (à privilégier). **Exemple professionnel** : Un service comptable veut retrouver le montant HT d’une facture dont le numéro est inscrit en cellule B2. La table des factures occupe A5:D20 (colonne A = numéro, colonne D = montant HT). La formule en E2 sera : `=RECHERCHEV(B2;A5:D20;4;FAUX)` ### B. Fonctions INDEX et EQUIV > **INDEX** renvoie la valeur d’une cellule située à l’intersection d’une ligne et d’une colonne dans une plage. > **EQUIV** retourne la position d’une valeur dans une plage linéaire. Ces deux fonctions sont souvent combinées pour remplacer RECHERCHEV lorsque la colonne de recherche n’est pas la première. Syntaxe combinée : `=INDEX(plage_retour; EQUIV(valeur_cherchée; plage_recherche; 0); [num_colonne])` **Exemple** : Dans un suivi de stock, la référence produit est en colonne C, le désignation en colonne B et le quantité en colonne D. Pour obtenir la quantité du produit dont la référence est en G2, on écrit : `=INDEX(D5:D50; EQUIV(G2;C5:C50;0))` ### C. Fonctions SI imbriquées et SI.CONDITIONS > **SI.CONDITIONS** permet de tester plusieurs conditions sans imbriquer plusieurs SI, ce qui rend la formule plus lisible. Syntaxe : `=SI.CONDITIONS(condition1;valeur1;condition2;valeur2;...;VRAI;valeur_par_defaut)` **Exemple** : Un service des ressources humaines attribue une prime selon l’ancienneté : - < 1 an : 0 % - 1 à 3 ans : 5 % - 3 à 5 ans : 10 % - > 5 ans : 15 % Avec l’ancienneté en années dans la cellule B2, la prime en pourcentage se calcule ainsi : `=SI.CONDITIONS(B2<1;0;B2<=3;0,05;B2<=5;0,10;VRAI;0,15)` ## II. Création et exploitation des tableaux croisés dynamiques ### A. Préparation des données source > Un tableau croisé dynamique nécessite une plage de données structurée : chaque colonne représente un champ, chaque ligne un enregistrement, aucune ligne ou colonne totalement vide, et les en-têtes sont uniques. Avant de créer le TCD, il convient de : 1. Supprimer les lignes totalement vides. 2. Uniformiser les formats (dates, texte, nombres). 3. Nommer clairement chaque colonne (ex. : `Date`, `Région`, `Produit`, `Montant HT`). ### B. Insertion d’un TCD 1. Sélectionner une cellule dans la plage de données. 2. Onglet **Insertion** → **Tableau croisé dynamique**. 3. Choisir l’emplacement (nouvelle feuille ou feuille existante). 4. Valider. ### C. Champs de valeur, de ligne, de colonne, filtres > Le volet **Champs de tableau croisé dynamique** permet de glisser‑déposer les champs dans quatre zones : - **Filtres** : pour affiner l’affichage (ex. : sélectionner une seule région). - **Étiquettes de lignes** : définissent les lignes du tableau (ex. : Produit). - **Étiquettes de colonnes** : définissent les colonnes (ex. : Mois). - **Valeurs** : calcul effectué (SOMME, MOYENNE, NB, etc.). **Exemple chiffré** : On veut connaître le chiffre d’affaires mensuel par région pour l’année 2023. - Champs **Date** → groupe par **Mois** et placé en **Étiquettes de colonnes**. - Champ **Région** placé en **Étiquettes de lignes**. - Champ **Montant HT** placé en **Valeurs** avec fonction **SOMME**. Le TCD résultant montre, pour chaque région, le total des ventes mois par mois. ### D. Utilisation des slicers et chronologie > Un **slicer** est un bouton de filtrage visuel qui permet de sélectionner rapidement un ou plusieurs éléments d’un champ. > Une **chronologie** fonctionne comme un slicer dédié aux dates, avec une barre de défilement par mois, trimestre ou année. Pour ajouter un slicer : 1. Clic droit sur un champ du TCD → **Ajouter un slicer**. 2. Choisir le champ (ex. : `Produit`). 3. Positionner le slicer où désiré. En cliquant sur un bouton du slicer, le TCD se met à jour instantanément, ce qui est très utile lors de présentations ou de réunions de suivi. ## III. Automatisation avec les macros VBA et protection des classeurs ### A. Enregistrement d’une macro simple > Une macro enregistre une séquence d’actions utilisateur pour les rejouer ultérieurement. Étapes : 1. Onglet **Développeur** → **Enregistrer une macro**. 2. Donner un nom (ex. : `MiseEnFormeFacture`), choisir un raccourci clavier éventuel, décrire brièvement. 3. Effectuer les actions souhaitées (ex. : mettre en gras les en-têtes, appliquer un bord extérieur, mettre le format monétaire). 4. Cliquer sur **Arrêter l’enregistrement**. La macro est maintenant disponible dans la liste des macros et peut être exécutée via le raccourci ou le bouton dédié. ### B. Édition du code VBA de base > Le langage VBA (Visual Basic for Applications) permet de modifier ou d’enrichir une macro enregistrée. Pour accéder à l’éditeur : - Onglet **Développeur** → **Visual Basic** ou `ALT+F11`. Dans l’arborescence du projet, double‑cliquer sur le module contenant la macro. Le code apparaît, par exemple : ```vba Sub MiseEnFormeFacture() ' ' MiseEnFormeFacture Macro ' Range("A1:D1").Font.Bold = True Range("A1:D1").Borders.LineStyle = xlContinuous Range("E2:E100").NumberFormat = "#,##0 FCFA" End Sub ``` On peut ajouter des boucles, des conditions ou des appels à des fonctions Excel. Par exemple, pour mettre en rouge les cellules contenant un montant négatif : ```vba If Range("E2").Value < 0 Then Range("E2").Font.Color = RGB(255, 0, 0) End If ``` ### C. Protection de feuille et de classeur > Protéger une feuille empêche la modification des cellules verrouillées ; protéger le classeur empêche l’ajout, la suppression ou le renommage de feuilles. **Protection d’une feuille** : 1. Sélectionner les cellules qui doivent rester modifiables → clic droit → **Format de cellule** → onglet **Protection** → décocher **Verrouillé**. 2. Tous les autres cellules restent verrouillées par défaut. 3. Onglet **Révision** → **Protéger la feuille** → définir un mot de passe (optionnel) et choisir les actions autorisées (sélectionner des cellules, insérer des lignes, etc.). **Protection du classeur** : - Onglet **Révision** → **Protéger le classeur** → choisir **Structure** pour empêcher la modification de l’ordre des feuilles. ### D. Bonnes pratiques de sécurité - Toujours enregistrer une copie de secours avant d’exécuter une macro qui modifie des données. - Utiliser des noms de macro explicites et éviter les macros provenant de sources non fiables (risque de code malveillant). - Désactiver l’exécution automatique des macros lors de l’ouverture d’un fichier provenant d’Internet (Centre de confiance → Paramètres des macros). - Documenter le VBA (commentaires) afin que d’autres utilisateurs puissent comprendre et maintenir le code. ## Synthèse du chapitre - Les fonctions de recherche (RECHERCHEV, RECHERCHEH, INDEX, EQUIV) permettent d’extraire précisément des données selon des critères. - SI.CONDITIONS simplifie les tests multiples et améliore la lisibilité des formules. - Un tableau croisé dynamique synthétise rapidement de grandes bases de données grâce aux champs de lignes, colonnes, valeurs et filtres. - Les slicers et la chronologie offrent une interaction visuelle pour filtrer le TCD en temps réel. - L’enregistrement d’une macro VBA automatise des tâches répétitives ; son édition permet d’ajouter de la logique conditionnelle ou des boucles. - La protection de feuille et de classeur sécurise les données contre les modifications accidentelles ou malveillantes. - Respecter les règles de sécurité (sauvegarde, provenance fiable, documentation) est indispensable en milieu professionnel. ## Glossaire > **RECHERCHEV** : Fonction qui recherche une valeur dans la première colonne d’une matrice et renvoie la valeur d’une colonne spécifiée de la même ligne. > **RECHERCHEH** : Équivalent horizontal de RECHERCHEV (recherche sur une ligne). > **INDEX** : Renvoie la valeur d’une cellule située à l’intersection d’une ligne et d’une colonne dans une plage donnée. > **EQUIV** : Retourne la position d’une valeur dans une plage linéaire. > **SI.CONDITIONS** : Fonction qui teste plusieurs conditions successivement et renvoie la valeur associée à la première vraie condition. > **Tableau croisé dynamique (TCD)** : Outil de synthèse qui réorganise et résume des données provenant d’une plage source. > **Slicer** : Bouton de filtrage visuel permettant de sélectionner un ou plusieurs éléments d’un champ dans un TCD ou un tableau. > **Chronologie** : Slicer spécialisé pour les dates, avec barre de défilement par période (mois, trimestre, année). > **Macro VBA** : Suite d’instructions enregistrées ou codées en Visual Basic for Applications pour automatiser des actions dans Excel. > **Protection de feuille** : Verrouillage des cellules afin d’empêcher leur modification sans autorisation. > **Protection de classeur** : Empêche la modification, l’ajout ou la suppression de feuilles dans le classeur. > **Module VBA** : Conteneur de procédures (Sub, Function) dans l’éditeur Visual Basic. ## Exercices d'application corrigés **Exercice 1 – Recherche de prix** Une feuille « Articles » contient en colonne A les références (texte), en colonne B les désignations, en colonne C les prix unitaires (FCFA). En feuille « Devis », la cellule A2 contient une référence saisie par l’utilisateur. *Consigne* : Écrire une formule qui renvoie le prix unitaire correspondant à la référence de A2, ou affiche « Introuvable » si la référence n’existe pas. **Corrigé** : `=SIERREUR(RECHERCHEV(A2;Articles!A:C;3;FAUX);"Introuvable")` Explication : RECHERCHEV cherche la référence dans la colonne A de la feuille Articles, renvoie la troisième colonne (prix). SIERREUR gère le cas où aucune correspondance n’est trouvée. **Exercice 2 – Tableau croisé dynamique des ventes** Le tableau « Ventes2023 » comporte les colonnes : Date (jj/mm/aaaa), Région, Produit, Quantité, PrixUnitaire. *Consigne* : Créer un TCD qui montre, pour chaque mois, le chiffre d’affaires total (Quantité × PrixUnitaire) par région. **Corrigé** : 1. Ajouter une colonne « CA » avec la formule `=[@Quantité]*[@PrixUnitaire]`. 2. Sélectionner une cellule du tableau → Insertion → Tableau croisé dynamique → nouvelle feuille. 3. Dans le volet des champs : - Glisser **Date** → **Étiquettes de colonnes**, puis cliquer droit sur une date → **Grouper** → choisir **Mois** (et éventuellement **Année**). - Glisser **Région** → **Étiquettes de lignes**. - Glisser **CA** → **Valeurs**, vérifier que le réglage est **Somme de CA**. Le TCD affiche le chiffre d’affaires mensuel par région. **Exercice 3 – Macro de mise en forme d’un état de compte** Un état de compte possède les colonnes A à E : Date, Libellé, Débit, Crédit, Solde. On veut que la ligne d’en-tête soit en gras, fond bleu clair, et que les colonnes Débit et Crédit soient au format monétaire avec deux décimales. **Corrigé (code VBA)** : ```vba Sub MiseEnFormeEtatCompte() With Feuil1.Range("A1:E1") .Font.Bold = True .Interior.Color = RGB(173, 216, 230) ' bleu clair End With Feuil1.Range("C2:C1000").NumberFormat = "#,##0,00 FCFA" Feuil1.Range("D2:D1000").NumberFormat = "#,##0,00 FCFA" End Sub ``` Après enregistrement de la macro, l’exécution applique la mise en forme souhaitée. ## Erreurs fréquentes et règles de sécurité | Erreur fréquente | Conséquence | Règle de prévention / correction | |------------------|-------------|----------------------------------| | Oublier de définir la recherche comme exacte (dernier argument VRAI au lieu de FAUX) dans RECHERCHEV | Retourne une valeur approximative pouvant être erronée | Toujours mettre **FAUX** (ou 0) pour une recherche exacte, sauf si l’on recherche une plage de valeurs. | | Placer un champ numérique dans les **Étiquettes de lignes** d’un TCD sans le regrouper | Le TCD affiche chaque valeur individuellement, rendant la lecture impossible | Regrouper les valeurs numériques (clic droit → Groupe) ou utiliser le champ comme **Valeur** (somme, moyenne). | | Enregistrer une macro qui supprime des feuilles sans confirmation | Perte irréversible de données si la macro est exécutée par erreur | Ajouter une boîte de confirmation (`MsgBox`) avant toute suppression, ou travailler sur une copie de secours. | | Ne pas déverrouiller les cellules devant rester éditables avant de protéger la feuille | L’utilisateur ne peut plus modifier aucune cellule, y compris celles devant être saisies | Sélectionner les cellules à laisser libres → Format de cellule → Protection → décocher **Verrouillé** avant de protéger la feuille. | | Utiliser des macros provenant d’Internet sans vérifier le code | Risque d’exécution de code malveillant (virus, ransomware) | Désactiver l’exécution automatique des macros (Centre de confiance) et n’activer que celles provenant de sources fiables ou après inspection du code VBA. | | Confondre **Slicer** et **Filtre de champ** lors de la présentation | Le slicer ne fonctionne pas parce qu’il n’est pas lié au TCD | Vérifier que le slicer a été créé à partir du TCD voulu (clic droit sur un champ du TCD → Ajouter un slicer). | --- *Fin du cours.*