XL 2016 comptage de cases contigües selon critères

[PiLVaLK]

Bonjour,
Je gère une tableau de planning pour une centaine de personnes et j'aimerais pouvoir compter le nombre de fois où mes camarades ont une activité particulière deux jours de suite.

J'y arrive sans trop de difficultés quand il s'agit de deux fois la même activité avec la formule suivante :
=NB.SI.ENS(D11:AF11;"popo";C11:AE11;"POPO")+NB.SI.ENS(D11:AF11;"coco";C11:AE11;"coco")+NB.SI.ENS(D11:AF11;"dodo";C11:AE11;"dodo")+NB.SI.ENS(D11:AF11;"toto";C11:AE11;"toto")+NB.SI.ENS(D11:AF11;"lolo";C11:AE11;"lolo")+NB.SI.ENS(D11:AF11;"bobo";C11:AE11;"bobo")
Disons que ça marche, ça m'augmente bien la valeur de sa cellule dans la colonne doublage, mais c'est moche...
Ce que j'aimerais, c'est simplifier ma formule tout en assouplissant les critères. Typiquement, la personne qui fait TOTO puis COCO devrait m'apporter aussi une valeur dans la colonne idoine. Pour que ça fonctionne, en l'état, la formule à taper est juste indigeste.
J'aimerais donc partir sur un principe de liste (B1:B6)
J'ai essayé =ESTNUM(EQUIV(C10:AF10;B1:B6;0)) mais ça ne me donne pas le bon résultat.
J'ai essayé avec NB. SI et NB.SI.ENS mais je n'arrive pas à faire simple.
Ah, et bien entendu, je souhaite ne pas utiliser VBE pour cette fonction là
Si vous aviez une petite idée ou une piste pour m'aider, je serais super content (Pas d'inquiétude, je ne suis pas Simon Jérémi et je ne vomis pas quand je suis content).
J'ai mis un fichier Excel dont j'ai enlevé une grosse partie et que j'ai anonymisé en plus de la copie d'écran.

Merci par avance, ne serait-ce que pour le temps passé à me lire !!

 

[PiLVaLK]

Bonjour,

@mapomme puisqu'il faut que les 2 EQUIV soient numériques on peut écrire :

=SOMMEPROD(--ESTNUM(EQUIV(C11:AE11;$B$1:$B$6;0)*EQUIV(D11:AF11;$B$1:$B$6;0)))

Nota : -- est nécessaire quand il y a un seul ESTNUM, pas quand il y en a 2.

A+

OK merci
La formule sommeprod est une formule que je n'avais jamais utilisée avant. Je me débrouille à peu près en VBE mais je suis plutôt mauvais en formules et je découvre au fur et à mesure.
J'ai cru comprendre qu'il y avait aussi un équivalent de from to et qu'on pouvait utiliser quelque chose comme $cell et je fouinerais de ce côté là aussi dès que possible.
Dans tous les cas, Ce forum est une bible pour moi, d'autant plus que les XLDnautes sont toujours de très bon conseil!!

 

[mapomme]

Bonjour @PiLVaLK ,

Je sens que tu es "à fond" dans ton planning contrairement à moi.
Peux-tu (si ça a du sens) ne faire un petit tableau de ce qui doit incrémenter le compteur.

 

[PiLVaLK]

Bonjour mapomme,

Effectivement, ce tableau, c'est mon "bébé", comme d'autres qu'on utilise dans mon travail et, en tant que personne très enthousiaste, j'oublie parfois que ça n'est pas parce que je comprends ce que je fais que je suis capable de l'expliquer correctement. Désolé
Voici donc le tableau que tu me demandes, en tout cas si j'ai bien compris.
Je me suis permis de faire figurer en plus les configurations impossibles histoire d'être le plus complet possible
De même, je suis allé jusqu'au mardi de s+1 pour montrer une configuration possible mais dont je ne souhaite pas qu'elle soit comptée...

 

[mapomme]

Re,

Donc, si je comprends bien (rappelle toi, il faut m'expliquer longtemps) il n'y que trois cas à compter :

• vendredi et le samedi suivant
• vendredi et le dimanche suivant
• samedi et le dimanche suivant
• tous les autres cas, on ne les retient pas et comptent pour 0

Faut-il toujours que les deux activités soient dans la liste B1:B6 ?

 

[PiLVaLK]

Oui et oui. Comme quoi, il ne faut pas t'expliquer si longtemps que ça

 

[mapomme]

Re,

Une formule un peu plus compliquée qui est la somme de trois termes (cellule AH11) :

Comptage des (vendredi, samedi) :

SOMMEPROD(ESTNUM(EQUIV(C11:AE11;$B$1:$B$6;0))*ESTNUM(EQUIV(D11:AF11;$B$1:$B$6;0))*(C$4:AE$4="VEN."))

Comptage des (samedi, dimanche) :

SOMMEPROD(ESTNUM(EQUIV(C11:AE11;$B$1:$B$6;0))*ESTNUM(EQUIV(D11:AF11;$B$1:$B$6;0))*(C$4:AE$4="SAM."))

Comptage des (vendredi, dimanche) :

SOMMEPROD(ESTNUM(EQUIV(C11:AD11;$B$1:$B$6;0))*ESTNUM(EQUIV(E11:AF11;$B$1:$B$6;0))*(C$4:AD$4="VEN."))

 

[mapomme]

Re,

Les deux premiers SOMMEPROD peuvent être regroupés en un seul ce qui raccourcit la formule finale.

=SOMMEPROD(ESTNUM(EQUIV(C11:AE11;$B$1:$B$6;0))*ESTNUM(EQUIV(D11:AF11;$B$1:$B$6;0))*(((C$4:AE$4="VEN.")+(C$4:AE$4="SAM."))>0 ))

Voir cellule ai11 dans le fichier joint.

 

[PiLVaLK]

RE, Mapomme,
Je regarde ça et je reviens vers toi

 

[PiLVaLK]

MERCI Mapomme !!
J'ai utilisé tes formules qui m'ont permis de faire le travail de façon nominale. J'ai ensuite ajouté une partie des miennes qui permettent donc de prendre en compte les deux derniers jours du mois précédent s'ils sont un vendredi et/ou un samedi. Ensuite mes responsables de planning m'ont dit :" et les gens qui doublent, on peut ne pas les compter dans les nuits "uniques". Bon là, ça a été j'ai simplement retranché du décompte des nuits uniques les formules que tu m'as proposées en fonction des cas.
Bref, c'est une histoire qui roule... Jusqu'à leur prochaine bonne idée
Je vais farfouiller un peu dans le "sommeprod" qui me parait TRES utile.
Merci encore. J'espère pouvoir aider les autres à mon tour et en fonction de mes modestes capacités quand j'aurai un peu progressé.

 

[mapomme]

Bref, c'est une histoire qui roule... Jusqu'à leur prochaine bonne idée

Suite au prochain numéro. L’imagination des utilisateur est sans limite ...

 

[PiLVaLK]

Ca c'est sûr !!