Somme.si.ens avec calcul sur le critère

[bobsnoopy]

Bonjour à tous,


Je m'échine sur un problème que je ne parviens à résoudre, donc j'en appelle à vous autres bonnes âmes.

J'ai un fichier avec en première ligne 1,2,3...31 correspondant aux jours du mois.

Les lignes de ce tableau sont les noms de différentes personnes, et il est rempli par le nombre d'heures travaillées chaque jour.

Le nom de l'onglet est de type "Avril 2011".

J'ai uneformule assez complexe qui me permet, en allant récupérer le nom de l'onglet, de déterminer pour chaque jour s'il s'agit d'un week-end ou non. Je l'utilise pour ma mise en forme automatique, ça marche très bien, merci.

Bien.

Je souhaite maintenant calculer, disons, le nombre d'heures travaillées durant les week-end. Je m'imagine utiliser une fonction de type somme.si.ens. Ma plage de cellules pour la somme, pas de soucis, mais pour le critère, je ne sais comment faire. En effet, le critère n'est pas le jour du mois, donc ma première ligne, mais le résultat d'un calcul à partrir de celle-ci.

Une solution pourrait consister à créer une autre ligne, avec le résultat de mon calcul dedans, qui me donnerait par exemple (F;F;F;F;F;V;V;F;F;F;F;F;V;V;F;F;F;...), que je mettrais en critère, et ça marcherait très bien. Je ne souhaite cependant pas le faire, pour des raisons qui me regardent! Je voudrais juste que mon critère ne soit pas une plage de cellules elle-même, mais le résultat d'un calcul appliqué à cette plage...

Je ne souhaite pas non plus utiliser VBA...

Merci d'avance pour vos réponses éclairées (si c'est possible)!

 

[Tibo]

Re : Somme.si.ens avec calcul sur le critère

Bonjour,

Pour qu'on puisse t'aider efficacement, il faudrait que tu nous joignes un extrait de ton fichier.

Car la description que tu fais de ton fichier risque d'être interprétée de façon différente par les personnes qui voudraient t'aider.

Quant à la fonction SOMME.SI.ENS, c'est une fonction apparue avec Excel 2007. Elle peut être remplacée par SOMMEPROD, surtout si ton fichier devait être utilisé sur un poste encore sous Excel 2003 par exemple.

A te (re)lire avec ton fichier

@+

 

[bobsnoopy]

Re : Somme.si.ens avec calcul sur le critère

Merci de ta réponse rapide!

Je pensais en fait être suffisamment clair dans mes explications pour ne pas nécessiter d'envoir de fichier!

Mais bon, pour mettre les idées au clair, voici...

Merci d'avance!

 

[Tibo]

Re : Somme.si.ens avec calcul sur le critère

Bonjour,

Plutôt que de reprendre les données du mois sur la première ligne, j'ai indiqué directement la date (d'après le nom de l'onglet) en ligne 2 (Voir cellules D2 et suivantes).

J'ai également appliqué une MFC pour mettre en exergue les samedi et dimanche.

Voir le fichier joint.

@+

 

[bobsnoopy]

Re : Somme.si.ens avec calcul sur le critère

Bonjour Tibo,


Merci pour ta réponse, qui va je pense me permettre de trouver la voie!

Je suis, en bon français, un éternel insatisfait!

En fait, je ne souhaitais pas modifier la ligne 2 non plus...

Ce que je n'arrive pas à reproduire, c'est que lorsque tu as la fonction joursem dans ta formule "=SOMMEPROD((JOURSEM(COLONNE($D$2:$AH$2);2)>5)*D4:AH4)", il renvoie bien vers une série de valeurs.

Avec ma formule, un peu plus complexe, ça donnerait:


=SOMMEPROD(SI(JOURSEM(INDIRECT(ADRESSE(2;COLONNE(D$2:AG$2);1))&"/"&GAUCHE(DROITE(CELLULE("nomfichier";$A$2);NBCAR(CELLULE("nomfichier";$A$2))-TROUVE("]";CELLULE("nomfichier";$A$2);1));TROUVE(" ";DROITE(CELLULE("nomfichier";$A$2);NBCAR(CELLULE("nomfichier";$A$2))-TROUVE("]";CELLULE("nomfichier";$A$2);1)))-1)&"/"&DROITE(CELLULE("nomfichier";$A$2);4);2)>5;1;0)*D4:AG4)

En orange, la série de dates de 1 à 31, et en vert, l'ajout automatique de "/04/2011".
Or lorsque je demande l'évaluation de formule, COLONNE(D$2:AG$2) ne me renvoie que 4, c'est-à-dire l'indice de la première colonne. Ce que je voudrais qu'il me renvoie, c'est 4,5,6,7,...! Pour ensuite retrouver le jour du mois correspondant et y ajouter à chaque le suffixe "/04/2011"

Tu vois ce que je veux dire?

Merci d'avance pour ton attention!

Dans l'espoir de te lire au plus vite!

Edit: j'ai trouvé ma bourde (le "SI"), mais je suis toujours bloqué...
Nouvelle formule:
=SOMMEPROD(JOURSEM(INDIRECT(ADRESSE(2;COLONNE(D$2:AG$2);1))&"/"&GAUCHE(DROITE(CELLULE("nomfichier";$A$2);NBCAR(CELLULE("nomfichier";$A$2))-TROUVE("]";CELLULE("nomfichier";$A$2);1));TROUVE(" ";DROITE(CELLULE("nomfichier";$A$2);NBCAR(CELLULE("nomfichier";$A$2))-TROUVE("]";CELLULE("nomfichier";$A$2);1)))-1)&"/"&DROITE(CELLULE("nomfichier";$A$2);4);2)>5*D4:AG4)

Au moment du "INDIRECT", il me trouve bien la série de cellules, mais me met un "VALEUR" du plus mauvais effet!

 

[Tibo]

Re : Somme.si.ens avec calcul sur le critère

Bonsoir,

En reprenant ta formule, et en simplifiant le début, essaye ceci :

=SOMMEPROD((JOURSEM(COLONNE(A$2:AD$2)&"/"&GAUCHE(DROITE(CELLULE("nomfichier";$A$2);NBCAR(CELLULE("nomfichier";$A$2))-TROUVE("]";CELLULE("nomfichier";$A$2);1));TROUVE(" ";DROITE(CELLULE("nomfichier";$A$2);NBCAR(CELLULE("nomfichier";$A$2))-TROUVE("]";CELLULE("nomfichier";$A$2);1)))-1)&"/"&DROITE(CELLULE("nomfichier";$A$2);4);2)>5)*D4:AG4)

@+

 

[bobsnoopy]

Re : Somme.si.ens avec calcul sur le critère

Merci Tibo!

C'était pas exactement ça, mais la piste était bonne et je suis arrivé à mes fins. Merci de ton aide!

Je m'attaque maintenant aux jours fériés, et c'est encore une autre paire de manches...

A bientôt!

 

[PhY]

Re : Somme.si.ens avec calcul sur le critère

Bonjour bobsnoopy,

ton fichier excel m'intéresse beaucoup, j'ai mis pas mal de temps à trouver un modèle de CRA avec la gestion des jours fériés.

Est il possible que tu me l'envoies si tu as réussi à régler la gestion des jours fériés ?

Merci.