Accueil > Tableur > supports > Consolider les données (Excel)

Consolider les données (Excel)

mercredi 13 mai 2015

Cet exercice est, comme d’habitude, accompagné de divers éléments que vous trouverez en bas de cette page en fichiers joints :

 une archive ZIP, avec les documents de travail

 les modèles finalisés aux formats Excel et Calc.

La présente page a été réalisée à partir d’une mise en situation sur Excel.

Vous trouverez, sur cet autre tuto, l’équivalent sur Calc.

Quelques différences de traitement significatives entre les deux tableurs nécessitent parfois une explication distincte, même si la logique de traitement générale de l’exercice est applicable quelle que soit la boutique fréquentée (Microsoft ou OpenOffice).

Voilà pourquoi, après une explication commune aux deux tableurs, vous trouverez, un peu plus bas, sur cette page, un renvoi par lien hypertexte permettant aux utilisateurs de Calc de garder le fil de l’explication.

Téléchargez l’archive zip, puis décompressez les fichiers en les installant à un niveau bien défini de votre système (j’ai pris, ici, comme exemple le dossier : « Mes documents »). Reportez-vous, si nécessaire, au support 7ZIP concernant la décompression d’une archive.

Ouvrez le dossier. Vous devriez trouver ces trois fichiers (ci-dessous).

Un exemple de consolidation : rassembler les dépenses par poste

Nous allons simuler la situation suivante : trois intervenants d’une société de service, Annie, Bertrand et Émilie, notent au fur et à mesure leurs dépenses sur une feuille de calcul.

Pour cela, chacun dispose en début de mois, d’un document identique aux deux autres, sur lequel figure une série de postes budgétaires prédéfinis. Le but du jeu consiste, à la fin du mois, à rassembler toutes les dépenses sur une quatrième feuille de calcul, poste par poste.

Saisir les dépenses

Double-cliquez sur ce fichier, nommé « EMILIE ».

Ce fichier, contrairement aux trois autres, ne comporte aucune dépense. Vous avez juste les postes budgétaires à vide. La feuille de calcul, en l’état, correspond au document ouvert au début de chaque mois.

Afin que vous puissiez bien comprendre la logique de la procédure, vous allez vous mettre dans la peau de cette personne en inscrivant les dépenses.

Voici votre modèle :

Le plus simple consiste à remplir ligne par ligne selon le modèle. Va pour les deux premières.

Remplissage automatique

La troisième dépense inscrite sur le modèle (en ligne 4) correspond à un poste qui vient en répétition : « Fourniture de bureau ». Eh oui, c’est tout l’intérêt de la démarche : pouvoir inscrire les dépenses comme elles viennent, en s’imposant juste la rigueur de les faire rentrer dans le bon poste budgétaire.

N’essayez pas d’insérer cette dépense après la ligne 3. Cliquez directement en bas de la liste, en A15 et commencez à taper les premières lettres du poste. Grâce au remplissage automatique, fonction du tableur permettant d’identifier que la même information figure dans la colonne, vous évitez de taper entièrement l’intitulé de votre poste : le texte s’affiche et il suffit d’appuyer sur la touche « Entrée » du clavier pour qu’il s’inscrive définitivement dans la cellule.

La même situation intervient après avoir repris la saisie suivant le modèle à cet endroit (après le premier poste « Achats petits équipements »).

Là encore, il faut inscrire une nouvelle dépense de type « Fourniture de bureau ».

Placez vous en bas de votre liste et tapez « Fou.. ». Le tableur identifie à nouveau ce poste et le propose donc en remplissage automatique, car il figure déjà dans la même colonne.

Validez par « Entrée » et tapez le reste des données (colonnes B, C, D et E) ; pour cela, je ne vous en voudrais pas si vous brodez selon votre humeur ou votre inspiration pour saisir vos propres descriptions et références facture.

Par contre, pour que l’exercice fonctionne correctement il est indispensable que vous respectiez la manipulation du remplissage automatique au moment de la saisie du poste (colonne A).

Cela permet de s’assurer que la dénomination des postes budgétaires correspond bien à un standard, que l’on retrouvera, non seulement sur ce même document, mais également sur ceux des collègues de notre cobaye « Émilie ».

Pour d’autres raisons, il est préférable de respecter également les dates du modèle, même si cela n’intervient pas directement sur la procédure de consolidation (voir plus loin).

Vous devriez normalement arriver aux étapes suivantes (selon le modèle) :

 saisie du poste « Services bancaires »

 puis, les deux dernières lignes sont, à nouveau, des répétitions : « Documentation technique ». Remarquez qu’il suffit de taper le « D » pour que le poste apparaisse en remplissage automatique. C’est normal, puisque c’est le seul intitulé qui commence par cette lettre.

 et enfin un deuxième « Sous traitance ». Là, par contre, il a fallu taper quelques lettres en plus pour que le tableur le distingue de « Services bancaires ».

Trier une liste

Après cela, sélectionnez votre liste complète …

… et passez par le menu « Données / Trier… »

Cela fait apparaître une boîte de dialogue : cliquez sur la liste déroulante « Trier par ».

Nous souhaitons que nos dépenses soient affichées par ordre chronologique.

Par conséquent, il faut sélectionner le critère de tri « Dates », lequel correspond justement à l’une des en-têtes de colonnes de notre tableau.

Cliquez ensuite sur le bouton OK.

Le tableau est désormais classé par dates, ordre croissant, tel que sur le modèle :

Vous pouvez enregistrer le document et le fermer.

Organiser un groupe de fichiers

Créez un nouveau document vierge sur votre tableur, puis passez par le menu « Fichier / Ouvrir »

Placez-vous au niveau le l’arborescence sur lequel vous avez enregistré le dossier « Consolider_les_donnees » (à partir de l’archive ZIP récupérée depuis cette page).

Double-cliquez sur le dossier « Consolider_les_donnees » afin de l’ouvrir…

Cliquez sur le premier fichier de la liste…

… maintenez la touche MAJ du clavier, puis cliquez sur le dernier de la liste (ce qui permet de conserver le premier tout en sélectionnant le(s) fichier(s) intermédiaire(s)).

Cliquez enfin sur le bouton « Ouvrir » de l’explorateur de fichiers.

Vous devriez avoir quelque chose qui ressemble à ça :

Peu importe qu’il s’agisse de Bertrand, Émilie ou Annie, car, en plus du fichier qui s’affiche dans la fenêtre, vous avez ouvert en même temps les deux autres.

Retournez sur le tuto Calc, pour la suite de l’exo, si vous êtes sur OpenOffice

Si vous passez par le menu « Fenêtre », vous constaterez que ces trois documents, sont actuellement édités :

Toujours depuis le menu « Fenêtre », cliquez sur « Réorganiser… »

Cliquez, au besoin, sur l’option « Mosaïque » (il faut le bouton rond soit coché) puis cliquez sur « OK ».

Cela permet d’afficher les trois feuilles de calcul dans la même fenêtre :

Depuis cette fenêtre, cliquez sur l’icône « Nouveau document » :

Une quatrième feuille de calcul vierge s’affiche en superposition de la fenêtre active :

Le but du jeu consiste maintenant à insérer cette quatrième feuille de calcul au côté des trois autres, dans la fenêtre d’arrière plan.

Pour cela passez à nouveau par le menu « Fenêtre / Réorganiser… ».

Même option que tout à l’heure (« Mosaïque »)…

Et voilà :

Il faut renommer la première feuille de calcul du document vierge. Pour cela double-cliquez sur l’onglet « Feuille 1 »…

… et tapez « récapitulatif_Janvier 2009 ». Validez le nom de cette feuille en appuyant sur la touche « Entrée » du clavier.

Pointez l’onglet de la feuille « ANNIE_janvier_2009 », puis, maintenez la souris baissée (enfin, vous m’aurez compris, le bouton de la souris baissée), et bougez légèrement la main. Vous devriez voir une icône en forme de feuille accompagné d’une petite flèche…

… cela indique que vous pouvez déplacer votre feuille de calcul – non seulement à l’intérieur du même classeur – mais également d’un classeur à un autre :

Tout en conservant la souris baissée, déplacez votre main en direction de la feuille « récapitulatif_Janvier 2009 »…

Lorsque vous voyez exactement ce qui affiché ci-dessous – la flèche placée devant récapitulatif_Janvier 2009 – vous pouvez lâcher le bouton de la souris.

Ce glisser-déposer d’un classeur à un autre a permis de copier la feuille de calcul.

Du coup, le classeur source (celui qui comporte « ANNIE_Janvier_2009 ») disparaît de la fenêtre :

Recommencez la même opération avec la feuille « BERTRAND_ Janvier_2009 » :

Puis la dernière : “ANNIE_ Janvier_2009 »

Il ne reste plus, à l’écran, que le classeur cible, avec nos quatre feuilles de calcul. Cliquez sur le bouton pour agrandir la fenêtre :

Faîtes défiler le scroller horizontal …

… afin d’atteindre la feuille « récapitulatif_Janvier_2009 ». Cliquez sur cet onglet.

Consolider les données

Le but du jeu, je le rappelle, consiste à rassembler dans cette feuille de calcul les dépenses, poste par poste, de nos trois intervenants.

Pour cela, il faut passer par le menu « Données / Consolider… »

Cet assistant (ci-dessous) devrait normalement apparaître à l’écran.

Important : vérifiez, en premier lieu, que la fonction souhaitée est affichée dans le sélecteur « Fonction ».

En ce qui nous concerne, comme nous voulons additionner des dépenses, il n’y a pas lieu de modifier quoi que ce soit, car c’est la fonction « Somme » qui est sélectionnée par défaut sur cet assistant.

Cliquez sur la case « Colonne de gauche ». cela nous permettra d’obtenir les intitulés de tous les postes de dépense :

Cliquez, ensuite sur ce bouton :

Comme nous l’avons déjà vu, de façon générale sur les tableurs, le clic sur ce bouton permet d’escamoter l’assistant afin de reprendre la main sur la feuille de calcul elle-même.

Cliquez sur le bouton du scroller horizontal afin d’afficher les premières feuilles de calcul.

Activez l’onglet d’Émilie.

Puis sélectionnez la plage de cellules suivantes (en fait le tableau dans son intégralité) :

Cliquez ensuite sur le bouton de l’assistant…

… afin de le développer.

Vous constatez, alors, que la référence complète plage de cellules sélectionnée (comportant le titre de la feuille) est affichée dans le champ « Référence ».

Il faut cliquer sur le bouton « Ajouter », pour que cette référence soit intégrée dans l’assistant ; ce qui provoque son affichage, en dessous, dans le champ « Référence source » :

Vous devez, à présent, intégrer dans la procédure de consolidation les feuilles de calcul des autres intervenants.

En fait, il n’est pas absolument nécessaire de rétracter l’assistant, si celui-ci ne vous empêche d’effectuer vos manipulations, vous pouvez même cliquer directement sur l’onglet de la feuille suivante : va pour Bertrand.

Dès l’affichage de la feuille « Bertrand », on constate que la même plage de cellule (que sur la feuille précédente) est sélectionnée ; ce qui provoque l’insertion de la nouvelle référence dans le premier champ.

Remarquez que rien ne vous empêcherait de redéfinir la plage de cellules, pour cette feuille « Bertrand »

Mais ce n’est pas nécessaire, puisque Bertrand et Émilie ont effectué le même nombre de dépenses. Il suffit donc de cliquer sur le bouton « Ajouter »…

Ce qui permet de mémoriser cette deuxième « référence source » :

Il reste Annie.

Même topo :

 clic sur l’onglet

Vérification que la plage sélectionnée correspond bien à ce qu’il faut mémoriser…

… et « Ajouter »

Voilà, le plus important est fait.

Après avoir vérifié que toutes les références figurent bien dans le champ « Références source », vous pouvez cliquer sur le bouton « OK » :

Cela ne paye pas de mine, mais l’essentiel y est : le total des dépenses par poste, pour nos trois intervenants.

Même si un poste est répété plusieurs fois sur une ou plusieurs feuilles de calcul, la consolidation permet de rassembler tout cela à la finale de façon synthétique.

Vous comprendrez donc qu’il est extrêmement important, pour cela, que les intitulés soient rigoureusement formulés de façon identique (d’où l’intérêt du remplissage automatique).

Bien.

Reste à finaliser tout ceci.

Cliquez sur le menu « Données / Consolider… »

Car il faut ajouter l’intitulé « Montants ».

Pour cela, cochez la case « Ligne du haut » puis « OK ».

Le tableau est, cette fois-ci, complet.

Il y a même des données qu’il va falloir supprimer : par exemple, les dates qui ont été additionnées et sont, de ce fait, totalement erronées.

Remarquez que, lors de la phase de mémorisation des plages de cellules, sur l’assistant, il aurait été possible de ne sélectionner que les deux premières colonnes. J’ai préféré laisser cela en état afin de simplifier la procédure.

Rien n’empêche, d’ailleurs, de supprimer après coup ce dont nous n’avons pas besoin.

Le reste n’est que cosmétique :

 modification de la largeur des colonnes..

 sélection de l’en-tête de colonne sur l’une des feuilles « source » :

 activation de l’outil pinceau afin de récupérer la mise en forme

 application du pinceau sur la plage de cellules « cible » à mettre en forme

OK.

Enregistrez !!!

 

Un message, un commentaire ?

modération a priori

Attention, votre message n’apparaîtra qu’après avoir été relu et approuvé.

Qui êtes-vous ?
Ajoutez votre commentaire ici
  • Ce champ accepte les raccourcis SPIP {{gras}} {italique} -*liste [texte->url] <quote> <code> et le code HTML <q> <del> <ins>. Pour créer des paragraphes, laissez simplement des lignes vides.