Coder une fonction SOMPROD en VBA

Bonjour,

Je refais un post suite à ma demande d'hier qui a déjà bcp avancé.

J'ai réussi à obtenir le chiffre que je cherchais (le nb de patients présent le 09/02/2014 à 09:00:00 présent depuis au moins déjà 24h00) avec une formule mais je souhaitais la passer en VBA, et les sites internet ne semblent pas toujours bien clair quant à la syntaxe à utiliser.

Dans le fichier en PJ, la formule excel que je souhaite transcrire en code vba est en sheets("Feuil2").range("A41")

=SOMMEPROD((Feuil1!B$2:B$8836<B41)*

(Feuil1!D$2:D$8836>=B41)*

((B41+9/24-Feuil1!B$2:B$8836-Feuil1!A$2:A$8836)>1)*

(Feuil1!C$2:C$8836>=9/24))

N'ayant jamais fais de sommeprod ni en excel ni en vba, j'essaie sumproduct et countifs

    X1 = Application.SumProduct( _
    [.Columns(2)& "<" & DTARGET], _
    [.Columns(4)& "=" & DTARGET], _
    [(DTARGET + 9 / 24 - Columns(2) + Columns(1))& ">" & 1], _
    [.Columns(3)&">=" & 9 / 24])

    X2 = Application.CountIfs( _
    .Columns(2), "<" & DTARGET, _
    .Columns(4), "=" & DTARGET, _
    (DTARGET + 9 / 24) - (Columns(2) + Columns(1)), ">" & 1, _
    .Columns(3), ">=" & 9 / 24)

Quelquechose m'échappe, je soupçonne countifs de ne pas accepter la 3e ligne trop éloignée de la simple déclaration de champ. Quant à la fonction sumproduct ne l'ayant jamais utilisée, je pense n'avoir pas compris les [arg1], [arg2], [arg3],... de l'aide Excel.

Est-ce que quelqu'un en a déjà traduite en VBA ?

Bonjour,

Faire en vba 150 sommeprod() sur 9000 lignes ne serait pas très efficace.

Tu devrais mettre tes données dans une variable tableau et compter en balayant toutes les lignes une seule fois.

Reste peut-être une question à se poser :

1 patient présent plusieurs jours + de 24h à 9h, le comptes-tu plusieurs fois ou seulement le 1er jour validé ? Actuellement il me semble qu'il est compté sur plusieurs jours.

eric

Bonjour Eric,

Je dois recompter le patient chaque jour dans son lit.

Les variables tableaux ce n'est pas la première fois que l'on m'en parle sur ce site.

Je vais regarder vos cours, j'imagine qu'on doit expliquer çà.

Merci Eric

EDIT cours num10

Bon je vous tiens au courant si j'y arrive !

J'ai lu le cours, j'ai bien compris ce qu'étaient les variables tableaux.

Je ne pense pas en avoir besoin pour mes formules.

Càd que j'ai besoin d'avoir le nb de patients présents etc...

pour chaque jour de l'année mais j'ai besoin de chacune des valeurs pas de la somme sur l'année.

j'ai besoin d'avoir le nb de patients présents etc...

pour chaque jour de l'année mais j'ai besoin de chacune des valeurs pas de la somme sur l'année.

On a le résultat de ce qu'on calcule.

Je ne voulais pas tout faire que tu apprennes, mais bon :

eric

Eric tu seras définitivement mon sauveur !

Je suis en train d'éplucher le code, c'est vraiment d'un tout autre niveau que le mien, je commence un peu à comprendre ce que tu as écris.

Je vois aussi l'intérêt d'utiliser des variables tableaux avec les fonctions If

Ne serait-ce que le fait d'enchainer les deux fonctions if à la suite plutôt que d'en faire une seule avec un AND c'est tellement plus intelligent en terme de rapidité de calcul.

Je n'ai pas encore tout compris mais je vais travailler ton code.

Je vois bien que tu limites la variable tableau avec le nombre de dates possibles (datemax - datedbt + 1)

que les boucles avec Ubound permettent d'aller jusqu'à la dernière ligne du tableau à 2 colonnes.

Peut-être peux-tu juste m'expliquer ce que signifie ?

    datemin = Application.Min(.[B:B], .[D:D]) + 1

J'aurai mis :

    datemin = Application.Min(.Columns(2), .Columns(4)) + 1

En fait je n'ai jamais utiliser les crochets jusqu'à présent.

Enfin, Eric merci beaucoup pour tout le temps que tu auras passé sur mon problème !

Ce code va beaucoup nous aider à sortir des indicateurs dans notre établissement !

ps

Je ne voulais pas tout faire que tu apprennes,

Je suis désolé je m'en veux un peu,... je me demandais en effet si des boucles n'allaient pas résoudre le problème mais je n'avais pas encore penser le coupler avec les var tableaux, je ne voyais celles-ci qu'avec les sumproduct et countifs. Je te comprends, je sais l'importance d'apprendre par soi-même. J’essaierai de plus réfléchir aussi la prochaine fois. Merci pour ton aide et je compte bien assimiler tout ce que tu m'as appris !

Tout s'apprend, on a tous commencé en tâtonnant plus ou moins bien

Utiliser les tableaux fait gagner énormément de temps sur les grandes bases.

Toutes les données sont lues en une fois. Lire cellule par cellule est très couteux (100 fois plus lent parfois)

Ne serait-ce que le fait d'enchainer les deux fonctions if à la suite plutôt que d'en faire une seule avec un AND

Si tu as une grande boucle c'est préférable oui.

Malheureusement vba continue d'évaluer les autres expressions même s'il a déjà False, du temps de perdu.

J'aurai mis :

Tu peux aussi.

C'est juste que j'ai trop l'habitude d'utiliser l'écriture abrégée des ranges.

[A1] est équivalent à range("A1")

Pas forcément une bonne habitude, d'autant plus que ça fait perdre l'autocomplétion...

eric

edit: à la réflexion il faut sans doute enlever le -1 du calcul de datemax.

Sur ton exemple c'est vide, mais c'est un cas particulier je pense.

eriiic a écrit :

edit: à la réflexion il faut sans doute enlever le -1 du calcul de datemax.

Sur ton exemple c'est vide, mais c'est un cas particulier je pense.

Haha oui je confirme je viens de rajouter ton programme au mien et en effet il a planté sur la dernière date !

Merci !