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

第1章.轉(zhuǎn)變觀念，正確認(rèn)識Excel01
1.1.設(shè)計(jì)科學(xué)規(guī)范的基礎(chǔ)表單.02
1.1.1　每個表單都是一個具體的業(yè)務(wù)02
1.1.2　表單設(shè)計(jì)一定要有邏輯03
1.1.3　表單數(shù)據(jù)一定要規(guī)范03
1.1.4　設(shè)計(jì)基礎(chǔ)表單的感悟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　學(xué)點(diǎn)代碼錦上添花06
1.3　培養(yǎng)Excel應(yīng)用的邏輯思路.06
1.3.1　表單設(shè)計(jì)中數(shù)據(jù)管理的邏輯思路.06
1.3.2　函數(shù)公式中數(shù)據(jù)計(jì)算的邏輯思路.06
1.3.3　數(shù)據(jù)分析中企業(yè)管理的邏輯思路.07
1.4　構(gòu)建數(shù)據(jù)自動化管理和分析模板08
1.4.1　設(shè)計(jì)數(shù)據(jù)自動化管理模板08
1.4.2　設(shè)計(jì)數(shù)據(jù)自動化分析模板09
第2章.快速整理加工財(cái)務(wù)數(shù)據(jù)10
2.1　將二維表轉(zhuǎn)換為一維表11
2.1.1.數(shù)據(jù)透視表方法：一個表格11
2.1.2.數(shù)據(jù)透視表方法：一個表格（準(zhǔn)二維表）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　有合并單元格的多行標(biāo)題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　批量刪除小計(jì)和總計(jì)35
2.4.1　使用常規(guī)方法批量刪除小計(jì)行35
2.4.2　使用篩選方法批量刪除小計(jì)行37
2.5　快速修改非法日期38
2.5.1　使用分列工具快速修改非法日期.38
2.5.2　使用分列工具修改非法日期的注意事項(xiàng)39
2.5.3　使用函數(shù)公式修改非法日期.40
2.5.4.輸入日期的技巧.41
2.5.5.設(shè)置日期格式的技巧42
2.6　轉(zhuǎn)換數(shù)字格式44
2.6.1.將數(shù)字轉(zhuǎn)換為原位數(shù)的文本型數(shù)字44
2.6.2.將數(shù)字轉(zhuǎn)換為固定位數(shù)的文本型數(shù)字，位數(shù)不夠就補(bǔ)零44
2.6.3　將文本型數(shù)字轉(zhuǎn)換為數(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　查找與刪除重復(fù)數(shù)據(jù)53
2.9.1　使用條件格式標(biāo)注重復(fù)值53
2.9.2　使用COUNTIF函數(shù)統(tǒng)計(jì)重復(fù)次數(shù)54
2.9.3　刪除重復(fù)值，保留值55
2.9.4　獲取不重復(fù)項(xiàng)目.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.快速確認(rèn)實(shí)際數(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章.財(cái)務(wù)表單設(shè)計(jì)技能與實(shí)戰(zhàn)77
3.1.正確區(qū)分兩類表格78
3.1.1　基礎(chǔ)表單78
3.1.2　報告表格79
3.1.3　務(wù)必將基礎(chǔ)表單和報告表格分開.79
3.2.嚴(yán)格區(qū)分三種數(shù)據(jù)80
3.2.1　文本80
3.2.2　日期和時間.80
3.2.3　數(shù)字82
3.3.設(shè)計(jì)基礎(chǔ)表單，是基礎(chǔ)中的基礎(chǔ)82
3.3.1　設(shè)計(jì)的基礎(chǔ)表單要有邏輯性.83
3.3.2　嚴(yán)禁用Word思維設(shè)計(jì)Excel表單83
3.3.3　數(shù)據(jù)輸入要規(guī)范.83
3.4　設(shè)計(jì)與維護(hù)表單的實(shí)用技能.84
3.4.1　思維導(dǎo)圖：設(shè)計(jì)表單架構(gòu)84
3.4.2.數(shù)據(jù)驗(yàn)證及其設(shè)置方法 84
3.4.3.數(shù)據(jù)驗(yàn)證基本用法1：只能輸入規(guī)定格式的數(shù)字86
3.4.4.數(shù)據(jù)驗(yàn)證基本用法2：只能輸入規(guī)定的日期.87
3.4.5.數(shù)據(jù)驗(yàn)證基本用法3：在單元格中設(shè)計(jì)下拉列表，快速選擇
輸入數(shù)據(jù).87
3.4.6.利用函數(shù)公式輸入連續(xù)的序號89
3.4.7　利用函數(shù)公式引用基本資料表的數(shù)據(jù).90
3.4.8　根據(jù)已輸入的列數(shù)據(jù)自動計(jì)算輸入數(shù)據(jù)90
3.4.9.智能表格：自動向下復(fù)制公式，美化表格91
3.5　快速輸入數(shù)據(jù)的常用技巧.94
3.6　表單設(shè)計(jì)演練：固定資產(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比對兩個表，快速得到各種差異結(jié)果103
4.2　核對數(shù)據(jù)的經(jīng)典案例.111
4.2.1.實(shí)際案例1：核對社保.112
4.2.2.實(shí)際案例2：核對內(nèi)部往來賬115
4.2.3.實(shí)際案例3：銀行對賬.121
4.2.4.實(shí)際案例4：核對應(yīng)付余額124
第5章.財(cái)務(wù)報表的格式化與美化.126
5.1　報表布局.127
5.1.1　在表格的左側(cè)和頂部留空127
5.1.2　設(shè)置邊框和不顯示網(wǎng)格線127
5.1.3　設(shè)置適合的行高與列寬128
5.1.4　設(shè)置字體和字號.128
5.1.5　設(shè)置數(shù)字的格式.129
5.1.6　設(shè)置文字向右縮進(jìn)129
5.1.7　設(shè)置單元格的填充顏色130
5.1.8　在表格頂部寫上單位130
5.2　使用自定義數(shù)字格式，增強(qiáng)數(shù)字的可閱讀性130
5.2.1　自定義數(shù)字格式的基本方法.131
5.2.2　數(shù)字的自定義格式代碼的結(jié)構(gòu)131
5.2.3　縮小位數(shù)顯示數(shù)字132
5.2.4　將數(shù)字顯示為指定的顏色，并添加標(biāo)識符號133
5.2.5　根據(jù)條件設(shè)置數(shù)字的自定義格式.135
5.2.6　自定義數(shù)據(jù)標(biāo)簽的數(shù)字格式.135
5.3　利用條件格式自動標(biāo)注重要數(shù)據(jù)137
5.3.1　數(shù)據(jù)條：標(biāo)注呈現(xiàn)不同變化的數(shù)據(jù).137
5.3.2　圖標(biāo)集：標(biāo)準(zhǔn)上升/下降和紅綠燈效果138
5.3.3　建立提前提醒模型，讓數(shù)據(jù)追蹤自動化139
5.3.4　使用公式設(shè)置條件格式的注意事項(xiàng)142
5.4　按照自己的要求進(jìn)行數(shù)據(jù)排序.142
5.4.1.自定義排序.143
5.4.2　先排序再恢復(fù)原始狀態(tài)146
第6章.財(cái)務(wù)管理中的常用函數(shù)與綜合應(yīng)用147
6.1.公式的基本規(guī)則148
6.1.1　什么是公式.148
6.1.2　公式中的元素148
6.1.3　公式中的運(yùn)算符.148
6.1.4　公式中的常量149
6.1.5　公式中的標(biāo)點(diǎn)符號149
6.1.6.復(fù)制公式時合理設(shè)置相對引用和引用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ù)的好習(xí)慣152
6.3　創(chuàng)建公式的實(shí)用技能技巧153
6.3.1　快速輸入函數(shù)153
6.3.2　利用“函數(shù)參數(shù)”對話框和名稱框快速創(chuàng)建嵌套函數(shù)公式.154
6.4　日期計(jì)算的常用函數(shù)及其應(yīng)用.156
6.4.1.獲取當(dāng)天日期：TODAY函數(shù).156
6.4.2.獲取當(dāng)天日期和當(dāng)前時間：NOW函數(shù)157
6.4.3.計(jì)算到期日： EDATE函數(shù)157
6.4.4.計(jì)算月底日期：EOMONTH函數(shù)157
6.4.5.計(jì)算兩個日期之間的期限：DATEDIF函數(shù)158
6.4.6.將年、月和日三個數(shù)字組合成日期：DATE函數(shù).159
6.4.7.計(jì)算星期幾：WEEKDAY函數(shù)159
6.4.8　實(shí)用公式：判斷指定日期所在的時間區(qū)間159
6.5　處理文本數(shù)據(jù)的常用函數(shù)及其應(yīng)用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ù)字和日期轉(zhuǎn)換為指定格式的文本：TEXT函數(shù)163
6.6　邏輯判斷函數(shù)及其應(yīng)用164
6.6.1　IF函數(shù)：基本原理與基本應(yīng)用164
6.6.2　IF函數(shù)嵌套的邏輯思路與技能技巧165
6.6.3　使用IFERROR函數(shù)處理錯誤值.166
6.7　分類匯總函數(shù)及其應(yīng)用167
6.7.1　單條件計(jì)數(shù)統(tǒng)計(jì)匯總：COUNTIF函數(shù).167
6.7.2　多條件計(jì)數(shù)統(tǒng)計(jì)匯總：COUNTIFS函數(shù)168
6.7.3　單條件求和匯總：SUMIF函數(shù)169
6.7.4　多條件求和匯總： SUMIFS函數(shù).170
6.7.5.超過15位數(shù)字長編碼的條件計(jì)數(shù)與求和問題.172
6.8　查找引用函數(shù)及其應(yīng)用173
6.8.1　VLOOKUP函數(shù)：基本原理與基本應(yīng)用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的總結(jié) .180
6.9　綜合練習(xí)與數(shù)據(jù)分析.181
6.9.1　制作銷售分析底稿181
6.9.2　編制應(yīng)收賬款賬齡分析表182
6.9.3　利用函數(shù)制作動態(tài)圖表185
6.9.4　財(cái)務(wù)報表動態(tài)分析191
第7章.快速合并匯總大量工作表.193
7.1　合并結(jié)構(gòu)完全相同的工作表194
7.1.1　使用SUM函數(shù)快速合并，得到所有工作表的合計(jì)數(shù)194
7.1.2　使用合并計(jì)算工具快速合并，得到具有分級顯示的合并表.195
7.1.3　匯總不同工作簿中大量結(jié)構(gòu)相同的工作表198
7.2　利用數(shù)據(jù)透視表合并匯總199
7.2.1　二維表的合并：使用多重合并計(jì)算數(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　快速匯總有關(guān)聯(lián)的工作表219
7.4.1　使用函數(shù)匯總有關(guān)聯(lián)的工作表219
7.4.2　使用Microsoft Query工具匯總有關(guān)聯(lián)的工作表221
7.4.3　使用Power Query工具匯總有關(guān)聯(lián)的工作表225
7.5　利用INDIRECT函數(shù)快速合并匯總228
7.5.1　INDIRECT函數(shù)：匯總大量工作表的核心函數(shù)228
7.5.2　INDIRECT函數(shù)：快速抓取每個表格的合計(jì)數(shù)230
7.5.3　INDIRECT函數(shù)：建立滾動匯總分析表.231
第8章.利用數(shù)據(jù)透視表靈活分析數(shù)據(jù)234
8.1　數(shù)據(jù)透視表的基礎(chǔ)是規(guī)范表單.235
8.1.1　規(guī)范表單結(jié)構(gòu)235
8.1.2　規(guī)范表單數(shù)據(jù)235
8.2　創(chuàng)建數(shù)據(jù)透視表的基本方法235
8.2.1　以當(dāng)前工作簿中的一個一維工作表創(chuàng)建數(shù)據(jù)透視表236
8.2.2　以當(dāng)前工作簿中的多個一維工作表創(chuàng)建數(shù)據(jù)透視表238
8.2.3　以當(dāng)前工作簿中的一個二維工作表創(chuàng)建數(shù)據(jù)透視表240
8.2.4　以當(dāng)前工作簿中的多個二維工作表創(chuàng)建數(shù)據(jù)透視表241
8.3.數(shù)據(jù)透視表的美化.242
8.3.1　設(shè)置數(shù)據(jù)透視表的樣式242
8.3.2　設(shè)置報表布局243
8.3.3　修改字段名稱244
8.3.4　顯示/取消分類匯總244
8.3.5　顯示/取消報表總計(jì)245
8.3.6　合并標(biāo)簽單元格.246
8.3.7　調(diào)整字段下項(xiàng)目的次序246
8.3.8　設(shè)置數(shù)字格式247
8.4　利用數(shù)據(jù)透視表分析數(shù)據(jù)的主要技能247
8.4.1　排序篩選，找出前10名客戶.248
8.4.2　改變值字段的匯總依據(jù)，制作多種計(jì)算結(jié)果的報告248
8.4.3　改變值字段的顯示方式，制作結(jié)構(gòu)分析報告249
8.4.4　組合日期，制作年報、季報、月報250
8.4.5.使用切片器快速篩選數(shù)據(jù)251
8.4.6　繪制數(shù)據(jù)透視圖，讓分析結(jié)果可視化.252
8.5　快速制作明細(xì)表253
8.5.1　每次制作一個指定項(xiàng)目的明細(xì)表.253
8.5.2　批量制作所有項(xiàng)目的明細(xì)表.254