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. Dans cet article, 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.
Pour relire mes anciens articles sur le sujet, vous pouvez vous rendre sur les pages suivantes:
- Découvrez la magie des tableaux Excel
- Excel: Mettre sous forme de tableaux
- Manipuler efficacement les tableaux Excel
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.
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.
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.
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.
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.
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.
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.
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.
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).
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 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.
salu a vous et merci pour tout
svpl j aimerais savoir formations il existe en format video si oui puis je avoir le lien