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.

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.

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.

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Consulter la liste des formations offertes

Pour info: 514-605-7112 ou info@lecfomasque.com