limite de la fonction SOMMEPROD

[strawberry]

Salut le forum !!

En consultant le forum, j'ai trouvé la réponse à ma question. La formule =SOMMEPROD(1/NB.SI(Plage;Plage)) marche super bien. Cependant, je pense qu'en raison de l'importance de la plage (+ 43000 lignes) que je dois traiter, mon fichier plante !!!
Avez vous un avis sur le pb ?
Merci d'avance,
Strawberry

 

[CBernardT]

Re : limite de la fonction SOMMEPROD

Bonsoir

Difficile de comprendre ce que tu cherches à obtenir !

La formule =SOMMEPROD(1/NB.SI(Plage;Plage)) est étrange. Elle aboutit normalement au même résultat que nb(Plage)

Une explication complémentaire s'impose avec un bout de fichier si c'est possible.

Cordialement

Bernard

 

[Tibo]

Re : limite de la fonction SOMMEPROD

Bonsoir Bernard,

Cette formule permet de donner le nombre d'éléments différents (et donc ne prend pas en compte les doublons et plus).

Notre ami souhaite faire un calcul sur 43000 lignes et il se trouve que cette fonction SOMMEPROD (même si elle ne demande pas de validation matricielle) est une formule matricielle gourmande en temps de calcul.

Sur 43000 lignes, il est évident que le temps de calcul peut être ... très important, au point que Excel ou le micro déclare forfait.

Je viens de tenter et effectivement il vaut mieux éviter.

Je pense que la solution est d'envisager un traitement VBA, mais là, je ne sais pas faire.

Bonne soirée à tous

@+

Edit : l'essai chez moi sur 43000 lignes (et avec une seule colonne de données) ne fait pas planter Excel. Mais toute modif de donnée déclenche des temps de calcul de plusieurs minutes et n'est donc guère exploitable.

@+

 

[Cousinhub]

Re : limite de la fonction SOMMEPROD

Bonsoir,

Bonsoir, CBernardT
Bonsoir, Tibo

@ Tibo, j'ai fait le même essai que toi, sur 40 000 lignes => Ctrl + Alt + Suppr....

Ci joint une petite fonction personnalisée, qui met 1.2 secondes, pour 40 000 lignes, avec 26 441 valeurs différentes :

Function NbUnique(Plg As Range)
Dim Uniques As Object, Cel As Range
Set Uniques = CreateObject("Scripting.Dictionary")
For Each Cel In Plg
    If Not Uniques.Exists(Cel.Value) Then Uniques.Add Cel.Value, Cel.Value
Next Cel
NbUnique = Uniques.Count
End Function

et dans une cellule, entrer cette fonction :

=NbUnique(A1:A40000)

Pour remettre à jour la formule, une façon assez simple est de sélectionner la cellule contenant la formule, Appui sur F2, puis "Entrer"

 

[job75]

Re : limite de la fonction SOMMEPROD

Bonsoir à tous,

Dans le même esprit que bh2, mais avec une collection :

Function Compte(plage As Range) As Long
Dim cel As Range, c As New Collection
On Error Resume Next
For Each cel In plage
c.Add cel, CStr(cel)
Next
Compte = c.Count [COLOR="Red"]'-1 'éventuellement s'il y a des cellules vides...[/COLOR]
End Function

Le temps de calcul pour 43000 cellules et 43000 valeurs différentes est aussi de l'ordre de la seconde.

A+