Excel財務數(shù)據(jù)處理與分析自動化案例視頻精講

第1章.轉變觀念，正確認識Excel01
1.1.設計科學規(guī)范的基礎表單.02
1.1.1　每個表單都是一個具體的業(yè)務02
1.1.2　表單設計一定要有邏輯03
1.1.3　表單數(shù)據(jù)一定要規(guī)范03
1.1.4　設計基礎表單的感悟04
1.2　掌握必需的Excel工具04
1.2.1　掌握常用的數(shù)據(jù)處理工具04
1.2.2　掌握常用的函數(shù).05
1.2.3　掌握數(shù)據(jù)透視表.05
1.2.4　掌握Power Query工具05
1.2.5　掌握數(shù)據(jù)分析可視化工具06
1.2.6　學點代碼錦上添花06
1.3　培養(yǎng)Excel應用的邏輯思路.06
1.3.1　表單設計中數(shù)據(jù)管理的邏輯思路.06
1.3.2　函數(shù)公式中數(shù)據(jù)計算的邏輯思路.06
1.3.3　數(shù)據(jù)分析中企業(yè)管理的邏輯思路.07
1.4　構建數(shù)據(jù)自動化管理和分析模板08
1.4.1　設計數(shù)據(jù)自動化管理模板08
1.4.2　設計數(shù)據(jù)自動化分析模板09
第2章.快速整理加工財務數(shù)據(jù)10
2.1　將二維表轉換為一維表11
2.1.1.數(shù)據(jù)透視表方法：一個表格11
2.1.2.數(shù)據(jù)透視表方法：一個表格（準二維表）14
2.1.3　數(shù)據(jù)透視表方法：多個表格16
2.1.4　Power Query方法：一個表格.19
2.1.5　Power Query方法：多個表格.23
2.2　處理合并單元格.29
2.2.1　有合并單元格的多行標題29
2.2.2　列數(shù)據(jù)中有大量的合并單元格30
2.3　批量刪除空行和空列32
2.3.1　使用常規(guī)方法批量刪除空行和空列32
2.3.2　使用篩選方法批量刪除空行和空列33
2.3.3.數(shù)據(jù)量大的情況下批量刪除空行.34
2.4　批量刪除小計和總計35
2.4.1　使用常規(guī)方法批量刪除小計行35
2.4.2　使用篩選方法批量刪除小計行37
2.5　快速修改非法日期38
2.5.1　使用分列工具快速修改非法日期.38
2.5.2　使用分列工具修改非法日期的注意事項39
2.5.3　使用函數(shù)公式修改非法日期.40
2.5.4.輸入日期的技巧.41
2.5.5.設置日期格式的技巧42
2.6　轉換數(shù)字格式44
2.6.1.將數(shù)字轉換為原位數(shù)的文本型數(shù)字44
2.6.2.將數(shù)字轉換為固定位數(shù)的文本型數(shù)字，位數(shù)不夠就補零44
2.6.3　將文本型數(shù)字轉換為數(shù)值的技巧.45
2.6.4　容易混淆的概念：單元格和數(shù)據(jù)47
2.7　快速刪除特殊字符47
2.7.1　刪除特殊字符的常規(guī)處理方法48
2.7.2　利用Power Query快速處理49
2.8　快速填充空單元格50
2.8.1　快速填充上一行數(shù)據(jù)50
2.8.2　快速填充下一行數(shù)據(jù)51
2.8.3　快速向空單元格中填充數(shù)字052
2.9　查找與刪除重復數(shù)據(jù)53
2.9.1　使用條件格式標注重復值53
2.9.2　使用COUNTIF函數(shù)統(tǒng)計重復次數(shù)54
2.9.3　刪除重復值，保留值55
2.9.4　獲取不重復項目.56
2.10　數(shù)據(jù)分列.57
   2.10.1　根據(jù)符號分列（分列工具）.57
   2.10.2　根據(jù)固定寬度分列（分列工具）.60
   2.10.3　根據(jù)多個分隔符號分列（分列工具）.63
   2.10.4　多次分列（分列工具）65
   2.10.5　使用函數(shù)分列（IF函數(shù)）66
   2.10.6　使用函數(shù)分列（文本函數(shù)）.68
   2.10.7　使用快速填充工具（Ctrl E組合鍵）69
   2.10.8　使用Power Query分列.69
2.11　批量修改數(shù)據(jù)71
   2.11.1　批量修改全部單元格數(shù)據(jù)71
   2.11.2　批量修改部分滿足條件的單元格數(shù)據(jù)72
2.12　整理數(shù)據(jù)的常用技巧.73
   2.12.1.選擇可見單元格73
   2.12.2.快速確認實際數(shù)據(jù)區(qū)域的大小74
   2.12.3.快速定位到數(shù)據(jù)區(qū)域的邊界74
   2.12.4.快速選擇數(shù)據(jù)區(qū)域.74
   2.12.5.快速向下填充數(shù)據(jù).74
   2.12.6.快速向上填充數(shù)據(jù).75
   2.12.7.快速向左填充數(shù)據(jù).75
   2.12.8.快速向右填充數(shù)據(jù).75
   2.12.9.快速刪除所有批注.75
第3章.財務表單設計技能與實戰(zhàn)77
3.1.正確區(qū)分兩類表格78
3.1.1　基礎表單78
3.1.2　報告表格79
3.1.3　務必將基礎表單和報告表格分開.79
3.2.嚴格區(qū)分三種數(shù)據(jù)80
3.2.1　文本80
3.2.2　日期和時間.80
3.2.3　數(shù)字82
3.3.設計基礎表單，是基礎中的基礎82
3.3.1　設計的基礎表單要有邏輯性.83
3.3.2　嚴禁用Word思維設計Excel表單83
3.3.3　數(shù)據(jù)輸入要規(guī)范.83
3.4　設計與維護表單的實用技能.84
3.4.1　思維導圖：設計表單架構84
3.4.2.數(shù)據(jù)驗證及其設置方法 84
3.4.3.數(shù)據(jù)驗證基本用法1：只能輸入規(guī)定格式的數(shù)字86
3.4.4.數(shù)據(jù)驗證基本用法2：只能輸入規(guī)定的日期.87
3.4.5.數(shù)據(jù)驗證基本用法3：在單元格中設計下拉列表，快速選擇
輸入數(shù)據(jù).87
3.4.6.利用函數(shù)公式輸入連續(xù)的序號89
3.4.7　利用函數(shù)公式引用基本資料表的數(shù)據(jù).90
3.4.8　根據(jù)已輸入的列數(shù)據(jù)自動計算輸入數(shù)據(jù)90
3.4.9.智能表格：自動向下復制公式，美化表格91
3.5　快速輸入數(shù)據(jù)的常用技巧.94
3.6　表單設計演練：固定資產(chǎn)管理表單94
第4章.快速核對數(shù)據(jù)98
4.1　核對數(shù)據(jù)的基本方法99
4.1.1.快速圈釋無效數(shù)據(jù)99
4.1.2.利用函數(shù)公式比對數(shù)據(jù)100
4.1.3　使用數(shù)據(jù)透視表比對數(shù)據(jù)100
4.1.4　利用Power Query比對兩個表，快速得到各種差異結果103
4.2　核對數(shù)據(jù)的經(jīng)典案例.111
4.2.1.實際案例1：核對社保.112
4.2.2.實際案例2：核對內(nèi)部往來賬115
4.2.3.實際案例3：銀行對賬.121
4.2.4.實際案例4：核對應付余額124
第5章.財務報表的格式化與美化.126
5.1　報表布局.127
5.1.1　在表格的左側和頂部留空127
5.1.2　設置邊框和不顯示網(wǎng)格線127
5.1.3　設置適合的行高與列寬128
5.1.4　設置字體和字號.128
5.1.5　設置數(shù)字的格式.129
5.1.6　設置文字向右縮進129
5.1.7　設置單元格的填充顏色130
5.1.8　在表格頂部寫上單位130
5.2　使用自定義數(shù)字格式，增強數(shù)字的可閱讀性130
5.2.1　自定義數(shù)字格式的基本方法.131
5.2.2　數(shù)字的自定義格式代碼的結構131
5.2.3　縮小位數(shù)顯示數(shù)字132
5.2.4　將數(shù)字顯示為指定的顏色，并添加標識符號133
5.2.5　根據(jù)條件設置數(shù)字的自定義格式.135
5.2.6　自定義數(shù)據(jù)標簽的數(shù)字格式.135
5.3　利用條件格式自動標注重要數(shù)據(jù)137
5.3.1　數(shù)據(jù)條：標注呈現(xiàn)不同變化的數(shù)據(jù).137
5.3.2　圖標集：標準上升/下降和紅綠燈效果138
5.3.3　建立提前提醒模型，讓數(shù)據(jù)追蹤自動化139
5.3.4　使用公式設置條件格式的注意事項142
5.4　按照自己的要求進行數(shù)據(jù)排序.142
5.4.1.自定義排序.143
5.4.2　先排序再恢復原始狀態(tài)146
第6章.財務管理中的常用函數(shù)與綜合應用147
6.1.公式的基本規(guī)則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.函數(shù)的基本規(guī)則151
6.2.1　什么是函數(shù).151
6.2.2　函數(shù)的基本語法.151
6.2.3　函數(shù)參數(shù)的類型.152
6.2.4　培養(yǎng)輸入函數(shù)的好習慣152
6.3　創(chuàng)建公式的實用技能技巧153
6.3.1　快速輸入函數(shù)153
6.3.2　利用“函數(shù)參數(shù)”對話框和名稱框快速創(chuàng)建嵌套函數(shù)公式.154
6.4　日期計算的常用函數(shù)及其應用.156
6.4.1.獲取當天日期：TODAY函數(shù).156
6.4.2.獲取當天日期和當前時間：NOW函數(shù)157
6.4.3.計算到期日： EDATE函數(shù)157
6.4.4.計算月底日期：EOMONTH函數(shù)157
6.4.5.計算兩個日期之間的期限：DATEDIF函數(shù)158
6.4.6.將年、月和日三個數(shù)字組合成日期：DATE函數(shù).159
6.4.7.計算星期幾：WEEKDAY函數(shù)159
6.4.8　實用公式：判斷指定日期所在的時間區(qū)間159
6.5　處理文本數(shù)據(jù)的常用函數(shù)及其應用160
6.5.1.截取一段字符： LEFT函數(shù)、RIGHT函數(shù)和MID函數(shù).160
6.5.2.查找指定字符在字符串中的位置： FIND函數(shù)161
6.5.3.替換固定位數(shù)的字符：SUBSTITUTE函數(shù)162
6.5.4.把數(shù)字和日期轉換為指定格式的文本：TEXT函數(shù)163
6.6　邏輯判斷函數(shù)及其應用164
6.6.1　IF函數(shù)：基本原理與基本應用164
6.6.2　IF函數(shù)嵌套的邏輯思路與技能技巧165
6.6.3　使用IFERROR函數(shù)處理錯誤值.166
6.7　分類匯總函數(shù)及其應用167
6.7.1　單條件計數(shù)統(tǒng)計匯總：COUNTIF函數(shù).167
6.7.2　多條件計數(shù)統(tǒng)計匯總：COUNTIFS函數(shù)168
6.7.3　單條件求和匯總：SUMIF函數(shù)169
6.7.4　多條件求和匯總： SUMIFS函數(shù).170
6.7.5.超過15位數(shù)字長編碼的條件計數(shù)與求和問題.172
6.8　查找引用函數(shù)及其應用173
6.8.1　VLOOKUP函數(shù)：基本原理與基本應用173
6.8.2　VLOOKUP函數(shù)：使用通配符匹配條件174
6.8.3　VLOOKUP函數(shù)：自動定位取數(shù)的列號175
6.8.4　VLOOKUP函數(shù)：模糊匹配查找.176
6.8.5　MATCH函數(shù)：基本原理與用法177
6.8.6　INDEX函數(shù)：基本原理與用法.178
6.8.7　常用查找函數(shù)VLOOKUP、MATCH和INDEX的總結 .180
6.9　綜合練習與數(shù)據(jù)分析.181
6.9.1　制作銷售分析底稿181
6.9.2　編制應收賬款賬齡分析表182
6.9.3　利用函數(shù)制作動態(tài)圖表185
6.9.4　財務報表動態(tài)分析191
第7章.快速合并匯總大量工作表.193
7.1　合并結構完全相同的工作表194
7.1.1　使用SUM函數(shù)快速合并，得到所有工作表的合計數(shù)194
7.1.2　使用合并計算工具快速合并，得到具有分級顯示的合并表.195
7.1.3　匯總不同工作簿中大量結構相同的工作表198
7.2　利用數(shù)據(jù)透視表合并匯總199
7.2.1　二維表的合并：使用多重合并計算數(shù)據(jù)區(qū)域透視表199
7.2.2　一維表的合并：現(xiàn)有連接 SQL語句202
7.3　利用Power Query快速合并匯總.207
7.3.1　合并匯總一個工作簿中的多個工作表.207
7.3.2　合并匯總一個文件夾中的多個工作簿.212
7.4　快速匯總有關聯(lián)的工作表219
7.4.1　使用函數(shù)匯總有關聯(lián)的工作表219
7.4.2　使用Microsoft Query工具匯總有關聯(lián)的工作表221
7.4.3　使用Power Query工具匯總有關聯(lián)的工作表225
7.5　利用INDIRECT函數(shù)快速合并匯總228
7.5.1　INDIRECT函數(shù)：匯總大量工作表的核心函數(shù)228
7.5.2　INDIRECT函數(shù)：快速抓取每個表格的合計數(shù)230
7.5.3　INDIRECT函數(shù)：建立滾動匯總分析表.231
第8章.利用數(shù)據(jù)透視表靈活分析數(shù)據(jù)234
8.1　數(shù)據(jù)透視表的基礎是規(guī)范表單.235
8.1.1　規(guī)范表單結構235
8.1.2　規(guī)范表單數(shù)據(jù)235
8.2　創(chuàng)建數(shù)據(jù)透視表的基本方法235
8.2.1　以當前工作簿中的一個一維工作表創(chuàng)建數(shù)據(jù)透視表236
8.2.2　以當前工作簿中的多個一維工作表創(chuàng)建數(shù)據(jù)透視表238
8.2.3　以當前工作簿中的一個二維工作表創(chuàng)建數(shù)據(jù)透視表240
8.2.4　以當前工作簿中的多個二維工作表創(chuàng)建數(shù)據(jù)透視表241
8.3.數(shù)據(jù)透視表的美化.242
8.3.1　設置數(shù)據(jù)透視表的樣式242
8.3.2　設置報表布局243
8.3.3　修改字段名稱244
8.3.4　顯示/取消分類匯總244
8.3.5　顯示/取消報表總計245
8.3.6　合并標簽單元格.246
8.3.7　調(diào)整字段下項目的次序246
8.3.8　設置數(shù)字格式247
8.4　利用數(shù)據(jù)透視表分析數(shù)據(jù)的主要技能247
8.4.1　排序篩選，找出前10名客戶.248
8.4.2　改變值字段的匯總依據(jù)，制作多種計算結果的報告248
8.4.3　改變值字段的顯示方式，制作結構分析報告249
8.4.4　組合日期，制作年報、季報、月報250
8.4.5.使用切片器快速篩選數(shù)據(jù)251
8.4.6　繪制數(shù)據(jù)透視圖，讓分析結果可視化.252
8.5　快速制作明細表253
8.5.1　每次制作一個指定項目的明細表.253
8.5.2　批量制作所有項目的明細表.254