Structurer vos données dans Excel

Utiliser les noms standards et les plages nommées dans Excel

Excel utilise des noms pour identifier les cellules et les plages de données. Comprendre ces noms, puis apprendre à en créer soi-même, permet de rendre les formules plus lisibles et de faciliter la gestion des données. Cette section présente d’abord les noms standards d’Excel, puis la manière de définir et d’utiliser des plages nommées.

Comprendre les noms standards d’Excel

Par défaut, Excel utilise des références standards comme A2, B2 ou C2 pour identifier les cellules dans les formules.

Avant d’aborder le nommage des cellules ou des plages (groupe de cellules), il est intéressant de savoir comment Excel gère les relations entre elles dans les formules.

 Ouvrez le fichier Adressage des cellules et sa feuille Adressages Relatifs - Absolus 1.

Il contient un tout petit tableau de valeurs de A1 à D4.

Le tableau des données présentes

Ces valeurs sont choisies de façon à voir immédiatement comment sont récupérées les valeurs lors des copies.

Il contient un second petit tableau contenant en colonne G, 4 valeurs et leur total, en G6, et, en colonne H, le pourcentage de la valeur de la colonne G sur le total en cellule G6.

 

Deux types de calculs utilisant les références relatives et/ou absolues

Si vous examinez la formule en G5 : =SOMME(G2:G5), rien ne vous choque ou surprend, il faut additionner les valeurs des cellules G2 à G5.

Si vous examinez les formules de la colonne H, et particulièrement H2, la formule est un peu particulière : =G2/G$6.

Elle calcule le rapport de la valeur de G2 à G6, mais le 6 est précédé d’un $.

Cela signifie que depuis longtemps, sans le savoir, vous utilisez les valeurs relatives des cellules entre elles, et qu’ici vous êtes face à une formule contenant une référence absolue.

Avec la référence relative (exemple : G6), si vous l’utilisez dans une formule en H10, Excel utilisera non pas la cellule (G6), mais l’écart en nombre...

Étude de cas : construire un tableau de suivi mensuel avec des plages nommées

Vous venez de voir comment les plages nommées permettent de rendre les formules plus lisibles. Pour illustrer concrètement leur intérêt, cette étude de cas présente la construction d’un tableau de suivi d’activité mensuelle basé sur ces noms. L’objectif est de montrer comment structurer un modèle complet, du calcul des indicateurs à la duplication des feuilles pour chaque mois de l’année. Comme une année est constituée de 12 mois, il faudrait construire 12 feuilles.

Nous devrons aussi disposer d’une feuille de saisie que nous remplirons au fil du temps ainsi que d’une feuille permettant de mesurer pour chaque mois le chiffre d’affaires réalisé. 

La feuille de saisie courante nommée Factures commence ainsi :

Extrait de la feuille Factures

Lorsque nous aborderons le mois de février, cela signifiera que janvier est clos et les données du mois de janvier ne pourront plus être modifiées.

Synthèses du mois de Janvier

=DATE(ANNEE(DébMois);MOIS(DébMois)+1;0) 

Utiliser les tableaux structurés

Les tableaux structurés sont une manière moderne et beaucoup plus souple d’organiser des données dans Excel. Contrairement aux listes classiques, ils s’adaptent automatiquement lorsque vous ajoutez ou retirez des lignes, appliquent uniformément les mises en forme et étendent les formules sans intervention manuelle. Ils offrent également un accès simplifié aux données grâce aux références structurées, qui rendent les formules plus lisibles et plus faciles à maintenir.

Dans cette section, vous allez apprendre à transformer une liste de données en tableau structuré. Vous comprendrez leurs propriétés essentielles et exploiterez les nombreux automatismes qu’ils apportent au quotidien : filtrage et tri intégrés, colonnes calculées, mise en forme dynamique et extension automatique des données. Les tableaux structurés constituent un outil incontournable pour travailler efficacement avec des données amenées à évoluer.

Créer un tableau structuré

 Pour créer un Tableau Structuré, placez-vous dans une cellule quelconque de votre plage de données, par exemple la cellule A4, puis faites un clic sur le bouton Tableau du groupe Tableaux de l’onglet Insertion.

Des cellules à transformer en tableau structuré

Excel place un cadre en traits pointillés autour de la plage qu’il détermine cohérente.

 Vérifiez que la plage proposée est correcte, cochez, si c’est le cas, l’option Mon tableau comporte des en-têtes. Si aucun en-tête n’est en place, ou déclaré, Excel en créera pour vous.

 Vous pouvez corriger, si nécessaire, dans la zone de saisie, la plage de cellules sélectionnée en la saisissant directement.

 Vous pouvez aussi cliquer sur le bouton flèche de la boîte de dialogue puis sélectionner les cellules concernées.

Définition du nouveau tableau

 Terminez avec le bouton OK.

Excel transforme immédiatement la zone sélectionnée en Tableau structuré (TS). Ce Tableau structuré est nommé Tableau1 :

Apparence du nouveau tableau structuré

Les lignes de titres...

Utiliser les tableaux croisés dynamiques (TCD)

Les tableaux croisés dynamiques, ou TCD, sont l’un des outils les plus puissants d’Excel pour analyser rapidement un grand volume de données. À partir d’une liste ou d’un tableau structuré, ils permettent de créer en quelques clics des synthèses, des regroupements, des comparaisons ou des indicateurs sans écrire aucune formule.

Un TCD offre la possibilité d’explorer vos données selon différents angles : par mois, par client, par produit, par technicien… Il suffit de déplacer des champs pour transformer instantanément l’analyse et faire apparaître de nouvelles perspectives.

Créer un tableau croisé dynamique

 Reprenez le fichier Facturation.xlsx, feuille Factures. Nommez la plage $A$1:$F$327 : Activité.

 Cette plage étant sélectionnée, créez le TCD.

 Dans l’onglet Insertion, cliquez sur le menu déroulant du bouton Tableau Croisé Dynamique du groupe Tableaux.

Création d’un tableau croisé dynamique

 Puis sélectionnez l’option À partir d’un tableau ou d’une plage.

Le nouveau tableau et son emplacement

 Dans le champ Tableau/Plage, saisissez le nom de la plage de cellules à prendre en compte : Activité.

 Conservez cochée l’option Nouvelle feuille de calcul pour que le tableau soit créé dans une nouvelle feuille.

Un rapport de tableau croisé dynamique vierge est inséré dans une nouvelle feuille :

Rapport de TCD vierge et volet Champs de tableau croisé dynamique

Le volet Champs de tableau croisé dynamique liste tous les champs qui existent dans la plage de données précédemment sélectionnée et propose quatre zones où les champs peuvent être placés. Chaque zone joue un rôle spécifique dans la construction de l’analyse. 

1 - La zone Filtres permet d’ajouter un ou plusieurs critères globaux pour filtrer l’ensemble du tableau. Le lecteur du TCD pourra, par exemple, choisir un mois, un client ou un technicien à l’aide d’une liste déroulante. Le filtre s’applique à l’ensemble du tableau.

2 - Les champs...