Compter dans une colonne de 2535 000 enregisterments, le noms sans les doublons

MichD

04/05/2011 à 15:13

Bonjour,

Selon la disposition de tes données :
Tu tries par ordre croissant : la colonne des 64 responsables

Tu as essayé la commande "Sous-total" du menu "Données" (Excel 2003 et antérieure)
Pour Excel 2007 et 2010, barre des menus / données / sous-section / outline(english) / sous-total

MichD
--------------------------------------------
"michir" a écrit dans le message de groupe de discussion :

Bonjour,
Je sais parfaitement que c'est là une question récurrente. J'ai fait
un recherche dans les forums mais aucune solution ne me convient
vraiment, essentiellement en raison du grand nombre de lignes à
traiter.
Les formules mettent un temps infini
Existe-t-il une solution macro qui serait plus rapide et élégante ?
Pour être plus clair, ma colonne comprend 64 responsables de secteur
différents sur 235 000 lignes en hauteur. J'aimerais faire des sous
totaux en utilisant des filtres sur d'autres colonnes... mon problème
est de le faire rapidement sans attendre 5 minutes le résultat...
Merci à vous tous
Michel

michir

04/05/2011 à 16:14

Re, MichD
Oui, j'ai bien pensé à ta méthode (qui fonctionne) mais elle est
terriblement longue , environ 5 Mn pour 235 000 lignes
Alors tu imagines pour peu que je change les conditions du filtrage
dans une autre colonne...
Je continue à chercher. La fonction sous.total est rapide mais n'est
pas adaptée à un comptage sans doublons, enfin je ne connais pas
J'aurais aimé que l'affichage du nombre des noms (ou autres) filtrés,
soit quasi immédiat. En ce sens, sous.total est fait pour, en principe
Merci
Michel

On 4 mai, 15:13, "MichD" wrote:

Bonjour,

Selon la disposition de tes données :
Tu tries par ordre croissant : la colonne des 64 responsables

Tu as essayé la commande "Sous-total" du menu "Données" (Excel 2003 e t antérieure)
Pour Excel 2007 et 2010, barre des menus / données / sous-section / out line(english) / sous-total

MichD
--------------------------------------------
"michir" a écrit dans le message de groupe de discussion :

Bonjour,
Je sais parfaitement que c'est là une question récurrente. J'ai fait
un recherche dans les forums mais aucune solution ne me convient
vraiment, essentiellement en raison du grand nombre de lignes à
traiter.
Les formules mettent un temps infini
Existe-t-il une solution macro qui serait plus rapide et élégante ?
Pour être plus clair, ma colonne comprend 64 responsables de secteur
différents sur 235 000 lignes en hauteur. J'aimerais faire des sous
totaux en utilisant des filtres sur d'autres colonnes... mon problème
est de le faire rapidement sans attendre 5 minutes le résultat...
Merci à vous tous
Michel

MichD

04/05/2011 à 16:43

=SOUS.TOTAL(9;C1:C8)
C'est ce type de formule qui prend une éternité ?

Si oui, il n'y a sûrement rien de plus rapide en VBA.

Sur une plage de cette importance, il est normal d'observer
un certain délai avant l'affichage du résultat de la formule !

MichD
--------------------------------------------
"michir" a écrit dans le message de groupe de discussion :

Re, MichD
Oui, j'ai bien pensé à ta méthode (qui fonctionne) mais elle est
terriblement longue , environ 5 Mn pour 235 000 lignes
Alors tu imagines pour peu que je change les conditions du filtrage
dans une autre colonne...
Je continue à chercher. La fonction sous.total est rapide mais n'est
pas adaptée à un comptage sans doublons, enfin je ne connais pas
J'aurais aimé que l'affichage du nombre des noms (ou autres) filtrés,
soit quasi immédiat. En ce sens, sous.total est fait pour, en principe
Merci
Michel

On 4 mai, 15:13, "MichD" wrote:

Bonjour,

Selon la disposition de tes données :
Tu tries par ordre croissant : la colonne des 64 responsables

Tu as essayé la commande "Sous-total" du menu "Données" (Excel 2003 et antérieure)
Pour Excel 2007 et 2010, barre des menus / données / sous-section / outline(english) / sous-total

MichD
--------------------------------------------
"michir" a écrit dans le message de groupe de discussion :

Bonjour,
Je sais parfaitement que c'est là une question récurrente. J'ai fait
un recherche dans les forums mais aucune solution ne me convient
vraiment, essentiellement en raison du grand nombre de lignes à
traiter.
Les formules mettent un temps infini
Existe-t-il une solution macro qui serait plus rapide et élégante ?
Pour être plus clair, ma colonne comprend 64 responsables de secteur
différents sur 235 000 lignes en hauteur. J'aimerais faire des sous
totaux en utilisant des filtres sur d'autres colonnes... mon problème
est de le faire rapidement sans attendre 5 minutes le résultat...
Merci à vous tous
Michel

=SOUS.TOTAL(9;C1:C8)
C'est ce type de formule qui prend une éternité ?

Si oui, il n'y a sûrement rien de plus rapide en VBA.

Sur une plage de cette importance, il est normal d'observer
un certain délai avant l'affichage du résultat de la formule !

MichD
--------------------------------------------
"michir" a écrit dans le message de groupe de discussion :
536f475c-7855-45f5-9ee4-1f0d89544ec9@u16g2000yqj.googlegroups.com...

Re, MichD
Oui, j'ai bien pensé à ta méthode (qui fonctionne) mais elle est
terriblement longue , environ 5 Mn pour 235 000 lignes
Alors tu imagines pour peu que je change les conditions du filtrage
dans une autre colonne...
Je continue à chercher. La fonction sous.total est rapide mais n'est
pas adaptée à un comptage sans doublons, enfin je ne connais pas
J'aurais aimé que l'affichage du nombre des noms (ou autres) filtrés,
soit quasi immédiat. En ce sens, sous.total est fait pour, en principe
Merci
Michel

On 4 mai, 15:13, "MichD" <michde...@hotmail.com> wrote:

Bonjour,

Selon la disposition de tes données :
Tu tries par ordre croissant : la colonne des 64 responsables

Tu as essayé la commande "Sous-total" du menu "Données" (Excel 2003 et antérieure)
Pour Excel 2007 et 2010, barre des menus / données / sous-section / outline(english) / sous-total

MichD
--------------------------------------------
"michir" a écrit dans le message de groupe de discussion :
8d6f5688-3c5e-4232-a377-f36d150d2...@35g2000yqy.googlegroups.com...

Bonjour,
Je sais parfaitement que c'est là une question récurrente. J'ai fait
un recherche dans les forums mais aucune solution ne me convient
vraiment, essentiellement en raison du grand nombre de lignes à
traiter.
Les formules mettent un temps infini
Existe-t-il une solution macro qui serait plus rapide et élégante ?
Pour être plus clair, ma colonne comprend 64 responsables de secteur
différents sur 235 000 lignes en hauteur. J'aimerais faire des sous
totaux en utilisant des filtres sur d'autres colonnes... mon problème
est de le faire rapidement sans attendre 5 minutes le résultat...
Merci à vous tous
Michel

Modeste

04/05/2011 à 16:50

Bonsour®

"michir" a écrit

Je sais parfaitement que c'est là une question récurrente. J'ai fait
un recherche dans les forums mais aucune solution ne me convient
vraiment, essentiellement en raison du grand nombre de lignes à
traiter.
Les formules mettent un temps infini
Existe-t-il une solution macro qui serait plus rapide et élégante ?
Pour être plus clair, ma colonne comprend 64 responsables de secteur
différents sur 235 000 lignes en hauteur. J'aimerais faire des sous
totaux en utilisant des filtres sur d'autres colonnes... mon problème
est de le faire rapidement sans attendre 5 minutes le résultat...

utiliser MSQuery

http://cherbe.free.fr/msquery.html
quelque chose du genre :
Select by Responsable countdistinct(itemresponsable) from latable

MichD

04/05/2011 à 17:12

Bonjour Modeste,

| utiliser MSQuery

| http://cherbe.free.fr/msquery.html
| quelque chose du genre :
| Select by Responsable countdistinct(itemresponsable) from latable

Je suppose qu'on pourrait utiliser aussi ADO (Activex Data Object) pour
interroger la table des données. La question, est-ce beaucoup plus rapide
que l'application d'un simple filtre dans Excel ? Je n'ai jamais testé cela
sur des plages de cette importance dans Excel...

Un exemple de code :

'Il faut ajouter la bibliothèque "Microsoft Data object Library"
dans la fenêtre de l'éditeur de code / barre des menus / outils / références

Bien évidemment, tu dois adapter les variables + la chaîne SQL (requête)
que tu veux explorer !

'----------------------------------------
Sub MaRequêteAvecADO()

Dim Conn As ADODB.Connection, Rst As New ADODB.Recordset
Dim Requete As String, FeuilData As String, Rg As Range
Dim File As String, C As Integer, Ok As Integer
Dim FeuilDest As String

'Nom de la feuille où sont les données
FeuilData = "denis"

'Nom de la feuille où tu veux copier les données
FeuilDest = "Feuil2"

'La requête qui sera exécutée.
Requete = "SELECT * From [" & FeuilData & "$] " & vbCrLf & _
"where Ville = 'Boston' and Pays = 'Etats-Unis' "

'Fichier sur lequel se fera la requête :
File = ThisWorkbook.FullName

'Défini la première cellule où seront copiées les
'données des requêtes ADO
With Worksheets(FeuilDest)
If .Range("A1") = "" Then
Set Rg = .Range("A1")
Else
Set Rg = .Range("A" & .Range("A65356").End(xlUp).Row)(2)
Ok = 1
End If
End With

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & File & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;"""

Rst.Open Requete, Conn, adOpenForwardOnly, adLockOptimistic

'Copie les étiquettes du recordset vers Excel
If Ok <> 1 Then
Do
Rg.Offset(, C) = Rst.Fields(C).Name
C = C + 1
x = x + 1
Loop Until x = Rst.Fields.Count
Rg.Offset(1).CopyFromRecordset Rst
Else
Rg.CopyFromRecordset Rst
End If

Rst.Close: Conn.Close
Set Rst = Nothing: Set Conn = Nothing

End Sub
'----------------------------------------

MichD
--------------------------------------------
"Modeste" a écrit dans le message de groupe de discussion : 4dc167a8$0$25206$

Bonsour®

"michir" a écrit

Je sais parfaitement que c'est là une question récurrente. J'ai fait
un recherche dans les forums mais aucune solution ne me convient
vraiment, essentiellement en raison du grand nombre de lignes à
traiter.
Les formules mettent un temps infini
Existe-t-il une solution macro qui serait plus rapide et élégante ?
Pour être plus clair, ma colonne comprend 64 responsables de secteur
différents sur 235 000 lignes en hauteur. J'aimerais faire des sous
totaux en utilisant des filtres sur d'autres colonnes... mon problème
est de le faire rapidement sans attendre 5 minutes le résultat...

utiliser MSQuery

http://cherbe.free.fr/msquery.html
quelque chose du genre :
Select by Responsable countdistinct(itemresponsable) from latable

Bonjour Modeste,

| utiliser MSQuery

| http://cherbe.free.fr/msquery.html
| quelque chose du genre :
| Select by Responsable countdistinct(itemresponsable) from latable

Je suppose qu'on pourrait utiliser aussi ADO (Activex Data Object) pour
interroger la table des données. La question, est-ce beaucoup plus rapide
que l'application d'un simple filtre dans Excel ? Je n'ai jamais testé cela
sur des plages de cette importance dans Excel...

Un exemple de code :

'Il faut ajouter la bibliothèque "Microsoft Data object Library"
dans la fenêtre de l'éditeur de code / barre des menus / outils / références

Bien évidemment, tu dois adapter les variables + la chaîne SQL (requête)
que tu veux explorer !

'----------------------------------------
Sub MaRequêteAvecADO()

Dim Conn As ADODB.Connection, Rst As New ADODB.Recordset
Dim Requete As String, FeuilData As String, Rg As Range
Dim File As String, C As Integer, Ok As Integer
Dim FeuilDest As String

'Nom de la feuille où sont les données
FeuilData = "denis"

'Nom de la feuille où tu veux copier les données
FeuilDest = "Feuil2"

'La requête qui sera exécutée.
Requete = "SELECT * From [" & FeuilData & "$] " & vbCrLf & _
"where Ville = 'Boston' and Pays = 'Etats-Unis' "

'Fichier sur lequel se fera la requête :
File = ThisWorkbook.FullName

'Défini la première cellule où seront copiées les
'données des requêtes ADO
With Worksheets(FeuilDest)
If .Range("A1") = "" Then
Set Rg = .Range("A1")
Else
Set Rg = .Range("A" & .Range("A65356").End(xlUp).Row)(2)
Ok = 1
End If
End With

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & File & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;"""

Rst.Open Requete, Conn, adOpenForwardOnly, adLockOptimistic

'Copie les étiquettes du recordset vers Excel
If Ok <> 1 Then
Do
Rg.Offset(, C) = Rst.Fields(C).Name
C = C + 1
x = x + 1
Loop Until x = Rst.Fields.Count
Rg.Offset(1).CopyFromRecordset Rst
Else
Rg.CopyFromRecordset Rst
End If

Rst.Close: Conn.Close
Set Rst = Nothing: Set Conn = Nothing

End Sub
'----------------------------------------

MichD
--------------------------------------------
"Modeste" a écrit dans le message de groupe de discussion : 4dc167a8$0$25206$426a74cc@news.free.fr...

Bonsour®

"michir" a écrit

Je sais parfaitement que c'est là une question récurrente. J'ai fait
un recherche dans les forums mais aucune solution ne me convient
vraiment, essentiellement en raison du grand nombre de lignes à
traiter.
Les formules mettent un temps infini
Existe-t-il une solution macro qui serait plus rapide et élégante ?
Pour être plus clair, ma colonne comprend 64 responsables de secteur
différents sur 235 000 lignes en hauteur. J'aimerais faire des sous
totaux en utilisant des filtres sur d'autres colonnes... mon problème
est de le faire rapidement sans attendre 5 minutes le résultat...

utiliser MSQuery

http://cherbe.free.fr/msquery.html
quelque chose du genre :
Select by Responsable countdistinct(itemresponsable) from latable

Vous avez filtré cet utilisateur ! Consultez son message

Bonjour Modeste,

| utiliser MSQuery

| http://cherbe.free.fr/msquery.html
| quelque chose du genre :
| Select by Responsable countdistinct(itemresponsable) from latable

Je suppose qu'on pourrait utiliser aussi ADO (Activex Data Object) pour
interroger la table des données. La question, est-ce beaucoup plus rapide
que l'application d'un simple filtre dans Excel ? Je n'ai jamais testé cela
sur des plages de cette importance dans Excel...

Un exemple de code :

'Il faut ajouter la bibliothèque "Microsoft Data object Library"
dans la fenêtre de l'éditeur de code / barre des menus / outils / références

Bien évidemment, tu dois adapter les variables + la chaîne SQL (requête)
que tu veux explorer !

'----------------------------------------
Sub MaRequêteAvecADO()

Dim Conn As ADODB.Connection, Rst As New ADODB.Recordset
Dim Requete As String, FeuilData As String, Rg As Range
Dim File As String, C As Integer, Ok As Integer
Dim FeuilDest As String

'Nom de la feuille où sont les données
FeuilData = "denis"

'Nom de la feuille où tu veux copier les données
FeuilDest = "Feuil2"

'La requête qui sera exécutée.
Requete = "SELECT * From [" & FeuilData & "$] " & vbCrLf & _
"where Ville = 'Boston' and Pays = 'Etats-Unis' "

'Fichier sur lequel se fera la requête :
File = ThisWorkbook.FullName

'Défini la première cellule où seront copiées les
'données des requêtes ADO
With Worksheets(FeuilDest)
If .Range("A1") = "" Then
Set Rg = .Range("A1")
Else
Set Rg = .Range("A" & .Range("A65356").End(xlUp).Row)(2)
Ok = 1
End If
End With

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & File & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;"""

Rst.Open Requete, Conn, adOpenForwardOnly, adLockOptimistic

'Copie les étiquettes du recordset vers Excel
If Ok <> 1 Then
Do
Rg.Offset(, C) = Rst.Fields(C).Name
C = C + 1
x = x + 1
Loop Until x = Rst.Fields.Count
Rg.Offset(1).CopyFromRecordset Rst
Else
Rg.CopyFromRecordset Rst
End If

Rst.Close: Conn.Close
Set Rst = Nothing: Set Conn = Nothing

End Sub
'----------------------------------------

MichD
--------------------------------------------
"Modeste" a écrit dans le message de groupe de discussion : 4dc167a8$0$25206$

Bonsour®

"michir" a écrit

Je sais parfaitement que c'est là une question récurrente. J'ai fait
un recherche dans les forums mais aucune solution ne me convient
vraiment, essentiellement en raison du grand nombre de lignes à
traiter.
Les formules mettent un temps infini
Existe-t-il une solution macro qui serait plus rapide et élégante ?
Pour être plus clair, ma colonne comprend 64 responsables de secteur
différents sur 235 000 lignes en hauteur. J'aimerais faire des sous
totaux en utilisant des filtres sur d'autres colonnes... mon problème
est de le faire rapidement sans attendre 5 minutes le résultat...

utiliser MSQuery

http://cherbe.free.fr/msquery.html
quelque chose du genre :
Select by Responsable countdistinct(itemresponsable) from latable

DanielCo

04/05/2011 à 17:29

Si quelqu'un a déjà testé Powerpivot ?
"Process massive amounts of data in seconds" (il faut Excel 2010).
Daniel

MichD

04/05/2011 à 17:50

Bonjour Daniel,

Téléchargeable gratuitement ici :
http://www.microsoft.com/downloads/fr-fr/details.aspx?FamilyIDà81c894-e4ab-42df-8c87-4b99c1f3c49b

Il s'installe seulement sur une version office 2010.

Un peu de lecture sur le sujet...
2 Volumes sur le sujet + Fichiers Exemples pour chacun
(Je ne sais pas si les versions françaises existent)

A)
Microsoft PowerPivot for Excel 2010 - Give Your Data Meaning
PUBLISHED BY Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2011 by Marco Russo and Alberto Ferrari
416 pages

B )
PowerPivot for the Data Analyst: Microsoft® Excel 2010
Copyright ©2010 by Pearson Education, Inc.
326 pages

On s'en reparle dès que tu auras fini la lecture !
;-))

MichD
--------------------------------------------
"DanielCo" a écrit dans le message de groupe de discussion : 4dc170b3$0$4780$

Si quelqu'un a déjà testé Powerpivot ?
"Process massive amounts of data in seconds" (il faut Excel 2010).
Daniel

michir

04/05/2011 à 18:15

Danel Co,
Je suis en train d'essayer PowerPivot précisemment sur ma base de
235000 lignes
A priori pas mal du tout mais peu de documentation en français
A suivre
Michel

On 4 mai, 17:29, DanielCo wrote:

Si quelqu'un a déjà testé Powerpivot ?
"Process massive amounts of data in seconds" (il faut Excel 2010).
Daniel

DanielCo

04/05/2011 à 18:25

Bonjour et merci,
Euh, tu n'aurais pas plutôt "Powerpivot pour les nuls" ? ;-)
Daniel

Bonjour Daniel,

Téléchargeable gratuitement ici :
http://www.microsoft.com/downloads/fr-fr/details.aspx?FamilyIDà81c894-e4ab-42df-8c87-4b99c1f3c49b

Il s'installe seulement sur une version office 2010.

Un peu de lecture sur le sujet...
2 Volumes sur le sujet + Fichiers Exemples pour chacun
(Je ne sais pas si les versions françaises existent)

A)
Microsoft PowerPivot for Excel 2010 - Give Your Data Meaning
PUBLISHED BY Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2011 by Marco Russo and Alberto Ferrari
416 pages

B )
PowerPivot for the Data Analyst: Microsoft® Excel 2010
Copyright ©2010 by Pearson Education, Inc.
326 pages

On s'en reparle dès que tu auras fini la lecture !
;-))

MichD
--------------------------------------------
"DanielCo" a écrit dans le message de groupe de discussion :
4dc170b3$0$4780$

Si quelqu'un a déjà testé Powerpivot ?
"Process massive amounts of data in seconds" (il faut Excel 2010).
Daniel

Compter dans une colonne de 2535 000 enregisterments, le noms sans les doublons

9 réponses

Veuillez sélectionner un problème