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
