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

文章推薦指數: 80 %
投票人數:10人

利用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個中文字元) 請輸入左方認證碼: 看不懂,換張圖 請輸入驗證碼 送出留言



請為這篇文章評分?