贊助廠商

在 Excel 中的資料查詢和資料篩選,常會以欄位查詢資料內容,或是以資料內容求欄位名稱,試試以下的練習。

本篇可以學到:

1.單一欄位篩選與跨欄位篩選

2.欄位內容和欄位名稱交換查詢

 

1.單一欄位篩選與跨欄位篩選

Excel-單一欄位篩選與跨欄位篩選

 

首先,選取儲存格A3:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、星期一、星期二。

1. 星期一和星期二分列不同清單

儲存格E5:=FILTER(姓名,星期一=E$3)

複製儲存格E5,貼至儲存格E5:G5。

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

延伸這篇:Excel-關於超連結的使用

在 Excel 裡,使用到網址超連結時,會自動轉換為 URL 編碼。

Excel-將網址轉換為URL編碼字串

如果使用 HYPERLINK 函數產生超連結時,

儲存格C6:=HYPERLINK(A5,B5)

會產生結果:https://demo.com/abc%20def.def

其中,空格已轉換為「%20」。

如果你是使用在儲存格上按右鍵以建立超連結,例如:https://demo.com/abc def.def。

Excel-將網址轉換為URL編碼字串

其產生的超連結在實際連線時為:https://demo.com/abc%20def.pdf

其中,空格已轉換為「%20」。

Excel-將網址轉換為URL編碼字串

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

在 Excel 裡要使用陣列公式時,對於陣列內容為定數,則必須要輸入較長的文字,例如:{"甲";"乙";"丙";"丁";"戊";"己";"庚";"辛";"壬";"癸"},通常在輸入文字時會帶來不便。

如今,使用 Excel 的 ARRAYTEXT 函數可以輕鬆轉換而得。

Excel-使用ARRAYTOTEXT函數取得陣列文字

【設計與解析】

例如,

在儲存格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)

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

(研習練習)

本篇要以 Excel 2021 的新增函數進行篩選、查詢、排序之綜合練習。會用到的函數:

(1) 篩選:FILTER

(2) 排序:SORT

(3) 查詢:XLOOKUP

(4) 取唯一值:UNIQUE

(5) 計算個數:COUNTA

(6) 計算乘積和:SUMPRODUCT

Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

【設計與解析】

首先,選取儲存格A4:C100,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:序號、姓名、居住地。

接著,定義名稱:清單。並指定參照:清單=FILTER(姓名,LEFT(姓名,1)=E4)。

文章標籤

vincent 發表在 痞客邦 留言(1) 人氣()

在 Excel 中,如果要製作下拉式清單為了以選取方式輸入資料,通常透過「資料驗證」工具可以完成。但是如果可選取項目很多,就不是那麼方便了。

如何能製作隨輸入字元列出可選項目的下拉式清單?輸入文字愈多,顯示可選的項目會愈來愈少,這可以大大的縮小選取的時間。

本篇你將會學到:

1. 動態的下拉式清單製作方式

2. 資料驗證的運用

3. MATCH、COUNTIF、OFFSET、XLOOKUP等函數的使用

Excel-製作隨輸入字元列出可選項目的下拉式清單

【設計與解析】

1. 選取儲存格B3:C100,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、居住地。

2. 找出第1筆姓林的位置

儲存格E4:=MATCH(G4&"*",姓名,0)

MATCH 函數中,尋找「G4&"*"」符合者,是利用萬用字元概念,讓 MATCH 函數找出姓林者的第一個位置。

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

在 Excel 的 FILTER 函數不能使用萬用字元,你可以利用輔助欄位來建立萬用字元的模糊篩選效果。

本篇你將學到:

1. FILTER、SORT 函數的應用

2.模糊篩選的設計(仿萬用字元概念) 

參考以下的例子。

1. 篩選姓名中含有「文」者

Excel-利用FILTER函數模糊篩選

(1) 輔助欄位

儲存格E4:=IFERROR(FIND($G$4,B4),0)

將儲存格E4,複製到輔助欄位的所有儲存格。

利用 FIND 函數尋找是否含有儲存格G4的內容,若有會傳回其位置(一個數字),否則會傳回錯誤訊息。

再利用 IFERROR 函數將錯誤訊息以「0」顯示。

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼