演繹生動(dòng)·Excel圖表：寫(xiě)給大家看的圖表書(shū)（全彩）

第1 部分 好的故事
第 1章有章可循.................................................................................3
1.1必須了解的事情.............................................................................4
1.1.1從何談起..............................................................................4
1.1.2圖表是視覺(jué)溝通語(yǔ)言...................................................................5
1.1.3“ 無(wú)所不能”的Excel.................................................................6
1.1.4 玩轉(zhuǎn)Excel圖表不簡(jiǎn)單.................................................................7
1.2原來(lái)我們都錯(cuò)了.............................................................................9
1.2.1盲目...................................................................................9
1.2.2迷失.................................................................................10
1.2.3專(zhuān)業(yè).................................................................................15
第 2章圖表演義..............................................................................17
2.1講述故事....................................................................................18
2.1.1清晰可見(jiàn)的圖表勢(shì)能.................................................................18
2.1.2自然流暢的動(dòng)線設(shè)計(jì).................................................................20
2.2演繹生動(dòng).....................................................................................22
2.2.1把握平衡............................................................................22
2.2.2換個(gè)角度............................................................................24
2.2.3打破規(guī)則............................................................................25
第2 部分 認(rèn)識(shí)圖表
第 3章構(gòu)圖基礎(chǔ)..............................................................................29
3.1元素........................................................................................30
3.1.1平面構(gòu)成............................................................................30
3.1.2圖表元素............................................................................32
3.2文字........................................................................................37
3.2.1字體.................................................................................37
3.2.2屬性.................................................................................39
3.3表達(dá)........................................................................................40
3.3.1表達(dá)形式............................................................................40
3.3.2布局.................................................................................43
3.3.3圖表修飾............................................................................45
第 4章秀色可餐..............................................................................51
4.1視覺(jué)魔方....................................................................................52
4.1.1色彩及混色..........................................................................52
4.1.2色彩空間............................................................................53
4.1.3為色彩做好準(zhǔn)備.....................................................................55
4.2潘朵拉魔盒................................................................................56
4.2.1 認(rèn)識(shí)Excel調(diào)色板....................................................................56
4.2.2調(diào)色板設(shè)置..........................................................................58
4.2.3 Excel取色板.........................................................................59
4.2.4 HSL顏色模式........................................................................63
4.3協(xié)調(diào)........................................................................................66
4.3.1色彩基調(diào)............................................................................66
4.3.2色環(huán).................................................................................67
4.3.3配色技巧............................................................................68
4.3.4 Excel的“前景色”和“背景色”....................................................74
4.3.5保存配色方案.........................................................................75
4.4平衡........................................................................................81
4.4.1不要迷戀美術(shù)配色....................................................................81
4.4.2 色彩1 23............................................................................82
4.4.3不要迷失在色彩里....................................................................84
4.4.4更加快捷的方法.....................................................................86
4.4.5繼承與拋棄習(xí)慣.....................................................................97
第 5章圖表基礎(chǔ)..............................................................................99
5.1圖表類(lèi)型..................................................................................100
5.1.1圖表類(lèi)型及其子類(lèi)型.................................................................100
5.1.2直角坐標(biāo)系........................................................................103
5.1.3極坐標(biāo)軸............................................................................111
5.2圖表系列.................................................................................114
5.2.1源數(shù)據(jù)............................................................................114
5.2.2組合不同類(lèi)型圖表系列............................................................118
5.3元素設(shè)置..................................................................................127
5.3.1與圖表系列相關(guān)元素.................................................................127
5.3.2與圖表系列無(wú)關(guān)元素................................................................138
5.3.3元素的格式化......................................................................140
5.4圖表制作之前............................................................................144
5.4.1磨刀不誤砍柴工....................................................................144
5.4.2SIPOC............................................................................157
第3 部分 故事技巧
第 6章重新認(rèn)識(shí)............................................................................167
6.1 作圖的次序 [瞞天過(guò)海]...................................................................168
6.1.1案例介紹...........................................................................168
6.1.2案例分析...........................................................................169
6.1.3案例實(shí)現(xiàn)...........................................................................169
6.1.4其他.................................................................................171
6.2 拋開(kāi)圖表看問(wèn)題 [圍魏救趙]...............................................................173
6.2.1案例介紹............................................................................173
6.2.2案例分析............................................................................173
6.2.3案例實(shí)現(xiàn)...........................................................................174
6.3 擺脫束縛 [借刀殺人].....................................................................177
6.3.1案例介紹............................................................................177
6.3.2案例分析............................................................................177
6.3.3案例實(shí)現(xiàn)............................................................................177
6.4 自動(dòng)化 [以逸待勞].......................................................................180
6.4.1 數(shù)值刻度的最大/最小值設(shè)定......................................................181
6.4.2批量生成圖表......................................................................183
6.4.3整理圖表系列邊框及標(biāo)簽位置......................................................186
6.5 煩人的餅圖“0”值 [趁火打劫]..........................................................188
6.5.1案例介紹...........................................................................188
6.5.2案例分析...........................................................................188
6.5.3案例實(shí)現(xiàn)...........................................................................189
6.5.4面積類(lèi)圖表中的“0”值處理........................................................193
6.5.5點(diǎn)線類(lèi)圖表中的“0”值處理.......................................................195
6.6 換種思維來(lái)考慮 [聲東擊西]..............................................................196
6.6.1巧借視覺(jué)參考元素.................................................................196
6.6.2圖表類(lèi)型轉(zhuǎn)換......................................................................197
第 7章突破困局............................................................................199
7.1 虛擬的圖表引用數(shù)據(jù) [無(wú)中生有]..........................................................200
7.1.1案例介紹............................................................................200
7.1.2案例分析............................................................................200
7.1.3案例實(shí)現(xiàn)............................................................................201
7.1.4其他.................................................................................203
7.2 有效利用錯(cuò)層 [暗渡陳倉(cāng)]................................................................205
7.2.1案例介紹............................................................................205
7.2.2案例分析............................................................................205
7.2.3案例實(shí)現(xiàn)............................................................................206
7.3 有趣的正方形 [隔岸觀火]................................................................209
7.3.1案例介紹............................................................................209
7.3.2案例分析............................................................................209
7.3.3案例實(shí)現(xiàn)............................................................................210
7.3.4其他.................................................................................213
7.4 多個(gè)暗藏的小技巧 [笑里藏刀].............................................................216
7.4.1上標(biāo)和下標(biāo)........................................................................216
7.4.2自定義圖例........................................................................217
7.4.3自定義坐標(biāo)軸標(biāo)簽.................................................................218
7.5 氣泡圖的圖形轉(zhuǎn)換 [李代桃僵].............................................................222
7.5.1案例介紹............................................................................222
7.5.2案例分析............................................................................222
7.5.3案例實(shí)現(xiàn)............................................................................223
7.5.4其他.................................................................................227
7.6 將一個(gè)系列變?yōu)槎鄠€(gè) [順手牽羊]..........................................................229
7.6.1案例介紹............................................................................229
7.6.2案例分析............................................................................230
7.6.3案例實(shí)現(xiàn)............................................................................230
第 8章打好攻堅(jiān)............................................................................233
8.1 強(qiáng)烈表達(dá)差異 [打草驚蛇]................................................................234
8.1.1柱形差異表達(dá).......................................................................234
8.1.2折線差異表達(dá).......................................................................237
8.2 拋開(kāi)圖表來(lái)制圖[借尸還魂]...............................................................240
8.2.1案例介紹............................................................................240
8.2.2案例分析............................................................................241
8.2.3案例實(shí)現(xiàn)............................................................................241
8.2.4其他.................................................................................242
8.3 一組數(shù)據(jù)的分拆使用 [調(diào)虎離山]..........................................................245
8.3.1案例介紹............................................................................245
8.3.2案例分析............................................................................245
8.3.3案例實(shí)現(xiàn)............................................................................246
8.4 充分利用時(shí)間刻度 [欲擒故縱]............................................................248
8.4.1案例介紹...........................................................................248
8.4.2案例分析............................................................................249
8.4.3案例實(shí)現(xiàn)............................................................................249
8.4.4其他.................................................................................252
8.5 有趣的百分比 [拋磚引玉]................................................................255
8.5.1案例介紹............................................................................255
8.5.2案例分析............................................................................255
8.5.3案例實(shí)現(xiàn)............................................................................256
8.5.4其他.................................................................................260
8.6 數(shù)據(jù)歸一化 [擒賊擒王]....................................................................262
8.6.1案例介紹............................................................................262
8.6.2案例分析............................................................................262
8.6.3案例實(shí)現(xiàn)............................................................................263
8.6.4其他.................................................................................266
第 9章亂中取勝............................................................................267
9.1 圖表的靜態(tài)化 [釜底抽薪].................................................................268
9.1.1案例介紹...........................................................................268
9.1.2案例分析...........................................................................268
9.1.3案例實(shí)現(xiàn)............................................................................269
9.1.4案例相關(guān)............................................................................272
9.2 堆積圖表的并列顯示 [混水摸魚(yú)]..........................................................273
9.2.1案例介紹............................................................................273
9.2.2案例分析............................................................................273
9.2.3案例實(shí)現(xiàn)............................................................................273
9.3 數(shù)據(jù)降維 [金蟬脫殼].....................................................................277
9.3.1案例介紹............................................................................277
9.3.2案例分析............................................................................277
9.3.3案例實(shí)現(xiàn)...........................................................................278
9.4 另類(lèi)雷達(dá)圖 [關(guān)門(mén)捉賊]...................................................................281
9.4.1案例介紹...........................................................................281
9.4.2案例分析...........................................................................281
9.4.3案例實(shí)現(xiàn)............................................................................282
9.4.4其他................................................................................285
9.5 數(shù)據(jù)的前后對(duì)比 [遠(yuǎn)交近攻]..............................................................287
9.5.1案例介紹...........................................................................287
9.5.2案例分析...........................................................................287
9.5.3案例實(shí)現(xiàn)...........................................................................288
9.6 有效利用堆積系列線 [假途伐虢]..........................................................292
9.6.1案例介紹............................................................................292
9.6.2案例分析............................................................................292
9.6.3案例實(shí)現(xiàn)............................................................................293
9.6.4其他.................................................................................295
第 10章借船出海............................................................................297
10.1 變換線形為面積 [偷梁換柱]............................................................298
10.1.1案例介紹.........................................................................298
10.1.2案例分析.........................................................................298
10.1.3案例實(shí)現(xiàn)........................................................................299
10.1.4其他............................................................................301
10.2 假借色彩和標(biāo)簽 [指桑罵槐].............................................................304
10.2.1案例介紹........................................................................304
10.2.2案例分析........................................................................304
10.2.3案例實(shí)現(xiàn)........................................................................305
10.2.4其他............................................................................307
10.3 重疊比例的妙用 [假癡不顛]............................................................308
10.3.1案例介紹.........................................................................308
10.3.2案例分析.........................................................................308
10.3.3案例實(shí)現(xiàn)........................................................................309
10.3.4其他............................................................................311
10.4 墊腳的堆積圖表系列 [上屋抽梯]........................................................312
10.4.1案例介紹.........................................................................312
10.4.2案例分析........................................................................312
10.4.3案例實(shí)現(xiàn)........................................................................313
10.4.4其他............................................................................316
10.5 環(huán)形圖巧扮綠葉 [樹(shù)上開(kāi)花]............................................................318
10.5.1案例介紹.........................................................................318
10.5.2案例分析.........................................................................318
10.5.3案例實(shí)現(xiàn)........................................................................319
10.6 善用XY 散點(diǎn)誤差線 [反客為主]........................................................323
10.6.1案例介紹.........................................................................323
10.6.2案例分析........................................................................323
10.6.3案例實(shí)現(xiàn)........................................................................324
10.6.4其他............................................................................326
第 11章尋求轉(zhuǎn)機(jī)............................................................................327
11.1 美只為表達(dá)訴求 [美人].................................................................328
11.1.1色彩與背景......................................................................328
11.1.2坐標(biāo)標(biāo)簽美化.....................................................................329
11.1.3處理極大值與極小值............................................................330
11.2 照相機(jī)的妙用 [空城].....................................................................334
11.2.1圖表旋轉(zhuǎn)與翻轉(zhuǎn).................................................................334
11.2.2圖表與其他圖形對(duì)象的拼裝......................................................335
11.2.3其他............................................................................335
11.3 圖表是矢量 [反間]......................................................................337
11.3.1案例介紹........................................................................337
11.3.2案例分析........................................................................337
11.3.3案例實(shí)現(xiàn)........................................................................337
11.3.4案例相關(guān)........................................................................340
11.4 作圖需要耐心 [苦肉].....................................................................341
11.4.1案例介紹與分析.................................................................341
11.4.2案例實(shí)現(xiàn)剖析.....................................................................341
11.4.3另類(lèi)實(shí)現(xiàn)........................................................................343
11.5 作圖需要多技巧 [連環(huán)].................................................................345
11.5.1案例介紹........................................................................345
11.5.2案例分析........................................................................345
11.5.3案例實(shí)現(xiàn).........................................................................346
11.5.4案例相關(guān)........................................................................349
11.6 糾結(jié)不如放棄 [走為上].................................................................350
11.6.1請(qǐng)勿局限在圖表.................................................................350
11.6.2表格也是訴求的表達(dá)............................................................352
11.6.3直接使用數(shù)字來(lái)表達(dá)............................................................353
11.6.4 將Excel放到一邊...............................................................353
11.6.5放棄不代表放任.................................................................354
第 12章融會(huì)貫通............................................................................355
12.1用心，而非用技.........................................................................356
12.1.1站在讀者的立場(chǎng).................................................................356
12.1.2從窗戶所見(jiàn)的世界................................................................357
12.2簡(jiǎn)繁之間................................................................................360
12.2.1由簡(jiǎn)至繁........................................................................360
12.2.2化繁為簡(jiǎn)........................................................................361
12.3躍然紙上................................................................................363
12.3.1點(diǎn)睛之筆........................................................................363
12.3.2楚河漢界.........................................................................364
12.4糾結(jié)的視覺(jué)表達(dá)........................................................................366
12.4.1形式與內(nèi)容.....................................................................366
12.4.2圖示與圖表的邊界................................................................367
12.5基于隔離的兼容.........................................................................370
12.5.1向下兼容........................................................................370
12.5.2向上兼容........................................................................371
12.5.3版本轉(zhuǎn)換........................................................................372
第4 部分 復(fù)雜故事
第 13章效率提升............................................................................375
13.1建立自己的自動(dòng)化圖表模板..............................................................376
13.1.1需求分析........................................................................376
13.1.2 使用VBA來(lái)演練.................................................................377
13.1.3其他.............................................................................381
13.2創(chuàng)建自己的自動(dòng)化工具...................................................................382
13.2.1需求分析........................................................................382
13.2.2建立用戶選項(xiàng)面板...............................................................383
13.2.3執(zhí)行標(biāo)簽設(shè)置及修改作業(yè)........................................................385
13.2.4關(guān)聯(lián)到程序......................................................................391
13.2.5建立菜單系統(tǒng).....................................................................392
13.2.6設(shè)置文檔........................................................................395
13.2.7其他.............................................................................396
13.3和圖表相關(guān)的自動(dòng)化工具.................................................................397
13.3.1 Excel自帶工具..................................................................397
13.3.2使用第三方工具.................................................................397
第 14章動(dòng)態(tài)交互............................................................................399
14.1篩選及列表.............................................................................400
14.1.1自動(dòng)篩選........................................................................400
14.1.2列表.............................................................................400
14.1.3輔助區(qū)域函數(shù)篩選................................................................402
14.2定義名稱(chēng).................................................................................405
14.2.1動(dòng)態(tài)更新.........................................................................405
14.2.2切片展示.........................................................................407
14.2.3多層次..........................................................................408
14.2.4多視角..........................................................................409
14.3數(shù)據(jù)透視圖..............................................................................412
14.3.1傳統(tǒng)應(yīng)用........................................................................412
14.3.2 數(shù)據(jù)的OLAP操作.............................................................414
14.3.3與定義名稱(chēng)結(jié)合................................................................416
14.4基于宏代碼的交互........................................................................420
14.4.1交互輔助.........................................................................420
14.4.2動(dòng)態(tài)可視........................................................................421
14.4.3動(dòng)態(tài)區(qū)間.........................................................................427
14.4.4圖表事件.........................................................................429
14.5 Excel交互圖表設(shè)計(jì)建議.................................................................440
14.5.1基本建議........................................................................440
14.5.2必要的思考.....................................................................441
第 15 章 BI儀表板...........................................................................443
15.1構(gòu)成.....................................................................................444
15.1.1 BI 與儀表板.....................................................................444
15.1.2數(shù)據(jù)集合.........................................................................446
15.1.3部件.............................................................................448
15.2層級(jí)....................................................................................451
15.2.1布局.............................................................................451
15.2.2層次.............................................................................451
15.2.3樣式............................................................................452
15.2.4案例............................................................................452
15.3關(guān)聯(lián).....................................................................................454
15.3.1聯(lián)系.............................................................................454
15.3.2交互.............................................................................454
15.3.3案例.............................................................................455
15.4其他.....................................................................................465
15.4.1典型應(yīng)用........................................................................465
15.4.2細(xì)節(jié)處理.........................................................................466
15.4.3性能提高.........................................................................467
第 16章協(xié)調(diào)共生............................................................................471
16.1嵌入式應(yīng)用..............................................................................472
16.1.1 插入Excel 圖表對(duì)象.............................................................472
16.1.2 VBA 操作Excel圖表對(duì)象.......................................................473
16.1.3 Microsoft Graph圖表對(duì)象..................................................... 479
16.2圖片導(dǎo)出.................................................................................481
16.2.1 以網(wǎng)頁(yè)保存形式導(dǎo)出............................................................481
16.2.2 借助其他軟件導(dǎo)出..............................................................481
16.2.3 借助VBA 導(dǎo)出................................................................482
16.3 基于Web的應(yīng)用........................................................................486
16.3.1 在網(wǎng)頁(yè)上使用Excel圖表........................................................486
16.3.2 使用第三方工具.................................................................487
16.3.3 基于網(wǎng)頁(yè)的交互式圖表......................................................... 488
附錄A圖表技巧、案例及相應(yīng)頁(yè)碼對(duì)照.......................................................491
附錄BExcel“蜂巢”色彩分析...............................................................499
附錄C網(wǎng)絡(luò)資源..............................................................................502
附錄D本書(shū)參考.............................................................................504