XL 2013 Chercher une valeur approchante avec tolérance (vers le haut ou le bas) dans un tableau strucuré

[Leguyl]

Bonjour à tou(te)s,

J'importe, dans un tableau structuré, les données utiles provenant de l'export xls brut d'un échéancier clients.

Dans le tableau, les colonnes 95% | 90% | 7,50% | 5% | 3,75% et 2,50% sont des pourcentages de la colonne Vente et correspondent à des tranches de paiements.

Le souci est que le fichier source n'indique pas à quoi correspondent exactement les paiements reçus. Je recherche dans les colonnes Cell. x%, une valeur correspondante qui m'indique à quelle cellule me référer dans la colonne Crédit,

Par ex, pour la colonne Cell. 95%, j'utilise la formule suivante : =SI([Client]<>[95bis];"";SIERREUR(SI([Réf.]="VEN";"S"&EQUIV(-[95%];[Crédit];0)+7;"");""))

Mon problème est que les paiements effectués ne sont pas toujours exactement de 95, 90, ou 7.50% etc. Parfois il y a des différences de quelques cents ou €uros de moins ou de plus.

J'aimerais donc savoir comment trouver la valeur approchante (qu'elle soit légèrement supérieure ou inférieure), si la valeur exacte n'existe pas.

Pas sûr que ce soit clair comme ça, je joins donc un fichier.

D'avance, mille mercis à qui voudra m'aider.


Bonne journée,
Leguyl

 

[Laurent78]

Bonjour,
Si j'ai bien compris, il s'agit de faire une sorte de lettrage, c'est à dire rapprocher la colonne [crédit] avec l'une des colonnes [95%] [90%] [7,5%] etc ....
Ces colonnes [95%] [90%] [7,5%] etc ne sont juste là que pour calculer le pourcentage de la colonne vente.

Est-ce bien cela ?

Je pense que le plus simple serait de faire une recherche sur des valeurs arrondies, par exemple en dizaine d'euros, cela permettrait d'avoir une "tolérance". Quelle fourchette souhaitez vous ?
Toutefois, que faire si plusieurs règlements on la même valeur ?
Cdlt
Laurent

 

[Leguyl]

Bonjour Laurent,

Oui, c'est bien ça, les colonnes 95 et 90% représente le gros du paiement effectué par le client et les 5 ou 10% restants sont des retenues payées en plusieurs étapes, après approbation de tel ou tel aspect d'un chantier.

J'avais déjà tenté le coup en arrondissant les valeurs de colonnes x% et Crédit pour trouver plus de correspondances mais ce n'était pas efficace à 100% et la direction veut voir les chiffres exacts dans le tableau.

Si plusieurs règlements ont la même valeur, par ex un de 90% et deux de 5%, cela veut dire que la totalité des échéances d'une facture est payée et elle retirée de l'échéancier source. Elle ne se retrouve donc pas dans mon template.

La tolérance pourrait être d'environ 0.5%, à adapter au besoin, mais je ne sais pas si c'est faisable simplement.

Merci de vous intéresser à mon cas, je me triture les quelques neurones qui me restent depuis ce midi, sans trouver la solution.

Bonne soirée,
Leguyl

 

[Laurent78]

Bonsoir,
Cela peut être énorme 0.5% d'écart (cela fait du 50/10000 et si en plus on considère + ou - 0.5% cela fait une belle marge.
Ne pourrait-on pas s'appuyer sur un montant ?

 

[Leguyl]

Re,

Oups en effet, je relis mes notes et c'est plutôt du 0.05%, voir 0.025% que le boss a évoqué Ce qui donne 5 ou 2.5€ pour 10000 € mais seulement 25 ou 12.5 cents pour 500 €.

Au final la totalité d'une facture est presque toujours payée au cent près mais les clients font parfois des "erreurs" qu'ils corrigent (ou pas) au paiement suivant. D'autres, pour je ne sais quelle raison, arrondissent toujours les premiers paiements et, au dernier, paient le reste de ce qu'ils doivent pour arriver au bon montant final.

S'appuyer sur un montant ? Peut-être mais combien ? Telle est la question. Je me demande ce qui serait le mieux au final.

Bonne fin de soirée

 

[Leguyl]

Bonjour à tou(te)s,

Le boss m'a confirmé qu'il préfère une tolérance de 0.05% mais je coince toujours sur la manière de l'appliquer.

Quelqu'un pour me mettre sur la voie ?

Bonne journée,
Leguyl

 

[JHA]

Bonjour à tous,

Un début de piste un peu alambiqué pour la colonne Cell. 95%, formule matricielle

=SIERREUR(SI(NB(SI((ABS([Crédit])>=([@[95%]]*(1-$T$5)))*(ABS([Crédit])<=([@[95%]]*(1+$T$5)));[Crédit]))>0;"S"&EQUIV(ARRONDI([@[95%]];0);ABS(ARRONDI([Plus 90];0));0)+7;"");"Il y a une valeur approchante différente de l'arrondi")

Il y a certainement mieux mais sous excel 2013 pas trouvé.

JHA

 

[Leguyl]

Bonjour JHA,

Merci, c'est presque ça mais... ça ne marche que si un paiement se trouve dans la colonne Plus 90, or certains paiements sont aussi effectués dans les 30, 60 ou 90 jours.

En tout cas, c'est déjà une belle avancée pour moi.

Bonne journée,
Leguyl

 

[Leguyl]

J'ai modifié la formule des colonnes Cell. x% pour qu'elle fonctionne quelque soit le délai dans lequel le payement a été effectué. Par ex, pour la colonne Cell. 95% :

=SIERREUR(SI(NB(SI((ABS([Crédit])>=([@[95%]]*(1-Tolérance)))*(ABS([Crédit])<=([@[95%]]*(1+Tolérance)));[Crédit]))>0;"S"&EQUIV(ARRONDI([@[95%]];0);ABS(ARRONDI([Crédit];0));0)+7;"");"")

Le souci est, maintenant, que je ne peux plus définir comme critère que si les valeurs de Client et 95bis sont différentes, rien ne doit être affiché dans la cellule.

Par ex, cette formule (matricielle) ne donne aucun résultat :

=SI([Client]<>[95bis];"";SIERREUR(SI(NB(SI((ABS([Crédit])>=([@[95%]]*(1-Tolérance)))*(ABS([Crédit])<=([@[95%]]*(1+Tolérance)));[Crédit]))>0;"S"&EQUIV(ARRONDI([@[95%]];0);ABS(ARRONDI([Crédit];0));0)+7;"");""))

Y a-t-il une autre solution ?

Bonne fin de journée,
Leguyl

 

[JHA]

Bonjour à tous,

Je suppose que tu as nommé la cellule avec le pourcentage "Tolérance"

A essayer

=SI([@Client]<>[@95bis];"";SIERREUR(SI(NB(SI((ABS([Crédit])>=([@[95%]]*(1-Tolérance)))*(ABS([Crédit])<=([@[95%]]*(1+Tolérance)));[Crédit]))>0;"S"&EQUIV(ARRONDI([@[95%]];0);ABS(ARRONDI([Crédit];0));0)+7;"");""))

JHA

 

[Leguyl]

Re,

Merci encore JHA, ça fonctionne nickel

J'étais pourtant sûr d'avoir déjà tenté cette formule mais il faut croire que j'ai manqué quelque chose.

 

[Leguyl]

Bonjour à tou(te)s,

J'ai parlé, ou plutôt écrit trop vite, il y a parfois des ratés.

Dans le fichier de travail où tous les calculs ont lieu dans un unique tableau, j'ai pu vérifier que ça ne me marche pas comme je l'espérais avec la dernière formule.

Dans ce tableau, je ne peux plus définir comme critère Client <> de 95bis sans provoquer des erreurs de réf circulaires.

Résultat, la formule me trouve un n° de ligne en rapport avec un paiement effectué par un autre client.

Je vous joins un fichier exemple.

Si l'un d'entre-vous à une astuce, je suis preneur.

Merci d'avance,
Leguyl

 

[JHA]

Bonjour à tous,

A tout hasard, à essayer

=SIERREUR(SI(NB(SI((ABS([Crédit])>=([@[95%]]*(1-Tolérance)))*(ABS([Crédit])<=([@[95%]]*(1+Tolérance)))[COLOR=rgb(226, 80, 65)]*([Client]=[@Client])[/COLOR];[Crédit]))>0;EQUIV(ARRONDI([@[95%]];0);ABS(ARRONDI([COLOR=rgb(226, 80, 65)]$S8:$S$473;0));0)+LIGNE()-1[/COLOR];"");"")

JHA

 

[Leguyl]

Bonne nuit ou bonjour JHA

Je te remercie pour ton aide précieuse, cette fois ça semble marcher exactement comme j'en ai besoin.

Ne me reste qu'à décortiquer ta formule pour la comprendre totalement et ne pas mourir idiot... enfin un peu moins que le jour précédent.

@ une prochaine au détour d'un post,
Leguyl

 

[Leguyl]

Re,

Oups il reste un dernier petit souci. Lorsque j'importe les données d'un nouvel échéancier, le nombre de lignes peut changer. Parfois, il peut contenir 600 lignes, parfois 1500. Par ex, dans mon fichier de travail, la dernière ligne du tableau est la 1005 et non plus la 473. J'ai donc modifié la formule en conséquence. Par ex, dans la première cellule de la colonne Ligne 95% :

=SIERREUR(SI(NB(SI((ABS([Crédit])>=([@[95%]]*(1-Tolérance)))*(ABS([Crédit])<=([@[95%]]*(1+Tolérance)))*([Client]=[@Client]);[Crédit]))>0;EQUIV(ARRONDI([@[95%]];0);ABS(ARRONDI($S8:$S$1005;0));0)+LIGNE()-1;"");"")

Je dois donc penser à modifier le n° de dernière ligne dans la formule dans les 6 colonnes Ligne x% après chaque importation.