Microsoft 365 Trouver et calculer écarts ventes provenant de 2 systèmes

Germaine · 29 Mars 2025

Bonjour le forum,
Je dois vérifier la réciprocité des ventes provenant de 2 logiciels de comptabilité et 1 autre de vente.
Je dois trouver le même montant des au global.
La difficulté que je rencontre, ce sont les libellés de (DATA-B) qui sont vraiment mal foutus.
Pourriez-vous m'aider à mettre en place une réflexion, un retraitement me permettant de comparer les 2 fichiers afin d'expliquer les écarts.
Dans la Data, il y'a la notion de facture et d'avoir.
L'Avoir vient en soustraction de la vente et est dans la colonne debit dans le fichier de comptabilité, mais le fichier commercial toutes les données sont dans une seule colonne.
Dans le calcul des ventes du fichier commercial "DATA - A", il faut tenir compte que si le libellé est AV....représentant un avoir.
Dites moi
Vous remerciant par avance
Germaine

Jean-Eric · 30 Mars 2025

Re,
Une petite mise à jour pour les expressions régulières.
Cdlt.

Germaine · 31 Mars 2025

Germaine à dit:
Bonsoir,
Merci pour ton retour,
Je teste comme expliqué, je n'y avais pas pensé
Germaine

At The One,
Je ne comprends pas pourquoi les calculs ne s'effectuent pas !
J'ai bien renouvelé le process
Germaine

AtTheOne · 31 Mars 2025

Re,
Et sur mon fichier, lorsque tu modifies des données dans DATA-A ou DATA-B est-ce-que les calculs fonctionnent ?

Germaine · 31 Mars 2025

Germaine à dit:
Bonsoir,
Merci pour ton retour,
Je teste comme expliqué, je n'y avais pas pensé
Germaine

AtTheOne à dit:
Re,
Et sur mon fichier, lorsque tu modifies des données dans DATA-A ou DATA-B est-ce-que les calculs fonctionnent ?

Je viens de tester avec les mêmes données du tableau, ça fonctionne.
Donc, il s'agit de mes nouvelles données le problème.
Je regarde calmement.
Germaine

AtTheOne · 31 Mars 2025

Re,
Peux-tu m'envoyer un échantillons de nouvelles données (anonymisées) que tu voudrais utiliser (fichier brut sans modification de ta part).
J'essayerai chez moi
À bientôt

AtTheOne · 31 Mars 2025

Germaine à dit:
Donc, il s'agit de mes nouvelles données le problème.

Pas forcément, j'ai peut-être loupé un truc, mais il me faudrait des nouvelles données proches de la réalité pour fouiller un peu plus.

AtTheOne · 31 Mars 2025

Re, Peux-tu m"envoyer une capture d'écran du résultat de la formule après importation des nouvelles données ?

Germaine · 31 Mars 2025

AtTheOne à dit:
Re, Peux-tu m"envoyer une capture d'écran du résultat de la formule après importation des nouvelles données ?

Bonsoir AtTheOne,
Voici une partie de ma Data
Merci pour ton aide
Germaine

AtTheOne · 31 Mars 2025

Ok; Merci, je regarde mais il y a d’emblée une différences : les Factures de retour étaient dans les crédits, en fait elle sont dans les débits ...
Je pense que la formule coince quand dans une rubrique il n'y a pas de données. Je cherche et je reviendrai vers toi.
À bientôt

Germaine · 31 Mars 2025

AtTheOne à dit:
Ok; Merci, je regarde mais il y a d’emblée une différences : les Factures de retour étaient dans les crédits, en fait elle sont dans les débits ...
Je pense que la formule coince quand dans une rubrique il n'y a pas de données. Je cherche et je reviendrai vers toi.
À bientôt

AtTheOne,
J'avais dû faire une erreur dans ma réflexion. Les factures retour fonctionnent comme des Avoirs.
C'est bien au "Debit".
Merci si tu y arrives
Germaine

AtTheOne · 31 Mars 2025

Re et Re e e e
J'ai trouvé la raison, dans certaine parties de la formule, j'avais oublié de renvoyer un ligne vide ({"".""."".""}) lorsque le filtre ne renvoie rien.
Il me reste à régler le pb des factures de retour ...

AtTheOne · 31 Mars 2025

Et c'est encore moi,
J'ai repèré 5 types de références :
AV suivi de 7 chiffres Fonctionne en tant qu'avoir
F Suivi de 7 chiffres Fonctionne en tant que facture
FAS suivi de 6 chiffres Fonctionne en tant que facture
FAT suivi de 6 chiffres Fonctionne en tant que facture
FR suivi de 7 chiffres Fonctionne en tant qu'avoir

Est-ce-bien ça ? Y en a-t-il d'autres ?
À bientôt

Germaine · 31 Mars 2025

AtTheOne à dit:
Et c'est encore moi,
J'ai repèré 5 types de références :
AV suivi de 7 chiffres Fonctionne en tant qu'avoir
F Suivi de 7 chiffres Fonctionne en tant que facture
FAS suivi de 6 chiffres Fonctionne en tant que facture
FAT suivi de 6 chiffres Fonctionne en tant que facture
FR suivi de 7 chiffres Fonctionne en tant qu'avoir

Est-ce-bien ça ? Y en a-t-il d'autres ?
À bientôt

AtTheOne,
Non pas d'autres.
Te remerciant
Germaine

AtTheOne · 31 Mars 2025

Bon, je crois avoir régler les problèmes
Les noms définis deviennent :

Réf_Factures_A	=FILTRE(TS_DATA_A[N° pièce];(GAUCHE(TS_DATA_A[N° pièce];1)="F")*(GAUCHE(TS_DATA_A[N° pièce];2)<>"FR");"")
Réf_Avoirs_A	=FILTRE(TS_DATA_A[N° pièce];(GAUCHE(TS_DATA_A[N° pièce];2)="AV")+(GAUCHE(TS_DATA_A[N° pièce];2)="FR");"")
Factures_A	=CHOISIRCOLS(FILTRE(TS_DATA_A;ESTNUM(EQUIV(TS_DATA_A[N° pièce];Réf_Factures_A;0));{"".""."".""});2;3;5;6)
Avoirs_A	=CHOISIRCOLS(FILTRE(TS_DATA_A;ESTNUM(EQUIV(TS_DATA_A[N° pièce];Réf_Avoirs_A;0));{"".""."".""});2;3;5;6)

Réf_B	=SUPPRESPACE(DROITE(TS_DATA_B[Libellé écriture];9))
Réf_Factures_B	=FILTRE(Réf_B;(GAUCHE(Réf_B;1)="F")*(GAUCHE(Réf_B;2)<>"FR");"")
Réf_Avoirs_B	=FILTRE(Réf_B;(GAUCHE(Réf_B;2)="AV")+(GAUCHE(Réf_B;2)="FR");"")
Factures_B	=ASSEMB.H(Réf_Factures_B;CHOISIRCOLS(FILTRE(TS_DATA_B;ESTNUM(EQUIV(Réf_B;Réf_Factures_B;0));{"".""."".""});1;3;5))
Avoirs_B	=ASSEMB.H(Réf_Avoirs_B;CHOISIRCOLS(FILTRE(TS_DATA_B;ESTNUM(EQUIV(Réf_B;Réf_Avoirs_B;0));{"".""."".""});1;3;4))

la formule devient :

VB:

=LET(FA;FILTRE(Factures_A;ESTNA(EQUIV(Réf_Factures_A;Réf_Factures_B;0));{"".""."".""});
          FB;FILTRE(Factures_B;ESTNA(EQUIV(Réf_Factures_B;Réf_Factures_A;0));{"".""."".""});
          AA;FILTRE(Avoirs_A;ESTNA(EQUIV(Réf_Avoirs_A;Réf_Avoirs_B;0));{"".""."".""});
          AB;FILTRE(Avoirs_B;ESTNA(EQUIV(Réf_Avoirs_B;Réf_Avoirs_A;0));{"".""."".""});
          DFAB;LET(A;TRIER(FILTRE(Factures_A;ESTNUM(EQUIV(Réf_Factures_A;Réf_Factures_B;0));{"".""."".""});1);
                              B;TRIER(FILTRE(Factures_B;ESTNUM(EQUIV(Réf_Factures_B;Réf_Factures_A;0));{"".""."".""});1);
                              C;TRIER(ASSEMB.V(FILTRE(A;CHOISIRCOLS(A;4)<>CHOISIRCOLS(B;4);{"".""."".""});FILTRE(B;CHOISIRCOLS(B;4)<>CHOISIRCOLS(A;4);{"".""."".""})));
                       FILTRE(C;CHOISIRCOLS(C;1)<>"";{"".""."".""}));
          DAAB;LET(A;TRIER(FILTRE(Avoirs_A;ESTNUM(EQUIV(Réf_Avoirs_A;Réf_Avoirs_B;0));{"".""."".""});1);
                               B;TRIER(FILTRE(Avoirs_B;ESTNUM(EQUIV(Réf_Avoirs_B;Réf_Avoirs_A;0));{"".""."".""});1);
                               C;TRIER(ASSEMB.V(FILTRE(A;CHOISIRCOLS(A;4)<>CHOISIRCOLS(B;4);{"".""."".""});FILTRE(B;CHOISIRCOLS(B;4)<>CHOISIRCOLS(A;4);{"".""."".""})));
                      FILTRE(C;CHOISIRCOLS(C;1)<>"";{"".""."".""}));
ASSEMB.V(ASSEMB.H("Factures DATA-A absentes de DATA_B";{"".""};SOMME(CHOISIRCOLS(FA;4)));FA;
                      ASSEMB.H("Factures DATA-B absentes de DATA_A";{"".""};SOMME(CHOISIRCOLS(FB;4)));FB;
                      ASSEMB.H("Avoirs et retours DATA-A absents de DATA_B";{"".""};SOMME(CHOISIRCOLS(AA;4)));AA;
                      ASSEMB.H("Avoirs et retours DATA-B absents de DATA_A";{"".""};SOMME(CHOISIRCOLS(AB;4)));AB;
                      ASSEMB.H("Factures DATA-A différentes de DATA_B";{"".""};SI(LIGNES(DFAB)>=2;SOMME(CHOISIRCOLS(DFAB;4)*EST.IMPAIR(SEQUENCE(LIGNES(DFAB))))-SOMME(CHOISIRCOLS(DFAB;4)*EST.PAIR(SEQUENCE(LIGNES(DFAB))));0));DFAB;
                     ASSEMB.H("Avoirs et retours DATA-A différents de DATA_B";{"".""};SI(LIGNES(DAAB)>=2;SOMME(CHOISIRCOLS(DAAB;4)*EST.IMPAIR(SEQUENCE(LIGNES(DAAB))))-SOMME(CHOISIRCOLS(DAAB;4)*EST.PAIR(SEQUENCE(LIGNES(DAAB))));0));DAAB))

Fais des essais avec d'autres jeux de données pour vérifier.
Pas besoin de macro pour importer les nouveaux Jeux ?

Voir Pièce jointe
À bientôt

Germaine · 1 Avril 2025

AtTheOne à dit:
Bon, je crois avoir régler les problèmes
Les noms définis deviennent :

Réf_Factures_A =FILTRE(TS_DATA_A[N° pièce];(GAUCHE(TS_DATA_A[N° pièce];1)="F")*(GAUCHE(TS_DATA_A[N° pièce];2)<>"FR");"")
Réf_Avoirs_A =FILTRE(TS_DATA_A[N° pièce];(GAUCHE(TS_DATA_A[N° pièce];2)="AV")+(GAUCHE(TS_DATA_A[N° pièce];2)="FR");"")
Factures_A =CHOISIRCOLS(FILTRE(TS_DATA_A;ESTNUM(EQUIV(TS_DATA_A[N° pièce];Réf_Factures_A;0));{"".""."".""});2;3;5;6)
Avoirs_A =CHOISIRCOLS(FILTRE(TS_DATA_A;ESTNUM(EQUIV(TS_DATA_A[N° pièce];Réf_Avoirs_A;0));{"".""."".""});2;3;5;6)
Réf_B =SUPPRESPACE(DROITE(TS_DATA_B[Libellé écriture];9))
Réf_Factures_B =FILTRE(Réf_B;(GAUCHE(Réf_B;1)="F")*(GAUCHE(Réf_B;2)<>"FR");"")
Réf_Avoirs_B =FILTRE(Réf_B;(GAUCHE(Réf_B;2)="AV")+(GAUCHE(Réf_B;2)="FR");"")
Factures_B =ASSEMB.H(Réf_Factures_B;CHOISIRCOLS(FILTRE(TS_DATA_B;ESTNUM(EQUIV(Réf_B;Réf_Factures_B;0));{"".""."".""});1;3;5))
Avoirs_B =ASSEMB.H(Réf_Avoirs_B;CHOISIRCOLS(FILTRE(TS_DATA_B;ESTNUM(EQUIV(Réf_B;Réf_Avoirs_B;0));{"".""."".""});1;3;4))

la formule devient :

VB:

=LET(FA;FILTRE(Factures_A;ESTNA(EQUIV(Réf_Factures_A;Réf_Factures_B;0));{"".""."".""}); FB;FILTRE(Factures_B;ESTNA(EQUIV(Réf_Factures_B;Réf_Factures_A;0));{"".""."".""}); AA;FILTRE(Avoirs_A;ESTNA(EQUIV(Réf_Avoirs_A;Réf_Avoirs_B;0));{"".""."".""}); AB;FILTRE(Avoirs_B;ESTNA(EQUIV(Réf_Avoirs_B;Réf_Avoirs_A;0));{"".""."".""}); DFAB;LET(A;TRIER(FILTRE(Factures_A;ESTNUM(EQUIV(Réf_Factures_A;Réf_Factures_B;0));{"".""."".""});1); B;TRIER(FILTRE(Factures_B;ESTNUM(EQUIV(Réf_Factures_B;Réf_Factures_A;0));{"".""."".""});1); C;TRIER(ASSEMB.V(FILTRE(A;CHOISIRCOLS(A;4)<>CHOISIRCOLS(B;4);{"".""."".""});FILTRE(B;CHOISIRCOLS(B;4)<>CHOISIRCOLS(A;4);{"".""."".""}))); FILTRE(C;CHOISIRCOLS(C;1)<>"";{"".""."".""})); DAAB;LET(A;TRIER(FILTRE(Avoirs_A;ESTNUM(EQUIV(Réf_Avoirs_A;Réf_Avoirs_B;0));{"".""."".""});1); B;TRIER(FILTRE(Avoirs_B;ESTNUM(EQUIV(Réf_Avoirs_B;Réf_Avoirs_A;0));{"".""."".""});1); C;TRIER(ASSEMB.V(FILTRE(A;CHOISIRCOLS(A;4)<>CHOISIRCOLS(B;4);{"".""."".""});FILTRE(B;CHOISIRCOLS(B;4)<>CHOISIRCOLS(A;4);{"".""."".""}))); FILTRE(C;CHOISIRCOLS(C;1)<>"";{"".""."".""})); ASSEMB.V(ASSEMB.H("Factures DATA-A absentes de DATA_B";{"".""};SOMME(CHOISIRCOLS(FA;4)));FA; ASSEMB.H("Factures DATA-B absentes de DATA_A";{"".""};SOMME(CHOISIRCOLS(FB;4)));FB; ASSEMB.H("Avoirs et retours DATA-A absents de DATA_B";{"".""};SOMME(CHOISIRCOLS(AA;4)));AA; ASSEMB.H("Avoirs et retours DATA-B absents de DATA_A";{"".""};SOMME(CHOISIRCOLS(AB;4)));AB; ASSEMB.H("Factures DATA-A différentes de DATA_B";{"".""};SI(LIGNES(DFAB)>=2;SOMME(CHOISIRCOLS(DFAB;4)*EST.IMPAIR(SEQUENCE(LIGNES(DFAB))))-SOMME(CHOISIRCOLS(DFAB;4)*EST.PAIR(SEQUENCE(LIGNES(DFAB))));0));DFAB; ASSEMB.H("Avoirs et retours DATA-A différents de DATA_B";{"".""};SI(LIGNES(DAAB)>=2;SOMME(CHOISIRCOLS(DAAB;4)*EST.IMPAIR(SEQUENCE(LIGNES(DAAB))))-SOMME(CHOISIRCOLS(DAAB;4)*EST.PAIR(SEQUENCE(LIGNES(DAAB))));0));DAAB))

Fais des essais avec d'autres jeux de données pour vérifier.
Pas besoin de macro pour importer les nouveaux Jeux ?

Voir Pièce jointe
À bientôt

Bonjour AtTheOne, le Forum,
Merci beaucoup, je te fais un retour après différents tests.
Trop fort....
Merci le forum
Germaine

Germaine · 1 Avril 2025

AtTheOne à dit:
Bon, je crois avoir régler les problèmes
Les noms définis deviennent :

Réf_Factures_A =FILTRE(TS_DATA_A[N° pièce];(GAUCHE(TS_DATA_A[N° pièce];1)="F")*(GAUCHE(TS_DATA_A[N° pièce];2)<>"FR");"")
Réf_Avoirs_A =FILTRE(TS_DATA_A[N° pièce];(GAUCHE(TS_DATA_A[N° pièce];2)="AV")+(GAUCHE(TS_DATA_A[N° pièce];2)="FR");"")
Factures_A =CHOISIRCOLS(FILTRE(TS_DATA_A;ESTNUM(EQUIV(TS_DATA_A[N° pièce];Réf_Factures_A;0));{"".""."".""});2;3;5;6)
Avoirs_A =CHOISIRCOLS(FILTRE(TS_DATA_A;ESTNUM(EQUIV(TS_DATA_A[N° pièce];Réf_Avoirs_A;0));{"".""."".""});2;3;5;6)
Réf_B =SUPPRESPACE(DROITE(TS_DATA_B[Libellé écriture];9))
Réf_Factures_B =FILTRE(Réf_B;(GAUCHE(Réf_B;1)="F")*(GAUCHE(Réf_B;2)<>"FR");"")
Réf_Avoirs_B =FILTRE(Réf_B;(GAUCHE(Réf_B;2)="AV")+(GAUCHE(Réf_B;2)="FR");"")
Factures_B =ASSEMB.H(Réf_Factures_B;CHOISIRCOLS(FILTRE(TS_DATA_B;ESTNUM(EQUIV(Réf_B;Réf_Factures_B;0));{"".""."".""});1;3;5))
Avoirs_B =ASSEMB.H(Réf_Avoirs_B;CHOISIRCOLS(FILTRE(TS_DATA_B;ESTNUM(EQUIV(Réf_B;Réf_Avoirs_B;0));{"".""."".""});1;3;4))

la formule devient :

VB:

=LET(FA;FILTRE(Factures_A;ESTNA(EQUIV(Réf_Factures_A;Réf_Factures_B;0));{"".""."".""}); FB;FILTRE(Factures_B;ESTNA(EQUIV(Réf_Factures_B;Réf_Factures_A;0));{"".""."".""}); AA;FILTRE(Avoirs_A;ESTNA(EQUIV(Réf_Avoirs_A;Réf_Avoirs_B;0));{"".""."".""}); AB;FILTRE(Avoirs_B;ESTNA(EQUIV(Réf_Avoirs_B;Réf_Avoirs_A;0));{"".""."".""}); DFAB;LET(A;TRIER(FILTRE(Factures_A;ESTNUM(EQUIV(Réf_Factures_A;Réf_Factures_B;0));{"".""."".""});1); B;TRIER(FILTRE(Factures_B;ESTNUM(EQUIV(Réf_Factures_B;Réf_Factures_A;0));{"".""."".""});1); C;TRIER(ASSEMB.V(FILTRE(A;CHOISIRCOLS(A;4)<>CHOISIRCOLS(B;4);{"".""."".""});FILTRE(B;CHOISIRCOLS(B;4)<>CHOISIRCOLS(A;4);{"".""."".""}))); FILTRE(C;CHOISIRCOLS(C;1)<>"";{"".""."".""})); DAAB;LET(A;TRIER(FILTRE(Avoirs_A;ESTNUM(EQUIV(Réf_Avoirs_A;Réf_Avoirs_B;0));{"".""."".""});1); B;TRIER(FILTRE(Avoirs_B;ESTNUM(EQUIV(Réf_Avoirs_B;Réf_Avoirs_A;0));{"".""."".""});1); C;TRIER(ASSEMB.V(FILTRE(A;CHOISIRCOLS(A;4)<>CHOISIRCOLS(B;4);{"".""."".""});FILTRE(B;CHOISIRCOLS(B;4)<>CHOISIRCOLS(A;4);{"".""."".""}))); FILTRE(C;CHOISIRCOLS(C;1)<>"";{"".""."".""})); ASSEMB.V(ASSEMB.H("Factures DATA-A absentes de DATA_B";{"".""};SOMME(CHOISIRCOLS(FA;4)));FA; ASSEMB.H("Factures DATA-B absentes de DATA_A";{"".""};SOMME(CHOISIRCOLS(FB;4)));FB; ASSEMB.H("Avoirs et retours DATA-A absents de DATA_B";{"".""};SOMME(CHOISIRCOLS(AA;4)));AA; ASSEMB.H("Avoirs et retours DATA-B absents de DATA_A";{"".""};SOMME(CHOISIRCOLS(AB;4)));AB; ASSEMB.H("Factures DATA-A différentes de DATA_B";{"".""};SI(LIGNES(DFAB)>=2;SOMME(CHOISIRCOLS(DFAB;4)*EST.IMPAIR(SEQUENCE(LIGNES(DFAB))))-SOMME(CHOISIRCOLS(DFAB;4)*EST.PAIR(SEQUENCE(LIGNES(DFAB))));0));DFAB; ASSEMB.H("Avoirs et retours DATA-A différents de DATA_B";{"".""};SI(LIGNES(DAAB)>=2;SOMME(CHOISIRCOLS(DAAB;4)*EST.IMPAIR(SEQUENCE(LIGNES(DAAB))))-SOMME(CHOISIRCOLS(DAAB;4)*EST.PAIR(SEQUENCE(LIGNES(DAAB))));0));DAAB))

Fais des essais avec d'autres jeux de données pour vérifier.
Pas besoin de macro pour importer les nouveaux Jeux ?

Voir Pièce jointe
À bientôt

Bonjour AtTheOne, le forum,

Le fichier fonctionne vraiment très bien.

J’ai rencontré une difficulté qui provenait des doublons de facture du type ayant un même numéro:

Dans ce cas les calculs ne s’exécutent pas, c’est cohérent.

Cependant, lister les doublons trouvés, ça serait aussi intéressant.
Je ne sais pas si vous pouvez porter une réflexion de comment lister les doublons afin d'évaluer l'impact sur les ventes.
C'est déjà formidable pour le partage AtTheOne
Encore merci AtTheOne
Germaine

Microsoft 365 Trouver et calculer écarts ventes provenant de 2 systèmes

Germaine

XLDnaute Occasionnel

Pièces jointes

Jean-Eric

Germaine

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

Germaine

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

AtTheOne

XLDnaute Accro

AtTheOne

XLDnaute Accro

Germaine

XLDnaute Occasionnel

Pièces jointes

AtTheOne

XLDnaute Accro

Germaine

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

AtTheOne

XLDnaute Accro

Germaine

XLDnaute Occasionnel

AtTheOne

XLDnaute Accro

Pièces jointes

Germaine

XLDnaute Occasionnel

Germaine

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Microsoft 365 Trouver et calculer écarts ventes provenant de 2 systèmes

XLDnaute Occasionnel

Pièces jointes

XLDnaute Occasionnel

XLDnaute Accro

XLDnaute Occasionnel

XLDnaute Accro

XLDnaute Accro

XLDnaute Accro

XLDnaute Occasionnel

Pièces jointes

XLDnaute Accro

XLDnaute Occasionnel

XLDnaute Accro

XLDnaute Accro

XLDnaute Occasionnel

XLDnaute Accro

Pièces jointes

XLDnaute Occasionnel

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Privacy & Transparency

Privacy & Transparency