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

[TheLio]

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

=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

 

[Wayki]

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

 

[TheLio]

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)

 

[Wayki]

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 +

 

[job75]

Bonjour TheLio, Wayki, le forum,

Définissez les noms Tableau2022 et Tableau2023 :

=DECALER('2022'!$1:$1;;;EQUIV("zzz";'2022'!$B:$B))
=DECALER('2023'!$1:$1;;;EQUIV("zzz";'2023'!$B:$B))

Formule en Stats!B6 :

=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) :

=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+

 

[chris]

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...

 

[TheLio]

Bonjour TheLio, Wayki, le forum,

Définissez les noms Tableau2022 et Tableau2023 :

=DECALER('2022'!$1:$1;;;EQUIV("zzz";'2022'!$B:$B))
=DECALER('2023'!$1:$1;;;EQUIV("zzz";'2023'!$B:$B))

Formule en Stats!B6 :

=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

 

[TheLio]

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

 

[chris]

RE

Et merci pour les corrections d'erreur

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

 

[job75]

J'ai édité mon post #5 avec une formule sans noms définis.

Bonjour chris.

 

[Wayki]

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+

 

[TheLio]

J'ai édité mon post #5 avec une formule sans noms définis.

Bonjour chris.

Hello
Merci, vous êtes vraiment incroyables
@++

 

[TheLio]

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
@++

 

[TheLio]

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

 

[chris]

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