Recherche de données via addition ou soustraction
Cedric
-
eriiic Messages postés 25847 Date d'inscription Statut Contributeur Dernière intervention -
eriiic Messages postés 25847 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
Je suis comptable et mon travail est de d'assembler les factures et les paiements.
Voici un exemple :
Paiement A / 10.-
Paiement B / 19.-
Facture X / 7.-
Facture Y / 20.-
Facture Z / 3.-
Note de credit E / -1.-
Dans ce cas, mon travail est de d'assembler le paiement "B" à la facture "Y" et la note de Credit "E" pour arriver à 0.-.
Ainsi que le paiement "A" à la facture "X" et la facture "Z" ce qui donne également 0.-
Et ceci sur des milliers et des milliers de lignes... Existe-il une formule qui chercher ces concordances elle-même s'il vous plait ?
Merci d'avance et bonne journée,
Je suis comptable et mon travail est de d'assembler les factures et les paiements.
Voici un exemple :
Paiement A / 10.-
Paiement B / 19.-
Facture X / 7.-
Facture Y / 20.-
Facture Z / 3.-
Note de credit E / -1.-
Dans ce cas, mon travail est de d'assembler le paiement "B" à la facture "Y" et la note de Credit "E" pour arriver à 0.-.
Ainsi que le paiement "A" à la facture "X" et la facture "Z" ce qui donne également 0.-
Et ceci sur des milliers et des milliers de lignes... Existe-il une formule qui chercher ces concordances elle-même s'il vous plait ?
Merci d'avance et bonne journée,
A voir également:
- Recherche de données via addition ou soustraction
- Fuite données maif - Guide
- Recherche automatique des chaînes ne fonctionne pas - Guide
- Rechercher ou entrer l'adresse mm - recherche google - Guide
- Formule addition excel - Guide
- Supprimer les données de navigation - Guide
4 réponses
Bonsoir,
pour y arriver, qu'est ce qui permet de savoir que le paiement B est lié à la facture Y ainsi que la note de crédit ?
Quel est l'élément commun ?
pour y arriver, qu'est ce qui permet de savoir que le paiement B est lié à la facture Y ainsi que la note de crédit ?
Quel est l'élément commun ?
Bonsoir à tous,
@cedric : Qu'est-ce qui te fait choisir "clairement" tes regroupements?
tu aurais pu prendre :
ou encore, en allant du côté des combinaisons d'au moins 30 termes :
Excel peut te sortir, si tu as quelques heures devant toi, des dizaines de possibilités de rapprochements (parmi lesquels tu retrouves tes "oranges-bleues").
Je répète donc la question de melanie1324 :qu'est-ce qui fait que tu as choisi tes rapprochements?
Une histoire de date? de libellés? de nombre de termes rapprochés?
Si tu t'intéresses à une solution ayant le plus grand (ou le plus petit) nombre de termes, et si tu restes dans les limites du solveur d'excel au niveau nombre de variables, tu peux obtenir en un peu moins d'une minute 2 solutions ayant 35 et 2 termes.
Si tu cherches une solution avec un nombre donné de termes, le solveur est beaucoup moins ton ami (ça tourne depuis 30 mn chez moi pour 33 termes).
Remarque : la somme de tes valeurs, mêmes arrondies à 2 décimales, laisse un résidu de l'ordre du milliardième.
Pour pallier ce souci, passe aux unités en pré-multipliant par 100.
cordialement
@cedric : Qu'est-ce qui te fait choisir "clairement" tes regroupements?
tu aurais pu prendre :
(2 termes)
: 2074 -2074
(6 termes)
: -2074 2014,88 444 -8,88 -70 -306
(7 termes)
: -41864 -294 2074 2844,36 36865,64 444 -70
(7 termes)
: -13556,98 -337,12 11940,32 2074 255,78 -70 -306
(8 termes)
: -337,12 -106 -168 2351,04 652,68 444 -2766,6 -70
(8 termes)
: 65069,96 -161875,62 -2074 7128,96 99883,86 -9229,84 652,68 444
(8 termes)
: 65069,96 -161875,62 99883,86 444 -75,05 -2766,6 -374,55 -306
(9 termes)
: -20352 -168 -294 11940,32 -2074 13644,83 444 -2766,6 -374,55
(9 termes)
: -20352 -539 -166 2351,04 13644,83 2014,88 444 2677,3 -75,05
(9 termes)
: -41864 -13556,98 -539 -383 2074 7128,96 36865,64 9830,38 444
(9 termes)
: -41864 -106 2351,04 2074 -9229,84 36865,64 255,78 9830,38 -177
(9 termes)
: -41864 -294 2074 29966,68 36865,64 9830,38 -36331,7 -70 -177
(9 termes)
: -13556,98 -337,12 -539 2074 7128,96 5526,93 2844,36 -2766,6 -374,55
(9 termes)
: -13556,98 -106 -539 -383 11940,32 255,78 2014,88 444 -70
(9 termes)
: -13556,98 -539 7128,96 13644,83 -9229,84 255,78 2677,3 -75,05 -306
(9 termes)
: -13556,98 -2074 13644,83 2014,88 444 -8,88 2677,3 -2766,6 -374,55
(9 termes)
: -106 -383 -39000,96 2074 -9229,84 36865,64 255,78 9830,38 -306
(9 termes)
: -106 36865,64 255,78 2014,88 444 -2766,6 -36331,7 -70 -306
(9 termes)
: -168 -166 -39000,96 2351,04 13644,83 29966,68 -9229,84 2677,3 -75,05
(10 termes)
: -20352 -41864 -168 11940,32 7128,96 13644,83 29966,68 2844,36 -2766,6 -374,55
ou encore, en allant du côté des combinaisons d'au moins 30 termes :
(30 termes)
: -20352 -41864 -13556,98 -337,12 -106 -168 -294 -539 -166 -383 65069,96 -161875,62 11940,32 2074 -2074 99883,86 13644,83 -9229,84 5526,93 2844,36 36865,64 255,78 652,68 9830,38 444 -8,88 2677,3 -75,05 -374,55 -306
(30 termes)
: -20352 -41864 -13556,98 -337,12 -106 -168 -294 -383 65069,96 -161875,62 11940,32 2351,04 2074 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 9830,38 444 -8,88 -2766,6 -36331,7 -70 -177 -306
(30 termes)
: -20352 -41864 -13556,98 -337,12 -106 -539 -166 -383 65069,96 -161875,62 11940,32 2351,04 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 9830,38 444 -8,88 -75,05 -374,55 -36331,7 -70 -177 -306
(30 termes)
: -20352 -41864 -13556,98 -106 -168 -294 -539 -166 65069,96 -39000,96 -161875,62 11940,32 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 2014,88 9830,38 444 -8,88 2677,3 -75,05 -374,55 -177 -306
(30 termes)
: -20352 -41864 -13556,98 -106 -168 -294 -166 -383 65069,96 -161875,62 11940,32 2351,04 2074 99883,86 13644,83 29966,68 2844,36 36865,64 255,78 652,68 9830,38 444 -8,88 2677,3 -75,05 -2766,6 -36331,7 -70 -177 -306
(30 termes)
: -20352 -41864 -13556,98 -106 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 2014,88 652,68 9830,38 2677,3 -75,05 -2766,6 -374,55 -70
(30 termes)
: -20352 -41864 -337,12 -106 -168 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 7128,96 99883,86 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 2014,88 652,68 9830,38 444 -8,88 -75,05 -70 -306
(30 termes)
: -41864 -337,12 -168 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 2074 -2074 7128,96 99883,86 13644,83 29966,68 5526,93 36865,64 2014,88 9830,38 444 -8,88 -75,05 -2766,6 -374,55 -36331,7 -177 -306
(31 termes)
: -20352 -337,12 -106 -168 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 2074 -2074 7128,96 99883,86 29966,68 -9229,84 2844,36 36865,64 255,78 2014,88 652,68 9830,38 444 2677,3 -2766,6 -36331,7 -70 -306
(31 termes)
: -337,12 -106 -168 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 2074 -2074 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 255,78 652,68 9830,38 444 -8,88 2677,3 -75,05 -374,55 -36331,7 -177 -306
(32 termes)
: -20352 -41864 -13556,98 -337,12 -106 -539 -166 -383 65069,96 -161875,62 11940,32 2351,04 2074 -2074 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 9830,38 444 -8,88 -75,05 -374,55 -36331,7 -70 -177 -306
(32 termes)
: -20352 -41864 -13556,98 -106 -168 -294 -539 -166 65069,96 -39000,96 -161875,62 11940,32 2074 -2074 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 2014,88 9830,38 444 -8,88 2677,3 -75,05 -374,55 -177 -306
(32 termes)
: -20352 -41864 -13556,98 -106 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 2074 -2074 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 2014,88 652,68 9830,38 2677,3 -75,05 -2766,6 -374,55 -70
(32 termes)
: -20352 -41864 -337,12 -106 -168 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 2074 -2074 7128,96 99883,86 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 2014,88 652,68 9830,38 444 -8,88 -75,05 -70 -306
(33 termes)
: -20352 -41864 -13556,98 -337,12 -106 -168 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 2014,88 652,68 9830,38 444 -8,88 2677,3 -75,05 -2766,6 -374,55
(35 termes)
: -20352 -41864 -13556,98 -337,12 -106 -168 -294 -539 -166 -383 65069,96 -39000,96 -161875,62 11940,32 2351,04 2074 -2074 7128,96 99883,86 13644,83 29966,68 -9229,84 5526,93 2844,36 36865,64 255,78 2014,88 652,68 9830,38 444 -8,88 2677,3 -75,05 -2766,6 -374,55
Excel peut te sortir, si tu as quelques heures devant toi, des dizaines de possibilités de rapprochements (parmi lesquels tu retrouves tes "oranges-bleues").
Je répète donc la question de melanie1324 :qu'est-ce qui fait que tu as choisi tes rapprochements?
Une histoire de date? de libellés? de nombre de termes rapprochés?
Si tu t'intéresses à une solution ayant le plus grand (ou le plus petit) nombre de termes, et si tu restes dans les limites du solveur d'excel au niveau nombre de variables, tu peux obtenir en un peu moins d'une minute 2 solutions ayant 35 et 2 termes.
Si tu cherches une solution avec un nombre donné de termes, le solveur est beaucoup moins ton ami (ça tourne depuis 30 mn chez moi pour 33 termes).
Remarque : la somme de tes valeurs, mêmes arrondies à 2 décimales, laisse un résidu de l'ordre du milliardième.
Pour pallier ce souci, passe aux unités en pré-multipliant par 100.
cordialement
Bonjour JvDo,
Ce que je recherche est exactement ce que tu viens de coller dans ta réponse (avec le moins de termes possible) ! Merci.
J'ai pu "activé" le solveur dans mon excel, cependant après une heure de recherche sur google je n'arrive pas a trouver comment le faire marcher... pourrais tu m'envoyer un autre indice ou une marche à suivre s'il te plait ?
En te remerciant et ne m'excusant de n'avoir pas trouver tout seul...
Bonne journée,
Ce que je recherche est exactement ce que tu viens de coller dans ta réponse (avec le moins de termes possible) ! Merci.
J'ai pu "activé" le solveur dans mon excel, cependant après une heure de recherche sur google je n'arrive pas a trouver comment le faire marcher... pourrais tu m'envoyer un autre indice ou une marche à suivre s'il te plait ?
En te remerciant et ne m'excusant de n'avoir pas trouver tout seul...
Bonne journée,
Bonsoir,
voici ton fichier avec :
1) une partie solveur pour trouver les min-max en nombre de termes réconciliables et
2) une partie macro légèrement adaptée de celle d'eriiic que gbinforme a cité dans son post #6.
J'étais parti initialement de la réalisation de Thierry Pourtier (en 2003 je crois) mais le PC qui la contient tourne depuis 10 heures pour identifier les rapprochements de termes sur les 39 mouvements de ton exemple.
Il en est rendu à 14 termes sur 39 mouvements ..... Il aura sans doute fini dans un an.
Tu comprendras qu'il n'est pas envisageable de rechercher les rapprochements sur plusieurs milliers de lignes au delà d'une profondeur (nbre de termes rapprochés) assez faible.
Tu pourras d'ailleurs vérifier ce que donne la macro sur ces milliers de lignes avec des profondeurs de 2 puis 3 puis .... pour voir jusqu'où tu acceptes d'attendre.
Une précision cependant : le délai d'identification des rapprochements de termes en fonction de la profondeur se comporte un peu comme les coeff du binôme de newton, ie avec une symétrie.
Ainsi, les rapprochements de 2 à 10 termes devraient demander le même temps de traitement que ceux de 30 à 39 termes.
Mes souvenirs du temps où j'avais des collègues comptables, m'amènent à penser qu'il serait utile de ne garder que la combinaison maximale de termes rapprochés pour isoler les montants non justifiés et y concentrer son attention.
Si le raisonnement est bon, la solution via le solveur d'excel va rapidement s'écrouler (le nombre de variables du solveur est limité à 200). Donc pour quelques milliers de lignes c'est rappé.
Il est néanmoins possible de passer par d'autres solveurs
Le fichier : https://www.cjoint.com/c/FDktys3Bntf
Pour la macro, il faut nommer la zone des mouvements "Montants".
Si on ne met rien dans les cellules nbTermesmini, nbTermesMaxi et nbMaxSolutions, les valeurs 2, 39 et 10 sont mises par défaut.
Pour rechercher les rapprochements de 2 à 10 termes, mettre 2, 10 et 240.
Pour rechercher les rapprochements de 30 à 39 termes, mettre 30, 39 et 240.
Pour rechercher les rapprochements de 25 termes, mettre 25, 25 et 240.
cordialement
voici ton fichier avec :
1) une partie solveur pour trouver les min-max en nombre de termes réconciliables et
2) une partie macro légèrement adaptée de celle d'eriiic que gbinforme a cité dans son post #6.
J'étais parti initialement de la réalisation de Thierry Pourtier (en 2003 je crois) mais le PC qui la contient tourne depuis 10 heures pour identifier les rapprochements de termes sur les 39 mouvements de ton exemple.
Il en est rendu à 14 termes sur 39 mouvements ..... Il aura sans doute fini dans un an.
Tu comprendras qu'il n'est pas envisageable de rechercher les rapprochements sur plusieurs milliers de lignes au delà d'une profondeur (nbre de termes rapprochés) assez faible.
Tu pourras d'ailleurs vérifier ce que donne la macro sur ces milliers de lignes avec des profondeurs de 2 puis 3 puis .... pour voir jusqu'où tu acceptes d'attendre.
Une précision cependant : le délai d'identification des rapprochements de termes en fonction de la profondeur se comporte un peu comme les coeff du binôme de newton, ie avec une symétrie.
Ainsi, les rapprochements de 2 à 10 termes devraient demander le même temps de traitement que ceux de 30 à 39 termes.
Mes souvenirs du temps où j'avais des collègues comptables, m'amènent à penser qu'il serait utile de ne garder que la combinaison maximale de termes rapprochés pour isoler les montants non justifiés et y concentrer son attention.
Si le raisonnement est bon, la solution via le solveur d'excel va rapidement s'écrouler (le nombre de variables du solveur est limité à 200). Donc pour quelques milliers de lignes c'est rappé.
Il est néanmoins possible de passer par d'autres solveurs
Le fichier : https://www.cjoint.com/c/FDktys3Bntf
Pour la macro, il faut nommer la zone des mouvements "Montants".
Si on ne met rien dans les cellules nbTermesmini, nbTermesMaxi et nbMaxSolutions, les valeurs 2, 39 et 10 sont mises par défaut.
Pour rechercher les rapprochements de 2 à 10 termes, mettre 2, 10 et 240.
Pour rechercher les rapprochements de 30 à 39 termes, mettre 30, 39 et 240.
Pour rechercher les rapprochements de 25 termes, mettre 25, 25 et 240.
cordialement
Bonjour à tous,
Une question en passant car je ne connais pas le métier :
est-ce que par hasard chaque valeur positive (ou 2 maxi,avec les lignes de crédit) est obligatoirement la somme de valeurs négatives (ou l'inverse) ?
Ce qui permettrait d'éliminer des solutions et un nombre considérable de branches.
eric
Une question en passant car je ne connais pas le métier :
est-ce que par hasard chaque valeur positive (ou 2 maxi,avec les lignes de crédit) est obligatoirement la somme de valeurs négatives (ou l'inverse) ?
Ce qui permettrait d'éliminer des solutions et un nombre considérable de branches.
eric
Si le paiement A se met avec la facture Z la somme n'est pas égale à 0.-
Voici un vrai exemple : https://www.dropbox.com/s/3j3uab6qckrb31k/Statement%20-%20Element_V2.xlsx?dl=0
On vois clairement que la facture et la note de credit "orange" vont ensemble.
De plus on voir que toutes les positions "bleues" vont ensemble.
Pour ce qui est du reste, je peux le faire manuellement,
J'espère avoir été plus claire.
Voici un vrai exemple : https://www.dropbox.com/s/3j3uab6qckrb31k/Statement%20-%20Element_V2.xlsx?dl=0
On vois clairement que la facture et la note de credit "orange" vont ensemble.
De plus on voir que toutes les positions "bleues" vont ensemble.
Pour ce qui est du reste, je peux le faire manuellement,
J'espère avoir été plus claire.
Bonjour à tous,
@cedric : tu devrais regarder la solution d'Éric qui correspond au sujet de ta demande en recherchant les solutions des combinaisons possibles.
@cedric : tu devrais regarder la solution d'Éric qui correspond au sujet de ta demande en recherchant les solutions des combinaisons possibles.
