Tables récapitulatives avec des tableaux dynamiques

Un tableau récapitulatif condense une longue liste de lignes brutes en un bloc réduit et lisible : une ligne par catégorie, avec les totaux à côté. Imaginez un journal des ventes comportant des centaines de lignes, transformé en un tableau ordonné affichant chaque région et son chiffre d’affaires total.

L’ancienne méthode consistait à utiliser un tableau croisé dynamique manuel qu’il fallait actualiser. La méthode moderne utilise des formules de tableaux dynamiques qui se mettent à jour dès que vos données changent. Aucun bouton, aucune actualisation.

Dans cette leçon, vous combinerez trois outils puissants : UNIQUE pour lister les catégories, SUMIFS pour totaliser chacune d’elles et FILTER pour extraire les lignes correspondantes. Ensemble, ils construisent un récapitulatif dynamique.

Imaginez une feuille nommée Ventes avec trois colonnes : Région dans la colonne A, Produit dans la colonne B et Montant dans la colonne C, sur les lignes 2 à 200.

Notre objectif est de créer un récapitulatif affichant chaque région distincte et le total de ses ventes. Le premier défi consiste à obtenir une liste propre des régions sans les saisir manuellement, car de nouvelles régions peuvent apparaître ultérieurement.

• A2:A200 contient de nombreux noms de régions répétés comme Est, Ouest, Est, Nord.
• Nous voulons uniquement : Est, Ouest, Nord, chacun affiché une seule fois.

Cette liste distincte constitue la base de tout le récapitulatif.

La fonction UNIQUE prend une plage et renvoie chaque valeur une seule fois. Elle se déverse, c’est-à-dire qu’une seule formule remplit autant de cellules qu’il y a de valeurs distinctes.

Saisissez-la dans la cellule E2 et la liste des régions s’affiche automatiquement en dessous :

Si une nouvelle région est ajoutée aux données ultérieurement, la liste déversée s’agrandit d’elle-même. Vous ne modifiez jamais la formule.

Nous devons maintenant obtenir le total des montants de chaque région de la colonne E. SUMIFS additionne les valeurs d’une plage uniquement lorsqu’une autre plage correspond à une condition.

La structure est SUMIFS(sum_range, criteria_range, criteria). Placez cette formule dans F2, à côté de la première région :

La référence E2# est l’élément clé. Le signe # désigne l’ensemble de la plage déversée à partir de E2. Ainsi, cette seule formule totalise toutes les régions produites par UNIQUE.

La référence de déversement E2# désigne toujours le bloc complet produit par une formule, quelle que soit sa taille. C’est ce qui rend le récapitulatif dynamique.

Lorsque UNIQUE trouve 3 régions, E2# mesure 3 cellules de haut et SUMIFS renvoie 3 totaux. Lorsque les données passent à 5 régions, les deux plages s’étendent ensemble, sans aucune modification.

• E2 = uniquement la cellule supérieure.
• E2# = l’ensemble du tableau déversé à partir de E2.

Familiarisez-vous avec le signe # : il est au cœur des formules de tableaux de bord.

Un récapitulatif est plus lisible lorsque les totaux sont ordonnés. Encadrez la liste des régions avec SORT pour afficher les catégories par ordre alphabétique, ou triez l’ensemble du tableau selon le total.

Pour lister les régions par ordre alphabétique dans E2 :

Comme les totaux de la colonne F font toujours référence à E2#, le tri des régions réaligne automatiquement les totaux. Les deux colonnes restent parfaitement synchronisées.

Vous souhaitez parfois afficher les lignes sous-jacentes d’une catégorie plutôt que son seul total. FILTER renvoie chaque ligne qui respecte une condition et les déverse.

Pour afficher toutes les lignes de ventes dont la Région est égale à la valeur de la cellule H1 :

Si H1 contient Est, vous obtenez chaque ligne de la région Est. Remplacez H1 par Ouest et le bloc se réécrit instantanément. C’est la base d’une vue d’exploration détaillée dans un tableau de bord.

FILTER renvoie une erreur #CALC! lorsqu’aucune correspondance n’est trouvée. Pour conserver un affichage propre, fournissez un message de remplacement comme troisième argument facultatif.

Le troisième argument s’affiche lorsqu’il n’y a aucune correspondance :

Désormais, une région sans ventes affiche une note explicite plutôt qu’une erreur. Ajoutez toujours ce message de remplacement dans les tableaux de bord afin qu’une sélection inattendue ne compromette jamais la mise en page.

Un récapitulatif indique souvent le nombre de commandes de chaque région, et pas seulement les montants. COUNTIFS compte les lignes qui respectent une condition, tout comme SUMIFS, mais sans plage de somme.

Placez cette formule dans la colonne G, à côté des totaux :

Votre récapitulatif à trois colonnes affiche désormais Région, Ventes totales et Nombre de commandes, le tout piloté par la seule liste de régions déversée dans E2#. Tout s’actualise ensemble.

Voici la recette complète, placée côte à côte :

• E2 : =SORT(UNIQUE(Sales!A2:A200)) liste les régions.
• F2 : =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) totalise chacune d’elles.
• G2 : =COUNTIFS(Sales!A2:A200, E2#) compte chacune d’elles.

Seule la formule E2 est saisie sur les lignes ; F et G se déversent à partir de la référence #. Ajoutez une nouvelle vente n’importe où dans Ventes et les trois colonnes se mettent à jour sans aucun clic.

Un récapitulatif fondé sur des formules offre de véritables avantages par rapport à la saisie de valeurs ou à l’actualisation d’un tableau croisé dynamique :

• Dynamique : il recalcule les résultats dès que les données changent.
• Redimensionnement automatique : les nouvelles catégories apparaissent automatiquement grâce à UNIQUE et à la référence #.
• Transparent : chacun peut lire la logique directement dans la cellule.

En contrepartie, les plages déversées ont besoin d’espace vide pour s’agrandir ; nous verrons les déversements bloqués dans une prochaine leçon. Pour le moment, laissez de la place sous vos formules.

Évaluez ce que vous avez appris sur la création d’un tableau récapitulatif qui se met à jour seul.

Vous avez créé un tableau récapitulatif qui se gère tout seul :

• UNIQUE liste chaque catégorie une seule fois et déverse le résultat.
• SORT ordonne cette liste pour la rendre plus lisible.
• SUMIFS et COUNTIFS totalisent et comptent chaque catégorie à l’aide de la référence de déversement E2#.
• FILTER extrait les lignes correspondantes pour une exploration détaillée, avec un message de remplacement en l’absence de correspondance.

Comme chaque formule s’appuie sur la liste déversée, l’ajout de nouvelles données met à jour l’ensemble du récapitulatif sans aucune intervention manuelle. Vous allez ensuite recréer des rapports complets de style tableau croisé uniquement avec des formules.