Microsoft 365 NB.SI.ENS et numéro de ligne variable sur d'autres onglets

  • Initiateur de la discussion Initiateur de la discussion TheLio
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

TheLio

XLDnaute Accro
Bonjour à toutes et à tous, bonjour le forum,
"Long time no see" comme disait quelqu'un dont j'ai oublié le nom.
En fichier joint, une requête sur la formule NB.SI.ENS

Code:
=SI($D$2="X";(NB.SI.ENS('2022'!6:6;B$5;'2022'!$5:$5;">="&$B$2;'2022'!$5:$5;"<="&$C$2));0)+SI($D$4="X";(NB.SI.ENS('2023'!6:6;B$5;'2023'!$5:$5;">="&$B$4;'2023'!$5:$5;"<="&$C$4));0)
La fonction choisie n'est peut-être pas la bonne, mais voilà ce que je cherche à faire :
  1. Sur l'onglet Stats
  2. Comptabiliser dans différents onglets [2022 & 2023] le nombre de "lettres" sur la ligne correspondant au nom de la personne
  3. J'ai essayé plusieurs fois d'expliquer qu'il ne fallait pas supprimer des lignes ou en rajouter, mais le personnel fluctuant, ....
  4. Bref, le N° de ligne peut varier selon l'onglet
Par avance je vous remercie pour votre précieuse aide
@++
Lio
 

Pièces jointes

Bonjour,
Avant d'aller plus loin, voulez vous le nombre de V du 1/01 au 31/12 ou bien sur la ligne entière ?
Pouvez vous vérifier le résultat de D6 du fichier joint ? Merci
A +
Édit : J'ai rajouté avec le calcul de 2023
 

Pièces jointes

Dernière édition:
Bonjour,
Avant d'aller plus loin, voulez vous le nombre de V du 1/01 au 31/12 ou bien sur la ligne entière ?
Pouvez vous vérifier le résultat de D6 du fichier joint ? Merci
A +
Édit : J'ai rajouté avec le calcul de 2023
Hello,
Je te remercie pour ta réponse.
  • Effectivement la fonction EQUIV va me donner le N° de ligne.
  • Ensuite, j'ai opté pour la fonction NB.SI.ENS qui me permet de faire le choix entre des plages de dates données. >="&$D$2;'2022'!$5:$5;"<="&$E$2
  • Ce qui me manque, c'est pouvoir, insérer le N° de ligne correspondant au candidat
=SI($D$2="X";(NB.SI.ENS('2022'!6:6;B$5;'2022'!$5:$5;">="&$B$2;'2022'!$5:$5;"<="&$C$2));0)+SI($D$4="X";(NB.SI.ENS('2023'!6:6;B$5;'2023'!$5:$5;">="&$B$4;'2023'!$5:$5;"<="&$C$4));0)
 
Hello,
Je te remercie pour ta réponse.
  • Effectivement la fonction EQUIV va me donner le N° de ligne.
  • Ensuite, j'ai opté pour la fonction NB.SI.ENS qui me permet de faire le choix entre des plages de dates données. >="&$D$2;'2022'!$5:$5;"<="&$E$2
  • Ce qui me manque, c'est pouvoir, insérer le N° de ligne correspondant au candidat
=SI($D$2="X";(NB.SI.ENS('2022'!6:6;B$5;'2022'!$5:$5;">="&$B$2;'2022'!$5:$5;"<="&$C$2));0)+SI($D$4="X";(NB.SI.ENS('2023'!6:6;B$5;'2023'!$5:$5;">="&$B$4;'2023'!$5:$5;"<="&$C$4));0)
Bonjour,
Effectivement j'avais pas décelé le choix de la plage de dates, ma fonction somme est matricielle nous pouvons rajouter toutes les conditions que l'on veut, je rectifie ça quand j'ai 5 min.
A +
 
Bonjour TheLio, Wayki, le forum,

Définissez les noms Tableau2022 et Tableau2023 :
Code:
=DECALER('2022'!$1:$1;;;EQUIV("zzz";'2022'!$B:$B))
=DECALER('2023'!$1:$1;;;EQUIV("zzz";'2023'!$B:$B))
Formule en Stats!B6 :
Code:
=SIERREUR(SI($D$2="x";NB.SI.ENS(INDEX(Tableau2022;EQUIV($A6;INDEX(Tableau2022;;2);0););B$5;INDEX(Tableau2022;5;);">="&$B$2;INDEX(Tableau2022;5;);"<="&$C$2));0)+SIERREUR(SI($D$4="x";NB.SI.ENS(INDEX(Tableau2023;EQUIV($A6;INDEX(Tableau2023;;2);0););B$5;INDEX(Tableau2023;5;);">="&$B$4;INDEX(Tableau2023;5;);"<="&$C$4));0)
Edit : bien sûr on peut se passer des noms définis et écrire directement en Stats!B6, fichier (2) :
Code:
=SIERREUR(SI($D$2="x";NB.SI.ENS(DECALER('2022'!$1:$1;EQUIV($A6;'2022'!$B:$B;0)-1;);B$5;'2022'!$5:$5;">="&$B$2;'2022'!$5:$5;"<="&$C$2));0)+SIERREUR(SI($D$4="x";NB.SI.ENS(DECALER('2023'!$1:$1;EQUIV($A6;'2023'!$B:$B;0)-1;);B$5;'2023'!$5:$5;">="&$B$4;'2023'!$5:$5;"<="&$C$4));0)
A+
 

Pièces jointes

Dernière édition:
Bonjour à tous

Il y a des erreurs de calcul dans les tableaux sources, M avec ou sans espace, le nom de l'homme libre avec un è qui traîne...

J'ai corrigé la dernière anomalie et traité par PowerQuery (intégré à Excel) + TCD

Il suffit d'actualiser le TCD quand on modifie ou bien la source ou bien les choix...
 

Pièces jointes

Dernière édition:
Bonjour TheLio, Wayki, le forum,

Définissez les noms Tableau2022 et Tableau2023 :
Code:
=DECALER('2022'!$1:$1;;;EQUIV("zzz";'2022'!$B:$B))
=DECALER('2023'!$1:$1;;;EQUIV("zzz";'2023'!$B:$B))
Formule en Stats!B6 :
Code:
=SIERREUR(SI($D$2="x";NB.SI.ENS(INDEX(Tableau2022;EQUIV($A6;INDEX(Tableau2022;;2);0););B$5;INDEX(Tableau2022;5;);">="&$B$2;INDEX(Tableau2022;5;);"<="&$C$2));0)+SIERREUR(SI($D$4="x";NB.SI.ENS(INDEX(Tableau2023;EQUIV($A6;INDEX(Tableau2023;;2);0););B$5;INDEX(Tableau2023;5;);">="&$B$4;INDEX(Tableau2023;5;);"<="&$C$4));0)
A+
Hello Job75
Merci pour ta réponse et la formule, elle fonctionne parfaitement (comme d'hab 😉)
@++
Lio
 
Bonjour à tous

Il y a des erreurs de calcul dans les tableaux sources, M avec ou sans espace, le nom de l'homme libre avec un è qui traîne...

J'ai corrigé la dernière anomalie et traité par PowerQuery (intégré à Excel) + TCD

Il suffit d'actualiser le TCDquand on modifie ou bien la source ou bien les choix...
Hello Chris
Merci pour la solution en TCD, j'aurai dû/pu y penser.
Je vais voir quel est la solution qui convient le mieux aux utilisateurs finaux (Direction et secrétariat)
Et merci pour les corrections d'erreur (je vais limiter le choix en écriture par "validation de données=> listes" ce qui empêchera les mauvaises manipulations.
@++
Lio
 
Bonjour le Fil,
Même si ce n'est plus nécessaire, voici la formule finie, en oneshot plus besoin des colonnes "lignes" que j'avais faite.
=SI($D$2="x";SOMME((INDIRECT("'2022'!C"&EQUIV($A6;'2022'!$B$1:$B$100;0)&":NC"&EQUIV($A6;'2022'!$B$1:$B$100;0))=Stats!B$5)*('2022'!$C$5:$NC$5>=N($B$2))*('2022'!$C$5:$NC$5<=N($C$2))*1);0)+SI($D$4="x";SOMME((INDIRECT("'2023'!C"&EQUIV($A6;'2023'!$B$1:$B$100;0)&":NC"&EQUIV($A6;'2023'!$B$1:$B$100;0))=B$5)*('2023'!$C$5:$NC$5>=N($B$4))*('2023'!$C$5:$NC$5<=N($C$4))*1);0)
Formule matricielle à insérer en B6, à valider par ctrl+maj+entrée et étirable.
Ne traite pas les erreurs de casse qui ont pu être relevées dans les posts.
Bonne journée,
A+
 
Bonjour le Fil,
Même si ce n'est plus nécessaire, voici la formule finie, en oneshot plus besoin des colonnes "lignes" que j'avais faite.

Formule matricielle à insérer en B6, à valider par ctrl+maj+entrée et étirable.
Ne traite pas les erreurs de casse qui ont pu être relevées dans les posts.
Bonne journée,
A+
Hello,
Je me répète,
Merci, vous êtes vraiment incroyables
@++
 
RE

Je n'ai pas corrigé les erreurs de calculs des sources : je n'ai utilisé que les colonnes A à NC

Si on utilise le TCD on peut donc supprimer les colonnes ND à UQ des 2 sources qui contiennent les erreurs
Re-bonjour le fil, le forum, Wayki, job75, chris,
Effectivement, ces colonnes avaient été crées par un/une collègue.
C'est pour cela que j'ai crée cette page unique de stats. qui est plus lisible.
Petite question pour ma culture personnelle (j'avais décelé des erreurs de frappes (espaces) liées à la saisie, je sais comment les supprimer toutes (un simple contrôle H) fait l'affaire. Existe t'il un autre moyen d'épurer ?
Et encore merci à tous pour les solutions apportées
 
Bonjour

Pour le M c'est fait dans PowerQuery.
On peut aussi le faire sur les noms : virer les espaces de début/fin

Mais si des accents sont tantôt là, tantôt absents c'est plus compliqué mais faisable

De façon générale le traitement de données dépend de la qualité de celles-ci : on dit trivialement
Mer.e in, mer.e out
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Retour