Excel: Ce qu’il faut savoir sur les formules appliquées à des données sous forme de tableau

Publié le 19 février 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Formules: Données sous forme de tableau

Excel: Ce qu’il faut savoir sur les formules appliquées à des données sous forme de tableau

Depuis 2011, je vous ai parlé à quelques reprises des tableaux Excel (option « Mettre sous forme de tableau » du menu d’accueil ou encore « Format as table » en anglais). Depuis, les tableaux Excel sont devenus essentiels à ma pratique, notamment à l’élaboration de tableaux de bord et d’outils de gestion dans Excel. De plus, ils font partie intégrante de la suite d’outils Power BI. En effet, toutes les données importées dans Excel via Power Query et/ou Power Pivot, sont mises sous forme de tableau. Pour relire mes anciens articles sur le sujet, vous pouvez vous rendre sur les pages suivantes:

Aujourd’hui, je souhaite mettre l’accent sur les formules que l’on applique à des données mises sous forme de tableau dans Excel. Si vous souhaitez creuser davantage cette notion, je vous recommande notamment le site exceltables.com et le livre Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables.

 

Formules liées aux données mises sous forme de tableau: La base

Si vous créez une somme des unités vendues, à partir du tableau Excel ci-bas (nommé « Ventes »), en sélectionnant simplement la colonne de données #Unités, vous obtiendrez la première version de la formule « Somme des unités » ci-bas. Toutefois, vous pouvez modifier cette formule afin de la faire correspondre davantage à vos besoins.

Formules: Données sous forme de tableau

 

En effet, si vous rédigez la formule de somme via la barre de formules, Excel vous fournira une liste de possibilités, tel qu’illustré ci-bas. Vous pourrez aisément choisir une colonne de données ou encore un élément qui servira à raffiner votre formule, comme #All, #Data, #Headers, # Totals, @ – This Row.

Formules: Données sous forme de tableau

 

Voici ce que signifient ces éléments:

  • # All: Fait référence au tableau en entier, incluant les titres de colonnes et les totaux de colonnes, si activés
  • #Data: Fait référence à une plage de données dans le tableau
  • #Headers: Fait référence aux titres de colonnes du tableau, si activés
  • #Totals: Fait référence aux totaux de colonnes, si activés
  • @ – This Row: Fait référence aux éléments du tableau se trouvant sur la ligne où la formule est insérée

 

Votre premier réflexe pourrait être de dire que toutes les formules de « Somme des unités » ci-bas sont équivalentes, mais ce serait faux. Par exemple, si vous jetez un coup d’oeil à la 3ième formule de « Somme des unités » ci-bas, vous remarquerez que la somme des unités n’est pas de 214,516 mais de 34. Cela s’explique par le fait que la formule fait référence à la ligne de total du tableau et cette ligne de total utilise la fonction Sous.total (ou Subtotal en anglais). Elle fait donc la somme des unités dont les lignes ne sont pas masquées.

Formules données mises sous forme de tableau

 

Lorsque vous ajoutez une ligne de total à un tableau, c’est à vous de choisir la formule qui répond à vos besoins. Lorsque vous choisissez la formule Somme ou Sum ci-bas, il s’agit en fait de la fonction Sous.Total ou Subtotal. Vous pouvez également écrire une formule personnalisée qui vous conviendrait davantage.

Formules données mises sous forme de tableau

 

Dans l’exemple ci-bas, j’ai choisi la fonction Nb ou Count plutôt que Somme ou Sum. On voit que le résultat de la somme des unités a varié en conséquence.

Formules données mises sous forme de tableau

 

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

 

Références de cellules, de colonnes et de tables

Dans l’exemple ci-bas, j’ai souhaité démontré deux choses.

D’abord, à la cellule M11, je souhaitais montrer que lorsque l’on fait référence à une cellule qui fait partie d’un tableau, Excel insère d’abord le nom de la table et place ensuite un @ devant le nom du champ. De même, lorsque l’on fait référence à une colonne qui fait partie d’un tableau, Excel insère d’abord le nom de la table et place ensuite le nom du champ entre […] (voir cellule N11 ci-bas).

Ensuite, je souhaitais montrer la logique de lignes concernant les formules liées à un tableau Excel. En M15, vous pouvez apercevoir une formule qui multiplie le prix unitaire par le nombre d’unités qui se trouvent tous les deux sur la ligne 15. Si vous placez votre curseur dans la cellule M17 et que vous sélectionnez la cellule I15 et que vous la multipliez par la cellule J15, vous n’obtiendrez pas une formule de tableau. Vous obtiendrez une formule à base de référence cellulaire. C’est parce que vous ne respectez pas le principe de la ligne….. De même si vous copiez la formule de la cellule M15 à la cellule M5, vous obtiendrez #VALUE! puisque qu’il n’y a aucune donnée à la ligne 5, le tableau débutant à la ligne 10.

Formules: Données sous forme de tableau

 

Copie de formules à l’intérieur d’un tableau

Dans l’exemple ci-bas, j’ai créé une formule dans une deuxième table, nommée « Somme », qui s’approvisionne à la table « Ventes ». Cette formule me permet de faire la somme des unités par région.

Formules: Données sous forme de tableau

 

Mais attention, si je copie/colle ensuite cette formule dans la colonne adjacente pour obtenir les ventes sous-jacentes, Excel ne s’ajustera pas puisque les références ne sont pas relatives. J’obtiens donc deux fois la même colonne.

Formules: Données sous forme de tableau

 

Pour copier judieusement la formule à ma colonne de vente, je dois plutôt utiliser la poignée de copie (la croix noire dans le coin inférieur droit).

Formules: Données sous forme de tableau

 

 

On voit à présent que la formule s’est bien ajustée au titre de colonne. Notez également que je fais référence à la colonne Région de la façon suivante: [[Région]:[Région]], c’est ce qui permet de garder cette portion de la formule, fixe.

Formules: Données sous forme de tableau

 

Formules pour déterminer des montants cumulatifs

Voici une autre formule qui pourrait vous être utile. Ci-bas, j’ai créé une 3ième table, nommée « Cumulatif ». Je lui demande de faire la somme de la colonne ventes mais d’arrêter le total à la ligne où se trouve la formule. Cela me permet d’obtenir les ventes cumulatives.

Formules: Données sous forme de tableau

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

1 réflexion sur “Excel: Ce qu’il faut savoir sur les formules appliquées à des données sous forme de tableau”

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top