Excel數(shù)據(jù)分析辭典（雙色）

目　　錄
第1章　定義Excel數(shù)據(jù)分析環(huán)境1
分類檢索． 1
用戶環(huán)境．．．．． 1
建立數(shù)據(jù)表格． 1
安全保護數(shù)據(jù)． 1
名詞術(shù)語． 2
Microsoft賬戶．．． 2
工作表標簽． 2
功能區(qū)/選項卡/組．．．．． 2
合并單元格/合并后居中/跨越合并． 2
001 用戶操作環(huán)境設(shè)置．． 3
難易度 ★　　適用版本：Excel 2003/2007/2010/2013
實例1-1：Microsoft用戶賬戶設(shè)置．．．． 3
實例1-2：定制Office背景和Office主題 5
實例1-3：設(shè)置默認工作表數(shù)與標準字體．．．． 6
實例1-4：自由控制功能區(qū)顯示 8
實例1-5：自定義選項卡和組．． 11
實例1-6：自定義界面顯示元素．．．． 13
002 Excel數(shù)據(jù)表格的建立．15
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例1-7：創(chuàng)建員工通訊錄工作表 15
實例1-8：創(chuàng)建周末加班申請表．．．． 17
003 數(shù)據(jù)的安全保護 ．19
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例1-9：設(shè)置密碼保護員工工資工作簿．． 19
實例1-10：將部門工作分配工作簿標記為最終狀態(tài)進行保護． 21
實例1-11：保護面試最終結(jié)果工作表限制人員編輯．．． 23
實例1-12：設(shè)置只允許填寫考核成績表中的成績． 24
第2章　數(shù)據(jù)的輸入．27
分類檢索．．．．． 27
各類數(shù)據(jù)的輸入．．．．． 27
目錄．indd 5 2014-3-27 17：24：26
VI Excel數(shù)據(jù)分析辭典
規(guī)律與非規(guī)律數(shù)據(jù)的輸入．． 27
有效數(shù)據(jù)的輸入．．．．． 28
不同的輸入方式．．．．． 28
名詞術(shù)語．．．．． 28
數(shù)據(jù)類型．．． 28
文本型數(shù)字．．． 29
科學計數(shù)．．． 29
004 數(shù)字和文本的輸入 29
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例2-1：輸入以001開頭的學員編號．． 30
實例2-2：輕松輸入身份證號．． 30
實例2-3：登記職員住房面積時自動插入小數(shù)點 32
實例2-4：橫向登記員工當月工資明細 33
005 日期和時間的輸入 34
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例2-5：登記銷售流水賬 34
006 分數(shù)的輸入
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例2-6：會議出席情況統(tǒng)計．． 35
007 金額的輸入
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例2-7：快速填制費用報銷單中的大寫金額 36
008 特殊字符的輸入 ．37
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例2-8：填寫員工考勤記錄．． 37
實例2-9：制作轉(zhuǎn)賬憑證 38
009 相同數(shù)據(jù)的輸入 ．39
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例2-10：不同職位崗位工資的快速填制 40
實例2-11：快速制作多份記賬憑證表單．．．． 40
010 規(guī)律數(shù)據(jù)的輸入 ．41
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例2-12：復制填充連續(xù)的調(diào)薪時間．． 42
實例2-13：序列填充連續(xù)的產(chǎn)品編號．． 43
實例2-14：自動為日常工作日程表填充工作日． 44
實例2-15：快速輸入幾個一組的固定商品名稱． 45
目錄．indd 6 2014-3-27 17：24：26
目錄 VII
01
實例2-16：員工姓與名的快速分列輸入．．．． 46
011 隨機數(shù)據(jù)的輸入 ．47
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例2-17：使用RAND()函數(shù)輸入隨機座位編號 47
012 有效數(shù)據(jù)的輸入 ．48
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例2-18：輸入不超過10分的有效評分．．．． 48
實例2-19：輸入不在當前日期范圍的值班日期． 49
實例2-20：輸入不重復的員工工號．． 50
實例2-21：有效輸入不小于當前時間的簽到．． 51
實例2-22：輸入不超過總預算額的單項訂購金額． 52
實例2-23：最高采購量輸入提醒．． 53
013 列表選擇輸入 ．．．．．54
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例2-24：從右鍵下拉列表中選擇年齡段 54
實例2-25：使用有效性列表選擇輸入所屬部門． 55
實例2-26：快速選擇員工出生省份和對應(yīng)的城市． 56
014 選擇性粘貼輸入 ．58
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例2-27：選擇性粘貼將各分公司營業(yè)收入金額由元轉(zhuǎn)換為萬元．．．． 59
015 記錄單輸入
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例2-28：使用記錄單登記產(chǎn)品入庫情況 60
第3章　格式化數(shù)據(jù)讓其更易分析．．．．．62
分類檢索．．．．． 62
數(shù)據(jù)的對齊與排列． 62
動動鼠標讓數(shù)據(jù)顯示更明確．．．． 62
讓數(shù)據(jù)按個人意愿顯示．． 62
按條件突出顯示數(shù)據(jù)．．． 63
名詞術(shù)語．．．．． 63
貨幣．．．．． 63
千位分隔符．．． 63
占位符． 63
條件格式．．． 64
數(shù)據(jù)條． 64
目錄．indd 7 2014-3-27 17：24：26
VIII Excel數(shù)據(jù)分析辭典
色階．．．．． 64
圖標集． 64
公式．．．．． 64
016 數(shù)據(jù)的對齊．．．．64
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例3-1：對齊產(chǎn)品銷售單價中小數(shù)點 65
017 數(shù)據(jù)的排列
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例3-2：讓日記賬中的月/日分列顯示．．．．． 66
實例3-3：換行顯示計量單位．． 68
實例3-4：實現(xiàn)銷售統(tǒng)計表的行列轉(zhuǎn)換 68
實例3-5：比較產(chǎn)銷記錄表中兩個間隔較遠的字段．．．．． 69
018 調(diào)整小數(shù)位數(shù)
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例3-6：快速增加員工離職率小數(shù)位 70
實例3-7：保證銷售回款率計算結(jié)果的精確度 71
019 數(shù)據(jù)的貨幣顯示
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例3-8：為金額添加人民幣符號 72
實例3-9：為工資表中的工資數(shù)額應(yīng)用會計專用格式． 74
020 顯示千位分隔符．．75
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例3-10：給應(yīng)收賬款添加千位分隔符以便查看． 75
021 用文本占位符自動添加文本．．76
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例3-11：在產(chǎn)品編號中自動添加固定型號．． 76
022 數(shù)字占位符的格式占位．．．78
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例3-12：將客戶編號用“0”補足為4位 78
實例3-13：用“#”統(tǒng)一金額格式 79
023 字符的重復顯示．．80
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例3-14：用“*”重復填充隱私信息 80
024 按條件顯示格式．．81
難易度 ★★★★★　　適用版本：Excel 2003/2007/2010/2013
實例3-15：用盈利/虧損替代具體的金額數(shù)字． 82
目錄．indd 8 2014-3-27 17：24：26
目錄 IX
01
025 靈活顯示日期時間．82
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例3-16：客戶來電日期時間的靈活設(shè)置 83
026 將滿足一定范圍的數(shù)據(jù)以某種格式顯示．．84
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例3-17：突出顯示能承受的貸款每月固定償還額．．． 85
實例3-18：標識本周新進人員 86
實例3-19：將兼職人員區(qū)別顯示．． 87
實例3-20：突出顯示重復的電話號碼．． 88
實例3-21：將積分排名前10%的員工以特定格式顯示．．．．． 89
實例3-22：找出工資表中高于平均收入的員工． 90
027 將數(shù)值大小以數(shù)據(jù)條長短反映．．91
難易度 ★★★　　適用版本： Excel 2007/2010/2013
實例3-23：使用實心數(shù)據(jù)條分析產(chǎn)品銷售狀況． 92
實例3-24：用負值條形圖表現(xiàn)收入盈虧．．．． 92
實例3-25：隱藏工資條形圖的工資數(shù)額．．．． 94
028 將數(shù)值高低以色階過渡呈現(xiàn)．．95
難易度 ★★　　適用版本：Excel 2007/2010/2013
實例3-26：用雙色刻度分析員工培訓成績 95
實例3-27：用三色刻度分析產(chǎn)品需求量．．．． 96
029 用圖標區(qū)分不同范圍的數(shù)據(jù)．．97
難易度 ★★★　　適用版本：Excel 2007/2010/2013
實例3-28：以箭頭形狀明示職工業(yè)績等級 97
實例3-29：用象限圖標識項目完成進度．．．． 98
030 自定義為滿足公式條件的數(shù)據(jù)設(shè)置格式．．99
難易度 ★★★★　　適用版本：Excel 2007/2010/2013
實例3-30：合同到期的提前提醒．． 99
實例3-31：最低庫存預警 101
第4章　數(shù)據(jù)的查找與更新．．103
分類檢索．．． 103
“查找”對話框查找． 103
名稱與記錄單查找．．．．． 103
定位查找． 103
名詞術(shù)語．．． 104
查找/替換/轉(zhuǎn)到/定位．． 104
目錄．indd 9 2014-3-27 17：24：27
X Excel數(shù)據(jù)分析辭典
常量．．． 104
關(guān)鍵字．．．．． 104
匹配查找． 105
空值．．． 105
在多個單元格中輸入數(shù)據(jù) 105
031 關(guān)鍵字查找
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例4-1：查找并快速更新訂購商品的單位．． 106
032 按格式查找
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例4-2：查找最新進貨的生鮮類食品．．．． 108
033 模糊查找
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例4-3：查找護理頭部的洗護商品．． 110
034 匹配查找
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例4-4：查找已到使用期限的固定資產(chǎn) 110
035 名稱查找
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例4-5：快速查找并更新折扣率．． 111
036 記錄單查找
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例4-6：使用記錄單瀏覽修改固定資產(chǎn)表．． 112
實例4-7：查找數(shù)量在2臺以上的記錄 114
037 差異查找
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例4-8：使用差異查找輔助對賬．． 115
038 查找數(shù)字
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例4-9：查找零售價格表中的單價并統(tǒng)一應(yīng)用貨幣格式 116
039 查找公式結(jié)果
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例4-10：查看近期商品出庫金額 118
040 查找錯誤值
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例4-11：查找排名表中出現(xiàn)的公式錯誤．．．． 119
目錄．indd 10 2014-3-27 17：24：27
目錄 XI
01
041 查找空值
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例4-12：在現(xiàn)有庫存的空單元格中快速輸入“缺貨”字樣．．．． 120
第5章　排序、篩選和分類匯總123
分類檢索．．． 123
數(shù)據(jù)的排序． 123
數(shù)據(jù)的篩選． 123
數(shù)據(jù)的分類匯總．．． 124
名詞術(shù)語．．． 124
關(guān)鍵字/字段 124
排序/篩選/分類匯總．．． 125
分類匯總/分級顯示．．．． 125
合并計算． 125
042 單一條件排序．．．．125
難易度 ★　　適用版本：Excel 2003/2007/2010/2013
實例5-1：將員工健康檢查評分由高到低排列． 126
043 多條件排序
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例5-2：按入職時間與當前崗位薪資排序員工信息表 126
044 按漢語拼音排序128
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例5-3：按考場人員姓名的漢語拼音排序編制考場名單 128
045 按筆劃排序
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例5-4：按考場人員姓名的筆劃編制考場名單． 130
046 自定義排序．．132
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-5：使用自定義排序制作部門工作計劃． 132
047 不規(guī)則編號排序133
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-6：按辦公用品編號排序整理辦公用品表． 134
048 按顏色排序．．136
難易度 ★★★　　適用版本：Excel 2007/2010/2013
實例5-7：通過顏色排序區(qū)分待辦事項是否緊急． 136
目錄．indd 11 2014-3-27 17：24：27
XII Excel數(shù)據(jù)分析辭典
049 按圖標排序．．138
難易度 ★★★　　適用版本：Excel 2007/2010/2013
實例5-8：按圖標排序查詢?nèi)粘ＹM用開支程度． 138
050 按行排序140
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-9：按行排序員工獎金評估表．． 140
051 對合并單元格數(shù)據(jù)排序．142
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-10：將小組當月銷售額由高到低排序 142
052 自動篩選144
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例5-11：自動篩選統(tǒng)計公司男員工人數(shù)．．．． 144
053 包含合并單元格的自動篩選145
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-12：篩選查看某日所有工作內(nèi)容．． 145
054 搜索篩選149
難易度 ★★★　　適用版本：Excel 2007/2010/2013
實例5-13：篩選當月全勤人員．．．． 149
實例5-14：篩選某月轉(zhuǎn)正的所有人員記錄．．．． 149
055 兩個條件內(nèi)的自定義篩選．．．150
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-15：查看年齡在20到25之間的員工信息 151
056 “且”規(guī)則的高級篩選．152
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-16：按一段時間的消費次數(shù)、消費金額劃分客戶等級．．．． 152
057“或”規(guī)則的高級篩選．．．．154
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-17：對遲到5次以上、缺席2天或業(yè)務(wù)額低于1萬的員工
予以勸退．． 154
058 混合規(guī)則的高級篩選．．．．．155
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-18：查詢具有法律專業(yè)本科學歷或有事業(yè)單位工作經(jīng)驗的
人員．．．． 156
059 計算條件的高級篩選．．．．．157
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-19：篩選工齡超過單位平均工齡的人員記錄． 157
目錄．indd 12 2014-3-27 17：24：27
目錄 XIII
01
060 不重復記錄的高級篩選．159
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-20：篩選本月有訂單的貨品名 159
061 簡單分類匯總
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例5-21：匯總統(tǒng)計不同職位的平均工資．．．． 161
062 選擇多個匯總項的分類匯總162
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-22：統(tǒng)計不同部門的基本工資和工資總額．．．．． 162
063 多重分類匯總
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-23：統(tǒng)計不同部門的工資總額和平均值 164
064 嵌套分類匯總．．．．166
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-24：匯總統(tǒng)計各部門中不同職位的工資狀況． 166
065 手動建立分級顯示169
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例5-25：以上、中、下旬時間查詢當月銷售記錄． 169
066 自動生成分級顯示171
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例5-26：自動建立分級顯示劃分客戶所在區(qū)域．．．．． 171
067 按位置合并計算172
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-27：按位置合并計算一季度各商品訂單額．．．．． 173
068 按分類合并計算175
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例5-28：按分類合并計算一季度各商品訂單額．．．．． 175
第6章　交互式分析的數(shù)據(jù)透視表．．．179
分類檢索．．． 179
數(shù)據(jù)透視表的創(chuàng)建與刷新 179
數(shù)據(jù)透視表的整理與查看 179
數(shù)據(jù)透視表的排序與篩選 179
數(shù)據(jù)透視表的組合與計算 180
數(shù)據(jù)透視表的多表分析 180
目錄．indd 13 2014-3-27 17：24：27
XIV Excel數(shù)據(jù)分析辭典
名詞術(shù)語．．． 180
數(shù)據(jù)透視表． 180
源數(shù)據(jù)．．．．． 180
字段．．． 181
數(shù)據(jù)項．．．．． 181
匯總函數(shù)． 181
行字段/列字段/值字段．． 181
篩選器．．．．． 181
切片器．．．．． 181
日程表．．．．． 181
069 數(shù)據(jù)透視表的建立182
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例6-1：建立員工檔案數(shù)據(jù)透視表．． 182
070 快速統(tǒng)計重復項183
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例6-2：快速統(tǒng)計公司人員情況．． 184
071 調(diào)整字段排列多維度分析．．．185
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例6-3：多角度分析公司人員構(gòu)成．． 185
072 自動刷新數(shù)據(jù)透視表．．．．．187
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例6-4：自動刷新員工檔案數(shù)據(jù)透視表 187
073 以不同匯總方式統(tǒng)計．．．．．188
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例6-5：以不同方式統(tǒng)計物品庫存情況 189
074 以不同顯示方式分析．．．．．190
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例6-6：統(tǒng)計不同物品庫存比例．． 190
075 查看數(shù)據(jù)透視表明細數(shù)據(jù)．．．192
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例6-7：查看某商品不同規(guī)格型號的庫存明細． 192
076 更改透視表數(shù)據(jù)源194
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例6-8：對滯銷品清理庫存后重新更新透視表． 194
077 在數(shù)據(jù)透視表中排序．．．．．195
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
目錄．indd 14 2014-3-27 17：24：27
目錄 XV
01
實例6-9：對現(xiàn)有庫存距安全庫存的情況進行排序．．． 196
078 報表篩選實現(xiàn)分頁瀏覽．197
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例6-10：按缺貨物品的緊急狀態(tài)進行篩選 197
079 使用篩選按鈕篩選199
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例6-11：對訂購額在一定金額以上的正價商品免運費．． 199
080 使用切片器直觀篩選數(shù)據(jù)．．．200
難易度 ★★★　　適用版本：Excel 2010/2013
實例6-12：按商品名、訂購日期查詢負責商品的銷售員．． 200
081 使用日程表實現(xiàn)時段篩選．．．203
難易度 ★★★　　適用版本：Excel 2013
實例6-13：使用日程表查看商品訂購情況．．．． 203
082 組合透視表中的日期項．204
難易度 ★★★★　　適用版本：Excel 2007/2010/2013
實例6-14：按月份查看商品的訂購情況．． 204
083 在透視表中添加計算字段．．．206
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例6-15：按月計算各商品的訂購額 206
084 數(shù)據(jù)透視表的多表分析
難易度 ★★★　　適用版本：Excel 2013
實例6-16：建立雙透視表分析公司產(chǎn)品的訂購情況． 208
第7章　統(tǒng)計分析、假設(shè)檢驗與數(shù)據(jù)預測．．210
分類檢索．．． 210
統(tǒng)計分析． 210
假設(shè)檢驗． 210
數(shù)據(jù)預測． 211
名詞術(shù)語．．． 211
方案．．． 211
規(guī)劃求解． 211
描述統(tǒng)計． 212
直方圖．．．．． 212
排位．．． 212
相關(guān)性．．．．． 212
目錄．indd 15 2014-3-27 17：24：27
XVI Excel數(shù)據(jù)分析辭典
抽樣．．． 212
回歸分析． 212
單變量求解． 213
模擬運算表． 213
方差．．． 213
t/Ｚ/Ｆ檢驗．． 213
移動平均法． 213
指數(shù)平滑法． 213
085 最優(yōu)方案分析
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-1：公司銷售的最優(yōu)方案分析．． 214
086 規(guī)劃求解最優(yōu)解216
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-2：求解利潤最大化生產(chǎn)方案．． 216
實例7-3：合理安排員工值班 218
087 描述統(tǒng)計數(shù)據(jù)．．．．220
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-4：描述統(tǒng)計分析職員工資分布情況．． 220
088 直方圖分段統(tǒng)計發(fā)生頻率．．．221
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-5：直方圖分析公司人員流動情況 222
089 排位
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-6：根據(jù)全年業(yè)績排位發(fā)放年終獎 223
090 抽樣分析
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-7：抽樣分析公司工資水平．． 225
實例7-8：抽樣調(diào)查產(chǎn)品的市場滿意度．．．． 227
實例7-9：未收款項的抽樣調(diào)查分析．． 229
091 相關(guān)性與回歸分析231
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-10：賣場面積與營業(yè)額的相關(guān)性分析 231
實例7-11：公司產(chǎn)品生產(chǎn)與銷售成本的回歸分析．．．．． 232
092 單變量求解
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-12：使用單變量求解實現(xiàn)目標存款額的最低月儲蓄額．．．． 234
目錄．indd 16 2014-3-27 17：24：27
目錄 XVII
01
093 單變量模擬運算表235
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-13：建立銷售員提成的單變量模擬運算表．．．．． 236
094 雙變量模擬運算表237
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-14：建立公司固定資產(chǎn)折舊的雙變量模擬運算表．． 237
095 單因素方差分析239
難易度 ★★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-15：不同行業(yè)收入狀況的單因素方差分析．．．．． 239
096 雙因素方差分析241
難易度 ★★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-16：工人和機器設(shè)備對產(chǎn)量影響的雙因素方差分析．． 241
實例7-17：產(chǎn)品品牌和銷售地區(qū)對銷量影響的雙因素方差分析 242
097 t檢驗．．243
難易度 ★★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-18：產(chǎn)品標準性的t檢驗．．． 244
實例7-19： t檢驗分析兩種血壓測定儀檢測值是否有明顯差別．． 245
098 Z檢驗．246
難易度 ★★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-20：Z檢驗法分析產(chǎn)品生產(chǎn)情況是否符合設(shè)計標準 246
099 F檢驗．248
難易度 ★★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-21：部門工作效率的F檢驗分析．．．． 248
100 時間序列的趨勢外推預測．．．249
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-22：使用趨勢線預測明年銷售額．． 250
101 時間序列的移動平均預測．．．252
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-23：利用移動平均法預測來年工資增長率．．．．． 252
實例7-24：利用二次移動平均法預測的未來兩年辦公費用．． 253
102 時間序列的指數(shù)平滑預測．．．255
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例7-25：利用指數(shù)平滑法預測公司明年利潤額．．．．． 256
實例7-26：公司產(chǎn)品銷量的二次指數(shù)平滑預測 258
目錄．indd 17 2014-3-27 17：24：27
XVIII Excel數(shù)據(jù)分析辭典
第8章　數(shù)據(jù)計算．．．260
分類檢索．．． 260
公式的輸入、編輯與復制 260
單元格引用． 260
名稱的應(yīng)用． 260
數(shù)組常量和數(shù)組公式． 261
錯誤分析與檢查．．． 261
名詞術(shù)語．．． 261
公式．．． 261
運算符及運算符優(yōu)先級 261
A1與R1C1引用樣式．．． 262
相對引用、絕對引用、混合引用以及三維引用．． 262
名稱．．． 263
數(shù)組、數(shù)組常量以及數(shù)組公式．．．． 263
迭代/循環(huán)引用 263
103 輸入與編輯公式264
難易度 ★　　適用版本：Excel 2003/2007/2010/2013
實例8-1：輸入編輯公式計算產(chǎn)品銷售額 264
104 復制公式266
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例8-2：使用“填充柄”復制公式計算銷售額． 267
實例8-3：使用“粘貼”功能復制公式計算銷售額．．． 268
105 自動求和、求平均值計算．．．269
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例8-4：使用自動求和計算材料采購金額．． 269
實例8-5：使用“自動求和”統(tǒng)計成績．．．． 270
106 相對引用計算．．．．272
難易度 ★　　適用版本：Excel 2003/2007/2010/2013
實例8-6：使用相對引用根據(jù)單價和銷量計算銷售額．．．．．272
107 絕對引用計算．．．．274
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例8-7：使用絕對引用計算不同商品相同折扣下的銷售額 274
108 混合引用計算．．．．275
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例8-8：使用混合引用計算同一商品不同折扣不同銷售下的銷
售額．．．． 276
目錄．indd 18 2014-3-27 17：24：27
目錄 XIX
01
109 三維引用計算．．．．277
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例8-9：在工資表中使用三維引用計算工齡工資．．． 277
110 使用名稱計算．．．．279
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例8-10：定義名稱計算固定資產(chǎn)計提折舊額 279
實例8-11：根據(jù)所選內(nèi)容創(chuàng)建名稱計算工資總額．．．．． 282
111 運用數(shù)組常量計算284
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例8-12：使用數(shù)組常量計算員工繳納個人所得稅． 284
112 運用數(shù)組公式計算287
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例8-13：運用數(shù)組公式計算銷售商品總額 287
實例8-14：運用數(shù)組公式統(tǒng)計滿足條件的員工姓名． 288
實例8-15：運用數(shù)組公式制作日期 289
113 啟用迭代計算．．．．290
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例8-16：使用迭代計算生成不變的隨機數(shù) 291
實例8-17：使用迭代計算獲取輸入時間．． 294
114 公式審核296
難易度 ★　　適用版本： Excel 2003/2007/2010/2013
實例8-18：審核固定資產(chǎn)計提折舊額公式的正確性． 296
實例8-19：修改顯示錯誤值的公式 297
第9章　數(shù)據(jù)處理函數(shù)．．300
分類檢索．．． 300
數(shù)據(jù)的邏輯判斷與處理函數(shù)．． 300
日期數(shù)據(jù)的處理函數(shù)． 300
文本數(shù)據(jù)的處理函數(shù)． 300
財務(wù)數(shù)據(jù)處理函數(shù)．．．．． 301
數(shù)據(jù)查找函數(shù)． 301
數(shù)據(jù)集中趨勢統(tǒng)計函數(shù) 301
數(shù)據(jù)離散趨勢分析統(tǒng)計函數(shù)．． 302
數(shù)據(jù)概率統(tǒng)計函數(shù)．．．．． 302
數(shù)據(jù)匯總計算函數(shù)．．．．． 302
目錄．indd 19 2014-3-27 17：24：27
XX Excel數(shù)據(jù)分析辭典
名詞術(shù)語．．． 302
集中趨勢． 302
離散趨勢． 303
概率．．． 303
115 數(shù)據(jù)的邏輯判斷與處理．303
難易度 ★★★　　適用版本：2003/2007/2010/2013
實例9-1：按年均消費額劃分客戶等級．．．． 304
實例9-2：判斷累積積分是否可以領(lǐng)取季度獎． 304
實例9-3：判斷培訓成績是否合格．． 305
116 處理日期數(shù)據(jù)．．．．306
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例9-4：計算工作年限．． 306
實例9-5：計算報表當前填制時間．． 307
實例9-6：計算轉(zhuǎn)正日期．． 308
實例9-7：計算項目執(zhí)行天數(shù) 309
117 處理文本數(shù)據(jù)．．．．310
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例9-8：從身份證號中獲取出生信息．．．． 310
實例9-9：將金額按設(shè)置格式顯示．． 311
實例9-10：從產(chǎn)品編號中獲取產(chǎn)品類別．． 312
118 處理財務(wù)數(shù)據(jù)．．．．313
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例9-11：計算固定資產(chǎn)線性折舊費 313
實例9-12：使用固定余額遞減法計算折舊值 315
實例9-13：計算公司貸款每期償還額 316
實例9-14：計算投資的未來值．．．． 317
實例9-15：計算某項目投資的現(xiàn)值和凈現(xiàn)值 317
實例9-16：計算一系列現(xiàn)金流的內(nèi)部收益率 318
119 查找數(shù)據(jù)319
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例9-17：查找某積分對應(yīng)的獎金金額．． 320
實例9-18：根據(jù)員工編號顯示員工個人信息 320
實例9-19：計算哪一個月完成了目標 322
實例9-20：查找游戲點數(shù)可兌換的禮物．． 323
實例9-21：從電話簿中提取姓名 324
實例9-22：將滿足一定條件的員工調(diào)配到集團工作． 325
目錄．indd 20 2014-3-27 17：24：27
目錄 XXI
01
120 集中趨勢統(tǒng)計．．．．325
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例9-23：算術(shù)與幾何平均值計算 326
實例9-24：計算眾數(shù)． 327
實例9-25：計算中位數(shù) 328
121 離散趨勢統(tǒng)計．．．．328
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例9-26：計算方差． 329
實例9-27：計算標準差 329
實例9-28：計算四分位數(shù) 330
實例9-29：計算偏態(tài)值 332
實例9-30：計算峰度值 333
122 概率統(tǒng)計334
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例9-31：使用二項分布檢查產(chǎn)品合格率．．．． 334
實例9-32：使用泊松分布制定公司生產(chǎn)目標 335
123 數(shù)據(jù)匯總計算．．．．337
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例9-33：統(tǒng)計客戶當月投訴次數(shù) 337
實例9-34：統(tǒng)計某部門業(yè)務(wù)量總額 338
實例9-35：統(tǒng)計女職員評為中級職稱的人數(shù) 338
實例9-36：返回不同年齡段的員工人數(shù)．． 339
第10章　單元格圖表和數(shù)據(jù)圖．．341
分類檢索．．． 341
迷你圖．．．．． 341
常用標準圖表的使用． 341
高級圖表． 342
組合圖．．．．． 342
動態(tài)圖表． 342
名詞術(shù)語．．． 343
迷你圖/迷你圖組．． 343
標準圖表． 343
溫度計圖． 344
甘特圖．．．．． 344
瀑布圖．．．．． 344
目錄．indd 21 2014-3-27 17：24：28
XXII Excel數(shù)據(jù)分析辭典
漏斗圖．．．．． 344
階梯圖．．．．． 344
柏拉圖．．．．． 344
124 使用柱形迷你圖比較數(shù)值大小344
難易度 ★★　　適用版本：Excel 2010/2013
實例10-1：使用柱形迷你圖分析每月收入狀況 345
實例10-2：創(chuàng)建間隔時間不等的柱形迷你圖分析網(wǎng)站訪問情況 346
125 使用折線迷你圖分析數(shù)據(jù)變動趨勢．．348
難易度 ★★　　適用版本：Excel 2010/2013
實例10-3：創(chuàng)建折線迷你圖分析一周股價變動趨勢． 348
126 使用盈虧迷你圖反映金額正負變化．．350
難易度 ★★　　適用版本：Excel 2010/2013
實例10-4：創(chuàng)建盈虧迷你圖分析信用卡是否欠款．．．．． 350
127 使用REPT函數(shù)自定義創(chuàng)建單元格迷你圖．352
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例10-5：用REPT函數(shù)創(chuàng)建商品信用等級圖． 352
實例10-6：用REPT函數(shù)制作銷量條形圖 353
128 使用柱形圖縱向比較數(shù)據(jù)．．．355
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例10-7：創(chuàng)建簇狀柱形圖比較產(chǎn)品銷量．．．． 355
實例10-8：創(chuàng)建堆積柱形圖比較各分公司營業(yè)額．．．．． 356
實例10-9：創(chuàng)建百分比堆積柱形圖查看近年新進職員學歷傾向 357
129 使用條形圖橫向比較分類項358
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例10-10：創(chuàng)建三維簇狀條形圖比較各項社團活動參與人數(shù)．． 358
實例10-11：創(chuàng)建水平圓柱圖比較項目計劃與實際完成情況 359
130 使用折線圖表現(xiàn)數(shù)據(jù)發(fā)展趨勢362
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例10-12：創(chuàng)建平滑折線圖分析年度營業(yè)利潤增長變動趨勢．． 362
實例10-13：創(chuàng)建帶數(shù)據(jù)標記的堆積折線圖分析不同等級客戶近
年消費趨勢．． 364
實例10-14：創(chuàng)建三維折線圖分析兩大產(chǎn)品線營業(yè)額成長趨勢．． 365
131 使用餅圖或圓環(huán)圖分析數(shù)據(jù)比例關(guān)系．．．．367
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-15：創(chuàng)建餅圖分析員工出勤狀況 367
實例10-16：創(chuàng)建分離型餅圖進行員工離職原因分析．．．．． 369
目錄．indd 22 2014-3-27 17：24：28
目錄 XXIII
01
實例10-17：創(chuàng)建復合餅圖分析公司年度福利經(jīng)費開支． 371
實例10-18：創(chuàng)建雙層復合餅圖分析不同年齡段客戶的消費能力．．．． 372
實例10-19：創(chuàng)建圓環(huán)圖分析各子公司的銷售額． 375
實例10-20：創(chuàng)建圓環(huán)圖分析各崗位工資構(gòu)成． 376
132 使用面積圖進行數(shù)據(jù)量與趨勢變化分析377
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-21：創(chuàng)建三維面積圖分析公司賬款回收狀況．．．．． 378
實例10-22：創(chuàng)建堆積面積圖分析公司近年來客戶的構(gòu)成 379
133 使用散點圖和氣泡圖進行相關(guān)性分析．．．．380
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-23：創(chuàng)建散點圖分析商品售價與銷量的相關(guān)性． 380
實例10-24：創(chuàng)建帶平滑線的散點圖進行競爭產(chǎn)品銷量比較 382
實例10-25：創(chuàng)建氣泡圖分析各品牌銷售狀況及市場地位 383
實例10-26：創(chuàng)建氣泡圖分析因廣告投入不同所呈現(xiàn)的銷售額的
變化．．．． 384
134 使用雷達圖進行傾向/平衡分析測試386
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-27：創(chuàng)建雷達圖分析員工體能狀況．． 386
實例10-28：創(chuàng)建雷達圖對比A、B產(chǎn)品營養(yǎng)成分 387
135 使用溫度計圖表分析項目完成比例．．388
難易度 ★★★★　　適用版本： Excel 2003/2007/2010/2013
實例10-29：創(chuàng)建溫度計圖表分析任務(wù)完成進度． 389
136 使用甘特圖表現(xiàn)項目執(zhí)行進度392
難易度 ★★★　　適用版本： Excel 2003/2007/2010/2013
實例10-30：使用懸浮條形圖查看項目階段．． 392
137 使用瀑布圖反應(yīng)數(shù)據(jù)增減變動395
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-31：通過堆積柱形圖創(chuàng)建瀑布圖表現(xiàn)庫存商品變動情況．．．． 395
實例10-32：通過漲跌柱線創(chuàng)建瀑布圖分析現(xiàn)金流．．． 396
實例10-33：創(chuàng)建帶負值的瀑布圖反映員工實發(fā)工資情況 398
138 使用漏斗圖表現(xiàn)數(shù)據(jù)階梯型遞減402
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-34：創(chuàng)建漏斗圖表現(xiàn)客戶等級．．．． 402
139 使用階梯圖分析數(shù)據(jù)階梯變動趨勢．．405
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-35：創(chuàng)建階梯圖表現(xiàn)本年費用遞增狀況． 405
目錄．indd 23 2014-3-27 17：24：28
XXIV Excel數(shù)據(jù)分析辭典
140 使用柏拉圖找到對數(shù)據(jù)起關(guān)鍵作用的因素409
難易度 ★★★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-36：創(chuàng)建柏拉圖分析產(chǎn)品不良率 409
141 使用不等寬柱形圖表現(xiàn)值的范圍和分布414
難易度 ★★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-37：創(chuàng)建不等寬柱形圖反映銷量與市場占有率． 414
142 組合圖的使用．．．．416
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-38：創(chuàng)建線-柱圖比較兩事業(yè)部銷售額差異． 417
實例10-39：創(chuàng)建雙軸線-柱圖分析商品銷售與折扣率的關(guān)系．．．． 418
實例10-40：創(chuàng)建雙軸線-柱圖進行男女比例與消費狀況相關(guān)分析．．．．． 419
實例10-41：創(chuàng)建雙軸線-面積圖進行男女比例與薪資狀況的相
關(guān)分析 421
143 解決值差較大的斷層圖的制作423
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-42：用模擬Y軸法制作斷層圖．．．．． 423
144 迷你圖與數(shù)據(jù)圖表的組合使用430
難易度 ★★★　　適用版本：Excel 2010/2013
實例10-43：在條形圖中建立迷你圖分析成本變化．．． 430
145 創(chuàng)建動態(tài)圖交互式分析數(shù)據(jù)432
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例10-44：使用輔助區(qū)域建立動態(tài)圖分析不同月份各產(chǎn)品訂單額．433
實例10-45：定義名稱建立隨記錄項增加的銷量流水動態(tài)圖 435
實例10-46：創(chuàng)建下拉菜單式圖表分析近年各部門人數(shù)變動 437
第11章　源數(shù)據(jù)鏈接、嵌入與輸出．440
分類檢索．．． 440
建立鏈接． 440
嵌入或被嵌入． 440
獲取數(shù)據(jù)． 440
共享與輸出． 441
名詞術(shù)語．．． 441
屏幕提示． 441
嵌入與鏈接． 441
目錄．indd 24 2014-3-27 17：24：28
目錄 XXV
01
146 使用超鏈接鏈接外部數(shù)據(jù)源442
難易度 ★　　適用版本：Excel 2003/2007/2010/2013
實例11-1：制作公司員工通訊簿 442
147 通過復制粘貼建立鏈接．444
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例11-2：制作一式三聯(lián)對賬單 444
148 通過公式建立鏈接447
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例11-3：銷售匯總的跨工作簿計算 447
實例11-4：引用計算保險扣款．．．． 449
149 在工作表中嵌入其他文件對象450
難易度 ★　　適用版本：Excel 2003/2007/2010/2013
實例11-5：在日常費用統(tǒng)計表中嵌入費用類別說明文件．． 450
150 將工作表或圖表嵌入到其他程序452
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例11-6：將Excel銷售統(tǒng)計圖嵌入到PPT中 452
151 獲取數(shù)據(jù)庫數(shù)據(jù)454
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例11-7：將Access中員工記錄導入到員工檔案表中．．． 454
152 獲取外部文本文件數(shù)據(jù)．456
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例11-8：將文本文件中本周工作計劃導入到Excel日程安排表中．．．．． 456
153 獲取網(wǎng)站數(shù)據(jù)．．．．458
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例11-9：從網(wǎng)站獲取Excel統(tǒng)計數(shù)據(jù)．．．．． 459
154 傳遞工作表記錄到Access數(shù)據(jù)庫中．．．．460
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例11-10：將員工通訊簿導入到Access數(shù)據(jù)庫中．．．． 460
155 將數(shù)據(jù)表存為文本文件．463
難易度 ★★　　適用版本：Excel 2003/2007/2010/2013
實例11-11：將人事通報表存為文本文件 463
156 將Excel數(shù)據(jù)表保存到云
難易度 ★　　適用版本：Excel 2013
實例11-12：將職務(wù)調(diào)動申請表保存到云 465
目錄．indd 25 2014-3-27 17：24：28
XXVI Excel數(shù)據(jù)分析辭典
157 邀請他人協(xié)同編輯數(shù)據(jù)．468
難易度 ★★　　適用版本：Excel 2013
實例11-13：邀請他人協(xié)同進行采購物品登記． 468
158 數(shù)據(jù)表的打印輸出471
難易度 ★★★　　適用版本：Excel 2003/2007/2010/2013
實例11-14：會議議程安排表的打印輸出 471