XL 2016 Nom de l'onglet dont la valeur est la plus grande

[turame]

Bonjour à tous,

J'ai 10 onglets nommés de "un" à "dix"
Sur mon 10ème onglet, je cherche la valeur maximale des cellule E30 pour les 9 premiers onglets : =MAX('un:neuf'!D30)

Ensuite je voudrais avoir le nom de la feuille correspondante à cette valeur mais je bloque...
Si quelqu'un a une idée je suis preneur.

Merci d'avance

PS : Pour info le nom de chaque onglet se situe également dans la cellule B2 de chaque onglet.

 

[jmfmarques]

Bonjour
La fonction Max s'applique à une matrice (ou une plage de données), ce que ne saurait être :
'un:neuf'!D30

 

[turame]

La formule que vous évoquez me renvoie bien la valeur la plus élevée des cellules D30 de tous les onglet. Aucun souci la dessus. En revanche comment avoir le nom de cet onglet ?

 

[laurent950]

Bonjour,
En partant sur l'hypothése que le classeur contient uniquement 10 feuilles dans le classeur
Feuille 1 à 10
J'utilise pas la feuille N°10
Je cherche la plus grande Valeur dans la cellule E30 de chacune des Feuille 1 à 9
Une fois trouvé = La Feuille exemple : Quatre et valeur 3 000

Le code :

Sub exemple()
Dim Ws As Worksheet
Dim tableau() As Variant
' Hypothése Classeur de 10 Feuilles (Uniquement)
ReDim Preserve tableau(1 To (ThisWorkbook.Sheets.Count - 1), 1 To 2)
    For Each Ws In ThisWorkbook.Worksheets
        If LCase(Ws.Name) <> LCase("Dix") Then
            tableau(Ws.Index, 1) = Ws.Name
            tableau(Ws.Index, 2) = Ws.Cells(1, 5)
        End If
    Next Ws
' Resultat
  MsgBox tableau(Application.Match(WorksheetFunction.Max(Application.Index(tableau, , 2)), Application.Index(tableau, , 2), 0), 1)
  MsgBox tableau(Application.Match(WorksheetFunction.Max(Application.Index(tableau, , 2)), Application.Index(tableau, , 2), 0), 2)
End Sub

 

[turame]

Merci pour votre réponse. Serait-il possible d'avoir l'information sans passé par du VBA ?
Directement via une formule. J'ai déjà trouvé la valeur qui m'intéresse mais comment y associer l'onglet en question ?

Merci d'avance

 

[mapomme]

Bonjour à tous,

Un essai sans macro.
On utilise une feuille masquée nommée"Auxil"

Sur cette feuille:

• la colonne A possède une formule donnant le nom des 10 feuilles Feuil1 à Feuil10
• la colonne B renvoie la valeur de la cellule A1 de la feuille mentionnée en colonne A de la même ligne
• la cellule D2 renvoie le max de la colonne B
• la cellule E2 renvoie le nom de la première feuille contenant le maximum

Dans tout autre feuille une référence à Auxil!D2 renvoie le max et une référence à Auxil!E2 renvoie le nom de la feuille du max.

nota : on peut changer sans problème le nom des feuilles concernées (Feuil1 à Feuil10 et Feuille Auxil)

Attention : La formule de la feuille "Auxil" en E2 est une formule matricielle !

 

[turame]

Merci pour cette réponse, je vais regarder ça de plus près.

 

[laurent950]

Re,
Si avec une formule (Choix 1 ou 2) : Coller se Module dans VBA
= MaxVal(1) C'est le nom de la feuille
= MaxVal(2) C'est la valeur max de la feuille

Public Type TrouveVal
    FeuiMax As String
    NbMax As Long
    Feuil As Variant
End Type
Sub DescribeFunction()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 1) As String

   FuncName = "MaxVal"
   FuncDesc = "Retourne la valeur Max qui correspond à la feuille ou ce trouve cette valeur"
   Category = 13 ' category 13  Personnalisées
   ArgDesc(1) = "1 = Nom de la Feuille Ou 2 = Valeur Max de la Feuille"

   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub

Function MaxVal(ByVal Nb As Byte) As Variant
Dim Res As TrouveVal

Res.Feuil = Array("un", "deux", "trois", "quatre", "cinq", "six", "sept", "huit", "neuf")

Dim Ws As Worksheet
Dim tableau() As Variant
' Hypothése Classeur de 10 Feuilles (Uniquement)
ReDim Preserve tableau(1 To (ThisWorkbook.Sheets.Count - 1), 1 To 2)
    For Each Ws In ThisWorkbook.Worksheets
        For i = 0 To 8
            If LCase(Ws.Name) = LCase(Res.Feuil(i)) Then
                tableau(Ws.Index, 1) = Ws.Name
                tableau(Ws.Index, 2) = Ws.Cells(1, 5)
            End If
        Next i
    Next Ws
' Stock
    Res.FeuiMax = tableau(Application.Match(WorksheetFunction.Max(Application.Index(tableau, , 2)), Application.Index(tableau, , 2), 0), 1)
    Res.NbMax = tableau(Application.Match(WorksheetFunction.Max(Application.Index(tableau, , 2)), Application.Index(tableau, , 2), 0), 2)
' Resultat
If Nb = 1 Then
    MaxVal = Res.FeuiMax
Else
    MaxVal = Res.NbMax
End If
End Function