信息技術(shù) 課件 單元2 Excel 2016電子表格處理-

單元2Excel2016電子表格處理匯報內(nèi)容02項目5道克圖文公司員工考核數(shù)據(jù)處理03目錄項目6開支明細表數(shù)據(jù)處理01項目4數(shù)據(jù)快速錄入及計算項目4數(shù)據(jù)快速錄入及計算學(xué)習(xí)目標(biāo)課前自學(xué)課中學(xué)習(xí)課后提升學(xué)習(xí)目標(biāo)1．知識目標(biāo)1）了解Excel2016的功能和窗口組成。2）掌握相對引用、絕對引用、混合引用和三維引用的概念和用法。3）掌握熟練自動填充的用法。4）熟練掌握等差數(shù)列和等比數(shù)列的操作用法。5）掌握公式中的運算符類型。6）掌握數(shù)據(jù)清單中輸入和編輯函數(shù)。2．能力目標(biāo)1）能夠使用Excel2016快速錄入數(shù)據(jù)。2）能夠熟練使用Excel2016輸入和編輯公式。3．素養(yǎng)目標(biāo)1）能夠樹立不怕困難，吃苦耐勞的精神，能夠攻堅克難完成實訓(xùn)任務(wù)。2）能夠在Excel2016快速錄入數(shù)據(jù)中培養(yǎng)熟能生巧的意識。3）具有團隊精神，能夠和團隊成員協(xié)商，共同完成實訓(xùn)任務(wù)。4）通過各類型數(shù)據(jù)自動填充任務(wù)練習(xí)，培養(yǎng)腳踏實地、力戒虛榮的意識。5）通過快速錄入實訓(xùn)培養(yǎng)一絲不茍、精益求精的工匠精神。課前自學(xué)（一）Eecel2016快速錄入1．Excel2016簡介Excel2016是微軟公司推出的辦公自動化套裝軟件Office2016的主要應(yīng)用程序之一，其是一個功能非常強大的電子表格應(yīng)用軟件，具有強大的數(shù)據(jù)計算與分析處理功能。2．工作簿、工作表和單元格基本概念工作簿是存儲數(shù)據(jù)、數(shù)據(jù)運算公式以及數(shù)據(jù)格式化等信息的文件。用戶在Excel中處理的各種數(shù)據(jù)最終都是以工作簿文件的形式存儲在磁盤上。其默認(rèn)的擴展名是.xlsx。每個工作簿通常都由多張工作表（Sheet）組成，工作表（Sheet）是一個由行和列交叉排列的二維表格，也稱作電子表格。單元格（Cell）就是工作表中行和列交叉的部分，是工作表的最基本的數(shù)據(jù)單元，也是電子表格軟件處理數(shù)據(jù)的最小單位。3．Excel2016的工作環(huán)境數(shù)據(jù)驗證

Excel2016啟動（啟動方法同Word2016）后，出現(xiàn)如圖所示的窗口，主要包括快速訪問工具欄、標(biāo)題欄、功能區(qū)（多個選項卡）、編輯欄（Excel2016特有）、工作表編輯區(qū)、狀態(tài)欄等4．在多個區(qū)域輸入相同的內(nèi)容啟動Excel2016，選擇空白工作簿，在工作表sheet1中按ctrl健選擇多個不連續(xù)的區(qū)域，松開ctrl，輸入學(xué)號“’2021010101”（切換到英文狀態(tài)，先輸入英文的單引號’，再輸入2021010101），按住ctrl健敲enter健結(jié)束。5．自動填充（1）快速錄入性別（2）自動填充數(shù)字輸入數(shù)字1，然后選擇第一個“1”的單元格，拖拽單元格右下面的填充柄，鼠標(biāo)指針變成“+”時，拖拽鼠標(biāo)左鍵，就在下面連續(xù)的單元格中輸入相同的“1”在某單元格，輸入數(shù)字1，然后選擇第一個“1”的單元格，拖拽單元格右下面的填充柄，鼠標(biāo)指針變成“+”時，按住ctrl拖拽鼠標(biāo)左鍵，就在下面連續(xù)的單元格中輸入1、2、3、4、5……。（3）自動填日期與數(shù)字相反，直接拖拽日期的單元格填充填充柄，日期自動增1；按住ctrl拖拽鼠標(biāo)左鍵，拖拽日期的單元格填充填充柄，日期不變。6.等差數(shù)列、等差數(shù)列等差數(shù)列，三個輸入方法等比數(shù)列，兩個輸入方法（二）Excel2016計算公式公式是電子表格的靈魂和核心內(nèi)容。每當(dāng)用戶輸入或者修改數(shù)據(jù)之后，公式便會自動地或者在用戶操作之后重新將有關(guān)數(shù)據(jù)計算一遍，并將最新結(jié)果顯示在屏幕上。Excel2016中公式的包括三個部分。（1）“=”符號：表示用戶輸入的內(nèi)容是公式不是數(shù)據(jù)（注意輸入公式必須以“=”開頭）。（2）運算符：用以指明公式中元素進行計算的類型。（3）參與計算的元素（運算數(shù)）。每個運算數(shù)可以是不改變的數(shù)值（常量數(shù)值）、單元格或單元格區(qū)域引用、標(biāo)志、名稱或工作表函數(shù)。1．公式中的運算符類型Excel2016包含四種類型的運算符：算術(shù)運算符、比較運算符、文本運算符和引用運算符。（1）算術(shù)運算符：+（加號）、.（減號或負號）、*（星號或乘號）、/（除號）、%（百分號）、^（乘方）。例如，在某個單元格中輸入：“=2+5^2”后回車，結(jié)果為27。（2）比較運算符：=（等號）、>（大于號）、<（小于號）、>=（大于等于號）、<=（小于等于號）、<>（不等于）。用以比較兩個值，結(jié)果是一個邏輯值，不是TRUE就是FALSE。例如：在某個單元格中輸入“=3<8”,結(jié)果為FALSE。（3）文本運算符：&，使用“&”加入或者連接一個或多個字符串以產(chǎn)生一大片文本。例如在某個中單元輸入“="山東"&"商職學(xué)院"”（注意文本輸入時須加英文引號）后回車，將產(chǎn)生“山東商職學(xué)院”的結(jié)果。4）單元格引用運算符：:（冒號）、,（逗號）、（空格）冒號（:）是區(qū)域運算符，表示一個單元格區(qū)域，是對兩個引用以及兩個引用之間的所有單元格進行引用。如“=SUM(B2:E2)”。逗號（，）是并集運算符，將多個引用合并為一個引用。例如，在學(xué)生成績表.xlsx文件sheet1的F2單元格中輸入“=SUM（B2:C2,D2:E2）”，結(jié)果是和輸入“=SUM（B2:E2)”一樣。注意，Excel2016中的并集同數(shù)學(xué)中并集概念不一樣，相同的部分會計算兩次?？崭瘢ǎ┦墙患\算符，只處理各單元格區(qū)域中共有的單元格中的數(shù)據(jù)。例如，在學(xué)生成績表.xlsx文件sheet1的C8單元格中輸入“=SUM(B2:C6C2:E6)”，結(jié)果是“410”，和輸入“=SUM（C2:C5)”的結(jié)果一樣。2．?dāng)?shù)據(jù)清單中輸入和編輯公式數(shù)據(jù)清單（二維表格）中公式的運算量要用單元格地址（而不是數(shù)據(jù)），以便于復(fù)制引用公式。數(shù)據(jù)清單中公式只需要輸入一次，其它公式的計算可以使用自動填充得到；也可通過事先把數(shù)據(jù)清單變成智能表格（選擇數(shù)據(jù)清單，使用“插入”選項卡中的“表格”命令），所有需要填充的公式只需要輸入一次。如果需要修改某公式，則先單擊包含該公式的單元格，在編輯欄中修改即可；也可以雙擊該單元格，直接在單元格中修改，或者仍然在編輯欄中修改。3．相對引用和絕對引用（1）相對引用相對引用是指當(dāng)把一個含有單元格或單元格區(qū)域地址的公式復(fù)制到新的位置時，公式中的單元格地址或單元格區(qū)域隨著改變，公式的值將會依據(jù)改后的單元格或單元格區(qū)域的值重新計算。公式中沒有$。（2）絕對引用絕對引用是指在公式中的單元格地址或單元格區(qū)域地址不隨著公式位置的改變而發(fā)生改變。不論公式的單元格處在什么位置，公式中所引用的單元格位置都是其在工作表中的確切位置。絕對單元格引用的形式是在每一個行標(biāo)及列標(biāo)前加一個$符號。（3）混合引用混合引用是指單元格或單元格區(qū)域的地址表示為部分是相對引用，部分是絕對引用。如$B2、B$2。（4）相對引用與絕對引用之間的切換如果創(chuàng)建了一個公式并希望將相對引用更改為絕對引用（反之亦然），則先選定包含該公式的單元格，然后在編輯欄中選擇要更改的引用并按F4鍵。每次按F4鍵時，Excel2016會在以下組合間切換：絕對列與絕對行（例如，$C$1），相對列與絕對行（C$1)，絕對列與相對行（$C1)以及相對列與相對行（C1)。例如，在公式中選擇地址$A$1并按F4鍵，引用將變?yōu)锳$1。再一次按F4鍵，引用將變?yōu)?A1，以此類推。（5）三維引用三維地址引用。在Excel中，不但可以引用同一工作表中的單元格，還能引用不同工作表中的單元格，引用格式為：[工作薄名]+工作表名！+單元格引用。例如在Book1中引用Book2的sheet1的第三行第五列，可表示為；[Book2]Sheet1!E3。函數(shù)的輸入方法（1）從鍵盤上直接輸入函數(shù)（2）使用公式選項卡的公式類別選擇相應(yīng)函數(shù)（3）使用智能表格進行函數(shù)輸入課中學(xué)習(xí)任務(wù)2-1：各類型數(shù)據(jù)自動填充（一）任務(wù)介紹某人于2020年1月1號開始從某機構(gòu)借錢，借款一年（365）天，名義借款1000，到個人賬戶上1000元，年利率3%，一年后到期還款1030元。若一年到期后，沒錢還前面借的1030元，又借了1030還上一筆款，再過一年后又是這樣操作。每次借出一筆用來還上一筆的本金和利息。以此類推，到了2120年1月1號，未還本金變成多少？，具體任務(wù)要求如下：1）正確輸入各類型的數(shù)據(jù)。2）數(shù)據(jù)清單中第一、二行數(shù)據(jù)自己手工輸入，其它行數(shù)據(jù)使用自動填充。（二）任務(wù)實施任務(wù)2-1：各類型數(shù)據(jù)自動填充（一）任務(wù)介紹某人于2020年1月1號開始從某機構(gòu)借錢，借款一年（365）天，名義借款1000，到個人賬戶上1000元，年利率3%，一年后到期還款1030元。若一年到期后，沒錢還前面借的1030元，又借了1030還上一筆款，再過一年后又是這樣操作。每次借出一筆用來還上一筆的本金和利息。以此類推，到了2120年1月1號，未還本金變成多少？，具體任務(wù)要求如下：1）正確輸入各類型的數(shù)據(jù)。2）數(shù)據(jù)清單中第一、二行數(shù)據(jù)自己手工輸入，其它行數(shù)據(jù)使用自動填充。（二）任務(wù)實施任務(wù)2-2：期末考試成績表計算（一）任務(wù)介紹期末考試后，輔導(dǎo)員要求小明對班級的期末成績進行計算排名，具體任務(wù)要求如下：1）使用公式和函數(shù)對期末考試成績表總分、平均分進行計算。2）使用rank函數(shù)對期末考試成績表名次進行計算。（二）任務(wù)實施課后提升（一）鞏固提升實訓(xùn)任務(wù)2-1：圖文店考核信息計算（二）拓展提升實訓(xùn)任務(wù)2-2：班級學(xué)號的輸入項目5道克圖文公司員工考核數(shù)據(jù)處理學(xué)習(xí)目標(biāo)課前自學(xué)課中學(xué)習(xí)課后提升學(xué)習(xí)目標(biāo)1．知識目標(biāo)1）了解Excel2016常用函數(shù)的種類。2）掌握常用函數(shù)的用法。3）掌握嵌套函數(shù)的計算。4）掌握在智能表格中進行函數(shù)輸入和編輯的操作方法。5）掌握刪除重復(fù)項的操作方法。6）掌握函數(shù)的各種錄入方法。2．能力目標(biāo)1）能夠使用Excel2016常用函數(shù)進行公司相關(guān)信息計算。2）能夠使用Excel2016函數(shù)進行公司相關(guān)考核和業(yè)務(wù)計算。3．素養(yǎng)目標(biāo)1）能夠樹立不怕困難，吃苦耐勞的精神，能夠攻堅克難完成實訓(xùn)任務(wù)。2）能夠在Excel2016函數(shù)計算中培養(yǎng)腳踏實地、熟能生巧的意識。3）具有團隊精神，能夠和團隊成員協(xié)商，共同完成實訓(xùn)任務(wù)。4）通過Excel2016函數(shù)計算中培養(yǎng)一絲不茍、精益求精的工匠精神。課前自學(xué)1．?dāng)?shù)學(xué)函數(shù)函

數(shù)功

能應(yīng)用舉例結(jié)

果INT(number)求小于number的最大整數(shù)=INT(6.5)6MOD(number,divisor)求number/divisor的余數(shù)=MOD(13,2)1ROUND(number,n)按n指定的位數(shù)四舍五入=ROUND(34.23,1)34.2SQRT(number)求number的平方根值=SQRT(25)5SUM(number1,number2,…)求若干個數(shù)的和=SUM(2,4,5)11SUMIF(Range,Criteria,Sum_range)單條件求和Range：條件的單元格區(qū)域。Criteria：需要滿足的條件。Sum_range：需要求和的實際單元格區(qū)域。

SUMIFS(Sum_range,Range1,criteria1,Range2,criteria2)多條件求和Sum_range：需要求和的實際單元格區(qū)域。Range1：條件1的單元格區(qū)域。Criteria1：需要滿足的條件1。Range2：條件2的單元格區(qū)域。Criteria2：需要滿足的條件2。

2．統(tǒng)計函數(shù)函

數(shù)功

能應(yīng)用舉例結(jié)果AVERAGE(number1,number2,…)求參數(shù)中數(shù)值的平均值=AVERAGE(4,6)5COUNT()求參數(shù)中數(shù)值數(shù)據(jù)的個數(shù)=COUNT(4,6)=COUNT(”張三”,6)21COUNTA()求參數(shù)中數(shù)值非空數(shù)據(jù)的個數(shù)=COUNTA(“張三”,6)2COUNTIF((Range,criteria)按單個條件計數(shù)Range：條件的單元格區(qū)域。Criteria：需要滿足的條件。

COUNTIF(Range1,criteria1,Range2,criteria2)多條件計數(shù)Range1：條件1的單元格區(qū)域。Criteria1：條件1。Range2：條件2的單元格區(qū)域。Criteria2：條件2。

MAX(number1,number2,…)求參數(shù)中數(shù)值的最大值=MAX(4,6)6MIN(number1,number2,…)求參數(shù)中數(shù)值的最小值=MIN(4,6)43．文本函數(shù)函

數(shù)功

能應(yīng)用舉例結(jié)

果LEFT(text,n)取text左邊n個字符=LEFT(“ABCD”,3)“ABC”LEN(text)求text的字符個數(shù)=LEN(“ABCD”)4MID(text,n,p)從text中第n個字符開始連續(xù)取p個字符=MID(“ABCD”,3,1)“C”RIGHT(text,n)取text右邊n個字符=RIGHT(“ABCD”,3)“BCD”4．日期和時間函數(shù)函

數(shù)功

能應(yīng)用舉例結(jié)

果DATE(year,month,day)生成日期=DATE(2007,1,2)2007-1-2DAY(date)取日期的天數(shù)=DAY(date(2007,1,2))2Year(date)取日期的年=YEAR(date(2007,1,2))2007MONTH(date)取日期的月份=MONTH(date(2007,1,2))1NOW()取現(xiàn)在的年月日時間=NOW()

5．邏輯函數(shù)函

數(shù)功

能應(yīng)用舉例結(jié)

果AND(邏輯表達式1,邏輯表達式2,邏輯表達式3,……)判斷各邏輯表達式是否成立，都成立，結(jié)果為TRUE；只要有一個不成立，結(jié)果為FALSE。=AND(5>8,7<9)FALSEOR(邏輯表達式1,邏輯表達式2,邏輯表達式3,……)判斷各邏輯表達式是否成立，只有一個成立，結(jié)果為TRUE；都不成立，結(jié)果為FALSE。=OR(5>8,7<9)TRUEIF(條件表達式，值1，值2)當(dāng)條件表達式為真時，返回值1，否則返回值2。

6．查找和引用函數(shù)函

數(shù)功

能應(yīng)用舉例結(jié)

果=ROW()返回所在行的行號在D12單元格輸入“=ROW()”。12=COLUMN()返回所在列的列號在D12單元格輸入“=COLUMN()”。4課中學(xué)習(xí)任務(wù)2-3：“山東道克圖文公司員工考核表”數(shù)據(jù)處理（一）任務(wù)介紹人力資源部馬經(jīng)理需要對山東道克圖文公司員工考核數(shù)據(jù)處理，打開山東道克圖文公司員工考核表（素材）.xlsx，另存為山東道克圖文公司員工考核表（結(jié)果）.xlsx，她進行了如下計算處理。1）根據(jù)身份證號信息計算年齡。2）根據(jù)工號前四位計算入店年份。3）計算員工所在道克圖文店。4）考核是否優(yōu)秀和等級的計算。5）員工獎金及其它信息的計算。6）圖文店業(yè)務(wù)統(tǒng)計計算。（二）任務(wù)實施1．職員基本信息計算2．圖文店考核信息計算3．圖文店業(yè)務(wù)統(tǒng)計課后提升（一）鞏固提升實訓(xùn)任務(wù)2-3：員工性別信息的計算（二）拓展提升實訓(xùn)任務(wù)2-4：“山東道克圖文公司員工考核表”隔行填色項目6開支明細表數(shù)據(jù)處理學(xué)習(xí)目標(biāo)課前自學(xué)課中學(xué)習(xí)課后提升學(xué)習(xí)目標(biāo)1．知識目標(biāo)1）掌握設(shè)置單元格邊框、行高、列寬、對齊方式的操作方法。2）掌握創(chuàng)建表格并設(shè)置表格樣式的操作方法。3）掌握數(shù)據(jù)驗證的作用和操作方法。4）掌握運用條件格式篩選數(shù)據(jù)的操作方法。5）掌握自定義多條件篩選數(shù)據(jù)的操作方法。6）掌握多關(guān)鍵字排序的操作方法。7）掌握數(shù)據(jù)透視表的作用和創(chuàng)建方法。8）掌握圖表的常用類型和選取數(shù)據(jù)創(chuàng)建圖表的操作方法。2．能力目標(biāo)1）能夠熟練對工作表進行美化。2）能夠根據(jù)需要選取相應(yīng)數(shù)據(jù)制作報表。3．素養(yǎng)目標(biāo)1）通過對實際問題的解決，培養(yǎng)學(xué)生的信息素養(yǎng)。2）體會成功的快樂，激進學(xué)習(xí)興趣，提高自主學(xué)習(xí)能力。3）具備協(xié)調(diào)溝通的團隊精神。課前自學(xué)（一）格式化表格

Excel表格提供了豐富的格式化命令，利用這些命令，可以對工作表內(nèi)的數(shù)據(jù)及外觀進行修飾，制作出符合日常習(xí)慣又美觀的表格。

單元格或單元格區(qū)域的格式化主要包含六部分：數(shù)字、對齊、字體、邊框、圖案和保護，如圖所示，為“設(shè)置單元格格式”對話框中包含的六部分。（二）數(shù)據(jù)處理數(shù)據(jù)驗證

數(shù)據(jù)驗證是Excel提供的用于檢驗數(shù)據(jù)有效性的功能。在Excel中，為了避免在輸入數(shù)據(jù)時出現(xiàn)過多錯誤，可以通過在單元格中設(shè)置數(shù)據(jù)有效性以防止輸入無效數(shù)據(jù)，或者在錄入無效數(shù)據(jù)時自動發(fā)出警告，從而保證數(shù)據(jù)輸入的準(zhǔn)確性，提高工作效率。

對于學(xué)生成績，有效值為0-100的數(shù)值，比如成績?yōu)?0，誤輸入為900，若已經(jīng)為學(xué)生成績列設(shè)置數(shù)據(jù)驗證，則此時會出現(xiàn)警告提示，保證數(shù)據(jù)有效性。數(shù)據(jù)驗證在“數(shù)據(jù)”項卡下的“數(shù)據(jù)工具”組中，如圖所示。

有一點需要注意，驗證功能只對新數(shù)據(jù)有效，在已經(jīng)填好數(shù)據(jù)的單元格增加驗證功能沒有作用。數(shù)據(jù)驗證是在當(dāng)單元格的值發(fā)生變化時觸發(fā)的，如果原有的值不符合標(biāo)準(zhǔn)，同時該值沒有發(fā)生變化，就不會觸發(fā)驗證功能。所以，如果決定使用驗證功能，最好在未填寫數(shù)據(jù)時就設(shè)置好，而不是等數(shù)據(jù)都填寫后再設(shè)置。條件格式Excel提供的條件格式功能可以迅速為滿足某些條件的單元格或單元格區(qū)域設(shè)定某項格式，條件格式會基于設(shè)定的條件來自動更改單元格區(qū)域的外觀，可以突出顯示所關(guān)注的單元格或單元格區(qū)域、強調(diào)異常值、使用數(shù)據(jù)條、色階和圖標(biāo)集來直觀地顯示數(shù)據(jù)。如圖所示。（1）突出顯示單元格規(guī)則

用比較運算符限定數(shù)據(jù)范圍，對該數(shù)據(jù)范圍內(nèi)的單元格設(shè)定格式。

規(guī)則選項有大于、小于、介于、等于、文本包含、發(fā)生日期、重復(fù)值和其他規(guī)則。

例如：查找高數(shù)科目成績大于85的值，如圖所示。（2）項目選取規(guī)則

對選定單元格區(qū)域中的前若干個最高值或后若干個最低值及高于或低于該區(qū)域平均值的單元格設(shè)定特殊格式。

規(guī)則有前10項、前10%、最后10項、最后10%、高于平均值、低于平均值和其他規(guī)則。

例如：查找高數(shù)科目成績前十名的值，如圖所示。（3）數(shù)據(jù)條

數(shù)據(jù)條的長度代表單元格中的值。數(shù)據(jù)條越長，值越高；數(shù)據(jù)條越短，值越低。

最常用的數(shù)據(jù)條有兩種樣式：漸變填充和實心填充。

例如：高數(shù)科目成績是漸變填充效果，體育科目成績是實心填充效果，如圖所示。（4）色階

使用兩種或者三種顏色的漸變效果直觀地比較單元格區(qū)域中數(shù)據(jù)，用來顯示數(shù)據(jù)的變化。一般情況下，顏色的深淺表示值得高低。

例如：用“綠-白色階”表示高數(shù)科目的成績值。成績越高，顏色越綠，成績越低，顏色越白。如圖所示。（5）圖標(biāo)集

可以使用圖標(biāo)集對數(shù)據(jù)進行注釋，每個圖標(biāo)代表一個值得范圍。

預(yù)設(shè)了方向、形狀、標(biāo)記、等級四種圖標(biāo)集。

例如：用三色交通燈來表示高數(shù)科目的成績。如圖所示。排序

對數(shù)據(jù)進行排序有助于快速直觀地組織并查找所需數(shù)據(jù)，可以對一列或多列中的數(shù)據(jù)文本、數(shù)值、日期和時間按升序或者降序的方式進行排序。還可以按照自定義序列、格式（包括單元格顏色、字體顏色等）進行排序。（1）快速簡單排序按“總分”列進行升序排序。單擊“總分”數(shù)據(jù)列中的任意一個單元格，選擇“數(shù)據(jù)”選項卡→“排序和篩選”組→“升序”按鈕，則按總分成績從小到大排序；若選擇“降序”，則按總分成績從大到小排序，如圖2-63所示。（2）復(fù)雜多條件排序按“高數(shù)”成績升序，高數(shù)成績相同的，按照“英語”成績降序排序。單擊數(shù)據(jù)列中的任意一個單元格，選擇“數(shù)據(jù)”選項卡→“排序和篩選”組中的“排序”按鈕→打開“排序”對話框→在“主要關(guān)鍵字”中選擇“高數(shù)”、“數(shù)值”、“升序”→單擊“添加條件”→在“次要關(guān)鍵字”中選擇“英語”、“數(shù)值”、“降序”→單擊”確定”按鈕，如圖所示。（3）按照字體顏色排序?qū)ⅰ案邤?shù)”成績相同的行，字體顏色設(shè)置為紅色，并將其所在行設(shè)置在數(shù)據(jù)表的頂端。單擊數(shù)據(jù)列中的任意一個單元格，選擇“數(shù)據(jù)”選項卡→“排序和篩選”組中的“排序”按鈕→打開“排序”對話框→在“主要關(guān)鍵字”中選擇“高數(shù)”、“排序依據(jù)”選擇“字體顏色”、“次序”選擇“紅色”“在頂端”→單擊“確定”按鈕，如圖2-65所示。篩選

通過篩選功能可以快速的從數(shù)據(jù)列表中查找符合條件的數(shù)據(jù)或者排除不符合條件的數(shù)據(jù)。對數(shù)據(jù)列表中的數(shù)據(jù)進行篩選后，就會僅顯示滿足指定條件的行，不滿足條件的行就會被隱藏。更方便用戶查看數(shù)據(jù)。首先單擊內(nèi)容數(shù)據(jù)表的任一單元格，選擇“數(shù)據(jù)”選項卡→“排序和篩選”組→“篩選”按鈕，此時，字段名右側(cè)會出現(xiàn)一個下拉箭頭。單擊需要篩選字段的下拉箭頭，設(shè)置篩選條件或自定義自動篩選方式。例如：篩選出“計算機”成績大于85的數(shù)據(jù)行，操作步驟如圖所示。篩選結(jié)果若取消篩選，則再單擊“篩選”按鈕取消選中即可。數(shù)據(jù)透視表

數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的交互式動態(tài)表格，能幫助用戶分析、組織數(shù)據(jù)。建好數(shù)據(jù)透視表后，可以對其中的字段重新安排，以便從不同角度查看數(shù)據(jù)。數(shù)據(jù)透視表能夠?qū)⒑Y選、排序、分類匯總等操作依次完成，并生成匯總報表，生成數(shù)據(jù)透視表的關(guān)鍵步驟如下頁圖2-68所示。圖2-68將性別、班級、姓名、總分4個字段分別拖入到相應(yīng)區(qū)域，生成的數(shù)據(jù)透視表如圖所示。將“性別”篩選為男，列標(biāo)簽改為“班級”，行標(biāo)簽改為“姓名”，效果如圖所示。迷你圖迷你圖是放入單個單元格中的小型圖，每個迷你圖代表所選內(nèi)容中的一行數(shù)據(jù)。選中Q3單元格，選擇“插入”選項卡→“迷你圖”組→“折線圖”按鈕，打開創(chuàng)建迷你圖對話框，數(shù)據(jù)范圍選擇F3:N3，位置Q3，單擊確定。拖動Q3右下角的填充柄，可以將迷你圖填充到最后。迷你圖的樣式及顏色，高點、低點、首點、尾點等的設(shè)置，在“迷你圖工具--設(shè)計”上下文選項卡下進行。圖表相對于迷你圖，圖表作為表格中的嵌入對象，類型更豐富、創(chuàng)建更靈活、功能更全面、作用更強大。圖表是工作表數(shù)據(jù)的圖形化表示，通過圖表，數(shù)據(jù)信息變得一目了然，更加詳細地表示數(shù)據(jù)的大小、變化、走向、趨勢及數(shù)據(jù)間的差異等，更方便進行數(shù)據(jù)的比較和分析。在Excel中，圖表由多個部分組成，這些組成部分被稱為圖表元素。一個完整的圖表通常由圖表區(qū)、繪圖區(qū)、圖表標(biāo)題、圖例等組成。最常用的圖表類型有：柱形圖，折線圖，餅圖，條形圖，面積圖等。圖表的基本組成：圖表區(qū)，繪圖區(qū)，橫坐標(biāo)軸和縱坐標(biāo)軸，圖例，圖標(biāo)標(biāo)題，坐標(biāo)軸標(biāo)題，數(shù)據(jù)標(biāo)簽等。課中學(xué)習(xí)任務(wù)2-4：“開支明細表”整理與分析（一）任務(wù)介紹

小張是一名參加工作不久的大學(xué)生，平時習(xí)慣使用Excel表格來記錄每月的個人開支情況。在2021年底，他將每個月各類支出的明細數(shù)據(jù)錄入了文件名為“開支明細表.xlsx”的Excel工作簿文檔中。請根據(jù)下列要求幫助小趙對明細表進行整理和分析：1）在工作表“小張的美好生活”的第一行添加表標(biāo)題“小張2021年開支明細表”，并通過合并單元格，放于整個表的上端、居中。2）將工作表應(yīng)用一種主題，并增大字號，適當(dāng)加大行高列寬，設(shè)置居中對齊方式，除表標(biāo)題“小張2021年開支明細表”外為工作表分別增加恰當(dāng)?shù)倪吙蚝偷准y以使工作表更加美觀。3）將每月各類支出及總支出對應(yīng)的單元格數(shù)據(jù)類型都設(shè)為“貨幣”類型，無小數(shù)、有人民幣貨幣符號。4）通過函數(shù)計算每個月的總支出、各個類別月均支出、每月平均總支出；并按每個月總支出升序?qū)ぷ鞅磉M行排序。5）利用“條件格式”功能：將月單項開支金額中大于1000元的數(shù)據(jù)所在單元格以不同的字體顏色與填充顏色突出顯示；將月總支出額中大于月均總支出110%的數(shù)據(jù)所在單元格以另一種顏色顯示，所用顏色深淺以不遮擋數(shù)據(jù)為宜。6）在“年月”與“服裝服飾”列之間插入新列“季度”，數(shù)據(jù)根據(jù)月份由函數(shù)生成，例如：1至3月對應(yīng)“1季度”、4至6月對應(yīng)“2季度”……7）復(fù)制工作表“小張的美好生活”，將副本放置到原表右側(cè)；改變該副本表標(biāo)簽的顏色，并重命名為“按季度匯總”；刪除“月均開銷”對應(yīng)行。8）通過分類匯總功能，按季度升序求出每個季度各類開支的月均支出金額。9）在“按季度匯總”工作表后面新建名為“折線圖”的工作表，在該工作表中以分類匯總結(jié)果為基礎(chǔ)，創(chuàng)建一個帶數(shù)據(jù)標(biāo)記的折線圖，水平軸標(biāo)簽為各類開支，對各類開支的季度平均支出進行比較，給每類開支的最高季度月均支出值添加數(shù)據(jù)標(biāo)簽。課后提升（一）鞏固提升

實訓(xùn)任務(wù)2-5：“股票走勢數(shù)據(jù)表”處理

小張就職于一家金融公司，需要對股票走勢進行分析計算。打開“股票走勢.xlsx”文件，對“股票走勢”數(shù)據(jù)表按以下要求操作。1）設(shè)置標(biāo)題，文字為黑體、20磅、粗體，并在A1:N1區(qū)域中合并居中，行高30。2）在J3單元格中，使用MIN函數(shù)求出該股票今日最低價，并將該公式向下填充J4:J7區(qū)域，在K3單元格中，使用MAX函數(shù)中求出該股票今日最高價，并將該公式向下填充K4:K7區(qū)域，在L3單元格中，利用公式計算今日漲跌。今日漲跌=最高價-最低價，并將該公式向下填充M4:M7區(qū)域，在M3單元格中，利用公式計算今日漲幅，今日漲幅=今日漲跌/昨收盤，并將該公式向下填充M4:M7區(qū)域。對今日漲幅列數(shù)據(jù)進行降序排序。3）在K9單元格中，使用AVERAGE函數(shù)求出今日平均漲幅。4）設(shè)置A2:N7區(qū)域中行的行高設(shè)置為30，并為該區(qū)表格設(shè)置所有框線，在N3單元格中插入折線圖迷你圖，并向下填充N4:N7區(qū)域。5）設(shè)置A1:N7區(qū)域的單元格對齊方式為水平方向居中、垂直方向居中。6）以B2:C7和I2:I7區(qū)域數(shù)據(jù)為數(shù)據(jù)源，在當(dāng)前表中生成一個二維簇狀柱形圖，圖表設(shè)計布局為“布局1”，圖表標(biāo)題為“股票價格變化圖”，為圖表添加圖表元素“數(shù)據(jù)標(biāo)簽”。最終效果如圖所示。（二）拓展提升

實訓(xùn)任務(wù)2-6：“產(chǎn)品銷售表”統(tǒng)計分析

銷售部助理小王需要根據(jù)2012年和2013年的圖書產(chǎn)品銷售情況進行統(tǒng)計分析，以便制訂新一年的銷售計劃和工作任務(wù)?，F(xiàn)在，請按照如下需求，在文檔“產(chǎn)品銷售表.xlsx”中完成以下工作并保存。1）在“銷售訂單”工作表的“圖書編號”列中，使用VLOOKUP函數(shù)填充所對應(yīng)“圖書名稱”的“圖書編號”，“圖書名稱”和“圖書編號”的對照關(guān)系請參考“圖書編目表”工作表。

2）將“銷售訂單”工作表的“訂單編號”列按照數(shù)值升序方式排序，并將所有重復(fù)的訂單編號數(shù)值標(biāo)記為紫色（標(biāo)準(zhǔn)色）字體，然后將其排列在銷售訂單列表區(qū)域的頂端。3）在“2013年圖書銷售分析”工作表中，統(tǒng)計2013年各類圖書在每月的銷售量，并將統(tǒng)計結(jié)果填充在所對應(yīng)的單元格中。為該表添加匯總行，在匯總行單元格中分別計算每月圖書的總銷量。4）在“2013年圖書銷售分析”工作表中的N4:N11單元格中，插入用于統(tǒng)計銷售趨勢的迷你折線圖，各單元格中迷你圖的數(shù)據(jù)范圍為所對應(yīng)圖書的1月～12月銷售數(shù)據(jù)。并為各迷你折線圖標(biāo)記銷量的最高點和最低點。5）根據(jù)“銷售訂單”工作表的銷售列表創(chuàng)建數(shù)據(jù)透視表，并將創(chuàng)建完成的數(shù)據(jù)透視表放置在新工作表中，以A1單元格為數(shù)據(jù)透視表的起點位置。將工作表重命名為“2012年書店銷量”。6）在“2012年書店銷量”工作表的數(shù)據(jù)透視表中，設(shè)置“日期”字段為列標(biāo)簽，“書店名稱”字段為行標(biāo)簽，“銷量（本）”字段為求和匯總項。并在數(shù)據(jù)透視表中顯示2012年期間各書店每季度的銷量情況。提示：為了統(tǒng)計方便，請勿對完成的數(shù)據(jù)透視表進行額外的排序操作。謝謝觀看單元2Excel2016電子表格處理匯報內(nèi)容02項目5道克圖文公司員工考核數(shù)據(jù)處理03目錄項目6開支明細表數(shù)據(jù)處理01項目4數(shù)據(jù)快速錄入及計算項目6開支明細表數(shù)據(jù)處理學(xué)習(xí)目標(biāo)課前自學(xué)課中學(xué)習(xí)課后提升學(xué)習(xí)目標(biāo)1．知識目標(biāo)1）掌握設(shè)置單元格邊框、行高、列寬、對齊方式的操作方法。2）掌握創(chuàng)建表格并設(shè)置表格樣式的操作方法。3）掌握數(shù)據(jù)驗證的作用和操作方法。4）掌握運用條件格式篩選數(shù)據(jù)的操作方法。5）掌握自定義多條件篩選數(shù)據(jù)的操作方法。6）掌握多關(guān)鍵字排序的操作方法。7）掌握數(shù)據(jù)透視表的作用和創(chuàng)建方法。8）掌握圖表的常用類型和選取數(shù)據(jù)創(chuàng)建圖表的操作方法。2．能力目標(biāo)1）能夠熟練對工作表進行美化。2）能夠根據(jù)需要選取相應(yīng)數(shù)據(jù)制作報表。3．素養(yǎng)目標(biāo)1）通過對實際問題的解決，培養(yǎng)學(xué)生的信息素養(yǎng)。2）體會成功的快樂，激進學(xué)習(xí)興趣，提高自主學(xué)習(xí)能力。3）具備協(xié)調(diào)溝通的團隊精神。課前自學(xué)（一）格式化表格

Excel表格提供了豐富的格式化命令，利用這些命令，可以對工作表內(nèi)的數(shù)據(jù)及外觀進行修飾，制作出符合日常習(xí)慣又美觀的表格。

單元格或單元格區(qū)域的格式化主要包含六部分：數(shù)字、對齊、字體、邊框、圖案和保護，如圖所示，為“設(shè)置單元格格式”對話框中包含的六部分。（二）數(shù)據(jù)處理數(shù)據(jù)驗證

數(shù)據(jù)驗證是Excel提供的用于檢驗數(shù)據(jù)有效性的功能。在Excel中，為了避免在輸入數(shù)據(jù)時出現(xiàn)過多錯誤，可以通過在單元格中設(shè)置數(shù)據(jù)有效性以防止輸入無效數(shù)據(jù)，或者在錄入無效數(shù)據(jù)時自動發(fā)出警告，從而保證數(shù)據(jù)輸入的準(zhǔn)確性，提高工作效率。

對于學(xué)生成績，有效值為0-100的數(shù)值，比如成績?yōu)?0，誤輸入為900，若已經(jīng)為學(xué)生成績列設(shè)置數(shù)據(jù)驗證，則此時會出現(xiàn)警告提示，保證數(shù)據(jù)有效性。數(shù)據(jù)驗證在“數(shù)據(jù)”項卡下的“數(shù)據(jù)工具”組中，如圖所示。

有一點需要注意，驗證功能只對新數(shù)據(jù)有效，在已經(jīng)填好數(shù)據(jù)的單元格增加驗證功能沒有作用。數(shù)據(jù)驗證是在當(dāng)單元格的值發(fā)生變化時觸發(fā)的，如果原有的值不符合標(biāo)準(zhǔn)，同時該值沒有發(fā)生變化，就不會觸發(fā)驗證功能。所以，如果決定使用驗證功能，最好在未填寫數(shù)據(jù)時就設(shè)置好，而不是等數(shù)據(jù)都填寫后再設(shè)置。條件格式Excel提供的條件格式功能可以迅速為滿足某些條件的單元格或單元格區(qū)域設(shè)定某項格式，條件格式會基于設(shè)定的條件來自動更改單元格區(qū)域的外觀，可以突出顯示所關(guān)注的單元格或單元格區(qū)域、強調(diào)異常值、使用數(shù)據(jù)條、色階和圖標(biāo)集來直觀地顯示數(shù)據(jù)。如圖所示。（1）突出顯示單元格規(guī)則

用比較運算符限定數(shù)據(jù)范圍，對該數(shù)據(jù)范圍內(nèi)的單元格設(shè)定格式。

規(guī)則選項有大于、小于、介于、等于、文本包含、發(fā)生日期、重復(fù)值和其他規(guī)則。

例如：查找高數(shù)科目成績大于85的值，如圖所示。（2）項目選取規(guī)則

對選定單元格區(qū)域中的前若干個最高值或后若干個最低值及高于或低于該區(qū)域平均值的單元格設(shè)定特殊格式。

規(guī)則有前10項、前10%、最后10項、最后10%、高于平均值、低于平均值和其他規(guī)則。

例如：查找高數(shù)科目成績前十名的值，如圖所示。（3）數(shù)據(jù)條

數(shù)據(jù)條的長度代表單元格中的值。數(shù)據(jù)條越長，值越高；數(shù)據(jù)條越短，值越低。

最常用的數(shù)據(jù)條有兩種樣式：漸變填充和實心填充。

例如：高數(shù)科目成績是漸變填充效果，體育科目成績是實心填充效果，如圖所示。（4）色階

使用兩種或者三種顏色的漸變效果直觀地比較單元格區(qū)域中數(shù)據(jù)，用來顯示數(shù)據(jù)的變化。一般情況下，顏色的深淺表示值得高低。

例如：用“綠-白色階”表示高數(shù)科目的成績值。成績越高，顏色越綠，成績越低，顏色越白。如圖所示。（5）圖標(biāo)集

可以使用圖標(biāo)集對數(shù)據(jù)進行注釋，每個圖標(biāo)代表一個值得范圍。

預(yù)設(shè)了方向、形狀、標(biāo)記、等級四種圖標(biāo)集。

例如：用三色交通燈來表示高數(shù)科目的成績。如圖所示。排序

對數(shù)據(jù)進行排序有助于快速直觀地組織并查找所需數(shù)據(jù)，可以對一列或多列中的數(shù)據(jù)文本、數(shù)值、日期和時間按升序或者降序的方式進行排序。還可以按照自定義序列、格式（包括單元格顏色、字體顏色等）進行排序。（1）快速簡單排序按“總分”列進行升序排序。單擊“總分”數(shù)據(jù)列中的任意一個單元格，選擇“數(shù)據(jù)”選項卡→“排序和篩選”組→“升序”按鈕，則按總分成績從小到大排序；若選擇“降序”，則按總分成績從大到小排序，如圖2-63所示。（2）復(fù)雜多條件排序按“高數(shù)”成績升序，高數(shù)成績相同的，按照“英語”成績降序排序。單擊數(shù)據(jù)列中的任意一個單元格，選擇“數(shù)據(jù)”選項卡→“排序和篩選”組中的“排序”按鈕→打開“排序”對話框→在“主要關(guān)鍵字”中選擇“高數(shù)”、“數(shù)值”、“升序”→單擊“添加條件”→在“次要關(guān)鍵字”中選擇“英語”、“數(shù)值”、“降序”→單擊”確定”按鈕，如圖所示。（3）按照字體顏色排序?qū)ⅰ案邤?shù)”成績相同的行，字體顏色設(shè)置為紅色，并將其所在行設(shè)置在數(shù)據(jù)表的頂端。單擊數(shù)據(jù)列中的任意一個單元格，選擇“數(shù)據(jù)”選項卡→“排序和篩選”組中的“排序”按鈕→打開“排序”對話框→在“主要關(guān)鍵字”中選擇“高數(shù)”、“排序依據(jù)”選擇“字體顏色”、“次序”選擇“紅色”“在頂端”→單擊“確定”按鈕，如圖2-65所示。篩選

通過篩選功能可以快速的從數(shù)據(jù)列表中查找符合條件的數(shù)據(jù)或者排除不符合條件的數(shù)據(jù)。對數(shù)據(jù)列表中的數(shù)據(jù)進行篩選后，就會僅顯示滿足指定條件的行，不滿足條件的行就會被隱藏。更方便用戶查看數(shù)據(jù)。首先單擊內(nèi)容數(shù)據(jù)表的任一單元格，選擇“數(shù)據(jù)”選項卡→“排序和篩選”組→“篩選”按鈕，此時，字段名右側(cè)會出現(xiàn)一個下拉箭頭。單擊需要篩選字段的下拉箭頭，設(shè)置篩選條件或自定義自動篩選方式。例如：篩選出“計算機”成績大于85的數(shù)據(jù)行，操作步驟如圖所示。篩選結(jié)果若取消篩選，則再單擊“篩選”按鈕取消選中即可。數(shù)據(jù)透視表

數(shù)據(jù)透視表是一種對大量數(shù)據(jù)快速匯總和建立交叉列表的交互式動態(tài)表格，能幫助用戶分析、組織數(shù)據(jù)。建好數(shù)據(jù)透視表后，可以對其中的字段重新安排，以便從不同角度查看數(shù)據(jù)。數(shù)據(jù)透視表能夠?qū)⒑Y選、排序、分類匯總等操作依次完成，并生成匯總報表，生成數(shù)據(jù)透視表的關(guān)鍵步驟如下頁圖2-68所示。圖2-68將性別、班級、姓名、總分4個字段分別拖入到相應(yīng)區(qū)域，生成的數(shù)據(jù)透視表如圖所示。將“性別”篩選為男，列標(biāo)簽改為“班級”，行標(biāo)簽改為“姓名”，效果如圖所示。迷你圖迷你圖是放入單個單元格中的小型圖，每個迷你圖代表所選內(nèi)容中的一行數(shù)據(jù)。選中Q3單元格，選擇“插入”選項卡→“迷你圖”組→“折線圖”按鈕，打開創(chuàng)建迷你圖對話框，數(shù)據(jù)范圍選擇F3:N3，位置Q3，單擊確定。拖動Q3右下角的填充柄，可以將迷你圖填充到最后。迷你圖的樣式及顏色，高點、低點、首點、尾點等的設(shè)置，在“迷你圖工具--設(shè)計”上下文選項卡下進行。圖表相對于迷你圖，圖表作為表格中的嵌入對象，類型更豐富、創(chuàng)建更靈活、功能更全面、作用更強大。圖表是工作表數(shù)據(jù)的圖形化表示，通過圖表，數(shù)據(jù)信息變得一目了然，更加詳細地表示數(shù)據(jù)的大小、變化、走向、趨勢及數(shù)據(jù)間的差異等，更方便進行數(shù)據(jù)的比較和分析。在Excel中，圖表由多個部分組成，這些組成部分被稱為圖表元素。一個完整的圖表通常由圖表區(qū)、繪圖區(qū)、圖表標(biāo)題、圖例等組成。最常用的圖表類型有：柱形圖，折線圖，餅圖，條形圖，面積圖等。圖表的基本組成：圖表區(qū)，繪圖區(qū)，橫坐標(biāo)軸和縱坐標(biāo)軸，圖例，圖標(biāo)標(biāo)題，坐標(biāo)軸標(biāo)題，數(shù)據(jù)標(biāo)簽等。課中學(xué)習(xí)任務(wù)2-4：“開支明細表”整理與分析（一）任務(wù)介紹

小張是一名參加工作不久的大學(xué)生，平時習(xí)慣使用Excel表格來記錄每月的個人開支情況。在2021年底，他將每個月各類支出的明細數(shù)據(jù)錄入了文件名為“開支明細表.xlsx”的Excel工作簿文檔中。請根據(jù)下列要求幫助小趙對明細表進行整理和分析：1）在工作表“小張的美好生活”的第一行添加表標(biāo)題“小張2021年開支明細表”，并通過合并單元格，放于整個表的上端、居中。2）將工作表應(yīng)用一種主題，并增大字號，適當(dāng)加大行高列寬，設(shè)置居中對齊方式，除表標(biāo)題“小張2021年開支明細表”外為工作表分別增加恰當(dāng)?shù)倪吙蚝偷准y以使工作表更加美觀。3）將每月各類支出及總支出對應(yīng)的單元格數(shù)據(jù)類型都設(shè)為“貨幣”類型，無小數(shù)、有人民幣貨幣符號。4）通過函數(shù)計算每個月的總支出、各個類別月均支出、每月平均總支出；并按每個月總支出升序?qū)ぷ鞅磉M行排序。5