Office辦公應(yīng)用立體化教程課件

項目六?Excel數(shù)據(jù)計算與管理新形態(tài)立體化精品系列教材Office辦公應(yīng)用立體化教程（Office2016）（微課版）任務(wù)一計算“員工銷售業(yè)績獎金”工作簿任務(wù)二登記并管理“生產(chǎn)記錄表”工作簿內(nèi)容導(dǎo)航（一）使用公式計算數(shù)據(jù)（二）引用單元格 （三）使用函數(shù)計算數(shù)據(jù) 項目實(shí)訓(xùn)課后練習(xí)技巧提升任務(wù)三管理“日常費(fèi)用統(tǒng)計表”工作簿一、任務(wù)描述每月月末，公司銷售部門都需要總結(jié)出銷售人員當(dāng)月的銷售業(yè)績，而米拉的工作則是根據(jù)這些銷售業(yè)績，計算出對應(yīng)的獎金，制作出“員工銷售業(yè)績獎金”表格。在公司普及自動化辦公的當(dāng)下，老洪要求米拉使用Excel表格代替計算器進(jìn)行計算。經(jīng)過不懈努力，米拉通過Excel自帶的公式和函數(shù)功能實(shí)現(xiàn)了各類數(shù)據(jù)的計算，成功掌握了更高級的計算方法。本例完成后的參考效果如下圖所示。二、任務(wù)實(shí)施Excel中的公式是對工作表中的數(shù)據(jù)進(jìn)行計算和操作的等式，它以等號“=”開始，其后是公式的表達(dá)式，如“=A1+A2*A3/SUM(A3:A10)”。公式的表達(dá)式包含了運(yùn)算符（如“+”“/”“&”“，”等）、數(shù)值或任意字符串，以及函數(shù)和單元格引用等元素。（一）使用公式計算數(shù)據(jù)（一）使用公式計算數(shù)據(jù)通常通過在單元格中輸入實(shí)現(xiàn)計算功能。例如，在“銷售數(shù)據(jù)統(tǒng)計”表中選擇F3單元格，輸入等號“=”，然后選擇D3單元格，引用其中的數(shù)據(jù)，并輸入運(yùn)算符“*”，將其作為公式表達(dá)式中的部分元素，繼續(xù)選擇E3單元格，引用其中的數(shù)據(jù)。1.輸入公式（一）使用公式計算數(shù)據(jù)復(fù)制與填充公式是快速計算同類數(shù)據(jù)的最佳方法，將鼠標(biāo)光標(biāo)移到該單元格右下角的填充柄上，當(dāng)鼠標(biāo)光標(biāo)變成+形狀時，按住鼠標(biāo)左鍵不放拖動至目標(biāo)單元格，即可快速填充公式。2.復(fù)制與填充公式（二）引用單元格絕對引用混合引用引用不同工作簿中的單元格引用同一工作簿中其他工作表的單元格引用單元格的主要方法指相對于公式單元格位于某一位置處的單元格引用。指把公式復(fù)制或移動到新位置后，公式中的單元格地址保持不變。指在一個單元格地址引用中，既有絕對引用，又有相對引用。工作簿中包含多個工作表，在其中一張工作表引用該工作簿中其他工作表中的數(shù)據(jù)。相對引用對不同工作簿中的單元格進(jìn)行引用，可使用“‘工作簿存儲地址[工作簿名稱]工作表名稱’!單元格地址”的方法來進(jìn)行引用。（三）使用函數(shù)計算數(shù)據(jù)

當(dāng)在工作表中對所使用的函數(shù)和參數(shù)類型都很熟悉時，可直接輸入函數(shù)，在編輯欄中單擊“插入函數(shù)”按鈕，打開“插入函數(shù)”對話框，選擇并插入所需函數(shù)。1.輸入函數(shù)（三）使用函數(shù)計算數(shù)據(jù)函數(shù)嵌套的方法是將某一函數(shù)或公式作為另一個函數(shù)的參數(shù)來計算。例如，嵌套函數(shù)“=IF(B3>50000,IF(B3=MAX($B$3:$B$17),”2000“,”“),”“)”，表示使用邏輯函數(shù)IF和最大值函數(shù)MAX進(jìn)行計算。2.嵌套函數(shù)（三）使用函數(shù)計算數(shù)據(jù)除了上面介紹的求和函數(shù)SUM，查找函數(shù)HLOOKUP，邏輯函數(shù)IF，還有一些在辦公中經(jīng)常使用的函數(shù)，如最大值函數(shù)MAX最小值函數(shù)MIN，平均值函數(shù)AVERAGE，統(tǒng)計函數(shù)COUNTIF函數(shù)。3.其他常用辦公函數(shù)介紹任務(wù)一計算“員工銷售業(yè)績獎金”工作簿任務(wù)二登記并管理“生產(chǎn)記錄表”工作簿內(nèi)容導(dǎo)航（一）使用記錄單輸入數(shù)據(jù)（二）數(shù)據(jù)篩選項目實(shí)訓(xùn)課后練習(xí)技巧提升任務(wù)三管理“日常費(fèi)用統(tǒng)計表”工作簿一、任務(wù)描述公司在產(chǎn)品生產(chǎn)數(shù)據(jù)的管理方面效率一直不太高，為了有效地整理并查找所需的產(chǎn)品生產(chǎn)數(shù)據(jù)，公司希望米拉找到方法快速登記并管理“生產(chǎn)記錄表”，要求能快速并準(zhǔn)確地輸入數(shù)據(jù)，并根據(jù)公司的指定條件篩選數(shù)據(jù)。米拉正一籌莫展之際，老洪讓她嘗試使用Excel的記錄單和數(shù)據(jù)篩選功能。米拉在Excel中不斷地進(jìn)行嘗試，最終找到優(yōu)化方法，高效率地完成了“生產(chǎn)記錄表”數(shù)據(jù)的登記和管理，效果如下圖所示。二、任務(wù)實(shí)施如果數(shù)據(jù)表比較龐大，數(shù)據(jù)記錄條數(shù)較多，使用記錄單輸入數(shù)據(jù)就特別方便。（一）使用記錄單輸入數(shù)據(jù)（一）使用記錄單輸入數(shù)據(jù)記錄單是用來詳細(xì)記錄所需資料的單據(jù)，它可以幫助用戶在一個小窗口中完成輸入數(shù)據(jù)的工作。使用之前可以打開“Excel選項”對話框，將“記錄單”選項添加至快速訪問工具欄中，以便于調(diào)用。（二）數(shù)據(jù)篩選自動篩選數(shù)據(jù)是根據(jù)用戶設(shè)定的篩選條件，自動將表格中符合條件的數(shù)據(jù)顯示出來，而將表格中的其他數(shù)據(jù)隱藏。單擊“數(shù)據(jù)”選項卡，在“排序和篩選”組中單擊“篩選”按鈕，在工作表中每個表頭數(shù)據(jù)對應(yīng)的單元格右側(cè)將出現(xiàn)下拉按鈕，單擊下拉按鈕，選擇或設(shè)置篩選條件。1.自動篩選（二）數(shù)據(jù)篩選自定義篩選即在自動篩選后的需自定義的字段名右側(cè)單擊按鈕，在打開的下拉列表中選擇相應(yīng)的選項，確定篩選條件后，在打開的“自定義自動篩選方式”對話框中進(jìn)行相應(yīng)的設(shè)置。在要篩選字段名右側(cè)單擊下拉按鈕，在彈出的列表中選擇如“日期篩選”選項，在子列表中選擇“自定義篩選”選項，在打開的“自定義自動篩選方式”對話框中設(shè)置參數(shù)。2.自定義篩選（二）數(shù)據(jù)篩選高級篩選功能可以篩選出同時滿足兩個或兩個以上約束條件的記錄。單擊“數(shù)據(jù)”選項卡，在“排序和篩選”組中單擊“高級”按鈕，在打開的“高級篩選”對話框的“列表區(qū)域”文本框中將自動選擇參與篩選的單元格區(qū)域。3.高級篩選任務(wù)一計算“員工銷售業(yè)績獎金”工作簿任務(wù)二登記并管理“生產(chǎn)記錄表”工作簿內(nèi)容導(dǎo)航（一）數(shù)據(jù)排序 （二）分類匯總 （三）定位選擇與分列顯示數(shù)據(jù)項目實(shí)訓(xùn)課后練習(xí)技巧提升任務(wù)三管理“日常費(fèi)用統(tǒng)計表”工作簿一、任務(wù)描述隨著公司的日益發(fā)展，日常辦公開支項目和費(fèi)用不斷增多，公司決定從下半年開始制作費(fèi)用統(tǒng)計表，記錄日常辦公費(fèi)用的支出項目和經(jīng)費(fèi)。為了查看費(fèi)用支出的總和等情況，老洪讓米拉對“日常費(fèi)用統(tǒng)計表”進(jìn)行管理，要求她先對相應(yīng)的數(shù)據(jù)進(jìn)行排序，再根據(jù)排序結(jié)果分類匯總，然后定位選擇數(shù)據(jù)，并分列顯示數(shù)據(jù)。本例完成后的參考效果如下圖所示。二、任務(wù)實(shí)施數(shù)據(jù)排序常用于統(tǒng)計工作中，在Excel中，數(shù)據(jù)排序是指根據(jù)存儲在表格中數(shù)據(jù)的類型，將其按一定的方式重新排列。數(shù)據(jù)排序有助于用戶快速直觀地查看數(shù)據(jù)，更好地理解數(shù)據(jù)，更方便地組織并查找所需數(shù)據(jù)。數(shù)據(jù)排序的常用方法有自動排序和按關(guān)鍵字排序。（一）數(shù)據(jù)排序（一）數(shù)據(jù)排序自動排序是數(shù)據(jù)排序管理中最基本的一種排序方式，選擇該方式系統(tǒng)將自動對數(shù)據(jù)進(jìn)行識別并進(jìn)行排序。方法是單擊“數(shù)據(jù)”選項卡，在“排序和篩選”組中單擊“升序”或“降序”按鈕。1.自動排序（一）數(shù)據(jù)排序按關(guān)鍵字的方式排序，可根據(jù)指定的關(guān)鍵字對某個字段（列單元格）或多個字段對數(shù)據(jù)進(jìn)行排序，通?？蓪⒃摲绞椒譃榘磫蝹€關(guān)鍵字排序與按多個關(guān)鍵字排序。單擊“數(shù)據(jù)”選項卡，在“排序和篩選”組中單擊“排序”按鈕，在打開的“排序”對話框中設(shè)置主要關(guān)鍵字和次要關(guān)鍵字。2.按關(guān)鍵字排序（二）分類匯總

Excel的數(shù)據(jù)分類匯總功能是用于將性質(zhì)相同的數(shù)據(jù)匯總到一塊，以便使表格的結(jié)構(gòu)更清晰，使用戶能更好地掌握表格中重要的信息。單擊“數(shù)據(jù)”選項卡，在“分級顯示”組中單擊“分類匯總”按鈕分類匯總”按鈕，打開“分類匯總”對話框，設(shè)置匯總方式等參數(shù)。（三）定位選擇與分列顯示數(shù)據(jù)定位是一種選擇單元格的方式，主要用來選擇位置相對無規(guī)則但條件有規(guī)則的單元格或單元格區(qū)域。在“開始”選項卡的“編輯”組中單擊“查找和選擇”按鈕，在彈出的列表中選擇“定位條件”選項，在打開的“定位條件”對話框中選擇條件。1.定位選擇數(shù)據(jù)（三）定位選擇與分列顯示數(shù)據(jù)在一些特殊情況下需要使用Excel的分列功能快速將一列中的數(shù)據(jù)分列顯示，如將日期以月與日分列顯示、將姓名以姓與名分列顯示等。單擊“數(shù)據(jù)”選項卡，在“數(shù)據(jù)工具”組中單擊“分列”按鈕，在打開的“文本分列向?qū)?第1步，共3步”對話框中根據(jù)提示進(jìn)行操作。2.分列顯示數(shù)據(jù)任務(wù)一計算“員工銷售業(yè)績獎金”工作簿任務(wù)二登記并管理“生產(chǎn)記錄表”工作簿內(nèi)容導(dǎo)航一、制作“員工工資表”工作簿二、管理“樓盤銷售信息表”工作簿項目實(shí)訓(xùn)課后練習(xí)技巧提升任務(wù)三管理“日常費(fèi)用統(tǒng)計表”工作簿一、制作“員工工資表”工作簿本實(shí)訓(xùn)的目標(biāo)是制作員工工資表，需要在表格中使用公式計算實(shí)發(fā)工資和稅后工資、使用自動求和功能計算應(yīng)領(lǐng)工資和應(yīng)扣工資，以及使用IF嵌套函數(shù)計算個人所得稅。本實(shí)訓(xùn)的最終效果如下圖所示。1.實(shí)訓(xùn)目標(biāo)一、制作“員工工資表”工作簿員工工資通常分為固定工資、浮動工和福利3部分，其中固定工資是不變的，而浮動工資和福利會隨著時間或員工表現(xiàn)而改變。不同的公司制定的員工工資管理制度不同，員工工資項目也不相同，因此應(yīng)結(jié)合實(shí)際情況計算員工工資。2.專業(yè)背景一、制作“員工工資表”工作簿完成本實(shí)訓(xùn)首先在F5:F20和J5:J20單元格區(qū)域中使用SUM函數(shù)計算應(yīng)領(lǐng)工資和應(yīng)扣工資，然后使用公式計算實(shí)發(fā)工資，最后使用IF函數(shù)計算個人所得稅并輸入公式計算稅后工資。3.操作思路二、管理“樓盤銷售信息表”工作簿對樓盤數(shù)據(jù)進(jìn)行排序、篩選開盤均價大于等于10000元的記錄。。本實(shí)訓(xùn)的最終效果如下圖所示。1.實(shí)訓(xùn)目標(biāo)二、管理“樓盤銷售信息表”工作簿樓盤銷售信息表具有針對性、引導(dǎo)性和參考價值，在制作時要包括開發(fā)公司名稱、樓盤位置、開盤價格以及銷售狀況等信息，從中可看出哪一類房產(chǎn)賣得好，受到客戶的歡迎，從而制定有針對性銷售策略。2.專業(yè)背景二、管理“樓盤銷售信息表”工作簿完成本實(shí)訓(xùn)首先對“開發(fā)公司”按筆畫降序排列，然后篩選出開盤均價大于等于10000元的記錄，最后將記錄按“開發(fā)公司”分類，并匯總“開盤均價”和“已售”項的最大值，其操作思路如下圖所示。3.操作思路任務(wù)一計算“員工銷售業(yè)績獎金”工作簿任務(wù)二登記并管理“生產(chǎn)記錄表”工作簿內(nèi)容導(dǎo)航1.制作“學(xué)生成績匯總表”工作簿 2.管理“區(qū)域銷售匯總表”工作簿項目實(shí)訓(xùn)課后練習(xí)技巧提升任務(wù)三管理“日常費(fèi)用統(tǒng)計表”工作簿1.?制作“學(xué)生成績匯總表”工作簿打開素材文件“學(xué)生成績匯總表.xlsx”工作簿，使用公式和函數(shù)計算相關(guān)成績，完成后的效果如下圖所示。2.?管理“區(qū)域銷售匯總表”工作簿下面將打開“區(qū)域銷售匯總表”工作簿，使用記錄單輸入相應(yīng)的數(shù)據(jù)內(nèi)容，然后對相應(yīng)數(shù)據(jù)進(jìn)行排序和匯總，參考效果如下圖所示。任務(wù)一計算“員工銷售業(yè)績獎金”工作簿任務(wù)二登記并管理“生產(chǎn)記錄表”工作簿內(nèi)容導(dǎo)航1.用NOW函數(shù)顯示當(dāng)前日期和時間 2.用MID函數(shù)從身份證號碼中提取出生日期3.用COUNT函數(shù)統(tǒng)計單元格數(shù)量4.用COUNTIFS函數(shù)按多條件進(jìn)行統(tǒng)計5.相同數(shù)據(jù)排名 項目實(shí)訓(xùn)課后練習(xí)技巧提升任務(wù)三管理“日常費(fèi)用統(tǒng)計表”工作簿技巧提升

NOW函數(shù)可以返回計算機(jī)系統(tǒng)內(nèi)部時鐘的當(dāng)前日期和時間。其語法結(jié)構(gòu)為：NOW()，沒有參數(shù)，并且如果包含公式的單元格格式設(shè)置不同，則返回的日期和時間的格式也不相同。其方法為：在工作簿中選擇目標(biāo)單元格，輸入“=NOW()”，按【Enter】鍵即可顯示計算機(jī)系統(tǒng)當(dāng)前的日期和時間。1.用NOW函數(shù)顯示當(dāng)前日期和時間技巧提升MID函數(shù)用于返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。其語法結(jié)構(gòu)為：MID(text,start_num,num_chars)，各參數(shù)含義分別是，text是包含要提取字符的文本字符串；start_num是文本中要提取的第一個字符的位置，文本中第一個字符的start_num為1，以此類推；num_chars指定希望MID從文本中返回字符的個數(shù)。2.用MID函數(shù)從身份證號碼中提取出生日期技巧提升COUNT函數(shù)用于只返回包含數(shù)字單元格的個數(shù)。同時還可以計算單元格區(qū)域或數(shù)字?jǐn)?shù)組中數(shù)字字段的輸入項個數(shù)，空白單元格或文本單元格將不計算在內(nèi)。其語法結(jié)構(gòu)為：COUNT(value1,value2,...)，其中參數(shù)value1，value2，...是可以包含或引用各種類型數(shù)據(jù)的1到255個參數(shù)，但只有數(shù)字類型的數(shù)據(jù)才計算在內(nèi)。3.用COUNT函