如果您需要對列表進行排序，那麼有很多方法可以為您服務，其中最簡單的方法是選項卡或菜單中的排序按鈕 數據 （數據——排序）. 但是，有些情況需要自動完成列表的排序，即公式。 這可能是必需的，例如，在為下拉列表生成數據時，在為圖表計算數據時等。如何使用公式對列表進行動態排序？
方法 1. 數值數據
如果列表僅包含數字信息，則可以使用函數輕鬆完成排序 最小 （小的） и LINE （排）:
功能 最小 （小的） 從數組（A 列）中取出一行中第 n 個最小的元素。 那些。 SMALL(A:A;1) 是列中最小的數字，SMALL(A:A;2) 是第二小的數字，依此類推。
功能 LINE （排） 返回指定單元格的行號，即 ROW(A1)=1, ROW(A2)=2 等。在這種情況下，它僅用作數字序列 n=1,2,3... 的生成器我們的排序列表。 以同樣的成功，可以創建一個額外的列，用數字序列 1,2,3 手動填充它......並引用它而不是 ROW 函數。
方法 2. 文本列表和正則公式
如果列表包含的不是數字，而是文本，那麼 SMALL 功能將不再起作用，因此您必須採用不同的、稍長的路徑。
首先，讓我們添加一個帶有公式的服務列，其中將使用函數計算未來排序列表中每個名稱的序列號 COUNTIF （縣）:
在英文版中，它將是：
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
第一項是計算小於當前單元格的單元格數量的函數。 第二個是安全網，以防任何名稱多次出現。 然後他們將不會有相同的，而是連續增加的數字。
現在接收到的號碼必須按升序排列。 為此，您可以使用該功能 最小 （小的） 從第一種方式：
好吧，最後，它仍然只是通過他們的數字從列表中拉出名字。 為此，您可以使用以下公式：
功能 更多曝光 （匹配） 在 B 列中搜索所需的序列號（1、2、3 等），實際上，返回該編號所在行的編號。 功能 INDEX （指數） 從 A 列中提取此行號處的名稱。
方法三：數組公式
這種方法實際上與方法 2 中的放置算法相同，但通過數組公式實現。 為了簡化公式，單元格範圍 C1:C10 被命名為 名單 （選擇單元格，按 Ctrl + F3鍵 和按鈕 創建):
在單元格 E1 中，複製我們的公式：
=INDEX(列表;匹配(小(COUNTIF(列表;“<”&列表);ROW(1:1));COUNTIF(列表;“<”&列表);0))
或者在英文版中：
=INDEX(列表, MATCH(小(COUNTIF(列表, «<"&列表), ROW(1:1)), COUNTIF(列表, "<"&列表), 0))
並推 Ctrl + Shift + Enter將其作為數組公式輸入。 然後可以將生成的公式複製到列表的整個長度。
如果您希望公式考慮的不是固定範圍，而是能夠在將新元素添加到列表時進行調整，那麼您將需要稍微改變策略。
首先，列表範圍需要動態設置。 為此，在創建時，您需要指定的不是固定範圍 C3:C10，而是一個特殊公式，該公式將引用所有可用值，無論其數量如何。 點擊 Alt + F3鍵 或打開標籤 公式 - 名稱管理器 （公式 - 名稱管理器），創建一個新名稱並在該字段中 Link （參考） 輸入以下公式（我假設要排序的數據范圍從單元格C1開始）：
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
其次，上述數組公式將需要向下延伸，以期將來輸入更多數據。 在這種情況下，數組公式將開始在尚未填充的單元格上給出錯誤 #NUMBER。 要攔截它，您可以使用該函數 錯誤，需要在我們的數組公式“周圍”添加：
=如果錯誤（INDEX(列表;匹配(小(COUNTIF(列表;“<”&列表);ROW(1:1));COUNTIF(列表;“<”&列表);0));»»)
=IFERROR(NDEX(列表, MATCH(SMALL(COUNTIF(列表, «<"&列表), ROW(1:1)), COUNTIF(列表, "<"&列表), 0));"")
它捕獲 #NUMBER 錯誤並改為輸出 void（空引號）。
:
- 按顏色排序範圍
- 什麼是數組公式以及為什麼需要它們
- 新 Office 365 中的 SORT 排序和動態數組