Synthétiser les données de différents fichiers en un seul tableau
Bonjour,
J'ouvre un nouveau post dans l'attente de la résolution du précédent.
Je travaille pour une asso d'hébergement qui verse de l'argent aux bénéficiaires. J'ai 3 tableaux de suivis compta (1 par mode de paiement), tous dans des fichiers différents. Je souhaite pouvoir regrouper toutes mes dépenses en un seul tableau me permettant de synthétiser, pour chaque bénéficiaire, le montant total versé par poste et par mois. Exemple : combien j'ai versé à M. X en septembre 2024 pour :
- ses dépenses médicales,
- ses fournitures scolaires,
- son alimentation.
Chacun des 3 tableaux n'est pas structuré de la même façon. Je ne peux pas me servir des mêmes colonnes de chaque tableau pour n'en créer qu'un. De plus, dans le même mois, je peux verser plusieurs fois des montants différents à la même personne pour une même utilisation (dépenses médicales, fournitures scolaires ou alimentation pour reprendre mon exemple) ou pas.
J'ai créé des fichiers fictifs pour mon exemple :
Les 3 fichiers de compta :
https://docs.google.com/spreadsheets/d/1Mto9mrHUEQN5xcFe2MDc454IGac898Z_q4ZPRDIA5s4/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1p4it_PIz2CqULS7-YvSiW8ydPKSrW2dXTDsqM5Li3IE/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1xxLEzmtnURuWc3v-aJmf-AyjaRyhPJpuwrLqCRtd4q0/edit?usp=sharing
Et le fichier sur lequel je veux synthétiser mes données :
https://docs.google.com/spreadsheets/d/17Ic2cuPuEU6NPU9Oii2VcFLtUn1L2xrq1bOe84uw7-o/edit?usp=sharing
Je vous remercie d'avance de votre aide.
Salut !
L'idéal serait de structurer les tableaux compta sous le même format, voici tout de même une parade.
1: regrouper toutes les données, cela peut se faire avec un cumul de IMPORTRANGE et de QUERY :
2.Une fois que toute les données sont regroupés, il faut identifier les postes de dépenses (idem, il serait préférable d'avoir une colonne prestation et une colonne nom prénom).
Pour se faire un QUERY suffit, il faut lui dire d'afficher le total du montant : sum(D) lorsque la prestations (C) ressemble à C1, D1 ou E1 et lorsque la référence dossier correspond :
=QUERY(Imports!$A:$D;"select sum(D) where C like '%"&C1&"%' and B = "&A2&" label sum(D)''")
Sauf que réécrire une formule sur chaque ligne c'est chiant, il faut rendre cela dynamique, donc la formule finale est un peu plus complexe :
=map($A2:$A;lambda(refDossier;SIERREUR(QUERY(Imports!$A:$D;"select sum(D) where C like '%"&C1&"%' and B = "&refDossier&" label sum(D)''");"")))
Et voici donc le résultat :
L'avantage est que tu peux ajouter des personnes, des prestations (dans le cas d'une prestation il suffit d'ajouter une colonne et faire glisser la formule), tout est dynamique.
Bonsoir,
Merci pour ta réponse. Je vais essayer ça et je reviendrai vers toi pour te dire ce que ça donne. Merci encore.
C'est parfait, cela fonctionne ! Merci beaucoup d'avoir pris le temps de m'aider. Je reviendrai sans hésitation sur ce forum !
Bonjour,
Je réouvre cet échange car, là où mon test fonctionnait, cela ne fonctionne plus sur mon "vrai" fichier. J'ai bien tenu compte de la formule QUERY et IMPORTRANGE mais pour 5 fichiers différents (peut-être que le problème est dû au fait qu'il y a trop de fichiers justement ?).
Bref, j'ai adapté mes fichiers comme conseillé pour avoir des colonnes identiques et séparer mes prestations des noms (2 colonnes différentes) mais quand je mets tous mes QUERY et IMPORTANGE à la suite, j'ai ce message d'erreur : #VALUE : ErreurDans ARRAY_LITERAL, il manque des valeurs pour une ou plusieurs lignes d'un littéral de tableau.
Mais chaque QUERY fonctionne pourtant individuellement. Voici ma formule complète, voyez-vous une erreur quelconque ?
={QUERY(IMPORTRANGE("LIEN"; "JANVIER 2025!A5:I"); "select Col1, Col5, Col6, Col7, Col8, Col9 where Col9 is not null"); QUERY(IMPORTRANGE("LIEN"; "JANVIER 2025!A5:I"); "select Col1, Col5, Col6, Col7, Col8, Col9 where Col9 is not null"); QUERY(IMPORTRANGE("LIEN"; "JANVIER 2025!A5:I"); "select Col1, Col5, Col6, Col7, Col8, Col9 where Col9 is not null"); QUERY(IMPORTRANGE("LIEN"; "JANVIER 2025!A5:I"); "select Col1, Col5, Col6, Col7, Col8, Col9 where Col9 is not null"); QUERY(IMPORTRANGE("LIEN"; "JANV!A5:I"); "select Col1, Col5, Col6, Col7, Col8, Col9 where Col9 is not null")}
Merci d'avance pour votre aide.
Bonjour,
Je crois me rappeler que cette erreur a lieu quand la taille des tableaux n'est pas identique, essayes d'ajouter 1 tableau, puis 2 , puis 3 , etc... voir si le problème vient d'un import en particulier.
Si c'est liée à la taille des données (trop de data à importer) alors il faudra passer par un script, fait le 1er test et revient dire si ça fonctionne ou non.
Re bonjour,
J'ai essayé ce que tu m'as conseillé. J'ai essayé toutes les combinaisons possibles et dès que je mets plus de deux fonction QUERY IMPORTRANGE, il commence par m'afficher le même d'erreur puis, au bout de quelques secondes, il affiche correctement soit 1 soit 2 de mes imports (jamais davantage) et de manière totalement aléatoire j'ai l'impression.
Cela pourrait venir de la taille des données d'après toi ?
Oui ! Importrange a une limite assez facilement atteignable quand on brasse un peu de data.
Une solution serait de remplacer ce fonctionnement par un script, le problème c'est que c'est moins facilement modifiable qu'une formule, il faut donc qu'il soit calibré pour durer et si des choses sont à changer, si possible le prévoir pour le faire du fichier sheets.
Je vois que tu importes un mois (janvier 2025) comment fonctionne grosso modo ton tableau ?
En fait, j'anticipe 2025, année à partir de laquelle je dois commencer à utiliser mon fichier avec les imports.
J'ai donc 5 tableaux (fichiers différents) de suivi comptable, 1 par mode de paiement : espèces, CB, chèque, carte prépayée et chèques services. Ca, cela ne peut pas bouger : exigence du service comptable.
Chacun de ces tableaux à le même nombre de colonnes et est structuré exactement de la même façon (mêmes noms de colonnes etc...). Et chacun de ces fichiers dispose de 12 onglets : 1 onglet par mois.
Dans un fichier à part, je dois importer les informations comptables des 6 colonnes de ces 5 fichiers dans un seul et même tableau. Là aussi, j'aurai 1 onglet par mois. Ces informations sont complétées ligne par ligne quotidiennement au fur et à mesure des dépenses réalisées dans chacun des 5 fichiers (en fait, ce sont "juste" des tableaux de tenue de compte). Cela représente un nombre de lignes différent d'un tableau à l'autre, soit entre 10 et 200 lignes selon les dépenses effectuées.
Au final, je dois me servir de ce tableau des imports pour en créer un autre, comme tu me l'as déjà conseillé, avec une fonction MAP et LAMBDA qui fonctionne d'ailleurs très bien lorsque le tableau des imports veut bien fonctionner également.
J'espère être assez claire dans mes explications, je me débrouille à peu près avec Sheets mais je n'en maîtrise pas le "jargon".