Excel數據處理與分析案例及實戰

《Excel數據處理與分析案例及實戰》是2019年9月電子工業出版社出版的圖書，作者是鄭小玲、劉威。

基本介紹

• 書名：Excel數據處理與分析案例及實戰
• 作者：鄭小玲、劉威
• 出版社：電子工業出版社
• 出版時間：2019年9月
• 頁數：292 頁
• 定價：56 元
• 開本：16 開
• ISBN：9787121369995

內容簡介

本書基於Office 2010平台編寫，通過精選的多個案例從不同的方面反映Excel的主要套用；通過模擬實際任務，詳細介紹其工作流程和操作方法，並且針對每項套用任務，介紹與其相關的Excel軟體知識、數據處理方法和操作技巧。本書共分為6個項目，每個項目有多個任務，每個任務包括任務導入、任務製作、知識點拓展和獨立實踐任務等四個部分。任務導入以任務為主題，介紹任務背景、要求以及對任務涉及的知識點進行分析，使讀者了解任務的背景、任務的具體要求以及完成此任務所需要的知識和操作。任務製作有非常好的重現性，通過模擬實際套用中的具體任務，使讀者真實地體會Excel解決實際問題的工作流程和操作方法。知識點拓展通過深入、細緻地介紹Excel軟體的相關知識、數據處理方法和操作技巧，使學生系統化地掌握Excel的知識體系。獨立實踐任務通過完成具體任務，使學生對Excel的套用有一個整體把握，培養學生獨立思考和獨立工作的能力。本書在編寫過程中力求深入淺出、通俗易懂，特別注意突出實用性的特點，並綜合運用實例和實訓，以使讀者能在較短的時間內掌握Excel。本書既可作為讀者自學教材，也可作為高等院校相關課程教材或參考書，還可作為社會各類學校的培訓教材。

圖書目錄

目錄

項目一建立人事檔案.......................................................................................1

任務1.1 製作人事檔案表

——Excel 工作表的建立與保存 ...........2

1.1.1　任務導入 .....................................................2

1.1.2　模擬實施任務 .............................................3

創建新工作簿| 保存工作簿| 重新組織工

作簿| 輸入表頭數據| 輸入工號| 輸入姓

名| 輸入性別| 輸入出生日期| 輸入學歷|

輸入籍貫| 輸入身份證號| 保存

1.1.3　拓展知識點 .................................................7

Excel 概述| 工作簿、工作表與單元格|

Excel 操作界面| 創建工作簿| 保存工作簿

| 工作表基本操作| 數據類型| 輸入文本|

輸入數值| 自動填充數據| 查找與替換數

據| 輸入日期和時間| 數據有效性| 在下

拉列表中選擇數據| 輸入身份證號

1.1.4　延伸知識點 ...............................................26

從文本檔案導入數據| 從Access 資料庫

導入數據| 改變工作表標籤顏色

1.1.5　獨立實踐任務 ...........................................27

1.1.6　課後練習 ...................................................28

任務1.2 修飾人事檔案表

——Excel 工作表的編輯與格式化 ......29

1.2.1　任務導入 ...................................................29

1.2.2　模擬實施任務 ...........................................30

打開工作簿| 移動“部門”列至“工號”

和“姓名”列之間| 移動“學歷”列至“職

稱”和“參加工作日期”列之間| 添加標

題| 設定標題和表頭格式| 設定“性別”

等列信息居中顯示| 設定“出生日期”顯

示格式| 設定工作表表格框線| 突出顯示

“未婚”信息| 設定工作表背景| 設定只

在所選單元格區域中顯示背景| 隱藏“身

份證號”列| 保護“身份證號”的隱藏信

息不被顯示| 保存“人事檔案表”

1.2.3　拓展知識點 ...............................................36

打開工作簿| 選定行與列| 插入整行或整

列| 移動、複製行或列| 刪除整行或整列|

合併單元格| 調整行高或列寬| 字型| 對

齊方式| 數字格式| 框線| 條件格式| 設定

工作表背景| 設定填充格式| 隱藏行或列|

保護工作表

1.2.4　延伸知識點 ...............................................53

插入或刪除單元格| 自定義數字格式

1.2.5　獨立實踐任務 ...........................................55

1.2.6　課後練習 ...................................................56

任務1.3 輸出人事檔案表

——Excel 工作表的顯示與列印 .........58

1.3.1　任務導入 ...................................................58

1.3.2 　模擬實施任務 ..........................................59

打開工作簿| 將“身份證號”列顯示出

來| 設定表格為橫向列印| 設定列印頁邊

距和居中方式| 設定每頁均顯示表格行標

題| 設定在每頁上端顯示表格名稱和公司

Logo | 設定在每頁下端顯示頁碼和時間|

列印預覽人事檔案表| 按部門列印預覽人

事檔案表

1.3.3　拓展知識點 ...............................................63

頁面| 頁邊距| 列印標題| 頁眉/ 頁腳| 打

印區域| 列印預覽| 分頁預覽| 列印工作表

1.3.4　延伸知識點 ...............................................70

縮放視窗| 拆分視窗| 凍結視窗| 並排查看

1.3.5　獨立實踐任務 ...........................................72

1.3.6　課後練習 ...................................................73

項目二核算員工薪酬.....................................................................................75

任務2.1 計算工資表

——Excel 公式的套用與簡單計算 ......76

2.1.1　任務導入 ...................................................76

2.1.2　模擬實施任務 ...........................................77

打開工作簿| 計算公積金| 計算醫療險、

養老險和失業險| 計算洗理費| 計算書報

費| 計算獎金| 計算應發工資| 計算稅金|

計算應納稅所得額| 計算實發工資| 保存

工資表

2.1.3　拓展知識點 ...............................................83

公式| 運算符| 輸入公式| 編輯公式| 單元

格引用| 函式及輸入|IF 函式|LEFT 函式|

SUM 函式

2.1.4　延伸知識點 ...............................................92

使用公式的常見錯誤| 追蹤單元格| 追蹤

錯誤| 添加監視| 查看公式計算結果

2.1.5　獨立實踐任務 ...........................................95

2.1.6　課後練習 ...................................................96

任務2.2 計算獎金表

——Excel 函式的套用與複雜計算 ......97

2.2.1　任務導入 ...................................................97

2.2.2　模擬實施任務 ...........................................99

打開工作簿| 計算累計銷售業績| 計算獎

金百分比| 計算本月銷售業績| 計算獎勵

獎金| 計算總獎金| 計算累計銷售額| 查

找並顯示業務員姓名| 計算數量合計和銷

售額合計| 計算銷售數量占總銷售數量的

百分比| 計算銷售額占總銷售額的百分比

2.2.3　拓展知識點 .............................................104

函式及函式種類| VLOOKUP 函式| HLOOKUP

函式|SUMIF 函式

2.2.4　延伸知識點 .............................................107

數學函式| 統計函式| 日期與時間函式|

邏輯函式| 文本函式| 查找與引用函式|

財務函式

2.2.5　獨立實踐任務 ......................................... 119

2.2.6　課後練習 .................................................120

任務2.3 調整工資表

—Excel 工作表的合併計算 ...........122

2.3.1　任務導入 .................................................122

2.3.2　模擬實施任務 .........................................123

打開工作簿| 將所有員工的薪級工資上調

100 元| 按不同幅度調整員工的薪級工資|

調整部分員工的薪級工資

2.3.3　拓展知識點 .............................................125

選擇性貼上| 合併計算概述| 按位置合併

計算| 按分類合併計算

2.3.4　延伸知識點 .............................................129

使用剪貼簿進行貼上| 自動更新合併計算

的數據

2.3.5　獨立實踐任務 .........................................131

2.3.6　課後練習 .................................................133

項目三顯示銷售業績...................................................................................135

任務3.1 製作銷售業績比較圖

——Excel 圖表的製作與套用 ...........136

3.1.1　任務導入 .................................................136

3.1.2　模擬實施任務 .........................................137

打開工作簿| 創建銷售趨勢迷你圖| 計算

累計銷售額最大值| 創建銷售業績折線圖|

更改圖表類型| 標記最大值| 刪除圖例

3.1.3　拓展知識點 .............................................139

圖表| 創建迷你圖| NA 函式| 創建圖表|

更改圖表類型| 設定數據標籤位置| 刪除

圖表元素

3.1.4　延伸知識點 .............................................146

編輯迷你圖| 編輯圖表

3.1.5　獨立實踐任務 .........................................149

3.1.6　課後練習 .................................................149

任務3.2 製作銷售業績動態顯示圖

——Excel 函式與圖表的綜合套用 ....151

3.2.1　任務導入 .................................................151

3.2.2　模擬實施任務 .........................................152

打開工作簿| 創建業務員姓名數據輸入列

表| 查找並顯示業務員銷售業績| 創建銷

售業績柱形圖| 刪除圖例並調整圖示標題

位置| 取消和添加格線線| 更改數值軸數

值顯示間隔| 更改數據系列顯示寬度| 為

繪圖區添加背景和框線| 為圖表標題添加

背景和框線

3.2.3　拓展知識點 .............................................155

添加或取消格線線| 坐標軸格式| 數據系

列格式| 繪圖區格式| 標題格式

3.2.4　延伸知識點 .............................................158

圖表格式| 美化三維圖表

3.2.5　獨立實踐任務 .........................................161

3.2.6　課後練習 .................................................162

項目四管理人事檔案...................................................................................165

任務4.1 分類統計人事檔案表

——Excel 數據的排序與分類匯總 ....166

4.1.1　任務導入 .................................................166

4.1.2　模擬實施任務 .........................................167

打開工作簿| 按婚姻狀況排序| 按姓氏筆

劃排序| 按部門升序和學歷由高到低排序

| 計算每個部門每種學歷的員工人數| 將

每個部門員工人數的計算結果存入新表

4.1.3　拓展知識點 .............................................171

數據列表| 按一個欄位排序| 按筆劃多少

排序| 按多個欄位排序| 自定義排序| 創

建分類匯總| 分級顯示數據| 按條件定位

4.1.4　延伸知識點 .............................................177

對部分數據進行排序| 清除分類匯總

4.1.5　獨立實踐任務 .........................................177

4.1.6　課後練習 .................................................178

任務4.2 查詢人事檔案表

——Excel 數據的篩選與套用 ...........180

4.2.1　任務導入 .................................................180

4.2.2　模擬實施任務 .........................................181

打開工作簿| 查詢未婚的男員工| 查詢中

級職稱的員工| 查詢年齡最小的5 位員工，

並按年齡從小到大排序| 查詢年齡高於所

有員工平均年齡的員工| 查詢高級職稱

1990 年以前和初級職稱2000 年以後參加

工作的員工

4.2.3　拓展知識點 .............................................184

自動篩選| 自定義自動篩選| 自動篩選前

10 個| 高級篩選

4.2.4　延伸知識點 .............................................189

清除自動篩選條件| 刪除重複記錄數據

4.2.5　獨立實踐任務 .........................................190

4.2.6　課後練習 .................................................191

項目五分析銷售情況...................................................................................193

任務5.1 製作銷售情況分析表

——Excel 數據透視表的創建與套用 ...194

5.1.1　任務導入 .................................................194

5.1.2　模擬實施任務 .........................................195

打開工作簿| 創建統計每月每種產品銷售

量的數據透視表| 分析各產品與銷量最好

的產品之間的數據差異| 統計每名業務員

每種產品的銷售額占其總銷售額的百分

比| 創建每名業務員不同產品的總銷售額

及排名的數據透視表

5.1.3　拓展知識點 .............................................198

認識數據透視表| 創建數據透視表| 組合

數據透視表內的數據項| 更改數據透視表

的值顯示方式| 隱藏和顯示行總計或列總

計| 在數據透視表中添加、刪除、移動字

段| 排列數據透視表中指定欄位的次序|

改變數據透視表的顯示格式

5.1.4　延伸知識點 .............................................205

調整數據透視表欄位的布局| 更改數據

透視表的值匯總方式| 排序和篩選數據

透視表中的數據| 刷新數據透視表內的

數據| 分頁顯示數據透視表的數據| 顯示

數據透視表中的明細數據| 調整數據透

視表的樣式

5.1.5　獨立實踐任務 .........................................209

5.1.6　課後練習 ................................................. 211

任務5.2 製作銷售情況動態分析圖

——Excel 數據透視圖的創建及切片器

的套用 ..............................................212

5.2.1　任務導入 .................................................212

5.2.2　模擬實施任務 .........................................213

打開工作簿| 創建計算業務員每季度累計

銷售額的數據透視圖| 修飾業務員每季度

累計銷售額的數據透視圖| 設定按業務員

動態顯示每季度累計銷售額| 生成某業務

員每季度累計銷售額的變化趨勢圖

5.2.3　知識點拓展 .............................................217

認識數據透視圖| 創建數據透視圖| 隱藏

和顯示數據透視圖中的欄位按鈕| 切片器

及套用| 顯示和隱藏數據透視表欄位列表

| 在數據透視圖中添加欄位| 設定雙坐標

軸並顯示次坐標軸

5.2.4　延伸知識點 .............................................222

調整數據透視圖| 篩選數據透視圖中的數據

5.2.5　獨立實踐任務 .........................................222 5.2.6　課後練習 .................................................224

項目六分析處理進銷存數據.......................................................................225

任務6.1 分析採購成本

——Excel 表單控制項的添加與套用 ....226

6.1.1　任務導入 .................................................226

6.1.2　模擬實施任務 .........................................227

打開工作簿| 計算“採購成本分析表”中

的所有項目| 在“採購成本分析表”中添

加滾動條控制項| 創建採購成本分析折線圖

6.1.3　拓展知識點 .............................................230

INDEX 函式| 常用控制項| 在工作表中添加

控制項| 設定控制項格式

6.1.4　延伸知識點 .............................................232

宏| 錄製宏| 編輯宏| 套用宏

6.1.5　獨立實踐任務 .........................................234

6.1.6　課後練習 .................................................236

任務6.2 預測銷售趨勢

——Excel 趨勢線的創建與套用 .......237

6.2.1　任務導入 .................................................237

6.2.2　模擬實施任務 .........................................238

打開工作簿| 創建計算每個月銷售量合計

的數據透視表| 創建銷售量歷史數據折線

圖| 添加趨勢線和趨勢方程| 預測銷售量

6.2.3　拓展知識點 .............................................240

趨勢線| 添加趨勢線| 自定義數字顯示格式

6.2.4　延伸知識點 .............................................243

修飾趨勢線

6.2.5　獨立實踐任務 .........................................244

6.2.6　課後練習 .................................................245

任務6.3 分析庫存積壓與短缺情況

——Excel 條件格式及批註的套用 ....246

6.3.1　任務導入 .................................................246

6.3.2　模擬實施任務 .........................................247

打開工作簿| 計算本期採購數量、本期銷

售數量和月末數量| 設定庫存積壓和庫存

短缺所在單元格的顯示格式| 添加批註顯

示庫存積壓和庫存短缺提示信息

6.3.3　拓展知識點 .............................................249

使用公式設定條件格式| 新建批註

6.3.4　延伸知識點 .............................................251

使用批註| 刪除批註

6.3.5　獨立實踐任務 .........................................252

6.3.6　課後練習 .................................................253

項目七共享客房入住信息...........................................................................255

任務7.1 建立客房入住信息表

——Excel 共享工作簿的創建與套用 ...256

7.1.1　任務導入 .................................................256

7.1.2　模擬實施任務 .........................................257

創建“客房入住信息表”工作表| 設定工

作表輸入規則| 設定工作表格式| 添加圖

片| 設定表格框線| 創建共享工作簿| 輸

入客人預訂信息| 輸入客人入住信息| 保

存共享工作簿

7.1.3　拓展知識點 .............................................260

插入圖片| 創建共享工作簿| 使用共享工

作簿

7.1.4　延伸知識點 .............................................264

形狀| 藝術字| 設定共享工作簿| 修訂共

享工作簿| 停止共享工作簿

7.1.5　獨立實踐任務 .........................................267

7.1.6　課後練習 .................................................268

任務7.2 製作客房入住管理平面圖

——Excel 超連結的創建與套用 .......269

7.2.1　任務導入 .................................................269

7.2.2　模擬實施任務 .........................................270

停止共享工作簿| 建立“客房入住管理平

面圖”工作表| 設定突出顯示已入住客房|

創建從客房入住管理平面圖跳轉至客房

入住信息表的超連結| 測試超連結效果|

創建從客房入住信息表跳轉至客房入住

管理平面圖的超連結

7.2.3　知識點拓展 .............................................273

LOOKUP 函式| 超連結

7.2.4　延伸知識點 .............................................277

編輯超連結| 將工作簿發布到Internet 上

| 將Excel 數據連結到Word 文檔中| 將

Excel 數據嵌入Word 文檔中

7.2.5　獨立實踐任務 .........................................280

7.2.6　課後練習 .................................................281