PRATIQUE MICROSOFT EXCEL (partie 1/3)

## Objectifs et prérequis **Objectifs** : À l’issue de ce chapitre, l’élève sera capable de : - créer un tableau Excel adapté à une situation de gestion (stock, facturation, suivi de chantier) ; - appliquer une mise en forme conditionnelle et des styles de cellule pour améliorer la lisibilité ; - saisir, vérifier et corriger des données numériques et textuelles dans un tableau ; - utiliser les fonctions usuelles (SOMME, MOYENNE, PRODUIT, QUOTIENT) pour réaliser des calculs de base ; - mettre en œuvre les fonctions avancées (JOURS360, ENT, ARRONDI, CONCATENER, GAUCHE, DROITE, ET, OU, MAX, MIN, LIGNE, RANG) afin de résoudre des problèmes de gestion courante (durée de contrat, arrondi financier, extraction de sous‑chaînes, critères multiples, classement) ; - interpréter les résultats obtenus et les présenter sous forme de rapport synthétique. **Prérequis** : maîtriser l’environnement Windows, savoir ouvrir Excel, connaître les notions de cellule, de plage, de référence relative et absolue, ainsi que les opérations arithmétiques de base. > **À retenir** : un tableau bien structuré constitue la base de toute analyse de données en gestion. ## Introduction contextualisée Dans les entreprises camerounaises, qu’il s’agisse d’une PME de transformation agroalimentaire à Douala, d’un bureau d’études de BTP à Yaoundé ou d’un service comptable d’une ONG, le tableur Microsoft Excel reste l’outil privilégié pour la collecte, le traitement et la présentation des informations chiffrées. La maîtrise de sa création, de sa mise en forme et de ses fonctions permet de gagner en productivité, de réduire les erreurs de saisie et de produire des tableaux de bord fiables pour la prise de décision. Ce chapitre s’inscrit dans la logique du référentiel de compétences du Probatoire technique : il passe de la manipulation élémentaire du tableur à l’application de fonctions spécifiques aux métiers de la gestion. ## I. Leçon 13 : Création et mise en forme d’un tableau ### A. Créer un tableau 1. **Lancer Excel** et choisir un classeur vierge. 2. **Définir la zone** : sélectionner la première cellule (ex. A1) puis étirer la sélection jusqu’à la dernière cellule souhaitée (ex. E20) pour obtenir un tableau de 5 colonnes × 20 lignes. 3. **Convertir en tableau** : avec la plage sélectionnée, aller dans l’onglet *Insertion* → *Tableau* (ou Ctrl+T). Cocher « Mon tableau comporte des en‑têtes » si la première ligne contient les libellés de colonne. Excel attribue alors un nom de tableau (ex. Tableau1) et ajoute des filtres automatiques. > **À retenir** : la conversion en tableau permet l’extension automatique des formules et des formats lorsqu’on ajoute une nouvelle ligne ou colonne. ### B. Mettre en forme un tableau - **Styles prédéfinis** : onglet *Création* → choisir un style (ex. *Style clair 9*) qui applique des bandes de couleur, du gras aux en‑têtes et des bordures. - **Mise en forme conditionnelle** : sélectionner la colonne des montants (ex. D2:D20) → *Accueil* → *Mise en forme conditionnelle* → *Nouvelle règle* → « Format des cellules qui contiennent » → valeur > 500 000 FCFA → remplissage vert clair. Cela souligne les factures élevées. - **Ajuster la largeur des colonnes** : double‑clic sur le bord droit de l’en‑tête pour adapter au contenu, ou saisir une largeur précise (ex. 12) via *Format* → *Largeur de colonne*. - **Geler les volets** : pour garder les en‑têtes visibles lors du défilement, *Affichage* → *Geler les volets* → *Geler la première ligne*. > **À retenir** : une mise en forme cohérente améliore la lisibilité et réduit les risques d’interprétation erronée lors de la présentation à un supérieur ou à un client. ### C. Saisir les données dans un tableau 1. **Saisie directe** : cliquer dans une cellule et taper la valeur (texte, nombre, date). 2. **Utilisation de la poignée de recopie** : après avoir saisi une formule ou une suite (ex. 1,2,3…), tirer la petite croix en bas‑à‑droite pour recopier vers le bas ou la droite. 3. **Validation des données** : onglet *Données* → *Validation* → autoriser uniquement des nombres entiers entre 0 et 10 000 pour la colonne *Quantité* afin d’éviter les saisies aberrantes. 4. **Vérification** : activer *Rechercher* (Ctrl+F) pour repérer les doublons ou les valeurs manquantes. > **À retenir** : la validation des données constitue une première barrière contre les erreurs de saisie qui peuvent fausser les calculs ultérieurs. ## II. Leçon 14 : Les fonctions simples ### A. Fonctions usuelles : SOMME, MOYENNE, PRODUIT, QUOTIENT | Fonction | Syntaxe | Exemple (FCFA) | Résultat | |----------|---------|----------------|----------| | SOMME | =SOMME(plage) | =SOMME(C2:C20) | total des ventes | | MOYENNE | =MOYENNE(plage) | =MOYENNE(D2:D20) | moyenne des factures | | PRODUIT | =PRODUIT(nombre1;nombre2;…) | =PRODUIT(B2;C2) | quantité × prix unitaire | | QUOTIENT | =QUOTIENT(numérateur;dénominateur) | =QUOTIENT(D2;B2) | prix unitaire obtenu | > **À retenir** : les fonctions simples travaillent sur des plages de cellules et retournent une valeur unique qui peut être réutilisée dans d’autres formules. ### B. Syntaxe et utilisation - **Références relatives** : lorsqu’on copie =SOMME(C2:C20) de la ligne 2 vers la ligne 3, Excel ajuste automatiquement en =SOMME(C3:C21). - **Références absolues** : pour fixer une cellule (ex. taux de TVA en $G$1), on utilise le signe $ avant la lettre de colonne et le numéro de ligne. - **Imbrication** : on peut combiner fonctions, ex. =MOYENNE(SI(C2:C20>0;D2:D20)) (formule matricielle, validée avec Ctrl+Maj+Entrée dans les versions antérieures à 365). ### C. Exemple professionnel **Situation** : un responsable de stock à Bamenda doit calculer la valeur totale du stock de ciment (colonne B = quantité en sacs, colonne C = prix unitaire en FCFA). 1. En D2 saisir : `=PRODUIT(B2;C2)` → valeur du stock ligne 2. 2. Recopier la formule jusqu’à D20. 3. En D21 saisir : `=SOMME(D2:D20)` → valeur totale du stock. 4. En D22 saisir : `=MOYENNE(D2:D20)` → valeur moyenne par ligne. Résultat obtenu (exemple chiffré) : - Somme = 12 450 000 FCFA - Moyenne = 622 500 FCFA > **À retenir** : la combinaison de PRODUIT et SOMME permet de passer rapidement du détail ligne‑à‑ligne au total agrégé, indispensable pour les bilans de stock. ## III. Leçon 15 : Les fonctions avancées ### A. Fonctions de date : JOURS360 - **Objectif** : calculer le nombre de jours entre deux dates selon la convention 30/360 (utilisée en comptabilité financière). - **Syntaxe** : `=JOURS360(date_debut;date_fin;[méthode])` - **Exemple** : contrat de location du 15/02/2024 au 15/08/2024 → `=JOURS360("15/02/2024";"15/08/2024")` renvoie 180 jours. > **À retenir** : JOURS360 évite les écarts dus aux mois de longueur variable, ce qui est essentiel pour le calcul d’intérêts ou de loyers. ### B. Fonctions d’arrondi : ENT, ARRONDI - **ENT** : renvoie le plus grand entier inférieur ou égal au nombre. Syntaxe : `=ENT(nombre)`. Exemple : `=ENT(123,78)` → 123. - **ARRONDI** : arrondit à un nombre spécifié de décimales. Syntaxe : `=ARRONDI(nombre;nb_decimales)`. Exemple : `=ARRONDI(123,786;2)` → 123,79. > **À retenir** : ENT est utile pour extraire la partie entière d’un taux, tandis qu’ARRONDI respecte les normes comptables d’arrondi à la monnaie. ### C. Fonctions de texte : CONCATENER, GAUCHE, DROITE - **CONCATENER** (ou `&`) : assemble plusieurs chaînes. Syntaxe : `=CONCATENER(texte1;texte2;…)`. Exemple : `=CONCATENER(A2;" ";B2)` → « Nom Prénom ». - **GAUCHE** : renvoie les n premiers caractères. Syntaxe : `=GAUCHE(texte;nb_caractères)`. Exemple : `=GAUCHE("CAMEROUN";3)` → « CAM ». - **DROITE** : renvoie les n derniers caractères. Syntaxe : `=DROITE(texte;nb_caractères)`. Exemple : `=DROITE("Facture2024";4)` → « 2024 ». > **À retenir** : ces fonctions permettent de créer des références de documents (numéros de facture, codes article) à partir de données séparées. ### D. Fonctions logiques : ET, OU - **ET** : retourne VRAI si tous les arguments sont vrais. Syntaxe : `=ET(condition1;condition2;…)`. - **OU** : retourne VRAI si au moins un argument est vrai. Syntaxe : `=OU(condition1;condition2;…)`. **Exemple** : valider qu’une facture est payable (montant > 0 ET date d’échéance ≤ aujourd’hui). `=ET(D2>0;E2<=AUJOURDHUI())` > **À retenir** : combiner ET et OU avec la fonction SI permet de créer des règles de décision complexes (ex. application d’une remise seulement si le client est fidèle ET le montant dépasse un seuil). ### E. Fonctions statistiques : MAX, MIN, LIGNE, RANG - **MAX** : renvoie la plus grande valeur d’une plage. `=MAX(plage)`. - **MIN** : renvoie la plus petite valeur. `=MIN(plage)`. - **LIGNE** : renvoie le numéro de ligne d’une référence. `=LIGNE([référence])`. Sans argument, donne le numéro de la ligne courante. - **RANG** : renvoie le rang d’un nombre dans une liste, en ordre décroissant par défaut. Syntaxe : `=RANG(nombre;plage;[ordre])`. **Exemple** : déterminer le meilleur vendeur du mois. - En F2 : `=MAX(C2:C20)` → meilleur chiffre d’affaires. - En G2 : `=RANG(C2;$C$2:$C$20;0)` → rang de chaque vendeur. - En H2 : `=INDEX(A$2:A$20;EQUIV(G2;$C$2:$C$20;0))` → nom du vendeur classé. > **À retenir** : LIGNE et RANG sont souvent utilisés ensemble pour créer des classements dynamiques sans recourir à des tableaux croisés dynamiques. ### F. Exemple intégré : suivi d’un chantier de construction **Données** : colonne A = Numéro de bon de commande, B = Date de début, C = Date de fin prévue, D = Coût prévisionnel (FCFA), E = Coût réel (FCFA). 1. **Durée prévue (jours)** : en F2 saisir `=JOURS360(B2;C2)`. Recopier. 2. **Écart de coût** : en G2 saisir `=E2-D2`. Recopier. 3. **Alertes de dépassement** : en H2 saisir `=SI(G2>0; "Dépassement"; "OK")`. Recopier. 4. **Coût total prévu** : en D21 `=SOMME(D2:D20)`. 5. **Coût total réel** : en E21 `=SOMME(E2:E20)`. 6. **Pourcentage de dépassement global** : en F21 `=SI(D21=0;0;(E21-D21)/D21)`. Appliquer un format pourcentage. 7. **Classement des bons de commande par écart** : en I2 saisir `=RANG(G2;$G$2:$G$20;0)` (plus grand écart en premier). Recopier. Résultat interprété : le bon de commande n° 7 présente le plus grand dépassement (+ 1 200 000 FCFA) et nécessite une révision du budget. > **À retenir** : l’association de fonctions de date, de calcul, logique et de classement permet de transformer un simple tableau de saisie en un outil de pilotage de projet. ## Synthèse du chapitre - Créer un tableau → sélection → Insertion → Tableau (Ctrl+T). - Mettre en forme → styles, mise en forme conditionnelle, gel des volets, ajustement largeur. - Saisir des données → entrée directe, poignée de recopie, validation, vérification. - Fonctions simples : SOMME, MOYENNE, PRODUIT, QUOTIENT (syntaxe, références relatives/absolues). - Fonctions avancées : - JOURS360 (jours selon 30/360) - ENT (partie entière) & ARRONDI (arrondi à n décimales) - CONCATENER, GAUCHE, DROITE (manipulation de texte) - ET, OU (tests logiques) - MAX, MIN, LIGNE, RANG (statistiques et classement) - Exemple intégré : suivi de chantier (durée, écarts, alertes, totaux, classement). - Bonnes pratiques : nommer les plages, utiliser des références absolues quand nécessaire, appliquer la validation des données, documenter les formules. ## Glossaire | Terme | Définition | |-------|------------| | Tableau Excel | Plage de cellules convertie en objet structuré avec filtres et références structurées. | | Mise en forme conditionnelle | Règle qui modifie l’apparence d’une cellule selon sa valeur ou une formule. | | Référence absolue | Référence de cellule précédée du symbole $ (ex. $G$1) qui ne change pas lors de la copie. | | Fonction SOMME | Additionne toutes les valeurs d’une plage numérique. | | Fonction JOURS360 | Calcule le nombre de jours entre deux dates selon la convention 30/360. | | Fonction ENT | Retourne le plus grand entier inférieur ou égal à un nombre donné. | | Fonction CONCATENER | Assemble plusieurs chaînes de caractères en une seule. | | Fonction ET | Retourne VRAI seulement si toutes les conditions évaluées sont vraies. | | Fonction RANG | Indique la position d’une valeur dans une liste, selon un ordre spécifié. | | Validation des données | Outil qui restreint le type ou la plage de valeurs pouvant être saisies dans une cellule. | | Gel des volets | Fonction qui garde visibles certaines lignes ou colonnes lors du défilement du tableau. | | Recopie incrémentée | Action de tirer la poignée de remplissage pour reproduire une valeur ou une formule avec adaptation automatique des références. | ## Exercices d'application corrigés ### Exercice 1 – Suivi de facturation **Consigne** : Une entreprise de fournitures de bureau à Yaoundé souhaite suivre ses factures du mois de mars. Créer un tableau avec les colonnes suivantes : A : Numéro de facture, B : Date de facturation, C : Client, D : Montant HT (FCFA), E : TVA (18 %), F : Montant TTC. 1. Saisir les données suivantes (5 lignes) : | A | B | C | D | E | F | |---|---|---|---|---|---| | F001 | 02/03/2024 | ABC SARL | 150 000 | | | | F002 | 05/03/2024 | XYZ Ltd | 230 000 | | | | F003 | 09/03/2024 | DEF SARL | 95 000 | | | | F004 | 14/03/2024 | GHI SARL | 310 000 | | | | F005 | 20/03/2024 | JKL Ltd | 180 000 | | | 2. En colonne E, calculer la TVA (18 % du HT). 3. En colonne F, calculer le Montant TTC (HT + TVA). 4. En bas de colonne D, calculer le total HT. 5. En bas de colonne F, calculer le total TTC. 6. Appliquer une mise en forme conditionnelle qui met en rouge les factures dont le Montant TTC dépasse 250 000 FCFA. **Corrigé** : - E2 : `=D2*0,18` → recopier jusqu’à E6. - F2 : `=D2+E2` → recopier jusqu’à F6. - D7 : `=SOMME(D2:D6)` → 965 000 FCFA. - F7 : `=SOMME(F2:F6)` → 1 138 700 FCFA. - Mise en forme conditionnelle : sélectionner F2:F6 → Nouvelle règle → « Format des cellules qui contiennent » → valeur > 250 000 → remplissage rouge clair. ### Exercice 2 – Calcul d’intérêts avec JOURS360 **Consigne** : Un prêt de 2 000 000 FCFA est consenti le 10/01/2024 à un taux annuel de 6 %. Le remboursement intervient le 10/07/2024. Calculer les intérêts dus selon la méthode 30/360. **Étapes** : 1. Calculer la durée en jours avec JOURS360. 2. Convertir le taux annuel en taux périodique (taux × jours/360). 3. Calculer les intérêts : Capital × taux périodique. **Corrigé** : - B2 (date de début) : 10/01/2024 - B3 (date de fin) : 10/07/2024 - C2 : `=JOURS360(B2;B3)` → 180 jours. - D2 (taux annuel) : 6 % → 0,06. - E2 : `=D2*C2/360` → 0,06*180/360 = 0,03 (3 %). - F2 (capital) : 2 000 000. - G2 : `=F2*E2` → 2 000 000 * 0,03 = 60 000 FCFA d’intérêts. ### Exercice 3 – Extraction de code article **Consigne** : Dans un fichier de gestion de stock, la colonne A contient des références du type « ART‑2024‑0015 ». Il faut extraire l’année (les 4 caractères situés après le deuxième tiret) et le numéro séquentiel (les 4 derniers chiffres). **Étapes** : 1. Extraire l’année avec GAUCHE et STXT (ou GAUCHE après recherche). 2. Extraire le numéro avec DROITE. 3. Concaténer année et numéro sous la forme « 2024‑0015 ». **Corrigé** (en supposant que la référence se trouve en A2) : - B2 (année) : `=STXT(A2;5;4)` → renvoie « 2024 ». - C2 (numéro) : `=DROITE(A2;4)` → renvoie « 0015 ». - D2 (code souhaité) : `=CONCATENER(B2;"-";C2)` → « 2024‑0015 ». ## Erreurs fréquentes et règles de sécurité | N° | Erreur fréquente | Conséquence | Règle de prévention / bonne pratique | |----|------------------|-------------|--------------------------------------| | 1 | Oublier de convertir une plage en tableau avant d’ajouter des lignes | Les formules ne s’étendent pas automatiquement → résultats incohérents | Toujours transformer la zone de saisie en tableau (Ctrl+T) dès la première saisie. | | 2 | Utiliser des références relatives lorsqu’une valeur fixe est requise (ex. taux de TVA) | Lors de la copie, la référence se décale et le calcul devient faux | Verrouiller les références absolues avec $ (ex. $G$1) pour les constantes. | | 3 | Saisir du texte dans une colonne prévue pour des nombres (ex. mettre « N/A » au lieu de 0) | Les fonctions SOMME, MOYENNE renvoient une erreur #VALEUR! | Appliquer une validation des données autorisant uniquement des nombres ou laisser zéro pour les valeurs manquantes. | | 4 | Ne pas mettre à jour la mise en forme conditionnelle après l’ajout de nouvelles lignes | Les nouvelles valeurs ne sont pas contrôlées visuellement → risque de dépassement non détecté | Définir la règle sur toute la colonne (ex. F:F) plutôt que sur une plage fixe. | | 5 | Confondre les fonctions ENT et ARRONDI lorsqu’on travaille avec des monnaies | ENT tronque systématiquement, pouvant sous‑estimer un montant ; ARRONDI mal utilisé peut sur‑estimer | Utiliser ARRONDI avec 2 décimales pour les monnaies ; réserver ENT à l’extraction de parties entières (ex. nombre de mois). | | 6 | Oublier de protéger la feuille contenant les formules clés | Un utilisateur peut écraser accidentellement une formule | Verrouiller les cellules contenant les formules (onglet Révision → Protéger la feuille) et laisser uniquement les cellules de saisie déverrouillées. | --- *Fin du cours.*