在 Excel 中的資料查詢和資料篩選,常會以欄位查詢資料內容,或是以資料內容求欄位名稱,試試以下的練習。
本篇可以學到:
1.單一欄位篩選與跨欄位篩選
2.欄位內容和欄位名稱交換查詢
1.單一欄位篩選與跨欄位篩選
首先,選取儲存格A3:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、星期一、星期二。
1. 星期一和星期二分列不同清單
儲存格E5:=FILTER(姓名,星期一=E$3)
複製儲存格E5,貼至儲存格E5:G5。
在 Excel 中的資料查詢和資料篩選,常會以欄位查詢資料內容,或是以資料內容求欄位名稱,試試以下的練習。
本篇可以學到:
1.單一欄位篩選與跨欄位篩選
2.欄位內容和欄位名稱交換查詢
1.單一欄位篩選與跨欄位篩選
首先,選取儲存格A3:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、星期一、星期二。
1. 星期一和星期二分列不同清單
儲存格E5:=FILTER(姓名,星期一=E$3)
複製儲存格E5,貼至儲存格E5:G5。
延伸這篇:Excel-關於超連結的使用
在 Excel 裡,使用到網址超連結時,會自動轉換為 URL 編碼。
如果使用 HYPERLINK 函數產生超連結時,
儲存格C6:=HYPERLINK(A5,B5)
會產生結果:https://demo.com/abc%20def.def
其中,空格已轉換為「%20」。
如果你是使用在儲存格上按右鍵以建立超連結,例如:https://demo.com/abc def.def。
其產生的超連結在實際連線時為:https://demo.com/abc%20def.pdf
其中,空格已轉換為「%20」。
在 Excel 裡要使用陣列公式時,對於陣列內容為定數,則必須要輸入較長的文字,例如:{"甲";"乙";"丙";"丁";"戊";"己";"庚";"辛";"壬";"癸"},通常在輸入文字時會帶來不便。
如今,使用 Excel 的 ARRAYTEXT 函數可以輕鬆轉換而得。
【設計與解析】
例如,
在儲存格A17中輸入公式:=ARRAYTOTEXT(A3:A12,0)
可以得到:甲, 乙, 丙, 丁, 戊, 己, 庚, 辛, 壬, 癸
在儲存格B16中輸入公式:=ARRAYTOTEXT(B3:B12,1)
可以得到:{"甲";"乙";"丙";"丁";"戊";"己";"庚";"辛";"壬";"癸"}
在儲存格C15中輸入公式:=ARRAYTOTEXT(C3:C12,1)
可以得到:{1;2;3;4;5;6;7;8;9;10}
在儲存格D14中輸入公式:=ARRAYTOTEXT(D3:DC12,1)
(研習練習)
本篇要以 Excel 2021 的新增函數進行篩選、查詢、排序之綜合練習。會用到的函數:
(1) 篩選:FILTER
(2) 排序:SORT
(3) 查詢:XLOOKUP
(4) 取唯一值:UNIQUE
(5) 計算個數:COUNTA
(6) 計算乘積和:SUMPRODUCT
【設計與解析】
首先,選取儲存格A4:C100,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:序號、姓名、居住地。
接著,定義名稱:清單。並指定參照:清單=FILTER(姓名,LEFT(姓名,1)=E4)。
在 Excel 中,如果要製作下拉式清單為了以選取方式輸入資料,通常透過「資料驗證」工具可以完成。但是如果可選取項目很多,就不是那麼方便了。
如何能製作隨輸入字元列出可選項目的下拉式清單?輸入文字愈多,顯示可選的項目會愈來愈少,這可以大大的縮小選取的時間。
本篇你將會學到:
1. 動態的下拉式清單製作方式
2. 資料驗證的運用
3. MATCH、COUNTIF、OFFSET、XLOOKUP等函數的使用
【設計與解析】
1. 選取儲存格B3:C100,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、居住地。
2. 找出第1筆姓林的位置
儲存格E4:=MATCH(G4&"*",姓名,0)
在 MATCH 函數中,尋找「G4&"*"」符合者,是利用萬用字元概念,讓 MATCH 函數找出姓林者的第一個位置。
在 Excel 的 FILTER 函數不能使用萬用字元,你可以利用輔助欄位來建立萬用字元的模糊篩選效果。
本篇你將學到:
1. FILTER、SORT 函數的應用
2.模糊篩選的設計(仿萬用字元概念)
參考以下的例子。
1. 篩選姓名中含有「文」者
(1) 輔助欄位
儲存格E4:=IFERROR(FIND($G$4,B4),0)
將儲存格E4,複製到輔助欄位的所有儲存格。
利用 FIND 函數尋找是否含有儲存格G4的內容,若有會傳回其位置(一個數字),否則會傳回錯誤訊息。
再利用 IFERROR 函數將錯誤訊息以「0」顯示。