Somme MFC (je sais que c'est impossible) Aide pour formule de calcul

[Shpountz]

Bonjour à tous

Je sais qu'il n'est pas possible de faire des calculs sur une MFC
Mais je suis certain que vous allez pouvoir m'aider pour la formule de calcul sur laquelle je seche...

J'ai besoin de calculer en ligne la somme des plus grands nombres d'une colonne.

Un petit fichier plus explicatif est joint.

Merci d'avance pour votre aide

François

 

[JBOBO]

Bonjour,

En X2 : =SI(MAX($I$2:$I$10)=$I2;$I2;0)+SI(MAX($J$2:$J$10)=$J2;$J2;0)+SI(MAX($K$2:$K$10)=$K2;$K2;0)+SI(MAX($L$2:$L$10)=$L2;$L2;0)+SI(MAX($M$2:$M$10)=$M2;$M2;0)+SI(MAX($N$2:$N$10)=$N2;$N2;0)+SI(MAX($O$2:$O$10)=$O2;$O2;0)+SI(MAX($P$2:$P$10)=$P2;$P2;0)+SI(MAX($Q$2:$Q$10)=$Q2;$Q2;0)+SI(MAX($R$2:$R$10)=$R2;$R2;0)+SI(MAX($S$2:$S$10)=$S2;$S2;0)+SI(MAX($T$2:$T$10)=$T2;$T2;0)+SI(MAX($U$2:$U$10)=$U2;$U2;0)+SI(MAX($V$2:$V$10)=$V2;$V2;0)

 

[Shpountz]

Merci JBOBO
Cela réponds à ma question et cela fonctionne à merveille

Encore un grand merci
Francois

 

[JHA]

Bonjour à tous,

Une petite modif de la MFC

JHA

 

[Shpountz]

Bonjour JHA

Parfait aussi

Merci beaucoup
François

 

[job75]

Bonsoir Spountz, JBOBO, JHA,

Deux solutions dans les fichiers joints.

Par formule avec en X2 :

=SOMMEPROD((I2:V2=SOUS.TOTAL(4;DECALER(I:I;;COLONNE(I2:V2)-COLONNE(I2))))*I2:V2)

Par VBA avec cette macro dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dest As Range, ncol%, a(), i&, j%
Set dest = [X1] 'cellule de destination, à adapter
With Intersect([I:V], Me.UsedRange.EntireRow) 'colonnes I:V à adapter
  ncol = .Columns.Count
  ReDim a(1 To .Rows.Count, 1 To 1)
  For i = 1 To .Rows.Count
    For j = 1 To ncol
      If .Cells(i, j).DisplayFormat.Interior.ColorIndex <> xlNone _
        And IsNumeric(CStr(.Cells(i, j))) Then a(i, 1) = a(i, 1) + .Cells(i, j)
  Next j, i
  If a(1, 1) = "" Then a(1, 1) = dest 'si titres
  Application.EnableEvents = False 'désactive les évènements
  dest.Resize(UBound(a)) = a 'restitution
  dest.Offset(UBound(a)).Resize(Rows.Count - UBound(a) - dest.Row + 1) = ""
  Application.EnableEvents = True 'réactive les évènements
End With
End Sub

La propriété DisplayFormat permet de connaître la couleur appliquée par une MFC.

Elle n'existe qu'à partir d'Excel 2010 et ne fonctionne pas dans une fonction VBA personnalisée.

A+

 

[job75]

Re,

J'ai testé les durées des calculs avec le tableau recopié vers le bas.

Sur 1000 lignes :

- par formule 0,14 seconde

- par VBA 1,20 seconde.

Sur 10 000 lignes :

- par formule 12,6 secondes

- par VBA 80 secondes.

VBA se traîne car avec DisplayFormat on est obligé d'étudier les cellules une par une.

A+

 

[job75]

Re Shpountz, le fil,

En VBA ce n'est donc pas DisplayFormat qu'on utilisera mais des tableaux VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dest As Range, ncol%, maxi#(), j%, a(), t, i&
Set dest = [X1] 'cellule de destination, à adapter
With Intersect([I:V], Me.UsedRange.EntireRow) 'colonnes I:V à adapter
  ncol = .Columns.Count
  ReDim maxi(1 To ncol)
  For j = 1 To ncol
    maxi(j) = Application.Max(.Columns(j))
  Next
  ReDim a(1 To .Rows.Count, 1 To 1)
  t = .Value 'matrice, plus rapide
  For i = 1 To .Rows.Count
    For j = 1 To ncol
      If t(i, j) <> "" And t(i, j) = maxi(j) Then a(i, 1) = a(i, 1) + t(i, j)
  Next j, i
  If a(1, 1) = "" Then a(1, 1) = dest 'si titres
  Application.EnableEvents = False 'désactive les évènements
  dest.Resize(UBound(a)) = a 'restitution
  dest.Offset(UBound(a)).Resize(Rows.Count - UBound(a) - dest.Row + 1) = ""
  Application.EnableEvents = True 'réactive les évènements
End With
End Sub

Fichier (2), sur 1000 lignes 0,03 seconde, sur 10 000 lignes 0,24 seconde.

A+

 

[Shpountz]

Merci Job75

Une macro solution de plus dans ma trousse à outils.
Un grand merci je la teste des demain

Bonne soirée
François

 

[ROGER2327]

Bonjour à tous.

Essai avec une fonction personnalisée. Je n'ai pas fait de test de vitesse.​

ℝOGER2327
#8446

Samedi 14 Décervelage 144 (Saints 4 Sans-Cou, enchanteurs - fête Suprême Quarte)
22 Nivôse An CCXXV, 0,4240h - sel
2017-W02-3T01:01:03Z

 

[job75]

Bonjour Roger,

J'ai testé votre fonction sur 10 000 lignes dans les mêmes conditions que mes fichiers précédents en la rendant volatile.

Elle s'exécute en 0,20 seconde, c'est un peu plus rapide que ma macro du post #8 (0,21 à 0,23 s sans l'effacement final).

Bonne journée.

 

[Shpountz]

Bonjour à tous !

Que puis je dire ?
Un grand merci à tous car en fait tout me convient et je me coucherai un peu plus intelligent ce soir

Amicalement
Francois