Le tableur Excel de Microsoft offre différentes fonctionnalités pour fusionner le contenu de tableaux à partir de différentes feuilles de calcul. Dans ce guide, vous apprendrez pas à pas comment fusionner des tableaux Excel avec des entrées identiques ou différentes. Excel offre deux fonctionnalités essentielles permettant de fusionner des fichiers Excel : d’une part, la fonction « Consolider », d’autre part l’éditeur Power Query.
L’avantage de fusionner des données provenant de feuilles de calcul est que cela vous permet de créer des tableaux Excel pour évaluer des données de l’entreprise ou de clients.
Si vous souhaitez fusionner deux tableaux dans Excel, vous pouvez utiliser la fonction Excel « Consolider ». Pour ce faire, votre fichier Excel doit comporter au minimum deux feuilles de calcul.
La fonction Consolider suffit pour les fusions simples avec le même formatage et le même contenu.
Dans cette formation nous abordons un type de graphique particulier, les graphiques combinés d'Excel. Ces graphiques permettent de représenter sur une même vue, des données dont la nature et/ou l'échelle sont différentes. Ce type de graphique propose deux axes des ordonnées avec des échelles adaptées. Si bien que les valeurs aux échelles pourtant très différentes, apparaissent dans les mêmes proportions sur la même représentation graphique.
Lire aussi: Fabriquer un pistolet LEGO qui tire
Il s'agit du classeur sur lequel nous avions commencé à travailler pour bâtir le deuxième volet de la formation sur les graphiques Excel. Le tableau de cette feuille présente les objectifs de chiffres d'affaire à réaliser au cours des différents mois avec en dessous, les chiffres d'affaire effectivement réalisés. Un calcul très simple, sur la ligne Ratio (/10), permet de constater rapidement si le CA réalisé a dépassé l'objectif posé (>10) ou non. Vous remarquez d'ailleurs la présence d'une mise en forme conditionnelle sur cette ligne, c'est-à-dire d'un format qui fait ressortir dynamiquement ces valeurs. La dernière ligne de ce tableau (Ratio (Obj.)) est un rappel du seuil à atteindre pour pouvoir représenter graphiquement cette ligne à franchir.
L'objectif à l'issue, est de retourner une représentation graphique, incluant toutes ces valeurs pourtant très différentes. Grâce à la présence des deux axes des ordonnées (Axes verticaux), aux échelles adaptées, ce graphique est capable de regrouper et combiner ces données, très différentes sur une même vue.
Si nous utilisons un graphique classique pour construire une représentation de l'ensemble de ces données, les valeurs des ratios ne seront pas visibles tellement elles sont faibles en comparaison des valeurs des chiffres d'affaires. Comme le rappelle le support de formation sur les raccourcis Excel, la touche CTRL permet de réaliser des sélections de plages de cellules qui ne se touchent pas. Ainsi nous allons pouvoir bâtir un graphique sur une multi-sélection.
Le graphique est ainsi déposé sur la feuille. Pour l'illustration, nous avons volontairement modifié la couleur de la légende de la série Ratio (Obj.). En bleu, nous remarquons que les variations et évolutions des chiffres d'affaire sont correctement représentées, grâce à une échelle de l'axe des ordonnées qui leur est adaptée. En revanche, les deux séries des ratios, ne décollent pas et restent plaquées sur l'axe des abscisses à la valeur 0. En effet, l'ordre de grandeur de ces ratios est la dizaine tandis que celui des CA est le millier.
Pour éviter d'appliquer un facteur de multiplication à ces valeurs et les ramener dans le même ordre de grandeur, Excel propose fort heureusement, les graphiques combinés. L'aperçu proposé n'est pas encore correct. Nous devons apporter des précisions pour ce graphique avant de le valider. Notamment il s'agit d'indiquer à Excel que certaines données doivent être représentées sur un axe des ordonnées secondaire, à l'échelle adaptée. Comme l'illustre la capture ci-dessus, les données des ratios apparaissent instantanément sur l'aperçu du graphique, dans les mêmes proportions que les CA, malgré leur différence d'ordre de grandeur. En effet, vous notez l'apparition sur la droite, d'un axe secondaire des ordonnées.
Lire aussi: Le fusil de chasse : comment ça marche ?
La grande force de ces graphiques combinés est de pouvoir recouper les observations grâce à des données regroupées sur une même vue. Si le graphique ne représentait que les chiffres d'affaire sous forme d'histogrammes (en bleu), nous risquerions de tirer des conclusions erronées. Ainsi, nous pourrions être satisfait de constater la nette reprise du chiffre d'affaire au mois d'Avril, deux fois supérieur à celui du mois de Mars. Pourtant le graphique combiné livre une toute autre interprétation, nous permettant de ne pas écarter le contexte ou la conjoncture de l'analyse. En effet, alors que le ratio en Orange est au-dessus de l'objectif à réaliser pour le mois de Mars, il plonge nettement en dessous de la barre grise pour le mois d'Avril. Cela signifie que le chiffre d'affaire attendu pour ce mois, aurait dû être encore supérieur.
Maintenant que le plus dur est fait et que le rendu livre une lecture efficace, il s'agit de personnaliser le graphique pour atteindre le résultat demandé par le modèle. Notamment, nous souhaitons donner une apparence 3D aux barres des histogrammes et courbes des ratios. Nous donnons ainsi l'illusion d'une légère ombre en arrière-plan des barres de l'histogramme. Au final, nous obtenons un graphique combiné semblable au modèle comme l'illustre la capture ci-dessous.
Nous avions tout d'abord bâti un graphique secteurs pour représenter les parts de charges consommées par différentes agences d'une entreprise. A la lecture de ce graphique, nous avions tiré une conclusion hâtive. Nous pensions qu'il fallait intervenir auprès de l'agence de Toulon pour endiguer ses fortes dépenses. La part de pourcentage en bleu, pour l'agence de Toulon, est en effet nettement supérieure aux autres. Mais en bâtissant un second graphique en courbes pour représenter l'évolution de ces charges au fil des années, nous avions révisé notre jugement. La courbe bleue, sur ce second graphique, montre en effet, que l'agence de Toulon semble réduire ses dépenses au fil des années pour au final parvenir à maîtriser ses charges. Nous comprenions alors que la forte part de pourcentage du premier graphique était essentiellement due aux fortes dépenses des premières années. Par contre le recoupement avec le second graphique avait permis de faire ressortir un autre problème.
Tout cela pour dire que sans une analyse croisée à l'aide de deux graphiques recoupés, nous aurions pu tirer de fausses interprétations et bâtir une mauvaise stratégie d'entreprise. C'est tout l'intérêt des graphiques combinés et c'est ce que nous allons encore une fois démontrer dans cet exercice.
Nous allons commencer par bâtir un graphique classique, en forme de cônes 3D, pour représenter seulement l'évolution des ventes. Le graphique se pose sur la feuille. La première conclusion que nous pouvons faire à la lecture de ce dernier est que les ventes en couleurs sont nettement supérieures à celles en noir et blanc. De plus, cette tendance semble s'accentuer au fil des années.
Lire aussi: Instructions : Pistolet laser à faire soi-même
Nous devons maintenant positionner la légende à un endroit personnalisé sur le graphique. Mais avant cela, elle doit être disposée sur deux lignes et non sur une seule. La légende est désormais bien écrite sur deux lignes. Sélectionner maintenant les barres bleues des ventes N.B. En faisant correspondre les limites de l'échelle aux valeurs du tableau, nous optimisons la taille du graphique dans sa zone de traçage. En réglant la valeur de l'unité principale, nous modifions le pas de l'axe, pour fournir moins de détails et obtenir une vue plus sobre. Nous allons donc invoquer un axe secondaire des ordonnées qui adaptera son échelle sur ces ordres de grandeurs. De cette façon les données seront représentées dans les mêmes proportions, malgré leurs différences.
Pour ces derniers, nous devons leur associer un axe secondaire des ordonnées adapté à leur échelle de valeurs. En bas de la boîte de dialogue, choisir la représentation Histogramme groupé, à l'aide des listes déroulantes pour les Ventes N.B. Le graphique ainsi conçu est déjà très proche du modèle. Il offre déjà une bonne lecture grâce à ses données regroupées.
Nous obtenons donc un second graphique similaire au modèle, riche en enseignements. En effet l'analyse croisée des données regroupées sur cette même vue permet deux remarques. Tout d'abord elle confirme que les ventes en couleurs sont meilleures que les ventes en noir et blanc. Donc les stratégies de communications en couleurs sont d'autant plus productives.
Il s'agit d'une donnée que nous ne pouvions pas prendre en compte avec le premier graphique.
Il est fréquent de se retrouver avec le besoin de fusionner plusieurs fichiers Excel en un seul. Par exemple, vous avez préparé un fichier modèle contenant un tableau. Puis, ce fichier est transmis à plusieurs personnes afin de collecter leurs données. Malheureusement, vous songez sérieusement à fusionner les fichiers Excel manuellement à l’aide de nombreux copier-coller.
Vous avez peut-être un autre objectif que fusionner plusieurs classeurs Excel en un ? En effet, fusionner plusieurs fichiers Excel peut viser trois objectifs très différents. Les mots ont toute leur importance.
Est-ce une action ponctuelle ou à faire régulièrement (et à quelle fréquence) ? Vais-je devoir intégrer des mises à jour de fichiers Excel déjà fusionnés ? Voici un exemple avec deux fichiers. Avant de commencer une quelconque opération, vous devez déterminer où vous souhaitez retrouver le résultat de la fusion de vos fichiers Excel. Ensuite, vous devez en premier lieu ouvrir votre fichier cible puis les fichiers à fusionner les uns après les autres. Si vous vous rendez compte que vous avez sélectionné une feuille de trop, pressez à nouveau sur la touche Ctrl puis cliquez sur la feuille que vous souhaitez retirer.
La fenêtre suivante s’affiche alors et vous permet de déterminer le classeur cible. En cliquant sur le menu déroulant, vous verrez apparaître la liste des classeurs ouverts. Vous aurez également une option supplémentaire « (nouveau classeur) ». Dans mon exemple, j’ai choisi de fusionner mes données dans le classeur Excel « 1er Trimestre.xlsx ». Comme la capture d’écran le présente, vous pouvez à cet instant viser un nouveau classeur. Déterminez l’emplacement de vos feuilles dans le nouveau classeur.
Je vous l’ai déjà écrit, je ne suis pas pour l’utilisation des macros à tout va dans Excel ou n’importe quel outil de la suite Microsoft 365 (nouveau nom de la suite Office depuis avril 2020). Vous avez plusieurs manières de résoudre ce cas de figure. La première sera sans code. Particularité : vous devrez utiliser un nouveau classeur comme point de départ. Si vous aimez Excel, vous allez adorer PowerQuery (avant de devenir passionné de PowerBI…). Cet outil est venu enrichir les fonctionnalités d’Excel depuis la version 2010. Depuis Excel 2016, PowerQuery fait partie intégrante d’Excel. Cette plateforme offre la possibilité d’automatiser de nombreuses manipulations sur les données. Les informaticiens appellent cela un ETL : Extract Transform Load. Je le rappelle à qui veut l’entendre, le code (= les macros ou le VBA pour les intimes) peut aussi être utilisé pour écrire des virus informatiques. PowerQuery apporte une très belle réponse à de nombreuses opérations qu’il fallait programmer auparavant.
Nous partons donc d’un classeur tout neuf, sans les données. C’est à partir de celui-ci que vous vous rendrez dans l’onglet « Données » puis « Obtenir des données ». Veillez à bien cocher la case « Sélectionner plusieurs éléments » afin que les cases à cocher apparaissent en 2. ATTENTION : En 3, je vous invite à bien sélectionner « Charger dans… » et non « Charger » car nous n’avons pas besoin de ces données maintenant. A ce stade, vous avez créé une connexion vers un classeur Excel. Ensuite, vous avez créé des tables basées sur les différentes feuilles qui le compose. Maintenant, il ne reste que le dernier mouvement : Fusionner vos feuilles. L’option « Fusionner » permet de connecter deux tables à l’aide d’une référence commune. En premier lieu, sélectionnez « Au moins trois tables » puis sélectionnez les tables disponibles. Vous pourrez changer le nom par défaut « Ajouter1 » en un nom qui correspond mieux à la situation.
A ce stade, vous disposez d’un nouveau classeur. Celui-ci est connecté à votre fichier Excel où se trouvent les feuilles à fusionner. Si votre fichier source est mis à jour, vous pourrez venir sur celui-ci et simplement « Actualiser les données » pour que la fusion opère à nouveau ! Alors, pas n’importe quel type de mise à jour.. Si les données des feuilles existantes sont modifiées, pas de problèmes (ajout, suppression ou modification). Si de nouvelles feuilles sont ajoutées, vous pourrez adapter les requêtes et compléter la fusion des feuilles. Je ne le développerai pas ici, car ce serait rentrer dans trop de détails sur PowerQuery.
Lorsque vous aurez goûté à la puissance de PowerQuery, vous aurez envie d’aller plus loin dans les transformations et l’analyse des données brutes. Comme votre fichier contenant les données fusionnées est lié à un autre fichier, celui contenant les données de départ ne doit pas être déplacé. Si cela arrivait, pas de panique, il y a quelques paramètres à modifier et la connexion peut être retrouvée. Vous disposez d’un classeur connecté. Le deuxième corolaire de la situation est que vous ne pouvez pas modifier la structure de ce tableau (ajouter des colonnes, en retirer).
La situation est la suivante : vous devez collecter des données provenant de plusieurs personnes. Le nom des fichiers que vous aurez collectés va être essentiel pour la fusion à venir. Excel (via PowerQuery) est capable d’aller récupérer l’ensemble des fichiers d’un dossier en particulier et les traiter. À ce moment (où à un autre), vous vous rendrez compte que d’autres fichiers sont présents dans votre dossier sélectionné (en 1). Vous vous trouverez dans une vue similaire à celle-ci. Un peu comme au sein d’Excel, vous aurez la possibilité de filtrer cette liste. Dans mon cas, je ne souhaite fusionner que les fichiers commençant par « Financial Sample_ ».
PowerQuery vous montre désormais la bonne liste de fichiers à importer. PowerQuery va alors vous présenter le premier des fichiers qu’il prendra comme exemple. Vous pouvez éventuellement sélectionner un autre fichier pour le prendre en exemple.Vous déterminez l’objet à importer. Ici, PowerQuery a détecté la présence d’un tableau. Mais si vous le souhaitez, vous pouvez importer la totalité de la feuille.Ici, cette option va éviter le blocage de PowerQuery en cas d’erreur. D’un côté, c’est une option utile car elle évite les erreurs. Sur la droite (1), vous trouverez la liste des instructions qui seront suivies à chaque rafraîchissement des données.
Vous venez de mettre en place une chaîne automatique d’import des données depuis un dossier. Toutes les données seront actualisées. Si un des fichiers a été mis à jour, ce sera pris en compte.Tous les nouveaux fichiers seront inclus. Comme dans le cas précédent, si votre dossier est déplacé ou que celui qui ouvre le fichier Excel n’a pas les droits d’accès, vous ne pourrez pas actualiser les données.
tags: #comment #juxtaposer #deux #tableaux #excel
Chargement des visites
