Excel財務數據處理與分析實戰技巧精粹

郭 辉

现任某公司财务总监，拥有28年的财务工作经验，历任成本会计、总账会计、财务经理、财务总监，具有丰富的财务实战经验；拥有20年的Excel学习培训经历，曾经担任Excel Home论坛函数版版主，在国内多个大型教育平台有视频课程，为诸多大型企业做Excel内训；精通企业管理，善于利用财务数据分析为企业决策提供数据支撑，曾利用金蝶BOS平台为企业量身打造出一款企业管理软件，覆盖企业的采购、生产、销售、外协、送货、资金和财务等所有环节。

通用技巧篇

第 1章　數據的獲取　2

技巧001　批量導入文本數據　2

技巧002　批量導入網頁數據　8

技巧003　批量填充導出數據　9

技巧004　批量修改數據　11

技巧005　剔除重復記錄　12

技巧006　批量處理不規範的日期數據　13

技巧007　行數據和列數據的轉換　15

技巧008　快速拆分英文代碼和漢字名稱的混合數據　16

技巧009　快速拆分日期　17

技巧010　兩種方法解決導出的數據不能求和的問題　20

技巧011　批量剔除導出數據首部或尾部的不可見字符　24

技巧012　解決導出的文本內容不匹配問題　29

第 2章　數據的自定義格式　32

技巧013　分段顯示長數據　32

技巧014　帶有計量單位的數據求和　34

技巧015　自動填寫表頭的3種方法　36

技巧016　批量編輯多個單元格區域　39

技巧017　隱藏敏感數據　40

技巧018　根據日期自動獲取對應的星期　41

技巧019　高效錄入編碼　43

第3章　數據的規範錄入　45

技巧020　使用單元格下拉列表規範錄入內容　45

技巧021　使用跨表單元格下拉列表規範錄入內容　47

技巧022　限制日期錄入範圍　48

技巧023　限制數字錄入範圍　49

技巧024　限制單元格只能錄入特定編碼　51

技巧025　設置數據錄入範圍提醒　52

技巧026　非法錄入數據時顯示出錯警告　53

技巧027　保持引用位置不變的單元格公式復制（絕對引用的作用）　54

技巧028　限制輸入重復數據　57

第4章　高效篩選數據　59

技巧029　單條件篩選　59

技巧030　雙條件交叉關系篩選　61

技巧031　多條件篩選　62

技巧032　區間條件篩選　63

技巧033　多字段條件篩選　64

技巧034　組合條件篩選　66

技巧035　數據對比篩選　67

技巧036　公式條件篩選　68

技巧037　公式篩選法之從外部獲取篩選條件　69

技巧038　利用高級篩選功能拆分數據匯總表　71

技巧039　利用高級篩選功能快速對比數據　72

技巧040　利用高級篩選功能快速對重復項目求和　74

第5章　數據的匯總與統計　76

技巧041　快速生成匯總表——數據透視表法　76

技巧042　快速拆分匯總表為明細表　82

技巧043　利用切片器自由選擇篩選數據　83

技巧044　更新追加數據後的匯總數據　85

技巧045　快速轉換匯總佈局　85

技巧046　批量合並相同內容的單元格　86

技巧047　對合並單元格數據進行排序　89

第6章　利用條件格式標識重點數據　92

技巧048　凸顯重復數據　92

技巧049　自動標記排名前三和後三的數據　95

技巧050　標記高於或低於平均值的數據　97

技巧051　用色階標識數據的大小差異　99

技巧052　用數據條展示數據大小　100

技巧053　用餅圖展示數據的百分比關系　102

技巧054　用圓環圖展示百分比指標　106

技巧055　用折線圖展示數據的趨勢變化　110

技巧056　用條形圖展示指標完成情況　112

技巧057　用瀑布圖展示數據增減幅度　117

第7章　IF函數的嵌套應用　119

技巧058　雙結果單條件判斷取值　119

技巧059　三結果單條件IF函數嵌套判斷取值　121

技巧060　雙結果雙條件（或）判斷取值　122

技巧061　雙結果雙條件（並且關系）不等判斷取值　124

技巧062　雙結果雙條件（並且關系）相等判斷取值　125

技巧063　雙結果多條件（或關系）判斷取值　127

技巧064　多條件的三類結果嵌套判斷取值　128

第8章　數據的查找　130

技巧065　用工號本表查找姓名　130

技巧066　用商品編號跨表查找商品信息　132

技巧067　工齡級別歸檔查詢　134

技巧068　銷售業績考核歸檔查詢　135

技巧069　查找編碼所在的位置數　139

技巧070　快速比對兩組數據中的相同內容　140

技巧071　用公式查找數據表中的最後一條記錄　141

第9章　數據的保護　145

技巧072　設置工作簿打開權限　145

技巧073　保護和取消保護工作簿　146

技巧074　保護工作表之正向操作法　147

技巧075　保護工作表之反向操作法　150

技巧076　保護工作表中允許操作的項目　151

技巧077　設置表格內報表區域的編輯權限　152

第 10章　巧用定位功能加工數據　155

技巧078　批量添加求和公式　155

技巧079　批量填充資產類別列中的空白單元格　156

技巧080　批量填充日期、會計期間、憑證字號列的空白單元格　158

技巧081　一鍵清除報表中的非公式區數據　160

技巧082　標記公式單元格　161

技巧083　批量製作工資條　163

技巧084　批量刪除數據間的不連續空白單元格　166

業務技巧篇

第 11章　二維數據圖表分析法　169

技巧085　二維柱形圖數據分析法　169

技巧086　二維條形圖數據分析法　178

技巧087　二維數據圖表分析法在多行業中的應用　185

第 12章　三維數據圖表分析法　187

技巧088　搭建日報表分析系統的架構　187

技巧089　編制一張普通的數據匯總表　189

技巧090　編制影城在時間軸上的數據統計分析表　192

技巧091　編制在一個時間點上的橫向數據統計分析表　195

第 13章　月度營業收入圖表分析法　197

技巧092　設計月度數據分析結構　197

技巧093　編制數據分析模板　199

第 14章　多方案完成進度圖表分析法　212

技巧094　用餅圖展示百分數指標　212

技巧095　用單元格圖展示百分數指標　218

技巧096　用形似蓄水池的柱形圖展示百分數指標　222

綜合案例篇

第 15章　數量與金額轉換圖表分析法　235

15.1　分析方案設計　237

15.2　分析數據準備　238

15.3　編制明細數據表　239

15.4　編制分月匯總表　239

15.4.1　編制分月匯總表的內容　239

15.4.2　插入數值調節鈕控件　240

15.4.3　設置輔助功能　241

15.4.4　編制主數據區公式　242

15.4.5　編制活動數據區公式　245

15.4.6　編制及應用圖表　246

第 16章　訂單拆解圖表分析法　249

16.1　案例背景　249

16.1.1　數據來源　249

16.1.2　日報表的結構和作用　250

16.1.3　部門統計表　251

16.1.4　業務員統計表　251

16.1.5　關於日報表持續報送的思考　252

16.2　日報表的編制　252

16.2.1　數據準備　253

16.2.2　設計日報表佈局及內容　253

16.2.3　插入數據切換的控件　253

16.2.4　編輯數據區公式　254

16.3　部門統計表的編制　255

16.3.1　設計部門統計表佈局及內容　255

16.3.2　添加控件　256

16.3.3　編輯數據區公式　257

16.3.4　繪制圖表　258

16.3.5　設置條件格式　260

16.4　業務員統計表　261

16.4.1　設計表格佈局並填寫基礎數據　261

16.4.2　添加控件　262

16.4.3　編輯數據區公式　262

16.4.4　繪制圖表　263

第 17章　業績拆解圖表分析法　264

17.1　案例背景　264

17.1.1　數據來源表　264

17.1.2　數據統計分析的需求　265

17.2　案例的實現過程　266

17.2.1　數據源表的製作　266

17.2.2　匯總表基礎數據準備　267

17.2.3　插入實現數據切換功能的控件　267

17.2.4　設置公司名稱下拉列表　268

17.2.5　編輯調取分公司數據的公式　269

17.2.6　編制月份合計數數據公式　272

17.2.7　編制業務員數據公式　273

17.2.8　編制自動圖表標題公式　274

17.2.9　繪制分公司全年銷售數據匯總圖表　275

17.2.10　繪制拆分到業務員名下的數據圖表　277

17.3　應用場景拓展　278

第 18章　收入與回款對比圖表分析法　279

18.1　案例背景　280

18.1.1　數據源表　280

18.1.2　數據篩選過渡表　280

18.1.3　以圖表方式輸出合同數據　281

18.2　案例實現過程　282

18.2.1　在數據源表中做篩選準備　282

18.2.2　篩選滿足條件的合同記錄　283

18.2.3　繪制圖表前的數據準備　284

18.2.4　繪制圖表　288

18.3　應用場景拓展　292

18.3.1　工程施工行業的項目進度管理分析　293

18.3.2　預算完成情況分析　293

18.3.3　訂單執行情況分析　293

第 19章　收入與回款匯總圖表分析法　295

19.1　案例背景　295

19.2　匯總數據表及數據圖的製作　297

19.2.1　數據表存儲結構設計和數據規範化處理　297

19.2.2　匯總表格式設計與插入控件　298

19.2.3　編輯數據匯總公式　300

19.2.4　設置明細表條件格式　301

19.2.5　使用匯總數據編制圖表　303

19.3　應用場景拓展　308

第 20章　收入與銷售費用對比圖表分析法　310

20.1　3種常用的對比指標組合　310

20.2　財務分析圖表化的操作流程　312

20.2.1　案例背景　312

20.2.2　數據準備　313

20.2.3　插入控件　313

20.2.4　編制控件關聯業務員數據的公式　314

20.2.5　編制控件關聯主營業務收入數據的公式　315

20.2.6　編制控件關聯銷售費用數據的公式　316

20.2.7　設置條件格式　316

20.2.8　繪制柱形圖　318

20.3　圖表功能的拓展應用　322

20.3.1　繪制折線圖　322

20.3.2　繪制溫度計圖　323

第 21章　製作電商客服績效考核表　325

21.1　根據業務量考核績效　325

21.1.1　案例背景和數據準備　325

21.1.2　根據考核方案設計表格中各項數據的佈局　328

21.1.3　使用SUMPRODUCT函數編輯績效考核公式　329

21.2　根據時間段業務量考核績效　330

21.2.1　案例背景　330

21.2.2　數據準備　330

21.2.3　編制統計不同時間段、不同金額檔次的公式　332

21.2.4　計算獎勵　334

21.3　根據各店鋪交易額均值考核績效　335

21.3.1　案例背景　335

21.3.2　計算每日收入與收入達成率　335

21.3.3　店鋪排名並計算獎勵　337