Discussion :

[mia73]

Bonjour,


je travaille sur deux plages de données excel : une colonne contient des dates et une autre contient des références (chacune associée à une mesure électrique). Je cherche à compter le nombre de références différentespour chaque année (2000, 2001, etc.).

Voici à quoi cela ressemble :

Dates Références
01/10/2000 25661a
12/04/2000 25661a
06/09/2000 15313e
22/04/2000 15313e
05/08/2000 15611b
12/09/2001 65894k
24/02/2001 65894k
30/11/2001 54783a
etc. etc.

Dans ce cas-ci, si je veux le nombre de références différentes sur l'année 2000, je dois obtenir 3.


Je pensais utiliser NB.SI ou SOMMEPROD, mais je n'arrive pas à trouver la formule exacte à appliquer à mon problème.

Après avoir lu divers posts sur le net, j'ai testé quelques formules à tout hasard... entre autres :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTNUM(TROUVE("2001";J3:J6519));SOMMEPROD(1/NB.SI(A3:A6519;A3:A6519)))

Mais SI me retourne "FAUX", alors que j'attends un nombre entier.

Sinon faut-il créer une macro ?


Merci d'avance si quelqu'un a une idée !

[larryj]

Après quelques essais, j'ai trouvé cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=ARRONDI(SOMMEPROD((ANNEE(Dates)=2000)*1/NB.SI(References;References));0)

Elle fonctionne chez moi...

[mia73]

J'ai oublié une troisième colonne ! Il s'agit d'une colonne contenant du texte (le bâtiment sur lequel a été fait la mesure).

Pour reprendre mon exemple :

Dates Références Type
01/10/2000 25661a école du pré
12/04/2000 25661a école du pré
06/09/2000 15313e gymnase 1
22/04/2000 15313e gymnase 1
05/08/2000 15611b école jaurès
12/09/2001 65894k social
24/02/2001 65894k social
30/11/2001 54783a gymnase 2
etc. etc. etc.

Je cherche donc le nombre de références différentes par année et par type de bâtiment : exemple, en 2000, pour les écoles, on a 2 références différentes.

En espérant que çà soit suffisamment clair... n'hésitez pas à me faire part de vos suggestions si vous avez quelques idées !
Merci

[mia73]

merci jarryj
J'ai essayé ta proposition et je pense que ça devrait marcher, mais pour le moment la formule ne considère pas mes dates comme des dates (j'ai regardé le détail du calcul, à la place j'ai un nombre du style 36587 par exemple). Alors que pourtant, mes cellules sont bien en format date. Je vais essayer de comprendre ce qui se passe, en tt cas merci pour la formule.

[larryj]

Il faut faire attention aux espaces. S'il y a un espace à la suite de la date, ça peut entraîner une erreur...

N'hésite pas si jamais...

[Invité]

Bonjour,

Vous indiquez :

Dates Références Type
01/10/2000 25661a école du pré
12/04/2000 25661a école du pré
06/09/2000 15313e gymnase 1
22/04/2000 15313e gymnase 1
05/08/2000 15611b école jaurès
12/09/2001 65894k social
24/02/2001 65894k social
30/11/2001 54783a gymnase 2

Je cherche donc le nombre de références différentes par année et par type de bâtiment : exemple, en 2000, pour les écoles, on a 2 références différentes.

Si j'ai bien compris, cela voudrait dire que école du pré et école jaurès sont du même type. C'est impossible, d'autant plus que vous ne pouvez pas extraire une partie du nom, le nombre de caractères n'étant pas constant.

[mia73]

Oui les types n'ont jamais le même nom ou le même nombre de caractères mais l'intitulé commencera à chaque fois par le mot "école" pour les écoles, "gymnase" pour les gymnases, etc. N'existe-t'il pas une fonction permettant de dire "si la cellule commence par "école" alors ... " ?

Et jarryj, ta formule fonctionne très bien (en fait il fallait que je mette le format date avec l'"*"), grâce à toi je peux déjà résoudre une partie de mon pb, merci bcp !

[EmmanuelleC]

un truc comme ca?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI ( gauche(cellule,5)="école" ;"alors" ;"sinon)

[Invité]

Bien sûr, on peut utiliser :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI ( gauche(cellule,5)="école" ;"alors" ;"sinon)

Mais, combien de types différents peut-on trouver ?

[David2304]

Bonjour,

En supposant que le type recherché soit en cellule F3, cette formule devrait te convenir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ARRONDI(SOMMEPROD((ANNEE(date)=2000)*1/NB.SI(ref;ref)*(GAUCHE(type;NBCAR(F3))=F3));0)

J'ai fait le test avec le mot "école" en F3 et le résultat est bien de 2

Par contre étant donné qu'un référence à l'air de correspondre à un type précis, tu peux te passer de la colonne référence

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ARRONDI(SOMMEPROD((ANNEE(date)=2000)*1/NB.SI(type;type)*(GAUCHE(type;NBCAR(F3))=F3));0)

[mia73]

on peut trouver 6 types différents, donc je peux utiliser une formule différente pour chaque. Je vais essayer avec ça, merci !

[mia73]

Ta formule marche très bien avec l'extrait de tableau que j'ai mis sur le post mais avec toutes mes données je n'obtiens que 9 références pour les écoles alors que je devrais avoir au moins 30 références différentes. (Je préfère travailler sur les références car même si c'est rare, parfois un même type peut avoir deux références différentes). Je ne comprends pas encore le problème mais si je trouve je vous tiens au courant.

[David2304]

Vérifie tes plages nommées, elles ne sont peut-être pas assez grandes

[mia73]

En fait je n'ai pas défini de plages nommées, j'utilise directement la plage de données (A1:A6000 par exemple).

[David2304]

Bonjour,

Je ne vois pas de soucis au niveau de la formule

Peux tu mettre ton classeur en pièce jointe ? ou un exemple qui ne donne pas le bon résultat

On verra mieux d'où vient le problème

[mia73]

Je viens de remarquer quelque chose. Si j'applique la formule seulement à mes données de l'année 2000, elle fonctionne très bien. Mais si j'insère les données de 2001, le nombre diminue, et il diminue de plus en plus au fur et à mesure que je rajoute les autres années dans la sélection.
En fait on retrouve très souvent une référence plusieurs fois sur toutes les années. Exemple : 056131G peut se retrouver 1 fois en 2000, 2 fois en 2001, 2 fois en 2002, etc.

Dans l'exemple que je vous avais mis, cela donnerait :

Dates Références type
01/10/2000 2606900Q école du pré
12/04/2000 2606900Q école du pré
06/09/2000 9101403L gymnase 1
22/04/2000 9101403L gymnase 1
05/08/2000 9926400J école jaurès
12/09/2001 2002001T social
24/02/2001 2002001T social
30/11/2001 8605080W gymnase 2
20/05/2001 2606900Q école du pré
20/05/2001 9101403L gymnase 1
05/10/2001 9101403L gymnase 1
20/05/2001 9926400J école jaurès
05/10/2001 2002001T social
12/04/2001 2002001T social

Si on applique la formule pour l'école en 2000 à ce cas, on n'obtient plus 2mais seulement 1.

Je ne sais pas si vous avez une solution à ça, au pire vous m'avez déjà bien aidée et je pourrais m'en sortir en triant mes données par année.

[Invité]

Bonjour,

À mon avis, à ce stade, on ne sait plus très bien quelle est exactement la formule que vous utilisez. Il faudrait peut-être la rappeler et en l'adaptant exactement à votre dernier exemple.

[mia73]

Il s'agit de la formule de David2403

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ARRONDI(SOMMEPROD((ANNEE(date)=2000)*1/NB.SI(ref;ref)*(GAUCHE(type;NBCAR(F3))=F3));0)

avec école en F3

[David2304]

Re,

Essaye cette formule matricielle (à valider en faisant CTRL+MAJ+ENTREE)

Pour l'exemple, En F3 le type (école) et en F4 l'année (2000)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(1/NB.SI(INDIRECT("B"&MIN(SI(ANNEE(A2:A16)=F4;LIGNE(A2:A16);""))):INDIRECT("B"&MAX(SI(ANNEE(A2:A16)=F4;LIGNE(A2:A16);"")));INDIRECT("B"&MIN(SI(ANNEE(A2:A16)=F4;LIGNE(A2:A16);""))):INDIRECT("B"&MAX(SI(ANNEE(A2:A16)=F4;LIGNE(A2:A16);""))))*(GAUCHE(INDIRECT("C"&MIN(SI(ANNEE(A2:A16)=F4;LIGNE(A2:A16);""))):INDIRECT("C"&MAX(SI(ANNEE(A2:A16)=F4;LIGNE(A2:A16);"")));NBCAR(F3))=F3))

Désolé j'ai pas trouvé plus simple et pour qu'elle fonctionne, ton tableau doit être trié par date

[mia73]

Ca marche parfaitement, merci beaucoup !