###### tags: `zoego` `開` `Excel`
# Excel 主題單元講義
:::warning
<span class="textc" style="font-weight:bold; font-size:1.2em">課程講師資訊</span>巨匠電腦講師：呂心怡
E-mail：zoego99@gmail.com
:::
# A. 主題範例
## <span class="h2">1. 月報及年累計</span>
註：課程範例中，先行針對【銷售明細】的表格化及名稱定義
### (1) 符合條件計算
符合條件計算函數：Countif、Sumif、CountifS、SumifS、Averageif、AverageifS
<a href="https://hackmd.io/_uploads/rkOzLxZPt.png"><span class="img80"></span></a>
### (2) 本月及年累計
<a href="https://hackmd.io/_uploads/B1-BUl-vK.png"><span class="img100"></span></a>
<span class="blue" style="font-size:1.2em">📌 範例說明：</span>在『總表2』工作表中，計算本月及年累計
<span class="step">S0：預備工作</span>
<span class="purple">A. 名稱定義：</span>(A) B1儲存格為【本期開始日】
(B) D1儲存格為【本期截止日】
<span class="purple">B2 儲存格報表所屬月份：</span>(A) 利用資料驗證，設定1~7月份的下拉選項
(B) 自訂格式：0"月"
<span class="step">S1：日期設定</span>
```
B1 儲存格【本期開始日】的公式
=EOMONTH(D1,-MONTH(D1))+1
```
```
D1 儲存格【本期截止日】的公式
=EOMONTH(DATE(2021,B2,1),0)
```
<span class="step">S2：本月合計</span>
<span class="purple">(A) 本年各商品筆數：</span>
```
C6 儲存格建立公式
=COUNTIFS(品號,$A6,
訂單日期,">="&EOMONTH(本期截止日,-1)+1,
訂單日期,"<="&本期截止日)
```
<span class="purple">(B) 本年各商品金額：</span>
```
D6 儲存格建立公式
=SUMIFS(金額,
品號,$A6,
訂單日期,">="&EOMONTH(本期截止日,-1)+1,
訂單日期,"<="&本期截止日)
```
<span class="step">S3：本年累計</span>
<span class="purple">(A) 本年各商品累計筆數：</span>
```
E6 儲存格建立公式
=COUNTIFS(品號,$A6,
訂單日期,">="&本期開始日,
訂單日期,"<="&本期截止日)
```
<br>
<span class="purple">(B) 本年各商品累計金額：</span>
```
F6 儲存格建立公式
=SUMIFS(金額,
品號,$A6,
訂單日期,">="&本期開始日,
訂單日期,"<="&本期截止日)
```
<br><br><br>
## <span class="h2">2. N函數/T函數</span>
### (1) 函數介紹
<span class="img50"></span>
❶ N函數，就是遇到文本變成0，否則不變
❷ T函數，就是遇到數值變成空，否則不變
>[color=blue] (1) T 函數返回值引用的文本。 T 函數可用於刪除非文本的值。
(2) 當值為文本或錯誤值時，T 函數返回準確的文本或錯誤值。
(3) 否則，該函數返回一個空文本字符串。
### (2) 例1: 隨時變化更新公式
因巨集函數不會重新計算，故需要加入以利自動更新計算
```
文本公式（計算的結果為文本） 刷新用=公式 & T( NOW( ) )
計算公式（計算的結果為數值） 刷新用=公式 + NOW( ) * 0
```
註：【數值 + 0】或者【數值 * 1】都不會改變數值的大小。
所以有：=公式&T(now())
公式後方加入【T(now( ))】、【 +NOW( ) * 0】、【 * Now( ) ^ 0】、【* Rand( )^0】
### (3) 例2: 成績缺考的改善
<span class="img80"></span>
### (4) 例3: 依小計分項加序號
<a href="https://hackmd.io/_uploads/SJm0GQhbj.png"><span class="img80"></span></a>
```
儲存格C4：
=IF( F4="小計" , "" , N(C3)+1 )
```
### (5) 例4: 累計應用
<a href="https://hackmd.io/_uploads/H19tABhWi.png"><span class="img90"></span></a>
<span class="purple">📌 G欄：累計</span>
```
儲存格G3：
=N(G2)+E3
```
<br>
<span class="purple">📌 F欄：依部門進行累計</span>
```
儲存格F3：
=IF((D2<>D3),E3,N(F2)+E3)
```
<br>
<span class="purple">📌 B欄：依部門加入序號</span>
```
儲存格A3：
=(D2<>D3)+N(B2)
```
<br>
<span class="purple">📌 依部門進行分區美化</span>S1：選取B3:G14
S2：【常用】>【條件式格式設定】>【新增規則】>【用公式來決定...】
```
儲存格B3：
=MOD( ($D2<>$D3) + N($B2) , 2 ) =1
```
<span class="img80"></span>
## <span class="h2">3. 解決Vlookup困擾</span>
><span style="text-align:right; line-height:1.5em;display:block;">[參考1：vlookup函數的使用方法](https://ppfocus.com/0/edbc762db.html)<br>[參考2：vlookup實現兩個或三個條件的多條件查找](http://www.liangshunet.com/ca/201811/917773826.htm)</span>
### (1) 有查詢值卻找不到??
因為查詢值/查詢表的欄位格式類型不同，導致無法查表。
<a href="https://hackmd.io/_uploads/rJFjmKnbi.png"><span class="img100"></span></a>
解決方法：文字轉數值： 【* 1】/ 數值轉文字：【&""】進行欄位一致。
### (2) 利用萬用字元 " * " 進行模糊查詢
```
=VLOOKUP("*"&H4&"*",訂單表,3,FALSE)
```
<span class="img100"></span>
### (3) 利用 Vlookup+IF 多表依條件查詢
因條件不同，需要查表的範圍不同，例如：依部門不同，獎金比例不同
<span class="img100"></span>
<span class="step">方法1：利用 IF 進行條件判斷</span>
```
=VLOOKUP(J4,
IF(H4="行政部",行政部獎金比例表,業務部獎金比例表),
2,TRUE)
```
<span class="step">方法2：利用 Indirect 自動查表範圍</span>
S1：分別將比例對照表，命名為【行政部比例對照表】及【業務部比例對照表】
S2：此時，可以將查詢表中【部門】欄位與"比例對照表"串接後，利用Indirect進行條件查找
```
=VLOOKUP(J4,
INDIRECT(H4&"獎金比例表"),
2,TRUE)
```
### (4) 利用 Vlookup+IF 多條件查詢
查詢該名【銷售員】+該項【商品名稱】的【銷售額】
```
I4 儲存格：
=VLOOKUP(G4,IF(商品名稱=H4,訂單表,),4,FALSE)
```
<a href="https://hackmd.io/_uploads/H1g8J3n-j.png"><span class="img100"></span></a>
### (5) 利用 IF({1,0}..)多條件 / 反向查詢
#### <span class="h4">A. 困擾情境：</span>
(1) 多條件查詢：【訂單代號 + 商品名稱】
(2) 查詢欄位不在查詢表的第一欄
<a href="https://hackmd.io/_uploads/HJlTL9nbs.png"><span class="img100"></span></a>
#### <span class="h4">B. 思考步驟：</span>
<span class="step"> S1：IF 理解 </span>
IF函數的理解：
=IF ( <span class="red">1</span> , "是" , "否" ) ➡ 返回 "是"
=IF ( <span class="red">0</span> , "是" , "否" ) ➡ 返回 "否"
<span class="step"> S2：IF + 數組 理解 </span>
IF + 數組 的理解：
=IF ( {1,0} , "是" , "否" ) ➡ 返回 "是","否"
<span class="step"> S3：套用至範例，利用 =IF ( {1,0}...) 另組列表 </span>
以範例說明，訂單代號不在查詢表的最左欄，可以利用 =IF ( {1,0}...) 另組列表。
```
G5 儲存格：
=IF ( { 1,0 } , 銷售員 , 銷售額 )
```
<a href="https://hackmd.io/_uploads/ryPWZjnbi.png"><span class="img90"></span></a>
<span class="step"> S4：利用 Vlookup + IF({1,0}...) 進行查表 </span>
利用 Vlookup + IF({1,0}...) 找出該名銷售員的銷售額
```
K5 儲存格：
=VLOOKUP( J5 , IF({1,0},銷售員,銷售額) , 2 , FALSE )
```
<a href="https://hackmd.io/_uploads/B1ukxj3-i.png"><span class="img90"></span></a>
<span class="step"> S5：多條件查詢 </span>
<a href="https://hackmd.io/_uploads/rywWDo3-i.png"><span class="img100"></span></a>
(1) 利用 =IF ( {1,0}…) 另組列表 【訂單代號 + 商品名稱】&【銷售額】
```
G4 儲存格：
= IF({1,0},訂單代號&商品名稱,銷售額)
```
(2) Vlookup + IF({1,0}...) 找出該筆訂單商品的銷售額
```
L4 儲存格：
=VLOOKUP(
J4&K4,
IF({1,0},訂單代號&商品名稱,銷售額),
2,
FALSE
)
```
### (6) 利用 Vlookup 多項目條件查詢
<span class="step">S1：利用IF({1}..)，將查詢值變成<span class="yellow"> 多個項目 </span>查詢
</span>
<span class="img100"></span>
<span class="step">S2：利用VLOOKUP，找出 2 月份 的銷售額
</span>
<span class="img100"></span>
<span class="step">S3：利用IFerror解決查無項目值困擾
</span>
<span class="img100"></span>
<span class="step">S4：利用Sum函數，加總查詢結果</span>
<span class="img100"></span>
<span class="step">S5：利用Match，彈性選擇 不同月份 計算</span>
<span class="img100"></span>
<span class="step">S6：依查詢條件，進行訂單表的色彩標註</span>
公式說明：
條件1：表中的<span class="blue"> 商品名稱 </span> 與 查詢中相同
條件2：表中的<span class="blue"> 月份 </span> 與 查詢中相同
進行色彩標註
<span class="img100"></span>
S1：選取B3:F8
S2：【常用】>【條件式格式設定】>【新增規則】>【用公式來決定…】
<span class="red">📌 第1次設定，加入黃色底色：</span>
條件1：表中的<span class="blue"> 商品名稱 </span> 與 查詢中相同
條件2：表中的<span class="blue"> 月份 </span> 與 查詢中相同
以上條件：其中一個符合則增加色彩
```
=OR( OR($B3=$H$4,B$3=$I$4) , OR($B3=$H$5,B$3=$I$4) )
```
<span class="img70"></span>
<span class="red">📌 第2次設定，加總金額標示為紅色：</span>
條件1：表中的<span class="blue"> 商品名稱 </span> 與 查詢中相同
條件2：表中的<span class="blue"> 月份 </span> 與 查詢中相同
以上條件：兩者條件皆符合則標示為文字紅色
```
=OR( AND($B3=$H$4,B$3=$I$4) , AND($B3=$H$5,B$3=$I$4) )
```
<span class="img70"></span>
<span class="red">📌 更動顯示的條件順序</span>
<span class="img100"></span>
<br><br><br>
# B. 報表視覺化操作技巧
<!-- ## <span class="h2">1. 輸入內容自動加入框線</span>
<span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span>
{%youtube YOXpwMshGKk %} -->
## <span class="h2">1. 十字交叉美化</span>
<span class="red">📌 範例結果</span>
<a href="https://hackmd.io/_uploads/rJX66-UEt.png"><span class="img100"></span></a>
<!-- ### (1) 公式建立
<span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span>
{%youtube YxT7UA6-DF8 %}
### ※ 格式化條件美化
<span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span>
{%youtube 58K211AwNSc %} -->
## <span class="h2">2. 聚光燈效果</span>
<!-- <span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span>
{%youtube Z4kv8TOeKLI %}
<br> -->
<span style="color:#5800cc; font-size:1.2em;">S1:選取欲設定聚光燈的範圍</span>
<span style="color:#5800cc; font-size:1.2em;">S2:【常用】>【條件式格式設定】>【新增規則】>【使用公式來決定...】>其公式</span>
```
=OR(CELL("row")=ROW(),CELL("col")=COLUMN())
```
<a href="https://hackmd.io/_uploads/HkWZGulvY.png"><span class="img80"></span></a>
<span style="color:#5800cc; font-size:1.2em;">S3:利用【F9】功能鍵，進行更新</span>
<br><br>
:::danger
>[color=red]<span style="text-align:right; line-height:1.5em;display:block;">[Cell 函數參考連結(微軟參考網站)](https://support.microsoft.com/zh-tw/office/cell-%E5%87%BD%E6%95%B8-51bd39a5-f338-4dbe-a33f-955d67c2b2cf)</span>
>
Cell 函數會傳回儲存格之格式、位置或內容的相關資訊。
= Cell ( info_type , [reference] )
= Cell ( "詢問儲存格的訊息" , 詢問儲存格的位置 )
說明：[reference]，若省略即顯示目前儲存格位置
| info_type| 傳回 |
| - | - |
|address|儲存格所在位置|
|contents|儲存格顯示的值|
|protect|儲存格未鎖定，其值為0<br>儲存格鎖定，其值為1|
|col|欄號|
|row|列號|
|filename|檔案路徑|
|type|儲存格資料類型格式<br><br>空白　　，回傳 "b" (代表 blank)<br>文字　　，回傳 "l" (代表 label)<br>其他類型，回傳 "v" (代表 value)|
|width|儲存格的所在寬度|
|color|如果儲存格中的負值以不同顏色顯示，則為值1；否則返回0（零）|
|prefix|儲存格對齊方法<br><br>靠左對齊　　，回傳單引號 (')<br>靠右對齊　　，回傳雙引號 (")<br>置中文字時　，回傳插入符號 (^)<br>填滿對齊　　，回傳反斜線 ( \ )<br>非以上情形　，回傳空白文字 ("")
:::
<br><br><br>
## <span class="h2">3. 下拉項目選擇圖片</span>
<span class="ex">🔶 Indirect函數應用</span>
```
= Indirect ( 定義名稱 )
```
<br>
<span class="ex">🔶 範例結果</span>
<a href="https://hackmd.io/_uploads/H10VUU3B5.png"><span class="img100"></span></a>
S1：以C2儲存格為例：
```
=VLOOKUP( $A2 , 銷售01月 !$A$2:$F$13 , 6 , FALSE )
=VLOOKUP( $A2 , indirect( $C1 & "!$A$2:$F$13" ) , 6 , FALSE )
```
以上公式完成複製於其他儲存格中即可。
S2：選取【員工編號】及【圖片】範圍，建立名稱定義，如下圖
<a href="https://hackmd.io/_uploads/HkCXYLnr5.png"><span class="img80"></span></a>
S3：將被查詢圖片的員工編號，設定為圖片的名稱定義來源，如下圖建立：
```
定義名稱：員工圖片
公式：=INDIRECT(總表!$L$3)&"_"
```
<a href="https://hackmd.io/_uploads/SyX0WvnH9.png"><span class="img90"></span></a>
S4：選取第一名員工圖片的<span class="red">儲存格</span>，利用複製，並點選【貼上】>【連結的圖片】，並將公式改為【=員工圖片】定義名稱
<a href="https://hackmd.io/_uploads/BJKtzw3Hc.png"><span class="img90"></span></a>
<br><br><br>
# C. VBA 單元
## <span class="h2">1. VBA 【逐欄】合併儲存格</span>
<span class="ex">🔶 範例結果</span>
<span class="img60"></span>
<span class="ex">🔶 VBA語法</span>
<span class="red">利用【F8】進行逐一步驟執行</span>
```
Sub MergeCell()
Dim rng As Range
Application.DisplayAlerts = False '解除Excel警訊訊息
Set rng = Selection '設定想要合併儲存格的範圍
k = rng.Count '計數想要合併儲存格的個數
For i = k To 2 Step -1
If rng(i) = rng(i - 1) Then '從後向前確認,當後方儲存格與上一個儲存格相同時
Range(rng(i - 1), rng(i)).Merge '則合併儲存格
End If
Next
Application.DisplayAlerts = True '恢復Excel警訊訊息
End Sub
```
## <span class="h2">2. VBA 取消合併儲存格並填值</span>
<span class="ex">🔶 範例結果</span>
<span class="img60"></span>
<span class="ex">🔶 VBA語法</span>
<span class="red">利用【F8】進行逐一步驟執行</span>
```
Sub UnMerge()
Dim rng As Range
Selection.UnMerge '選取範圍進行取消合併儲存格
Selection.Borders.LineStyle = xlContinuous '繪製框線
For Each rng In Selection '針對上一步驟選取範圍進行內容循環提取
If rng = "" Then '判斷是否為空值，若是
rng.Value = rng.Offset(-1, 0) '上一儲存格值填入
End If
Next
End Sub
```
## <span class="h2">3. VBA 取消所有隱藏工作表</span>
<span style="text-align:right; line-height:1.5em;display:block;">[參考網站](https://zh-tw.extendoffice.com/documents/excel/524-excel-unhide-worksheets.html)</span>
<span class="ex">🔶 VBA語法</span>
```
Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
```
<br><br><br><br><br><br>
<br><br><br><br><br>
# D. 陣列應用
## <span class="h2">1. 動態數組公式寫法</span>
<span class="blue" style="font-size:1.1em;display:block;padding:10px 0">運用Excel建立函數後，一定經常聽到【陣列】其思考邏輯與在2019版本後【動態數組】意義相同。</span>
### (1) 陣列情境
<span class="img60"></span>
數組：逗號分隔是水平數組，分號分隔是垂直數組。
### (2) 雙負號【- -】
說明：
1. 此非新舊函數會造成的影響，僅在條件判斷時，<span class="red">將邏輯值（True/False）轉換為數字（1/0）</span>
1. <span class="red">多條件篩選時，【 * 】條件為【且】，【 + 】條件為【或】</span>
<br>
### (3) A1＃引用法
在2019版本後強化陣列功能，使用A1#來表示引用動態數組，可以隨著數據的變化無縫調整大小。相當於引用A1中動態數組的整個填充範圍。
## <span class="h2">2. 例1：多範圍依條件加總</span>
<span class="blue" style="font-size:1.2em">📌 範例結果</span>
<span class="img100"></span>
<span class="blue" style="font-size:1.2em">📌 步驟</span>
S1：將2020 ~ 2022年度中的三表複製於同一欄中，利用【資料】>【移除重複項】，將相同項目合併顯示，如上圖【Q~S欄】
<br>
S2：建立【儲存格 T4】公式，思考邏輯說明：
利用以【2020年度】說明符合條件後，未來加入計算
<a href="https://hackmd.io/_uploads/BknunGdWj.png"><span class="img100"></span></a>
S3：建立【儲存格 T4】公式結果
<a href="https://hackmd.io/_uploads/SkM2cMObi.png"><span class="img100"></span></a>
```
建立【儲存格 T4】公式：
=SUM( (2020員工編號=R4) * 2020金額 , (2020員工編號=R4) * 2020金額 , (2020員工編號=R4)*2020金額 )
=SUM( ($B$4:$B$18=R4)*$D$4:$D$18 , ($J$4:$J$15=R4)*$L$4:$L$15 , ($N$4:$N$12=R4)*$P$4:$P$12 )
```
<span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span>
{%youtube cKgmmI9UHU8 %}
## <span class="h2">3. 例2：尋找文字應用</span>
### 1. 函數介紹
<span class="blue" style="font-size:1.2em">📌 Find / Serach </span>
:::info
▶️ 找出尋找文字的位置
=Serach ( "尋找的文字", 字串)
=Find ( "尋找的文字", 字串)
<br>
▶️ 其差異：
1. FIND 函數會區分大小寫，且不支援使用萬用字元。
2. Search函數中的尋找字元可以使用萬用字元 ，例如：問號 ( ? )、及星號 ( * )。
問號( ? ) 代表任何單一字元；星號( * )代表任何字元序列。
如果想要尋找「?」或「*」，則在該字元前輸入波狀符號「~」。
:::
<span class="blue" style="font-size:1.2em">📌 Switch</span>
:::info
Switch 函數：
目的：多條件判斷
公式： = Switch ( 1️⃣ 比對的值 , 2️⃣ 符合值1 , 3️⃣ 顯示結果1 , ... )
<a href="https://hackmd.io/_uploads/H1eH5mWtY.png"><span class="img80"></span></a>
:::
### 2. 範例說明
<a href="https://hackmd.io/_uploads/B1ee7dqWo.png"><span class="img100"></span></a>
```
儲存格C2：
=SWITCH(COUNT(SEARCH({".com",".tw"},B2)),
1,"內有.com 或 .tw",
2,"兩者皆有",
"皆無"
)
```
<br><br><br><br><br><br>
<br><br><br>
<br><br><br>
# E. 2019版本以後新函數
## <span class="h2">1. Excel函數列表</span>
::: danger
連結【Microsoft官網】函數列表：
(1) [Windows 版 Excel 2019 的新增功能](https://support.microsoft.com/zh-tw/office/Windows-版-Excel-2019-的新增功能-5a201203-1155-4055-82a5-82bf0994631f)
(2) [Excel 函數列表 (依英文字母順序排列)](https://support.microsoft.com/zh-tw/office/Excel-函數-依英文字母順序排列-b3944572-255d-4efb-bb96-c6d90033e188)
(3) [Excel 函數列表 (依類別排列)](https://support.microsoft.com/zh-tw/office/Excel-函數-依類別-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb)
:::
## <span class="h2">2. Excel版本差異問題</span>
### (1) #SPILL!(#溢位!)
>[color=red]<span class="urlr">[微軟官網介紹：隱含交集運算子：@](https://support.microsoft.com/zh-tw/office/%E9%9A%B1%E5%90%AB%E4%BA%A4%E9%9B%86%E9%81%8B%E7%AE%97%E5%AD%90-ce3be07b-0101-4450-a24e-c1c999be2b34) </span><span class="urlr">[微軟官網介紹：如何修正 #SPILL! 錯誤](https://support.microsoft.com/zh-tw/office/%E5%A6%82%E4%BD%95%E4%BF%AE%E6%AD%A3-spill-%E9%8C%AF%E8%AA%A4-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023) </span>
#### <span class="h4">發生情形1：</span>
<a href="https://hackmd.io/_uploads/SynFEebDt.png"><span class="img100"></span></a>
#### <span class="h4">發生情形2：</span>
<a href="https://hackmd.io/_uploads/H1PoExWwF.png"><span class="img60"></span></a>
### (2) 公式前面顯示 _xlfn.
<span class="blue" style="font-size:1.2em">📌 錯誤示意圖</span>
<a href="https://hackmd.io/_uploads/rkQbBlZPt.png"><span class="img90"></span></a>
::: danger
連結【Microsoft官網】函數列表：
(1) [Windows 版 Excel 2019 的新增功能](https://support.microsoft.com/zh-tw/office/Windows-版-Excel-2019-的新增功能-5a201203-1155-4055-82a5-82bf0994631f)
(2) [Excel 函數列表 (依英文字母順序排列)](https://support.microsoft.com/zh-tw/office/Excel-函數-依英文字母順序排列-b3944572-255d-4efb-bb96-c6d90033e188)
(3) [Excel 函數列表 (依類別排列)](https://support.microsoft.com/zh-tw/office/Excel-函數-依類別-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb)
:::
<a href="https://hackmd.io/_uploads/BkmurlWvF.png"><span class="img100"></span></a>
### (3) 動態數組公式寫法
<span class="blue" style="font-size:1.1em;display:block;padding:10px 0">運用Excel建立函數後，一定經常聽到【陣列】其思考邏輯與在2019版本後【動態數組】意義相同。</span>
#### <span class="h4">📌 陣列情境</span>
<span class="img60"></span>
#### <span class="h4">📌 雙負號【- -】</span>
說明：
1. 此非新舊函數會造成的影響，僅在條件判斷時，<span class="red">將邏輯值（True/False）轉換為數字（1/0）</span>
1. <span class="red">多條件篩選時，【 * 】條件為【且】，【 + 】條件為【或】</span>
<br>
#### <span class="h4">📌A1＃引用法</span>
使用A1#來表示引用動態數組，可以隨著數據的變化無縫調整大小。相當於引用A1中動態數組的整個填充範圍。
#### <span class="h4">📌 其他說明</span>
* 檢查公式可
* 利用`F9`/還原為`Ctrl`+`Z`
* 利用點選資料編輯列的【fx】
* 陣列公式完成時，`Ctrl`+`Shift`+`Enter`
## <span class="h2">3. 文字串接函數：Concat / TextJoin</span>
>[color=red]<span class="urlr">[微軟官網介紹：Concat函數](https://support.microsoft.com/zh-tw/office/concat-%E5%87%BD%E6%95%B8-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2) </span><span class="urlr">[微軟官網介紹：TextJoin函數](https://support.microsoft.com/zh-tw/office/textjoin-%E5%87%BD%E6%95%B8-357b449a-ec91-49d0-80c3-0e8fc845691c) </span>
### (1) Concat 函數
:::info
Concat 函數：
目的：合併多範圍 / 字串，但不提供分隔符號
公式：= Concat ( 1️⃣ 範圍1/文字1, 2️⃣ 範圍2/文字2 ... )
備註：
(1) CONCAT 會取代 CONCATENATE 函數。
(2) 合併文字間，需要加上分隔符號，可使用TextJoin
:::
📌 <span class="blue" style="font-weight:normal;font-size:.9em">Concat 範例</span>
```
=CONCAT(IF(名單[組別]=$I4,名單[姓名],""))
```
<a href="https://hackmd.io/_uploads/S1Tw8rxNc.png"><span class="img100">
</span></a>
### (2) TextJoin 函數
:::info
TextJoin 函數：
目的：合併多範圍 / 字串，但並提供分隔符號
公式：= TextJoin ( 1️⃣ "分隔符號", 2️⃣ 是否忽略空白儲存格 , 3️⃣ 加入的文字項目/範圍 )
<br>
　2️⃣ 是否忽略空白儲存格
　　 True：忽略空白儲存格
　　 Fasle：不忽略空白儲存格
:::
📌 <span class="blue" style="font-weight:normal;font-size:.9em">TextJoin 範例</span>
```
人員列表：
=TEXTJOIN("、",TRUE,IF(名單[組別]=$L4,名單[姓名],""))
人數：
=COUNTA(UNIQUE(IF(名單[組別]=L4,名單[姓名]),FALSE,TRUE))
```
<a href="https://hackmd.io/_uploads/Bk8t_BeEq.png"><span class="img100">
</span></a>
## <span class="h2">4. UNIQUE：傳回範圍中唯一值清單</span>
>[color=red]<span class="urlr">[微軟官網介紹：Unique函數](https://support.microsoft.com/zh-tw/office/unique-%E5%87%BD%E6%95%B8-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e) </span>
### (1) UNIQUE 函數說明
:::info
UNIQUE 函數：
目的：傳回範圍中唯一值清單
公式：= UNIQUE ( 1️⃣ 比對範圍, 2️⃣ 如何進行比較 , 3️⃣ 出現次數 )
<br>
　2️⃣ 如何進行比較：
　　True：傳回唯一欄
　　False (可省略)：傳回唯一列
　3️⃣ 如何進行比較：
　　True：傳回只顯示一次的項目，<span class="red">顯示『只出現一次』者</span>
　　False (可省略)：傳回每個相異的項目，只顯示一次的項目，<br>　　　　　　　　　<span class="red">顯示『不重覆』者</span>
:::
### <span class="blue" style="font-weight:normal;font-size:.9em">(2) 範例 1：UNIQUE 函數介紹</span>
```
儲存格 L7： 只出現一次人員
=UNIQUE(獎金明細[業務員],FALSE,TRUE)
------------------------------------------------
儲存格 H7： 不重覆人員
=UNIQUE(獎金明細[業務員])
儲存格 I7： 不重覆人員次數
=COUNTIF(獎金明細[業務員],H7#)
儲存格 J7： 獎金總和
=SUMIF(獎金明細[業務員],H7#,獎金明細[獎金])
```
<a href="https://hackmd.io/_uploads/Hk-RASlE9.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(3) 範例 2：與TEXTJOIN 結合</span>
```
儲存格 Q3： 橫向顯示不重覆人員列表
=TEXTJOIN("、",TRUE,UNIQUE(B3:P3,TRUE))
```
<a href="https://hackmd.io/_uploads/SyriR9ZvF.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(3) 範例 3：與TRANSPOSE結合</span>
```
儲存格 O3： 各年級顯示，利用Transpose轉換列表方向
=TRANSPOSE(SORT(UNIQUE(名單[年級]),,1))
儲存格 O4： 各年級人員列表
=TEXTJOIN("、",TRUE,IF((名單[組別]=$L4)*(名單[年級]=O$3),名單[姓名],""))
```
<a href="https://hackmd.io/_uploads/BkeUiHx45.png"><span class="img100"></span></a>
## <span class="h2">5. Sort：排序範圍內容</span>
>[color=red]<span class="urlr">[微軟官網介紹：Sort函數](https://support.microsoft.com/zh-tw/office/sort-%E5%87%BD%E6%95%B8-22f63bd0-ccc8-492f-953d-c20e8e44b86c) </span>
### (1) Sort 函數說明
:::info
Sort 函數：
目的：排序範圍內容
公式：
= Sort ( 1️⃣ 要排序範圍 , 2️⃣ 依第幾列/欄排序 , 3️⃣ 遞增/減 , 4️⃣ 排序方向列/欄 )
<br>
　2️⃣ 依第幾列/欄排序 [sort_index]
　3️⃣ 遞增/減 [sort_order]：
　　 1：遞增 (預設值)
　　-1：遞減
　4️⃣ 排序方向列/欄 [by_col]：
　　False：依『欄』排序(預設值)
　　True：依『列』排序
:::
### <span class="blue" style="font-weight:normal;font-size:.9em">(2) 範例 1：單欄顯示、單欄排序</span>
```)
儲存格 K5： 依【總計】欄進行排序
=SORT ( 商品銷售表[總計] )
```
<a href="https://hackmd.io/_uploads/H1OdNobwY.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(3) 範例 2：總表顯示，依【總計】欄排序</span>
```
儲存格 A17： 依【總計】欄進行排序
=SORT ( 商品銷售表,8,-1)
```
<a href="https://hackmd.io/_uploads/B1BX8s-DF.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(4) 範例 3：SortBy 函數，多層排序</span>
:::info
SortBy 函數：
目的：多欄位依據進行排序
公式：
= SortBy ( 1️⃣ 要排序範圍 , 2️⃣ 依據欄位1 , 3️⃣ 遞增/減 , ... )
<br>
　2️⃣ 依據欄位
　3️⃣ 遞增/減 [sort_order]：
　　 1：遞增 (預設值)
　　-1：遞減
:::
說明：利用SortBy進行依【總計】欄，進行【類別】分組排序
```
儲存格 A28： 依【總計】欄進行排序
=SORTBY(商品銷售表,商品銷售表[類別],1,商品銷售表[總計],-1)
```
<a href="https://hackmd.io/_uploads/SyUUPibPY.png"><span class="img100"></span></a>
## <span class="h2">6. Filter：依準則進行排序範圍內容</span>
>[color=red]<span class="urlr">[微軟官網介紹：Filter函數](https://support.microsoft.com/zh-tw/office/filter-%E5%87%BD%E6%95%B8-f4f7cb66-82eb-4767-8f7c-4877ad80c759) </span>
### (1) Filter 函數說明
:::info
Filter 函數：
目的：多欄位依據進行排序
公式：
= Filter ( 1️⃣ 要篩選範圍 , 2️⃣ 條件 , 3️⃣ 查無時回傳的值 )
:::
### <span class="blue" style="font-weight:normal;font-size:.9em">(2) 範例 1：單條件篩選：找出【類別】條件的範圍</span>
```
=FILTER(商品銷售表,商品銷售表[類別]=$M$2,"查無")
```
<a href="https://hackmd.io/_uploads/BkQ5ooZDK.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(3) 範例 2：多條件篩選1：找出【類別：咖啡】、【業務員：許亦屏】條件的範圍</span>
<span class="red">說明：多條件篩選時，【 * 】條件為【且】，【 + 】條件為【或】</span>
```
=FILTER(
商品銷售表,
(商品銷售表[類別]=$M$2)*(商品銷售表[業務員]=$O$2),
"查無"
)
```
<a href="https://hackmd.io/_uploads/BJ1DJ3WDY.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(4) 範例 3：多條件篩選2：【業務員：許亦屏、李明竹】條件的範圍</span>
<span class="red">說明：善用【ISNUMBER】、【MATCH】進行條件判斷</span>
```
=FILTER(商品銷售表,
IsNumber(Match(商品銷售表[業務員],C13:C14,0)),)
```
<a href="https://hackmd.io/_uploads/HkKcohrdo.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(5) 範例 4：多條件篩選3：模糊查詢字串【商品名稱：包含"咖啡"字串】的範圍，並顯示要求欄位</span>
<span class="red">S1：善用【ISNUMBER】、【Search】進行條件判斷</span>
```
=FILTER(商品銷售表,
ISNUMBER(SEARCH(B13,商品銷售表[商品名稱])))
```
<a href="https://hackmd.io/_uploads/Skajs-_Oi.png"><span class="img100"></span></a>
<span class="red">S2：利用【Filter】再次嵌套 S1篩選結果</span>
```
=FILTER(
FILTER(商品銷售表,
ISNUMBER(SEARCH(B13,商品銷售表[商品名稱]))),
,{1,1,1,0,0,0,0,1,1})
```
<a href="https://hackmd.io/_uploads/H1gVHzduj.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(6) 範例 5：篩選出前三名商品</span>
<a href="https://hackmd.io/_uploads/rJoAb2bvF.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(7) 範例 6：篩選出前三名商品，並顯示要求欄位</span>
#### <span class="h4">步驟S1： 利用Choose函數，顯示要求欄位</span>
:::info
Choose 函數：
目的：利用索引值，回傳項目列表清單中的值
公式：
= Choose ( 1️⃣ 索引值 , 2️⃣ 回傳值1 , 3️⃣ 回傳值2,... )
:::
課程範例應用
```
CHOOSE({1,2,3,4,5},
商品銷售表[類別],
商品銷售表[商品名稱],
商品銷售表[業務員],
商品銷售表[總計],
商品銷售表[排名])
```
<a href="https://hackmd.io/_uploads/HkEbFoSdj.png"><span class="img60"></span></a>
#### <span class="h4">步驟S2： 利用Filter函數，篩選前三名商品，並且依其排名</span>
```
=SORT(
FILTER(CHOOSE({1,2,3,4,5},
商品銷售表[類別],商品銷售表[商品名稱],商品銷售表[業務員],商品銷售表[總計],商品銷售表[排名]),
商品銷售表[總計]>LARGE(商品銷售表[總計],4))
,4,-1)
```
<a href="https://hackmd.io/_uploads/Bky0NhbDK.png"><span class="img100"></span></a>
### <span class="blue" style="font-weight:normal;font-size:.9em">(8) 範例 7：各業務員的銷售總金額</span>
A1＃引用法
使用A1#來表示引用動態數組，可以隨著數據的變化無縫調整大小。相當於引用A1中動態數組的整個填充範圍。
```
儲存格S23：顯示業務員列表
=UNIQUE(商品銷售表[業務員])
儲存格T23：計算各業務員銷售總金額
=SUMIF(商品銷售表[業務員],S23#,商品銷售表[總計])
```
<a href="https://hackmd.io/_uploads/SyHR8h-vK.png"><span class="img100"></span></a>
## <span class="h2">7. IFs & Switch</span>
>[color=red]<span class="urlr">[微軟官網介紹：IFs函數](https://support.microsoft.com/zh-tw/office/ifs-%E5%87%BD%E6%95%B8-36329a26-37b2-467c-972b-4a39bd951d45) </span><span class="urlr">[微軟官網介紹：Switch函數](https://support.microsoft.com/zh-tw/office/switch-%E5%87%BD%E6%95%B8-47ab33c0-28ce-4530-8a45-d532ec4aa25e) </span>
### (1) IFs 函數
#### <span class="h4">A. 函數說明</span>
:::info
IFs 函數：
目的：多條件判斷
公式： = IFs ( 1️⃣ 判斷式1 , 2️⃣ 成立時顯示結果1 , ... )
:::
#### <span class="h4">B. 範例說明</span>
<span class="purple">📌 【部門】欄位</span>
<a href="https://hackmd.io/_uploads/BJoSThbPF.png"><span class="img100"></span></a>
<span class="purple">📌 【獎金】計算</span>
<a href="https://hackmd.io/_uploads/S1HYpD9bj.png"><span class="img90"></span></a>
<!-- <span class="purple">📌 【區間判斷】公式寫法</span>說明區間「11000～13000」的條件式寫法
<a href="https://hackmd.io/_uploads/SJRTAv9-o.png"><span class="img70"></span></a> -->
<br><br><br>
## <span class="h2">7. IFs & Switch</span>
>[color=red]<span class="urlr">[微軟官網介紹：IFs函數](https://support.microsoft.com/zh-tw/office/ifs-%E5%87%BD%E6%95%B8-36329a26-37b2-467c-972b-4a39bd951d45) </span><span class="urlr">[微軟官網介紹：Switch函數](https://support.microsoft.com/zh-tw/office/switch-%E5%87%BD%E6%95%B8-47ab33c0-28ce-4530-8a45-d532ec4aa25e) </span>
### (1) IFs 函數
#### <span class="h4">A. 函數說明</span>
:::info
IFs 函數：
目的：多條件判斷
公式： = IFs ( 1️⃣ 判斷式1 , 2️⃣ 成立時顯示結果1 , ... )
:::
#### <span class="h4">B. 範例說明</span>
<span class="purple">📌 【部門】欄位</span>
<a href="https://hackmd.io/_uploads/BJoSThbPF.png"><span class="img100"></span></a>
<span class="purple">📌 【獎金】計算</span>
<a href="https://hackmd.io/_uploads/S1HYpD9bj.png"><span class="img90"></span></a>
<!-- <span class="purple">📌 【區間判斷】公式寫法</span>說明區間「11000～13000」的條件式寫法
<a href="https://hackmd.io/_uploads/SJRTAv9-o.png"><span class="img70"></span></a> -->
<br><br><br>
### (2) Switch 函數
#### <span class="h4">A. 函數說明</span>
:::info
Switch 函數：
目的：多條件判斷
公式： = Switch ( 1️⃣ 比對的值 , 2️⃣ 符合值1 , 3️⃣ 顯示結果1 , ... )
<a href="https://hackmd.io/_uploads/H1eH5mWtY.png"><span class="img80"></span></a>
:::
#### <span class="h4">B. 範例說明</span>
<a href="https://hackmd.io/_uploads/BJoSThbPF.png"><span class="img100"></span></a>
<span class="purple">📌 【部門】欄位</span>
<a href="https://hackmd.io/_uploads/SkKeRsHOi.png"><span class="img100"></span></a>
<br><br><br>
<span class="purple">📌 【獎金】計算</span>
<a href="https://hackmd.io/_uploads/r1sK3P5Zj.png"><span class="img100"></span></a>
<br><br><br>
## <span class="h2">8. Xlookup函數</span>
>[color=red]<span class="urlr">[微軟官網介紹：xLookup函數](https://support.microsoft.com/zh-tw/office/xlookup-%E5%87%BD%E6%95%B8-b7fd680e-6d10-43e6-84f9-88eae8bf5929) </span>
### (1) XLOOKUP介紹
:::info
=XLOOKUP ( 1️⃣ 要找的值 , 2️⃣ 從哪裡找 , 3️⃣ 傳回什麼 , <br>　　　　　　4️⃣ [錯誤說明], 5️⃣ [相符類型], 6️⃣ [搜尋模式] )
　1️⃣ 要找的值：如果省略，將傳回找到的空白儲存格
　4️⃣ 錯誤說明：可省略。
　　 找不到時，回傳提供的文字；若省略，則回傳 #N/A
　5️⃣ 相符類型：可省略。
　　 0：(預設值) 若找不到，請傳回 #N/A。
　　-1：如果找不到，請傳回下一個較小的對應值。
　　 1：如果找不到，請傳回下一個較大的對應值。
　　 2：萬用字元搭配 * 、 ？ 和 ~ 具有 特殊意義。
|使用|目的|範例|
|-|-|-|
|?|任一字元|sm？th 會找到 "smith" 和 "smyth"|
|* |任何字元數|* east 會找到 "East" 和 "Southeast"|
|~|尋找萬用字元|fy06~？ 找到"fy06？|
<br>
　6️⃣ 搜尋模式：可省略。
　　 1：查表範圍 <span class="red">未排序</span>。從上往下搜尋。 (預設值)
　　-1：查表範圍 <span class="red">未排序</span>。從下往上搜尋。
　　 2：查表範圍 <span class="red">遞增排序</span> 搜尋。
　　-2：查表範圍 <span class="red">遞減排序</span> 搜尋。
:::
### <span class="blue" style="font-weight:normal;font-size:.9em">(2) 範例 1：依品號找出其商品名稱</span>
```
在『報價單』工作表中， D15 儲存格修改公式
函數： =XLOOKUP(要找的值 , 從哪裡找 , 傳回什麼 , [錯誤說明] , [相符類型] )
修改： =XLOOKUP( $B15 , 商品明細[品號] , 商品明細[品名] , "" , 0 )
```
<br><br>
### <span class="blue" style="font-weight:normal;font-size:.9em">(3) 範例 2：依銷售金額找出銷售區間</span>
<span class="step">範例說明：依右表查詢，顯示各商品銷售金額的區間分項情形。</span>
<a href="https://hackmd.io/_uploads/SyEAgg-Dt.png"><span class="img100"></span></a>
```
在『總表1』工作表中， E4 儲存格建立公式
函數： =XLOOKUP(要找的值 , 從哪裡找 , 傳回什麼 , [錯誤說明] , [相符類型] )
說明： =XLOOKUP( 總金額 , 區間 , 區間標示 , 省略 , 接近值回傳較小值 )
修改： =XLOOKUP( $D4 , $A$19:$A$23 , $B$19:$B$23 , , -1 )
```
<br><br>
<span class="blue" style="font-weight:normal;font-size:.9em">(4) 範例 3：模糊查詢</span>
<span class="step">範例說明：利用模糊查詢，尋找商品銷售情形。</span>
<a href="https://hackmd.io/_uploads/BksbWlZvK.png"><span class="img100"></span></a>
```
在『總表1』工作表中， E4 儲存格建立公式
函數： =XLOOKUP(要找的值, 從哪裡找 , 傳回什麼 ,[錯誤說明], [相符類型] , [搜尋模式])
說明： =XLOOKUP( *麵* , 品名 , 品名 , 省略 , 2萬用字元比對 , 尋找方向 )
修改： =XLOOKUP( $F$19 ,$C$5:$C$16,$C$5:$C$16, , 2 , 1 )
```
<br><br><br><br><br><br>
# F. PowerQuery (PQ)
## <span class="h2">1. 多檔案匯入</span>
透過Power Query將多個資料來源的資料合併、追加到一起，任意組合資料、將資料進行分組、透視等整理操作方法快速整併成同一個，以利數據分析。
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq26.jpg"><span class="img80"></span></a>
S1：開啟Excel新檔 > 載入資料 > 活頁簿
【2016版Excle】
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq27.jpg"><span class="img60"></span></a>
【365版Excle】
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq28.jpg"><span class="img50"></span></a>
S1：建立 PowerBI新檔 > 常用 > 取得資料 > 其他 > 活頁簿
【PowerBI 軟體】
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq99.jpg"><span class="img80" style="border:1px solid #000"></span></a>
S2：顯示載入資料情形 > 點選【轉換資料】
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq29.jpg"><span class="img100"></span></a>
S3：保留【Content】欄位，其他欄位移除，其內容如下：
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq30.jpg"><span class="img100"></span></a>
S4：【新增資料行】>【自訂資料行】
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq98.jpg"><span class="img60 textc"></span></a>
:::danger
(1) 載入CSV檔案至PQ：
　　 Csv.Document ( )
(2) 本身Excel檔案載入至PQ：
　　 Excel.CurrentWorkbook ( ) { [ Name="表格名稱" ] } [Content]
(3) 由其他Excel檔案載入至PQ：
　　 Excel.Workbook ( [Content] , true )
:::
```
= Excel.Workbook([Content],true)
```
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq31.jpg"><span class="img100"></span></a>
<span class="red bold">※ 若為多工作表時，需要指定工作表標籤名稱匯入時的語法</span>
```
= Excel.Workbook([Content]){[Item="Sheet1"]}[Data])
```
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq107.jpg"><span class="img100"></span></a>
S6：依序展開其欄位
## <span class="h2">2. 利用合併查詢進行兩表比對</span>
利用合併查詢進行兩表比對，比Vlookup查詢更加簡單及靈活
### A. 說明：六種查詢理論
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq1.jpg"><span class="img100"></span></a>
### B. 資料比對：找出新/舊/離職員工
#### <span class="h4">(1) 工作目標</span>
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq2.jpg"><span class="img70"></span></a>
#### <span class="h4">(2) 說明</span>
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq3.jpg"><span class="img100"></span></a>
#### <span class="h4">(3) 步驟</span>
S1：載入『表7月』、『表8月』表格
S2：【常用】>【合併查詢】>【將查詢合併為新查詢】，並展開其合併欄位
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq4.jpg"><span class="img80"></span></a><a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq5.jpg"><span class="img80"></span></a>
S3：【新增資料行】>【條件資料行】，進行新舊員工類型判斷
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq6.jpg"><span class="img80"></span></a>
S4：最後整理
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq7.jpg"><span class="img100"></span></a>
## <span class="h2">3. 分組依據應用</span>
### 範例：依部門進行列表及計算
<!-- >[color=red]<span style="text-align:right; line-height:2em;display:block;">[PQ數據處理入門課程_講師：雲淡風輕微課堂](https://study.163.com/course/courseMain.htm?courseId=1006378110&share=1&shareId=1392317403)</span> -->
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq106.jpg"><span class="img100"></span></a>
S1：利用【分組依據】，針對【部門】欄位進行分組
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq102.jpg"><span class="img100"></span></a>
S2：利用【自訂資料行】將人員姓名進行導出
```
從中了解 [表][資料行]結構，進行拆解
人員列表 =[人員明細][姓名]
```
(1) 【自訂資料行】結果顯示
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq103.jpg"><span class="img100"></span></a>
(2) 【擷取值】結果顯示
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq104.jpg"><span class="img100"></span></a>
## <span class="h2">4. { List } 應用</span>
### A. 廠商及訂單編號列表
#### <span class="h4">(1) 說明：【笛卡爾積 , 笛卡兒積】應用</span>
笛卡爾乘積是指在數學中，兩個集合X和Y的笛卡爾積（Cartesian product），又稱直積，表示為X×Y，第一個對象是X的成員而第二個對象是Y的所有可能有序對的其中一個成員。簡單來說就是2組資料互相交集.<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq21.jpg"><span class="img70"></span></a>
#### <span class="h4">(2) 步驟</span>
S1：利用分割資料切割新增【訂單編號_起】、【訂單編號_迄】欄位
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq22.jpg"><span class="img100"></span></a>
S2：【新增資料行】>【自訂資料行】
```
資料行名稱list
= {[#"訂單編號 -起"]..[#"訂單編號 - 迄"]}
```
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq95.jpg"><span class="img100"></span></a>
### B. 文本 移除 / 提取
#### <span class="h4">(1) 說明：文本 移除 / 提取函式</span>
<span class="red">📌 Text.Select ( ) 和Text.Remove ( )</span>
<br>
|提取項目| M函數|
|-|-|
|各種特殊符號、0-9數值、<br>所有大小寫英文字母|Text.Select ( [文本資料行] , { ″ ″ .. ″~″})|
|數值0-9|Text.Select ( [文本資料行] , { 0 .. 9})|
|文字0-9|Text.Select ( [文本資料行] , { ″0″ .. ″9″})|
|英文大寫|Text.Select ( [文本資料行] , { ″A″ .. ″Z″})|
|英文小寫|Text.Select ( [文本資料行] , { ″a″ .. ″z″})|
|英文大/小寫|Text.Select ( [文本資料行] , { ″A″ .. ″z″})|
|中文|Text.Select ( [文本資料行] , { ″一″ .. ″龜″})|
#### <span class="h4">(2) 練習：中英文拆分</span>
<span class="red">【目的】</span>
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq23.jpg"><span class="img60"></span></a>
<span class="red">【步驟】</span>
S1：抓取中文文字語法，新增【自訂資料行】>公式為【 = Text.Select([產品],{″一″..″龜″})】
<br>
```
= Text.Select([商品],{"一".."龜"})
```
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq24.jpg"><span class="img100"></span></a>
S2：抓取英文，新增【自訂資料行】><br>公式為【 = Text.Trim ( Text.Select ( [產品] , {″ a ″…″ z ″,″ A ″…″ Z ″,″ ″,″ . ″,″ ‘ ″}))】<br>移除前後多餘空格，因範例中英文有【’】及【.】
<br>
```
= Text.Trim ( Text.Select ( [商品] , {"a".."z","A".."Z"," ",".","'"}))
```
<a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/pq25.jpg"><span class="img100"></span></a>
S3：也可以移除中文字，來取得英文
<br>
```
= Text.Remove([商品],{"一".."龜"})
```
<br><br><br><br><br><br><br><br>
<br><br><br>
# <!-- style美化 -->
<style>
/* 加入Logo */
.toc:{position: relative;}
.toc:before{
content: url(https://zoego.tech/img/logo.svg);
width:80%; height:80%
position:fixed; top:0 ; left:0;
}
/* 調整 左側NAV */
.markdown-body { max-width: 900px !important;}
.markdown-body h1,.markdown-body h2{border-bottom: none;}
.ui-toc-dropdown .nav>li>a{ color: #000}
.ui-toc-dropdown .nav>.active:focus>a, .ui-toc-dropdown .nav>.active:hover>a, .ui-toc-dropdown .nav>.active>a{ color:#C00000;}
/* h1~H5 修改 */
.h1_title{
color:#4C4C6D; text-align:center; font-weight:bold; font-size:2em; padding:0;margin:0}
h1{ color:#4C4C6D; }
.h2{ color:#52006A; background-color:#A2DBFA; line-height:1.8em;font-size:.9em;display:block; padding-left:10px;border-radius:5px;}
h3{ color:#BF1363; }
.h4{color:#000099; font-size:1.2em;}
.h5{color:#7030A0; font-size:1.2em;}
.p{ margin:2em 0;}
/* 講師資訊 美化 */
.alert-warning { border-width: 2px; width: 60%; margin: auto;}
/* 指令 縮圖 ICON圖設定 */
.img3{ width:30px; display:inline-block;margin:0 5px;}
.img4{ width:40px; display:inline-block;margin:0 5px;}
.img5{ width:50px; display:inline-block;margin:0 5px;}
.img6{ width:60px; display:inline-block;margin:0 5px;}
/* 圖片尺寸 設定 */
.img20{ width:20%; margin:20px auto; display:block; text-align: center;}
.img30{ width:30%; margin:20px auto; display:block; text-align: center;}
.img40{ width:40%; margin:20px auto; display:block; text-align: center;}
.img50{ width:50%; margin:20px auto; display:block; text-align: center;}
.img60{ width:60%; margin:20px auto; display:block; text-align: center;}
.img70{ width:70%; margin:20px auto; display:block; text-align: center;}
.img80{ width:80%; margin:20px auto; display:block; text-align: center;}
.img90{ width:90%; margin:20px auto; display:block; text-align: center;}
.img100{ width:100%; margin:20px auto; display:block; }
/* 連結URL 調整 */
.urlr{ text-align:right; line-height:1.5em;display:block;}
.urll{ line-height:1.5em;display:block;}
/* 提供講義標示頁碼 */
.page{display:flex; justify-content: right;}
.page span{
background:#ffdd75;border:1px solid #d1a128;border-radius:5px; padding:5px;color:#000;text-align:right; float:right;
}
/* Slider 外掛 美化 */
iframe{ width:850px; height:478px; display:block; margin:auto; border-width: 0;border:2px solid #000;}
/* 設定 pre 及 快速鍵 */
.markdown-body code { border: 1px solid #000; margin:0 5px; }
.markdown-body pre>code { font-size: 1.2em; }
.markdown-body pre { border: 1px solid #ab0505 !important; margin:10px 0; padding:5px 10px;}
/* 快速鍵 > 語法於P中 */
.markdown-body p>code { color:#BF1363 !important; border:none; background:none}
/* 表格 設定 */
.markdown-body table {
width: fit-content;
margin: auto;
}
.markdown-body table th, .markdown-body table td {
border: 1px solid #7F7F7F;
}
.tableTitle{text-align:center; display:block;}
.red{color:#BF1363;font-weight:bold;}
.blue{color:#265F92;font-weight:bold;}
.yellow{color:#fff176;font-weight:bold;}
.purple{
color:#5E00BC;
display:block;font-weight:bold;
font-size:1.1em
}
.step{ padding-left:1.5em;color:blue;font-size:1.1em; }
.textc{text-align:center; display:block;}
.markdown-body p{ margin-bottom: 2;}
.ex{color:#333399 ; font-size:1.2em; font-weight:bold; line-height:1em;}
/* 置中、圓角框、項目 */
.title{ text-align:center; display:block;color:#766161;font-size:1.2em;font-weight:bold; border:1px solid;line-height:2em;border-radius:5px}
.youtube:before{
content:"";
display:block;
position:absolute;
top:5%;
left:5%;
z-index:1;
/* transform:translate(-50%,-50%); */
width:80px;
height:80px;
background:url(https://zoego.tech/img/icon_youtube2.png);
background-size: contain;
border-radius: 10px;
box-shadow: 1px 1px 3px #000;
}
.youtube{
border:1px solid #333;
}
.step{
color:#fff; font-size:1.1em;
/* background:#b90303; */
background:#1a237e;
padding:5px; margin-top:10px;
display:inline-block; border-radius:5px;
}
</style>
Sign in with Wallet
Connect another wallet