Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

pilote301152

XLDnaute Occasionnel
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
 
Solution

pilote301152

XLDnaute Occasionnel
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

XLDnaute Barbatruc
Inactif
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
 

job75

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

Voyez celle-ci, très simple, dans le fichier joint :
VB:
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.
 

Pièces jointes

  • Fonction MaxTexte(1).xlsm
    564 KB · Affichages: 17
Dernière édition:

soan

XLDnaute Barbatruc
Inactif
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

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

soan

XLDnaute Barbatruc
Inactif
@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
 

soan

XLDnaute Barbatruc
Inactif
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) :
VB:
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
 

Pièces jointes

  • test.xlsm
    16.8 KB · Affichages: 3
Dernière édition:

pilote301152

XLDnaute Occasionnel
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

XLDnaute Occasionnel
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

XLDnaute Barbatruc
Inactif
@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
 
Dernière édition:

pilote301152

XLDnaute Occasionnel
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
 

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…