Excel效率寶典：財務管理高手之路

基本介紹

• 書名：Excel效率寶典：財務管理高手之路
• 作者：王彬, 吳榮發
• 出版社：清華大學出版社
• 出版時間：2017年9月1日

圖書簡介

《Excel效率寶典：財務管理高手之路》是2017年9月1日由清華大學出版社出版的一本圖書，作者王彬、吳榮發。

本書涵蓋了財務工作中的大部分難點，通過真實案例的形式展開Excel在財務管理、財務分析及財務模型中的運用，為讀者提供解決的思路和方案。同時，通過圖文並茂的拆分講述方式，將Excel的運算過程直觀地表現出來，使Excel的知識點通俗易懂，真正做到學以致用，提高讀者掌握Excel精髓的效率。在財務知識點內容的選擇上，捨棄了很多與Excel關係不大、不必要的內容，更加精益求精。本書內容豐富、注重實用，可操作性強，直達問題核心，既可作為財會從業人員的參考書，也適合從事金融投資、市場行銷、管理諮詢等工作的專業人士以及廣大Excel愛好者閱讀，還可作為學校、培訓機構的教學用書。

圖書目錄

第1章Excel基礎與技巧

1.1設定必填內容及類型1

1.1.1設定必填內容、先後順序1

1.1.2設定輸入類型必須為數字3

1.1.3使用公式防止數據漏入5

1.2自定義填充、自定義排序7

1.2.1設定自定義序列7

1.2.2根據自定義序列填充9

1.2.3根據自定義序列排序9

1.3自定義短語簡寫11

1.3.1調出Excel選項界面11

1.3.2設定自動更正12

1.3.3驗證自定義短語簡寫14

1.4數據格式快速批量轉換14

1.4.1使用分列批量轉換數據格式14

1.4.2選擇性貼上批量轉換為數字格式17

1.5查找重複&避免重複錄入18

1.5.1查找重複值18

1.5.2防止錄入重複數據20

1.6自動切換輸入法23

1.6.1Windws7自動切換輸入法23

1.6.2Windws10自動切換輸入法26

1.7設定下拉選單29

1.7.1設定一級下拉選單29

1.7.2設定二級下拉選單31

1.8定製Excel界面36

1.8.1增加自定義選項卡36

1.8.2移動選項卡位置38

1.8.3重命名選項卡38

1.8.4添加自定義命令40

1.8.5保存自定義設定42

1.8.6自定義快速訪問工具列43

1.8.7自定義狀態欄45

1.9Excel列印技巧48

1.9.1列印標題行或標題列48

1.9.2列印設定特定列印區域51

1.9.3分頁預覽設定特定列印區域52

1.9.4分頁預覽縮放列印大小55

1.9.5列印設定縮放列印大小57

1.9.6列印時不顯示零值58

1.9.7列印時不顯示錯誤值58

1.10Excel公式及函式基礎59

1.10.1函式類型59

1.10.2公式構成62

第2章應收賬款日常管理與賬齡分析

2.1建立應收賬款客戶資料66

2.2建立應收賬款台賬66

2.2.1使用VLKUP函式查找客戶名稱68

2.2.2使用IFERRR函式糾錯70

2.2.3SUMPRDUCT函式計算單號餘額71

2.2.4SUMPRDUCT多條件求和73

2.2.5使用VLKUP函式查找責任人75

2.3生成應收賬款明細76

2.3.1INDEX+MATCH+CUNTIF組合取唯一的銷售單號76

2.3.2INDEX+MATCH組合查找日期、客戶編號、客戶名稱79

2.3.3INDEX+MATCH多列用法81

2.3.3LKUP（1,0）求單號餘額82

2.3.4使用VLKUP函式查找收款期84

2.3.5IF函式確認是否超期86

2.4生成應收賬款匯總表87

2.4.1INDEX+MATCH+CUNTIF組合取唯一的客戶編號88

2.4.2VLKUP函式查找客戶名稱、收款期、責任人90

2.4.3SUMIF函式計算借方累計、貸方累計91

2.4.4計算應收餘額94

2.5應收賬款賬齡分析94

2.5.1賬齡分析要素94

2.5.2FFSET+CUNT+SUM組合計算Current賬齡94

2.5.3CUNT函式95

2.5.4FFSET函式96

2.5.5FFSET+CUNT組合99

2.5.6SUM函式同時滿足條件求和100

2.5.7SUM函式滿足任意一個條件求和103

2.5.8計算超過1個月的其餘賬齡104

2.5.9使用IF函式計算超期金額105

第3章內部往來日常管理

3.1根據內部往來資料核對入賬科目109

3.1.1內部往來的確認方法109

3.1.2內部往來的會計原理109

3.1.3使用IF公式確定入賬科目110

3.1.4使用VLKUP公式確定入賬科目111

3.2根據內部往來資料核對入賬金額112

3.2.1用數據透視表匯總科目金額112

3.2.2指定數據透視表的數據範圍113

3.2.3指定數據透視表存放位置113

3.2.4設定數據透視表布局114

3.2.5改變數據透視表欄位順序116

3.2.6數據透視表多個行標籤分成多列117

3.2.7數據透視表取消匯總118

3.2.8數據透視表報表篩選119

3.3FFSET+CUNTA動態更新數據透視表119

3.3.1CUNTA函式120

3.3.2FFSET+CUNTA組合120

3.3.3指定自定義名稱為數據透視表的數據範圍121

3.3.4驗證透視表是否能動態更新122

3.4同一賬套內，內部部門之間往來123

3.4.1整理內部往來原始數據124

3.4.2製作內部往來餘額表126

3.4.3製作內部往來差異表127

3.4.4內部往來有差異的數值自動標記顏色128

3.4.5內部往來有差異的部門自動標記顏色129

3.5不同賬套中，各關聯公司之間往來131

3.5.1借方顯示正數，貸方顯示負數131

3.5.2VLKUP模糊查找132

第4章銷售分析

4.1對比分析133

4.1.1創建柱狀圖表134

4.1.2給圖表添加標題135

4.1.3切換銷售對比圖表中行和列136

4.1.4改變圖表中的系列次序136

4.1.5改變圖表顏色及效果138

4.1.6多種類型圖表顯示在一張圖表中140

4.1.7圖表中設定數據表和圖例項144

4.2銷售趨勢分析145

4.2.1製作銷售折線圖146

4.2.2創建動態圖表數據源146

4.2.3更改圖表數據源148

4.2.4創建表單控制項148

4.2.5設定控制項149

4.2.6使用控制項實現圖表動態變化149

4.3銷售貢獻率149

4.3.1錄入業績貢獻數據並創建堆積柱形圖150

4.3.2設定圖表區域格式151

4.3.3設定繪圖區格式152

4.3.4設定坐標最大值和最小值153

4.3.5設定數據標籤及標題154

4.4銷售渠道來源155

4.4.1錄入銷售渠道數據並創建折線圖156

4.4.2銷售渠道分析157

4.5銷售成本利潤157

4.5.1錄入銷售成本利潤數據並創建柱形圖158

4.5.2圖表效果渲染，美化圖表158

4.5.3毛利率、銷量分析159

4.5.4製作毛利環形圖159

4.6銷售完成情況160

4.6.1錄入銷售完成差異數據並創建柱形圖161

4.6.2切換銷售完成情況圖表中的行、列162

4.6.3製作銷售完成比例雷達圖162

第5章固定資產管理

5.1建立固定資產台賬165

5.1.1固定資產分類規則165

5.1.2固定資產編碼規則166

5.1.3固定資產的其他欄位167

5.1.4將記錄單按鈕添加至功能區168

5.1.5使用記錄單錄入固定資產卡片171

5.1.6表格的下拉列表173

5.2基本的折舊公式174

5.2.1年限平均法175

5.2.2雙倍餘額遞減法176

5.2.3年數總和法178

5.3固定資產台賬標準和常用日期函式179

5.3.1固定資產台賬基礎標準180

5.3.2使用年限和殘值率180

5.3.3確定折舊方法180

5.3.4計算折舊常用的日期函式181

5.3.5為函式自定義簡化的名稱182

5.4生成固定資產折舊183

5.4.1計算累計計提月份183

5.4.2平均年限法計算本月折舊185

5.4.3平均年限法計算累計折舊186

5.4.4計算本年計提月份186

5.4.5平均年限法計算本年累計折舊186

5.4.6雙倍餘額遞減法計算月折舊187

5.4.7年數總和法計算月折舊188

5.5固定資產分析189

5.5.1使用數據透視表分析固定資產189

5.5.2多維度固定資產分析191

第6章進銷存管理

6.1建立進銷存台賬195

6.2基本的成本計算方法195

6.2.1移動加權平均法195

6.2.2先進先出法196

6.2.3後進先出法196

6.2.4個別計價法196

6.3生成進銷存報表197

6.3.1設定加權平均法公式197

6.3.2設定先進先出法公式197

6.3.3自定義名稱198

6.3.4RW函式201

6.3.5先進先出法公式思路202

6.3.6SUMPRDUCT函式實現先進先出205

6.3.7設定個別計價法公式208

6.4進銷存分析209

6.4.1使用數據透視表分析進銷存209

6.4.2多維度進銷存分析211

6.4.3最佳訂貨量212

第7章快速製作現金流量表

7.1現金流量表項目介紹216

7.1.1經營活動產生的現金流量216

7.1.2投資活動產生的現金流量217

7.1.3籌資活動產生的現金流量218

7.1.4匯率變動對現金的影響219

7.1.5編制現金流量表的常用方法219

7.2會計科目明細分錄法219

7.2.1規範會計分錄舉例219

7.2.2導出併合並現金、銀行日記賬220

7.2.3創建合併日記賬數據透視表221

7.2.4設定合併日記賬數據透視表布局223

7.2.5設定數據透視表欄位計算類型224

7.2.6現金流項目模糊求和225

7.2.7展開欄位明細並按現金流項目分類228

7.2.8按現金流項目分類229

7.2.9匯總明細金額返回至科目匯總表231

7.2.10根據現金流性質調整明細分類234

7.2.11調整後現金流借方、貸方發生額236

7.2.12編制現金流量表236

7.3工作底稿法241

7.3.1編制資產負債表項目工作底稿244

7.3.2編制利潤項目工作底稿245

7.3.3編制現金流量表項目工作底稿245

7.3.4編制調整分錄246

7.3.5調整分錄過入工作底稿251

7.3.6核對調整分錄253

7.3.7計算現金流量表項目發生額255

7.3.8編制現金流量表258

第8章財務模型、數據分析案例

8.1多表格之間數據分析261

8.1.1調出多重透視表按鈕262

8.1.2多表格數據透視表263

8.2單日最大銷售額267

8.2.1日期函式268

8.2.2自定義單元格後綴269

8.2.3自定義單元格前綴270

8.2.4自定義條件的單元格格式270

8.2.4高級篩選求不重複值272

8.2.5橫排豎排轉置273

8.2.6MAX+IF組合274

8.3銷售自動排名277

8.3.1RANK函式排名277

8.3.2SUMPRDUCT函式排名278

8.4體現排名變化情況的排行榜279

8.4.1設計銷售排行榜框架282

8.4.2銷售數據及排名填入明細表282

8.4.3查找銷售及排名283

8.4.4排名變化情況285

8.4.5自定義單元格顯示箭頭288

8.4.6名次上升店鋪顯示紅色底紋289

8.4.7名次下降店鋪顯示綠色底紋291

8.4.8名次未變店鋪顯示藍色底紋291

8.4.9新上榜店鋪顯示棕色底紋292

8.4.10備註底紋顏色代表的含義293

8.4.11計算同比銷售增量及增速293

8.5大海撈針，找到我要的那些數據296

8.5.1INDEX+MATCH和VLKUP的區別297

8.5.2多條件查找CUNTIFS299

8.6盈虧平衡試算300

8.6.1綜合扣點、綜合毛利率302

8.6.2盈虧平衡點303

8.6.3測算結果306

8.7利潤最大化求解307

8.7.1利潤測算表308

8.7.2將規劃求解按鈕添加至功能區309

8.7.3設定規劃求解參數單元格311

8.7.4設定規劃求解約束條件311

8.7.5設定誤差率並求解313

8.7.6規劃求解結果及報告314

8.7.7利潤最大化的最佳產量315

8.8利潤敏感性分析317

8.8.1價格單因素敏感性分析319

8.8.2產量單因素敏感性分析323

8.8.3變動、固定成本單因素敏感性分析325

8.8.4銷售價格、銷售量雙因素敏感性分析328

8.8.5絕對值法求臨界點332

8.8.6多因素多方案分析333

8.9製作杜邦分析表337

8.9.1杜邦分析法概述337

8.9.2創建杜邦分析體系338

8.9.3SUBTTAL函式341

8.9.4取消表格中的格線線342

8.10沃爾比重分析343

8.10.1沃爾比重分析概述343

8.10.2創建沃爾比重分析表345

第9章投資分析案例

9.1貸款計算及分析349

9.1.1等額本息法349

9.1.2PMT函式350

9.1.3等額本金法351

9.1.4等額利息法353

9.1.5到期還本付息法355

9.1.6全部提前還貸359

9.1.7部分提前還貸361

9.2日期自動遞增364

9.2.1INT函式367

9.2.2MD函式367

9.2.3CNCATENATE函式367

9.2.4DATE、DATEIF函式368

9.2.5遞增月份公式解析371

9.3數據分析——趨勢預測、線性描述372

9.3.1Excel趨勢線373

9.3.2線性回歸函式375

9.4股票估值-自由現金流量模型（內在價值法）376

9.4.1股權自由現金流377

9.4.2折現率377

9.4.3美X公司案例378

9.4.4NPV函式382

9.5股票估值-相對價值法383

9.5.1市盈率383

9.5.2市淨率383

9.5.3平安銀行案例384

9.5.4RUND系列函式385

9.5.5AVERAGE函式387

9.5.6相對價值法的局限性387

9.6β係數388

9.6.1年收益率388

9.6.2樣本標準差389

9.6.3相關係數390

9.6.4計算β值391

9.6.4協方差、方差計算β值392

9.7資本資產定價模型396

9.7.1無風險收益率396

9.7.2市場平均收益率397

9.7.3計算期望報酬率399

9.8項目投資分析399

9.8.1淨現值法399

9.8.2淨現值率、獲利指數401

9.8.3插值法計算內部收益率403

9.8.4IRR函式計算內部收益率404

9.8.5靜態投資回收期406

9.8.6動態投資回收期408

第10章財務預算編制

10.1建立預算部門資料413

10.2建立預算科目資料414

10.3建立預算科目明細表414

10.4建立部門預算匯總表416

10.4.1編制部門設定下拉選單416

10.4.2設定提示信息419

10.4.3預算金額420

10.4.4填寫報表設定連結421

10.4.5IF+R組合指定可填寫部門423

10.4.6返回部門預算匯總表連結425

10.4.7保護工作表和保護工作簿429

10.4.8取消顯示工作表標籤433

10.5建立公司預算匯總表435

10.5.1建立月份總預算資料夾436

10.5.2編輯表間取數公式436

10.6建立公司實際使用表438

10.6.1建立預算實際使用明細表439

10.6.2建立預算實際使用匯總表439

10.7預算分析441