行號跟著數據跑—excel之自動排序

行號跟著數據跑—excel之自動排序在Excel中利用數據的排序功能可以很輕鬆地進行排序，但這種排序會破壞原有的數據清單。筆者經過摸索，發現了兩種可以利用公式自動排序且不破壞原始數據清單的方法。　　一、利用數組公式　　數組公式可以同時進行多重計算並返回一種或多種結果。數組公式對兩組或多組被稱為數組參數的數值進行運算。數組公式的創建方法很簡單，在單元格中輸入公式後按CTRL+SHIFT+ENTER組合鍵即可生成數組公式。我們以下圖中的Excel表中數據為例，現在我們想根據工資多少進行排序。　　為了便於輸入，用Salary來代替$F$2:$F$31這個範圍區域，用Name來代替$B$2:$B$31。　　在單元格H2中輸入"=INDEX(Name,MATCH(LARGE(Salary+ROW(Salary),ROW()-1),Salary+ROW(Salary),0))"，最後按CTRL+SHIFT+ENTER，自動在公式兩端加上{}成為數組公式。　　下面我們將公式的作用詳細說明如下。　　ROW(參數)函數的作用是得到「參數」所代表的單元格或單元格區域的行號，如果在數組公式中輸入這個公式就得到一個行號數組。　　ROW(Salary)記錄的是行號的信息，Salary+ROW(Salary)就是再原來工資的數目上再加上行號，這樣是為了防止有相同的工資數目出現，避免因相同的工資數而出現錯誤的排序。　　ROW()-1則是給出一個從1到24的序數數組，便於從大到小對工資進行排序。LARGE(Salary+ROW(Salary),ROW()-1)是在Salary+ROW(Salary)的範圍內找出一個ROW()-1大的數X（暫時用X來代替其返回值）。　　MATCH函數是返回在指定方式下與指定數值匹配的數組中元素的相應位置。MATCH(X,Salary+ROW(Salary),0)的作用是在Salary範圍內查找X並且返回其所在的行號M（暫時用M代替返回的行號M）。　　INDEX(Name,M)是在Name範圍內返回第M個元素的內容。　　這樣就完成了從大到小的排序。　　為了便於與原數據進行比較，可在I2中輸入「=INDEX(Name,MATCH(LARGE(Salary+ROW(Salary),ROW()-1),Salary+ROW(Salary),0))」，然後再按組合鍵，這樣就可以將工資數目從高至低排列出來。如果要從小到大排序則只需把LARGE()函數換成SMALL()函數即可。二、利用普通公式進行排序　　在K2單元格中輸入公式"=IF(B2=0,0,INT(CONCATENATE(INT(F2),200-ROW(B1))))"，將該公式下拉到K31（「下拉」指將滑鼠移動到公式所在單元格的右下角，當滑鼠變成一個小十字元號的時候，按住滑鼠左鍵向下拉動，則此列的單元格中會自動加上相應的公式，下同）。　　該公式的作用是將工資與所在的行號信息進行整合。　　公式中的ROW(B1)就是B1單元格所在的行號。　　CONCATENATE函數是一個整合函數，本處是把F列的數據和它所在行數整合為一個數據，這樣在對它進行排序後就包含了它所在的行的信息。　　用200減去ROW(B1)是為了使CONCATENATE的第2個參數保持3位數，保證整合後的數據的位數一致（當然本處用100來減也可以）。　　INT函數是為了把原來的文本內容變為數字。　　在L2單元格中輸入"=LARGE(K:K,ROW(B1))"，並下拉至L31，對K列的數據進行排序。　　在N2中輸入"=IF(L2=0,0,200-RIGHT(L2,3))"，並下拉至N31。該公式取得數據的最初行數信息。RIGHT(L2,3)的返回值是L2單元格數據的後3位數，用200來減去此數就是該數據所在的行數。　　在M2單元格中輸入"=IF(N2=0,0,INDEX(Name,N2,))"，再下拉至M31，即可完成排序。該公式是根據行號來取得所對應的Name值，其實在N列中就已經完成了排序。INDEX(Name,N2)就是根據N2單元格中所代表的行號來返回其在name區域中所代表的單元格的內容。使用IF()函數是一種安全策略，防止出現0值。　　兩種方法各有優劣，數組公式法較簡單，但不太好理解，而普通公式法則正相反，容易理解但操作較繁瑣。
推薦閱讀：