Microsoft 365 Remplir cellules à partir de plusieurs fichiers Excel d'un dossier

[edgy63]

Bonjour,
Je cherche à remplir automatiquement dans un fichier excel des cellules dont les valeurs proviennent de plusieurs fichiers excel présent dans un même dossier.
Pour l'instant je rempli les cellules manuellement en copiant collant le chemin et nom du fichier avec la formule suivante :
='chemin_d'accès_du_dossier\[nom_du_fichier]nom_de_l'onglet' ! position_de_la_cellule
Afin d'aller plus vite je voudrai inclure directement le nom du chemin + dossier dans la formule.
Pour ça j'ai fait "obtenir des données", "à partir d'un dossier" et j'obtiens un tableau avec une colonne "chemin" et une colonne "nom de fichier" que je lie avec la formule =concat
Mais je n'arrive pas à inclure le résultat de cette formule dans la formule du haut. Donc inclure une formule dans une formule. J'ai beau chercher sur les forums et google, je ne trouve pas comment faire.
Avez vous un moyen de faire ceci ou une autre solution à m'apporter?

 

[chris]

Bonjour

Puisque tu utilises PowerQuery va jusqu'au bout et rapatrie les cellules

Elle sont toujours au même endroit dans les feuilles ?

 

[edgy63]

Merci pour ta réponse.
J'ai utilisé powerquery en cherchant des tutos mais je n'ai pas l'habitude de l'utiliser.
Comment faire pour rapatrier les formules?
Oui les valeurs sont toutes au même endroit

 

[chris]

RE

Tu peux poster 2 fichiers exemples anonymisés, préciser la cellule et si le nom du fichier a une importance pour ta synthèse

 

[edgy63]

Voici 2 fichiers :
- BDD avec dans l'onglet résultat ce que je veux faire et dans l'onglet magasins ce que j'ai essayé de faire.
J'ai copié le chemin manuellement dans la formule et je n'arrive à aller chercher que la cellule A1.
- P1 dans lequel il faut aller chercher les données en A2 et B2.

Le nom du fichier n'a pas d'importance à part pour aller ouvrir le bon fichier mais je ne comprend pas bien cette question

 

[chris]

RE
Modifie le chemin puis actualise la synthese

 

[edgy63]

Ca marche très bien mais c'est un peu de la sorcellerie pour moi.
Comment tu as fait? C'est tout à partir de query?

 

[edgy63]

Finalement j'ai réussi à le refaire parce que les fichiers sont dans des tableaux simples et bien ordonnés.
Que ce passe t'il s'il y a plusieurs données dispatchées dans la feuille excel (mais toujours au même endroit pour chaque fichiers.
Comment faire pour aller chercher la valeur dans une cellule précise?
Par exemple, dans le nouveaux fichiers joint, comment créer une nouvelle colonne "lieu" et pour chaque magasin aller chercher la valeur en cellule C7 et enlever les lignes non voulues?
Merci

 

[chris]

RE

A donner des exemples trop simplistes toi et moi perdons du temps...

Pour apprécier la requête à faire il faut avoir une vue d'ensemble des tableaux sources : là avec quelques cellules on ne sait pas si ce sont des listes (il semble que non) ou autre chose et dans ce dernier cas, quels repères ont peut prendre ou pas...

PowerQuery ne se sert pas des adresses de cellules : on est dans la logique base de données et non tableur.
Donc selon la source il faut imaginer des passerelles que seules une vue complète permet de déceler

 

[edgy63]

Ah c'est dommage ça vu que les valeurs sont dans la même cellule.
J'essaie de comprendre le fonctionnement pour pouvoir y arriver seul parce que la mise en forme varie d'un fichier à l'autre. Mais en gros ils sont plutôt comme ceux dans l'exemple que je viens d'envoyer.
As tu une idée de comment faire avec ceux-ci? Et après je ne t'embête plus
(Et inclure une formule dans une formule comme j'essayais de le faire n'est pas possible?)

 

[chris]

RE

Comme dit il faut des repères : un mot clé dans une colonne peut en être un mais impossible de donner une piste sans rien voir... Surtout si c'est variable...

Avec une carte IGN j'arrive à faire des randos sympas mais au doigt mouillé rarement

C'est pareil dans PQ

 

[edgy63]

Là il y a le mot clé "Lieu" avec dans la cellule à sa droite le lieu en question.
Mais tu as téléchargé les nouveaux fichiers?

 

[chris]

RE

Cela ne suffit pas juger des autres contraintes et je ne vais pas perdre mon temps en supputations...

Tu ne veux pas fournir de cas réellement représentatifs, c'est ton choix...

 

[edgy63]

J'ai essayé de bidouiller un peu avec query. J'arrive à extraire les données quand le fichier est simple.
Mais je comprend maintenant ce que tu veux dire au sujet des contraintes et des repères. Désolé pour la perte de temps.
Je remet donc en pièce jointe des fichiers avec un cas représentatif.
Le problème de la mise en forme est qu'il y a un entête qui reviens plusieurs fois et que certaines des données dont j'ai besoin sont sur la première ligne et sont donc transformées en titre de colonne.

 

[mromain]

Bonjour edgy63, chris, le forum,

@edgy63 :
Tu trouveras ci-dessous une requête PowerQuery qui fonctionne sur les fichiers exemples fournis.
Il faut adapter l'étape pathFolder pour renseigner l'emplacement du dossier contenant les fichiers magasin :

let
    pathFolder = "C:\...\Magasins",
    FolderFiles = Folder.Files(pathFolder),
    FilterRemoveTempFiles = Table.SelectRows(FolderFiles, each not Text.StartsWith([Name], "~")),
    fnExtractFileData = (excelBinary as binary) as record =>
        let
            SourceFeuil1 = Excel.Workbook(excelBinary){[Kind="Sheet", Name="Feuil1"]}[Data],
            BufferData = Table.SelectRows(SourceFeuil1[[Column1], [Column2], [Column3], [Column4]], each List.NonNullCount(Record.ToList(_))>0),
            TableMontants = Table.FromRecords(List.Transform(List.PositionOf(BufferData[Column1], "Catégorie", 2), each [Catégorie=BufferData[Column2]{_}, CA=BufferData[Column2]{_+1}, Bénéfice=BufferData[Column2]{_+2}])),
            Result = [Magasin=BufferData[Column2]{0}, Lieu=BufferData[Column4]{0}, Adresse=BufferData[Column2]{1}, Téléphone=BufferData[Column4]{1}, GPS=BufferData[Column4]{2}, Montants=TableMontants]
        in
            Result,
    ExtractFilesInfos = Table.TransformColumns(FilterRemoveTempFiles, {{"Content", fnExtractFileData, type record}}),
    SelectColumnContent = Table.SelectColumns(ExtractFilesInfos,{"Content"}),
    DevelopColumnContent = Table.ExpandRecordColumn(SelectColumnContent, "Content", {"Magasin", "Lieu", "Adresse", "Téléphone", "GPS", "Montants"}, {"Magasin", "Lieu", "Adresse", "Téléphone", "GPS", "Montants"}),
    ChangeColumnsTypes = Value.ReplaceType(DevelopColumnContent, type table [Magasin=text, Lieu=text, Adresse=text, Téléphone=text, GPS=text, Montants=table])
in
    ChangeColumnsTypes

Les différents montants sont également récupérés, à toi de voir si et comment tu veux les exploiter.

A+