Comment consolider les données de plusieurs feuilles à l’aide de références 3D dans Excel
Excel 3D Reference est une formule courte et flexible qui vous permet d'effectuer des calculs en référençant la même cellule ou une plage de cellules sur plusieurs feuilles. L'utilisation de formules 3D élimine le besoin de référencer chaque cellule, ce qui facilite les calculs. Lorsque vous appliquez une fois la formule 3D, elle inclut les données des feuilles que vous ajoutez ultérieurement.
Cet article explore comment la consolidation des données dans une seule feuille peut aider à réduire le temps nécessaire pour calculer les données sur plusieurs feuilles. La gestion de grandes quantités d'informations dans une seule feuille de calcul peut être simple et efficace, conduisant à des résultats précis avec des exigences minimales.
Différentes approches pour additionner les données dans Excel
Dans Excel, la méthode traditionnelle pour additionner les informations sur plusieurs feuilles consiste à additionner manuellement chaque cellule à l'aide de la fonction Somme. En revanche, les ajouter manuellement peut devenir de plus en plus problématique si vous disposez d'un vaste ensemble de données de ventes mensuelles pendant une période prolongée.
Ce n'est pas non plus une méthode dynamique, vous devrez donc à nouveau modifier les formules si vous ajoutez une feuille supplémentaire. Voyons rapidement comment la consolidation des données, sur plusieurs feuilles, en une seule feuille à l'aide d'Excel 3D Reference permet de résumer rapidement et précisément les données.
Syntaxe de la référence Excel 3D
Jetez un œil à la syntaxe d'une formule de référencement 3D qui additionne les valeurs des cellules B4 pour quatre feuilles, nommées janvier, février, mars et avril.
=SUM(January:April!B4)Comme indiqué ci-dessus, il existe deux éléments principaux dans la formule Excel 3D. Le premier fait référence aux noms des première et dernière feuilles séparés par un point-virgule. Dans la deuxième partie, la même cellule ou plage de cellules est référencée sur les quatre feuilles.
Dans ce cas, SUM est utilisé. Vous pouvez également utiliser toute autre formule lors du calcul. Cependant, la syntaxe de la référence 3D reste la même. Pour utiliser la référence 3D d'Excel, les données doivent être cohérentes sur les quatre feuilles.
Exemple de référence Excel 3D
Comprenons maintenant la raison d'être de la fonction Excel 3D. Pour évaluer cela, mettons-le en pratique à l'aide d'un ensemble de données.
Jetez un œil aux exemples de données qui contiennent les ventes pour les quatre mois, le numéro de stock, le stock vendu, le stock acheté et le stock de clôture à la fin de chaque mois.
Données de janvier:
Données de février:
Données de mars:
Données d'avril:
Dans la première étape, évaluez les données disponibles sur quatre feuilles pour voir si elles sont séquentielles. Ce n'est qu'alors que les références 3D peuvent être implémentées.
Implémentation de la référence 3D
Pour calculer le total des ventes pendant quatre mois, sélectionnez la cellule dans laquelle le chiffre apparaîtra.
Pour cet exemple, la valeur des ventes réside dans la cellule B3 pour les quatre feuilles; les données de référence sur quatre feuilles seront dans la même cellule. La plage apparaîtra de janvier à avril, car les deux sont des noms définis pour les feuilles de début et de fin, respectivement.
1. Ajoutez la cellule B3 comme cellule de référence .
2. De janvier à avril, choisissez la gamme à utiliser.
3. Appuyez sur Entrée pour exécuter la formule.
Pour vous assurer que la formule a été correctement appliquée, vous pouvez additionner manuellement les valeurs d'un élément spécifique de l'ensemble de données. Une fois que vous savez que c'est correct, vous pouvez vous attendre à ce que le reste des calculs soit correct tant que les valeurs sont cohérentes.
La même approche pour calculer la valeur totale des ventes pendant quatre mois s'applique au stock, au stock acheté, vendu et au stock de clôture à la fin de chaque mois.
La plage reste la même pendant quatre mois, tandis que la cellule référencée changera en fonction de leur emplacement dans différentes feuilles. Résumons les données Stock pendant quatre mois.
1. Ajoutez la cellule B4 comme cellule de référence .
2. Choisissez la gamme à partir de janvier: avril .
Faites-le glisser pour implémenter la formule de somme 3D pour les autres cellules de la ligne à l'aide de la séquence de remplissage automatique .
De la même manière, vous pouvez appliquer des références 3D à une plage de cellules sur différentes feuilles de calcul. Dans ce cas, la sélection des feuilles reste la même.
Cependant, vous devez choisir la plage entière au lieu d'une cellule comme référence. Assurez-vous également que les données sont cohérentes sur toutes les feuilles.
Ajout d'une nouvelle feuille dans le classeur
La référence Excel 3D met automatiquement à jour les données lors de l'ajout d'une ou plusieurs nouvelles feuilles tant que ces feuilles se situent entre les plages sélectionnées dans la formule 3D. Voyons voir comment ça fonctionne.
Comme indiqué ci-dessous, le total des ventes pour quatre mois est de 250000 pour les mois de janvier à avril.
Ajoutons une nouvelle feuille contenant les données du mois de la mi-février.
Incorporez la valeur des ventes de la feuille de mi-février. Les données apparaîtront automatiquement dans la feuille principale de l'analyse de quatre mois.
Comme indiqué ci-dessus, les ventes totales pour tous les mois augmentent d'un montant de 10 000. Ainsi, sans altérer la formule, le résultat a été mis à jour automatiquement.
Cette flexibilité est pratique tout en suivant le record de ventes de toute l'année sur des ensembles de données massifs. Ajoutons le stock, la quantité vendue, la quantité achetée et les données de stock de fin à la feuille de mi-février.
Jetez un œil à l'impact de l'ajout ci-dessus sur les données dans la feuille d'analyse de quatre mois.
Par conséquent, la formule de référence a mis à jour avec succès toutes les données de la feuille principale.
Rendre les données cohérentes pour appliquer des références 3D
Lorsque vous prévoyez de consolider les données de plusieurs feuilles en une seule, assurez-vous que le nombre de cellules est cohérent. Si, par exemple, le stock acheté se trouve dans une certaine cellule de la feuille 1, il est nécessaire de le placer dans la même cellule sur toutes les autres feuilles. De plus, lors de l'ajout d'une nouvelle feuille, saisissez correctement les données dans les cellules.
Simplifiez les calculs avec le référencement Excel 3D
La mise en œuvre de formules 3D peut sembler difficile à mettre en œuvre, mais elles sont faciles à maintenir une fois que vous les exécutez correctement. Il vous permet d'ajouter de nouvelles données chaque mois sans avoir à faire les calculs depuis le début.
La formule 3D élimine également les risques de duplication ou d'omission des ventes pour un mois donné. Cette fonctionnalité sera plus avantageuse pour les comptables qui mettent à jour des ensembles de données chaque mois.