6Excel在財(cái)務(wù)與管理中的高效運(yùn)用PPT課件(35頁(yè)P(yáng)PT)

1、Excel在財(cái)務(wù)與管理中的高效運(yùn)用韓小良第1頁(yè)，共35頁(yè)。韓小良 老師 簡(jiǎn)介韓小良，資深實(shí)戰(zhàn)型Excel培訓(xùn)講師、應(yīng)用解決方案專家和管理系統(tǒng)軟件開發(fā)設(shè)計(jì)師，對(duì)Excel及Excel VBA在企業(yè)管理中的應(yīng)用有著較深的研究和獨(dú)特的認(rèn)識(shí)，對(duì)Excel及VBA在管理中高級(jí)應(yīng)用培訓(xùn)有著豐富的實(shí)戰(zhàn)經(jīng)驗(yàn)，尤其是Excel及VBA在企業(yè)財(cái)務(wù)、會(huì)計(jì)、銷售、人力資源、金融財(cái)務(wù)等管理中的應(yīng)用。韓老師具有豐富的實(shí)際管理經(jīng)驗(yàn)和極強(qiáng)的Excel應(yīng)用開發(fā)能力。從事Excel研究及應(yīng)用多年，已經(jīng)為數(shù)百家中外企業(yè)進(jìn)行了Excel培訓(xùn)和應(yīng)用方案解決咨詢服務(wù)，出版了近30余部關(guān)于Excel應(yīng)用方面的著作，并陸續(xù)開發(fā)出了財(cái)務(wù)、銷售

2、、人力資源、工資等管理、客戶管理、進(jìn)銷存等基于Excel的管理軟件，成功應(yīng)用于數(shù)十家企業(yè)。主要服務(wù)客戶包括：中國(guó)銀行、中國(guó)民生銀行、浦發(fā)銀行南京分行、上海農(nóng)業(yè)商業(yè)銀行、深圳聯(lián)合證券、百度總部、 Google(中國(guó))總部、上海醫(yī)藥集團(tuán)、上海萬(wàn)家基金、上海電信、上海太平洋資產(chǎn)管理公司、索尼愛(ài)立信、中儲(chǔ)股份、北京國(guó)華能源投資、浙江中煙集團(tuán)、天津索恩照明、天津Vestas風(fēng)力技術(shù)、卓倫機(jī)械(天津)、北京奧瑞金種業(yè)、蘇州瀚得汽車產(chǎn)品、蘇州珈農(nóng)生化(丁家宜)、蘇州賽發(fā)過(guò)濾科技、強(qiáng)生醫(yī)療器械（蘇州）、上海海斯特叉車、蘇州GIF研發(fā)中心、無(wú)錫華潤(rùn)上華科技、中化(寧波)集團(tuán)、偉?？萍脊I(yè)(武漢)、貴州銀燕集團(tuán)、

3、江蘇交通科學(xué)研究院、南瑞集團(tuán)、南京勁草時(shí)裝、南京華德火花塞、上海麥考林國(guó)際郵購(gòu)、常州瓦盧瑞克鋼管、廣東歐普照明、廣東移動(dòng)、上海晶澳太陽(yáng)能光伏、上海博萊科信誼藥業(yè)、上海SKF、嘉興阿克蘇諾貝爾涂料、揚(yáng)州亞普汽車部件、江蘇德邦化工集團(tuán)、南京朗詩(shī)集團(tuán)、金螳螂、鄭州煤機(jī)股份、上海三菱電梯、豪森藥業(yè)、輝瑞制藥、揚(yáng)州太平洋重工、南益地產(chǎn)集團(tuán)、圣戈班、等等數(shù)百家中外企業(yè)。韓小良第2頁(yè)，共35頁(yè)。韓小良老師的主要著作1.用圖表說(shuō)話：Excel實(shí)用精美圖表大制作，電子工業(yè)出版社2.Excel數(shù)據(jù)透視表從入門到精通，中國(guó)鐵道出版社3.Excel高效辦公應(yīng)用大全：方法、技巧及實(shí)際案例，中國(guó)鐵道出版社4.Excel

4、2003/2007函數(shù)和公式的使用藝術(shù)，中國(guó)鐵道出版社5.Excel VBA (2003/2007)高效辦公實(shí)用寶典，中國(guó)鐵道出版社6.Excel VBA( 2003/2007)活用范例大辭典，中國(guó)鐵道出版社7.運(yùn)用Excel VBA進(jìn)行高效投資決策，中國(guó)鐵道出版社8.Excel VBA從入門到精通，中國(guó)鐵道出版社9.Excel VBA實(shí)用技巧大全，中國(guó)鐵道出版社10.Excel VBA與數(shù)據(jù)庫(kù)整合應(yīng)用范例精解，科學(xué)出版社11.Excel VBA銷售管理系統(tǒng)開發(fā)入門與實(shí)踐，科學(xué)出版社12.Excel VBA整合數(shù)據(jù)庫(kù)應(yīng)用從基礎(chǔ)到實(shí)踐，電子工業(yè)出版社13.Excel在銷售與財(cái)務(wù)管理中的應(yīng)用，電子

5、工業(yè)出版社14. Excel企業(yè)管理應(yīng)用案例精萃，電子工業(yè)出版社15.Excel VBA應(yīng)用開發(fā)(技巧精華版)，電子工業(yè)出版社韓小良第3頁(yè)，共35頁(yè)。韓小良老師的主要著作(續(xù))16.Excel +SQL Server數(shù)據(jù)庫(kù)管理技術(shù)詳解，機(jī)械工業(yè)出版社17.Excel在納稅管理與籌劃中的應(yīng)用，科學(xué)出版社18.Excel會(huì)計(jì)應(yīng)用范例精解，電子工業(yè)出版社19.Excel VBA +Access財(cái)務(wù)系統(tǒng)開發(fā)從基礎(chǔ)到實(shí)踐，電子工業(yè)出版社20.Excel在投資理財(cái)中的應(yīng)用，電子工業(yè)出版社21.Excel VBA工資管理應(yīng)用案例詳解，中國(guó)鐵道出版社22.Excel在財(cái)務(wù)管理與分析中的應(yīng)用，中國(guó)水利水電道出版

6、社23.運(yùn)用Excel VBA創(chuàng)建高效財(cái)務(wù)管理模型，中國(guó)鐵道出版社24.Excel VBA行政與人力資源管理應(yīng)用案例詳解，中國(guó)鐵道出版社25.Excel VBA財(cái)務(wù)管理應(yīng)用案例詳解，中國(guó)鐵道出版社26.Excel 2003/2007數(shù)據(jù)透視表 從入門到精通 ，中國(guó)鐵道出版社27.Excel 高效管理表單設(shè)計(jì)從入門到精通，中國(guó)鐵道出版社28.Excel 人力資源高效管理從入門到精通，中國(guó)鐵道出版社29.Excel 數(shù)據(jù)分析之道：讓分析報(bào)告更有說(shuō)服力，中國(guó)鐵道出版社韓小良第4頁(yè)，共35頁(yè)。韓小良老師的經(jīng)典課程Excel高效財(cái)務(wù)管理：方法、技巧和實(shí)用模型Excel高效人力資源管理：方法、技巧和實(shí)用模

7、型Excel高效管理應(yīng)用：方法、技巧和實(shí)用模型讓你的分析報(bào)告更有說(shuō)服力Excel高效數(shù)據(jù)處理與分析Excel及Excel VBA高級(jí)金融建模Excel VBA快速入門與實(shí)戰(zhàn)演練Excel與數(shù)據(jù)庫(kù)整合應(yīng)用：方法、技巧和實(shí)際案例韓小良第5頁(yè)，共35頁(yè)。課程目錄實(shí)用高效的數(shù)據(jù)整理技巧常用函數(shù)的高效運(yùn)用快速匯總多個(gè)工作簿和工作表數(shù)據(jù)數(shù)據(jù)透視表工具的靈活應(yīng)用其他應(yīng)用韓小良第6頁(yè)，共35頁(yè)。實(shí)用高效的數(shù)據(jù)整理技巧快速修改非法日期快速轉(zhuǎn)換文本型數(shù)字快速刪除數(shù)據(jù)中的特殊字符快速分列數(shù)據(jù)快速填充數(shù)據(jù) 韓小良第7頁(yè)，共35頁(yè)?？焖傩薷姆欠ㄈ掌谌掌谑翘厥獾臄?shù)字，是從1開始的序列號(hào)。日期永遠(yuǎn)是大于零的正整數(shù)。正確輸入

8、日期正確輸入日期：2010-11-17，或者 2010/11/17錯(cuò)誤的輸入日期方式：2010.11.17，或者 20101118很多情況下，從系統(tǒng)導(dǎo)入的日期數(shù)據(jù)是文本，并不是真正的日期，必須進(jìn)行修改（案例01）韓小良第8頁(yè)，共35頁(yè)?？焖俎D(zhuǎn)換文本型數(shù)字在有些情況下，從數(shù)據(jù)庫(kù)導(dǎo)入的數(shù)字是文本型數(shù)字。文本型數(shù)字無(wú)法使用SUM等函數(shù)進(jìn)行計(jì)算，也無(wú)法利用數(shù)據(jù)透視表等工具進(jìn)行分析，需要轉(zhuǎn)換為純數(shù)字。方法1：利用智能標(biāo)記方法2：利用VALUE函數(shù)方法3：利用公式（兩個(gè)負(fù)號(hào)或者乘以1或除以1）方法4：利用選擇性粘貼的批量修改功能（乘以1或者除以1）方法5：利用分列工具案例02 將文本型數(shù)字轉(zhuǎn)化為純數(shù)字韓小

9、良第9頁(yè)，共35頁(yè)?？焖賱h除數(shù)據(jù)中的特殊字符數(shù)據(jù)中的很多“垃圾”字符會(huì)影響到數(shù)據(jù)的處理和分析，應(yīng)當(dāng)刪除。比如：刪除數(shù)據(jù)中的空格刪除字符中的看不見(jiàn)、打印不出的特殊字符（案例03）韓小良第10頁(yè)，共35頁(yè)?？焖俜至袛?shù)據(jù)有些情況下，從數(shù)據(jù)庫(kù)導(dǎo)入的數(shù)據(jù)是一列數(shù)據(jù)，需要根據(jù)實(shí)際情況進(jìn)行分列。方法方法1：使用“分列”工具方法2：實(shí)用文本函數(shù)案例04 數(shù)據(jù)分列 分列前的數(shù)據(jù)分列后的數(shù)據(jù)韓小良第11頁(yè)，共35頁(yè)。快速填充數(shù)據(jù)有些時(shí)候，從系統(tǒng)導(dǎo)入的數(shù)據(jù)表格有大量的空白單元格，需要進(jìn)行填充。根據(jù)具體情況：填充為上一行的數(shù)據(jù)填充為數(shù)字0案例05 快速填充數(shù)據(jù)韓小良第12頁(yè)，共35頁(yè)。常用函數(shù)的高效運(yùn)用一個(gè)表格中，處

10、理分析最多的是數(shù)據(jù)求和匯總和數(shù)據(jù)查詢，因此必須熟練掌握相關(guān)求和匯總函數(shù)和查找函數(shù)的使用。高效求和匯總數(shù)據(jù)的靈活查詢韓小良第13頁(yè)，共35頁(yè)。高效求和匯總低效率的加減公式，既不科學(xué)，也容易出錯(cuò)。在很多情況下，可以使用有關(guān)的求和函數(shù)來(lái)解決。常用的求和函數(shù)SUM函數(shù)：無(wú)條件求和。但SUM函數(shù)還有另類用法，比如快速計(jì)算表格中的小計(jì)總和 ； 快速對(duì)多個(gè)工作表求和SUMIF 函數(shù)：?jiǎn)螚l件求和=SUMIF(條件判斷區(qū)域，條件值，實(shí)際求和區(qū)域)SUMPRODUCT 函數(shù)：計(jì)算乘積和，可以用于多條件求和與多條件計(jì)數(shù)= SUMPRODUCT(數(shù)組1，數(shù)組2，數(shù)組3，.)SUBTOTAL 函數(shù)：對(duì)數(shù)據(jù)進(jìn)行分類匯總，

11、主要用于篩選和列表 = SUBTOTAL(函數(shù)類型，計(jì)算區(qū)域1，計(jì)算區(qū)域2，.)案例06 高效求和匯總韓小良第14頁(yè)，共35頁(yè)。數(shù)據(jù)的靈活查詢查找函數(shù)有很多，其中使用最多的是VLOOKUP函數(shù)、INDEX函數(shù)、MATCH函數(shù)、INDIRECT函數(shù)。VLOOKUP函數(shù)：根據(jù)首列數(shù)據(jù)進(jìn)行查詢（案例07）=VLOOKUP(查找依據(jù)，查找區(qū)域，指定列位置，邏輯值)INDEX函數(shù)：根據(jù)行索引和列索引進(jìn)行查詢（案例08）= INDEX(查找區(qū)域，指定行位置，指定列位置)MATCH函數(shù)：對(duì)數(shù)據(jù)進(jìn)行定位（案例08）= MATCH(查找依據(jù)，指定行位置，指定列位置) 韓小良第15頁(yè)，共35頁(yè)?？焖賲R總多個(gè)工作簿

12、和工作表數(shù)據(jù)快速匯總多個(gè)結(jié)構(gòu)完全相同的工作表 快速匯總結(jié)構(gòu)不同的多個(gè)工作表（特殊情況）快速匯總結(jié)構(gòu)不同的多個(gè)工作表（一般情況）快速匯總多個(gè)有關(guān)聯(lián)的工作表 韓小良第16頁(yè)，共35頁(yè)?？焖賲R總多個(gè)結(jié)構(gòu)完全相同的工作表當(dāng)多個(gè)工作表的結(jié)構(gòu)相同時(shí)，可以使用合并計(jì)算工具快速進(jìn)行合并計(jì)算，同時(shí)還可以創(chuàng)建分類匯總和分級(jí)顯示。案例研究:案例09 快速合并多個(gè)結(jié)構(gòu)相同的分報(bào)表(當(dāng)前工作簿)案例10 快速合并多個(gè)結(jié)構(gòu)相同的分報(bào)表(不同工作簿) 韓小良第17頁(yè)，共35頁(yè)?？焖賲R總結(jié)構(gòu)不同的多個(gè)工作表（特殊情況）利用多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表，可以快速把數(shù)十個(gè)甚至上百個(gè)工作表的數(shù)據(jù)匯總在一起，然后在此基礎(chǔ)上進(jìn)行相

13、關(guān)的統(tǒng)計(jì)分析。注意：要匯總的多個(gè)工作表數(shù)據(jù)行數(shù)和列數(shù)可以不同，但從數(shù)據(jù)區(qū)域的第2列開始必須是數(shù)字。案例11 快速匯總多個(gè)工作表數(shù)據(jù)(特殊情況) 三個(gè)分公司數(shù)據(jù)匯總到一張工作表韓小良第18頁(yè)，共35頁(yè)?？焖賲R總結(jié)構(gòu)不同的多個(gè)工作表（一般情況）一般情況下，要匯總的各個(gè)工作表數(shù)據(jù)可能含有多列文本，此時(shí)就不能采用普通的多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表來(lái)進(jìn)行匯不總了。最好的解決方法：使用導(dǎo)入外部數(shù)據(jù)+SQL語(yǔ)句。案例12 快速匯總多個(gè)工作表數(shù)據(jù)(一般情況) 韓小良第19頁(yè)，共35頁(yè)?？焖賲R總多個(gè)有關(guān)聯(lián)的工作表有些情況下，幾個(gè)工作表中分別保存不同的數(shù)據(jù)，但他們通過(guò)一個(gè)關(guān)鍵列數(shù)據(jù)（比如工號(hào)）聯(lián)系起來(lái)。要將這

14、樣幾個(gè)相關(guān)聯(lián)的工作表數(shù)據(jù)進(jìn)行匯總，可使用Microsoft Query。案例13 快速匯總多個(gè)有關(guān)聯(lián)的工作表數(shù)據(jù) 韓小良第20頁(yè)，共35頁(yè)。數(shù)據(jù)透視表的靈活應(yīng)用數(shù)據(jù)透視表的基本概念數(shù)據(jù)透視表的類型準(zhǔn)備工作創(chuàng)建數(shù)據(jù)透視表的基本方法和注意事項(xiàng)設(shè)置數(shù)據(jù)透視的格式利用數(shù)據(jù)透視表快速編制各類統(tǒng)計(jì)分析報(bào)表綜合應(yīng)用1：對(duì)比分析兩個(gè)表格數(shù)據(jù)綜合應(yīng)用2：進(jìn)銷存數(shù)據(jù)查詢韓小良第21頁(yè)，共35頁(yè)。數(shù)據(jù)透視表的基本概念什么是數(shù)據(jù)透視表？數(shù)據(jù)透視表能做什么？怎樣靈活使用數(shù)據(jù)透視表？韓小良第22頁(yè)，共35頁(yè)。數(shù)據(jù)透視表的類型普通區(qū)域數(shù)據(jù)透視表合并區(qū)域數(shù)據(jù)透視表外部數(shù)據(jù)源的數(shù)據(jù)透視表OLAP數(shù)據(jù)透視表韓小良第23頁(yè)，共35

15、頁(yè)。準(zhǔn)備工作制作數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域必須是數(shù)據(jù)清單數(shù)據(jù)區(qū)域第一行為列標(biāo)題列標(biāo)題不能重名數(shù)據(jù)區(qū)域中不能有空行和空列數(shù)據(jù)區(qū)域中不能有合并單元格每列數(shù)據(jù)為同一種類型的數(shù)據(jù)整理數(shù)據(jù)區(qū)域修改非法日期把文本型數(shù)字轉(zhuǎn)換為純數(shù)字刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行和空列取消合并單元格并進(jìn)行填充去掉字符串前后的空格刪除數(shù)字中的特殊字符刪除不必要的小計(jì)行和總計(jì)行將二維表格整理為數(shù)據(jù)清單等等韓小良第24頁(yè)，共35頁(yè)。創(chuàng)建數(shù)據(jù)透視表的基本方法和注意事項(xiàng)利用數(shù)據(jù)透視表向?qū)Вò咐?4）按照向?qū)Р襟E進(jìn)行操作缺點(diǎn)：無(wú)法實(shí)現(xiàn)數(shù)據(jù)源的動(dòng)態(tài)更新，除非使用動(dòng)態(tài)數(shù)據(jù)區(qū)域名稱解決方法：（1）利用導(dǎo)入數(shù)據(jù)方法；（2）利用OFFSET函數(shù)定義動(dòng)態(tài)名稱利

16、用導(dǎo)入數(shù)據(jù)方法按照導(dǎo)入數(shù)據(jù)向?qū)нM(jìn)行操作優(yōu)點(diǎn)：可以實(shí)現(xiàn)數(shù)據(jù)源的動(dòng)態(tài)更新；可以在不打開其他工作簿的情況下創(chuàng)建數(shù)據(jù)透視表可以以數(shù)據(jù)庫(kù)的數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表缺點(diǎn)：刪除數(shù)據(jù)項(xiàng)目后，會(huì)遺留“假”項(xiàng)目名稱利用數(shù)據(jù)庫(kù)查詢方法按照數(shù)據(jù)庫(kù)查詢向?qū)нM(jìn)行操作優(yōu)點(diǎn)：不用導(dǎo)入數(shù)據(jù)，就可以制作需要的報(bào)表可以有選擇性地對(duì)某些符合條件的數(shù)據(jù)制作報(bào)表可以在不打開其他工作簿的情況下創(chuàng)建數(shù)據(jù)透視表可以以數(shù)據(jù)庫(kù)的數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表韓小良第25頁(yè)，共35頁(yè)。設(shè)置數(shù)據(jù)透視的的格式在數(shù)據(jù)透視表上對(duì)數(shù)據(jù)透視表進(jìn)行重新布局修改字段名稱設(shè)置字段的數(shù)字格式取消數(shù)據(jù)透視表的行或列匯總?cè)∠侄蔚姆诸悈R總讓數(shù)據(jù)透視表數(shù)據(jù)按照某一字段進(jìn)行排序合并數(shù)據(jù)標(biāo)志設(shè)置

17、錯(cuò)誤值的顯示方式更新數(shù)據(jù)透視表的數(shù)據(jù)韓小良第26頁(yè)，共35頁(yè)。利用數(shù)據(jù)透視表快速編制各類統(tǒng)計(jì)分析報(bào)表布局透視表設(shè)置字段的分類匯總方式（案例14）設(shè)置字段的顯示方式（案例14）自定義數(shù)據(jù)透視表（案例14）組合字段（案例15）制作明細(xì)表快速匯總大量工作表數(shù)據(jù)韓小良第27頁(yè)，共35頁(yè)。綜合應(yīng)用1：對(duì)比分析兩個(gè)表格數(shù)據(jù)兩個(gè)工作表有什么不同？如何快速把兩個(gè)工作表的差異數(shù)據(jù)找出來(lái)？案例16 對(duì)比分析兩個(gè)表格數(shù)據(jù) 韓小良第28頁(yè)，共35頁(yè)。綜合應(yīng)用2：進(jìn)銷存數(shù)據(jù)查詢?nèi)绾慰焖倭私飧鱾€(gè)物料、商品等的入庫(kù)、出庫(kù)以及庫(kù)存情況？利用數(shù)據(jù)透視表，可以非常容易地制作出進(jìn)銷存數(shù)據(jù)查詢表格。案例17 進(jìn)銷存數(shù)據(jù)查詢韓小良第2

18、9頁(yè)，共35頁(yè)。其他應(yīng)用圖表制作的注意事項(xiàng)和典型圖表演示宏和VBA簡(jiǎn)介Excel報(bào)表和圖表與Word/PPT的鏈接 韓小良第30頁(yè)，共35頁(yè)。圖表制作的注意事項(xiàng)和典型圖表演示用圖表準(zhǔn)確表達(dá)出你的觀點(diǎn)繪制圖表基本方法及注意事項(xiàng)圖表的修飾與美化用組合圖表表達(dá)更加復(fù)雜的信息讓圖表按照您的要求靈活顯示案例18 組合圖表演示案例案例19 動(dòng)態(tài)圖表演示案例韓小良第31頁(yè)，共35頁(yè)。宏和VBA簡(jiǎn)介什么是宏？什么是VBA？如何學(xué)習(xí)宏和VBA？宏和VBA的應(yīng)用：提高數(shù)據(jù)分析效率（案例演示）開發(fā)自己的管理系統(tǒng)（案例演示）韓小良第32頁(yè)，共35頁(yè)。Excel報(bào)表和圖表與Word/PPT的鏈接Excel報(bào)表和圖表與Word和PPT的鏈