1. Ce site utilise des "témoins de connexion" (cookies) conformes aux textes de l'Union Européenne. Continuer à naviguer sur nos pages vaut acceptation de notre règlement en la matière. En savoir plus.

=SOMMEPROD imbrique ne fonctionne pas

Discussion dans 'Forum Excel' démarrée par chich, 10 Octobre 2017.

  1. chich

    chich XLDnaute Junior

    Inscrit depuis le :
    25 Mai 2017
    Messages :
    70
    "J'aime" reçus :
    1
    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
     
  2. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9632
    "J'aime" reçus :
    302
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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.
    upload_2017-10-12_14-47-38.png

    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<>"")) ?
     
    Dernière édition: 12 Octobre 2017 à 14:59
  3. chich

    chich XLDnaute Junior

    Inscrit depuis le :
    25 Mai 2017
    Messages :
    70
    "J'aime" reçus :
    1
    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
     
  4. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9632
    "J'aime" reçus :
    302
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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
     

    Pièces jointes:

    Dernière édition: 13 Octobre 2017 à 09:36
  5. chich

    chich XLDnaute Junior

    Inscrit depuis le :
    25 Mai 2017
    Messages :
    70
    "J'aime" reçus :
    1
    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
     
  6. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9632
    "J'aime" reçus :
    302
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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
     
  7. chich

    chich XLDnaute Junior

    Inscrit depuis le :
    25 Mai 2017
    Messages :
    70
    "J'aime" reçus :
    1
    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
     
  8. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9632
    "J'aime" reçus :
    302
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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
     
    Dernière édition: 14 Octobre 2017 à 00:40
  9. chich

    chich XLDnaute Junior

    Inscrit depuis le :
    25 Mai 2017
    Messages :
    70
    "J'aime" reçus :
    1
    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
     

    Pièces jointes:

    Dernière édition: 14 Octobre 2017 à 13:10
  10. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9632
    "J'aime" reçus :
    302
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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.
    upload_2017-10-14_15-4-0.png

    @ plus
     

    Pièces jointes:

    Dernière édition: 15 Octobre 2017 à 18:17
    chich aime votre message.
  11. chich

    chich XLDnaute Junior

    Inscrit depuis le :
    25 Mai 2017
    Messages :
    70
    "J'aime" reçus :
    1
    Bonjour un grand merci ta piece jointe est exactement ce que veux super merci pour ton aide jusqu’à
    l’aboutissement.
    a tu un lien vers un tuto pour que j'apprenne a vérifier mes formules a l'avenir
     
  12. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9632
    "J'aime" reçus :
    302
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonjour

    Pour ce qui est du tuto, il faut piocher à droite, à gauche, sur le net. J'ai presque tout appris, pour ce qui est de l'utilisation des formules sur Excel, sur le forum.

    Pour ce qui est de voir les calculs intermédiaires, Formules, puis Evaluation de formule. Autre solution, surligner la partie qui t'intéresse dans la barre des formules, sans oublier les parenthèses nécessaires lorsqu'il y en a, puis F9. Esc pour revenir à l'état précédent.
    upload_2017-10-15_18-21-3.png
    @ plus
     
    chich aime votre message.

Partager cette page