=SOMMEPROD imbrique ne fonctionne pas

[chich]

Bonjour la communauté

je n'arrive pas a imbrique sommeprod
Merci d'avance


=SI(suivant+MIN(1;SOMMEPROD(--(DECALER(Feuil1!F$6;4+suivant;1-JOURSEM(Feuil1!F$6;2);1;7);Feuil1!F32:F41={"a"."b"."c"}))=6;1;suivant+MIN(1;SOMMEPROD(--I(DECALER(Feuil1!F$6;4+suivant;1-JOURSEM(Feuil1!F$6;2);1;7);Feuil1!F32:F41={"a"."b"."c"})))))

Cordialement

 

[chich]

Rebonjour

Le mieux serait que tu m'expliques bien ton besoin. On peut certainement remplacer tes NB.SI, mais ne sachant ce que tu veux...

Quand je vois le style de tes formules dans ton tableau, je pense qu'il y a certainement moyen de faire plus "simple", au besoin avec du matriciel.

@ plus

Je souhaite faire un planning des absence et d' astreinte
- astreinte = 7 jours par collaborateur
- motif d'absence = a,b,c
- cycle d'astreinte = ligne 7
- la plage C11:C21 va contenir des formules qui revois a ou b ou c
il faut que le cycle d'astreinte s'adapte au planning d'absence si un collaborateur a au moins un jour
avec un motif d'absence donc le cycle d'astreinte passe au collaborateur suivant qui na pas de motif
d'absence dans dans sa semaine
Dans cette formule en ligne 7 qui est nommé prochain
=SI(suivant+MIN(1;NB.SI(DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7);"<>"))=6;1;suivant+MIN(1;NB.SI(DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7);"<>")))
j'aimerai remplacer NB.SI par la méthode SOMMEPROD(--(C11:C21={"a"."b"."c"}))
la plage C11:C21 va contenir de formules qui revois a ou b ou c
je n'arrive pas a imbriqué la méthode SOMMEPROD(--(C11:C21={"a"."b"."c"})) a la place de nb.si(C11:C21;"<>")
j' espère etres suffisamment
Cordialement

 

[CISCO]

Bonjour


Si tu m'avais expliqué cela dès le début, on aurait été plus vite...

Autrement dit, tu veux que la formule de la ligne 7, te donne le numéro de la personne d'astreinte (et pas forcément un FAUX), sachant que l'astreinte dure 7 jours consécutifs (donc sans prendre les collaborateurs absents dans la période de 7 j considérée).

Dans ces conditions, pourquoi n'as tu pas B7 = 6 puisque tu as mis dans A7 le collaborateur 5 ?
Est-ce qu'on est forcé de respecter l'ordre de passage, 1, puis 2, puis 3 et ainsi de suite, ou est-ce qu'on peut revenir "en arrière" ?

@ plus

 

[CISCO]

Bonjour

Est-ce que c'est quelque chose comme ça qu'il te faut ?

La formule tient compte des personnes ayant déja fait une astreinte dans les 36 derniers jours.

@ plus

P.S : On peut faire sans les 6 premières colonnes que j'ai rajoutée (et que tu peux masquer), mais en attendant...

 

[chich]

Bonjour

Est-ce que c'est quelque chose comme ça qu'il te faut ?

La formule tient compte des personnes ayant déja fait une astreinte dans les 36 derniers jours (9 * 7 = 36). Si tu as moins ou plus de collaborateurs, il faudra changé les 35 dans la formule.

@ plus

P.S : On peut faire sans les 6 premières colonnes que j'ai rajoutée (et que tu peux masquer), mais en attendant...

Bonjour
Merci bon boulot désole pour la perte de temps
mais il y a encore quelque point a voir
- astreinte = 7 jours du lundi au dimanche par collaborateur et jamais deux semaines de suite
et sur 9 collaborateur il y a que 5 collaborateur qui prennent l'astreinte
NB.SI compte les cellules non vide c'est pour ca que je veux la remplacer par
SOMMEPROD qui va compter uniquement les critères a,b,c
- la plage C11:C19 va contenir des formules qui revois a ou b ou c

 

[CISCO]

Bonjour

Et comme ça ?

Chaque lundi, un nouveau collaborateur est tiré au hasard s'il est présent toute la semaine, et s'il n'était pas déjà d'astreinte la semaine précédente.

Il s'agit d'une formule matricielle, il faut donc la valider avec les 3 touches Ctrl+Maj+entrer.

Défauts du système :
* une personne peut très bien être d'astreinte une semaine, pas la suivante, et de nouveau d'astreinte la troisième. Si tu veux, je peux essayer de modifier la formule pour qu'il y ait forcément deux semaines entre chaque période d'astreinte, et pas uniquement une.
* Si je ne me trompe, comme il y a un tirage au hasard, les résultats seront différents à chaque ouverture du fichier. Il faut donc que lorsqu'un roulement te convient, tu le copies --> collage spécial valeurs.


@ plus

 

[chich]

Bonjour

Et comme ça ?

Chaque lundi, un nouveau collaborateur est tiré au hasard s'il est présent toute la semaine, et s'il n'était pas déjà d'astreinte la semaine précédente.

Il s'agit d'une formule matricielle, il faut donc la valider avec les 3 touches Ctrl+Maj+entrer.

Défauts du système :
* une personne peut très bien être d'astreinte une semaine, pas la suivante, et de nouveau d'astreinte la troisième. Si tu veux, je peux essayer de modifier la formule pour qu'il y ait forcément deux semaines entre chaque période d'astreinte, et pas uniquement une.
* Si je ne me trompe, comme il y a un tirage au hasard, les résultats seront différents à chaque ouverture du fichier. Il faut donc que lorsqu'un roulement te convient, tu le copies --> collage spécial valeurs.


@ plus

Re merci pour ton très ton travail
je tien a un cycle qui ne sois pas un tirage au sort et qui ne change pas a chaque ouverture du fichier
et c'est vrai qu'il faut l' astreinte revienne le moins souvent possible .
Ne peut on pas juste imbriquer ma formule d' origine ? car la on s’éloigne de mon projet

 

[CISCO]

Bonjour

Oui, effectivement, on s'éloigne de ton projet initial. Vu que je ne peux pas tester ta formule car j'ai ce satané message d'erreur, à l'ouverture du fichier et dès que je fais une modification, qui ne veut bien disparaitre qu'au bout d'une dizaine de clics sur la croix, vu que je n'y comprend pas grand chose à ta formule (J'ai essayé d'y remettre le contenu du nom suivant...)... j'ai préféré repartir à zéro en effaçant la ligne 7.

Je peux très bien te faire un roulement sans tirage au sort, comme c'était le cas dans ma première proposition, mais il faut que j'y réfléchisse un peu pour trouver la solution...

@ plus

P.S : Est-ce que tu as essayé avec SOMMEPROD(--(C11:C19<>"")) ?

 

[chich]

Bonjour

Oui, effectivement, on s'éloigne de ton projet initial. Vu que je ne peux pas tester ta formule car j'ai ce satané message d'erreur, à l'ouverture du fichier et dès que je fais une modification, qui ne veut bien disparaitre qu'au bout d'une dizaine de clics sur la croix, vu que je n'y comprend pas grand chose à ta formule (J'ai essayé d'y remettre le contenu du nom suivant...)... j'ai préféré repartir à zéro en effaçant la ligne 7.
Regarde la pièce jointe 998471

Je peux très bien te faire un roulement sans tirage au sort, comme c'était le cas dans ma première proposition, mais il faut que j'y réfléchisse un peu pour trouver la solution...

@ plus

P.S : Est-ce que tu as essayé avec SOMMEPROD(--(C11:C19<>"")) ?

re
voici ce que j'ai imbriquer =SOMMEPROD(--(C11C19={"a"."b"."c"}))

voici le formule qui génère le message d'erreur =SI(suivant+MIN(1;SOMMEPROD(DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7);{"a"."b"."c"})=6;1;suivant+MIN(1;SOMMEPROD(DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7);{"a"."b"."c"}))))
J’espère que tu va réussir a me corriger
Cordialement

 

[CISCO]

Bonjour

Je connais la formule que tu as utilisé sur la ligne 7. Le problème, c'est que je ne peux pas la tester facilement avec la fenêtre "Evaluation d'une formule", à cause du message précité, pour voir les calculs intermédiaires.

1) Est-ce indispensable de compter le nombre de cellules contenant les lettres a, b ou c ? Ne te faut-il pas plus simplement le nombre de cellules contenant quelque chose, ce que fait SOMMEPROD(--(C11:C19<>"")) ?

2) Si tu ne veux pas que les noms soient tirés au hasard, quelle méthode veux tu utiliser ? Quel ordre utilises-tu : 1, 2, 3, 4 et 5 s'il ni a pas d'absent. Mais si il y a un abs, par ex le 2, et qu'on appelle le 3 à sa place, que faut-il faire la semaine suivante : appeler le 2 s'il est libre, ou le 4 ?

En pièce jointe, une possibilité tirant les nombres dans l'ordre. Si la semaine précédente c'était le collaborateur x - 1, on essaye avec le collaborateur x. Si on ne peut pas le mettre d'astreinte parce qu'il est abs au moins un jour cette semaine là, on fait avec x+1 s'il est disponible, et ainsi de suite (x + 2...). Pour le collaborateur 5, on essaye avec le collaborateur 1.

@ plus

 

[chich]

Bonjour

Je connais la formule que tu as utilisé sur la ligne 7. Le problème, c'est que je ne peux pas la tester facilement avec la fenêtre "Evaluation d'une formule", à cause du message précité, pour voir les calculs intermédiaires.

1) Est-ce indispensable de compter le nombre de cellules contenant les lettres a, b ou c ? Ne te faut-il pas plus simplement le nombre de cellules contenant quelque chose, ce que fait SOMMEPROD(--(C11:C19<>"")) ?

2) Si tu ne veux pas que les noms soient tirés au hasard, quelle méthode veux tu utiliser ? Quel ordre utilises-tu : 1, 2, 3, 4 et 5 s'il ni a pas d'absent. Mais si il y a un abs, par ex le 2, et qu'on appelle le 3 à sa place, que faut-il faire la semaine suivante : appeler le 2 s'il est libre, ou le 4 ?

En pièce jointe, une possibilité tirant les nombres dans l'ordre. Si la semaine précédente c'était le collaborateur x - 1, on essaye avec le collaborateur x. Si on ne peut pas le mettre d'astreinte parce qu'il est abs au moins un jour cette semaine là, on fait avec x+1 s'il est disponible, et ainsi de suite (x + 2...). Pour le collaborateur 5, on essaye avec le collaborateur 1.

@ plus

Bonjour
super travail merci pour ton investissement dans mon projet
- ton cycle c'est exactement se que je veux mais je tien a compter uniquement a,b ou c qui seront renvoyés par formules dans la plage C11:C19
c'est pour ca que je voulais utiliser sommeprod au lieux nb.si ou nbval
Cordialement

 

[CISCO]

Bonjour

Tu peux faire avec SOMMEPROD((C11:I19="a")+(C11:I19="b")+(C11:I19="c")) ou avec SOMMEPROD((C11:C19="a")+(C11:C19="b")+(C11:C19="c")), avec des $ en plus si besoin est, en fonction de ce que tu veux exactement.

@ plus

 

[chich]

Bonjour

Tu peux faire avec SOMMEPROD((C11:I19="a")+(C11:I19="b")+(C11:I19="c")) ou avec SOMMEPROD((C11:C19="a")+(C11:C19="b")+(C11:C19="c")), avec des $ en plus si besoin est, en fonction de ce que tu veux exactement.

@ plus

Re
La ou est tout mon problème c'est que je
Dans ta formule tu utilise naval et moi à utiliser sommeprod à la place je n'arrive pas à faire le remplacement
Cordialement

 

[CISCO]

Bonsoir

En supprimant toutes les cellules de la plage D7: I7, j'ai fini par ne plus avoir le message d'erreur précité.

1) Pourquoi veux-tu remplacer la partie en NB.SI(DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7);"<>") dans la formule dans C7 par SOMMEPROD(--(C11:C21={"a"."b"."c"})) ?
Si dans B7, tu as par ex 5, DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7) va te donner la plage C11:I11 et pas C11:C21.
Il faut plutôt faire avec
SOMMEPROD(
DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7)="a"
+DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7)="b"
+DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7)="c") = 0.
Et comme c'est beaucoup plus long que la formule initiale, pourquoi le faire ?

2) Pourquoi fais-tu avec NB.SI(....;"<>") = 6, et pas avec NB.SI(....;"<>") = 7, puisque tu veux vérifier la présence de 7 cellules vides ?

3) Si une semaine tu as le collaborateur x - 1 d'astreinte. La semaine suivante, ta formule ne convient pas si tu as deux collaborateurs, x et x+1, absents, car elle va proposer le collaborateur x + 1.

@ plus

 

[chich]

Bonsoir

En supprimant toutes les cellules de la plage D7: I7, j'ai fini par ne plus avoir le message d'erreur précité.

1) Pourquoi veux-tu remplacer la partie en NB.SI(DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7);"<>") dans la formule dans C7 par SOMMEPROD(--(C11:C21={"a"."b"."c"})) ?
Si dans B7, tu as par ex 5, DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7) va te donner la plage C11:I11 et pas C11:C21.
Il faut plutôt faire avec
SOMMEPROD(
DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7)="a"
+DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7)="b"
+DECALER(Feuil1!C$6;4+suivant;1-JOURSEM(Feuil1!C$6;2);1;7)="c") = 0.
Et comme c'est beaucoup plus long que la formule initiale, pourquoi le faire ?

2) Pourquoi fais-tu avec NB.SI(....;"<>") = 6, et pas avec NB.SI(....;"<>") = 7, puisque tu veux vérifier la présence de 7 cellules vides ?

3) Si une semaine tu as le collaborateur x - 1 d'astreinte. La semaine suivante, ta formule ne convient pas si tu as deux collaborateurs, x et x+1, absents, car elle va proposer le collaborateur x + 1.

@ plus

Re merci merci encore pour le temps que je te prend
j'ai essayé ca mais je n'y arrive pas j' ai un fichier échantillon avec le collaborateur 1 qui
j’espère sera plus explicite

=SI(JOURSEM(I6)<>2;H7;
SI((SOMMEPROD(DECALER(I10;PLAFOND(MOD(H7+1;5,1);1);;;7))={"a"."b"."c"});PLAFOND(MOD(H7+1;5,1);1);
SI((SOMMEPROD(DECALER(I10;PLAFOND(MOD(H7+2;5,1);1);;;7))={"a"."b"."c"});PLAFOND(MOD(H7+2;5,1);1);
SI((SOMMEPROD(DECALER(I10;PLAFOND(MOD(H7+3;5,1);1);;;7))={"a"."b"."c"});PLAFOND(MOD(H7+3;5,1);1);
SI((SOMMEPROD(DECALER(I10;PLAFOND(MOD(H7+4;5,1);1);;;7))={"a"."b"."c"});PLAFOND(MOD(H7+4;5,1);1);
SI((SOMMEPROD(DECALER(I10;PLAFOND(MOD(H7+5;5,1);1);;;7))={"a"."b"."c"});PLAFOND(MOD(H7+5;5,1);1)))))))
Cordialement

 

[CISCO]

Bonjour

Il faut utiliser Excel avec logique, et non essayer un truc à droite, un truc à gauche un peu au hasard.

Dans ta formule, dans I7, tu as écris au début
=SI(JOURSEM(I6)<>2;H7;
SI((SOMMEPROD(DECALER(I10;PLAFOND(MOD(H7+1;5,1);1);;;7))={"a"."b"."c"}).
La parenthèse en rouge est mal placée
DECALER(....) te renvoie une plage allant de la colonne I à la colonne O sur une ligne dépendant de la valeur dans H7.
Dans ton exemple, avec H7 = 5, SOMMEPROD(DECALER(I10;PLAFOND(MOD(H7+1;5,1);1);;;7)) donne SOMMEPROD({"a"."b"."b"."c"."c"."".""}), qui donne 0.

Donc tu as 0 = {"a"."b"."c"}, ce qui te donne {FAUX;FAUX;FAUX}.

Si tu déplaces la parenthèse en rouge à la fin, cela donne
SI((SOMMEPROD(DECALER(I10;PLAFOND(MOD(H7+1;5,1);1);;;7)={"a"."b"."c"})). Tu obtiens
SOMMEPROD({"a"."b"."b"."c"."c"."".""} ={"a"."b"."c"}), ce qui donne {VRAI;VRAI;FAUX;#NA;#NA;#NA;#NA}). Normal, {"a"."b"."b"."c"."c"."".""} comporte 7 termes, et {"a"."b"."c"} seulement 3. Excel peut comparer le 1er terme de la matrice de gauche avec le 1er terme de la matrice de droite. Idem pour les seconds et les 3èmes. Mais cela ne peut plus fonctionner à partir du 4ème terme de la matrice de gauche. A partir de là, tu auras forcément un #NA comme résultat final

Si tu veux absolument compter uniquement les "a", les "b" et les "c", on peut faire avec le SOMMEPROD que j'ai mis dans mon dernier post, SOMMEPROD((DECALER(H10;PLAFOND(MOD(G7+1;5,1);1);;;7)="a")+(DECALER(H10;PLAFOND(MOD(G7+1;5,1);1);;;7)="b")+... ce qui te donne une formule faisant 3 km de long !!! Quel intérêt ? As-tu autre chose à écrire dans ton tableau que des a, des b et des c ?
Dans ce cas on obtient SOMMEPROD({"a"."b"."b"."c"."c"."".""} ={"a"}+{"a"."b"."b"."c"."c"."".""} ={"b"}+..., qui devient SOMMEPROD({VRAI.FAUX.FAUX.FAUX.FAUX.FAUX.FAUX}+{FAUX.VRAI.VRAI.FAUX.FAUX.FAUX.FAUX}+... On a finalement 1 + 1 + 1 +... = 5 dans l'exemple traité.

1) Cf. en pièce jointe ce que cela donne.
2) Choisis une formule de la ligne 7. Cliques sur l'onglet Formules, puis sur Evaluation de formule.
Tu obtiendras la fenêtre Evaluation de formule ci-dessous. Clique sur Evaluer et regarde les résultats intermédiaires pour comprendre le fonctionnement de la formule.

@ plus