Excel：使用萬用字元（Wildcard）搜尋用法

Excel：使用萬用字元（Wildcard）搜尋用法：

在使用 Excel 365 的過程中，可以使用使用萬用字元（Wildcard）作搜尋，萬用字元是一種特殊字元，可代表文字值中的未知字元，很適合尋找具有類似但並非相同資料的多個專案。萬用字元也可以協助根據指定的模式比對來取得資料，而且並可使用不同函數來實現，傳回相同的結果。

萬用字元（Wildcard）：

使用	尋找	範例
?	任何單一字元 例如，sm？th 會找到 “smith” 和 “smyth”。	b？ll 會找出 ball、bell 和 bill。
*	任何數目的字元 例如，*east 會找到 “Southeast” 和 “Southeast”	wh* 會找出 what、white 和 why，但是不會找出 awhile 或 watch。
[ ]	比對括號內的字元	b[ae]ll 會 找出 ball 和 bell，但不會找出 bill。
!	排除括號內的字元	b[!ae]ll 會找出 bill 和 bull，但是不會找出 ball 或 bell。Like “[！a]*”會尋找不是以字母 a 開頭的所有專案。
-	比對某個範圍的字元。 請記得以遞增順序指定字元 (A 到 Z，而不是以 Z 到 A) 。	b[a-c]d 會找出 bad、bbd 和 bcd。
#	比對任何單一數字字元	1#3 會找出 103、113 和 123。
~	(波狀符號) 後面跟著 ?、* 或 ~，問號、星號或波狀符號	fy06~？ 會找到 “fy06？”

萬用字元（Wildcard）數據：

Year	Brand	Model	Q1	Q2	Q3	Q4	Total
2022	Banana	phone 12	100	200	300	400	1000
2023	Banana	phone 12	200	260	370	410	1240
2024	Banana	phone 12	150	300	350	440	2240
2023	Banana	Skybook 128M	5600	5000	3000	2000	15600
2024	Banana	Skybook 256M	7200	7800	7900	8000	30900
2024	BWS	Thinkbook X1	400	460	470	440	1770
2024	Free	Space II E5000	200	230	210	180	820
2024	Free	Inspiron 150	200	330	210	450	1190
2023	Free	EXPS 17	200	999	1999	290	3488
2024	Free	EXPS 17	200	1999	2999	390	5588
2024	Free	EXPS 18	2200	21999	22999	2390	49588
						Total :	113424

1. VLOOKUP 函數搭配萬用字元（Wildcard）搜尋用法：
例子 1.1； 使用 VLOOKUP 搜尋某個範圍中的牌子名，然後傳回型號。它包括 lookup_value (單元格 J2)、lookup_array (範圍 C2:C12)，以及 return_array (範圍 H2:H12 = Row 6) 自變數。
=VLOOKUP("*"&J2&"*",C2:H12,6,0)

VLOOKUP 函數搭配萬用字元（Wildcard）搜尋用法

2. XLOOKUP 函數搭配萬用字元（Wildcard）搜尋用法：
例子 2.1； 使用 XLOOKUP 搜尋某個範圍中的牌子名，然後傳回型號。它包括 lookup_value (單元格 J2)、lookup_array (範圍 C2:C12)，以及 return_array (範圍 H2:H12) 自變數。
=XLOOKUP("*"&J2&"*",C2:C12,H2:H12,"NG",2)

XLOOKUP 函數搭配萬用字元（Wildcard）搜尋用法

3. INDEX & MATCH 函數搭配萬用字元（Wildcard）搜尋用法：
例子 3.1； 使用INDEX & MATCH 搜尋某個範圍中的牌子名，然後傳回型號。它包括 lookup_value (單元格 J2)、lookup_array (範圍 C2:C12)，以及 return_array (範圍 H2:H12) 自變數。
=INDEX(H2:H12,MATCH("*"&J2&"*",""&C2:C12,0))

INDEX & MATCH 函數搭配萬用字元（Wildcard）搜尋用法

例子 3.2； 使用 INDEX & MATCH 搜尋某個範圍中的牌子名，然後傳回型號。它包括 lookup_value (單元格 J2，K2)、lookup_array (範圍 B2:B12，C2:C12)，以及 return_array (範圍 H2:H12) 自變數。
=INDEX(H2:H12,MATCH(1,(LEFT(C2:C12,LEN(K2))=K2)*(B2:B12=J2),0),0)

INDEX & MATCH 函數搭配萬用字元（Wildcard）搜尋用法

例子 3.3； 使用 INDEX & MATCH 搜尋某個範圍中的牌子名，然後傳回型號。它包括 lookup_value (單元格 J2，K2)、lookup_array (範圍 B2:B12，C2:C12)，以及 return_array (範圍 H2:H12) 自變數。
=INDEX(H2:H12,MATCH(1,(LEFT(C2:C12,5)=LEFT(K2,5))*(B2:B12=J2),0),0)

INDEX & MATCH函數搭配萬用字元（Wildcard）搜尋用法

4. FILTER 函數搭配萬用字元（Wildcard）搜尋用法：
例子 4.1； 使用FILTER 搜尋某個範圍中的牌子名，然後傳回型號。它包括 lookup_value (單元格 J2)、lookup_array (範圍 C2:C12)，以及 return_array (範圍 H2:H12) 自變數。
=FILTER(H2:H12,LEFT(C2:C12,LEN(J2))=J2)

FILTER 函數搭配萬用字元（Wildcard）搜尋用法

2024年 4月 28日（Sun）天氣報告

氣溫：48.0°F / 9.0°C @ 08:00

風速：每小時 21公里

降雨機會：10%

相對濕度：百分之 77%

天氣：多雲

Excel：XLOOKUP 函數功能

Excel：XLOOKUP 函數功能：

在使用 Excel 365 的過程中，發現了 XLOOKUP 的強大功能，它合併了VLOOKUP (垂直搜尋) 或 HLOOKUP (水平搜尋) 函數，能夠在工作上解決複雜的搜尋問題，使用 XLOOKUP 函數，在表格或範圍中依列尋找專案。使用 XLOOKUP，您可以在一欄中尋找搜尋字詞，並從另一欄的同一列傳回結果，無論傳回數據行位於哪一側。但 XLOOKUP 在 Excel 2016 以上的版本才有此函數。

XLOOKUP 函數功能

XLOOKUP 函數優點：
與傳統的 VLOOKUP 及 HLOOKUP 相比， XLOOKUP 有以下 8 個優點：

• 可以垂直或水平查找。不需要花心力挑選 VLOOKUP 或 HLOOKUP ，只要使用 XLOOKUP ，就可以進行垂直或是水平查找
• 【查找值】可以在任意欄列。不同於 VLOOKUP 要求【查找值】在最左欄；HLOOKUP 要求【查找值】在最上列，XLOOKUP 【查找值】可以在任意欄列
• 【查找模式】預設為【完全符合】
• 可以反方向進行查找
• 可以返回多個值
• 查找不到資料時，可以指定回傳值
• 可以 "多條件" 查找
• 更快的執行速度。不同於 VLOOKUP/HLOOKUP 【查找範圍】涵蓋整個表格， XLOOKUP 只查找/回傳我們指定的欄位，大幅增加運算效能

XLOOKUP 語法：
XLOOKUP 函數會搜尋範圍或陣列，然後傳回找到的第一個相符專案。 如果沒有相符專案，則 XLOOKUP 可以傳回最接近 (大約) 相符專案。
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP 引數：

引數	描述	選擇
lookup_value	要搜尋的值 *如果省略，XLOOKUP 會傳回 在 lookup_array 中找到的空白單元格。	必填*
lookup_array	要搜尋的陣列或範圍	必要
return_array	要傳回的陣列或範圍	必要
[if_not_found]	如果找不到有效的相符專案，請傳回您提供的 [if_not_found] 文字。 如果找不到有效的相符專案，且 [if_not_found] 遺失，則會傳回 #N/A 。	選擇性
[match_mode]	指定相符類型：  0 - 完全符合。 如果找不到，請傳回 #N/A。 這是預設值。 -1 - 完全符合。 如果找不到，請傳回下一個較小的專案。  1 - 完全符合。 如果找不到，請傳回下一個較大的專案。  2: 萬用字元比對，其中 *、？和 ~ 具有特殊意義。	選擇性
[search_mode]	指定要使用的搜尋模式：  1 - 從第一個項目開始執行搜尋。 這是預設值。 -1 - 從最後一個項目開始執行反向搜尋。  2 - 執行依賴lookup_array以 遞增 順序排序的二進位搜尋。 如果未排序，將會傳回無效結果。 -2: 執行二進位搜尋，依賴 lookup_array 以遞減順序排序搜尋。 如果未排序，將會傳回無效結果。	選擇性

1. XLOOKUP函數基本搜尋用法：
例子 1.1； 使用 XLOOKUP 搜尋某個範圍中的牌子名，然後傳回型號。它包括 lookup_value (單元格 J3)、lookup_array (範圍 B3:B12)，以及 return_array (範圍 C3:C12) 自變數。
=XLOOKUP(J3,B3:B12,C3:C12,"Not Found",0)

XLOOKUP 函數基本搜尋用法

例子 1.2； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回總銷售額。它包括 lookup_value (單元格 J3)、lookup_array (範圍 C3:C12)，以及 return_array (範圍 G3:G12) 自變數。
=XLOOKUP(J3,C3:C12,G3:G12)

XLOOKUP 函數基本搜尋用法

例子 1.3； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回總銷售額。它包括 lookup_value (單元格 J3)、lookup_array (範圍 C3:C12)，以及 return_array (範圍 G3:G12) 自變數，從最後起搜尋。
=XLOOKUP(J3,C3:C12,G3:G12,"Not Found",0,-1)

XLOOKUP 函數基本搜尋用法

2. XLOOKUP 函數搭配萬用字元（Wildcard）搜尋用法：
例子 2.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號 (使用萬能字 / Wildcard)，然後傳回銷售總計。它包括 lookup_value (單元格 J3)、lookup_array (範圍 C3:C12)，以及 return_array (範圍 H3:H12) 自變數。
=XLOOKUP("*"&J3&"*",C3:C12,H3:H12,"Not Found",2)

XLOOKUP 函數搭配萬用字元（Wildcard）搜尋用法

3. XLOOKUP 函數進行多條件搜尋用法：
例子 3.1： 使用 XLOOKUP 搜尋某個範圍中的含有型號和年份，然後傳回總銷售額。它包括 lookup_value (單元格 J3) 和(單元格 K3)、lookup_array (範圍 C3:C12) 和(範圍 A3:A12)，以及 return_array (範圍 H3:H12) 自變數。
=XLOOKUP(1,(C3:C12 =J3)*(A3:A12=K3),H3:H12)

XLOOKUP 函數進行多條件搜尋用法

4. XLOOKUP 函數二維度搜尋用法：
例子 4.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號和季度，然後傳回Q4銷售額。它包括 lookup_value (單元格 J3) 和(單元格 K3)、 lookup_array (範圍 C3:C12) 和(範圍 D2:G2)，以及 return_array (範圍 H3:H12) 自變數。
=XLOOKUP(J3,C3:C12,XLOOKUP(K3,D2:G2,D3:G12))

XLOOKUP 函數二維度搜尋用法

5. XLOOKUP 函數搜尋後返回多個值用法：
例子 5.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回Q1~Q4的銷售額和總銷售額。它包括 lookup_value (單元格 J3)、 lookup_array (範圍 C3:C12)，以及 return_array (範圍 D3:H12) 自變數。
=TRANSPOSE(XLOOKUP(J3,C3:C12,D3:H12,"NG"))

XLOOKUP 函數搜尋後返回多個值用法

例子 5.2； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回 Q1~Q4 的銷售額和總銷售額。它包括 lookup_value (單元格 J3)、 lookup_array (範圍 C3:C12)，以及 return_array (範圍 D3:H12) 自變數，從最後起搜尋。
=TRANSPOSE(XLOOKUP(J3,C3:C12,D3:H12,"NG",0,-1))

XLOOKUP 函數搜尋後返回多個值用法

6. XLOOKUP 函數搜尋後FILTER 篩選用法：
例子 6.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回 Q1~Q4 的銷售額和總銷售額。它包括 lookup_value (單元格 J3)、 lookup_array (範圍 C3:C12)，以及 return_array (範圍 D3:H12) 自變數，但過濾輸出的列數。
=XLOOKUP(J3,C3:C12,FILTER(D3:H12,{0,0,0,0,1}))

XLOOKUP 函數搜尋後FILTER 篩選用法

7. XLOOKUP 函數符合大小寫比對搜尋用法：
例子 7.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回總銷售額。它包括 lookup_value (單元格 J3)、 lookup_array (範圍 C3:C12)，以及 return_array (範圍 D3:H12) 自變數，但搜尋要完全相同包括大小寫(case-sensitive)。
=XLOOKUP(TRUE, EXACT(J3,C3:C12),H3:H12, "NG")

XLOOKUP函數符合大小寫比對搜尋用法

※※ 在舊版的 Excel 中若要回傳陣列「{…}」，在公式輸入時需要使用 Ctrl + Shift +Enter 的輸入方式，會在公式最外圍自動加上一個「{…………}」。新版(Microsoft 365)則可以直接按 Enter 即可。