學不完．教不停．用不盡

在 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」顯示。