Recherche une formule pour savoir le nbr de poste ouvert / jour

Question

Je sèche sur une formule... un petit coup de pouce svp... Je cherche une formule qui me compte le nombre de poste ouvert par jours. 3 Poste possible M = Matin A = Après-midi N = Nuit Je vous joint mon planning pour mieux comprendre. Merci d'avance pour vos retour...

tulipe_4 · Answer

=7-SOMMEPROD(--ESTNUM(CHERCHE({"CP"."MAL"."AT"."CHP"."RH"."RTT"};D$6:D$12))) a tester cordialement...

LouReeD · Answer

Un essai : =NB.SI(D$6:D$12;"M")+NB.SI(D$6:D$12;"N")+NB.SI(D$6:D$12;"A") @ bientôt LouReeD Bonsoir tulipe_4...

MFerrand · Answer

Une autre, inspirée par Tulipe =SOMMEPROD(--ESTNUM(CHERCHE(D6:D12;"AMNJ")))-NB.VIDE(D6:D12) Cordialement...

LouReeD · Answer

Un petit mot pour vous dire au revoir... (pas) @ bientôt LouReeD...

MFerrand · Answer

Houlà donc ! Que t'arrive-t-il ...

MFerrand · Answer

Ce n'était effectivement pas clair ! Mais alors J, c'est quoi ? C'est : aucun poste ...

MFerrand · Answer

Si j'ai bien compris maintenant, je ferais ceci : =SOMME(--(FREQUENCE(SIERREUR(EQUIV(D6:D12;{"M";"A";"N"};0);0);LIGNE(1:4)-1)&gt;0))-1 Matricielle (valider par Ctrl+Maj+Entrée) Cordialement...

tulipe_4 · Answer

Je crois qu'en modifiant les paramètres de telle ou telle formules proposées ,tu vas trouver ton bonheur Ce qui me turlupine c'est le message de Lou cordialement...

MFerrand · Answer

Toujours un peu compliqué avec ce type de formule :

=SOMME(--(FREQUENCE(SIERREUR(EQUIV(D6:D12;{"M";"A";"N"};0);0);LIGNE(1:4)-1)>0))-1

Il faut rappeler que tu voulais savoir si les postes désignés par les lettres M, A ou N sont ou non occupés un jour donné : si M, A ou N apparaît une fois ou plusieurs sur une journée (journée=colonne de ligne 6 à 12), le poste est occupé... On aura donc 3 postes au maximum occupés, sinon 2 ou 1 ou 0...

La fonction EQUIV permet de faire une recherche dans une matrice, et elle renvoie le rang de l'élément trouvée.

Nous allons donc l'utiliser pour chercher dans une matrice que nous construisons ainsi : {"M";"A";"N"}

Soit une matrice composée des lettres M, A et N, disposées verticalement : les accolades sont utilisées pour indiquer que ce qui est entre elles est une matrice de constantes, les point-virgules sont le séparateurs d'éléments d'une matrice verticale, le point est le séparateur d'éléments d'une matrice horizontale [NB- on désigne aussi de telles matrices sous le nom de vecteur ligne ou colonne, quand elle n'ont qu'une dimension ; le fait qu'on la définisse comme horizontale ou verticale est sans incidence dans cette formule].

Si donc on recherche une valeur dans cette matrice composée de 3 éléments : si la valeur correspond à l'un des éléments, EQUIV renverra 1, 2, ou 3 selon le rang de l'élément trouvé, et s'il ne trouve pas il renverra #N/A.

Avec cette partie de la formule : EQUIV(D6:D12;{"M";"A";"N"};0), on ne se contente pas de faire chercher une valeur, mais un ensemble de valeurs, figurant dans une plage de 7 cellules en colonne.

On recherche des valeurs exactes dans la matrice à 3 éléments (3e argument de la fonction = 0). EQUIV va donc nous renvoyer une matrice résultat de 7 éléments qui pourront être 1, 2, 3 ou #N/A.

Naturellement, dès lors qu'on a une valeur d'erreur, celle-ci peut poser problème et empêcher d'obtenir le résultat voulu. Il faut donc éliminer cette valeur d'erreur...

Ce que l'on fait en mettant ce fragment de formule sous SIERREUR : SIERREUR(EQUIV(D6:D12;{"M";"A";"N"};0);0)

Ainsi quand le résultat sera #N/A, SIERREUR le remplacera par 0. On aura donc une matrice trouvée composée de 0 ou 1 ou 2 ou 3.

Je rappelle que ce que l'on cherche, c'est savoir s'il y a des 1 ou non, des 2 ou non, des 3 ou non, sans se préoccuper de leur nombre.

La fonction FREQUENCE nous permet de traiter une matrice numérique en comptant les valeurs de la matrice réparties par intervalles : il nous faut donc définir des intervalles permettant de séparer les 4 valeurs trouvées.

C'est l'expression : LIGNE(1:4)-1 qui va nous permettre de définir les intervalles voulus : - 0 - 1 - 2 - 3 -

Ce qui nous définit de fait 5 intervalles, les bornes couvrant les valeurs <= à la borne et le dernier intervalle étant constitué par les valeurs supérieures à la dernière borne. Comme l'on ne peut avoir de valeur supérieure à 3, les valeurs trouvées seront dénombrées dans 4 intervalles.

FREQUENCE va donc nous renvoyer à partir de la matrice trouvée précédemment, une matrice de 5 éléments (intervalles) dont un sera nécessairement à 0 et les 4 autres peuvent avoir une valeur supérieure à 0.

N'étant pas intéressé par le nombre on va donc se contenter de tester si ces éléments sont >0.

(FREQUENCE(MatriceTrouvée;LIGNE(1:4)-1>0) va donc nous renvoyer une matrice de 5 valeurs VRAI ou FAUX [au maximum 4 VRAI et au minimum 1 VRAI (voir remarque à la fin sur ce point)]

En faisant précéder l'expression de l'opérateur : -- qui a pour effet de transformer les VRAI en 1 et les FAUX en 0 et en faisant la somme de cette matrice transformée (avec SOMME) on va donc compter le nombre de VRAI.

On considère (peut-être à tort ?) qu'il y aura toujours des valeurs autres que M, A ou N dans la colonne visée par la formule, et que en conséquence l'intervalle 0 aura toujours un résultat supérieur à 0, et que l'on peut donc retirer 1 systématiquement de la somme finale pour obtenir le résultat cherché...

Si ce n'était pas toujours le cas et qu'il arrive qu'aucune valeur sur les 7 ne soit différente de M ou A ou N, il faudrait procéder autrement qu'en retirant simplement 1 (ne le retirer que sur test, qui allongerait pas mal la formule... ou bien en pouvant inclure dans la plage une cellule supplémentaire dont on peut être sûr qu'elle produira une valeur 0 [ce qui alors n'allongerait pas du tout la formule et la fiabiliserait]).

Comme on l'a vu, faisant travailler toutes nos fonctions avec des matrices, on n'échappe pas au caractère matriciel de la formule, et à la nécessité de la valider matriciellement.

[Comme toujours, c'est plus long à expliquer qu'à faire... ! ]

Cordialement.

MFerrand · Answer

Merci du retour... J'ai craint un moment t'avoir enseveli sous une surabondance d'explications ! Bonne fin d'année...