Somme de variables dans un intervalle de dates TCD ? VBA ?

[Sylvie]

Bonjour Cher Forum,

Je me heurte au problème suivant.
Mon fichier (qui se compose en réalité de près de 10000 lignes) est un export excel d'un logiciel commercial.
Toutes les lignes correspondent à des ventes d'abonnements et ont donc :
- Une date de début : colonne D
- Un date de fin : Colonne E
Chaque ligne correspond à un article colonne G, un numéro de commande colonne I et comprend en colonne Q le montant du Chiffre d'Affaires (CA) Total pour la période définie entre les dates de début et date de fin (Colonne D et E)

Je souhaite pouvoir sur une 2eme feuille soit via un TCD si cela est possible soit via une macro :

- Pouvoir déterminer le CA compris entre deux dates que je vais renseigner et qui vont donc être amenés à varier (dATE DEBUT ANALYSE et DATE FIN ANALYSE)

- Les abonnements déjà finis à ma DATE DEBUT ANALYSE ne génèrent aucun Chiffre d'affaires (exemple : si je choisis une période 1/12/2014 au 31/12/2014 mes lignes d'abonnements qui ont fini avant le 1/12/2014 ne génèrent aucun CA pour cette période)
- Idem pour les abonnements qui ne sont pas encore commencés (DATE FIN ANALYSE < date de fin de ma feuille 1)

- Par contre pour un abonnement dont la date de début et la date de fin, couvre la fourchette de dates que je choisis , il faudrait que je puisse obtenir le total du CA de mes lignes (env 10000 dans ma feuille 1) compris entre ces dates et proratisé en conséquence

Exemple : Un abonnement va du 11/3/2012 au 10/03/2015 soit une période de 3 ans ou 3 x 365 jours = 1095 Le Chiffre d'affaires renseigné colonne Q est de 8.000 Euros pour 1095 jours

Pour la période du 1/12/2014 au 31/12/2014 : mon CA calculé au prorata est de 226,48 E (8000 *31 j / 1095) = 226,48)

Pour une période d'analyse allant du 1/12/2014 au 28/2/2015 alors mon CA pour cette ligne sera de (8000 * 90 j / 1095 = 657,53

Pour une période d'analyse du 1/4/2015 au 31/5/2015 le CA sur cette ligne serait nul (car l'abonnement est fini)


Je souhaite obtenir le total de mon CA sur mes 10000 lignes de la feuille 1 , en totalité (donc pour tous les articles) et si une notion de filtre pouvait être intégrée, obtenir une analyse pus fine de mon CA par article (donc colonne G) ou par compte comptable (colonne H)

Le nombre de jours entre mes dates d'abonnement peut être soit re-calculé soit repris à partir de la colonne N qui est un champ exporté et calculé dans le logiciel commercial

Par un TCD je n'y parviens pas , à moins qu'une notion de champ calculé puisse être intégrée.

Je vous remercie par avance pour votre aide, sachant que la contrainte de taille est le nombre de lignes à traiter et à sommer.

Bonne soirée ou bonne journée à tous ceux qui auront eu la force de lire ce post jusqu'au bout

Sylvie

 

[Modeste]

Re : Somme de variables dans un intervalle de dates TCD ? VBA ?

Salut Sylvie (ça faisait vraiment très longtemps!)

Pas eu le temps de tout lire ... mais regarde si la proposition de chris dans ce fil ne te donnerait pas des pistes

 

[Sylvie]

Re : Somme de variables dans un intervalle de dates TCD ? VBA ?

Bonjour Modeste,

Et oui que le temps passe vite
Je te remercie pour ce lien vers ce fil très intéressant et qui effectivement semble me donner une très très bonne piste. Je regarde cela de plus près et je te fais un retour asap

Merci Modeste : lecture rapide certes (car post long et barbant) mais hyper efficacité : tu n'as pas changé

Bonne journée

 

[chris]

Re : Somme de variables dans un intervalle de dates TCD ? VBA ?

Bonjour
Coucou Modeste

Pour la période de dates on peut effectivement utiliser une colonne supplémentaire comme dans l'exemple cité par Modeste

Pour la proratisation, un champ calculé de TCD ne peut le faire car les champs calculés calculent sur la base de la somme et non de l'unité et les conditions ne peuvent se référer à des champs autres que numériques.

Là aussi il faut donc un champ calculé dans la source. Voir ci-joint : je n'ai proratisé que le CA.

Reste à ajouter un bout de VBA si tu veux que l'actualisation du TCD suive le changement de dates d'analyse.

 

[Sylvie]

Re : Somme de variables dans un intervalle de dates TCD ? VBA ?

Bonjour Chris,

Merci beaucoup pour ton aide. J’étais précisément en train de plancher sur le lien adressé par Modeste.

Effectivement à l'ouverture de mon fichier excel, le nec plus ultra serait que je puisse automatiser l'ajout des 2 colonnes comme tu l'as fait ainsi que mon TCD. Est ce qu'une macro complémentaire avec une barre d'outil est envisageable ?
La proratisation du CA dans le tableau source est une excellente idée (je galérais pour le faire dans le TCD et je comprends mieux pourquoi maintenant).

Dois je procéder de même si je souhaite avoir dans mon TCD mon CA proratisé comme tu l'as fait et sur une autre colonne ce que seraient mes PCA à la date de l'analyse ?
PCA = Chiffre d'affaires total sur la date de l'abonnement restant à courir après ma période d'analyse

Pour être plus claire je te renvoie ton fichier avec un exemple de PCA calculé

Encore merci pour ton aide très précieuse

 

[chris]

Re : Somme de variables dans un intervalle de dates TCD ? VBA ?

RE

Oui Pour le PCA c'est ça.

Si tu extrais régulièrement, je pense que le mieux est d'exporter toujours sous le même nom, et de faire un liaison dans le classeur où tu analyses. Ainsi les données seront sous forme de tableau et les formules suivront (dans ce cas, je crois qu'il faut les mettre à droite mais à vérifier).

Cela évite de rebidouiller à chaque intégration. Si tu veux gérer des classeurs différents au fil du temps fais-le en les rangeant dans une arborescence explicite pas en nommant le classeur exporté.

Remarque : pour le -1 sur les calcul d’écart de dates, je me suis posé la question mais la colonne Q étant calculé sans cet ajustement, je n'ai rien fait...