Transformer vos plages de cellules en Table Excel

(Last Updated On: 4 novembre 2017)

Un tableau est une plage rectangulaire de données structurées, et contient généralement une ligne d’en-tête en haut qui décrit le type d’information contenues dans chaque colonne.

Excel contient une fonction magique qui permet de convertir une plage de données en une Table “officielle”.

Convertir une plage de cellules en Table

Pour créer une Table Excel, il suffit de sélectionner n’importe quelle cellule de la plage de données à convertir, puis allez dans l’onglet Insertion, Tableaux, Insérer un Tableau.

convertir-plage-table_004

Excel déterminera ensuite automatiquement la plage de cellules. Vérifiez que l’option Mon tableau comporte des en-têtes soit cochée si la première ligne de la plage contient les titres des colonnes.

convertir-plage-table_003

Dans l’exemple ci-dessous, la plage de données n’est pas encore convertie en Table Excel :

convertir-plage-table_001

Après conversion, la plage de cellules ressemble à cela :

convertir-plage-table_002

La mise en page, ainsi que des filtres ont été ajoutés automatiquement.

Pourquoi convertir une plage en Table Excel

La conversion d’une plage en Table donne accès à des fonctionnalités supplémentaires :

  • Une mise en forme visuelle plus agréable
  • Chaque en-tête de colonne contient un bouton de tri/filtre
  • Vous pouvez ajouter des segments pour filtrer le tableau plus facilement
  • Les formules entrées dans une cellule de la Table sont automatiquement propagées à toutes les cellules de la colonne
  • Les Tables ont une référence nommée. Au lieu d’utiliser des références de cellules, les formules peuvent utiliser les noms des tables
  • Les Tables sont dynamiques, c’est-à-dire qu’en utilisant la référence d’une table dans une formule,  l’ajout de lignes ou de colonnes dans la table est pris en compte automatiquement dans la formule
  • Le coin inférieur droit de la Table contient un petit contrôle sur lequel vous pouvez cliquer et faire glisser pour agrandir la taille de la Table, soit horizontalement (ajouter des colonnes) soit verticalement (ajouter des lignes)
  • L’ajout optionnel d’une ligne Total à la fin de la plage. Il est possible de choisir le type de total (somme, moyenne, nombre, max, min, etc…)

Ajout des Totaux dans une table

La ligne Total d’un tableau permet de synthétiser les données de la colonne. Lorsque vous créez une table, la ligne Total n’est pas activée. Pour afficher la ligne Total, sélectionnez une cellule du tableau, puis allez dans l’onglet Création.

  1. Cochez la case Ligne Total
  2. Par défaut, une ligne Total affiche la somme des valeurs de la colonne
  3. Dans certains cas, vous aurez besoin d’un autre type de formule
  4. Sélectionnez une cellule dans la ligne Total, une flèche déroulante apparaît dans la cellule

convertir-plage-table_005

Vous pouvez sélectionner différentes formules :

  • Aucun : Pas de formule
  • Moyenne : calcule la moyenne des nombres dans la colonne
  • Nombre : calcule le nombre d’entrées dans la colonne (Les cellules vides ne sont pas comptées)
  • Chiffres : calcule le nombre de valeurs numériques dans la colonne (Les cellules vides, le texte et les erreurs ne sont pas comptées)
  • Max. : affiche la valeur maximale de la colonne
  • Min. : affiche la valeur minimale dela colonne
  • Somme : calcule la somme des valeurs dans la colonne
  • Ecartype : calcule l’écart-type des valeurs dans la colonne
  • Var : calcule la variance des valeurs dans la colonne
  • Autres fonctions : affiche la boîte de dialogue d’insertion d’une fonction de telle sorte que vous pouvez sélectionner une autre fonction non présente dans la liste

Utilisation des tables dans les formules

Avant d’utiliser la Table, je vous recommande de la renommer,  sinon elle aura un nom pas très explicite du style “Tableau1”.

Sélectionnez une cellule du tableau, allez dans l’onglet Création, puis dans Propriétés renommer le tableau et valider avec la touche Entrée.

convertir-plage-table_006

Dans l’exemple, ci-dessous, on insère dans une cellule une fonction SOMME pour calculer le montant total des ventes contenues dans la colonne Prix de la Table. Puis on sélectionne toutes les lignes de la Table contenant les prix (cellules H4:H46) :

=SOMME(Tableau1[Prix])

Excel détecte automatiquement l’étendue de la plage de prix (H4:H46) et la renomme en [Prix]. Tableau1 est le nom de la Table.

Remarque : L’énorme avantage par rapport à une plage standard est que si vous ajoutez ou supprimez des lignes de la table Tableau1, la formule SOMME dans la cellule se met à jour automatiquement, car la référence à la plage ne change pas (Tableau1[Prix]).

Utilisation des tables pour créer un graphique dynamique

Dans l’exemple ci-dessous, j’ai créé une colonne Ventes cumulées dans la Table, en cumulant simplement les valeurs de la colonnes Prix entre elles (pour voir comment faire, vous pouvez télécharger le classeur associé en fin d’article).

Ensuite, pour insérer le graphique des ventes cumulées, procédez comme suit :

  1. Sélectionnez la colonne Date, maintenez la touche Ctrl et sélectionnez la colonne Ventes cumulées
  2. Allez dans l’onglet Insérer, sélectionnez Graphiques recommandésconvertir-plage-table_007

Maintenant, si j’insère une nouvelle ligne de ventes à la suite du tableau, le graphique se met à jour automatiquement en affichant le nouveau montant cumulé des ventes. Magique non ?  🙂

Note : il était possible dans les versions précédentes d’Excel (< 2010) de créer un graphique dynamique en créant une référence de plage nommée dynamique via le Gestionnaire de Noms. Désormais j’utilise exclusivement une Table Excel pour créer des plages dynamiques, car c’est beaucoup plus simple à mettre en oeuvre.

Utilisation des tables pour créer un TCD dynamique

Pour créer un tableau croisé dynamique à partir d’une source de données convertie en Table Excel, cliquez sur le lien suivant:

Trier et filtrer une Table

Chaque élément de la ligne d’en-tête d’une table contient une flèche déroulante permettant de filtrer et trier le tableau. Dans l’exemple ci-dessous, le bouton de filtre de la colonne Item est activé et permet de sélectionner le type d’item à afficher dans la table, ainsi que les options de tri par ordre alphabétique.

convertir-plage-table_008

Ajouter un segment pour filtrer la Table

Une autre façon de filtrer une Table est d’utiliser un ou plusieurs segments. Cette méthode est moins souple mais visuellement attrayante.

Les segments sont particulièrement utiles pour faire des présentations ou des tableaux de bord. En effet, les segments sont très visuels, et il est facile de voir exactement quel type de filtre est actif. Un inconvénient des segments est qu’ils prennent beaucoup de place à l’écran.

Remarque : Les segments peuvent ne pas convenir pour filtrer les colonnes de valeurs numériques, en particulier si le nombre de valeurs est important.

Pour insérer un segment, procédez comme suivant :

  1. Sélectionnez une cellule de la Table, allez dans l’onglet Création
  2. Sélectionnez Insérer un segment
  3. Dans la liste des champs, choisissez la colonne à filtrer
  4. Déplacer le segment dans une zone libre de la feuille de calcul

convertir-plage-table_009

Dans l’exemple ci-dessous, j’ai ajouté un segment à la feuille pour filtrer les régions, et sélectionné la région Ouest.

convertir-plage-table_011

EDIT : Dans l’exemple ci-dessus, le filtre appliqué à la table n’affecte pas la colonne Ventes cumulées comme il devrait, ceci est dû à la formule de calcul utilisée. Je n’ai pas encore trouvé de solution pour cumuler les ventes uniquement sur le filtre sélectionné ! …

TELECHARGER LE FICHIER D’EXEMPLE

Télécharger le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

 

 

 

Sondage lecteurs

Pourriez-vous répondre au court questionnaire ci-dessous ?

Vos réponses m’aideront à mieux connaître vos problématiques pour pouvoir mieux y répondre. Merci beaucoup 🙂

Nom
E-mail

Quelles sont vos principales tâches sous Excel ?

Quelle est votre plus grande difficulté actuellement sous Excel ?

Quels sont vos principaux objectifs sur Excel ?

Si vous pouviez me poser une question pour atteindre ces objectifs, n'importe laquelle, quelle serait cette question ?

Connaissez-vous Power BI ? Seriez-vous intéressé(e) pour voir plus de contenu sur mon blog sur Power BI ?

Je vais faire de mon mieux pour répondre à ces questions et vous permettre d'atteindre vos objectifs. À quel rythme préférez-vous utiliser ces conseils ?

Je suis en train de mettre sur pied un projet pour répondre aux questions que j’ai reçues et pouvoir vous aider. Quel format vous aidera le plus ?

Si vous pouviez atteindre exactement l’objectif que vous avez indiqué tout à l’heure, combien seriez-vous prêt(e) à investir pour un service qui corresponde exactement à vos attentes et vous aide dans votre objectif ?

Quelle est votre tranche d’âge ?

Quel est votre secteur d'activité ?

Avez-vous un message à me laisser ? Est-ce que ce que je publie vous a aidé ?

Merci pour votre participation !

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *