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

第1章.轉(zhuǎn)變觀念，正確認(rèn)識(shí)Excel01
1.1.設(shè)計(jì)科學(xué)規(guī)范的基礎(chǔ)表單.02
1.1.1　每個(gè)表單都是一個(gè)具體的業(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ù)自動(dòng)化管理和分析模板08
1.4.1　設(shè)計(jì)數(shù)據(jù)自動(dòng)化管理模板08
1.4.2　設(shè)計(jì)數(shù)據(jù)自動(dòng)化分析模板09
第2章.快速整理加工財(cái)務(wù)數(shù)據(jù)10
2.1　將二維表轉(zhuǎn)換為一維表11
2.1.1.數(shù)據(jù)透視表方法：一個(gè)表格11
2.1.2.數(shù)據(jù)透視表方法：一個(gè)表格（準(zhǔn)二維表）14
2.1.3　數(shù)據(jù)透視表方法：多個(gè)表格16
2.1.4　Power Query方法：一個(gè)表格.19
2.1.5　Power Query方法：多個(gè)表格.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　容易混淆的概念：?jiǎn)卧窈蛿?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ù)符號(hào)分列（分列工具）.57
   2.10.2　根據(jù)固定寬度分列（分列工具）.60
   2.10.3　根據(jù)多個(gè)分隔符號(hào)分列（分列工具）.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.選擇可見(jiàn)單元格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　報(bào)告表格79
3.1.3　務(wù)必將基礎(chǔ)表單和報(bào)告表格分開(kāi).79
3.2.嚴(yán)格區(qū)分三種數(shù)據(jù)80
3.2.1　文本80
3.2.2　日期和時(shí)間.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ù)的序號(hào)89
3.4.7　利用函數(shù)公式引用基本資料表的數(shù)據(jù).90
3.4.8　根據(jù)已輸入的列數(shù)據(jù)自動(dòng)計(jì)算輸入數(shù)據(jù)90
3.4.9.智能表格：自動(dòng)向下復(fù)制公式，美化表格91
3.5　快速輸入數(shù)據(jù)的常用技巧.94
3.6　表單設(shè)計(jì)演練：固定資產(chǎn)管理表單94
第4章.快速核對(duì)數(shù)據(jù)98
4.1　核對(duì)數(shù)據(jù)的基本方法99
4.1.1.快速圈釋無(wú)效數(shù)據(jù)99
4.1.2.利用函數(shù)公式比對(duì)數(shù)據(jù)100
4.1.3　使用數(shù)據(jù)透視表比對(duì)數(shù)據(jù)100
4.1.4　利用Power Query比對(duì)兩個(gè)表，快速得到各種差異結(jié)果103
4.2　核對(duì)數(shù)據(jù)的經(jīng)典案例.111
4.2.1.實(shí)際案例1：核對(duì)社保.112
4.2.2.實(shí)際案例2：核對(duì)內(nèi)部往來(lái)賬115
4.2.3.實(shí)際案例3：銀行對(duì)賬.121
4.2.4.實(shí)際案例4：核對(duì)應(yīng)付余額124
第5章.財(cái)務(wù)報(bào)表的格式化與美化.126
5.1　報(bào)表布局.127
5.1.1　在表格的左側(cè)和頂部留空127
5.1.2　設(shè)置邊框和不顯示網(wǎng)格線127
5.1.3　設(shè)置適合的行高與列寬128
5.1.4　設(shè)置字體和字號(hào).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)識(shí)符號(hào)133
5.2.5　根據(jù)條件設(shè)置數(shù)字的自定義格式.135
5.2.6　自定義數(shù)據(jù)標(biāo)簽的數(shù)字格式.135
5.3　利用條件格式自動(dòng)標(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ù)追蹤自動(dòng)化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)符號(hào)149
6.1.6.復(fù)制公式時(shí)合理設(shè)置相對(duì)引用和引用150
6.2.函數(shù)的基本規(guī)則151
6.2.1　什么是函數(shù).151
6.2.2　函數(shù)的基本語(yǔ)法.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ù)”對(duì)話框和名稱框快速創(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)前時(shí)間：NOW函數(shù)157
6.4.3.計(jì)算到期日： EDATE函數(shù)157
6.4.4.計(jì)算月底日期：EOMONTH函數(shù)157
6.4.5.計(jì)算兩個(gè)日期之間的期限：DATEDIF函數(shù)158
6.4.6.將年、月和日三個(gè)數(shù)字組合成日期：DATE函數(shù).159
6.4.7.計(jì)算星期幾：WEEKDAY函數(shù)159
6.4.8　實(shí)用公式：判斷指定日期所在的時(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ù)處理錯(cuò)誤值.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.超過(guò)15位數(shù)字長(zhǎng)編碼的條件計(jì)數(shù)與求和問(wèn)題.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ù)：自動(dòng)定位取數(shù)的列號(hào)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ù)制作動(dòng)態(tài)圖表185
6.9.4　財(cái)務(wù)報(bào)表動(dòng)態(tài)分析191
第7章.快速合并匯總大量工作表.193
7.1　合并結(jié)構(gòu)完全相同的工作表194
7.1.1　使用SUM函數(shù)快速合并，得到所有工作表的合計(jì)數(shù)194
7.1.2　使用合并計(jì)算工具快速合并，得到具有分級(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語(yǔ)句202
7.3　利用Power Query快速合并匯總.207
7.3.1　合并匯總一個(gè)工作簿中的多個(gè)工作表.207
7.3.2　合并匯總一個(gè)文件夾中的多個(gè)工作簿.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ù)：快速抓取每個(gè)表格的合計(jì)數(shù)230
7.5.3　INDIRECT函數(shù)：建立滾動(dòng)匯總分析表.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)前工作簿中的一個(gè)一維工作表創(chuàng)建數(shù)據(jù)透視表236
8.2.2　以當(dāng)前工作簿中的多個(gè)一維工作表創(chuàng)建數(shù)據(jù)透視表238
8.2.3　以當(dāng)前工作簿中的一個(gè)二維工作表創(chuàng)建數(shù)據(jù)透視表240
8.2.4　以當(dāng)前工作簿中的多個(gè)二維工作表創(chuàng)建數(shù)據(jù)透視表241
8.3.數(shù)據(jù)透視表的美化.242
8.3.1　設(shè)置數(shù)據(jù)透視表的樣式242
8.3.2　設(shè)置報(bào)表布局243
8.3.3　修改字段名稱244
8.3.4　顯示/取消分類匯總244
8.3.5　顯示/取消報(bào)表總計(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é)果的報(bào)告248
8.4.3　改變值字段的顯示方式，制作結(jié)構(gòu)分析報(bào)告249
8.4.4　組合日期，制作年報(bào)、季報(bào)、月報(bào)250
8.4.5.使用切片器快速篩選數(shù)據(jù)251
8.4.6　繪制數(shù)據(jù)透視圖，讓分析結(jié)果可視化.252
8.5　快速制作明細(xì)表253
8.5.1　每次制作一個(gè)指定項(xiàng)目的明細(xì)表.253
8.5.2　批量制作所有項(xiàng)目的明細(xì)表.254