大學(xué)計(jì)算機(jī)基礎(chǔ)（第4版） 課件 項(xiàng)目8 計(jì)算和分析Excel數(shù)據(jù)

計(jì)算和分析Excel數(shù)據(jù)項(xiàng)目八大學(xué)計(jì)算機(jī)基礎(chǔ)（第4版）（微課版）制作工作考核表統(tǒng)計(jì)分析員工績(jī)效表目錄制作銷售分析表任務(wù)一任務(wù)二任務(wù)三使用求和函數(shù)SUM計(jì)算總分。使用平均值函數(shù)AVERAGE計(jì)算平均分。使用最大值函數(shù)MAX和最小值函數(shù)MIN求出考核的最高分和最低分。使用排名函數(shù)

RANK

計(jì)算考核總分排名。使用IF嵌套函數(shù)判斷考核成績(jī)是否合格。使用INDEX函數(shù)查詢“陳銳”的領(lǐng)導(dǎo)能力和“周曉梅”的工作態(tài)度。任務(wù)要求任務(wù)要求制作工作考核表任務(wù)一相關(guān)知識(shí)相關(guān)知識(shí)制作工作考核表任務(wù)一（一）公式運(yùn)算符和語法（二）單元格引用及其分類（三）使用公式計(jì)算數(shù)據(jù)（四）Excel中的常用函數(shù)（一）公式運(yùn)算符和語法1.運(yùn)算符運(yùn)算符即公式中的運(yùn)算符號(hào)，用于對(duì)公式中的元素進(jìn)行特定計(jì)算。運(yùn)算符主要用于連接數(shù)字并產(chǎn)生相應(yīng)的計(jì)算結(jié)果。運(yùn)算符有算術(shù)運(yùn)算符（如加、減、乘、除）、比較運(yùn)算符（如邏輯值FALSE與TRUE）、文本運(yùn)算符（如&）、引用運(yùn)算符（如冒號(hào)與空格）和括號(hào)運(yùn)算符（如()）5種，當(dāng)一個(gè)公式中包含了這5種運(yùn)算符時(shí)，應(yīng)遵循從高到低的優(yōu)先級(jí)進(jìn)行計(jì)算；若公式中還包含括號(hào)運(yùn)算符，則一定要注意每個(gè)左括號(hào)必須配一個(gè)右括號(hào)。（一）公式運(yùn)算符和語法2.語法Excel中的公式是按照特定的順序進(jìn)行數(shù)值運(yùn)算的，這一特定順序即為語法。Excel中的公式遵循特定的語法：最前面是等號(hào)，后面是參與計(jì)算的元素和運(yùn)算符。如果公式中同時(shí)用到了多個(gè)運(yùn)算符，則需按照運(yùn)算符的優(yōu)先級(jí)別進(jìn)行運(yùn)算，如果公式中包含了相同優(yōu)先級(jí)別的運(yùn)算符，則先進(jìn)行括號(hào)里面的運(yùn)算，然后再?gòu)淖蟮接乙来斡?jì)算。（二）單元格引用及其分類1．單元格引用1．單元格引用Excel2016是通過單元格的地址來引用單元格的，單元格地址是指單元格的行號(hào)與列標(biāo)的組合。例如，“=193800+123140+146520+152300”，數(shù)據(jù)“193800”位于B3單元格，其他數(shù)據(jù)依次位于C3、D3和E3單元格中，通過單元格引用，將公式輸入為“=B3+C3+D3+E3”，同樣可以獲得這4個(gè)數(shù)據(jù)的計(jì)算結(jié)果。（二）單元格引用及其分類2．單元格引用分類相對(duì)引用是指輸入公式時(shí)直接通過單元格地址來引用單元格。相對(duì)引用單元格后，如果復(fù)制或剪切公式到其他單元格，那么公式中引用的單元格地址會(huì)根據(jù)復(fù)制或剪切的位置而發(fā)生相應(yīng)改變。絕對(duì)引用是指無論引用單元格的公式的位置如何改變，所引用的單元格均不會(huì)發(fā)生變化。絕對(duì)引用的形式是在單元格的行列號(hào)前加上符號(hào)“$”?；旌弦冒讼鄬?duì)引用和絕對(duì)引用?；旌弦糜袃煞N形式，一種是行絕對(duì)、列相對(duì)，如“B$2”表示行不發(fā)生變化，但是列會(huì)隨著新的位置發(fā)生變化；另一種是行相對(duì)、列絕對(duì)，如“$B2”表示列保持不變，但是行會(huì)隨著新的位置而發(fā)生變化。相對(duì)引用絕對(duì)引用混合引用（三）使用公式計(jì)算數(shù)據(jù)1．輸入公式1．單元格引用在Excel2016中輸入公式的方法為：選擇要輸入公式的單元格，在單元格或編輯欄中輸入“=”，接著輸入公式內(nèi)容，完成后按【Enter】鍵或單擊編輯欄上的“輸入”按鈕即可。在單元格中輸入公式后，按【Enter】鍵可在計(jì)算出公式結(jié)果的同時(shí)選擇同列的下一個(gè)單元格；按【Tab】鍵可在計(jì)算出公式結(jié)果的同時(shí)選擇同行的下一個(gè)單元格；按【Ctrl+Enter】組合鍵則可在計(jì)算出公式結(jié)果后，仍保持當(dāng)前單元格的選擇狀態(tài)。（三）使用公式計(jì)算數(shù)據(jù)2．編輯公式編輯公式與編輯數(shù)據(jù)的方法相同。選擇含有公式的單元格，將插入點(diǎn)定位在編輯欄或單元格中需要修改的位置，按【Backspace】鍵刪除多余或錯(cuò)誤的內(nèi)容，再輸入正確的內(nèi)容。完成后按【Enter】鍵即可完成對(duì)公式的編輯，Excel會(huì)自動(dòng)計(jì)算新公式。（三）使用公式計(jì)算數(shù)據(jù)3．復(fù)制公式1．單元格引用在Excel2016中復(fù)制公式是快速計(jì)算數(shù)據(jù)的最佳方法，因?yàn)樵趶?fù)制公式的過程中，Excel會(huì)自動(dòng)改變引用單元格的地址，可避免手動(dòng)輸入公式的麻煩，提高工作效率。通常使用“開始”選項(xiàng)卡或單擊鼠標(biāo)右鍵進(jìn)行復(fù)制粘貼；也可以通過拖動(dòng)控制柄進(jìn)行復(fù)制；還可選擇添加了公式的單元格，按【Ctrl+C】組合鍵進(jìn)行復(fù)制，然后再將插入點(diǎn)定位到要復(fù)制到的單元格，按【Ctrl+V】組合鍵進(jìn)行粘貼就可完成對(duì)公式的復(fù)制。（四）Excel中的常用函數(shù)Excel2016中提供了多種函數(shù)，每個(gè)函數(shù)的功能、語法結(jié)構(gòu)及其參數(shù)的含義各不相同，常用的函數(shù)有以下幾種。SUM函數(shù)AVERAGE函數(shù)IF函數(shù)MAX/MIN函數(shù)COUNT函數(shù)SIN函數(shù)PMT函數(shù)SUMIF函數(shù)RANK函數(shù)INDEX函數(shù)任務(wù)實(shí)現(xiàn)任務(wù)實(shí)現(xiàn)制作工作考核表任務(wù)一（一）使用求和函數(shù)SUM計(jì)算總分（二）使用平均值函數(shù)AVERAGE計(jì)算平均分（三）使用最大值函數(shù)MAX和最小值函數(shù)MIN計(jì)算極值（四）使用排名函數(shù)RANK計(jì)算名次（五）使用IF嵌套函數(shù)判斷考核成績(jī)是否合格（六）使用INDEX函數(shù)查詢成績(jī)（一）使用求和函數(shù)SUM計(jì)算總分插入求和函數(shù)1自動(dòng)填充總分2（二）使用平均值函數(shù)AVERAGE計(jì)算平均分更改函數(shù)參數(shù)1自動(dòng)填充平均分2（三）使用最大值函數(shù)MAX和最小值函數(shù)MIN計(jì)算極值插入最小值函數(shù)3自動(dòng)填充最低分4選擇“最大值”選項(xiàng)1插入最大值函數(shù)2（四）使用排名函數(shù)RANK計(jì)算名次選擇RANR函數(shù)1設(shè)置函數(shù)參數(shù)2（五）使用IF嵌套函數(shù)判斷考核成績(jī)是否合格選擇LF函數(shù)1設(shè)置判斷條件和返回邏輯值2（六）使用INDEX函數(shù)查詢成績(jī)確認(rèn)函數(shù)的應(yīng)用1選擇參數(shù)2制作工作考核表統(tǒng)計(jì)分析員工績(jī)效表目錄制作銷售分析表任務(wù)一任務(wù)二任務(wù)三打開已經(jīng)創(chuàng)建并編輯完成的員工績(jī)效表，對(duì)其中的數(shù)據(jù)分別進(jìn)行快速排序、組合排序和自定義排序。對(duì)表中的數(shù)據(jù)按照不同的條件進(jìn)行自動(dòng)篩選、自定義篩選和高級(jí)篩選操作，并在表格中使用條件格式。按照不同的設(shè)置字段，為表格中的數(shù)據(jù)創(chuàng)建分類匯總、嵌套分類匯總，然后查看分類匯總的數(shù)據(jù)。創(chuàng)建數(shù)據(jù)透視表，然后創(chuàng)建數(shù)據(jù)透視圖。任務(wù)要求任務(wù)要求統(tǒng)計(jì)分析員工績(jī)效表任務(wù)二相關(guān)知識(shí)相關(guān)知識(shí)統(tǒng)計(jì)分析員工績(jī)效表任務(wù)二（一）數(shù)據(jù)排序（二）數(shù)據(jù)篩選（一）數(shù)據(jù)排序數(shù)據(jù)排序是統(tǒng)計(jì)工作中的一項(xiàng)重要內(nèi)容，Excel中可將數(shù)據(jù)按照指定的順序規(guī)律進(jìn)行排序。一般情況下，數(shù)據(jù)排序分為以下3種情況。單列數(shù)據(jù)排序是指在工作表中以一列單元格中的數(shù)據(jù)為依據(jù)，對(duì)工作表中的所有數(shù)據(jù)進(jìn)行排序。單列數(shù)據(jù)排序在對(duì)多列數(shù)據(jù)進(jìn)行排序時(shí)，需要按某個(gè)數(shù)據(jù)進(jìn)行排列，該數(shù)據(jù)則稱為“關(guān)鍵字”。以關(guān)鍵字進(jìn)行排序，其他列中的單元格數(shù)據(jù)將隨之發(fā)生變化。對(duì)多列數(shù)據(jù)進(jìn)行排序時(shí)，首先需要選擇多列數(shù)據(jù)對(duì)應(yīng)的單元格區(qū)域，然后選擇關(guān)鍵字，排序時(shí)就會(huì)自動(dòng)以該關(guān)鍵字進(jìn)行排序，未選擇的單元格區(qū)域?qū)⒉粎⑴c排序。多列數(shù)據(jù)排序使用自定義排序可以通過設(shè)置多個(gè)關(guān)鍵字對(duì)數(shù)據(jù)進(jìn)行排序，并可以通過其他關(guān)鍵字對(duì)相同的數(shù)據(jù)進(jìn)行排序。自定義排序（二）數(shù)據(jù)篩選數(shù)據(jù)篩選功能是對(duì)數(shù)據(jù)進(jìn)行分析時(shí)常用的操作之一。數(shù)據(jù)排序分為以下3種情況。自動(dòng)篩選數(shù)據(jù)即根據(jù)用戶設(shè)定的篩選條件，自動(dòng)將表格中符合條件的數(shù)據(jù)顯示出來，而表格中的其他數(shù)據(jù)將隱藏。自動(dòng)篩選自定義篩選是在自動(dòng)篩選的基礎(chǔ)上進(jìn)行操作的，即單擊自動(dòng)篩選后的需自定義的字段名稱右側(cè)的下拉按鈕，在打開的下拉列表中選擇相應(yīng)的選項(xiàng)確定篩選條件，然后在打開的“自定義篩選方式”對(duì)話框中進(jìn)行相應(yīng)的設(shè)置。自定義篩選若需要根據(jù)自己設(shè)置的篩選條件對(duì)數(shù)據(jù)進(jìn)行篩選，則需要使用高級(jí)篩選功能。高級(jí)篩選功能可以篩選出同時(shí)滿足兩個(gè)或兩個(gè)以上條件的數(shù)據(jù)。高級(jí)篩選任務(wù)實(shí)現(xiàn)任務(wù)實(shí)現(xiàn)統(tǒng)計(jì)分析員工績(jī)效表任務(wù)二（一）排序員工績(jī)效表數(shù)據(jù)（二）篩選員工績(jī)效表數(shù)據(jù)（三）對(duì)數(shù)據(jù)進(jìn)行分類匯總（四）創(chuàng)建并編輯數(shù)據(jù)透視表（五）創(chuàng)建數(shù)據(jù)透視圖（一）排序員工績(jī)效表數(shù)據(jù)設(shè)置主要排序條件1設(shè)置自定義序列2（二）篩選員工績(jī)效表數(shù)據(jù)1．自動(dòng)篩選（1）打開表格，選擇工作表中的任意單元格，在【數(shù)據(jù)】/【排序和篩選】組中單擊“篩選”按鈕，進(jìn)入篩選狀態(tài)，列標(biāo)題單元格右側(cè)顯示出“篩選”按鈕。（2）在C2單元格中單擊“篩選”按鈕，在打開的下拉列表框中取消選中“檢驗(yàn)”“流水”和“運(yùn)輸”復(fù)選框，僅單擊選中“裝配”復(fù)選框，單擊“確定”按鈕。（3）此時(shí)將在數(shù)據(jù)表中顯示工種為“裝配”的員工數(shù)據(jù)，而其他員工數(shù)據(jù)將全部被隱藏。（二）篩選員工績(jī)效表數(shù)據(jù)2.自定義篩選（二）篩選員工績(jī)效表數(shù)據(jù)3.高級(jí)篩選（1）打開“員工績(jī)效表.xlsx”工作簿，在C16單元格中輸入篩選序列“1月份”，在C17單元格中輸入條件“>510”，在D16單元格中輸入篩選序列“季度總產(chǎn)量”，在D17單元格中輸入條件“>1556”，在表格中選擇任意的單元格，在“數(shù)據(jù)”/“排序和篩選”組中單擊“高級(jí)”按鈕。（2）打開“高級(jí)篩選”對(duì)話框，選中“將篩選結(jié)果復(fù)制到其他位置”單選項(xiàng)，將“列表區(qū)域”設(shè)置為“$A$2:$G$14”，在“條件區(qū)域”文本框中輸入“$C$16:$D$17”，在“復(fù)制到”文本框中輸入“$A$18:$G$25”，單擊“確定”按鈕。（3）此時(shí)可在原數(shù)據(jù)表下方的A18:G19單元格區(qū)域中單獨(dú)顯示出篩選結(jié)果。（二）篩選員工績(jī)效表數(shù)據(jù)4.使用條件格式設(shè)置條件格式1應(yīng)用條件格式2（三）對(duì)數(shù)據(jù)進(jìn)行分類匯總設(shè)置分類匯總1查看嵌套分類匯總結(jié)果2（四）創(chuàng)建并編輯數(shù)據(jù)透視表設(shè)置數(shù)據(jù)透視表的放置位置1添加字段2對(duì)匯總結(jié)果進(jìn)行篩選3（五）創(chuàng)建數(shù)據(jù)透視圖創(chuàng)建數(shù)據(jù)透視圖1篩選數(shù)據(jù)透視圖2制作工作考核表統(tǒng)計(jì)分析員工績(jī)效表目錄制作銷售分析表任務(wù)一任務(wù)二任務(wù)三打開已經(jīng)創(chuàng)建并編輯好的素材表格，根據(jù)表格中的數(shù)據(jù)創(chuàng)建圖表，并將其移動(dòng)到新的工作表中。對(duì)圖表進(jìn)行相應(yīng)的編輯處理，修改圖表數(shù)據(jù)、修改圖表類型、設(shè)置圖表樣式、調(diào)整圖表布局、設(shè)置圖表格式、調(diào)整圖表對(duì)象的顯示與分布和使用趨勢(shì)線等。為表格中的數(shù)據(jù)插入迷你圖。任務(wù)要求任務(wù)要求制作銷售分析表任務(wù)三相關(guān)知識(shí)相關(guān)知識(shí)制作銷售分析表任務(wù)三（一）圖表的類型（二）使用圖表的注意事項(xiàng)（一）圖表的類型柱形圖常用于進(jìn)行幾個(gè)項(xiàng)目之間數(shù)據(jù)的對(duì)比。柱形圖折線圖多用于顯示等時(shí)間間隔數(shù)據(jù)的變化趨勢(shì)，它強(qiáng)調(diào)的是數(shù)據(jù)的時(shí)間性和變動(dòng)率。折線圖面積圖用于顯示每個(gè)數(shù)值的變化量，強(qiáng)調(diào)數(shù)據(jù)隨時(shí)間變化的幅度，還能直觀地體現(xiàn)整體和部分的關(guān)系。面積圖條形圖與柱形圖的用法相似，但數(shù)據(jù)位于y軸，值位于x軸，位置與柱形圖相反。條形圖餅圖用于顯示一個(gè)數(shù)據(jù)系列中各項(xiàng)的大小與各項(xiàng)總和的比例。餅圖0102030405（二）使用圖表的注意事項(xiàng)在制作圖表前如需先制作表格，應(yīng)根據(jù)前期收集的數(shù)據(jù)制作出相應(yīng)的電子表格，并對(duì)表格進(jìn)行一定的美化不同的圖表類型能夠進(jìn)行的操作可能不同，如二維圖表和三維圖表就具有不同的格式設(shè)置。根據(jù)表格中某些數(shù)據(jù)項(xiàng)或所有數(shù)據(jù)項(xiàng)創(chuàng)建相應(yīng)形式的圖表。選擇電子表格中的數(shù)據(jù)時(shí)，可根據(jù)圖表的需要視情況而定。圖表中的數(shù)據(jù)較多時(shí)，應(yīng)該盡量將所有數(shù)據(jù)都顯示出來，所以一些非重點(diǎn)的部分，如圖表標(biāo)題、坐標(biāo)軸標(biāo)題和數(shù)據(jù)表格等都可以省略。檢查創(chuàng)建的圖表中的數(shù)據(jù)有無遺漏，及時(shí)對(duì)數(shù)據(jù)進(jìn)行添加或刪除。然后對(duì)圖表形狀樣式和布局等內(nèi)容進(jìn)行相應(yīng)的設(shè)置，完成圖表的創(chuàng)建與修改。辦公文件講究簡(jiǎn)單