| 在Excel中利用數(shù)據(jù)的排序功能可以很輕松地進行排序，但這種排序會破壞原有的數(shù)據(jù)清單。筆者經(jīng)過摸索，發(fā)現(xiàn)了兩種可以利用公式自動排序且不破壞原始數(shù)據(jù)清單的方法。 一、利用數(shù)組公式 數(shù)組公式可以同時進行多重計算并返回一種或多種結(jié)果。數(shù)組公式對兩組或多組被稱為數(shù)組參數(shù)的數(shù)值進行運算。數(shù)組公式的創(chuàng)建方法很簡單，在單元格中輸入公式后按 CTRL+SHIFT+ENTER 組合鍵即可生成數(shù)組公式。我們以下圖中的Excel表中數(shù)據(jù)為例，現(xiàn)在我們想根據(jù)工資多少進行排序。 為了便于輸入，用Salary來代替$F$2:$F$31這個范圍區(qū)域，用Name來代替$B$2:$B$31。 在單元格H2中輸入"=INDEX(Name,MATCH(LARGE(Salary+ROW(Salary),ROW()-1),Salary+ROW(Salary),0))"，最后按CTRL+SHIFT+ENTER，自動在公式兩端加上{ }成為數(shù)組公式。 下面我們將公式的作用詳細說明如下。 ROW(參數(shù))函數(shù)的作用是得到“參數(shù)”所代表的單元格或單元格區(qū)域的行號，如果在數(shù)組公式中輸入這個公式就得到一個行號數(shù)組。 ROW(Salary)記錄的是行號的信息， Salary+ROW(Salary)就是再原來工資的數(shù)目上再加上行號，這樣是為了防止有相同的工資數(shù)目出現(xiàn)，避免因相同的工資數(shù)而出現(xiàn)錯誤的排序。 ROW()-1則是給出一個從1到24的序數(shù)數(shù)組，便于從大到小對工資進行排序。LARGE(Salary+ROW(Salary),ROW()-1)是在Salary+ROW(Salary)的范圍內(nèi)找出一個ROW()-1大的數(shù)X（暫時用X來代替其返回值）。 MATCH函數(shù)是返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。MATCH(X,Salary+ROW(Salary),0)的作用是在Salary范圍內(nèi)查找X并且返回其所在的行號M（暫時用M代替返回的行號M）。 INDEX(Name,M)是在Name范圍內(nèi)返回第M個元素的內(nèi)容。 這樣就完成了從大到小的排序。 為了便于與原數(shù)據(jù)進行比較，可在I2中輸入“=INDEX(Name,MATCH(LARGE(Salary+ROW(Salary),ROW()-1),Salary+ROW(Salary),0))”，然后再按組合鍵，這樣就可以將工資數(shù)目從高至低排列出來。 如果要從小到大排序則只需把LARGE()函數(shù)換成SMALL()函數(shù)即可。 二、利用普通公式進行排序 在K2單元格中輸入公式"=IF(B2=0,0,INT(CONCATENATE(INT(F2),200-ROW(B1))))"，將該公式下拉到K31（“下拉”指將鼠標移動到公式所在單元格的右下角，當鼠標變成一個小十字符號的時候，按住鼠標左鍵向下拉動，則此列的單元格中會自動加上相應(yīng)的公式，下同）。 該公式的作用是將工資與所在的行號信息進行整合。 公式中的ROW(B1)就是B1單元格所在的行號。 CONCATENATE函數(shù)是一個整合函數(shù)，本處是把F列的數(shù)據(jù)和它所在行數(shù)整合為一個數(shù)據(jù)，這樣在對它進行排序后就包含了它所在的行的信息。 用200減去ROW(B1)是為了使CONCATENATE的第2個參數(shù)保持3位數(shù)，保證整合后的數(shù)據(jù)的位數(shù)一致（當然本處用100來減也可以）。 INT函數(shù)是為了把原來的文本內(nèi)容變?yōu)閿?shù)字。 在L2單元格中輸入"=LARGE(K:K,ROW(B1))"，并下拉至L31，對K列的數(shù)據(jù)進行排序。 在N2中輸入"=IF(L2=0,0,200-RIGHT(L2,3))"，并下拉至N31。該公式取得數(shù)據(jù)的最初行數(shù)信息。RIGHT(L2,3)的返回值是L2單元格數(shù)據(jù)的后3位數(shù)，用200來減去此數(shù)就是該數(shù)據(jù)所在的行數(shù)。 在M2單元格中輸入"=IF(N2=0,0,INDEX(Name,N2,))"，再下拉至M31，即可完成排序。該公式是根據(jù)行號來取得所對應(yīng)的Name值，其實在N列中就已經(jīng)完成了排序。INDEX(Name,N2)就是根據(jù)N2單元格中所代表的行號來返回其在name區(qū)域中所代表的單元格的內(nèi)容。使用IF()函數(shù)是一種安全策略，防止出現(xiàn)0值。 兩種方法各有優(yōu)劣，數(shù)組公式法較簡單，但不太好理解，而普通公式法則正相反，容易理解但操作較繁瑣。 |
|站長郵箱|小黑屋|手機版|Office中國/Access中國
( 粵ICP備10043721號-1 )
GMT+8, 2025-7-13 08:18 , Processed in 0.061874 second(s), 16 queries .
Powered by Discuz! X3.3
© 2001-2017 Comsenz Inc.
