XL 2016 Rechercher plus grande valeur dans une colonne avec données combinant chiffre puis texte

[pilote301152]

Bonjour,
je cherche une fonction pour extraire d'une colonne la donnée complète (Chiffre plus texte) avec le plus grand chiffre.
Les données sont soit "1 - Faible", "2 - Plutôt faible", "3 - Moyen" et "4 - Fort"
J'ai bien trouvé une solution partielle avec Grande.Valeur en créant une colonne supplémentaire qui extrait la partie numérique (avec cnum et gauche). Seulement je ne réussis pas à reconstituer la donnée complète (si Grande.valeur donne 4 je veux "4 - Fort" .......)
Connaissez-vous en outre un moyen plus direct d'obtenir le résultat sans passer par la création d'une colonne supplémentaire ?
Merci de vos suggestions.`Cordialement

 

[soan]

Bonjour pilote301152,

je te propose le fichier ci-dessous.

formule en B2 (déjà tirée vers le bas jusqu'en B5) :

=SI(A2="";"";SI(CODE(GAUCHE(A2))-48=4;A2;""))

soan

 

[JHA]

Bonjour à tous,

As-tu toujours les mêmes notations?
Peux-tu mettre un petit exemple sur fichier?

JHA

 

[pilote301152]

oui toujours ces 4 notations . Voir fichier exemple joint. Merci d'avance

 

[pilote301152]

Bonjour pilote301152,

je te propose le fichier ci-dessous.

formule en B2 (déjà tirée vers le bas jusqu'en B5) :

=SI(A2="";"";SI(CODE(GAUCHE(A2))-48=4;A2;""))

soan

Bonjour Soan et merci pour cette proposition. Mais si il n'y a pas de notation "4 - Fort" comment extraire la plus forte existante ?

 

[job75]

Bonjour pilote301152,

En supposant que les données soient dans la plage B2:B100 utilisez cette formule matricielle :

=RECHERCHEV(MAX(SIERREUR(--GAUCHE(B2:B100);))&"*";B2:B100;1;0)

à valider par Ctrl+Maj+Entrée.

A+

 

[pilote301152]

Bonjour pilote301152,

En supposant que les données soient dans la plage B2:B100 utilisez cette formule matricielle :

=RECHERCHEV(MAX(SIERREUR(--GAUCHE(B2:B100);))&"*";B2:B100;1;0)

à valider par Ctrl+Maj+Entrée.

A+

Bonjour Job75 et grand merci car cela fonctionne à merveille !
Bonne journée à tous

 

[soan]

@pilote301152 (salut job75)

tu as écrit : « Mais s'il n'y a pas de notation "4 - Fort" comment extraire la plus forte existante ? » ; alors voici une autre solution, avec une fonction personnalisée :​

Function NivMax() As String
  Application.Volatile
  Dim n&: n = Cells(Rows.Count, 3).End(3).Row: If n < 7 Then Exit Function
  Dim s$, i&, j&, k As Byte, m As Byte
  For i = 7 To n
    s = Cells(i, 3)
    If s <> "" Then
      k = Asc(Left$(s, 1)) - 48: If k > m Then m = k: j = i
    End If
  Next i
  If m > 0 Then NivMax = Cells(j, 3)
End Function

formule en B1 : =NivMax() ; y'a pas d'colonne D, et tu peux ajouter d'autres données sous C17 : elles seront automatiquement prises en compte, sans devoir changer la macro ; si y'a pas de "4 - Fort" dans les données, c'est "3 - Moyen" qui sera affiché (s'il y en a) ; idem pour les niveaux qui sont moins bons ; exemple : va en C15, et supprime le "4 - Fort" ➯ en B1, il y a maintenant : "3 - Moyen" ; accessoirement, tu peux voir que ça marche même si y'a une cellule vide au milieu des autres données. ​

---

j'ai essayé la formule de job75 ; en l'adaptant à ton fichier, ça donne :

=RECHERCHEV(MAX(SIERREUR(--GAUCHE(C7:C100);))&"*";C7:C100;1;0)​

(formule matricielle, à valider par Ctrl Maj Entrée)

bien sûr, il ne faudra pas oublier d'adapter le C100 (2×) si tu ajoutes beaucoup de données et que ça dépasse la ligne 100.​

soan

 

[pilote301152]

@pilote301152 (salut job75)

tu as écrit : « Mais s'il n'y a pas de notation "4 - Fort" comment extraire la plus forte existante ? » ; alors voici une autre solution, avec une fonction personnalisée :​

Function NivMax() As String
  Application.Volatile
  Dim n&: n = Cells(Rows.Count, 3).End(3).Row: If n < 7 Then Exit Function
  Dim s$, i&, j&, k As Byte, m As Byte
  For i = 7 To n
    s = Cells(i, 3)
    If s <> "" Then
      k = Asc(Left$(s, 1)) - 48: If k > m Then m = k: j = i
    End If
  Next i
  If m > 0 Then NivMax = Cells(j, 3)
End Function

formule en B1 : =NivMax() ; y'a pas d'colonne D, et tu peux ajouter d'autres données sous C17 : elles seront automatiquement prises en compte, sans devoir changer la macro ; si y'a pas de "4 - Fort" dans les données, c'est "3 - Moyen" qui sera affiché (s'il y en a) ; idem pour les niveaux qui sont moins bons ; exemple : va en C15, et supprime le "4 - Fort" ➯ en B1, il y a maintenant : "3 - Moyen" ; accessoirement, tu peux voir que ça marche même si y'a une cellule vide au milieu des autres données. ​

---

j'ai essayé la formule de job75 ; en l'adaptant à ton fichier, ça donne :

=RECHERCHEV(MAX(SIERREUR(--GAUCHE(C7:C100);))&"*";C7:C100;1;0)​

(formule matricielle, à valider par Ctrl Maj Entrée)

bien sûr, il ne faudra pas oublier d'adapter le C100 (2×) si tu ajoutes beaucoup de données et que ça dépasse la ligne 100.​

soan

Effectivement cette solution sous VBA fonctionne également. Je retiens son code car très formatrice pour moi. Encore merci et bonne journée

 

[Rouge]

Bonjour à tous,

Tant que nous y sommes, encore une autre formule en B2 à valider avec CTRL + SHIFT + ENTREE

=INDIRECT("C"&EQUIV(MAX(GAUCHE($C$7:$C$17;1)*1);GAUCHE($C$7:$C$17;1)*1;0)+6)

Cdlt

 

[pilote301152]

Merci Rouge mais pour info cette formule ne fonctionne pas si il y a une notation absente dans la série !

Cordialement

 

[pilote301152]

Bonjour pilote301152,

En supposant que les données soient dans la plage B2:B100 utilisez cette formule matricielle :

=RECHERCHEV(MAX(SIERREUR(--GAUCHE(B2:B100);))&"*";B2:B100;1;0)

à valider par Ctrl+Maj+Entrée.

A+

Bonsoir job75
Après avoir utilisé cette solution j'essaie à présent de tout comprendre de cette fonction.
J'aurais 2 questions : pourquoi insérer "si erreur"? et quelle est la signification des -- avant la fonction "gauche" ? En effet ces 2 insertions sont indispensables au bon fonctionnement de la formule ...
Cordialement

 

[soan]

Bonsoir pilote301152, le fil,

GAUCHE(B2:B100) : idem que GAUCHE(B2:B100;1) : pour chaque cellule de B2:B100, ça retourne le 1er caractère de gauche : un chiffre de 1 à 4 ; mais c'est le texte "1" à "4", c'est pas numérique.

avec --GAUCHE(B2:B100) le 1er signe moins convertit le chiffre_texte en chiffre_nombre ; comme il devient négatif, on double le signe moins pour rétablir le signe positif ; exemple : -(-1) = +1 = 1.

enfin, au cas où ça retournerait une valeur d'erreur (du genre #VALEUR! ou #N/A!), pour éviter qu'elle soit affichée, on utilise SIERREUR() ; je te laisse lire l'Aide Excel pour plus d'infos. ​

soan

 

[pilote301152]

Bonjour Soan et merci beaucoup pour ces explications ; Bonne journée

 

[job75]

Bonjour pilote301152, le forum,

Avec une plage comme B2:B100 ou B2:B1000 il y a forcément des cellules vides.

Avec GAUCHE elles donnent le texte vide "" qui avec -- donne la valeur d'erreur #VALEUR!.

SIERREUR(xxx; ) transforme cette valeur d'erreur en zéro.

A+