Excel應用技巧秒查秒懂（486集視頻課+425個應用技巧）

目 錄

第 1 章　工作簿操作與打印 1

1.1 工作簿創建技巧 1

1．將 Excel 2021固定到開始屏幕  1

2．保存新工作簿  2

3．創建模板工作簿  3

4．保存工作簿模板  5

5．打開最近使用的工作簿  6

6．固定最近常用的工作簿  7

1.2 工作簿優化技巧 8

1．一鍵快速選擇常用命令  8

2．為工作簿定製默認字體格式  9

3．為工作簿定製默認表格數量  10

4．為工作簿定製常用的功能按鈕  11

5．為工作簿定製默認的保存路徑  12

6．快速恢復工作簿默認狀態  13

例 1：工作表中的行號和列標恢復法 13

例 2：取消鼠標中鍵的智能縮放功能 14

7．啟動 Excel 時自動打開某個工作簿 14

1.3 工作簿打印技巧 15

1．打印表格的指定頁  15

2．只打印表格的指定區域  16

3．一次性打印多張工作表  17

4．超寬工作表打印技巧  18

5．在表格每一頁顯示指定行列  19

6．按數據透視表的字段分項打印  21

7．按數據透視表的篩選字段分項打印  22

8．分頁打印分類匯總數據表  24

9．將打印內容顯示到紙張中間  25

1.4 工作簿保護技巧 27

1．加密保護重要工作簿  27

2．設置工作表修改權限密碼  28

3．保護工作表的指定區域  29

4．清除工作簿中的個人信息  30

5．隱藏“最近使用的工作簿”  31

6．阻止文檔中的外部內容  32 

1.5 工作簿打包技巧 33 

1．將工作簿導出為 PDF 文件  33 

2．將數據透視表輸出為報表  35 

3．將圖表保存為圖片  35 

4．將 Excel 表格或圖表應用到 Word 報告中  36 

5．將 Excel 表格或圖表應用到 PPT 幻燈片  37 

 

 

第 2 章 工作表基本操作  39 

2.1 工作表操作 39 

1．添加新工作表  39 

2．重命名工作表  39 

3．重設工作表標簽顏色  40 

4．快速移動或復制工作表  41 

5．跨工作簿移動復制工作表  42 

6．將多張工作表建立為一個工作組  43 

7．隱藏包含重要數據的工作表  44 

8．隱藏工作表標簽  44 

2.2 工作表數據查看 45 

1．查看數據時凍結窗格  46 

2．在任意位置拆分表格  47 

3．縮放表格窗口的顯示比例  47 

4．並排比較工作表  48 

2.3 導入外部工作表 49 

1．導入 Excel 表格數據 50 

2．導入文本文件數據  51 

3．導入網頁中的表格  53 

 

第３章 數據輸入與填充 55 

3.1 數據輸入技巧 55 

1．輸入生僻字的技巧  55 

2．運用“墨跡公式”手寫數學公式  56 

3．輸入身份證號碼或長編碼  58 

4．設置指定的日期格式  59 

5．輸入大寫人民幣金額  60 

6．輸入時在指定位置強制換行  61 

7．記憶式輸入提高數據錄入效率  62 

8．讓數據自動保留兩位小數碼數  64 

3.2 數據填充技巧 65

1．在連續單元格中填充相同數據  65 

2．在不連續單元格中填充相同數據  66 

3．在大範圍區域中填充相同數據  66 

4．在多張工作表中填充相同數據  67 

3.3 自動填充選項技巧 68 

1．快速填充遞增序號  69 

2．快速填充相同日期  70 

3．快速填充工作日日期  70 

4．按分鐘數遞增填充  71 

5．快速填充大批量序號  71 

6．自定義填充序列  72 

7．解決填充柄消失問題  74 

 

第 4 章 數據定位應用  76 

1．定位大範圍區域  76 

2．超大範圍公式復制的辦法  77 

3．忽略非空單元格填充數據  78 

4．查看公式引用的所有單元格  79 

5．快速定位合並的單元格  80 

6．自定義視圖快速定位區域  81 

7．定位設置數據驗證的單元格  83 

8．核對兩列數據是否一致  84 

 

第 5 章 數據查找替換與復制粘貼  85 

5.1 數據的查找和替換 85 

1．為替換後的內容設置格式  85 

2．單元格匹配替換數據  87 

3．查找數據應用通配符  88 

4．替換小於指定數值的數據  89 

5．快速選中格式相同的單元格  91 

5.2 復制粘貼及選擇性粘貼 92 

1．正確復制篩選後的數據  93 

2．僅復制單元格的格式  94 

3．粘貼數據匹配目標區域格式  95 

4．同增同減數據時忽略空單元格  96 

5．讓粘貼數據隨源數據自動更新  98 

6．鏈接 Excel 與 Word 中的表格  99 

 

第 6 章 規範表格結構與數據 100 

6.1 表格結構調整 100

1．一次性插入多行、多列  100 

2．一次性隔行插入空行  101 

3．每隔 4 行（或任意指定行數）插入空行  102 

4．整行整列數據調換  103 

5．刪除有空白單元格的所有行  105 

6.2 規範表格數據 106 

1．刪除重復值  106 

2．拆分不同類型的數據  107 

3．解決分類匯總的合並單元格問題  109 

4．巧用 Word 批量清除指定字符  111 

6.3 處理無法計算的數據 113 

1．為什麽明明顯示的是數值卻不能計算  113 

2．處理不規範的無法計算的日期  114 

3．一次性處理文本中所有空格  116 

4．謹防空值陷阱  117 

 

 

第7 章 數據驗證與自定義格式 120 

7.1 設置數據驗證規範輸入 120 

1．建立可選擇輸入序列  120 

2．限制輸入的數據範圍  121 

3．限制數據的最大值  123 

4．限制輸入指定範圍日期  123 

5．圈釋無效數據  125 

6．快速應用相同的數據驗證  126 

7.2 公式在數據驗證中的應用 127 

1．避免輸入重復值  127 

2．限制單元格只能輸入文本  128 

3．避免輸入文本數據  129 

4．限制輸入空格  130 

5．限定單元格內必須包含指定內容  132 

6．禁止出庫量大於庫存量  133 

7．輕松製作二級下拉菜單  134 

7.3 自定義單元格格式 137 

1．自定義日期數據格式  139 

2．在考勤表中只顯示日  140 

3．為數據批量添加重量單位  141 

4．數據部分重復時的快捷輸入技巧  142 

5．輸入 1 顯示“√”，輸入 0 顯示“×”  143 

6．根據正負值自動顯示前綴文字  144 

7．約定數據寬度不足時用 0 補齊  145

8．設置數據以“萬元”為顯示單位  146 

9．根據數值大小顯示不同的顏色  148 

10．設置業績小於 5 萬元時顯示“業績不達標”  149 

11．隱藏單元格中所有或部分數據  150 

 

第 8 章 表格美化設置 151 

8.1 表格外觀美化 151 

1．合並居中顯示表格的標題  151 

2．設置標題分散對齊效果  152 

3．為表格標題設置下畫線  153 

4．讓表格部分數據豎向顯示  154 

5．批量調整行高、列寬  155 

6．精確調整行高、列寬  157 

7．為表格定製邊框線條  158 

8．快速套用表格樣式  159 

8.2 表格格式美化 160 

1．設置文字對齊方式  161 

2．使用藝術字標題  161 

3．自定義藝術字樣式  162 

4．快速設置註釋文本樣式  164 

5．自定義單元格樣式  165 

6．“格式刷”快速復制格式  167 

8.3 表格頁面美化 168 

1．設置頁面背景  168 

2．調整默認頁邊距  169 

3．在任意位置分頁  170 

4．應用頁眉  172 

5．應用頁腳  173 

8.4 用圖形、圖片、圖示來美化表格 174 

1．插入並調整圖片  174 

2．裁剪圖片  176 

3．刪除圖片背景  178 

4．調整圖片色調  180 

5．快速應用圖片樣式  181 

6．快速應用圖標  181 

7．應用圖片版式  182 

8．應用形狀  184 

9．為形狀添加文本  184 

10．設置形狀邊框線條  185

11．設置形狀填充效果  186 

12．設置對象疊放次序  188 

13．應用 SmartArt 圖形  188 

14．SmartArt 圖形不夠時自定義添加  189 

15．更改 SmartArt 圖形佈局和樣式  190 

 

第 9 章 條件格式應用 193 

9.1 常用條件格式類型 193 

1．突出顯示大於指定數值的單元格  193 

2．突出顯示介於指定數值的單元格  194 

3．設定包含某文本時顯示特殊格式  195 

4．按發生日期顯示特殊格式  196 

5．重復值（唯一值）顯示特殊格式  197 

6．排名靠前 / 後 N 位顯示特殊格式  198 

7．高 / 低於平均值時顯示特殊格式 199 

8．設定排除某文本時顯示特殊格式  200 

9．設定以某文本開頭時顯示特殊格式  202 

9.2 使用圖形的條件格式 203 

1．應用數據條開關  204 

2．給優秀成績插紅色旗幟  207 

3．升降箭頭標識數據  209 

9.3 使用公式建立規則 210 

1．自動標識周末日期  210 

2．自動提醒值班日  212 

3．標記出優秀學生姓名  213 

4．突出顯示每行的最大 / 最小值 214 

5．設置多個條件格式規則  216 

6．自動標識指定值班人  218 

7．查找數據後自動標記顏色  220 

 

第 10 章 數據篩選應用 223 

10.1 數值篩選  223 

1．篩選大於特定值的記錄  223 

2．篩選介於指定值之間的記錄  224 

3．篩選小於平均值的記錄  225 

4．篩選出指定時間區域的記錄  225 

5．篩選出以指定數字開頭的記錄  226

10.2 文本篩選  227 

1．模糊篩選獲取同一類型的數據  227 

2．篩選出開頭不包含某文本的所有記錄  228 

3．利用搜索篩選器將篩選結果中的某類數據再次排除  229 

10.3 日期篩選  230 

1．將日期按年（月、周）篩選  230 

2．按季度篩選數據記錄  232 

3．按任意日期區間篩選  233 

4．篩選日期時不按年月日分組  234 

10.4 高級篩選  235 

1．使用高級篩選提取指定數據  235 

2．篩選出不重復的記錄  236 

3．根據目標單元格進行快速篩選  237 

4．對雙行標題列表進行篩選  237 

5．使用“表”功能篩選並匯總數據  238 

6．高級篩選實現一對多查詢  240 

7．高級篩選出同時滿足多個條件的記錄  241 

8．高級篩選出滿足多個條件中一個條件的所有記錄  243 

9．在高級篩選條件區域中使用通配符  244 

 

第 11 章 數據排序應用 246 

11.1 簡單排序  246 

1．按列排序數據  246 

2．按行排序數據  246 

3．按漢字筆畫排序  247 

4．按單元格顏色排序  249 

5．按單元格圖標排序  250 

11.2 按條件排序  251 

1．按多條件排序  251 

2．自定義排序規則  252 

3．應用函數隨機排序  253 

4．恢復排序前的數據  254 

第 12 章 數據透視表應用 256 

12.1 按步驟創建數據透視表  256 

1．整理好數據源表格  256 

2．兩步創建數據透視表  257 

3．為透視表添加字段  258 

4．重新設置值的匯總方式  259 

5．添加多個值匯總方式  260

6．重新設置值的顯示方式  261 

7．應用外部數據表  262 

8．重新更改數據透視表的數據源  263 

9．更新數據時要刷新數據透視表  264 

10．創建自動更新的數據透視表  265 

11．美化數據透視表樣式  267 

12.2 數據透視表應用實例  268 

1．統計指定時間段訂單  268 

2．應用多張數據透視表  271 

3．按百分比統計各店鋪數據  272 

4．應用多張數據透視圖  273 

5．應用日程表查看數據  275 

6．應用切片器查看數據  276 

7．統計銷售額占分類匯總的百分比  278 

8．按日累計銷售額  279 

9．自定義公式求解各業務員獎金  280 

10．顯示項的明細數據  281 

11．篩選銷售額在前 5 名的產品  282 

12．篩選介於指定日期區間的匯總值  283 

13．按周分組匯總產量  284 

14．統計指定分數區間的人數  284 

15．按分數區間將成績分為“優”“良”“差”等級  286 

16．將支出金額按半年匯總  287 

12.3 設置數據透視表、圖格式  288 

1．重設數據的顯示格式  288 

2．更改透視表的報表佈局  289 

3．添加空行讓分級顯示更清晰  290 

4．在數據透視圖中篩選查看部分數據  290 

 

第 13 章 圖表應用 292 

13.1 按步驟創建圖表  292 

1．整理數據源表格  292 

2．瞭解常用圖表類型  294 

3．瞭解圖表佈局元素  295 

4．圖表設計應當以簡潔為主  296 

5．兩步快速創建最佳圖表  297 

6．選擇部分數據源創建圖表  298 

7．快速向圖表中添加新數據  299 

8．快速更改圖表為另一類型  299 

9．正確添加數據標簽  301

10．添加值標簽以外的數據標簽  301 

11．為餅圖添加百分比數據標簽  302 

12．隱藏元素以簡化圖表  303 

13．快速美化圖表  304 

14．將圖表保存為模板  305 

13.2 其他圖表類型應用  307 

1．展示數據層級關系的旭日圖  307 

2．展示數據累計的瀑布圖  308 

3．展示數據分佈區域的直方圖  309 

4．展示數據中最重要因素的排列圖  311 

5．展示數據環節的漏鬥圖  311 

6．展示重要數據的復合條餅圖  312 

7．展示數據統計的迷你圖  314 

13.3 圖表編輯技巧應用  315 

1．快速改變圖表展示重點  315 

2．解決坐標軸被覆蓋的問題  316 

3．用垂直軸分割圖表  317 

4．設置數值刻度單位  318 

5．解決刻度值次序顛倒問題  320 

6．根據不連續日期建立柱形圖  321 

7．突出顯示圖表中的極值  321 

8．左右對比的條形圖  323 

9．兩項指標比較的溫度計圖  325 

10．添加趨勢線預測數據  327 

13.4 公式輔助圖表應用  328 

1．製作柱形圖均值比較線  328 

2．為柱形圖添加升降箭頭  330 

3．為柱形圖添加動態排序  336 

4．在條形圖中顯示進度  337 

13.5 圖表格式應用技巧  339 

1．設置折線圖數據點格式  339 

2．圖形、圖片、文本框輔助設計  343 

3．製作南丁格爾玫瑰圖  345 

 

第 14 章 高級數據分析 348 

14.1 模擬運算與單變量求解  348 

1．單變量求解示例  348 

2．單變量模擬運算示例  349 

3．雙變量模擬運算示例  350 

14.2 規劃求解  351

1．加載規劃求解功能  352 

2．建立最佳報價方案  353 

3．建立合理的生產方案  355 

14.3 方案管理器  358 

1．創建方案分析模型  358 

2．建立方案管理器  359 

3．應用方案管理器  361 

14.4 分析工具庫  362 

1．單因素方差分析——分析學歷層次對綜合考評能力的影響  363 

2．雙因素方差分析——分析何種因素對生產量有顯著性影響  364 

3．相關系數——分析產量和施肥量是否有相關性  366 

4．協方差——分析數據的相關性  367 

5．描述統計——分析學生成績的穩定性  368 

6．移動平均——使用移動平均預測銷售量  369 

7．移動平均——指數平滑法預測產品的生產量  371 

8．回歸——一元線性回歸預測  372 

9．回歸——多元線性回歸預測  374 

10．排位與百分比排位——對學生成績進行排位 375 

11．抽樣——從數據庫中快速抽取樣本 376 

 

第 15 章 數據合並計算與分類匯總 378 

15.1 合並計算  378 

1．合並計算統計總費用支出  378 

2．合並計算時應用通配符  380 

3．合並計算核對兩列數據  382 

4．統計各部門平均工資  383 

5．統計員工值班次數  386 

6．分析各班成績的穩定程度  386 

15.2 分類匯總  388 

1．創建單級分類匯總  388 

2．顯示分類匯總結果  390 

3．添加多種匯總方式  390 

4．創建多級分類匯總  391 

5．製作帶頁小計的工作表  393 

6．復制分類匯總統計結果  395 

 

第 16 章 瞭解公式與函數 397 

16.1 公式操作技巧  397 

1．手動編輯公式  397 

2．復制公式完成批量計算  398

3．引用相對數據源  400 

4．引用絕對數據源  401 

5．引用當前工作表之外的單元格  402 

6．定義名稱簡化公式引用  403 

16.2 公式數組運算技巧  405 

1．多個單元格數組公式  406 

2．單個單元格數組公式  406 

16.3 函數應用技巧  408 

1．函數的優勢作用  408 

2．函數應用的結構  409 

3．函數參數的編寫技巧  410 

4．Excel 搜索功能快速學習函數 413 

5．利用提示列表快速選擇函數  413 

6．多種函數嵌套應用  414 

7．利用“公式求值”理解公式  415 

 

第 17 章 邏輯判斷與數學函數應用 417 

17.1 邏輯判斷函數  417 

1．AND 函數  417 

2．OR 函數  419 

3．IF 函數  420 

例 1：根據銷售額返回提成率（IF 函數嵌套）  420 

例 2：只為滿足條件的員工調整薪資（IF 函數嵌套其他函數）  421 

4．IFS 函數  423 

例 1：根據銷售額返回提成率（IFS 函數）  423 

例 2：根據工齡和職位統計年終獎  424 

例 3：根據分數進行等級評定  425 

17.2 求和函數應用  425 

1．SUM（對給定的數據區域求和）  426 

例 1：用“自動求和”按鈕快速求和  427 

例 2：對一個數據區域求和  428 

例 3：求排名前三的產量總和  429 

2．SUMIF（按照指定條件求和）  430 

例 1：統計各銷售員的銷售業績總和  430 

例 2：統計指定時段的總銷售額  431 

例 3：用通配符對某一類數據求和  432 

例 4：用通配符求所有車間工人的工資和  433 

3．SUMIFS（對滿足多重條件的單元格求和） 434 

例 1：統計指定店面中指定品牌的總銷售額  434 

例 2：按月匯總出庫量  435

4．SUMPRODUCT（將數組間對應的元素相乘並返回乘積之和） 437 

例 1：計算商品的折後總銷售額  440 

例 2：滿足多條件時求和運算  440 

例 3：統計周末的總營業額  441 

例 4：匯總某兩種產品的總銷售額  442 

例 5：統計大於 12 個月的賬款 443 

例 6：統計指定部門工資大於指定數值的人數  444 

17.3 數據的舍入  445 

1．INT（將數字向下舍入到最接近的整數）  445 

例：對平均銷量取整  446 

2．ROUND（對數據進行四捨五入） 447 

例：為超出完成量的計算獎金  448 

3．ROUNDUP（遠離 0 值向上舍入數值）  449 

例 1：計算材料長度（材料只能多不能少）  450 

例 2：計算物品的快遞費用  450 

4．ROUNDDOWN（靠近 0 值向下舍入數值）  451 

例：折後金額舍尾取整  452 

5．RANDARRAY（返回一組隨機數字）  452 

例：返回一組 100 ～ 1000 之間的隨機整數  453 

6. SWITCH（根據表達式的返回值匹配結果） 454 

例 1：只安排星期一至星期三值班  454 

例 2：提取產品的規格容量  455 

 

第 18 章 統計函數應用 457 

18.1 求平均值計算  457 

1．AVERAGE（計算算術平均值）  457 

例 1：計算月平均支出費用  458 

例 2：計算平均成績（將空白單元格計算在內）  459 

例 3：實現平均分數的動態計算  460 

2．AVERAGEIF（返回滿足條件的平均值） 461 

例 1：按班級統計平均分  462 

例 2：計算平均值時排除 0 值  463 

例 3：使用通配符對某一類數據求平均值  463 

例 4：排除部分數據計算平均值  464 

3．AVERAGEIFS（返回滿足多重條件的平均值）  465 

例：計算指定班級指定性別的平均分鐘數  466 

4．GEOMEAN（返回幾何平均值）  467 

例：判斷兩組數據的穩定性  468 

5．HARMEAN（返回數據集的調和平均值）  468 

例：計算固定時間內幾位學生的平均解題數  469

6．TRIMMEAN（截頭尾返回數據集的平均值）  469 

例：通過 10 位評委打分計算選手的最後得分  470 

18.2 按條件計數統計  471 

1．COUNT（統計含有數字的單元格個數）  471 

例 1：統計出席會議的人數  471 

例 2：統計 1 月份獲取交通補助的總人數  472 

例 3：統計出某一項成績為滿分的人數  473 

2．COUNTIF（統計滿足給定條件的單元格的個數）  474 

例 1：統計指定學歷的人數  474 

例 2：統計成績表中分數大於 90 分的人數 475 

3．COUNTIFS（統計同時滿足多個條件的單元格的個數）  476 

例 1：統計指定部門銷量達標人數  476 

例 2：統計指定職位男、女應聘人數  477 

4．COUNTBLANK（計算空白單元格的數目）  478 

例：檢查應聘者信息填寫是否完善  479 

18.3 最大 / 最小值計數  480 

1．MAX/MIN（返回數據集的最大 / 最小值）  480 

2．MAXIFS/MINIFS（返回給定條件指定單元格的最大 / 最小值）481 

例 1：求指定班級的最高分  481 

例 2：忽略 0 值求出最低分數  482 

3．LARGE（返回表格或區域中的值或值的引用）  483 

例 1：返回排名前 3 的銷售額  483 

例 2：分班級統計各班級的前 3 名的成績  484 

4．SMALL（返回某數據集的某個最小值）  485 

例：返回倒數第一名的成績與對應的姓名  486 

18.4 排位統計  487 

1．MEDIAN（返回中位數）  487 

例：返回一個數據序列的中間值  488 

2．RANK.EQ（返回數組的最高排位）  489 

例 1：對銷售業績進行排名  489 

例 2：對不連續的數據進行排名  490 

3．RANK.AVG（返回數字列表中的排位）  491 

例：對員工考核成績排名次  491 

4．QUARTILE.INC（返回四分位數）  492 

例：四分位數偏度系數  493 

5．PERCENTILE.INC（返回第 k 個百分點值）  495 

例：返回一組數據 k 百分點處的值  495 

 

第 19 章 文本處理函數應用 497 

19.1 查找指定字符的位置  497

1．FIND（查找指定字符在字符串中的位置）  497 

例 1：找出指定文本所在位置  497 

例 2：提取指定位置的字符（配合文本提取函數）  498 

例 3：查找位置是為了輔助提取（從產品名稱中提取規格）  499 

2．SEARCH（查找字符串的起始位置）  500 

19.2 提取指定文本  501 

1．LEFT（按指定字符數從最左側提取字符串）  501 

例 1：提取分部名稱  501 

例 2：從商品全稱中提取產地  502 

例 3：根據產品名稱進行一次性調價  503 

2．RIGHT（按指定字符數從最右側提取字符串）  503 

例：從文字與金額合並顯示的字符串中提取金額數據  504 

3．MID（從任意位置提取指定字符數的字符串）  504 

例 1：從產品名稱中提取貨號  505 

例 2：提取括號內的字符串  506 

19.3 替換指定文本  507 

1．REPLACE（用指定的字符和字符數替換文本字符串中的 

部分文本）  507 

例：對產品名稱批量更改  507 

2．SUBSTITUTE（替換舊文本）  508 

例 1：快速批量刪除文本中的多餘空格  508 

例 2：根據報名學員統計人數  509 

19.4 文本格式轉換  510 

1．TEXT（設置數字格式並將其轉換為文本） 510 

例 1：返回值班日期對應的星期數  511 

例 2：計算加班時長並顯示為“* 時 * 分”形式  512 

例 3：解決日期計算返回日期序列號問題  513 

例 4：從身份證號碼中提取性別  514 

2．DOLLAR（四捨五入數值，並添加千分位符號和“$”符號） 515 

例：將銷售額轉換為美元格式  516 

3．RMB（四捨五入數值，並添加千分位符號和“￥”符號）  516 

例：將銷售額轉換為人民幣格式  516 

4．UPPER（將文本轉換為大寫形式）  517 

例：將文本轉換為大寫形式  517 

5．VALUE（將文本數字轉換成數值）  518 

例：將文本型數字轉換為可計算的數值  518 

19.5 其他常用文本函數  519 

1．CONCAT（合並兩個或多個文本字符串）  519 

例 1：將分散兩列的數據合並為一列  519 

例 2：合並面試人員的總分數與錄取情況  520

2．LEN（返迴文本字符串的字符數量）  521 

例：檢測員工編號位數是否正確  521 

3．EXACT（比較兩個文本字符串是否完全相同）  522 

例：比較兩次測試數據是否完全一致  522 

4．LET（可以定義名稱）  523 

例：將表達式的名稱定義為 X  524 

5．TEXTJOIN（將多個區域中的文本組合起來）  525 

例 1：獲取離職人員的全部名單  526 

例 2：獲取各職位的錄取人員名單  527 

 

第 20 章 日期與時間函數應用 529 

20.1 返回系統當前日期  529 

1．NOW（返回當前日期與時間）  529 

例：計算活動剩餘時間  529 

2．TODAY（返回當前的日期）  530 

例：判斷會員是否可以升級  530 

20.2 構建與提取日期  531 

1．DATE（構建標準日期） 531 

例：將不規範的日期轉換為標準日期  532 

2．YEAR（返回某日對應的年份）  533 

例：計算出員工的工齡  533 

3．MONTH（返回日期中的月份）  535 

例 1：判斷是否為本月的應收賬款  535 

例 2：統計指定月份的總庫存量  536 

4．DAY（返回日期中的天數）  536 

例 1：計算本月上旬的銷量  537 

例 2：按本月缺勤天數計算扣款金額  538 

5．WEEKDAY（返回指定日期對應的星期數）  538 

例 1：快速返回加班日期對應星期幾  539 

例 2：判斷加班日期是工作日還是雙休日  539 

例 3：匯總周日總銷售額  540 

6．WEEKNUM（返回日期對應 1 年中的第幾周）  541 

例：計算培訓課程的歷時周數  541 

7．EOMONTH（返回指定月份前 / 後幾個月最後一天的序列號） 542 

例 1：根據促銷開始時間計算促銷天數  543 

例 2：計算食品的有效保質日期  544 

20.3 日期計算  544 

1．DATEDIF（用指定的單位計算起始日和結束日之間的天數） 544 

例：計算借款逾期月份  546 

2．DAYS360（按照一年 360 天的算法計算兩個日期間相差

的天數）  547 

例：計算借款的還款剩餘天數  548 

3．EDATE（計算間隔指定月份數後的日期）  548 

例 1：計算借款的到期日期  549 

例 2：根據出生日期與性別計算退休日期  549 

20.4 關於工作日的計算  550 

1．WORKDAY（獲取間隔若乾工作日後的日期）  550 

例：根據項目開始日期計算項目結束日期  551 

2．WORKDAY.INTL 函數  551 

例：根據項目各流程所需工作日計算項目結束日期  553 

3．NETWORKDAYS（計算兩個日期間的工作日）  554 

例：計算臨時工的實際工作天數  554 

4．NETWORKDAYS.INTL 函數  555 

例：計算臨時工的實際工作天數（指定只有周一為休息日）  556 

20.5 時間函數  556 

1．HOUR（返回時間值的小時數）  557 

例：判斷商場活動的整點時間區間  557 

2．MINUTE（返回時間值的分鐘數）  558 

例：統計比賽分鐘數  558 

3．SECOND（返回時間值的秒數）  559 

例：統計比賽歷時秒數  559 

 

第 21 章 查找與引用函數應用 561 

21.1 返回行列號的 ROW 與 COLUMN 561 

1．ROW（返回引用的行號）  561 

例：生成批量序列  562 

2．COLUMN（返回引用的列號）  564 

21.2 LOOK 類函數  564 

1．VLOOKUP（在數組第 1 列中查找並返回指定列中同一位置 

的值）  565 

例 1：按編號查詢員工的工資明細  566 

例 2：代替 IF 函數的多層嵌套（模糊匹配）  568 

例 3：應用通配符查找數據  569 

例 4：VLOOKUP 應對多條件匹配  570 

2．LOOKUP（查找並返回同一位置的值）  571 

例 1：LOOKUP 模糊查找  573 

例 2：利用 LOOKUP 模糊查找動態返回最後一條數據 574 

例 3：通過簡稱或關鍵字模糊匹配  575 

例 4：LOOKUP 滿足多條件查找  576 

3．HLOOKUP（查找數組的首行，並返回指定單元格的值）  577

例：根據不同的返利率計算各筆訂單的返利金額  578 

4．XLOOKUP（查找區域或數組返回對應項）  580 

例 1：XLOOKUP 實現多行多列查找  582 

例 2：指定錯誤值返回空白  583 

例 3：XLOOKUP 應對多條件匹配  584 

21.3 經典組合 INDEX+MATCH 585 

1．MATCH（查找並返回指定值所在位置） 585 

例：用 MATCH 函數判斷某數據是否包含在另一組數據中  586 

2．INDEX（從引用或數組中返回指定位置的值）  587 

例 1：MATCH+INDEX 的搭配使用 588 

例 2：查詢總金額最高的銷售員（逆向查找）  589 

例 3：查找報銷次數最多的員工  590 

3．XMATCH（查找並返回值所在位置）  591 

例：XMATCH 搭配 INDEX  592 

21.4 Excel 新增的查找函數  593 

1．SORT（對區域或數組排序） 593 

例：數據動態排序  594 

2．SORTBY（多條件排序）  595 

例：多條件排序  596 

3．UNIQUE（提取唯一值）  596 

例：按單列去重復值  597 

4．FILTER（按條件篩選數據）  597 

例 1：單條件查詢  598 

例 2：多條件查詢  598 

 

第 22 章 財務函數應用 600 

22.1 本金和利息計算  600 

1．PMT（返回貸款的每期還款額）  600 

例 1：計算貸款的每年償還額  600 

例 2：按季（月）還款時計算每期應償還額  601 

2．PPMT（返回給定期間內的本金償還額） 601 

例 1：計算指定期間的本金償還額  602 

例 2：計算第 1 個月與最後一個月的本金償還額  602 

3．IPMT（返回給定期間內的利息償還額）  603 

例 1：計算每年償還額中的利息金額  603 

例 2：計算每月償還額中的利息金額  603 

4．ISPMT（等額本金還款方式下的利息計算）  604 

例：計算投資期內需支付的利息額  604 

22.2 投資計算  605 

1．FV（返回某項投資的未來值）  605

例 1：計算投資的未來值  606 

例 2：計算某項保險的未來值  606 

例 3：計算住房公積金的未來值  607 

2．FVSCHEDULE（計算投資在變動或可調利率下的未來值） 607 

例：計算投資在可變利率下的未來值  607 

3．PV（返回投資的現值）  608 

例：判斷購買某種保險是否合算  608 

4．NPV（返回一筆投資的凈現值）  609 

例：計算一筆投資的凈現值  609 

5．XNPV（返回一組不定期現金流的凈現值）  610 

例：計算出一組不定期盈利額的凈現值  610 

6．NPER（返回某筆投資的總期數） 611 

例 1：計算某筆貸款的清還年數  611 

例 2：計算一筆投資的期數  611 

7．EFFECT 函數（計算實際年利率）  612 

例 1：計算投資的實際利率與本利和  612 

例 2：計算信用卡的實際年利率  613 

8．NOMINAL 函數（計算名義年利率）  613 

例：根據實際年利率計算名義年利率  614 

22.3 償還率函數  614 

1．IRR（計算內部收益率）  614 

例：計算一筆投資的內部收益率  614 

2．MIRR（計算修正內部收益率） 615 

例：計算不同利率下的修正內部收益率  615 

3．XIRR（計算不定期現金流的內部收益率）  616 

例：計算一組不定期現金流的內部收益率  616 

4．RATE（返回年金的各期利率） 617 

例：計算一筆投資的收益率  617 

22.4 資產折舊計算  617 

1．SLN（直線法計提折舊值）  617 

例 1：直線法計算固定資產的每年折舊額  618 

例 2：直線法計算固定資產的每月折舊額  618 

2．SYD（年數總和法計提折舊值）  619 

例：年數總和法計算固定資產的年折舊額  620 

3．DB（固定餘額遞減法計算折舊值）  621 

例：固定餘額遞減法計算固定資產的每年折舊額  621 

4．DDB（雙倍餘額遞減法計算折舊值）  622 

例：雙倍餘額遞減法計算固定資產的每年折舊額  623 

5．VDB（返回指定期間的折舊值）  624 

例：計算任意指定期間的資產折舊值  624