Somme des valeurs maximums de différentes références
Bonjour,
Après plusieurs heures à essayer de trouver une solution, je partage ce problème:
J'ai un tableau excel qui pourrait à terme avoir plus de 1000 lignes (Exemple simplifié ci-joint) avec différentes références (A, B, C, D...) qui se répètent et qui peuvent avoir des valeurs différentes (ex. A apparait sur 2 lignes avec deux valeurs différentes).
J'ai besoin de:
1. Compter le nombre de références (Combien de références différentes?).
2. Faire la somme des valeurs maximums des différentes références. Max valeur A + Max valeur B +....avec une formule globale car je pourrai avoir avoir plus de 500 références différentes dans le tableau.
Pour le point 1, cela marche avec la fonction sommeprod: =SOMMEPROD(1/NB.SI(B5:B10;B5:B10)).
Mais je n'arrive pas à trouver de formule pour le point2? Et idéalement sans formule matricielle). Avez-vous une idée?
Merci à l'avance!
Michael
Bonsoir le forum,
=SOMMEPROD(MAX((C5:C10)*(B5:B10="A")))Bonsoir,
Merci pour la proposition mais cela ne marche pas car j'ai besoin du maximum de toutes les références et pas seulement de "A" (Et j'aurai plus de 500 références différentes donc impossible de toutes les taper dans la formule). Auriez vous une solution pour remplacer "A" par "toutes les références"?
Je croise les doigts...
Michael
Bonjour et bienvenue sur le forum
Un essai à tester. Te convient-il ?
Bye !
Bonsoir,
A défaut d'une formule classique, tu peux essayer une fonction personnalisée :
Function SOMMEMAX(plr As Range, plv As Range)
Dim d As Object, i%
Application.Volatile
If plr.Cells.Count <> plv.Cells.Count Then
SOMMEMAX = CVErr(xlErrValue): Exit Function
End If
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To plr.Cells.Count
If IsNumeric(plv.Cells(i)) Then
If d.exists(plr.Cells(i).Value) Then
If plv.Cells(i) > d(plr.Cells(i).Value) Then d(plr.Cells(i).Value) = plv.Cells(i)
Else
d(plr.Cells(i).Value) = plv.Cells(i)
End If
End If
Next i
SOMMEMAX = WorksheetFunction.Sum(d.items)
End FunctionUtilisation : =SOMMEMAX(B5:B10;C5:C10)
On vérifie le caractère numérique du contenue de la cellule "valeur", car si une valeur texte figurait, elle ne déclencherait pas d'erreur (la fonction SOMME utilisée les supporte) mais la comparaison avec > se solderait par une évaluation que Texte est supérieur à nombre, le Max concerné disparaîtrait et le résultat serait faux....
Cordialement.
Bonsoir @ tous,
on peut le faire par une seule formule matricielle si on peut trier la base de données par Num de Ref et par ordre décroissant des valeurs, du fait que les valeurs MAX vont être les premières valeurs qui apparaissent pour chaque Num de Ref.
je vais essayer de trouver une formule matricielle qui ne nécessite pas de tri.
Reste un cas dont tu ne nous as pas parlé, en cas des exæquos pour le même Numéro de Référence par exemple pour A on a 2 fois le 10 qui est le MAX, est ce que l'on est censé le prendre en compte 2 fois ou une seule fois ?
@ + +
Bonjour,
voir la pièce jointe
pour une solution alternative.
(Liste est un peu généralisé p/r à la structure en dynamique)
Bonsoir,
Merci à tous pour vos réponses et propositions: Vraiment impressionnant.
Après avoir testé les différentes idées, j'ai finalement utilisé la proposition de fonction personnalisée "sommemax" de MFerrand (et je garde aussi celle de sakman26 en réserve).
MERCI BEAUCOUP
Michael
bonjour
sujet qui n'a l'air de rien ........
avis aux amateurs ; je remonte le sujet
cordialement
Bonjour @ tous,
Salut Tulipe_4
tulipe_4 a écrit :sujet qui n'a l'air de rien ........
avis aux amateurs ; je remonte le sujet
Mais les a;b;c et d ici ne sont que des exemples, il peut y avoir 100 critères ou plus, alors pour faire une formule et parler d'une formule il faut pas être censé y citer les critères manuellement.
@ + +
re
d'accord avec toi ; celle qui serait interessante c'est celle qui renvoie 10 car dans cette derniere ce sont les val fournies par EQUIV(plage;plage) qui permettent (apres tri sans double de ces denières) de ne selectionner les val de tel ou tel nom donc on en recupere facilement le max ;mais ça ce n'est que pour un equiv donc si il y en a 200 pour 500noms ;il faudrait (mais je ne crois pas que ce soit faisable) arriver a obtenir une matrice du genre
{lemax du nom1;;;;;;lemax du2;;;;;;;;lemax du 4 }
pour l'instant ma formule marche mais ne repertorie pas les max parce que je n'arrive pas a gerer le K de petite .valeur etant donné que l'on s'interdit d'incrementer la formule
mais peut etre qu'avec TRANSPOSE on arriverait a faire un matrice virtuelle pour k=1 k=2........ ; bref le tout est creer cette matrice virtuelle composée uniquement des MAX afin de les sommer
cordialement
Bonjour à tous
@Tulipe
Et tu dis que tes interventions sont toujours la même salade ?
Drôlement assaisonnée celle-là !
je ne doute pas que Rachid te comprenne mais pour moi, c'est du chinois.
Bye !
Re,
j'ai essayé de tricoter une formule en y ajoutant VALEURS.UNIQUES() de la macro complémentaire MoreFunc mais sans succès, mais sûrement il y a une façon de faire, j'ai pu réussir un jour à convertir une somme de matrices en une matrice de sommes, mais c’était pas comme ça.
depuis que la question a été posée et je suis en train de chercher comment je pourrais la faire en combinant les fonctions natives d'Excel.
@ + +
salut gmb
mais non
la matrice virtuelle c'est comme dictionnary ; donc un dictionnary des differents max ;pour l'instant je n'en ai meme pas la couverture pourtant je sais ce qu'il doit contenir et comment l'obtenir
prefere tu le serbo croate ??
cordialement
salut R@chid
moi aussi ça me tarabusque, derniere idée >>>SOMME .produit
Bonjour,
Heuuu, c'était trop simple un TCD sans formules, sans liste à faire ni VBA ou j'ai oublié quelque chose ?
eric
salut Eriiic
tout a fait ; mais nous nous interdisons le tdc :question de sport ; tu comprends que ce serait trop facile
ainsi ;quand on arrive a faire du compliqué ,ça aide pour le simple ; pour l'instant ça cogite
cordialement
Ah !
Je me demandais si j'avais mal compris le truc, ça n'aurait pas été la 1ère fois
Bonjour Eric
tulipe_4 a écrit :pour l'instant ça cogite
Moi, ça m'amuse ! Vraiment ! Quel champion va gagner le match ?
Bye !
bonjour
delire dominical
puisque visiblement ;ça a l'air complexe ..... une proposition tulipienne
1) on concanne la colb et c >>> colB&"."colC
2) on trie par ordre decroissant ( genre nb.si(........"<"&.....)mais qui marche >>>cela doit cracher une matrice virtuelle
3) l'equiv (matrice triée ;matricetriée) doit donner la position du premier MAX ; donc virer les doublons d'equiv ( peut etre FREQUENCE)>>>>du coup en noyant ça dans un SUBSTITUE ou autre qui recupere la val apès le "." avec *1 pour sommer
est-ce que ça vous parle ??
cordialement