XL 2016 Combiner ESTNUM EQUIV et OU

[Mikakawel]

Bonjour à tous,

Je réalise un fichier de planification qui donne une réponse OK ou KO mais avec de nombreux critères. Je cherche à réduire la taille d'une formule qui fonctionne sous cette forme:

=SI(OU(ESTNUM(EQUIV("Sensibilisation";J8:U8;0));(ESTNUM(EQUIV("QCM";J8:U8;0))));"OK";"KO")

J'aimerais réussir à avoir quelque chose du genre:

=SI(ESTNUM(EQUIV(OU("Sensibilisation";"QCM");J8:U8;0));"OK";"KO")

Auriez vous une idée géniale s'il vous plait? Je n'ai pas trouvé de sujet similaire au miens via la recherche du forum.

 

[job75]

Bonjour Mikakawel,

Un peu plus simple :

=SI(NB.SI(J8:U8;"Sensibilisation")+NB.SI(J8:U8;"QCM");"OK";"KO")

A+

 

[JHA]

Bonjour à tous, Bonjour Job75

A essayer

=SOMMEPROD(($J$8:$U$8="QCM")+($J$8:$U$8="Sensibilisation"))>0

JHA

 

[AtTheOne]

Bonjour à toutes & à tous,
Allez j'y vais de ma proposition :

=CHOISIR((SOMMEPROD(--(J8:U8={"Sensibilisation";"QCM"}))>0)+1;"KO";"OK")

À bientôt

 

[Mikakawel]

Bonjour JOB75, JHA et AtTheOne !

Avant tout un immense merci pour vos propositions, qui fonctionnent toutes les 3! Et je ne comprends pas les 2 dernières, donc j'étudie cela! Avant d'avoir d'autres réponses, je me rend compte que j'ai mal posé ma question ou tu du moins de manière trop simple:

J'essaie de décliner la formule qui sert à savoir si au moins une "Sensibilisation" ou "QCM" est réalisée par an, en semestriel, trimestriel, bimensuel (et en option ++ mensuel: mais ma formule est déjà HYPEEEER longue).

J'ai créé un fichier pour vous expliquer vraiment mon besoin que je vous envois, je pense qu'il serait intéressant de comparer toutes nos solutions (j'ai volontairement diminué mes exigence de configurations, le défi est uniquement de la longueur de formules et donc maintenance dans le temps...). L'exigence est que l'utilisateur respecte le rythme imposé par exemple par la semestrialisation: 1 sensibilisation ou QCM PAR semestre et non 2 sur le 1er trimestre...

edit: vous avez le droit de me traiter de fou ! Fichier MAJ avec ma version complete répondant à mon besoin: je cherche à réduite les formules de I surtout I6 !

 

[JHA]

Bonjour à tous,

Une autre approche

JHA

 

[AtTheOne]

Bonne nuit à toutes & à tous, bonne nuit @Mikakawel , bonne nuit @JHA

Voilà ce que j'ai compris :
En fonction du critère de criticité on doit mener, suivant une périodicité, des actions (Sensibilisation ou QCM), un minimum d'une action pour chaque période. de plus pour certaines criticités il faut parmi ces actions réaliser un nombre de QCM dans l'année (pour l'instant 0 ou 1).
Quand les conditions sont remplies, on affiche OK sinon KO.

Pour distinguer les périodes comprise dans l'année j'utilise une formule qui pondère chaque période
pour une périodicité mensuelle 1 2 4 8 16 32 64 128 256 512 1024 2048,
pour une périodicité bimestrielle 1 1 3 3 9 9 27 27 81 81 243 243
etc ...
La pondération d'une période est égale à la somme des pondérations des mois précédent plus 1
Ainsi lorsque je fait mon calcul des actions menées je sais si au moins une action a été menée dans chaque période.
Pour rendre plus lisible les formules et éviter la validation matricielle j'ai défini 4 Noms :

_Période_Mois	=SIERREUR(12/RECHERCHEV('planification 2'!$H46;Tb_Param;3;FAUX);0)	Durée en mois de chaque période en fonction de la criticité (qui se trouve en colonne H)
_Pondération_Période	=SIERREUR((_Période_Mois+1)^ENT(({1.2.3.4.5.6.7.8.9.10.11.12}-1)/_Période_Mois);0)	Pondération des périodes pour distiguer les actions menées durant chacune d'entre elle
_Cible	=SIERREUR(((_Période_Mois+1)^(12/_Période_Mois)-1)/_Période_Mois;0)	Cible à atteindre pour respecter la criticité (hors QCM requis)
_Nb_QCM_Requis	=RECHERCHEV('planification 2'!$H45;Tb_Param;6;FAUX)	Nombre de QCM Requis sur un an en fonction de la criticité.

La formule en colonne I devient ICI pour la ligne 22

=CHOISIR(2^ET(SOMMEPROD(--(J22:U22={"Sensibilisation";"QCM"})*_Pondération_Période)>=_Cible;NB.SI(J22:U22;"QCM")>=_Nb_QCM_Requis);"KO";"OK")

1. SOMMEPROD((J22:U22={"Sensibilisation";"QCM"})*_Pondération_Période)>=_Cible
   renvoie VRAI ou FAUX selon que la cible (Nombre d'action dans chaque période) est atteinte ou non
2. NB.SI(J22:U22;"QCM")>=_Nb_QCM_Requis Renvoie VRAI OU FAUX selon que le nombre de QCM requis est atteint ou non
3. 2^ET(SOMMEPROD(--J22:U22={"Sensibilisation";"QCM"})*_Pondération_Période)>=_Cible;NB.SI(J22:U22;"QCM")>=_Nb_QCM_Requis)
   Renvoie 1 ou 2 suivant que le résultat du ET() est FAUX ou VRAI (Faux est interprété comme un 0 et vrai comme un 1)
4. CHOISIR(...;"KO";"OK")
   renvoie KO Ou OK Selon le résultat (1 ou 2) du calcul précédent

Dans la fonction SOMMEPROD,
(le fait d'effectuer une multiplication convertit les résultats logiques en valeurs numériques (0 ou 1))
(J22:U22={"Sensibilisation";"QCM"}) renvoie un tableau de 2 lignes sur 12 colonnes
(la première ligne identifie par VRAI les colonnes qui contiennent Sensibilisation, la deuxième celles qui contiennent QCM)
On multiplie ce tableau par la pondération,
SOMMEPROD fait la multiplication colonne par colonne puis renvoie la somme des colonnes résultantes.
On n'a plus qu'à comparer ce résultat avec la cible

Voilà Voir le fichier en pièce jointe
Et bon courage

 

[Mikakawel]

Bonjour JHA et AtTheOne, un immense merci à tous les deux pour vos solutions développées!

J'avoue avoir appris avec chacune des deux. La dernière est bluffante d'un point de vue réduction et practicité, mais la logique était hors de ma porté ! J'ai bien fait de venir demander de l'aide.

Le fichier est finalisé, grace à vous j'ai à présent plusieurs options.

Pour la petite histoire se tableau sera mis à disposition d'un service Qualité Sécurité Environnement. Dans notre domaine prouver la plannification des sensibilisations et évalutions est très important en cas d'audit. Je vais tenter d'ajouter ma version finalisé pour apporter une option de plus (moins bonne certe).