第6章Excel2003軟件應用能力目標?本章學習電子表格制作軟件Excel2003的應用，要求掌握以下技能：①編輯電子表格，設置電子表格及數(shù)據(jù)格式；②運用公式對數(shù)據(jù)進行統(tǒng)計，利用函數(shù)處理數(shù)據(jù)。③利用圖表直觀顯示數(shù)據(jù)；④對數(shù)據(jù)庫進行排序、篩選、匯總統(tǒng)計等處理。概述6.1編輯操作6.2數(shù)據(jù)計算6.3函數(shù)應用6.4制作圖表6.5數(shù)據(jù)庫與數(shù)據(jù)分析6.6Excel實驗6.7Excel實訓6.8小結6.96.1概述6.1.1功能簡介?MicrosoftOfficeExcel2003是主要有如下三大功能。①計算②圖表③分析6.1.2啟動程序?可以通過以下方法之一啟動MicrosoftOfficeExcel2003程序。通常桌面上會有“MicrosoftOfficeExcel2003程序”的快捷圖標，直接雙擊即可打開該程序。這是最常用的方法。選擇【開始】→【程序】（或【所有程序】）→【MicrosoftOffice】→【MicrosoftOfficeExcel2003】命令。選擇【開始】→【運行】命令，在打開的“運行”對話框內(nèi)輸入excel后確定。6.1.3界面簡介

圖6-1

Excel2003程序界面（1）標題欄（2）菜單欄（3）工具欄圖6-2工具欄種類（4）名稱框（5）編輯欄（6）行標、列標（7）全選按鈕（8）工作區(qū)（9）滾動條（10）窗口拆分框圖6-3任務窗格（11）任務窗格（12）工作表標簽（13）工作表標簽滾動按鈕（14）標簽拆分框（15）狀態(tài)欄6.1.4基本概念1．工作簿2．工作表3．單元格4．單元格區(qū)域5．數(shù)據(jù)與信息6.1.5視圖

1．拆分窗口圖6-4拆分窗口2．凍結窗格圖6-5凍結窗口3．新建窗口

圖6-6重排窗口4．視圖

圖6-7“選項”對話框圖6-8分頁預覽6.2編輯操作6.2.1選中對象1．選中工作表2．選中單元格3．選中單元格區(qū)域4．選中不連續(xù)的單元格5．全選6．選中行或列7．使用鍵盤移動光標6.2.2編輯工作表1．設為當前工作表2．插入工作表

圖6-9工作表標簽

圖6-10“插入”對話框3．修改工作表名稱4．設置工作表標簽顏色

圖6-11設置工作表標簽顏色5．移動、復制工作表

圖6-12移動或復制工作表6.2.3輸入數(shù)據(jù)

1．數(shù)據(jù)輸入的一般方法2．數(shù)據(jù)類型（1）字符型（2）數(shù)值型（3）邏輯型3．單元格移動與復制（1）通過鼠標拖動操作（2）利用命令操作（3）選擇性粘貼圖6-13選擇性粘貼4．填充（1）通過鼠標拖動操作圖6-14填充（2）利用命令填充序列圖6-15填充序列（3）自定義序列?【例6-2】自定義序列“業(yè)務一部、業(yè)務二部、業(yè)務三部、業(yè)務四部”并進行填充。圖6-16添加自定義序列5．數(shù)據(jù)有效性?【例6-3】在如圖6-17所示的工作表中，要設定E3:E36區(qū)域內(nèi)的日期必須在2010年上半年內(nèi)。圖6-17工作表

圖6-18設置數(shù)據(jù)有效性條件

圖6-19設置數(shù)據(jù)有效性出錯警告6．清除與刪除單元格圖6-20“刪除”操作7．插入行、列或單元格6.2.4錯誤提示

圖6-21“被零除”錯誤信息

圖6-22字符型數(shù)值提示信息6.2.5設置單元格格式1．設置行高、列寬2．設置合適的行高、列寬3．隱藏行或列4．字體格式圖6-23設置單元格字體格式5．底紋圖6-24設置單元格底紋格式6．對齊方式圖6-25設置單元格對齊方式圖6-26設置單元格文本方向7．邊框線

圖6-27設置單元格邊框圖6-28繪圖邊框8．數(shù)值的不同顯示格式圖6-29設置單元格數(shù)值顯示格式（1）更改顯示的小數(shù)位數(shù)（2）改變負數(shù)的顯示格式（3）顯示或隱藏千位分隔符（4）以分數(shù)或百分比形式顯示數(shù)字（5）以科學記數(shù)法顯示數(shù)字（6）改變?nèi)掌诨驎r間格式（7）如何添加或刪除貨幣符號（8）將數(shù)字設置成文本格式（9）如何將存為文本的數(shù)字轉換成數(shù)字9．樣式與格式刷（1）創(chuàng)建樣式（2）應用樣式（3）修改樣式10．自動套用格式11．條件格式圖6-30設置條件格式12．保護工作表

圖6-31保護單元格

圖6-32單元格保護設置13．頁面設置（1）頁眉和頁腳圖6-33設置打印工作表的頁眉和頁腳（2）打印標題圖6-34設置打印工作表的標題6.3數(shù)據(jù)計算

6.3.1編輯公式1．輸入公式2．運算符（1）算術運算符（2）文本連接運算符（3）比較運算符（4）引用運算符3．運算次序（1）運算符優(yōu)先級（2）使用括號6.3.2復制公式

1．公式的復制與填充2．相對引用?【例6-4】如圖6-35所示的工作表，在B2單元格中輸入：=A2+B1，則返回“5”，也就是“2+3”的值。圖6-35工作表的相對引用3．絕對引用4．混合引用5．引用類型的判別

圖6-36工作表的絕對引用?【例6-5】制作九九乘積表。圖6-37九九乘積表6．公式的移動7．單元格引用的移動?【例6-6】如圖6-35所示的工作表，在B2單元格輸入=A2+B1，返回值為5。?將B1單元格移動到F6單元格后，B2單元格的公式變?yōu)椤癆2+F6”，但返回值仍然為5。6.4函數(shù)應用6.4.1函數(shù)概述圖6-38插入函數(shù)6.4.2使用函數(shù)的注意事項

6.4.3數(shù)學和三角函數(shù)（1）ABS(number)?【例6-7】=ABS(?3)，結果為3。（2）SUM(number1,number2,)?【例6-8】=SUM("3",TRUE,5)，結果為“9”。

圖6-39例6-9數(shù)據(jù)（3）SUMIF(range,criteria,sum_range)?【例6-10】要計算A1:A5區(qū)域中對應B1:B5區(qū)域中為“紅”的數(shù)值總和：=SUMIF(A1:A5,"紅"，B1:B5)，結果為“4”。

圖6-40例6-10數(shù)據(jù)（4）PRODUCT(number1,number2,)（5）INT(number)、TRUNC(number,num_digits)與ROUND(number,num_digits)?【例6-11】=INT(?88.88)，結果為?89；=INT(88.88)，結果為88。?【例6-12】=TRUNC(88.88,1)，結果為88.8。?【例6-13】=TRUNC(88.88)，結果為88；=TRUNC(?88.88)，結果為?88。?【例6-14】=TRUNC(88.88,?1)，結果為80。?【例6-15】=ROUND(88.88,1)，結果為88.9。（6）MOD(number,divisor)?【例6-16】=MOD(3,2)，結果為1；=MOD(?3,2)，結果為1；=MOD(3,?2)，結果為?1。（7）SQRT(number)?【例6-17】=SQRT(4)，即=4^(1/2)，結果為2。（8）PI()?【例6-18】求半徑為1的圓的面積，則公式為：=PI()*(1^2)。（9）RAND()?【例6-19】要隨機生成一個a與b之間的整數(shù)，則公式為：=INT(RAND()*(b?a+1))+a。（10）SIN(number)?例如，求30°（即弧度為/6）的正弦值，則可以輸入以下公式之一：=SIN(PI()/2)=SIN(30*PI()/180)=SIN(RADIANS(30))6.4.4統(tǒng)計函數(shù)（1）AVERAGE(number1,number2,)?【例6-20】=AVERAGE(A1:A4)，結果為“4”。即(3+4+5)/3=4，A3是空白單元格，忽略不計（見圖6-41）。

圖6-41例6-20數(shù)據(jù)（2）COUNT(value1,value2,)與COUNTA(value1,value2,)?【例6-21】=COUNT(A1:A4)，結果為“2”；COUNTA(A1:A4)，結果為“3”。?其中A3為空白單元格，A4為文字型單元格（見圖6-42）。圖6-42例6-21數(shù)據(jù)（3）COUNTIF(range,criteria)?【例6-22】要計算A1:A5內(nèi)大于等于4的單元格個數(shù)，則輸入公式：=COUNTIF(A1:A5,">=4")，結果為2（見圖6-43）。圖6-43例6-22數(shù)據(jù)（4）MAX(number1,number2,)與MIN(number1,number2,)（5）LARGE(array,k)與SMALL(array,k)?【例6-23】假設array區(qū)域中有n個數(shù)值，則函數(shù)LARGE(array,1)或SMALL(array,n)返回最大值，函數(shù)LARGE(array,n)或SMALL(array,1)返回最小值。（6）RANK(number,ref,order)?【例6-24】在一系列按升序排列的數(shù)值中，如果數(shù)值10出現(xiàn)兩次，其排位均為5，則下一個比10大的數(shù)值排位為7（沒有排位為6的數(shù)值）。（7）FREQUENCY(data_array,bins_array)?【例6-25】分別統(tǒng)計下表中60分以下（即不及格）、60～69、70～79、80～89、90～99、99分以上的學生人數(shù)。?則：①在C2:C6單元格區(qū)域依次輸入間隔點59、69、79、89、99。?注意不是60、70、80、90、100，因為每個間隔點的含義是小于或等于，如果設置為60，會將60分當作不及格統(tǒng)計。②選擇D2:D7單元格區(qū)域。③在編輯欄輸入：=FREQUENCY(B2:B9,C2:C6)。④按【Ctrl+Shift+Enter】組合鍵。?結果顯示在D2:D7單元格區(qū)域，其中D7單元格顯示的是99分以上的學生人數(shù)。?數(shù)據(jù)如圖6-44所示。圖6-44例6-25數(shù)據(jù)6.4.5財務函數(shù)

①rate參數(shù)為利率。②nper參數(shù)是支付間隔期的總數(shù)。③pmt參數(shù)表示各期應支付的金額。④pv參數(shù)表示現(xiàn)值，也稱為本金。⑤fv參數(shù)表示未來值。⑥type參數(shù)表示取1或0。⑦guess參數(shù)代表預期利率。6.4.6日期與時間函數(shù)（1）DATE(year,month,day)（2）TIME(hour,minute,second)（3）YEAR(serial_number)、MONTH(serial_number)與DAY(serial_number)（4）HOUR(erial_number)、MINUTE(erial_number)與SECOND(erial_number)（5）NOW()（6）TODAY()6.4.7文本函數(shù)

（1）LEFT(text,num_chars)與RIGHT(text,num_chars)（2）MID(text,start_num,num_chars)（3）FIND(find_text,within_text,start_num)與SEARCH(find_text,within_text,start_num)（4）FIXED(number,decimals,no_commas)與VALUE(text)FIXED()函數(shù)是將數(shù)值number轉化為文本型數(shù)據(jù)，并且四舍五（5）TEXT(value,format_text)（6）TRIM(text)圖6-45數(shù)值單元格格式6.4.8邏輯函數(shù)（1）NOT(logical)（2）AND(logical1,logical2,)與OR(logical1,logical2,)（3）IF(logical_test,value_if_true,value_if_false)圖6-46二叉樹6.4.9查找與引用函數(shù)1．VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)與HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

圖6-50

VLOOKUP()函數(shù)參數(shù)

圖6-51

HLOOKUP()函數(shù)參數(shù)圖6-52學生分數(shù)表2．

MATCH(lookup_value,lookup_array,match_type)3．

INDEX(reference,row_num,column_num,area_num)4．

CHOOSE（index_num,value1,value2,）6.5制作圖表

6.5.1圖表概述圖6-54表格圖6-55圖表6.5.2創(chuàng)建圖表

（1）選擇圖表類型①柱形圖。

圖6-56選擇圖表類型②條形圖。③餅圖。④圓環(huán)圖。⑤折線圖。⑥面積圖。⑦XY散點圖。圖6-57查看圖表類型示例（2）選擇圖表源數(shù)據(jù)

圖6-58選擇圖表數(shù)據(jù)區(qū)域

圖6-59設置圖表系列（3）設置圖表各個選項圖6-60設置圖表標題圖6-61設置圖表坐標軸圖6-62設置圖表網(wǎng)格線圖6-63設置圖表圖例圖6-64設置圖表數(shù)據(jù)標志圖6-65設置圖表數(shù)據(jù)表（4）指定圖表位置①嵌入圖表圖6-66指定圖表位置②圖表工作表6.5.3編輯圖表

1．選中（1）選中圖表（2）選中圖表的組成部分圖6-67選擇圖表組成部分2．修改圖表3．格式設置圖6-68設置圖表區(qū)格式4．移動圖表（1）在同一工作表中移動（2）在不同工作表中移動5．改變圖表大小6．改變圖表類型7．改變圖表選項8．添加文本6.6數(shù)據(jù)庫與數(shù)據(jù)分析

6.6.1數(shù)據(jù)庫與數(shù)據(jù)庫函數(shù)1．數(shù)據(jù)庫基本概念（1）數(shù)據(jù)庫圖6-69數(shù)據(jù)庫（2）字段①字段名②字段值（3）記錄2．DCOUNT(database,field,criteria)與DCOUNTA(database,field,criteria)圖6-70比較條件式圖6-71重復第1行字段名的比較條件式3．DSUM(database,field,criteria)4．DAVERAGE(database,field,criteria)5．DMAX(database,field,criteria)與DMIＮ(database,field,criteria)6.6.2數(shù)據(jù)排序

1．排序的依據(jù)①數(shù)值②字母③漢字④混合⑤撇號（‘）和連字符（-）⑥邏輯值⑦錯誤值⑧自定義序列⑨空白單元格2．操作步驟（1）方法一：利用工具欄命令按鈕（2）方法二：利用菜單命令（3）方法三：對不規(guī)范數(shù)據(jù)庫的排序

圖6-72排序

圖6-73設置排序選項6.6.3數(shù)據(jù)篩選

1．自動篩選（1）操作步驟圖6-74自動篩選前10項

圖6-75自定義自動篩選（2）取消篩選圖6-79自定義自動篩選業(yè)務一部2010年3月份的記錄2．高級篩選（1）操作步驟

圖6-80高級篩選（2）恢復數(shù)據(jù)庫顯示6.6.4分類匯總

1．分類匯總分類匯總的操作步驟如下。①對分類匯總的字段進行排序，升序、降序均可。②選中需要進行分類匯總的數(shù)據(jù)庫中的任意單元格。③選擇【數(shù)據(jù)】→【分類匯總】命令。④在彈出的“分類匯總”對話框中進行設置。2．刪除分類匯總的結果6.6.5數(shù)據(jù)透視表

1．創(chuàng)建數(shù)據(jù)透視表圖6-84數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之1

圖6-85數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之2圖6-86數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之3圖6-87數(shù)據(jù)透視表布局圖6-88數(shù)據(jù)透視表

圖6-89分組

圖6-90分組后的數(shù)據(jù)透視表圖6-91調(diào)整順序后的數(shù)據(jù)透視表2．修改數(shù)據(jù)透視表3．刷新數(shù)據(jù)透視表4．刪除數(shù)據(jù)透視表6.7

Excel實驗

?本實驗所有任務的素材均在“Excel應用實驗”文件夾內(nèi)。實驗1數(shù)據(jù)輸入與格式設置一、技能掌握要求1．在單元格輸入數(shù)據(jù)；2．快速填充序列。3．設置數(shù)據(jù)有效性的限制選項；4．設置單元格的字體、邊框、對齊等格式；5．設置條件格式。二、實驗過程?打開“成績表”工作簿按以下要求進行操作，完成后以原文件名保存。1．在A1單元格輸入標題：“《辦公軟件》課程成績”。2．在B17單元格輸入自己的姓名。3．在B18單元格輸入當天的日期，并顯示為中文的“****年*月*日”。圖6-92單元格格式設置4．在A3:A12單元格區(qū)域輸入序列101～110。5．在B3:B12單元格區(qū)域依次輸入“甲”、“乙”、“丙”、“丁”、“戊”、“己”、“庚”、“辛”、“壬”、“癸”。6．將A20:J20單元格區(qū)域的數(shù)據(jù)轉置復制到F3:F12單元格區(qū)域。

圖6-93轉置粘貼7．刪除A22:J22單元格區(qū)域的數(shù)據(jù)，將下方單元格上移。8．設置E3:F12單元格區(qū)域的有效性，輸入的成績必須在0～100間，否則出現(xiàn)停止樣式的警告信息對話框，標題為“輸入錯誤”，信息為“分數(shù)必須在0至100之間”。?以上單元格均忽略空值。圖6-94設置數(shù)據(jù)有效性圖6-95設置出錯警告對話框9．設置C3:C12單元格區(qū)域的有效性，只能輸入“男”或“女”。?輸入時單元格右側顯示下三角按鈕，并提供“男”、“女”選項。?然后，C3:C12單元格區(qū)域分別為“男”、“女”、“男”、“男”、“女”、“女”、“男”、“男”、“女”、“男”。圖6-96有效性設置10．將A1:G1單元格區(qū)域合并居中，設置其字體為黑體，字號為22，顏色為紫色（紫羅蘭），底紋顏色為淺青綠，字形加粗、傾斜，添加雙下畫線。圖6-97合并居中11．設置A2:G2單元格區(qū)域的字體為仿宋GB2312，底紋顏色為淺藍，底紋圖案為細逆對角線條紋，有內(nèi)外邊框線，水平對齊方式為分散對齊（縮進），垂直對齊方式為居中。圖6-98設置單元格底紋12．設置E3:G12單元格區(qū)域的條件格式，對于小于60分的分數(shù)，字體設置為紅色，60～89之間的分數(shù)，字體設置為綠色，90分及以上為金色。?

當單元格分數(shù)有修改時，其格式會相應變化。圖6-99條件格式13．給A2:G12單元格區(qū)域設置邊框線，其中外邊框線為雙實線，內(nèi)邊框線為單實線。14．將A21單元格內(nèi)的“備注”文字設置為豎向排列，水平、垂直均居中。圖6-100單元格對齊格式設置15．將工作表Sheet1重命名為“成績表”，設置其標簽顏色為紅色。實驗2公式與簡單函數(shù)應用一、技能掌握要求1．公式的輸入；2．相對引用與絕對引用的應用；3．常用函數(shù)SUM()、AVERAGE()、COUNT()、MAX()、MIN()的應用。二、實驗過程?打開完成上次任務后保存的“成績表”工作簿，按以下要求進行操作，完成后以原文件名保存。1．在G3單元格中輸入公式：=

E3*0.3+F3*0.7，然后填充至G4:G12單元格區(qū)域。2．使用函數(shù)計算作業(yè)總次數(shù)（結果放在D13單元格）、總評成績的平均分（結果放在G13單元格）、最高分（結果放在G14單元格）、最低分（結果放在G15單元格）、學生總人數(shù)（結果放在D14單元格）。圖6-101函數(shù)按鈕3．打開“酬金表”工作表，在B15單元格計算全年的酬金收入；在C4:C14單元格區(qū)域計算每月累計值，要求使用公式填充；在D4:D14單元格區(qū)域計算當月酬金占全年總收入的比例值，要求使用公式填充，顯示為保留1位小數(shù)的百分比形式。4．在“酬金表”工作表中使用函數(shù)和公式在D19:D26單元格區(qū)域計算日薪，計算公式為：日薪=基本日薪+時薪工資，其中基本日薪為50元，時薪每小時8元。?將該區(qū)域設置為前綴為“CNY”的貨幣格式，保留到整數(shù)位。5．在“成績表”工作簿中新建一個工作表，命名為“公式學習”。?在A1:A5單元格區(qū)域分別輸入以下公式，看看返回值分別是什么？并想想為什么。①在A1單元格輸入：=2+2。②在A2單元格輸入：=2&2。③在A3單元格輸入：=2。④在A4單元格輸入：=2=2。⑤在A5單元格輸入：=2=2=2。實驗3數(shù)值計算函數(shù)應用一、技能掌握要求1．數(shù)學函數(shù)ROUND()、SUMIF()的應用；2．統(tǒng)計函數(shù)COUNTA()、COUNTIF()、LARGE()、SMALL()、RANK()的應用。3．日期函數(shù)TODAY()、NEW()、DATA()的應用；4．財務函數(shù)PMT()、FV()的應用。二、實驗過程?

打開“數(shù)值計算函數(shù)應用”工作簿，按以下要求進行操作，完成后以原文件名保存。1．在“四舍五入”工作表中，對A3:A14單元格區(qū)域的數(shù)值使用函數(shù)公式進行計算，分別填充四舍五入后保留2位小數(shù)的值；在A3:A14單元格區(qū)域分別填充四舍五入到整數(shù)的值；在A3:A14單元格區(qū)域分別填充四舍五入到百位的值。2．在“條件”工作表中，在F3:F14單元格區(qū)域分別輸入包含函數(shù)的公式，分別統(tǒng)計學生總數(shù)、女生人數(shù)、及格學生人數(shù)、不及格學生人數(shù)、所有學生的成績總和、女生成績總和、及格學生的成績總和、不及格學生的成績總和、所有學生的成績平均分、女生的成績平均分、及格學生的成績平均分、不及格學生的成績平均分。?

最后4項平均分使用函數(shù)公式計算，且公式中不引用F列的單元格數(shù)據(jù)，結果保留到整數(shù)。3．在“統(tǒng)計”工作表中，對B13:B16單元格區(qū)域分別使用函數(shù)公式計算最高分、次高分、最低分、次低分；在C2:C11單元格區(qū)域使用函數(shù)公式計算對應學生的名次并填充。4．打開“日期與財務”工作表①在B2:B4單元格區(qū)域分別輸入含函數(shù)的公式計算當天的日期、當天的日期及時間、相對于當天8年8月8日以后的日期（要求能隨時間變換而更新）。②在B5單元格中計算當天之后100天的日期。③銀行貸款50萬買房，20年還清，假設現(xiàn)時的年利率為6.12%，每月月底等額還款，在B7單元格使用公式計算出每月還款金額。④小努每月拿1000元存入銀行，方式為零存整取，共五年，假設現(xiàn)時的年利率為2.52%，在B8單元格使用公式計算小努五年之后可以取回多少錢。實驗4邏輯、查找函數(shù)應用一、技能掌握要求1．邏輯函數(shù)IF()的應用；2．查找函數(shù)VLOOKUP()的應用；3．各種函數(shù)的綜合應用。二、實驗過程?

打開“邏輯查找函數(shù)應用”工作簿，按以下要求進行操作，完成后以原文件名保存。1．在“成績等次”工作表中①在C2:C11單元格區(qū)域填充函數(shù)公式，如果成績在60分及以上，顯示“通過”；否則顯示“不通過”。②在D2:D11單元格區(qū)域填充函數(shù)公式，如果成績在60分以下，顯示“不合格”；在60～80之間，顯示“合格”；在80分及以上，顯示“優(yōu)秀”。③在E2:E11單元格區(qū)域填充函數(shù)公式，顯示成績的等級。?

成績在90分及以上為“優(yōu)”；80分及以上為“良”；70分及以上為“中”；60分及以上為“可”；60分以下為“差”。④在F2:F11單元格區(qū)域填充函數(shù)公式。?如果某位同學總分為最高分，對應的單元格顯示為“尖子”，其余則顯示為“一般”。?要求使用公式完成，當數(shù)據(jù)修改后，單元格能進行相應的改變。2．打開“稅金”工作表①在C3單元格中輸入公式計算稅金。?假設工資不超過1600元的部分不扣稅，在1600～3000元的部分扣稅金6%，在3000元以上的部分扣稅金10%，然后將該公式復制到C4:C10單元格區(qū)域，“稅金”的數(shù)值保留2位小數(shù)。②在D3:D10區(qū)域中輸入公式計算每個人在扣除稅金之后的實際收入，數(shù)值保留2位小數(shù)。③如果現(xiàn)在的時刻介于5:30至19:00之間，在A14單元格中顯示“白晝”；否則顯示“黑夜”。3．甲、乙兩城市電話號碼升為8位，甲城市的升位方法：在原7位號碼前加“8”，乙城市：在原“2”、“3”開頭的7位號碼前加“2”，其余在原7位號碼前加“8”。?

打開“電話本”工作表，按上述規(guī)則在D3:D17單元格區(qū)域輸入新的電話號碼。①首先要判斷電話號碼所屬的城市是不是“甲”，可以使用IF()函數(shù)判斷，列出D3單元格的大概公式為=IF(B3="甲"，使用甲城市的升級方法，再判斷是否是“乙”城市)②判斷是否為乙城市，如果也不是，則電話號碼照舊。?

使用IF函數(shù)嵌套，將公式進一步改進為=IF(B3="甲"，使用甲城市的升級方法，(IF(B3="乙"，使用乙城市的升級方法，C3))③細化甲城市的升級方法。?

電話號碼的數(shù)據(jù)類型為文本型，只要在其前面加入文本“8”即可升位，文本的連接符為“&”。?因此甲城市的升級方法為："8"&C3，因此公式改進為=IF(B3="甲","8"&C3,(IF(B3="乙",使用乙城市的升級方法,C3))④乙城市的升級方法分兩種情況，要根據(jù)舊號碼的首位判斷。?可使用LEFT()函數(shù)取號碼的首位：LEFT(C3,1)或LEFT(C3)（因為LEFT()函數(shù)的第二個參數(shù)為“1”時可以省略）。?以“2”、“3”開頭的表示為OR(LEFT(C3)=“2”，LEFT(C3)=“3”)。?注意不要使用AND()函數(shù)，因為不是判斷首位同時為“2”與“3”，而是首位為“2”或“3”，所以要使用OR()函數(shù)。?OR()函數(shù)中的參數(shù)必須是能返回TRUE或者FALSE的表達式。?不要寫成LEFT(C3)=OR（"2","3"）乙城市的升級方法使用IF()函數(shù)列出：IF(OR(LEFT(C3)="2",LEFT(C3)="3"),"2"&C3,"8"&C3)⑤綜上所述，可以得到最終的公式。?要仔細注意括號的匹配，不要錯漏。4．打開“查找”工作表，在C25單元格輸入函數(shù)公式，給出張素蘭同學的成績。實驗5圖表制作一、技能掌握要求1．利用“圖表向?qū)А敝谱鲌D表；2．設置圖表的格式。二、實驗過程?

打開“圖表”工作簿，按以下要求進行操作，完成后以原文件名保存。1．打開“統(tǒng)計表”工作表，插入圖表，圖表類型為三維柱形圖，顯示業(yè)務一部、業(yè)務三部在1～6月的業(yè)績，系列產(chǎn)生在列，圖表標題為“2010年上半年業(yè)績統(tǒng)計表”，系列（Y）軸與數(shù)值（Z）軸有主要網(wǎng)格線，圖例靠左，顯示數(shù)據(jù)表。圖6-102圖表網(wǎng)格線設置?

顯示數(shù)據(jù)表，則在圖表向?qū)У?步選擇“數(shù)據(jù)表”選項卡，進行相應設置（見圖6-103）。

圖6-103圖表數(shù)據(jù)表顯示2．打開“學生人數(shù)”工作表①建立數(shù)據(jù)點折線圖，以顯示不同年級資訊科技系各專業(yè)的人數(shù)。?設置圖表標題為“資訊科技系學生人數(shù)一覽”，分類軸標題為“年級”，數(shù)值軸標題為“人數(shù)”，數(shù)值軸刻度的最小值為0，最大值為500，主要刻度單位為50，建立的圖表嵌入在該工作表中。②將圖表標題“資訊科技系學生人數(shù)一覽”字號設為12，其余文字字號設為10。3．打開“預算表”工作表，使用“項目”與“實際支出”中的數(shù)據(jù)創(chuàng)建一個分離型三維餅圖。?

系列產(chǎn)生在列，圖表標題為“項目實際支出”，圖例位于圖表的左方，數(shù)據(jù)標志顯示百分比，建立的圖表嵌入在該工作表中。實驗6數(shù)據(jù)分析一、技能掌握要求1．數(shù)據(jù)庫函數(shù)的應用；2．數(shù)據(jù)排序；3．數(shù)據(jù)自動篩選與高級篩選；4．分類匯總數(shù)據(jù)；5．制作數(shù)據(jù)透視表。二、實驗過程?

打開“數(shù)據(jù)庫分析”工作簿，按以下要求進行操作，完成后以原文件名保存。1．打開“員工表（1）”工作表①在E12:E15單元格區(qū)域分別使用數(shù)據(jù)庫函數(shù)計算30歲以上工程師的工資總和、平均工資、最高工資以及人數(shù)。②對數(shù)據(jù)區(qū)域A2:E10進行排序，有標題行，以“姓名”為主要關鍵字按筆畫遞增，以“年齡”為次要關鍵字遞減，以“工齡”為第三關鍵字遞減。

圖6-104按筆畫排序

2．打開“員工表（2）”工作表①對數(shù)據(jù)區(qū)域A2:E10進行排序，有標題行，以“年齡”為主要關鍵字遞減，以“工齡”為次要關鍵字遞減。②篩選出“工齡”不小于12，且職稱為工程師的記錄。?

用同樣的方法對“職稱”字段進行自定義篩選。

圖6-105自定義自動篩選3．打開“員工表（3）”工作表，運用高級篩選操作，把年齡不小于35歲的工程師，以及工齡16年以上的高級工程師記錄篩選到以A13為左上角單元格的區(qū)域中，選擇不重復的記錄。?條件區(qū)域設在以G3為左上角單元格的區(qū)域中。

圖6-106高級篩選4．打開“銷售表”工作表，以“品牌”為分類字段，對銷售量（臺）、總銷售額（元）進行求和匯總，匯總結果顯示在數(shù)據(jù)下方。

圖6-107分類匯總5．打開“錄取表”工作表，使用該工作表的數(shù)據(jù)建立數(shù)據(jù)透視表，布局以“類別”為行字段，以“錄取批次”為列字段，以“武許二中”上線人數(shù)為求和項，將數(shù)據(jù)透視表建立在新的工作表中。圖6-108數(shù)據(jù)透視表布局?

結果6-109所示。圖6-109數(shù)據(jù)透視表6．打開“訂單”工作表，根據(jù)工作表中的數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)透視表，以反映不同性別（行字段）、不同部門（列字段）的平均訂單金額（所有數(shù)值只保留2位小數(shù)）。?

取消行總計與列總計項，并命名為“訂單數(shù)據(jù)透視表”。?將所創(chuàng)建的透視表放在“訂單”工作表以A41單元格開始的區(qū)域中，并將“部門”字段按“業(yè)務一部”、“業(yè)務二部”、“業(yè)務三部”、“業(yè)務四部”的次序排列。①選擇【數(shù)據(jù)】→【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】命令，在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А钡牡?步設置布局，然后雙擊布局中的“求和項:訂單金額”按鈕（見圖6-110）。?在打開的“數(shù)據(jù)透視表字段”對話框中，選擇匯總方式為“平均值”（見圖6-111），然后單擊“數(shù)字”按鈕，在彈出的“單元格格式”對話框中將數(shù)字設置為小數(shù)位數(shù)為“2”的數(shù)值（見圖6-112）。?完成以上操作后，布局顯示為“平均值項:訂單金額”（見圖6-113）。

圖6-110數(shù)據(jù)透視表布局

圖6-111數(shù)據(jù)透視表字段

圖6-112數(shù)值的顯示方式

圖6-113數(shù)據(jù)透視表布局?

在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А钡?步的對話框中選擇數(shù)據(jù)透視表顯示位置為“現(xiàn)有工作表”，將光標移入其下的文本框，單擊工作表的A41單元格，則顯示“訂單!$A$41”（見圖6-114）。圖6-114數(shù)據(jù)透視表位置?單擊“選項”按鈕，在打開的“數(shù)據(jù)透視表選項”對話框中取消選中“行總計”與“列總計”復選框，并在名稱文本框中輸入“訂單數(shù)據(jù)透視表”（見圖6-115）。圖6-115數(shù)據(jù)透視表選項②選中“部門”字段的選項“業(yè)務一部”等，右擊后在彈出的快捷菜單中選擇【順序】命令，移動該選項的位置，從而調(diào)整“部門”字段選項的次序。6.8Excel實訓

項目1制作成績單一、職業(yè)情景?企業(yè)需要對員工的績效進行考核，以促進企業(yè)發(fā)展。?績效考核的表格類似成績單，本項目以同學們熟悉的成績單為例，學習相關技能，既可以在平日輔助老師的工作，也可以在日后舉一反三，制作績效考核表格。二、能力運用

1．設置單元格格式，美化表格；2．綜合運用數(shù)學、統(tǒng)計函數(shù)進行數(shù)據(jù)運算，得出統(tǒng)計結果。三、任務要求?制作成績單，滿足以下要求：1．課程成績包括平時成績與期末考試成績，當輸入成績時，如果范圍不