Additionner des zéros dans 38 classeurs :-)

LSteph

24/08/2007 à 23:11

Bonsoir Pierre,

Aurais-je plus de chance en mettant mes 38 feuilles dans un même
classeur
Peut-être site de LL

Morefun.xll

=NB.SI.3D(Feuil38:Feuil1!F5;0)

Cordialement.

lSteph

Bonjour à toutes et tous;

J'ai 38 classeurs !! tous identiques au niveau de leur structure (1
seule feuille)

Chaque cellule des plages F5:AX1000 de chacun d'eux contient soit un 0
(zéro) soit un nombre entier positif soit rien ("").

Je souhaiterais dans un classeur récapitulatif que chacune des cellules
de la plage F5:AX1000 elle comptabilise le nombre total de 0 des
cellules de même nom des 38 classeurs.

Est-ce possible??

C'est une espèce de nb.si(????;"0") inter-classeur.

Aurais-je plus de chance en mettant mes 38 feuilles dans un même
classeur ??

Merci.

Cordialement,
Pierre F.

JB

25/08/2007 à 06:12

Bonjour,

C2:C6 contient les noms des onglets

=SOMMEPROD(NB.SI(INDIRECT($C$2:$C$6&"!A2:A10");"0"))

Si noms de feuilles génériques:

=SOMMEPROD(NB.SI(INDIRECT("feuil"&LIGNE(INDIRECT("1:5"))&"!
A2:A10");"0"))

http://boisgontierjacques.free.fr/pages_site/sommeprod.htm#NbSi3d

JB

On 24 août, 22:35, "Pierre F." wrote:

Bonjour à toutes et tous;

J'ai 38 classeurs !! tous identiques au niveau de leur structure (1
seule feuille)

Chaque cellule des plages F5:AX1000 de chacun d'eux contient soit un 0
(zéro) soit un nombre entier positif soit rien ("").

Je souhaiterais dans un classeur récapitulatif que chacune des cellules
de la plage F5:AX1000 elle comptabilise le nombre total de 0 des
cellules de même nom des 38 classeurs.

Est-ce possible??

C'est une espèce de nb.si(????;"0") inter-classeur.

Aurais-je plus de chance en mettant mes 38 feuilles dans un même classe ur ??

Merci.

Cordialement,
Pierre F.

JB

25/08/2007 à 08:45

En VBA:

Application.ScreenUpdating = False
For Each classeur In Array("classeur1.xls", "classeur2.xls")
Chemin = ThisWorkbook.Path
Workbooks.Open Filename:=Chemin & "" & classeur
zéro = zéro + Application.CountIf([A2:A10], "0")
ActiveWorkbook.Close False
Next classeur
[A1] = zéro
End Sub

JB

On 25 août, 06:12, JB wrote:

Bonjour,

C2:C6 contient les noms des onglets

=SOMMEPROD(NB.SI(INDIRECT($C$2:$C$6&"!A2:A10");"0"))

Si noms de feuilles génériques:

=SOMMEPROD(NB.SI(INDIRECT("feuil"&LIGNE(INDIRECT("1:5"))&"!
A2:A10");"0"))

http://boisgontierjacques.free.fr/pages_site/sommeprod.htm#NbSi3d

JB

On 24 août, 22:35, "Pierre F." wrote:

Bonjour à toutes et tous;

J'ai 38 classeurs !! tous identiques au niveau de leur structure (1
seule feuille)

Chaque cellule des plages F5:AX1000 de chacun d'eux contient soit un 0
(zéro) soit un nombre entier positif soit rien ("").

Je souhaiterais dans un classeur récapitulatif que chacune des cellul es
de la plage F5:AX1000 elle comptabilise le nombre total de 0 des
cellules de même nom des 38 classeurs.

Est-ce possible??

C'est une espèce de nb.si(????;"0") inter-classeur.

Aurais-je plus de chance en mettant mes 38 feuilles dans un même clas seur ??

Merci.

Cordialement,
Pierre F.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Pierre F.

25/08/2007 à 13:08

En VBA:

Application.ScreenUpdating = False
For Each classeur In Array("classeur1.xls", "classeur2.xls")
Chemin = ThisWorkbook.Path
Workbooks.Open Filename:=Chemin & "" & classeur
zéro = zéro + Application.CountIf([A2:A10], "0")
ActiveWorkbook.Close False
Next classeur
[A1] = zéro
End Sub

Merci pour les réponses de LSteph et JB

Ce problème est complexe et il faut que je réfléchisse pour trouver la
solutions la moins "gourmande"

En fait, je veux compter le nombre 0 de la cellule F5 dans 38 classeurs
différents (mais de même structure et ne comportant qu'une feuille
chacun)... et faire la même chose pour environ 37000 autres cellules !!

Merci.

Cordialement,
Pierre F.

Hervé

25/08/2007 à 19:36

Bonsoir Pierre,
Avec VBA.
Les classeurs peuvent restés fermés mais doivent tous être dans le même
dossier.
Colle tout le code ci-dessous dans un module standard du classeur
récapitulatif, adapte :
- le chemin de ton dossier
- le nom des feuilles, qui doit impérativement être le même pour tous les
classeurs
- la plage de recherche (F5:AX1000 si je ne me trompe pas)
et exécute la proc "RecupValeurs".
Elle fonctionne de la manière suivante :
1- récupère le chemin de tous les classeurs
2- utilise ADO pour s'y connecter
3- récupère les valeurs dans un tableau à 3 dimensions, la dimension Z étant
le nombre de classeurs
4- compte les 0 de chaque cellules dans la dimension Z (chaque classeur) et
en additionne le nombre
5- inscrit le résultat dans les cellules correspondantes.

Je n'ai testé que sur tois classeurs et sur une plage de 20 cellules donc je
ne connais pas le temp de traitement :o((, à toi de voir si cela te convient
:

Private Sub ConnectCLasseur(ConnectCL As Object, _
Fichier As String, _
Optional Rs)

Set ConnectCL = CreateObject("ADODB.Connection")
If Not IsMissing(Rs) Then
Set Rs = CreateObject("ADODB.Recordset")
End If

ConnectCL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Fichier & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;IMEX= 2;"""
End Sub

Sub RecupValeurs()
Dim ConnectCL As Object
Dim Rs As Object
Dim Champ As Object
Dim Chemin As String
Dim Classeur As String
Dim NomFeuille As String
Dim Plage As String
Dim TblClasseur() As String
Dim TblValeur()
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim T As Integer

'chemin où vont être récupérés les
'classeurs, A ADAPTER
Chemin = "D:Mon Dossier"

'retourne un tableau contenant tous
'les classeurs du dossier
TblClasseur = Fichiers(Chemin, J)

'si aucun classeur dans le dossier, fin
'J est passé en Ref. et sa valeur est ensuite testée
If J = 0 Then
MsgBox "Ancun classeur ne se trouve dans ce dossier !"
Exit Sub
End If

'feuille ou s'effectuera la récup, A ADAPTER
NomFeuille = "Feuil1"
'plage ou s'effectuera la récup, A ADAPTER
Plage = "F5:AX1000"

For I = 1 To UBound(TblClasseur)
'chemin du classeur cible
Classeur = TblClasseur(I)
'connecxion
ConnectCLasseur ConnectCL, Classeur, Rs

J = 0
K = 0
'récupère les valeurs dans les classeurs
'et les stocke dans un tableau à 3 dimensions
With Rs
.CursorType = 1
.LockType = 3
.Open "SELECT * FROM `" & NomFeuille & "$" & _
Plage & "` ", ConnectCL
.MoveFirst
ReDim Preserve TblValeur(1 To .Fields.Count, _
1 To UBound(TblClasseur), _
1 To .RecordCount)
Do While Not .EOF
K = K + 1
For Each Champ In .Fields
J = J + 1
TblValeur(J, I, K) = Champ.Value
Next
J = 0
.MoveNext
Loop
End With

ConnectCL.Close
Next I

'récupére les valeurs stockées dans le tableau
For I = 1 To UBound(TblValeur, 3) 'Lignes
'TblValeur(Colonnes, Classeurs, Lignes)
For J = 1 To UBound(TblValeur, 1) 'Colonnes
For K = 1 To UBound(TblValeur, 2) 'Classeurs
'compte le nombre de zéros (direction Z)
If TblValeur(J, K, I) = 0 Then
T = T + 1
End If
Next K
'inscrit le total dans la cellule correspondante
Cells(I + 5, J + 6) = T
T = 0
Next J
Next I

Erase TblClasseur
Erase TblValeur
Set Rs = Nothing
Set ConnectCL = Nothing
End Sub

Private Function Fichiers(Dossier As String, _
J As Integer) As String()
Dim TableauFichiers() As String
'J est passé en Ref. et si il est incrémenté,
'le dossier contient bien des classeurs
'sinon, le dossier est vide de classeurs
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = Dossier
If .Execute > 0 Then
For J = 1 To .FoundFiles.Count
ReDim Preserve TableauFichiers(1 To J)
TableauFichiers(J) = .FoundFiles(J)
Next J
End If
End With
Fichiers = TableauFichiers()
End Function

Hervé.

"Pierre F." a écrit dans le message de news:
ed1f8$46cf4118$55da2ea5$

Bonjour à toutes et tous;

J'ai 38 classeurs !! tous identiques au niveau de leur structure (1
seule feuille)

Chaque cellule des plages F5:AX1000 de chacun d'eux contient soit un 0
(zéro) soit un nombre entier positif soit rien ("").

Je souhaiterais dans un classeur récapitulatif que chacune des cellules
de la plage F5:AX1000 elle comptabilise le nombre total de 0 des
cellules de même nom des 38 classeurs.

Est-ce possible??

C'est une espèce de nb.si(????;"0") inter-classeur.

Aurais-je plus de chance en mettant mes 38 feuilles dans un même classeur
??

Merci.

Cordialement,
Pierre F.

Bonsoir Pierre,
Avec VBA.
Les classeurs peuvent restés fermés mais doivent tous être dans le même
dossier.
Colle tout le code ci-dessous dans un module standard du classeur
récapitulatif, adapte :
- le chemin de ton dossier
- le nom des feuilles, qui doit impérativement être le même pour tous les
classeurs
- la plage de recherche (F5:AX1000 si je ne me trompe pas)
et exécute la proc "RecupValeurs".
Elle fonctionne de la manière suivante :
1- récupère le chemin de tous les classeurs
2- utilise ADO pour s'y connecter
3- récupère les valeurs dans un tableau à 3 dimensions, la dimension Z étant
le nombre de classeurs
4- compte les 0 de chaque cellules dans la dimension Z (chaque classeur) et
en additionne le nombre
5- inscrit le résultat dans les cellules correspondantes.

Je n'ai testé que sur tois classeurs et sur une plage de 20 cellules donc je
ne connais pas le temp de traitement :o((, à toi de voir si cela te convient
:

Private Sub ConnectCLasseur(ConnectCL As Object, _
Fichier As String, _
Optional Rs)

Set ConnectCL = CreateObject("ADODB.Connection")
If Not IsMissing(Rs) Then
Set Rs = CreateObject("ADODB.Recordset")
End If

ConnectCL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Fichier & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;IMEX= 2;"""
End Sub

Sub RecupValeurs()
Dim ConnectCL As Object
Dim Rs As Object
Dim Champ As Object
Dim Chemin As String
Dim Classeur As String
Dim NomFeuille As String
Dim Plage As String
Dim TblClasseur() As String
Dim TblValeur()
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim T As Integer

'chemin où vont être récupérés les
'classeurs, A ADAPTER
Chemin = "D:Mon Dossier"

'retourne un tableau contenant tous
'les classeurs du dossier
TblClasseur = Fichiers(Chemin, J)

'si aucun classeur dans le dossier, fin
'J est passé en Ref. et sa valeur est ensuite testée
If J = 0 Then
MsgBox "Ancun classeur ne se trouve dans ce dossier !"
Exit Sub
End If

'feuille ou s'effectuera la récup, A ADAPTER
NomFeuille = "Feuil1"
'plage ou s'effectuera la récup, A ADAPTER
Plage = "F5:AX1000"

For I = 1 To UBound(TblClasseur)
'chemin du classeur cible
Classeur = TblClasseur(I)
'connecxion
ConnectCLasseur ConnectCL, Classeur, Rs

J = 0
K = 0
'récupère les valeurs dans les classeurs
'et les stocke dans un tableau à 3 dimensions
With Rs
.CursorType = 1
.LockType = 3
.Open "SELECT * FROM `" & NomFeuille & "$" & _
Plage & "` ", ConnectCL
.MoveFirst
ReDim Preserve TblValeur(1 To .Fields.Count, _
1 To UBound(TblClasseur), _
1 To .RecordCount)
Do While Not .EOF
K = K + 1
For Each Champ In .Fields
J = J + 1
TblValeur(J, I, K) = Champ.Value
Next
J = 0
.MoveNext
Loop
End With

ConnectCL.Close
Next I

'récupére les valeurs stockées dans le tableau
For I = 1 To UBound(TblValeur, 3) 'Lignes
'TblValeur(Colonnes, Classeurs, Lignes)
For J = 1 To UBound(TblValeur, 1) 'Colonnes
For K = 1 To UBound(TblValeur, 2) 'Classeurs
'compte le nombre de zéros (direction Z)
If TblValeur(J, K, I) = 0 Then
T = T + 1
End If
Next K
'inscrit le total dans la cellule correspondante
Cells(I + 5, J + 6) = T
T = 0
Next J
Next I

Erase TblClasseur
Erase TblValeur
Set Rs = Nothing
Set ConnectCL = Nothing
End Sub

Private Function Fichiers(Dossier As String, _
J As Integer) As String()
Dim TableauFichiers() As String
'J est passé en Ref. et si il est incrémenté,
'le dossier contient bien des classeurs
'sinon, le dossier est vide de classeurs
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = Dossier
If .Execute > 0 Then
For J = 1 To .FoundFiles.Count
ReDim Preserve TableauFichiers(1 To J)
TableauFichiers(J) = .FoundFiles(J)
Next J
End If
End With
Fichiers = TableauFichiers()
End Function

Hervé.

"Pierre F." <pfornerodNO@SPAMhotmail.com> a écrit dans le message de news:
ed1f8$46cf4118$55da2ea5$9642@news.hispeed.ch...

Bonjour à toutes et tous;

J'ai 38 classeurs !! tous identiques au niveau de leur structure (1
seule feuille)

Chaque cellule des plages F5:AX1000 de chacun d'eux contient soit un 0
(zéro) soit un nombre entier positif soit rien ("").

Je souhaiterais dans un classeur récapitulatif que chacune des cellules
de la plage F5:AX1000 elle comptabilise le nombre total de 0 des
cellules de même nom des 38 classeurs.

Est-ce possible??

C'est une espèce de nb.si(????;"0") inter-classeur.

Aurais-je plus de chance en mettant mes 38 feuilles dans un même classeur
??

Merci.

Cordialement,
Pierre F.

Vous avez filtré cet utilisateur ! Consultez son message

Bonsoir Pierre,
Avec VBA.
Les classeurs peuvent restés fermés mais doivent tous être dans le même
dossier.
Colle tout le code ci-dessous dans un module standard du classeur
récapitulatif, adapte :
- le chemin de ton dossier
- le nom des feuilles, qui doit impérativement être le même pour tous les
classeurs
- la plage de recherche (F5:AX1000 si je ne me trompe pas)
et exécute la proc "RecupValeurs".
Elle fonctionne de la manière suivante :
1- récupère le chemin de tous les classeurs
2- utilise ADO pour s'y connecter
3- récupère les valeurs dans un tableau à 3 dimensions, la dimension Z étant
le nombre de classeurs
4- compte les 0 de chaque cellules dans la dimension Z (chaque classeur) et
en additionne le nombre
5- inscrit le résultat dans les cellules correspondantes.

Je n'ai testé que sur tois classeurs et sur une plage de 20 cellules donc je
ne connais pas le temp de traitement :o((, à toi de voir si cela te convient
:

Private Sub ConnectCLasseur(ConnectCL As Object, _
Fichier As String, _
Optional Rs)

Set ConnectCL = CreateObject("ADODB.Connection")
If Not IsMissing(Rs) Then
Set Rs = CreateObject("ADODB.Recordset")
End If

ConnectCL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Fichier & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;IMEX= 2;"""
End Sub

Sub RecupValeurs()
Dim ConnectCL As Object
Dim Rs As Object
Dim Champ As Object
Dim Chemin As String
Dim Classeur As String
Dim NomFeuille As String
Dim Plage As String
Dim TblClasseur() As String
Dim TblValeur()
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim T As Integer

'chemin où vont être récupérés les
'classeurs, A ADAPTER
Chemin = "D:Mon Dossier"

'retourne un tableau contenant tous
'les classeurs du dossier
TblClasseur = Fichiers(Chemin, J)

'si aucun classeur dans le dossier, fin
'J est passé en Ref. et sa valeur est ensuite testée
If J = 0 Then
MsgBox "Ancun classeur ne se trouve dans ce dossier !"
Exit Sub
End If

'feuille ou s'effectuera la récup, A ADAPTER
NomFeuille = "Feuil1"
'plage ou s'effectuera la récup, A ADAPTER
Plage = "F5:AX1000"

For I = 1 To UBound(TblClasseur)
'chemin du classeur cible
Classeur = TblClasseur(I)
'connecxion
ConnectCLasseur ConnectCL, Classeur, Rs

J = 0
K = 0
'récupère les valeurs dans les classeurs
'et les stocke dans un tableau à 3 dimensions
With Rs
.CursorType = 1
.LockType = 3
.Open "SELECT * FROM `" & NomFeuille & "$" & _
Plage & "` ", ConnectCL
.MoveFirst
ReDim Preserve TblValeur(1 To .Fields.Count, _
1 To UBound(TblClasseur), _
1 To .RecordCount)
Do While Not .EOF
K = K + 1
For Each Champ In .Fields
J = J + 1
TblValeur(J, I, K) = Champ.Value
Next
J = 0
.MoveNext
Loop
End With

ConnectCL.Close
Next I

'récupére les valeurs stockées dans le tableau
For I = 1 To UBound(TblValeur, 3) 'Lignes
'TblValeur(Colonnes, Classeurs, Lignes)
For J = 1 To UBound(TblValeur, 1) 'Colonnes
For K = 1 To UBound(TblValeur, 2) 'Classeurs
'compte le nombre de zéros (direction Z)
If TblValeur(J, K, I) = 0 Then
T = T + 1
End If
Next K
'inscrit le total dans la cellule correspondante
Cells(I + 5, J + 6) = T
T = 0
Next J
Next I

Erase TblClasseur
Erase TblValeur
Set Rs = Nothing
Set ConnectCL = Nothing
End Sub

Private Function Fichiers(Dossier As String, _
J As Integer) As String()
Dim TableauFichiers() As String
'J est passé en Ref. et si il est incrémenté,
'le dossier contient bien des classeurs
'sinon, le dossier est vide de classeurs
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = Dossier
If .Execute > 0 Then
For J = 1 To .FoundFiles.Count
ReDim Preserve TableauFichiers(1 To J)
TableauFichiers(J) = .FoundFiles(J)
Next J
End If
End With
Fichiers = TableauFichiers()
End Function

Hervé.

"Pierre F." a écrit dans le message de news:
ed1f8$46cf4118$55da2ea5$

Bonjour à toutes et tous;

J'ai 38 classeurs !! tous identiques au niveau de leur structure (1
seule feuille)

Chaque cellule des plages F5:AX1000 de chacun d'eux contient soit un 0
(zéro) soit un nombre entier positif soit rien ("").

Je souhaiterais dans un classeur récapitulatif que chacune des cellules
de la plage F5:AX1000 elle comptabilise le nombre total de 0 des
cellules de même nom des 38 classeurs.

Est-ce possible??

C'est une espèce de nb.si(????;"0") inter-classeur.

Aurais-je plus de chance en mettant mes 38 feuilles dans un même classeur
??

Merci.

Cordialement,
Pierre F.

Pierre F.

25/08/2007 à 22:12

Bonsoir Pierre,
Avec VBA.
Les classeurs peuvent restés fermés mais doivent tous être dans le même
dossier.
Colle tout le code ci-dessous dans un module standard du classeur
récapitulatif, adapte : [...]

Bonsoir Hervé,

Merci pour ce "monstre" de VBA :-)

J'ai complété les items à adapter, vérifié que les classeurs se trouvent
tous dans le même dossier comme le classeur récapitulatif

... mais il plante chaque fois à l'instruction ci-dessous:

.Open "SELECT * FROM `" & NomFeuille & "$" & _
Plage & "` ", ConnectCL

... que je ne comprends pas ;-)

Le nom de toutes les feuilles est "Sem (1)"

Merci.
Cordialement,
Pierre F.

Hervé

26/08/2007 à 15:56

Bonjour Pierre,
Essais en ne mettant pas le classeur récapitulatif dans le même dossier que
les autres, l'erreur vient probablement de là.

Hervé.

"Pierre F." a écrit dans le message de news:
6ffc8$46d08d20$55da2ea5$

Bonsoir Pierre,
Avec VBA.
Les classeurs peuvent restés fermés mais doivent tous être dans le même
dossier.
Colle tout le code ci-dessous dans un module standard du classeur
récapitulatif, adapte : [...]

Bonsoir Hervé,

Merci pour ce "monstre" de VBA :-)

J'ai complété les items à adapter, vérifié que les classeurs se trouvent
tous dans le même dossier comme le classeur récapitulatif

... mais il plante chaque fois à l'instruction ci-dessous:

.Open "SELECT * FROM `" & NomFeuille & "$" & _
Plage & "` ", ConnectCL

... que je ne comprends pas ;-)

Le nom de toutes les feuilles est "Sem (1)"

Merci.
Cordialement,
Pierre F.

Pierre F.

26/08/2007 à 19:44

Bonjour Pierre,
Essais en ne mettant pas le classeur récapitulatif dans le même dossier que
les autres, l'erreur vient probablement de là.

Ça ne change rien, il plante sur la même instruction.. ??

Cordialement,
Pierre F.

Hervé

27/08/2007 à 21:00

Bonsoir Pierre,
Alors là, je sèche :o((
Quand je teste chez moi (10 classeurs plage de F5:AX100), ça fonctionne très
bien, donc il m'est très difficile de trouver la cause de ce
disfonctionnement ? Je suis désolé :o((

Hervé.

"Pierre F." a écrit dans le message de news:
6a8e3$46d1bc09$55da2ea5$

Bonjour Pierre,
Essais en ne mettant pas le classeur récapitulatif dans le même dossier
que

les autres, l'erreur vient probablement de là.

Ça ne change rien, il plante sur la même instruction.. ??

Cordialement,
Pierre F.

Pierre F.

27/08/2007 à 21:29

Bonsoir Pierre,
Alors là, je sèche :o((
Quand je teste chez moi (10 classeurs plage de F5:AX100), ça fonctionne très
bien, donc il m'est très difficile de trouver la cause de ce
disfonctionnement ? Je suis désolé :o((

C'est pas grave; j'ai configuré mon projet autrement en créant 5 fiches
récapitulatives qui me permettent d'utiliser nb.si sans que les formules
soient trop longues.

merci pour ton aide.
Désolé de t'avoir pris la tête avec ça.

Cordialement,
Pierre F.

Additionner des zéros dans 38 classeurs :-)

10 réponses

Veuillez sélectionner un problème