Module 5 : Gestion des Tableaux et Mise en Forme Conditionnelle
Introduction
Bienvenue dans le module 5 de notre formation Excel. Après avoir maîtrisé les bases des formules et fonctions, nous allons maintenant explorer deux fonctionnalités essentielles qui vont transformer votre façon de travailler avec les données : les tableaux structurés et la mise en forme conditionnelle.
Le travail avec les données brutes peut rapidement devenir complexe et désorganisé. Les tableaux structurés d'Excel offrent une solution élégante pour organiser, filtrer et analyser vos informations. Quant à la mise en forme conditionnelle, elle vous permet de visualiser instantanément les tendances et les valeurs importantes dans vos données sans avoir à les parcourir ligne par ligne.
Dans ce module, vous apprendrez à transformer vos plages de données en tableaux intelligents, à les manipuler efficacement et à faire ressortir visuellement les informations critiques grâce à la mise en forme conditionnelle.
FICHE PRATIQUE : TABLEAUX ET MISE EN FORME CONDITIONNELLE
I. CRÉER ET GÉRER DES TABLEAUX STRUCTURÉS
Qu'est-ce qu'un tableau structuré ?
Un tableau structuré (ou "Table" en anglais) est bien plus qu'une simple plage de cellules. C'est une structure de données intelligente avec des fonctionnalités intégrées :
- En-têtes de colonnes automatiquement reconnus et toujours visibles pendant le défilement
- Filtrage et tri instantanés pour chaque colonne
- Mise en forme automatique avec des styles prédéfinis
- Formules de tableau qui s'ajustent automatiquement lorsque le tableau est redimensionné
- Lignes de total avec accès rapide aux fonctions comme SOMME, MOYENNE, etc.
- Références simplifiées dans les formules (noms de colonnes plutôt que références de cellules)
Créer un tableau structuré
- Préparez vos données avec des en-têtes de colonnes clairs sur la première ligne
- Sélectionnez toute la plage de données, y compris les en-têtes
- Utilisez l'une des méthodes suivantes :
- Cliquez sur "Tableau" dans l'onglet Insertion
- Utilisez le raccourci Ctrl + T
- Cliquez sur "Mettre sous forme de tableau" dans l'onglet Accueil (groupe Styles)
- Confirmez la plage et indiquez si votre tableau comporte des en-têtes
- Choisissez un style de tableau dans la galerie proposée
Personnaliser un tableau structuré
Une fois le tableau créé, vous pouvez le personnaliser via l'onglet "Création de tableau" qui apparaît automatiquement :
- Nom du tableau : Donnez un nom significatif à votre tableau pour y faire référence facilement
- Options de style : Activez/désactivez les lignes à bandes, la ligne d'en-tête, la ligne de total, etc.
- Styles de tableau : Choisissez parmi une variété de styles prédéfinis
- Outils : Insérez des segments pour filtrer visuellement votre tableau
Redimensionner un tableau
Pour modifier la taille d'un tableau existant :
- Méthode 1 : Faites glisser la poignée de redimensionnement (coin inférieur droit du tableau)
- Méthode 2 : Utilisez l'option "Redimensionner le tableau" dans l'onglet "Création de tableau"
- Ajout automatique : Tapez dans la cellule juste en dessous de la dernière ligne du tableau pour l'étendre automatiquement
II. FILTRER ET TRIER LES DONNÉES
Utiliser les filtres de tableau
Les tableaux structurés intègrent des filtres automatiques pour chaque colonne :
- Activer/désactiver les filtres : Cochez/décochez "Filtrer" dans l'onglet "Création de tableau"
- Filtrer une colonne : Cliquez sur la flèche dans l'en-tête de colonne pour afficher le menu de filtre
- Options de filtre :
- Filtres textuels : Contient, Commence par, Se termine par, etc.
- Filtres numériques : Supérieur à, Inférieur à, Entre, etc.
- Filtres de date : Mois, Trimestre, Année, Avant, Après, etc.
- Filtres par valeur : Cochez/décochez les valeurs à afficher
- Filtres combinés : Appliquez des filtres sur plusieurs colonnes pour affiner les résultats
- Effacer les filtres : Cliquez sur "Effacer" dans le menu de filtre ou sur "Effacer les filtres" dans l'onglet Données
Trier les données du tableau
Le tri permet de réorganiser vos données selon un ou plusieurs critères :
- Tri rapide : Cliquez sur les boutons "Trier de A à Z" ou "Trier de Z à A" dans le menu de filtre
- Tri multicritère : Utilisez l'option "Trier par couleur" ou "Plus d'options de tri..." dans le menu de filtre
- Tri personnalisé : Utilisez la fenêtre "Trier" accessible via l'onglet Données ou le menu de filtre pour définir plusieurs niveaux de tri
- Tri par couleur : Triez en fonction des couleurs de cellule, de police ou d'icônes conditionnelles
- Ordre personnalisé : Créez des listes de tri personnalisées pour des séquences non alphabétiques (ex : jours de la semaine, mois)
Rechercher dans un tableau
Pour trouver rapidement des informations spécifiques :
- Recherche simple : Utilisez Ctrl + F pour ouvrir la boîte de dialogue "Rechercher"
- Recherche avancée : Utilisez l'option "Recherche avancée" dans l'onglet Accueil (groupe Édition) pour des recherches plus complexes
- Filtrage instantané : Utilisez les segments (disponibles dans l'onglet "Création de tableau") pour filtrer visuellement votre tableau
III. UTILISER LA MISE EN FORME CONDITIONNELLE
Principes de la mise en forme conditionnelle
La mise en forme conditionnelle permet de modifier automatiquement l'apparence des cellules selon leur contenu :
- Modifie automatiquement les couleurs, les polices, les bordures ou ajoute des icônes selon des règles prédéfinies
- Met en évidence visuellement les tendances, les exceptions ou les seuils dans vos données
- S'adapte dynamiquement : la mise en forme change automatiquement lorsque les données sont modifiées
- Peut être appliquée à des cellules individuelles, des plages ou des tableaux entiers
Ajouter une mise en forme conditionnelle
- Sélectionnez les cellules auxquelles vous souhaitez appliquer la mise en forme
- Dans l'onglet Accueil, cliquez sur "Mise en forme conditionnelle" (groupe Style)
- Choisissez le type de règle à appliquer :
- Mise en surbrillance des cellules
- Règles des valeurs supérieures/inférieures
- Barres de données
- Nuances de couleurs
- Jeux d'icônes
- Configurez les paramètres de la règle (seuils, couleurs, etc.)
- Cliquez sur "OK" pour appliquer
Types de règles courantes
| Type de règle | Description | Exemple d'utilisation |
|---|---|---|
| Supérieur à / Inférieur à | Met en forme les cellules dont la valeur est supérieure/inférieure à un seuil | Mettre en rouge les ventes inférieures à l'objectif |
| Entre / En dehors | Met en forme les cellules dont la valeur est comprise dans une plage ou en dehors | Mettre en jaune les valeurs entre 90% et 110% de l'objectif |
| Texte contenant | Met en forme les cellules contenant un texte spécifique | Mettre en évidence tous les produits "Premium" |
| Date | Met en forme les cellules selon une date ou une période | Mettre en rouge les dates d'échéance dépassées |
| Doublons | Met en forme les valeurs répétées | Identifier les entrées en double dans une liste de clients |
| Top 10 / Bottom 10 | Met en forme les N valeurs les plus élevées/basses | Mettre en évidence les 5 meilleurs vendeurs du mois |
| Moyenne | Met en forme les cellules au-dessus/en dessous de la moyenne | Identifier les performances supérieures à la moyenne |
| Formule | Met en forme les cellules selon une formule personnalisée | Conditions complexes impliquant plusieurs critères |
Visualisations conditionnelles avancées
- Barres de données : Ajoute une barre horizontale dans chaque cellule proportionnelle à sa valeur
- Nuances de couleurs : Applique un dégradé de couleurs en fonction des valeurs (utile pour visualiser des distributions)
- Jeux d'icônes : Ajoute des icônes qui changent selon les valeurs (flèches, feux tricolores, symboles, etc.)
- Règles basées sur des formules : Permet de créer des conditions complexes avec des formules personnalisées
Gérer les règles de mise en forme
- Voir toutes les règles : Utilisez l'option "Gérer les règles" dans le menu Mise en forme conditionnelle
- Modifier : Ajustez les paramètres d'une règle existante
- Supprimer : Effacez une ou toutes les règles
- Priorité : Réorganisez l'ordre d'application des règles en cas de chevauchement
- Étendue d'application : Modifiez la plage à laquelle une règle s'applique
- Arrêter si vrai : Empêche l'application des règles de priorité inférieure si la condition est remplie
IV. COMBINER TABLEAUX ET MISE EN FORME CONDITIONNELLE
Tableaux de bord visuels
La combinaison des tableaux structurés et de la mise en forme conditionnelle permet de créer des tableaux de bord puissants :
- Appliquez une mise en forme conditionnelle à l'ensemble de votre tableau pour une analyse visuelle instantanée
- Utilisez les filtres pour isoler un sous-ensemble de données et voir comment la mise en forme conditionnelle s'ajuste
- Ajoutez une ligne de total avec des calculs dynamiques qui s'adaptent au filtrage
- Intégrez des segments pour filtrer visuellement et voir les impacts sur les indicateurs mis en forme
- Combinable avec les mini-graphiques pour une visualisation encore plus riche
Export et partage
Points à connaître pour partager vos tableaux avec mise en forme conditionnelle :
- La mise en forme conditionnelle est préservée lors de l'enregistrement au format Excel (.xlsx)
- Lors de l'export au format PDF, l'apparence visuelle est maintenue
- Pour les présentations, vous pouvez figer les volets pour garder les en-têtes visibles
- Pensez à verrouiller les cellules importantes si vous partagez un fichier éditable
- Pour partager uniquement les données filtrées, copiez-les dans une nouvelle feuille ou utilisez l'option "Copier uniquement les cellules visibles"
Bonnes pratiques
- Simplicité : Évitez de surcharger vos tableaux avec trop de mises en forme conditionnelles différentes
- Cohérence : Utilisez les mêmes schémas de couleurs pour des significations similaires
- Accessibilité : Pensez aux utilisateurs daltoniens en ne vous fiant pas uniquement aux couleurs (utilisez des icônes ou des motifs)
- Documentation : Incluez une légende explicative des codes couleurs et des règles utilisées
- Maintenance : Vérifiez périodiquement que les règles conditionnelles sont toujours pertinentes
- Modèles : Enregistrez vos tableaux bien conçus comme modèles pour une réutilisation future
Créer et personnaliser des tableaux structurés
Les tableaux structurés d'Excel représentent une évolution majeure par rapport aux simples plages de cellules. Ils offrent une structure intelligente qui simplifie considérablement la manipulation et l'analyse des données.
Pour convertir vos données en tableau structuré :
- Préparez vos données :
- Assurez-vous que vos données sont bien organisées
- Vérifiez que la première ligne contient des en-têtes de colonnes clairs
- Éliminez les lignes ou colonnes vides à l'intérieur de votre plage
- Sélectionnez toute la plage de données à convertir
- Transformez en tableau via l'une de ces méthodes :
- Onglet Insertion > bouton "Tableau"
- Raccourci Ctrl + T
- Onglet Accueil > Styles > "Mettre sous forme de tableau"
- Configurez la boîte de dialogue :
- Vérifiez que la plage proposée est correcte
- Cochez l'option "Mon tableau comporte des en-têtes" si c'est le cas
- Choisissez un style parmi les options proposées
Astuce : La sélection d'une seule cellule à l'intérieur d'une plage de données cohérente permet souvent à Excel de détecter automatiquement toute la plage lors de la création d'un tableau. Vérifiez toujours la plage proposée dans la boîte de dialogue de création de tableau.
Une fois votre plage convertie en tableau, vous remarquerez plusieurs changements immédiats : un style appliqué automatiquement, des flèches de filtre dans les en-têtes, et l'apparition d'un nouvel onglet contextuel "Création de tableau" dans le ruban.
Par défaut, Excel attribue des noms génériques comme "Tableau1", "Tableau2", etc. Pour une meilleure organisation :
- Sélectionnez n'importe quelle cellule dans votre tableau
- Dans l'onglet "Création de tableau", localisez la zone "Nom du tableau" (à gauche)
- Remplacez le nom par défaut par un nom significatif (sans espaces, ex : "VentesTrimestrielles")
- Appuyez sur Entrée pour confirmer
Ce nom personnalisé vous permettra de faire référence facilement à votre tableau dans des formules.
Personnalisez l'apparence de votre tableau selon vos besoins :
- Options de style de tableau (section centrale de l'onglet) :
- Ligne d'en-tête : active/désactive la mise en forme spéciale pour la première ligne
- Ligne de total : ajoute/supprime une ligne de total en bas du tableau
- Lignes à bandes/Colonnes à bandes : alterne les couleurs pour améliorer la lisibilité
- Première colonne/Dernière colonne : applique une mise en forme distincte à ces colonnes
- Styles de tableau (à droite) : choisissez parmi une galerie de styles prédéfinis
Pour ajuster la taille de votre tableau après sa création :
- Méthode manuelle : Faites glisser la petite poignée de redimensionnement dans le coin inférieur droit du tableau
- Via le ruban : Dans l'onglet "Création de tableau", cliquez sur "Redimensionner le tableau" et ajustez les références
- Extension automatique : Tapez simplement dans la cellule immédiatement en dessous de la dernière ligne du tableau, et Excel étendra automatiquement le tableau pour inclure cette nouvelle entrée
Attention : Si vous supprimez accidentellement l'en-tête ou les données d'un tableau, vous risquez de perdre sa structure. Utilisez Ctrl + Z immédiatement pour annuler. Pour supprimer intentionnellement un tableau tout en conservant les données, cliquez sur "Convertir en plage" dans l'onglet "Création de tableau".
La ligne de total est particulièrement utile pour les analyses rapides :
- Cochez "Ligne de total" dans les options de style de l'onglet "Création de tableau"
- Une ligne "Total" apparaît en bas du tableau
- Cliquez sur une cellule de cette ligne sous la colonne pour laquelle vous voulez un calcul
- Un menu déroulant apparaît, vous permettant de choisir la fonction à appliquer (Somme, Moyenne, Nombre, etc.)
La ligne de total est dynamique : elle recalcule automatiquement lorsque vous filtrez les données.
Filtrer et trier efficacement vos données
L'un des principaux avantages des tableaux structurés est leur capacité à filtrer et trier rapidement les données sans modifier la source originale. Ces fonctionnalités vous permettent d'isoler précisément les informations pertinentes pour votre analyse.
Les filtres vous permettent d'afficher uniquement les lignes qui correspondent à des critères spécifiques :
- Accéder aux options de filtre :
- Cliquez sur la flèche déroulante dans l'en-tête de la colonne que vous souhaitez filtrer
- Un menu contextuel s'affiche avec toutes les options de filtrage
- Types de filtrage disponibles :
- Filtrage par valeurs : Cochez/décochez les cases correspondant aux valeurs à afficher/masquer
- Filtres textuels : Pour les colonnes contenant du texte (Commence par, Contient, etc.)
- Filtres numériques : Pour les colonnes numériques (Supérieur à, Entre, Top 10, etc.)
- Filtres de date : Pour les colonnes de dates (Demain, Mois prochain, Cette année, etc.)
- Filtres par couleur : Si vous avez appliqué des couleurs ou une mise en forme conditionnelle
- Filtres avancés :
- Sélectionnez "Filtres textuels/numériques/de dates" pour accéder à des options plus précises
- Créez des conditions personnalisées comme "supérieur à 1000 ET inférieur à 5000"
- Gérer les filtres multiples :
- Appliquez des filtres sur plusieurs colonnes pour affiner davantage les résultats
- Les filtres sont cumulatifs : chaque nouveau filtre s'applique aux résultats déjà filtrés
- Supprimer les filtres :
- Pour une colonne spécifique : cliquez sur la flèche et sélectionnez "Effacer le filtre"
- Pour toutes les colonnes : dans l'onglet Données, cliquez sur "Effacer"
Avant le filtrage :
Un tableau contenant 100 transactions de différentes régions, avec des montants variés et des dates étalées sur une année
Après le filtrage :
Uniquement les transactions de la région "Est" supérieures à 5000€ effectuées au premier trimestre
Lorsque des filtres sont actifs, les numéros de lignes s'affichent en bleu et l'icône de filtre de la colonne change d'apparence, indiquant visuellement que seule une partie des données est visible.
Astuce : Pour analyser rapidement des données filtrées, regardez la barre d'état en bas de la fenêtre Excel. Elle affiche automatiquement le nombre d'entrées filtrées et, selon votre sélection, d'autres statistiques comme la somme ou la moyenne des valeurs visibles.
Le tri permet de réorganiser l'ordre d'affichage de vos données selon différents critères :
Tri simple à partir d'une colonne
- Cliquez sur la flèche déroulante dans l'en-tête de la colonne à trier
- Sélectionnez "Trier de A à Z" ou "Trier de Z à A" pour du texte
- Pour les nombres, choisissez "Trier du plus petit au plus grand" ou "Trier du plus grand au plus petit"
- Pour les dates, sélectionnez "Trier du plus ancien au plus récent" ou l'inverse
Tri sur plusieurs colonnes
- Cliquez sur la flèche déroulante d'une colonne et choisissez "Trier par couleur" puis "Plus d'options de tri..."
- Ou accédez directement à l'onglet Données et cliquez sur "Trier"
- Dans la fenêtre "Trier" qui s'ouvre :
- Configurez votre premier critère de tri
- Cliquez sur "Ajouter un niveau" pour définir un deuxième critère (appliqué en cas d'égalité sur le premier)
- Continuez d'ajouter des niveaux selon vos besoins
- Réorganisez les priorités avec les boutons "Monter"/"Descendre"
Tri personnalisé
- Pour trier selon un ordre personnalisé (jours de la semaine, mois, etc.), utilisez l'option "Liste personnalisée" dans la fenêtre de tri
- Pour trier en fonction des couleurs ou des icônes conditionnelles, utilisez l'option "Trier par couleur"
Scénario pratique : Analyse des ventes régionales
Imaginons un tableau des ventes contenant les colonnes suivantes : Région, Vendeur, Produit, Date, Montant.
Question : Quels sont les trois meilleurs vendeurs de la région "Ouest" pour le produit "Ordinateur portable" au deuxième trimestre ?
Solution avec filtres et tri :
- Filtrez la colonne "Région" pour n'afficher que "Ouest"
- Filtrez la colonne "Produit" pour n'afficher que "Ordinateur portable"
- Filtrez la colonne "Date" pour n'afficher que le deuxième trimestre
- Triez la colonne "Montant" par ordre décroissant
- Les trois premiers vendeurs de la liste sont maintenant vos meilleurs vendeurs
Cette analyse, qui aurait pris beaucoup de temps avec des méthodes manuelles, s'effectue en quelques clics grâce aux tableaux structurés.
Les segments offrent une interface plus intuitive pour filtrer vos données :
- Sélectionnez une cellule de votre tableau
- Dans l'onglet "Création de tableau", cliquez sur "Insérer un segment"
- Sélectionnez les colonnes pour lesquelles vous voulez créer des segments
- Les segments apparaissent sous forme de petites boîtes flottantes
- Cliquez sur les valeurs dans le segment pour filtrer (maintenez Ctrl pour sélectionner plusieurs valeurs)
- Cliquez sur l'icône en forme d'entonnoir pour effacer le filtre du segment
Les segments sont particulièrement utiles pour les tableaux de bord car ils offrent une interface intuitive qui ne nécessite pas de connaître le fonctionnement des filtres.
Maîtriser la mise en forme conditionnelle
La mise en forme conditionnelle transforme vos données brutes en informations visuelles, permettant d'identifier instantanément les tendances, les anomalies ou les points d'attention sans avoir à analyser chaque valeur individuellement.
Voici la démarche pour mettre en évidence des données selon des critères spécifiques :
- Sélectionnez les cellules auxquelles vous souhaitez appliquer la mise en forme conditionnelle
- Accédez au menu : Dans l'onglet Accueil, cliquez sur "Mise en forme conditionnelle"
- Choisissez le type de règle :
- "Mise en surbrillance des cellules" pour les règles basées sur des valeurs
- "Règles de première/dernière" pour mettre en évidence les valeurs extrêmes
- "Barres de données", "Nuances de couleurs" ou "Jeux d'icônes" pour les visualisations graphiques
- "Nouvelle règle" pour des conditions plus complexes
- Configurez les paramètres de la règle dans la boîte de dialogue qui apparaît
- Prévisualisez le résultat et ajustez si nécessaire
- Cliquez sur OK pour appliquer
Pour mettre en évidence des valeurs spécifiques (par exemple, les ventes supérieures à un objectif) :
- Sélectionnez la plage contenant vos valeurs
- Accédez à Accueil > Mise en forme conditionnelle > Mise en surbrillance des cellules > Supérieur à
- Saisissez la valeur seuil (ex : 10000)
- Choisissez un style prédéfini dans la liste déroulante ou cliquez sur "Personnalisé" pour définir votre propre format
- Cliquez sur OK
Toutes les cellules contenant une valeur supérieure à votre seuil seront immédiatement mises en surbrillance. La mise en forme s'actualisera automatiquement si les valeurs changent.
Les nuances de couleurs (ou "cartes thermiques") sont parfaites pour visualiser la distribution des valeurs :
- Sélectionnez la plage de données
- Accédez à Accueil > Mise en forme conditionnelle > Nuances de couleurs
- Choisissez un style de dégradé (deux couleurs, trois couleurs ou colorimétrique)
- Personnalisez les couleurs et les points de référence si nécessaire
- Cliquez sur OK
Cette visualisation est particulièrement efficace pour les grands ensembles de données où les tendances sont difficiles à repérer manuellement.
Pour identifier rapidement les entrées en double ou les valeurs uniques :
- Sélectionnez la plage à vérifier
- Accédez à Accueil > Mise en forme conditionnelle > Mise en surbrillance des cellules > Valeurs en double (ou Valeurs uniques)
- Choisissez le format à appliquer
- Cliquez sur OK
Cette fonctionnalité est extrêmement utile pour nettoyer des listes de clients, vérifier des numéros de série uniques, ou identifier des erreurs de saisie.
Pour des conditions plus complexes impliquant plusieurs critères ou colonnes :
- Sélectionnez les cellules à formater
- Accédez à Accueil > Mise en forme conditionnelle > Nouvelle règle
- Sélectionnez "Utiliser une formule pour déterminer les cellules à mettre en forme"
- Saisissez une formule qui renvoie VRAI/FAUX (ex :
=ET(B2>1000;C2 - Cliquez sur "Format" pour définir l'apparence des cellules qui correspondent à la condition
- Cliquez sur OK pour appliquer
Les règles basées sur des formules offrent une flexibilité illimitée pour des scénarios complexes comme "mettre en évidence les produits dont les ventes dépassent la moyenne ET dont le stock est inférieur à 20 unités".
Important : Lorsque vous utilisez des formules dans la mise en forme conditionnelle, la première référence de cellule doit correspondre à la cellule supérieure gauche de la plage sélectionnée. Excel ajustera automatiquement les références pour les autres cellules (références relatives).
Au fil du temps, vous pourriez accumuler plusieurs règles ou avoir besoin de les ajuster :
- Afficher toutes les règles :
- Accédez à Accueil > Mise en forme conditionnelle > Gérer les règles
- Sélectionnez "Cette feuille" dans le menu déroulant pour voir toutes les règles de la feuille
- Modifier une règle existante :
- Sélectionnez la règle dans la liste
- Cliquez sur "Modifier la règle"
- Ajustez les paramètres selon vos besoins
- Changer la priorité :
- Utilisez les boutons "Monter" et "Descendre" pour réorganiser l'ordre d'application des règles
- Les règles sont appliquées de haut en bas ; en cas de conflit, la règle de priorité supérieure prévaut
- Supprimer des règles :
- Sélectionnez la ou les règles à supprimer
- Cliquez sur "Supprimer la règle"
- Pour supprimer toutes les règles d'une plage, sélectionnez la plage puis accédez à Accueil > Mise en forme conditionnelle > Effacer les règles
- Copier des règles :
- Utilisez l'outil Pinceau de mise en forme (Ctrl + Maj + C) pour copier la mise en forme d'une cellule vers une autre
- Ou copiez-collez les cellules avec mise en forme conditionnelle, puis utilisez l'option "Coller en tant que valeurs et formats de nombre"
Cas pratiques et exercices guidés
Scénario réel : Tableau de suivi des performances commerciales
Un responsable commercial souhaite créer un tableau de bord visuel pour suivre les performances de son équipe. Il dispose d'un fichier contenant les ventes mensuelles de chaque commercial, par région et par produit.
Objectifs :
- Transformer ces données brutes en un tableau structuré facile à filtrer
- Mettre en évidence les performances exceptionnelles ou préoccupantes
- Offrir une vue synthétique qui s'actualise automatiquement à chaque mise à jour
Solution étape par étape :
- Conversion en tableau structuré :
- Sélection de toutes les données et conversion en tableau (Ctrl + T)
- Renommage du tableau en "VentesCommerciales"
- Activation de la ligne de total
- Application de mises en forme conditionnelles :
- Mise en surbrillance verte des montants > 120% de l'objectif
- Mise en surbrillance rouge des montants < 80% de l'objectif
- Utilisation de barres de données pour visualiser la proportion par rapport à l'objectif
- Ajout de segments pour filtrage interactif :
- Création de segments pour "Région", "Commercial" et "Produit"
- Positionnement et mise en forme des segments pour une utilisation intuitive
Ce tableau de bord permet désormais au responsable de filtrer instantanément les données selon différents critères et d'identifier visuellement les performances exceptionnelles ou problématiques.
Exercices pratiques
Exercice 1 : Création et personnalisation d'un tableau structuré
Objectif : Transformer une plage de données en tableau structuré et exploiter ses fonctionnalités.
- Créez une nouvelle feuille Excel avec les données suivantes :
- Colonne A (Produit) : Ordinateur, Téléphone, Tablette, Écran, Clavier, Souris, Imprimante, Scanner, Webcam, Casque
- Colonne B (Catégorie) : Informatique, Mobile, Mobile, Périphérique, Périphérique, Périphérique, Périphérique, Périphérique, Accessoire, Accessoire
- Colonne C (Prix HT) : 1200, 800, 450, 200, 60, 40, 150, 100, 50, 80
- Colonne D (Frais de port) : 0, 8, 10, 15, 5, 5, 20, 15, 5, 6
- Colonne E (Stock) : 15, 45, 30, 25, 50, 60, 10, 8, 40, 35
- Transformez cette plage en tableau structuré avec des en-têtes
- Renommez le tableau "CatalogueProduits"
- Ajoutez une colonne "Prix TTC" qui calcule le Prix HT + 20% de TVA + Frais de port
- Appliquez un style de tableau à votre choix
- Activez la ligne de total et affichez le total des stocks et le prix moyen TTC
- Ajoutez deux segments pour filtrer par Catégorie et par un critère de prix (à créer : "Économique" pour < 100€, "Intermédiaire" pour 100-500€, "Premium" pour > 500€)
Exercice 2 : Filtrage et tri avancés
Objectif : Maîtriser le filtrage et le tri dans un contexte plus complexe.
- Téléchargez le fichier d'exercice "Ventes_Régionales.xlsx" contenant des données de ventes fictives sur une année
- Transformez la plage en tableau structuré
- Réalisez les opérations suivantes :
- Filtrez pour afficher uniquement les ventes du premier trimestre de l'année
- Parmi ces ventes, filtrez pour n'afficher que les produits de la catégorie "Électronique"
- Triez ces résultats par montant des ventes, du plus élevé au plus faible
- Identifiez les 3 meilleures ventes et notez les détails (produit, région, vendeur)
- Ensuite, effacez tous les filtres et effectuez un tri à plusieurs niveaux :
- D'abord par région (A à Z)
- Puis par mois (du plus ancien au plus récent)
- Enfin par montant des ventes (du plus élevé au plus faible)
- Ajoutez une ligne de total qui affiche le nombre de ventes et le montant total pour chaque combinaison de filtres
Exercice 3 : Mise en forme conditionnelle avancée
Objectif : Appliquer différentes techniques de mise en forme conditionnelle pour visualiser efficacement les données.
- Créez un tableau de suivi mensuel des dépenses avec les catégories suivantes :
- Loyer/Hypothèque
- Alimentation
- Transport
- Loisirs
- Factures
- Divers
- Créez 12 colonnes pour les mois de l'année
- Remplissez avec des montants réalistes (ou fictifs)
- Ajoutez une colonne "Budget mensuel" et une colonne "Moyenne annuelle"
- Appliquez les mises en forme conditionnelles suivantes :
- Nuances de couleur (rouge à vert) pour toutes les dépenses mensuelles, où rouge = montant élevé et vert = montant faible
- Mise en surbrillance en rouge vif des dépenses qui dépassent le budget de plus de 10%
- Barres de données pour visualiser la proportion des dépenses par rapport à la moyenne annuelle
- Jeux d'icônes (flèches) pour indiquer si la tendance est à la hausse ou à la baisse par rapport au mois précédent
- Créez une règle personnalisée basée sur une formule pour mettre en évidence les mois où le total des dépenses dépasse un certain seuil que vous déterminerez
Exercice 4 : Création d'un tableau de bord de suivi des objectifs
Objectif : Combiner toutes les techniques apprises pour créer un outil de suivi visuel complet.
- Créez un tableau avec les données suivantes :
- Colonne A (Vendeur) : noms de 5 vendeurs fictifs
- Colonne B (Région) : Est, Ouest, Nord, Sud, Centre (chaque vendeur appartient à une région)
- Colonnes C à N : 12 mois de l'année avec des chiffres de vente fictifs
- Colonne O : Objectif annuel pour chaque vendeur
- Transformez cette plage en tableau structuré
- Ajoutez une colonne "Total annuel" qui calcule la somme des ventes sur les 12 mois
- Ajoutez une colonne "% Réalisation" qui calcule le pourcentage de réalisation de l'objectif
- Appliquez les mises en forme conditionnelles suivantes :
- Pour chaque mois : mise en évidence des valeurs > 110% de la moyenne mensuelle
- Pour la colonne "% Réalisation" : barre de données et code couleur (rouge < 85%, jaune entre 85% et 100%, vert > 100%)
- Pour la totalité du tableau : mise en surbrillance de la meilleure et de la moins bonne performance mensuelle de chaque vendeur
- Ajoutez des segments pour filtrer par Région et par Trimestre (à créer)
- Ajoutez une ligne de total qui affiche la somme des ventes et le pourcentage moyen de réalisation
- Bonus : Créez une petite zone de synthèse qui affiche automatiquement :
- Le meilleur vendeur (en termes de % de réalisation)
- Le meilleur mois (en termes de ventes totales)
- La meilleure région (en termes de % moyen de réalisation)
Résumé des points clés
- Un tableau structuré offre des fonctionnalités avancées par rapport à une simple plage de cellules
- La conversion s'effectue via Ctrl + T ou l'option "Tableau" de l'onglet Insertion
- Les tableaux permettent un filtrage et un tri intégrés via les en-têtes de colonnes
- La ligne de total permet des calculs dynamiques qui s'ajustent automatiquement au filtrage
- Les tableaux s'étendent automatiquement pour inclure les nouvelles données adjacentes
- Le filtrage permet d'afficher un sous-ensemble de données répondant à des critères spécifiques
- Les filtres peuvent être appliqués sur plusieurs colonnes pour affiner les résultats
- Le tri peut être simple (une colonne) ou complexe (plusieurs niveaux)
- Les segments offrent une interface visuelle et interactive pour le filtrage
- Les opérations de filtrage et de tri ne modifient pas les données sources
- Permet de mettre en évidence visuellement les données selon des critères spécifiques
- Les règles simples incluent les comparaisons (supérieur à, inférieur à, entre, etc.)
- Les visualisations avancées comprennent les barres de données, nuances de couleurs et jeux d'icônes
- Les formules personnalisées permettent des conditions complexes
- La gestion des règles permet de modifier, prioriser ou supprimer les mises en forme existantes
- La combinaison des tableaux strutcurés avec la mise en forme conditionnelle crée des tableaux de bord dynamiques et visuels
- Les tableaux filtrés conservent leur mise en forme conditionnelle, qui s'adapte aux données affichées
- Les segments permettent une interaction intuitive avec les tableaux de bord
- Cette approche permet d'analyser rapidement de grands ensembles de données sans recourir à des techniques avancées
Pour aller plus loin : Les compétences acquises dans ce module constituent la base de l'analyse de données dans Excel. Dans les modules avancés, vous découvrirez comment combiner ces techniques avec les tableaux croisés dynamiques, les fonctions de recherche avancées, et les graphiques dynamiques pour créer des solutions d'analyse encore plus puissantes.