2013年4月4日 星期四

Excel-以公式排序資料(INDEX,MATCH,COUNTIF)

在 Excel 中使用排序工具,很容易將一個資料表加以排序。如果想要練習用公式來達到排序效果,該如何處理?

參考下圖左的項目和數值組成的資料表,現在要操作以這個資料表的「數值」來排序,必須藉助一個輔助欄位。

(1) 建立排序前「輔助」欄位

儲存格C2:=B2+(COUNTIF($B$2:B2,B2)-1)*0.001

複製儲存格C2,貼至儲存格C2:C19。

(COUNTIF($B$2:B2,B2)-1)*0.001 的作用在於,在數值如果有相同大小時,能有所區隔。

 

(2) 定義名稱

選取儲存格A1:C19,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、數值、輔助。

 

(3) 建立排序後的「輔助」欄位

儲存格G2:=LARGE(輔助,ROW(1:1))

利用 LARGE 函數由大至小排出數值的順序,其中如果有相同數值,會重覆出現。

複製儲存格G2,貼至儲存格G2:G19。

 

(4) 建立排序後的「數值」欄位

儲存格F2:=INDEX(數值,MATCH(G2,輔助,0))

MATCH(G2,輔助,0):根據輔助欄位的內容,找到儲存格G2的數值位於儲存格陣列的位置。

利用 INDEX 函數以查表方式找出對應的「數值」內容。

複製儲存格F2,貼至儲存格F2:F19。

 

(5) 建立排序後的「項目」欄位

儲存格E2:=INDEX(項目,MATCH(G2,輔助,0))

MATCH(G2,輔助,0):根據輔助欄位的內容,找到儲存格G2的數值位於儲存格陣列的位置。

利用 INDEX 函數以查表方式找出對應的「項目」內容。

複製儲存格E2,貼至儲存格E2:E19。

 

【補充說明】

詳細函數說明,請參閱微軟網站的說明: 

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

 

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

COUNTIFhttp://office.microsoft.com/zh-tw/excel-help/HP010342346.aspx

COUNTIF:計算範圍內符合您所指定單一條件準則的儲存格總數。

語法:COUNTIF(range, criteria)

range:要列入計算的一個或多個儲存格,包括數字或包含數字的名稱、陣列或參照;空白或文字值會忽略。

criteria:定義要將哪些儲存格列入計算的準則,可以是數字、表示式、儲存格參照或文字字串。

可以在準則中使用萬用字元:問號 (?) 及星號 (*);問號可以替代任何單一字元;星號可以替代任何一系列的字元。如果確實要尋找實際的問號或星號,請在該字元前輸入波狀符號 (~)

沒有留言:

張貼留言

檢視其他文章

好康東東