Somme conditionnelle

[Fatifatirose]

Bonjour tout le monde;
J'espère que vous allez bien.
Alors, j'ai besoin d'effectuer une somme conditionnelle sous excel. Je m'explique:
Je dispose d'un fichier d'origine où y'a quatre colonnes (Mois de l'échéance - Année de l'échéance - Montant des Impayés - Montant des échéances )
Pour chaque mois, je désire faire une sorte de suivi des impayés (ci-joint le tableau que je désire remplir).
Les mois vont de janvier 2012 à Mai 2013. Donc pour le mois d'observation Mai 2012 par exemple et le mois d'échéance Janvier 2012, l'indicateur que je calcule est le somme des impayés de Janvier à Mai 2012 divisée par la somme des échéances de Janvier à Mai 2012.
Je sais que j'ai du mal à m'expliquer, j'espère que le fichier excel est bien plus parlant.
Je vous remercie d'avance de votre aide!
Excellente journée

 

[Dugenou]

Re : Somme conditionnelle

Bonjour,
Quelquechose comme =INDEX($D$32:$D$65;EQUIV(C$3;$B$32:$B$48;0))/INDEX($E$32:$E$65;EQUIV($B4;$B$32:$B$48;0))
mais il faudrait que tu entres quelques données dans ta table car là j'ai pris les dates du tableau pour la colonne des mois de ta table. Si le mois est en texte il faudra reconstruire la date correspondante.
A te lire

PS seulement si tu as une seule ligne par mois d'échéance

 

[Fatifatirose]

Re : Somme conditionnelle

Bonjour Dugenou, je te remercie énormément pour ta réponse rapide
Je suis en période d'essai au sein de l'entreprise dans laquelle je bosse, je préfère ne pas commettre d'erreurs ...
J'ai essayé d'appliquer ta formule mais en fait, j'ai oublié de préciser la forme exacte de ma table de données (En voici un échantillon en pièce jointe).
Merci bcp !!

 

[Dugenou]

Re : Somme conditionnelle

Re,
Voir en pj le calcul en dernière colonne pour valider le principe.
Dans ta table il n'y a pas de donnée pour le mois d'observation : c'est normal ?

 

[Fatifatirose]

Re : Somme conditionnelle

En effet, chaque échéance est observée du 01/2012 au 05/2013; donc je peux pas rajouter une colonne pour le mois d'observation.
Par exemple, pour remplir la case du mois de l'échéance Mars 2012 observée en Mai 2012, je vais faire la somme des impayés allant de Mars à Mai et les diviser par les échéances sur le même horizon. J'espère que c un peu plus clair
La formule est on ne peut plus correcte mais elle ne prend pas en considération le mois de l'observation ...
Si j'observe les échéances de Mars en Mai 2012, j'aurai 3 impayés (Mars - Avril - Mai ) sur 3 échéances, et si j'observe ces échéances en Mai 2013, j'aurai 15 Impayés sur 15 échéances ...
Je te prie de m'excuser, tu ne peux pas savoir comme je te suis reconnaissante !

 

[Victor21]

Re : Somme conditionnelle

Bonjour, fatifatirose, Dugenou

Un essai à tester avec ce que j'ai compris

 

[Fatifatirose]

Re : Somme conditionnelle

Bonjour Victor21
C'est exactement ce dont j'ai besoin !
Je vous suis, Dugenou et toi, amplement reconnaissante.

 

[Fatifatirose]

Re : Somme conditionnelle

Rebonjour, en fait, j'ai essayé d'appliquer la formule sur ma base de données qui contient un peu plus de 954000 Lignes, en vain. Je n'arrive pas à trouver l'erreur ... Ci-joint un échantillon de mon fichier.
Merci merci.

PS: suis-je obligée de mettre des plages limitées dans les critères de SOMMEPROD. Par exemple, au lieu de SOMMEPROD((DATA!$G$2:$G$900>=MOIS(Synthèse!$B4)), je veux avoir SOMMEPROD((DATA!$G:$G>=MOIS(Synthèse!$B4))

Et, pourrai-je appliquer un filtre dans la fonction, par exemple calculer les impayés par type de produit (Automobile ou prêt personnel) et par type de personnes (personnes physiques, personnes morales) ...

Excusez le dérangement !

 

[Dugenou]

Re : Somme conditionnelle

Hello Fati, Victor,
Pour le premier point : une réponse "à la Misange" : déclarer votre table en tableau et utiliser les noms des champs dans la formule. Ce qui est dommage c'est qu'elle ne se recopie pas à droite(les noms des champs changent) mais elle se recopie bien vers le bas.
Voir PJ
Pour le second point : c'est possible mais ce sera plus light, je pense, avec somme.si.ens : je prépare un autre fichier.
Cordialement

 

[Dugenou]

Re : Somme conditionnelle

Re,
Il y peu être moins lourdingue : la formule à 4 ou 5 lignes devient difficile à lire, mais je crois que ça marche.

 

[Victor21]

Re : Somme conditionnelle

Re,

Les fonctions matricielles consomment beaucoup de ressources, et je ne crois pas qu'il soit possible de définir des matrices avec des colonnes complètes.
Il est toujours possible d'ajouter des critères (produit ou personnes). Il suffit dans la synthèse d'ajouter deux listes déroulantes, et de s'y référer lors de la comparaison avec les matrices correspondantes de Données (dans les deux termes de la formule).
Mais j'ai bien peur qu'avec 954000 lignes, la durée de calcul ne soit insupportable.

Il me semble plus raisonnable d'envisager du VBA, mais sans moi


Oupps : pas vu, Dugenou

 

[Fatifatirose]

Re : Somme conditionnelle

Re,
C'est très gentil Dugenou, merci bcp pour ton aide.
Sinon Victor, je crois que tu as raison par rapport à VBA ... J'y aurai probablement recours