Somme d'une plage variable (calcul DSO)
Bonjour,
Petit problème qui trotte dans la tête depuis 2 jours :
J'ai plusieurs lignes avec des montants (de chiffre d'affaire)
J'ai une cellule dans laquelle j'ai un montant (les créances clients)
Je cherche à déterminer le DSO, soit à combien de jours de ventes correspond le total des créances clients. Le tout en partant d'un mois de vente et en remontant jusqu'à apurer le total des créances clients.
En gros, pour exemple :
- Ventes par mois :
Aout 08 80
Sept 08 90
Oct 08 85
Nov 08 95
Dec 08 75
- Créances clients :
Dec 08 285
Pour le calcul final en jours, j'ai déjà la réponse, ici ce serait 100 exactement (en considérant chaque mois = 30 jours)
Ma question porte sur : comment arriver à déterminer combien de mois (donc la taille de la plage 'ventes') il faut pour arriver au total des créances clients.
PS : j'ai déjà la solution avec les 'if' imbriqués mais j'aimerais trouver qqch de moins limité/lourd
Et voici le fichier :
https://www.excel-pratique.com/~files/doc2/DSO_pour_test.xls
Bonjour,
A condition de saisir des vraies dates de A1 à A5 (août 08 à déc 08), tes montants de B1 à B5, en B7 ton montant de créances.
Essaye cette formule :
=B7/(SOMME($B$1:$B$5)/(DATE(ANNEE(A5);MOIS(A5)+1;0)-DATE(ANNEE(A1);MOIS(A1);1)))@+
Edit : je n'avais pas vu ton fichier. Donc à adapter
@+
Bonjour
https://www.excel-pratique.com/~files/doc2/zUaBUClasseur2.xls
et avec ton fichier
Corhttp://www.excel-pratique.com/~files/doc2/Y8hYsDSO_pour_test.xls
dialement
Petites clarifications :
1. je cherche à détermine si la somme de la plage est supérieure ou égale au total des créances et à partir de cette somme, déduire la taille de la plage
2. le DSO ne sera pas calculé sur la moyenne mais bien sur les mois réels. Résultat attendu = 100 comme indiqué dans le fichier.
Je joins un fichier avec 1 mois de plus, vous allez comprendre pourquoi j'ai besoin de trouver la taille de la plage variable (sans passer par les si imbriqués)
https://www.excel-pratique.com/~files/doc2/5Yd6CDSO_pour_test.xls
Amadéus a écrit :Bonjour
https://www.excel-pratique.com/~files/doc2/zUaBUClasseur2.xls
et avec ton fichier
https://www.excel-pratique.com/~files/doc2/Y8hYsDSO_pour_test.xls
Cordialement
Avec la moyenne des cellules, ca ne fonctionne pas puisqu'on peut imaginer des valeurs extrêmes (arrêts certains mois). Par exemple, si je remplace ma première valeur (aout 08)par 1, on obtient 5 pour la formule que tu donnes, or ce n'est pas le bon résultat.
Re,
Tu dis août 2008=1
Alors comment définir les mois à prendre en compte ?
Cordialement
Amadéus a écrit :Re,
Tu dis août 2008=1
Alors comment définir les mois à prendre en compte ?
Cordialement
Eh bien justement, c'est là toute la question! Combien de mois faut-il prendre pour que leur somme soit >= aux créances clients ?
Dans l'exemple ci-dessus on procède comme suit :
285-75 (déc 08 ) = 210 ----> on prend 30 jours
210-95 (nov 08 ) = 115 -----> on prend 30 jours
115-85 (oct 08 ) = 30 -----> on prend 30 jours
30-90 (sept 08 )= -60 -----> on fait au prorata du mois = 30/90 = 10 jours
Total = 100 jours
Maintenant si les créances avaient été de 90 au lieu de 285, on aurait obtenu :
90-75 (dec 08 ) = 15 --------> on prend 30 jours
15-95 (nov 08 ) = -80 --------> au prorate 15/95 = 15.8
Total = 45.8 jours
Premier cas, la plage fait 4 cellules. Second cas, elle en fait 2. Et j'ai besoin de ce chiffre pour déterminer le solde au prorata du dernier mois repris.
Bonjour à tous,
Pas sûr d'avoir bien compris mais est-ce que ça marche ça?
https://www.excel-pratique.com/~files/doc2/DSO_pour_test_V1.xls
A+
L'idée est intéressante mais inapplicable vu le nombre de périodes qui seront présentes à terme. Il faudrait rajouter trop de colonnes
Regarde le second fichier avec 2 périodes de comparaison, c'est sans doute plus compréhensible.
Re,
Dans le second fichier, est-ce que la plage fait 5 cellules?
Si oui, regarde dans le fichier suivant :
https://www.excel-pratique.com/~files/doc2/5Yd6CDSO_pour_test_V2.xls
Sinon, j'ai pas bien compris. Dans ce cas, quel chiffre faut-il trouver pour la plage variable dans ton deuxième fichier?
A+
A nouveau l'idée est bonne et on trouve bien le nombre de lignes nécessaires
Seulement voyez le second fichier que j'ai chargé, avec 2 périodes, on ne peut pas ajouter une colonne par période, ca ne tiendrait pas dans un excel
Bonjour à tous,
Speculoos, tu n'as pas répondu à mon post précédent.
Dans le second fichier, quelle est la taille de plage que l'on doit trouver?
Pour le premier fichier, tu nous as dit comment faire mais pas pour le second fichier que tu as chargé!
En attendant une réponse...
J'ai ajouté un max d'infos et de données afin que vous puissiez comprendre où je veux en venir :
https://www.excel-pratique.com/~files/doc2/S0yaaDSO_pour_test.xls
Utilisez de préférence ce fichier plutot que les autres, il est plus complet et plus explicite. J'ai indiqué la taille des plages à trouver.
Enfin pour répondre à ta question, je pensais l'avoir exprimé clairement, la taille de la plage à trouver était de 5 dans le premier cas et 2 dans le second. Voir le nouveau fichier que je viens de poster pour plus d'infos sur d'autres exemples.
Re,
Speculoos, veux-tu absolument résoudre ton problème qu'avec des formules?
Parce que je pense qu'une solution en VBA est plus adaptée.
Quoi qu'il en soit, je te propose une solution en VBA qui ne marche que si la structure est rigoureusement la même.
C'est-à-dire que :
1. les intitulés des tableaux ("Ventes ('000 Eur)" ; "Créances clients ('000 Eur)" ; "DSO :") restent les mêmes. Si tu veux les changer, tu devras le faire également dans le code de la macro.
2. s'il y a d'autres mois à ajouter, il faut que cela ne dépasse pas la limite de chaque tableau (tableau des ventes, des créances, des DSO). Exemple :
A ne pas faire :
A faire :
Il y a peut-être d'autres remarques à ajouter mais je n'en vois pas pour le moment
Encore une fois, une solution par macro est peut-être plus adpatée qu'une solution avec formule? A toi de nous dire
Voici le fichier :
https://www.excel-pratique.com/~files/doc2/S0yaaDSO_pour_test_V3.xls
Oui effectivement, une solution VBA est plus adaptée, je le sais depuis le début 8)
Sauf que :
- j'ai de base déjà beaucoup de colonnes et lignes où je devrais appliquer une telle macro (assez lourd du coup)
- je ne suis pas seul à bosser sur ce fichier et mes collègues ne comprendront pas cette macro
donc oui, c'est le côté formule qui m'intéresse
Par contre pour la structure, je prévois les espaces nécessaires de base pour les mois à venir avant d'introduire les formules/le code VBA
Salut speculoos!
Pas facile qu'avec des formules!
Mais voici une solution qui a l'air de marcher!
https://www.excel-pratique.com/~files/doc2/S0yaaDSO_pour_test_V4.xls
Dis-moi ce que t'en penses.
Encore une fois, il faut que la structure du fichier reste peu ou prou la même
A+
Ca fonctionne presque 8)
Sauf qu'il faut une colonne par taille de plage possible. Pour un DSO de 150, ca fait 5 colonnes supplémentaires alors que j'ai déjà ce résultat avec les if imbriqués (voir le fichier) en une seule cellule. Mon fichier d'origine fait déjà 90 colonnes !! Si on multiplie par 5 ca devient ingérable ou illisible (ou les 2) voire carrément inutilisable dans excel 2003 et antérieurs (limité à 256 colonnes si je ne m'abuse)
Si je pousse la formule des ifs imbriqués au maximum, je pense pouvoir arriver à 210 jours soit un range de 8 au maximum.
PS : d'ailleurs quelqu'un connaît la limite du nombre de parenthèses dans excel 2007 ? Enfin s'il y a en une...
Oula speculoos!
Ca fonctionne presque 8)
Sauf qu'il faut une colonne par taille de plage possible.
Jusque là j'ai compris.
Pour un DSO de 150, ca fait 5 colonnes supplémentaires alors que j'ai déjà ce résultat avec les if imbriqués (voir le fichier) en une seule cellule. Mon fichier d'origine fait déjà 90 colonnes !! Si on multiplie par 5 ca devient ingérable ou illisible (ou les 2) voire carrément inutilisable dans excel 2003 et antérieurs (limité à 256 colonnes si je ne m'abuse)
Si je pousse la formule des ifs imbriqués au maximum, je pense pouvoir arriver à 210 jours soit un range de 8 au maximum.
J'ai rien compris!
Peux-tu par hasard illustrer par une copie d'écran?
PS : d'ailleurs quelqu'un connaît la limite du nombre de parenthèses dans excel 2007 ? Enfin s'il y a en une...
Tu parles peut-être du nombre maximal de fonctions imbriquées?
Si oui, pour excel 2007, il est de 64 (excel 2003 : 7
Sinon, pour connaître les autres limites d'excel 2007, dans l'aide, tape limite
A+
[color=blue]Edit : il faut que tu sois un peu plus clair et plus précis speculoos!
Où veux-tu mettre la taille de la plage? Quelle cellule? A quoi servent toutes ces 90 colonnes? Quelles données y a-t-il dedans?
Au risque de me répéter, il est primordial de connaître la structure du fichier pour trouver une solution adéquate!