Excel 講義 - HackMD

###### tags: `zoego` `開` `Excel` # Excel 講義 :::warning 課程講師資訊巨匠電腦講師：呂心怡 E-mail：zoego99@gmail.com [課程練習下載區：https://bit.ly/346jldq](https://bit.ly/346jldq) [AVI](https://www.youtube.com/playlist?list=PL4NYIOTBJXNOXdVAlesvFZdsQeBzNLyHu&jct=uFDAlnn5rjq2POwhD9sX16xKe2Z_2g) ::: # 00. 開始 ## A. Excel可以做什麼？ ### 1. Excel 可以做什麼？ <a class="btn btn-btn btn-warning" href="https://zoego.tech/pptslider/excel_00_start.html">展開</a> <iframe src="https://zoego.tech/pptslider/excel_00_start.html" width="100%" ></iframe> ### 2. Excel 軟體結構 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://filedn.com/lzai0pPTnpfyPiXAjMDqr4V/hackmd/excel1.png) </details> ## B. 選取方式 <details> <summary>【細項說明】點擊展開/摺疊</summary> 討論方向： 1.Excel 界面結構為【儲存格】、【列】、【欄】分別討論選取方式。 2.選取時，配合快速鍵`Ctrl`與`Shift`鍵。 1. `Shift` 連續選取 ![](https://hackmd.io/_uploads/SyE6n5xEK.png) 2. `Ctrl` 挑選選取 🔶 第二個範圍以後才按住`Ctrl`鍵 🔶 移動過程 + `Ctrl` = 複製 3. 快速大範圍選取 ![](https://hackmd.io/_uploads/Bkgt0qlEK.png) </details> ## C. 剪貼簿功能 ### 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> ## D. 必要性快速鍵 <details> <summary>【細項說明】點擊展開/摺疊</summary> |項目A|快速鍵A|項目B|快速鍵B| |-|-|-|-| |儲存格內強制換行|`Alt`+`Enter` 公式內自動換行=Char(10) 相反地移除無法列印元素 =clean(儲存格) |多範圍內容輸入|`Ctrl`+`Enter`| |自動累加1|儲存格右下角控點拖曳時+`Ctrl`|選取非隱藏欄列|`Alt`+`;`| |拖曳移動範圍|資料欄列拖曳時+`Shift`|自動加總|`Alt`+`=`| |複製後選取範圍貼入|`Enter`|表格化|`Ctrl`+`T`| |執行重複指令|`F4`|快速填入(2013)|`Ctrl`+`E`| |儲存格格式設定|`Ctrl`+`1`|顯示公式計算結果|`F9` 點選資料編輯列的【fx】| 註：常用的ASCII碼對應表 |代碼|元素|代碼|元素| |-|-|-|-| |char(9)|tab空格|char(10)|換行| |char(13)|換行|char(32)|空格|  </details> # 01. Excel基本操作 ## A. 報表經常性的處理 ### 1. 報表基本操作 #### (1) 報表標題置中 <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：選取目的範圍 S2：常用 > 【對齊方式】工作群組 > 【跨欄置中】 ![](https://hackmd.io/_uploads/S16-X2gNK.png) </details> #### (2) 欄寬 / 列高調整 <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> #### (3) 強制換行 <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶 Excel：`Alt` + `Enter` 🔶 Word、PPT：`Shift` + `Enter` </details> #### (4) 資料列移動 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：右鍵>【插入剪下的儲存格】或快速鍵：資料欄列拖曳時+`Shift` <a href="https://hackmd.io/_uploads/BkYo1pl4t.png">![](https://hackmd.io/_uploads/BkYo1pl4t.png)</a> </details> ### 2. 螢幕控制小操作 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 利用隱藏列，將報表標題與表格分隔 (2) 凍結 / 分割窗格 <a href="https://hackmd.io/_uploads/B1ynmAeNY.png"> ![](https://hackmd.io/_uploads/B1ynmAeNY.png)</a> (3) 自訂檢視模式 ![](https://hackmd.io/_uploads/HylznvTB9.png) (4) 大綱(組成群組) 指令：【資料】>【組成群組】/【自動建立大綱】 <a href="https://hackmd.io/_uploads/SJZoswpS9.png">![](https://hackmd.io/_uploads/SJZoswpS9.png)</a> ![](https://hackmd.io/_uploads/rJ3AeWGEF.png) (5) 善用前導/從屬參照 <a href="https://hackmd.io/_uploads/By5-bbzVK.png">![](https://hackmd.io/_uploads/By5-bbzVK.png)</a> </details> ### 3. 框線繪製 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 基本原則 <a href="https://hackmd.io/_uploads/SJS-__T81e.png">![](https://hackmd.io/_uploads/SJS-__T81e.png)</a> (2) 表格左上角繪製 <a href="https://hackmd.io/_uploads/SkDoO0WVF.png">![](https://hackmd.io/_uploads/SkDoO0WVF.png)</a> </details> ### 4. 表格化 <details> <summary>【細項說明】點擊展開/摺疊</summary> 可以快速建立、格式化及展開 Excel 表格，組織工作表上的資料，讓其更易於使用。 (1) 指令介紹 📌 建立 <a href="https://hackmd.io/_uploads/BJko6I7NK.png">![](https://hackmd.io/_uploads/BJko6I7NK.png)</a> 📌 表格化指令 <a href="https://hackmd.io/_uploads/Synd8KY4Y.png">![](https://hackmd.io/_uploads/Synd8KY4Y.png)</a> (2) 表格化選取方法 📌 左鍵一下選取內容、左鍵一下+一下選取內容及標題 ![](https://hackmd.io/_uploads/HkW-xKY4t.png) (3) 表格化後，公式表示結構 =表格名稱〔＠欄位名稱〕 有＠：相對性欄位 沒有＠：整欄計算 (4) 交叉分析篩選器 交叉分析篩選器為一視覺化控制項，以互動且直覺的方式，快速篩選僅顯示所需的資料的資料。 <a href="https://hackmd.io/_uploads/ryV6FFKVt.png">![](https://hackmd.io/_uploads/ryV6FFKVt.png) </a> (5) 注意事項 🔶 移除表格化前，先確認是否要移除色彩 為了取得帶狀列的色彩，直接移除表格化 因反悔表格化功能，而表格會動用排序時，必須要移除色彩 🔶 移除方法 ![](https://hackmd.io/_uploads/ryMkItYEt.png) </details> ## B. 【控點填滿】及【智慧標籤】結合 ### 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> ## C. 排序與自訂清單 ### 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> ## D. 快速選取【常規】大範圍 ### 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> ## E. 改善跨欄置中的不方便 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![image](https://hackmd.io/_uploads/r1QtZvELp.png) </details> ## E. 列印 <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> # 02. 管理工作表 ## A. 管理工作表 ### 1. 指令位置 <details> <summary>【細項說明】點擊展開/摺疊</summary> 相關指令在工作表索引標籤下→右鍵→即可詳見相關指令 ![](https://hackmd.io/_uploads/rJGLgvQNF.png) </details> ### 2. 注意事項 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 多張工作表同時進行設定時，善用`Ctrl`不連續選取、`Shift`連續選取工作表 (2) 【工作群組】設定必須 記得解除 解除方法： 點選未設定工作群組的工作表 已選取的工作表索引標籤→右鍵→取消工作群組設定 </details> ## B. 保護【檔案】 <details> <summary>【細項說明】點擊展開/摺疊</summary> 【檔案】>【資訊】>【保護活頁簿】，其中包含檔案唯、密碼加密及顯示完稿無法編輯 ![](https://hackmd.io/_uploads/r1SDT4HNt.png) </details> ## C. 保護【工作表】 <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：解除不保護的儲存格 🔶 方法1：儲存格鎖定 【儲存格格式】之下， (1) 鎖定：在保護工作表之下，勾選鎖定，即保護儲存格不可修改 ![](https://hackmd.io/_uploads/SJqnsNrVt.png) (2)隱藏：在保護工作表之下，勾選隱藏，即在資料編輯列下不顯示公式 🔶 方法2：設定允許編輯範圍 指令：【校閱】>【允許編輯範圍】 ![](https://hackmd.io/_uploads/By1JaVrEK.png) S2：保護工作表：【校閱】>【保護工作表】 ![](https://hackmd.io/_uploads/SJFJpNSVK.png) </details> ## D. 保護活頁簿及工作表 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/SJZdQvXNF.png) </details> # 03. 儲存格【值】顯示設定 ## A. 預設格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/SyeNYFEVY.png) </details> ## B. 自訂格式 ### 1. 您是否有這樣的困擾？ <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 日期如何對齊呢？ ![](https://hackmd.io/_uploads/Sy0XJcEEK.png) (2) 特殊日期格式，如何設定呢？ ![](https://hackmd.io/_uploads/Skkae5NEK.png) (3) 報表數值異常時，如何顯示顏色呢？ <a href="https://hackmd.io/_uploads/SkVZmq44Y.png">![](https://hackmd.io/_uploads/SkVZmq44Y.png)</a> </details> ### 2. 自訂【日期】格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/Hy5GuFTL1l.png">![](https://hackmd.io/_uploads/Hy5GuFTL1l.png)</a> ※ 日期輸入原則的重要性 輸入結構【年/月/日】，定義為西元年份 日期以 `/` 或 `-` 區隔，時間以 `:` 區隔 今年年份輸入時可省略 Win系統為台灣版本時，民國年份之日期前方加入`R` 例`R30/3/5`，Excel自動轉換為1941/3/5。 </details> ### 3. 自訂【數值】格式 <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> ### 4. 自訂【其他】格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/B1jLSsNEY.png) </details> ### 5. 數值以【零】為基準格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/HyXQU5NEK.png">![](https://hackmd.io/_uploads/HyXQU5NEK.png)</a> 🔶【範例】 <a href="https://hackmd.io/_uploads/rkSSw54VF.png">![](https://hackmd.io/_uploads/rkSSw54VF.png)</a> </details> ### 6. 數值以【非零】為基準格式 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/Sy8cwqE4t.png">![](https://hackmd.io/_uploads/Sy8cwqE4t.png) </a>🔶【範例】 <a href="https://hackmd.io/_uploads/B1mswcNVY.png">![](https://hackmd.io/_uploads/B1mswcNVY.png) </a> </details> # 04. 建立公式的觀念 ## A. 函數介紹 ### 1. 公式基本結構 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 公式運算式元素 |類型|說明| | -------- | -------------- | |數字常數|固定值，如數字、"日期"、"時間"... "2021/01/1"、"12:30"| |文字|固定文字(前後加上雙引號)| | 參照位址 | 儲存格位址計算 = B2 * C2 跨工作表或檔案計算= [ 檔案名稱 ] 工作表名稱 ! 參照位置 | |函數語法 | = 函數名稱 ( 條件1 , 條件2 , ... )| (2) 公式可用運算符號 | 符號 | 說明 | |-|-| |( ) |括號最內層括號公式先運算| |+-(正負號)、%、^(指數)、 *(乘)、/(除)、+(加)、-(減)|一般運算符號| |& 例： = "A"+"B" → 結果為 AB| 連結文字| |=、<>、<、>、>=、<= 例： =5>3 結果為 TRUE =5<3 結果為 FALSE |大小相比較| (3) 常用錯誤值類型 |顯示結果|說明| |-|-| |#DIV/0!|表示除式之分母為0} |#N/A|表示計算公式中有無效值，暫不計算此值。| |#VALUE!|表示公式中的運算式的類型錯誤時，#VALUE!錯誤值出現| |#NUM!|輸入數值有問題。 如：要求出現正數處卻出現負數或數值超出範圍| |#NAME|公式內有無法辨識之名稱或函數時。| |#NULL!|當指定二個範圍相交並無交集時。| |###|輸入內容，因儲存格寬度不足，其無法顯示結果| (4) 公式中「相對與絕對(固定)」的考量時機 公式複製時才需考量 利用`F4`功能鍵，進行相對與絕對(固定)切換，【$】表示為絕對(固定) 公式【縱向】複製考量【列】是否固定 公式【橫向】複製考量【欄】是否固定 </details> ### 2. 常用函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/ry_-tFaL1x.png">![](https://hackmd.io/_uploads/ry_-tFaL1x.png)</a> </details> ### 3. 處理小數位數之函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶目的：處理小數位數之函數 <a href="https://hackmd.io/_uploads/B1TO2FTUye.png">![](https://hackmd.io/_uploads/B1TO2FTUye.png)</a> </details> ### 4. 乘積計算函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/BJ1QTYaLJg.png) </details> ## B. 【人員銷售獎金】練習 🔶 【範例】 <a href="https://hackmd.io/_uploads/B1cKfPE8T.png">![](https://hackmd.io/_uploads/B1cKfPE8T.png)</a> ### 1. 金額 ( 台幣 ) ( 欄位 I ) <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶 目的：了解公式中「相對與絕對」的考量時機 公式複製時才需考量 利用`F4`功能鍵，進行相對與絕對(固定)切換，【$】表示為絕對(固定) 公式【縱向】複製考量【列】是否固定 公式【橫向】複製考量【欄】是否固定 🔶 目的：處理小數位數之函數 <a href="https://hackmd.io/_uploads/B1TO2FTUye.png">![](https://hackmd.io/_uploads/B1TO2FTUye.png)</a> <a href="https://hackmd.io/_uploads/SJMQ0FUUp.png">![](https://hackmd.io/_uploads/SJMQ0FUUp.png)</a> ``` 在 I5 儲存格建立公式 = Round ( 數值 , 取捨小數位數 ) = Round ( 總金額(美金) * 匯率 , 0 ) = Round ( H5 * $I$2 , 0 ) ``` </details> ### 2. IF 函數進行條件判斷 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rJWiXlHVK.png) #### (1) 欄位K，目標達成與否 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/BJA48c8UT.png) ``` 在 K5 儲存格建立公式【銷售金額】>=【銷售目標】者標示"達成"字樣 = IF ( 判斷式 , 是 , 否 ) = IF ( 銷售金額>=銷售目標 , "達成" , "未達成" ） = IF ( I5 >= J5 , "達成" , "未達成" ） ``` </details> #### (2) 欄位L，台幣金額達標90%，提撥獎金【台幣金額】*【獎金倍率】 <details> <summary>【細項說明】點擊展開/摺疊</summary> ▶️ 階段一：判斷是否達標 90% 者，獎金提撥 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rkQ-AqIUa.png) ``` 在 L5 儲存格建立公式 = IF ( 判斷式 , 【是】的工作 , 【否】的工作 ) = IF ( (銷售金額/銷售目標)>=90% , 金額 (台幣)*獎金倍率(0.8) , 0 ） = IF ( I5/J5>=90% , I5*$L$2 , 0 ） ``` </details> ▶️ 階段二：獎金金額取至無條件捨去至百位數 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/S1xcAc8Ip.png) ``` 在 L5 儲存格建立公式 = RoundDown ( IF((銷售金額/銷售目標)>=90%,金額(台幣)*獎金倍率(0.8), 0 ） , -2 ) = RoundDown ( IF ( I5/J5>=90% , I5*$L$2 , 0 ） , -2 ) ``` </details> </details> </details> ### 4. 評語結果 ( 欄位 M ) <details> <summary>【細項說明】點擊展開/摺疊</summary> ``` 在 M5 儲存格建立公式陳蘭名科長，本季獎金 1800 元 = B5 & C5 & "，本季獎金" & L5 & "元" ``` </details> ### 5. 權重計算 ( 欄位 R ) <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/H1j0muPn0.png) ``` 在 R5 儲存格建立公式 =SumProduct ( 數列1 , 數列2 ) =SumProduct ( N5:Q5 , $N$2:$Q$2 ) ``` </details> ### 6. 排名 ( 欄位 S ) <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info = Rank ( 查詢值 , 查詢範圍 , 順序 ) 順序：指定排序的方式，　　　若省略或是填0：由大排到小，遞減　　　若填為1：由小排到大，遞增 <a href="https://hackmd.io/_uploads/B1YvPlS4F.png">![](https://hackmd.io/_uploads/B1YvPlS4F.png)</a> ::: ``` 在 S5 儲存格建立公式 =RANK ( 查詢值 , 查詢範圍 , 順序 ) =RANK ( R5 , $R$5:$R$24 , 0 ) ``` </details> ## C. 【成績單】練習 🔶 【範例】 <a href="https://hackmd.io/_uploads/Hk04j1SVF.png">![](https://hackmd.io/_uploads/Hk04j1SVF.png)</a> ### 1. 平均值 ( 欄位 I ) <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶 目的：處理小數位數之函數 <a href="https://hackmd.io/_uploads/B1TO2FTUye.png">![](https://hackmd.io/_uploads/B1TO2FTUye.png)</a> ``` 在 I5 儲存格建立公式 = Round ( 數值 , 取捨小數位數 ) = Round ( Average(E5:G5) , 1 ) ``` </details> ### 2. 加權分數 ( 欄位 J ) <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶 目的：了解公式中「相對與絕對」的考量時機 公式複製時才需考量 利用`F4`功能鍵，進行相對與絕對(固定)切換，【$】表示為絕對(固定) 公式【縱向】複製考量【列】是否固定 公式【橫向】複製考量【欄】是否固定 ``` 在 J5 儲存格建立公式加權分數 = 平均值＊加權值＋平均值 = Round( I5 * $J$2 + I5 , 1 ) ``` </details> ### 3. IF 函數進行條件判斷 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/rJWiXlHVK.png">![](https://hackmd.io/_uploads/rJWiXlHVK.png)</a> (1) 欄位K，>=70者使用加權分數 ``` 在 K5 儲存格建立公式 >=70者使用加權分數 = IF ( 判斷式 , 是 , 否 ) = IF ( 平均值 >= 加權分數 , 平均值 , 加權值） = IF ( I5 >= $L$2 , J5 , I5 ) ``` (2) 欄位L，合格與否 ``` 在 L5 儲存格建立公式 =IF(K5>=$L$2,"合格","不合格") ``` </details> ### 4. 評語結果 ( 欄位 M ) <details> <summary>【細項說明】點擊展開/摺疊</summary> ``` 在 M5 儲存格建立公式 = 85.5 & 分， & 合格 = K5 & "分，" & L5 ``` </details> ### 5. 權重計算 ( 欄位 R ) <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/H1j0muPn0.png">![](https://hackmd.io/_uploads/H1j0muPn0.png)</a> ``` 在 R5 儲存格建立公式 =SumProduct ( 數列1 , 數列2 ) =SumProduct ( N5:Q5 , $N$2:$Q$2 ) ``` </details> ### 6. 排名 ( 欄位 S ) <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info = Rank ( 查詢值 , 查詢範圍 , 順序 ) 順序：指定排序的方式，　　　若省略或是填0：由大排到小，遞減　　　若填為1：由小排到大，遞增 <a href="https://hackmd.io/_uploads/B1YvPlS4F.png">![](https://hackmd.io/_uploads/B1YvPlS4F.png)</a> ::: ``` 在 S5 儲存格建立公式 =RANK ( 查詢值 , 查詢範圍 , 順序 ) =RANK ( R5 , $R$5:$R$24 , 0 ) ``` </details> ### 7. 繳交日期(O欄) S1：在其他儲存格輸入O5的公式 ``` =IF(O5="",NOW(),O5) ``` 公式輸入思考原則：繳交日期O5顯示當下時間，否則顯示空白 <a href="https://hackmd.io/_uploads/S1VKgP6Sc.png">![](https://hackmd.io/_uploads/S1VKgP6Sc.png)</a> S2：將S1公式在編輯公式下複製其內公式文字，則入O5儲存格，修改公式 ``` =IF(N5="","",IF(O5="",NOW(),O5)) ``` 公式輸入思考原則：當N5輸入已繳交班費(V)，繳交日期O5顯示當下時間，否則顯示空白 <a href="https://hackmd.io/_uploads/B1BHVvar5.png">![](https://hackmd.io/_uploads/B1BHVvar5.png)</a> S3：在進行S2公式建立時，會造成公式循環問題時，利用【檔案】>【選項】(設定如下圖) [參考資訊：移除或允許循環參照，並了解反覆運算用意](https://support.microsoft.com/zh-tw/office/%E7%A7%BB%E9%99%A4%E6%88%96%E5%85%81%E8%A8%B1%E5%BE%AA%E7%92%B0%E5%8F%83%E7%85%A7-8540bd0f-6e97-4483-bcf7-1b49cd50d123) <a href="https://hackmd.io/_uploads/HkciSwpHc.png">![](https://hackmd.io/_uploads/HkciSwpHc.png)</a> ## D. 設定格式化條件 設定格式化的條件：增加各種色彩提昇趣味性及圖示有效地呈現視覺性的簡易分析效果。 指令：【常用】>【設定格式化條件】 🔶 範例目的：合格者設定提醒色彩 <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：選取A5:M24 S2：【常用】>【設定格式化條件】>【新增規則】>【用公式來決定…】 S3： 公式思考邏輯：當個人分數 >= 標準分數時，標示色彩 ![](https://hackmd.io/_uploads/HktmherVK.png) </details> 🔶 範例目的：隔列變色 <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：選取A5:M24 S2：【常用】>【設定格式化條件】>【新增規則】>【用公式來決定…】 S3： :::info 📌 利用MOD函數，將奇、偶值，變更為1與0 公式思考邏輯：奇數值(1、3、5、7)除以2，其餘數值為1，相反的偶數值餘數為0 ``` = MOD( ROW ( ) , 2 ) = 1 ``` ![](https://hackmd.io/_uploads/ryIgc4SVK.png) ::: ![](https://hackmd.io/_uploads/SyyexSr4K.png) </details> # 05. 龐大資料工作處理建議 ## A. 善用看得懂的文字公式 ### 1. 名稱定義讓公式更容易檢查 <a href="https://hackmd.io/_uploads/HJeIbBHEt.png">![](https://hackmd.io/_uploads/HJeIbBHEt.png)</a> ### 2. 表格化 <details> <summary>【細項說明】點擊展開/摺疊</summary> 可以快速建立、格式化及展開 Excel 表格，組織工作表上的資料，讓其更易於使用。 (1) 指令介紹 📌 建立 <a href="https://hackmd.io/_uploads/BJko6I7NK.png">![](https://hackmd.io/_uploads/BJko6I7NK.png)</a> 📌 表格化指令 <a href="https://hackmd.io/_uploads/Synd8KY4Y.png">![](https://hackmd.io/_uploads/Synd8KY4Y.png)</a> (2) 表格化選取方法 📌 左鍵一下選取內容、左鍵一下+一下選取內容及標題 ![](https://hackmd.io/_uploads/HkW-xKY4t.png) (3) 表格化後，公式表示結構 =表格名稱〔＠欄位名稱〕 有＠：相對性欄位 沒有＠：整欄計算 (4) 交叉分析篩選器 交叉分析篩選器為一視覺化控制項，以互動且直覺的方式，快速篩選僅顯示所需的資料的資料。 <a href="https://hackmd.io/_uploads/ryV6FFKVt.png">![](https://hackmd.io/_uploads/ryV6FFKVt.png) </a> (5) 注意事項 🔶 移除表格化前，先確認是否要移除色彩 為了取得帶狀列的色彩，直接移除表格化 因反悔表格化功能，而表格會動用排序時，必須要移除色彩 🔶 移除方法 ![](https://hackmd.io/_uploads/ryMkItYEt.png) </details> ## B. 解決總和遇上篩選困擾 ### 1. 小計函數：Subtotal <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/BJePGHrNK.png">![](https://hackmd.io/_uploads/BJePGHrNK.png)</a> </details> ### 2. 會除錯的小計函數：Aggregate <details> <summary>【細項說明】點擊展開/摺疊</summary> Aggregate函數比Subtotal函數還要強大，因面對忽略錯誤值、隱藏行和分類匯總嵌套，滿足Subtotal無法處理，但Aggregate滿足各種情況下的統計需求！ <a href="https://hackmd.io/_uploads/SkycGSSNY.png">![](https://hackmd.io/_uploads/SkycGSSNY.png)</a><a href="https://hackmd.io/_uploads/Bk_r2nxvY.png">![](https://hackmd.io/_uploads/Bk_r2nxvY.png)</a> </details> ### 3. 課程範例 <details> <summary>【細項說明】點擊展開/摺疊</summary> 【說明Subtotal函數】工作表，範例計算結果，如下圖： <a href="https://hackmd.io/_uploads/HJkJNrHVK.png">![](https://hackmd.io/_uploads/HJkJNrHVK.png)</a> </details> ## C. 資料驗證 <details> <summary>【細項說明】點擊展開/摺疊</summary> 1. 說明 設定允許使用者輸入無效資料，防止使用者輸入無效的資料，並可發出警告修正錯誤填入 ![](https://hackmd.io/_uploads/HJZUHtrNt.png) 2. Indirect函數 ``` = Indirect ( 定義名稱 ) ``` 3. 課程範例 <a href="https://hackmd.io/_uploads/HJZOrBS4F.png">![](https://hackmd.io/_uploads/HJZOrBS4F.png)</a> </details> ## D. 排序與小計 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info ※【格式化為表格】無法使用小計功能，利用【轉換為範圍】移除格式化為表格前，應確認顏色是否移除 ::: S1：小計之前先排序 指令：【資料】>【排序】 S2：先排序的欄位，先執行小計工作 指令：【資料】>【小計】 S2-1：第一階層排序欄位【經銷商】 <a href="https://hackmd.io/_uploads/r1DdTvgPK.png">![](https://hackmd.io/_uploads/r1DdTvgPK.png)</a> S2-2：第二階層排序欄位【商品名稱】 <a href="https://hackmd.io/_uploads/S1eh6wgwF.png">![](https://hackmd.io/_uploads/S1eh6wgwF.png)</a> </details> ## E. 進階篩選 <details> <summary>【細項說明】點擊展開/摺疊</summary> ::: danger 進階篩選原則： (1)準則條件應包含資料來源欄位名稱 (2)準則條件同列為且，不同列為或 (3)準則條件若為公式，則準則條件欄位名稱不可與現有欄位相同 ::: 指令：【資料】>【進階...】 <a href="https://hackmd.io/_uploads/rJzlCPevY.png">![](https://hackmd.io/_uploads/rJzlCPevY.png)</a> </details> ## F. 數據整理前置作業 ### 0. 注意事項 * 樞紐分析時，不要無謂的空格 * 樞紐分析報表呈現不靈活時，建議改為運用CountIF函數較為彈性 ### 1. 取得數據無法計算的困擾 <details> <summary>【細項說明】點擊展開/摺疊</summary> * 善用【資料剖析】進行轉換 * 指令：【資料】>【資料剖析】 <a href="https://hackmd.io/_uploads/HkagXxfEt.png">![](https://hackmd.io/_uploads/HkagXxfEt.png)</a> </details> ### 2. 符合條件計算 <details> <summary>【細項說明】點擊展開/摺疊</summary> * 樞紐分析報表呈現不靈活時，建議改為您原先運用CountIF函數較為彈性 📌 樞紐分析報表呈現不靈活時，建議改為您原先運用CountIF函數較為彈性 註：課程範例中，先行針對【職員薪資明細】的表格化及名稱定義，在『總表』工作表中，依各職務進行計算 符合條件計算函數：Countif、Sumif、CountifS、SumifS、Averageif、AverageifS ![](https://hackmd.io/_uploads/ry1YgrRUJx.png) </details> ### 3. 唯一值列表方法 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 方法1：利用移除重複項指令 缺點：原名單異動，此功能不會更新指令：【資料】>【移除重複項】 <a href="https://hackmd.io/_uploads/SkYwAxwS5.png">![](https://hackmd.io/_uploads/SkYwAxwS5.png)</a> (2) 方法2：利用色彩進行數據比對 指令：【常用】>【條件式格式設定】>【醒目提示儲存格規則】>【重複的值...】 <a href="https://hackmd.io/_uploads/Sy6kkbwH5.png">![](https://hackmd.io/_uploads/Sy6kkbwH5.png)</a> (3) 方法3：Unique函數 Notes：2019版本後新功能 :::info UNIQUE 函數：目的：傳回範圍中唯一值清單公式：= UNIQUE ( 1️⃣ 比對範圍, 2️⃣ 如何進行比較 , 3️⃣ 出現次數 ) 　2️⃣ 如何進行比較： 　　True：傳回唯一欄 　　False (可省略)：傳回唯一列 　3️⃣ 如何進行比較： 　　True：傳回只顯示一次的項目，顯示『只出現一次』者 　　False (可省略)：傳回每個相異的項目，只顯示一次的項目， 　　　　　　　　　顯示『不重覆』者 ::: </details> # 06. 員工年終考績統計 ## A. 課程函數 ### 1. 一維查表函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 依查詢值進行資料比對輸出相關資料之函數 :::success = Vlookup ( 查詢值 , 查表範圍 , 回傳指定欄數 , 選項) 說明 * 查詢值若為『文字』，選項為False 查詢值若為『數值』，選項通常為True，且查表範圍應遞增 * 查詢值應為查表範圍的第一欄 / 列 * 查表範圍若為『縱』向查詢，即為 Vlookup 函數 查表範圍若為『橫』向查詢，即為 Hlookup 函數 * 選項： * False，必須比對完合符合才回傳，查無資料以 “#N/A” 表示 True 或省略，比對回傳接近不超過查詢值的資料 * 查表函數，可搭配 Iferror 函數 ::: </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) <a href="https://hackmd.io/_uploads/B1RQCnr4K.png">![](https://hackmd.io/_uploads/B1RQCnr4K.png)</a> ### 1. 全勤獎金 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/BJ4nOqrVF.png) ``` 在『職員薪資明細』工作表中， I4 儲存格建立公式 =IF ( 判斷式 , 【是】的工作 , 【否】的工作 ) =IF ( [@請假時數]=0 , 2000 , 0 ) ``` </details> ### 2. 績效等級 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 利用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 在『職員薪資明細』工作表中， G4 儲存格建立公式 =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 在『職員薪資明細』工作表中， E4 儲存格建立公式 =IF ( 　　　判斷式　　, 【是】　,　　　　　　　【否】 　　　　　　 ) =IF ( [@績效評分]>=90 ,　"A"　, IF ( AND([@績效評分]>=80 , [@績效評分]<90 ) , "B" , "C" ) 　　 ) ::: </details> </details> (2) 利用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> ``` 在『職員薪資明細』工作表中， G4 儲存格建立公式 =Vlookup ( 查詢值 , 查詢範圍 , 回傳欄數 , 選項 ) =VLOOKUP ( [@員工號碼] , 基本資料 , 9 , FALSE ) ``` </details> ### 4. 績效獎金 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/r1jPpiHEK.png)<a href="https://hackmd.io/_uploads/BJu80jrNK.png">![](https://hackmd.io/_uploads/BJu80jrNK.png)</a> ``` 在『職員薪資明細』工作表中， L4 儲存格建立公式 =ROUNDDOWN( VLOOKUP([@績效等級],績效等級對照表!$B$3:$D$5,3,FALSE)*[@薪資], -2) ``` </details> ### 5. 薪資區間 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/SkkVqnBEK.png) ``` 在『職員薪資明細』工作表中， N4 儲存格建立公式 VLOOKUP ( [@薪資合計] , 區間標示 , 2 , TRUE ) ``` </details> ### 6. 查詢員工相關資料 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/r1dkR2r4F.png)<a href="https://hackmd.io/_uploads/SJ5tfTr4F.png)">![](https://hackmd.io/_uploads/SJ5tfTr4F.png)</a> </details> ## C. 課程範例(2) ### 1. 符合條件計算 <details> <summary>【細項說明】點擊展開/摺疊</summary> 註：課程範例中，先行針對【職員薪資明細】的表格化及名稱定義，在『總表』工作表中，依各職務進行計算 符合條件計算函數：Countif、Sumif、CountifS、SumifS、Averageif、AverageifS ![](https://hackmd.io/_uploads/ry1YgrRUJx.png) </details> ### 2. 年資 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/H1RhqCBVY.png) (1) 公式說明：計算兩日期間的天數、月數或年數 ``` = Datedif ( 開始日 , 結束日 , " 單位 " ) ※ 說明：單位：決定要傳回的單位，必須前後加上 " " Y代表滿幾年、M代表滿幾月、D代表滿幾日 MD代表兩日期間的天數差，忽略月和年 YM代表兩日期間的月數差，忽略日和年 YD代表兩日期間的天數差，忽略年要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位 ``` (2) 範例結果：年資計算 ``` = Datedif ( [@起聘日期] , TODAY() , "Y" ) ``` </details> ### 3. 性別 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 公式說明：文字、數值間轉換公式 <a href="https://hackmd.io/_uploads/rya6hRBEt.png">![](https://hackmd.io/_uploads/SkLiC0S4F.png)</a> (2) 公式說明：字串中抓取文字 ``` 依開始指定位置，傳回字元數的文字字串 = Mid ( 文字值 , 開始指定位置 , 欲回傳字元數) ``` (3) 範例結果：性別 <a href="https://hackmd.io/_uploads/BkDd1JLNF.png">![](https://hackmd.io/_uploads/BkDd1JLNF.png)</a> </details> ### 4. Text 延伸應用 <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶 星期(中) ![](https://hackmd.io/_uploads/BkGWMJ8Vt.png)🔶 星期(英) ![](https://hackmd.io/_uploads/ryg5GyLNY.png) </details> ## D. 課程範例2019函數應用 ### 1. 績效等級 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) Ifs函數 📌 函數介紹 :::info IFs 函數：目的：多條件判斷公式： = IFs ( 1️⃣ 判斷式1 , 2️⃣ 成立時顯示結果1 , ... ) ::: 📌 範例說明 ![](https://hackmd.io/_uploads/S1_3nFFZj.png) ``` =IFS([@績效評分]<80,"C",[@績效評分]<90,"B",TRUE,"A") ``` #### (2) Switch函數 📌 函數介紹 :::info Switch 函數：目的：多條件判斷公式： = Switch ( 1️⃣ 比對的值 , 2️⃣ 符合值1 , 3️⃣ 顯示結果1 , ... ) <a href="https://hackmd.io/_uploads/H1eH5mWtY.png">![](https://hackmd.io/_uploads/H1eH5mWtY.png)</a> ::: 📌 範例說明![](https://hackmd.io/_uploads/SJkQz9YZj.png) ``` 函數： = Switch ( 1️⃣ 比對的值 , 2️⃣ 符合值1 , 3️⃣ 顯示結果1 , … ) 修改： = Switch ( TRUE , [@績效評分]>=90 , "A" ,[@績效評分]>=80,"B","C") ``` </details> ### 2. 職務 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) xLOOKUP 📌 函數介紹 :::info =xLOOKUP(要找的值, 從哪裡找, 傳回什麼, [錯誤說明], [相符類型], [搜尋模式]) ::: 📌 範例說明 ``` 函數： =xLOOKUP( 要找的值 , 從哪裡找 , 傳回什麼 , [錯誤說明] , [相符類型] ) 修改： =xLOOKUP([@員工號碼] , 基本資料[員工號碼] , 基本資料[職務] , "查無" , 0 ) ``` </details> ## E. 樞紐分析介紹 樞紐分析(Pivot)：進行摘要、分析、探索，呈現資料來源的摘要表 ### 1. 指令原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【插入】>【樞紐分析表】 ![](https://hackmd.io/_uploads/BJqQp1LNK.png) </details> ### 2. 樞紐設定原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【插入】>【樞紐分析表】 ![](https://hackmd.io/_uploads/S1DOR1UNF.png) </details> ### 3. 選項 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rJicfx8Nt.png) </details> ### 4. 欄位排序 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 手動 欄位直接拖曳 (2) 自動 指令：【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目 ![](https://hackmd.io/_uploads/ryAbWYaU1e.png) </details> ### 5. 群組類型 <details> <summary>【細項說明】點擊展開/摺疊</summary> 文字 數值 日期 </details> ## F. 樞紐分析(1) 🔶 資料來源：員工薪資明細 <a href="https://hackmd.io/_uploads/BkrvE1UEt.png">![](https://hackmd.io/_uploads/BkrvE1UEt.png)</a> ### 1. 各職務/地區/績效等級之薪資情形 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/rkmESJUEt.png">![](https://hackmd.io/_uploads/rkmESJUEt.png) </a> </details> ### 2. 各職務之薪資統計 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 原始：樞紐分析設定欄位情形 <a href="https://hackmd.io/_uploads/HkSnSyIEY.png">![](https://hackmd.io/_uploads/HkSnSyIEY.png)</a> (2) 結果：樞紐分析【值的顯示方式】設定情形 <a href="https://hackmd.io/_uploads/HkzlIy84t.png">![](https://hackmd.io/_uploads/HkzlIy84t.png)</a> </details> ## G. 樞紐分析(2) 🔶 資料來源：音樂產品銷售 <a href="https://hackmd.io/_uploads/Hkx8ZHALyx.png">![](https://hackmd.io/_uploads/Hkx8ZHALyx.png)</a> ### 1. 開始：建立樞紐分析表 <details> <summary>【細項說明】點擊展開/摺疊</summary> 建立各銷售員、各產品商品銷售金額情形，討論其不同計算方式。 <a href="https://hackmd.io/_uploads/HJZlDJUNK.png">![](https://hackmd.io/_uploads/HJZlDJUNK.png)</a> </details> ### 2. 討論：【值的顯示方式】 <details> <summary>【細項說明】點擊展開/摺疊</summary> 針對【值的顯示方式】的不同計算方式，討論其不同計算方式， 其中【父項...】、【索引】不列入討論 <a href="https://hackmd.io/_uploads/SkqXdkUVY.png">![](https://hackmd.io/_uploads/SkqXdkUVY.png)</a> 3. 總計百分比 <a href="https://hackmd.io/_uploads/Bk4jOyUVt.png">![](https://hackmd.io/_uploads/Bk4jOyUVt.png)</a> 4. 欄總計百分比 <a href="https://hackmd.io/_uploads/BJ5kK1INY.png">![](https://hackmd.io/_uploads/BJ5kK1INY.png)</a> 5. 列總計百分比 <a href="https://hackmd.io/_uploads/B17zF1IVt.png">![](https://hackmd.io/_uploads/B17zF1IVt.png)</a> 6. 百分比... <a href="https://hackmd.io/_uploads/HJhVYkINK.png">![](https://hackmd.io/_uploads/HJhVYkINK.png)</a> <a href="https://hackmd.io/_uploads/H1SFY1LNt.png">![](https://hackmd.io/_uploads/H1SFY1LNt.png)</a> 7. 差異... <a href="https://hackmd.io/_uploads/ryw3YJIEY.png">![](https://hackmd.io/_uploads/ryw3YJIEY.png)</a> 8. 差異百分比 <a href="https://hackmd.io/_uploads/ryWeqkLVt.png">![](https://hackmd.io/_uploads/ryWeqkLVt.png)</a> 9. 計算加總至... <a href="https://hackmd.io/_uploads/B1TM9JL4F.png">![](https://hackmd.io/_uploads/B1TM9JL4F.png) </a> 10. 計算加總至百分比 <a href="https://hackmd.io/_uploads/rkgwc1L4Y.png">![](https://hackmd.io/_uploads/rkgwc1L4Y.png)</a> 11. 最大到最小的排列 <a href="https://hackmd.io/_uploads/BJJqcy84Y.png">![](https://hackmd.io/_uploads/BJJqcy84Y.png)</a> </details> # 07. 圖表 ## A. 基本操作說明 ### 1. 圖表原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/r1EUXBRUJx.png">![](https://hackmd.io/_uploads/r1EUXBRUJx.png)</a> </details> ### 2. 繪製圖表上手口訣 <details> <summary>【細項說明】點擊展開/摺疊</summary> 口訣1：白點在誰身上，對誰做美化 口訣2：找不到工具，針對白點處【按二下】 圖表原始指令：【圖表工具】>【格式】>【格式化選取範圍】 <a href="https://hackmd.io/_uploads/rJ6hPeLNt.png">![](https://hackmd.io/_uploads/rJ6hPeLNt.png)</a> </details> ### 3. 圖形圖注意事項 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/SJMzoxLNF.png">![](https://hackmd.io/_uploads/SJMzoxLNF.png)</a> </details> ## B. 善用【主副座標軸】 ### 1. 處理差異懸殊的資料 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/S1WdFlIVF.png">![](https://hackmd.io/_uploads/S1WdFlIVF.png)</a> </details> ### 2. 各項及總合值顯示技巧 🔶 範例結果 <a href="https://hackmd.io/_uploads/S1_c3l8NK.png">![](https://hackmd.io/_uploads/S1_c3l8NK.png)</a> <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：設定主副座標軸 <a href="https://hackmd.io/_uploads/Bkid6lUEK.png">![](https://hackmd.io/_uploads/Bkid6lUEK.png)</a> S2：針對合計列，快速左鍵二下，設定類別間距<a href="https://hackmd.io/_uploads/SyO1kW8Vt.png">![](https://hackmd.io/_uploads/SyO1kW8Vt.png)</a> </details> ### 3、顯示目標及當前完成情形 🔶 範例結果 <a href="https://hackmd.io/_uploads/Byjxxb84Y.png">![](https://hackmd.io/_uploads/Byjxxb84Y.png)</a> <details> <summary>【細項說明】點擊展開/摺疊</summary> 相關設定 <a href="https://hackmd.io/_uploads/Hy4DebI4F.png">![](https://hackmd.io/_uploads/Hy4DebI4F.png)</a> </details> ## C. 善用【圖表範本】 <details> <summary>【細項說明】點擊展開/摺疊</summary> 🔶 目的：相同圖表類型，進行重覆美化 S1：針對圖表按右鍵 > 另存為範本 <a href="https://hackmd.io/_uploads/rydPfWIEY.png">![](https://hackmd.io/_uploads/rydPfWIEY.png)</a> S2：針對想要套用範本的圖表 > 右鍵 > 【變更圖表類型】 ![](https://hackmd.io/_uploads/Hkv3QWIVt.png) </details> ## D. 善用【組合圖】+【輔助列】 <details> <summary>【細項說明】點擊展開/摺疊</summary> 往往既有的數列所製作的圖表都較為陽春，需要較多變化時會新增數列，將原有數列進行計算，以利後續圖表變化應用 🔶 範例：隨著數據變動，最大值與最小值自動改變位置 以商品銷售情形為例，適合使用各項產品銷售量上差異的表現，或逐月、逐年在時間上銷售量的變化，為了更突出數據，有時需要在圖表上特別強調最大值與最小值，並且隨著數據改變而自動變更位置。 ![](https://hackmd.io/_uploads/HkAIIW8Vt.png) S1：如圖所示，新增輔助列寫入公式找出最大值，點選儲存格D3後，輸入以下公式後，並向下複製。 ``` =IF($C3=MAX($C$3:$C$14),$C3,NA()) ``` 同理，新增找出最小值數列，點選儲存格D3後，以下公式。(以下最小值步驟相同，將不贅述) ``` =IF($C3=MIN($C$3:$C$14),$C3,NA()) ``` ![](https://hackmd.io/_uploads/r1EQ9-UNF.png) S2：選取B2至E14數據範圍後，新增為【群組直條圖】，接著切換至【所有圖表】索引標籤下，點選【組合式】(如圖所示)： 總金額選取【群組直條圖】 最大/最小值數列選取【含有資料標記的折線圖】 <a href="https://hackmd.io/_uploads/BkJ0cWU4F.png">![](https://hackmd.io/_uploads/BkJ0cWU4F.png)</a> S3：點選總金額直條圖數列後，按【右鍵】>【資料數列格式】>如圖所示，利用【類別間距】拉桿，調整直條圖數列的寬度 <a href="https://hackmd.io/_uploads/ry8Hi-L4K.png">![](https://hackmd.io/_uploads/ry8Hi-L4K.png)</a> S4：點選最大值數列後，【右鍵】>【新增資料標籤】>【新增資料圖說文字】次選項指令。 <a href="https://hackmd.io/_uploads/B1DOob8NF.png">![](https://hackmd.io/_uploads/B1DOob8NF.png)</a> S5：調整圖說文字 點選最大值數列>【右鍵】>【變更資料標籤圖案】 如圖所示，顯示【數列名稱】，標籤位置【上】 <a href="https://hackmd.io/_uploads/BkkajbI4Y.png">![](https://hackmd.io/_uploads/BkkajbI4Y.png) </a> </details> # 08. 樞紐分析 樞紐分析(Pivot)：進行摘要、分析、探索，呈現資料來源的摘要表 ## A. 指令原則 ### 1. 指令原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【插入】>【樞紐分析表】 ![](https://hackmd.io/_uploads/BJqQp1LNK.png) </details> ### 2. 樞紐設定原則 <details> <summary>【細項說明】點擊展開/摺疊</summary> 指令：【插入】>【樞紐分析表】 ![](https://hackmd.io/_uploads/S1DOR1UNF.png) </details> ### 3. 選項 <details> <summary>【細項說明】點擊展開/摺疊</summary> ![](https://hackmd.io/_uploads/rJicfx8Nt.png) </details> ### 4. 欄位排序 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 手動 欄位直接拖曳 (2) 自動 指令：【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目 ![](https://hackmd.io/_uploads/SkhtieMNY.png) </details> ### 5. 群組類型 <details> <summary>【細項說明】點擊展開/摺疊</summary> 文字 數值 日期 </details> ## B. 課程範例 🔶 資料來源：商品銷售明細 <a href="https://hackmd.io/_uploads/S1t6mlIEt.png">![](https://hackmd.io/_uploads/S1t6mlIEt.png)</a> ### 1. 【文字】群組 <details> <summary>【細項說明】點擊展開/摺疊</summary> 各經銷商、各地區之商品銷售金額情形 <a href="https://hackmd.io/_uploads/SJ-7NxIEK.png">![](https://hackmd.io/_uploads/SJ-7NxIEK.png)</a> </details> ### 2. 【數值】群組 <details> <summary>【細項說明】點擊展開/摺疊</summary> 了解產品銷售數量頻率 <a href="https://hackmd.io/_uploads/HJfFNeIEK.png">![](https://hackmd.io/_uploads/HJfFNeIEK.png)</a> </details> ### 3. 【日期】群組 <details> <summary>【細項說明】點擊展開/摺疊</summary> 彙整各季/年/月產品銷售金額情形 <a href="https://hackmd.io/_uploads/r1RjElINt.png">![](https://hackmd.io/_uploads/r1RjElINt.png)</a> </details> # 09. 函數技巧應用 ## A. 善用特殊目標1 _填滿空格 <details> <summary>【Youtube說明】點擊展開/摺疊</summary> 【Youtube】 {%youtube wk6VB5Y2lhE %} </details> ## B. 善用特殊目標2 _每五筆表格拆分 <details> <summary>【Youtube說明】點擊展開/摺疊</summary> 【Youtube】 {%youtube 36N759w_6io %} </details> ## C. 每月各項收支報表 <a href="https://hackmd.io/_uploads/rJX66-UEt.png">![](https://hackmd.io/_uploads/rJX66-UEt.png)</a> ### 1. 函數介紹 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) Index函數 根據欄列號索引回傳查表範圍的值 ``` = Index ( 查表範圍 , 指定列數 , 指定欄數 ) ``` ![](https://hackmd.io/_uploads/BkEql5r4F.png) (2) Match函數 傳回搜尋項目於搜尋範圍中的相對位 ``` = Match ( 查詢值, 查表範圍 , 比對方式類型 ) 📌比對方式類型 1或省略：查詢範圍須『遞增』 0：查詢範圍不須事前排序，找到完全符合查詢值 -1：查詢範圍須『遞減』 ``` 🔶 公式比擬 ![](https://hackmd.io/_uploads/rk3dE5B4t.png) </details> ### 2. Youtube說明 <details> <summary>【細項說明】點擊展開/摺疊</summary> 【Youtube】 {%youtube YxT7UA6-DF8 %} </details> ## D. Excel批次貼入圖片方法 🔶 範例結果 ![](https://hackmd.io/_uploads/S1U_CZ8VK.png) <details> <summary>【細項說明】點擊展開/摺疊</summary> S1：複製圖片路徑，將所有照片置於同一資料夾中，並複製其路徑 ![](https://hackmd.io/_uploads/rJ3bJGU4Y.png) S2：語法說明複製下方法語法至Excel中，修改其內容 ``` <table><img src='圖片路徑資料夾\" & 檔名 & ".副檔名' width='50'></table> ``` 語法說明 ![](https://hackmd.io/_uploads/BJ8Lyz8VF.png) S3：貼入Excel調整情形參考 ![](https://hackmd.io/_uploads/HyhqyfLVY.png) S4:路徑結果貼入記事本中，將路徑結果貼入記事本中，將其Excel設定移除 ![](https://hackmd.io/_uploads/SJ_CyML4t.png) S5:再將記事本路徑貼回Excel中，再將記事本路徑貼回Excel中，調整圖片大小即可 </details> # 11. 課程公式彙整 ## O. Excel函數列表 :::danger [微軟官網連結：Excel 函數 (依英文字母順序排列)](https://support.microsoft.com/zh-tw/office/excel-%E5%87%BD%E6%95%B8-%E4%BE%9D%E8%8B%B1%E6%96%87%E5%AD%97%E6%AF%8D%E9%A0%86%E5%BA%8F%E6%8E%92%E5%88%97-b3944572-255d-4efb-bb96-c6d90033e188#bm6) ::: ## A. 公式建立開始 ### 1. 公式基本結構 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 公式運算式元素 |類型|說明| | -------- | -------------- | |數字常數|固定值，如數字、"日期"、"時間"... "2021/01/1"、"12:30"| |文字|固定文字(前後加上雙引號)| | 參照位址 | 儲存格位址計算 = B2 * C2 跨工作表或檔案計算= [ 檔案名稱 ] 工作表名稱 ! 參照位置 | |函數語法 | = 函數名稱 ( 條件1 , 條件2 , ... )| (2) 公式可用運算符號 | 符號 | 說明 | |-|-| |( ) |括號最內層括號公式先運算| |+-(正負號)、%、^(指數)、 *(乘)、/(除)、+(加)、-(減)|一般運算符號| |& 例： = "A"+"B" → 結果為 AB| 連結文字| |=、<>、<、>、>=、<= 例： =5>3 結果為 TRUE =5<3 結果為 FALSE |大小相比較| (3) 常用錯誤值類型 |顯示結果|說明| |-|-| |#DIV/0!|表示除式之分母為0} |#N/A|表示計算公式中有無效值，暫不計算此值。| |#VALUE!|表示公式中的運算式的類型錯誤時，#VALUE!錯誤值出現| |#NUM!|輸入數值有問題。 如：要求出現正數處卻出現負數或數值超出範圍| |#NAME|公式內有無法辨識之名稱或函數時。| |#NULL!|當指定二個範圍相交並無交集時。| |###|輸入內容，因儲存格寬度不足，其無法顯示結果| (4) 公式中「相對與絕對(固定)」的考量時機 公式複製時才需考量 利用`F4`功能鍵，進行相對與絕對(固定)切換，【$】表示為絕對(固定) 公式【縱向】複製考量【列】是否固定 公式【橫向】複製考量【欄】是否固定 </details> ## B. 常用函數 <a href="https://hackmd.io/_uploads/ry_-tFaL1x.png">![](https://hackmd.io/_uploads/ry_-tFaL1x.png)</a> ## C. 處理小數位數之函數 🔶目的：處理小數位數之函數 <a href="https://hackmd.io/_uploads/B1TO2FTUye.png">![](https://hackmd.io/_uploads/B1TO2FTUye.png)</a> ## D. IF 函數進行條件判斷 ![](https://hackmd.io/_uploads/rJWiXlHVK.png) ## E. 乘積計算函數 ![](https://hackmd.io/_uploads/BJ1QTYaLJg.png) ## F. 解決總和遇上篩選困擾 ### 1. 小計函數：Subtotal <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/BJePGHrNK.png">![](https://hackmd.io/_uploads/BJePGHrNK.png)</a> </details> ### 2. 會除錯的小計函數：Aggregate <details> <summary>【細項說明】點擊展開/摺疊</summary> Aggregate函數比Subtotal函數還要強大，因面對忽略錯誤值、隱藏行和分類匯總嵌套，滿足Subtotal無法處理，但Aggregate滿足各種情況下的統計需求！ <a href="https://hackmd.io/_uploads/SkycGSSNY.png">![](https://hackmd.io/_uploads/SkycGSSNY.png)</a><a href="https://hackmd.io/_uploads/Bk_r2nxvY.png">![](https://hackmd.io/_uploads/Bk_r2nxvY.png)</a> </details> ## G. Indirect函數 ``` = Indirect ( 定義名稱 ) ``` ## H. 一維查表函數 ### 1. Vlookup函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 依查詢值進行資料比對輸出相關資料之函數 :::success = Vlookup ( 查詢值 , 查表範圍 , 回傳指定欄數 , 選項) 說明 * 查詢值若為『文字』，選項為False 查詢值若為『數值』，選項通常為True，且查表範圍應遞增 * 查詢值應為查表範圍的第一欄 / 列 * 查表範圍若為『縱』向查詢，即為 Vlookup 函數 查表範圍若為『橫』向查詢，即為 Hlookup 函數 * 選項： * False，必須比對完合符合才回傳，查無資料以 “#N/A” 表示 True 或省略，比對回傳接近不超過查詢值的資料 * 查表函數，可搭配 Iferror 函數 ::: </details> ### 2. Iferror函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> 若公式計算結果發生錯誤時，想要執行工作 :::success = Iferror ( 判斷運算公式 , 發生錯誤欲執行工作) 評估的錯誤類型包括： #N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 和 #NULL!。 ::: </details> ## I. 二維查表函數 <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> ## J. 符合條件計算 <details> <summary>【細項說明】點擊展開/摺疊</summary> 符合條件計算函數：Countif、Sumif、CountifS、SumifS、Averageif、AverageifS ![](https://hackmd.io/_uploads/ry1YgrRUJx.png) </details> ## K. 日期函數列表 ### 1. 傳回電腦系統日期及時間 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 傳回電腦日期及時間 ``` =Now( ) ``` (2) 傳回電腦日期 ``` =Today( ) ``` </details> ### 2. 常用日期類函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 傳回日期的年 / 月 / 日值 ``` = Year ( 日期 ) = Month( 日期 ) = Day ( 日期 ) ``` (2) 組合年、月、日值為日期 ``` = Date ( 年 , 月 , 日 ) ※ 最終抓取值為數值 ``` (3) 計算兩日期間的天數、月數或年數 ``` = Datedif ( 開始日 , 結束日 , " 單位 " ) ※ 說明：單位：決定要傳回的單位，必須前後加上 " " Y代表滿幾年、M代表滿幾月、D代表滿幾日 MD代表兩日期間的天數差，忽略月和年 YM代表兩日期間的月數差，忽略日和年 YD代表兩日期間的天數差，忽略年要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位 ``` (4) 計算前後月的某一天 ``` = EDATE (開始日期,月數) ``` (5) 計算當月的月底日期 ``` = EOMONTH (開始日期,月數) ``` (6) 傳回自指定工作天數的日期 ``` = Workday ( 開始日期 , 日數 , 假日列表 ) ※ 延伸學習函數 Workday.intl ``` (7) 傳回兩日期間的工作天數 ``` = Networkdays ( 開始日期 , 結束日期 , 假日列表 ) ※ 延伸學習函數 Networkdays.intl ``` (8) Workday.intl 及 Networkdays.intl 注意事項以Workday.intl函數為例： ``` = Workday.intl ( 開始日期 , 日數, 週末代號 , 假日列表 ) ``` 其中，週末代號： 　a. 若非為預設代號，其字串值為七個字元， 　　且字串代表一週內的一天，從星期一開始。 　b. 1：代表非工作日 / 0：代表工作日。 　c. 字串中僅允許字元 1 / 0，1111111 是無效字串。 ※ 例如，0000011 代表週末為星期六和星期日。 </details> ## L. 文字函數 ### 1. 函數列表 <details> <summary>【細項說明】點擊展開/摺疊</summary> <a href="https://hackmd.io/_uploads/rya6hRBEt.png">![](https://hackmd.io/_uploads/rya6hRBEt.png)</a> </details> ### 2. Text 日期結合 <details> <summary>【細項說明】點擊展開/摺疊</summary> (1) 日期代碼 <a href="https://hackmd.io/_uploads/Hy5GuFTL1l.png">![](https://hackmd.io/_uploads/Hy5GuFTL1l.png)</a> (2) 星期 🔶 星期(中) ![](https://hackmd.io/_uploads/BkGWMJ8Vt.png)🔶 星期(英) ![](https://hackmd.io/_uploads/ryg5GyLNY.png) (3) 季度 ``` = Int ((MONTH(日期)+2)/3) = RoundUP(MONTH(日期)/3,0) = Len(2 ^ MONTH(日期)) ``` ![](https://hackmd.io/_uploads/B1-ye2UWs.png) </details> # 12. 2019版本以後新函數 ## 1. Excel函數列表 <details> <summary>【細項說明】點擊展開/摺疊</summary> ::: 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) ::: </details> ## 2. Excel版本差異問題 ### (1) #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： <a href="https://hackmd.io/_uploads/SynFEebDt.png">![](https://hackmd.io/_uploads/SynFEebDt.png)</a> 發生情形2： <a href="https://hackmd.io/_uploads/H1PoExWwF.png">![](https://hackmd.io/_uploads/H1PoExWwF.png)</a> </details> ### (2) 公式前面顯示 _xlfn. <details> <summary>【細項說明】點擊展開/摺疊</summary> 📌 錯誤示意圖 <a href="https://hackmd.io/_uploads/rkQbBlZPt.png">![](https://hackmd.io/_uploads/rkQbBlZPt.png)</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) ::: </details> ### (3) 動態數組公式寫法 <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> ## 3. 文字串接函數：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) ### (1) Concat 函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info Concat 函數：目的：合併多範圍 / 字串，但不提供分隔符號公式：= Concat ( 1️⃣ 範圍1/文字1, 2️⃣ 範圍2/文字2 ... ) 備註： (1) CONCAT 會取代 CONCATENATE 函數。 (2) 合併文字間，需要加上分隔符號，可使用TextJoin ::: </details> ### (2) TextJoin 函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info TextJoin 函數：目的：合併多範圍 / 字串，但並提供分隔符號公式：= TextJoin ( 1️⃣ "分隔符號", 2️⃣ 是否忽略空白儲存格 , 3️⃣ 加入的文字項目/範圍 ) 　2️⃣ 是否忽略空白儲存格 　　 True：忽略空白儲存格 　　 Fasle：不忽略空白儲存格 ::: </details> ## 4. UNIQUE：傳回範圍中唯一值清單 <details> <summary>【細項說明】點擊展開/摺疊</summary> :::info UNIQUE 函數：目的：傳回範圍中唯一值清單公式：= UNIQUE ( 1️⃣ 比對範圍, 2️⃣ 如何進行比較 , 3️⃣ 出現次數 ) 　2️⃣ 如何進行比較： 　　True：傳回唯一欄 　　False (可省略)：傳回唯一列 　3️⃣ 如何進行比較： 　　True：傳回只顯示一次的項目，顯示『只出現一次』者 　　False (可省略)：傳回每個相異的項目，只顯示一次的項目， 　　　　　　　　　顯示『不重覆』者 ::: </details> ## 5. Xlookup函數 <details> <summary>【細項說明】點擊展開/摺疊</summary> >[color=red][微軟官網介紹：xLookup函數](https://support.microsoft.com/zh-tw/office/xlookup-%E5%87%BD%E6%95%B8-b7fd680e-6d10-43e6-84f9-88eae8bf5929) > :::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> #  <style> /* 加入Logo */ .toc:{position: relative;} .toc:before{ content: url(https://zoego.tech/img/logo.svg); width:200px; 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{ 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:1em; font-weight:bold; } .h4{color:#000099; font-size:1.2em;} .h5{color:#7030A0; font-size:1.2em;} /* 講師資訊美化 */ .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;} .markdown-body details code { background-color: transparent !important; } /* 快速鍵 > 語法於P中 */ .markdown-body p>code { color:#BF1363 !important; border:none; background:none} /* details 摺疊設定 */ .markdown-body details { background-color: transparent !important; } .markdown-body details summary { color:#7d7d7d; font-size:1.2rem; 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;} .red{color:#BF1363;font-weight:bold;} .blue{color:#265F92;font-weight:bold;} .step{ padding-left:1.5em;color:blue;font-size:1.1em; } .textc{text-align:center; display:block;} .markdown-body p{ margin-bottom: 0;} /* .flex{ display:flex; border:2px solid #000; padding:20px; margin:20px; } */ .ex{color:#333399 ; font-size:1em; font-weight:bold;} /* 置中、圓角框、項目 */ .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; } .purple{ color:#5E00BC; display:block;font-weight:bold; font-size:1.1em } </style>