Discussion :

[IAmByB]

Bonjour,

J'ai une application écrite en VBA sous Access qui génère un fichier Excel, et insère automatiquement des formules dans certaines cases du fichier.

En l'occurrence, mon problème se situe sur une formule utilisant SUMPRODUCT (SOMMEPROD en français), à savoir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SUMPRODUCT((DY2:DY162>0)* (CY2:CY162))

La formule renvoie une erreur #VALEUR car la colonne CY du fichier Excel contient soit des nombres, soit des chaines (vides) de caractères, et donc SUMPRODUCT n'arrive pas à additionner le contenu de cette colonne.

J'ai donc fait des recherches sur Internet, et tenté la chose suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SUMPRODUCT((DY2:DY162>0)*VALUE(CY2:CY162))

Malheureusement, le résultat est toujours la même erreur #VALEUR.

J'aimerais autant ne pas modifier le contenu de la colonne CY car il est lui-même généré par une formule que je ne voudrais pas modifier etc.

Donc la question est : Y a t-il un moyen de convaincre SUMPRODUCT de considérer le contenu de ma colonne CY comme des nombres, quitte à considérer les chaines vides comme des 0 ?

Merci de vos conseils.

[Kimy_Ire]

Bonjour IAmByB,

Réponse rapide sans forcément connaître la réponse :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SUMPRODUCT((DY2:DY162>0)* (CY2:CY162>0))

Ce n'est pas possible de faire ça ?

Je reviens vers toi en cherchant d'avantage.

Cordialement,
Kimy

[IAmByB]

Merci pour la suggestion. J'ai fait un essai et ça ne marche pas, car mon but est d'additionner les valeurs contenues dans la colonne CY, alors qu'avec la syntaxe que tu proposes, on se contente de les compter (à ce que j'ai compris).

[casefayere]

Bonjour,

essayes cette formule, normalement les nombres en format texte seront reconnus comme des nombres

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("ce que tu veux").Formula = "=SUMPRODUCT((DY2:DY162>0)*(CY2:CY162>0)*(CY2:CY162))"

Ps : je viens d'essayer cette formule sans soucis :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
'E1 à remplacer par la cellule de destination
Range("E1").Formula = "=SUMPRODUCT((DY2:DY162<>"""")*(CY2:CY162))"

[IAmByB]

Merci à ceux qui m'ont répondu.

Je bute toujours sur le même problème. Apparemment, le problème n'est pas de reconnaitre comme nombres des valeurs stockées comme texte, mais de gérer le cas où la case est "vide", c'est à dire qu'elle contient une chaine vide ("").

Suite aux suggestions, j'ai essayé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMMEPROD((CY2:CY162>0)*(DG2:DG162="IRP")*(CS2:CS162="VRAI")*(CY2:CY162<>"")*(CY2:CY162))

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMMEPROD((DG2:DG162="IRP")*(CS2:CS162="VRAI")*(CY2:CY162<>"")*(CY2:CY162))

(retiré la première condition)

sans plus de résultats. A chaque fois, j'ai le message #VALEUR! qui apparait, apparemment parce qu'il arrive que la colonne CY contienne des cases vides (c'est à dire avec une chaine vide).

Je viens de faire un essai avec la première formule de ce message, et en remplaçant toutes les chaines vides de la colonne par des 0, et je constate que dans ce cas, la formule fonctionne et donne bien un résultat numérique au lieu de l'erreur #VALEUR!

Nouveau test avec la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMMEPROD((DG2:DG162="IRP")*(CS2:CS162="VRAI")*(NON(ESTVIDE(CY2:CY162)))*(CY2:CY162))

sans plus de résultats ...

[casefayere]

Bonjour,

apparemment parce qu'il arrive que la colonne CY contienne des cases vides (c'est à dire avec une chaine vide).

Perso, j'ai fait l'essai avec la formule que je t'ai proposé sans avoir de problème, il faut certainement chercher l'erreur ailleurs, mais........

Bon courage

[IAmByB]

Ce qui m'étonne le plus, c'est que la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMMEPROD((CY2:CY162>0)*(CY2:CY162))

me donne toujours la même erreur, alors que la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMMEPROD((CY2:CY162))

me donne bien le total de la colonne CY (de CY2 à CY162) sans être apparemment gênée par les cases vides (mais bien entendu, je n'ai plus mes critères de sélection).

[Kimy_Ire]

Bonjour IAmByB,

Alors, si j'ai bien compris, tu veux faire la somme de ta plage CY2:CY162 si la valeur correspondante en DY2Y162 est > 0, c'est ça ?

Je te propose cette solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(SI((DY2:DY162)>0;(CY2:CY162);0))

... avec un calcul matriciel à savoir Ctrl + Maj + Entrée.

J'attends ton retour !

Cordialement,
Kimy

[IAmByB]

Merci de ta suggestion, mais en réalité j'ai deux critères à appliquer dans ma formule. La description de mon problème est une version simplifiée de ma formule pour me concentrer sur ce qui ne marche pas.


J'ai changé de fonction et suis passé de SOMMEPROD à SOMME.SI.ENS (voir le code ci-dessous) mais la seule différence, c'est qu'au lieu d'obtenir une erreur #VALEUR! j'obtiens un résultat de 0 quelles que soient les valeurs dans ma colonne CY.

J'ai fait des essais de mes SOMMEPROD et SOMME.SI.ENS dans un fichier Excel tout neuf avec 3 colonnes, et cela fonctionne très bien.

J'en conclus qu'il doit y avoir un problème dans le format de cette colonne CY, mais je ne vois pas lequel.

Mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMME.SI.ENS(CY2:CY162;DG2:DG162;"IRP";CS2:CS162;"VRAI")

[casefayere]

perso, je suis étonné, n'as-tu pas un fichier avec données bidon à joindre (en xls) ?

[IAmByB]

Voilà. La formule est en bas à droite, dans la zone jaune du fichier, à droite de l'intitulé "Formule qui ne marche pas".

[mercatog]

La 1ère colonne contenant VRAI (en texte) et non comme valeur booléenne.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A2:A8="VRAI")*(O2:O8="IRP")*(G2:G8))

[IAmByB]

J'ai essayé, et effectivement, cela fonctionne. Mais il me semblait avoir essayé cette syntaxe (sans résultats) dans mon message de 10:49.

Par ailleurs, comment peut-on le coder en utilisant la fonction SOMME.SI.ENS ?

[casefayere]

A moins d'éviter d'utiliser un mot protégé (Vrai), je ne sais pas

[IAmByB]

La formule que j'utilise est construite par une application Access en VBA, puis insérée dans la feuille Excel.
Et lorsque j'utilise le code VBA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
Dim valApha as String
Dim Quote
 
valAlpha = "VRAI"
Quote = """"
 
Formule3 = "SUMPRODUCT((DG" & Debut & ":DG" & Fin & "=" & Quote & "IRP" & Quote & ")*(CS" & CStr(Debut) & ":CS" & CStr(Fin) & "=" & Quote & valAlpha & Quote & ")*(CY" & CStr(Debut) & ":CY" & CStr(Fin) & "))"
Formule = "=" & Formule3
.Range(sCol05 & Fin + indexLigne).Formula = Formule
.Range(sCol05 & Fin + indexLigne).NumberFormat = "0.00%"

le résultat est

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SOMMEPROD((DG2:DG162="IRP")*(CS2:CS162="VRAI")*(CY2:CY162))

ce qui me parait correspondre à la syntaxe suggérée par Mercatog, mais le résultat dans la feuille est encore une erreur #VALEUR!

[IAmByB]

Finalement, j'ai modifié la formule qui déterminait la valeur de ma colonne CY. Du coup, à la place des cellules contenant une chaine vide (et qui bloquaient les fonctions SOMMEPROD et SOMME.SI.ENS, allez savoir pourquoi), j'ai des zéros. Et du coup, plus rien ne s'oppose à la justesse de mon résultat.

Il manque encore l'explication du pourquoi le SOMMEPROD n'accepte pas les chaines vides de ma colonne CY, mais je crois que je pourrai vivre sans.

Merci beaucoup à tous ceux qui ont participé à ce débat enrichissant et qui ont apporté leurs idées et suggestions !

[casefayere]

mercatog écrit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

A2:A8="VRAI"

tu écris

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CS2:CS162="VRAI"

à voir cette plage "CS2:CS162" ....

[IAmByB]

Désolé, mais je ne vois pas trop où tu veux en venir. La colonne A ou la colonne CS sont toujours des colonnes Excel, non ? Quelle différence vois tu ?

[casefayere]

Simplement parce que tu n'as envoyé qu'un fichier allégé et c'est normal mais on se méfie de tout (avec l'habitude), je ne sais pas comment ta plage originale est structurée, ce n'est qu'une réflexion !

Bonne soirée