Regrouper et consolider des données Excel

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


Sujets que vous pourriez aussi aimer :


Consolider des données avec Excel

La consolidation de données permet à une entreprise de regrouper et combiner toutes les données, qui auraient été saisies depuis plusieurs endroits ou à différents moments afin d'offrir une vue synthétisée. Excel offre deux possibilités pour résumer les résultats en les consolidant, soit par les formules, soit par une boîte de dialogue.

Consolider les données avec une formule
Cette technique impose que les tableaux sources aient tous la même structure afin de permettre à la formule de consolidation, de faire la correspondance entre les données à consolider.

• Télécharger le classeur consolidation-donnees.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,

La feuille Synthèse des ventes s'active par défaut. Elle propose un tableau dans lequel sont référencés, dans la colonne de gauche, les noms des vendeurs de l'entreprise. La colonne Total quant à elle, est vide pour l'instant. Elle doit accueillir la synthèse des ventes consolidées, réalisées par chacun des vendeurs au cours de l'année.

• Cliquer sur l'onglet Ventes T1 en bas de la fenêtre Excel,

Vous affichez ainsi le tableau synthétisant les chiffres réalisés par chacun des vendeurs, au cours du premier trimestre. Si vous affichez tour à tour, les feuilles Ventes T2, Ventes T3 et Ventes T4, vous constaterez qu'il s'agit de la synthèse des ventes, pour ces mêmes vendeurs, pour chaque trimestre. Les tableaux ont donc tous la même structure. Ils possèdent le même nombre de lignes, correspondant aux noms des vendeurs ainsi que le même nombre de colonnes, dont la colonne Total. Ce principe est fondamental pour que la formule de consolidation puisse réaliser les liens cohérents, avec les données à regrouper.

Pour faciliter l'interprétation des résultats, la feuille Synthèse des ventes propose donc, de consolider la somme des chiffres réalisés par chacun des vendeurs, au cours des 4 trimestres. N'importe quelle formule Excel peut être utilisée pour consolider des données issues de sources différentes. C'est la méthode de conception de la formule qui indique à Excel de consolider les valeurs numériques, pour offrir l'opération de synthèse correspondant à la formule choisie.

Consolider la somme des chiffres d'affaires
Pour réaliser la synthèse des chiffres réalisés par chaque vendeur, dans le tableau de la feuille Synthèse des ventes, nous devons effectuer une somme des totaux par trimestre pour chacun d'entre eux. Bien sûr, pour que la méthode de consolidation soit intéressante, il ne s'agit pas de réaliser une somme en désignant chaque cellule du total, sur chacune des feuilles des ventes par trimestre. Mais le début de la procédure est identique.

• Activer la feuille Synthèse des ventes en cliquant sur son onglet,
• Sélectionner la première cellule du total, soit C6 et taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit Somme(,
• Cliquer alors sur l'onglet de la feuille Ventes T1,
• Puis, tout en maintenant la touche MAJ enfoncée (Shift en anglais), cliquer tour à tour sur les onglets des feuilles Ventes T2, Ventes T3 et Ventes T4 pour les inclure dans la consolidation,
• Cliquer alors sur la cellule du total correspondant de la feuille Ventes T1 restée active, soit F6,
• Fermer la parenthèse de la fonction et valider le calcul par CTRL + Entrée,

Ce raccourci permet de valider le calcul en conservant la cellule active, dans le but par exemple, de le reproduire dans la foulée. Cette technique et tant d'autres sont d'ailleurs enseignées dans le support de formation sur les trucs et astuces Excel.


La capture ci-dessus illustre la formule de consolidation ainsi conçue. Seule la cellule du total de la feuille Ventes T1 pour le vendeur Hamalibou a été désignée. Mais comme les quatre feuilles ont été préalablement englobées dans la sélection (Touche Maj), et que chaque tableau possède la même structure, chaque cellule F6 pour ce même vendeur a été désignée pour chaque trimestre.

C'est d'ailleurs ce qu'indique le contenu de la fonction Somme : 'Ventes T1:Ventes T4'!F6. Les deux points (:) permettent habituellement de désigner une plage de cellules. Cette fois, ils désignent une plage de feuilles. Littéralement, ce contenu signifie : Pour chaque cellule F6 de la feuille Ventes T1 jusqu'à la feuille Ventes T4, faire la somme des valeurs.

• Double cliquer sur la poignée de la cellule afin de reproduire la consolidation pour l'ensemble des vendeurs dans la colonne,

Afin que cette consolidation soit encore plus spectaculaire, nous pouvons utiliser une méthode plus productive pour répliquer les données à consolider, sur tous les vendeurs.

• Supprimer toutes les données des résultats, de C6 à C11,
• Sélectionner de nouveau la plage de cellules vides, soit C6:C11,
• Taper le symbole = pour débuter le calcul,

Celui-ci s'initie dans la première cellule de la plage sélectionnée, soit C6. Tout le reste de la procédure est inchangé. C'est la présélection des cellules pour recevoir les données consolidées qui est importante.

• Taper le nom de la fonction de synthèse et ouvrir la parenthèse soit Somme(,
• Cliquer sur l'onglet de la feuille Ventes T1 pour la sélectionner,
• Tout en maintenant la touche MAJ enfoncée (Shift), cliquer sur les onglets Ventes T2, Ventes T3 et Ventes T4 pour les intégrer dans la sélection,
• Cliquer alors sur la cellule F6 du total de la feuille Ventes T1 restée active,
• Fermer la parenthèse de la fonction et valider le calcul par CTRL + Entrée,

Comme la plage de cellules était présélectionnée, le raccourci clavier CTRL + Entrée permet de reproduire la consolidation sur l'ensemble des cellules en même temps qu'il valide la formule.

Comme vous l'avez compris, cette technique peut s'étendre à n'importe quel domaine. On pourrait par exemple l'utiliser pour consolider les heures de travail des salariés d'une entreprise. On imaginerait ainsi, les heures comptabilisées et inscrites pour chaque semaine dans une feuille différente. Un tableau de synthèse permettrait alors de consolider les heures du mois, avec la même technique pour la fonction Somme. Il suffirait de modifier le format des heures en [h]:mm, pour pouvoir comptabiliser les heures supplémentaires de chacun à l'issue.

A partir du moment où la cohérence de structure est respectée entre les tableaux sources, n'importe qu'elle fonction statistique peut être utilisée pour réaliser la consolidation. Nous pourrions par exemple choisir de calculer la moyenne des ventes réalisées par vendeur, au cours de l'année. Pour cela, il suffit de remplacer le nom de la fonction Somme par Moyenne dans l'expression.

• Sélectionner de nouveau la plage de cellules C6:C11 de la feuille Synthèse des ventes,
• Enfoncer la touche F2 du clavier pour activer la modification de la formule de la première cellule de la plage, soit C6,
• Remplacer le terme SOMME par MOYENNE et valider par CTRL + Entrée,

Toutes les données consolidées se mettent à jour pour chaque vendeur de la colonne, mais en affichant cette fois, la moyenne des ventes réalisées par chacun, au cours de l'année.


Recouper les données et intégrer les nouvelles
Les données numériques à consolider ne présentent pas forcément la même structure. Dans l'exemple suivant, nous souhaitons synthétiser les ventes réalisées par plusieurs magasins d'une entreprise, dans la feuille Consolidation CA. Certains produits sont vendus par chacun des magasins, d'autres en revanche, sont spécifiques selon la région. La consolidation doit néanmoins les intégrer dans la feuille de synthèse. Pour ce faire, Excel propose une fonctionnalité de consolidation dédiée très puissante, que nous allons exploiter.


Les feuilles concernées sont les feuilles Magasin Lyon, Magasin Paris et Magasin Toulouse. Chacune réalise la synthèse des ventes, réalisées par produit, au cours du premier semestre. Certaines gammes comme Hygiène bébé sont référencées à Paris et Toulouse mais pas à Lyon. De même, les produits de diététique sont une exclusivité de la parapharmacie de Toulouse. Pourtant, la somme des ventes doit bien être réalisée et consolidée pour tous les produits communs dans la feuille Consolidation CA. Et en même temps, tous les produits, y compris ceux absents de certaines parapharmacies, doivent être ajoutés et consolidés dans cette même feuille.

• Activer la feuille Consolidation CA et cliquer sur la cellule B2 pour la sélectionner,

Contrairement à la méthode précédente, cette feuille est vide. C'est fort logiquement, la fonction de consolidation d'Excel qui va reconstruire le tableau final, en même temps qu'elle regroupe les données et les consolide. Cette méthode permet de consolider les sources en reconstruisant toutes les données synthétisées, y compris celles qui ne sont pas communes. Il s'agit donc de consolider les ventes réalisées par les trois magasins, saisies dans des feuilles différentes.

• Sélectionner la cellule B2 de la feuille Consolidation CA,
• Cliquer sur l'onglet Données, en haut de la fenêtre Excel, pour activer son ruban,
• Dans la section Outils de données, cliquer sur le bouton Consolider,

La boîte de dialogue Consolider apparaît. Dans la liste déroulante Fonction, sont proposées par défaut, les opérations de synthèses pour la consolidation, sur les données à regrouper. Nous conservons la somme pour synthétiser la somme des ventes par produit. La partie inférieure de la boîte de dialogue consiste à désigner et empiler les plages de cellules sources à consolider.

• Cliquer dans la zone de saisie Référence pour l'activer,
• Cliquer alors sur l'onglet Magasin Lyon pour activer sa feuille,
• Sélectionner intégralement le tableau, soit la plage de cellules B4:H9,
• Cliquer sur le bouton Ajouter de la boîte de dialogue,

La plage de cellules source du tableau de la première feuille est ainsi ajoutée dans la liste des références à consolider.

• Cliquer sur l'onglet de la feuille Magasin Paris,
• Sélectionner tout le tableau, soit la plage de cellules B4:H11,
• Cliquer sur le bouton Ajouter de la boîte de dialogue,
• Activer la feuille Magasin Toulouse,
• Sélectionner tout le tableau, soit la plage de cellules B4:H11,
• Puis, cliquer une dernière fois sur le bouton Ajouter,

Tous les tableaux à regrouper sont ainsi désignés. La consolidation a pour objectif de réaliser la somme des valeurs numériques pour les éléments communs.

• Dans la zone Etiquettes dans, cocher les cases Ligne du haut et Colonne de gauche,

Excel reconstituera ainsi les titres du tableau, en ligne et en colonne, sinon ils disparaissent.

• Cocher de même la case Lier aux données sources,
• Cliquer sur Ok pour valider la fusion et la consolidation des données,

Les données regroupées et consolidées sont livrées sur la feuille, dans une version brute sans mise en forme. Vous notez néanmoins que pour tous les produits communs, les chiffres des ventes ont bien été sommés et que tous les autres ont été intégrés. Il est désormais beaucoup plus simple, pour la direction, d'analyser les chiffres, dans leur globalité. Le tableau a bien été créé, à partir de la cellule B2 que nous avions présélectionnée, avant d'enclencher la consolidation.

Vous notez de même la présence de symboles +, dans la marge, à gauche des étiquettes de lignes de la feuille, ainsi que les boutons 1 et 2, en haut à gauche des étiquettes de colonnes. En même temps qu'Excel consolide les données sources pour livrer le tableau de synthèse des éléments recoupés, il construit un plan, qui permet d'avoir la trace sur les valeurs d'origine. Mais avant d'analyser ces données, nous allons les mettre en forme.

• Sélectionner n'importe quelle cellule du tableau, par exemple E13,
• Dans la section Style du ruban Accueil, cliquer sur le bouton Mettre sous forme de tableau,
• Dans la section Moyen de la liste des visuels proposés, appliquer un style de mise en forme automatique comme le style moyen 17 par exemple,
• Dans la boîte de dialogue qui suit, re-sélectionner si nécessaire la plage de cellules du tableau, soit B2:I29,
• Cocher la case Mon tableau comporte des entêtes et cliquer sur Ok,

Nous appliquons ainsi des préférences groupées de mise en forme, à l'ensemble du tableau, comme des styles et couleurs de police, ainsi que des bordures et couleurs de fond avec une nette démarcation pour les titres. Les hauteurs de lignes et largeurs de colonnes sont de même ajustées. Le support de formation sur les techniques avancées de mise en forme dans Excel apprend notamment ces méthodes.

Les données sont ainsi plus claires à lire et plus simples à interpréter. Vous notez la présence de boutons de filtre en entêtes de colonnes. Ils permettent d'isoler des données selon critères, comme nous l'avions vu dans le support de formation pour filtrer et extraire les informations de bases de données Excel. Ici, ils ne nous intéressent pas, donc nous allons les masquer.

• Dans le ruban Création du tableau, décocher la case Bouton de filtre,

Ils peuvent aussi être désactivés ou activés par le biais du bouton Trier et filtrer du ruban Accueil. Nous allons modifier les deux premiers titres de colonne du tableau,

• En B2, remplacer le texte Colonne 1 par Produits,
• En C2, remplacer le texte Colonne 2 par Sources,
• Puis, cliquer sur le petit bouton 2 du plan, en haut à gauche des étiquettes de colonnes,

Vous déployez ainsi l'affichage et accédez au détail des sources qui ont permis de construire le résultat consolidé des ventes, pour chaque produit. A ce titre, vous remarquez que les symboles + se transforment en symboles -, invitant à replier l'affichage. Les résultats des ventes pour les crèmes hydratantes ont ainsi été consolidés à partir de trois sources, les trois magasins. En revanche, plus bas dans le tableau, il apparaît clairement que les produits diététiques ont été intégrés dans la consolidation, bien qu'ils ne proviennent que d'un seul magasin.

• Cliquer sur le bouton 1 pour retrouver l'affichage de synthèse replié d'origine,
• Cliquer sur le symbole + à gauche de la ligne 13 pour les produits d'hygiène bébé,

Cette méthode permet d'accéder au détail d'une partie seulement des données consolidées. Vous notez ainsi que ces produits sont en vente seulement sur deux des trois magasins. Si vous souhaitez désactiver ces fonctionnalités de plan, depuis le ruban Données, vous devez cliquer sur le bouton Dissocier de la section plan, et choisir Effacer le plan, dans la liste.