Tableau de bord automatisé sur des impayés

[Choupardine]

Bonjour,
J'ai une question pour les pros d'Excel.
Je débute tout juste ma formation sur les formules dans Excel mais je n'ai pas encore tous les automatismes.
Vous trouverez en pièce jointe un tableau Excel sur des impayés.
Je travaille au département risques d'une banque.
Chaque semaine voire tous les 15 jours maximum, je reçois du service comptable et financier un tableau Excel sur les impayés de nos clients par zone géographique et par société.
Mon chef me demande de mettre en place un tableau pour déterminer :
- les nouveaux impayés (ceux qui ne sont pas présents la semaine d'avant et qui apparaissent la semaine d'après)
- les impayés qui perdurent
- les impayés soldés
Ensuite, à partir de ce filtre, je dois faire un TCD et un graphique pour montrer l'évolution des impayés.
J'ai déjà commencé à faire le test en juillet (onglets du 12 juillet et du 25 juillet) mais je n'arrive pas à trouver la formule pour repérer les nouveaux impayés d'une semaine à une autre.
Je pense qu'il faut une recherche v mais je n'arrive pas à le formuler.
Peut-être auriez-vous d'autres suggestions, des astuces à me proposer pour retravailler mon tableau.
Merci pour vos retours et votre aide car je dois finaliser ce travail assez rapidement.
D'autre part, j'aurais aussi besoin de vos lumières pour mettre en place une macro (je n'ai pas encore fait la formation) pour modifier et formater les tableaux que je reçois de la compta qui sont extraits de Business Object.

 

[Tirou]

Re : Tableau de bord automatisé sur des impayés

Bonjour Choupardine,

Ci-joint un premier jet, avec des tri selon l'ancien et nouveau relevé.

Qu'en penses-tu?

Edit : Version sans les noms d'entreprise.
Par ailleurs, à la vue de tes premiers essais, je pense être hors sujet. Je m'y penche plus tard.

 

[GeoTrouvePas]

Re : Tableau de bord automatisé sur des impayés

Bonjour,

Attention à l'anonymisation de tes pièces jointes !!!!!!

Poster une liste d'entreprises en défaut de paiement sur un forum n'est peut être pas ce qu'il y a de plus judicieux.....

 

[Choupardine]

Re : Tableau de bord automatisé sur des impayés

Merci pour ta remarque je l'ai eu aussi en privé.
Je viens de reposter le fichier Excel avec des noms de société bidons.
Désolé pour cette gaffe !

 

[Choupardine]

Re : Tableau de bord automatisé sur des impayés

Merci Tirou,
J'ai regardé ton tableau et ça m'a l'air pas mal.
J'ai oublié de préciser que mon chef est surtout intéressé par les impayés situés au niveau de la colonne I, tous les défauts de paiement à moins de 2 mois.
Mais ton idée est bonne.
Je vais exploiter le filon en attendant que peut-être quelqu'un d'autre propose une autre astuce.
Merci encore.

 

[gauguin]

Re : Tableau de bord automatisé sur des impayés

Bonjour,

Avant de faire une tentative, je voudrais savoir si cette présentation conviendrait. (elle permet de réaliser facilement un graphique)

Cordialement,

 

[Choupardine]

Re : Tableau de bord automatisé sur des impayés

Bonjour Gauguin,
Je dois malheureusement faire une présentation dans le détail par le biais d'un TCD et souvent rapportée à la zone géographique et non pas au nombre d'impayés dans chaque catégorie chaque semaine.
Mais cela peut être une présentation qui peut servir d'exemple sur d'autres tableaux de bord sur lesquels je travaille.
Merci en tout cas d'avoir proposé quelque chose,
Vanessa

Bonjour,

Avant de faire une tentative, je voudrais savoir si cette présentation conviendrait. (elle permet de réaliser facilement un graphique)

Cordialement,

 

[gauguin]

Re : Tableau de bord automatisé sur des impayés

Voici pour obtenir un TCD, une autre idée qu'il est possible d'envisager et fort probablement d'automatiser avec VBA
.
I - Créer une feuille de compilation des données ou l'on accole les défauts de paiements des différentes semaines.
On place en-tête de chaque colonne "défaut xxx", le nom de la semaine concernée. (voir les en-têtes de la feuilles compilation) => feuille "compildonnées"
II - On peut ainsi, à moindre coup avec les filtres de TCD, extraire les données qui vous interessent. Attention, par contre, lorsque vous extrayez les "NEW" d'une semaine, pensez dans le filtre du TCD à "défiltrer" les semaines précédentes (en mettant sur "tous").

Pour le graphique sur plusieurs semaines, vous ne l'obtiendrez pas facilement à partir du TCD, à mon avis).
D'où l'idée, pour la partie graphique, de mon post ci-dessus (avec la capture d'écran). => feuille "TCD".
J'ai fait un essai dans le classeur ci-joint, dans la feuille "données pour graphique"
Il y a des plages nommées à mettre en place, poiur chaque feuille comprenant les 4 colonnes de données de défaut.
Pour cela faites Ctrl + F3 pour prendre exemple sur ceux déjà créés.

ps : J'ai modifié les valeurs pour avoir des NEW à fins de vérification.

 

[Tirou]

Re : Tableau de bord automatisé sur des impayés

Bonjour,

Je me heurte à la manière dont on déterminer les "NEW"

J'ai deux exemples dans le tableau, les N° TIERS
9101 Guyane Française OMR 769,55 14,86 14,86 -
503271 Afrique du Sud AFR - - 0,01 -

Ces deux tiers sont, dans l'ancien relevé :
9101 Guyane Française OMR 769,55 14,86 - 14,86
503271 Afrique du Sud AFR - - - 0,01

Pour moi, le traitement est le même pour les données à moins de 2 mois qui passent dans la rubrique 2 à 4 mois.
Néanmoins, dans le cas du 9101, dans la rubrique 2 à 4 mois, le résultat est "ADDITIONAL", tandis que pour le second, c'est du "NEW".
Pouvez-vous m'éclairer sur la différence entre ces 2 cas que je finalise ma tentative?

EDIT : Enfer et damnation ! J'en suis en fait loin de la finalisation !!!
Je n'avais pas remarqué la présence de doublons dans les N° de TIERS. La fonction RECHERCHEV si souvent utilisée ne les gères pas. Attention donc à la première solution que j'avais proposé.

Je m'y attaque de nouveau plus tard, j'ai mon propre taf sur le feu.

EDIT 2: L'évolution des impayés doit être représenté d'un relevé à l'autre ou sur tous les relevés?

 

[Choupardine]

Re : Tableau de bord automatisé sur des impayés

Bonsoir Tirou,
Et merci pour votre tableau d'hier soir qui m'a déjà très bien aidé.
Ensuite par rapport au problème que vous soulevez, il y a un souci au niveau des impayés.
Vous avez plusieurs cas de figure :
- un impayé de moins de 2 mois qui n'est toujours pas soldé et qui passe en impayé entre 2 et 4 mois (transfert de données)
- un impayé de moins de 2 mois qui perdure et des fois la somme change d'une semaine à une autre (ex: on est à 15 en semaine 1 puis à 28 en semaine 2 mais dans des cas, on peut avoir la même somme qui n'est pas payée d'une semaine à l'autre).
Dans le 1er cas, l'impayé de moins de 2 mois a disparu et dans le 2ème, on a un additif par rapport à la semaine passée.
Mon chef veut une évolution hebdo des impayés.
Si ce n'était pas clair, n'hésitez pas à m'envoyer un email en mp.
Désolé de ne répondre que maintenant, j'étais en réunion.
merci encore de vous prendre la tête pour moi !!
Ca m'aide beaucoup !

Bonjour,

Je me heurte à la manière dont on déterminer les "NEW"

J'ai deux exemples dans le tableau, les N° TIERS
9101 Guyane Française OMR 769,55 14,86 14,86 -
503271 Afrique du Sud AFR - - 0,01 -

Ces deux tiers sont, dans l'ancien relevé :
9101 Guyane Française OMR 769,55 14,86 - 14,86
503271 Afrique du Sud AFR - - - 0,01

Pour moi, le traitement est le même pour les données à moins de 2 mois qui passent dans la rubrique 2 à 4 mois.
Néanmoins, dans le cas du 9101, dans la rubrique 2 à 4 mois, le résultat est "ADDITIONAL", tandis que pour le second, c'est du "NEW".
Pouvez-vous m'éclairer sur la différence entre ces 2 cas que je finalise ma tentative?

EDIT : Enfer et damnation ! J'en suis en fait loin de la finalisation !!!
Je n'avais pas remarqué la présence de doublons dans les N° de TIERS. La fonction RECHERCHEV si souvent utilisée ne les gères pas. Attention donc à la première solution que j'avais proposé.

Je m'y attaque de nouveau plus tard, j'ai mon propre taf sur le feu.

EDIT 2: L'évolution des impayés doit être représenté d'un relevé à l'autre ou sur tous les relevés?