Pour trouver la chaîne la plus longue dans une colonne dans Google Sheets, nous pouvons utiliser les formules Index-Match ou Filter. Les deux formules ont leurs avantages et leurs inconvénients. Je vais expliquer cela plus tard. Tout d’abord, essayons de comprendre le scénario.
Il s’agit de trouver des chaînes en fonction du nombre maximum de caractères. Supposons que j’ai une liste de noms dans la plage B2:B de Google Sheets. Je veux renvoyer le nom qui a le nombre maximum de caractères.
Deux formules pour trouver la chaîne la plus longue dans une colonne dans Google Sheets
Nous commencerons par Index-Match en combinaison avec Max et Len.
Option 1
Appliquez la formule suivante dans la cellule C2.
=index(B2:B, match(max(len(B2:B)),len(B2:B),0), 0 )
La formule d’index-match ci-dessus renvoie la chaîne la plus longue dans la plage de colonnes B2:B dans la cellule C2, qui est « Ernestine ».
Pouvez-vous l’expliquer ?
Pourquoi pas ? Voici comment cela fonctionne !
Syntaxe :-
INDEX( référence, ligne, colonne )Référence – B2:B
Ligne – match(max(len(B2:B)),len(B2:B),0)
Colonne – 0
La formule de correspondance ci-dessus (ligne) joue un rôle essentiel dans la recherche de la chaîne la plus longue dans la colonne B. Elle renvoie la position relative de la chaîne la plus longue dans la plage de cellules B2:B. L’index décale alors de ce nombre de lignes et de zéro colonnes. Dans l’exemple ci-dessus, match renvoie le nombre 10, c’est-à-dire la position relative de la chaîne la plus longue dans B2:B. Donc, l’index se décale de dix lignes et de zéro colonnes.
Essayons de comprendre la formule MATCH. La voici !
Syntaxe :-
MATCH(clé_recherche, plage, type_recherche)Clé de recherche – max(len(B2:B))
Plage – len(B2:B)
Type de recherche – 0
Si vous pouvez comprendre la partie len(B2:B), vous pouvez facilement comprendre la formule. La voici !
Remarque : – Dans l’index, nous pouvons utiliser len(B2:B), mais lorsqu’il est utilisé seul, vous devez l’utiliser comme arrayformula(len(B2:B)).
La copie d’écran ci-dessus (colonne C) représente la plage de formule MATCH (la fonction LEN renvoie la longueur des caractères de chaque nom). La clé de recherche est la valeur MAX dans cette plage (colonne C) qui sera 9 (veuillez voir les cellules en cyan). Donc, MATCH correspond à la longueur de caractère 9 (clé de recherche) et renvoie 10, qui est la position relative de la clé de recherche dans la plage.
Option 2
Voici une autre formule que vous pouvez utiliser pour trouver la chaîne la plus longue dans une colonne dans Google Sheets.
=filter(B2:B,len(B2:B)=max(len(B2:B)))Elle est beaucoup plus propre que la formule OPTION 1 ci-dessus. Ici, la formule filtre les noms en fonction de la longueur des chaînes. C’est comme cela :
Si la longueur des chaînes est égale à sa longueur maximale dans la plage, renvoyez cette valeur. Cette formule renvoie parfois plus d’une valeur. Par exemple, si deux noms ont neuf caractères chacun, la formule renverra les deux noms. Mais l’Index-Match ne renverra que le premier nom.
Formule Array pour trouver la chaîne la plus longue dans chaque colonne
Si vous souhaitez effectuer la même évaluation dans chaque colonne, vous pouvez vous appuyer sur une formule de tableau en remplacement de l’index-match.
Dans l’exemple ci-dessous, la formule dans la cellule B8 trouve les valeurs les plus longues dans chaque colonne de la plage B2:F6.
=ArrayFormula(vlookup(dmax({column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1),{column(B1);if(,,)}), H2:I5, 2,0))
Explication de la formule
C’est une formule de recherche en colonne. Syntaxe :
ARRAYFORMULA(VLOOKUP(clé_recherche, plage, index, [trié]))Voici la référence de la plage dans cette formule.
Plage
Regardez la plage auxiliaire H2:I5. Elle agit comme la plage dans Vlookup. La plage I2:I5 contient toutes les chaînes uniques dans B2:F6. Vous pouvez les saisir manuellement ou utiliser la formule suivante dans I2.
=sort(filter(unique(flatten(B2:F6)),unique(flatten(B2:F6))<>""))Dans H2:H5, j’ai saisi la longueur des chaînes uniques ci-dessus. J’ai utilisé la formule suivante dans la cellule H2.
=ArrayFormula(len(I2:I5))Revenons maintenant à la formule de tableau dans la cellule B8 qui renvoie la chaîne la plus longue dans chaque colonne. Nous avons déjà vu la plage utilisée dans cette formule ci-dessus. Les clés de recherche sont la formule DMAX qui est la suivante.
Clé de recherche
dmax({column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1),{column(B1);if(,,)})Testons-le. Pour cela, nous devons l’envelopper avec la fonction ARRAYFORMULA comme suit.
=ArrayFormula(dmax({column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1),{column(B1);if(,,)}))
La fonction DMAX ci-dessus renvoie la longueur de la plus grande chaîne dans chaque colonne. La recherche l’utilise comme clé de recherche pour rechercher la première colonne dans la plage H2:I5 et renvoie les valeurs correspondantes de I2:I5.
Explication de DMAX (Clé de recherche)
Syntaxe :
DMAX(database, champ, critères)Base de données – {column(B1:F1);len(B2:F6)}
Comme vous le savez peut-être, les fonctions de base de données nécessitent des étiquettes de champ. Étant donné que notre plage ci-dessus B2:F6 ne les contient pas, j’ai utilisé les numéros de colonne comme étiquettes de champ. Testons la base de données (nous devons utiliser ARRAYFORMULA avec la base de données dans un test autonome).
Champ – sequence(1,columns(B1:F1),1)
Il est égal à spécifier ={1,2,3,4,5}, c’est-à-dire les index de colonne.
Critères – {column(B1);if(,,)}
Le premier label de champ est 2 (veuillez voir la cellule B8 de l’image ci-dessus), et =column(B1) renvoie cela. Le IF renvoie un caractère nul, ce qui signifie aucune condition.
Voilà tout sur la façon de trouver la chaîne la plus longue dans chaque colonne de Google Sheets. Merci de votre attention. Profitez-en !
Similaire : Trouver la chaîne la plus longue dans chaque ligne de Google Sheets.
