Consolidation de plusieurs feuilles Excel par formule

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Consolider plusieurs feuilles Excel

Avec cette nouvelle astuce Excel, nous allons apprendre à consolider les données émanant de plusieurs feuilles, sur une seule et même feuille. Et nous allons le voir, l'astuce réside dans une technique de calcul.



Dans l'exemple illustré par la capture, nous récoltons les résultats de différentes agences d'une entreprise. Chaque bilan est restitué dans une feuille indépendante. Dans une dernière feuille nommée synthèse, nous produisons la consolidation de toutes ces données cumulées.

Classeur source et présentation
Pour la découverte de cette astuce Excel, nous suggérons de récupérer un classeur offrant déjà ces données à consolider.

• Télécharger le classeur consolider-donnees-feuilles.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier résultant pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons donc un classeur constitué de cinq feuilles. Les quatre premières représentent les résultats respectifs des agences sur le premier trimestre pour différents articles. Bien sûr, les données y sont organisées de la même façon. Par exemple, chaque total pour le premier article vendu est situé dans une cellule dont les coordonnées sont identiques d'une feuille à une autre. La dernière feuille se nomme donc Synthèse. C'est elle qui doit consolider pour chaque article représenté, les résultats cumulés sur trois mois pour les quatre agences.

Sommes sur plusieurs feuilles
Quand les feuilles se suivent comme c'est le cas ici, la consolidation des résultats par calcul est un jeu d'enfants. Mais nous le verrons, lorsque l'enchaînement de ces feuilles est rompu, la technique reste relativement simple.

• Cliquer sur l'onglet Synthèse en bas de la fenêtre Excel pour activer sa feuille,
• Désigner le premier résultat consolidé à trouver en cliquant sur sa cellule C4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Agence1 pour désigner sa feuille,
• Tout en maintenant la touche Maj (Shift) enfoncée, cliquer sur l'onglet Agence4,

Nous réunissons ainsi les quatre feuilles dans une même sélection. Il en résulte la syntaxe suivante : 'Agence1:Agence4'!.

• Sur la première feuille toujours active par défaut, désigner le premier total, soit la cellule F4,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,

De cette manière et comme vous le savez, nous conservons active la cellule du résultat. L'objectif est de l'exploiter dans l'enchaînement.

• Double cliquer sur la poignée de la cellule du résultat,

Nous répliquons ainsi la logique du calcul sur la hauteur du tableau. Grâce à cette syntaxe :

=SOMME(Agence1:Agence4!F4)

Ce sont les totaux du premier article pour chaque feuille qui sont consolidés.



Et grâce à la réplication de la formule sur les lignes du dessous, ce sont les totaux respectifs de chaque article qui sont cumulés et livrés dans la feuille Synthèse.

La vérification est très simple à obtenir en consultant les totaux fournis sur chaque feuille.

Sommes cumulées sur des feuilles séparées
Jusque-là, nous avons donc traité un cas idéal. Toutes les feuilles se suivent. Nous allons maintenant nous confronter au cas où des intrus se glissent dans l'énumération. Il s'agit de feuilles intercalées portant des valeurs numériques à ne pas considérer. Or, avec la syntaxe précédente, ces données sont intégrées de la première à la dernière feuille mentionnée. Pour l'exemple, nous proposons de dupliquer l'une des feuilles existantes.

• En bas de la fenêtre Excel, cliquer droit sur l'onglet Agence1,
• Dans le menu contextuel, choisir la commande Déplacer ou Copier,

• Dans la section Avant la feuille de la boîte de dialogue, sélectionner la feuille Agence4,
• En bas de la boîte de dialogue, cocher la case Créer une copie,
• Puis, valider en cliquant sur le bouton Ok de la boîte de dialogue,

• En bas de la fenêtre Excel, double cliquer sur l'onglet Agence1 (2) fraîchement créé,

Cette astuce permet d'activer rapidement la saisie de ce dernier.

• Saisir le nom suivant : Intrus et le valider avec la touche Entrée du clavier.
• En bas de la fenêtre Excel, cliquer sur l'onglet Synthèse pour revenir sur sa feuille,

Comme vous pouvez le voir, les résultats consolidés ont changé. Comme la feuille Intrus est intercalée dans l'énumération, ses données numériques sont considérées et cumulées avec les autres. Nous souhaitons l'exclure de la synthèse.

• Sélectionner tous les résultats consolidés, soit la plage de cellules C4:C9,
• Enfoncer la touche Suppr du clavier pour les effacer,
• Puis, sélectionner de nouveau la première cellule, soit C4,
• Taper le symbole égal (=) pour initier la nouvelle formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Agence1 pour désigner sa feuille,
• Tout en maintenant la touche Maj (Shift) enfoncée, cliquer sur l'onglet Agence3,
• Puis, sur la feuille encore active, cliquer sur le premier total, soit la cellule F4,

Nous obtenons pour l'instant une syntaxe similaire à la précédente : Agence1:Agence3!F4. Mais, la feuille Agence4 est encore manquante.

• Taper un point-virgule (;) pour annoncer les nouvelles données à ajouter,
• En bas de la fenêtre Excel, cliquer sur l'onglet Agence4 pour désigner sa feuille,
• Puis, cliquer de nouveau sur la cellule F4,
• Fermer alors la parenthèse de la fonction Somme,
• Valider le calcul par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique de la formule,

Nous retrouvons bien la même consolidation que celle exécutée précédemment. Grâce à cette syntaxe : =SOMME(Agence1:Agence3!F4; Agence4!F4), nous excluons bien les données de la feuille Intrus intercalée. Et cette syntaxe nous est finalement familière. Dans une fonction Excel, les deux points permettent d'énumérer une plage de la première à la dernière mentionnée. Le point-virgule permet d'intégrer indépendamment une nouvelle référence.

Et si la feuille Intrus avait été intercalée au beau milieu de l'énumération, soit entre les feuilles Agence2 et Agence3, nous aurions pu exploiter une seconde fois la touche Maj, après avoir inscrit le point-virgule pour signifier la rupture : =SOMME(Agence1:Agence2!F4; Agence3:Agence4!F4).