Aujourd'hui, une technique ancienne appelée consolidation de données. Si vous devez combiner les données de plusieurs feuilles de calcul ou de plusieurs classeurs, Consolidation peut gérer le travail si vos données sont dans un format spécifique. En savoir plus sur cet ancien outil.
Il existe deux anciens outils de consolidation dans Excel.
Pour les comprendre, disons que vous disposez de trois ensembles de données. Chacun a des noms sur le côté gauche et des mois en haut. Notez que les noms sont différents et qu'il y a un nombre de mois différent dans chaque ensemble de données.
Vous souhaitez les combiner en un seul ensemble de données. Tournez la page pour une discussion sur les deux méthodes.
Le premier outil est la commande Consolider dans l'onglet Données. Choisissez une section vide du classeur avant de démarrer la commande. Utilisez le bouton RefEdit pour pointer vers chacun de vos ensembles de données, puis cliquez sur Ajouter. En bas à gauche, choisissez Ligne du haut et Colonne de gauche.
Lorsque vous cliquez sur OK, un sur-ensemble des trois ensembles de données est produit. La première colonne contient n'importe quel nom dans l'un des trois ensembles de données. La ligne 1 contient n'importe quel mois dans n'importe quel ensemble de données.
Dans la figure ci-dessus, remarquez trois désagréments. La cellule A1 est toujours vide. Les données de A ne sont pas triées. Si une personne était absente d'un ensemble de données, les cellules sont laissées vides au lieu d'être remplies avec 0.
Remplir la cellule A1 est assez simple. Le tri par nom implique l'utilisation du remplissage Flash pour obtenir le nom de famille dans la colonne N.Voici comment remplir les cellules vides avec 0:
- Sélectionnez toutes les cellules qui doivent avoir des nombres: B2: M11.
- Sélectionnez Accueil, Rechercher et sélectionner, Aller à spécial.
- Choisissez Blanks, puis cliquez sur OK. Vous serez laissé avec toutes les cellules vides sélectionnées.
-
Tapez 0 puis Ctrl + Entrée.
Aller au spécial
Le résultat: un rapport de synthèse joliment formaté.
Regarder la vidéo
- Consolider est une fonctionnalité ancienne d'Excel
- Spécifiez plusieurs plages à consolider
- Utiliser des libellés dans la rangée supérieure et la colonne de gauche
- Désagréments: A1 est toujours vide, la colonne A n'est pas triée, les données sont vides
- Aller à Special, Blanks, 0, Ctrl + Enter
- Consolidate peut pointer vers des classeurs externes
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2046 - Consolider les feuilles de calcul!
Je vais baladoer tous mes conseils de ce livre, cliquez sur le «i» en haut à droite pour accéder à la playlist!
D'accord, j'ai un vieux truc génial et génial. J'ai trois ensembles de données ici, Q1, Q2, Q3, ils ont tous une forme similaire, et qu'ils ont des noms en bas à gauche, des mois en haut, mais pas exactement la même forme. Q1 a janvier-février-mars, Q2 a 5 mois, je suppose que nous sommes devenus paresseux et n'avons pas compris de le faire à la fin du mois de juin et quelqu'un l'a finalement fait à la fin du mois d'août, puis le quatrième trimestre a 4 mois. Très bien, le 1er va A: D, le 2ème A: F, le 3ème A: E. Différents noms, certains des noms sont les mêmes, comme Michael Seeley est là dans chacun d'eux, mais d'autres noms vont et viennent. D'accord, c'est une fonctionnalité incroyable, elle existe depuis toujours. Je me souviens même de l'avoir fait en 1995, Data, Consolidates!
D'accord, nous allons utiliser la fonction SUM, je n'ai jamais utilisé aucune des autres, mais je suppose qu'ils sont là. Fonction SOMME, la première chose que nous allons faire est de revenir à Q1 et de pointer sur cette plage, ces quatre colonnes, cliquez sur Ajouter, puis allez à Q2, sélectionnez ces colonnes, cliquez sur Ajouter, puis sur Q4, sélectionnez ces colonnes . Très bien, cochez cette case pour Utiliser les étiquettes dans la ligne supérieure et la colonne de gauche, ce bouton Parcourir signifie que ces ensembles de données peuvent être dans différents classeurs! Créez des liens vers des données sources, nous en parlerons à la fin. Lorsque je clique sur OK, ils obtiennent tous les noms figurant dans l'une des 3 listes, les mois dans l'une des 3 listes, et nous avons maintenant ce super ensemble incroyable, d'accord, des ennuis!
C'est une fonctionnalité intéressante, mais voici les choses qui viennent de me pirater. Ils ne me donnent pas l'étiquette en A1, ils ne prennent pas la peine de trier les données, et si quelqu'un n'avait pas d'enregistrement dans le premier, ils me donnent des blancs au lieu de zéros. Très bien, pour remplir les blancs avec des zéros, Origine, Rechercher et sélectionner, Aller à spécial, choisissez les blancs, cliquez sur OK, tapez un zéro, Ctrl + Entrée les remplira. Assez facile pour trier les données, les données, AZ et il triera les données, d'accord. Créez des liens, bon sang, ça ne fonctionne jamais bien, bien, créez des liens, pour que Créer des liens fonctionne, cela doit être dans un classeur externe. D'accord, je vais donc cliquer sur Parcourir ici, j'ai créé un cahier de travail appelé OtherWorkbook, et les données sont en A1: D7, cliquez sur Ajouter, d'accord, et il y a le premier. Les données suivantes sont dans G1: L8, donc je vais parcourir OtherWorkbook, G1: L8,cliquez sur Ajouter, d'accord. Alors maintenant, j'ai deux références à d'autres classeurs, ligne du haut, colonne de gauche, créer des liens vers les données source. L'aide d'Excel indique qu'une fois que vous utilisez Créer des liens avec les données source, vous ne pourrez jamais modifier ces plages, cliquez à nouveau sur OK, et voici ce que nous obtenons.
D'accord, première chose, il semble que cela nous a donné les résultats, cela nous a donné les résultats, mais il y a une colonne supplémentaire B ici, et nous avons le groupe et le plan. Et quand nous allons à la vue numéro 2, ah. Donc, idéalement, vous auriez un classeur appelé janvier et un autre classeur appelé février, et il vous montrera ici janvier, voici février. Voici le total pour Mike Seeley, ce sont des formules indiquant ces ventes, puis voici une somme de ces deux, d'accord, c'est bizarre.
Si vous l'utilisez tout le temps, je veux avoir de vos nouvelles dans les commentaires YouTube, je suis sûr qu'il existe un moyen plus simple de le faire pour moi, je ne l'ai jamais fait de ma vie, bien une fois avant aujourd'hui, puis aujourd'hui, juste pour que je puisse l'expliquer, d'accord. Consolidez, cependant, lorsque nous consolidons des feuilles du classeur actuel, une astuce géniale et géniale. Demain, nous allons comparer la plage de consolidation multiple, les tableaux croisés dynamiques, mais toutes ces astuces sont dans le livre, cliquez sur le «i» dans le coin supérieur droit pour accéder à ce livre.
Consolider, ancienne, ancienne fonctionnalité dans Excel, vous spécifiez plusieurs plages à consolider, je coche toujours la case pour la ligne du haut et la colonne de gauche. Les résultats sont excellents, mais A1 est vide, la colonne A n'est pas triée et il y a des blancs dans les données, utilisez Atteindre les blancs spéciaux, tapez un 0, Ctrl + Entrée pour remplir ces blancs. Et puis ce dernier exemple là-bas, consolider peut pointer vers des classeurs externes, pourrait être utile!
Très bien, je tiens à vous remercier d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2046.xlsm
