L’utilisation d’Excel est souvent axée sur l’identification des relations entre différents ensembles de données. Pourtant, lors de l’insertion de formules complexes, l’utilisation excessive de références de cellules explicites relatives et absolues, telles que « B7 » et ses variantes, peut entraîner une barre de formules encombrée et chaotique.
L’utilisation de références structurées dans Excel peut améliorer ce processus en vous permettant d’attribuer des noms aux tableaux et à leurs en-têtes. Ces noms désignés servent de références de cellules implicites, permettant à Excel de récupérer et de calculer automatiquement les données structurées.
Vous trouverez ci-dessous plusieurs méthodes efficaces pour utiliser des références structurées dans Excel.
1. Effectuer des calculs dans des tableaux
Les références structurées fonctionnent exclusivement avec des tableaux, la méthode optimale pour les utiliser est donc dans ces types de tableaux.
Par exemple, créez un tableau simple allant de B2 à F8 contenant les informations sur les ventes d’un magasin. Assurez-vous d’étiqueter le tableau comme « Ventes » (vérifiez le « Nom du tableau » dans le coin supérieur gauche).
Ensuite, nous allons calculer le total pour chaque vente individuelle :
Étape 1 : sélectionnez la cellule F2 (en vous assurant que l’icône déroulante n’est pas cliquée). Accédez à « Accueil », puis à « Insérer » et choisissez « Insérer des colonnes de tableau à droite » pour inclure automatiquement une nouvelle colonne dans le tableau.
Étape 2 : Désignez l’en-tête de la nouvelle colonne G comme « Total ».
Étape 3 : Dans la cellule G3, saisissez =[@PricePerUnit]*[@Quantity]et appuyez sur Entrée. Ajustez la mise en forme de la cellule si nécessaire.
Les références « [@PricePerUnit] » et « [@Quantity] » correspondent à leurs champs respectifs dans les colonnes. Le symbole « @ » précédant les noms de colonne indique que chaque cellule de résultat dérivera ses valeurs de la même ligne du tableau.
Pour clarifier, la formule =[@PricePerUnit]*[@Quantity]de G3 correspond effectivement à =$C3*$D3.
2. Récupération d’une plage au-delà de la table
Lorsque vous souhaitez utiliser une référence structurée dans une cellule située en dehors du tableau, vous devez commencer la référence par le nom du tableau. Par exemple, dans notre exemple précédent, l’utilisation de « Ventes[Total] » récupérera toute la plage de valeurs sous la colonne « Total » du tableau « Ventes ». Cela fournit un tableau de plusieurs valeurs pour une manipulation ultérieure.
Voici à quoi cela ressemble dans Excel, en particulier dans la cellule I3, où il reste suffisamment d’espace pour que la plage s’étende vers le bas.
3. Somme et somme partielle d’une colonne
Pour obtenir rapidement la somme d’une colonne entière, utilisez la case à cocher « Total de la ligne » située dans les paramètres « Conception du tableau » (dans « Options de style du tableau »). Voici comment obtenir les totaux pour les colonnes « Quantité » et « Total ».
Bien que la ligne « Total » ne puisse pas être déplacée et soit systématiquement positionnée à la fin du tableau (permettant des entrées supplémentaires), vous pouvez dupliquer son résultat ailleurs :
- Pour calculer le total de chaque entrée dans la colonne « Total », utilisez
=SUM(Sales[Total]). - Pour obtenir la somme des seules entrées visibles, telles que celles qui apparaissent après le filtrage du tableau, appliquez
=SUBTOTAL(109,Sales[Total]). Cette formule reflète ce que fait l’option « Total de lignes » dans Format de tableau pour sa ligne.
Vous pouvez également calculer une somme partielle en fonction d’un critère spécifique trouvé dans le tableau sans avoir à le formater. Par exemple :
- Pour trouver le total des ventes attribuées à Mike, utilisez
=SUMIF(Sales[Seller]," Mike" ,Sales[Total]). Ici, « Mike » est une chaîne saisie manuellement. - Pour additionner tous les produits représentés par l’ID produit « 41230 », vous devez utiliser
=SUMIF(Sales[ProductID],41230,Sales[Total]). Notez que la colonne ProductID étant formatée comme « Général », vous pouvez saisir directement le numéro.
4. Implémentation de la validation des données à partir d’une table avec INDIRECT
En supposant que vous travaillez avec la table Sales mentionnée précédemment, vous pouvez définir des paramètres de validation des données personnalisés pour faciliter la recherche dans les données. Créons une table plus petite qui permet de sélectionner des identifiants de produit, des dates ou des vendeurs, ce qui vous permet de choisir un seul élément de ces ensembles pour afficher le sous-total.
Étape 1 : Dans la cellule B13, configurez la validation des données (onglet Données > Outils de données > Validation des données).
Étape 2 : Dans la fenêtre contextuelle qui s’affiche, sélectionnez « Liste » dans le menu déroulant « Autoriser » et saisissez manuellement les valeurs de la colonne dans le champ « Source », séparées par des virgules. Pour cet exemple, nous avons saisi « ProductID, Seller, Date ».
Étape 3 : Dans la cellule C13, établissez une autre instance de validation des données. Sélectionnez à nouveau « Liste ». Pour la « Source », incluez la formule : =INDIRECT("Sales["&B13&"]").
Étape 4 : Dans la cellule D13, appliquez la formule suivante : =SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total]).
Vous pouvez désormais sélectionner des options parmi les deux listes de validation des données, le sous-total résultant étant affiché dans D13.
Articles connexes:
Les 5 meilleures méthodes pour empêcher Windows 11 de collecter des données personnelles
7:30
Guide de téléchargement et d’installation du navigateur Safari sur Windows 11
7:26
Découvrez quatre fonctionnalités intéressantes de Windows Phone Link
16:03
Laisser un commentaire