XL 2019 Somme des cellules de plusieurs fichiers Excel

[nanie]

Bonjour,

Je dispose de plus de 60 fichiers excel identiques. Chaque fichier contient 25 feuilles.

Je voudrais créer un fichier global , qui a la même structure que les 60 autres. Ce fichier sera la somme des autres fichiers.

Comment proceder ? Je ne connais pas du tout VBA ou query ou les tableaux croisés dynamiques

En vous remerciant vivement

Merci

 

[chris]

RE

J'ai remis le fichier au #27 : j'avais corrigé ce lien mais pas posté le bon

Ferme tout puis ouvre Excel avant de lancer l'une ou l'autre solution pour partir sur de bonnes bases

Ton chemin me parait très long et plein de caractères spéciaux, j'espère que cela ne pose pas de problèmes.
Sinon tu dispose de quelle RAM sur ton PC ?

Si cela se reproduit clique sur la ligne copier les détails pour voir si cela explique le PB

EDIT : sur tes fichiers volumineux la solution VBA est nettement plus rapide

 

[nanie]

J'ai fait comme tu m'as dit.
J'ai tout fermé, puis relancé.
Là tout a été ok.

J'ai ensuite refermé, puis lancé une nouvelle simulation avec un autre intervenant et là l'erreur est réapparue.
Voici ce qui est noté quand j'ai fait copié les détails.

Feedback Type:
Frown (Error)

Error Message:
The MashupResource's PageReader is not available.

Stack Trace:
à Microsoft.Mashup.Client.Excel.Fill.SchemaInfo.CreateSchemaInfo(IMashupResource mashupResource, QueryFillStatus FillStatus, Boolean isMashupResourceEvaluationCompleted)
à Microsoft.Mashup.Client.Excel.Fill.PassiveFillSession.CompleteRefresh(QueryFillStatus newFillStatus)
à Microsoft.Mashup.Client.Excel.Fill.PassiveFillSession.SetQueryRefreshCompleted()
à Microsoft.Mashup.Client.Excel.Fill.PassiveFillSession.ResumeFill(Boolean onTimer)
à Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<UpdateQueriesProgress>b__17_0(IFillSession fillSession)
à Microsoft.Mashup.Client.UI.Shared.Model.QueriesUtilities.ForEachWithChangeScope[T](IEnumerable`1 items, Func`2 getQueries, Action`1 action)
à Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
à Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
à Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action)
à Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action)
à Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<OnFillUpdateTimerTick>b__11_0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
The MashupResource's PageReader is not available.

Invocation Stack Trace:
à Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
à Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
à Microsoft.Mashup.Client.UI.Windows.UIHost.RaiseErrorDialog(IWindowHandle activeWindow, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
à Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
à Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
à Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à System.Windows.Forms.Timer.OnTick(EventArgs e)
à System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
à System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


Supports Premium Content:
False

MashupResource.Status:
Initializing

Formulas:


section Section1;

shared Fanny = let
Source = Folder.Files(Table.FirstValue(Excel.CurrentWorkbook(){[Name="Dossier"]}[Content])),
#"Lignes filtrées" = Table.SelectRows(Source, each not Text.StartsWith([Name], "Synthese")),
#"Fichiers masqués filtrés1" = Table.SelectRows(#"Lignes filtrées", each [Attributes]?[Hidden]? <> true),
#"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier à partir de Fanny", each #"Transformer le fichier à partir de Fanny"([Content])),
#"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
#"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Transformer le fichier à partir de Fanny"}),
#"Transformer le fichier à partir de Fanny développé" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier à partir de Fanny", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Index", "Index.1", "Type"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Index", "Index.1", "Type"}),
#"Lignes triées" = Table.Sort(#"Transformer le fichier à partir de Fanny développé",{{"Index", Order.Ascending}})
in
#"Lignes triées";

shared #"Paramètre de l'exemple de fichier1" = #"Exemple de fichier" meta [IsParameterQuery=true, BinaryIdentifier=#"Exemple de fichier", Type="Binary", IsParameterQueryRequired=true];

shared #"Transformer l'exemple de fichier à partir de Fanny" = let
Source = Excel.Workbook(#"Paramètre de l'exemple de fichier1", null, true),
ANNEE_Sheet = Source{[Item="ANNEE",Kind="Sheet"]}[Data],
#"Colonnes supprimées" = Table.RemoveColumns(ANNEE_Sheet,{"Column11"},MissingField.Ignore),
#"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
#"Premières lignes supprimées" = Table.Skip(#"Type modifié",7),
#"Lignes filtrées1" = Table.SelectRows(#"Premières lignes supprimées", each ([Column1] <> "EN €") and ([Column2] <> "€ HT / H")),
#"Index ajouté" = Table.AddIndexColumn(#"Lignes filtrées1", "Index", 0, 1),
#"Lignes filtrées" = Table.SelectRows(#"Index ajouté", each [Index] <= 30),
#"Rempli vers le bas" = Table.FillDown(#"Lignes filtrées",{"Column1"}),
#"Index ajouté1" = Table.AddIndexColumn(#"Rempli vers le bas", "Index.1", 1, 1),
#"Requêtes fusionnées" = Table.NestedJoin(#"Index ajouté1",{"Index"},#"Index ajouté1",{"Index.1"},"Index ajouté1",JoinKind.LeftOuter),
#"Index ajouté1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Index ajouté1", {"Column1"}, {"Column1.1"}),
#"Lignes triées" = Table.Sort(#"Index ajouté1 développé",{{"Index", Order.Ascending}}),
#"Personnalisée ajoutée" = Table.AddColumn(#"Lignes triées", "Type", each if Text.Start([Column2],2)="VP" or Text.Start([Column2],3)="DSN" then Text.BeforeDelimiter([Column2]," ") else
if [Column1]="TOTAL MOIS" and [Column1.1]="TOTAL MOIS" then
null else if [Column1]="TOTAL MOIS" then [Column1.1] else [Column1]),
#"Rempli vers le bas1" = Table.FillDown(#"Personnalisée ajoutée",{"Type"}),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Rempli vers le bas1",{"Column1.1"})
in
#"Colonnes supprimées1";

shared #"Exemple de fichier" = let
Source = Folder.Files(Table.FirstValue(Excel.CurrentWorkbook(){[Name="Dossier"]}[Content])),
#"Lignes filtrées" = Table.SelectRows(Source, each not Text.Contains([Name], "Synthese")),
Navigation1 = #"Lignes filtrées"{0}[Content]
in
Navigation1;

[ FunctionQueryBinding = "{""exemplarFormulaName"":""Transformer l\u0027exemple de fichier à partir de Fanny""}" ]
#"Transformer le fichier à partir de Fanny" = let
Source = (#"Paramètre de l'exemple de fichier1" as binary) => let
Source = Excel.Workbook(#"Paramètre de l'exemple de fichier1", null, true),
ANNEE_Sheet = Source{[Item="ANNEE",Kind="Sheet"]}[Data],
#"Colonnes supprimées" = Table.RemoveColumns(ANNEE_Sheet,{"Column11"},MissingField.Ignore),
#"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
#"Premières lignes supprimées" = Table.Skip(#"Type modifié",7),
#"Lignes filtrées1" = Table.SelectRows(#"Premières lignes supprimées", each ([Column1] <> "EN €") and ([Column2] <> "€ HT / H")),
#"Index ajouté" = Table.AddIndexColumn(#"Lignes filtrées1", "Index", 0, 1),
#"Lignes filtrées" = Table.SelectRows(#"Index ajouté", each [Index] <= 30),
#"Rempli vers le bas" = Table.FillDown(#"Lignes filtrées",{"Column1"}),
#"Index ajouté1" = Table.AddIndexColumn(#"Rempli vers le bas", "Index.1", 1, 1),
#"Requêtes fusionnées" = Table.NestedJoin(#"Index ajouté1",{"Index"},#"Index ajouté1",{"Index.1"},"Index ajouté1",JoinKind.LeftOuter),
#"Index ajouté1 développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Index ajouté1", {"Column1"}, {"Column1.1"}),
#"Lignes triées" = Table.Sort(#"Index ajouté1 développé",{{"Index", Order.Ascending}}),
#"Personnalisée ajoutée" = Table.AddColumn(#"Lignes triées", "Type", each if Text.Start([Column2],2)="VP" or Text.Start([Column2],3)="DSN" then Text.BeforeDelimiter([Column2]," ") else
if [Column1]="TOTAL MOIS" and [Column1.1]="TOTAL MOIS" then
null else if [Column1]="TOTAL MOIS" then [Column1.1] else [Column1]),
#"Rempli vers le bas1" = Table.FillDown(#"Personnalisée ajoutée",{"Type"}),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Rempli vers le bas1",{"Column1.1"})
in
#"Colonnes supprimées1"
in
Source;

shared BTS1_DMN_ECCT = let
Source = Fanny,
#"Autres colonnes supprimées" = Table.SelectColumns(Source,{"Column2", "Column3", "Column4", "Column5", "Index", "Type"}),
#"Lignes filtrées" = Table.SelectRows(#"Autres colonnes supprimées", each ([Index] = 1 or [Index] = 3 or [Index] = 6 or [Index] = 8 or [Index] = 11 or [Index] = 13 or [Index] = 27 or [Index] = 29)),
#"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Lignes filtrées", {"Index", "Type"}, "Attribut", "Info"),
#"Personnalisée ajoutée" = Table.AddColumn(#"Supprimer le tableau croisé dynamique des autres colonnes", "Donnee", each if (try Number.From([Info]) otherwise null) =null then [Info] else null),
#"Personnalisée ajoutée1" = Table.AddColumn(#"Personnalisée ajoutée", "Valeur", each try Number.From([Info]) otherwise null),
#"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée1",{"Attribut", "Info"}),
#"Personnalisée ajoutée2" = Table.AddColumn(#"Colonnes supprimées", "Index2", each if [Donnee]=null then [Index]-2 else [Index]),
#"Type modifié" = Table.TransformColumnTypes(#"Personnalisée ajoutée2",{{"Valeur", type number}})
in
#"Type modifié";

shared BTS2 = let
Source = Fanny,
#"Autres colonnes supprimées" = Table.SelectColumns(Source,{"Column7", "Column8", "Column9", "Column10", "Index", "Type"}),
#"Lignes filtrées" = Table.SelectRows(#"Autres colonnes supprimées", each [Index] = 1 or [Index] = 3 or [Index] = 6 or [Index] = 8),
#"Valeur remplacée" = Table.ReplaceValue(#"Lignes filtrées","1","2",Replacer.ReplaceText,{"Type"}),
#"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Valeur remplacée", {"Index", "Type"}, "Attribut", "Info"),
#"Personnalisée ajoutée" = Table.AddColumn(#"Supprimer le tableau croisé dynamique des autres colonnes", "Donnee", each if (try Number.From([Info]) otherwise null) =null then [Info] else null),
#"Personnalisée ajoutée1" = Table.AddColumn(#"Personnalisée ajoutée", "Valeur", each try Number.From([Info]) otherwise null),
#"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée1",{"Attribut", "Info"}),
#"Personnalisée ajoutée2" = Table.AddColumn(#"Colonnes supprimées", "Index2", each if [Donnee]=null then [Index]-2 else [Index]),
#"Type modifié" = Table.TransformColumnTypes(#"Personnalisée ajoutée2",{{"Valeur", type number}})
in
#"Type modifié";

shared VP = let
Source = Fanny,
#"Lignes filtrées" = Table.SelectRows(Source, each ([Type] = "DSN" or [Type] = "VP") and ([Index] =15 or [Index] =18 or [Index] =20 or [Index] =24)),
#"Autres colonnes supprimées" = Table.SelectColumns(#"Lignes filtrées",{"Index", "Type","Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
#"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Autres colonnes supprimées", {"Index", "Type"}, "Attribut", "Info"),
#"Personnalisée ajoutée" = Table.AddColumn(#"Supprimer le tableau croisé dynamique des autres colonnes", "Donnee", each if (try Number.From([Info]) otherwise null) =null then [Info] else null),
#"Personnalisée ajoutée1" = Table.AddColumn(#"Personnalisée ajoutée", "Valeur", each try Number.From([Info]) otherwise null),
#"Personnalisée ajoutée2" = Table.AddColumn(#"Personnalisée ajoutée1", "Index2", each if [Donnee]<>null then [Index] else [Index]-(if [Type]="VP" then 3 else 4)),
#"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée2",{"Attribut", "Info"}),
#"Type modifié1" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Valeur", type number}})
in
#"Type modifié1";

shared Tout = let
Source = Table.Combine({BTS1_DMN_ECCT, BTS2, VP}),
#"Lignes groupées" = Table.Group(Source, {"Type", "Index"}, {{"Tabl", each _, type table}}),
#"Personnalisée ajoutée3" = Table.AddColumn(#"Lignes groupées", "Personnalisé", each Table.AddIndexColumn([Tabl],"Idx",1)),
#"Personnalisé développé" = Table.ExpandTableColumn(#"Personnalisée ajoutée3", "Personnalisé", {"Donnee", "Valeur", "Index2", "Idx"}, {"Donnee", "Valeur", "Index2", "Idx"}),
#"Lignes groupées1" = Table.Group(#"Personnalisé développé", {"Type", "Index2", "Idx"}, {{"Valeur", each List.Sum([Valeur]), type number}, {"Tabl", each _, type table}}),
#"Tabl développé" = Table.ExpandTableColumn(#"Lignes groupées1", "Tabl", {"Donnee"}, {"Donnee"}),
#"Lignes filtrées1" = Table.SelectRows(#"Tabl développé", each ([Donnee] <> null)),
#"Lignes groupées2" = Table.Group(#"Lignes filtrées1", {"Type", "Donnee"}, {{"Valeur", each List.Sum([Valeur]), type number}})
in
#"Lignes groupées2";


Pour la RAM, comment je peux voir ça ?

 

[chris]

RE

Teste le VBA : d'après mes essais sur tes fichiers volumineux c'est nettement plus rapide

1ères recherches sur le net concernant l'erreur PowerQuery : cela semble un bug
Vérifier si la version est à jour mais pars sur VBA.

 

[nanie]

Re
Donc j'ai enlevé le fichier SyntheseCours pour mettre le fichier Recap3 dans mon dossier.
Je l'ai ouvert et j'ai appuyer sur consolider.
et là j'ai ça

C'est normal ?

 

[chris]

RE

Il faut modifier le chemin d'accès dans le code VBA

 

[nanie]

Il faut modifier le chemin d'accès dans le code VBA

heu .... tu peux m'expliquer comment je dois faire ? Je ne connais pas du tout, désolée.

 

[chris]

Re

tu ouvres Récap(2).xlsm , Alt F11 pour acèder à l'éditeur
A gauche tu double cliques sur Module1
A droite sur la ligne chemin, tu remplaces
T:\TEMP\____xld\Fanny
par ton chemin d'accès

tu peux ensuite fermer l'éditeur et lancer le code

A noter que sur les 60 fichiers tests basés sur tes 2 fichiers tests initiaux (poids unitaire 1630ko) , je n'ai pas de problème, ni sur 2010 ni sur 365, et des temps de 20 à 30 secondes sur Powerquery et de 3,2 secondes avec VBA (PC 365, le plus rapide)

 

[nanie]

Re !!
Oh la la !!!! ça fonctionne !!!!!!!
C'est GE-NIAL !!!!!!!
Vous êtes des génies ! Vous n'imaginez même pas la joie !!
merci ! merci ! merci !!

et ... imaginons maintenant que j'ai besoin de rajouter dans ce fichier RECAP la partie basse Salaires, et formations continues afin de savoir aussi combien d'heures ont été réalisées pour ces parties, je peux rajouter ? ou cela nécessite également une modification du fichier que vous m'avez fait ?

 

[chris]

RE

au départ job75 avait tout traité dans un modèle identique à test
Ton modèle étant différent j'ai modifié et limité son code aux zones de ta Récap

Il faudrait dans le code ajouter les plages concernées (donc avoir ton modèle complet) et compléter la table des décalages entre les lignes de récap et celle des sources...

 

[job75]

Bonjour nanie, chris,

Au post #1 il a été dit :

Je voudrais créer un fichier global , qui a la même structure que les 60 autres. Ce fichier sera la somme des autres fichiers.

C'est quand même le plus simple non ? Et le plus logique.

Donc utilisez Récap(2).xlsm, et c'est logique aussi d'utiliser le dossier ThisWorkbook.Path.

A+

 

[chris]

RE

 

[nanie]

chris !
Je te remercie sincèrement.
Ca fonctionne parfaitement.
Bien à toi.