Consolider des chiffres périodiques

Pour illustrer les ventes d’un magasin, vous avez créé des tableaux de ventes en affectant chaque trimestre à une feuille de classeur Excel. Vous souhaitez maintenant consolider les résultats, c’est-à-dire recueillir, dans une feuille Total, les sommes des chiffres de chaque trimestre. Il existe pour cela, deux techniques que nous avons choisi d’illustrer avec Excel 2007. Vous pourrez toutefois facilement adapter les manipulations à un autre tableur.

Vérifiez l’homogénéité des tableaux

Vous simplifierez considérablement le travail si vous adoptez la même disposition pour chaque feuille de détail et pour celle de totaux. En d’autres termes, les mêmes données doivent, sur les différentes feuilles, occuper des emplacements identiques. Si une donnée manque pour une période, laissez vide la ligne (ou la colonne) correspondante mais ne la supprimez pas. Dans l’exemple illustré ici, le classeur se compose de quatre feuilles trimestrielles Q1 à Q4 et d’une feuille Année censée recevoir les totaux. Dans chaque feuille, les chiffres occupent le champ B2:E8 et les totaux figurent en colonne F et en ligne 9.

Ouvrez l’assistant de consolidation

Pour calculer le total des quatre trimestres, sélectionnez la feuille Année et sélectionnez la cellule B2. Activez le menu Données et, dans le Ruban, cliquez sur le bouton Consolider. Cliquez maintenant sur le petit bouton qui porte une flèche rouge, à droite du champ Référence. Activez l’onglet Q1 (le premier trimestre) et étendez, à la souris, la sélection sur le champ B2:E8. Cliquez de nouveau sur le bouton rouge : le champ sélectionné (‘Q1’!$B$2:$B$8) apparaît dans le champ Référence. Cliquez sur Ajouter pour que cette plage soit prise en compte dans le total. Refaites alors la même chose pour les trois autres feuilles trimestrielles Q2, Q3 et Q4. La fenêtre Consolider doit maintenant ressembler à l’écran ci-dessous. Enfin, cliquez sur OK : les totaux sont placés dans la feuille Année.

Mettez à jour les totaux

Les données que l’Assistant de consolidation vient d’écrire dans la feuille Année sont des nombres, pas des formules. Par conséquent, si une donnée d’une feuille de détail change, les totaux ne seront pas mis à jour. Les actualiser est heureusement plus rapide que de les créer. Sélectionnez la feuille Année, la cellule B2, cliquez sur le bouton Consolider : les champs de détail sont toujours en place. Validez par OK.

Utilisez des formules 3D

L’Assistant convient pour les cas simples, ceux pour lesquels on applique le même traitement – ici l’addition – à toutes les feuilles de détail. Pour des besoins plus précis, vous devrez exploiter des formules 3D, exploitant des coordonnées en trois dimensions ligne / colonne / feuille. Par exemple, lors du calcul du total, vous devez ajouter un pourcentage de 15 % aux chiffres du premier trimestre (autrement dit, les multiplier par 1,15) pour prendre en compte les marchandises d’exportation. Vous ajouterez, de même, des taux respectifs de 20 %, 25 % et 40 % aux chiffres des trois autres trimestres. La formule globale sera donc Année = (Q1x1,15) + (Q2x1,20) + (Q3x1,25) + (Q4x1,40). Pour créer cette formule, sélectionnez la feuille Année, puis la cellule B2. Tapez = pour débuter une formule puis cliquez sur l’onglet Q1 pour le premier trimestre. Cliquez en B2 puis tapez *1,15+ et sélectionnez la feuille Q2. Cliquez en B2 et tapez *1,2+ puis sélectionnez la feuille Q3. Cliquez en B2 puis tapez *1,25+ et sélectionnez Q4. Cliquez en B2 et tapez *1,4. Validez par OK. Vous voyez en B2 la formule définitive =’Q1′!B2*1,15+’Q2′!B2*1,2+’Q3′!B2*1,25+’Q4′!B2*1,4. Recopiez-la sur le champ B2:E8, placez les formules de somme en ligne 9 et en colonne F. Votre calcul est terminé.