從小白到高手：Excel財務(wù)達(dá)人精進(jìn)之路

CHAPTER 01 你真會用Excel 嗎 1

1.1　十年茫茫不思量，Excel 讓我癲又狂.................................................................. 1

1.1.1 一張爛表，讓我痛不欲生.....................................................................................1

1.1.2 沒有技能，讓我事倍功半.....................................................................................3

1.1.3 沒有思路，讓我前功盡棄.....................................................................................5

1.1.4 沒說服力，讓我喪失很多機會............................................................................. 5

1.2　痛定思痛：從今天重新開始 ...............................................................................8

CHAPTER 02 打好基礎(chǔ)，從設(shè)計表格開始 9

2.1　正確區(qū)分Excel 兩個表格...................................................................................9

2.1.1 基礎(chǔ)表單..............................................................................................................9

2.1.1 分析報告............................................................................................................10

2.1.3 關(guān)于分析底稿.....................................................................................................10

2.1.4 基礎(chǔ)表單的設(shè)計原則...........................................................................................11

2.2　亂用Excel 十三大罪........................................................................................12

2.2.1 設(shè)計的表單無邏輯.............................................................................................12

2.2.2 大而全的表格.....................................................................................................13

2.2.3 用二維表格管理數(shù)據(jù)..........................................................................................15

2.2.4 行標(biāo)題和列標(biāo)題使用合并單元格........................................................................ 15

2.2.5 表單中插入小計行.............................................................................................16

2.2.6 表單中插入大量的空行空列...............................................................................16

2.2.7 在表單數(shù)據(jù)區(qū)域外輸入其他無關(guān)的數(shù)據(jù)............................................................. 16

2.2.8 不同類型的數(shù)據(jù)保存在一列...............................................................................17

2.2.9 數(shù)字和單位寫在一起..........................................................................................17

2.2.10 名稱文字中強制添加空格.................................................................................17

2.2.11 輸入非法日期...................................................................................................17

2.2.12 不區(qū)分文本型數(shù)字和純數(shù)字.............................................................................18

2.2.13 在基礎(chǔ)表單添加不必要的計算列...................................................................... 18

2.3　設(shè)計科學(xué)規(guī)范財務(wù)表單實戰(zhàn)練習(xí)：資金管理.................................................... 18

2.3.1 繪制邏輯架構(gòu)圖.................................................................................................19

2.3.2 設(shè)計銀行賬號資料表..........................................................................................19

2.3.3 設(shè)計項目資料管理表..........................................................................................20

2.3.4 設(shè)計期初余額表.................................................................................................20

2.3.5 設(shè)計資金管理臺賬.............................................................................................20

2.4　集團(tuán)下屬子公司表單的格式統(tǒng)一化.................................................................. 22

2.4.1 結(jié)構(gòu)完全固化的表單模板...................................................................................23

2.4.2 一維數(shù)據(jù)表單模板.............................................................................................23

2.4.3 建立共享工作簿.................................................................................................24

CHAPTER 03 談笑間，整理好財務(wù)表單 25

3.1　清理數(shù)據(jù)中的垃圾............................................................................................25

3.1.1 清除數(shù)據(jù)中的空格.............................................................................................25

3.1.2 清除數(shù)據(jù)中的回車符..........................................................................................25

3.1.3 清除眼睛看不見的特殊字符...............................................................................26

3.1.4 清除數(shù)據(jù)中的星號.............................................................................................27

3.1.5 清除表格中的圖形對象......................................................................................28

3.2　修改非法日期...................................................................................................28

3.2.1 使用分列工具快速修改非法日期........................................................................ 29

3.2.2 使用函數(shù)處理特殊格式的非法日期.................................................................... 30

3.2.3 輸入日期的幾個小技巧......................................................................................31

3.3.4 設(shè)置日期格式技巧.............................................................................................31

3.2.5 過去、現(xiàn)在和將來：常用日期函數(shù)及其應(yīng)用...................................................... 33

3.3　轉(zhuǎn)換數(shù)字格式...................................................................................................36

3.3.1 將數(shù)字轉(zhuǎn)換為原位數(shù)的文本型數(shù)字.................................................................... 37

3.3.2 將數(shù)字轉(zhuǎn)換為固定位數(shù)的文本型數(shù)字，位數(shù)不夠就補0 .................................... 37

3.3.3 將文本型數(shù)字轉(zhuǎn)換為純數(shù)字的5 大技法............................................................. 37

3.3.4 容易混淆的概念：單元格和數(shù)據(jù)........................................................................ 39

3.4　快速填充空單元格............................................................................................39

3.4.1 快速填充上一行數(shù)據(jù)..........................................................................................39

3.4.2 快速填充下一行數(shù)據(jù)..........................................................................................41

3.4.3 快速往空單元格填充數(shù)字0................................................................................41

3.4.4 快速取消合并單元格并填充數(shù)據(jù)........................................................................ 42

3.5　批量修改數(shù)據(jù) ...................................................................................................43

3.5.1 批量修改全部單元格數(shù)據(jù)...................................................................................43

3.5.2 批量修改部分單元格數(shù)據(jù)...................................................................................43

3.5.3 復(fù)雜條件下的批量修改數(shù)據(jù)...............................................................................44

3.6　數(shù)據(jù)重新排列...................................................................................................44

3.6.1 將二維表格轉(zhuǎn)換為一維表格：數(shù)據(jù)透視表方法.................................................. 44

3.6.2 將二維表格轉(zhuǎn)換為一維表格：PowerQuery 方法 ............................................... 46

3.6.3 行列互轉(zhuǎn)............................................................................................................48

3.6.4 將多列名單重新排列成一列...............................................................................48

3.6.5 依數(shù)據(jù)個數(shù)重新排列成一列...............................................................................48

3.7　數(shù)據(jù)快速分列...................................................................................................49

3.7.1 分列科目編碼和科目名稱：根據(jù)特殊符號......................................................... 49

3.7.2 分列科目編碼和科目名稱：根據(jù)常規(guī)符號......................................................... 50

3.7.3 分列科目編碼和中文科目名稱：利用函數(shù)......................................................... 51

3.7.4 分列科目編碼和英文科目名稱：利用函數(shù)......................................................... 52

3.7.5 提取內(nèi)部支票號：利用函數(shù)...............................................................................52

3.7.6 分列規(guī)格、數(shù)量和單位：利用函數(shù).................................................................... 53

3.7.7 含有換行符的數(shù)據(jù)分列......................................................................................55

3.7.8 指紋考勤數(shù)據(jù)分列.............................................................................................55

3.7.9 文本文件的導(dǎo)入與分列......................................................................................55

3.8　數(shù)據(jù)快速合并...................................................................................................57

3.8.1 使用簡單的連接符（&）合并數(shù)據(jù)..................................................................... 57

3.8.2 使用CONCATENATE 函數(shù)合并數(shù)據(jù)................................................................. 57

3.8.3 使用CONCAT 函數(shù)合并數(shù)據(jù).............................................................................58

3.8.4 使用TEXTJOIN 函數(shù)合并數(shù)據(jù).......................................................................... 58

3.8.5 使用快速填充工具合并數(shù)據(jù)...............................................................................58

3.9　處理表格中的垃圾行........................................................................................58

3.9.1 快速刪除表單中的空行......................................................................................59

3.9.2 快速刪除小計行.................................................................................................59

3.10　處理重復(fù)數(shù)據(jù).................................................................................................59

3.10.1 在一列中查找重復(fù)數(shù)據(jù)....................................................................................60

3.10.2 在一列中跟蹤計算每個數(shù)的重復(fù)次數(shù)...............................................................60

3.10.3 從多列數(shù)據(jù)中查找重復(fù)行數(shù)據(jù)......................................................................... 61

3.10.4 刪除數(shù)據(jù)清單中的重復(fù)數(shù)據(jù).............................................................................62

3.10.5 處理重復(fù)的考勤刷卡數(shù)據(jù).................................................................................62

3.10.6 從一列數(shù)據(jù)中獲取不重復(fù)數(shù)據(jù)列表.................................................................. 63

3.10.7 從多個表格中獲取不重復(fù)數(shù)據(jù)列表.................................................................. 63

3.10.8 從兩個表格中查找都存在的數(shù)據(jù)...................................................................... 67

3.10.9 只查找某個表格存在的數(shù)據(jù).............................................................................67

CHAPTER 04 只需要一分鐘，輕松搞定數(shù)據(jù)核對 68

4.1　在一個工作表中查找非法數(shù)據(jù).......................................................................... 68

4.1.1 使用數(shù)據(jù)驗證圈釋無效數(shù)據(jù)...............................................................................68

4.1.2 使用條件格式標(biāo)識非法數(shù)據(jù)...............................................................................69

4.2　從兩個表格中查找不一樣的數(shù)據(jù)...................................................................... 69

4.2.1 單條件核對：使用函數(shù)......................................................................................70

4.2.2 單條件核對：使用透視表...................................................................................70

4.2.3 多條件核對：使用函數(shù)......................................................................................72

4.2.4 多條件核對：使用SQL 透視表....................................................................... 73

4.2.5 多條件核對：使用PowerQuery Power Pivot .................................................. 77

4.3　核對數(shù)據(jù)實際應(yīng)用案例精選.............................................................................80

4.3.1 實際案例1：核對社保.......................................................................................80

4.3.2 實際案例2：核對公積金和繳費基數(shù)................................................................. 82

4.3.3 實際案例3：核對個稅.......................................................................................82

4.3.4 實際案例4：核對增值稅...................................................................................83

4.3.5 實際案例5：核對內(nèi)部往來賬............................................................................ 84

4.3.6 實際案例6：銀行對賬.......................................................................................85

4.3.7 實際案例7：稅金核銷.......................................................................................88

4.3.8 核對總公司與分公司開票和未開票.................................................................... 90

4.3.9 核對應(yīng)付余額.....................................................................................................93

4.3.10 核對網(wǎng)銀日期和財務(wù)日期.................................................................................94

4.3.11 核對總表和明細(xì)表............................................................................................95

4.3.12 核對收入和發(fā)票 ...............................................................................................96

4.3.13 核對產(chǎn)量和工時...............................................................................................97

4.3.14 核對物料數(shù)量...................................................................................................99

4.3.15 核對Excel 憑證設(shè)置與系統(tǒng)憑證設(shè)置............................................................... 99

4.3.16 核對報關(guān)資料.................................................................................................100

4.3.17 核對發(fā)貨和收貨.............................................................................................102

4.4　核對數(shù)據(jù)總結(jié).................................................................................................102

4.4.1 核對數(shù)據(jù)的核心及主要技能.............................................................................103

4.4.2 使用條件格式自動標(biāo)注不一樣的數(shù)據(jù)...............................................................103

CHAPTER 05 練好基本技能，高效處理表單數(shù)據(jù) 104

5.1　篩選，把滿足條件的數(shù)據(jù)快速找出來............................................................. 104

5.1.1 篩選的本質(zhì)......................................................................................................104

5.1.2 篩選不重復(fù)記錄...............................................................................................105

5.1.3 刪除重復(fù)記錄...................................................................................................105

5.1.4 將篩選結(jié)果復(fù)制到其他工作表......................................................................... 105

5.1.5 指定任意復(fù)雜條件下的高級篩選...................................................................... 105

5.1.6 對數(shù)字?jǐn)?shù)據(jù)進(jìn)行特殊篩選.................................................................................107

5.1.7 對日期數(shù)據(jù)進(jìn)行特殊篩選.................................................................................107

5.1.8 對文本數(shù)據(jù)進(jìn)行特殊篩選.................................................................................107

5.1.9 按顏色進(jìn)行篩選...............................................................................................108

5.1.10 只修改篩選出來的數(shù)據(jù)..................................................................................108

5.1.11 只修改篩選出來單元格的公式........................................................................108

5.1.12 在篩選狀態(tài)下自動顯示連續(xù)序號.................................................................... 109

5.2　排序很簡單，但也很奇妙................................................................................110

5.2.1 排序的規(guī)則.......................................................................................................110

5.2.2 排序的正確方法................................................................................................110

5.2.3 按照筆劃排序....................................................................................................111

5.2.4 對多個關(guān)鍵字排序 ............................................................................................111

5.2.5 自定義排序.......................................................................................................111

5.2.6 根據(jù)單元格顏色進(jìn)行排序..................................................................................113

5.2.7 利用排序函數(shù)進(jìn)行排序.....................................................................................113

5.2.8 先排序再恢復(fù)原始狀態(tài).....................................................................................114

5.3　創(chuàng)建分類匯總和分級顯示，讓大型表格結(jié)構(gòu)更加清晰 .....................................115

5.3.1 對分類匯總工具的看法.....................................................................................115

5.3.2 自動創(chuàng)建地區(qū)和月份的分級顯示.......................................................................115

5.3.3 手動組合創(chuàng)建總賬科目和明細(xì)科目的分級顯示.................................................116

5.3.4 復(fù)制分類匯總和分級顯示數(shù)據(jù)..........................................................................117

5.3.5 取消分類匯總和分級顯示..................................................................................118

5.4　很多人忽略的智能表格：快速管理表格和分析數(shù)據(jù) ........................................118

5.4.1 創(chuàng)建智能表格....................................................................................................118

5.4.2 智能表格的格式化............................................................................................119

5.4.3 手工調(diào)整表格大小............................................................................................119

5.4.4 為表格添加列、插入行....................................................................................120

5.4.5 刪除表格的列或行...........................................................................................121

5.4.6 為表格添加匯總行 ...........................................................................................121

5.4.7 使用切片器快速篩選數(shù)據(jù).................................................................................122

5.4.8 使用快速分析工具箱來分析表格...................................................................... 123

5.4.9 將表格作為數(shù)據(jù)模型使用.................................................................................123

5.5　把錯誤消滅在萌芽：利用數(shù)據(jù)驗證控制規(guī)范數(shù)據(jù)輸入 .................................... 124

5.5.1 數(shù)據(jù)驗證的設(shè)置方法........................................................................................124

5.5.2 控制規(guī)范日期輸入...........................................................................................125

5.5.3 控制規(guī)范輸入數(shù)字...........................................................................................126

5.5.4 只能輸入不超過兩位小數(shù)的數(shù)字...................................................................... 126

5.5.5 在單元格設(shè)計下拉菜單，快速選擇輸入數(shù)據(jù).................................................... 126

5.5.6 在單元格設(shè)計二級下拉菜單，快速選擇指定項目下的數(shù)據(jù) .............................. 128

5.5.7 在一列里限制不能輸入重復(fù)數(shù)據(jù)...................................................................... 129

5.5.8 在多列里不允許輸入重復(fù)數(shù)據(jù)......................................................................... 130

5.5.9 不能輸入另外一個工作表已有的數(shù)據(jù)...............................................................131

5.5.10 只有在上一行數(shù)據(jù)全部輸入后，才能輸入下一行數(shù)據(jù) ................................... 132

5.6　熟練運用分列工具，讓數(shù)據(jù)整理不再消耗大好時光 ....................................... 132

5.6.1 根據(jù)一個符號分列...........................................................................................133

5.6.2 根據(jù)多個符號分列...........................................................................................134

5.6.3 根據(jù)指定的特殊符號分列.................................................................................134

5.6.4 根據(jù)固定寬度對數(shù)據(jù)進(jìn)行分列：手工插入分列線.............................................135

5.6.5 根據(jù)固定寬度對數(shù)據(jù)進(jìn)行分列：自動插入分列線.............................................136

5.6.6 把一個單元格的科目名稱和部門名稱分成兩列................................................ 137

5.6.7 把一列發(fā)生額分列成借方和貸方...................................................................... 139

5.6.8 利用分列工具轉(zhuǎn)換列數(shù)據(jù)格式......................................................................... 139

5.7　數(shù)據(jù)查詢中的汽車：MicrosoftQuery ............................................................. 140

5.7.1 從當(dāng)前工作簿中查詢數(shù)據(jù).................................................................................140

5.7.2 從其他工作簿中查詢數(shù)據(jù).................................................................................143

5.7.3 從文本文件中查詢數(shù)據(jù)....................................................................................144

5.7.4 從數(shù)據(jù)庫中查詢數(shù)據(jù)........................................................................................146

5.7.5 查找兩個表都存在的數(shù)據(jù).................................................................................146

5.8　數(shù)據(jù)查詢中的高鐵：Power Query.................................................................. 148

5.8.1 Power Query 在哪里.......................................................................................148

5.8.2 從當(dāng)前工作簿的一個工作表中查詢數(shù)據(jù)........................................................... 148

5.8.3 從當(dāng)前工作簿的多個工作表中查詢數(shù)據(jù)........................................................... 151

5.8.4 從其他一個工作簿中查詢數(shù)據(jù)......................................................................... 153

5.8.5 從其他多個工作簿中查詢數(shù)據(jù)：每個工作簿只有一個工作表........................... 154

5.8.6 從其他多個工作簿中查詢數(shù)據(jù)：每個工作簿有多個工作表 .............................. 160

5.8.7 從兩個表格中查找都存在的數(shù)據(jù)...................................................................... 162

5.8.8 只查找存在于某個表的數(shù)據(jù).............................................................................166

5.8.9 從文本文件中查詢數(shù)據(jù)....................................................................................167

5.8.10 從一個工作表中查詢并完成匯總計算.............................................................168

5.8.11 從多個工作表中查詢并完成匯總計算............................................................. 172

5.8.12 從多個工作簿中查詢并完成匯總計算.............................................................172

5.8.13 綜合練習(xí)：兩年客戶流動分析....................................................................... 174

CHAPTER 06 不會函數(shù)公式，怎么做財務(wù)管理和財務(wù)分析183

6.1　必須了解和掌握的公式基礎(chǔ)知識.................................................................... 184

6.1.1 什么是公式......................................................................................................184

6.1.2 公式的元素......................................................................................................184

6.1.3 公式的運算符...................................................................................................185

6.1.4 公式運算符的優(yōu)先順序....................................................................................185

6.1.5 單元格的引用：相對引用與絕對引用...............................................................186

6.1.6 公式返回的錯誤信息........................................................................................188

6.1.7 復(fù)制和移動公式...............................................................................................188

6.1.8 用F9 鍵查看公式的計算結(jié)果...........................................................................190

6.1.9 用公式求值來檢查公式....................................................................................190

6.1.10 將公式轉(zhuǎn)換為值.............................................................................................190

6.1.11 隱藏、顯示和保護(hù)公式..................................................................................190

6.1.12 將公式分行輸入，以便使公式更加容易理解和查看 ....................................... 192

6.1.13 在公式表達(dá)式中插入空格，編輯起來更加方便.............................................. 192

6.1.14 公式和函數(shù)中的字母不區(qū)分大小寫................................................................ 192

6.1.15 公式和函數(shù)中的標(biāo)點符號都必須是半角字符.................................................. 192

6.2　必須了解和掌握的函數(shù)基礎(chǔ)知識.................................................................... 193

6.2.1 什么是函數(shù)......................................................................................................193

6.2.2 函數(shù)的基本語法...............................................................................................193

6.2.3 函數(shù)參數(shù)的類型...............................................................................................193

6.2.4 養(yǎng)成輸入函數(shù)的好習(xí)慣....................................................................................194

6.2.5 函數(shù)的種類......................................................................................................195

6.2.6 在單元格快速輸入函數(shù)的技巧......................................................................... 196

....... 196

6.3.1 邏輯思路，是函數(shù)公式的核心，也是Excel的核心 ......................................... 196

6.3.2 學(xué)會繪制邏輯思路圖........................................................................................197

6.3.3 輸入嵌套函數(shù)技能之一：分解綜合法...............................................................199

6.3.4 輸入嵌套函數(shù)技能之二：函數(shù)對話框 名稱框法，單流程嵌套IF 公式 ..........201

6.3.5 輸入嵌套函數(shù)技能之三：函數(shù)對話框 名稱框法，多流程嵌套IF 公式 ..........202

6.3.6 輸入嵌套函數(shù)技能之四：函數(shù)對話框 名稱框法，不同函數(shù)的嵌套公式 ........ 205

6.4　使用名稱提高公式效率，解決復(fù)雜的計算問題.............................................. 206

6.4.1 什么是名稱......................................................................................................206

6.4.2 定義名稱的規(guī)則...............................................................................................207

6.4.3 定義名稱的四種方法 ........................................................................................207

6.4.4 編輯、修改和刪除名稱....................................................................................208

6.4.5 應(yīng)用案例1：在單元格制作二級下拉菜單........................................................ 208

6.4.6 應(yīng)用案例2：制作動態(tài)數(shù)據(jù)源的數(shù)據(jù)透視表 .................................................... 208

6.4.7 應(yīng)