Présentation

Nous avons déjà utilisé les calculs de table dans le tableau de douze camemberts que nous avions fait au chapitre Un premier tour de découverte. Nous allons maintenant revenir plus en profondeur sur ce puissant mécanisme de calcul afin vous puissiez comprendre et maîtriser ses nombreuses utilisations.

Comprendre le partitionnement et l’adressage

Les formules de calcul que nous avons utilisées au chapitre Requêtes complexes concernaient l’un ou l’autre de deux niveaux de calcul différents.

Niveau 1 : c’est le cas général, le niveau détaillé des données source. La formule donne un résultat pour chaque ligne de donnée.
Niveau 2 : le niveau agrégé, quand la formule comporte une fonction d’agrégation telle que la somme, la moyenne, etc. La formule donne alors un résultat pour chaque combinaison des dimensions utilisées sur le graphique.

Le calcul de table consiste à introduire un troisième niveau de calcul au-dessus du niveau agrégé ; autrement dit, à refaire des calculs à partir des totaux représentés sur le graphique. Prenons un exemple :

Créez un nouveau classeur Tableau, utilisant le fichier Ventes_AlimOL.txt.

Créez le champ calculé CA, avec comme formule [Prix Unitaire] * [Quantité].

Glissez le champ Secteur sur l’étagère Lignes et le champ CA sur la propriété Texte.

Le champ CA est une formule de niveau détaillé (niveau 1) : pour chaque ligne du fichier Ventes_AlimOL.txt, il y a un prix unitaire et une quantité, et Tableau calcule un CA en les multipliant. Sur le graphique, ce qui est représenté n’est pas le chiffre d’affaires mais la somme du chiffre d’affaires c’est un calcul agrégé (niveau 2), avec un résultat pour chaque valeur de la dimension Secteur, celle qui est utilisée sur le graphique.

Le terme de « ligne » est vite ambigu. Au niveau 1 du calcul détaillé, on parle de lignes de données, c’est-à-dire celles du ou des fichier(s) de données. Au niveau 2, on parle des lignes du graphique, c’est-à-dire celles qui sont définies par la ou le(s) dimension(s) placée(s) sur l’étagère Lignes.

Nous voulons maintenant afficher ce chiffre d’affaires sous forme de pourcentage, comme nous le ferions sur un camembert :

Faites un clic droit sur la mesure SOMME(CA) et choisissez Calcul de table rapide, puis Pourcentage...

Utiliser les fonctions à calcul de table

Les calculs de table rapide correspondent à une série de fonctions d’une catégorie spécifique (nommée également Calcul de table). Une première façon de découvrir ces fonctions est de récupérer la formule correspondant à un calcul de table mis en place à travers l’interface graphique, comme nous venons de le faire avec le pourcentage de total ou le classement. Pour cela, il suffit de glisser la gélule dans un champ calculé :

Créez un nouveau champ calculé et laissez ouverte la fenêtre de formule.

Prenez la gélule SOMME(CA) de la propriété Étiquette et glissez-la dans la fenêtre de formule.

Formule du calcul de table Pourcentage du total

Effacez la formule, puis répétez l’opération avec la gélule SOMME(CA) qui est sur l’étagère Lignes ; vous devez obtenir RANK([CA]).

Quand vous avez fini d’observer les syntaxes des deux formules, vous pouvez fermer la fenêtre de formule sans valider.

Explicitons le fonctionnement de ces deux formules : la fonction TOTAL permet d’appliquer un calcul d’agrégat à un périmètre de données plus large que la seule cellule où est situé le résultat, ce périmètre étant défini par la ou les dimension(s) de partitionnement. Par exemple, la Raclette Courdavault est affichée à 13,2 % sur Ivry ; ce chiffre correspond bien au calcul SUM([CA]) de la cellule correspondante divisée par le calcul TOTAL(SUM([CA])) de la partition Ivry, soit donc 4 779 / 36 100 ≈ 0,132 ≈ 13,2 %.

La fonction RANK calcule l’ordre de classement de la cellule en cours à l’intérieur de sa partition définie par la ou les dimension(s) de partitionnement, en triant les éléments définis par la ou les dimension(s) d’adressage par ordre décroissant de l’agrégat indiqué. Sur le même exemple de la Raclette Courdavault à Ivry, la fonction trie les produits par somme de chiffres d’affaires décroissante à l’intérieur de la partition Ivry. Le rang 1...

Découvrir les autres fonctions à calcul de table

Toutes les fonctions exploitant le mécanisme des calculs de table sont regroupées dans la catégorie éponyme :

Les fonctions à calcul de table

Sans chercher à tester chacune des fonctions, voici un petit panorama.

Les fonctions de position et de décalage : nous avons déjà vu FIRST, LAST et LOOKUP INDEX sert à numéroter les données selon l’ordre de tri de l’adressage.
Les fonctions MODEL_EXTENSION et SCRIPT_ sont des passerelles vers des API ou des extensions externes.
Les fonctions RANK_ correspondent au classement que nous avons vu au début de ce chapitre.
Les fonctions RUNNING_ sont les totaux cumulés.
La fonction SIZE donne le nombre de lignes de données agrégées dans chaque partition.
Nous avons déjà vu TOTAL, qui applique une agrégation sur le total de chaque partition.
Restent les fonctions WINDOW_ que nous avons utilisées, qui permettent de réagréger sur une fenêtre mobile à l’intérieur de chaque partition.

Tout cela fait beaucoup d’informations, il est temps de mettre en pratique avec un petit exercice.

Exercices : construire un diagramme de Pareto

La responsable marketing d’AlimOL s’intéresse en ce moment au principe du 80/20, qu’elle souhaiterait utiliser pour cibler ses campagnes plus efficacement. Elle voudrait que vous mettiez en place deux nouvelles analyses de la répartition du chiffre d’affaires autour des deux questions suivantes :

Quels sont les secteurs qui représentent 80 % du CA ?
Est-ce que 20 % des clients suffisent à générer 80 % du CA ?

Les analyses autour de la problématique du 80/20 se font typiquement à l’aide d’un diagramme de Pareto, inventé par l’ingénieur Joseph Juran et ainsi nommé en hommage à l’économiste Vilfredo Pareto, réputé être le premier à observer que 80 % de la richesse étaient détenus par seulement 20 % de la population. Il existe différentes variantes de ce diagramme, plus ou moins complexes.

Commençons par le cas le plus simple, celui du CA par secteur. La responsable marketing vous soumet la maquette suivante :

Répartition du CA 2024 par secteur (maquette)

Voici le fonctionnement du diagramme :

Les barres verticales représentent simplement le CA par secteur ; elles doivent être triées par ordre de CA décroissant.
La courbe représente...

Solutions avec l’interface graphique des calculs de table : construire un diagramme de Pareto

Exercice 1 : créez ou réutilisez un classeur fondé sur le fichier Ventes_AlimOL.txt ; sur une nouvelle feuille, construisez le diagramme à barres verticales pour l’année 2024, sans vous soucier de la courbe pour l’instant.

Pas de difficulté particulière à cette étape :

Si vous êtes sur un nouveau classeur, pensez à créer un champ calculé nommé CA avec la formule [Prix Unitaire] * [Quantité].

Filtrez sur l’année 2024, mettez le Secteur sur l’étagère Colonnes et le CA sur l’étagère Lignes.

Faites un clic droit sur la gélule Secteur, et Triez par Champ, ledit champ étant le CA avec une Agrégation par Somme, en Ordre décroissant.

Exercice 2 : ajoutez une courbe représentant le pourcentage du chiffre d’affaires total de chaque secteur.

Il faut utiliser un calcul de table, puis passer en axe double :

Glissez à nouveau le champ CA sur l’étagère Lignes afin d’avoir deux gélules SOMME(CA).

Faites un clic droit sur la seconde gélule, choisissez Calcul de table rapide et Total cumulé.

Sur l’étagère Repères du premier CA, réglez le type de repère sur Barre plutôt qu’Automatique.

Sur sur l’étagère Repères du second CA (celui qui porte le symbole Δ), réglez le type de repère sur Ligne.

Revenez sur l’étagère Lignes, faites un clic droit sur la mesure du CA cumulé et sélectionnez Axe double.

Exercice 3: modifiez la courbe pour cumuler le pourcentage du total.

Faites un clic droit sur la gélule du CA en total cumulé et choisissez Modifier le calcul de table.

Cochez la case Ajouter un calcul secondaire, puis paramétrez ce calcul secondaire en Pourcentage du total.

Utiliser un calcul secondaire

Exercice 4 : ajoutez la ligne horizontale à hauteur de 80 % du CA total.

Pas de difficulté, à condition...

Solutions avec des formules de calcul de table : construire un diagramme de Pareto

Exercice 1 : créez ou réutilisez un classeur fondé sur le fichier Ventes_AlimOL.txt ; sur une nouvelle feuille, construisez le diagramme à barres verticales pour l’année 2024, sans vous soucier de la courbe pour l’instant.

Pas de difficulté particulière à cette étape :

Si vous êtes sur un nouveau classeur, pensez à créer un champ calculé nommé CA avec la formule [Prix Unitaire] * [Quantité].

Filtrez sur l’année 2024, mettez le Secteur sur l’étagère Colonnes et le CA sur l’étagère Lignes.

Faites un clic droit sur la gélule Secteur, et choisissez Trier par Champ, ledit champ étant le CA avec une Agrégation par Somme, en Ordre décroissant.

Exercice 2 : ajoutez une courbe représentant le pourcentage du chiffre d’affaires total de chaque secteur.

Créez un champ calculé nommé CA cumulé avec la formule ci-dessous :

RUNNING_SUM(SUM([CA]))

Les fonctions RUNNING_ effectuent un cumul depuis le début de la partition jusqu’à la position en cours. La formule ci-dessus est donc équivalente à WINDOW_SUM(SUM([CA]), FIRST(), 0), mais sa syntaxe est plus simple.

Glissez le nouveau champ sur l’étagère Lignes, à droite de la gélule SOMME(CA).

Sur l’étagère Repères SOMME(CA), choisissez le type de repère Barre plutôt qu’Automatique.

Sur l’étagère Repères du CA cumulé (donc SOMME(CA) ∆), choisissez le type de repère Ligne.

Sur l’étagère Lignes, faites un clic droit sur la gélule du CA cumulé et sélectionnez Axe double.

Exercice 3 : modifiez la courbe pour cumuler le pourcentage du total.

Il y a plusieurs façons d’écrire...

Aller plus loin avec les calculs de table