Excel應(yīng)用技巧秒查秒懂（486集視頻課+425個應(yīng)用技巧）

第 1 章　工作簿操作與打印 1
1.1 工作簿創(chuàng)建技巧 1
1．將 Excel 2021固定到開始屏幕  1
2．保存新工作簿  2
3．創(chuàng)建模板工作簿  3
4．保存工作簿模板  5
5．打開最近使用的工作簿  6
6．固定最近常用的工作簿  7
1.2 工作簿優(yōu)化技巧 8
1．一鍵快速選擇常用命令  8
2．為工作簿定制默認(rèn)字體格式  9
3．為工作簿定制默認(rèn)表格數(shù)量  10
4．為工作簿定制常用的功能按鈕  11
5．為工作簿定制默認(rèn)的保存路徑  12
6．快速恢復(fù)工作簿默認(rèn)狀態(tài)  13
例 1：工作表中的行號和列標(biāo)恢復(fù)法 13
例 2：取消鼠標(biāo)中鍵的智能縮放功能 14
7．啟動 Excel 時自動打開某個工作簿 14
1.3 工作簿打印技巧 15
1．打印表格的指定頁  15
2．只打印表格的指定區(qū)域  16
3．一次性打張工作表  17
4．超寬工作表打印技巧  18
5．在表格每一頁顯示指定行列  19
6．按數(shù)據(jù)透視表的字段分項打印  21
7．按數(shù)據(jù)透視表的篩選字段分項打印  22
8．分頁打印分類匯總數(shù)據(jù)表  24
9．將打印內(nèi)容顯示到紙張中間  25
1.4 工作簿保護技巧 27
1．加密保護重要工作簿  27
2．設(shè)置工作表修改權(quán)限密碼  28
3．保護工作表的指定區(qū)域  29
4．清除工作簿中的個人信息  30
5．隱藏“最近使用的工作簿”  31
6．阻止文檔中的外部內(nèi)容  32 
1.5 工作簿打包技巧 33 
1．將工作簿導(dǎo)出為 PDF 文件  33 
2．將數(shù)據(jù)透視表輸出為報表  35 
3．將圖表保存為圖片  35 
4．將 Excel 表格或圖表應(yīng)用到 Word 報告中  36 
5．將 Excel 表格或圖表應(yīng)用到 PPT 幻燈片  37 
第 2 章 工作表基本操作  39 
2.1 工作表操作 39 
1．添加新工作表  39 
2．重命名工作表  39 
3．重設(shè)工作表標(biāo)簽顏色  40 
4．快速移動或復(fù)制工作表  41 
5．跨工作簿移動復(fù)制工作表  42 
6．將多張工作表建立為一個工作組  43 
7．隱藏包含重要數(shù)據(jù)的工作表  44 
8．隱藏工作表標(biāo)簽  44 
2.2 工作表數(shù)據(jù)查看 45 
1．查看數(shù)據(jù)時凍結(jié)窗格  46 
2．在任意位置拆分表格  47 
3．縮放表格窗口的顯示比例  47 
4．并排比較工作表  48 
2.3 導(dǎo)入外部工作表 49 
1．導(dǎo)入 Excel 表格數(shù)據(jù) 50 
2．導(dǎo)入文本文件數(shù)據(jù)  51 
3．導(dǎo)入網(wǎng)頁中的表格  53 
第３章 數(shù)據(jù)輸入與填充 55 
3.1 數(shù)據(jù)輸入技巧 55 
1．輸入生僻字的技巧  55 
2．運用“墨跡公式”手寫數(shù)學(xué)公式  56 
3．輸入身份證號碼或長編碼  58 
4．設(shè)置指定的日期格式  59 
5．輸入大寫人民幣金額  60 
6．輸入時在指定位置強制換行  61 
7．記憶式輸入提高數(shù)據(jù)錄入效率  62 
8．讓數(shù)據(jù)自動保留兩位小數(shù)位數(shù)  64 
3.2 數(shù)據(jù)填充技巧 65
1．在連續(xù)單元格中填充相同數(shù)據(jù)  65 
2．在不連續(xù)單元格中填充相同數(shù)據(jù)  66 
3．在大范圍區(qū)域中填充相同數(shù)據(jù)  66 
4．在多張工作表中填充相同數(shù)據(jù)  67 
3.3 自動填充選項技巧 68 
1．快速填充遞增序號  69 
2．快速填充相同日期  70 
3．快速填充工作日日期  70 
4．按分鐘數(shù)遞增填充  71 
5．快速填充大批量序號  71 
6．自定義填充序列  72 
7．解決填充柄消失問題  74 
第 4 章 數(shù)據(jù)定位應(yīng)用  76 
1．定位大范圍區(qū)域  76 
2．超大范圍公式復(fù)制的辦法  77 
3．忽略非空單元格填充數(shù)據(jù)  78 
4．查看公式引用的所有單元格  79 
5．快速定位合并的單元格  80 
6．自定義視圖快速定位區(qū)域  81 
7．定位設(shè)置數(shù)據(jù)驗證的單元格  83 
8．核對兩列數(shù)據(jù)是否一致  84 
第 5 章 數(shù)據(jù)查找替換與復(fù)制粘貼  85 
5.1 數(shù)據(jù)的查找和替換 85 
1．為替換后的內(nèi)容設(shè)置格式  85 
2．單元格匹配替換數(shù)據(jù)  87 
3．查找數(shù)據(jù)應(yīng)用通配符  88 
4．替換小于指定數(shù)值的數(shù)據(jù)  89 
5．快速選中格式相同的單元格  91 
5.2 復(fù)制粘貼及選擇性粘貼 92 
1．正確復(fù)制篩選后的數(shù)據(jù)  93 
2．僅復(fù)制單元格的格式  94 
3．粘貼數(shù)據(jù)匹配目標(biāo)區(qū)域格式  95 
4．同增同減數(shù)據(jù)時忽略空單元格  96 
5．讓粘貼數(shù)據(jù)隨源數(shù)據(jù)自動更新  98 
6．鏈接 Excel 與 Word 中的表格  99 
第 6 章 規(guī)范表格結(jié)構(gòu)與數(shù)據(jù) 100 
6.1 表格結(jié)構(gòu)調(diào)整 100
1．一次性插入多行、多列  100 
2．一次性隔行插入空行  101 
3．每隔 4 行（或任意指定行數(shù)）插入空行  102 
4．整行整列數(shù)據(jù)調(diào)換  103 
5．刪除有空白單元格的所有行  105 
6.2 規(guī)范表格數(shù)據(jù) 106 
1．刪除重復(fù)值  106 
2．拆分不同類型的數(shù)據(jù)  107 
3．解決分類匯總的合并單元格問題  109 
4．巧用 Word 批量清除指定字符  111 
6.3 處理無法計算的數(shù)據(jù) 113 
1．為什么明明顯示的是數(shù)值卻不能計算  113 
2．處理不規(guī)范的無法計算的日期  114 
3．一次性處理文本中所有空格  116 
4．謹(jǐn)防空值陷阱  117 
第7 章 數(shù)據(jù)驗證與自定義格式 120 
7.1 設(shè)置數(shù)據(jù)驗證規(guī)范輸入 120 
1．建立可選擇輸入序列  120 
2．限制輸入的數(shù)據(jù)范圍  121 
3．限制數(shù)據(jù)的最大值  123 
4．限制輸入指定范圍日期  123 
5．圈釋無效數(shù)據(jù)  125 
6．快速應(yīng)用相同的數(shù)據(jù)驗證  126 
7.2 公式在數(shù)據(jù)驗證中的應(yīng)用 127 
1．避免輸入重復(fù)值  127 
2．限制單元格只能輸入文本  128 
3．避免輸入文本數(shù)據(jù)  129 
4．限制輸入空格  130 
5．限定單元格內(nèi)必須包含指定內(nèi)容  132 
6．禁止出庫量大于庫存量  133 
7．輕松制作二級下拉菜單  134 
7.3 自定義單元格格式 137 
1．自定義日期數(shù)據(jù)格式  139 
2．在考勤表中只顯示日  140 
3．為數(shù)據(jù)批量添加重量單位  141 
4．?dāng)?shù)據(jù)部分重復(fù)時的快捷輸入技巧  142 
5．輸入 1 顯示“√”，輸入 0 顯示“×”  143 
6．根據(jù)正負(fù)值自動顯示前綴文字  144 
7．約定數(shù)據(jù)寬度不足時用 0 補齊  145
8．設(shè)置數(shù)據(jù)以“萬元”為顯示單位  146 
9．根據(jù)數(shù)值大小顯示不同的顏色  148 
10．設(shè)置業(yè)績小于 5 萬元時顯示“業(yè)績不達標(biāo)”  149 
11．隱藏單元格中所有或部分?jǐn)?shù)據(jù)  150 
第 8 章 表格美化設(shè)置 151 
8.1 表格外觀美化 151 
1．合并居中顯示表格的標(biāo)題  151 
2．設(shè)置標(biāo)題分散對齊效果  152 
3．為表格標(biāo)題設(shè)置下畫線  153 
4．讓表格部分?jǐn)?shù)據(jù)豎向顯示  154 
5．批量調(diào)整行高、列寬  155 
6．精確調(diào)整行高、列寬  157 
7．為表格定制邊框線條  158 
8．快速套用表格樣式  159 
8.2 表格格式美化 160 
1．設(shè)置文字對齊方式  161 
2．使用藝術(shù)字標(biāo)題  161 
3．自定義藝術(shù)字樣式  162 
4．快速設(shè)置注釋文本樣式  164 
5．自定義單元格樣式  165 
6．“格式刷”快速復(fù)制格式  167 
8.3 表格頁面美化 168 
1．設(shè)置頁面背景  168 
2．調(diào)整默認(rèn)頁邊距  169 
3．在任意位置分頁  170 
4．應(yīng)用頁眉  172 
5．應(yīng)用頁腳  173 
8.4 用圖形、圖片、圖示來美化表格 174 
1．插入并調(diào)整圖片  174 
2．裁剪圖片  176 
3．刪除圖片背景  178 
4．調(diào)整圖片色調(diào)  180 
5．快速應(yīng)用圖片樣式  181 
6．快速應(yīng)用圖標(biāo)  181 
7．應(yīng)用圖片版式  182 
8．應(yīng)用形狀  184 
9．為形狀添加文本  184 
10．設(shè)置形狀邊框線條  185
11．設(shè)置形狀填充效果  186 
12．設(shè)置對象疊放次序  188 
13．應(yīng)用 SmartArt 圖形  188 
14．SmartArt 圖形不夠時自定義添加  189 
15．更改 SmartArt 圖形布局和樣式  190 
第 9 章 條件格式應(yīng)用 193 
9.1 常用條件格式類型 193 
1．突出顯示大于指定數(shù)值的單元格  193 
2．突出顯示介于指定數(shù)值的單元格  194 
3．設(shè)定包含某文本時顯示特殊格式  195 
4．按發(fā)生日期顯示特殊格式  196 
5．重復(fù)值（唯一值）顯示特殊格式  197 
6．排名靠前 / 后 N 位顯示特殊格式  198 
7．高 / 低于平均值時顯示特殊格式 199 
8．設(shè)定排除某文本時顯示特殊格式  200 
9．設(shè)定以某文本開頭時顯示特殊格式  202 
9.2 使用圖形的條件格式 203 
1．應(yīng)用數(shù)據(jù)條開關(guān)  204 
2．給優(yōu)秀成績插紅色旗幟  207 
3．升降箭頭標(biāo)識數(shù)據(jù)  209 
9.3 使用公式建立規(guī)則 210 
1．自動標(biāo)識周末日期  210 
2．自動提醒值班日  212 
3．標(biāo)記出優(yōu)秀學(xué)生姓名  213 
4．突出顯示每行的最大 / 最小值 214 
5．設(shè)置多個條件格式規(guī)則  216 
6．自動標(biāo)識指定值班人  218 
7．查找數(shù)據(jù)后自動標(biāo)記顏色  220 
第 10 章 數(shù)據(jù)篩選應(yīng)用 223 
10.1 數(shù)值篩選  223 
1．篩選大于特定值的記錄  223 
2．篩選介于指定值之間的記錄  224 
3．篩選小于平均值的記錄  225 
4．篩選出指定時間區(qū)域的記錄  225 
5．篩選出以指定數(shù)字開頭的記錄  226
10.2 文本篩選  227 
1．模糊篩選獲取同一類型的數(shù)據(jù)  227 
2．篩選出開頭不包含某文本的所有記錄  228 
3．利用搜索篩選器將篩選結(jié)果中的某類數(shù)據(jù)再次排除  229 
10.3 日期篩選  230 
1．將日期按年（月、周）篩選  230 
2．按季度篩選數(shù)據(jù)記錄  232 
3．按任意日期區(qū)間篩選  233 
4．篩選日期時不按年月日分組  234 
10.4 高級篩選  235 
1．使用高級篩選提取指定數(shù)據(jù)  235 
2．篩選出不重復(fù)的記錄  236 
3．根據(jù)目標(biāo)單元格進行快速篩選  237 
4．對雙行標(biāo)題列表進行篩選  237 
5．使用“表”功能篩選并匯總數(shù)據(jù)  238 
6．高級篩選實現(xiàn)一對多查詢  240 
7．高級篩選出同時滿足多個條件的記錄  241 
8．高級篩選出滿足多個條件中一個條件的所有記錄  243 
9．在高級篩選條件區(qū)域中使用通配符  244 
第 11 章 數(shù)據(jù)排序應(yīng)用 246 
11.1 簡單排序  246 
1．按列排序數(shù)據(jù)  246 
2．按行排序數(shù)據(jù)  246 
3．按漢字筆畫排序  247 
4．按單元格顏色排序  249 
5．按單元格圖標(biāo)排序  250 
11.2 按條件排序  251 
1．按多條件排序  251 
2．自定義排序規(guī)則  252 
3．應(yīng)用函數(shù)隨機排序  253 
4．恢復(fù)排序前的數(shù)據(jù)  254 
第 12 章 數(shù)據(jù)透視表應(yīng)用 256 
12.1 按步驟創(chuàng)建數(shù)據(jù)透視表  256 
1．整理好數(shù)據(jù)源表格  256 
2．兩步創(chuàng)建數(shù)據(jù)透視表  257 
3．為透視表添加字段  258 
4．重新設(shè)置值的匯總方式  259 
5．添加多個值匯總方式  260
6．重新設(shè)置值的顯示方式  261 
7．應(yīng)用外部數(shù)據(jù)表  262 
8．重新更改數(shù)據(jù)透視表的數(shù)據(jù)源  263 
9．更新數(shù)據(jù)時要刷新數(shù)據(jù)透視表  264 
10．創(chuàng)建自動更新的數(shù)據(jù)透視表  265 
11．美化數(shù)據(jù)透視表樣式  267 
12.2 數(shù)據(jù)透視表應(yīng)用實例  268 
1．統(tǒng)計指定時間段訂單  268 
2．應(yīng)用多張數(shù)據(jù)透視表  271 
3．按百分比統(tǒng)計各店鋪數(shù)據(jù)  272 
4．應(yīng)用多張數(shù)據(jù)透視圖  273 
5．應(yīng)用日程表查看數(shù)據(jù)  275 
6．應(yīng)用切片器查看數(shù)據(jù)  276 
7．統(tǒng)計銷售額占分類匯總的百分比  278 
8．按日累計銷售額  279 
9．自定義公式求解各業(yè)務(wù)員獎金  280 
10．顯示項的明細數(shù)據(jù)  281 
11．篩選銷售額在前 5 名的產(chǎn)品  282 
12．篩選介于指定日期區(qū)間的匯總值  283 
13．按周分組匯總產(chǎn)量  284 
14．統(tǒng)計指定分?jǐn)?shù)區(qū)間的人數(shù)  284 
15．按分?jǐn)?shù)區(qū)間將成績分為“優(yōu)”“良”“差”等級  286 
16．將支出金額按半年匯總  287 
12.3 設(shè)置數(shù)據(jù)透視表、圖格式  288 
1．重設(shè)數(shù)據(jù)的顯示格式  288 
2．更改透視表的報表布局  289 
3．添加空行讓分級顯示更清晰  290 
4．在數(shù)據(jù)透視圖中篩選查看部分?jǐn)?shù)據(jù)  290 
第 13 章 圖表應(yīng)用 292 
13.1 按步驟創(chuàng)建圖表  292 
1．整理數(shù)據(jù)源表格  292 
2．了解常用圖表類型  294 
3．了解圖表布局元素  295 
4．圖表設(shè)計應(yīng)當(dāng)以簡潔為主  296 
5．兩步快速創(chuàng)建最佳圖表  297 
6．選擇部分?jǐn)?shù)據(jù)源創(chuàng)建圖表  298 
7．快速向圖表中添加新數(shù)據(jù)  299 
8．快速更改圖表為另一類型  299 
9．正確添加數(shù)據(jù)標(biāo)簽  301
10．添加值標(biāo)簽以外的數(shù)據(jù)標(biāo)簽  301 
11．為餅圖添加百分比數(shù)據(jù)標(biāo)簽  302 
12．隱藏元素以簡化圖表  303 
13．快速美化圖表  304 
14．將圖表保存為模板  305 
13.2 其他圖表類型應(yīng)用  307 
1．展示數(shù)據(jù)層級關(guān)系的旭日圖  307 
2．展示數(shù)據(jù)累計的瀑布圖  308 
3．展示數(shù)據(jù)分布區(qū)域的直方圖  309 
4．展示數(shù)據(jù)中最重要因素的排列圖  311 
5．展示數(shù)據(jù)環(huán)節(jié)的漏斗圖  311 
6．展示重要數(shù)據(jù)的復(fù)合條餅圖  312 
7．展示數(shù)據(jù)統(tǒng)計的迷你圖  314 
13.3 圖表編輯技巧應(yīng)用  315 
1．快速改變圖表展示重點  315 
2．解決坐標(biāo)軸被覆蓋的問題  316 
3．用垂直軸分割圖表  317 
4．設(shè)置數(shù)值刻度單位  318 
5．解決刻度值次序顛倒問題  320 
6．根據(jù)不連續(xù)日期建立柱形圖  321 
7．突出顯示圖表中的極值  321 
8．左右對比的條形圖  323 
9．兩項指標(biāo)比較的溫度計圖  325 
10．添加趨勢線預(yù)測數(shù)據(jù)  327 
13.4 公式輔助圖表應(yīng)用  328 
1．制作柱形圖均值比較線  328 
2．為柱形圖添加升降箭頭  330 
3．為柱形圖添加動態(tài)排序  336 
4．在條形圖中顯示進度  337 
13.5 圖表格式應(yīng)用技巧  339 
1．設(shè)置折線圖數(shù)據(jù)點格式  339 
2．圖形、圖片、文本框輔助設(shè)計  343 
3．制作南丁格爾玫瑰圖  345 
第 14 章 高級數(shù)據(jù)分析 348 
14.1 模擬運算與單變量求解  348 
1．單變量求解示例  348 
2．單變量模擬運算示例  349 
3．雙變量模擬運算示例  350 
14.2 規(guī)劃求解  351
1．加載規(guī)劃求解功能  352 
2．建立最佳報價方案  353 
3．建立合理的生產(chǎn)方案  355 
14.3 方案管理器  358 
1．創(chuàng)建方案分析模型  358 
2．建立方案管理器  359 
3．應(yīng)用方案管理器  361 
14.4 分析工具庫  362 
1．單因素方差分析——分析學(xué)歷層次對綜合考評能力的影響  363 
2．雙因素方差分析——分析何種因素對生產(chǎn)量有顯著性影響  364 
3．相關(guān)系數(shù)——分析產(chǎn)量和施肥量是否有相關(guān)性  366 
4．協(xié)方差——分析數(shù)據(jù)的相關(guān)性  367 
5．描述統(tǒng)計——分析學(xué)生成績的穩(wěn)定性  368 
6．移動平均——使用移動平均預(yù)測銷售量  369 
7．移動平均——指數(shù)平滑法預(yù)測產(chǎn)品的生產(chǎn)量  371 
8．回歸——一元線性回歸預(yù)測  372 
9．回歸——多元線性回歸預(yù)測  374 
10．排位與百分比排位——對學(xué)生成績進行排位 375 
11．抽樣——從數(shù)據(jù)庫中快速抽取樣本 376 
第 15 章 數(shù)據(jù)合并計算與分類匯總 378 
15.1 合并計算  378 
1．合并計算統(tǒng)計總費用支出  378 
2．合并計算時應(yīng)用通配符  380 
3．合并計算核對兩列數(shù)據(jù)  382 
4．統(tǒng)計各部門平均工資  383 
5．統(tǒng)計員工值班次數(shù)  386 
6．分析各班成績的穩(wěn)定程度  386 
15.2 分類匯總  388 
1．創(chuàng)建單級分類匯總  388 
2．顯示分類匯總結(jié)果  390 
3．添加多種匯總方式  390 
4．創(chuàng)建多級分類匯總  391 
5．制作帶頁小計的工作表  393 
6．復(fù)制分類匯總統(tǒng)計結(jié)果  395 
第 16 章 了解公式與函數(shù) 397 
16.1 公式操作技巧  397 
1．手動編輯公式  397 
2．復(fù)制公式完成批量計算  398
3．引用相對數(shù)據(jù)源  400 
4．引用絕對數(shù)據(jù)源  401 
5．引用當(dāng)前工作表之外的單元格  402 
6．定義名稱簡化公式引用  403 
16.2 公式數(shù)組運算技巧  405 
1．多個單元格數(shù)組公式  406 
2．單個單元格數(shù)組公式  406 
16.3 函數(shù)應(yīng)用技巧  408 
1．函數(shù)的優(yōu)勢作用  408 
2．函數(shù)應(yīng)用的結(jié)構(gòu)  409 
3．函數(shù)參數(shù)的編寫技巧  410 
4．Excel 搜索功能快速學(xué)習(xí)函數(shù) 413 
5．利用提示列表快速選擇函數(shù)  413 
6．多種函數(shù)嵌套應(yīng)用  414 
7．利用“公式求值”理解公式  415 
第 17 章 邏輯判斷與數(shù)學(xué)函數(shù)應(yīng)用 417 
17.1 邏輯判斷函數(shù)  417 
1．AND 函數(shù)  417 
2．OR 函數(shù)  419 
3．IF 函數(shù)  420 
例 1：根據(jù)銷售額返回提成率（IF 函數(shù)嵌套）  420 
例 2：只為滿足條件的員工調(diào)整薪資（IF 函數(shù)嵌套其他函數(shù)）  421 
4．IFS 函數(shù)  423 
例 1：根據(jù)銷售額返回提成率（IFS 函數(shù)）  423 
例 2：根據(jù)工齡和職位統(tǒng)計年終獎  424 
例 3：根據(jù)分?jǐn)?shù)進行等級評定  425 
17.2 求和函數(shù)應(yīng)用  425 
1．SUM（對給定的數(shù)據(jù)區(qū)域求和）  426 
例 1：用“自動求和”按鈕快速求和  427 
例 2：對一個數(shù)據(jù)區(qū)域求和  428 
例 3：求排名前三的產(chǎn)量總和  429 
2．SUMIF（按照指定條件求和）  430 
例 1：統(tǒng)計各銷售員的銷售業(yè)績總和  430 
例 2：統(tǒng)計指定時段的總銷售額  431 
例 3：用通配符對某一類數(shù)據(jù)求和  432 
例 4：用通配符求所有車間工人的工資和  433 
3．SUMIFS（對滿足多重條件的單元格求和） 434 
例 1：統(tǒng)計指定店面中指定品牌的總銷售額  434 
例 2：按月匯總出庫量  435
4．SUMPRODUCT（將數(shù)組間對應(yīng)的元素相乘并返回乘積之和） 437 
例 1：計算商品的折后總銷售額  440 
例 2：滿足多條件時求和運算  440 
例 3：統(tǒng)計周末的總營業(yè)額  441 
例 4：匯總某兩種產(chǎn)品的總銷售額  442 
例 5：統(tǒng)計大于 12 個月的賬款 443 
例 6：統(tǒng)計指定部門工資大于指定數(shù)值的人數(shù)  444 
17.3 數(shù)據(jù)的舍入  445 
1．INT（將數(shù)字向下舍入到最接近的整數(shù)）  445 
例：對平均銷量取整  446 
2．ROUND（對數(shù)據(jù)進行四舍五入） 447 
例：為超出完成量的計算獎金  448 
3．ROUNDUP（遠離 0 值向上舍入數(shù)值）  449 
例 1：計算材料長度（材料只能多不能少）  450 
例 2：計算物品的快遞費用  450 
4．ROUNDDOWN（靠近 0 值向下舍入數(shù)值）  451 
例：折后金額舍尾取整  452 
5．RANDARRAY（返回一組隨機數(shù)字）  452 
例：返回一組 100 ～ 1000 之間的隨機整數(shù)  453 
6. SWITCH（根據(jù)表達式的返回值匹配結(jié)果） 454 
例 1：只安排星期一至星期三值班  454 
例 2：提取產(chǎn)品的規(guī)格容量  455 
第 18 章 統(tǒng)計函數(shù)應(yīng)用 457 
18.1 求平均值計算  457 
1．AVERAGE（計算算術(shù)平均值）  457 
例 1：計算月平均支出費用  458 
例 2：計算平均成績（將空白單元格計算在內(nèi)）  459 
例 3：實現(xiàn)平均分?jǐn)?shù)的動態(tài)計算  460 
2．AVERAGEIF（返回滿足條件的平均值） 461 
例 1：按班級統(tǒng)計平均分  462 
例 2：計算平均值時排除 0 值  463 
例 3：使用通配符對某一類數(shù)據(jù)求平均值  463 
例 4：排除部分?jǐn)?shù)據(jù)計算平均值  464 
3．AVERAGEIFS（返回滿足多重條件的平均值）  465 
例：計算指定班級指定性別的平均分鐘數(shù)  466 
4．GEOMEAN（返回幾何平均值）  467 
例：判斷兩組數(shù)據(jù)的穩(wěn)定性  468 
5．HARMEAN（返回數(shù)據(jù)集的調(diào)和平均值）  468 
例：計算固定時間內(nèi)幾位學(xué)生的平均解題數(shù)  469
6．TRIMMEAN（截頭尾返回數(shù)據(jù)集的平均值）  469 
例：通過 10 位評委打分計算選手的最后得分  470 
18.2 按條件計數(shù)統(tǒng)計  471 
1．COUNT（統(tǒng)計含有數(shù)字的單元格個數(shù)）  471 
例 1：統(tǒng)計出席會議的人數(shù)  471 
例 2：統(tǒng)計 1 月份獲取交通補助的總?cè)藬?shù)  472 
例 3：統(tǒng)計出某一項成績?yōu)闈M分的人數(shù)  473 
2．COUNTIF（統(tǒng)計滿足給定條件的單元格的個數(shù)）  474 
例 1：統(tǒng)計指定學(xué)歷的人數(shù)  474 
例 2：統(tǒng)計成績表中分?jǐn)?shù)大于 90 分的人數(shù) 475 
3．COUNTIFS（統(tǒng)計同時滿足多個條件的單元格的個數(shù)）  476 
例 1：統(tǒng)計指定部門銷量達標(biāo)人數(shù)  476 
例 2：統(tǒng)計指定職位男、女應(yīng)聘人數(shù)  477 
4．COUNTBLANK（計算空白單元格的數(shù)目）  478 
例：檢查應(yīng)聘者信息填寫是否完善  479 
18.3 最大 / 最小值計數(shù)  480 
1．MAX/MIN（返回數(shù)據(jù)集的最大 / 最小值）  480 
2．MAXIFS/MINIFS（返回給定條件指定單元格的最大 / 最小值）481 
例 1：求指定班級的最高分  481 
例 2：忽略 0 值求出最低分?jǐn)?shù)  482 
3．LARGE（返回表格或區(qū)域中的值或值的引用）  483 
例 1：返回排名前 3 的銷售額  483 
例 2：分班級統(tǒng)計各班級的前 3 名的成績  484 
4．SMALL（返回某數(shù)據(jù)集的某個最小值）  485 
例：返回倒數(shù)第一名的成績與對應(yīng)的姓名  486 
18.4 排位統(tǒng)計  487 
1．MEDIAN（返回中位數(shù)）  487 
例：返回一個數(shù)據(jù)序列的中間值  488 
2．RANK.EQ（返回數(shù)組的最高排位）  489 
例 1：對銷售業(yè)績進行排名  489 
例 2：對不連續(xù)的數(shù)據(jù)進行排名  490 
3．RANK.AVG（返回數(shù)字列表中的排位）  491 
例：對員工考核成績排名次  491 
4．QUARTILE.INC（返回四分位數(shù)）  492 
例：四分位數(shù)偏度系數(shù)  493 
5．PERCENTILE.INC（返回第 k 個百分點值）  495 
例：返回一組數(shù)據(jù) k 百分點處的值  495 
第 19 章 文本處理函數(shù)應(yīng)用 497 
19.1 查找指定字符的位置  497
1．FIND（查找指定字符在字符串中的位置）  497 
例 1：找出指定文本所在位置  497 
例 2：提取指定位置的字符（配合文本提取函數(shù)）  498 
例 3：查找位置是為了輔助提?。◤漠a(chǎn)品名稱中提取規(guī)格）  499 
2．SEARCH（查找字符串的起始位置）  500 
19.2 提取指定文本  501 
1．LEFT（按指定字符數(shù)從最左側(cè)提取字符串）  501 
例 1：提取分部名稱  501 
例 2：從商品全稱中提取產(chǎn)地  502 
例 3：根據(jù)產(chǎn)品名稱進行一次性調(diào)價  503 
2．RIGHT（按指定字符數(shù)從最右側(cè)提取字符串）  503 
例：從文字與金額合并顯示的字符串中提取金額數(shù)據(jù)  504 
3．MID（從任意位置提取指定字符數(shù)的字符串）  504 
例 1：從產(chǎn)品名稱中提取貨號  505 
例 2：提取括號內(nèi)的字符串  506 
19.3 替換指定文本  507 
1．REPLACE（用指定的字符和字符數(shù)替換文本字符串中的 
部分文本）  507 
例：對產(chǎn)品名稱批量更改  507 
2．SUBSTITUTE（替換舊文本）  508 
例 1：快速批量刪除文本中的多余空格  508 
例 2：根據(jù)報名學(xué)員統(tǒng)計人數(shù)  509 
19.4 文本格式轉(zhuǎn)換  510 
1．TEXT（設(shè)置數(shù)字格式并將其轉(zhuǎn)換為文本） 510 
例 1：返回值班日期對應(yīng)的星期數(shù)  511 
例 2：計算加班時長并顯示為“* 時 * 分”形式  512 
例 3：解決日期計算返回日期序列號問題  513 
例 4：從身份證號碼中提取性別  514 
2．DOLLAR（四舍五入數(shù)值，并添加千分位符號和“$”符號） 515 
例：將銷售額轉(zhuǎn)換為美元格式  516 
3．RMB（四舍五入數(shù)值，并添加千分位符號和“￥”符號）  516 
例：將銷售額轉(zhuǎn)換為人民幣格式  516 
4．UPPER（將文本轉(zhuǎn)換為大寫形式）  517 
例：將文本轉(zhuǎn)換為大寫形式  517 
5．VALUE（將文本數(shù)字轉(zhuǎn)換成數(shù)值）  518 
例：將文本型數(shù)字轉(zhuǎn)換為可計算的數(shù)值  518 
19.5 其他常用文本函數(shù)  519 
1．CONCAT（合并兩個或多個文本字符串）  519 
例 1：將分散兩列的數(shù)據(jù)合并為一列  519 
例 2：合并面試人員的總分?jǐn)?shù)與錄取情況  520
2．LEN（返回文本字符串的字符數(shù)量）  521 
例：檢測員工編號位數(shù)是否正確  521 
3．EXACT（比較兩個文本字符串是否完全相同）  522 
例：比較兩次測試數(shù)據(jù)是否完全一致  522 
4．LET（可以定義名稱）  523 
例：將表達式的名稱定義為 X  524 
5．TEXTJOIN（將多個區(qū)域中的文本組合起來）  525 
例 1：獲取離職人員的全部名單  526 
例 2：獲取各職位的錄取人員名單  527 
第 20 章 日期與時間函數(shù)應(yīng)用 529 
20.1 返回系統(tǒng)當(dāng)前日期  529 
1．NOW（返回當(dāng)前日期與時間）  529 
例：計算活動剩余時間  529 
2．TODAY（返回當(dāng)前的日期）  530 
例：判斷會員是否可以升級  530 
20.2 構(gòu)建與提取日期  531 
1．DATE（構(gòu)建標(biāo)準(zhǔn)日期） 531 
例：將不規(guī)范的日期轉(zhuǎn)換為標(biāo)準(zhǔn)日期  532 
2．YEAR（返回某日對應(yīng)的年份）  533 
例：計算出員工的工齡  533 
3．MONTH（返回日期中的月份）  535 
例 1：判斷是否為本月的應(yīng)收賬款  535 
例 2：統(tǒng)計指定月份的總庫存量  536 
4．DAY（返回日期中的天數(shù)）  536 
例 1：計算本月上旬的銷量  537 
例 2：按本月缺勤天數(shù)計算扣款金額  538 
5．WEEKDAY（返回指定日期對應(yīng)的星期數(shù)）  538 
例 1：快速返回加班日期對應(yīng)星期幾  539 
例 2：判斷加班日期是工作日還是雙休日  539 
例 3：匯總周日總銷售額  540 
6．WEEKNUM（返回日期對應(yīng) 1 年中的第幾周）  541 
例：計算培訓(xùn)課程的歷時周數(shù)  541 
7．EOMONTH（返回指定月份前 / 后幾個月最后一天的序列號） 542 
例 1：根據(jù)促銷開始時間計算促銷天數(shù)  543 
例 2：計算食品的有效保質(zhì)日期  544 
20.3 日期計算  544 
1．DATEDIF（用指定的單位計算起始日和結(jié)束日之間的天數(shù)） 544 
例：計算借款逾期月份  546 
2．DAYS360（按照一年 360 天的算法計算兩個日期間相差
的天數(shù)）  547 
例：計算借款的還款剩余天數(shù)  548 
3．EDATE（計算間隔指定月份數(shù)后的日期）  548 
例 1：計算借款的到期日期  549 
例 2：根據(jù)出生日期與性別計算退休日期  549 
20.4 關(guān)于工作日的計算  550 
1．WORKDAY（獲取間隔若干工作日后的日期）  550 
例：根據(jù)項目開始日期計算項目結(jié)束日期  551 
2．WORKDAY.INTL 函數(shù)  551 
例：根據(jù)項目各流程所需工作日計算項目結(jié)束日期  553 
3．NETWORKDAYS（計算兩個日期間的工作日）  554 
例：計算臨時工的實際工作天數(shù)  554 
4．NETWORKDAYS.INTL 函數(shù)  555 
例：計算臨時工的實際工作天數(shù)（指定只有周一為休息日）  556 
20.5 時間函數(shù)  556 
1．HOUR（返回時間值的小時數(shù)）  557 
例：判斷商場活動的整點時間區(qū)間  557 
2．MINUTE（返回時間值的分鐘數(shù)）  558 
例：統(tǒng)計比賽分鐘數(shù)  558 
3．SECOND（返回時間值的秒數(shù)）  559 
例：統(tǒng)計比賽歷時秒數(shù)  559 
第 21 章 查找與引用函數(shù)應(yīng)用 561 
21.1 返回行列號的 ROW 與 COLUMN 561 
1．ROW（返回引用的行號）  561 
例：生成批量序列  562 
2．COLUMN（返回引用的列號）  564 
21.2 LOOK 類函數(shù)  564 
1．VLOOKUP（在數(shù)組第 1 列中查找并返回指定列中同一位置 
的值）  565 
例 1：按編號查詢員工的工資明細  566 
例 2：代替 IF 函數(shù)的多層嵌套（模糊匹配）  568 
例 3：應(yīng)用通配符查找數(shù)據(jù)  569 
例 4：VLOOKUP 應(yīng)對多條件匹配  570 
2．LOOKUP（查找并返回同一位置的值）  571 
例 1：LOOKUP 模糊查找  573 
例 2：利用 LOOKUP 模糊查找動態(tài)返回最后一條數(shù)據(jù) 574 
例 3：通過簡稱或關(guān)鍵字模糊匹配  575 
例 4：LOOKUP 滿足多條件查找  576 
3．HLOOKUP（查找數(shù)組的首行，并返回指定單元格的值）  577
例：根據(jù)不同的返利率計算各筆訂單的返利金額  578 
4．XLOOKUP（查找區(qū)域或數(shù)組返回對應(yīng)項）  580 
例 1：XLOOKUP 實現(xiàn)多行多列查找  582 
例 2：指定錯誤值返回空白  583 
例 3：XLOOKUP 應(yīng)對多條件匹配  584 
21.3 經(jīng)典組合 INDEX MATCH 585 
1．MATCH（查找并返回指定值所在位置） 585 
例：用 MATCH 函數(shù)判斷某數(shù)據(jù)是否包含在另一組數(shù)據(jù)中  586 
2．INDEX（從引用或數(shù)組中返回指定位置的值）  587 
例 1：MATCH INDEX 的搭配使用 588 
例 2：查詢總金額最高的銷售員（逆向查找）  589 
例 3：查找報銷次數(shù)最多的員工  590 
3．XMATCH（查找并返回值所在位置）  591 
例：XMATCH 搭配 INDEX  592 
21.4 Excel 新增的查找函數(shù)  593 
1．SORT（對區(qū)域或數(shù)組排序） 593 
例：數(shù)據(jù)動態(tài)排序  594 
2．SORTBY（多條件排序）  595 
例：多條件排序  596 
3．UNIQUE（提取唯一值）  596 
例：按單列去重復(fù)值  597 
4．FILTER（按條件篩選數(shù)據(jù)）  597 
例 1：單條件查詢  598 
例 2：多條件查詢  598 
第 22 章 財務(wù)函數(shù)應(yīng)用 600 
22.1 本金和利息計算  600 
1．PMT（返回貸款的每期還款額）  600 
例 1：計算貸款的每年償還額  600 
例 2：按季（月）還款時計算每期應(yīng)償還額  601 
2．PPMT（返回給定期間內(nèi)的本金償還額） 601 
例 1：計算指定期間的本金償還額  602 
例 2：計算第 1 個月與最后一個月的本金償還額  602 
3．IPMT（返回給定期間內(nèi)的利息償還額）  603 
例 1：計算每年償還額中的利息金額  603 
例 2：計算每月償還額中的利息金額  603 
4．ISPMT（等額本金還款方式下的利息計算）  604 
例：計算投資期內(nèi)需支付的利息額  604 
22.2 投資計算  605 
1．FV（返回某項投資的未來值）  605
例 1：計算投資的未來值  606 
例 2：計算某項保險的未來值  606 
例 3：計算住房公積金的未來值  607 
2．FVSCHEDULE（計算投資在變動或可調(diào)利率下的未來值） 607 
例：計算投資在可變利率下的未來值  607 
3．PV（返回投資的現(xiàn)值）  608 
例：判斷購買某種保險是否合算  608 
4．NPV（返回一筆投資的凈現(xiàn)值）  609 
例：計算一筆投資的凈現(xiàn)值  609 
5．XNPV（返回一組不定期現(xiàn)金流的凈現(xiàn)值）  610 
例：計算出一組不定期盈利額的凈現(xiàn)值  610 
6．NPER（返回某筆投資的總期數(shù)） 611 
例 1：計算某筆貸款的清還年數(shù)  611 
例 2：計算一筆投資的期數(shù)  611 
7．EFFECT 函數(shù)（計算實際年利率）  612 
例 1：計算投資的實際利率與本利和  612 
例 2：計算信用卡的實際年利率  613 
8．NOMINAL 函數(shù)（計算名義年利率）  613 
例：根據(jù)實際年利率計算名義年利率  614 
22.3 償還率函數(shù)  614 
1．IRR（計算內(nèi)部收益率）  614 
例：計算一筆投資的內(nèi)部收益率  614 
2．MIRR（計算修正內(nèi)部收益率） 615 
例：計算不同利率下的修正內(nèi)部收益率  615 
3．XIRR（計算不定期現(xiàn)金流的內(nèi)部收益率）  616 
例：計算一組不定期現(xiàn)金流的內(nèi)部收益率  616 
4．RATE（返回年金的各期利率） 617 
例：計算一筆投資的收益率  617 
22.4 資產(chǎn)折舊計算  617 
1．SLN（直線法計提折舊值）  617 
例 1：直線法計算固定資產(chǎn)的每年折舊額  618 
例 2：直線法計算固定資產(chǎn)的每月折舊額  618 
2．SYD（年數(shù)總和法計提折舊值）  619 
例：年數(shù)總和法計算固定資產(chǎn)的年折舊額  620 
3．DB（固定余額遞減法計算折舊值）  621 
例：固定余額遞減法計算固定資產(chǎn)的每年折舊額  621 
4．DDB（雙倍余額遞減法計算折舊值）  622 
例：雙倍余額遞減法計算固定資產(chǎn)的每年折舊額  623 
5．VDB（返回指定期間的折舊值）  624 
例：計算任意指定期間的資產(chǎn)折舊值  624