Lettrage automatisé avec Excel 2003

[Exceli]

Bonjour à tous,

Aujourd'hui je vous solicite car j'aimerais avoir un coup de main. Depuis plusieurs jours j'essaie de mettre la main sur une formule pour lettrer des comptes sur Excel mais je n'y arrive pas .

Quelle formule je pourrais utiliser sous Excel 2003 qui me permettrai d'effectuer un lettrage automatisé des codes Clients en m'affichant une suite de lettres comme code de lettrage (A, B, C,..., ZZZZ) pour les lignes rapprochées et vide pour les non rapprochées, sachant que pour le lettrage:
- le code Client doit etre identique et
- l'Exercice doit etre identique
La difficulté pour moi reside essentiellement sur le fait qu'une ligne ou plusieurs lignes au débit peuvent correspondre à une ligne ou plusieurs lignes au crédit sans que les montant soient pour autant égaux, et viceversa.

Je vous joint un petit fichier qui va être plus parlant, il illustre bien ma demande je pense.

Merci d'avance

 

[vgendron]

Re : Lettrage automatisé avec Excel 2003

Bonsoir,
j'ignore ce qu'est un lettrage.. mais au vu de ma compréhension, il faudrait coder l'algo suivant ?

pour chaque année colonne E
pour chaque client colonne F
faire la somme des crédits (H) moins les débits (I)
si écart=0 alors nouvelle lettre (ou lettre spécifique??)
sinon si <=2 ou >2 alors lettre B ou G
fin faire
next client
next année

en fait. tant que tu n'aura pas décrit précisément ta méthode manuelle.. on pourra coder tout ce qu'on veut. ca ne répondra pas à ton besoin

autre question: une lettre a t elle une signification particulière?

 

[Exceli]

Re : Lettrage automatisé avec Excel 2003

Merci Gardien pour votre réponse si rapide
Et avec le code client et l'année de gestion ça nemarche pas?

 

[Exceli]

Re : Lettrage automatisé avec Excel 2003

Bonsoir vgendron et merci pour votre réponse,

Le lettrage manuel que j'ai mis est à titre d'exemple, il faut juste que ça soit des lettres.
Pour mon lettrage manuel je trie par code client puis par année de gestion et cherche les montants crédit et débit identiques (solde=0) si je ne trouve rien je tatonne pour que la somme de mon crédit soit égale à la somme de mon débit. Mais je vais passer beaucoup beaucoup de temps à faire ça, j'ai plus de cent milles lignes à traiter comme ça
J'aimerais trouver une formule pour ça car en vba je suis moins que débutante, je risqe de ne rien comprendre sans explications

 

[vgendron]

Re : Lettrage automatisé avec Excel 2003

Re,
ci joint le fichier avec deux trois tableaux à côté
j'explique
j'ai fait la liste des clients que j'ai placés en ligne
la liste des années que j'ai mises en colonnes
pour chaque client et chaque année j'ai calculé les crédits (1er tableau Vert) les débits (2nd tableau rose) enfin les écarts (3eme tableau mauve)

à partir de ce dernier tableau, comment faites vous le lettrage?
pour le premier client n° 04008999: il y a 0 pour toutes les années sauf 2010: mettez vous une seule et unique lettre ou une lettre nouvelle pour chaque année?
le cas ou l'écart est à -1.2? comment le traitez vous?

 

[Exceli]

Re : Lettrage automatisé avec Excel 2003

Dans votre exemple si je prend le client n° 04008999 effectivement il n'y que des mouvements pour l'annee 2010 avec un solde=1.20, dans ce cas je met un code de lettrage A (par exemple) avec un commentaire "Ecriture de régul. à saisir".
Si je continue votre exemple pour le client n° 0403521 pour l'année 2000 l'écart est égal à zero, donc je met comme code lettrage B uniquement.
Ainsi de suite.
Je laisse vide les cellulles qui ne sont pas lettrées.

Imaginons que pour le client n° 04008999 j'ai des mouvements pour l'année 2002 sans écart, dans ce cas j'aurais mis comme code lettrage C par exemple (sachant qu'en 2010 j'ai mis A comme code lettrage).

Donc je met une nouvelle lettre pour chaque année sans écart du même client.

Merci pour votre aide vgendron

 

[vgendron]

Re : Lettrage automatisé avec Excel 2003

Bonjour,

Voir PJ updatée avec un 4eme tableau (en bleu)
Je commence à appercevoir la méthode:
Corrigez si je me trompe
Pour qu'une ligne soit "elligible" au lettrage, il faut qu'il y ait eu au moins un mvt (une opération de crédit ou de débit)
(ici pour le client 04008999, il n'y a qu'une année qui répond à cette condition: 2010)
selon la valeur de l'écart, on met une lettre
Si écart nul: on met une lettre
si écart non nul une autre lettre

y a t il une régle selon l'importance de l'écart?

Dans le tableau, il y a trois lignes avec écart nul (Mvt Soldé) ca fait donc 3 lettres
pour les mvt non soldés??

s'il faut lettrer aussi les mvt non soldés. on voit que le nombre de lettres va vite est insuffisant...
faut il faire un lettrage sur plusieurs niveaux? genre un meme client commence toujours par la meme lettre, puis on rajoutte
par exemple:
client 04030521: année 1998: AA
année 1999 AB
année 2000 AC
Année 2001 AD ??

 

[Exceli]

Re : Lettrage automatisé avec Excel 2003

Bonjour vgendron,

-Oui, Pour qu'une ligne soit éligible au lettrage, il faut qu'il y ait au moins un mvt (une opération de crédit ou de débit)
-Oui, si l’écart est nul, on met la même lettre sur toutes ces lignes
-Non, si l’écart est non nul mais qui est compris entre -2 et 2, on met la même lettre sur toutes ces lignes
-Si l’écart est non nul, il faut chercher la combinaison des lignes où la somme des crédits=somme des débits (c’est là que ça se complique).
-Si l’écart est nul et pas de combinaison possible alors laisser les lignes vides

Donc on lettre seulement si :
- l’écart est nul,
- l’écart est compris entre -2 et 2,
- l’on arrive à trouver les combinaisons (parmi les mouvements non soldés) où l’écart soit nul.

« Dans le tableau, il y a trois lignes avec écart nul (Mvt Soldé) ca fait donc 3 lettres
pour les mvt non soldés?? »

Non, ça fait trois lettres pour ces mouvements soldés.
On ne lettre pas les mouvements non soldés, seulement ceux où l’on arrive à trouver les combinaisons avec un solde nul.

Merci bcp vgendron pour aide, c'est tres sympa

 

[vgendron]

Re : Lettrage automatisé avec Excel 2003

ok pour les deux premiers cas, ca doit se faire "facilement"
pour le cas des combinaisons..

c'est quoi la règle.. s'il y en a une..
déjà j'imagine qu'on se place sur un seul et meme client, mais.. une ou plusieurs années? ou une seule référence lettrage (colonne G) ?
ensuite un débit doit etre couvert par UN crédit ou plusieurs crédits?
est ce qu'un crédit peut couvrir plusieurs débits?
je crois qu'on aborde le pb d'algo dont parlait Papou net.. ;-)

sinon je te le dis de suite.. ce ne sera pas une formule mais une macro.. mais j'ai pour habitude (enfin j'essaie) de bien commenter chaque bloc de code si bien que meme si tu ne comprends pas la commande, tu sais au moins ce qu'elle est censée faire..

 

[Exceli]

Re : Lettrage automatisé avec Excel 2003

« ok pour les deux premiers cas, ca doit se faire "facilement" »
Ok super, c’est déjà ça 

« pour le cas des combinaisons… »
je sais, même s’il va falloir que je le fasse de toute façon…à la mano 
Pour les combinaisons (concerne les lignes non soldées uniquement) la règle est de chercher:
-Toujours le même code client, ça oui
-Toujours la même année de gestion
- Le crédit – débit=0 peut être :
• Un crédit=un débit
• Un crédit=plusieurs débits
• Un débit=plusieurs crédits
• Plusieurs crédits=plusieurs crédit
Ah je pensais qu’avec une formule ça pouvait être possible, bon bienvenue la macro avec vos commentaires, j’espère être à la hauteur pour la compréhension :~
Si vous pensez que pour avoir les bons résultats il faut faire une macro, est ce qu’il serait possible d’appliquer la formule pour tout ce qui est « lettrable » dans la première partie puis faire une macro uniquement pour les combinaisons ?
La référence lettrage qui me donne le logiciel de compta je la prendrai en compte vraiment à la fin du lettrage auto pour voir si je trouve des clients à lettre, je pense que ça serai beaucoup plus simple comme ça à gérer.

 

[vgendron]

Re : Lettrage automatisé avec Excel 2003

Re,

Ci joint un début de réponse. enfin.. j'espère ;-)

ce que ca fait:
à partir des données en feuille "Data", je récupère la liste des clients associés aux années, pour en faire une table en feuille "Tempo"
dans cette feuille,
je fais les calculs intermédiaires (comme ceux des différents tableaux de ma proposition précédente post #21)
Somme des Crédits, des débits, écarts, et calcul d'eligibilité au lettrage et Le lettrage en lui meme

ensuite, je reporte le résultat du lettrage dans la feuille Data

le lettrage qui est effectué
1) si pas de mvt, alors. pas de lettre
2) En cas de mvt, écart nul--> une lettre
3) En cas de mvt; -2<=écart<=2--->une lettre
4) En cas de mvt mais écart autre (donc non soldé) doit faire l'objet d'essais combinatoires: la. je dis juste combinatoire sans aller plus loin


limitations:
pas d'essais de combinaison; pour le moment ;-)
le lettrage est limité aux 26 lettres de l'alphabet.. dans le cas de l'exemple, c'est nickel, mais dans le cas de votre fichier complet..??
il faudra sans doute augmenter le nombre de possibilités de lettrages en doublant ou triplant la lettre, ou en y associant un numéro?


Voila, pour le moment
si vous pouviez vérifier que les lettrages 1 2 et 3 sont bons.. ce serait déjà pas mal..
je regarde ce qui pourrait etre possible pour le 4...

 

[vgendron]

Re : Lettrage automatisé avec Excel 2003

Bonjour,

Ci jointe la dernière version:
elle ne fait toujours pas de combinaison, Mais.. elle fait un premier tri pour "supprimer" les cas ou la combinaison sera impossible
ca épure déjà pas mal..
à tester

 

[Exceli]

Re : Lettrage automatisé avec Excel 2003

Bonjour vgendron et un grand merci encore,

Si je comprend bien je copie ma selection sur l'onglet "Data" de votre fichier puis j'appuie sur ALT+F8 pour executer la macro.
Sauf que quand je fais ça j'ai un message d'"erreur d'execution 9 L'indice n'appartient pas à la selection" apparait. Puis il me demande si Fin ou débogage. :~

Pour doubler, tripler... les lettres comment je pourrais formuler ça dans votre macro?

 

[vgendron]

Re : Lettrage automatisé avec Excel 2003

Une fois que vous avez copié vos data, il faut cliquer d'abord sur le bouton Lettrage (qui lance la macro test)
PUIS sur le bouton Combinaison (lance la macro combinaison)

Pas besoin d'effacer le tableau dans "tempo"
d'ailleurs, il faut peut etre mieux pas..;-)

 

[vgendron]

Re : Lettrage automatisé avec Excel 2003

Re Re Re..;-)

Dernière version améliorée
effectue des lettrages simples
1) pas de Mvt: pas de lettre -->Cellule vide
2) il y a mvt (au moins un débit et un Crédit) Soldé-->Lettre
3) il y a mvt avec -2<=écart<=2--->Lettre
4) il y a mvt mais écart autre (donc non soldé) doit faire l'objet d'essais combinatoires:
version d'avant je disais juste combinatoire sans aller plus loin
maintenant je fais
a) pour les cas ou il n'y a que des crédits OU que des débits (pas de lettrage possible) --> cellule vide
b) recherche des combinaisons simples: 1 crédit = 1 débit -->une lettre
lorsqu'une combinaison simple est trouvée, je met une lettre..
je vérifie qu'une combinaison est toujours possible ou pas (il reste des Crédits ET des débits) sinon, Cellule vide

dans l'exemple fourni, le dernier cas ne se présente pas (ie: j'ai trouvé une combinaison simple, mais d'autres restent possibles)
on se retrouve donc ici avec un fichier complètement lettré..

il y a tout de meme une différence avec votre lettrage manuel: la ou vous mettez une lettre G (lignes 29 30 31) je n'ai rien, car c'est le meme client, mais pas la meme année...
Voila.. c'est tout ;-)

 

[Exceli]

Re : Lettrage automatisé avec Excel 2003

Ca ne marche pas mais je pense que cela vient du fait que j'utilise Excel 2003 et la formule "somme.si.sens" la macro ne la reconnais pas chez moi.
Sinon, la macro peut s'executer avec n'importe quel nombre de lignes?

Merci vgendron