calculer le nombre de fois un mot apparait dans plusieurs feuilles ?

[alabri]

bonjour à tous
moi j'ai fait ce type de formule et de module et pour une raison bizarre la réponse est supérieure au nombre réel
j'ai plusieurs onglets et plusieurs personnes peuvent etre indiquée dans chaque onglet.
je veux compter le nombre de fois qu'une personne est inscrite
et le résultat est bizarre, car une personne est intervenu 7 fois et le résultat est 15. j'avoue que je n'y comprend rien.
ma formule de recherche: =SOMMEPROD(NB.SI(INDIRECT("'"&J5:J25&"'!F23:I34"); A4)) validée par CTRL ALT ENTREE
J5:J25 nom des onglets
F23:I34 cellule de chaque onglet ou peuvent etre inscrit le nom des personnes intervenantes
A4 nom de la personne recherchée
ici module
Function NomsOnglets() ' fonction matricielle
Dim temp()
ReDim temp(1 To Sheets.Count)
j = 1
For i = 1 To Sheets.Count
temp(j) = Sheets(i).Name
j = j + 1
Next i
NomsOnglets = Application.Transpose(temp)
End Function
et de J5 à J25
=NomsOnglets() validée par CTRL ALT ENTREE
j'ai l'impression que le calcul me double le nombre réel
voila mon problème.
merci d'avance

 

[CISCO]

Re : calculer le nombre de fois un mot apparait dans plusieurs feuilles ?

Bonjour

Avec un petit fichier anonymé en pièce jointe, cela serait beaucoup plus simple pour nous. Merci d'avance.

@ plus

 

[job75]

Re : calculer le nombre de fois un mot apparait dans plusieurs feuilles ?

Bonsoir alabri, CISCO,

Aucune chance pour la formule avec SOMMEPROD !!!

Placez dans un module standard le code :

Function CompteNom&(nom$, onglet As Range, plage As Range)
On Error Resume Next 'si des feuilles n'existent pas
For Each onglet In onglet
CompteNom = CompteNom + Application.CountIf(Sheets(onglet.Text).Range(plage.Address), nom)
Next
End Function

Et dans la feuille où se trouve le nom en A4 et les noms d'onglets en J5:J25, entrez où vous voulez :

=CompteNom(A4;J5:J25;F23:I34)

A+

 

[job75]

Re : calculer le nombre de fois un mot apparait dans plusieurs feuilles ?

Re,

Notez que la fonction précédente ne se recalcule pas si l'on modifie les noms dans les autres feuilles.

Pour qu'elle se recalcule à chaque modification, rendons-la volatile :

Function CompteNom&(nom$, onglet As Range, plage As Range)
Application.Volatile
On Error Resume Next 'si des feuilles n'existent pas
For Each onglet In onglet
CompteNom = CompteNom + Application.CountIf(Sheets(onglet.Text).Range(plage.Address), nom)
Next
End Function

A+

 

[job75]

Re : calculer le nombre de fois un mot apparait dans plusieurs feuilles ?

Re,

Bien sûr si vous voulez tirer la cellule vers le bas pour traiter les noms en A4 A5 A6... mettez des $ :

=CompteNom(A4;J$5:J$25;F$23:I$34)

A+

 

[job75]

Re : calculer le nombre de fois un mot apparait dans plusieurs feuilles ?

Bonjour le fil, le forum,

Je ne sais pas pourquoi j'ai dit "aucune chance" pour la formule...

Sans VBA, sur Excel 2007 :

=SOMME(SIERREUR(NB.SI(INDIRECT("'"&J$5:J$25&"'!F23:I34"); A4);))

Sur Excel 2003 :

=SOMME(SI(ESTNUM(NB.SI(INDIRECT("'"&J$5:J$25&"'!F23:I34"); A4));NB.SI(INDIRECT("'"&J$5:J$25&"'!F23:I34"); A4)))

Chacune à valider par Ctrl+Maj+Entrée.

A+

 

[job75]

Re : calculer le nombre de fois un mot apparait dans plusieurs feuilles ?

Re,

Bah j'avais mal lu votre post #1, en fait vous étudiez toutes les feuilles.

La plage J5:J25 est donc inutile, utilisez cette simple fonction :

Function CompteNom&(Nom$, plage As Range)
Application.Volatile
Dim w As Worksheet
For Each w In Worksheets
CompteNom = CompteNom + Application.CountIf(w.Range(plage.Address), Nom)
Next
End Function

Et la formule :

=CompteNom(A4;$F$23:$I$34)

A+

 

[job75]

Re : calculer le nombre de fois un mot apparait dans plusieurs feuilles ?

Re,

Autre solution sans VBA.

1) Définir le nom Onglets par cette formule :

=LIRE.CLASSEUR(1)

LIRE.CLASSEUR est une fonction macro Excel 4.0.

2) S'il n'y a que des feuilles de calcul, formule :

=SOMMEPROD(NB.SI(INDIRECT("'"&Onglets&"'!F23:I34");A4))

3) s'il y a aussi des feuilles graphiques, formule matricielle sur Excel 2007 :

=SOMME(SIERREUR(NB.SI(INDIRECT("'"&Onglets&"'!F23:I34");A4);))

ou sur Excel 2003 :

=SOMME(SI(ESTNUM(NB.SI(INDIRECT("'"&Onglets&"'!F23:I34");A4));NB.SI(INDIRECT("'"&Onglets&"'!F23:I34");A4)))

Le fichier doit être en .xlsm ou .xls.

A+