Excel: Remplir Des Données Sur Plusieurs Feuilles De Calcul

Malheureusement pour mon employeur, aucun de mes ingénierie de réseau de cours inclus advanced Excel formule de programmation. Inutile de dire, je ne sais rien à propos de Excel enregistrer pour la SOMME de base et de la NUMÉRATION de la formule de commandes.

Mon employeur dispose d'un classeur Excel avec plusieurs feuilles de calcul à l'intérieur de celui-ci représentant de chacun des mois de l'année civile. Nous voulons être en mesure d'avoir un "total" de la feuille de calcul dans le classeur qui reflète toutes les données concernant l'ensemble du classeur dans chaque colonne/rangée.

Un exemple pour plus de clarté:

  • Dans la feuille de calcul "May_2013", Une colonne est intitulée "DATE". La cellule A2 contient les données "PEUVENT-1".

  • Dans la feuille de calcul "June_2013", Une colonne est intitulée "DATE". La cellule A2 contient les données de JUIN "-1".

  • Dans la feuille "Total", Une colonne est intitulée "DATE". Nous voulons que les cellules A2 à réfléchir "MAI-1" et A3 pour refléter "JUIN-1".

Nous voulons faire cela pour toutes les feuilles de calcul, des colonnes A-Q, des lignes 2-33 et remplir une fiche à la fin, contenant toutes les données de toutes les feuilles de calcul dans leurs colonnes correspondantes.

Est-ce possible?

  • Lire plus attentivement (après affichage rapide bits de code) je ne suis pas sûr de ce que vous avez l'intention de faire avec la ligne 3 de la feuille de calcul. J'ai supposé qu'il serait copié à la ligne 3 de la fiche de synthèse, mais maintenant je vois que vous voulez mettre la ligne 2 de JUIN. Comment les données de l'autre les lignes traitées? Pouvez-vous préciser?
  • Jusqu'à présent, j'ai trouvé une solution, mais il est incroyablement fastidieux et demande beaucoup de temps, même si les résultats sont ce que je suis à la recherche pour. Quelqu'un m'a recommandé d'entrer le texte suivant: "=SheetName!Cellule pour chaque cellule correspondante sur les Totaux de la feuille. C'est une folle quantité de travail, mais il fait ce que je veux faire, c'est la fusion de toutes les données sur toutes les feuilles de calcul du classeur pour les totaux de la feuille.
InformationsquelleAutor Brandon | 2013-04-10
  1. 2

    Voici deux solutions VBA. Le premier fait ceci:

    1. Vérifier si une feuille "totaux" existe. Créer si elle n'est pas
    2. De copie de la première rangée (de A à Q), de la première feuille "totaux"
    3. Copie de bloc A2:Q33 à "totaux" de la feuille de départ à la ligne 2
    4. Répétez l'opération pour toutes les autres feuilles, en ajoutant 32 lignes inférieures à chaque fois

    La deuxième montre comment faire des manipulations de la colonne de données avant la copie: pour chaque colonne, il applique la WorksheetFunction.Sum(), mais vous pouvez la remplacer avec une autre fonction d'agrégation que vous souhaitez utiliser. Puis il copie le résultat (une ligne par feuille) pour les "totaux" de la feuille.

    Les deux solutions sont dans le classeur vous pouvez télécharger à partir de ce site. Exécuter les macros avec , et de choisir le plus approprié dans la liste d'options qui s'affiche. Vous pouvez modifier le code en invoquant l'éditeur VBA avec .

    Sub aggregateRaw()
Dim thisSheet, newSheet As Worksheet
Dim sheetCount As Integer
Dim targetRange As Range
sheetCount = ActiveWorkbook.Sheets.Count
' add a new sheet at the end:
If Not worksheetExists("totals") Then
Set newSheet = ActiveWorkbook.Sheets.Add(after:=Sheets(sheetCount))
newSheet.Name = "totals"
Else
Set newSheet = ActiveWorkbook.Sheets("totals")
End If
Set targetRange = newSheet.[A1]
' if you want to clear the sheet before copying data, uncomment this line:
' newSheet.UsedRange.Delete
' assuming you want to copy the headers, and that they are the same
' on all sheets, you can copy them to the "totals" sheet like this:
ActiveWorkbook.Sheets(1).Range("1:1").Copy targetRange
Set targetRange = targetRange.Offset(1, 0) ' down a row
' copy blocks of data from A2 to Q33 into the "totals" sheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> newSheet.Name Then
ws.Range("A2", "Q33").Copy targetRange
Set targetRange = targetRange.Offset(32, 0) ' down 32 rows
End If
Next ws
End Sub
Sub aggregateTotal()
Dim thisSheet, newSheet As Worksheet
Dim sheetCount As Integer
Dim targetRange As Range
Dim columnToSum As Range
sheetCount = ActiveWorkbook.Sheets.Count
' add a new sheet at the end:
If Not worksheetExists("totals") Then
Set newSheet = ActiveWorkbook.Sheets.Add(after:=Sheets(sheetCount))
newSheet.Name = "totals"
Else
Set newSheet = Sheets("totals")
End If
' assuming you want to copy the headers, and that they are the same
' on all sheets, you can copy them to the "totals" sheet like this:
Set targetRange = newSheet.[A1]
ActiveWorkbook.Sheets(1).Range("A1:Q1").Copy targetRange
Set targetRange = targetRange.Offset(1, 0) ' down a row
For Each ws In ActiveWorkbook.Worksheets
' don't copy data from "total" sheet to "total" sheet...
If ws.Name <> newSheet.Name Then
' copy the month label
ws.[A2].Copy targetRange
' get the sum of the coluns:
Set columnToSum = ws.[B2:B33]
For colNum = 2 To 17 ' B to Q
targetRange.Offset(0, colNum - 1).Value = WorksheetFunction.Sum(columnToSum.Offset(0, colNum - 2))
Next colNum
Set targetRange = targetRange.Offset(1, 0) ' next row in output
End If
Next ws
End Sub
Function worksheetExists(wsName)
' adapted from http://www.mrexcel.com/forum/excel-questions/3228-visual-basic-applications-check-if-worksheet-exists.html
worksheetExists = False
On Error Resume Next
worksheetExists = (Sheets(wsName).Name <> "")
On Error GoTo 0
End Function

    Final(?) edit:
    Si vous voulez que ce script à exécuter automatiquement chaque fois que quelqu'un fait un changement pour le classeur, vous pouvez capturer l' SheetChange de l'événement par l'ajout du code pour le classeur. Pour cela, procédez comme suit:

    1. ouvrir l'éditeur Visual Basic ()
    2. Dans l'explorateur de projet (à gauche de l'écran), développez le projet vba
    3. Clic droit sur "ThisWorkbook", et sélectionnez "Afficher le Code"
    4. Dans la fenêtre qui s'ouvre, copiez/collez les lignes de code suivantes:
     
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' handle errors gracefully:
On Error GoTo errorHandler
' turn off screen updating - no annoying "flashing"
Application.ScreenUpdating = False
' don't respond to events while we are updating:
Application.EnableEvents = False
' run the same sub as before:
aggregateRaw
' turn screen updating on again:
Application.ScreenUpdating = True
' turn event handling on again:
Application.EnableEvents = True
Exit Sub ' if we encountered no errors, we are now done.
errorHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
' you could add other code here... for example by uncommenting the next two lines
' MsgBox "Something is wrong ... " & Err.Description
' Err.Clear
End Sub
    • Où dois-je saisir ce script VB? Et il sera de fusionner toutes les données sur toutes les feuilles de calcul dans un master totaux de la feuille?
    • Je me trompe peut-être, mais en regardant ton code, il me semble que si il fait un peu d'arithmétique. Ce que je veux je pense que c'est un peu plus simple. Je veux juste toutes les données sur toutes les feuilles dans le classeur apparaissent sur des totaux de la page.
    • Il n'était pas clair à partir de la question de savoir si tu voulais totaux ou tout simplement toutes les données brutes - en particulier depuis que vous avez dit que vous vouliez A3 avoir "juin" en elle. J'ai donné deux options: la première des copies de toutes les données, le second prend totaux. Et oui, vous l'ouvrez l'éditeur VBA , module d'insertion, de coller le code. Laissez-moi savoir si vous avez besoin de plus d'informations pour obtenir ce travail.
    • Pour vous aider, j'ai créé un fichier fictif avec un couple de feuilles de données, avec une version de travail de ces deux macros en elle. Vous pouvez le télécharger à partir de cet emplacement. Exécuter une macro à l'aide de <Alt-F8>, regarder code avec <Atl-F11>. Vous verrez deux fonctions - aggregateRaw et aggregateTotal, correspondant environ à chacun des ci-dessus. Laissez-moi savoir si cela fonctionne pour vous!
    • Vous pouvez copier vos feuilles dans le mannequin classeur et supprimer les feuilles qui sont actuellement là-bas, puis l'enregistrer sous un nouveau nom (avec .xlsm extension à activer les macros). Que doit résoudre pour vous.
    • Mes excuses pour mon manque de clarté. J'ai couru votre script, et il fait tout ce que je voulais faire, mais quand j'ai modifier une cellule sur les autres feuilles de calcul, il n'est pas à jour les totaux de la feuille, et lorsque je tente de l'exécuter à nouveau le script de mise à jour, il renvoie une erreur d'exécution. Suis-je en train de faire quelque chose de mal?
    • Pas de soucis à propos de "manque de clarté" - il peut être difficile de décrire ces choses, c'est pourquoi nous utilisons des commentaires à préciser. Ce "erreur d'exécution" message avez-vous? Obtenez-vous l'occasion de "debug" ? - il serait de vous montrer la ligne contenant l'erreur. L'exigence de la mise à jour automatique n'a jamais été explicitées dans votre question...
    • En passant, avez-vous d'exécuter le script, comme c'est montrer a l'heure actuelle (la plus récente édition qui se passait pendant que vous écrivez votre commentaire...). Je n'ai corriger un petit problème dans le code, dans les 15 dernières minutes... version la plus récente est au même lien que précédemment.
    • Dans la première itération de votre code que j'ai essayé, il a parfaitement fonctionné la première fois que j'ai couru. La deuxième fois (pour le mettre à jour comme il ne l'a pas fait automatiquement) retourné "erreur d'Exécution: 1004 définie par l'Application ou par l'objet défini erreur".
    • La dernière ligne de code fonctionne comme un charme! Est-il un moyen de continuellement exécuter ce script automatiquement?
    • J'ai édité ma réponse: suivez les instructions pour le script de s'exécuter chaque fois que vous modifiez la feuille. L'effet secondaire est que si vous modifiez manuellement le "totaux" de la feuille, votre modification sera annulée (puisque les données écraser lorsque le script s'exécute).
    • Dois-je l'appliquer à l'Classeur en VB ou les Totaux de la feuille?
    • Lorsque j'exécute ce script, j'obtiens cette erreur:
    • Votre commentaire a été tronqué; je ne peux pas voir votre erreur. Le nouveau script doit aller dans le classeur de code (de cette façon, il sera piège de l'événement, quelle que soit la feuille qui en est la cause).
    • Lorsque j'exécute ce script, j'obtiens cette erreur: "erreur de Compilation: Sub ou function non définie", et il met en évidence cette ligne: "Private Sub Workbook_SheetChange(ByVal Sh as Object, ByVal Target as Range)"
    • Je suis surpris. Êtes-vous essayer d'exécuter la SheetChange chose? Vous n'avez pas besoin d'exécuter quoi que ce soit. Chaque fois que vous changer quelque chose dans votre classeur, les totaux seront automatiquement mise à jour. Vous n'avez pas à "exécuter" quoi que ce soit maintenant. Juste au cas où, j'ai re-téléchargé une version qui a été de travailler sur mon ordinateur quelques minutes plus tôt (avec deux feuilles de données factices) sur le même lien que ci-dessus. Il suffit d'entrer les données et regarder la magie...
    • Désolé, je ne savais pas. L'ensemble du script fonctionne très bien. Seul problème, c'est qu'il supprime tous mes en-têtes au sommet de l'totaux de la feuille de calcul. Peut-on garder mes en-têtes intact, et ajouter les infos ci-dessous la toute première ligne de la feuille de calcul? Toutes les autres feuilles de calcul ont exactement les mêmes en-têtes. Si nous pouvons obtenir les en-têtes de travail, c'est une bouée de sauvetage, et exactement ce dont j'ai besoin. Merci beaucoup pour votre attention et votre aide.
    • Dans Module1, de trouver les lignes ' if you want to clear the sheet before copying data, uncomment this line: ' newSheet.UsedRange.Delete ' assuming you want to copy the headers, and that they are the same ' on all sheets, you can copy them to the "totals" sheet like this: ActiveWorkbook.Sheets(1).Range("1:1").Copy targetRange . Si vous commentez la ligne (en mettant une apostrophe ' en face d'elle), votre problème devrait disparaître? Une ligne similaire existe dans le aggregateTotal fonction, mais je pense que vous ne l'utilisez pas, que de toute façon. Lorsque vous confirmer que c'est OK, vous pourriez envisager de faire "accepter" la réponse...
    • Merci. Obtenu les en-têtes de passe. L'information ne semble pas être de mise à jour lorsqu'une cellule à l'un des autres feuilles de calcul est modifié. Est-il un moyen pour obtenir les totaux de la feuille de mise à jour à l'instant une cellule du classeur est édité?
    • Je me demande si quelque chose vous a déclenché une erreur, donc vous êtes laissé dans un état de traitement des événements a été désactivé. Essayer deux choses: tout d'abord, fermer Excel et ouvrez-le à nouveau, est - ce que de l'aide? Deuxièmement, ajouter un nouveau sous de Module1 avec seulement trois lignes: Sub reStart() Application.EnableEvents=True End Sub. Exécuter cette macro. Voir si les choses s'améliorent. Laissez-moi savoir.
    • Fermé Excel et réouvert par itération en cours de code en vain. Ajout de la ligne affichée Sous reStart() Application.EnableEvents=True End Sub de nouveau en vain. Est-il un paramètre que je dois activer pour autoriser les macros à boucle en continu?
    • La macro n'est pas "en continu" en boucle - à la capture d'un événement spécifique (SheetChange), et les mises à jour les totaux de la feuille lorsque cela se produit. Assurez-vous que le Fichier-->Options - >Centre de gestion de la-->Paramètres des Macros-->"Activer toutes les macros" est activé. Je suis en supposant que vous utilisez une version récente d'Excel?
    • Il a été le paramètre spécifié dans Excel. Je vous remercie beaucoup pour votre temps et de la patience avec Excel noobie. En fait j'ai appris beaucoup avec cela. J'apprécie beaucoup votre persistance et de l'aide avec ce problème. Merci encore!
    • Heureux de vous aider. Heureux que vous avez appris quelque chose - nous avons tous commencé quelque part, et avait quelqu'un pour nous aider....

    InformationsquelleAutor Floris
  2. 0

    Veuillez utiliser RDBMerge add-in qui permettra de combiner les données de feuille de calcul différente et de créer une fiche pour vous. Veuillez voir le lien ci-dessous pour plus de détails.

    http://duggisjobstechnicalstuff.blogspot.in/2013/03/how-to-merge-all-excel-worksheets-with.html

    Télécharger RDBMerge

    • Il n'y a aucun moyen de le faire sans ajouter de logiciel?
    • Son excel add-in. Une fois que vous consultez le complément de votre classeur sa comme plug n play. Bien sûr, il peut être possible sans cet addin.
    • J'ai téléchargé et installé l'add-in. Je vois où elle me permet de fusionner toutes les données dans chaque colonne correspondante, mais je ne vois pas où je peux définir une colonne de sortie ou une feuille de calcul pour ce type de données.
    • le résultat sur la feuille de calcul dans laquelle vous exécutez la macro complémentaire.
    InformationsquelleAutor Santosh
  3. 0

    Vous pouvez utiliser l'indirects de la fonction de référence le nom de la feuille. Dans l'image ci-dessous cette fonction prend le nom d'en-tête (37) et l'utilise comme la feuille de référence. Tout ce que vous avez à faire est de choisir le bon "nombre total de cellules" que j'ai fait "A1" à "MAY_2013". J'ai mis une image ci-dessous pour vous montrer mon nom de référence ainsi que le nom de l'onglet

    Excel: Remplir Des Données Sur Plusieurs Feuilles De Calcul

    • Elle renvoie une référence de cellule non valide erreur. Ce que les cellules dois-je faire référence ici?
    • la première référence doit être une cellule avec exactement le nom de l'onglet. la deuxième référence doit être l'emplacement de cellule dans le référencés onglet qui a de la valeur que vous souhaitez retourner. Si vous n'avez manuellement, il devrait ressembler à =MAY_2013!A1
    • C'est précisément ce que je veux faire, mais je vais avoir besoin de saisir de cette formule dans chaque cellule sur le total de la feuille? Les totaux de la feuille doit contenir toutes les données sur toutes les feuilles dans le classeur, donc c'est un ensemble beaucoup de l'édition. Est-il un moyen de remplir les cellules avec cette information à une plus grande rationalisation de la sorte? Est-il possible de définir une plage? Quelque chose comme =May_2013!A1:A35 (cela ne fonctionne pas, mais juste à titre d'exemple).
    • dans vos feuilles de calcul, vous pouvez avoir une totale en "A1" et si cet emplacement est le même pour toutes les feuilles, alors cela va fonctionner. Sinon, vous pouvez avoir une plage nommée pour ces valeurs sur chaque fiche, mais alors vous n'auriez pas besoin indirects.
    • Chaque feuille de calcul dans le classeur a exactement la même table de configuration, avec les mêmes catégories, y compris les totaux de la feuille. Il est en fait pas d'autres formules n'importe où dans le classeur. C'est assez simple autre que ce totaux feuille je suis en train de créer. Quelle serait la bonne syntaxe de la formule à mis en place une gamme donc je n'ai pas à mettre une formule séparée dans chaque cellule?
    • Essayez cette =SUM(INDIRECT(INDIRECT("B35")&"!A1:A35")) Il fera le bilan de cette gamme spécifique pour ce que l'en-tête que vous avez tant que l'en-tête correspond à un onglet.
    • Essaie de tester cette formule: qu'est-Ce que le "B35", en référence à nouveau? Voici comment je suis dans elle: "=SOMME(May_2013(May_2013("B35")&"!A1:A35"))' Retourne une erreur de "non Valide erreur de nom". Je pense que c'est en référence à l'B35.
    • B35 est la cellule qui contient le nom de l'onglet en elle, A1:A35 est la plage que vous souhaitez somme dans l'onglet qui porte le même nom que la cellule B35. Vous devez utiliser le INDIRECT fonction pour que cela fonctionne
    • L'onglet de la feuille de calcul? Ou de l'onglet dans la colonne catégorie dans la May_2013 feuille de calcul? J'ai des données utilisé comme une catégorie de colonne "Dernier" des noms de famille dans la cellule E1. Si je veux copier la gamme de tous les noms, aurais-je le faire -- '=SOMME(INDIRECT(INDIRECT("E1")&"!E2:E35"))'
    • onglet comme dans le nom de la feuille. Voir l'image ci-dessus.
    • J'ai tapé "=SOMME(INDIRECT(INDIRECT("May_2013")&"!E2:E35"))" (May_2013 est une feuille de calcul dans le classeur), et il est revenu "référence de cellule non Valide".

    InformationsquelleAutor grandocu