Discussion :

[karpi]

Bonjour à tous,

J'ai cherché mais ne trouve pas de réponse à ma question car je ne parviens pas à être explicite dans les moteurs de recherches, donc je me résous à l'expliquer dans un forum.

• J'utilise un ERP/CRM et exporte depuis cet outil des données au format XLS.
• Parmi les données j'ai un champ codé qui me permet d'extraire plusieurs informations, j'utilise pour cela les formules GAUCHE, DROITE, STXT d'Excel sur une autre feuille de calcul.
• Les données extraitent sont ensuite compilées dans des tableaux croisés dynamiques

Mon problème réside dans le fait que je ne peux pas anticiper le nombre de lignes du fichier excel importé car il augmente régulièrement. Sachant que ce fichier est sensé durer plusieurs années, je suis contraint de compter large, aussi j'ai prévu 100.000 écritures reproduites sur 4 onglets différents. Mon fichier excel de traitement devient donc très vite très lourd (26 Mo).

• Feuille 1 = BDD importée (= données sources)
• Feuille 2, 3, 4 et 5 = des formules GAUCHE, DROITE, STXT mélangé avec des SI (en fonction de la condition SI les données apparaissent ou non dans chacune de ces feuilles)
• Feuille 6 = mes tableaux croisés dynamiques de synthèse

Je me demande s'il n'existe pas une option/formule qui me permettrait d'étendre les formules sur le nombre de lignes nécessaires, pour une meilleure compréhension:

• Ma base de données importées (Feuil1) a 50 lignes; toutes les formules de ma ligne 2 de la Feuil2 sont reproduites sur ces 50 lignes
• Mon projet évolue, ma base de données passe à 100 lignes, toutes les formules de ma ligne 2 de la Feuil2 s'étendent jusqu'à la 100ème ligne
• Mon projet évolue, ma base de données passe à X lignes, toutes les formules de ma ligne 2 de la Feuil2 s'étendent jusqu'à la Xème ligne

C'est possible?

Merci d'avance pour votre aide éclairée

[Pierre Dumas]

Bonjour

Je pense qu'il suffit que les données de la feuille 1 soit sous forme de Tableau Structuré (Accueil > Mettre sous forme de tableau).
De plus, l'ensemble des formules des feuilles 2 à 5 me semblent devoir rester dans la feuille 1.
Quand on est en Tableau Structuré, il suffit de rajouter des lignes en copiant-collant sur la première ligne juste en dessous de la dernière saisie. Le Tableau structuré s'agrandit et toutes les formules se recopient toutes seules jusqu'à la nouvelle dernière ligne.

En espérant que cela aide.

Bonne fin d'après-midi

Pierre Dumas

[Pierre Fauconnier]

Salut.

Si les données sont agencées comme une "table à plat"^(*), c'est-à-dire une plage rectangulaire avec éventuellement une ligne d'intitulés, je laisserais le classeur xls comme source et je rapatrierais ses données dans mon classeur de travail avec Power Query. Ca permettrait au passage de traiter les infos avec Power Query plutôt que par formules. Le résultat du traitement Power Query est injecté sous forme de tableau structuré dans Excel, de sorte que tu n'as besoin de rien prévoir.

Pour moi, la règle est de ne jamais touché à la source, mais de l'importer dans le classeur de travail. Si le nom de la source change, tu peux soit renommer manuellement le fichier, soit saisir son nom complet dans une cellule nommée de ton fichier de travail et utiliser cette donnée dans Power Query pour récupérer les données.

On ne travaille JAMAIS dans le classeur source


^(*) Si elle ne le sont pas mais que leur agencement suit une règle, il y a souvent moyen de traiter en PQ pour les remettre d'équerre. Si vraiment on ne sait pas, on amène les données brutes dans le classeur et on traite en VBA.

[karpi]

Merci !

@Pierre Dumas : ah oui je n'y avais pas pensé, le seul hic que je vois dans ta proposition est que j'écrase l'intégralité des données du tableau source, l'extraction ne permet pas de distinguer les nouvelles entrées.

Si les données sont agencées comme une "table à plat"(*), c'est-à-dire une plage rectangulaire avec éventuellement une ligne d'intitulés, je laisserais le classeur xls comme source [...] On ne travaille JAMAIS dans le classeur source

Héhé, c'est exactement ce que je fais:

• Génération du fichier source depuis mon ERP/CRM
• Enregistrement des données dans un fichier SOURCE.XLS
• Depuis mon fichier TRAVAIL.XLS j'importe les données sources dans Feuil1 (Données/Obtenir des données/Depuis Fichier/classeur excel), je traite les données dans Feuil 2 à 5

je rapatrierais ses données dans mon classeur de travail avec Power Query. Ca permettrait au passage de traiter les infos avec Power Query plutôt que par formules. Le résultat du traitement Power Query est injecté sous forme de tableau structuré dans Excel, de sorte que tu n'as besoin de rien prévoir.

Ah mais je ne connais pas Power Query, va falloir que je me documente car ça me paraît très intéressant comme solution. Je suis preneur d'info pertinentes pour des tutoriels bien faits à ce sujet si tu en as.

Merci à vous!

[Pierre Dumas]

Bonsoir

@Pierre Dumas : ah oui je n'y avais pas pensé, le seul hic que je vois dans ta proposition est que j'écrase l'intégralité des données du tableau source, l'extraction ne permet pas de distinguer les nouvelles entrées.

Je ne comprends pas le souci.
Si la nouvelle extraction doit se rajouter aux données précédentes, il suffit de faire un copier/coller en-dessous.
Si la nouvelle extraction doit se substituer aux données précédentes, il suffit de supprimer les anciennes données et de coller les n

[...]
Depuis mon fichier TRAVAIL.XLS j'importe les données sources dans Feuil1 (Données/Obtenir des données/Depuis Fichier/classeur excel), je traite les données dans Feuil 2 à 5

Ah mais je ne connais pas Power Query, va falloir que je me documente car ça me paraît très intéressant comme solution. Je suis preneur d'info pertinentes pour des tutoriels bien faits à ce sujet si tu en as.

Ce que j'ai mis en rouge est exactement le point de départ de PowerQuery dans Excel 365. A moins de faire du PowerQuery sans le savoir...

Pour avoir des tutoriels, il est largement possible de lire le blog de Pierre Fauconnier (entre autres).

En espérant que cela aide.

Bonne soirée

Pierre Dumas

[karpi]

Je ne comprends pas le souci.
Si la nouvelle extraction doit se rajouter aux données précédentes, il suffit de faire un copier/coller en-dessous.
Si la nouvelle extraction doit se substituer aux données précédentes, il suffit de supprimer les anciennes données et de coller les n

Non, il n'y a pas de soucis, mais j'avais compris qu'il fallait rajouter les nouvelles entrées aux autres. Je n'utilise pas cette fonction tableau bien que je connaissais son existence. Je l'ai fait sous tes conseils, ça marche et c'est effectivement très pratique.

Ce que j'ai mis en rouge est exactement le point de départ de PowerQuery dans Excel 365. A moins de faire du PowerQuery sans le savoir...

Il semblerait que ce soit bien le cas
Par contre je ne suis pas très loin du point de départ, il va falloir que je trouve un peu de temps pour creuser.

Merci @ vous!