XL 2010 Help ! compter des éléments en fonction d'une date

[Neldel]

Bonjour à tous,

J'ai un problème. Je dois reporter sur le 1er onglet d'un fichier le nombre total de personnes formées. Les données doivent être comptabilisées par ville/mois/année 2020. Sachant que le format utilisé pour marquer le mois est par exemple 31/01/20 pour le mois de janvier. En fait dernier jour de chaque mois - mais il faut comprendre 01/20, 02/20...
Sur le 2ème onglet, se trouvent toutes les données d'origine. Ces données reprennent les formations suivies par personnes par dates JJ/mm/aa. Toute période confondue.
Je joins un fichier test.

Qui peut m'aider ?

 

[JHA]

Bonjour à tous,

Un début de solution avec sommeprod()

=SOMMEPROD((Sheet2!$B$2:$B$6=$A2)*(MOD(COLONNE(Sheet2!$C$2:$H$6);2)=1)*(ANNEE(Sheet2!$C$2:$H$6)=ANNEE($B2))*(MOIS(Sheet2!$C$2:$H$6)=MOIS($B2)))

JHA

 

[pierrejean]

Bonjour Neldel
Salut JHA
Un essai

 

[Neldel]

Merci JHA et Pierre-Jean.
Pour l'instant la solution de JHA me semble plus adaptée (pas encore testée).
Pierre-Jean, j'ai l'impression que cela ne me renvoie pas les bons résultats (uniquement les formations 2020).

 

[pierrejean]

Re
Je n'ai pas fait de contrôle exhaustif mais il y a bien des 2019 !!!!

 

[Neldel]

Bonsoir JHA (et les autres),
Je suis désolée mais je n'ai jamais utilisé les fonctions MOD et COLONNE. Aussi, pourriez-vous m'indiquer à quoi correspond le 2 et le =1 qui se trouve dans la formule ? Lorsque j'essaie de l'appliquer à mon cas concret, j'ai une erreur. Du coup je pense qu'il faut peut-être que je modifie quelque chose à ce niveau car mon cas réel les formations sont détaillées en cellules E4 à BU500.
La colonne A est dédiée au nom du collaborateur, la B = pays, la C= Entité et la D=Status.

Sorry pour mon ignorance et encore merci pour vos lumières.

 

[JHA]

Bonjour à tous,

Dans ton fichier exemple tu as en "A" les collaborateurs, en "B" Non d'entité, en "C;E;G" les dates de formation et en "D;F;H" les N° de certificat .
Le but est de trouver les dates une colonne sur deux. Pour cela, j'utilise la fonction mod()
MOD(nombre, diviseur) qui renvoie le reste de la division.
MOD(COLONNE(Sheet2!$C$2:$H$6);2)=1


Les dates sont en C;E;G soit dans les colonnes 3;5;7
si on fait une division par 2 puisque toutes les deux colonnes on a
3/2 =reste 1
5/2 =reste 1
7/2 =reste 1
Comme on doit sélectionner une plage Sheet2!$C$2:$H$6; avec la fonction mod, excel sélectionnera uniquement les colonnes 3; 5; 7
si tu avais besoin de récupérer les N) de certificat, la formule serait:
MOD(COLONNE(Sheet2!$C$2:$H$6);2)=0
Les certificats sont en d;f;h soit dans les colonnes 4;6;8
si on fait une division par 2 puisque toutes les deux colonnes on a:
4/2 =reste 0
6/2 =reste 0
8/2 =reste 0
si les dates sont toutes les 2 colonnes la formule sera:
MOD(COLONNE(Sheet2!$E$4:$bU$500);2)=1
si les dates sont toutes les 3 colonnes la formule sera:
MOD(COLONNE(Sheet2!$E$4:$bU$500);3)=2

JHA

 

[Neldel]

Merci pour cette réponse rapide. J'ai tenté d'appliquer la formule sur un fichier test plus proche de la réalité. J'obtiens une erreur que je n'arrive pas à situer. Je joins ce nouveau fichier (formule entrée sur le 1er onglet). Help again !

 

[JHA]

Bonjour à tous,

A vérifier, je suis passé avec NB(si()) en matricielle

=NB(SI((Certifications!$C$4:$C$23=$A2)*(MOD(COLONNE(Certifications!$E$4:$Z$4);2)=1)*(ANNEE(Certifications!$E$4:$Z$23)=ANNEE($B2))*(MOIS(Certifications!$E$4:$Z$23)=MOIS($B2));Certifications!$E$4:$Z$23))

JHA

 

[Neldel]

Bonjour,

Cela a l'air de fonctionner, mais j'ai l'impression que quelques données ne sont pas comptabilisées. Je pense qu'il s'agit peut être d'un problème de format dans le fichier d'origine. Je vérifierai et vous tiendrai au courant.

Je vous remercie beaucoup pour votre aide et vous souhaite un excellent après-midi.

 

[Neldel]

Bonsoir JHA (et les autres membres),

Alors que la formule reportée sur mon fichier d'origine fonctionnait vendredi, j'ai eu la désagréable surprise de voir que samedi elle ne fonctionnait plus (elle ne renvoie que 0 pour résultat alors que c'est faux). Il semblerait qu'entre temps une collègue a mis à jour des données sur le fichier (fichier en réseau). Elle me dit n'avoir qu'ajouté des lignes. De ce fait cela n'aurait pas du jouer sur la formule. J'ai passé ces 3 derniers jours à rechercher le problème, sans succès. J'espère que vous pourrez m'aider à voir où cela pêche. J'ai joins un fichier qui se rapproche bien plus du fichier d'origine que celui que j'avais posté. Je suis désolée,mais je compte encore sur votre aide. La formule est sur l'onglet "Data Studio". Help ! Help ! Please. Merci à vous.

 

[JHA]

Bonjour à tous,

J'ai incorporé des plages nommées en cas de rajout de ligne.

=(NB(SI((Entity=$A2)*(MOD(COLONNE(Certifications!$F$1:$BU$1);2)=0)*(ANNEE(Tableau)=ANNEE($B2))*(MOIS(Tableau)=MOIS($B2));Tableau)))

JHA

 

[Neldel]

Merci, merci. Mon problème maintenant (j'en suis vraiment confuse, s'en est gênant), est que les plages nommées ne sont pas reconnues sous Google Sheet. J'ai droit à un "invalid data range). L'entreprise dans laquelle je travaille passe sous Google Sheet. Petite surprise de fin de semaine dernière.
J'imagine qu'il faut que je passe sous le forum Google Sheet pour avoir une solution. Si vous en avez une ici je prends... Si vous n'en avez pas, dites le moi afin que je ferme la discussion ici (pour ne pas vous poluer) et l'ouvrir sous la rubrique Google Sheet.

 

[JHA]

Bonjour à tous,

Désolé, jamais utilisé "Google Sheet".

JHA