Nous pouvons utiliser une fonction Lambda pour trier des données séparées par des sauts de ligne dans les cellules de Google Sheets, que ce soit dans l’ordre croissant ou décroissant. Cette approche utilise une formule qui utilise une fonction d’aide Lambda. En conséquence, le résultat trié générera une nouvelle plage de données.
Veuillez noter que les fonctions Lambda peuvent être gourmandes en ressources par rapport aux fonctions natives. Par conséquent, utilisez mon approche de formule si vous n’avez pas de données sur plus de 1000 lignes.
À mon avis, n’utilisez des sauts de ligne que lorsque cela est nécessaire, car ils peuvent affecter la manipulation des données.
Sauts de ligne dans une cellule et comment ils affectent le tri
Généralement, nous séparons les valeurs par un délimiteur tel qu’une virgule, un pipe, etc., dans une cellule. Parfois, nous souhaitons avoir des valeurs sur plusieurs lignes dans une cellule.
Pour cela, nous pouvons utiliser le raccourci clavier Alt+Entrée (Windows) ou Option+Entrée (Mac) dans Google Sheets.
Par exemple, si vous souhaitez trois lignes dans la cellule A1, et qu’elles sont « 23 Main Street », « Anytown » et « IN 12345 », tapez « 23 Main Street » et appuyez sur Alt+Entrée ou Option+Entrée, selon votre système d’exploitation. Ensuite, tapez « Anytown » et appliquez le raccourci clavier, puis entrez « IN 12345 ».
En procédant ainsi, vous avez créé deux nouvelles lignes dans la cellule A1 en insérant des sauts de ligne (caractères de nouvelle ligne). Le caractère de saut de ligne que vous avez appliqué en utilisant le raccourci clavier est celui que vous obtenez lorsque vous saisissez la formule =CHAR(10).
Dans les formules, vous pouvez utiliser CHAR(10) pour insérer ou faire correspondre des sauts de ligne, et nous l’utiliserons pour trier des données séparées par des sauts de ligne dans les cellules de Google Sheets.
Bien que les sauts de ligne améliorent la lisibilité et maintiennent l’intégrité des données dans la cellule, il y a des inconvénients lors du filtrage, du tri, de la mise en forme conditionnelle et de la manipulation des données.
Dans ce tutoriel, nous aborderons le tri des données contenant des sauts de ligne. Tout d’abord, voyons comment les sauts de ligne affectent le tri.
Exemple de données et comportement de tri avec des sauts de ligne dans les cellules
Dans l’exemple suivant, je souhaite trier les données d’exemple dans la plage A2:B. D’abord, triez la colonne A2:A dans l’ordre croissant, puis triez la colonne B2:B dans l’ordre décroissant.
Il y a des sauts de ligne dans de nombreuses cellules de la colonne B. Vous pouvez voir plusieurs lignes dans les cellules B2, B3, B4, B6 et B7. Lors du tri, je veux les trier dans les cellules dans l’ordre décroissant, et la colonne A dans l’ordre croissant.
Voici la formule dans la cellule G2:
=SORT(A2:B, 1, TRUE, 2, FALSE)
Elle trie simplement la colonne A dans l’ordre croissant, et cela n’a aucun impact sur le tri des données séparées par des sauts de ligne dans la colonne B.
Explication de la formule
La formule implique l’utilisation de plusieurs fonctions: IFERROR, SORT, HSTACK, MAP, TEXTJOIN, TOCOL, SPLIT et CHAR.
La partie MAP est essentielle dans la formule. Elle trie les données de la colonne B, séparées par des sauts de ligne, dans l’ordre décroissant.
La fonction MAP crée un nouveau tableau en appliquant une fonction lambda à chaque valeur de B2:B, où chaque valeur est représentée par l’identifiant r. La fonction lambda, LAMBDA(r, TEXTJOIN(CHAR(10), TRUE, SORT(TOCOL(SPLIT(r, CHAR(10))), 1, FALSE))), fonctionne sur chaque valeur r:
- SPLIT(r, CHAR(10)): Divise au niveau du caractère de saut de ligne (CHAR(10)).
- TOCOL(SPLIT(r, CHAR(10))): Transforme le résultat en une seule colonne.
- SORT(TOCOL(SPLIT(r, CHAR(10))), 1, FALSE): Trie ce résultat par ordre décroissant.
- TEXTJOIN(CHAR(10), TRUE, …): Combine le résultat en utilisant le caractère de saut de ligne.
- MAP répète cette opération pour chaque ligne de la plage B2:B.
La fonction HSTACK ajoute la plage A2:A avec le résultat de MAP, qui est la donnée de colonne triée.
La formule SORT extérieure trie ensuite la colonne A dans l’ordre croissant.
Enfin, IFERROR élimine toutes les valeurs d’erreur, le cas échéant.
Conseils avancés: tri des données avec des sauts de ligne dans plusieurs colonnes
Dans l’exemple suivant, j’ai des noms d’articles dans la colonne 1, la date de réception dans la colonne B et les quantités correspondantes dans la colonne C.
Les colonnes B et C contiennent des sauts de ligne dans les cellules pour améliorer la lisibilité des données. Cela signifie que si un article est reçu plusieurs fois, les dates sont saisies dans une cellule et les quantités correspondantes dans une autre cellule en insérant des sauts de ligne.
Comme d’habitude avec les sauts de ligne, le tri est un problème ici.
Je veux trier les articles en fonction de leur date de réception et de leur quantité. Si un article est reçu plusieurs fois dans la journée, je veux que la quantité maximale soit en haut.
Comment trier des données séparées par des sauts de ligne dans plusieurs colonnes?
La formule générique sera:
=SORT(HSTACK(plage_1_sans_sauts_de_ligne, plages_2_3_avec_sauts_de_ligne), 1, TRUE)
Dans notre exemple, la colonne 2 contient des dates et la colonne 3 contient des chiffres. Donc, dans la formule générique, remplacez range_1_sans_sauts_de_ligne par A2:A et plages_2_3_avec_sauts_de_ligne par la formule suivante:
ArrayFormula(TRIM(SUBSTITUTE(MAP(B2:B, C2:C, LAMBDA(r, rr, QUERY(LET(data, IFERROR(SORT(HSTACK(TOCOL(SPLIT(r, CHAR(10))), TOCOL(SPLIT(rr, CHAR(10)))), 1, TRUE, 2, FALSE), » « ), HSTACK(TEXT(CHOOSECOLS(data, 1), « DD/MM/YY »), CHOOSECOLS(data, 2)))&CHAR(10),,9^9))), CHAR(10)& » « , CHAR(10))))
Remarque : Si la colonne 2 dans votre tableau est du texte ou des chiffres, supprimez la partie mise en évidence dans la formule qui formate essentiellement la valeur de date en date.
Logique de la formule et décomposition
Je vais décomposer la formule pour plages_2_3_avec_sauts_de_ligne.
Dans la formule, il y a deux tableaux dans la fonction MAP: B2:B et C2:C. Les identifiants correspondants pour chaque ligne sont représentés par r et rr.
L’opération TOCOL + SPLIT est appliquée séparément à ces deux tableaux:
TOCOL(SPLIT(r, CHAR(10))) TOCOL(SPLIT(rr, CHAR(10)))
La fonction HSTACK ajoute ces données horizontalement, et la fonction SORT est ensuite utilisée pour trier la colonne 1 (dates) dans l’ordre croissant et la colonne 2 (quantités) dans l’ordre décroissant.
SORT(HSTACK(TOCOL(SPLIT(r, CHAR(10))), TOCOL(SPLIT(rr, CHAR(10)))), 1, TRUE, 2, FALSE)
Ensuite, la fonction IFERROR est utilisée pour remplacer les valeurs d’erreur dans les lignes vides par des caractères vides:
IFERROR(SORT(HSTACK(TOCOL(SPLIT(r, CHAR(10))), TOCOL(SPLIT(rr, CHAR(10)))), 1, TRUE, 2, FALSE), » « )
La fonction LET est utilisée pour nommer ce résultat en tant que données, et chaque colonne est extraite à l’aide de CHOOSECOLS. Cela est fait pour formater les dates dans la colonne #1.
LET(data, IFERROR(SORT(HSTACK(TOCOL(SPLIT(r, CHAR(10))), TOCOL(SPLIT(rr, CHAR(10)))), 1, TRUE, 2, FALSE), » « ), HSTACK(TEXT(CHOOSECOLS(data, 1), « DD/MM/YY »), CHOOSECOLS(data, 2)))
Les caractères de saut de ligne sont ajoutés, et la fonction QUERY est utilisée pour joindre les lignes.
QUERY(…&CHAR(10),,9^9)
MAP répète cette opération pour chaque ligne de la plage B2:C.
Enfin, les fonctions TRIM et SUBSTITUTE sont appliquées pour supprimer les nouveaux sauts de ligne et les caractères d’espace.
Conclusion
Dans la discussion ci-dessus, nous avons exploré deux exemples différents de tri de données séparées par des sauts de ligne dans les cellules de Google Sheets.
Dans le premier exemple, nous avons des données dans deux colonnes, avec des sauts de ligne dans la deuxième colonne. En revanche, dans le deuxième exemple, il y a trois colonnes, et les colonnes 2 et 3 contiennent des sauts de ligne.
Le deuxième exemple est destiné aux utilisateurs avancés. Les débutants peuvent trouver un peu difficile de modifier la formule en fonction du type de données – qu’il s’agisse de dates, de nombres ou de texte. J’ai expliqué les choses et indiqué où vous pouvez apporter des ajustements.
Si vous avez des questions ou si vous n’êtes pas sûr de quelque chose, n’hésitez pas à demander dans les commentaires.
Pour plus de conseils et de tutoriels sur Google Sheets, consultez notre site bolamarketing.com.
