前 言

Microsoft Office Excel（以下簡(jiǎn)稱Excel）是功能強(qiáng)大的電子表格應(yīng)用軟件，目前廣泛應(yīng)用于經(jīng)濟(jì)、工程、科研、教學(xué)等領(lǐng)域。使用Excel管理及分析數(shù)據(jù)、制作報(bào)表及圖表、編寫程序等已經(jīng)成為人們?nèi)粘９ぷ鲀?nèi)容之一。

Excel內(nèi)容非常豐富，許多人學(xué)了很長(zhǎng)時(shí)間，卻感覺(jué)只是見(jiàn)到冰山一角。其實(shí)，學(xué)習(xí)Excel的目的是為了應(yīng)用Excel解決問(wèn)題，而不是為了精通Excel。對(duì)Excel知識(shí)既要能“濃縮”又要能“擴(kuò)展”，了解Excel常識(shí)、掌握學(xué)習(xí)及使用它的方法，將它濃縮成知識(shí)點(diǎn)，當(dāng)解決實(shí)際問(wèn)題時(shí)，查找、學(xué)習(xí)細(xì)節(jié)內(nèi)容，以解決問(wèn)題為目的，擴(kuò)展相關(guān)知識(shí)。

本書共7章：Excel基礎(chǔ)知識(shí)、公式和函數(shù)、數(shù)據(jù)管理與分析、數(shù)據(jù)透視表、VBA編程、Excel中的圖形處理、綜合練習(xí)。

本書側(cè)重于從應(yīng)用角度學(xué)習(xí)Excel，精選有實(shí)際應(yīng)用意義的案例，力求將各方面的知識(shí)融合在一起。案例劃分：關(guān)于員工管理方面有“員工檔案”“員工考核”“工資管理”等案例；關(guān)于企業(yè)資金運(yùn)作、財(cái)務(wù)狀況方面有“明細(xì)表”“總賬表”“利潤(rùn)表”“資產(chǎn)負(fù)債表”等案例；關(guān)于決策方面有“商品進(jìn)貨”“值班安排”“生產(chǎn)決策”“最短路徑”等案例；關(guān)于調(diào)查及分析市場(chǎng)需求方面有“銷量統(tǒng)計(jì)分析”“網(wǎng)上調(diào)查問(wèn)卷”等案例；關(guān)于矢量圖與圖形處理方面有“地圖調(diào)色”“文字修飾”“氣泡圖”“交通流量圖”等。本書內(nèi)容編排按照實(shí)際應(yīng)用順序，先介紹具體業(yè)務(wù)的實(shí)際操作，然后是需要查找的相關(guān)Excel知識(shí)。

本書由盧山任主編，江成、楊艷紅、郭高卉子、閆瑾任副主編。其中盧山編寫第1章、第5章、第7章，楊艷紅編寫第2章，江成編寫第3章，郭高卉子編寫第4章，閆瑾編寫第6章。參與本書部分編寫工作的還有劉克強(qiáng)、田瑾、李欣午、王紀(jì)文、劉彥平。首都經(jīng)濟(jì)貿(mào)易大學(xué)信息學(xué)院的張軍教授、高迎教授、周廣軍老師對(duì)本書的編寫給予了很大幫助，提出了許多寶貴意見(jiàn)和建議，在此向他們表示衷心感謝。

由于編者水平有限，加之創(chuàng)作時(shí)間倉(cāng)促，本書不足之處在所難免，歡迎廣大讀者批評(píng)指正。

編 者

2018年12月

目 錄
前言

第1章 Excel基礎(chǔ)知識(shí) 1
1.1　案例1 員工檔案管理 1
1.1.1 啟動(dòng)Excel 1
1.1.2 使用模板 2
1.1.3 錄入數(shù)據(jù) 3
1.1.4 美化“員工檔案表” 4
1.1.5 使用樣式 6
1.1.6 設(shè)置條件格式 7
1.1.7 添加批注 7
1.1.8 使用名稱 8
1.1.9 隱藏工作表和行 8
1.1.10 保護(hù)表格 9
1.1.11 生成圖表 9
1.2 Excel相關(guān)知識(shí) 10
1.2.1 智能標(biāo)記 10
1.2.2 自動(dòng)列表與自動(dòng)完成 10
1.2.3 自動(dòng)填充 11
1.2.4 快速?gòu)?fù)制、移動(dòng)數(shù)據(jù) 12
1.2.5 工作表的大小 13
1.2.6 選中有效行、有效列 13
1.2.7 工作表網(wǎng)格設(shè)置 13
1.2.8 樣式 13
1.2.9 格式復(fù)制 14
1.2.10 名稱 14
1.2.11 批注 14
1.2.12 隱藏工作簿、工作表、行、列 15
1.2.13 保護(hù)表格 15
1.2.14 操作大型表 15
第2章 公式與函數(shù) 17
2.1　公式與函數(shù)基礎(chǔ) 17
2.1.1 公式 17
2.1.2 函數(shù) 18
2.2　案例2 員工考核管理 19
2.2.1 建立“出勤量統(tǒng)計(jì)表” 20
2.2.2 建立“績(jī)效表” 21
2.2.3 建立各季度考核表 21
2.2.4 計(jì)算年度總成績(jī) 24
2.2.5 計(jì)算成績(jī)排名 26
2.2.6 計(jì)算獎(jiǎng)金 27
2.2.7 相關(guān)Excel知識(shí) 29
2.3　案例3 員工工資管理 32
2.3.1 建立“基本工資表” 33
2.3.2 建立“考勤表” 34
2.3.3 建立“獎(jiǎng)金表” 35
2.3.4 建立“補(bǔ)貼表” 36
2.3.5 建立“工資發(fā)放明細(xì)表” 36
2.3.6 定義數(shù)據(jù)名稱 37
2.3.7 計(jì)算基本工資 38
2.3.8 計(jì)算獎(jiǎng)金 39
2.3.9 計(jì)算補(bǔ)貼 39
2.3.10 計(jì)算應(yīng)發(fā)工資 39
2.3.11 計(jì)算缺勤扣款 40
2.3.12 計(jì)算扣所得稅 40
2.3.13 計(jì)算實(shí)發(fā)工資 41
2.3.14 制作工資條 41
2.3.15 相關(guān)Excel知識(shí) 43
2.4　案例4 企業(yè)日常財(cái)務(wù)明細(xì)表 45
2.4.1 建立“會(huì)計(jì)科目代碼表” 45
2.4.2 添加會(huì)計(jì)科目 46
2.4.3 命名數(shù)據(jù)區(qū)域名稱 48
2.4.4 建立“企業(yè)日常財(cái)務(wù)明細(xì)表” 49
2.4.5 設(shè)置數(shù)據(jù)有效性 49
2.4.6 輸入內(nèi)容一致性 49
2.4.7 設(shè)置其他單元格格式 51
2.4.8 輸入第一筆業(yè)務(wù) 52
2.4.9 輸入多筆業(yè)務(wù) 52
2.4.10 使用凍結(jié)窗格 52
2.4.11 添加業(yè)務(wù)種類批注 53
2.4.12 添加借貸方合計(jì) 54
2.4.13 憑證信息查詢 54
2.4.14 相關(guān)Excel知識(shí) 56
2.5 案例5 企業(yè)財(cái)務(wù)總賬表和月利潤(rùn)表 57
2.5.1 建立“企業(yè)財(cái)務(wù)總賬表” 59
2.5.2 輸入初始數(shù)據(jù) 59
2.5.3 輸入本期借方發(fā)生額 60
2.5.4 輸入本期貸方發(fā)生額 61
2.5.5 計(jì)算期末余額 61
2.5.6 添加平衡測(cè)算表 62
2.5.7 建立“企業(yè)月利潤(rùn)表” 63
2.5.8 計(jì)算利潤(rùn)表中各項(xiàng)內(nèi)容 64
2.5.9 相關(guān)Excel知識(shí) 66
2.6 案例6 企業(yè)資產(chǎn)負(fù)債表 66
2.6.1 建立“資產(chǎn)負(fù)債表” 67
2.6.2 輸入年初數(shù) 68
2.6.3 計(jì)算流動(dòng)資產(chǎn) 68
2.6.4 計(jì)算其他資產(chǎn) 71
2.6.5 計(jì)算負(fù)債 74
2.6.6 計(jì)算所有者權(quán)益 77
2.6.7 計(jì)算負(fù)債及所有者權(quán)益合計(jì) 78
2.6.8 相關(guān)Excel知識(shí) 78
2.6.9 學(xué)習(xí)一些財(cái)務(wù)知識(shí) 79
2.7　案例7 企業(yè)資產(chǎn)負(fù)債表分析 81
2.7.1 建立“資產(chǎn)負(fù)債絕對(duì)變化對(duì)比
分析表” 82
2.7.2 導(dǎo)入“資產(chǎn)負(fù)債表”數(shù)據(jù) 82
2.7.3 計(jì)算資產(chǎn)類和負(fù)債類項(xiàng)目的絕對(duì)
變化和變化幅度 82
2.7.4 建立“資產(chǎn)負(fù)債相對(duì)變化對(duì)比
分析表” 86
2.7.5 導(dǎo)入“資產(chǎn)負(fù)債表”數(shù)據(jù) 86
2.7.6 計(jì)算資產(chǎn)類和負(fù)債類項(xiàng)目的相對(duì)
變化和變化幅度 87
2.8 案例8 固定資產(chǎn)折舊 91
2.8.1 建立“固定資產(chǎn)匯總表” 92
2.8.2 建立“企業(yè)固定資產(chǎn)平均年限
折舊法分析表” 94
2.8.3 復(fù)制初始數(shù)據(jù) 94
2.8.4 輸入當(dāng)前時(shí)間和折舊年限 95
2.8.5 計(jì)算月折舊額 95
2.8.6 計(jì)算總折舊月數(shù)和本年折舊月數(shù) 96
2.8.7 計(jì)算本年折舊額和總折舊額 97
2.8.8 添加餅形圖 99
2.8.9 數(shù)據(jù)合并與拆分 100
2.8.10 制作“單個(gè)設(shè)備的雙倍余額遞減
折舊分析表” 101
2.8.11 相關(guān)Excel知識(shí) 102
2.9 公式習(xí)題 104
2.9.1 單科成績(jī)表的制作 104
2.9.2 九九乘法口訣表的制作 106
2.9.3 評(píng)分表的制作 107
2.9.4 身份證號(hào)的相關(guān)計(jì)算 108
2.9.5 統(tǒng)計(jì)組裝量最值和排名的相關(guān)計(jì)算 110
2.9.6 分組統(tǒng)計(jì)組裝量 111
2.9.7 定位查找數(shù)據(jù) 113
2.9.8 截取字符串 115
第3章 數(shù)據(jù)管理與分析 116
3.1　數(shù)據(jù)管理與分析基礎(chǔ) 116
3.2　案例9 員工貸款購(gòu)房 117
3.2.1 建立“購(gòu)房貸款計(jì)劃表” 117
3.2.2 使用PMT函數(shù)計(jì)算月還款額 117
3.2.3 使用單變量模擬運(yùn)算表 118
3.2.4 使用雙變量模擬運(yùn)算表 119
3.2.5 使用方案管理器 121
3.2.6 單變量求解的應(yīng)用 125
3.2.7 住房按揭貸款的等額還款與等本
還款 126
3.2.8 相關(guān)Excel函數(shù) 131
3.3 案例10 商品進(jìn)貨量決策 131
3.3.1 加載“規(guī)劃求解”工具 132
3.3.2 建立問(wèn)題求解模型 133
3.3.3 輸入計(jì)算公式 133
3.3.4 設(shè)置規(guī)劃求解參數(shù) 135
3.3.5 設(shè)置約束條件 136
3.3.6 用規(guī)劃求解工具求解 137
3.4 案例11 員工值班安排 140
3.4.1 建立“員工值班安排表” 140
3.4.2 設(shè)置變量、輸入公式 141
3.4.3 設(shè)置規(guī)劃求解參數(shù) 142
3.4.4 用規(guī)劃求解工具求解 144
3.4.5 保存規(guī)劃求解模型 145
3.4.6 在工作表中再加入一個(gè)規(guī)劃
求解模型 146
3.4.7 在工作表中的不同模型之間切換 147
3.4.8 相關(guān)Excel知識(shí) 148
3.5 案例12 生產(chǎn)決策問(wèn)題 149
3.5.1 建立生產(chǎn)決策問(wèn)題求解模型 149
3.5.2 輸入計(jì)算公式 149
3.5.3 設(shè)置參數(shù)、添加約束 150
3.5.4 用規(guī)劃求解工具求解 151
3.6 案例13 運(yùn)輸配送問(wèn)題 152
3.6.1 建立運(yùn)輸配送問(wèn)題求解模型 152
3.6.2 輸入計(jì)算公式 153
3.6.3 設(shè)置規(guī)劃求解參數(shù) 154
3.6.4 用規(guī)劃求解工具求解 155
3.6.5 相關(guān)Excel知識(shí) 156
3.7 案例14 最小費(fèi)用流 156
3.7.1 建立求解最小費(fèi)用流模型 156
3.7.2 計(jì)算節(jié)點(diǎn)靜流量 157
3.7.3 輸入總成本公式 158
3.7.4 設(shè)置規(guī)劃求解參數(shù) 159
3.7.5 用規(guī)劃求解工具求解 160
3.8 案例15 最短路徑問(wèn)題 161
3.8.1 建立求解最短路徑模型 161
3.8.2 輸入計(jì)算公式 162
3.8.3 設(shè)置規(guī)劃求解參數(shù) 163
3.8.4 用規(guī)劃求解工具求解 164
3.8.5 最短路徑問(wèn)題的改進(jìn) 165
3.9 案例16 旅行商問(wèn)題 167
3.9.1 建立旅行商求解模型 168
3.9.2 輸入計(jì)算公式 169
3.9.3 使用規(guī)劃求解工具 170
3.9.4 對(duì)求解結(jié)果加以修改 172
第4章 數(shù)據(jù)透視表 174
4.1　數(shù)據(jù)透視表的基礎(chǔ)知識(shí) 174
4.2　案例17 企業(yè)銷售業(yè)務(wù)統(tǒng)計(jì)分析 174
4.2.1 建立“公司銷量統(tǒng)計(jì)表” 174
4.2.2 建立透視表和透視圖 175
4.2.3 設(shè)置總計(jì)和分類匯總 178
4.2.4 自定義計(jì)算 179
4.3　案例18 企業(yè)盈利能力的財(cái)務(wù)指標(biāo)分析 180
4.3.1 建立“損益表” 180
4.3.2 建立“損益表”的數(shù)據(jù)透視表 181
4.4　案例19 用透視表生成企業(yè)日常財(cái)務(wù)
總賬表 183
4.4.1 導(dǎo)入數(shù)據(jù) 184
4.4.2 使用數(shù)據(jù)透視表創(chuàng)建日常財(cái)務(wù)
總賬表 184
第5章 VBA 187
5.1　VBA基礎(chǔ)知識(shí) 187
5.1.1 VBA開(kāi)發(fā)環(huán)境簡(jiǎn)介 187
5.1.2 VBA的基本語(yǔ)法 188
5.1.3 VBA程序結(jié)構(gòu) 190
5.1.4 VBA錄制宏和運(yùn)行宏 191
5.1.5 編寫簡(jiǎn)單的VBA程序 192
5.2　案例20 商品入庫(kù)管理 193
5.2.1 建立原始數(shù)據(jù) 193
5.2.2 計(jì)算入庫(kù)商品總次數(shù) 194
5.2.3 添加按鈕控件 194
5.2.4 添加代碼 195
5.2.5 運(yùn)行效果 196
5.2.6 相關(guān)Excel知識(shí) 197
5.3　案例21 設(shè)計(jì)畢業(yè)生網(wǎng)上調(diào)查問(wèn)卷 197
5.3.1 設(shè)計(jì)調(diào)查問(wèn)卷的說(shuō)明文字 198
5.3.2 設(shè)計(jì)單項(xiàng)選擇功能 198
5.3.3 設(shè)計(jì)下拉列表功能 200
5.3.4 為選項(xiàng)按鈕創(chuàng)建單元格鏈接 202
5.3.5 為組合框創(chuàng)建單元格鏈接 203
5.3.6 設(shè)計(jì)自動(dòng)記錄功能 204
5.3.7 設(shè)計(jì)統(tǒng)計(jì)功能 207
5.3.8 設(shè)置工作表的保護(hù) 209
5.3.9 設(shè)置工作表的外觀 210
5.3.10 將“畢業(yè)生調(diào)查問(wèn)卷”發(fā)布到網(wǎng)上 211
第6章 Excel中的圖形處理 213
6.1 矢量圖形與EMF文件 213
6.1.1 什么是矢量圖形 213
6.1.2 矢量圖形格式 213
6.1.3 Windows圖元文件格式 214
6.1.4 矢量圖形格式轉(zhuǎn)換工具 214
6.2 案例22 將北京行政區(qū)劃圖由SVG格式
轉(zhuǎn)換為WMF/EMF格式 214
6.3 案例23 在Excel中導(dǎo)入行政區(qū)劃圖 216
6.4 案例24 地圖調(diào)色（采用分級(jí)色階） 217
6.5 案例25 用VBA腳本著色 220
6.6 案例26 透明度的使用 221
6.7 案例27 地圖文字修飾與氣泡圖 222
6.7.1 在矢量圖形上添加文本框 222
6.7.2 文本框位置微調(diào) 224
6.7.3 氣泡圖展示 224
6.8 案例28 交通圖的創(chuàng)建與編輯 226
6.8.1 交通圖的導(dǎo)入與命名 226
6.8.2 保存與恢復(fù)交通圖的特征 227
6.8.3 交通圖原始數(shù)據(jù)處理 230
6.8.4 交通圖圖元調(diào)整 231
6.9 案例29 旅行商問(wèn)題 232
6.9.1 旅行商問(wèn)題概述 232
6.9.2 使用規(guī)劃求解工具處理旅行商問(wèn)題 233
6.9.3 旅行商問(wèn)題的結(jié)果展示 235
第7章 綜合練習(xí) 240
7.1 企業(yè)產(chǎn)銷預(yù)算表 240
7.1.1 建立“企業(yè)產(chǎn)銷預(yù)算表” 240
7.1.2 建立“銷量預(yù)算表” 241
7.1.3 建立“直接成本預(yù)算表” 241
7.1.4 計(jì)算預(yù)計(jì)期生產(chǎn)量 241
7.1.5 計(jì)算直接人工成本 243
7.1.6 計(jì)算整年數(shù)據(jù) 244
7.1.7 加入動(dòng)態(tài)餅形圖 244
7.2 企業(yè)現(xiàn)金流量表 246
7.2.1 建立“企業(yè)現(xiàn)金流量表” 247
7.2.2 計(jì)算經(jīng)營(yíng)活動(dòng)的各項(xiàng)數(shù)據(jù) 248
7.2.3 計(jì)算投資活動(dòng)的各項(xiàng)數(shù)據(jù) 249
7.2.4 計(jì)算籌資活動(dòng)的各項(xiàng)數(shù)據(jù) 249
7.2.5 計(jì)算現(xiàn)金及現(xiàn)金等價(jià)物凈增加額 249
7.2.6 建立企業(yè)現(xiàn)金流量匯總表 250
7.2.7 建立“企業(yè)現(xiàn)金流量定比表” 252
7.2.8 與第一季度相比企業(yè)現(xiàn)金流量的
定比變化 253
7.3 銷售預(yù)測(cè)分析 254
7.3.1 建立原始數(shù)據(jù)表 254
7.3.2 繪制月銷量折線圖 255
7.3.3 添加趨勢(shì)線及趨勢(shì)方程 256
7.3.4 預(yù)測(cè)銷售量 257
7.3.5 內(nèi)插值法預(yù)測(cè)銷售量 257
7.3.6 Excel相關(guān)知識(shí) 259
7.4　企業(yè)證券投資分析 260
7.4.1 輸入股票基本信息 261
7.4.2 建立“K線圖” 261
7.4.3 制作股票收益計(jì)算器 262
7.5　馬爾可夫法生產(chǎn)預(yù)測(cè) 267
7.5.1 馬爾可夫預(yù)測(cè)法 267
7.5.2 輸入企業(yè)經(jīng)營(yíng)信息 268
7.5.3 歸納企業(yè)的經(jīng)營(yíng)信息 269
7.5.4 計(jì)算“一步轉(zhuǎn)移概率” 269
7.5.5 實(shí)現(xiàn)2012年某月份生產(chǎn)預(yù)測(cè) 270
7.6 求最短路徑 271
7.6.1 輸入路徑信息 272
7.6.2 建立求解最短路徑問(wèn)題模型 272
7.6.3 規(guī)劃求解最短路徑問(wèn)題 272
參考文獻(xiàn) 275

1. 嵌入式人工智能技術(shù)應(yīng)用（數(shù)字教材） [主編 胡娜 楊國(guó)勇 晏廷榮]
2. Java Web項(xiàng)目化設(shè)計(jì)實(shí)踐教程 [邱云 曾陳萍]
3. 人工智能開(kāi)發(fā)框架應(yīng)用 [主編 王明超 蘆婭云]
4. 民航客艙服務(wù)實(shí)用英語(yǔ)教程 [主編 田靜 徐亞玲]
5. Python數(shù)據(jù)分析 [主編　馮志輝　趙磊　李放]
6. 大學(xué)信息技術(shù) [曾翰穎 編著]
7. 多維視域下的高校籃球教學(xué)創(chuàng)新研究 [張利超 李寧 著]
8. 創(chuàng)新創(chuàng)業(yè)訓(xùn)練實(shí)踐教程 [主編 王日華 陳武 黃喆誠(chéng)]
9. 實(shí)用運(yùn)籌學(xué) [主編　邢育紅　于晉臣]
10. 電子技術(shù)（第二版） [主編 覃愛(ài)娜 李飛]
11. 勞動(dòng)爭(zhēng)議處理實(shí)務(wù) [主編 王秀卿 羅靜]
12. 工程數(shù)學(xué) [主編 郭立娟 王海]
13. 語(yǔ)音識(shí)別理論與實(shí)踐 [主編　莫宏偉]
14. 武術(shù)基礎(chǔ)教程 [主編 李代勇 謝志民]
15. 計(jì)算機(jī)網(wǎng)絡(luò)實(shí)訓(xùn)教程 [主編 張浩軍 趙玉娟]
16. 畫法幾何與機(jī)械制圖習(xí)題集（多學(xué)時(shí)） [主編 趙軍]
17. 電工電子技術(shù)基礎(chǔ) [主編 劉 軍 楊國(guó)龍 劉天成]
18. MySQL數(shù)據(jù)庫(kù)項(xiàng)目式教程 [陳亞峰]
19. 機(jī)械設(shè)計(jì)基礎(chǔ)（第二版） [主編 田亞平 李愛(ài)姣]
20. 畫法幾何與機(jī)械制圖 [主編 趙軍]
21. C語(yǔ)言程序設(shè)計(jì)習(xí)題與實(shí)驗(yàn)指導(dǎo)（第二版） [主編 甄增榮 張賓]
22. C語(yǔ)言程序設(shè)計(jì)（第二版） [主編 甄增榮 田云霞]
23. Unity3D虛擬現(xiàn)實(shí)應(yīng)用開(kāi)發(fā)實(shí)踐 [主 編 劉龍]
24. 智能化技術(shù)基礎(chǔ)（第三版） [鄧文達(dá)　李禮]
25. 面向?qū)ο蟪绦蛟O(shè)計(jì) [主編 張勇 張平華 趙小龍]
26. 網(wǎng)絡(luò)營(yíng)銷 [主編 夏薇薇 劉婷 尚潔]
27. 電視節(jié)目策劃與制作（微課版） [主　編　黃滴滴]
28. 數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用實(shí)踐教程（SQL Server 2019） [主 編 嚴(yán)暉 周肆清]
29. 大學(xué)應(yīng)用數(shù)學(xué) [主編 郭立娟]
30. 工業(yè)機(jī)器人拆裝與調(diào)試 [主編 胡月霞 向艷芳 朱奇]