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

TheLio · 24 Janvier 2023

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 :

Sur l'onglet Stats
Comptabiliser dans différents onglets [2022 & 2023] le nombre de "lettres" sur la ligne correspondant au nom de la personne
J'ai essayé plusieurs fois d'expliquer qu'il ne fallait pas supprimer des lignes ou en rajouter, mais le personnel fluctuant, ....
Bref, le N° de ligne peut varier selon l'onglet

Par avance je vous remercie pour votre précieuse aide
@++
Lio

Wayki · 24 Janvier 2023

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 · 25 Janvier 2023

Wayki à dit:
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 · 25 Janvier 2023

TheLio à dit:
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 · 25 Janvier 2023

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+

chris · 25 Janvier 2023

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 · 25 Janvier 2023

job75 à dit:

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

TheLio · 25 Janvier 2023

chris à dit:
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 · 25 Janvier 2023

RE

TheLio à dit:
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 · 25 Janvier 2023

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

Bonjour chris.

Wayki · 25 Janvier 2023

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 · 26 Janvier 2023

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

Bonjour chris.

Hello
Merci, vous êtes vraiment incroyables
@++

TheLio · 26 Janvier 2023

Wayki à dit:
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 · 26 Janvier 2023

chris à dit:
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 · 26 Janvier 2023

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

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

TheLio

XLDnaute Accro

Pièces jointes

Wayki

XLDnaute Impliqué

Pièces jointes

TheLio

XLDnaute Accro

Wayki

XLDnaute Impliqué

job75

XLDnaute Barbatruc

Pièces jointes

chris

XLDnaute Barbatruc

Pièces jointes

TheLio

XLDnaute Accro

TheLio

XLDnaute Accro

chris

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Wayki

XLDnaute Impliqué

TheLio

XLDnaute Accro

TheLio

XLDnaute Accro

TheLio

XLDnaute Accro

chris

XLDnaute Barbatruc

Discussions similaires