《Excel實(shí)用技能及其在管理中的應(yīng)用》

1、Excel實(shí)用技能及其在管理中的應(yīng)用韓小良資深實(shí)戰(zhàn)型 Excel 培訓(xùn)講師和應(yīng)用解決方案專家韓小良 老師 簡介韓小良，資深實(shí)戰(zhàn)型Excel培訓(xùn)講師、應(yīng)用解決方案專家和管理系統(tǒng)軟件開發(fā)設(shè)計(jì)師，對Excel及Excel VBA在企業(yè)管理中的應(yīng)用有著較深的研究和獨(dú)特的認(rèn)識(shí)，對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ù)，出版了20余部關(guān)于Excel應(yīng)用方面

2、的著作，并陸續(xù)開發(fā)出了財(cái)務(wù)、銷售、人力資源、工資等管理、客戶管理、進(jìn)銷存等基于Excel的管理軟件，應(yīng)用于數(shù)十家企業(yè)，取得了明顯的經(jīng)濟(jì)效益。主要服務(wù)客戶包括：中國銀行總部、Google（中國）總部、百度總部、索尼愛立信、蘇州華星會(huì)計(jì)師事務(wù)所、中儲(chǔ)股份、北京國華能源投資、浙江中煙集團(tuán)、索恩照明、美國Halliburton公司、法國Saint-Gobain公司、瑞典HALDEX汽車產(chǎn)品、瑞士SEFAR科技、上海海斯特叉車、德國GIF研發(fā)中心、丹麥Vestas風(fēng)力技術(shù)、江蘇交通研究院、無錫華潤上華科技、南京華德火花塞、中化(寧波)集團(tuán)、德國卓倫機(jī)械(天津)、晶澳太陽能（上海）、日本（F.tech）偉

3、?？萍脊I(yè)（武漢）、貴州銀燕集團(tuán)、南京南瑞繼保電器、上海醫(yī)藥集團(tuán)、南京勁草時(shí)裝、等等數(shù)百家中外企業(yè)。韓小良 主要著作1.Excel會(huì)計(jì)應(yīng)用范例精解，電子工業(yè)出版社2.Excel VBA +Access財(cái)務(wù)系統(tǒng)開發(fā)從基礎(chǔ)到實(shí)踐，電子工業(yè)出版社3.Excel在投資理財(cái)中的應(yīng)用，電子工業(yè)出版社4.Excel VBA工資管理應(yīng)用案例詳解，中國鐵道出版社5.Excel在財(cái)務(wù)管理與分析中的應(yīng)用，中國水利水電道出版社6.運(yùn)用Excel VBA創(chuàng)建高效財(cái)務(wù)管理模型，中國鐵道出版社7.Excel VBA行政與人力資源管理應(yīng)用案例詳解，中國鐵道出版社8.Excel VBA財(cái)務(wù)管理應(yīng)用案例詳解，中國鐵道出版社9.運(yùn)

4、用Excel VBA進(jìn)行高效投資決策，中國鐵道出版社10.Excel VBA從入門到精通，中國鐵道出版社11.Excel VBA實(shí)用技巧大全，中國鐵道出版社12.Excel VBA與數(shù)據(jù)庫整合應(yīng)用范例精解，科學(xué)出版社13.Excel VBA銷售管理系統(tǒng)開發(fā)入門與實(shí)踐，科學(xué)出版社14.Excel VBA整合數(shù)據(jù)庫應(yīng)用從基礎(chǔ)到實(shí)踐，電子工業(yè)出版社15.Excel在銷售與財(cái)務(wù)管理中的應(yīng)用，電子工業(yè)出版社16.Excel VBA應(yīng)用開發(fā)（技巧精華版），電子工業(yè)出版社17.Excel企業(yè)管理應(yīng)用案例精萃，電子工業(yè)出版社18.Excel +SQL Server數(shù)據(jù)庫管理技術(shù)詳解，機(jī)械工業(yè)出版社19.Exc

5、el在納稅管理與籌劃中的應(yīng)用，科學(xué)出版社20.Excel高效辦公：方法、技巧和實(shí)用模型，中國鐵道出版社21.Excel VBA （ 2003/2007）高效辦公實(shí)用寶典，中國鐵道出版社22.用圖表說話：Excel實(shí)用精美圖表大制作，電子工業(yè)出版社23.Excel數(shù)據(jù)透視表從入門到精通，中國鐵道出版社24.Excel 2003/2007函數(shù)和公式的使用藝術(shù)，中國鐵道出版社25.Excel VBA（ 2003/2007）活用范例大辭典，中國鐵道出版社如何高效使用Excel，實(shí)現(xiàn)科學(xué)管理？掌握常用函數(shù)和工具設(shè)計(jì)規(guī)范表格快速制作匯總統(tǒng)計(jì)分析報(bào)表和圖表熟練使用數(shù)據(jù)透視表用圖表說話HR數(shù)據(jù)日常管理本課程目

6、標(biāo)宗旨：提高利用Excel進(jìn)行日常管理和數(shù)據(jù)處理與分析的效率快速制作各種數(shù)據(jù)匯總統(tǒng)計(jì)分析報(bào)表和圖表目標(biāo)：掌握Excel的常用操作技巧掌握規(guī)范整理表格的常用方法和技巧掌握制作科學(xué)規(guī)范表單的方法和技巧掌握常用數(shù)據(jù)分析工具的使用方法掌握常用函數(shù)的使用方法重點(diǎn)掌握利用數(shù)據(jù)透視表處理分析數(shù)據(jù)的方法、技巧和各種應(yīng)用掌握制作精美實(shí)用圖表的基本方法課程目錄Excel數(shù)據(jù)表格的設(shè)計(jì)與規(guī)范常用函數(shù)及其靈活運(yùn)用快速制作匯總、統(tǒng)計(jì)分析報(bào)表數(shù)據(jù)透視表的靈活應(yīng)用用圖表說話制作精美實(shí)用圖表在PPT上多維展示需要的數(shù)據(jù)和信息綜合案例練習(xí)與測驗(yàn)學(xué)員的實(shí)際問題研究與答疑Excel財(cái)務(wù)表格的設(shè)計(jì)與規(guī)范個(gè)性化Excel操作界面，提高

7、Excel操作效率快速準(zhǔn)確地審核別人遞交的Excel表格關(guān)聯(lián)報(bào)表間的鏈接與引用規(guī)范整理從數(shù)據(jù)庫導(dǎo)入的數(shù)據(jù)Excel表格數(shù)據(jù)的美化整理表格的其他實(shí)用方法和技巧設(shè)計(jì)清晰、美觀、實(shí)用的Excel數(shù)據(jù)管理表格個(gè)性化Excel操作界面，提高Excel操作效率養(yǎng)成使用Excel的好習(xí)慣設(shè)置統(tǒng)一字體和字號(hào)（比如11號(hào)的Arial字體）設(shè)置工作簿的默認(rèn)保存/打開位置合理設(shè)計(jì)Excel表單，分清楚報(bào)告型表單和管理型表單的區(qū)別等等自定義Excel操作界面添加常用的操作按鈕（格式按鈕、增大/縮小字號(hào)按鈕、選擇性粘貼按鈕、自動(dòng)篩選按鈕、等等）顯示常用工具欄熟練使用常用的快捷鍵等等快速準(zhǔn)確地審核別人遞交的Excel表格

8、查找并修改錯(cuò)誤的、非法的數(shù)據(jù)方法：利用數(shù)據(jù)有效性和圈釋無效數(shù)據(jù)的方法案例01 圈釋無效數(shù)據(jù)案例02 查找非法日期審核公式的正確性：引用了哪些單元格數(shù)據(jù)？數(shù)據(jù)間的勾稽關(guān)系是否正確？方法：使用公式審核工具欄案例03 審核公式查找并修改錯(cuò)誤值或公式方法：利用定位、錯(cuò)誤檢查、公式審核的方法，快速查找錯(cuò)誤值或出現(xiàn)錯(cuò)誤的公式案例04 快速查找錯(cuò)誤數(shù)據(jù)的單元格關(guān)聯(lián)報(bào)表間的鏈接與引用同一個(gè)工作簿之間不同工作表間的鏈接與引用引用格式：工作表名!單元格地址不同一個(gè)工作簿之間不同工作表間的鏈接與引用引用格式：路徑工作簿.xls工作表名!單元格地址建議：引用其它工作簿數(shù)據(jù)時(shí)，最好把該工作簿先打開。應(yīng)用擴(kuò)展：利用報(bào)表間

9、的鏈接與引用格式，我們可以使用INDIRECT函數(shù)動(dòng)態(tài)查詢引用其他工作表數(shù)據(jù)，很方便地制作動(dòng)態(tài)匯總報(bào)表，快速匯總數(shù)據(jù)。規(guī)范整理從數(shù)據(jù)庫導(dǎo)入的數(shù)據(jù)從數(shù)據(jù)庫導(dǎo)出的數(shù)據(jù)很多情況下都需要進(jìn)行整理。例如：數(shù)據(jù)分列修改非法日期將文本型數(shù)字轉(zhuǎn)化為純數(shù)字刪除數(shù)字中的特殊字符查找重復(fù)數(shù)據(jù)對比分析兩個(gè)表格的數(shù)據(jù)數(shù)據(jù)分列有些情況下，從數(shù)據(jù)庫導(dǎo)入的數(shù)據(jù)是一列數(shù)據(jù)，需要根據(jù)實(shí)際情況進(jìn)行分列。案例05 數(shù)據(jù)分列問題：分列后的日期如何處理？ 分列前的數(shù)據(jù)分列后的數(shù)據(jù)修改非法日期非法的日期有很多種，比如2009.1.15，20090115，90115等?？筛鶕?jù)實(shí)際情況，采用分列、查找/替換、函數(shù)等方法進(jìn)行轉(zhuǎn)換。案例06 修改

10、非法日期 小知識(shí)：Excel對日期和時(shí)間的處理方式案例研究：對于下圖的年、月、日3個(gè)數(shù)字，如何將其整合為真正的日期？工號(hào)部門姓名性別進(jìn)公司時(shí)間年月日100001公司本部王金明男200881100002公司本部吳亞平女200471100003公司本部宋少華男200471100004公司本部龔國華男2007112110001公司本部陳剛男2007417將文本型數(shù)字轉(zhuǎn)化為純數(shù)字有些情況下，從數(shù)據(jù)庫導(dǎo)入的數(shù)字是文本型數(shù)字。文本型數(shù)字無法使用 SUM函數(shù)等進(jìn)行計(jì)算， 需要轉(zhuǎn)換為純數(shù)字。方法有很多：方法1：利用智能標(biāo)記方法2：利用VALUE函數(shù)方法3：利用公式（兩個(gè)負(fù)號(hào)或者乘以1或除以1）利用4：利用選擇

11、性粘貼的批量修改功能（乘以1或者除以1）案例07 將文本型數(shù)字轉(zhuǎn)化為純數(shù)字刪除數(shù)字中的特殊字符某些場合下，從數(shù)據(jù)庫導(dǎo)入的數(shù)據(jù)中含有看不見也打印不出來的特殊字符，造成無法進(jìn)行計(jì)算分析。解決方法1、查找/替換2、利用有關(guān)函數(shù)案例08 刪除數(shù)字中的特殊字符查找重復(fù)數(shù)據(jù)對于重復(fù)數(shù)據(jù)，需要將其查找出來，或者刪除多余的重復(fù)數(shù)據(jù)，使數(shù)據(jù)清單保持唯一性。方法：條件格式高級(jí)篩選數(shù)據(jù)透視表案例09 查找重復(fù)數(shù)據(jù) 對比分析兩個(gè)表格的數(shù)據(jù)自己有一個(gè)表格，別人又發(fā)給您一個(gè)表格，如何快速查找這兩個(gè)表格數(shù)據(jù)的差異？方法：數(shù)據(jù)透視表案例10 對比分析兩個(gè)表格的數(shù)據(jù) Excel表格數(shù)據(jù)的美化Excel表格數(shù)據(jù)的美化，是一個(gè)非常

12、重要的工作，不僅可以使報(bào)表美觀、整潔，也易于察看分析數(shù)據(jù)例如：縮位顯示數(shù)字日期和時(shí)間格式的設(shè)置計(jì)算誤差的處理不顯示工作表或者某個(gè)數(shù)據(jù)區(qū)域的零值縮位顯示數(shù)字如果工作表金額數(shù)據(jù)很大，原封不動(dòng)顯示實(shí)際金額會(huì)使報(bào)表很難看。我們可以利用自定義數(shù)字格式的方式，將數(shù)字縮小1千倍、1萬倍、10萬倍、1百萬倍、1千萬倍等等顯示。這種縮位顯示不改變原數(shù)字大小。方法：使用自定義數(shù)字格式代碼：縮小千位顯示數(shù)字：0.00，縮小萬位顯示數(shù)字：0!.0,縮小十萬位顯示數(shù)字：0!.00,縮小百萬位顯示數(shù)字：0.00,縮小億位顯示數(shù)字：0.00,案例11：縮位顯示數(shù)字（縮小1萬倍）日期和時(shí)間的格式設(shè)置日期和時(shí)間是一種特殊的數(shù)字

13、，可以設(shè)置成各種顯示格式。 案例12 將日期顯示為需要的格式案例13 顯示超過24小時(shí)的累計(jì)時(shí)間計(jì)算誤差的處理Excel的計(jì)算誤差非常令人惱火，在有些情況下會(huì)產(chǎn)生意想不到的后果。例如，在單元格輸入公式“= 6.1-6.2+1”，看看結(jié)果是否0.9解決方法：使用ROUND函數(shù)，或者設(shè)置Excel選項(xiàng)。案例14 如何解決Excel的計(jì)算誤差 不顯示工作表或者某個(gè)數(shù)據(jù)區(qū)域的零值當(dāng)工作表存在大量的零值時(shí)，會(huì)使報(bào)表很難看，可以將這些零值隱藏。這些零值可能是輸入的，但大多數(shù)是查詢公式的結(jié)果。方法：設(shè)置Excel選項(xiàng)（隱藏整個(gè)工作表的零值）自定義數(shù)字格式/條件格式（隱藏某個(gè)區(qū)域的零值）整理表格的其他實(shí)用方法

14、和技巧不規(guī)范表格的重新整理和構(gòu)架搭建批量修改數(shù)據(jù)復(fù)制可見單元格數(shù)據(jù)刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行刪除字符串中的空格并排顯示多個(gè)工作表快速選擇單元格區(qū)域的小技巧快速選擇工作表的小技巧不規(guī)范表格的重新整理和構(gòu)架搭建對于不規(guī)范的表格，例如多表頭，有合并單元格，等等，需要進(jìn)行相應(yīng)的整理。方法：根據(jù)情況，可以使用復(fù)制/粘貼，函數(shù)、VBA等方法。案例15 取消合并單元格并快速填充數(shù)據(jù)錯(cuò)誤的表格正確的表格批量修改數(shù)據(jù)如果需要對某些區(qū)域的數(shù)據(jù)進(jìn)行批量修改，可以使用選擇性粘貼工具。既可以用于連續(xù)單元格區(qū)域，也可以用于不連續(xù)單元格區(qū)域。復(fù)制可見單元格數(shù)據(jù)當(dāng)需要僅僅復(fù)制可見單元格數(shù)據(jù)，而剔出掉被隱藏的數(shù)據(jù)時(shí)，需要先定位這

15、些可見單元格，然后再進(jìn)行復(fù)制粘貼。定位可見單元格的方法：使用“定位條件”對話框使用快捷組合鍵“Alt+;”刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行數(shù)據(jù)區(qū)域內(nèi)空行的存在，會(huì)影響到數(shù)據(jù)的統(tǒng)計(jì)分析，必須將其刪除。刪除方法：查找/刪除篩選輔助列排序刪除字符串中的空格如果字符串的前后或者內(nèi)部有空格，可能會(huì)影響到以后的數(shù)據(jù)分析，比如查不到數(shù)據(jù)，篩選不出數(shù)據(jù)，等等。刪除字符串中空格的方法：查找/替換使用TRIM函數(shù)并排顯示多個(gè)工作表當(dāng)需要同時(shí)查看和比較分析多個(gè)工作表數(shù)據(jù)時(shí)，可以新建幾個(gè)窗口，然后再將這些窗口并排顯示。問題：如何將某個(gè)工作表分成幾個(gè)窗格，以便由于察看比較該工作表不同區(qū)域的數(shù)據(jù)？快速選擇單元格區(qū)域的小技巧當(dāng)需要

16、選擇的單元格區(qū)域很大，或者在工作表很遠(yuǎn)的位置時(shí)，使用鼠標(biāo)點(diǎn)選的方法是比較費(fèi)勁的?？旖莘椒ǎ豪妹Q框快速選擇工作表的小技巧當(dāng)工作簿有數(shù)十個(gè)或者上百個(gè)工作表時(shí)，采用單擊工作表標(biāo)簽滾動(dòng)條的方法是效率較低的?？梢允褂每旖莶藛蝸砜焖龠x擇某個(gè)工作表。小技巧：如何禁止別人隨意打開并查看您的工作表？不顯示工作表標(biāo)簽掩蓋工作表標(biāo)簽設(shè)計(jì)清晰、美觀、實(shí)用的Excel數(shù)據(jù)管理表格正確了解和使用兩類基本的Excel表格設(shè)計(jì)科學(xué)規(guī)范Excel數(shù)據(jù)管理表格的基本原則設(shè)計(jì)科學(xué)規(guī)范Excel數(shù)據(jù)管理表格的基本方法案例演示與練習(xí)正確了解和使用兩類基本的Excel表格要正確了解和使用兩類基本的Excel表格：報(bào)告型表格：是給別人

17、看的，可以根據(jù)需要設(shè)計(jì)成任意的樣式。管理型表單：是保存數(shù)據(jù)、進(jìn)行數(shù)據(jù)分析用的，要設(shè)計(jì)成數(shù)據(jù)清單的形式。說明：在大多數(shù)情況下，報(bào)告型表格可以以管理型表單為基礎(chǔ)，利用查找函數(shù)進(jìn)行制作。如果使用VBA，還可以以報(bào)告型表格為數(shù)據(jù)錄入界面，完成管理型表單數(shù)據(jù)的錄入，或者制作查詢報(bào)告。有些情況下，我們必須設(shè)計(jì)結(jié)構(gòu)非常復(fù)雜的表格，比如成本分析表格，此時(shí)，就只能使用相關(guān)函數(shù)進(jìn)行計(jì)算分析了。管理型表格報(bào)告型表格設(shè)計(jì)科學(xué)規(guī)范Excel表單的基本原則設(shè)計(jì)目標(biāo)明確：表單是做什么用的？結(jié)構(gòu)合理：表單要表達(dá)什么信息？信息表達(dá)結(jié)構(gòu)？要整潔美觀：可讀性如何？（數(shù)據(jù)格式）；結(jié)構(gòu)可視性如何？ （對齊格式）；結(jié)構(gòu)框架性如何？（框線

18、、背景顏色） 要重視顏色的作用！表格格式的靈活性如何？（條件格式）禁止或避免輸入垃圾數(shù)據(jù)?？梢灾貜?fù)使用和共享讓各個(gè)部門或人員按照您的要求填寫數(shù)據(jù)實(shí)現(xiàn)網(wǎng)上多人協(xié)作編輯數(shù)據(jù)設(shè)計(jì)科學(xué)規(guī)范Excel表單的基本方法控制數(shù)據(jù)的輸入，讓別人按照您的要求和格式輸入數(shù)據(jù)：使用數(shù)據(jù)有效性，可以進(jìn)行各種輸入規(guī)則的設(shè)置快速準(zhǔn)確輸入某些信息：使用查找函數(shù)，使用名稱美化表格：合理設(shè)置單元格格式使用條件格式動(dòng)態(tài)設(shè)置格式禁止輸入垃圾數(shù)據(jù)：使用保護(hù)工作表方法禁止別人操作某些特殊單元格：采用局部保護(hù)工作表每個(gè)部門只能在自己的區(qū)域輸入編輯數(shù)據(jù)：設(shè)置工作表的多用戶編輯區(qū)域禁止別人隨意插入、刪除、重命名工作表：使用VBA語句等等案例演

19、示與練習(xí)設(shè)計(jì)一個(gè)日常費(fèi)用管理臺(tái)賬表單，基本要求:1、標(biāo)準(zhǔn)的數(shù)據(jù)清單（流水賬）2、不能空行輸入3、只有某行全部單元格都輸入數(shù)據(jù)后才能出現(xiàn)邊框4、選擇輸入部門名稱5、只能選擇輸入某個(gè)部門下的員工姓名6、只能輸入某年的日期7、只有在A列至D列都輸入數(shù)據(jù)后才能輸入金額數(shù)據(jù)8、根據(jù)不同的費(fèi)用項(xiàng)目，有不同的費(fèi)用限制9、只有在上一行都輸入完畢數(shù)據(jù)后，才能開始輸入新的紀(jì)錄10、不能在數(shù)據(jù)區(qū)域外的其他單元格輸入“垃圾”數(shù)據(jù)案例16 日常費(fèi)用管理：設(shè)計(jì)科學(xué)的Excel表單 常用函數(shù)及其靈活運(yùn)用關(guān)于單元格引用如何快速輸入嵌套函數(shù)和公式保護(hù)和隱藏公式邏輯判斷函數(shù)和信息函數(shù)計(jì)數(shù)函數(shù)求和函數(shù)查找函數(shù)文本函數(shù)日期函數(shù)四舍五

20、入函數(shù)關(guān)于單元格引用什么是引用在進(jìn)行數(shù)據(jù)計(jì)算時(shí)，用戶既可以輸入數(shù)值，也可以輸入某數(shù)值所在的單元格地址，還可以輸入單元格的名稱，這就是引用。引用方式 相對引用絕對引用 半相對引用（半絕對引用） 混合引用 三維引用 引用快速轉(zhuǎn)換小技巧循環(huán)按【F4】鍵，就會(huì)依照相對引用絕對引用列相對行絕對列絕對行相對相對引用這樣的順序循環(huán)下去。 如何快速輸入嵌套函數(shù)和公式大多數(shù)的實(shí)際問題，需要聯(lián)合使用幾個(gè)函數(shù)來解決，也就是必須創(chuàng)建嵌套函數(shù)公式?？焖贉?zhǔn)確嵌套函數(shù)公式有兩種方法：方法1、聯(lián)合使用名稱框和函數(shù)參數(shù)對話框方法2、先分解函數(shù)和公式，最后再綜合成一個(gè)公式案例案例06 修改非法日期保護(hù)和隱藏公式普通顯示和隱藏：顯

21、示公式計(jì)算結(jié)果或公式本身按【Ctrl+】組合鍵真正隱藏和保護(hù)只保護(hù)有公式的單元格實(shí)施步驟較為繁瑣邏輯判斷函數(shù)和信息函數(shù)邏輯判斷函數(shù)IF函數(shù)根據(jù)條件對數(shù)據(jù)進(jìn)行處理AND函數(shù)將幾個(gè)條件連結(jié)成與條件OR函數(shù)將幾個(gè)條件連結(jié)成或條件信息函數(shù)：IS類函數(shù)ISNUMBER函數(shù)：判斷是否為數(shù)字ISERROR函數(shù)：判斷是否為錯(cuò)誤值N函數(shù)：將數(shù)據(jù)轉(zhuǎn)換為數(shù)字說明：IF函數(shù)很多情況下是單獨(dú)使用，但在有些情況下需要與其他函數(shù)聯(lián)合使用。聯(lián)合使用IF函數(shù)和ISERROR函數(shù)可處理公式的錯(cuò)誤，尤其是在利用查找函數(shù)查找數(shù)據(jù)時(shí)出現(xiàn)錯(cuò)誤的處理上。應(yīng)用案例案例17 計(jì)算個(gè)人所得稅計(jì)數(shù)函數(shù)COUNT函數(shù)統(tǒng)計(jì)數(shù)據(jù)區(qū)域內(nèi)含有數(shù)字的單元格個(gè)

22、數(shù) 注意：日期和時(shí)間也是數(shù)字COUNTA函數(shù)統(tǒng)計(jì)數(shù)據(jù)區(qū)域內(nèi)含有非空白單元格的個(gè)數(shù)COUNTIF計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)案例18 統(tǒng)計(jì)訂單數(shù)和訂單總額 求和函數(shù)SUM函數(shù)無條件求和要注意SUM函數(shù)的使用條件：當(dāng)單元格有錯(cuò)誤值時(shí)無法計(jì)算，等。SUMIF函數(shù)單條件求和案例18 統(tǒng)計(jì)訂單數(shù)和訂單總額問題研究及解決方案：如何在滿足多個(gè)條件下進(jìn)行計(jì)數(shù)和求和計(jì)算？比如要統(tǒng)計(jì)周德宇在彩電上的訂單總數(shù)和訂單總額？解決方法：使用多條件的計(jì)數(shù)、求和數(shù)組公式使用數(shù)據(jù)透視表查找函數(shù)CHOOSE函數(shù)根據(jù)序號(hào)位置查找對應(yīng)數(shù)據(jù)MATCH函數(shù)查找指定數(shù)據(jù)在數(shù)據(jù)區(qū)域的相對位置INDEX函數(shù)從數(shù)據(jù)區(qū)域中查找指定的行號(hào)和

23、列號(hào)交叉處的數(shù)據(jù)。VLOOKUP函數(shù)根據(jù)查找值在數(shù)據(jù)區(qū)域的首列的位置來確定指定列的數(shù)值。 ROW函數(shù)和COLUMN函數(shù)獲取行號(hào)和列號(hào)OFFSET函數(shù)動(dòng)態(tài)引用單元格或單元格區(qū)域INDIRECT函數(shù)將字符串轉(zhuǎn)換為引用或名稱查找函數(shù)（續(xù)）應(yīng)用案例案例19 VLOOKUP函數(shù)：制作庫存查詢系統(tǒng)根據(jù)指定的庫存代碼，將某庫存商品的信息查找出來。 查找函數(shù)（續(xù)）應(yīng)用案例案例20 CHOOSE函數(shù)：跨表查詢根據(jù)指定的月份數(shù)字，將該月份工作表數(shù)據(jù)查找并顯示出來。 說明：這個(gè)問題還可以使用IF函數(shù)，但不如CHOOSE函數(shù)簡單文本函數(shù)常用函數(shù)LEFT函數(shù)：從左取指定個(gè)數(shù)的字符RIGHT函數(shù)：從右取指定個(gè)數(shù)的字符MI

24、D函數(shù)：從中間某位置取指定個(gè)數(shù)的字符LEN函數(shù)：獲取字符串的長度TRIM函數(shù)：去除文本兩端的空格CLEAN函數(shù)：去除文本中打印不出的特殊字符案例演示 案例21 從身份證號(hào)碼中獲取員工基本信息 日期和時(shí)間函數(shù)常用函數(shù)TODAY函數(shù)：獲取當(dāng)天日期NOW函數(shù)：獲取當(dāng)天日期和當(dāng)前時(shí)間DATE函數(shù)：將代表年、月和日的三個(gè)數(shù)字組合成日期YEAR函數(shù)、MONTH函數(shù)、DAY函數(shù)：取出日期的年、月、日三個(gè)數(shù)字WEEKDAY函數(shù)：獲取某個(gè)日期是星期幾分析工具庫里的日期函數(shù)，在Excel 2003中，必須加載分析工具庫才能使用：EOMONTH函數(shù)：獲取某個(gè)月的月底日期WEEKNUM函數(shù)：獲取某個(gè)日期是某年的第幾周

25、DATEDIF函數(shù)：常用于計(jì)算年齡和工齡YEARFRAC函數(shù)：常用于計(jì)算年齡和工齡四舍五入函數(shù)主要函數(shù)ROUND函數(shù)：四舍五入ROUNDUP函數(shù)：向上進(jìn)位ROUNDDOWN函數(shù)：向下進(jìn)位應(yīng)用解決實(shí)際值與顯示不一致問題解決計(jì)算誤差問題快速制作需要的各種匯總、統(tǒng)計(jì)、分析報(bào)表 數(shù)據(jù)透視表工具的靈活應(yīng)用對于大多數(shù)的工作表數(shù)據(jù)，以及數(shù)據(jù)庫數(shù)據(jù)，利用數(shù)據(jù)透視表來制作需要的各種匯總、統(tǒng)計(jì)、分析報(bào)表，是非常快捷和靈活的。不僅可以迅速匯總計(jì)算分析某個(gè)工作表數(shù)據(jù)，還可以迅速匯總多個(gè)工作表甚至多個(gè)工作簿數(shù)據(jù)。還可以對數(shù)據(jù)進(jìn)行各種分組和組合，使報(bào)表內(nèi)容更加豐富?？焖僦谱餍枰母鞣N匯總、統(tǒng)計(jì)、分析報(bào)表 數(shù)據(jù)透視表工具的

26、靈活應(yīng)用準(zhǔn)備工作創(chuàng)建數(shù)據(jù)透視表數(shù)據(jù)透視表結(jié)構(gòu)及術(shù)語數(shù)據(jù)透視表的類型設(shè)置數(shù)據(jù)透視的格式和選項(xiàng)，得到漂亮的匯總報(bào)表自定義數(shù)據(jù)透視表：添加計(jì)算字段和計(jì)算項(xiàng)快速創(chuàng)建地區(qū)銷售報(bào)表快速創(chuàng)建商品銷售報(bào)表快速創(chuàng)建周報(bào)、月報(bào)、季報(bào)和年報(bào)表快速創(chuàng)建環(huán)比分析報(bào)表快速創(chuàng)建同比分析報(bào)表快速創(chuàng)建明細(xì)分類報(bào)表快速匯總多個(gè)工作表和多個(gè)工作簿的數(shù)據(jù) 準(zhǔn)備工作制作數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域必須是數(shù)據(jù)清單數(shù)據(jù)清單的概念數(shù)據(jù)區(qū)域第一行為列標(biāo)題列標(biāo)題不能重名數(shù)據(jù)區(qū)域中不能有空行和空列數(shù)據(jù)區(qū)域中不能有合并單元格每列數(shù)據(jù)為同一種類型的數(shù)據(jù)整理數(shù)據(jù)區(qū)域刪除數(shù)據(jù)區(qū)域內(nèi)的所有空行和空列取消合并單元格并進(jìn)行填充去掉字符串前后的空格刪除數(shù)字中的特殊字符刪

27、除不必要的小計(jì)行和總計(jì)行將二維表格整理為數(shù)據(jù)清單等等創(chuàng)建數(shù)據(jù)透視表利用數(shù)據(jù)透視表向?qū)Вò咐?2）按照向?qū)Р襟E進(jìn)行操作缺點(diǎn)：無法實(shí)現(xiàn)數(shù)據(jù)源的動(dòng)態(tài)更新，除非使用動(dòng)態(tài)數(shù)據(jù)區(qū)域名稱利用導(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ù)庫的數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表缺點(diǎn)：刪除數(shù)據(jù)項(xiàng)目后，會(huì)遺留“假”項(xiàng)目名稱利用數(shù)據(jù)庫查詢方法按照數(shù)據(jù)庫查詢向?qū)нM(jìn)行操作優(yōu)點(diǎn)：不用導(dǎo)入數(shù)據(jù)，就可以制作需要的報(bào)表可以有選擇性地對某些符合條件的數(shù)據(jù)制作報(bào)表可以在不打開其他工作簿的情況下創(chuàng)建數(shù)據(jù)透視表可以以數(shù)據(jù)庫的數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表數(shù)據(jù)透視表結(jié)構(gòu)及術(shù)語什么是數(shù)據(jù)透視表

28、數(shù)據(jù)透視表結(jié)構(gòu)及術(shù)語字段項(xiàng)行字段列字段頁字段數(shù)據(jù)字段數(shù)據(jù)區(qū)拖放區(qū)域字段列表【數(shù)據(jù)透視表】工具欄：非常重要的工具數(shù)據(jù)透視表的類型普通數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表外部數(shù)據(jù)的數(shù)據(jù)透視表OLAP多維數(shù)據(jù)集的數(shù)據(jù)透視表設(shè)置數(shù)據(jù)透視的的格式和選項(xiàng)：得到漂亮的匯總報(bào)表在數(shù)據(jù)透視表上對數(shù)據(jù)透視表進(jìn)行重新布局修改字段名稱設(shè)置字段的數(shù)字格式取消數(shù)據(jù)透視表的行或列匯總讓數(shù)據(jù)透視表數(shù)據(jù)按照某一字段進(jìn)行排序合并數(shù)據(jù)標(biāo)志設(shè)置錯(cuò)誤值的顯示方式更新數(shù)據(jù)透視表的數(shù)據(jù)自定義數(shù)據(jù)透視表：添加計(jì)算字段和計(jì)算項(xiàng)為數(shù)據(jù)透視表設(shè)置自定義計(jì)算項(xiàng)目添加自定義計(jì)算項(xiàng)目修改自定義計(jì)算項(xiàng)目刪除自定義計(jì)算項(xiàng)目為數(shù)據(jù)透視表設(shè)置自

29、定義計(jì)算字段添加自定義計(jì)算字段修改自定義計(jì)算字段刪除自定義計(jì)算字段案例練習(xí)1：添加商品的平均單價(jià) 快速創(chuàng)建地區(qū)銷售報(bào)表創(chuàng)建地區(qū)銷售報(bào)表，分析市場占有率，為制定銷售策略提供基礎(chǔ)?？焖賱?chuàng)建商品銷售報(bào)表創(chuàng)建商品銷售報(bào)表，分析商品的銷售狀況，為產(chǎn)品生產(chǎn)計(jì)劃提供依據(jù) 快速創(chuàng)建周報(bào)、月報(bào)、季報(bào)和年報(bào)表快速創(chuàng)建每周報(bào)表、月度報(bào)表、季度報(bào)表和年度報(bào)表，分析公司的生產(chǎn)銷售情況，并進(jìn)行預(yù)測 月報(bào)、季報(bào)和年報(bào)表分析每年、季度和月的銷售情況快速創(chuàng)建環(huán)比分析報(bào)表制作環(huán)比增長分析報(bào)表，分析每個(gè)年度中各月相對于上個(gè)月的增長變化情況。這種報(bào)表適用于企業(yè)經(jīng)營穩(wěn)定，不受季節(jié)影響的場合。 環(huán)比分析報(bào)表環(huán)比分析圖表快速創(chuàng)建同比分析報(bào)

30、表制作同比增長分析報(bào)表，分析某兩個(gè)年度中各月同期的對比增長情況。這種報(bào)表適用于企業(yè)產(chǎn)品受季節(jié)影響較大的場合 同比分析報(bào)表同比分析圖表快速創(chuàng)建明細(xì)分類報(bào)表雙擊某個(gè)匯總數(shù)據(jù)單元格，就可以迅速得到明細(xì)報(bào)表。例如，單元格C6，就得到“房天琦”在“北京”的所有銷售記錄明細(xì)表。快速匯總多個(gè)工作表和多個(gè)工作簿的數(shù)據(jù)方法和注意事項(xiàng)快速匯總多個(gè)工作表數(shù)據(jù)（特殊情況）快速匯總多個(gè)工作表數(shù)據(jù)（一般情況）方法和注意事項(xiàng)利用數(shù)據(jù)透視表，可以快速把數(shù)十個(gè)甚至上百個(gè)工作表的數(shù)據(jù)匯總在一起，然后在此基礎(chǔ)上進(jìn)行相關(guān)的統(tǒng)計(jì)分析。方法：方法1：使用多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表方法2：使用導(dǎo)入數(shù)據(jù)工具+SQL語句注意事項(xiàng)：如果使用方法1，那么：要匯總的表格從第2列開始必須是數(shù)字。各個(gè)表格的列數(shù)和行數(shù)可以不同。如果表格從第2列開始不是數(shù)字，就需要采用方法2快速匯總多個(gè)工作表數(shù)據(jù)（特殊情況）案例23： 要匯