Trouver la chaîne de texte la plus longue avec des critères
Dans ce tutoriel, nous allons expliquer comment rechercher la chaîne de texte la plus longue dans une colonne ou une ligne avec des critères dans Excel à l'aide des fonctions INDEX, MATCH, LEN et MAX.
Comment trouver la chaîne de texte la plus longue avec des critères dans Excel ?
Pour trouver rapidement la personne venant d'Amérique avec le nom le plus long figurant dans la plage de noms ci-dessus, une formule combinant les fonctions INDEX, MATCH, LEN et MAX vous aidera de cette manière : La fonction MAX récupérera la plus grande valeur dans la plage de longueurs de chaînes fournies par la fonction LEN. Ensuite, MATCH localisera la position de la première valeur la plus longue dans la plage et INDEX récupérera la valeur correspondante à cette position.
Syntaxe générique
=INDEX(plage_retour,MATCH(MAX(LEN(plage_retour)*(plage_critère=valeur_critère)),LEN(plage_retour)*(plage_critère=valeur_critère),0))
√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être validée avec Ctrl + Shift + Enter.
- plage_retour : La plage où vous souhaitez que la formule combinatoire retourne le nom le plus long. Ici, cela fait référence à la plage de noms.
- plage_critère : La plage où les critères sont listés. Ici, cela fait référence à la plage de pays.
- valeur_critère : Le critère que vous définissez.
Pour trouver la personne venant d'Amérique avec le nom le plus long, veuillez copier ou entrer les formules ci-dessous dans la cellule F6, puis appuyez sur Ctrl + Shift + Enter pour obtenir le résultat :
=INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)*(C5:C11="Amérique")),LEN(B5:B11)*(C5:C11="Amérique"),0))
Ou, utilisez une référence de cellule pour rendre la formule dynamique :
=INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)*(C5:C11=F5)),LEN(B5:B11)*(C5:C11=F5),0))
Explication de la formule
=INDEX(B5:B11,MATCH(MAX(LEN(B5:B11)*(C5:C11=F5)),LEN(B5:B11)*(C5:C11=F5),0))
- LEN(B5:B11)*(C5:C11=F5): La fonction LEN renvoie un tableau des longueurs de chaque nom dans B5:B11 comme ceci : {5;5;6;3;4;8;5}; La partie (C5:C11=F5) vérifie si chaque valeur dans la plage C5:C11 est égale à la valeur dans F5, et génère un tableau comme celui-ci : {FAUX;FAUX;VRAI;FAUX;VRAI;FAUX;VRAI}, qui se transformera en {0;0;1;0;1;0;1} car il sera multiplié. Et voici ce que nous obtiendrons après la multiplication : {0;0;6;0;4;0;5}.
- MAX(MAX(LEN(B5:B11)*(C5:C11=F5))) = MAX(MAX({0;0;6;0;4;0;5})): La fonction MAX renvoie ensuite la plus grande valeur du tableau {0;0;6;0;4;0;5}, qui est 6.
- MATCH(MATCH(MAX(LEN(B5:B11)*(C5:C11=F5)LEN(B5:B11)*(C5:C11=F5)),,LEN(B5:B11)*(C5:C11=F5)LEN(B5:B11)*(C5:C11=F5),0),0) = MATCH(MATCH(6,,{0;0;6;0;4;0;5},0),0): Le type_de_correspondance 0 force la fonction MATCH à trouver la position du premier nombre exact 6 dans le tableau {0;0;6;0;4;0;5}. Elle renvoie donc 3, car le nombre est à la 3ème position.
- INDEX(B5:B11B5:B11,,,MATCH(MAX(LEN(B5:B11)*(C5:C11=F5)LEN(B5:B11)*(C5:C11=F5))),,,LEN(B5:B11)*(C5:C11=F5)LEN(B5:B11)*(C5:C11=F5),0))) = INDEX(B5:B11B5:B11,,,3) : La fonction INDEX renvoie la 3ème valeur dans la plage de noms B5:B11, qui est Oliver.
Fonctions connexes
La fonction INDEX d'Excel renvoie la valeur affichée en fonction d'une position donnée dans une plage ou un tableau.
La fonction MATCH d'Excel recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de cette valeur.
La fonction LEN renvoie le nombre de caractères dans une chaîne de texte.
Formules connexes
Trouver la chaîne de texte la plus longue ou la plus courte dans une colonne ou une ligne
Pour rechercher la chaîne de texte la plus longue ou la plus courte dans une colonne ou une ligne dans Excel, vous pouvez utiliser ensemble les fonctions INDEX, MATCH, LEN et MAX ou MIN.
Correspondance approximative avec INDEX et MATCH
Il arrive que nous devions trouver des correspondances approximatives dans Excel pour évaluer la performance des employés, noter les scores des étudiants, calculer les frais d'affranchissement en fonction du poids, etc. Dans ce tutoriel, nous expliquerons comment utiliser les fonctions INDEX et MATCH pour récupérer les résultats dont nous avons besoin.
Recherche de la valeur la plus proche avec plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la valeur la plus proche ou approximative basée sur plusieurs critères. Avec la combinaison des fonctions INDEX, MATCH et SI, vous pouvez y arriver rapidement dans Excel.
Les meilleurs outils de productivité pour Office
Kutools pour Excel - Vous aide à vous démarquer de la foule
Kutools pour Excel compte plus de 300 fonctionnalités, garantissant que ce dont vous avez besoin n'est qu'à un clic...
Office Tab - Active la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts !
- Réduit des centaines de clics de souris pour vous chaque jour, dites adieu à la main de la souris.
- Augmente votre productivité de 50 % lors de la consultation et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.