Excel財務數據處理與分析自動化案例視頻精講

目錄

第1章.轉變觀念，正確認識Excel........................01

1.1.設計科學規範的基礎表單............................................................................02

1.1.1　每個表單都是一個具體的業務....................................................................02

1.1.2　表單設計一定要有邏輯.................................................................................03

1.1.3　表單數據一定要規範......................................................................................03

1.1.4　設計基礎表單的感悟......................................................................................04

1.2　掌握必需的Excel工具.................................................................................04

1.2.1　掌握常用的數據處理工具.............................................................................04

1.2.2　掌握常用的函數..............................................................................................05

1.2.3　掌握數據透視表..............................................................................................05

1.2.4　掌握Power Query工具.................................................................................05

1.2.5　掌握數據分析可視化工具.............................................................................06

1.2.6　學點代碼錦上添花..........................................................................................06

1.3　培養Excel應用的邏輯思路.........................................................................06

1.3.1　表單設計中數據管理的邏輯思路................................................................06

1.3.2　函數公式中數據計算的邏輯思路................................................................06

1.3.3　數據分析中企業管理的邏輯思路................................................................07

1.4　構建數據自動化管理和分析模板.................................................................08

1.4.1　設計數據自動化管理模板.............................................................................08

1.4.2　設計數據自動化分析模板.............................................................................09

第2章.快速整理加工財務數據..............................10

2.1　將二維表轉換為一維表................................................................................11

2.1.1.數據透視表方法：一個表格..........................................................................11

2.1.2.數據透視表方法：一個表格（準二維表）..................................................14

2.1.3　數據透視表方法：多個表格..........................................................................16

2.1.4　Power Query方法：一個表格......................................................................19

2.1.5　Power Query方法：多個表格......................................................................23

2.2　處理合並單元格...........................................................................................29

2.2.1　有合並單元格的多行標題.............................................................................29

2.2.2　列數據中有大量的合並單元格....................................................................30

2.3　批量刪除空行和空列....................................................................................32

2.3.1　使用常規方法批量刪除空行和空列............................................................32

2.3.2　使用篩選方法批量刪除空行和空列............................................................33

2.3.3.數據量大的情況下批量刪除空行................................................................34

2.4　批量刪除小計和總計....................................................................................35

2.4.1　使用常規方法批量刪除小計行....................................................................35

2.4.2　使用篩選方法批量刪除小計行....................................................................37

2.5　快速修改非法日期.......................................................................................38

2.5.1　使用分列工具快速修改非法日期................................................................38

2.5.2　使用分列工具修改非法日期的註意事項...................................................39

2.5.3　使用函數公式修改非法日期.........................................................................40

2.5.4.輸入日期的技巧..............................................................................................41

2.5.5.設置日期格式的技巧......................................................................................42

2.6　轉換數字格式...............................................................................................44

2.6.1.將數字轉換為原位數的文本型數字............................................................44

2.6.2.將數字轉換為固定位數的文本型數字，位數不夠就補零.....................44

2.6.3　將文本型數字轉換為數值的技巧................................................................45

2.6.4　容易混淆的概念：單元格和數據.................................................................47

2.7　快速刪除特殊字符.......................................................................................47

2.7.1　刪除特殊字符的常規處理方法....................................................................48

2.7.2　利用Power Query快速處理........................................................................49

2.8　快速填充空單元格.......................................................................................50

2.8.1　快速填充上一行數據......................................................................................50

2.8.2　快速填充下一行數據......................................................................................51

2.8.3　快速向空單元格中填充數字0.....................................................................52

2.9　查找與刪除重復數據....................................................................................53

2.9.1　使用條件格式標註重復值.............................................................................53

2.9.2　使用COUNTIF函數統計重復次數..............................................................54

2.9.3　刪除重復值，保留唯一值.............................................................................55

2.9.4　獲取不重復項目..............................................................................................56

2.10　數據分列....................................................................................................57

   2.10.1　根據符號分列（分列工具）......................................................................57

   2.10.2　根據固定寬度分列（分列工具）.............................................................60

   2.10.3　根據多個分隔符號分列（分列工具）....................................................63

   2.10.4　多次分列（分列工具）..............................................................................65

   2.10.5　使用函數分列（IF函數）..........................................................................66

   2.10.6　使用函數分列（文本函數）......................................................................68

   2.10.7　使用快速填充工具（Ctrl+E組合鍵）.....................................................69

   2.10.8　使用Power Query分列............................................................................69

2.11　批量修改數據.............................................................................................71

   2.11.1　批量修改全部單元格數據........................................................................71

   2.11.2　批量修改部分滿足條件的單元格數據..................................................72

2.12　整理數據的常用技巧..................................................................................73

   2.12.1.選擇可見單元格.........................................................................................73

   2.12.2.快速確認實際數據區域的大小...............................................................74

   2.12.3.快速定位到數據區域的邊界....................................................................74

   2.12.4.快速選擇數據區域.....................................................................................74

   2.12.5.快速向下填充數據.....................................................................................74

   2.12.6.快速向上填充數據.....................................................................................75

   2.12.7.快速向左填充數據.....................................................................................75

   2.12.8.快速向右填充數據.....................................................................................75

   2.12.9.快速刪除所有批註.....................................................................................75

第3章.財務表單設計技能與實戰...........................77

3.1.正確區分兩類表格.......................................................................................78

3.1.1　基礎表單...........................................................................................................78

3.1.2　報告表格...........................................................................................................79

3.1.3　務必將基礎表單和報告表格分開................................................................79

3.2.嚴格區分三種數據.......................................................................................80

3.2.1　文本....................................................................................................................80

3.2.2　日期和時間.......................................................................................................80

3.2.3　數字....................................................................................................................82

3.3.設計基礎表單，是基礎中的基礎.................................................................82

3.3.1　設計的基礎表單要有邏輯性.........................................................................83

3.3.2　嚴禁用Word思維設計Excel表單..............................................................83

3.3.3　數據輸入要規範..............................................................................................83

3.4　設計與維護表單的實用技能.........................................................................84

3.4.1　思維導圖：設計表單架構..............................................................................84

3.4.2.數據驗證及其設置方法 ................................................................................84

3.4.3.數據驗證基本用法1：只能輸入規定格式的數字...................................86

3.4.4.數據驗證基本用法2：只能輸入規定的日期...........................................87

3.4.5.數據驗證基本用法3：在單元格中設計下拉列表，快速選擇 輸入數據................................................................................................................87

3.4.6.利用函數公式輸入連續的序號....................................................................89

3.4.7　利用函數公式引用基本資料表的數據.......................................................90

3.4.8　根據已輸入的列數據自動計算輸入數據...................................................90

3.4.9.智能表格：自動向下復制公式，美化表格................................................91

3.5　快速輸入數據的常用技巧............................................................................94

3.6　表單設計演練：固定資產管理表單..............................................................94

第4章.快速核對數據..........................................98

4.1　核對數據的基本方法....................................................................................99

4.1.1.快速圈釋無效數據..........................................................................................99

4.1.2.利用函數公式比對數據..............................................................................100

4.1.3　使用數據透視表比對數據..........................................................................100

4.1.4　利用Power Query比對兩個表，快速得到各種差異結果.................103

4.2　核對數據的經典案例..................................................................................111

4.2.1.實際案例1：核對社保...............................................................................112

4.2.2.實際案例2：核對內部往來賬..................................................................115

4.2.3.實際案例3：銀行對賬...............................................................................121

4.2.4.實際案例4：核對應付餘額.......................................................................124

第5章.財務報表的格式化與美化.........................126

5.1　報表佈局....................................................................................................127

5.1.1　在表格的左側和頂部留空..........................................................................127

5.1.2　設置邊框和不顯示網格線..........................................................................127

5.1.3　設置適合的行高與列寬..............................................................................128

5.1.4　設置字體和字號...........................................................................................128

5.1.5　設置數字的格式...........................................................................................129

5.1.6　設置文字向右縮進.......................................................................................129

5.1.7　設置單元格的填充顏色..............................................................................130

5.1.8　在表格頂部寫上單位...................................................................................130

5.2　使用自定義數字格式，增強數字的可閱讀性.............................................130

5.2.1　自定義數字格式的基本方法......................................................................131

5.2.2　數字的自定義格式代碼的結構.................................................................131

5.2.3　縮小位數顯示數字.......................................................................................132

5.2.4　將數字顯示為指定的顏色，並添加標識符號.......................................133

5.2.5　根據條件設置數字的自定義格式.............................................................135

5.2.6　自定義數據標簽的數字格式......................................................................135

5.3　利用條件格式自動標註重要數據...............................................................137

5.3.1　數據條：標註呈現不同變化的數據..........................................................137

5.3.2　圖標集：標準上升/下降和紅綠燈效果..................................................138

5.3.3　建立提前提醒模型，讓數據追蹤自動化................................................139

5.3.4　使用公式設置條件格式的註意事項.........................................................142

5.4　按照自己的要求進行數據排序...................................................................142

5.4.1.自定義排序....................................................................................................143

5.4.2　先排序再恢復原始狀態..............................................................................146

第6章.財務管理中的常用函數與綜合應用............147

6.1.公式的基本規則.........................................................................................148

6.1.1　什麽是公式....................................................................................................148

6.1.2　公式中的元素................................................................................................148

6.1.3　公式中的運算符...........................................................................................148

6.1.4　公式中的常量................................................................................................149

6.1.5　公式中的標點符號.......................................................................................149

6.1.6.復制公式時合理設置相對引用和絕對引用............................................150

6.2.函數的基本規則.........................................................................................151

6.2.1　什麽是函數....................................................................................................151

6.2.2　函數的基本語法...........................................................................................151

6.2.3　函數參數的類型...........................................................................................152

6.2.4　培養輸入函數的好習慣..............................................................................152

6.3　創建公式的實用技能技巧..........................................................................153

6.3.1　快速輸入函數................................................................................................153

6.3.2　利用“函數參數”對話框和名稱框快速創建嵌套函數公式.............154

6.4　日期計算的常用函數及其應用...................................................................156

6.4.1.獲取當天日期：TODAY函數......................................................................156

6.4.2.獲取當天日期和當前時間：NOW函數...................................................157

6.4.3.計算到期日： EDATE函數...........................................................................157

6.4.4.計算月底日期：EOMONTH函數..............................................................157

6.4.5.計算兩個日期之間的期限：DATEDIF函數.............................................158

6.4.6.將年、月和日三個數字組合成日期：DATE函數..................................159

6.4.7.計算星期幾：WEEKDAY函數....................................................................159

6.4.8　實用公式：判斷指定日期所在的時間區間.............................................159

6.5　處理文本數據的常用函數及其應用...........................................................160

6.5.1.截取一段字符： LEFT函數、RIGHT函數和MID函數.........................160

6.5.2.查找指定字符在字符串中的位置： FIND函數.......................................161

6.5.3.替換固定位數的字符：SUBSTITUTE函數.............................................162

6.5.4.把數字和日期轉換為指定格式的文本：TEXT函數..............................163

6.6　邏輯判斷函數及其應用..............................................................................164

6.6.1　IF函數：基本原理與基本應用..................................................................164

6.6.2　IF函數嵌套的邏輯思路與技能技巧.........................................................165

6.6.3　使用IFERROR函數處理錯誤值................................................................166

6.7　分類匯總函數及其應用..............................................................................167

6.7.1　單條件計數統計匯總：COUNTIF函數....................................................167

6.7.2　多條件計數統計匯總：COUNTIFS函數..................................................168

6.7.3　單條件求和匯總：SUMIF函數..................................................................169

6.7.4　多條件求和匯總： SUMIFS函數................................................................170

6.7.5.超過15位數字長編碼的條件計數與求和問題.....................................172

6.8　查找引用函數及其應用..............................................................................173

6.8.1　VLOOKUP函數：基本原理與基本應用...................................................173

6.8.2　VLOOKUP函數：使用通配符匹配條件...................................................174

6.8.3　VLOOKUP函數：自動定位取數的列號...................................................175

6.8.4　VLOOKUP函數：模糊匹配查找................................................................176

6.8.5　MATCH函數：基本原理與用法.................................................................177

6.8.6　INDEX函數：基本原理與用法...................................................................178

6.8.7　常用查找函數VLOOKUP、MATCH和INDEX的總結 .........................180

6.9　綜合練習與數據分析..................................................................................181

6.9.1　製作銷售分析底稿.......................................................................................181

6.9.2　編制應收賬款賬齡分析表..........................................................................182

6.9.3　利用函數製作動態圖表..............................................................................185

6.9.4　財務報表動態分析.......................................................................................191

第7章.快速合並匯總大量工作表.........................193

7.1　合並結構完全相同的工作表.......................................................................194

7.1.1　使用SUM函數快速合並，得到所有工作表的合計數........................194

7.1.2　使用合並計算工具快速合並，得到具有分級顯示的合並表.............195

7.1.3　匯總不同工作簿中大量結構相同的工作表............................................198

7.2　利用數據透視表合並匯總..........................................................................199

7.2.1　二維表的合並：使用多重合並計算數據區域透視表...........................199

7.2.2　一維表的合並：現有連接+SQL語句......................................................202

7.3　利用Power Query快速合並匯總.............................................................207

7.3.1　合並匯總一個工作簿中的多個工作表....................................................207

7.3.2　合並匯總一個文件夾中的多個工作簿....................................................212

7.4　快速匯總有關聯的工作表..........................................................................219

7.4.1　使用函數匯總有關聯的工作表.................................................................219

7.4.2　使用Microsoft Query工具匯總有關聯的工作表.................................221

7.4.3　使用Power Query工具匯總有關聯的工作表.......................................225

7.5　利用INDIRECT函數快速合並匯總............................................................228

7.5.1　INDIRECT函數：匯總大量工作表的核心函數.......................................228

7.5.2　INDIRECT函數：快速抓取每個表格的合計數.......................................230

7.5.3　INDIRECT函數：建立滾動匯總分析表....................................................231

第8章.利用數據透視表靈活分析數據..................234

8.1　數據透視表的基礎是規範表單...................................................................235

8.1.1　規範表單結構................................................................................................235

8.1.2　規範表單數據................................................................................................235

8.2　創建數據透視表的基本方法.......................................................................235

8.2.1　以當前工作簿中的一個一維工作表創建數據透視表..........................236

8.2.2　以當前工作簿中的多個一維工作表創建數據透視表..........................238

8.2.3　以當前工作簿中的一個二維工作表創建數據透視表..........................240

8.2.4　以當前工作簿中的多個二維工作表創建數據透視表..........................241

8.3.數據透視表的美化.....................................................................................242

8.3.1　設置數據透視表的樣式..............................................................................242

8.3.2　設置報表佈局................................................................................................243

8.3.3　修改字段名稱................................................................................................244

8.3.4　顯示/取消分類匯總...................................................................................244

8.3.5　顯示/取消報表總計...................................................................................245

8.3.6　合並標簽單元格...........................................................................................246

8.3.7　調整字段下項目的次序..............................................................................246

8.3.8　設置數字格式................................................................................................247

8.4　利用數據透視表分析數據的主要技能........................................................247

8.4.1　排序篩選，找出前10名客戶...................................................................248

8.4.2　改變值字段的匯總依據，製作多種計算結果的報告..........................248

8.4.3　改變值字段的顯示方式，製作結構分析報告.......................................249

8.4.4　組合日期，製作年報、季報、月報.........................................................250

8.4.5.使用切片器快速篩選數據..........................................................................251

8.4.6　繪制數據透視圖，讓分析結果可視化....................................................252

8.5　快速製作明細表.........................................................................................253

8.5.1　每次製作一個指定項目的明細表.............................................................253

8.5.2　批量製作所有項目的明細表......................................................................254