第1章(zhāng)  Excel 2016基礎
1.1 工(gōng)作(zuò)簿的(de)♣₹基本操作(zuò) ············· α>··················1
1.1.1 新建工(gōng)作(zuò)簿 ·······················≥✔♥•·······1
1.1.2 保存工(gōng)作(zuò)簿 ·····¥σ·························2
1.1.3 打開(kāi)工(gōng)®β ±作(zuò)簿 ················>  ··············3
1.1.4 關閉工(gōng)作(zuò)簿 ·············σ₩♦·················4
1.1.5 隐藏工(gōng)作(zuò)簿 ········₩÷•······················∏ ®¶5
1.2 工(gōng)作(zuò)表的(de)基本操作(zuò) ······←$®·························5
1.2.1 插入工(gōng)作(zuò)表 ·····‍λ≥·························6
1.2.2 移動和(hé)複制(zhì)工(gōng)作(zuò)表 ···↑×¥················7
1.2.3 删除工(gōng)作(zuò)表 ·······‌£♣ ·······················9
1.2.4 重命名工(gōng)作(zuò)表 ············<★↓············· 10
1.2.5 顯示和(hé)隐藏工(gōng£∏)作(zuò)表 ················· 11
1.2.6 保護工(gōng)作(zuò)表 ···········≥∞················· 12
1.2.7 拆分(fēn)工(gōng)作(zuò)表 ·········←>δ··················· 13
1.2.8 凍結工(gōng)作(zuò)表 ············¥ ★················ 14
1.3 單元格的(de)基本操作(zuò) ··········‍₩¥··················· 16
1.3.1 插入單元格 ························ ≈↑←···· 16
1.3.2 删除單元格 ····················'σ········ 18
1.3.3 清除單元格 ···········σ→αδ················· 19
1.3.4 命名單元格 ··············β♦∏·············· 20
1.4 Excel 2016工(gōng)作(zuò)環境的(d$φe)設置··············· 21
1.4.1 顯示與隐藏網格線 ·············§$♦···· 21
1.4.2 顯示與隐藏标尺 ····················· 22
1.4.3 顯示與隐藏功能(néng)區(q♦∞¥∏ū) ················· 23
1.4.4 設置自(zì)動保存信息時(shí)間(jiān)間(jiān)®®☆隔 ·· 23
1.5 綜合實例——成績單的(de)設置 和(hé)保護 ···········<™ ···································· 24
本章(zhāng)小(xiǎo)結 ························♠ €·························‌λ₩·· 25
課後習(xí)題 ···································<γ→ε················ 25
第2章(zhāng)  Excel數(shù)據的(de)錄入與編輯§∞
2.1 錄入與選擇數(shù)據 ·······························‌¶· 27
2.1.1 手動輸入數(shù)據 ···δ₩····················¶<· 27
2.1.2 選擇單元格 ············↔₹λ‍················ 30
2.2 快(kuài)速填充數(shù)據 ·····$‌✘·····················ε↓·········· 31
2.2.1 使用(yòng)填充柄快(kuài)速填充數(shù)據 ····· 31
2.2.2 使用(yòng)快(kuài)捷鍵填充相(xiàng)同數(shù)↕δ據 ····· 32
2.2.3 使用(yòng)“填充序列”功能(né≤<>♦ng)········· 34
2.2.4 使用(yòng)“快(kuài)速填充”功能(néng) ······&≤÷····· 35
2.3 添加與管理(lǐ)批注 ·························≥Ω······· 37
2.3.1 為(wèi)單元格添加批注 ················ 37
2.3.2 編輯批注 ····························Ω≠& ··· 38
2.3.3 隐藏或顯示批注 ···················· 39
2.3.4 更改批注的(de)位置和(hé)顯示 方式 ·······÷σ································ 39
2.3.5 複制(zhì)批注 ···············≠←​​················ 41
2.3.6 删除批注 ················←↑✘≤··············· 41
2.4 設置數(shù)據驗證 ·························· ₹φ‌·········· 42
2.4.1 限制(zhì)用(yòng)戶輸入€‌≈σ ····················$∞···· 42
2.4.2 創建下(xià)拉菜單 ·······'¶ ↓················· 43
2.4.3 校(xiào)驗無效數(shù)據 ·············≈☆··········· 45
2.5 綜合實例——制(zhì)作(zuò)員(yu≠∑ án)工(gōng)信息表 ········· 47
本章(zhāng)小(xiǎo)結 ···········↑≤∑​········································· 49
課後習(xí)題 ···············δ±····································₩>×β· 49
2 中文(wén)版 Excel 2016 電(diàn)子(zǐ)表格β∑®制(zhì)作(zuò)實例教程
第3章(zhāng)  工(gōng)作(zuò)表格式設置與美(měi)化(huà)
3.1 設置單元格數(shù)字格式 ··············∞®···········52
3.1.1 應用(yòng)預設數(shσ×ù)字格式 ··············∑↔₽π···52
3.1.2 自(zì)定義日(rì)期格式≤§δ ·····················54
3.1.3 使用(yòng)千位分(fēn)隔符 ···········ε✘♣··········55
3.1.4 更改數(shù)值大(dà)小(xiǎo) ··········γ↕···············56
3.1.5 對(duì)齊小(xiǎo)數(shù)點·········"£····················5γ•γ7
3.1.6 添加單位 ····················✔>············57
3.1.7 添加前導或後綴符号 ·············58
3.1.8 自(zì)定義數(shù)字顯示方式 ····· ∏©········59
3.1.9 自(zì)定義數(shù)字格式的(de)準則 ·········60
3.2 設置格式化(huà)單元格 ·····················¥π········62
3.2.1 調整行(xíng)高(gāo)和(hé)£‍列寬 ···················™‌··62
3.2.2 設置單元格邊框 ·····················64
3.2.3 設置單元格背景色和(hé)圖ε♣&♥案 ······64
3.2.4 設置工(gōng)作(zuò)φφ‌‍表的(de)背景圖 ·············66
3.3 設置條件(jiàn)格式 ··················‍‌···················66
3.3.1 編輯數(shù)據條規則 ·············↕π········67
3.3.2 編輯圖标集規則 ·····················68
3.3.3 編輯最前/最後規則 ················71
3.3.4 疊加多(duō)種條件(jiàn)格 ↕式 ·················72
3.3.5 使用(yòng)公式創建條件(jiàn)格式 ·········73
3.4 套用(yòng)表格樣式 ································↑≤π·····76
3.4.1 應用(yòng)表樣式·········≈§÷♥····················76
3.4.2 自(zì)定義表格樣式 ·····················76
3.5 套用(yòng)單元格樣式 ·····························ε♦★φ····79
3.5.1 應用(yòng)單元格樣式 ··········€✘®&···········79
3.5.2 創建新樣式·····························79
3.5.3 修改和(hé)删除樣式 ··· ★&‌··················80
3.6 綜合實例——美(měi)化(huà)員(yuán)工(gōng)考核表 ····‍¶"•······82
本章(zhāng)小(xiǎo)結 ··φ±···················· $ ·······························83
課後習(xí)題 ·············↓↕‌↑········································8δ¶4
第4章(zhāng)  Excel數(shù)據' 處理(lǐ)與分(fēn)析
4.1 數(shù)據排序 ········λα··································· 85
4.1.1 簡單排序 ······························· ×δ®"85
4.1.2 按單元格特征排序 ·····∏γ→₽··········· 86
4.1.3 恢複排序前的(de)狀态 ··········¶≈¥······ 87
4.1.4 按自(zì)定義序列排序 ··<×·············· 88
4.1.5 添加排序條件(jiàn) ························ 90
4.2 數(shù)據篩選 ····························₹∞··············· 90
4.2.1 自(zì)動篩選 ····················>$β··········· 91
4.2.2 同字段多(duō)條件(jiàn)篩選 ················ 92
4.2.3 不(bù)同字段多(duō)條件(ji<®✘àn)篩選 ············ 93
4.2.4 将篩選結果複制(zhì)到(dào)其他(tā) 工(gōng)作(zφ ✘♥uò)表中 ······························· 93
4.2.5 利用(yòng)排序和(hé)篩選功能(néng)分(fēn)隔 ™α×銷售清單 ······························· 94
4.3 數(shù)據分(fēn)類彙總 ····························§‌········ 97
4.3.1 認識分(fēn)類彙總 ····················≠ ···· 97
4.3.2 分(fēn)類彙總數(shù)據 ········→× ················ 97
4.3.3 删除分(fēn)類彙總 ························ ☆±±98
4.3.4 嵌套分(fēn)類彙總 ························ 99
4.4 數(shù)據合并計(jì)算(suà$₩↑n) ·······························₩¥····101
4.4.1 按位置合并計(jì)算(suàn) ···················✔"101
4.4.2 按分(fēn)類合并計(jì)算(suàn) ·······∏®≠············103
4.5 綜合實例——分(fēn)析與彙總商品 采購(gòu)情況 ·‍≥↕€·······································ε<≈···104
本章(zhāng)小(xiǎo)結 ···π•···························&™↕>·····················106
課後習(xí)題 ···································¥ ················107
第5章(zhāng)  公式的φ€​(de)使用(yòng)
5.1 認識公式 ······························<Ω♣™···········108
5.1.1 公式的(de)結構 ···························1φ‍♥08
目錄 3
5.1.2 運算(suàn)符 ·················· ★®················ 108
5.1.3 運算(suàn)符優先級 ······················· 110
5.2 公式的(de)基本操作(zuò) ···········×‌···················· 111
5.2.1 輸入公式 ············∞€·················· 111
5.2.2 結合鍵盤和(hé)鼠标輸入公式 ···↔¥>¶· 112
5.2.3 顯示公式 ······························α✔÷π 113
5.2.4 修改公式 ·················•"↓············· 114
5.2.5 複制(zhì)公式 ······························ ✘÷114
5.3 定義名稱簡化(huà)公式 ·····················∑→<······ 115
5.3.1 定義名稱 ·················↑©············· 115
5.3.2 在公式中引用(yòng)名稱 ···₽ ✔ ············ 116
5.3.3 管理(lǐ)名稱 ························♥$······ 116
5.4 單元格的(de)引用(yòng)☆'★§···························© ™₩········ 118
5.4.1 相(xiàng)對(duì)引用(yòng) ···············Ω₩··············· 119
5.4.2 絕對(duì)引用(yòng) ····×‌·························· 120
5.4.3 混合引用(yòng) ·················±$β············· 120
5.5 審核公式 ··················​€↔························ 121
5.5.1 公式返回的(de)錯(cuò)誤值及其 産生(shēng)原因 ····· £······················§✘☆··· 121
5.5.2 檢查錯(cuò)誤 ····'®₹·························· γ§×122
5.5.3 追蹤引用(yòng)單元格 ············×≠σ······· 123
5.5.4 追蹤從(cóng)屬單元格 ··················· 124
5.6 綜合實例——制(zhì)作(zuò)銷售報(bào)表 ············ ✔'☆​124
本章(zhāng)小(xiǎo)結 ························ ™··························· 126
課後習(xí)題 ····················×★→······························· 126
第6章(zhāng) 函數(shù)的(de)使用(y→♦♣òng)
6.1 認識函數(shù) ···········π£······························· 128
6.1.1 函數(shù)的(de)含義 ······················​φ•>···· 128
6.1.2 函數(shù)的(de)分(fēn)類 ··············αππ↑············ 128
6.1.3 輸入函數(shù) ··········£™€···················· 129
6.2 統計(jì)函數(shù) ·················↔∏☆¥························· 131
6.2.1 常用(yòng)統計(jì)函數(shù) ················✘α×······· 131
6.2.2 AVERAGE函數(shù) ·¥✔♠·················· 133
6.2.3 MAX函數(shù) ············· ↔··············· 135
6.2.4 COUNTA函數(shù) ·············♥☆δ☆········ 136
6.2.5 COUNT函數(shù) ···₩§♠····················· 137
6.3 文(wén)本函數(shù) ·····················φ¶$¥·····················£✘∑↓ 138
6.3.1 常用(yòng)文(wén)本函數(shù) ·············♦♥↓·········· 138
6.3.2 EXACT函數(shù) ·····§®£··················· 140
6.3.3 LOWER與UPPER函數(sh"→♥ù) ······ 141
6.3.4 CONCATENATE函數(shù) ·········· 142
6.3.5 REPT函數(shù) ···············•&············· 144
6.3.6 SUBSTITUTE函數(shù) ··✔→············ 145
6.4 邏輯函數(shù) ········₽​✘•································≤γ·· 146
6.4.1 常用(yòng)邏輯函數(shù) ···················≥σ≠···· 146
6.4.2 AND函數(shù) ··········♦₹₩£··················· 147
6.4.3 OR函數(shù) ·········∑₽αε······················· 148
6.4.4 NOT函數(shù) ················÷'•············· 149
6.4.5 IF函數(shù) ························ ↓·········· 149
6.5 日(rì)期與時(shí)間(jiān)函數(shù) ·©↔≤↑·························♦δ♠↑····· 150
6.5.1 常用(yòng)日(rì)期與時(shí)間(jiān)函數(shùλ'•) ··········· 150
6.5.2 NOW函數(shù)：返回指定日(rì)期 · 152
6.5.3 HOUR函數(shù)：返回小(xiǎo)時(sh©₹​•í)數(shù) ··· 153
6.5.4 DAYS360函數(shù)：計(jì)算(suàn)兩日(rì)≤"↔期 之間(jiān)的(de)天數(shù)········♠∏≥≥··················· 154
6.6 查找與引用(yòng)函數(shù) ························✘ε······· 155
6.6.1 常用(yòng)查找與引用(yòng)函數(shù) ·γ₩→¶·········· 155
6.6.2 使用(yòng)VLOOKU↓™αP函數(shù)計(jì)算(suàn) 年(nián)齡所屬分(fēn)段 ·₩€•≥······················ 158
6.6.3 使用(yòng)HLOOKUP函數(shù)模糊 查找計‍‌✔γ(jì)算(suàn)提成 ······················· 159
6.7 綜合實例——商品庫存狀态分(fēn)析 ···♥αε· 160
本章(zhāng)小(xiǎo)結 ·······························✘₹···················· 162
課後習(xí)題 ············₹✘·····················↕γ·················· 163
4 中文(wén)版 Excel 2016 電(diàn)子(zǐ)表格制(zhì)作(zu™✔γ↕ò)實例教程
第7章(zhāng) 創建數(shù)據透視(shì)表與 ∏數(shù)據透視(shì)圖
7.1 數(shù)據透視(shì)表的(de)基本操作(zuò) ······★'<············· 164
7.1.1 數(shù)據透視(shì)表的(de)用(yòng)途 ···········γ≈σ···· 164
7.1.2 在新工(gōng)作(zuò)表中創建數(shù)據 透視(shì)表 ····≤✔······························ 164
7.1.3 在當前工(gōng)作(zuò)表中創建數(shù)據 透視(sh→™ì)表 ·······························₽₽··· 166
7.1.4 移動數(shù)據透視(shì)表 ··················· 167
7.1.5 删除數(shù)據透視(shì)↔♦↔π表 ··················· 168
7.2 更改數(shù)據透視(shì)表的(de)布局 ··÷σ<················· 168
7.2.1 顯示/隐藏分(fēn)類彙總 ·············· 168
7.2.2 設置報(bào)表布局 ·····₹&®·················· 169
7.2.3 更改數(shù)據透視(shì)表樣式 ··········· δγ170
7.3 設置數(shù)據透視(shì)表字段 ······················· γ¶≥≈171
7.3.1 更改字段名稱 ······················· 171
7.3.2 更改值的(de)彙總方式 ··············· 172
7.3.3 以百分(fēn)比顯示數(shù)據 ··············· 173'≈¶ 
7.4 使用(yòng)數(shù)據透視(shì)表篩選™₩δ→數(shù)據 ················&<β₽ 174
7.4.1 創建切片器(qì)·········✘™·················· 174
7.4.2 使用(yòng)切片器(qì)篩選數(shù)據 ··φΩ Ω········· 175
7.4.3 删除切片器(qì)·········®♣·················· 175
7.4.4 更改切片器(qì)樣式 ··············Ω★↔★····· 176
7.4.5 添加篩選器(qì)···············☆π™β············ 178
7.4.6 顯示報(bào)表篩選頁 ··················· 178
7.5 使用(yòng)數(shù)據透視(λ↓♠shì)圖 ······························· >×179
7.5.1 認識數(shù)據透視(shì)圖 ·········​±→·········· 179
7.5.2 使用(yòng)現(xiàn)→→Ω∞有(yǒu)數(shù)據透視(shì)表創建 數(shù)據±φε透視(shì)圖 ·························· 180
7.5.3 創建全新數(shù)據透視(shì)圖 ··········β™©"· 181
7.5.4 使用(yòng)數(shù)據透視(shì)圖篩選數(shù)據 ····×< 182
7.5.5 轉換為(wèi)标準圖表 ········₩≥ ₩··········· 183
7.6 綜合實例——商品标題關鍵詞 分(fēn)析 ·····················₩ε ·······················♣≥♥······· 184
本章(zhāng)小(xiǎo)結 ········φ ···································↓€₩········187
課後習(xí)題 ··························· ∏ΩΩ························187
第8章(zhāng) 使用(yòng)圖✔​表實現(xiàn)數(shù)據可(kě✔←)視(shì)化(huà)
8.1 認識圖表 ··············<∑®····························18& ☆♠9
8.1.1 圖表的(de)結構 ·············✔✘&←··············189
8.1.2 圖表的(de)類型 ···················•₹········190
8.2 創建圖表 ···············₹®" ·····················∏£±¶······193
8.2.1 創建基本圖表 ·······················193
8.2.2 創建組合圖表 ·········<•··············194
8.2.3 調整圖表的(de)位置和(hé)大(dà)小(xiǎo) ········195
8.2.4 更改圖表類型 ·······················196
8.2.5 創建迷你(nǐ)圖 ············$♦₹<···············197
8.3 編輯圖表數(shù)據和(hé)元素 ············☆♣···········200
8.3.1 更改和(hé)删除系列值 ···············200
8.3.2 添加系列 ·····················♣α·········202
8.3.3 重新選擇圖表數(shù)據區♥≠♣₽(qū)域 ········203
8.3.4 添加數(shù)據标簽和(hé)網格線 ········204≈§
8.3.5 添加坐(zuò)标軸标題 ················σ×···205
8.3.6 将标題鏈接到(dào)單元格 ···········206
8.4 美(měi)化(huà)圖表 ········™↕·························÷≤±·········207
8.4.1 應用(yòng)圖表樣式 ··₽♠​·····················207
8.4.2 通(tōng)過格式窗(chuāng)格"♠設置圖表 格式 ····································✘​πφ··208
8.4.3 插入形狀 ···················✘™↑···········210
8.4.4 插入圖片 ·························✔∑‍φ····· 211
8.4.5 設置圖片填充 ······················≥♠↓>·213
8.5 綜合實例——使用(yòng)甘特圖制(zhì)作(zuò) 商品營•σ≠₩銷策劃流程 ··························≤ ··········214
本章(zhāng)小(xiǎo)結 ····························α®♣·······················217
課後習(xí)題 ·····················®✔&······························2♠≈φ±17
目錄 5
第9章(zhāng) 創建鏈接與引用(yòng)
9.1 創建鏈接 ·····························'£≠₩············· 219
9.1.1 創建指向現(xiàn)有(yǒu)文(wén)件(jiàn)的(d<÷e)鏈接 ···· 219
9.1.2 創建指向特定網頁的(de)鏈接 ···· 220
9.1.3 創建指向工(gōng)作(zuò)表的(de)鏈接 ······· 2≠≥₩21
9.1.4 創建指向特定單元格的(de) 鏈接 ························· ♠✘············· 222
9.1.5 删除鏈接 ························✔♦∞······ 223
9.2 數(shù)據引用(yòng) ······​ ·······················€→············· 224
9.2.1 引用(yòng)本工(gōng)作(zuò)表內(nèi•'₽←)的(de)單元格 ···· 224
9.2.2 引用(yòng)其他(tā)&‌>工(gōng)作(zuò)表的(de)單元格 ···· 225
9.2.3 引用(yòng)其他(tā)工(gōng₩σ)作(zuò)簿的(de)單元格 ···· 226♥♣•↓
9.2.4 粘貼引用(yòng)鏈接 ····················· ©·· 227
9.3 嵌入與鏈接文(wén)件(jiàn) ···········  ···················· 228
9.3.1 嵌入Word文(wén)檔 ·············✘"Ω······· 228
9.3.2 嵌入PowerPoint演示文(wén)稿 ··· 229
9.3.3 鏈接Excel工(gōng)作(zuò)簿 ················€♦  231
9.4 綜合實例——在Word中調用(yòng) Excel工♠φ(gōng)作(zuò)表 ···············≥↓···················· 232
本章(zhāng)小(xiǎo)結 ··········÷<·························‍∞‍················ 233
課後習(xí)題 ··············₹β································♣βΩ····· 234
第10章(zhāng) 電(diàn)子(zǐ)表格頁面設置與打印
10.1 設置頁面打印版式 ························· 235
10.1.1 設置打印頁面 ····················· 235§Ω&
10.1.2 設置頁邊距 ·····················♣§↔···· 236
10.2 設置打印選項 ·································φ∏ 237
10.2.1 設置打印區(qū)域 ··········≥Ω>··········· 237
10.2.2 設置打印标題 ··········&£≈··········· 238
10.2.3 設置打印行(xíng)号和(hé)列标 ···↔≈<······ 239
10.2.4 設置草(cǎo)稿和(hé)單色方式 打印 ···✘€································· 2✘€↓×40
10.2.5 設置網格線打印 ················· 24←δ™0
10.2.6 打印不(bù)連續的(de)行(xíng)或列 ·βπ×········ 241
10.2.7 取消圖片打印 ····················↑♦§←· 241
10.2.8 打印設置 ···························· 242
10.3 使用(yòng)分(fēn)頁符 ················δ±∞♠····················· 243
10.3.1 插入分(fēn)頁符 ························· 243♣β∑↕
10.3.2 删除分(fēn)頁符 ·················φ★γ········ 244
10.4 設置頁眉和(hé)頁腳 ····························· Ω‌244
10.4.1 使用(yòng)內(nèi)置的(de)頁眉和(hé)頁腳 ······ 245
10.4.2 自(zì)定義頁眉和(hé)頁腳 ············· 245≠✔π
10.4.3 删除頁眉和(hé)頁腳 ················· 247
10.5 添加水(shuǐ)印效果 ························÷≤↕δ········· 248
10.5.1 使用(yòng)頁眉和(hé)頁腳添加÷≤ 水(shuǐ)印效果 ···························· 248
10.5.2 通(tōng)過插入藝術(shù)字添加 水(shuǐ)印效果 ···✘→························· 250
10.6 綜合實例——設置與打印 工(gōng)資表 ···'₽₹✔··········································≥™≠σ 251
本章(zhāng)小(xiǎo)結 ················♠ ··································· 253
課後習(xí)題 ·······························♦λ···················· 2≥<×γ53