Connaitre le nombre de cellule contenant tel mois et tel année

[galia74]

bonjour,

Voilà je me heurte à un problème sur une fonction Excel.

J'ai une liste de date dans une colonne sous ce format : jj-mm-aaaa

Je souhaite dans un tableau récapitulatif trouver le nombre de cellule pour chaque mois et l'année correspondante.

J'ai tapé la fonction suivante :

=SOMMEPROD((MOIS('Tableaux de bord '!$M$59:$M$1048576)=2) *(ANNEE('Tableaux de bord '!$I$59:$I$1048576)=$R$14)*1)

R14 correspond à la fonction aujourd'hui() sous le format aaaa convertir sous le format nombre mais qui me donne bien 2016 comme résultat.

Le problème est quand je fais entré ensuite dans la cellule j'ai le résultat #Valeur!

Si quelqu'un pouvais m'aider.

Merci d'avance

 

[Lolote83]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Salut GALIA74,
Je pense que tout vien de ta cellule R14.
Effectivement, même si elle contient la formule AUJOURDHUI et tes conversions, cela reste une date a part entière avec un jour, un mois et une année.
Essaye dans un premier temps de remplacer dans ta formule la valeur $R$14 par "2016"

Si cela fonctionne, il faudrait donc peut-être écrire :
=SOMMEPROD((MOIS('Tableaux de bord '!$M$59:$M$1048576)=2) *(ANNEE('Tableaux de bord '!$I$59:$I$1048576)=ANNEE(AUJOURD'HUI())*1)
Non testé
@+ Lolote83

 

[galia74]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Bonjour,

Merci pour ta réponse rapide.

Mais si je tape la formule suivante =SOMMEPROD((MOIS('Tableaux de bord '!$I$59:$I$1048576)=1)*(ANNEE('Tableaux de bord '!$I$59:$I$1048576)=ANNEE(AUJOURDHUI()))) avec tes instrcutions, j'ai toujours le même problème

 

[Lolote83]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Salut,
Sans fichier, ce n'est pas facile car on y va un peu a l'aveugle.
Comme dit dans mon dernier post, je n'ai pas testé mais normalement, cela devrait fonctionner.
Transmet un petit fichier exemple.
@+ Lolote83

 

[galia74]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Le fichier risque d'être très lourd,

J'ai du créer un système de base de donnée sous Excel avec un tableau de bord.

Mais je me suis rendue compte qu'en écrivant la fonction MOIS ou ANNEE avec pour référence une seule date, j'ai une erreur aussi dans la cellule

 

[galia74]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Mon essai se trouve dans la feuille calcul tableaubord

 

[Lolote83]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Salut,
Cette formule en P20 de l'onglet Calcul TableauBord me donne 4 comme résultat.

=SOMMEPROD((MOIS('Tableaux de bord '!$I$59:$I$1048576)=5)*(ANNEE('Tableaux de bord '!$I$59:$I$1048576)=ANNEE(AUJOURDHUI())*1))

Ce n'est pas ce que tu attendait ????
Par contre les recalculs sont extrêmement long du fait que tu prends une plage énorme. Si tu réduit tes plages, tu devrais gagner en temps de réponses.
Tu sélectionnes ne serait-ce dans la formule ci-dessus, I59:I1048576. Ne peux tu pas sélectionner que 50000 lignes. Tu en gagneras déjà 998576 et vu que tu as un nombre incalculable de cellules formulées !!!! A tester

@+ Lolote83

 

[galia74]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Bonjout,

Merci pour ta réponse, je ne comprends pas car j'ai fais un copié collé de ta formule et ça ne fonctionne pas ...

A bientôt

 

[Lolote83]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Salut GALIA74,
Comme tu peux le constater, j'ai fait 2 copies d'écran :
- 1 avec comme valeur 2016, voir ICI
- 1 avec comme valeur ANNEE(AUJOURDHUI()), voir

Tu sélectionnes ne serait-ce dans la formule ci-dessus, I59:I1048576. Ne peux tu pas sélectionner que 50000 lignes. Tu en gagneras déjà 998576 et vu que tu as un nombre incalculable de cellules formulées !!!!

De plus, afin de rendre ton fichier plus fluide, j'ai remplacé dans celui transmis ici les formules faisant appel à la valeur 1048576 par 50000 soit 1640 cellules ce qui représente quand même 1 637 664 640 calculs économisés.
Bref, voici ton fichier en retour.

 

[chris]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Bonjour

D'accord avec Lolote83 que je salue sur la quantité de données.
Pourquoi ne pas mettre la source et les données sous forme de tableau : cela évitera des formules et MFC sur des lignes vides. Elles se recopieront toutes seules lors de l'ajout de lignes et les SOMMPROD et autres INDEX/QUIV appliqués aux tableaux devraient moins poser de problème si le volume reste raisonnable.

Le calcul de l'onglet "Calcul tableaubord" pourrait aussi être fait par TCD, ce qui éviterait le calcul auto quasi impossible en cas de gros volumes.

Mais le fond du problème est que toutes tes dates sont considérées comme texte.

 

[galia74]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Bonjour,

Merci pour tout ces bons conseils. Pour le format de la date, en effet j'ai modifié pour faire en sorte que ce ne soit pas du texte mais bien le format date.

Mais pour les valeurs erreurs, je pense que c'est soit la version Excel, soit mon ordi qui beugue ... Quand j'utilise Excel 2016 sur mon ordi portable les fonctions SOMMEPROD fonctionnent très bien

Merci encore.

Bonne journée

 

[galia74]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Bonjour,

Chris pourrais-tu m'en dire un peu plus sur la forme tableau.
Je ne comprends pas très bien.

Merci d'avance

 

[chris]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Re

Excel utilise la mémoire de l'ordinateur : s'il n'en a pas assez pour calculer, il s'arrête et indique "calculer" à gauche de la barre d'état pour indiquer que le calcul n'a pu être réalisé donc selon le PC cela peut ou non marcher...

De toute façon il est totalement contreproductif de calculer sur 1 million de lignes.

Voir ici Ce lien n'existe plus et ton fichier en retour où j'ai modifié les formules des plages colorées en vert.
A mon avis les formules de colonnes D:H seraient sans doute à revoir.

 

[galia74]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Bonjour,

Merci pour ton aide. Je vois ce que tu as fais, mais si je rajoute une nouvelle requête dans mes données, elle ne s'ajoute pas à l'onglet tableau de bord, or c'est ce que j'ai besoin.

Et pour le date en format texte, finallement je n'ai pas trouvé la solution. En effet je rentre les données par un formulaire qui prend la date pour du texte. Je ne sais pas comment faire pour changer. Je suis en pleine recherche.

Merci encore

 

[chris]

Re : Connaitre le nombre de cellule contenant tel mois et tel année

Re

Pour les dates en VBA Comprendre et gérer les dates sous Excel et en VBA

Pour le tableau du Tableau de bord, effectivement je n'avais pas creusé le fait qu'il dupliquait le 1er.

Mais quel est l’intérêt ?

Le libellé du statut peut être réglé autrement (formats de nombres personnalisés + MFC) et c'est la seule colonne qui diffère...