Excel 講義 - HackMD

# Excel 講義 :::info 課程講師資訊巨匠電腦講師：呂心怡 E-mail：zoego99@gmail.com [課程練習下載區：Excel練習題](https://drive.google.com/file/d/11fBAUvL87mG7etSsL7JjGNLlpO2Xvjsc/view?usp=sharing) [１](https://drive.google.com/file/d/1siR35Sz-uW7CAPYb6ft51Aw0HHKg7kE2/view) 、[２-６](https://www.youtube.com/playlist?list=PL4NYIOTBJXNNhb2cJd6RuXHFobs5kerk2) ::: # 0. 開始 ## (1) 選取方式 <details> <summary>【細項說明】點擊展開/摺疊</summary> 討論方向： * Excel 界面結構為【儲存格】、【列】、【欄】分別討論選取方式。 * 選取時，配合快速鍵 `Ctrl` 與 `Shift` 鍵。 A. `Shift` 連續選取 ![](https://hackmd.io/_uploads/SyE6n5xEK.png) B. `Ctrl` 挑選選取 🔶 第二個範圍以後才按住`Ctrl`鍵 🔶 移動過程 + `Ctrl` = 複製 C. 快速大範圍選取 ![](https://hackmd.io/_uploads/Bkgt0qlEK.png) </details> ## (2) 剪貼簿功能 ### 1. ![](https://hackmd.io/_uploads/ByIAqfnXt.png)剪下、![](https://hackmd.io/_uploads/H1tBsf2mF.png)複製、![](https://hackmd.io/_uploads/rkUwhGnQF.png)貼上 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/H1J52M2XK.png) </details> ### 2. ![](https://hackmd.io/_uploads/rkpsAfnQt.png)複製格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rJ9GRf3Qt.png) </details> ### 3. 相同內容同時輸入方法 <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：選取想要同時輸入相同內容的範圍 S2：不要管滑鼠位置，直接輸入 S3：輸入完成，執行 `Ctrl` (複製) + `Enter`(確定) </details> # 1. Excel基本操作 ## (1) 報表經常性的處理 ### A. 報表基本操作 #### a. 報表標題置中 <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：選取目的範圍 S2：常用 > 【對齊方式】工作群組 > 【跨欄置中】 ![](https://hackmd.io/_uploads/S16-X2gNK.png) </details> #### b. 欄寬 / 列高調整 <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：選取【欄】 S2：在欄名之間![](https://hackmd.io/_uploads/SJtqthl4F.png) (a) ![](https://hackmd.io/_uploads/SkE5qnxVY.png)，左右移動：一致性欄寬 (b) ![](https://hackmd.io/_uploads/SkE5qnxVY.png)，左鍵2下：最適欄寬(依內容調整欄寬) </details> #### c. 強制換行 <details> <summary>【細項說明】點擊展開/摺疊</summary> * Excel：`Alt` + `Enter` * Word、PPT：`Shift` + `Enter` </details> #### d. 資料列移動 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：右鍵>【插入剪下的儲存格】或快速鍵：資料欄列拖曳時+`Shift` <a href="https://hackmd.io/_uploads/BkYo1pl4t.png">![](https://hackmd.io/_uploads/BkYo1pl4t.png)</a> </details> ### B. 螢幕控制小操作 <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 利用隱藏列，將報表標題與表格分隔 b. 凍結 / 分割窗格 <a href="https://hackmd.io/_uploads/B1ynmAeNY.png"> ![](https://hackmd.io/_uploads/B1ynmAeNY.png)</a> </details> ### C. 框線繪製 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/SJS-__T81e.png) </details> ### D. 表格化 <details> <summary>【細項說明】點擊展開/摺疊</summary> 可以快速建立、格式化及展開 Excel 表格，組織工作表上的資料，讓其更易於使用。 a. 指令介紹 📌 建立 ![](https://hackmd.io/_uploads/BJko6I7NK.png) 📌 表格化指令 ![](https://hackmd.io/_uploads/Synd8KY4Y.png) b. 表格化選取方法 📌 左鍵一下選取內容、左鍵一下+一下選取內容及標題 ![](https://hackmd.io/_uploads/HkW-xKY4t.png) c. 表格化後，公式表示結構 =表格名稱〔＠欄位名稱〕 * 有＠：相對性欄位 * 沒有＠：整欄計算 d. 交叉分析篩選器 交叉分析篩選器為一視覺化控制項，以互動且直覺的方式，快速篩選僅顯示所需的資料的資料。 ![](https://hackmd.io/_uploads/ryV6FFKVt.png) e. 注意事項 🔶 移除表格化前，先確認是否要移除色彩 為了取得帶狀列的色彩，直接移除表格化 因反悔表格化功能，而表格會動用排序時，必須要移除色彩 🔶 移除方法 ![](https://hackmd.io/_uploads/ryMkItYEt.png) </details> ## (2) 列印 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【版面配置】> ![image](https://hackmd.io/_uploads/B1riJK4G0.png) <a href="https://hackmd.io/_uploads/rkCD1KEGR.png">![](https://hackmd.io/_uploads/rkCD1KEGR.png)</a> <a href="https://hackmd.io/_uploads/BknhyFEMC.png">![](https://hackmd.io/_uploads/BknhyFEMC.png)</a> </details> ## (3) 【控點填滿】及【智慧標籤】結合 ### 1. 控點填滿 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 「數列」填滿 (a) 數值 (b) 日期 (C) 文字清單 (結合自訂清單) (2) 儲存格內容「複製」 (3) 公式複製 ![](https://hackmd.io/_uploads/Skpn00ZVY.png) <details> <summary>【原始指令說明】點擊展開/摺疊</summary> 🔶 原始指令：【常用】>【編輯】工作群組>【填滿】功能 ![](https://hackmd.io/_uploads/Sk9YGyMVF.png) </details> </details> ### 2. 錯誤提醒設定 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【檔案】>【選項】 <a href="https://hackmd.io/_uploads/HylWE1f4K.png">![](https://hackmd.io/_uploads/HylWE1f4K.png)</a> </details> ### 3. 日期輸入原則的重要性 <details> <summary>【細項說明】點擊展開/摺疊</summary> 輸入結構【年/月/日】，定義為西元年份 日期以 `/` 或 `-` 區隔，時間以 `:` 區隔 今年年份輸入時可省略 Win系統為台灣版本時，民國年份之日期前方加入`R` 例`R30/3/5`，Excel自動轉換為1941/3/5。 </details> ## (4) 排序與自訂清單 ### 1. 排序 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【資料】>【排序】 <a href="https://hackmd.io/_uploads/SyPmjFaLye.png">![](https://hackmd.io/_uploads/SyPmjFaLye.png)</a> </details> ### 2. 自訂清單 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 目的 :::info ※資料排序與自訂清單結合在排序完成後以使用者角度來看，例如：職務、考績或地區，有自行的排序順序，但電腦只能依筆劃或數值排序時，則需運用【自訂清單】要求順序。 ::: 填滿序列 資料排序 樞紐分析 #### (2) 指令 【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目 <a href="https://hackmd.io/_uploads/ryAbWYaU1e.png">![](https://hackmd.io/_uploads/ryAbWYaU1e.png)</a> </details> ## (5) 快速選取【常規】大範圍 ### 1. 特殊目標..介紹 <details> <summary>【細項說明】點擊展開/摺疊</summary> 善用【常用】>【尋找與選取】>【特殊目標】 ![](https://hackmd.io/_uploads/H1hvWzfEt.png) [線上銷售明細](https://zoego.tech/salelist.html) </details> ### 2. 課程範例 🔶【範例】![](https://hackmd.io/_uploads/Bkr6XfGVt.png) <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶【步驟】 練習1：空格向下填滿 S1：選取數據範圍 S2：【常用】>【尋找與選取】 S3：【特殊目標】> 點選【空格】選項 S4：輸入`=`，方向鍵`↑` S5：輸入完成`Ctrl`+`Enter` S6：移除公式 全選範圍>【複製】>【選擇性貼上】>【值】123 練習2：依篩選條件進行美化 快速鍵：【Alt】+【；】 </details> # 2. 管理工作表 ### (1) 指令位置 <details> <summary>【細項說明】點擊展開/摺疊</summary> 相關指令在工作表索引標籤下→右鍵→即可詳見相關指令 ![](https://hackmd.io/_uploads/rJGLgvQNF.png) </details> ### (2) 注意事項 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 多張工作表同時進行設定時，善用`Ctrl`不連續選取、`Shift`連續選取工作表 (2) 【工作群組】設定必須 記得解除 解除方法： 點選未設定工作群組的工作表 已選取的工作表索引標籤→右鍵→取消工作群組設定 </details> # 3. 儲存格【值】顯示設定 ## (1) 預設格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/SyeNYFEVY.png) </details> ## (2) 自訂格式 ### A. 您是否有這樣的困擾？ <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 日期如何對齊呢？ ![](https://hackmd.io/_uploads/Sy0XJcEEK.png) b. 特殊日期格式，如何設定呢？ ![](https://hackmd.io/_uploads/Skkae5NEK.png) </details> ### B. 自訂【日期】格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/Hy5GuFTL1l.png) ※ 日期輸入原則的重要性 輸入結構【年/月/日】，定義為西元年份 日期以 `/` 或 `-` 區隔，時間以 `:` 區隔 今年年份輸入時可省略 Win系統為台灣版本時，民國年份之日期前方加入`R` 例`R30/3/5`，Excel自動轉換為1941/3/5。 </details> ### C. 自訂【數值】格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/S1L6VcEEt.png">![](https://hackmd.io/_uploads/S1L6VcEEt.png)</a><a href="https://hackmd.io/_uploads/SJP9YPgPK.png">![](https://hackmd.io/_uploads/SJP9YPgPK.png)</a> </details> ### D. 自訂【其他】格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/B1jLSsNEY.png) </details> # 4. 建立公式的觀念 ## (1) 函數介紹 ### A. 公式基本結構 <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 公式運算式元素 |類型|說明| | -------- | -------------- | |數字常數|固定值，如數字、"日期"、"時間"... "2021/01/1"、"12:30"| |文字|固定文字(前後加上雙引號)| | 參照位址 | 儲存格位址計算 = B2 * C2 跨工作表或檔案計算= [ 檔案名稱 ] 工作表名稱 ! 參照位置 | |函數語法 | = 函數名稱 ( 條件1 , 條件2 , ... )| b. 公式可用運算符號 | 符號 | 說明 | |-|-| |( ) |括號最內層括號公式先運算| |+-(正負號)、%、^(指數)、 *(乘)、/(除)、+(加)、-(減)|一般運算符號| |& 例： = "A"+"B" → 結果為 AB| 連結文字| |=、<>、<、>、>=、<= 例： =5>3 結果為 TRUE =5<3 結果為 FALSE |大小相比較| c. 常用錯誤值類型 |顯示結果|說明| |-|-| |#DIV/0!|表示除式之分母為0} |#N/A|表示計算公式中有無效值，暫不計算此值。| |#VALUE!|表示公式中的運算式的類型錯誤時，#VALUE!錯誤值出現| |#NUM!|輸入數值有問題。 如：要求出現正數處卻出現負數或數值超出範圍| |#NAME|公式內有無法辨識之名稱或函數時。| |#NULL!|當指定二個範圍相交並無交集時。| |###|輸入內容，因儲存格寬度不足，其無法顯示結果| d. 公式中「相對與絕對(固定)」的考量時機 * 公式複製時才需考量 * 利用`F4`功能鍵，進行相對與絕對(固定)切換，【$】表示為絕對(固定) * 公式【縱向】複製考量【列】是否固定 * 公式【橫向】複製考量【欄】是否固定 </details> ### B. 常用函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/ry_-tFaL1x.png) </details> ### C. 處理小數位數之函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶目的：處理小數位數之函數 ![](https://hackmd.io/_uploads/B1TO2FTUye.png) </details> ### D. 乘積計算函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/BJ1QTYaLJg.png) </details> ## (2) 【人員銷售獎金】練習 🔶 【範例】 ![](https://hackmd.io/_uploads/S1t5a2dtll.png) ### A. 金額 ( 歐元 ~ 台幣 ) ( 欄位 I ~ K ) <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 目的：了解公式中「相對與絕對」的考量時機 * 公式複製時才需考量 * 利用`F4`功能鍵，進行相對與絕對(固定)切換，【$】表示為絕對(固定) * 公式【縱向】複製考量【列】是否固定 * 公式【橫向】複製考量【欄】是否固定 b. 目的：處理小數位數之函數 ![](https://hackmd.io/_uploads/B1TO2FTUye.png)![](https://hackmd.io/_uploads/SJMQ0FUUp.png) ``` 在 I5 儲存格建立公式 = Round ( 數值 , 取捨小數位數 ) = Round ( 總金額(美金) * 匯率 , 0 ) = Round ( $H5 * I$2 , 0 ) ``` </details> ### B. IF 函數進行條件判斷 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rJWiXlHVK.png) a. 欄位M，目標達成與否 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/BJA48c8UT.png) ``` 在 K5 儲存格建立公式【銷售金額】>=【銷售目標】者標示"達成"字樣 = IF ( 判斷式 , 是 , 否 ) = IF ( 銷售金額>=銷售目標 , "達成" , "未達成" ） = IF ( K5 >= L5 , "達成" , "未達成" ） ``` </details> b. 欄位L，台幣金額達標90%，提撥獎金【台幣金額】*【獎金倍率】 <details> <summary>【細項說明】點擊展開/摺疊</summary> ▶️ 階段一：判斷是否達標 90% 者，獎金提撥 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rkQ-AqIUa.png) ``` 在 L5 儲存格建立公式 = IF ( 判斷式 , 【是】的工作 , 【否】的工作 ) = IF ( (銷售金額/銷售目標)>=90% , 金額 (台幣)*獎金倍率(0.8) , 0 ） = IF ( K5/L5>=90% , K5*$N$2 , 0 ） ``` </details> ▶️ 階段二：獎金金額取至無條件捨去至百位數 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/S1xcAc8Ip.png) ``` 在 L5 儲存格建立公式 = RoundDown ( IF((銷售金額/銷售目標)>=90%,金額(台幣)*獎金倍率(0.8), 0 ） , -2 ) = RoundDown ( IF ( K5/L5>=90% , K5*$N$2 , 0 ） , -2 ) ``` </details> </details> </details> ### C. 確認勾選 ( 欄位 O ) <details> <summary>【細項說明】點擊展開/摺疊</summary> 儲存格目的：取得獎金且為副科長者，該儲存格進行勾選 ``` 在 O5 儲存格建立公式 =IF(AND( 獎金(N欄)>0 , 組別="副科長" ) , "V" , "" ) =IF(AND( N5>0 , C5="副科長" ) , "V" , "" ) ``` </details> ### D. 權重計算 ( 欄位 T ) <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/H1j0muPn0.png) ``` 在 T5 儲存格建立公式 =SumProduct ( 數列1 , 數列2 ) =SumProduct ( P5:S5 , P$2:S$2 ) ``` </details> ### E. 排名 ( 欄位 U ) <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info = Rank ( 查詢值 , 查詢範圍 , 順序 ) 順序：指定排序的方式，　　　若省略或是填0：由大排到小，遞減　　　若填為1：由小排到大，遞增 ![](https://hackmd.io/_uploads/B1YvPlS4F.png) ::: ``` 在 U5 儲存格建立公式 =RANK ( 查詢值 , 查詢範圍 , 順序 ) =RANK ( T5 , T$5:T$24 , 0 ) ``` </details> ## (3) Iferror函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 若公式計算結果發生錯誤時，想要執行工作 :::warning = Iferror ( 判斷運算公式 , 發生錯誤欲執行工作) 評估的錯誤類型包括： #N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 和 #NULL!。 ::: ![](https://hackmd.io/_uploads/BJDgNputle.png) </details> ## (4) 設定格式化條件 <details> <summary>【細項說明】點擊展開/摺疊</summary> A. 指令： 設定格式化的條件：增加各種色彩提昇趣味性及圖示有效地呈現視覺性的簡易分析效果。指令：【常用】>【設定格式化條件】 ![](https://hackmd.io/_uploads/H1q6Ba_Fxg.png) B. 基本練習： ![](https://hackmd.io/_uploads/By76O6dFxg.png) C. 合格者設定提醒色彩 S1：選取A5:J19 S2：【常用】>【設定格式化條件】>【新增規則】>【用公式來決定…】 S3： 公式思考邏輯：當達成佔比(%) >= 100% 時，標示色彩 ![](https://hackmd.io/_uploads/rkpm5pOtgg.png) </details> # 5-1. 員工年終考績統計(1) ## A. 課程函數 ### 1. 一維查表函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 依查詢值進行資料比對輸出相關資料之函數 ![](https://hackmd.io/_uploads/BkIQs6uFlg.png) </details> ### 2. 二維查表函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) Index函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 根據欄列號索引回傳查表範圍的值 ``` = Index ( 查表範圍 , 指定列數 , 指定欄數 ) ``` <a href="https://hackmd.io/_uploads/BkEql5r4F.png">![](https://hackmd.io/_uploads/BkEql5r4F.png)</a> </details> (2) Match函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 傳回搜尋項目於搜尋範圍中的相對位 :::info Match 函數：目的：傳回搜尋項目於搜尋範圍中的相對位置公式：=Match ( 1️⃣ 查詢值, 2️⃣ 查表範圍 , 3️⃣ 比對方式類型 ) 　3️⃣ 比對方式類型： 　　1或省略：查詢範圍須『遞增』 　　0：查詢範圍不須事前排序，找到完全符合查詢值 　　-1：查詢範圍須『遞減』 ::: 🔶 公式比擬 <a href="https://hackmd.io/_uploads/rk3dE5B4t.png">![](https://hackmd.io/_uploads/rk3dE5B4t.png)</a> </details> </details> ### 3. Iferror函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 若公式計算結果發生錯誤時，想要執行工作 :::success = Iferror ( 判斷運算公式 , 發生錯誤欲執行工作) 評估的錯誤類型包括： #N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 和 #NULL!。 ::: </details> ### 4. 符合條件計算 <details> <summary>【細項說明】點擊展開/摺疊</summary> 符合條件計算函數：Countif、Sumif、CountifS、SumifS、Averageif、AverageifS ![](https://hackmd.io/_uploads/BkjfkRBNY.png) ![](https://hackmd.io/_uploads/rJAFq7RLJl.png) </details> ### 5. 兩日期間的天/月/年數 <details> <summary>【細項說明】點擊展開/摺疊</summary> ``` = Datedif ( 開始日 , 結束日 , " 單位 " ) ※ 說明：單位：決定要傳回的單位，必須前後加上 " " Y代表滿幾年、M代表滿幾月、D代表滿幾日 MD代表兩日期間的天數差，忽略月和年 YM代表兩日期間的月數差，忽略日和年 YD代表兩日期間的天數差，忽略年要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位 ``` </details> ### 6. 文字函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/rya6hRBEt.png">![](https://hackmd.io/_uploads/rya6hRBEt.png)</a> </details> ## B. 課程範例(1) ![](https://hackmd.io/_uploads/SJnfhTuYle.png) ### (1) 全勤獎金 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/BJ4nOqrVF.png) ``` 在『職員薪資明細』工作表中， K10 儲存格建立公式 =IF ( 判斷式 , 【是】的工作 , 【否】的工作 ) =IF ( [@請假時數]=0 , 2000 , 0 ) ``` </details> ### (2) 績效等級 <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 利用IF執行多選一 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/r1qPgirEt.png) 🔶方法1：IF多選1思考方向 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/HyjjxsHEK.png">![](https://hackmd.io/_uploads/HyjjxsHEK.png) </a> :::info 在『職員薪資明細』工作表中， I10 儲存格建立公式 =IF ( 　 　 　 判斷式 　 　　, 　【是】　,　　　　　　　【否】 　　　　　　 ) =IF ( [@績效評分] >=90 　,　　"A"　　,　　 IF([@績效評分]<80,"C","B") 　　 ) ::: </details> 🔶方法2：【介於】應用 <details> <summary>【細項說明】點擊展開/摺疊</summary> 📌【介於】思考邏輯 錯誤思考邏輯：`80 <= 績效評分 < 90 ` 【介於】思考邏輯： <a href="https://hackmd.io/_uploads/ryvVUsSVK.png">![](https://hackmd.io/_uploads/ryvVUsSVK.png)</a> 📌 範例結果 <a href="https://hackmd.io/_uploads/BkYpKoBVt.png">![](https://hackmd.io/_uploads/BkYpKoBVt.png)</a> :::info 在『職員薪資明細』工作表中， I10 儲存格建立公式 =IF ( 　　　判斷式　　, 【是】　,　　　　　　　【否】 　　　　　　 ) =IF ( [@績效評分]>=90 ,　"A"　, IF ( AND([@績效評分]>=80 , [@績效評分]<90 ) , "B" , "C" ) 　　 ) ::: </details> </details> b. 利用Vlookup函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/rkpoiir4F.png">![](https://hackmd.io/_uploads/rkpoiir4F.png)</a> </details> </details> ### (3) 姓名、分區、部門、職務、薪資 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/Sy5PCpOtxl.png) ``` 在『職員薪資明細』工作表中， C10 儲存格建立公式 =IFERROR( VLOOKUP([@員工編號],基本資料, MATCH(C$7,基本資料[#標題],0), FALSE) ,"") ``` </details> ### (4) 薪資區間 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/SkkVqnBEK.png) ``` 在『職員薪資明細』工作表中， P10 儲存格建立公式 VLOOKUP ( [@薪資合計] , 區間標示 , 2 , TRUE ) ``` </details> ### (5) G欄符合條件 <details> <summary>【細項說明】點擊展開/摺疊</summary> 儲存格目的G10：針對【會計】且為【薪資合計>=45000】者，進行勾選 ``` 在『職員薪資明細』工作表中， G10 儲存格建立公式 =IF(AND([@職務]="會計",[@薪資合計]>=45000),"V","") ``` </details> ## C. 課程範例(2) ### (1) 小計函數：Subtotal <details> <summary>【細項說明】點擊展開/摺疊</summary> 解決總和遇上篩選困擾 ![](https://hackmd.io/_uploads/BJePGHrNK.png) </details> ### (2) 符合條件計算 <details> <summary>【細項說明】點擊展開/摺疊</summary> 符合條件計算函數：Countif、Sumif、CountifS、SumifS、Averageif、AverageifS ![](https://hackmd.io/_uploads/ry1YgrRUJx.png) </details> ### (3) 年資 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/H1RhqCBVY.png) a. 公式說明：計算兩日期間的天數、月數或年數 ``` = Datedif ( 開始日 , 結束日 , " 單位 " ) ※ 說明：單位：決定要傳回的單位，必須前後加上 " " Y代表滿幾年、M代表滿幾月、D代表滿幾日 MD代表兩日期間的天數差，忽略月和年 YM代表兩日期間的月數差，忽略日和年 YD代表兩日期間的天數差，忽略年要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位 ``` b. 範例結果：年資計算 ``` = Datedif ( [@起聘日期] , TODAY() , "Y" ) ``` </details> ### (4) 性別 <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 公式說明：文字、數值間轉換公式 <a href="https://hackmd.io/_uploads/rya6hRBEt.png">![](https://hackmd.io/_uploads/SkLiC0S4F.png)</a> b. 公式說明：字串中抓取文字 ``` 依開始指定位置，傳回字元數的文字字串 = Mid ( 文字值 , 開始指定位置 , 欲回傳字元數) ``` c. 範例結果：性別 ![](https://hackmd.io/_uploads/BkDd1JLNF.png) </details> ### (5) Text 延伸應用 <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶 星期(中) ![](https://hackmd.io/_uploads/BkGWMJ8Vt.png)🔶 星期(英) ![](https://hackmd.io/_uploads/ryg5GyLNY.png) </details> # 5-2. 員工年終考績統計(2) ## (1) 樞紐分析介紹 樞紐分析(Pivot)：進行摘要、分析、探索，呈現資料來源的摘要表 ### A. 指令原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【插入】>【樞紐分析表】 ![](https://hackmd.io/_uploads/BJqQp1LNK.png) </details> ### B. 樞紐設定原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【插入】>【樞紐分析表】 ![](https://hackmd.io/_uploads/S1DOR1UNF.png) </details> ### C. 選項 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rJicfx8Nt.png) </details> ### D. 欄位排序 <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 手動 欄位直接拖曳 b. 自動 指令：【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目 ![](https://hackmd.io/_uploads/ryAbWYaU1e.png) </details> ## (2) 樞紐分析(1) 🔶 資料來源：員工薪資明細 ![](https://hackmd.io/_uploads/BkrvE1UEt.png) ### 1. 各職務/地區/績效等級之薪資情形 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rkmESJUEt.png) </details> ### 2. 各職務之薪資統計 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 原始：樞紐分析設定欄位情形 ![](https://hackmd.io/_uploads/HkSnSyIEY.png) (2) 結果：樞紐分析【值的顯示方式】設定情形 ![](https://hackmd.io/_uploads/HkzlIy84t.png) </details> ## (3) 樞紐分析(2) 🔶 資料來源：音樂產品銷售 ![](https://hackmd.io/_uploads/Hkx8ZHALyx.png) ### A. 開始：建立樞紐分析表 <details> <summary>【細項說明】點擊展開/摺疊</summary> 建立各銷售員、各產品商品銷售金額情形，討論其不同計算方式。 ![](https://hackmd.io/_uploads/HJZlDJUNK.png) </details> ### B. 討論：【值的顯示方式】 <details> <summary>【細項說明】點擊展開/摺疊</summary> 針對【值的顯示方式】的不同計算方式，討論其不同計算方式，其中【父項...】、【索引】不列入討論 ![](https://hackmd.io/_uploads/HkRVrC_Fel.png) a. 總計百分比/欄總計百分比/列總計百分比 ![](https://hackmd.io/_uploads/rJOnH0uYex.png) b. 百分比.../差異.../差異百分比... ![](https://hackmd.io/_uploads/HJN18CdKxg.png) c. 計算加總至.../計算加總至百分比... ![](https://hackmd.io/_uploads/Bk8GLCutxl.png) d. 最大到最小/最小到最大 ![](https://hackmd.io/_uploads/H1-NIAuKxl.png) </details> ## (4) 儀表板 專業數據儀表板，透過圖表來找出了解數據情形，並使用【互動式】在不同條件下，動態視覺化即時性觀察數據變化情形。 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/H1IUDCOtxg.png) </details> # 6. 交叉查表 ### (1) 函數介紹 <details> <summary>【細項說明】點擊展開/摺疊</summary> A. Index函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 根據欄列號索引回傳查表範圍的值 ``` = Index ( 查表範圍 , 指定列數 , 指定欄數 ) ``` <a href="https://hackmd.io/_uploads/BkEql5r4F.png">![](https://hackmd.io/_uploads/BkEql5r4F.png)</a> </details> B. Match函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 傳回搜尋項目於搜尋範圍中的相對位 :::info Match 函數：目的：傳回搜尋項目於搜尋範圍中的相對位置公式：=Match ( 1️⃣ 查詢值, 2️⃣ 查表範圍 , 3️⃣ 比對方式類型 ) 　3️⃣ 比對方式類型： 　　1或省略：查詢範圍須『遞增』 　　0：查詢範圍不須事前排序，找到完全符合查詢值 　　-1：查詢範圍須『遞減』 ::: 🔶 公式比擬 ![](https://hackmd.io/_uploads/rk3dE5B4t.png) </details> </details> ### (2) 練習 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/HkcOc0_Fxe.png) </details> # 7. 圖表 ## (1) 基本操作說明 ### A. 圖表原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/r1EUXBRUJx.png) </details> ### B. 繪製圖表上手口訣 <details> <summary>【細項說明】點擊展開/摺疊</summary> * 口訣1：白點在誰身上，對誰做美化 * 口訣2：找不到工具，針對白點處【按二下】 * 圖表原始指令：【圖表工具】>【格式】>【格式化選取範圍】 * ![](https://hackmd.io/_uploads/rJ6hPeLNt.png) </details> ### C. 圖形圖注意事項 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/SJMzoxLNF.png) </details> ## (2) 善用【主副座標軸】 ### A. 處理差異懸殊的資料 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/S1WdFlIVF.png) </details> ### B. 各項及總合值顯示技巧 🔶 範例結果 ![](https://hackmd.io/_uploads/S1_c3l8NK.png) <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：設定主副座標軸 ![](https://hackmd.io/_uploads/Bkid6lUEK.png) S2：針對合計列，快速左鍵二下，設定類別間距 ![](https://hackmd.io/_uploads/SyO1kW8Vt.png) </details> ### C. 顯示目標及當前完成情形 🔶 範例結果 ![](https://hackmd.io/_uploads/Byjxxb84Y.png) <details> <summary>【細項說明】點擊展開/摺疊</summary> 相關設定 ![](https://hackmd.io/_uploads/Hy4DebI4F.png) </details> ## (3) 善用【圖表範本】 <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶 目的：相同圖表類型，進行重覆美化 S1：針對圖表按右鍵 > 另存為範本 ![](https://hackmd.io/_uploads/rydPfWIEY.png) S2：針對想要套用範本的圖表 > 右鍵 > 【變更圖表類型】 ![](https://hackmd.io/_uploads/Hkv3QWIVt.png) </details> # 8-1. 數據彙整前置作業執行Excel工作時，有時遇到小問題卻不知道什麼原因嗎？如下圖，希望自訂排序進行名單順序，或者數據加總為什麼結果有誤….等情形，卻不道該如何解決嗎？本單元將針對工作上時常發生的小問題解析說明並分享Excel常忽略的功能介紹。 <a href="https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/excel128.jpg">![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/excel128.jpg)</a> ## (1) 快速鍵及選取 <details> <summary>【細項說明】點擊展開/摺疊</summary> A. 快速鍵 | 項目| 快速鍵| | - | - | |儲存格內強制換行|`Alt`+`Enter`| |選取非隱藏欄列|`Alt`+`;`| |儲存格格式設定|`Ctrl`+`1`| |自動加總|`Alt`+`=`| |工作表顯示列表|工作表最左側空間按`右鍵` | B. 大範圍快速選取 ![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/excel3.png) </details> ## (2) 左上角【綠點】原因？ <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/excel130.jpg) </details> ## (3) 取得數據卻無法後法計算的困擾 <details> <summary>【細項說明】點擊展開/摺疊</summary> A. 日期輸入原則的重要性 輸入結構【年/月/日】，定義為西元年份 日期以 `/` 或 `-` 區隔，時間以 `:` 區隔 今年年份輸入時可省略 B. 利用【資料剖析】轉換完成 指令：【資料】>【資料剖析】 ![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/excel25.png) C. 利用【選擇性貼上】進行運算 S1. 在任一儲存格輸入【1】> 進行【複製】 S2. 選取欲將格式進行轉換者，右鍵 > 【選擇性貼上】 ![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd_excel/e3.jpg) </details> ## (4) 如何由北到南順序排定？ <details> <summary>【細項說明】點擊展開/摺疊</summary> A. 排序 ![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/excel134.jpg) B. 自訂清單 (1) 目的 :::info ※資料排序與自訂清單結合在排序完成後以使用者角度來看，例如：職務、考績或地區，有自行的排序順序，但電腦只能依筆劃或數值排序時，則需運用【自訂清單】要求順序。 ::: 填滿序列 資料排序 樞紐分析 (2) 指令 【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目 ![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/excel135.jpg) </details> ## (5) 資料驗證 <details> <summary>【細項說明】點擊展開/摺疊</summary> 工作情境：在企業中，經常會有多個使用者同時編輯數據，防止用戶輸入無效或錯誤的數據，提高數據的準確性和一致性。 A. 指令 ![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd_excel/e4.jpg) B. 下拉清單與Excel365函數結合 a. UNIQUE：傳回範圍中唯一值清單 >[color=red][微軟官網介紹：Unique函數](https://support.microsoft.com/zh-tw/office/unique-%E5%87%BD%E6%95%B8-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e) :::info UNIQUE 函數：目的：傳回範圍中唯一值清單公式：= UNIQUE ( 1️⃣ 比對範圍, 2️⃣ 如何進行比較 , 3️⃣ 出現次數 ) <a href="https://hackmd.io/_uploads/HkAmbP1ygl.png">![](https://hackmd.io/_uploads/HkAmbP1ygl.png)</a> ::: b. 動態數組公式【A1＃】寫法 使用A1#來表示引用動態數組，可以隨著數據的變化無縫調整大小。相當於引用A1中動態數組的整個填充範圍。 </details> # 8-2. 樞紐分析 課程目標：精通樞紐分析的運用，並能製作更具專業性的圖表樞紐分析(Pivot)：進行摘要、分析、探索，呈現資料來源的摘要表 ## (1) 指令原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> A. 指令原則 指令：【插入】>【樞紐分析表】 ![](https://hackmd.io/_uploads/BJqQp1LNK.png) B. 樞紐設定原則 指令：【插入】>【樞紐分析表】 ![](https://hackmd.io/_uploads/S1DOR1UNF.png) C. 選項 ![](https://hackmd.io/_uploads/rJicfx8Nt.png) D. 欄位排序 a. 手動 欄位直接拖曳 b. 自動 指令：【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目 ![](https://hackmd.io/_uploads/SkhtieMNY.png) E. 群組類型 * 文字 * 數值 * 日期 </details> ## (2) 課程範例 🔶 資料來源：商品銷售明細 ![](https://hackmd.io/_uploads/HyxK9eoeeg.png) ### A. 各分區/縣市/銷售員之銷售情形 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/HJDIy-jgxx.png) </details> ### B. 各商品/分區之銷售情形 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/HJhVWWjgeg.png) </details> ### C. 各銷售員之銷售統計 <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 原始：樞紐分析設定欄位情形 ![](https://hackmd.io/_uploads/HJ-ezZjlxl.png) b. 結果：樞紐分析【值的顯示方式】設定情形 ![](https://hackmd.io/_uploads/SJtjGWjxge.png) </details> ### D. 各銷售員之獎金計算 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/HkwQVZjell.png) </details> ### E. 各年度銷售計算 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/HJ4iSWjgxe.png) </details> # 9. 2019版本以後新函數 ::: 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) ::: ## (1) Excel版本差異問題 ### A. #SPILL!(#溢位!) <details> <summary>【細項說明】點擊展開/摺疊</summary> >[color=red][微軟官網介紹：隱含交集運算子：@](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) [微軟官網介紹：如何修正 #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) 發生情形1： ![](https://hackmd.io/_uploads/SynFEebDt.png) 發生情形2： ![](https://hackmd.io/_uploads/H1PoExWwF.png) </details> ### B. 公式前面顯示 _xlfn. <details> <summary>【細項說明】點擊展開/摺疊</summary> 📌 錯誤示意圖 ![](https://hackmd.io/_uploads/rkQbBlZPt.png) </details> ### C. 動態數組公式寫法 <details> <summary>【細項說明】點擊展開/摺疊</summary> 運用Excel建立函數後，一定經常聽到【陣列】其思考邏輯與在2019版本後【動態數組】意義相同。 📌 陣列情境 ![](https://hackmd.io/_uploads/rk0Q2bubi.png) 📌 雙負號【- -】 說明： 1. 此非新舊函數會造成的影響，僅在條件判斷時，將邏輯值（True/False）轉換為數字（1/0） 1. 多條件篩選時，【 * 】條件為【且】，【 + 】條件為【或】 📌A1＃引用法 使用A1#來表示引用動態數組，可以隨著數據的變化無縫調整大小。相當於引用A1中動態數組的整個填充範圍。 📌 其他說明 * 檢查公式可 * 利用`F9`/還原為`Ctrl`+`Z` * 利用點選資料編輯列的【fx】 * 陣列公式完成時，`Ctrl`+`Shift`+`Enter` </details> ## (2) xlookup函數 >[color=red][微軟官網介紹：xLookup函數](https://support.microsoft.com/zh-tw/office/xlookup-%E5%87%BD%E6%95%B8-b7fd680e-6d10-43e6-84f9-88eae8bf5929) <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info =XLOOKUP ( 1️⃣ 要找的值 , 2️⃣ 從哪裡找 , 3️⃣ 傳回什麼 , 　　　　　　4️⃣ [錯誤說明], 5️⃣ [相符類型], 6️⃣ [搜尋模式] ) 　1️⃣ 要找的值：如果省略，將傳回找到的空白儲存格 　4️⃣ 錯誤說明：可省略。 　　 找不到時，回傳提供的文字；若省略，則回傳 #N/A 　5️⃣ 相符類型：可省略。 　　 0：(預設值) 若找不到，請傳回 #N/A。 　　-1：如果找不到，請傳回下一個較小的對應值。 　　 1：如果找不到，請傳回下一個較大的對應值。 　　 2：萬用字元搭配 * 、？和 ~ 具有特殊意義。 |使用|目的|範例| |-|-|-| |?|任一字元|sm？th 會找到 "smith" 和 "smyth"| |* |任何字元數|* east 會找到 "East" 和 "Southeast"| |~|尋找萬用字元|fy06~？找到"fy06？| 　6️⃣ 搜尋模式：可省略。 　　 1：查表範圍 未排序。從上往下搜尋。 (預設值) 　　-1：查表範圍 未排序。從下往上搜尋。 　　 2：查表範圍 遞增排序 搜尋。 　　-2：查表範圍 遞減排序 搜尋。 ::: </details> ## (3) IFs & Switch >[color=red][微軟官網介紹：IFs函數](https://support.microsoft.com/zh-tw/office/ifs-%E5%87%BD%E6%95%B8-36329a26-37b2-467c-972b-4a39bd951d45) [微軟官網介紹：Switch函數](https://support.microsoft.com/zh-tw/office/switch-%E5%87%BD%E6%95%B8-47ab33c0-28ce-4530-8a45-d532ec4aa25e) ### A. Switch 函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> a. 函數說明 :::info Switch 函數：目的：多條件判斷公式： = Switch ( 1️⃣ 比對的值 , 2️⃣ 符合值1 , 3️⃣ 顯示結果1 , ... ) ![](https://hackmd.io/_uploads/H1eH5mWtY.png) ::: b. 美妝範例說明 [參考文獻：母親節美妝購物季年輕vs熟齡消費偏好揭密](https://www.kantarworldpanel.com/tw/news/Taiwan-beauty-market-2024) [參考文獻：【消費者洞察】不同世代的美妝消費行為：從痛點到決策](https://cmri.itrue.com.tw/2025/03/05/202503_generational_pain_points/) ![](https://hackmd.io/_uploads/rJaCGFK0kg.png) * Z 世代：適合用短影片行銷、潮流話題與社群互動（ex. TikTok challenge、聯名產品） * Y 千禧世代：透過生活方式內容、KOL 開箱、會員制等增加黏著度 * X 世代：可強化品牌故事、專業信任感（ex. 客戶見證、老品牌印象） * 嬰兒潮世代：行銷訊息要清晰、具體，操作流程設計需簡易直覺 ``` = Switch ( 1️⃣ 比對的值 , 2️⃣ 符合值1 , 3️⃣ 顯示結果1 , … ) = Switch ( True , 年齡 < 24 , "Z世代" , 年齡<44 , " Y世代 ", 年齡<54 , " X世代 " ) ``` C. 範例說明 ![](https://hackmd.io/_uploads/BJoSThbPF.png) 📌 【部門】欄位 ![](https://hackmd.io/_uploads/ByGYSwqWs.png) 📌 【獎金】計算 ![](https://hackmd.io/_uploads/r1sK3P5Zj.png) </details> ### B. IFs 函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> A. 函數說明 :::info IFs 函數：目的：多條件判斷公式： = IFs ( 1️⃣ 判斷式1 , 2️⃣ 成立時顯示結果1 , ... ) ::: B. 範例說明 📌 【部門】欄位 ![](https://hackmd.io/_uploads/BJoSThbPF.png) 📌 【獎金】計算 ![](https://hackmd.io/_uploads/S1HYpD9bj.png) 📌 【區間判斷】公式寫法說明區間「11000～13000」的條件式寫法 ![](https://hackmd.io/_uploads/SJRTAv9-o.png) </details> ## (4) 文字串接函數：Concat / TextJoin >[color=red][微軟官網介紹：Concat函數](https://support.microsoft.com/zh-tw/office/concat-%E5%87%BD%E6%95%B8-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2) [微軟官網介紹：TextJoin函數](https://support.microsoft.com/zh-tw/office/textjoin-%E5%87%BD%E6%95%B8-357b449a-ec91-49d0-80c3-0e8fc845691c) ### A. Concatenate 函數(舊函數) <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info Concatenate 函數：公式：= Concatenate ( 1️⃣ 範圍1/文字1, 2️⃣ 範圍2/文字2 … ) 備註：此函數已由 CONCAT 函數取代。將來的 Excel 版本中可能不再提供 Concatenate 函數。 ::: </details> ### B. Concat 函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info Concat 函數：目的：合併多範圍 / 字串，但不提供分隔符號公式：= Concat ( 1️⃣ 範圍1/文字1, 2️⃣ 範圍2/文字2 ... ) 備註： (1) CONCAT 會取代 CONCATENATE 函數。 (2) 合併文字間，需要加上分隔符號，可使用TextJoin ::: 📌 Concat 範例 ``` =CONCAT(IF(名單[組別]=$I4,名單[姓名],"")) ``` ![](https://hackmd.io/_uploads/ryWwz4kl1l.png) </details> ### C. TextJoin 函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info TextJoin 函數：目的：合併多範圍 / 字串，但並提供分隔符號公式：= TextJoin ( 1️⃣ "分隔符號", 2️⃣ 是否忽略空白儲存格 , 3️⃣ 加入的文字項目/範圍 ) 　2️⃣ 是否忽略空白儲存格 　　 True：忽略空白儲存格 　　 Fasle：不忽略空白儲存格 ::: 📌 TextJoin 範例 ``` 人員列表： =TEXTJOIN("、",TRUE,IF(名單[組別]=$L4,名單[姓名],"")) 人數： =COUNTA(UNIQUE(IF(名單[組別]=L4,名單[姓名]),FALSE,TRUE)) ``` ![](https://hackmd.io/_uploads/r15df4yekg.png) </details> ## (5) UNIQUE：傳回範圍中唯一值清單 >[color=red][微軟官網介紹：Unique函數](https://support.microsoft.com/zh-tw/office/unique-%E5%87%BD%E6%95%B8-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e) ### A. UNIQUE 函數說明 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info UNIQUE 函數：目的：傳回範圍中唯一值清單公式：= UNIQUE ( 1️⃣ 比對範圍, 2️⃣ 如何進行比較 , 3️⃣ 出現次數 ) ![](https://hackmd.io/_uploads/HkAmbP1ygl.png) ::: </details> ### B. 範例 1：UNIQUE 函數介紹 <details> <summary>【細項說明】點擊展開/摺疊</summary> ``` 儲存格 L7：只出現一次人員 =UNIQUE(獎金明細[業務員],FALSE,TRUE) ------------------------------------------------ 儲存格 H7：不重覆人員 =UNIQUE(獎金明細[業務員]) 儲存格 I7：不重覆人員次數 =COUNTIF(獎金明細[業務員],H7#) 儲存格 J7：獎金總和 =SUMIF(獎金明細[業務員],H7#,獎金明細[獎金]) ``` ![](https://hackmd.io/_uploads/HkZcfEke1g.png) </details> ### C. 範例 2：與TEXTJOIN 結合 <details> <summary>【細項說明】點擊展開/摺疊</summary> ``` 儲存格 Q3：橫向顯示不重覆人員列表 =TEXTJOIN("、",TRUE,UNIQUE(B3:P3,TRUE)) ``` ![](https://hackmd.io/_uploads/SyriR9ZvF.png) </details> ### D. 範例 3：與TRANSPOSE結合 <details> <summary>【細項說明】點擊展開/摺疊</summary> ``` 儲存格 O3：各年級顯示，利用Transpose轉換列表方向 =TRANSPOSE(SORT(UNIQUE(名單[年級]),,1)) 儲存格 O4：各年級人員列表 =TEXTJOIN("、",TRUE,IF((名單[組別]=$L4)*(名單[年級]=O$3),名單[姓名],"")) ``` ![](https://hackmd.io/_uploads/rJosMEJgJg.png) </details> ## (6) Transpose：轉置範圍 >[color=red][微軟官網介紹：Transpose函數](https://support.microsoft.com/zh-tw/office/transpose-%E5%87%BD%E6%95%B8-ed039415-ed8a-4a81-93e9-4b6dfac76027) ### A. Transpose 函數說明 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info Transpose 函數：目的：轉置範圍公式：= Transpose ( 欲轉置範圍 ) ::: </details> ### B. 範例應用 <details> <summary>【細項說明】點擊展開/摺疊</summary> ``` 儲存格 A17：依【總計】欄進行排序 =SORT ( 商品銷售表,8,-1) ``` <a href="https://hackmd.io/_uploads/BkjrV_3kkl.png">![](https://hackmd.io/_uploads/BkjrV_3kkl.png)</a> </details> ## (7) Filter：依準則進行排序範圍內容 >[color=red][微軟官網介紹：Filter函數](https://support.microsoft.com/zh-tw/office/filter-%E5%87%BD%E6%95%B8-f4f7cb66-82eb-4767-8f7c-4877ad80c759) ### A. Filter 函數說明 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info Filter 函數：目的：多欄位依據進行排序公式： = Filter ( 1️⃣ 要篩選範圍 , 2️⃣ 條件 , 3️⃣ 查無時回傳的值 ) ::: </details> ### B. 範例 1：單條件篩選找出【類別】條件的範圍 <details> <summary>【細項說明】點擊展開/摺疊</summary> ``` =FILTER(商品銷售表,商品銷售表[類別]=$M$2,"查無") ``` ![](https://hackmd.io/_uploads/BkQ5ooZDK.png) </details> ### C. 範例 2：多條件篩選找出【類別：咖啡】、【業務員：許亦屏】條件的範圍 <details> <summary>【細項說明】點擊展開/摺疊</summary> 說明：多條件篩選時，【 * 】條件為【且】，【 + 】條件為【或】 ``` =FILTER( 商品銷售表, (商品銷售表[類別]=$M$2)*(商品銷售表[業務員]=$O$2), "查無" ) ``` ![](https://hackmd.io/_uploads/BJ1DJ3WDY.png) </details> #  <style> /* 調整左側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{ 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; } .h3{ color:#BF1363; font-size:1.4em; font-weight:bold; } .h4{color:#000099; font-size: 1.1em; line-height: 2.5em;} .h5{color:#7030A0; font-size:1.0em;} .fz10{font-size:1.0em;} .fz11{font-size:1.1em;} .fz12{font-size:1.2em;} /* 講師資訊美化 */ .pleft20{padding-left:20px;} .mleft20{margin-left:20px;} /* 指令縮圖 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; } /* 圖片去除圓角 */ markdown-body img, .markdown-body iframe { border-radius: none;} /* 連結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:2px;margin-left:5px;color:#000; float:right; } /* 標示靠右 */ .dright{ display:block; text-align: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;} .markdown-body details code { background-color: transparent !important; } :not(pre)>code { color:#BF1363 !important; border:none !important; background:none} .markdown-body :not(pre)>code { background: #eee !important; } /* 快速鍵 > 語法於P中 */ .markdown-body p>code { color:#BF1363 !important; border:none; background:none} .markdown-body p>code::before { color:#BF1363 !important; border:none; background:none} .markdown-body p>code::after { color:#BF1363 !important; border:none; background:none} /* details 摺疊設定 */ .markdown-body details { background-color: transparent !important; } .markdown-body details summary { color:#7d7d7d; font-size:1.5rem; /* padding-bottom: 10px; */ } /* 表格設定 */ .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;} .bold{font-weight:bold;} .red{color:#BF1363;font-weight:bold;} .blue{color:#265F92;font-weight:bold;} .pp{color:#5E00BC;} /* 紫色*/ .pp2{color:#311B92;} /* 深紫色*/ .markblue{ color:#fff; background:#232B99; border-radius:5px;padding:5px 10px;line-height:1.8em; } /* 藍色背景標註*/ .textc{text-align:center; display:block;} .markdown-body p{ margin-bottom: 0;} .block{display:block;} /* .flex{ display:flex; border:2px solid #000; padding:20px; margin:20px; } */ .ex{color:#333399 ; font-size:1em; font-weight:bold;} .eg{ color:#fff; background:#232B99; border-radius:5px;padding:5px 10px;line-height:3em;} /* 置中、圓角框、項目 */ .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; } .ub{ font-weight:bold; text-decoration: underline; } .db{ font-weight:bold; text-decoration: double; text-decoration-style: dotted; } .linkRight{display:block; text-align:right;} /* check list */ .markdown-body li>p {margin-top: 0; line-height: 1.3em;} .task-list-item+.task-list-item {margin-top: 0px} .markdown-body li+li { padding-top: 0em;} </style>