本篇是要針對在 2015/9/23 的這一篇文章公式的改變:
Excel-根據雙條件來摘要資料(SUMPRODUCT,OFFSET,LARGE)
以下是要改用 Excel 2021 以上版本的 FILTER 函數來實作。
公式變的簡單易懂,用舊版的人撰寫公式較為辛苦,在這方面,Excel 是有進步的。
本篇是要針對在 2015/9/23 的這一篇文章公式的改變:
Excel-根據雙條件來摘要資料(SUMPRODUCT,OFFSET,LARGE)
以下是要改用 Excel 2021 以上版本的 FILTER 函數來實作。
公式變的簡單易懂,用舊版的人撰寫公式較為辛苦,在這方面,Excel 是有進步的。
參考下圖,在 Excel 要將一個資料清單裡篩選出其中一組,如果採用 Excel 2021 以後版本的 FILTER 函數,則公式較簡短且簡單。如果使用傳統的陣列公式,除了冗長且不易理解。
【設計與解析】
選取儲存格A3:H28,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:品名、售價、忠店、孝店、仁店、愛店、信店、義店。
在儲存各J4中設定資料驗證為下拉式清單,來源為:=$C$3:$H$3
觀察下圖,在 Excel 裡兩欄的資料,要轉換矩陣式的統計表,該如何處理?
【設計與解析】
選取儲存格B3:C29,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:茶飲、特調。
本例提供4種公式來比較:
(1) 儲存格F4:=COUNTA(FILTER(茶飲,(茶飲=$E4)*(特調=F$3)))
在 Excel 裡的資料清單,很常會使用「分組計算」的功能,以下的三個公式使用不同的函數來表現,一方面互相比較,一方面融會貫通。
【設計與解析】
選取儲存格C3:D100,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:組別、數值。
1. 利用FILTER函數
在 Excel 中的資料查詢和資料篩選,常會以欄位查詢資料內容,或是以資料內容求欄位名稱,試試以下的練習。
本篇可以學到:
1.單一欄位篩選與跨欄位篩選
2.欄位內容和欄位名稱交換查詢
延伸這篇:Excel-關於超連結的使用
在 Excel 裡,使用到網址超連結時,會自動轉換為 URL 編碼。
如果使用 HYPERLINK 函數產生超連結時,
儲存格C6:=HYPERLINK(A5,B5)
在 Excel 裡要使用陣列公式時,對於陣列內容為定數,則必須要輸入較長的文字,例如:{"甲";"乙";"丙";"丁";"戊";"己";"庚";"辛";"壬";"癸"},通常在輸入文字時會帶來不便。
如今,使用 Excel 的 ARRAYTEXT 函數可以輕鬆轉換而得。
【設計與解析】
例如,
本篇要以 Excel 2021 的新增函數進行篩選、查詢、排序之綜合練習。會用到的函數:
(1) 篩選:FILTER
(2) 排序:SORT
(3) 查詢:XLOOKUP
(4) 取唯一值:UNIQUE