Comment utiliser des références structurées dans Microsoft Excel pour améliorer l'efficacité

L'utilisation de références structurées dans Microsoft Excel constitue l'un des moyens les plus puissants d'améliorer la précision et l'efficacité de votre travail avec les données. Que vous travailliez sur de grandes feuilles de calcul ou que vous deviez accélérer le processus de mises à jour fréquentes, apprendre à appliquer ces références peut vous faire gagner beaucoup de temps et d'efforts. Dans cet article, nous apprendrons comment utiliser efficacement les références structurées pour obtenir les meilleurs résultats et améliorer le formatage des données et des équations dans les fichiers Excel.

Astuces rapides

• Les références structurées dans Excel ne fonctionnent que sur les tableaux formatés de cette manière dans le programme, pas sur les plages de données.
• L'utilisation de références structurées rend les formules plus lisibles et plus dynamiques.
• Lorsque vous formatez un tableau dans Excel, changez son nom en quelque chose de significatif. Sinon, Excel l’appellera Tableau[numéro], ce qui peut prêter à confusion.
• Les références structurées fonctionnent à l'intérieur et à l'extérieur des tables, peuvent être utilisées dans d'autres fonctions et seront mises à jour automatiquement si les en-têtes reçoivent de nouveaux noms.

Travailler dans Excel consiste généralement à trouver des connexions entre différents points de données. Cependant, lors de l'insertion de formules complexes, l'utilisation fréquente de références de cellules relatives et absolues explicites (telles que « B7 » ou ses variantes) ne peut vous mener que jusqu'à un certain point avant que la barre de formule ne devienne un désordre illisible.

Les références structurées dans Excel vous permettent de simplifier ce travail en attribuant des noms aux tableaux et à leurs en-têtes. Ces noms peuvent ensuite être utilisés comme références de cellules implicites afin qu'Excel puisse automatiquement récupérer et calculer des données structurées.

Voici quelques-unes des façons les plus courantes d’utiliser des références structurées dans Excel.

1. Calcul à l'intérieur des tableaux

Étant donné que les références structurées ne fonctionnent que sur des tableaux, la meilleure façon d’en tirer parti est au sein des mêmes tableaux.

Par exemple, nous allons créer un simple tableau B2 à F8 avec les données de ventes d'un magasin. Notez que nous avons nommé la table « Ventes » (voir « Nom de la table » en haut à droite).

Calculons le total pour chaque vente :

Étape 1: Cliquez sur F2 (mais pas sur l'icône du menu déroulant). Accédez à « Accueil », puis à « Insérer » et sélectionnez « Insérer les colonnes du tableau à droite ». Cela ajoutera automatiquement une nouvelle colonne au tableau.

Étape 2: Étiquetez l’en-tête de colonne G « Total ».

Passons à l'étape 3 : Dans G3, saisissez =[@PricePerUnit]*[@Quantity] et appuyez sur Entrée. Formatez la sortie de la cellule selon vos besoins.

« [@PricePerUnit] » et « [@Quantity] » sont des références aux champs correspondants dans ces colonnes. L'argument « @ » avant les noms de colonnes signifie que chaque cellule de résultat utilisera les références de la même ligne du tableau.

Pour la traduction, la formule =[@PricePerUnit]*[@Quantity] dans G3 équivaut à écrire =$C3*$D3.

2. Sortez la portée de la table

Lorsque vous souhaitez utiliser une référence structurée dans une cellule en dehors d'un tableau, vous devez fournir à la référence le nom de la table. Dans notre exemple précédent, l'utilisation de « Sales[Total] » amènera la gamme entière sous l'en-tête « Total » du tableau « Sales ». Cela signifie que vous aurez plusieurs valeurs dans un tableau que vous pourrez manipuler.

Voici à quoi cela ressemble dans Excel dans la cellule I3, à condition de laisser suffisamment de place pour que la plage s'étende.

3. Somme de colonne et somme partielle

Pour additionner rapidement une colonne entière, vous pouvez utiliser la coche Ligne totale dans les options de conception de tableau (sous Options de style de tableau). Voici un exemple d'obtention des totaux pour les colonnes Quantité et Total.

Bien que la ligne Total ne puisse pas être déplacée seule et soit placée à la fin du tableau (avec insertion autorisée), vous pouvez dupliquer son résultat ailleurs :

Pour obtenir la somme de toutes les lignes d'une colonne Total, utilisez =SUM(Sales[Total]).
Si vous souhaitez obtenir la somme des colonnes visibles uniquement, par exemple après avoir filtré le tableau, utilisez =SUBTOTAL(109,Sales[Total]). Cette formule correspond à ce que fait réellement l’option « Total Row » dans « Format Table » pour sa ligne.
Vous pouvez également obtenir une somme partielle basée sur une variable spécifique contenue dans le tableau sans le formater. Par exemple:

Pour obtenir la somme de toutes les ventes réalisées par Mike, vous pouvez utiliser =SUMIF(Sales[Seller],"Mike",Sales[Total]). Dans la formule, « Mike » est une chaîne saisie manuellement.
Pour obtenir la somme de tous les produits portant l'ID « 41230 », utilisez =SUMIF(Sales[ProductID],41230,Sales[Total]). Notez que puisque la colonne ProductID a un format « Général », vous pouvez saisir le numéro directement.

4. Valider les données d'une table via INDIRECT

Supposons que vous disposez de la table Sales précédemment utilisée. Vous pouvez créer des options de validation de données personnalisées pour faciliter la recherche dans le tableau. Créons un tableau plus petit qui vous permet de choisir entre les ID de produit, les dates ou les vendeurs, puis sélectionnons n'importe quel élément individuel de ces sous-groupes pour afficher le sous-total.

Étape 1: Dans la cellule B13, créez la validation des données (onglet « Données » > Outils de données > Validation des données).

Étape 2: Dans la fenêtre contextuelle, choisissez « Liste » parmi les options « Autoriser », puis saisissez manuellement les valeurs des colonnes dans la case « Source », séparées par des virgules. Dans ce cas, nous avons saisi « ID produit, vendeur, date ».

Passons à l'étape 3 : Dans la cellule C13, créez une autre vérification des données. Encore une fois, choisissez « Liste ». Pour « Source », saisissez la formule suivante : =INDIRECT(« Sales[« &B13& »] »).

Étape 4: Dans la cellule D13, utilisez la formule suivante : =SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total]).

Vous pouvez maintenant choisir des options dans les deux menus de validation des données et le sous-total sera affiché dans D13.