第二講_煙臺環(huán)球碼頭-Excel基礎操作與函數(shù)應用培訓

1、ExcelExcel函數(shù)培訓函數(shù)培訓高效辦公之Excel基礎操作與基礎函數(shù)培訓ExcelExcel函數(shù)培訓函數(shù)培訓Excel基礎知識基礎知識提升提升Excel技能的必要性技能的必要性如何快速成為如何快速成為Excel高手高手認識認識Excel單元格引用單元格引用打印工作表打印工作表數(shù)據(jù)高級篩選數(shù)據(jù)高級篩選p函數(shù)基礎知識函數(shù)基礎知識函數(shù)功能函數(shù)功能函數(shù)結(jié)構函數(shù)結(jié)構介紹運算符介紹運算符函數(shù)類型函數(shù)類型常見函數(shù)介紹常見函數(shù)介紹邏輯函數(shù)邏輯函數(shù)lIF函數(shù)函數(shù)lAND函數(shù)函數(shù)lOR函數(shù)函數(shù)求和類函數(shù)求和類函數(shù)lSUM函數(shù)函數(shù)lSUMIF函數(shù)函數(shù)目錄目錄常見函數(shù)介紹常見函數(shù)介紹查找函數(shù)查找函數(shù)lMATCH

2、函數(shù)函數(shù)lVLOOKUP函數(shù)函數(shù)lOFFSET函數(shù)函數(shù)信息函數(shù)信息函數(shù)l常見錯誤及排除錯誤的方法常見錯誤及排除錯誤的方法lISERROR函數(shù)函數(shù)l ISNA函數(shù)函數(shù)lISNUMBER函數(shù)函數(shù)p函數(shù)實例講解函數(shù)實例講解實例一：根據(jù)箱型自動確認箱代碼實例一：根據(jù)箱型自動確認箱代碼實例二：個人所得稅的計算實例二：個人所得稅的計算實例三：根據(jù)話費清單快速編制話費報表實例三：根據(jù)話費清單快速編制話費報表實例四：經(jīng)營租入機械掃雪作業(yè)統(tǒng)計實例四：經(jīng)營租入機械掃雪作業(yè)統(tǒng)計實例五：集卡作業(yè)統(tǒng)計表實例五：集卡作業(yè)統(tǒng)計表實例六：中海內(nèi)外貿(mào)標箱收入分析實例六：中海內(nèi)外貿(mào)標箱收入分析函數(shù)運用的關鍵點函數(shù)運用的關鍵點結(jié)束

3、語及提問結(jié)束語及提問ExcelExcel函數(shù)培訓函數(shù)培訓提升提升ExcelExcel技能的必要性技能的必要性 A A A A A A A A已已知知p p, ,n n, ,I I, ,求求A A= =？問題一：揭秘銀行等額本息下分期付款額的計算及還款計劃表的編制問題一：揭秘銀行等額本息下分期付款額的計算及還款計劃表的編制問題二：零存整取下問題二：零存整取下N年后可以獲得的現(xiàn)金？年后可以獲得的現(xiàn)金？問題四：已知公司月度電話費清單，如何快速按部門統(tǒng)計電話費報表？問題四：已知公司月度電話費清單，如何快速按部門統(tǒng)計電話費報表？問題五：如何對手工錄入作業(yè)票的原始數(shù)據(jù)進行加工、整理我們需要的數(shù)據(jù)？問題五：

4、如何對手工錄入作業(yè)票的原始數(shù)據(jù)進行加工、整理我們需要的數(shù)據(jù)？問題三：已知個人應發(fā)工資以及各種扣款，如何計算應交納的個人所得稅？問題三：已知個人應發(fā)工資以及各種扣款，如何計算應交納的個人所得稅？為什么要提升為什么要提升Excel技能？技能？1、在日常工作中、在日常工作中Excel使使用頻率非常高；用頻率非常高；2、滿足個人家庭理財?shù)?、滿足個人家庭理財?shù)男枰恍枰?、提高工作效率，節(jié)省、提高工作效率，節(jié)省數(shù)據(jù)處理的時間，將節(jié)數(shù)據(jù)處理的時間，將節(jié)省時間用于思考省時間用于思考Excel不不能解決的問題上（如：能解決的問題上（如：工作流程的改進）工作流程的改進）4、提升職業(yè)技能，目前、提升職業(yè)技能，目

5、前很多公司在面試時，都很多公司在面試時，都要求熟練掌握要求熟練掌握Excel；5、在國外很多小型企業(yè)、在國外很多小型企業(yè)都利用都利用Excel來代來代ERP； A A A A A A A A已已 知知 A A , , n n , , I I , , 求求 F F = = ？ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓如何快速成為如何快速成為ExcelExcel高手高手強意識強意識-會提問會提問-善總結(jié)善總結(jié)1、增強提高、增強提高Excel工作效率的意識；工作效率的意識；2、培養(yǎng)學習、培養(yǎng)學習Excel的興趣；的興趣；3、多提問、勤動手、多動腦；、多提問、勤動手、多動

6、腦；4、面對重復性、復雜性的工作，想想能否將其系統(tǒng)化、面對重復性、復雜性的工作，想想能否將其系統(tǒng)化、智能化智能化-培養(yǎng)善于找捷徑的習慣；培養(yǎng)善于找捷徑的習慣；5、Excel中的中的F1鍵；鍵；6、網(wǎng)絡學習；、網(wǎng)絡學習； 電腦這東西最大的優(yōu)勢在于不怕煩瑣，最擅長做重復性電腦這東西最大的優(yōu)勢在于不怕煩瑣，最擅長做重復性的工作。如果在工作中發(fā)現(xiàn)重復性的工作太多，就應該的工作。如果在工作中發(fā)現(xiàn)重復性的工作太多，就應該想辦法了。想辦法了。提問的智慧：提問的智慧：(1)提問之前，明確自己要做什么，自己的思路是否清晰？提問之前，明確自己要做什么，自己的思路是否清晰？(2)明確思路后，如不能解決問題，按明確思

7、路后，如不能解決問題，按F1查看幫助；查看幫助；(3)將自己的問題做成附件，說明問題，網(wǎng)絡求助；將自己的問題做成附件，說明問題，網(wǎng)絡求助；(4)如果該問題有很強的實用性，自己可以就其問題涉及如果該問題有很強的實用性，自己可以就其問題涉及到的所有知識點全面學習總結(jié)；到的所有知識點全面學習總結(jié)；ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓認識認識ExcelExcelExcelExcel三要素三要素ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓認識認識ExcelExcelExcelExcel菜單及工具欄菜單及工具欄菜單類別菜單類別可以通過工作表菜

8、單欄中視圖可以通過工作表菜單欄中視圖-工具欄來調(diào)用各種菜單！工具欄來調(diào)用各種菜單！ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓1、常用工具術語：名稱框、編輯框、狀態(tài)欄、查找替換對話框、定位對話框、工具、常用工具術語：名稱框、編輯框、狀態(tài)欄、查找替換對話框、定位對話框、工具-選項選項(1)名稱框通常適用于為單元格定義名稱或者快速定位單元格，如圖：名稱框通常適用于為單元格定義名稱或者快速定位單元格，如圖：(2)編輯欄通常適用于公式的編輯；編輯欄通常適用于公式的編輯；(3)查找替換對話框通常用來批量查找或者批量替換數(shù)據(jù)或者公式中的某一部分，熟練使用該功能可以大查找替換對話

9、框通常用來批量查找或者批量替換數(shù)據(jù)或者公式中的某一部分，熟練使用該功能可以大大提高工作效率；大提高工作效率；應用一：有時從數(shù)據(jù)庫中導出的數(shù)據(jù)包括空值或者其他不可識別的字符，導致數(shù)據(jù)無法參與運算，此時可應用一：有時從數(shù)據(jù)庫中導出的數(shù)據(jù)包括空值或者其他不可識別的字符，導致數(shù)據(jù)無法參與運算，此時可以利用替換功能；以利用替換功能；應用二：當工作表中函數(shù)很多時，有時需要替換函數(shù)中的某些參數(shù)值，此時可以利用替換功能實現(xiàn)替換部應用二：當工作表中函數(shù)很多時，有時需要替換函數(shù)中的某些參數(shù)值，此時可以利用替換功能實現(xiàn)替換部分數(shù)據(jù)；分數(shù)據(jù)；應用三：該命令可以適用于應用三：該命令可以適用于word以及各種瀏覽器中；以

10、及各種瀏覽器中；認識認識ExcelExcel常用工具常用工具ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓2、單元格區(qū)域選擇及常用快捷鍵、單元格區(qū)域選擇及常用快捷鍵(1)單元格區(qū)域快速選擇的方法單元格區(qū)域快速選擇的方法建議方法：建議方法： 當區(qū)域較小時，可直接用鼠標拖動選擇當區(qū)域較小時，可直接用鼠標拖動選擇區(qū)域稍大，但在屏幕范圍內(nèi)，用鼠標點擊左上角單元格，按住區(qū)域稍大，但在屏幕范圍內(nèi)，用鼠標點擊左上角單元格，按住Shift鍵點擊右下角單元格鍵點擊右下角單元格區(qū)域較大，區(qū)域較大，直接在名稱框輸入范圍，如：在名稱框輸入直接在名稱框輸入范圍，如：在名稱框輸入A3:C7來選擇

11、黃色區(qū)域，輸入來選擇黃色區(qū)域，輸入3:7來選擇黃色區(qū)域所在的行，輸入來選擇黃色區(qū)域所在的行，輸入A:D選擇列選擇列常用區(qū)域，選擇后直接在名稱框輸入名稱，再使用時從名稱框直接選擇名稱常用區(qū)域，選擇后直接在名稱框輸入名稱，再使用時從名稱框直接選擇名稱不連續(xù)區(qū)不連續(xù)區(qū)域選擇：按住域選擇：按住Ctrl鍵用鼠標選擇或在名稱框輸入各區(qū)域名稱，區(qū)域名稱間用逗鍵用鼠標選擇或在名稱框輸入各區(qū)域名稱，區(qū)域名稱間用逗號分隔如：號分隔如：A3:B7,A10:D17(2)常用快捷鍵常用快捷鍵Ctrl+C/X/V分別表示復制、剪切、粘貼分別表示復制、剪切、粘貼Ctrl+Home返回到返回到a1單元格單元格Home返回活動

12、單元格首列返回活動單元格首列Ctrl+Shift+*返回當前單元格所在的一個區(qū)域返回當前單元格所在的一個區(qū)域(一個封閉在空白行和空白列中的矩形區(qū)域一個封閉在空白行和空白列中的矩形區(qū)域)Ctrl+Shift+方向鍵（將選定區(qū)域擴展到與活動單元格同一行或同一列的最后一個非空白單元格方向鍵（將選定區(qū)域擴展到與活動單元格同一行或同一列的最后一個非空白單元格Ctrl+方向鍵方向鍵CTRL+SHIFT+HOME將選定區(qū)域擴展到工作表的開始將選定區(qū)域擴展到工作表的開始CTRL+SHIFT+END將選定區(qū)域擴展到工作表的最后一個使用的單元格（右下角）將選定區(qū)域擴展到工作表的最后一個使用的單元格（右下角）Ctr

13、l+G編輯定位，可以定位特殊單元格，是一個非常實用的技巧編輯定位，可以定位特殊單元格，是一個非常實用的技巧Ctrl+F查找數(shù)據(jù)查找數(shù)據(jù)Ctrl+H替換數(shù)據(jù)替換數(shù)據(jù)Ctrl+F3自定義名稱自定義名稱F3粘貼自定義名稱粘貼自定義名稱感興趣的同事可以查看幫助中的快捷鍵！感興趣的同事可以查看幫助中的快捷鍵！認識認識ExcelExcelExcelExcel基礎知識基礎知識Excel2003Excel2003工作表中工作表中有多少行？有多少行？多少列？多少列？ExcelExcel函數(shù)培訓函數(shù)培訓三、常用技巧三、常用技巧1、常用工具欄中：格式刷、復制、粘貼命令的應用；、常用工具欄中：格式刷、復制、粘貼命令的

14、應用；(1)雙擊格式刷的妙用：雙擊格式刷后，可以一直對不同的單元格雙擊格式刷的妙用：雙擊格式刷后，可以一直對不同的單元格來刷格式，直到按一下格式刷按鈕或是按一下來刷格式，直到按一下格式刷按鈕或是按一下Esc鍵，這個用法在鍵，這個用法在word里也實用。里也實用。(2)選擇性粘貼：通常我們需要將文本型的數(shù)據(jù)轉(zhuǎn)換為數(shù)值型數(shù)據(jù)、需要將單元格區(qū)域選擇性粘貼：通常我們需要將文本型的數(shù)據(jù)轉(zhuǎn)換為數(shù)值型數(shù)據(jù)、需要將單元格區(qū)域中很多數(shù)據(jù)加、減、乘、除一個數(shù)據(jù)、取消工作表中的函數(shù)、數(shù)據(jù)轉(zhuǎn)置等中很多數(shù)據(jù)加、減、乘、除一個數(shù)據(jù)、取消工作表中的函數(shù)、數(shù)據(jù)轉(zhuǎn)置等2、有時大家需要將在、有時大家需要將在Excel中制作的圖表

15、放置在中制作的圖表放置在PowerPoint中，同時又不希望中，同時又不希望將其中的數(shù)據(jù)源復制過來。此時可以在將其中的數(shù)據(jù)源復制過來。此時可以在Excel中選擇需要復制的圖表，使用中選擇需要復制的圖表，使用ctrl+shift+鼠標單擊編輯鼠標單擊編輯-復制圖片命令，然后在復制圖片命令，然后在powerpoint中使用中使用ctrl+v；3、當數(shù)據(jù)表很大時，可以使用、當數(shù)據(jù)表很大時，可以使用ctrl+鼠標左鍵來調(diào)整顯示窗口，以方便閱覽；鼠標左鍵來調(diào)整顯示窗口，以方便閱覽；4、凍結(jié)窗口：凍結(jié)選定單元格左上區(qū)域，也可以凍結(jié)行、凍結(jié)列；、凍結(jié)窗口：凍結(jié)選定單元格左上區(qū)域，也可以凍結(jié)行、凍結(jié)列；5、填

16、充值：、填充值：Ctrl+d表示向下填充值表示向下填充值Ctrl+r表示向右填充值表示向右填充值6、快速填充單元格工具、快速填充單元格工具-填充炳填充炳(1)填充炳定義：位于選定區(qū)域右下角的小黑方塊。將用鼠標指向填充柄時，鼠填充炳定義：位于選定區(qū)域右下角的小黑方塊。將用鼠標指向填充柄時，鼠標的指針更改為黑十字；標的指針更改為黑十字；(2)禁用填充炳：工具禁用填充炳：工具-選項選項-編輯編輯-設置設置-單元格拖放取消勾；單元格拖放取消勾；(3)雙擊填充炳，雙擊填充炳，Excel會跟據(jù)選擇區(qū)域左邊列的內(nèi)容，自動填充到下一個有內(nèi)容會跟據(jù)選擇區(qū)域左邊列的內(nèi)容，自動填充到下一個有內(nèi)容的行，或者與左邊列的

17、內(nèi)容相同同。的行，或者與左邊列的內(nèi)容相同同。7、利用、利用Excel”拍照拍照“，工具，工具-自定義自定義-命令命令-工具工具-攝影，通常用來對工作表中選定區(qū)域進行攝影，通常用來對工作表中選定區(qū)域進行拍照，以生成圖片；拍照，以生成圖片；認識認識ExcelExcelExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓(4)快速定位單元格區(qū)域或者某些特殊區(qū)域，當工作表中數(shù)據(jù)超過一個屏幕時，有時需要快速定位快速定位單元格區(qū)域或者某些特殊區(qū)域，當工作表中數(shù)據(jù)超過一個屏幕時，有時需要快速定位單元格區(qū)域。單元格區(qū)域。A已知單元格區(qū)域，在名稱框中輸入?yún)^(qū)域或者單元格名稱，如：已知單元格區(qū)域

18、，在名稱框中輸入?yún)^(qū)域或者單元格名稱，如：A100:C150B使用編輯使用編輯-定位對話框來定位特殊單元格區(qū)域；定位對話框來定位特殊單元格區(qū)域；(5)工具工具-選項，通常用來對選項，通常用來對Excel進行初始設置，如：進行初始設置，如：是否顯示任務欄、行號列表、編輯欄、網(wǎng)絡線、水平是否顯示任務欄、行號列表、編輯欄、網(wǎng)絡線、水平滾動條以及工作表標簽等，在實際工作中可以根據(jù)滾動條以及工作表標簽等，在實際工作中可以根據(jù)自己的需要自行設置；自己的需要自行設置；認識認識ExcelExcelExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓單元格引用是函數(shù)中最常見的參數(shù)，引用的目的在

19、于標識工作表單元格或單元格區(qū)域，并單元格引用是函數(shù)中最常見的參數(shù)，引用的目的在于標識工作表單元格或單元格區(qū)域，并指明公式或函數(shù)所使用的數(shù)據(jù)的位置，便于它們使用工作表各處的數(shù)據(jù)，或者在多個函數(shù)中使指明公式或函數(shù)所使用的數(shù)據(jù)的位置，便于它們使用工作表各處的數(shù)據(jù)，或者在多個函數(shù)中使用同一個單元格的數(shù)據(jù)。還可以引用同一工作簿不同工作表的單元格，甚至引用其他工作簿中用同一個單元格的數(shù)據(jù)。還可以引用同一工作簿不同工作表的單元格，甚至引用其他工作簿中的數(shù)據(jù)。根據(jù)公式所在單元格的位置發(fā)生變化時，單元格引用的變化情況，我們可以引用分為的數(shù)據(jù)。根據(jù)公式所在單元格的位置發(fā)生變化時，單元格引用的變化情況，我們可以引用

20、分為相對引用、絕對引用和混合引用三種類型相對引用、絕對引用和混合引用三種類型如何快速輸入一個乘法公式，實現(xiàn)如何快速輸入一個乘法公式，實現(xiàn)“九九乘法表九九乘法表”?單元格引用單元格引用ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓單元格引用單元格引用ExcelExcel基礎知識基礎知識制作該表時，當輸入一個公式使用向右或者向下拖拉公式，都遵循一個規(guī)律：享有拖拉：制作該表時，當輸入一個公式使用向右或者向下拖拉公式，都遵循一個規(guī)律：享有拖拉：A列保持不變，向列保持不變，向下拖拉時，第二行保持不變；下拖拉時，第二行保持不變；-引用的四種類型：引用的四種類型：(1)相對行引用：

21、列固定，行可變，表示方式相對行引用：列固定，行可變，表示方式$A1，在輸入單元格公式后，向右拖拉列不發(fā)生變動，向下拖拉行會，在輸入單元格公式后，向右拖拉列不發(fā)生變動，向下拖拉行會發(fā)生變動；發(fā)生變動；(2)相對列引用：行固定，列可變，表示方式相對列引用：行固定，列可變，表示方式A$1，在輸入單元格公式后，向右拖拉列會發(fā)生變動，向下拖拉行不，在輸入單元格公式后，向右拖拉列會發(fā)生變動，向下拖拉行不發(fā)生變動；發(fā)生變動；(3)絕對引用：行列均固定，表示方式絕對引用：行列均固定，表示方式$A$1，在輸入單元格公式后，無論向右拖拉還是向下拖拉該單元格均不發(fā)，在輸入單元格公式后，無論向右拖拉還是向下拖拉該單元

22、格均不發(fā)生變動；生變動；(4)相對引用：行列均可變，表示方式相對引用：行列均可變，表示方式A1，在輸入單元格公式后，向下拖拉行發(fā)生變動，向右拖拉列發(fā)生變動；，在輸入單元格公式后，向下拖拉行發(fā)生變動，向右拖拉列發(fā)生變動；引用輸入小技巧：在書寫函數(shù)時，可以根據(jù)實際需要，輸入單元格引用后，按引用輸入小技巧：在書寫函數(shù)時，可以根據(jù)實際需要，輸入單元格引用后，按F4來快速切換引用的方式來快速切換引用的方式ExcelExcel函數(shù)培訓函數(shù)培訓打印工作表打印工作表ExcelExcel基礎知識基礎知識報表5要素：要素1：報表名稱要素2：報表日期要素3：報表單位要素4：填報人要素5：備注說明，方便報表使用者更直

23、觀的了解報表ExcelExcel函數(shù)培訓函數(shù)培訓填報報表注意事項：填報報表注意事項：*一份完美的報表需要遵循的原則：報表數(shù)據(jù)準確、直觀、易讀、美觀一份完美的報表需要遵循的原則：報表數(shù)據(jù)準確、直觀、易讀、美觀1、在數(shù)據(jù)準確的基礎上，注意明確報表的、在數(shù)據(jù)準確的基礎上，注意明確報表的5個要素；個要素；2、在美觀方面，注意表格的行高、邊框、數(shù)據(jù)的字體類型、數(shù)據(jù)的對齊方式以及數(shù)值型數(shù)據(jù)的顯示方、在美觀方面，注意表格的行高、邊框、數(shù)據(jù)的字體類型、數(shù)據(jù)的對齊方式以及數(shù)值型數(shù)據(jù)的顯示方式，通常一些財務報表數(shù)值型數(shù)據(jù)均已貨幣形式反映式，通常一些財務報表數(shù)值型數(shù)據(jù)均已貨幣形式反映(易讀易讀)；3、必要的時候，設

24、置單元格顏色用來重點區(qū)分某行數(shù)據(jù)；、必要的時候，設置單元格顏色用來重點區(qū)分某行數(shù)據(jù)；4、設置打印頁面，遵循美觀、節(jié)省紙張的原則；、設置打印頁面，遵循美觀、節(jié)省紙張的原則；通過頁面設置，可以設置：通過頁面設置，可以設置：（1）打印頁面的方向（橫向、縱向）打印頁面的方向（橫向、縱向)、縮放比例、紙張的大?。?、縮放比例、紙張的大??；（2）設置打印頁面的頁邊距（左邊距、右邊距、上邊距、下邊距、頁眉邊距、頁角邊距以及頁面的顯示方）設置打印頁面的頁邊距（左邊距、右邊距、上邊距、下邊距、頁眉邊距、頁角邊距以及頁面的顯示方式（水平居中和垂直居中式（水平居中和垂直居中);（3）設置頁眉頁角；）設置頁眉頁角；（4

25、）設置打印區(qū)域、打印頂端標題行等；）設置打印區(qū)域、打印頂端標題行等；（5）在打印時替換錯誤值，如果不想打印出工作表中的錯誤，請選中該工作表。）在打印時替換錯誤值，如果不想打印出工作表中的錯誤，請選中該工作表。在在“文件文件”菜單上，單擊菜單上，單擊“頁面設置頁面設置”，再單擊，再單擊“工作表工作表”選項卡。選項卡。在在“錯誤單元格打印為錯誤單元格打印為”框之下，單擊框之下，單擊“”、“-”或或“#N/A”。打印工作表打印工作表ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓打印工作表之前，需要對工作表的頁面進行設置，單擊菜單文件下的頁面設置，如圖打印工作表之前，需要對工

26、作表的頁面進行設置，單擊菜單文件下的頁面設置，如圖頁面選項卡：可以進行方向、縮放、紙張大小的設定。頁面選項卡：可以進行方向、縮放、紙張大小的設定。打印工作表打印工作表ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓頁邊距選項卡：可以對打印表格頁邊距進行設置。頁邊距選項卡：可以對打印表格頁邊距進行設置。打印工作表打印工作表ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓頁眉頁眉/頁腳選項卡：可以對打印文件設置頁眉、頁腳。頁腳選項卡：可以對打印文件設置頁眉、頁腳。我們可以通過單擊自定義頁眉或自定義頁腳來進行自定義頁眉、頁腳。我們可以通過單擊自定義頁眉

27、或自定義頁腳來進行自定義頁眉、頁腳。打印工作表打印工作表ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓工作表選項卡：可以設置要打印的區(qū)域，默認是全部區(qū)域。如果是多頁打印，可以將表頭部分設置為工作表選項卡：可以設置要打印的區(qū)域，默認是全部區(qū)域。如果是多頁打印，可以將表頭部分設置為“打印標題打印標題”，這樣便可以在每頁出現(xiàn)標題內(nèi)容。，這樣便可以在每頁出現(xiàn)標題內(nèi)容。打印工作表打印工作表ExcelExcel基礎知識基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓數(shù)據(jù)的篩選數(shù)據(jù)的篩選請使用若要查找?（問號）任何單個字符例如，sm?th 查找“smith”和“smyth”*（星號）任

28、何字符數(shù)例如，*east 查找“Northeast”和“Southeast”（波形符）后跟 ?、* 或 問號、星號或波形符例如，“fy91?”將會查找“fy91?”通配符通配符以下通配符可作為篩選以及查找和替換內(nèi)容時的比較條件:ExcelExcel函數(shù)培訓函數(shù)培訓數(shù)據(jù)的篩選數(shù)據(jù)的篩選1、當我們的數(shù)據(jù)源在不斷增減變化時，我們?nèi)绾胃鶕?jù)預定的條件篩選出我們需要的數(shù)據(jù)？2、如何讓其實現(xiàn)自動化？引申：引申：1 1、動態(tài)自定義名稱、動態(tài)自定義名稱2 2、錄制宏修改宏代碼、錄制宏修改宏代碼 - -運用函數(shù)建立模型之精華運用函數(shù)建立模型之精華ExcelExcel函數(shù)培訓函數(shù)培訓函數(shù)基礎知識函數(shù)基礎知識函數(shù)功能

29、函數(shù)功能Excel函數(shù):指在Excel中預定義的內(nèi)置公式,可以進行數(shù)學、文本和邏輯的運算或者查找工作表信息，是一個已經(jīng)提供給用戶的公式。為什么要使用Excel函數(shù)？提高工作效率，確保數(shù)據(jù)的及時性在日常工作中，有很多工作屬于周而復始的工作，規(guī)律性較強。此時，我們需要系統(tǒng)的思考,對工作流程進行思考，形成一個系統(tǒng)的工作方法。Excel的使用也不例外；分析數(shù)據(jù)源，理清思路，明確想要達到的目的，建立數(shù)據(jù)模型，一勞永逸，提升工作效率。提高數(shù)據(jù)的準確性、完整性Excel函數(shù)一旦建立，在各類數(shù)據(jù)規(guī)范的前提下，數(shù)據(jù)計算精準。而且，通過查看函數(shù)，可以了解整個數(shù)據(jù)計算的全過程，無需修改數(shù)據(jù)源，確保了數(shù)據(jù)的完整性，同

30、時，也明確了各方的責任。同類計算，公式的通用性靈活運用混合引用，同類計算只需編寫一個公式，利用填充功能即可快速完成其他公式的編寫。且修改便利。ExcelExcel函數(shù)培訓函數(shù)培訓函數(shù)結(jié)構函數(shù)結(jié)構函數(shù)名稱：如果要查看可用函數(shù)的列表函數(shù)名稱：如果要查看可用函數(shù)的列表, ,可以單擊一個單元格并按可以單擊一個單元格并按Shift+F3Shift+F3鍵；鍵；參數(shù)：參數(shù)可以是數(shù)字、文本、邏輯值（參數(shù)：參數(shù)可以是數(shù)字、文本、邏輯值（TRUETRUE或或FALSEFALSE）、數(shù)值、錯誤值（如）、數(shù)值、錯誤值（如#N/A#N/A）或單元格引用。或單元格引用。參數(shù)工具提示：在輸入函數(shù)時參數(shù)工具提示：在輸入函數(shù)

31、時, ,會出現(xiàn)一個帶有語法和參數(shù)的工具提示。會出現(xiàn)一個帶有語法和參數(shù)的工具提示。輸入公式：輸入公式：“插入函數(shù)插入函數(shù)”對話框。對話框。函數(shù)的參數(shù)函數(shù)的參數(shù)函數(shù)的參數(shù)是一個函數(shù)體中括號內(nèi)的部分函數(shù)的參數(shù)是一個函數(shù)體中括號內(nèi)的部分函數(shù)基礎知識函數(shù)基礎知識ExcelExcel函數(shù)培訓函數(shù)培訓使用公式使用公式公式中的運算符公式中的運算符算術運算符算術運算符算術運算符號 名稱用途示例+加號加3+3-減號“減”以及表示負數(shù)。31*星號乘3*3/斜杠除3月3日%百分號百分比20%脫字符乘方32（與3*3相同）ExcelExcel函數(shù)培訓函數(shù)培訓使用公式使用公式公式中的運算符公式中的運算符2.文本運算符文本

32、運算符“&”號號,可以將文本連接起來可以將文本連接起來.3.比較運算符比較運算符比較運算符名稱用途示例=等號等于A1=B1大于號大于A1B1小于號小于A1=大于等于號大于等于A1=B1=小于等于號小于等于A1=B1不等于不等于A1B1ExcelExcel函數(shù)培訓函數(shù)培訓使用公式使用公式公式中的運算符公式中的運算符4.引用運算符引用運算符引用運算符引用運算符運算符含義運算符含義示例示例:區(qū)域運算符區(qū)域運算符,產(chǎn)生對包產(chǎn)生對包括在丙相引用之間的所括在丙相引用之間的所有單元格的引用有單元格的引用(A5:A15),聯(lián)合運算符聯(lián)合運算符,將多個引將多個引用合并為一個引用用合并為一個引用SUM(A

33、5:A15,C5:C15)(空格空格)交叉運算符交叉運算符,產(chǎn)生對兩產(chǎn)生對兩個引用構有的單元格的個引用構有的單元格的引用引用(B7:D7C6:C8)例:A4=B4+C4+C4+E4+F4,可寫為:A4=SUM(B4:F4)ExcelExcel函數(shù)培訓函數(shù)培訓3、Excel函數(shù)的類型函數(shù)的類型Excel中共有中共有10類函數(shù)（包括自定義函數(shù)），分別為：財務函類函數(shù)（包括自定義函數(shù)），分別為：財務函數(shù)、日期與時間函數(shù)、數(shù)學與統(tǒng)計函數(shù)、查找與引用函數(shù)、數(shù)據(jù)庫數(shù)、日期與時間函數(shù)、數(shù)學與統(tǒng)計函數(shù)、查找與引用函數(shù)、數(shù)據(jù)庫函數(shù)、文本函數(shù)、邏輯函數(shù)、信息函數(shù)、自定義函數(shù)，其中：日期函數(shù)、文本函數(shù)、邏輯函數(shù)、信

34、息函數(shù)、自定義函數(shù)，其中：日期與時間函數(shù)、查找與引用函數(shù)、文本函數(shù)和邏輯函數(shù)使用頻率高。與時間函數(shù)、查找與引用函數(shù)、文本函數(shù)和邏輯函數(shù)使用頻率高。在日常工作中需要重點掌握的幾個函數(shù)：在日常工作中需要重點掌握的幾個函數(shù)：IF函數(shù)、函數(shù)、SUM函數(shù)、函數(shù)、SUMIF函數(shù)、函數(shù)、VLOOKUP函數(shù)、函數(shù)、MATCH函數(shù)、函數(shù)、INDEX函數(shù)、函數(shù)、SUMPRODUCT函數(shù)等。函數(shù)等。ExcelExcel函數(shù)培訓函數(shù)培訓ExcelExcel常用函數(shù)介紹簡單加減乘除應用常用函數(shù)介紹簡單加減乘除應用 A A A A A A A A已已知知p p, ,n n, ,I I, ,求求A A= =？等等額額本本息

35、息還還款款額額的的計計算算每月償還額1208=PMT(B5/12,B4*12,-B3)貸款額120000每月償還額1208=B3*B5/12/(1-(1+B5/12)(-B4*12)貸款年限10累計償還額144905貸款利率3.870%本金120000利息249051、簡單加、減、乘、除等在、簡單加、減、乘、除等在Excel中的應用中的應用應用一：零存整取下應用一：零存整取下N年后可以獲得的現(xiàn)金？年后可以獲得的現(xiàn)金？計算公式：計算公式：F=A*(F/A,i,n)=A*(1+i)n-1)/i應用二：等額本息下分期付款額的計算及還款計劃表的編制應用二：等額本息下分期付款額的計算及還款計劃表的編制計

36、算公式：計算公式：A=p*(A/p,i,n)=p*i/(1-(1+i)(-n)在日常理財與工作中，很多計算性的問題都可以用類似簡單的函數(shù)解決。我們最重要的任務是確定其計算在日常理財與工作中，很多計算性的問題都可以用類似簡單的函數(shù)解決。我們最重要的任務是確定其計算方法與建立計算模型！方法與建立計算模型！同樣的問題既可以利同樣的問題既可以利用簡單的加減乘除解決，用簡單的加減乘除解決，也可以利用也可以利用Excel中中財務函數(shù)解決！財務函數(shù)解決！ A A A A A A A A已知A,n,I,求F=？已知A,n,I,求F=？ExcelExcel函數(shù)培訓函數(shù)培訓ExcelExcel常用函數(shù)介紹常用函數(shù)

37、介紹- -邏輯函數(shù)邏輯函數(shù)1、IF函數(shù)，語法如下：函數(shù)，語法如下：應用一：成績考評應用一：成績考評ExcelExcel函數(shù)培訓函數(shù)培訓方法二：方法二：Vlookup函數(shù)函數(shù)應用二：個人所得稅的計算應用二：個人所得稅的計算級級 數(shù)數(shù)上上 限限全全 月月 應應 納納 稅稅 所所 得得 額額稅稅 率率（ ）速速 算算 扣扣 除除 數(shù)數(shù)10不超過500元的5%02500超過500元至2000元的部分10%2532000超過2000元至5000元的部分15%12545000超過5000元至20000元的部分20%375520000超過20000元至40000元的部分25%1375640000超過4000

38、0元至60000元的部分30%3375760000超過60000元至80000元的部分35%6375880000超過80000元至100000元的部分40%103759100000超過100000元的部分45%15375=IF($N18=500,$N18*$J$4-$K$4,IF($N18=2000,$N18*$J$5-$K$5,IF($N18=5000,$N18*$J$6-$K$6,IF($N18=20000,$N18*$J$7-$K$7,IF($N18=40000,$N18*$J$8-$K$8,IF($N18=60000,$N18*$J$9-$K$9,IF($N18=80000,$N18*

39、$J$10-$K$10,IF($N18=60)*(a1212,1=3,0)請回答下面四個問題：請回答下面四個問題：題四：如果題四：如果a1a1單元格的值大于等于單元格的值大于等于6060或者小于或者小于5050，那么返回正確，否則返回錯誤。，那么返回正確，否則返回錯誤。如何書寫該公式？如何書寫該公式？引申：=(a160)+(a150)表示何意？ExcelExcel常用函數(shù)介紹常用函數(shù)介紹- -邏輯函數(shù)邏輯函數(shù)ExcelExcel函數(shù)培訓函數(shù)培訓ExcelExcel常用函數(shù)介紹常用函數(shù)介紹- -求和函數(shù)求和函數(shù)1、SUM函數(shù)，求和函數(shù)。記住快捷鍵以及聯(lián)合引用函數(shù)，求和函數(shù)。記住快捷鍵以及聯(lián)合引用

40、2、SUMIF函數(shù)單條件求和函數(shù)單條件求和產(chǎn)品名稱產(chǎn)品名稱銷售單價銷售單價銷售數(shù)量銷售數(shù)量銷售額銷售額A10.5200021000B11.5210024150C12.5220027500D13.5230031050E14.5240034800F15.6250039000A16.7210035070B17.8220039160C18.5230042550E E14145000050000700000700000附表：某公司附表：某公司1 1月份各產(chǎn)品的銷售記錄月份各產(chǎn)品的銷售記錄思考：思考：1 1、如何根據(jù)附表編制銷售報表？、如何根據(jù)附表編制銷售報表？ 2 2、新增產(chǎn)品或者某產(chǎn)品停產(chǎn)，又如何編制

41、銷、新增產(chǎn)品或者某產(chǎn)品停產(chǎn)，又如何編制銷 售報表？即如何使銷售報表自動化？售報表？即如何使銷售報表自動化？產(chǎn)品名稱產(chǎn)品名稱銷售數(shù)量銷售數(shù)量銷售額銷售額平均單價平均單價A41004100560705607013.6813.68B43004300633106331014.7214.72C45004500700507005015.5715.57D23002300310503105013.5013.50E524005240073480073480014.0214.02F25002500390003900015.6015.60合計合計701007010099428099428014.1814.18某公司

42、某公司1 1月份產(chǎn)品銷售報表月份產(chǎn)品銷售報表ExcelExcel函數(shù)培訓函數(shù)培訓= =S SU UM MI IF F( (r ra an ng ge e, ,c cr ri it te er ri ia a, ,s su um m_ _r ra an ng ge e) )第第一一個個參參數(shù)數(shù)：R Ra an ng ge e表表示示條條件件所所在在區(qū)區(qū)域域第第二二個個參參數(shù)數(shù)：條條件件第第三三個個參參數(shù)數(shù)：求求和和區(qū)區(qū)域域3 3、SumifSumif函數(shù)函數(shù)(1)(1)函數(shù)功能：單一條件求和函數(shù)功能：單一條件求和(2)(2)語法：語法：ExcelExcel常用函數(shù)介紹常用函數(shù)介紹- -求和函數(shù)求

43、和函數(shù)ExcelExcel函數(shù)培訓函數(shù)培訓ExcelExcel常用函數(shù)介紹常用函數(shù)介紹- -求和函數(shù)求和函數(shù)4 4、SUMPRODUCTSUMPRODUCT函數(shù)函數(shù)(1)(1)函數(shù)功能：在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘，并返回乘積之和。函數(shù)功能：在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘，并返回乘積之和。(2)(2)語法：語法：SUMPRODUCT(array1,array2,array3,.)SUMPRODUCT(array1,array2,array3,.)ExcelExcel函數(shù)培訓函數(shù)培訓數(shù)據(jù)源根據(jù)編號查找姓名編號姓名編號031101031101張三姓名張三031102李四=

44、VLOOKUP(E83,$B$83:$C$86,2,)031103王五=VLOOKUP(E83,$B$83:$C$86,2,FALSE)1、vlookup函數(shù)函數(shù)(2)語法語法在學習使用函數(shù)時，要學會按在學習使用函數(shù)時，要學會按F1查看幫助！查看幫助！(1)函數(shù)功能：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。函數(shù)功能：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。ExcelExcel常用函數(shù)介紹常用函數(shù)介紹- -查找函數(shù)查找函數(shù)ExcelExcel函數(shù)培訓函數(shù)培訓2、Match函數(shù)函數(shù)(1)函數(shù)功能：查找某一數(shù)據(jù)函數(shù)功

45、能：查找某一數(shù)據(jù)lookup_value在某數(shù)據(jù)區(qū)域在某數(shù)據(jù)區(qū)域(單行或者單列或者一維數(shù)組單行或者單列或者一維數(shù)組lookup_array)中第一次出現(xiàn)的位置中第一次出現(xiàn)的位置(2)語法：語法：ExcelExcel常用函數(shù)介紹常用函數(shù)介紹- -查找函數(shù)查找函數(shù)ExcelExcel函數(shù)培訓函數(shù)培訓3、offset函數(shù)函數(shù)(1)函數(shù)功能：以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單函數(shù)功能：以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。(2)語法：語法：

46、(3)如何根據(jù)指定的參照系返回一個值？如何根據(jù)指定的參照系返回一個值？Offset函數(shù)后面的四個參數(shù)均函數(shù)后面的四個參數(shù)均可以使用變量、函數(shù)來代替！可以使用變量、函數(shù)來代替！ExcelExcel常用函數(shù)介紹常用函數(shù)介紹- -查找函數(shù)查找函數(shù)ExcelExcel函數(shù)培訓函數(shù)培訓4、Match與與offset函數(shù)組合運用函數(shù)組合運用(5)Sum、Match與與offset函數(shù)組合運用函數(shù)組合運用會計科目會計科目名稱1月2月3月4月5月6月7月8月9月10月11月 12月合計1002銀行存款510 520 530 540 550 560 570 580 590 600 610 620 6,780 1

47、012其他貨幣資金31 32 33 34 35 36 37 38 39 40 41 42 438 求求 某某 期期 間間 科科 目目 的的 發(fā)發(fā) 生生 額額科目代碼1002起始月份3終止月份5發(fā)生額1620= =S SU UM M( (O OF FF FS SE ET T( ($ $A A$ $1 1, ,M MA AT TC CH H( ($ $B B$ $6 6, ,$ $A A: :$ $A A, ,F FA AL LS SE E) )- -1 1, ,$ $B B$ $7 7+ +1 1, ,1 1, ,$ $B B$ $8 8- -$ $B B$ $7 7+ +1 1) ) )ExcelExcel常用函數(shù)介紹常用函數(shù)介紹- -查找函數(shù)查找函數(shù)ExcelExcel函數(shù)培訓函數(shù)培訓ExcelExcel函數(shù)常見錯