Fichier excel à transformer en tableau
CarineVL -  

Bonjour,

J'ai un petit souci avec les résumés de rapport journalier exportés en excel à l'aide d'un logiciel de gestion.

Ce n'est pas très clair et ce serait tellement plus facile d'avoir un tableau.

Dans le fichier joint, sur la gauche ce qui est exporté en excel et sur la droite ce que je souhaiterais avoir à l'aide de formules (dans la partie jaune) avec les dates que j'indiquerais moi-même (dans la partie verte)..

Est-ce possible ?

Merci d'avance ...

https://www.cjoint.com/c/OCljCIC5X1t


Windows / Chrome 133.0.0.0

Je suis capable du meilleur comme du pire. Mais dans le pire, c'est moi la meilleure ...

13 réponses

Résumé de la discussion

Transformation des résumés journaliers exportés en Excel en un tableau structuré, avec des dates personnalisables. Une solution complexe a été proposée, utilisant des formules avancées (LET, REDUCE, ASSEMB…) pour générer les colonnes Montant HTVA, Remise, TVA et Montant TTC et un total, avec entêtes inclus. Des ajustements pratiques ont été discutés : correction du nom de la feuille, compatibilité Excel 365 et gestion des insertions de lignes qui décalent les formules. Pour plus de robustesse, des versions élargissant les plages (jusqu’à 60 ans) et un contrôle de présence de la ligne « Total des Remises » ont été envisagées. Une approche dynamique et autonome a été présentée pour automatiser la génération du tableau et des totaux, en s’appuyant sur des formules de type LET et assemblages dynamiques.

Bobot (l’IA à votre service)
  1. danielc0

    Mon dernier mot :

    https://www.cjoint.com/c/OCnsAX0j6x4

    Pour le plaisir, je mets la formule monstrueuse (entêtes et totaux inclus, quand même) :

    =LET(tout;ASSEMB.H(ASSEMB.V("DATE";SEQUENCE(MAX(CNUM(C5:C20000))-C5+1;;C5));REDUCE("MONTANT H/TVA";SEQUENCE(MAX(CNUM(C5:C20000))-C5+1;;C5);LAMBDA(x;y;ASSEMB.V(x;LET(l;EQUIVX("*";A:A;2;-1);tbl;ASSEMB.H(EXCLURE(REDUCE(CNUM($C$5);DECALER($C$5;;;l);LAMBDA(x;y;SI(DECALER(y;;-2)="récapitulatif du";ASSEMB.V(x;CNUM(y));ASSEMB.V(x;PRENDRE(x;-1)))));-1);DECALER($D$5:$E$5;;;l));PRENDRE(FILTRE(PRENDRE(tbl;;-1);(PRENDRE(tbl;;1)=y)*(CHOISIRCOLS(tbl;2)="HTVA");"");1)))));REDUCE("REMISE";SEQUENCE(MAX(CNUM(C5:C20000))-C5+1;;C5);LAMBDA(x;y;ASSEMB.V(x;LET(l;EQUIVX("*";A:A;2;-1);tbl;ASSEMB.H(EXCLURE(REDUCE(CNUM($C$5);DECALER($C$5;;;l);LAMBDA(x;y;SI(DECALER(y;;-2)="récapitulatif du";ASSEMB.V(x;CNUM(y));ASSEMB.V(x;PRENDRE(x;-1)))));-1);DECALER($D$5:$E$5;;;l));PRENDRE(FILTRE(CHOISIRCOLS(tbl;2);(PRENDRE(tbl;;1)=y)*(CHOISIRCOLS(tbl;2)<>0)*(ESTNUM(CHOISIRCOLS(tbl;2)));"");1)))));REDUCE("TVA";SEQUENCE(MAX(CNUM(C5:C20000))-C5+1;;C5);LAMBDA(x;y;ASSEMB.V(x;LET(l;EQUIVX("*";A:A;2;-1);tbl;ASSEMB.H(EXCLURE(REDUCE(CNUM($C$5);DECALER($C$5;;;l);LAMBDA(x;y;SI(DECALER(y;;-2)="récapitulatif du";ASSEMB.V(x;CNUM(y));ASSEMB.V(x;PRENDRE(x;-1)))));-1);DECALER($D$5:$E$5;;;l));PRENDRE(FILTRE(PRENDRE(tbl;;-1);(PRENDRE(tbl;;1)=y)*(CHOISIRCOLS(tbl;2)="TVA");"");1)))));REDUCE("MONTANT TTC";SEQUENCE(MAX(CNUM(C5:C20000))-C5+1;;C5);LAMBDA(x;y;ASSEMB.V(x;LET(l;EQUIVX("*";A:A;2;-1);tbl;ASSEMB.H(EXCLURE(REDUCE(CNUM($C$5);DECALER($C$5;;;l);LAMBDA(x;y;SI(DECALER(y;;-2)="récapitulatif du";ASSEMB.V(x;CNUM(y));ASSEMB.V(x;PRENDRE(x;-1)))));-1);DECALER($D$5:$E$5;;;l));PRENDRE(FILTRE(PRENDRE(tbl;;-1);(PRENDRE(tbl;;1)=y)*(CHOISIRCOLS(tbl;2)="TTC");"");1))))));ASSEMB.V(tout;{""."".""."".""};ASSEMB.H("TOTAL";SOMME(CHOISIRCOLS(tout;2));SOMME(CHOISIRCOLS(tout;3));SOMME(CHOISIRCOLS(tout;4));SOMME(PRENDRE(tout;;-1)))))

    Daniel

    2
    1. CarineVL Auteur

      RE,

      Je viens juste de tester ...

      Top de chez top, au cent près ...

      Pour le plaisir ... en souvenir aussi d'Herbert Leonard

      C'est vrai que la formule est vraiment à rallonge.

      Encore un grand merci et chapeau encore plus bas ...

      Carine

      0
    2. CarineVL Auteur

      Encore une dernière petite chose:

      Je n'ai même pas osé vous demander l'explication de la formule car je pense que pour moi ce sera quand même "mission impossible" :-) :-) :-) .

      Bien à vous,

      Carine

      0
  3. danielc0

    Bonjour,

    Une première approche avec Excel 365.

    1. Est-il normal qu'il y ait deux fois le 01/02/2025  ?

    2. Est-il normal qu'il manque parfois la ligne "total remises" ?

    Formule unique :

    =LET(tbl;ASSEMB.H(C5:C194;E7:E196;D6:D195;E8:E197;E9:E198);FILTRE(tbl;PRENDRE(tbl;;1)>40000))

    Daniel

    0
    1. CarineVL Auteur

      Bonjour,

      merci de la réponse ...

      La dernière ligne c'est la ligne de totalisation commençant par "TOTAL" ...

      Il n'y a pas de total remise si aucune remise n'a été effectuée ce jour-là....

      Je teste et reviens vers vous ...

      Carine

      0
    2. CarineVL Auteur

      Re,

      Tout est parfait sauf les jours où il n'y a justement pas de remise car le résultat est décalé d'une colonne sur la gauche ...

      Encore merci,

      Carine

      0
    3. CarineVL Auteur

      Re,

      Je viens de percuter pour le deuxième 01/02 ...

      Dans le bas, c'est la période demandée ... (de la date à la date)

      0
  4. PHILOU10120

    Bonjour CarineVL

    Une idée dans le fichier

    https://www.cjoint.com/c/OClmGwhUkx4

    0
    1. CarineVL Auteur

      Bonjour,

      Merci de la réponse ...

      Il serait donc nécessaire d'insérer une ligne les jours où il n'y a pas eu de remise ?

      Carine,

      0
    3. CarineVL Auteur
      @PHILOU10120

      Re,

      Ne serait-il pas possible que cette insertion se fasse aussi via une première formule afin d'éviter les erreurs "humaines" ?

      Encore merci ...

      Carine

      0
    4. PHILOU10120
      @CarineVL

      Dans ce cas n'est-il pas possible dans votre logiciel de gestion d'extraire la ligne remise même si celle-ci est à zéro

      0
    5. CarineVL Auteur
      @PHILOU10120

      Re,

      Si j'ai bien compris dans le cas où la remise est à 0 et qu'elle ne figure donc pas, au lieu de l'extraire, il faudrait plutôt l'inclure et indiquer "0" pour que cela fonctionne.

      Non malheureusement car la société ayant créé ce logiciel sur mesure (pendant plus de 10 ans) n'existe plus car en faillite ...

      C'est la raison pour laquelle on essaie de se débrouiller d'une autre manière "avec les moyens du bord" car plus aucun développement n'est possible dans le futur ...

      Bien à vous,

      Carine

      0
  6. danielc0

    L'image était incorrecte :

    =LET(tbl;ASSEMB.H(C5:C194;E7:E196;D6:D195;E8:E197;E9:E198);FILTRE(tbl;PRENDRE(tbl;;1)>40000))

    Daniel

    0
    1. CarineVL Auteur

      Re,

      Avec la formule je n'arrive pas au même résultat.

      Comment faites-vous avec CCM pour envoyer une image ou une capture d'écran car je n'y parviens pas ?

      Bien à vous,

      Carine

      0
    3. danielc0
      @CarineVL

      C'est une histoire de fous ? Qu'est-ce que tu obtiens avec mon classeur ?

      Daniel

      0
  7. danielc0

    Clique sur cette icône :

    Voici un lien sur mon fichier :

    https://www.cjoint.com/c/OCloeMcKxv4

    Daniel

    0
    1. CarineVL Auteur

      Re,

      Cela m'affiche ce résultat...

      0
    2. CarineVL Auteur

      Re,

      Voila ce qu'il affiche ...

      0
    3. danielc0
      @CarineVL

      La différence, c'est que tu veux voir figurer les jours comme le 02/02/2025 qui ne figurent pas dans le tableau initial ? Ou il y a autre chose ?

      Daniel

      0
    4. CarineVL Auteur
      @danielc0

      Re,

      Si on peut faire figurer tous les jours du mois, c'est mieux ...

      Mais il y a des jours (comme le 06/02) où les colonnes sont décalées et ne reprennent pas le montant dans la bonne colonne ...

      Bien à toi,

      Carine

      0
    5. CarineVL Auteur
      @CarineVL

      Re,

      Je viens (peut-être) de comprendre ...

      Cela fonctionne avec le fichier de Philou qui avait inséré une ligne à chaque fois qu'il n'y avait pas de remise pour ce jour ...

      Ne peut-on pas faire une première formule pour ces jours où il n'y a effectivement pas de remise et insérer une ligne?.

      Bien à toi,

      Carine

      0
  8. danielc0

    Formule modifiée :

    =LET(tbl;ASSEMB.H(C5:C194;E7:E196;BYROW(D6:D195;LAMBDA(x;SI(ESTTEXTE(x);"";x)));E8:E197;E9:E198);FILTRE(tbl;PRENDRE(tbl;;1)>40000))

    Daniel

    0
    1. danielc0

      Donc, ,avec cette disposition :

      Daniel

      0
    2. CarineVL Auteur
      @danielc0

      Re,

      Je viens (peut-être) de comprendre ...

      Cela fonctionne avec le fichier de Philou qui avait inséré une ligne à chaque fois qu'il n'y avait pas de remise pour ce jour ...

      Ne peut-on pas faire une première formule pour ces jours où il n'y a effectivement pas de remise et insérer une ligne?.

      Je commence à avoir tellement de fichiers que cela commence à être difficile de s'en sortir.

      Le résultat vient bien du premier fichier envoyé ?

      Le plus facile ce serait avec un fichier joint.

      Merci d'avance.

      Bien à toi,

      Carine

      0
  9. danielc0

    ok. Re-voici le lien :

    https://www.cjoint.com/c/OClshav2ET4

    Daniel

    0
    1. CarineVL Auteur

      Bonjour Daniel,

      Vous serait-possible de m'expliquer la formule pour essayer de comprendre et de mourir moins idiote  et aussi pour pouvoir l'adapter aux autres mois plus longs ?

      Je vous en remercie d'avance ...

      Carine

      0
    2. danielc0
      @CarineVL

      Pour avoir le nombre de jours du mois, mets (avec le premier jour du mois en C5) :

      =SEQUENCE(FIN.MOIS(C5;0)-C5+1;;C5)

      Pour les explications, je prépare un classeur, ça ferait trop long pour un message.

      Daniel

      0
    4. CarineVL Auteur
      @danielc0

      Un grand merci ...

      Carine

      0
  10. PapyLuc51

    Salutations CarineVL

    ""aussi pour pouvoir l'adapter aux autres mois plus longs""

    J'ai fais une réponse dans ce sens dans mon message # 29

    Cordialement

    0
    1. danielc0

      Pour adapter aux mois les plus longs (et plus encore), en une seule formule, entêtes et totaux compris :

      =LET(tbl;ASSEMB.H($C$5:$C$1904;$E$7:$E$1906;$D$6:$D$1905;$E$8:$E$1907;$E$9:$E$1908);flt;FILTRE(tbl;PRENDRE(tbl;;1)>40000);ASSEMB.V(ASSEMB.H(ASSEMB.V("DATE";SEQUENCE(FIN.MOIS(C5;0)-C5+1;;C5));REDUCE({"MONTANT HTVA"."REMISE"."TVA"."MONTANT TTC"};SEQUENCE(FIN.MOIS(C5;0)-C5+1;;C5);LAMBDA(x;y;ASSEMB.V(x;RECHERCHEX(y;PRENDRE(flt;;1);EXCLURE(flt;;1);ASSEMB.H("";"";"";""))))));{""."".""."".""};ASSEMB.H("TOTAL";SOMME(CHOISIRCOLS(flt;2));SOMME(CHOISIRCOLS(flt;3));SOMME(CHOISIRCOLS(flt;4));SOMME(CHOISIRCOLS(flt;5)))))

      Daniel

      0
      1. CarineVL Auteur

        Re Daniel,

        J'ai fait le test sur un autre mois (Décembre 2024)

        Tout fonctionne parfaitement sauf en ce qui concerne les totaux qui ont l'air d'avoir doublé sauf la remise.

        Cela me semble très facile d'utilisation car on peut déplacer librement la zone de calcul sans problèmes.

        (voir fichier joint)

        Bien à vous,

        Carine

        https://www.cjoint.com/c/OCmpDo1Enkt

        0
      2. danielc0
        @CarineVL

        Ce qui fausse les totaux, c'est le dernier groupe qui est en lui-même un total :

        Comme le montant des remises figure avec un point décimal au lieu d'être une virgule, il n'est pas pris en compte.

        Je regarde comment éliminer ça.

        Daniel

        0
      3. danielc0
        @danielc0

        Ca n'a pas été trop compliqué :

        =LET(tbl;ASSEMB.H($C$5:$C$1904;$E$7:$E$1906;$D$6:$D$1905;$E$8:$E$1907;$E$9:$E$1908);flt;EXCLURE(FILTRE(tbl;PRENDRE(tbl;;1)>40000);-1);ASSEMB.V(ASSEMB.H(ASSEMB.V("DATE";SEQUENCE(FIN.MOIS(C5;0)-C5+1;;C5));REDUCE({"MONTANT HTVA"."REMISE"."TVA"."MONTANT TTC"};SEQUENCE(FIN.MOIS(C5;0)-C5+1;;C5);LAMBDA(x;y;ASSEMB.V(x;RECHERCHEX(y;PRENDRE(flt;;1);EXCLURE(flt;;1);ASSEMB.H("";"";"";""))))));{""."".""."".""};ASSEMB.H("TOTAL";SOMME(CHOISIRCOLS(flt;2));SOMME(CHOISIRCOLS(flt;3));SOMME(CHOISIRCOLS(flt;4));SOMME(CHOISIRCOLS(flt;5)))))

        Veux-tu que je joigne le classeur ?

        Daniel

        1
      4. CarineVL Auteur
        @danielc0

        re,

        Fonctionne parfaitement.

        Chapeau bas ...

        Dans le cas où le fichier est plus important (comme par exemple 6 mois au lieu d'un, cela marche toujours ou faut-t-il se limiter à un seul mois ?

        Bien à vous, et vous remerciant encore,

        Carine

        0
      5. danielc0
        @CarineVL

        Il faut que je modifie. Là, c'est prévu pour un mois.

        Daniel

        1
    2. PHILOU10120

      Bonjour CarineVL

      Le fichier modifier avec mois janvier février mars c'est un essai (données copier coller pour exemple, dites moi si cela vous convient ?

      https://www.cjoint.com/c/OCmp0kFSm64

      0
      1. CarineVL Auteur

        Bonjour,

        C'est vraiment top ...effectué avec minutie pour faciliter le travail ...

        Je vois qu'il y a même un contrôle si la ligne contenant "Total des Remises" existe bien.!!!

        Une petite question à ce niveau:

        Quelle est la procédure à suivre dans le cas où cette ligne n'existe pas ?

        J'ai inséré une ligne au 03/12/2024, ajouté "Total des remises" mais le message est le même car en faisant l'insertion, la formule s'est également décalée de 1.

        (voir fichier joint)

        Comment faire donc pour arriver à un message "OK" ? (tout en conservant le tableau sans vides due aux éventuelles insertions)

        Bien à vous,

        Carine

        https://www.cjoint.com/c/OCniXZ7i3Yt

        1
      2. danielc0

        Bonjour,

        A tester, mais il semble que la formule :

        =SIERREUR(INDEX(E:E;EQUIV(G5;C:C;0)+EQUIV("HTVA";INDIRECT("D"&EQUIV(G5;C:C;0)&":D"&EQUIV(G5;C:C;0)+5);0)-1);"")

        en H5 permette d'éviter d'ajouter les lignes en cas ne remise absente.

        Daniel

        0
      3. CarineVL Auteur
        @danielc0

        Re, 

        Effectivement la colonne h/tva est juste sans faire quoique ce soit au niveau des lignes de remises, hormis une petite différence de 0,03€ (voir fichier joint).

        Pour le mois de décembre 2024 (2), j'ai sur le serveur 53500,28€ et après récupération des données 53500,31€ (en faisant l'addition sur la partie gauche extraite).

        Vos formules sont donc tout-fait correctes ...

        Cette petite erreur d'extraction ne proviendrait-t-elle pas de la manière dont les données sont extraites sur le serveur ?

        J'avais choisi le mode TEXTE ... et je ne sais pas quelles sont les décimales après la virgule dont il a été tenu compte ...

        Comme il y a différents types de collages possibles ...il y en a peut-être un qui correspond mieux ...

        Bien à vous,

        Carine

        https://www.cjoint.com/c/OCnmtKvPbit

        0
      4. CarineVL Auteur
        @CarineVL

        ou devoir peut-être reconvertir les données extraites à la décimale nécessaire pour arriver au même chiffre exact ?

        0
      5. CarineVL Auteur
        @CarineVL

        Je remarque sur le serveur, il y a 6 chiffres après la décimale alors que sur les données extraites, il n'y en a que 2  de la manière que je l'ai faite en copiant en mode texte...

        0
    3. cousinhub29

      Re-,

      Un fichier utilisant Power Query

      Dans l'onglet "Paramètres", tu mets le chemin et le nom du fichier xml (comme dans l'exemple)

      Dans l'onglet "Resultat", tu cliques sur le bouton "Actualiser tout" du ruban "Données" (ou un clic droit sur une cellule de la requête, "Actualiser"), pour mettre à jour la requête.

      Pour voir...

      https://www.cjoint.com/c/OCns6iglR7F

      0
      1. CarineVL Auteur

        Bonsoir,

        Je testerai demain car je suis morte ce soir.

        Je ne manquerai pas de revenir vers vous.

        Une très bonne soirée,

        Carine

        0
      2. CarineVL Auteur

        Bonjour,

        J'ai regardé un tuto et cela me semble effectivement très intéressant pour transformer les données.

        Entretemps DanielCo m'a fait un fichier très facile qui m'évite de faire toutes ces manipulations.

        Je vous remercie néanmoins pour cette info qui s'avérera sans doute fort utile dans le futur.

        Bien à vous,

        Carine

        0
    4. PHILOU10120

      Bonsoir CarineVL

      Le fichier au dernier jus de décembre à avril données copier coller pour essai

      Tout à l'air Ok à vous de tester

      https://www.cjoint.com/c/OCnuf3gJTI4

      Bon testes 

      0
      1. CarineVL Auteur

        Bonjour Philou,

        Cela fonctionne aussi très bien mais DanielCo a réussi à éviter l'ensemble de ces manip juste à l'aide d'une seule formule (monstrueuse) à coller dans n'importe quelle cellule.

        Encore merci et bien à vous,

        Carine

        0

    Discussions similaires

    question pix mot caché
    aorum10

    7 réponses