###### tags: `Excel` # Excel 講義 <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; } .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;} /* 快速鍵 > 語法於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;} .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: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; } .purple{ color:#5E00BC; display:block;font-weight:bold; font-size:1.1em } </style> :::warning <span class="textc" style="font-weight:bold; font-size:1.2em">課程講師資訊</span>巨匠電腦講師:呂心怡 E-mail:zoego99@gmail.com <span class="urll">[課程練習下載區:https://bit.ly/346jldq](https://bit.ly/346jldq) </span> ::: # 一、課程講義 # 00、開始 ## <span class="h2">A、Excel可以做什麼?</span> ### 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 軟體結構 ![](https://hackmd.io/_uploads/BkapVKSBt.png) ## <span class="h2">B、選取方式</span> <span class="step" style="color:#7030A0">討論方向:</span> <span class="step" style="color:#7030A0">1.Excel 界面結構為【儲存格】、【列】、【欄】分別討論選取方式。</span> <span class="step" style="color:#7030A0">2.選取時,配合快速鍵`Ctrl`與`Shift`鍵。</span> ### 1. `Shift` 連續選取 <span class="img90">![](https://hackmd.io/_uploads/SyE6n5xEK.png)</span> ### 2. `Ctrl` 挑選選取 <span style="color:#333399 ; font-size:1.1em; font-weight:bold; line-height:2em;margin-left:60px;">🔶 第二個範圍以後<span style="color:red; font-size:1.5em">才</span>按住`Ctrl`鍵</span> <span style="color:#333399 ; font-size:1.1em; font-weight:bold; line-height:2em;margin-left:60px;">🔶 移動過程 + `Ctrl` = 複製</span> ### 3. 快速大範圍選取 <span class="img80">![](https://hackmd.io/_uploads/Bkgt0qlEK.png)</span> ## <span class="h2">E、剪貼簿功能</span> ### 1、 <span class="img3">![](https://hackmd.io/_uploads/ByIAqfnXt.png)</span>剪下、<span class="img3">![](https://hackmd.io/_uploads/H1tBsf2mF.png)</span>複製、<span class="img3">![](https://hackmd.io/_uploads/rkUwhGnQF.png)</span>貼上 <span class="img60">![](https://hackmd.io/_uploads/H1J52M2XK.png)</span> ### 2、<span class="img3">![](https://hackmd.io/_uploads/rkpsAfnQt.png)</span>複製格式 <span class="img50">![](https://hackmd.io/_uploads/rJ9GRf3Qt.png)</span> ### 3、相同內容同時輸入方法 <span class="step">S1:選取想要同時輸入相同內容的範圍</span> <span class="step">S2:<span class="red">不要管滑鼠位置</span>,直接輸入</span> <span class="step">S3:輸入完成,執行`Ctrl`<span style="font-size:.8em">(複製)</span> + `Enter`<span style="font-size:.8em">(確定)</span></span> ## <span class="h2">D、 必要性快速鍵</span> |項目A|快速鍵A|項目B|快速鍵B| |-|-|-|-| |儲存格內強制換行|`Alt`+`Enter` <br> 公式內自動換行=Char(10)|多範圍內容輸入|`Ctrl`+`Enter`| |自動累加1|儲存格右下角控點拖曳時+`Ctrl`|選取非隱藏欄列|`Alt`+`;`| |拖曳移動範圍|資料欄列拖曳時+`Shift`|自動加總|`Alt`+`=`| |複製後選取範圍貼入|`Enter`|表格化|`Ctrl`+`T`| |執行重複指令|`F4`|快速填入(2013)|`Ctrl`+`E`| |儲存格格式設定|`Ctrl`+`1`|顯示公式計算結果|`F9`<br>點選資料編輯列的【fx】| <!-- 簡單版快速鍵 | 項目| 快速鍵 | | - | - | |儲存格內強制換行|【Alt】+【Enter】| |選取非隱藏欄列|【Alt】+【;】| |複製後選取範圍貼入|【Enter】| |多範圍內容輸入|【Ctrl】+【Enter】| |儲存格格式設定|【Ctrl】+【1】|--> # 01、Excel基本操作 ## <span class="h2">A、報表經常性的處理</span> ### 1、報表基本操作 #### <span class="h4">(1) 報表標題置中</span> <span class="step">S1:選取目的範圍</span> <span class="step">S2:常用 > 【對齊方式】工作群組 > 【跨欄置中】</span> <span class="img40">![](https://hackmd.io/_uploads/S16-X2gNK.png)</span> #### <span class="h4">(2) 欄寬 / 列高調整</span> <span class="step">S1:選取【<span style="color:#C00000; font-size:1.3em">欄</span>】</span> <span class="step">S2:在欄名之間<span class="img30" style="display:inline-block">![](https://hackmd.io/_uploads/SJtqthl4F.png)</span></span> <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:60px;">(a) <span class="img5">![](https://hackmd.io/_uploads/SkE5qnxVY.png)</span>,左右移動:一致性欄寬</span> <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:60px;">(b) <span class="img5">![](https://hackmd.io/_uploads/SkE5qnxVY.png)</span>,左鍵2下:最適欄寬</span> #### <span class="h4">(3) 強制換行</span> <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">🔶 Excel:`Alt` + `Enter`</span> <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">🔶 Word、PPT:`Shift` + `Enter`</span> #### <span class="h4">(4) 資料列移動</span> <span class="step">指令:右鍵>【插入剪下的儲存格】 或 快速鍵:資料欄列拖曳時+`Shift`</span> <a href="https://hackmd.io/_uploads/BkYo1pl4t.png"><span class="img100">![](https://hackmd.io/_uploads/BkYo1pl4t.png)</span></a> ### 2、螢幕控制小操作 #### <span class="h4">(1) 利用隱藏列,將報表標題與表格分隔</span> #### <span class="h4">(2) 凍結 / 分割窗格</span> <a href="https://hackmd.io/_uploads/B1ynmAeNY.png"><span class="img100"> ![](https://hackmd.io/_uploads/B1ynmAeNY.png)</span></a> #### <span class="h4">(3) 自訂檢視模式</span> <span class="img60" style="border:1px solid #000">![](https://hackmd.io/_uploads/HylznvTB9.png)</span> #### <span class="h4">(4) 大綱(組成群組)</span> <span class="step">指令:【資料】>【組成群組】/【自動建立大綱】</span> <a href="https://hackmd.io/_uploads/SJZoswpS9.png"><span class="img100">![](https://hackmd.io/_uploads/SJZoswpS9.png)</span></a> <span class="img40" style="border:1px solid #000">![](https://hackmd.io/_uploads/rJ3AeWGEF.png)</span> #### <span class="h4">(5) 善用前導/從屬參照</span> <a href="https://hackmd.io/_uploads/By5-bbzVK.png"><span class="img100">![](https://hackmd.io/_uploads/By5-bbzVK.png)</span></a> ### 3、框線繪製 #### <span class="h4">(1) 基本原則</span> <span></span> <span class="flex" style="border:none;margin:0; padding:0"> <span><span class="step">S1:【常用】><span class="img7">![](https://hackmd.io/_uploads/H1H8Dtb4Y.png)</span></span> <span class="step">S2:決定框線色彩及樣式 </span> <span class="step">S3:手繪框線</span> <span class="step" style="padding-left:3em">繪製框線:<span style="color:red;padding-right:.5em;font-weight:bold">單線</span>框線繪製</span> <span class="step" style="padding-left:3em">繪製框線格線:<span style="color:red;padding-right:.5em;font-weight:bold">大範圍</span>框線繪製</span></span> <span class="img40" style="margin:0 auto;">![](https://hackmd.io/_uploads/rJZ7bAWEY.png) </span> </span> #### <span class="h4">(2) 表格左上角繪製</span> <a href="https://hackmd.io/_uploads/SkDoO0WVF.png"><span class="img100">![](https://hackmd.io/_uploads/SkDoO0WVF.png)</span></a> ### 4、表格化 <span class="step">可以快速建立、格式化及展開 Excel 表格,組織工作表上的資料,讓其更易於使用。</span> #### <span class="h4">(A) 指令介紹</span> <span class="step">📌 建立</span> <a href="https://hackmd.io/_uploads/BJko6I7NK.png"><span class="img70">![](https://hackmd.io/_uploads/BJko6I7NK.png)</span></a> <span class="step">📌 表格化指令</span> <a href="https://hackmd.io/_uploads/Synd8KY4Y.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/Synd8KY4Y.png)</span></a> #### <span class="h4">(B) 表格化選取方法</span> <span class="step">📌 左鍵一下選取內容、連二下選取內容及標題</span> <span class="img70">![](https://hackmd.io/_uploads/HkW-xKY4t.png)</span> #### <span class="h4">(&#067;) 表格化後,公式表示結構</span> <span class="red" style="font-size:1.5em; text-align:center;display:block; border:1px solid; border-radius:5px; width:60%; margin:auto; background:#FEF7DB;line-height:2.1em">=表格名稱〔@欄位名稱〕</span> <span style="color:blue;font-size:1.2em;padding-left:2em;line-height:2em"><i class="fa fa-hand-o-right"></i> 有@:相對性欄位</span> <span style="color:blue;font-size:1.2em;padding-left:2em;line-height:2em"><i class="fa fa-hand-o-right"></i> 沒有@:整欄計算</span> #### <span class="h4">(D) 交叉分析篩選器</span> <span class="step">交叉分析篩選器為一視覺化控制項,以互動且直覺的方式,快速篩選僅顯示所需的資料的資料。</span> <a href="https://hackmd.io/_uploads/ryV6FFKVt.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/ryV6FFKVt.png) </span></a> #### <span class="h4">(E) 注意事項</span> <span style="color:#BF1363;font-size:1.2em;padding-left:2em;line-height:2em">🔶 移除表格化前,先確認是否要移除色彩</span> <span class="step" style="padding-left:5em"><i class="fa fa-hand-o-right"></i> 為了取得帶狀列的色彩,直接移除表格化</span> <span class="step" style="padding-left:5em"><i class="fa fa-hand-o-right"></i> 因反悔表格化功能,而<span class="red">表格會動用排序時</span>,<span class="red">必須</span>要移除色彩</span> <span style="color:#BF1363;font-size:1.2em;padding-left:2em;line-height:2em">🔶 移除方法</span> <span class="img40">![](https://hackmd.io/_uploads/ryMkItYEt.png)</span> ## <span class="h2">B、【控點填滿】及【智慧標籤】結合</span> ### 1、控點填滿<span></span> <span class="flex" style="line-height:2em; border:none;padding:10px; margin:10px;"><span style="flex:1"><span class="h4">(1) 「數列」填滿</span> <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">(a) 數值</span> <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">(b) 日期</span> <span style="color:blue ; font-size:1.1em;margin-left:30px;">(&#067;) 文字清單 (結合自訂清單) </span> <span class="h4">(2) 儲存格內容「複製」</span> <span class="h4">(3) 公式複製</span> </span> <span style="margin:0 auto; flex:1"><span class="img70">![](https://hackmd.io/_uploads/Skpn00ZVY.png) </span></span></span><span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">🔶 原始指令:【常用】>【編輯】工作群組>【填滿】功能</span> <span class="img30">![](https://hackmd.io/_uploads/Sk9YGyMVF.png)</span> ### 2、錯誤提醒設定 <span style="color:blue ; font-size:1.1em;margin-left:30px;">指令:【檔案】>【選項】</span> <a href="https://hackmd.io/_uploads/HylWE1f4K.png"><span class="img90">![](https://hackmd.io/_uploads/HylWE1f4K.png)</span></a> ### 3、日期輸入原則的重要性 <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 輸入結構【年/月/日】,定義為西元年份</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 日期以 `/` 或 `-` 區隔,時間以 `:` 區隔</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 今年年份輸入時可省略</span> <span class="step" style="line-height:2em;color:#C00000"><i class="fa fa-hand-o-right"></i> Win系統為台灣版本時,民國年份之日期前方加入`R`</span> <span class="step" style="padding-left:3em;line-height:2em">例`R30/3/5`,Excel自動轉換為1941/3/5。</span> ## <span class="h2">C、排序與自訂清單</span> ### 1、排序 <a href="https://hackmd.io/_uploads/HJXQKgf4F.png"><span class="img100">![](https://hackmd.io/_uploads/HJXQKgf4F.png)</span></a> ### 2、自訂清單 #### <span class="h4">(1) 目的</span> :::info ※資料排序與自訂清單結合 在排序完成後以使用者角度來看,例如:職務、考績或地區,有自行的排序順序,但電腦只能依筆劃或數值排序時,則需運用【自訂清單】要求順序。 ::: <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 填滿序列</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 資料排序</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 樞紐分析</span> #### <span class="h4">(2) 指令</span> <span class="step">【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目</span> <a href="https://hackmd.io/_uploads/SkhtieMNY.png"><span class="img90">![](https://hackmd.io/_uploads/SkhtieMNY.png)</span></a><a href="https://hackmd.io/_uploads/SJlZJMGEF.png"><span class="img80">![](https://hackmd.io/_uploads/SJlZJMGEF.png)</span></a> ## <span class="h2">D、快速選取【常規】大範圍</span> ### 1、特殊目標..介紹 <span class="step">善用【常用】>【尋找與選取】>【特殊目標】</span> <span class="img90">![](https://hackmd.io/_uploads/H1hvWzfEt.png)</span> <span style="text-align:right;display:block;">[線上銷售明細](https://zoego.tech/salelist.html)</span> ### 2、課程範例 <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">🔶【範例】</span><span class="img100">![](https://hackmd.io/_uploads/Bkr6XfGVt.png)</span> <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">🔶【步驟】</span> <span class="step" style="margin-left:2em;">練習1:空格向下填滿</span> <span class="step" style="margin-left:5em;">S1:選取數據範圍</span> <span class="step" style="margin-left:5em;">S2:【常用】>【尋找與選取】</span> <span class="step" style="margin-left:5em;">S3:【特殊目標】> 點選【空格】選項</span> <span class="step" style="margin-left:5em;line-height:2em">S4:輸入`=`,方向鍵`↑`</span> <span class="step" style="margin-left:5em;">S5:輸入完成`Ctrl`+`Enter`</span> <span class="step" style="margin-left:5em;color:red;font-weight:bold">S6:移除公式</span> <span class="step" style="margin-left:7em;color:red;font-weight:bold">全選範圍>【複製】>【選擇性貼上】>【值】123</span> <span class="step" style="margin-left:2em;">練習2:依篩選條件進行美化</span> <span style="color:red ; font-size:1.1em; line-height:2em;margin-left:7em;">快速鍵:【Alt】+【;】</span> ## <span class="h2">E、改善跨欄置中的不方便</span> <span class="img90">![](https://hackmd.io/_uploads/BkoNOIX4K.png)</span> # 02、管理工作表 ## <span class="h2">A、管理工作表</span> ### 1、指令位置 <span class="step">相關指令在工作表索引標籤下→右鍵→即可詳見相關指令</span> <span class="img40">![](https://hackmd.io/_uploads/rJGLgvQNF.png)</span> ### 2、注意事項 #### <span class="h4"> (1) 多張工作表同時進行設定時,善用`Ctrl`不連續選取、`Shift`連續選取工作表</span> #### <span class="h4">(2) 【工作群組】設定<span style="color:red">必須</span> 記得解除</span> <span class="step">解除方法:</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 點選未設定工作群組的工作表</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 已選取的工作表索引標籤→右鍵→取消工作群組設定</span> ## <span class="h2">D、 保護【工作表】</span> <span class="step">S1:解除不保護的儲存格</span> #### <span class="h4" style="padding-left:2em">🔶 方法1:儲存格鎖定</span> <span class="step" style="padding-left:3em">【儲存格格式】之下,</span> <span class="step" style="padding-left:5em">(1) 鎖定:在保護工作表之下,勾選鎖定,即保護儲存格不可修改</span> <span class="img80">![](https://hackmd.io/_uploads/SJqnsNrVt.png) </span> <span class="step" style="padding-left:5em">(2)隱藏:在保護工作表之下,勾選隱藏,即在資料編輯列下不顯示公式</span> #### <span class="h4" style="padding-left:2em">🔶 方法2:設定允許編輯範圍</span> <span class="step" style="padding-left:5em">指令:【校閱】>【允許編輯範圍】</span> <span class="img90">![](https://hackmd.io/_uploads/By1JaVrEK.png) </span> <span class="step">S2:保護工作表:【校閱】>【保護工作表】</span> <span class="img80">![](https://hackmd.io/_uploads/SJFJpNSVK.png) </span> ## <span class="h2">C、 保護【檔案】</span> <span class="step">【檔案】>【資訊】>【保護活頁簿】,其中包含檔案唯、密碼加密及顯示完稿無法編輯</span> <span class="img80">![](https://hackmd.io/_uploads/r1SDT4HNt.png) </span> ## <span class="h2">B、保護活頁簿及工作表</span> <span class="img40">![](https://hackmd.io/_uploads/SJZdQvXNF.png)</span> # 03、儲存格【值】顯示設定 ## <span class="h2">A、預設格式</span> <span class="img90">![](https://hackmd.io/_uploads/SyeNYFEVY.png)</span> ## <span class="h2">B、自訂格式</span> ### 1、您是否有這樣的困擾? #### <span class="h4"> (1) 日期如何對齊呢?</span> <span class="img90">![](https://hackmd.io/_uploads/Sy0XJcEEK.png)</span> #### <span class="h4"> (2) 特殊日期格式,如何設定呢?</span> <span class="img60">![](https://hackmd.io/_uploads/Skkae5NEK.png)</span> #### <span class="h4"> (3) 報表數值異常時,如何顯示顏色呢?</span> <a href="https://hackmd.io/_uploads/SkVZmq44Y.png"><span class="img100">![](https://hackmd.io/_uploads/SkVZmq44Y.png)</span></a> ### 2、自訂【日期】格式 <a href="https://hackmd.io/_uploads/S1msN9N4t.png"><span class="img100">![](https://hackmd.io/_uploads/S1msN9N4t.png)</span></a> ### 3、自訂【數值】格式 <a href="https://hackmd.io/_uploads/S1L6VcEEt.png"><span class="img100">![](https://hackmd.io/_uploads/S1L6VcEEt.png)</span></a><a href="https://hackmd.io/_uploads/SJP9YPgPK.png"><span class="img60">![](https://hackmd.io/_uploads/SJP9YPgPK.png)</span></a> ### 4、自訂【其他】格式 <span class="img90">![](https://hackmd.io/_uploads/B1jLSsNEY.png)</span> ### 5、數值以【零】為基準格式 <a href="https://hackmd.io/_uploads/HyXQU5NEK.png"><span class="img90">![](https://hackmd.io/_uploads/HyXQU5NEK.png)</span></a> <span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">🔶【範例】</span> <a href="https://hackmd.io/_uploads/rkSSw54VF.png"><span class="img90">![](https://hackmd.io/_uploads/rkSSw54VF.png)</span></a> ### 6、數值以【非零】為基準格式 <a href="https://hackmd.io/_uploads/Sy8cwqE4t.png"><span class="img90">![](https://hackmd.io/_uploads/Sy8cwqE4t.png) </span></a><span style="color:blue ; font-size:1.1em; line-height:2em;margin-left:30px;">🔶【範例】</span> <a href="https://hackmd.io/_uploads/B1mswcNVY.png"><span class="img90">![](https://hackmd.io/_uploads/B1mswcNVY.png) </span></a> # 04、建立公式的觀念 ## <span class="h2">A、函數介紹</span> ### 1. 公式基本結構 #### <span class="h4">(1) 公式運算式元素</span> |類型|說明| | -------- | -------------- | |數字常數|固定值,如數字、"日期"、"時間"...<br>"2021/01/1"、"12:30"| |文字|固定文字(前後加上雙引號)| | 參照位址 | 儲存格位址計算 = B2 * C2<br>跨工作表或檔案計算= <span style="color:red;font-weight:bold;">[</span> 檔案名稱 <span style="color:red;;font-weight:bold;">]</span> 工作表名稱 <span style="color:red;;font-weight:bold;">!</span> 參照位置 | |函數語法 | = 函數名稱 ( 條件1 , 條件2 , ... )| #### <span class="h4">(2) 公式可用運算符號</span> | 符號 | 說明 | |-|-| |( ) |括號最內層括號公式先運算| |+-(正負號)、%、^(指數)、<br>*(乘)、/(除)、+(加)、-(減)|一般運算符號| |&<br>例: = "A"+"B" → 結果為 AB| 連結文字| |=、<>、<、>、>=、<=<br>例:<br> =5>3 結果為 TRUE<br>=5<3 結果為 FALSE |大小相比較| #### <span class="h4">(3) 常用錯誤值類型</span> |顯示結果|說明| |-|-| |#DIV/0!|表示除式之分母為0} |#N/A|表示計算公式中有無效值,暫不計算此值。| |#VALUE!|表示公式中的運算式的類型錯誤時,#VALUE!錯誤值出現| |#NUM!|輸入數值有問題。<br>如:要求出現正數處卻出現負數或數值超出範圍| |#NAME|公式內有無法辨識之名稱或函數時。| |#NULL!|當指定二個範圍相交並無交集時。| |###|輸入內容,因儲存格寬度不足,其無法顯示結果| #### <span class="h4">(4) 公式中「相對與絕對(固定)」的考量時機</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式複製時才需考量 </span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 利用`F4`功能鍵,進行相對與絕對(固定)切換,【$】表示為絕對(固定)</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式【縱向】複製考量【列】是否固定 </span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式【橫向】複製考量【欄】是否固定</span> ### 2. 常用函數 <a href="https://hackmd.io/_uploads/HJT1kn4NK.png"><span class="img100">![](https://hackmd.io/_uploads/HJT1kn4NK.png)</span></a> ### 3. 處理小數位數之函數 <span class="ex">🔶目的:處理小數位數之函數</span> | 函數|說明| | -|-| | INT( 數值 ) | 小數位數無條件<span style="color:#FF0000">捨去至整數值</span> | | Round( 數值 , 取捨位數 ) | 數值 <span style="color:#FF0000">四捨五入</span> 成為指定位數 | | RoundDown( 數值 , 取捨位數 ) | 數值<span style="color:#FF0000">無條件捨去</span>成為指定位數 | | RoundUp( 數值 , 取捨位數 ) | 數值<span style="color:#FF0000">無條件進位</span>成為指定位數 | 說明:位數取捨方式: &#12288;&#12288;&#12288;正值:表示小數位數取捨 &#12288;&#12288;&#12288;負值:表示整數位數取捨 ### 4. 乘積計算函數 <span style="margin:auto; display:block;">![](https://hackmd.io/_uploads/HJ86VlSEY.png)</span> ## <span class="h2">B、【成績單】練習</span> <span class="ex">🔶 【範例】</span> <a href="https://hackmd.io/_uploads/Hk04j1SVF.png"><span class="img100">![](https://hackmd.io/_uploads/Hk04j1SVF.png)</span></a> ### 1. 平均值 ( 欄位 I ) <span class="ex">🔶 目的:處理小數位數之函數</span> | 函數|說明| | -|-| | INT( 數值 ) | 小數位數無條件<span style="color:#FF0000">捨去至整數值</span> | | Round( 數值 , 取捨位數 ) | 數值 <span style="color:#FF0000">四捨五入</span> 成為指定位數 | | RoundDown( 數值 , 取捨位數 ) | 數值<span style="color:#FF0000">無條件捨去</span>成為指定位數 | | RoundUp( 數值 , 取捨位數 ) | 數值<span style="color:#FF0000">無條件進位</span>成為指定位數 | 說明:位數取捨方式: &#12288;&#12288;&#12288;正值:表示小數位數取捨 &#12288;&#12288;&#12288;負值:表示整數位數取捨 ``` 在 I5 儲存格建立公式 = Round ( 數值 , 取捨小數位數 ) = Round ( Average(E5:G5) , 1 ) ``` ### 2. 加權分數 ( 欄位 J ) <span class="ex">🔶 目的:了解公式中「相對與絕對」的考量時機</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式複製時才需考量 </span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 利用`F4`功能鍵,進行相對與絕對(固定)切換,【$】表示為絕對(固定)</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式【縱向】複製考量【列】是否固定 </span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式【橫向】複製考量【欄】是否固定</span> ``` 在 J5 儲存格建立公式 加權分數 = 平均值 * 加權值 + 平均值 = Round( I5 * $J$2 + I5 , 1 ) ``` ### 3. IF 函數進行條件判斷 <span style="margin:auto; display:block; width:70%;">![](https://hackmd.io/_uploads/rJWiXlHVK.png) </span> #### <span class="h4">(a) 欄位K,>=70者使用加權分數</span> ``` 在 K5 儲存格建立公式 >=70者使用加權分數 = IF ( 判斷式 , 是 , 否 ) = IF ( 平均值 >= 加權分數 , 平均值 , 加權值 ) = IF ( I5 >= $L$2 , J5 , I5 ) ``` #### <span class="h4">(b) 欄位L,合格與否</span> ``` 在 L5 儲存格建立公式 =IF(K5>=$L$2,"合格","不合格") ``` ### 4. 評語結果 ( 欄位 M ) ``` 在 M5 儲存格建立公式 = 85.5 & 分, & 合格 = K5 & "分," & L5 ``` ### 5. 權重計算 ( 欄位 R ) <span style="margin:auto; display:block;">![](https://hackmd.io/_uploads/HJ86VlSEY.png)</span> ``` 在 R5 儲存格建立公式 =SumProduct ( 數列1 , 數列2 ) =SumProduct ( N5:Q5 , $N$2:$Q$2 ) ``` ### 6. 排名 ( 欄位 S ) :::info = Rank ( 查詢值 , 查詢範圍 , 順序 ) 順序:指定排序的方式,    若省略或是填0:由大排到小,遞減    若填為1:由小排到大,遞增 <a href="https://hackmd.io/_uploads/B1YvPlS4F.png"><span class="img100">![](https://hackmd.io/_uploads/B1YvPlS4F.png)</span></a> ::: ``` 在 S5 儲存格建立公式 =RANK ( 查詢值 , 查詢範圍 , 順序 ) =RANK ( R5 , $R$5:$R$24 , 0 ) ``` ### 7. 繳交日期(O欄) S1:<span class="red">在其他儲存格</span>輸入O5的公式 ``` =IF(O5="",NOW(),O5) ``` <span class="blue">公式輸入思考原則:繳交日期O5顯示當下時間,否則顯示空白</span> <a href="https://hackmd.io/_uploads/S1VKgP6Sc.png"><span class="img100">![](https://hackmd.io/_uploads/S1VKgP6Sc.png)</span></a> S2:將S1公式在<span class="red">編輯公式下</span>複製其內公式文字,則入O5儲存格,修改公式 ``` =IF(N5="","",IF(O5="",NOW(),O5)) ``` <span class="blue">公式輸入思考原則:當N5輸入已繳交班費(V),繳交日期O5顯示當下時間,否則顯示空白</span> <a href="https://hackmd.io/_uploads/B1BHVvar5.png"><span class="img100">![](https://hackmd.io/_uploads/B1BHVvar5.png)</span></a> S3:在進行S2公式建立時,會造成公式循環問題時,利用【檔案】>【選項】(設定如下圖)<br> <span class="urll red" style="font-size:1.2em">[參考資訊:移除或允許循環參照,並了解反覆運算用意](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) </span> <a href="https://hackmd.io/_uploads/HkciSwpHc.png"><span class="img100">![](https://hackmd.io/_uploads/HkciSwpHc.png)</span></a> ## <span class="h2">C、 設定格式化條件</span> <span class="step">設定格式化的條件:增加各種色彩提昇趣味性及圖示有效地呈現視覺性的簡易分析效果。</span> <span class="step">指令:【常用】>【設定格式化條件】</span> <br> <span class="ex" style="line-height:2em;">🔶 範例目的:合格者設定提醒色彩</span> <span class="step">S1:選取A5:M24</span> <span class="step">S2:【常用】>【設定格式化條件】>【新增規則】>【用公式來決定…】</span> <span class="step">S3:<span style="color:red; font-size:1em;"> 公式思考邏輯:當個人分數 &#062;= 標準分數時,標示色彩 </span></span> <span class="img80" style="border:1px solid #000">![](https://hackmd.io/_uploads/HktmherVK.png)</span> <span class="ex" style="line-height:2em;">🔶 範例目的:隔列變色</span> <span class="step">S1:選取A5:M24</span> <span class="step">S2:【常用】>【設定格式化條件】>【新增規則】>【用公式來決定…】</span> <span class="step">S3:</span> :::info 📌 利用MOD函數,將奇、偶值,變更為1與0 <span style="color:red; font-size:1em; padding-left:2em"> 公式思考邏輯:奇數值(1、3、5、7)除以2,其餘數值為1,相反的偶數值餘數為0 </span> <br> ``` = MOD( ROW ( ) , 2 ) = 1 ``` <span class="img60" style="border:1px solid #000">![](https://hackmd.io/_uploads/ryIgc4SVK.png)</span> ::: <span class="img80" style="border:1px solid #000">![](https://hackmd.io/_uploads/SyyexSr4K.png) </span> # 05、龐大資料工作處理建議 ## <span class="h2">A、善用看得懂的文字公式</span> ### 1、 名稱定義 <a href="https://hackmd.io/_uploads/HJeIbBHEt.png"><span class="img80">![](https://hackmd.io/_uploads/HJeIbBHEt.png)</span></a> ### 2、 表格化 可以使用新的使用者介面快速地建立、格式化及展開 Excel 表格,組織工作表上的資料,讓其更易於使用。 目的:當數據增加時,公式計算範圍隨著數據增加而延伸 * 指令:【常用】>【格式化為表格】 * 優點:範圍可快速選取、公式容易識別、公式隨著數據增加而調整計算範圍 #### <span class="h4">(A) 指令介紹</span> <span class="step">📌 建立</span> <span class="step">指令:【常用】>【格式化為表格】</span> <span class="img70">![](https://hackmd.io/_uploads/BJko6I7NK.png)</span> <span class="step">📌 表格化指令</span> <a href="https://hackmd.io/_uploads/Synd8KY4Y.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/Synd8KY4Y.png)</span></a> #### <span class="h4">(B) 表格化選取方法</span> <span class="step">🔶 左鍵一下選取內容、連二下選取內容及標題</span> <span class="img70">![](https://hackmd.io/_uploads/HkW-xKY4t.png)</span> #### <span class="h4">(&#067;) 表格化後,公式表示結構</span> <span class="red" style="font-size:1.5em; text-align:center;display:block; border:1px solid; border-radius:5px; width:60%; margin:auto; background:#FEF7DB;line-height:2.1em">=表格名稱〔@欄位名稱〕</span> <span style="color:blue;font-size:1.2em;padding-left:2em;line-height:2em"><i class="fa fa-hand-o-right"></i> 有@:相對性欄位</span> <span style="color:blue;font-size:1.2em;padding-left:2em;line-height:2em"><i class="fa fa-hand-o-right"></i> 沒有@:整欄計算</span> #### <span class="h4">(D) 交叉分析篩選器</span> <span class="step">交叉分析篩選器為一視覺化控制項,以互動且直覺的方式,快速篩選僅顯示所需的資料的資料。</span> <a href="https://hackmd.io/_uploads/ryV6FFKVt.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/ryV6FFKVt.png) </span></a> #### <span class="h4">(E) 注意事項</span> <span style="color:#BF1363;font-size:1.2em;padding-left:2em;line-height:2em">🔶 移除表格化前,先確認是否要移除色彩</span> <span class="step" style="padding-left:5em"><i class="fa fa-hand-o-right"></i> 為了取得帶狀列的色彩,直接移除表格化</span> <span class="step" style="padding-left:5em"><i class="fa fa-hand-o-right"></i> 因反悔表格化功能,而<span class="red">表格會動用排序時</span>,<span class="red">必須</span>要移除色彩</span> <span style="color:#BF1363;font-size:1.2em;padding-left:2em;line-height:2em">🔶 移除方法</span> <span class="img40">![](https://hackmd.io/_uploads/ryMkItYEt.png)</span> ## <span class="h2">B、解決總和遇上篩選困擾</span> ### 1、 小計函數:Subtotal <a href="https://hackmd.io/_uploads/BJePGHrNK.png"><span class="img100">![](https://hackmd.io/_uploads/BJePGHrNK.png)</span></a> ### 2、 會除錯的小計函數:Aggregate Aggregate函數比Subtotal函數還要強大,因面對忽略錯誤值、隱藏行和分類匯總嵌套,滿足Subtotal無法處理,但Aggregate滿足各種情況下的統計需求! <a href="https://hackmd.io/_uploads/SkycGSSNY.png"><span class="img100">![](https://hackmd.io/_uploads/SkycGSSNY.png)</span></a><a href="https://hackmd.io/_uploads/Bk_r2nxvY.png"><span class="img100">![](https://hackmd.io/_uploads/Bk_r2nxvY.png)</span></a> ### 3、課程範例 <span class="step">【說明Subtotal函數】工作表,範例計算結果,如下圖:</span> <a href="https://hackmd.io/_uploads/HJkJNrHVK.png"><span class="img100">![](https://hackmd.io/_uploads/HJkJNrHVK.png)</span></a> <br><br><br> ## <span class="h2">C、資料驗證</span> ### 1、 說明 設定允許使用者輸入無效資料,防止使用者輸入無效的資料,並可發出警告修正錯誤填入 <span class="img90">![](https://hackmd.io/_uploads/HJZUHtrNt.png)</span> ### 2、Indirect函數 ``` = Indirect ( 定義名稱 ) ``` ### 3、 課程範例 <a href="https://hackmd.io/_uploads/HJZOrBS4F.png"><span class="img100">![](https://hackmd.io/_uploads/HJZOrBS4F.png)</span></a> ## <span class="h2">D、排序與小計</span> :::info <span style="color:#b71c1c; ">※【格式化為表格】無法使用小計功能,利用【轉換為範圍】移除格式化為表格前,應確認顏色是否移除</span> ::: ### S1:小計之前先排序 <span class="step">指令:【資料】>【排序】</span> ### S2:先排序的欄位,先執行小計工作 <span class="step">指令:【資料】>【小計】</span> <span class="step">S2-1:第一階層排序欄位【經銷商】</span> <a href="https://hackmd.io/_uploads/r1DdTvgPK.png"><span class="img40">![](https://hackmd.io/_uploads/r1DdTvgPK.png)</span></a> <span class="step">S2-2:第二階層排序欄位【商品名稱】</span> <a href="https://hackmd.io/_uploads/S1eh6wgwF.png"><span class="img40">![](https://hackmd.io/_uploads/S1eh6wgwF.png)</span></a> ## <span class="h2">E、 進階篩選</span> ::: danger <span style="color:#b71c1c;font-size:1.2em;">進階篩選原則:</span> <span style="color:#5800cc; ">(1)準則條件<span style="color:#b71c1c;font-weight:bold ">應包含</span>資料來源欄位名稱</span> <span style="color:#5800cc; ">(2)準則條件<span style="color:#b71c1c;font-weight:bold ">同列</span>為<span style="color:#b71c1c;font-weight:bold ">且</span>,<span style="color:#b71c1c;font-weight:bold ">不同列</span>為<span style="color:#b71c1c;font-weight:bold ">或</span></span> <span style="color:#5800cc; ">(3)準則條件若為公式,則準則條件欄位名稱<span style="color:#b71c1c;font-weight:bold ">不可</span>與現有欄位相同</span> ::: <span class="step">指令:【資料】>【進階...】</span> <a href="https://hackmd.io/_uploads/rJzlCPevY.png"><span class="img100">![](https://hackmd.io/_uploads/rJzlCPevY.png)</span></a> ## <span class="h2">F、 數據整理前置作業</span> ### 0、注意事項 * 樞紐分析時,不要無謂的空格 * 樞紐分析報表呈現不靈活時,建議改為運用CountIF函數較為彈性 ### 1、取得數據無法計算的困擾 * <span class="step">善用【資料剖析】進行轉換</span> * <span class="step">指令:【資料】>【資料剖析】</span> <a href="https://hackmd.io/_uploads/HkagXxfEt.png"><span class="img90">![](https://hackmd.io/_uploads/HkagXxfEt.png)</span></a> ### 2. 符合條件計算 * 樞紐分析報表呈現不靈活時,建議改為您原先運用CountIF函數較為彈性 <span class="red">📌 樞紐分析報表呈現不靈活時,建議改為您原先運用CountIF函數較為彈性</span><br><br> 註:課程範例中,先行針對【職員薪資明細】的表格化及名稱定義,在『總表』工作表中,依各職務進行計算 <span class="step">符合條件計算函數:Countif、Sumif、CountifS、SumifS、Averageif、AverageifS</span> <span class="img100">![](https://hackmd.io/_uploads/BkjfkRBNY.png)</span> <span class="img60" style="margin:0">![](https://hackmd.io/_uploads/H1oD0PxE5.png)</span> ### 3. 唯一值列表方法 #### <span class="h4">(1) 方法1:利用移除重複項指令</span> 缺點:原名單異動,此功能不會更新 指令:【資料】>【移除重複項】 <a href="https://hackmd.io/_uploads/SkYwAxwS5.png"><span class="img60">![](https://hackmd.io/_uploads/SkYwAxwS5.png)</span></a> #### <span class="h4">(2) 方法2:利用色彩進行數據比對</span> 指令:【常用】>【條件式格式設定】>【醒目提示儲存格規則】>【重複的值...】 <a href="https://hackmd.io/_uploads/Sy6kkbwH5.png"><span class="img60">![](https://hackmd.io/_uploads/Sy6kkbwH5.png)</span></a> #### <span class="h4">(2) 方法3:Unique函數</span> Notes:2019版本後新功能 :::info UNIQUE 函數: 目的:傳回範圍中唯一值清單 公式:= UNIQUE ( 1️⃣ 比對範圍, 2️⃣ 如何進行比較 , 3️⃣ 出現次數 ) <br> &#12288;2️⃣ 如何進行比較: &#12288;&#12288;True:傳回唯一欄 &#12288;&#12288;False (可省略):傳回唯一列 &#12288;3️⃣ 如何進行比較: &#12288;&#12288;True:傳回只顯示一次的項目,<span class="red">顯示『只出現一次』者</span> &#12288;&#12288;False (可省略):傳回每個相異的項目,只顯示一次的項目,<br>&#12288;&#12288;&#12288;&#12288;&#12288;&#12288;&#12288;&#12288;&#12288;<span class="red">顯示『不重覆』者</span> ::: # 06、員工年終考績統計 ## <span class="h2">A、課程函數</span> ### 1、一維查表函數 依查詢值進行資料比對輸出相關資料之函數 :::success = Vlookup ( <span style="color:blue;">查詢值</span> , <span style="color:red;">查表範圍</span> , 回傳指定欄數 , <span style="color:#7030A0;">選項</span>) 說明 * <span style="color:blue;">查詢值若為『文字』,選項為False<br>查詢值若為『數值』,選項通常為True,且查表範圍應遞增</span> * <span style="color:blue;">查詢值應為查表範圍的第一欄 / 列</span> * <span style="color:red;">查表範圍若為『縱』向查詢,即為 Vlookup 函數<br>查表範圍若為『橫』向查詢,即為 Hlookup 函數</span> * <span style="color:#7030A0;">選項:</span> * <span style="color:#7030A0;">False,必須比對完合符合才回傳,查無資料以 “#N/A” 表示<br>True 或省略,比對回傳接近不超過查詢值的資料</span> * 查表函數,可搭配 Iferror 函數 ::: ### 2、二維查表函數 #### <span class="h4">(1) Index函數</span> <span class="step">根據欄列號索引回傳查表範圍的值</span> ``` = Index ( 查表範圍 , 指定列數 , 指定欄數 ) ``` <a href="https://hackmd.io/_uploads/BkEql5r4F.png"><span class="img40">![](https://hackmd.io/_uploads/BkEql5r4F.png)</span></a> #### <span class="h4">(2) Match函數</span> <span class="step">傳回搜尋項目於搜尋範圍中的相對位</span> :::info Match 函數: 目的:傳回搜尋項目於搜尋範圍中的相對位置 公式:=Match ( 1️⃣ 查詢值, 2️⃣ 查表範圍 , 3️⃣ 比對方式類型 ) &#12288;3️⃣ 比對方式類型: &#12288;&#12288;1或省略:查詢範圍須『遞增』 &#12288;&#12288;0:查詢範圍不須事前排序,找到完全符合查詢值 &#12288;&#12288;-1:查詢範圍須『遞減』 ::: <span class="step">🔶 公式比擬</span> <a href="https://hackmd.io/_uploads/rk3dE5B4t.png"><span class="img90">![](https://hackmd.io/_uploads/rk3dE5B4t.png)</span></a> ### 3、Iferror函數 若公式計算結果發生錯誤時,想要執行工作 :::success = Iferror ( 判斷運算公式 , 發生錯誤欲執行工作) <br><span style="color:red;">評估的錯誤類型包括:<br>#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 和 #NULL!。</span> ::: ### 4、符合條件計算 <span class="step">符合條件計算函數:Countif、Sumif、CountifS、SumifS、Averageif、AverageifS</span> <span class="img100">![](https://hackmd.io/_uploads/BJXnxAS4t.png)</span> ### 5、兩日期間的天/月/年數 ``` = Datedif ( 開始日 , 結束日 , " 單位 " ) ※ 說明: 單位:決定要傳回的單位,必須前後加上 " " Y代表滿幾年、M代表滿幾月、D代表滿幾日 MD代表兩日期間的天數差,忽略月和年 YM代表兩日期間的月數差,忽略日和年 YD代表兩日期間的天數差,忽略年 要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位 ``` ### 6、文字函數 <a href="https://hackmd.io/_uploads/rya6hRBEt.png"><span class="img100">![](https://hackmd.io/_uploads/rya6hRBEt.png)</span></a> ## <span class="h2">B、課程範例(1)</span> <a href="https://hackmd.io/_uploads/B1RQCnr4K.png"><span class="img100">![](https://hackmd.io/_uploads/B1RQCnr4K.png)</span></a> ### 1、全勤獎金 <span class="img80">![](https://hackmd.io/_uploads/BJ4nOqrVF.png)</span> ``` 在『職員薪資明細』工作表中, I4 儲存格建立公式 =IF ( 判斷式 , 【是】的工作 , 【否】的工作 ) =IF ( [@請假時數]=0 , 2000 , 0 ) ``` ### 2、績效等級 #### <span class="h4">(1) 利用IF執行多選一</span> <span class="img40">![](https://hackmd.io/_uploads/r1qPgirEt.png)</span> <span style="color:#333399 ; font-size:1.2em; font-weight:bold; line-height:1em;margin-left:10px;">🔶方法1:IF多選1思考方向</span> <a href="https://hackmd.io/_uploads/HyjjxsHEK.png"><span class="img90">![](https://hackmd.io/_uploads/HyjjxsHEK.png) </span></a> :::info 在『職員薪資明細』工作表中, G4 儲存格建立公式 =IF ( &#12288; &#12288; &#12288; 判斷式 &#12288; &#12288;&#12288;, &#12288;【是】&#12288;,&#12288;&#12288;&#12288;&#12288;&#12288;&#12288;&#12288;<span style="color:blue">【否】</span> &#12288;&#12288;&#12288;&#12288;&#12288;&#12288; ) =IF ( [@績效評分] &#062;=90 &#12288;,&#12288;&#12288;"A"&#12288;&#12288;,&#12288;&#12288;<span style="color:blue"> IF([@績效評分]&#060;80,"C","B")</span> &#12288;&#12288; ) ::: <span style="color:#333399 ; font-size:1.2em; font-weight:bold; line-height:1em;margin-left:10px;">🔶方法2:【介於】應用</span> <span class="step">📌【介於】思考邏輯</span> <span class="step" style="line-height:2em;padding-left:3em"><i class="fa fa-hand-o-right"></i> 錯誤思考邏輯:`80 <= 績效評分 < 90 `</span> <span class="step" style="line-height:2em;padding-left:3em"><i class="fa fa-hand-o-right"></i> 【介於】思考邏輯:</span> <a href="https://hackmd.io/_uploads/ryvVUsSVK.png"><span class="img80">![](https://hackmd.io/_uploads/ryvVUsSVK.png)</span></a> <span class="step">📌 範例結果 </span> <a href="https://hackmd.io/_uploads/BkYpKoBVt.png"><span class="img100">![](https://hackmd.io/_uploads/BkYpKoBVt.png)</span></a> :::info 在『職員薪資明細』工作表中, E4 儲存格建立公式 =IF ( &#12288;&#12288;&#12288;判斷式&#12288;&#12288;, 【是】&#12288;,&#12288;&#12288;&#12288;&#12288;&#12288;&#12288;&#12288;<span style="color:blue">【否】</span> &#12288;&#12288;&#12288;&#12288;&#12288;&#12288; ) =IF ( [@績效評分]>=90 ,&#12288;"A"&#12288;, <span style="color:blue"> IF ( <span style="color:red">AND([@績效評分]>=80 , [@績效評分]&#060;90 )</span> , "B" , "C" )</span> &#12288;&#12288; ) ::: <span style="color:#333399 ; font-size:1.2em; font-weight:bold; line-height:1em;margin-left:10px;">🔶方法3:利用Vlookup函數</span><a href="https://hackmd.io/_uploads/rkpoiir4F.png"><span class="img80">![](https://hackmd.io/_uploads/rkpoiir4F.png)</span></a> ### 3、職務 ``` 在『職員薪資明細』工作表中, G4 儲存格建立公式 =Vlookup ( 查詢值 , 查詢範圍 , 回傳欄數 , 選項 ) =VLOOKUP ( [@員工號碼] , 基本資料 , 9 , FALSE ) ``` ### 4、績效獎金 <span class="img50">![](https://hackmd.io/_uploads/r1jPpiHEK.png)</span><a href="https://hackmd.io/_uploads/BJu80jrNK.png"><span class="img100">![](https://hackmd.io/_uploads/BJu80jrNK.png)</span></a> ``` 在『職員薪資明細』工作表中, L4 儲存格建立公式 =ROUNDDOWN( VLOOKUP([@績效等級],績效等級對照表!$B$3:$D$5,3,FALSE)*[@薪資], -2) ``` ### 5、薪資區間 <span class="img40">![](https://hackmd.io/_uploads/SkkVqnBEK.png)</span> ``` 在『職員薪資明細』工作表中, N4 儲存格建立公式 VLOOKUP ( [@薪資合計] , 區間標示 , 2 , TRUE ) ``` ### 6、查詢員工相關資料 <span class="img80">![](https://hackmd.io/_uploads/r1dkR2r4F.png)</span><a href="https://hackmd.io/_uploads/SJ5tfTr4F.png)"><span class="img100">![](https://hackmd.io/_uploads/SJ5tfTr4F.png)</span></a> ## <span class="h2">C、課程範例(2)</span> ### 1、符合條件計算 註:課程範例中,先行針對【職員薪資明細】的表格化及名稱定義,在『總表』工作表中,依各職務進行計算 <span class="step">符合條件計算函數:Countif、Sumif、CountifS、SumifS、Averageif、AverageifS</span> <span class="img100">![](https://hackmd.io/_uploads/BkjfkRBNY.png)</span> <span class="img60" style="margin:0">![](https://hackmd.io/_uploads/H1oD0PxE5.png)</span> ### 2、年資 <span class="img100">![](https://hackmd.io/_uploads/H1RhqCBVY.png)</span> #### <span class="h4">(1) 公式說明:計算兩日期間的天數、月數或年數</span> ``` = Datedif ( 開始日 , 結束日 , " 單位 " ) ※ 說明: 單位:決定要傳回的單位,必須前後加上 " " Y代表滿幾年、M代表滿幾月、D代表滿幾日 MD代表兩日期間的天數差,忽略月和年 YM代表兩日期間的月數差,忽略日和年 YD代表兩日期間的天數差,忽略年 要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位 ``` #### <span class="h4">(2) 範例結果:年資計算</span> ``` = Datedif ( [@起聘日期] , TODAY() , "Y" ) ``` ### 3、性別 #### <span class="h4">(1) 公式說明:文字、數值間轉換公式</span> <a href="https://hackmd.io/_uploads/rya6hRBEt.png"><span class="img60">![](https://hackmd.io/_uploads/SkLiC0S4F.png)</span></a> #### <span class="h4">(2) 公式說明:字串中抓取文字</span> ``` 依開始指定位置,傳回字元數的文字字串 = Mid ( 文字值 , 開始指定位置 , 欲回傳字元數) ``` #### <span class="h4">(3) 範例結果:性別 </span> <a href="https://hackmd.io/_uploads/BkDd1JLNF.png"><span class="img80">![](https://hackmd.io/_uploads/BkDd1JLNF.png)</span></a> ### 4、Text 延伸應用 <span class="ex" style="padding-left:2em">🔶 星期(中)</span> <span class="img80">![](https://hackmd.io/_uploads/BkGWMJ8Vt.png)</span><span class="ex" style="padding-left:2em">🔶 星期(英)</span> <span class="img80">![](https://hackmd.io/_uploads/ryg5GyLNY.png)</span> ## <span class="h2">D、課程範例2019函數應用</span> ### 1. 績效等級 #### <span class="h4">(1) Ifs函數</span> <span class="purple">📌 函數介紹</span><br> :::info IFs 函數: 目的:多條件判斷 公式: = IFs ( 1️⃣ 判斷式1 , 2️⃣ 成立時顯示結果1 , ... ) ::: <span class="purple">📌 範例說明</span> <span class="img90">![](https://hackmd.io/_uploads/S1_3nFFZj.png)</span> ``` =IFS([@績效評分]<80,"C",[@績效評分]<90,"B",TRUE,"A") ``` #### <span class="h4">(2) Switch函數</span> <span class="purple">📌 函數介紹</span> :::info Switch 函數: 目的:多條件判斷 公式: = Switch ( 1️⃣ 比對的值 , 2️⃣ 符合值1 , 3️⃣ 顯示結果1 , ... ) <a href="https://hackmd.io/_uploads/H1eH5mWtY.png"><span class="img60">![](https://hackmd.io/_uploads/H1eH5mWtY.png)</span></a> ::: <span class="purple">📌 範例說明</span><span class="img100">![](https://hackmd.io/_uploads/SJkQz9YZj.png)</span> ``` 函數: = Switch ( 1️⃣ 比對的值 , 2️⃣ 符合值1 , 3️⃣ 顯示結果1 , … ) 修改: = Switch ( TRUE , [@績效評分]>=90 , "A" ,[@績效評分]>=80,"B","C") ``` ### 2. 職務 #### <span class="h4">(1) xLOOKUP</span> <span class="purple">📌 函數介紹</span> :::info =xLOOKUP(要找的值, 從哪裡找, 傳回什麼, [錯誤說明], [相符類型], [搜尋模式]) ::: <span class="purple">📌 範例說明</span> ``` 函數: =xLOOKUP( 要找的值 , 從哪裡找 , 傳回什麼 , [錯誤說明] , [相符類型] ) 修改: =xLOOKUP([@員工號碼] , 基本資料[員工號碼] , 基本資料[職務] , "查無" , 0 ) ``` <br><br> ## <span class="h2">E、樞紐分析介紹</span> <span class="step">樞紐分析(Pivot):進行摘要、分析、探索,呈現資料來源的摘要表</span> ### 1、指令原則 <span class="step">指令:【插入】>【樞紐分析表】</span> <span class="img40">![](https://hackmd.io/_uploads/BJqQp1LNK.png)</span> ### 2、樞紐設定原則 <span class="step">指令:【插入】>【樞紐分析表】</span> <span class="img40">![](https://hackmd.io/_uploads/S1DOR1UNF.png)</span> ### 3、選項 <span class="img80">![](https://hackmd.io/_uploads/rJicfx8Nt.png)</span> ### 4、欄位排序 #### <span class="h4">(1) 手動</span> <span class="step">欄位直接拖曳</span> #### <span class="h4">(2) 自動</span> <span class="step">指令:【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目</span> <span class="img100">![](https://hackmd.io/_uploads/SkhtieMNY.png)</span> ### 5、群組類型 <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 文字</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 數值</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 日期</span> ## <span class="h2">F、樞紐分析(1)</span> <span class="ex">🔶 資料來源:員工薪資明細</span> <a href="https://hackmd.io/_uploads/BkrvE1UEt.png"><span class="img100">![](https://hackmd.io/_uploads/BkrvE1UEt.png)</span></a> ### 1、各職務/地區/績效等級之薪資情形 <a href="https://hackmd.io/_uploads/rkmESJUEt.png"><span class="img100">![](https://hackmd.io/_uploads/rkmESJUEt.png) </span></a> ### 2、各職務之薪資統計 #### <span class="h4">(1) 原始:樞紐分析設定欄位情形</span> <a href="https://hackmd.io/_uploads/HkSnSyIEY.png"><span class="img100">![](https://hackmd.io/_uploads/HkSnSyIEY.png)</span></a> #### <span class="h4">(2) 結果:樞紐分析【值的顯示方式】設定情形</span> <a href="https://hackmd.io/_uploads/HkzlIy84t.png"><span class="img100">![](https://hackmd.io/_uploads/HkzlIy84t.png)</span></a> ## <span class="h2">G、樞紐分析(2)</span> <span class="ex">🔶 資料來源:音樂產品銷售</span> <a href="https://hackmd.io/_uploads/ByKuL1U4K.png"><span class="img80">![](https://hackmd.io/_uploads/ByKuL1U4K.png)</span></a> ### 1、開始:建立樞紐分析表 <span class="step">建立各銷售員、各產品商品銷售金額情形,討論其不同計算方式。</span> <a href="https://hackmd.io/_uploads/HJZlDJUNK.png"><span class="img100">![](https://hackmd.io/_uploads/HJZlDJUNK.png)</span></a> ### 2、討論:【值的顯示方式】 <span class="step">針對【值的顯示方式】的不同計算方式,討論其不同計算方式,</span> <span class="step">其中【父項...】、【索引】不列入討論</span> <a href="https://hackmd.io/_uploads/SkqXdkUVY.png"><span class="img100">![](https://hackmd.io/_uploads/SkqXdkUVY.png)</span></a> ### 3、總計百分比 <a href="https://hackmd.io/_uploads/Bk4jOyUVt.png"><span class="img70">![](https://hackmd.io/_uploads/Bk4jOyUVt.png)</span></a> ### 4、欄總計百分比 <a href="https://hackmd.io/_uploads/BJ5kK1INY.png"><span class="img70">![](https://hackmd.io/_uploads/BJ5kK1INY.png)</span></a> ### 5、列總計百分比 <a href="https://hackmd.io/_uploads/B17zF1IVt.png"><span class="img70">![](https://hackmd.io/_uploads/B17zF1IVt.png)</span></a> ### 6、百分比... <a href="https://hackmd.io/_uploads/HJhVYkINK.png"><span class="img100">![](https://hackmd.io/_uploads/HJhVYkINK.png)</span></a> <a href="https://hackmd.io/_uploads/H1SFY1LNt.png"><span class="img100">![](https://hackmd.io/_uploads/H1SFY1LNt.png)</span></a> ### 7、差異... <a href="https://hackmd.io/_uploads/ryw3YJIEY.png"><span class="img100">![](https://hackmd.io/_uploads/ryw3YJIEY.png)</span></a> ### 8、差異百分比 <a href="https://hackmd.io/_uploads/ryWeqkLVt.png"><span class="img100">![](https://hackmd.io/_uploads/ryWeqkLVt.png)</span></a> ### 9、計算加總至... <a href="https://hackmd.io/_uploads/B1TM9JL4F.png"><span class="img100">![](https://hackmd.io/_uploads/B1TM9JL4F.png) </span></a> ### 10、計算加總至百分比 <a href="https://hackmd.io/_uploads/rkgwc1L4Y.png"><span class="img100">![](https://hackmd.io/_uploads/rkgwc1L4Y.png)</span></a> ### 11、最大到最小的排列 <a href="https://hackmd.io/_uploads/BJJqcy84Y.png"><span class="img100">![](https://hackmd.io/_uploads/BJJqcy84Y.png)</span></a> # 07、圖表 ## <span class="h2">A、 基本操作說明</span> ### 1、 圖表原則 <a href="https://hackmd.io/_uploads/SkSpSeLNF.png"><span class="img100">![](https://hackmd.io/_uploads/SkSpSeLNF.png)</span></a> ### 2、 繪製圖表上手口訣 <span class="step" style="line-height:2em; color:#d20808; font-weight:bold"><i class="fa fa-hand-o-right"></i> 口訣1:白點在誰身上,對誰做美化</span> <span class="step" style="line-height:2em; color:#d20808; font-weight:bold"><i class="fa fa-hand-o-right"></i> 口訣2:找不到工具,針對白點處【按二下】</span> <span class="step" style="line-height:2em; font-weight:bold"><i class="fa fa-hand-o-right"></i> 圖表原始指令:【圖表工具】>【格式】>【格式化選取範圍】</span> <a href="https://hackmd.io/_uploads/rJ6hPeLNt.png"><span class="img20">![](https://hackmd.io/_uploads/rJ6hPeLNt.png)</span></a> ### 3、 圖形圖注意事項 <a href="https://hackmd.io/_uploads/SJMzoxLNF.png"><span class="img100">![](https://hackmd.io/_uploads/SJMzoxLNF.png)</span></a> ## <span class="h2">B、善用【主副座標軸】</span> ### 1、處理差異懸殊的資料 <a href="https://hackmd.io/_uploads/S1WdFlIVF.png"><span class="img100">![](https://hackmd.io/_uploads/S1WdFlIVF.png)</span></a> ### 2、各項及總合值顯示技巧 <span class="ex">🔶 範例結果</span> <a href="https://hackmd.io/_uploads/S1_c3l8NK.png"><span class="img100">![](https://hackmd.io/_uploads/S1_c3l8NK.png)</span></a><span class="step">S1:設定主副座標軸</span> <a href="https://hackmd.io/_uploads/Bkid6lUEK.png"><span class="img100">![](https://hackmd.io/_uploads/Bkid6lUEK.png)</span></a> <span class="step">S2:針對合計列,快速左鍵二下,設定類別間距</span><a href="https://hackmd.io/_uploads/SyO1kW8Vt.png"><span class="img100">![](https://hackmd.io/_uploads/SyO1kW8Vt.png)</span></a> ### 3、顯示目標及當前完成情形 <span class="ex">🔶 範例結果</span> <a href="https://hackmd.io/_uploads/Byjxxb84Y.png"><span class="img100">![](https://hackmd.io/_uploads/Byjxxb84Y.png)</span></a> <span class="step"><i class="fa fa-hand-o-right"></i> 相關設定</span> <a href="https://hackmd.io/_uploads/Hy4DebI4F.png"><span class="img100">![](https://hackmd.io/_uploads/Hy4DebI4F.png)</span></a> ## <span class="h2">B、善用【圖表範本】</span> <span class="ex">🔶 目的:相同圖表類型,進行重覆美化</span> <span class="step">S1:針對圖表按右鍵 > 另存為範本</span> <a href="https://hackmd.io/_uploads/rydPfWIEY.png"><span class="img80">![](https://hackmd.io/_uploads/rydPfWIEY.png)</span></a> <span class="step">S2:針對想要套用範本的圖表 > 右鍵 > 【變更圖表類型】</span> <span class="img70" style="border:1px solid">![](https://hackmd.io/_uploads/Hkv3QWIVt.png)</span> ## <span class="h2">C、善用【組合圖】+【輔助列】</span> <span class="step">往往既有的數列所製作的圖表都較為陽春,需要較多變化時會新增數列,將原有數列進行計算,以利後續圖表變化應用</span> <span class="ex">🔶 範例:隨著數據變動,最大值與最小值自動改變位置</span> <span class="step">以商品銷售情形為例,適合使用各項產品銷售量上差異的表現,或逐月、逐年在時間上銷售量的變化,為了更突出數據,有時需要在圖表上特別強調最大值與最小值,並且隨著數據改變而自動變更位置。</span> <span class="img100">![](https://hackmd.io/_uploads/HkAIIW8Vt.png)</span> <span class="step">S1:如圖所示,新增輔助列寫入公式找出最大值,點選儲存格D3後,輸入以下公式後,並向下複製。</span> ``` =IF($C3=MAX($C$3:$C$14),$C3,NA()) ``` <span class="step">同理,新增找出最小值數列,點選儲存格D3後,以下公式。(以下最小值步驟相同,將不贅述)</span> ``` =IF($C3=MIN($C$3:$C$14),$C3,NA()) ``` <span class="img70">![](https://hackmd.io/_uploads/r1EQ9-UNF.png)</span> <span class="step">S2:選取B2至E14數據範圍後,新增為【群組直條圖】,接著切換至【所有圖表】索引標籤下,點選【組合式】(如圖所示):</span> <span class="step"><i class="fa fa-hand-o-right"></i>總金額選取【群組直條圖】</span> <span class="step"><i class="fa fa-hand-o-right"></i>最大/最小值數列選取【含有資料標記的折線圖】</span> <a href="https://hackmd.io/_uploads/BkJ0cWU4F.png"><span class="img100">![](https://hackmd.io/_uploads/BkJ0cWU4F.png)</span></a> <span class="step">S3:點選總金額直條圖數列後,按【右鍵】>【資料數列格式】>如圖所示,利用【類別間距】拉桿,調整直條圖數列的寬度</span> <a href="https://hackmd.io/_uploads/ry8Hi-L4K.png"><span class="img100">![](https://hackmd.io/_uploads/ry8Hi-L4K.png)</span></a> <span class="step">S4:點選最大值數列後,【右鍵】>【新增資料標籤】>【新增資料圖說文字】次選項指令。</span> <a href="https://hackmd.io/_uploads/B1DOob8NF.png"><span class="img100">![](https://hackmd.io/_uploads/B1DOob8NF.png)</span></a> <span class="step">S5:調整圖說文字</span> <span class="step"><i class="fa fa-hand-o-right"></i>點選最大值數列>【右鍵】>【變更資料標籤圖案】</span> <span class="step"><i class="fa fa-hand-o-right"></i>如圖所示,顯示【數列名稱】,標籤位置【上】</span> <a href="https://hackmd.io/_uploads/BkkajbI4Y.png"><span class="img100">![](https://hackmd.io/_uploads/BkkajbI4Y.png) </span></a> # 08、樞紐分析 <span class="step">樞紐分析(Pivot):進行摘要、分析、探索,呈現資料來源的摘要表</span> ## <span class="h2">A、指令原則</span> ### 1、指令原則 <span class="step">指令:【插入】>【樞紐分析表】</span> <span class="img40">![](https://hackmd.io/_uploads/BJqQp1LNK.png)</span> ### 2、樞紐設定原則 <span class="step">指令:【插入】>【樞紐分析表】</span> <span class="img40">![](https://hackmd.io/_uploads/S1DOR1UNF.png)</span> ### 3、選項 <span class="img80">![](https://hackmd.io/_uploads/rJicfx8Nt.png)</span> ### 4、欄位排序 #### <span class="h4">(1) 手動</span> <span class="step">欄位直接拖曳</span> #### <span class="h4">(2) 自動</span> <span class="step">指令:【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目</span> <span class="img100">![](https://hackmd.io/_uploads/SkhtieMNY.png)</span> ### 5、群組類型 <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 文字</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 數值</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 日期</span> ## <span class="h2">B、課程範例</span> <span class="ex">🔶 資料來源:商品銷售明細</span> <a href="https://hackmd.io/_uploads/S1t6mlIEt.png"><span class="img70">![](https://hackmd.io/_uploads/S1t6mlIEt.png)</span></a> ### 1、【文字】群組 <span class="step">各經銷商、各地區之商品銷售金額情形</span> <a href="https://hackmd.io/_uploads/SJ-7NxIEK.png"><span class="img100">![](https://hackmd.io/_uploads/SJ-7NxIEK.png)</span></a> ### 2、【數值】群組 <span class="step">了解產品銷售數量頻率</span> <a href="https://hackmd.io/_uploads/HJfFNeIEK.png"><span class="img100">![](https://hackmd.io/_uploads/HJfFNeIEK.png)</span></a> ### 3、【日期】群組 <span class="step">彙整各季/年/月產品銷售金額情形</span> <a href="https://hackmd.io/_uploads/r1RjElINt.png"><span class="img100">![](https://hackmd.io/_uploads/r1RjElINt.png)</span></a> # 09、函數技巧應用 ## <span class="h2">A. 善用特殊目標1 _填滿空格</span> <span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span> {%youtube wk6VB5Y2lhE %} ## <span class="h2">B. 善用特殊目標2 _每五筆表格拆分</span> <span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span> {%youtube 36N759w_6io %} ## <span class="h2">C. 每月各項收支報表</span> <a href="https://hackmd.io/_uploads/rJX66-UEt.png"><span class="img100">![](https://hackmd.io/_uploads/rJX66-UEt.png)</span></a> ### 1、函數介紹 #### <span class="h4">(1) Index函數</span> <span class="step">根據欄列號索引回傳查表範圍的值</span> ``` = Index ( 查表範圍 , 指定列數 , 指定欄數 ) ``` <span class="img40">![](https://hackmd.io/_uploads/BkEql5r4F.png)</span> #### <span class="h4">(2) Match函數</span> <span class="step">傳回搜尋項目於搜尋範圍中的相對位</span> ``` = Match ( 查詢值, 查表範圍 , 比對方式類型 ) 📌比對方式類型 1或省略:查詢範圍須『遞增』 0:查詢範圍不須事前排序,找到完全符合查詢值 -1:查詢範圍須『遞減』 ``` <span class="step">🔶 公式比擬</span> <span class="img80">![](https://hackmd.io/_uploads/rk3dE5B4t.png)</span> ### 2、範例公式建立 <span style="color:#b71c1c; display:block; background:#fce4ec; text-align:center; line-height:2em">【Youtube】</span> {%youtube YxT7UA6-DF8 %} ## <span class="h2">D. Excel批次貼入圖片方法</span> <span class="ex">🔶 範例結果</span> <span class="img90">![](https://hackmd.io/_uploads/S1U_CZ8VK.png)</span> <span class="step">S1:複製圖片路徑,將所有照片置於同一資料夾中,並複製其路徑</span> <span class="img90">![](https://hackmd.io/_uploads/rJ3bJGU4Y.png)</span> <span class="step">S2:語法說明複製下方法語法至Excel中,修改其內容</span> ``` ="<table><img src='圖片路徑資料夾\" & 檔名 & ".副檔名' width='50'>" ``` 語法說明 <span class="img100">![](https://hackmd.io/_uploads/BJ8Lyz8VF.png)</span> <span class="step">S3:貼入Excel調整情形參考</span> <span class="img100">![](https://hackmd.io/_uploads/HyhqyfLVY.png)</span> <span class="step">S4:路徑結果貼入記事本中,將路徑結果貼入記事本中,將其Excel設定移除</span> <span class="img80" style="border:1px solid;padding:10px">![](https://hackmd.io/_uploads/SJ_CyML4t.png)</span> <span class="step">S5:再將記事本路徑貼回Excel中,再將記事本路徑貼回Excel中,調整圖片大小即可 </span> <br><br><br><br><br><br> # 11、課程公式彙整 ## <span class="h2">O、Excel函數列表</span> :::danger <span class="urll">[微軟官網連結: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) </span> ::: ## <span class="h2">A、公式建立開始</span> ### 1. 公式基本結構 |類型|說明| | -------- | -------------- | |數字常數|固定值,如數字、"日期"、"時間"...<br>"2021/01/1"、"12:30"| |文字|固定文字(前後加上雙引號)| | 參照位址 | 儲存格位址計算 = B2 * C2<br>跨工作表或檔案計算= <span style="color:red;font-weight:bold;">[</span> 檔案名稱 <span style="color:red;;font-weight:bold;">]</span> 工作表名稱 <span style="color:red;;font-weight:bold;">!</span> 參照位置 | |函數語法 | = 函數名稱 ( 條件1 , 條件2 , ... )| ### 2. 公式可用運算符號 | 符號 | 說明 | |-|-| |( ) |括號最內層括號公式先運算| |+-(正負號)、%、^(指數)、<br>*(乘)、/(除)、+(加)、-(減)|一般運算符號| |&<br>例: = "A"+"B" → 結果為 AB| 連結文字| |=、<>、<、>、>=、<=<br>例:<br> =5>3 結果為 TRUE<br>=5<3 結果為 FALSE |大小相比較| ### 3. 常用錯誤值類型 |顯示結果|說明| |-|-| |#DIV/0!|表示除式之分母為0} |#N/A|表示計算公式中有無效值,暫不計算此值。| |#VALUE!|表示公式中的運算式的類型錯誤時,#VALUE!錯誤值出現| |#NUM!|輸入數值有問題。<br>如:要求出現正數處卻出現負數或數值超出範圍| |#NAME|公式內有無法辨識之名稱或函數時。| |#NULL!|當指定二個範圍相交並無交集時。| |###|輸入內容,因儲存格寬度不足,其無法顯示結果| ### 4. 公式中「相對與絕對(固定)」的考量時機 <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式複製時才需考量 </span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 利用`F4`功能鍵,進行相對與絕對(固定)切換,【$】表示為絕對(固定)</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式【縱向】複製考量【列】是否固定 </span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 公式【橫向】複製考量【欄】是否固定</span> ## <span class="h2">B、常用函數</span> <span class="img100">![](https://hackmd.io/_uploads/HJT1kn4NK.png)</span> ## <span class="h2">C、處理小數位數之函數</span> <span class="ex">🔶 目的:處理小數位數之函數</span> | 函數|說明| | -|-| | INT( 數值 ) | 小數位數無條件<span style="color:#FF0000">捨去至整數值</span> | | Round( 數值 , 取捨位數 ) | 數值 <span style="color:#FF0000">四捨五入</span> 成為指定位數 | | RoundDown( 數值 , 取捨位數 ) | 數值<span style="color:#FF0000">無條件捨去</span>成為指定位數 | | RoundUp( 數值 , 取捨位數 ) | 數值<span style="color:#FF0000">無條件進位</span>成為指定位數 | 說明:位數取捨方式: &#12288;&#12288;&#12288;正值:表示小數位數取捨 &#12288;&#12288;&#12288;負值:表示整數位數取捨 ## <span class="h2">D、 IF 函數進行條件判斷</span> <span style="margin:auto; display:block; width:70%;">![](https://hackmd.io/_uploads/rJWiXlHVK.png)</span> ## <span class="h2">E、 乘積計算函數</span> <span style="margin:auto; display:block;">![](https://hackmd.io/_uploads/HJ86VlSEY.png)</span> ## <span class="h2">F、解決總和遇上篩選困擾</span> ### 1、 小計函數:Subtotal <span class="img100">![](https://hackmd.io/_uploads/BJePGHrNK.png)</span> ### 2、 會除錯的小計函數:Aggregate Aggregate函數比Subtotal函數還要強大,因面對忽略錯誤值、隱藏行和分類匯總嵌套,滿足Subtotal無法處理,但Aggregate滿足各種情況下的統計需求! <span class="img100">![](https://hackmd.io/_uploads/SkycGSSNY.png)</span> <span class="img100">![](https://hackmd.io/_uploads/SJxjGSrVt.png)</span> ## <span class="h2">G、Indirect函數</span> ``` = Indirect ( 定義名稱 ) ``` ## <span class="h2">H、一維查表函數</span> ### 1、Vlookup函數 依查詢值進行資料比對輸出相關資料之函數 :::success = Vlookup ( <span style="color:blue;">查詢值</span> , <span style="color:red;">查表範圍</span> , 回傳指定欄數 , <span style="color:#7030A0;">選項</span>) 說明 * <span style="color:blue;">查詢值若為『文字』,選項為False<br>查詢值若為『數值』,選項通常為True,且查表範圍應遞增</span> * <span style="color:blue;">查詢值應為查表範圍的第一欄 / 列</span> * <span style="color:red;">查表範圍若為『縱』向查詢,即為 Vlookup 函數<br>查表範圍若為『橫』向查詢,即為 Hlookup 函數</span> * <span style="color:#7030A0;">選項:</span> * <span style="color:#7030A0;">False,必須比對完合符合才回傳,查無資料以 “#N/A” 表示<br>True 或省略,比對回傳接近不超過查詢值的資料</span> * 查表函數,可搭配 Iferror 函數 ::: ### 2、Iferror函數 若公式計算結果發生錯誤時,想要執行工作 :::success = Iferror ( 判斷運算公式 , 發生錯誤欲執行工作) <br><span style="color:red;">評估的錯誤類型包括:<br>#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 和 #NULL!。</span> ::: ## <span class="h2">I、二維查表函數</span> ### 1、 Index函數 <span class="step">根據欄列號索引回傳查表範圍的值</span> ``` = Index ( 查表範圍 , 指定列數 , 指定欄數 ) ``` <span class="img40">![](https://hackmd.io/_uploads/BkEql5r4F.png)</span> ### 2、 Match函數 <span class="step">傳回搜尋項目於搜尋範圍中的相對位</span> ``` = Match ( 查詢值, 查表範圍 , 比對方式類型 ) 📌比對方式類型 1或省略:查詢範圍須『遞增』 0:查詢範圍不須事前排序,找到完全符合查詢值 -1:查詢範圍須『遞減』 ``` <span class="step">🔶 公式比擬</span> <span class="img80">![](https://hackmd.io/_uploads/rk3dE5B4t.png)</span> ## <span class="h2">J、符合條件計算</span> <span class="step">符合條件計算函數:Countif、Sumif、CountifS、SumifS、Averageif、AverageifS</span> <span class="img100">![](https://hackmd.io/_uploads/BJXnxAS4t.png)</span> ## <span class="h2">K、日期函數列表</span> ### 1. 傳回電腦系統日期及時間 #### <span class="h4">(1) 傳回電腦日期及時間</span> ``` =Now( ) ``` #### <span class="h4">(2) 傳回電腦日期</span> ``` =Today( ) ``` ### 2. 常用日期類函數 #### <span class="h4">(1) 傳回日期的年 / 月 / 日值</span> ``` = Year ( 日期 ) = Month( 日期 ) = Day ( 日期 ) ``` #### <span class="h4">(2) 組合年、月、日值為日期</span> ``` = Date ( 年 , 月 , 日 ) ※ 最終抓取值為數值 ``` #### <span class="h4">(3) 計算兩日期間的天數、月數或年數</span> ``` = Datedif ( 開始日 , 結束日 , " 單位 " ) ※ 說明: 單位:決定要傳回的單位,必須前後加上 " " Y代表滿幾年、M代表滿幾月、D代表滿幾日 MD代表兩日期間的天數差,忽略月和年 YM代表兩日期間的月數差,忽略日和年 YD代表兩日期間的天數差,忽略年 要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位 ``` #### <span class="h4">(4) 計算前後月的某一天</span> ``` = EDATE (開始日期,月數) ``` #### <span class="h4">(5) 計算當月的月底日期</span> ``` = EOMONTH (開始日期,月數) ``` #### <span class="h4">(6) 傳回自指定工作天數的日期</span> ``` = Workday ( 開始日期 , 日數 , 假日列表 ) ※ 延伸學習函數 Workday.intl ``` #### <span class="h4">(7) 傳回兩日期間的工作天數</span> ``` = Networkdays ( 開始日期 , 結束日期 , 假日列表 ) ※ 延伸學習函數 Networkdays.intl ``` #### <span class="h4">(8) Workday.intl 及 Networkdays.intl 注意事項 以Workday.intl函數為例:</span> ``` = Workday.intl ( 開始日期 , 日數, 週末代號 , 假日列表 ) ``` 其中,週末代號: &#12288;a. 若非為預設代號,其字串值為七個字元,<br>&#12288;&#12288;且字串代表一週內的一天,從星期一開始。 &#12288;b. 1:代表非工作日 / 0:代表工作日。 &#12288;c. 字串中僅允許字元 1 / 0,1111111 是無效字串。 <span style="color:#b71c1c; display:block;">※ 例如,0000011 代表週末為星期六和星期日。</span> ## <span class="h2">L、文字函數</span> ### 1. 函數列表 <a href="https://hackmd.io/_uploads/rya6hRBEt.png"><span class="img100">![](https://hackmd.io/_uploads/rya6hRBEt.png)</span></a> ### 2. Text 日期 結合 #### <span class="h4">(1) 日期代碼</span> <a href="https://hackmd.io/_uploads/S1msN9N4t.png"><span class="img100">![](https://hackmd.io/_uploads/S1msN9N4t.png)</span></a> #### <span class="h4">(2) 星期</span> <span class="ex" style="padding-left:2em">🔶 星期(中)</span> <span class="img60">![](https://hackmd.io/_uploads/BkGWMJ8Vt.png)</span><span class="ex" style="padding-left:2em">🔶 星期(英)</span> <span class="img60">![](https://hackmd.io/_uploads/ryg5GyLNY.png)</span> #### <span class="h4">(3) 季度</span> ``` = Int ((MONTH(日期)+2)/3) = RoundUP(MONTH(日期)/3,0) = Len(2 ^ MONTH(日期)) ``` <span class="img80">![](https://hackmd.io/_uploads/B1-ye2UWs.png)</span> <br><br><br><br><br><br><br><br><br><br><br>