Excel-以公式排序資料(INDEX,MATCH,COUNTIF)
文章推薦指數: 80 %
利用LARGE 函數由大至小排出數值的順序,其中如果有相同數值,會重覆出現。
複製儲存格G2,貼至儲存格G2:G19。
(4) 建立排序後的「數值」欄位.
學不完.教不停.用不盡
跳到主文
分享個人電腦教學和回答網友提問解決資料處理與設計問題
部落格全站分類:數位生活
相簿
部落格
留言
名片
贊助廠商
Apr04Thu201319:38
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。
【延伸學習】
Excel-2021版新增函數的使用
Excel-利用SORT和SORTBY函數進行排序
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
Excel-亂數重排座位
Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)
【補充說明】
詳細函數說明,請參閱微軟網站的說明:
INDEX:http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx
INDEX:傳回表格或範圍內的某個值或值的參照。
語法:INDEX(array,row_num,[column_num])
Array:儲存格範圍或陣列常數。
Row_num:選取陣列中傳回值的列。
Column_num:選取陣列中傳回值的欄。
MATCH:http://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引數內的值必須以遞減次序排序。
COUNTIF:http://office.microsoft.com/zh-tw/excel-help/HP010342346.aspx
COUNTIF:計算範圍內符合您所指定單一條件準則的儲存格總數。
語法:COUNTIF(range,criteria)
range:要列入計算的一個或多個儲存格,包括數字或包含數字的名稱、陣列或參照;空白或文字值會忽略。
criteria:定義要將哪些儲存格列入計算的準則,可以是數字、表示式、儲存格參照或文字字串。
可以在準則中使用萬用字元:問號(?)及星號(*);問號可以替代任何單一字元;星號可以替代任何一系列的字元。
如果確實要尋找實際的問號或星號,請在該字元前輸入波狀符號(~)。
全站熱搜
創作者介紹
vincent
學不完.教不停.用不盡
vincent發表在痞客邦留言(6)人氣()
全站分類:數位生活個人分類:講義資料上一篇:Excel-使用各種小計(SUBTOTAL,VLOOKUP)
下一篇:Excel-在儲存格輸入時不自動產生超連結
▲top
留言列表
發表留言
到站人數
本日人氣:
累積人氣:
文章關鍵字搜尋
贊助商連結
回到頁首
回到主文
免費註冊
客服中心
痞客邦首頁
©2003-2022PIXNET
關閉視窗
PIXNET
Facebook
Yahoo!
Google
MSN
{{guestName}}
(登出)
您尚未登入,將以訪客身份留言。
亦可以上方服務帳號登入留言
請輸入暱稱(最多顯示6個中文字元)
請輸入標題(最多顯示9個中文字元)
請輸入內容(最多140個中文字元)
請輸入左方認證碼:
看不懂,換張圖
請輸入驗證碼
送出留言
延伸文章資訊
- 1Excel Rank 如何由小到大排名?5 步驟教學 | Po3C
學期成績要結算了,如何透過Excel 幫同學排名次?甚至不只是成績,我們有時候需要幫一整排的資料,依照數值大小排序,看看排名大約是落在哪些地方,這 ...
- 2使用函數「RANK」進行智慧排序,同一個名次也能顯示出來
一般的排序方式不管由小到大或由大到小,並不會將同樣的數值判斷為不同 ... 放在同一個名次(例如兩個第八名),但如果單純使用基本的Excel 排序法會 ...
- 3Excel中的排序函數RANK,這6種使用技巧你一定不能錯過
- 4Excel-以公式排序資料(INDEX,MATCH,COUNTIF)
利用LARGE 函數由大至小排出數值的順序,其中如果有相同數值,會重覆出現。 複製儲存格G2,貼至儲存格G2:G19。 (4) 建立排序後的「數值」欄位.
- 5快速入門:排序Excel 工作表中的資料