XL 2019 Trouver la valeur MAX -1

  • Initiateur de la discussion Initiateur de la discussion atalec
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

atalec

XLDnaute Nouveau
Bonjour

J'aimerai savoir comment je dois m'y prendre pour trouver la valeur de ma colonne qui est juste avant la valeur MAX

Par exemple si ma valeur la plus haute est 10 je voudrais trouver 9

J'ai fais un essai avec une formule MAX(...;...)-1 mais c'est pas concluant

Quelle formule utiliser ?

Merci
 
Bonjour à tous🙂,

Juste pour le fun, une fonction personnalisée nommée GrdeVal(TS , Mois , Rang) qui en entrée prend 3 paramètres :
  • TS est la plage du tableau structuré (avec les en-têtes)
  • Mois est le numéro du mois souhaité
  • Rang est le numéro de la plus grande valeur à extraire (sans doublons) : 1 -> on extrait la plus grande valeur, 2 -> on extrait la seconde plus grande valeur, etc.
Nota : si Rang est négatif, on extrait non pas les plus grandes mais les plus petites valeurs : -1 -> on extrait la plus petite valeur, -2 -> on extrait la seconde plus petite valeur, etc.

Voir les formules en G23 à G25. Exemple pour la seconde plus grande valeur : =GrdeVal(Tableau5[#Tout] ; 6 ; 1)

Le code de la fonction est dans Module8 (le code est légèrement commenté et il fonctionne sur Apple) :
VB:
Function GrdeVal(TS As Range, Mois&, Rang&)
Dim t, j&, i&, n&, coll As New Collection
   t = TS.Value: ReDim r(1 To 1)          ' t=array des valeur du TS, r futur array des valeurs sans doublon
   For j = 1 To UBound(t, 2)              ' boucle sur les colonnes de t
      If IsDate(t(1, j)) Then             ' si l'en-tête (1ère ligne) de la colonne j est une date
         If Month(t(1, j)) = Mois Then    ' si l'en-tête du mois a pour numéro de mois : Mois
            On Error Resume Next          ' en cas d'erreur, on continue l'exécution
            For i = 2 To UBound(t)        ' boucle sur les élément de la colonne
               ' si la valeur n'est pas vide et est numérique, on essaye de l'ajouter à la collection
               If t(i, j) <> "" Then If IsNumeric(t(i, j)) Then n = coll.Count: coll.Add t(i, j), CStr(t(i, j))
               ' si le nombre d'éléments de coll avant ajout est différent du nombre d'éléments de coll après ajout
               ' alors t(i,j) n'était pas un doublon et on l'ajoute au tableau r des éléments sans doublon
               If n <> coll.Count Then ReDim Preserve r(1 To coll.Count): r(coll.Count) = t(i, j)
            Next i
         End If
      End If
   Next j
   ' r contient l'ensemble des valeurs sans doublon - on en prend la valeur d'index Rang
   If Rang <= 0 Then GrdeVal = Application.Small(r, Abs(Rang)) Else GrdeVal = Application.Large(r, Rang)
   ' si GrdeVal est une erreur alors c'est que la valeur d'index Rang n'existe pas => on retourne ""
   If IsError(GrdeVal) Then GrdeVal = ""
End Function

edit : bonjour @job75😉
 

Pièces jointes

Dernière édition:
Bonjour le forum,

La fonction UNIQUE est disponible sur Office 365 ou à partir d'Excel 2021.

Pour répondre à TooFatBoy voici une fonction VBA très simple qui peut la remplacer :
VB:
Function ValUnique(matrice)
Dim e, coll As New Collection, a, i
On Error Resume Next
For Each e In matrice
    If Not IsError(e) Then coll.Add e, CStr(e)
Next e
ReDim a(1 To coll.Count)
For i = 1 To UBound(a): a(i) = coll(i): Next i
ValUnique = a 'vecteur horizontal
End Function
Comme mapomme j'utilise une collection au lieu du Dictionary qui n'est pas disponible sur MAC.

Les valeurs d'erreur sont ignorées.

Dans le fichier joint formule matricielle en G23 à copier vers le bas :
Code:
=GRANDE.VALEUR(ValUnique((MOIS(Tableau5[#En-têtes])=6)*Tableau5);F23)
A+
 

Pièces jointes

La fonction UNIQUE est disponible sur Office 365 ou à partir d'Excel 2021.
Oui c'est ce qu'on peut voir dans le lien donné par djidji que je remercie.


Pour répondre à TooFatBoy voici une fonction VBA très simple qui peut la remplacer
Merci pour ta réponse, mais ça ne correspond pas à ce que je demandais, puisque je demandais une formule et non une macro.

Je crois que tes propositions ne correspondent pas non plus à la question initiale, car à mon avis la formule ne doit travailler que sur une colonne à la fois pour retourner son résultat, et donc être "translatable" de colonne en colonne.

[edit] Comme l'a fait remarquer mapomme, le texte de #1 vient d'être modifié. Mon intuition était donc la bonne puisque maintenant il est écrit clairement qu'il faut travailler sur une colonne. [/edit]
 
Dernière édition:
En gros je recherchai une formule me donnant :
- soit la liste sans doublon des éléments d'une colonne de son tableau, pour l'intégrer dans un GRANDE.VALEUR ;
- soit la liste sans doublon et triée des éléments d'une colonne de son tableau, pour l'intégrer dans un INDEX.

N'arrivant pas à pondre une telle formule, j'ai fait des recherches et suis tombé sur une formule (donnée par job75 sur ce forum) qui donnent un élément de la liste sans doublon.
J'ai essayé essayé de la modifier pour qu'elle me retourne la liste complète au lieu d'un seul élément, mais je n'ai pas réussi... ☹️
 
Dernière édition:
Si tu rêves mapomme, alors je rêve aussi.

Effectivement, je ne l'avais pas remarqué mais si la question est toujours la même, ou du moins son objectif est identique, en revanche le texte a bien été modifié. 😲


Bien vu, camarade bleu. 👍
 
Dernière édition:
S'il faut travailler sur chaque colonne séparément voyez ce fichier (3) avec les 3 conditions de la MFC :
Code:
=(B2=GRANDE.VALEUR(ValUnique((MOIS(B$1)=6)*B$2:B$20);1))/B2
=(B2=GRANDE.VALEUR(ValUnique((MOIS(B$1)=6)*B$2:B$20);2))/B2
=(B2=GRANDE.VALEUR(ValUnique((MOIS(B$1)=6)*B$2:B$20);3))/B2
Evidemment dans les MFC la validation matricielle n'est jamais nécessaire.
 

Pièces jointes

En gros je recherchai une formule me donnant :
- soit la liste sans doublon des éléments d'une colonne de son tableau, pour l'intégrer dans un GRANDE.VALEUR ;
- soit la liste sans doublon et triée des éléments d'une colonne de son tableau, pour l'intégrer dans un INDEX.
Toujours pas de réponse à ma question. 🙁
Est-ce que ça veut dire qu'aucune solution n'existe ?
 
Bonjour

J'aimerai savoir comment je dois m'y prendre pour trouver la valeur de ma colonne qui est juste avant la valeur MAX

Par exemple si ma valeur la plus haute est 10 je voudrais trouver 9

J'ai fais un essai avec une formule MAX(...;...)-1 mais c'est pas concluant

Quelle formule utiliser ?

Merci
Bonjour je pige pas tout ce ramdam moi
=GRANDE.VALEUR(A1:A10; 2)
grande.valeur matrice , index te donne ça normalement non?
maintenant le fait est que si par exemple le plus grand est 100 mais qu'il n'y a pas 99 il te donnera celui qui est juste en dessous par ordre de grandeur de la matrice
je dis ça je me fit au premier message mais comme il y a deux pages de réponses je me doute que le sujet a encore dévié
merci pour les lecteurs qui vous liront
1 post = 1 sujet c'est pourtant pas compliqué à comprendre
charte paragraphe 2.3
 
re
bien vu @djidji59430
alors la solution est simple
soit on fait un grandevaleur sur FILTRE(matrice;NB.Si(....));2
soit alors on travaille avec SI frequence car je n'ai pas filtre dans ma version 2013
et comme je code toujours vba ou formule dans le but d'avoir une compatibilité la plus grande en terme de version d'excel
exemple
=GRANDE.VALEUR(SI(FREQUENCE(A1:A10; A1:A10)>0; A1:A10); 2)
je sais pas si on l'a pas sur 2007 frequence je ne me souviens plus

Attention si il y a des vides il va falloir gérer

on peut le faire avec unique aussi

patrick
 
Dernière édition:
Bonjour
sinon avec une fonction perso toute simple
VB:
Function Grande_ValeurX(rng As Range, Optional N As Long = 0)
    Dim d As Object, tb
    tb = rng.Value
    Set d = CreateObject("scripting.dictionary")
    For i = 1 To UBound(tb): d(Val(tb(i, 1))) = "": Next
    Grande_ValeurX = WorksheetFunction.Small(d.keys, UBound(d.keys) - (N - 1))
End Function

formule :
=Grande_ValeurX(A1:A15;1)
le 1 c'est pour la 1ere valeur en dessous le max

les vides ainsi que les texte sont gérés sans de problème puisque que ca vaut 0 avec val

et si on veut gérer une plage éventuellement filtrée ::

VB:
Function Grande_ValeurX(rng As Range, Optional n As Long = 1)
    Dim d As Object, cell As Range
    Set d = CreateObject("Scripting.Dictionary")
    For Each cell In rng.Cells
        If cell.EntireRow.Hidden = False Then d(Val(cell.Value)) = ""
    Next
    If d.Count < n Then Grande_ValeurX = CVErr(xlErrNA): Exit Function
         Grande_ValeurX = WorksheetFunction.Small(d.Keys, d.Count - ((n - 1) + 1))
End Function
 
Dernière édition:
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

  • Résolu(e)
Microsoft 365 DATEDIF
Réponses
11
Affichages
184
Retour