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