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

 

[pilote301152]

bonjour et merci Job75 pour cette réponse claire et précise.
Bonne journée

 

[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

Bonjour Soan,
je ne comprends pas la formule utilisée pour définir k (utilisation de Acs qui renvoie un code asti moins 48 !!!!!!?!) et pourquoi left$ (pour changer le retour en integer ?)
Merci de ton éclairage car je n'ai pas trouvé de réponse sur aide en ligne ....

 

[soan]

Bonjour pilote301152, le fil,

j'viens d'lire ton post #17.

k = Asc(Left$(s, 1)) - 48

* pour une ligne i : s = Cells(i, 3) ➯ s est le texte en colonne C,
par exemple, pour la ligne 15 : s = "4 - Fort"

* Left$(s, 1) retourne le 1er caractère de gauche de s ➯ "4" ;
attention : c'est le texte "4" et pas le nombre 4 !

* Asc(Left$(s, 1)) est donc Asc("4") ➯ 52 ; en effet,
le code ASCII du caractère « 4 » est 52.

* il faut donc enlever 48 pour passer de 52 à 4 : 52 - 48 = 4 ; cette fois, c'est bien un nombre, et on peut s'en servir pour trouver quel est le chiffre maximum de la plage C7:C17 ; note que pour cet exo, comme c'est le j (n° de la ligne) qui servira ensuite, et pas k, j'aurais pu éviter le -48 ; car même sans, ça aurait trouvé le maximum par comparaison des nombres 49 à 52 au lieu de comparer les nombres 1 à 4.

essaye la macro avec cette ligne :​

k = Asc(Left$(s, 1)): If k > m Then m = k: j = i

tu pourras voir que ça fonctionne tout aussi bien ; en fait, j'avais mis le - 48 pour que tu puisses comprendre plus facilement mon code VBA : tu sais que 4 est le chiffre de "4 - Très fort", mais 52, tu aurais pu te demander : « késaco ? » ​

moi qui avais cru pouvoir échapper à une demande d'explications, c'est loupé !

soan

 

[pilote301152]

Bonsoir Soan. et merci encore pour ces explications très précises . J'ai tout compris et te remercie vivement de ta réactivité.

 

[job75]

@soan chaque fois que c'est possible il faut éviter les fonctions volatiles.

Voyez celle-ci, très simple, dans le fichier joint :

Function MaxTexte$(plage As Range)
Dim i&, maxi$
Set plage = Intersect(plage, plage.Parent.UsedRange)
For i = 1 To plage.Count
    If plage(i) > maxi Then maxi = plage(i)
Next
MaxTexte = maxi
End Function

Avec 100 000 cellules remplies le recalcul de la formule en B1 se fait en 0,23 seconde chez moi.

 

[soan]

Bonsoir job75,

j'ai utilisé Application.Volatile afin que le résultat soit mis à jour même si on change une donnée de la plage C7:C17 (modification ou suppression) ; mais bien sûr, c'est mieux si ta méthode est plus efficace que ma sub et se passe de Application.Volatile ! ​

soan

 

[pilote301152]

Bonsoir job75,

j'ai utilisé Application.Volatile afin que le résultat soit mis à jour même si on change une donnée de la plage C7:C17 (modification ou suppression) ; mais bien sûr, c'est mieux si ta méthode est plus efficace que ma sub et se passe de Application.Volatile ! ​

soan

Vous êtes super merci à vous pour toutes ces astuces et conseils

 

[pilote301152]

Vous êtes super merci à vous pour toutes ces astuces et conseils

 

[soan]

@job75, pilote301152,

j'avais pas eu l'temps d'bien regarder ta proposition du post #20.

c'est effectivement très rapide, et ça marche très bien ! bravo !

mais y'a une chose qui m'intrigue :

* l'argument de ta fonction est plage As Range

* ta formule en B1 est : =MaxTexte(B2:B1048576)

* donc plage est B2:B1048576

pour tout ça : ok ! mais pour Set plage = Intersect(plage, plage.Parent.UsedRange)

quand tu mets : plage.Parent.UsedRange c'est quoi le Parent de plage ?

est-ce que le Parent de B2:B1048576 n'est pas la feuille active ?

si oui, on pourrait peut-être mettre :​

Set plage = Intersect(plage, ActiveSheet.UsedRange)

mais ça m'paraît bizarre, et j'pense que j'ai dû m'tromper quelque part !

---

d'autre part, je vais bientôt proposer à pilote301152 une autre solution très différente de celles qu'on lui a déjà proposé ; c'est assez spécial, et peut-être qu'il aimera ? ce sera aussi sans Volatile, et super rapide !​

soan

 

[job75]

Il faut utiliser plage.Parent qui est la feuille du Range plage, ce n'est pas forcément la feuille active.

 

[soan]

Bonjour pilote301152, job75,​

je te laisse ouvrir le fichier joint ci-dessous.

* note que le niveau maxi est : 3 Moyen ; si tu regardes les données, c'est bien ça !

* note que B15:C15 est vide, et que la cellule active est B15

* en B15, saisis 1 ➯ en C15 : Très Faible ; niveau maxi : inchangé

* en B15, saisis 2 ➯ en C15 : Faible ; niveau maxi : inchangé

* en B15, saisis 3 ➯ en C15 : Moyen ; niveau maxi : inchangé

* en B15, saisis a ➯ y'a un message ; niveau maxi : inchangé

* en B15, saisis 5 ➯ y'a un message ; note que B1:C1 est vide

* en B15, saisis 4 ➯ en C15 : Fort ; niveau maxi : 4 Fort

* avec la touche Suppression, efface le 4 que tu viens de saisir en B15
➯ C15 est vide ; niveau maxi : c'est de nouveau : 3 Moyen

* ça marche pareil pour toute cellule jaune de B7:B17
​

---

* formule en B1 : =SI(MAX(B7:B17)>4;"";MAX(B7:B17))

* formule en C1 : =SIERREUR(CHOISIR(B1;"très faible";"Faible";"Moyen";"Fort");"")

* bien sûr, pour B7:B17, j'ai mis une Validation de données : seuls les nombres 1 à 4 sont autorisés.

* la feuille est protégée, mais c'est une protection simple sans mot de passe ; ça permet d'éviter que les formules de B1 et C1 soient écrasées par inadvertance ; de plus, j'ai masqué les formules ➯ quand la feuille est protégée, les formules n'apparaissent pas dans la barre de formules. (ça fait plus clean dans le sens où un utilisateur néophyte d'Excel ne sera pas perturbé à cause d'une formule qu'il ne comprend pas ; et ça te permet aussi de cacher une formule que tu as faite et que tu ne veux pas divulguer ; mais pour ce 2ème cas, il faut bien sûr protéger la feuille avec un mot de passe qui est difficile à deviner)

* note que d'avoir séparé le chiffre du Niveau du reste du Niveau fait que c'est beaucoup plus simple ! y'a plus besoin d'extraire le chiffre du Niveau (c'est à dire le 1er caractère) ; y'a plus besoin de convertir le texte "4" en nombre 4 ; et une simple fonction =Max(B7:B17) suffit pour afficher le niveau maxi ! ce que j'y ai ajouté pour avoir =SI(MAX(B7:B17)>4;"";MAX(B7:B17)) est juste pour éviter l'affichage d'un nombre supérieur à 4 quand tu saisis par exemple 5 ; car même s'il est refusé par la Validation de données, sans mon complément de formule : le 5 s'affichait quand même en B1, en arrière-plan de la boîte de dialogue du message d'erreur de saisie.

* en B1 et C1, l'affichage du niveau maxi est instantané (moins de 0,02 seconde !), et y'a plus d'fonction qui contient l'instruction VBA Application.Volatile !

avec tous ces avantages, il va de soi que perso, j'préfère largement cette version ! mais bien sûr, si toi tu préfères quand même que le chiffre du Niveau soit « collé » avec le reste du Niveau, c'est ton choix qui prime avant tout puisque c'est toi l'utilisateur final ! par contre, la solution de ce post ne te sera alors d'aucune utilité, et il vaudra mieux que tu utilises une des solutions précédentes !
​

---

le seul code VBA existant est pour quand tu changes la valeur d'une cellule de la plage B7:B17 : comme tu as pu le voir lors de la démo, ça modifie de façon adéquate la cellule qui est juste à droite, donc une des cellules de la plage C7:C17 ; voici ce code VBA (qui est placé dans le module de Feuil1) :
​

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Niv, chn$, vx&
  With Target
    If .CountLarge > 1 Then Exit Sub
    If Intersect(Target, [B7:B17]) Is Nothing Then Exit Sub
    Niv = Array("Très faible", "Faible", "Moyen", "Fort")
    chn = .Value: vx = Val(chn)
    If vx > 0 And vx < 5 Then chn = Niv(vx - 1) Else chn = "@"
    .Offset(, 1) = IIf(chn = "@", Empty, chn)
  End With
End Sub

si besoin, tu peux demander une adaptation.
à te lire pour avoir ton avis.

​

---

@job75 : ah, ok ! merci pour ton explication ! ​

soan

 

[pilote301152]

Merci Soan pour cette proposition. Malheureusement il n'est pas possible de scinder la "notation" (ce n'est pas moi qui ait la main sur la base des données initiales) . Mon problème quant à la recherche du "max" tenait justement à cette cohabitation de chiffre et texte. Ceci étant je te remercie pour les idées associées à ta dernière proposition et aurai encore une question sur le code VBA:
IIf(chn = "@", Empty, chn)
que signifie cette formule conditionnelle avec If (deux I !!!!) ?
Merci encore

 

[pilote301152]

Merci Soan pour cette proposition. Malheureusement il n'est pas possible de scinder la "notation" (ce n'est pas moi qui ait la main sur la base des données initiales) . Mon problème quant à la recherche du "max" tenait justement à cette cohabitation de chiffre et texte. Ceci étant je te remercie pour les idées associées à ta dernière proposition et aurai encore une question sur le code VBA:
IIf(chn = "@", Empty, chn)
que signifie cette formule conditionnelle avec If (deux I !!!!) ?
Merci encore

Oups j'ai oublié une autre question :
Pourquoi ta validation de données (valeur comprise entre mini 1 et max 4) ouvre une boîte de dialogue (BDD) avec Oui/ Non et donc autorise un 5 en tapant Oui ? Je préfère la BDD habituelle (Annuler / Recommencer) que j'obtiens du reste en créant une validation de données dans une autre plage (impossible d'entrer un chiffre supérieur à 4)....

 

[soan]

@pilote301152

c'est dommage que tu n'aies pas la main sur les données initiales ! tant pis... peut-être que mon long post #26 pourra quand même être utile à d'autres lecteurs de cette conversation ?​

1) le if avec 2 i est l'équivalent VBA de la fonction Excel SI() ; il y a donc 3 arguments :
* le 1er est la condition : si chn = "@" (c'est le cas où vx n'est pas un nombre de 1 à 4)
* le 2ème est pour le cas où le test est Vrai : Empty ; on efface la cellule de droite
* le 3ème est pour le cas où le test est Faux : chn ; on met chn à droite

2) effectivement, on peut quand même saisir 5 en cliquant sur le bouton "Oui" ; je trouve aussi que c'est un inconvénient, mais hélas, je n'ai pas la main sur ce comportement : c'est Microsoft qui a créé Excel, pas moi ! toutefois, j'ai fait le nécessaire pour réduire cet inconvénient : même si 5 est affiché, la cellule de droite est vide et B1:C1 est vide aussi. ​

soan

 

[pilote301152]

@pilote301152

c'est dommage que tu n'aies pas la main sur les données initiales ! tant pis... peut-être que mon long post #26 pourra quand même être utile à d'autres lecteurs de cette conversation ?​

1) le if avec 2 i est l'équivalent VBA de la fonction Excel SI() ; il y a donc 3 arguments :
* le 1er est la condition : si chn = "@" (c'est le cas où vx n'est pas un nombre de 1 à 4)
* le 2ème est pour le cas où le test est Vrai : Empty ; on efface la cellule de droite
* le 3ème est pour le cas où le test est Faux : chn ; on met chn à droite

2) effectivement, on peut quand même saisir 5 en cliquant sur le bouton "Oui" ; je trouve aussi que c'est un inconvénient, mais hélas, je n'ai pas la main sur ce comportement : c'est Microsoft qui a créé Excel, pas moi ! toutefois, j'ai fait le nécessaire pour réduire cet inconvénient : même si 5 est affiché, la cellule de droite est vide et B1:C1 est vide aussi. après tout, l'utilisateur de ton classeur Excel n'a qu'à saisir un nombre valide de 1 à 4 ; ce n'est pas encore quelque chose de trop difficile à faire ... à condition qu'il sache compter jusqu'à 4 ! et ne t'inquiètes pas : s'il veut absolument saisir un nombre autre que 1 à 4, il se lassera vite de la boîte de dialogue Erreur de saisie ! mébon, je ne perds pas de vue que ces infos ne te serviront pas à toi puisque tu ne peux pas utiliser cette solution ; ces infos seront donc pour d'autres éventuels lecteurs intéressés par cette conversation.​

soan

Toujours aussi réactif SOAN !
Je me suis peut être mal exprimé à propos de l'apparition du OUI/NON dans la boite de dialogue.
Je veux dire que si je crée en A15 (colorié en jaune vif) une validation conditionnelle j'obtiens dans la BDD le choix Annuler/Recommencer qui évite l'entrée d'une valeur erronée .......... alors que les validations conditionnelles que tu as créées en colonne B ne donnent pas les mêmes choix !!!!!!
Enfin note que cette dernière proposition même si non retenue m'auras permis d'apprendre ( toujours et encore ...). Merci