Quatre façons d'utiliser les références structurées dans Microsoft Excel

Travailler dans Excel consiste généralement à trouver des connexions entre différents points de données. Cependant, lors de l'insertion de formules compliquées, l'utilisation répétée de références de cellules explicites relatives et absolues (comme « 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 rationaliser 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 récupérer automatiquement les données structurées et les calculer.

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

1. Calculer des tableaux intérieurs

Étant donné que les références structurées ne fonctionnent que sur des tableaux, la meilleure façon de les utiliser est à l'intérieur de ces mêmes tableaux.

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

Calculons le total pour chaque vente :

Étape 1 : Cliquez sur F2 (mais pas sur l'icône déroulante). Allez dans « Accueil » puis dans « Insérer » et sélectionnez « Insérer des colonnes de tableau à droite ». Cela ajoutera automatiquement une nouvelle colonne au tableau.

Étape 2 : Nommez l'en-tête de la colonne G « Total ».

Étape 3 : Dans G3, insérez =[@PricePerUnit]*[@Quantity] et appuyez sur Entrée. Formatez la sortie de la cellule selon vos besoins.

Les « [@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 traduire, la formule =[@PricePerUnit]*[@Quantity] en G3 est essentiellement la même que l'écriture =$C3*$D3.

2. Récupérer une plage en dehors de la table

Lorsque vous souhaitez utiliser une référence structurée dans une cellule en dehors du tableau, vous devez faire précéder la référence de TableName. Dans notre exemple précédent, l'utilisation de « Sales[Total] » récupérera toute la plage sous l'en-tête « Total » du tableau « Sales ». Cela signifie que vous obtiendrez 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. Résumer et résumer partiellement une colonne

Pour résumer rapidement une colonne entière, vous pouvez utiliser la coche « Ligne totale » dans les options « Conception du 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 » seule ne puisse pas être déplacée et sera placée à la fin du tableau (permettant les insertions), vous pouvez dupliquer son résultat ailleurs :

• Pour obtenir la somme de toutes les lignes de la colonne « Total », utilisez =SUM(Sales[Total]).
• Si vous souhaitez obtenir uniquement la somme des colonnes visibles, par exemple après avoir filtré le tableau, utilisez =SUBTOTAL(109,Sales[Total]). Cette formule correspond à ce que fait réellement l'option « Ligne totale » dans le format de tableau dans sa ligne.

Vous pouvez également obtenir une somme partielle basée sur une variable spécifique trouvée à l'intérieur du tableau sans le formater. Par exemple :

• Pour obtenir la somme de toutes les ventes de Mike, vous pouvez utiliser =SUMIF(Sales[Seller],”Mike”,Sales[Total]). Dans la formule, « Mike » est une chaîne qui a été saisie manuellement.
• Pour obtenir la somme de tous les produits portant l'ID « 41230 », utilisez ce qui suit =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. Validation des données à partir d'une table via INDIRECT

Supposons que vous disposiez 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 permettra de choisir entre les ID de produit, les dates ou les vendeurs, puis de choisir n'importe quel élément parmi ces sous-ensembles 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 popup, choisissez « Liste » parmi les options « Autoriser », puis insérez les valeurs des colonnes manuellement dans la case « Source », séparées par des virgules. Dans ce cas, nous avons inséré « ProductID, Seller, Date. »

Étape 3 : Dans la cellule C13, créez une autre validation de données. Encore une fois, choisissez « Liste ». Pour « Source », insérez 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 désormais choisir les options parmi les deux listes de validation des données, et le sous-total sera affiché en D13.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!