Name: Tremplin Numérique
Price range: $$$

Excel est bien connu comme un programme qui vous permet de créer des tableaux de données complexes. Cependant, certaines personnes connaissent moins bien un type particulier de référence qui accompagne ces tableaux : les références structurées.

Les références structurées peuvent être utilisées dans Excel 2016 ou version ultérieure, y compris Excel 365.

Sommaire

Qu'est-ce qu'une référence structurée ?

Les références de cellules directes dans Excel lient une cellule à une autre à l’aide des étiquettes d’en-tête de colonne et de ligne. Par exemple, la cellule A1 se trouve dans la première colonne et la première ligne, tandis que la cellule C10 se trouve dans la troisième colonne et la dixième ligne.

D'un autre côté, plutôt que d'utiliser les étiquettes d'en-tête de colonne et de ligne, les références structurées utilisent des noms de table et de colonne. Dans l'exemple ci-dessous,

=SUM((@(Daily profit))*7)

prend les valeurs de chacune des cellules de la colonne nommée « Bénéfice quotidien » et les multiplie par sept.

Utilisation de références structurées dans une table

Pour utiliser des références structurées dans votre tableau, comme dans l'exemple ci-dessus, vous devez d'abord ajouter des données à votre feuille de calcul Excel, avec les noms de colonnes inclus en haut de ces données. Si vous n'ajoutez pas de noms de colonnes, Excel utilisera par défaut la colonne 1, la colonne 2, etc., ce qui est beaucoup moins clair lorsque vous souhaitez utiliser ultérieurement les en-têtes dans les formules.

Avant d'utiliser l'une des données pour créer des calculs, sélectionnez une cellule dans vos données et cliquez sur « Formater en tableau » dans l'onglet Accueil du ruban. Là, sélectionnez le design qui vous convient le mieux.

Maintenant, utilisez la boîte de dialogue Créer une table pour vérifier que toutes vos données sont sélectionnées, cochez la case « Ma table a des en-têtes » et cliquez sur « OK ».

Maintenant que vos données sont dans un tableau Excel formaté, vous êtes prêt à utiliser des références structurées.

Dans mon exemple, je vais sélectionner la cellule C2 (la première cellule où je souhaite créer un calcul), et taper :

=SUM(

Maintenant, je vais cliquer sur la première cellule contenant des données dans ma colonne « Bénéfice quotidien » et Excel insérera une référence structurée à cette colonne.

=SUM((@(Daily profit))

Les parenthèses carrées permettent à Excel d'indiquer qu'il utilise une référence structurée, et le symbole @ (également appelé opérateur d'intersection) signifie que le calcul sera appliqué respectivement à chaque ligne du tableau.

Enfin, je dois multiplier les valeurs de la colonne « Bénéfice quotidien » par sept pour générer le profit hebdomadaire et fermer les parenthèses rondes.

=SUM((@(Daily profit))*7)

Lorsque j'appuie sur Entrée, la formule sera automatiquement copiée dans les cellules restantes de la colonne « Bénéfice hebdomadaire ».

Si je supprimais manuellement le symbole @, le calcul dans la colonne « Bénéfice hebdomadaire » additionnerait l'ensemble de la colonne « Bénéfice quotidien » et multiplierait ce total par sept.

Utilisation de références structurées en dehors d'une table

Les références structurées sont également utilisées dans les formules en dehors d'un tableau Excel qui référencent les données du tableau. Dans cet exemple, j'utiliserai la formule XLOOKUP pour générer un profit hebdomadaire dans la cellule E4 en fonction du site indiqué dans la cellule E2.

La première étape consiste à nommer la table. Sinon, Excel nommera mes données Table1, ce qui pourrait prêter à confusion s'il y a plusieurs tableaux dans mon classeur. En fait, il est bon de prendre l'habitude de nommer vos tableaux chaque fois que vous les créez dans Excel, même si vous n'avez pas l'intention de les référencer ailleurs dans votre feuille de calcul.

Pour ce faire, je vais sélectionner n'importe quelle cellule du tableau, ouvrir l'onglet « Conception du tableau » sur le ruban et saisir un nom approprié dans le champ Nom du tableau. Dans mon cas, j'appellerai ma table « Bénéfices ».

Vous devez suivre certaines règles lorsque vous nommez votre table :

Le nom de votre table doit commencer par une lettre, un trait de soulignement (_) ou une barre oblique inverse ().
Le reste du nom de la table peut être une combinaison de 255 lettres, chiffres, points et traits de soulignement maximum.
Votre tableau ne peut pas s'appeler « C », « c », « R » ou « r », car ils sont utilisés à d'autres fins dans Excel.
Votre tableau ne peut pas non plus être identique à une référence de cellule, telle que A1 ou $A$1.
Essayez de limiter le nom de la table à un seul mot afin qu'il soit facile de s'y référer. Cependant, si vous doit utilisez plus d'un mot, utilisez un trait de soulignement (pas un espace) entre chaque mot.
Assurez-vous que le nom de la table n'a pas été utilisé ailleurs dans votre classeur.

Maintenant, dans la cellule E4, je peux démarrer ma formule XLOOKUP en tapant :

=XLOOKUP(E2

Ensuite, je dois sélectionner le tableau de recherche, qui comprend les cellules A2 à A9. Remarquez comment ma formule convertit automatiquement cela en une référence structurée, « Profits » étant le nom de la table que j'ai créée précédemment et « Site » étant l'en-tête de colonne.

=XLOOKUP(E2,Profits(Site),

Enfin, je peux sélectionner le tableau de retour, qui correspond aux cellules C2 à C9, et fermer mes parenthèses rondes. Encore une fois, Excel en fait une référence structurée pour moi.

=XLOOKUP(E2,Profits(Site),Profits(Weekly profit))

Lorsque j'appuie sur Entrée, le bénéfice hebdomadaire est récupéré avec succès de ma table.

Pourquoi utiliser des références structurées ?

Vous pensez peut-être : « À quoi ça sert ? » Eh bien, l’utilisation de références structurées au lieu de références directes présente d’innombrables avantages lorsqu’il s’agit de tableaux dans Excel.

Premièrement, les références structurées sont plus faciles à créer et à lire que les références directes. En conséquence, je peux comprendre à quoi servent mes formules simplement en y jetant un coup d’œil, et je peux facilement analyser ma formule si Excel renvoie une erreur.

Deuxièmement, si je devais ajouter une ligne supplémentaire à mon tableau en cliquant et en faisant glisser la poignée dans le coin inférieur droit (étiquetée « A » dans la capture d'écran ci-dessous), puis en remplissant la ligne supplémentaire avec des données supplémentaires, les références structurées que je que j'ai déjà utilisé dans ma feuille de calcul s'appliquerait automatiquement à ces nouvelles données.

Dans cet exemple, j'ai ajouté des données pour un site supplémentaire (ligne 10). Non seulement le bénéfice hebdomadaire a été calculé automatiquement lorsque j'ai ajouté le bénéfice quotidien, mais j'ai également modifié la référence du site dans la cellule E2 en site I, et les données ont été récupérées correctement. Si j'avais utilisé des références directes, j'aurais dû faire plusieurs ajustements manuels pour arriver au même résultat.

Troisièmement, si je devais ajouter une autre colonne entre mes colonnes « Bénéfice quotidien » et « Bénéfice hebdomadaire », je pourrais être assuré que toutes les références à mes colonnes existantes resteraient sécurisées, car j'ai référencé les noms des colonnes plutôt que la lettre associée à chaque colonne.

Enfin, les références structurées sont dynamiques. Si je change le nom d'une de mes colonnes de table, toutes les références structurées associées seront mises à jour en conséquence. Dans cet exemple, j'ai modifié ma colonne « Bénéfice hebdomadaire » en « TOTALS » et la formule XLOOKUP dans la cellule E4 a adopté cet amendement.

Bref, dès que vous créez un tableau dans Excel, pensez à utiliser des références structurées pour tirer le meilleur parti de vos données.

Outre les avantages énumérés ci-dessus, les références structurées utilisent également moins de mémoire de votre ordinateur que les références directes – l'une des nombreuses façons d'accélérer vos feuilles de calcul Excel.