Excel財務數據合並與分析建模案例視頻精講

目錄

第1章.門店經營分析實例....................................01

1.1　數據來源於哪裡...........................................................................................02

1.2　我們需要著手解決什麽問題.........................................................................02

1.3　哪些工具可以幫助我們實現目標.................................................................03

1.4　數據合並與建模的主要步驟.........................................................................03

第2章.財務數據合並匯總的一般方法..............................07

2.1　特殊結構表格的合並....................................................................................08

2.1.1　結構完全相同的特殊表格：當前工作簿內的多個工作表......................08

2.1.2　結構完全相同的特殊表格：不同工作簿內的工作表..............................16

2.1.3　結構不完全相同的特殊表格.........................................................................20

2.2　一維表格的合並匯總....................................................................................21

2.2.1　匯總全部字段全部數據：基本方法.............................................................21

2.2.2　匯總全部字段滿足條件的部分數據............................................................25

2.2.3　匯總部分字段全部數據.................................................................................26

2.2.4　匯總部分字段滿足條件的數據....................................................................28

2.2.5.在匯總表自動添加新字段.............................................................................29

2.2.6　特別重要的幾個註意事項.............................................................................30

2.2.7　數據刷新...........................................................................................................30

2.2.8　直接創建基於多個工作表數據的合並與分析模型..................................31

2.3　二維結構表格的合並匯總............................................................................31

2.3.1　簡單的加總匯總..............................................................................................31

2.3.2　一個頁字段區分的合並匯總.........................................................................34

2.3.3　多個頁字段區分的合並匯總.........................................................................35

2.3.4　從匯總表再得到一維表.................................................................................37

2.4　關聯表格的合並匯總....................................................................................38

2.4.1　使用查找函數進行關聯合並匯總................................................................38

2.4.2　使用匯總函數進行關聯合並匯總................................................................39

2.5　重要數據合並匯總的函數方法.....................................................................40

2.5.1　使用查找引用函數匯總工作表....................................................................41

2.5.2　使用分類匯總函數匯總工作表....................................................................46

第3章.財務數據合並的Power Query方法............48

3.1　當前工作簿內的多個一維工作表匯總..........................................................49

3.1.1　合並匯總當前工作簿內的多個一維工作表：列結構完全相同.............49

3.1.2　合並匯總當前工作簿內的多個一維工作表：列結構不相同.................54

3.1.3　將當前工作簿內的多個一維工作表合並為二維工作表.........................61

3.2　當前工作簿內的多個二維工作表匯總..........................................................64

3.2.1　合並匯總當前工作簿內的多個二維工作表：列結構完全相同.............64

3.2.2　合並匯總當前工作簿內的多個二維工作表：列結構不相同.................65

3.2.3　將多個二維工作表合並為一維工作表.......................................................67

3.3　特殊情況下的工作表匯總............................................................................70

3.3.1　合並匯總當前工作簿內指定的幾個工作表...............................................70

3.3.2　合並匯總當前工作簿內的多個關聯工作表...............................................72

3.3.3　工作簿內工作表個數不定的情況................................................................78

3.3.4　有合並單元格標題的情況工作表處理.......................................................82

3.3.5　在不打開工作簿的情況下進行匯總............................................................91

3.4　多個工作簿內的多個工作表匯總.................................................................91

3.4.1　每個工作簿只有一個工作表的情況............................................................91

3.4.2　每個工作簿有多個工作表的情況................................................................95

3.4.3　匯總文件夾內指定類型的工作簿.............................................................102

3.4.4　工作簿保存在不同文件夾的情況.............................................................103

3.5　匯總數據導出方式.....................................................................................103

3.5.1　導出為Excel表.............................................................................................103

3.5.2　導出為僅連接................................................................................................103

3.5.3　添加到數據模型...........................................................................................104

第4章.財務數據合並的Excel VBA方法...............105

4.1　需要瞭解和掌握的Excel VBA基本知識....................................................106

4.1.1　VBA代碼保存位置.......................................................................................106

4.1.2　有VBA代碼的工作簿保存.........................................................................106

4.1.3　程序基本結構................................................................................................107

4.1.4　定義變量........................................................................................................108

4.1.5　循環語句........................................................................................................110

4.1.6　判斷語句........................................................................................................111

4.1.7　語句書寫的幾個技巧...................................................................................113

4.1.8　操作工作簿....................................................................................................114

4.1.9　操作工作表....................................................................................................116

4.1.10　操作單元格..................................................................................................118

4.1.11　獲取指定文件夾里的文件........................................................................121

4.2　匯總當前工作簿的工作表數據...................................................................122

4.2.1　匯總當前工作簿的指定工作表的所有數據............................................122

4.2.2　匯總當前工作簿的指定工作表的滿足條件的數據...............................124

4.2.3　當前工作簿內特殊結構工作表的匯總....................................................126

4.2.4　匯總工作簿中指定類型的工作表.............................................................129

4.3　匯總多個工作簿的指定工作表數據...........................................................130

4.3.1　每個工作簿只有一個工作表......................................................................130

4.3.2　每個工作簿有多個工作表..........................................................................133

4.3.3　工作簿保存在不同文件夾中的情況.........................................................134

第5章.財務數據合並的Tableau方法.................137

5.1　關於並集和關聯.........................................................................................138

5.2　並集合並：一個Excel工作簿內多個工作表..............................................138

5.2.1　創建並集的基本方法...................................................................................138

5.2.2　合並一個工作簿內的全部工作表：列數、列順序一樣.......................143

5.2.3　合並一個工作簿內的全部工作表：列數一樣，但列順序不一樣......143

5.2.4　合並一個工作簿內的全部工作表：列數和列順序不一樣...................144

5.2.5　合並一個工作簿中的部分特定工作表....................................................145

5.2.6　合並一個工作簿中除特定工作表外的其他工作表...............................147

5.2.7　向已有的並集中追加新表..........................................................................148

5.2.8　移除並集中不需要的表..............................................................................151

5.3　並集合並：多個Excel工作簿....................................................................152

5.3.1　同一個文件夾里的所有工作簿.................................................................152

5.3.2　同一個文件夾里的部分工作簿.................................................................154

5.3.3　不同文件夾里的所有工作簿......................................................................156

5.3.4　不同文件夾里的部分工作簿......................................................................159

5.3.5　文件夾里的部分工作簿的部分工作表....................................................160

5.4　關聯合並：一個Excel工作簿內的多個工作表..........................................160

5.4.1　內部連接........................................................................................................160

5.4.2　左側連接........................................................................................................163

5.4.3　右側連接........................................................................................................166

5.4.4　完全外部連接................................................................................................167

5.4.5　多字段連接....................................................................................................168

5.4.6　多表不同字段的連接與整合......................................................................171

5.4.7　不同數據來源的表連接合並......................................................................174

5.4.8　編輯連接........................................................................................................176

5.5　從PDF文件採集數據................................................................................176

5.5.1　一個簡單的PDF表格文件.........................................................................176

5.5.2　從PDF報告中提取表格數據....................................................................180

5.5.3　從PDF報告中合並表格數據....................................................................181

第6章.財務分析建模：財務報表分析...................185

6.1　利潤分析....................................................................................................186

6.1.1　分析累計利潤預算完成情況......................................................................187

6.1.2　跟蹤分析各月的預算完成情況.................................................................190

6.1.3　分析某個月的預算完成情況......................................................................191

6.1.4　分析某季度的預算完成情況......................................................................194

6.1.5　兩年同比增長情況分析..............................................................................195

6.1.6　損益項目結構分析.......................................................................................197

6.1.7　單項分析........................................................................................................201

6.2　資產與負債分析.........................................................................................202

6.2.1　資產變化及結構分析...................................................................................203

6.2.2　負債變化及結構分析...................................................................................205

6.2.3　分析應收項目與應付項目的關聯與變化................................................206

6.2.4　應收賬款與存貨的對比與變化分析.........................................................207

6.2.5　重點項目的比率分析...................................................................................208

6.3　現金流量分析.............................................................................................211

6.3.1　現金流量變化跟蹤分析..............................................................................212

6.3.2　現金流入與流出項目變化跟蹤分析.........................................................212

6.3.3　其他現金流入與流出項目變化跟蹤分析................................................213

6.3.4　現金流量凈額跟蹤分析..............................................................................214

6.4　三大報表關聯分析.....................................................................................215

6.4.1　收入與現金的對比分析..............................................................................215

6.4.2　支出與現金的對比分析..............................................................................216

6.4.3　收入、應收和存貨的對比分析.................................................................217

6.4.4　財務指標預警................................................................................................217

第7章.財務分析建模：綜合應用案例...................219

7.1　以Excel為工具的數據分析建模................................................................220

7.1.1　各門店的累計銷售排名分析......................................................................220

7.1.2　指定門店的累計銷售結構分析.................................................................221

7.1.3　指定門店的日銷售趨勢分析......................................................................223

7.1.4　各門店各類別銷售毛利率對比分析.........................................................224

7.2　以Power Query為工具的數據分析建模..................................................225

7.2.1　構建數據合並模型.......................................................................................225

7.2.2　創建Power Pivot.........................................................................................226

7.2.3　設計分析報告................................................................................................228

7.3　以Tableau為工具的數據分析建模...........................................................229

7.3.1　建立數據連接，合並整理數據.................................................................229

7.3.2　製作可視化圖表...........................................................................................233