如何對Excel中的混合數字和文本，多級數字進行排序

該教程解釋了在Excel中訂購字母數字字符串的常見問題，並提供了將數字和文本作為數字的數字排序的工作解決方案。

在Excel中分別對文本和數字進行排序與​​ABC或123一樣容易:)，但是重新將產品ID，SKU和其他具有字母和數字的值重新排序可能會導致問題。 Excel的字母順序無法區分字符串的數字部分以分別處理它。如果您想更多地控製字母數字刺的方式，請查看以下示例。

• 將數字排序為文本
• 排序混合數字和文字
• 通過文本和數字對字符串進行排序
• 對多級 /層次結構進行排序
• 對帶有文本前綴的多級數字進行排序

將數字排序為excel中的文本

在Excel中從最小到最大的數字排序時，而不是這樣做：

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

有時您可能會得到這樣的東西：

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

當數字實際上是文本並且它們以文本為單位時，就會發生這種情況 - 就像根據字母安排的單詞，“文本數”是根據數字而不是其值對其進行排序的。

如果目標列包含格式為文本的數字，則數字正常排序所需的只是將文本轉換為數字。結果是在下面的屏幕截圖中列為C列。

相反，如果您想將數字排序為文本，請先將數字轉換為文本，然後單擊“主”選項卡>編輯組> “ sort＆filter”>“＆filter” > “分類a -z” 。

結果將就像在上一個屏幕截圖中的A列中一樣。

如何在Excel中對混合數字和文字進行排序

借助內置的Excel排序功能，字母數字字符串（組合文本和數字）始終被分類為文本，即按字母數字，數字數字。要對包含字母前綴或後綴數字的數字列進行排序，請執行以下步驟。這就是我們試圖實現的結果。

為了防止Excel將字母字符串中的數字作為文本處理，我們將在輔助列中提取這些數字，然後按該列進行排序。這將使您保持原始數據不變，但會按照自己喜歡的方式重新排列。

1. 在原始值旁邊的列中，輸入一個從字符串中提取數字的公式。

   在此示例中，我們可以簡單地在連字符之後提取所有字符（“ - ”）。在Excel 365中，可以使用TextAfter函數完成此操作。與任何其他文本功能一樣，它的輸出始終是文本，無論您是檢索字母還是數字，因此我們將結果乘以1將文本字符串轉換為數字。

   =TEXTAFTER(A2, "-")*1

   在較舊的Excel版本中，可以使用三種不同函數的組合來實現相同的結果，這些函數在特定字符之後提取文本。此外，執行 *1操作以將文本輸出轉換為數字：

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

   

   提示。要快速從單元格中的任何位置提取數字，您可以使用我們的Excel終極套件中包含的提取工具。

   

2. 按提取的數字對原始數據進行排序。選擇的數字，轉到“主頁”選項卡> sort＆filter > “排序最小到最大” 。在彈出的對話框中，選擇“展開選擇”選項，然後單擊“排序” 。有關更多詳細信息，請查看如何對行進行排序和保持行在一起。

   

   現在，包含字母和數字的字符串被分類為數字，您可以在必要時刪除或隱藏助手列。

   

一次按文本和數字對字符串進行排序

如果您的源字符串具有幾個不同的元素，則可以在單獨的列中提取每個元素，然後按多個列進行排序。結果，字符串將像下面的屏幕截圖中的D一樣排列：

由於我們數據集中的所有字符串都具有相同的模式，因此將文本和數字分為不同列的最簡單方法是使用正則表達式。這可以在名為RegexPextract的自定義功能的幫助下完成。首先，如本教程所述，您將其代碼添加到Excel中。之後，使用以下公式。

要從單元A2提取文本的第一次出現，B2中的公式為：

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

要提取第一個數字，C2中的公式為：

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

要提取第二個數字，D2中的公式為：

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

在2 ^nd和3 ^rd公式中，我們將RegexPexTract輸出乘以1，以將提取的子字符串變成一個數字。

我們最終套件的用戶可以利用Regex工具來達到相同的結果。他們只需要執行一個更快速的操作即可將提取的數字字符串（C列C和D）轉換為數字：

一旦文本和數字被拆分，請通過多列對數據集進行排序：

1. 選擇所有列（在我們的情況下為A2：D16）。
2. 在“數據”選項卡上，在“排序和過濾器組”中，單擊“排序”按鈕。
3. 根據需要添加盡可能多的排序級別，然後選擇所需的排序順序。
4. 完成後，單擊確定。

在我們的情況下，選定的範圍首先按A到Z進行文本排序，然後按數字為1 ，然後從最小到最大的數字排名第2 。

結果正是我們要尋找的：

提示。要以不同的方式重新安排字符串，請在排序對話框中更改級別的順序。

如何在Excel中對多級 /層次結構進行排序

在Microsoft Excel中，多級數字，例如1.1、1.1.1、1.1.2是字符串，它們排序為文本，而不是數字：

要將多級字符串排序為數字，您可以使用具有以下語法的自定義用戶定義函數：

層次數（range，del，max_level）

在哪裡：

• 範圍是要排序的範圍。
• DEL是用於分離級別的定界符，通常是一個點（。）。
• max_level是層次結構中的最大級別數量。

這是函數的代碼：

定制函數以排序層次結構編號

函數eRARCHYNUMBER（範圍為range，del as string ，max_level as integer ）作為double as as as as as as as aS variant Part = split（range.value，del，del） for index = 0 to to ubound （part）hierarchynumber = hierarchynumber = hierarchynumber = hierarchynumber part （ index）

該函數的作用是生成允許對多級數字字符串進行排序的數字。更確切地說，它將層次結構字符串分為單個數字（部分），根據以下算法處理每個部分，並將結果加起來：

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

例如，對於1.1，該功能會產生此結果：

1*（10^（（2-0）*2））1*（10^（（2-1）*2））= 10100

對於1.2，是：

1*（10^（（2-0）*2））2*（10^（（2-1）*2））= 10200

如何使用自定義功能對層次結構編號進行排序：

1. 將層次函數的代碼插入工作簿的標準代碼模塊中，並將其保存為宏觀啟用的工作簿（.XLSM）。詳細說明在這裡。
2. 在第一個層次字符串旁邊的空白單元格中，輸入層次結構公式，然後按Enter鍵。
3. 根據需要將公式拖到盡可能多的行上。
4. 根據公式列對數據集進行排序。

假設您的多級數字字符串在A列A中最多3級。B2的公式為：

=HierarchyNumber(A2, ".", 3)

複製公式後，您將獲得此結果：

之後，您可以按公式列從最小到最大的公式列對整個數據集（A2：B19）進行排序：

並將多級數字字符串排序為數字：

對帶有文本前綴的多級數字進行排序

此示例討論了一個略有不同的情況，其中多級數字帶有一些文本，例如“級別1.1.1”。乍一看，任務聽起來更複雜，但實際上解決方案更簡單:)

首先，將多級數字子字符串提取到單獨的列中。為此，您可以使用我們的Ultimate Suite包含的提取工具：

或者，您可以編寫一個公式以在空間之後拉動所有字符：

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

然後，進行自定義排序。在Excel排序對話框中，添加兩個級別 - 首先由提取的層次結構編號，然後由原始字符串 - 然後單擊“確定：

以下警告將彈出，您選擇“將存儲為單獨存儲為文本的數字和數字”。

結果，包含多級數字的字符串按數字排序：

這就是如何對Excel中的數字字符串和多級數字進行排序。感謝您閱讀並在下週在我們的博客上與您見面！

可用下載

在Excel中對混合數字和文本進行排序 - 示例（.xlsm File）Ultimate Suite完全功能的試用版（.EXE文件）

以上是如何對Excel中的混合數字和文本，多級數字進行排序的詳細內容。更多資訊請關注PHP中文網其他相關文章！