Comment trier les nombres et le texte mixtes, nombres à plusieurs niveaux dans Excel

Le didacticiel explique les problèmes communs avec la commande de chaînes alphanumériques dans Excel et fournit des solutions de travail pour trier les nombres en tant que texte et texte que les nombres.

Le trier du texte et des nombres séparément dans Excel est aussi simple que ABC ou 123 :) Mais réarranger les ID de produit, les SKU et d'autres valeurs qui contiennent des lettres et des chiffres peuvent causer des problèmes. Le type alphabétique d'Excel ne peut pas distinguer une partie numérique d'une chaîne pour la gérer séparément. Si vous voulez avoir plus de contrôle sur la façon dont les piqûres alphanumériques sont triées, parcourez les exemples ci-dessous.

• Tri des numéros comme texte
• Trier les nombres et le texte mitigés
• Trier les chaînes par texte et nombres à la fois
• Trier les numéros à plusieurs niveaux / hiérarchies
• Trier les numéros à plusieurs niveaux préfixés avec du texte

Trier les nombres en tant que texte dans Excel

Lors du tri d'une colonne de nombres du plus petit au plus grand d'Excel, au lieu de l'avoir:

1, 2, 3, 11, 12, 13, 110, 120, 130

Vous pouvez parfois obtenir quelque chose comme ceci:

1, 11, 110, 12, 120, 13, 130, 2, 3

Cela se produit lorsque les nombres sont réellement textuels et qu'ils trient en texte - comme les mots qui sont disposés en fonction de leurs lettres, les "numbers de texte" sont triés en fonction de leurs chiffres au lieu de leurs valeurs.

Si la colonne cible contient des nombres formatés en texte, tout ce qu'il faut pour que les nombres trient normalement est de convertir le texte en numéro. Le résultat est dans la colonne C dans la capture d'écran ci-dessous.

Au contraire, si vous souhaitez trier les numéros sous forme de texte , convertissez d'abord les numéros en texte, puis cliquez sur l'onglet Home > Édition de groupe> Trier et filtre > Trier A - Z.

Le résultat sera comme dans la colonne A dans la capture d'écran précédente.

Comment trier les nombres et le texte mixtes dans Excel

Avec la fonction de tri Excel intégrée, les chaînes alphanumériques (texte et numéros combinés) sont toujours triés sous forme de texte, c'est-à-dire lettre par lettre, chiffre par chiffre. Pour trier une colonne de nombres contenant un préfixe de lettre ou un suffixe en tant que nombres , effectuez les étapes ci-dessous. Et voici le résultat que nous essayons de réaliser.

Pour empêcher Excel de gérer les nombres dans les chaînes alphanumériques en tant que texte, nous allons extraire ces nombres dans une colonne d'assistance et trier par cette colonne. Cela vous permettra de garder les données d'origine inchangées mais de la réorganiser comme vous le souhaitez.

1. Dans une colonne à côté des valeurs d'origine, entrez une formule qui extrait le numéro de la chaîne.

   Dans cet exemple, nous pouvons simplement extraire tous les caractères après un trait d'union ("-"). Dans Excel 365, cela peut être fait en utilisant la fonction Textafter. Comme pour toute autre fonction de texte, sa sortie est toujours du texte, que vous récupériez des lettres ou des chiffres, nous multiplions donc le résultat par 1 pour convertir une chaîne de texte en nombre.

   =TEXTAFTER(A2, "-")*1

   Dans les versions Excel plus anciennes, le même résultat peut être obtenu en utilisant une combinaison de trois fonctions différentes qui extraient du texte après un caractère spécifique. De plus, l'opération * 1 est effectuée pour convertir la sortie du texte en numéro:

   =RIGHT(A2, LEN(A2) - SEARCH("-", A2)) *1

   

   Conseil. Pour extraire rapidement les nombres de n'importe quelle position dans une cellule, vous pouvez utiliser l'outil d'extrait inclus avec notre suite ultime pour Excel.

   

2. Triez les données d'origine par les nombres extraits. Avec les numéros sélectionnés, accédez à l'onglet Home > Tri et filtre > Trier le plus petit à plus grand . Dans la boîte de dialogue qui apparaît, choisissez l' élargissement de l'option de sélection et cliquez sur Trier . Pour plus de détails, voyez comment trier et garder les rangées ensemble.

   

   Maintenant, les chaînes contenant des lettres et des chiffres sont triés sous forme de numéros, et vous pouvez supprimer ou masquer la colonne d'assistance si nécessaire.

   

Trier les chaînes par texte et chiffres à la fois

Dans le cas où vos chaînes de source ont plusieurs éléments différents, vous pouvez extraire chaque élément dans une colonne distincte, puis trier par plusieurs colonnes. En conséquence, les chaînes seront organisées comme dans la colonne D dans la capture d'écran ci-dessous:

Comme toutes les chaînes de notre ensemble de données ont le même modèle, la façon la plus simple de diviser le texte et les nombres en différentes colonnes est d'utiliser des expressions régulières. Cela peut être fait à l'aide de la fonction personnalisée nommée RegexPextract. Tout d'abord, vous ajoutez son code à votre Excel comme expliqué dans ce tutoriel. Après cela, utilisez les formules suivantes.

Pour extraire la première occurrence de texte de la cellule A2, la formule en B2 est:

=RegExpExtract(A2, "[^\d] ", 1)

Pour extraire le premier numéro, la formule en C2 est:

=RegExpExtract(A2, "\d ", 1)*1

Pour extraire le deuxième numéro, la formule en D2 est:

=RegExpExtract(A2, "\d ", 2)*1

Dans les formules ² et 3 ^Rd , nous multiplions la sortie regexpextract par 1 pour transformer une sous-chaîne extraite en un nombre.

Les utilisateurs de notre suite ultime peuvent tirer parti des outils Regex pour obtenir le même résultat. Ils auront juste besoin d'effectuer une opération rapide de plus pour convertir les chaînes numériques extraites (colonnes C et D) en numéros:

Une fois le texte et les numéros divisés, triez votre ensemble de données par plusieurs colonnes:

1. Sélectionnez toutes les colonnes (A2: D16 dans notre cas).
2. Dans l'onglet Données , dans le groupe de tri et de filtre , cliquez sur le bouton Tri .
3. Ajoutez autant de niveaux de tri que vous en avez besoin et choisissez l'ordre de tri souhaité.
4. Une fois terminé, cliquez sur OK .

Dans notre cas, la plage sélectionnée est d'abord triée par texte de A à Z, puis par numéro 1 , puis par numéro 2 du plus petit au plus grand:

Le résultat est exactement ce que nous recherchions:

Conseil. Pour réorganiser les chaînes différemment, modifiez l'ordre des niveaux dans la boîte de dialogue de tri .

Comment trier les numéros à plusieurs niveaux / hiérarchies dans Excel

Dans Microsoft Excel, des nombres à plusieurs niveaux tels que 1.1, 1.1.1, 1.1.2 sont des chaînes, et ils trient en texte, pas des nombres:

Pour trier les chaînes à plusieurs niveaux en tant que numéros, vous pouvez utiliser une fonction définie par l'utilisateur personnalisé qui a la syntaxe suivante:

HIERARCHYNNIND (Range, Del, Max_Level)

Où:

• La plage est la plage à trier.
• Del est le délimiteur utilisé pour séparer les niveaux, généralement un point (.).
• Max_level est le nombre maximum de niveaux dans les numéros de hiérarchie.

Et voici le code de la fonction:

Fonction personnalisée pour trier les numéros de hiérarchie

Fonction hiérarchyNumber (plage en tant que plage, del as string , max_level as Integer ) as double dim part as variant part) hierarchyNnumber (range.value, del) pour index = 0 à ubound (partie) hiéarchyNumber = hiérarchyNumber partie (index) * (10 ^ ((max_level - index) * 2)) Next index final fonction final function index final function index final function fund function fund function

Ce que fait la fonction, c'est générer des nombres qui permettent de tri les chaînes numériques à plusieurs niveaux. Plus précisément, il divise une chaîne de hiérarchie en nombres (parties) individuels, traite chaque pièce en fonction de l'algorithme ci-dessous et ajoute les résultats:

part(Index) * (10 ^ ((level - Index) * 2))

Par exemple, pour 1,1, la fonction produit ce résultat:

1 * (10 ^ ((2-0) * 2)) 1 * (10 ^ ((2-1) * 2)) = 10100

Pour 1,2, c'est:

1 * (10 ^ ((2-0) * 2)) 2 * (10 ^ ((2-1) * 2)) = 10200

Comment trier les numéros de hiérarchie à l'aide de la fonction personnalisée:

1. Insérez le code de la fonction HierarchyNumber dans un module de code standard de votre classeur et enregistrez-le en tant que classeur compatible macro (.xlsm). Les instructions détaillées sont là.
2. Dans une cellule vierge à côté de la première chaîne de hiérarchie, entrez une formule de hiérarchynimber et appuyez sur la touche Entrée .
3. Faites glisser la formule vers le bas sur autant de lignes que nécessaire.
4. Triez votre ensemble de données en fonction de la colonne des formules.

Supposons que vous ayez des chaînes numériques à plusieurs niveaux avec un maximum de 3 niveaux dans la colonne A. La formule pour B2 est:

=HierarchyNumber(A2, ".", 3)

Après avoir copié la formule, vous obtiendrez ce résultat:

Après cela, vous triez l'ensemble de données (A2: B19) par la colonne de formule du plus petit au plus grand:

Et obtenez les chaînes numériques à plusieurs niveaux triées comme des nombres:

Trier les numéros à plusieurs niveaux préfixés avec du texte

Cet exemple traite d'un cas légèrement différent où les nombres à plusieurs niveaux sont préfixés avec du texte, disons "niveau 1.1.1". À première vue, la tâche semble plus compliquée, mais en fait, la solution est plus simple :)

Tout d'abord, vous extriez des sous-chaînes numériques à plusieurs niveaux dans une colonne distincte. Pour cela, vous pouvez utiliser l'outil d'extrait inclus avec notre suite ultime:

Ou vous pouvez écrire une formule pour tirer tous les caractères après un espace:

=RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

Et puis, faites le tri sur mesure. Dans la boîte de dialogue Excel Trier, ajoutez deux niveaux - d'abord par le numéro de hiérarchie extrait, puis par la chaîne d'origine - et cliquez sur OK:

L'avertissement suivant apparaîtra, où vous choisissez de « trier les nombres et les nombres stockés en texte séparément ».

En conséquence, les chaînes contenant des nombres à plusieurs niveaux sont triées par des nombres:

C'est comment trier les chaînes numériques et les nombres à plusieurs niveaux dans Excel. Merci d'avoir lu et de vous voir sur notre blog la semaine prochaine!

Téléchargements disponibles

Trier les nombres et le texte mixtes dans Excel - Exemples (fichier .xlsm) Version d'essai entièrement fonctionnel de suite ultime (fichier .exe)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!