SOMME.SI.ENS avec... critères variant...
Le
HD
Bonjour,
J'ai un tableau comportant en colonne des numéros de comptes (qui
peuvent se répéter plusieurs fois), des dates, des montants en colonne
débit ou en colonne crédit.
Jusque là, pour calculer un SoldeDébit j'utilisais une formule
SOMME.SI.ENS afin de faire ressortir le total de la colonne débit et le
total de la colonne crédit d'une racine de compte. Je le faisais pas
exemple pour une racine de compte (par exemple 445) et pour une
fourchette de date donnée.
Grosso modo:
SI total débit (445) > total crédit (445) ALORS soldedébit (445) = total
débit (445) - total crédit (445) SINON soldedébit (445) = 0
Seul souci la formule n'est pas exacte. Je m'en suis rendu compte il y'a
peu.
si le compte 445 comporte plusieurs sous comptes (exemple 4451, 4452,
4453) l'on doit peut avoir des incohérences
Compte débit crédit
4451 4
4451 6
solde débit = 0 (car total débit - total crédit est négatif)
4452 8
4452 3
solde débit = 5
4453 2
4453 57
solde débit = 0 (car total débit - total crédit est négatif)
Le solde débit du 445 (avec ses sous comptes 4451, 4452, 4453) doit donc
donner 5.
Or si je prend la formule (qui est fausse) et que j'avais utilisée le
total débit - total crédit du 445
4451 4
4451 6
4452 8
4452 3
4453 2
4453 57
j'aurai alors total débit - total crédit = 14 - 66 = -52 donc solde
débit = 0 (puisque le résultat est négatif).
Ma formule erronée s'apparente donc à ça :
=SOMME.SI.ENS(B:B, A:A, "445*")-SOMME.SI.ENS(C:C, A:A, "445*")
(pour info: SOMME.SI.ENS(somme_plage; plage_critères1;
critères1;[plage_critères2; critères2]; .)
Comment calculer le solde débit de chaque sous comptes possible en une
formule ?
En sachant que les sous comptes peuvent être trés variés j'aurais
très bien pu avoir des 4451200, 44511690, 44595ZZ, etc donc je ne
peux me permettre de les listes.
A savoir également que je cherche à mettre ensuite la formule en vba
pour ressortir via une fonction volatile (avec arguments) le résultat
directement dans une cellule. Chose qui fonctionnait jusqu'à ce que
je me rende compte que ma formule n'est pas rigoureusement exacte
puisqu'elle peut aboutir à un bug dans le cas de sous compte avec des
soldes débits négatifs
Merci d'avance pour votre aide
Cordialement,
HD
J'ai un tableau comportant en colonne des numéros de comptes (qui
peuvent se répéter plusieurs fois), des dates, des montants en colonne
débit ou en colonne crédit.
Jusque là, pour calculer un SoldeDébit j'utilisais une formule
SOMME.SI.ENS afin de faire ressortir le total de la colonne débit et le
total de la colonne crédit d'une racine de compte. Je le faisais pas
exemple pour une racine de compte (par exemple 445) et pour une
fourchette de date donnée.
Grosso modo:
SI total débit (445) > total crédit (445) ALORS soldedébit (445) = total
débit (445) - total crédit (445) SINON soldedébit (445) = 0
Seul souci la formule n'est pas exacte. Je m'en suis rendu compte il y'a
peu.
si le compte 445 comporte plusieurs sous comptes (exemple 4451, 4452,
4453) l'on doit peut avoir des incohérences
Compte débit crédit
4451 4
4451 6
solde débit = 0 (car total débit - total crédit est négatif)
4452 8
4452 3
solde débit = 5
4453 2
4453 57
solde débit = 0 (car total débit - total crédit est négatif)
Le solde débit du 445 (avec ses sous comptes 4451, 4452, 4453) doit donc
donner 5.
Or si je prend la formule (qui est fausse) et que j'avais utilisée le
total débit - total crédit du 445
4451 4
4451 6
4452 8
4452 3
4453 2
4453 57
j'aurai alors total débit - total crédit = 14 - 66 = -52 donc solde
débit = 0 (puisque le résultat est négatif).
Ma formule erronée s'apparente donc à ça :
=SOMME.SI.ENS(B:B, A:A, "445*")-SOMME.SI.ENS(C:C, A:A, "445*")
(pour info: SOMME.SI.ENS(somme_plage; plage_critères1;
critères1;[plage_critères2; critères2]; .)
Comment calculer le solde débit de chaque sous comptes possible en une
formule ?
En sachant que les sous comptes peuvent être trés variés j'aurais
très bien pu avoir des 4451200, 44511690, 44595ZZ, etc donc je ne
peux me permettre de les listes.
A savoir également que je cherche à mettre ensuite la formule en vba
pour ressortir via une fonction volatile (avec arguments) le résultat
directement dans une cellule. Chose qui fonctionnait jusqu'à ce que
je me rende compte que ma formule n'est pas rigoureusement exacte
puisqu'elle peut aboutir à un bug dans le cas de sous compte avec des
soldes débits négatifs
Merci d'avance pour votre aide
Cordialement,
HD
Pour calculer le solde d'un compte, ceci:
Avec
Les noms ou n° des comptes en colonne A
Les débits en colonne B
Les crédits en colonne C
En G1 le Nom ou n° du compte pour lequel on souhaite le solde
=SOMMEPROD(($A$2:$A$1000=$G$1)*($C$2:$C$1000)-($A$2:$A$1000=$G$1)*($B$2:$B$1000))
ou
Il serait avantageux de nommer les plages dynamiquement, par exemple
=SOMMEPROD((PlageDesComptes=LeCompteAChercher)*(PlageDesCrédits)
-(PlageDesComptes=LeCompteAChercher)*(PlageDesDébits))
--
Salutations
JJ
"HD"
Le demandeur a parlé de comptes et de sous-comptes.
Dès lors, ne conviendrait-il pas de commencer la partition avec un
gauche(a1;3)D5, puis partir avec pianissimo Sommeprod ....comme tu l'as
énoncé?
Il est évident que si l'on nomme les partitions, ce sera plus facile.
Jacquouille
" Le vin est au repas ce que le parfum est à la femme."
"Jacky" a écrit dans le message de groupe de discussion :
m42oqb$7rd$
Bonsoir,
Pour calculer le solde d'un compte, ceci:
Avec
Les noms ou n° des comptes en colonne A
Les débits en colonne B
Les crédits en colonne C
En G1 le Nom ou n° du compte pour lequel on souhaite le solde
=SOMMEPROD(($A$2:$A$1000=$G$1)*($C$2:$C$1000)-($A$2:$A$1000=$G$1)*($B$2:$B$1000))
ou
Il serait avantageux de nommer les plages dynamiquement, par exemple
=SOMMEPROD((PlageDesComptes=LeCompteAChercher)*(PlageDesCrédits)
-(PlageDesComptes=LeCompteAChercher)*(PlageDesDébits))
--
Salutations
JJ
"HD" m42m6p$8tc$
tu as tout à fait raison, je n'avais pas déployé le message dans sa totalité
Ceci devrait faire
http://cjoint.com/?DKntjqjhve7
--
Salutations
JJ
"Jacquouille"
Jacquouille
" Le vin est au repas ce que le parfum est à la femme."
"Jacky" a écrit dans le message de groupe de discussion :
m42rv2$g3l$
Oui Jacques,
tu as tout à fait raison, je n'avais pas déployé le message dans sa totalité
Ceci devrait faire
http://cjoint.com/?DKntjqjhve7
--
Salutations
JJ
"Jacquouille" m42q5s$be3$
Jacquouille
" Le vin est au repas ce que le parfum est à la femme."
"Jacky" a écrit dans le message de groupe de discussion :
m42rv2$g3l$
Oui Jacques,
tu as tout à fait raison, je n'avais pas déployé le message dans sa totalité
Ceci devrait faire
http://cjoint.com/?DKntjqjhve7
--
Salutations
JJ
"Jacquouille" m42q5s$be3$
Justement... votre formule ne fonctionne pas.
Dans l'exemple que vous donnez, l'on demande le SoldeCréditeur du 446.
Qui est en fait le total des soldes créditeurs des sous comptes du 446.
(solde crédit = si (crédit > débit alors solde crédit = crédit - débit
sinon solde crédit = 0)
Pour la racine 446, l'on ne devrait pas avoir 55 euros puisque le solde
crédit du 4461 est égal à 57 et le solde crédit du 44611 est égal à 0
(puisque l'on a 0 en crédit et 2 euros en débit). Le solde crédit de la
racine 446 doit donc être 57 et non 55.
@+
HD
Je ferais une proposition en vba si cela convient.....
Que doit il se passer si le solde de 2 ou plusieurs compte ont un solde débit positif ?
Compte débit crédit
4451 4
4451 6
solde débit = 0 (car total débit - total crédit est négatif)
4452 8
4452 3
solde débit = 5
4453 2
4453 57
solde débit = 0 (car total débit - total crédit est négatif)
4456 59
4456 57
solde débit = 2
'_________________________
Retenir
le 5 du 4452
le 2 du 4456
Ou le total des 2 comptes soit: 7
--
Salutations
JJ
"HD"
Faudrait accorder vos violons.
Tu demandes le bilan de tous les comptes dont les 3 premiers chiffres sont
446.
Un bilan = AMHA, la colonne + moins la colonne -. La formule de Jacky fait
exactement cela.
Si tu veux un bilan compte par compte, il faut préciser la demande.
Si tu veux la somme des crédits pour les comptes 446* et celle des débits,
idem, il faut le dire clairement.
Si tu veux que la cel se colorie en rouge si crédits < débits ... il faut le
dire. Jacky te fera exactement cela. -))
Jacquouille
" Le vin est au repas ce que le parfum est à la femme."
"HD" a écrit dans le message de groupe de discussion :
m44gce$6t7$
Bonjour,
Justement... votre formule ne fonctionne pas.
Dans l'exemple que vous donnez, l'on demande le SoldeCréditeur du 446.
Qui est en fait le total des soldes créditeurs des sous comptes du 446.
(solde crédit = si (crédit > débit alors solde crédit = crédit - débit
sinon solde crédit = 0)
Pour la racine 446, l'on ne devrait pas avoir 55 euros puisque le solde
crédit du 4461 est égal à 57 et le solde crédit du 44611 est égal à 0
(puisque l'on a 0 en crédit et 2 euros en débit). Le solde crédit de la
racine 446 doit donc être 57 et non 55.
@+
HD
Je l'ai précisé dans ma demande initiale. Mon souci est justement que,
tout comme Jacky, lorsque j'ai fait ma première formule j'ai fait
l'erreur de prendre le total débit - total crédit des comptes 446* pour
obtenir le solde débit du 446*... sauf que cela ne se calcule pas comme
cela puisque le solde débit du 446 se calcule en additionnant les soldes
débits des sous-comptes du 446* en sachant que dans le cas où total
débit - total crédit est négatif alors le résultat du solde débit est
zéro et non un montant négatif. C'est ce qui fait que le solde débit du
446 n'est pas égal au total débit - total crédit du 446 mais qu'il est
égal à la somme des soldes débit des sous-comptes du 446.
@+
HD
Dans ce cas, l'on a :
le solde débit du 445 (et donc de ses sous-comptes qui sont ici 4451,
4452, 4453 et 4456) est égal à 7.
Mon souci est que je peux très bien avoir à tout moment un nouveau
compte qui sera 445456... je ne peux donc pas partir de sous-comptes à 4
chiffres puisque je peux avoir un nouveau compte à 6 caractères qui
arrivera. En fait les possibilités sont très large suivant la longueur
des numéros de comptes maximum.
@+
HD