Excel 2013實戰(zhàn)應用超級手冊（含盤）

第1部分 基 礎 篇
第1章 Excel 2013工作簿與工作表
1.1 工作簿的創(chuàng)建與保存 2
1.1.1 創(chuàng)建新工作簿 2
1.1.2 保存工作簿 4
1.2 工作表操作 7
1.2.1 重命名工作表 7
1.2.2 插入新工作表 7
1.2.3 刪除工作表 8
1.2.4 設置工作表標簽顏色 9
1.2.5 移動工作表到其他位置 10
1.2.6 復制工作表 11
1.3 單元格操作 11
1.3.1 插入、刪除單元格 12
1.3.2 插入行或列 13
1.3.3 合并單元格 15
1.3.4 設置單元格大小 16
1.3.5 隱藏含有重要數(shù)據(jù)的行或列 17
1.4 數(shù)據(jù)保護 18
1.4.1 保護工作表 18
1.4.2 加密保護工作簿 20
1.5 工作簿的共享使用 22
1.5.1 共享工作簿 22
1.5.2 云共享 26
第2章 表格數(shù)據(jù)的輸入與編輯
2.1 輸入各種類型數(shù)據(jù) 31
2.1.1 輸入文本內(nèi)容 31
2.1.2 輸入數(shù)值 33
2.1.3 日期與時間數(shù)據(jù)輸入 35
2.1.4 輸入特殊符號 37
2.2 數(shù)據(jù)批量輸入 39
2.2.1 批量輸入相同數(shù)據(jù) 39
2.2.2 填充輸入有規(guī)則數(shù)據(jù) 40
2.2.3 導入外部數(shù)據(jù) 43
2.3 數(shù)據(jù)有效性驗證 46
2.3.1 限制只能輸入指定類型數(shù)據(jù) 46
2.3.2 建立可選擇輸入序列 47
2.3.3 用公式建立驗證條件 49
2.3.4 設置鼠標指向時顯示輸入提示 50
2.3.5 圈釋無效數(shù)據(jù) 51
2.4 表格數(shù)據(jù)的修改、移動、復制與刪除 52
2.4.1 修改數(shù)據(jù) 52
2.4.2 移動數(shù)據(jù)到新位置 53
2.4.3 復制數(shù)據(jù)以實現(xiàn)快速輸入 54
2.4.4 利用“粘貼選項”功能選擇性粘貼 54
2.4.5 刪除數(shù)據(jù) 56
2.5 查找與替換數(shù)據(jù) 56
2.5.1 表格數(shù)據(jù)的查找 57
2.5.2 表格數(shù)據(jù)的替換 57
第3章 表格的美化設置及打印
3.1 表格字體與對齊方式設置 62
3.1.1 設置表格字體 62
3.1.2 設置表格對齊方式 63
3.2 表格邊框與底紋設置 65
3.2.1 設置表格邊框效果 65
3.2.2 設置表格底紋效果 66
3.3 在表格中使用圖片或圖形 68
3.3.1 插入圖片 68
3.3.2 圖片編輯 69
3.3.3 繪制圖形并美化 71
3.3.4 圖形立體效果 73
3.4 套用表格格式來美化表格 73
3.4.1 套用默認表格格式 74
3.4.2 新建表樣式 75
3.5 應用單元格樣式來美化表格 76
3.5.1 套用默認單元格樣式 76
3.5.2 新建單元格樣式 77
3.5.3 合并單元格樣式 80
3.5.4 利用格式刷快速復制單元格樣式 80
3.6 表格頁面設置 81
3.6.1 設置頁面 82
3.6.2 設置分頁 83
3.7 打印設置 85
3.7.1 添加打印區(qū)域 86
3.7.2 設置打印份數(shù)或打印指定頁 87
第2部分 高 級 篇
第4章 公式計算
4.1 認識公式 90
4.1.1 什么是公式 90
4.1.2 公式運算符 91
4.1.3 輸入與編輯公式 92
4.1.4 復制公式完成批量計算 94
4.2 公式計算中對單元格的引用 95
4.2.1 引用相對數(shù)據(jù)源計算 96
4.2.2 引用絕對數(shù)據(jù)源計算 97
4.2.3 引用其他工作表數(shù)據(jù)源計算 97
4.2.4 引用其他工作簿數(shù)據(jù)源計算 98
4.3 公式計算中函數(shù)的使用 99
4.3.1 初識函數(shù) 99
4.3.2 辦公常用函數(shù)簡介 101
4.4 公式中對名稱的使用 108
4.4.1 將單元格區(qū)域定義為名稱 109
4.4.2 使用定義的名稱建立公式 109
4.5 公式檢測與審核 111
4.5.1 公式查錯 111
4.5.2 公式逐步分解查錯 111
第5章 數(shù)據(jù)管理與分析
5.1 突出顯示滿足條件的數(shù)據(jù) 114
5.1.1 了解“條件格式” 114
5.1.2 條件格式設置 115
5.1.3 管理條件格式規(guī)則 120
5.2 數(shù)據(jù)的排序 122
5.2.1 按單個條件排序 122
5.2.2 按多個條件排序 122
5.3 數(shù)據(jù)的篩選 123
5.3.1 添加自動篩選 123
5.3.2 數(shù)字篩選 124
5.3.3 文本篩選 126
5.3.4 日期篩選 128
5.3.5 高級篩選 129
5.3.6 取消篩選 131
5.4 表格數(shù)據(jù)的分類匯總 132
5.4.1 創(chuàng)建分類匯總統(tǒng)計數(shù)據(jù) 132
5.4.2 創(chuàng)建多級分類匯總 133
5.4.3 取消分類匯總 134
5.5 合并計算功能 135
5.5.1 按位置合并計算 135
5.5.2 按類別合并計算 136
5.5.3 更改合并計算的函數(shù) 138
第6章 插入圖表分析數(shù)據(jù)
6.1 認識圖表 141
6.1.1 圖表結構 141
6.1.2 準確選中圖表中的對象 142
6.2 根據(jù)分析目的創(chuàng)建圖表 143
6.2.1 學會按照分析目的選擇圖表類型 143
6.2.2 使用推薦的圖表 146
6.2.3 更改創(chuàng)建的圖表類型 147
6.2.4 更改圖表的數(shù)據(jù)源 148
6.2.5 切換數(shù)據(jù)行列 149
6.3 使用迷你圖分析數(shù)據(jù) 150
6.3.1 創(chuàng)建迷你圖 150
6.3.2 編輯迷你圖 152
6.4 圖表的編輯設置 154
6.4.1 圖表大小及位置調(diào)整 154
6.4.2 圖表的復制 156
6.5 圖表對象編輯 158
6.5.1 快速應用圖表布局 158
6.5.2 編輯圖表坐標軸 159
6.5.3 編輯圖表數(shù)據(jù)系列 161
6.6 圖表的美化設置 162
6.6.1 套用圖表樣式一鍵美化 163
6.6.2 設置圖表文字格式 164
6.6.3 為對象套用形狀樣式 165
6.6.4 為對象設置邊框及填充 166
第7章 建立數(shù)據(jù)透視表分析數(shù)據(jù)
7.1 了解數(shù)據(jù)透視表 171
7.1.1 數(shù)據(jù)透視表的作用 171
7.1.2 數(shù)據(jù)透視表的結構 171
7.2 建立數(shù)據(jù)透視表分析數(shù)據(jù) 173
7.2.1 創(chuàng)建數(shù)據(jù)透視表 173
7.2.2 根據(jù)統(tǒng)計目的設置字段 175
7.3 數(shù)據(jù)透視表分析 177
7.3.1 數(shù)據(jù)透視表排序 177
7.3.2 數(shù)據(jù)透視表篩選 179
7.3.3 使用切片器篩選 180
7.3.4 使用“組合”對話框分組字段 181
7.4 數(shù)據(jù)透視表計算 182
7.4.1 更改默認的匯總方式 182
7.4.2 更改數(shù)據(jù)透視表的值顯示方式 184
7.4.3 自定義計算項 186
7.5 設置數(shù)據(jù)透視表布局及樣式 186
7.5.1 報表布局設置 187
7.5.2 套用數(shù)據(jù)透視表樣式 188
7.6 創(chuàng)建數(shù)據(jù)透視圖 189
7.6.1 創(chuàng)建數(shù)據(jù)透視圖 189
7.6.2 編輯數(shù)據(jù)透視圖 190
7.6.3 美化數(shù)據(jù)透視圖 192
第8章 高級分析工具的應用
8.1 安裝分析工具庫 196
8.2 模擬運算表 197
8.2.1 單變量模擬運算 197
8.2.2 雙變量模擬運算 198
8.2.3 將模擬運算結果轉(zhuǎn)換為常量 199
8.3 單變量求解 200
8.3.1 單變量求解預定銷售計劃 200
8.3.2 單變量求解可貸款金額 201
8.4 規(guī)劃求解 202
8.4.1 規(guī)劃求解確定最優(yōu)生產(chǎn)方案 202
8.4.2 規(guī)劃求解確定最優(yōu)運輸方案 206
8.5 方案管理器進行貸款方案決策 208
8.5.1 定義方案 209
8.5.2 得出最佳方案 211
8.6 數(shù)據(jù)分析工具 212
8.6.1 直方圖分析員工工資 212
8.6.2 銷量與利潤總額回歸分析 214
8.6.3 使用移動平均預測銷售額 216
第3部分 實 戰(zhàn) 篇
第9章 日常行政管理分析表格
9.1 辦公用品采購申請表 220
9.1.1 創(chuàng)建辦公用品采購申請表 220
9.1.2 設置公式計算金額 222
9.2 辦公用品領用登記表 224
9.2.1 創(chuàng)建辦公用品領用登記表 224
9.2.2 計算領用物品總價及判斷是否到期 228
9.3 值班人員提醒表 229
9.3.1 建立值班安排表 229
9.3.2 設置單元格格式實現(xiàn)自動提醒 231
9.4 假期值班人員安排表 232
9.4.1 設計值班表求解模型 232
9.4.2 進行規(guī)劃求解 234
9.4.3 根據(jù)規(guī)劃求解的值得出正確的排班日期 236
9.4.4 打印值班安排表 237
9.5 客戶資料管理表 239
9.5.1 建立客戶資料管理表 239
9.5.2 將客戶名稱鏈接到公司網(wǎng)頁 240
9.5.3 設置條件格式特殊顯示“大客戶” 241
9.5.4 篩選出指定條件的客戶信息 243
9.6 員工培訓成績統(tǒng)計分析表 245
9.6.1 建立員工培訓成績統(tǒng)計分析表 245
9.6.2 計算總成績、平均成績、名次 246
9.6.3 設置圖標集查看總分 247
9.6.4 篩選查看培訓合格情況 249
第10章 企業(yè)員工檔案管理與人事分析
10.1 建立檔案管理表 252
10.1.1 規(guī)劃員工檔案表框架 252
10.1.2 表格基本數(shù)據(jù)的輸入 252
10.1.3 設置公式自動返回相關信息 255
10.2 快速查詢?nèi)我鈫T工檔案 259
10.2.1 建立員工檔案查詢表框架 259
10.2.2 設置單元格的公式 261
10.2.3 實現(xiàn)快速查詢 263
10.3 分析員工學歷層次 263
10.3.1 建立數(shù)據(jù)透視表分析員工學歷層次 263
10.3.2 用圖表直觀顯示各學歷占比情況 264
10.4 分析員工年齡層次 266
10.4.1 建立數(shù)據(jù)透視表分析員工年齡層次 266
10.4.2 用圖表直觀顯示各年齡段人數(shù)占比情況 268
10.5 分析公司員工穩(wěn)定性 269
10.5.1 建立數(shù)據(jù)透視表統(tǒng)計各工齡段的人數(shù) 269
10.5.2 用圖表直觀顯示各年齡段人數(shù)占比情況 271
第11章 企業(yè)員工考勤管理與分析
11.1 員工考勤表 273
11.1.1 創(chuàng)建考勤表 273
11.1.2 設置條件格式顯示周末 275
11.1.3 填制考勤表 277
11.2 考勤統(tǒng)計表 278
11.2.1 統(tǒng)計各員工本月出勤數(shù)據(jù) 278
11.2.2 計算滿勤獎、應扣工資 280
11.3 本月各部門缺勤情況比較分析 281
11.3.1 建立數(shù)據(jù)透視表分析各部門缺勤情況 281
11.3.2 建立數(shù)據(jù)透視圖直觀比較缺勤情況 283
11.4 分析本月出勤情況 285
11.4.1 分析各工作天數(shù)對應人數(shù)的占比情況 285
11.4.2 分析全月出勤率走勢 286
第12章 薪酬福利管理
12.1 員工基本工資表 291
12.1.1 創(chuàng)建員工基本工資表 291
12.1.2 設置公式計算工齡、工齡工資 292
12.2 員工福利補貼管理表 293
12.3 員工績效獎金計算表 296
12.4 加班統(tǒng)計表 297
12.4.1 創(chuàng)建加班統(tǒng)計表 297
12.4.2 創(chuàng)建加班費計算表 299
12.5 個人所得稅計算表 303
12.5.1 創(chuàng)建個人所得稅計算表 303
12.5.2 設置公式計算應納稅額 304
12.6 員工月度工資表 306
12.6.1 創(chuàng)建員工月度工資表 306
12.6.2 計算工資表中應發(fā)金額 307
12.6.3 計算工資表中應扣金額 311
12.7 員工月度工資部門匯總表 314
12.7.1 查詢實發(fā)工資過萬的員工 314
12.7.2 部門分類匯總統(tǒng)計 315
12.8 員工工資條 317
12.8.1 創(chuàng)建工資條 317
12.8.2 打印輸出工資條 319
第13章 企業(yè)日常費用報銷與費用支出管理分析
13.1 差旅費報銷明細表 322
13.1.1 創(chuàng)建差旅費報銷明細表 322
13.1.2 利用公式實現(xiàn)自動填寫 325
13.2 招聘培訓費明細表 330
13.3 業(yè)務招待費用報銷明細表 332
13.3.1 創(chuàng)建業(yè)務招待費用報銷明細表 332
13.3.2 設置公式計算合計值 333
13.4 建立企業(yè)費用支出匯總表 335
13.4.1 費用支出表標題、列標識格式設置 335
13.4.2 表格編輯區(qū)域數(shù)據(jù)驗證設置 336
13.5 日常費用數(shù)據(jù)透視分析 339
13.5.1 統(tǒng)計各部門各費用類別的支出金額 339
13.5.2 統(tǒng)計各類別費用支出的次數(shù) 341
13.6 日常費用支出預算表 343
13.6.1 使用移動平均法預測費用支出 344
13.6.2 使用指數(shù)平滑法預測費用支出 346
第14章 企業(yè)產(chǎn)品銷售數(shù)據(jù)管理與分析
14.1 銷售記錄表 349
14.1.1 創(chuàng)建銷售記錄表 349
14.1.2 表格編輯區(qū)域數(shù)據(jù)驗證設置 349
14.2 利用分類匯總分析銷售數(shù)據(jù) 353
14.2.1 按照收貨方統(tǒng)計金額 353
14.2.2 按照產(chǎn)品名稱統(tǒng)計金額 354
14.3 建立品種匯總報表 356
14.3.1 利用數(shù)據(jù)透視表匯總產(chǎn)品銷售情況 356
14.3.2 用圖表查看產(chǎn)品銷售情況 358
14.4 快速查詢各購貨單位銷售情況 360
14.4.1 建立各購貨單位條目查詢表 360
14.4.2 建立各購貨單位明細表 364
14.4.3 實現(xiàn)快速查詢 367
14.5 單位銷售明細表 368
第15章 企業(yè)生產(chǎn)管理數(shù)據(jù)處理與分析
15.1 年度生產(chǎn)成本分析 372
15.1.1 創(chuàng)建年度生產(chǎn)成本分析表 372
15.1.2 計算各月各項數(shù)據(jù) 372
15.1.3 年度生產(chǎn)成本趨勢分析圖表 376
15.2 各月生產(chǎn)成本年度比較 379
15.2.1 創(chuàng)建年度生產(chǎn)成本分析表 379
15.2.2 各月生產(chǎn)成本年度比較圖表 381
15.3 企業(yè)生產(chǎn)計劃決策分析 383
15.3.1 生產(chǎn)計劃及利潤預測分析 384
15.3.2 求解利潤最大化生產(chǎn)方案 387
15.4 生產(chǎn)成本預測 388
15.4.1 GROWTH()函數(shù)預測成本 389
15.4.2 因素分析法預測成本 390
15.5 生產(chǎn)設備管理表 393
第16章 企業(yè)庫存管理數(shù)據(jù)處理與分析
16.1 期初庫存 397
16.2 產(chǎn)品入庫記錄表 397
16.2.1 創(chuàng)建產(chǎn)品入庫記錄表 397
16.2.2 設置公式自動返回入庫產(chǎn)品的基本信息 398
16.3 利用分類匯總查看入庫情況 401
16.3.1 按照供貨商統(tǒng)計入庫金額 401
16.3.2 按照產(chǎn)品名稱統(tǒng)計入庫金額 402
16.4 產(chǎn)品出庫記錄表 404
16.4.1 創(chuàng)建產(chǎn)品出庫記錄表 404
16.4.2 設置公式自動返回出庫記錄表的基本信息 405
16.5 建立數(shù)據(jù)透視表進行產(chǎn)品出庫匯總 407
16.6 產(chǎn)品庫存月報表 409
16.6.1 設置公式返回基本信息 409
16.6.2 設置公式計算本月入庫、出庫數(shù)據(jù) 411
16.6.3 設置公式計算本月結余 414
16.6.4 設置公式判斷產(chǎn)品唯一性 416
16.6.5 設置條件格式進行庫存預警提示 416
16.7 庫存產(chǎn)品盤點表 417
16.7.1 創(chuàng)建庫存產(chǎn)品盤點表 417
16.7.2 設置公式自動返回庫存基本信息 418
16.7.3 設置條件格式查看產(chǎn)品盈虧 421
第17章 企業(yè)市場調(diào)查數(shù)據(jù)統(tǒng)計與分析
17.1 設計調(diào)查問卷和調(diào)查結果接收表 423
17.1.1 在Excel工作表中建立調(diào)查問卷 423
17.1.2 建立記錄調(diào)查結果的工作表 426
17.2 調(diào)查結果的匯總統(tǒng)計 429
17.2.1 匯總所有調(diào)查結果 429
17.2.2 替換編碼生成結果數(shù)據(jù)庫 430
17.3 樣本的組成分析 434
17.3.1 樣本性別組成分析 434
17.3.2 樣本年齡組成分析 438
17.4 產(chǎn)品認知度分析 441
17.4.1 分析產(chǎn)品知名度 441
17.4.2 年齡與產(chǎn)品認知度的關系 442
17.5 分析產(chǎn)品最受歡迎的特性 445
17.5.1 建立表格統(tǒng)計產(chǎn)品各特性被選中的次數(shù) 445
17.5.2 利用圖表直觀顯示出消費者喜歡的性能 446
17.6 分析產(chǎn)品的返修率 447
17.6.1 建立返修情況分析表格 447
17.6.2 利用圖表直觀顯示出返修情況 448
17.7 分析潛在消費者 449
第18章 企業(yè)客戶資源管理系統(tǒng)設計
18.1 系統(tǒng)功能結構分析 453
18.1.1 系統(tǒng)功能規(guī)劃 453
18.1.2 在工作簿中創(chuàng)建“主頁”工作與“數(shù)據(jù)庫”工作表 453
18.2 客戶資源管理系統(tǒng)主體界面 455
18.2.1 創(chuàng)建窗體并定義屬性 455
18.2.2 創(chuàng)建控件 457
18.2.3 功能塊的實現(xiàn) 462
18.3 添加/修改客戶信息功能系統(tǒng) 464
18.3.1 編寫代碼 464
18.3.2 運行測試 465
18.4 客戶信息查詢功能系統(tǒng) 467
18.4.1 編寫代碼 467
18.4.2 運行測試 468
18.5 客戶信息刪除功能系統(tǒng) 469
18.5.1 編寫代碼 469
18.5.2 運行測試 469
18.6 清空界面功能系統(tǒng) 470
18.6.1 編寫代碼 470
18.6.2 運行測試 470
18.7 客戶信息導出功能系統(tǒng) 471
18.7.1 編寫代碼 471
18.7.2 運行測試 471
18.8 為進入系統(tǒng)的按鈕指定宏 472
第19章 企業(yè)自動化銷售管理系統(tǒng)設計
19.1 系統(tǒng)功能結構描述 475
19.2 商品管理系統(tǒng) 475
19.2.1 在工作簿中創(chuàng)建“主頁”工作表與“商品列表”工作表 475
19.2.2 創(chuàng)建商品資料管理系統(tǒng)窗體 476
19.2.3 功能塊的實現(xiàn) 478
19.2.4 運行測試 479
19.2.5 為進入界面指定宏 480
19.3 訂單輸入系統(tǒng) 481
19.3.1 在工作簿中創(chuàng)建“主頁”工作表 481
19.3.2 創(chuàng)建訂單輸入界面 482
19.3.3 定義事件 484
19.3.4 運行測試 486
19.3.5 為進入界面指定宏 487
19.4 銷售數(shù)據(jù)統(tǒng)計分析系統(tǒng) 488
19.4.1 生成統(tǒng)計分析表 488
19.4.2 生成統(tǒng)計分析圖 490


第1部分 基 礎 篇
第1章 Excel 2013工作簿與工作表
1.1 工作簿的創(chuàng)建與保存 2
1.1.1 創(chuàng)建新工作簿 2
1.1.2 保存工作簿 4
1.2 工作表操作 7
1.2.1 重命名工作表 7
1.2.2 插入新工作表 7
1.2.3 刪除工作表 8
1.2.4 設置工作表標簽顏色 9
1.2.5 移動工作表到其他位置 10
1.2.6 復制工作表 11
1.3 單元格操作 11
1.3.1 插入、刪除單元格 12
1.3.2 插入行或列 13
1.3.3 合并單元格 15
1.3.4 設置單元格大小 16
1.3.5 隱藏含有重要數(shù)據(jù)的行或列 17
1.4 數(shù)據(jù)保護 18
1.4.1 保護工作表 18
1.4.2 加密保護工作簿 20
1.5 工作簿的共享使用 22
1.5.1 共享工作簿 22
1.5.2 云共享 26
第2章 表格數(shù)據(jù)的輸入與編輯
2.1 輸入各種類型數(shù)據(jù) 31
2.1.1 輸入文本內(nèi)容 31
2.1.2 輸入數(shù)值 33
2.1.3 日期與時間數(shù)據(jù)輸入 35
2.1.4 輸入特殊符號 37
2.2 數(shù)據(jù)批量輸入 39
2.2.1 批量輸入相同數(shù)據(jù) 39
2.2.2 填充輸入有規(guī)則數(shù)據(jù) 40
2.2.3 導入外部數(shù)據(jù) 43
2.3 數(shù)據(jù)有效性驗證 46
2.3.1 限制只能輸入指定類型數(shù)據(jù) 46
2.3.2 建立可選擇輸入序列 47
2.3.3 用公式建立驗證條件 49
2.3.4 設置鼠標指向時顯示輸入提示 50
2.3.5 圈釋無效數(shù)據(jù) 51
2.4 表格數(shù)據(jù)的修改、移動、復制與刪除 52
2.4.1 修改數(shù)據(jù) 52
2.4.2 移動數(shù)據(jù)到新位置 53
2.4.3 復制數(shù)據(jù)以實現(xiàn)快速輸入 54
2.4.4 利用“粘貼選項”功能選擇性粘貼 54
2.4.5 刪除數(shù)據(jù) 56
2.5 查找與替換數(shù)據(jù) 56
2.5.1 表格數(shù)據(jù)的查找 57
2.5.2 表格數(shù)據(jù)的替換 57
第3章 表格的美化設置及打印
3.1 表格字體與對齊方式設置 62
3.1.1 設置表格字體 62
3.1.2 設置表格對齊方式 63
3.2 表格邊框與底紋設置 65
3.2.1 設置表格邊框效果 65
3.2.2 設置表格底紋效果 66
3.3 在表格中使用圖片或圖形 68
3.3.1 插入圖片 68
3.3.2 圖片編輯 69
3.3.3 繪制圖形并美化 71
3.3.4 圖形立體效果 73
3.4 套用表格格式來美化表格 73
3.4.1 套用默認表格格式 74
3.4.2 新建表樣式 75
3.5 應用單元格樣式來美化表格 76
3.5.1 套用默認單元格樣式 76
3.5.2 新建單元格樣式 77
3.5.3 合并單元格樣式 80
3.5.4 利用格式刷快速復制單元格樣式 80
3.6 表格頁面設置 81
3.6.1 設置頁面 82
3.6.2 設置分頁 83
3.7 打印設置 85
3.7.1 添加打印區(qū)域 86
3.7.2 設置打印份數(shù)或打印指定頁 87
第2部分 高 級 篇
第4章 公式計算
4.1 認識公式 90
4.1.1 什么是公式 90
4.1.2 公式運算符 91
4.1.3 輸入與編輯公式 92
4.1.4 復制公式完成批量計算 94
4.2 公式計算中對單元格的引用 95
4.2.1 引用相對數(shù)據(jù)源計算 96
4.2.2 引用絕對數(shù)據(jù)源計算 97
4.2.3 引用其他工作表數(shù)據(jù)源計算 97
4.2.4 引用其他工作簿數(shù)據(jù)源計算 98
4.3 公式計算中函數(shù)的使用 99
4.3.1 初識函數(shù) 99
4.3.2 辦公常用函數(shù)簡介 101
4.4 公式中對名稱的使用 108
4.4.1 將單元格區(qū)域定義為名稱 109
4.4.2 使用定義的名稱建立公式 109
4.5 公式檢測與審核 111
4.5.1 公式查錯 111
4.5.2 公式逐步分解查錯 111
第5章 數(shù)據(jù)管理與分析
5.1 突出顯示滿足條件的數(shù)據(jù) 114
5.1.1 了解“條件格式” 114
5.1.2 條件格式設置 115
5.1.3 管理條件格式規(guī)則 120
5.2 數(shù)據(jù)的排序 122
5.2.1 按單個條件排序 122
5.2.2 按多個條件排序 122
5.3 數(shù)據(jù)的篩選 123
5.3.1 添加自動篩選 123
5.3.2 數(shù)字篩選 124
5.3.3 文本篩選 126
5.3.4 日期篩選 128
5.3.5 高級篩選 129
5.3.6 取消篩選 131
5.4 表格數(shù)據(jù)的分類匯總 132
5.4.1 創(chuàng)建分類匯總統(tǒng)計數(shù)據(jù) 132
5.4.2 創(chuàng)建多級分類匯總 133
5.4.3 取消分類匯總 134
5.5 合并計算功能 135
5.5.1 按位置合并計算 135
5.5.2 按類別合并計算 136
5.5.3 更改合并計算的函數(shù) 138
第6章 插入圖表分析數(shù)據(jù)
6.1 認識圖表 141
6.1.1 圖表結構 141
6.1.2 準確選中圖表中的對象 142
6.2 根據(jù)分析目的創(chuàng)建圖表 143
6.2.1 學會按照分析目的選擇圖表類型 143
6.2.2 使用推薦的圖表 146
6.2.3 更改創(chuàng)建的圖表類型 147
6.2.4 更改圖表的數(shù)據(jù)源 148
6.2.5 切換數(shù)據(jù)行列 149
6.3 使用迷你圖分析數(shù)據(jù) 150
6.3.1 創(chuàng)建迷你圖 150
6.3.2 編輯迷你圖 152
6.4 圖表的編輯設置 154
6.4.1 圖表大小及位置調(diào)整 154
6.4.2 圖表的復制 156
6.5 圖表對象編輯 158
6.5.1 快速應用圖表布局 158
6.5.2 編輯圖表坐標軸 159
6.5.3 編輯圖表數(shù)據(jù)系列 161
6.6 圖表的美化設置 162
6.6.1 套用圖表樣式一鍵美化 163
6.6.2 設置圖表文字格式 164
6.6.3 為對象套用形狀樣式 165
6.6.4 為對象設置邊框及填充 166
第7章 建立數(shù)據(jù)透視表分析數(shù)據(jù)
7.1 了解數(shù)據(jù)透視表 171
7.1.1 數(shù)據(jù)透視表的作用 171
7.1.2 數(shù)據(jù)透視表的結構 171
7.2 建立數(shù)據(jù)透視表分析數(shù)據(jù) 173
7.2.1 創(chuàng)建數(shù)據(jù)透視表 173
7.2.2 根據(jù)統(tǒng)計目的設置字段 175
7.3 數(shù)據(jù)透視表分析 177
7.3.1 數(shù)據(jù)透視表排序 177
7.3.2 數(shù)據(jù)透視表篩選 179
7.3.3 使用切片器篩選 180
7.3.4 使用“組合”對話框分組字段 181
7.4 數(shù)據(jù)透視表計算 182
7.4.1 更改默認的匯總方式 182
7.4.2 更改數(shù)據(jù)透視表的值顯示方式 184
7.4.3 自定義計算項 186
7.5 設置數(shù)據(jù)透視表布局及樣式 186
7.5.1 報表布局設置 187
7.5.2 套用數(shù)據(jù)透視表樣式 188
7.6 創(chuàng)建數(shù)據(jù)透視圖 189
7.6.1 創(chuàng)建數(shù)據(jù)透視圖 189
7.6.2 編輯數(shù)據(jù)透視圖 190
7.6.3 美化數(shù)據(jù)透視圖 192
第8章 高級分析工具的應用
8.1 安裝分析工具庫 196
8.2 模擬運算表 197
8.2.1 單變量模擬運算 197
8.2.2 雙變量模擬運算 198
8.2.3 將模擬運算結果轉(zhuǎn)換為常量 199
8.3 單變量求解 200
8.3.1 單變量求解預定銷售計劃 200
8.3.2 單變量求解可貸款金額 201
8.4 規(guī)劃求解 202
8.4.1 規(guī)劃求解確定最優(yōu)生產(chǎn)方案 202
8.4.2 規(guī)劃求解確定最優(yōu)運輸方案 206
8.5 方案管理器進行貸款方案決策 208
8.5.1 定義方案 209
8.5.2 得出最佳方案 211
8.6 數(shù)據(jù)分析工具 212
8.6.1 直方圖分析員工工資 212
8.6.2 銷量與利潤總額回歸分析 214
8.6.3 使用移動平均預測銷售額 216
第3部分 實 戰(zhàn) 篇
第9章 日常行政管理分析表格
9.1 辦公用品采購申請表 220
9.1.1 創(chuàng)建辦公用品采購申請表 220
9.1.2 設置公式計算金額 222
9.2 辦公用品領用登記表 224
9.2.1 創(chuàng)建辦公用品領用登記表 224
9.2.2 計算領用物品總價及判斷是否到期 228
9.3 值班人員提醒表 229
9.3.1 建立值班安排表 229
9.3.2 設置單元格格式實現(xiàn)自動提醒 231
9.4 假期值班人員安排表 232
9.4.1 設計值班表求解模型 232
9.4.2 進行規(guī)劃求解 234
9.4.3 根據(jù)規(guī)劃求解的值得出正確的排班日期 236
9.4.4 打印值班安排表 237
9.5 客戶資料管理表 239
9.5.1 建立客戶資料管理表 239
9.5.2 將客戶名稱鏈接到公司網(wǎng)頁 240
9.5.3 設置條件格式特殊顯示“大客戶” 241
9.5.4 篩選出指定條件的客戶信息 243
9.6 員工培訓成績統(tǒng)計分析表 245
9.6.1 建立員工培訓成績統(tǒng)計分析表 245
9.6.2 計算總成績、平均成績、名次 246
9.6.3 設置圖標集查看總分 247
9.6.4 篩選查看培訓合格情況 249
第10章 企業(yè)員工檔案管理與人事分析
10.1 建立檔案管理表 252
10.1.1 規(guī)劃員工檔案表框架 252
10.1.2 表格基本數(shù)據(jù)的輸入 252
10.1.3 設置公式自動返回相關信息 255
10.2 快速查詢?nèi)我鈫T工檔案 259
10.2.1 建立員工檔案查詢表框架 259
10.2.2 設置單元格的公式 261
10.2.3 實現(xiàn)快速查詢 263
10.3 分析員工學歷層次 263
10.3.1 建立數(shù)據(jù)透視表分析員工學歷層次 263
10.3.2 用圖表直觀顯示各學歷占比情況 264
10.4 分析員工年齡層次 266
10.4.1 建立數(shù)據(jù)透視表分析員工年齡層次 266
10.4.2 用圖表直觀顯示各年齡段人數(shù)占比情況 268
10.5 分析公司員工穩(wěn)定性 269
10.5.1 建立數(shù)據(jù)透視表統(tǒng)計各工齡段的人數(shù) 269
10.5.2 用圖表直觀顯示各年齡段人數(shù)占比情況 271
第11章 企業(yè)員工考勤管理與分析
11.1 員工考勤表 273
11.1.1 創(chuàng)建考勤表 273
11.1.2 設置條件格式顯示周末 275
11.1.3 填制考勤表 277
11.2 考勤統(tǒng)計表 278
11.2.1 統(tǒng)計各員工本月出勤數(shù)據(jù) 278
11.2.2 計算滿勤獎、應扣工資 280
11.3 本月各部門缺勤情況比較分析 281
11.3.1 建立數(shù)據(jù)透視表分析各部門缺勤情況 281
11.3.2 建立數(shù)據(jù)透視圖直觀比較缺勤情況 283
11.4 分析本月出勤情況 285
11.4.1 分析各工作天數(shù)對應人數(shù)的占比情況 285
11.4.2 分析全月出勤率走勢 286
第12章 薪酬福利管理
12.1 員工基本工資表 291
12.1.1 創(chuàng)建員工基本工資表 291
12.1.2 設置公式計算工齡、工齡工資 292
12.2 員工福利補貼管理表 293
12.3 員工績效獎金計算表 296
12.4 加班統(tǒng)計表 297
12.4.1 創(chuàng)建加班統(tǒng)計表 297
12.4.2 創(chuàng)建加班費計算表 299
12.5 個人所得稅計算表 303
12.5.1 創(chuàng)建個人所得稅計算表 303
12.5.2 設置公式計算應納稅額 304
12.6 員工月度工資表 306
12.6.1 創(chuàng)建員工月度工資表 306
12.6.2 計算工資表中應發(fā)金額 307
12.6.3 計算工資表中應扣金額 311
12.7 員工月度工資部門匯總表 314
12.7.1 查詢實發(fā)工資過萬的員工 314
12.7.2 部門分類匯總統(tǒng)計 315
12.8 員工工資條 317
12.8.1 創(chuàng)建工資條 317
12.8.2 打印輸出工資條 319
第13章 企業(yè)日常費用報銷與費用支出管理分析
13.1 差旅費報銷明細表 322
13.1.1 創(chuàng)建差旅費報銷明細表 322
13.1.2 利用公式實現(xiàn)自動填寫 325
13.2 招聘培訓費明細表 330
13.3 業(yè)務招待費用報銷明細表 332
13.3.1 創(chuàng)建業(yè)務招待費用報銷明細表 332
13.3.2 設置公式計算合計值 333
13.4 建立企業(yè)費用支出匯總表 335
13.4.1 費用支出表標題、列標識格式設置 335
13.4.2 表格編輯區(qū)域數(shù)據(jù)驗證設置 336
13.5 日常費用數(shù)據(jù)透視分析 339
13.5.1 統(tǒng)計各部門各費用類別的支出金額 339
13.5.2 統(tǒng)計各類別費用支出的次數(shù) 341
13.6 日常費用支出預算表 343
13.6.1 使用移動平均法預測費用支出 344
13.6.2 使用指數(shù)平滑法預測費用支出 346
第14章 企業(yè)產(chǎn)品銷售數(shù)據(jù)管理與分析
14.1 銷售記錄表 349
14.1.1 創(chuàng)建銷售記錄表 349
14.1.2 表格編輯區(qū)域數(shù)據(jù)驗證設置 349
14.2 利用分類匯總分析銷售數(shù)據(jù) 353
14.2.1 按照收貨方統(tǒng)計金額 353
14.2.2 按照產(chǎn)品名稱統(tǒng)計金額 354
14.3 建立品種匯總報表 356
14.3.1 利用數(shù)據(jù)透視表匯總產(chǎn)品銷售情況 356
14.3.2 用圖表查看產(chǎn)品銷售情況 358
14.4 快速查詢各購貨單位銷售情況 360
14.4.1 建立各購貨單位條目查詢表 360
14.4.2 建立各購貨單位明細表 364
14.4.3 實現(xiàn)快速查詢 367
14.5 單位銷售明細表 368
第15章 企業(yè)生產(chǎn)管理數(shù)據(jù)處理與分析
15.1 年度生產(chǎn)成本分析 372
15.1.1 創(chuàng)建年度生產(chǎn)成本分析表 372
15.1.2 計算各月各項數(shù)據(jù) 372
15.1.3 年度生產(chǎn)成本趨勢分析圖表 376
15.2 各月生產(chǎn)成本年度比較 379
15.2.1 創(chuàng)建年度生產(chǎn)成本分析表 379
15.2.2 各月生產(chǎn)成本年度比較圖表 381
15.3 企業(yè)生產(chǎn)計劃決策分析 383
15.3.1 生產(chǎn)計劃及利潤預測分析 384
15.3.2 求解利潤最大化生產(chǎn)方案 387
15.4 生產(chǎn)成本預測 388
15.4.1 GROWTH()函數(shù)預測成本 389
15.4.2 因素分析法預測成本 390
15.5 生產(chǎn)設備管理表 393
第16章 企業(yè)庫存管理數(shù)據(jù)處理與分析
16.1 期初庫存 397
16.2 產(chǎn)品入庫記錄表 397
16.2.1 創(chuàng)建產(chǎn)品入庫記錄表 397
16.2.2 設置公式自動返回入庫產(chǎn)品的基本信息 398
16.3 利用分類匯總查看入庫情況 401
16.3.1 按照供貨商統(tǒng)計入庫金額 401
16.3.2 按照產(chǎn)品名稱統(tǒng)計入庫金額 402
16.4 產(chǎn)品出庫記錄表 404
16.4.1 創(chuàng)建產(chǎn)品出庫記錄表 404
16.4.2 設置公式自動返回出庫記錄表的基本信息 405
16.5 建立數(shù)據(jù)透視表進行產(chǎn)品出庫匯總 407
16.6 產(chǎn)品庫存月報表 409
16.6.1 設置公式返回基本信息 409
16.6.2 設置公式計算本月入庫、出庫數(shù)據(jù) 411
16.6.3 設置公式計算本月結余 414
16.6.4 設置公式判斷產(chǎn)品唯一性 416
16.6.5 設置條件格式進行庫存預警提示 416
16.7 庫存產(chǎn)品盤點表 417
16.7.1 創(chuàng)建庫存產(chǎn)品盤點表 417
16.7.2 設置公式自動返回庫存基本信息 418
16.7.3 設置條件格式查看產(chǎn)品盈虧 421
第17章 企業(yè)市場調(diào)查數(shù)據(jù)統(tǒng)計與分析
17.1 設計調(diào)查問卷和調(diào)查結果接收表 423
17.1.1 在Excel工作表中建立調(diào)查問卷 423
17.1.2 建立記錄調(diào)查結果的工作表 426
17.2 調(diào)查結果的匯總統(tǒng)計 429
17.2.1 匯總所有調(diào)查結果 429
17.2.2 替換編碼生成結果數(shù)據(jù)庫 430
17.3 樣本的組成分析 434
17.3.1 樣本性別組成分析 434
17.3.2 樣本年齡組成分析 438
17.4 產(chǎn)品認知度分析 441
17.4.1 分析產(chǎn)品知名度 441
17.4.2 年齡與產(chǎn)品認知度的關系 442
17.5 分析產(chǎn)品最受歡迎的特性 445
17.5.1 建立表格統(tǒng)計產(chǎn)品各特性被選中的次數(shù) 445
17.5.2 利用圖表直觀顯示出消費者喜歡的性能 446
17.6 分析產(chǎn)品的返修率 447
17.6.1 建立返修情況分析表格 447
17.6.2 利用圖表直觀顯示出返修情況 448
17.7 分析潛在消費者 449
第18章 企業(yè)客戶資源管理系統(tǒng)設計
18.1 系統(tǒng)功能結構分析 453
18.1.1 系統(tǒng)功能規(guī)劃 453
18.1.2 在工作簿中創(chuàng)建“主頁”工作與“數(shù)據(jù)庫”工作表 453
18.2 客戶資源管理系統(tǒng)主體界面 455
18.2.1 創(chuàng)建窗體并定義屬性 455
18.2.2 創(chuàng)建控件 457
18.2.3 功能塊的實現(xiàn) 462
18.3 添加/修改客戶信息功能系統(tǒng) 464
18.3.1 編寫代碼 464
18.3.2 運行測試 465
18.4 客戶信息查詢功能系統(tǒng) 467
18.4.1 編寫代碼 467
18.4.2 運行測試 468
18.5 客戶信息刪除功能系統(tǒng) 469
18.5.1 編寫代碼 469
18.5.2 運行測試 469
18.6 清空界面功能系統(tǒng) 470
18.6.1 編寫代碼 470
18.6.2 運行測試 470
18.7 客戶信息導出功能系統(tǒng) 471
18.7.1 編寫代碼 471
18.7.2 運行測試 471
18.8 為進入系統(tǒng)的按鈕指定宏 472
第19章 企業(yè)自動化銷售管理系統(tǒng)設計
19.1 系統(tǒng)功能結構描述 475
19.2 商品管理系統(tǒng) 475
19.2.1 在工作簿中創(chuàng)建“主頁”工作表與“商品列表”工作表 475
19.2.2 創(chuàng)建商品資料管理系統(tǒng)窗體 476
19.2.3 功能塊的實現(xiàn) 478
19.2.4 運行測試 479
19.2.5 為進入界面指定宏 480
19.3 訂單輸入系統(tǒng) 481
19.3.1 在工作簿中創(chuàng)建“主頁”工作表 481
19.3.2 創(chuàng)建訂單輸入界面 482
19.3.3 定義事件 484
19.3.4 運行測試 486
19.3.5 為進入界面指定宏 487
19.4 銷售數(shù)據(jù)統(tǒng)計分析系統(tǒng) 488
19.4.1 生成統(tǒng)計分析表 488
19.4.2 生成統(tǒng)計分析圖 490