Références structurées dans Excel

Comment créer des références structurées dans Excel?

Les références structurées commencent par des tableaux Excel. Dès que les tableaux créés dans Excel, il crée automatiquement des références structurées pour vous.

Jetez maintenant un œil à l'image ci-dessous.

• Étape 1: J'avais donné un lien vers la cellule B3, au lieu d'afficher le lien comme B2, il apparaît comme Table1 [@Sales]. Ici, Table1 est le nom de la table et @Sales est la colonne à laquelle nous nous référons. Toutes les cellules de cette colonne sont référencées par un nom de table et suivies du nom de l'en-tête de colonne.
• Étape 2: Maintenant, je vais changer le nom de la table en Data_Table et changer l'en-tête de colonne en Montant .
• Étape 3: Afin de changer le nom de la table, placez un curseur dans la table> allez dans Design> Nom de la table.

• Étape 4: mentionnez le nom de la table comme Data_Table.

• Étape 5: Maintenant, changez pour donner une référence à la cellule B3.

Nous avons donc compris que la référence structurée comporte deux parties Nom de la table et Nom de la colonne.

Exemples

Vous pouvez télécharger ce modèle Excel de références structurées ici - Modèle Excel de références structurées

Exemple 1

En utilisant des références structurées, vous pouvez rendre votre formule dynamique. Contrairement aux références de cellules normales, il permet à la formule d'être active en cas d'ajout et de suppression dans la plage de données.

Permettez-moi d'appliquer la formule SUM pour la plage normale et la table Excel.

SOMME Formule pour la plage normale.

Formule SOMME pour tableau Excel.

Permettez-moi d'ajouter quelques lignes aux données de la table normale et Excel. J'ai ajouté 2 éléments de campagne aux données, voyez maintenant la différence.

La référence structurée dans le tableau Excel affiche la valeur mise à jour, mais la plage de données normale n'affiche pas les valeurs mises à jour, sauf si vous apportez des modifications à la formule manuellement.

Exemple # 2

Maintenant, regardez un autre exemple. J'ai un nom de produit, une quantité et des informations sur le prix. En utilisant ces informations, j'ai besoin pour arriver à la valeur de vente.

Afin d'obtenir la valeur des ventes, la formule est Qté * Prix . Appliquons cette formule dans le tableau.

La formule dit [@QTY] * [@PRICE]. Ceci est plus compréhensible que la référence normale de B2 * C2. Nous n'obtenons pas le nom de la table si nous mettons la formule à l'intérieur de la table.

Problèmes avec les références structurées Excel

En utilisant des références structurées, nous faisons face à certains problèmes qui sont énumérés ci-dessous.

Problème n ° 1

Les références structurées ont également leurs propres problèmes. Nous sommes tous habitués à appliquer la formule Excel et à la copier ou à la faire glisser vers les autres cellules restantes. Ce n'est pas le même processus dans les références structurées, cela fonctionne un peu différemment.

Maintenant, regardez l'exemple ci-dessous. J'ai appliqué la formule SUM dans Excel pour la plage normale.

Si je veux additionner le prix et la valeur de vente, je vais simplement copier et coller ou faire glisser la formule actuelle vers les deux autres cellules et cela me donnera la valeur SOMME du prix et de la valeur de vente.

Appliquez maintenant la même formule pour la table Excel pour la colonne Qté.

Maintenant, nous avons la somme de la colonne Qty. Comme la plage normale, la formule copie la formule actuelle et la colle dans la colonne Prix pour obtenir le total du prix.

Oh mon Dieu!!! Il n'affiche pas le total de la colonne Prix, mais affiche toujours uniquement le total de la colonne Qté. Ainsi, nous ne pouvons pas copier et coller cette formule dans la cellule adjacente ou dans toute autre cellule pour faire référence à la colonne ou à la ligne relative.

Faites glisser la formule pour modifier la référence

Maintenant que nous connaissons ses limites, nous ne pouvons plus faire le travail de copier-coller avec des références structurées. Alors, comment pouvons-nous surmonter cette limitation?

La solution est très simple, il suffit de faire glisser la formule au lieu de la copier. Sélectionnez la cellule de formule et utilisez la poignée de recopie et faites-la glisser vers les deux cellules restantes pour modifier la référence de colonne en Prix et Valeur de vente.

Nous avons maintenant mis à jour les formules pour obtenir les totaux respectifs.

Problème n ° 2

Nous avons vu un problème avec les références de structure et nous avons également trouvé la solution, mais nous avons encore un problème ici, nous ne pouvons pas faire l'appel en tant que référence absolue si nous faisons glisser la formule vers d'autres cellules.

Jetons un coup d'œil à l'exemple ci-dessous maintenant. J'ai une table de ventes avec plusieurs entrées et je souhaite consolider les données en utilisant la fonction SUMIF dans Excel.

Maintenant, je vais appliquer la fonction SUMIF pour obtenir les valeurs de ventes consolidées pour chaque produit.

J'ai appliqué la formule pour le mois de janvier, car il s'agit d'une référence structurée, nous ne pouvons pas copier et coller la formule dans les deux colonnes restantes, cela ne changera pas la référence en février et mars, je vais donc faire glisser la formule.

Oh!! Je n'ai obtenu aucune valeur dans la colonne février et mars. Quel serait le problème??? Regardez attentivement la formule.

Nous avons tiré la formule du mois de janvier. Dans la fonction SUMIF, le premier argument est Criteria Range Sales_Table [Product]  puisque nous avons fait glisser la formule qu'elle a modifiée vers Sales _Table [Jan].

Alors, comment y faire face ?? Nous devons faire le premier argument, à savoir la colonne Product comme absolue et les autres colonnes comme une référence relative. Contrairement à la référence normale, nous n'avons pas le luxe d'utiliser la touche F4 pour modifier le type de référencement.

La solution est que nous devons dupliquer la colonne de référencement comme indiqué dans l'image ci-dessous.

Maintenant, nous pouvons faire glisser la formule vers d'autres alésages de deux colonnes. La plage de critères sera constante et les autres références de colonne changeront en conséquence.

Astuce de pro: Afin de faire de la ROW une référence absolue, nous devons faire une double entrée de ROW, mais nous devons mettre le symbole @ avant le nom de ROW.

= Sales_Table [@ [Product]: [Product]]

Comment désactiver la référence structurée dans Excel?

Si vous n'êtes pas fan des références structurées, vous pouvez désactiver en suivant les étapes ci-dessous.

• Étape 1: Accédez à FICHIER> Options.
• Étape 2: Formules> Décochez Utiliser les noms de table dans les formules.

Choses à retenir

• Afin de faire la référence absolue dans la référence structurée, nous devons doubler le nom de la colonne.
• Nous ne pouvons pas copier la formule de référence structurée à la place, nous devons faire glisser la formule.
• Nous ne pouvons pas voir exactement à quelle cellule nous nous référons dans les références structurées.
• Si les références structurées ne vous intéressent pas, vous pouvez les désactiver.