Rapports de type tableau croisé avec des formules

Un tableau croisé dynamique croise les données : les lignes correspondent à une catégorie, les colonnes à une autre et les totaux remplissent la grille. Un exemple classique place les régions verticalement, les trimestres horizontalement et les ventes dans chaque cellule.

Les tableaux croisés dynamiques sont très utiles, mais ils nécessitent une actualisation manuelle et occupent un bloc fixe. Un tableau croisé piloté par des formules se reconstruit automatiquement dès que les données changent.

Dans cette leçon, vous agencerez les en-têtes de lignes, les en-têtes de colonnes et un corps de formules SUMIFS qui calculeront automatiquement chaque intersection.

Nous utiliserons une feuille nommée Ventes avec les colonnes suivantes : Région en A, Trimestre en B et Montant en C, sur les lignes 2 à 500.

Le rapport souhaité se présente ainsi :

• Étiquettes de lignes : chaque Région distincte dans la colonne E.
• Étiquettes de colonnes : T1, T2, T3, T4 sur la ligne 1, de F à I.
• Corps : total du Montant pour chaque paire Région-Trimestre.

Chaque cellule du corps répond à une question : combien cette région a-t-elle vendu pendant ce trimestre ?

Les en-têtes de lignes correspondent aux régions distinctes. Utilisez UNIQUE avec SORT afin qu’elles se déversent dans la colonne E tout en restant ordonnées.

Placez cette formule dans E2 :

Les régions remplissent désormais E2 et les cellules situées en dessous d’elles-mêmes. Comme pour les tableaux récapitulatifs, cette liste constitue l’ancrage vers lequel toute la grille se réfère.

Les en-têtes de colonnes correspondent aux trimestres répartis sur une ligne. Vous pouvez saisir manuellement T1, T2, T3, T4, ou les déverser horizontalement en entourant UNIQUE de TRANSPOSE.

Dans F1, cette formule place les trimestres distincts sur la ligne supérieure :

TRANSPOSE transforme une liste verticale en liste horizontale : une colonne de trimestres devient ainsi une ligne d’en-têtes. Les deux axes de la grille sont maintenant en place.

Remplissons maintenant le corps du tableau. Chaque cellule doit contenir le total correspondant à la région de sa ligne et au trimestre de sa colonne. SUMIFS gère facilement deux conditions.

Dans la première cellule du corps, F2, écrivez :

Cela signifie : le Montant lorsque la Région est égale à l’étiquette située à gauche et que le Trimestre est égal à l’en-tête situé au-dessus. Il s’agit d’une seule intersection du tableau croisé.

Les signes dollar permettent à une seule formule de remplir toute la grille par copie. Examinez les références mixtes :

• $E2 verrouille la colonne E, mais laisse la ligne changer : chaque ligne lit ainsi sa propre région.
• F$1 verrouille la ligne 1, mais laisse la colonne changer : chaque colonne lit ainsi son propre trimestre.
• $C$2:$C$500 est entièrement verrouillée, car la plage de données ne se déplace jamais.

Copiez F2 sur tous les trimestres et toutes les régions : chaque cellule s’ajuste parfaitement d’elle-même.

Une fois F2 correctement écrite, sélectionnez-la et faites glisser la poignée de recopie vers la droite sur les colonnes des trimestres, puis vers le bas sur les lignes des régions. Excel réécrit les parties relatives pour vous.

• La cellule G2 devient Région $E2 et Trimestre G$1.
• La cellule F3 devient Région $E3 et Trimestre F$1.

Vous obtenez un tableau croisé complet, avec le total de chaque intersection. Aucun assistant de tableau croisé n’est nécessaire et le résultat se recalcule dès que les données de Ventes changent.

Un véritable tableau croisé affiche des totaux généraux. Ajoutez une colonne Total à droite et une ligne Total en bas à l’aide de SUM simple sur chaque ligne.

Pour le total de la ligne de la première région, placez cette formule dans la colonne située après le dernier trimestre :

Pour le total d’une colonne, additionnez les cellules du corps correspondant à ce trimestre sur toutes les lignes. Ces totaux en bordure donnent au rapport un aspect complet et permettent aux lecteurs de vérifier rapidement la cohérence des nombres.

Si votre outil le permet, vous pouvez éviter la copie en fournissant directement les références de déversement à SUMIFS. Utilisez les en-têtes déversés comme critères.

Cette seule formule totalise chaque intersection de région et de trimestre :

Ici, E2# est la liste verticale des régions et F1# la liste horizontale des trimestres. Excel les associe pour créer une grille complète en une seule opération. La méthode du glisser-déposer est plus compatible, mais cette version moderne est plus élégante.

Les rapports sont plus instructifs lorsqu’ils affichent une part et pas uniquement des montants. Ajoutez une colonne qui exprime le total de chaque région en pourcentage du total général.

Si le total de la ligne de la région se trouve en J2 et le total général en J10, écrivez :

Le verrouillage du total général avec $J$10 vous permet de recopier la formule vers le bas pour toutes les régions, tout en divisant toujours par le même dénominateur. Mettez la colonne au format pourcentage et les lecteurs verront immédiatement quelles régions sont dominantes.

Quelques habitudes permettent de conserver un tableau croisé fondé sur des formules fiable :

• Référencez des plages complètes et généreuses, comme les lignes 2 à 500, afin d’inclure les nouvelles lignes.
• Verrouillez les plages de données avec des ancrages $ complets ; seules les références aux en-têtes doivent pouvoir changer.
• Laissez de l’espace vide en dessous et à droite afin que les en-têtes et les totaux déversés aient la place nécessaire.

Si vous procédez ainsi, ce rapport ne nécessitera aucune maintenance. Saisissez de nouvelles ventes et la grille, les totaux ainsi que les étiquettes se mettront tous à jour.

Vérifiez votre maîtrise des références mixtes qui font fonctionner un tableau croisé fondé sur des formules.

Vous avez recréé un tableau croisé dynamique avec de simples formules :

• UNIQUE associé à SORT a créé les en-têtes de lignes dans une colonne déversée.
• TRANSPOSE a réparti les en-têtes de colonnes sur une ligne.
• SUMIFS avec les références mixtes $E2 et F$1 a rempli chaque intersection, par glisser-déposer ou avec des références de déversement telles que E2# et F1#.
• SUM a ajouté les bordures des totaux généraux.

La grille entière se recalcule en direct. Vous allez maintenant rendre le tableau de bord interactif avec des menus déroulants qui pilotent les indicateurs.