Excel 2003電子表格軟件使用

1、1第第5章章 電子表格軟件電子表格軟件Excel 2003的使用的使用 日常生活和工作中，經(jīng)常需要處理數(shù)據(jù)和表格，Microsoft Office Excel 2003具有強(qiáng)大的數(shù)據(jù)計(jì)算、分析和處理功能，并且可以完成各種統(tǒng)計(jì)圖表的繪制，已經(jīng)廣泛地用于金融、財(cái)務(wù)、企業(yè)管理、行政管理等領(lǐng)域。 2 5.1 認(rèn)識(shí)Excel 2003 5.2實(shí)踐案例1工資表編制 5.3實(shí)踐案例2學(xué)生成績(jī)分析35.1 認(rèn)識(shí)認(rèn)識(shí)Excel 2003 Excel文件稱為工作簿，其默認(rèn)文件名為Book1（依次為Book2，Book3），擴(kuò)展名為.xls。每個(gè)工作簿中可以創(chuàng)建多張工作表，每張工作表可以存儲(chǔ)不同的數(shù)據(jù)。Excel

2、2003工作窗口如圖5.1所示。 菜單欄工作簿名稱 【常用】工具欄【格式】工具欄編輯欄行標(biāo)， 一個(gè)工作表最多可容納 65 536 行 工作表名狀態(tài)欄 列標(biāo)， 一個(gè)工作表最多可容納 256 列 4 菜單欄工作簿名稱 【常用】工具欄【格式】工具欄編輯欄行標(biāo)， 一個(gè)工作表最多可容納 65 536 行 工作表名狀態(tài)欄 列標(biāo)， 一個(gè)工作表最多可容納 256 列 5 5.1.1 數(shù)據(jù)編輯 5.1.2 公式和函數(shù) 5.1.3 圖表的建立 5.1.4 其他功能65.1.1 數(shù)據(jù)編輯數(shù)據(jù)編輯 1數(shù)據(jù)輸入 活動(dòng)單元格 移動(dòng)“活動(dòng)單元格 2數(shù)據(jù)修改3單元格的選取與復(fù)制 （1）單元格的選?。哼B續(xù)區(qū)域的選擇、非連續(xù)區(qū)域

3、的選擇。 （2）單元格的復(fù)制：選取了單元格后，單擊【常用】工具欄上【復(fù)制】按鈕 ；把鼠標(biāo)定位到要復(fù)制到單元格區(qū)域的起始單元格，單擊【粘貼】按鈕 。74數(shù)據(jù)的清除、刪除與插入 1）數(shù)據(jù)清除 2）數(shù)據(jù)的刪除 刪除單元格：選定一個(gè)或多個(gè)單元格，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【刪除】選項(xiàng)，彈出【刪除】對(duì)話框，可選取“右側(cè)單元格左移”或“下方單元格上移”，如圖5.3所示。我們要?jiǎng)h除第3、4行第一個(gè)單元格，并且讓“下方單元格上移”，然后單擊【確定】按鈕，下方的單元格向上移動(dòng)，第6、7行內(nèi)單元格沒(méi)有數(shù)據(jù)了。8 刪除行（列）：選中要?jiǎng)h除的一行（列）或多行（列），單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【刪除

4、】選項(xiàng)，即可以把這些行（列）刪除。 單元格的插入、行（列）的插入，與單元格、行（列）的刪除操作類似。95. 單元格格式設(shè)置 （1）數(shù)字分類設(shè)置：【格式】菜單下選擇【單元格】選項(xiàng)，彈出【單元格格式】對(duì)話框 名 稱說(shuō) 明常規(guī)系統(tǒng)默認(rèn)格式數(shù)值適用一般數(shù)字，可設(shè)置“小數(shù)位數(shù)”、“使用千位分隔符”、“負(fù)數(shù)”表示方式貨幣與數(shù)值類型相似，不過(guò)可以在數(shù)值前面添加貨幣符號(hào)會(huì)計(jì)專用與貨幣相似，還可以進(jìn)行貨幣符號(hào)和小數(shù)點(diǎn)對(duì)齊顯示日期提供多種日期格式，并提供了不同國(guó)家的日期格式時(shí)間提供多種時(shí)間格式，并提供了不同國(guó)家的時(shí)間格式百分比以百分?jǐn)?shù)的形式顯示單元格數(shù)值，并可以設(shè)置小數(shù)位數(shù)科學(xué)計(jì)數(shù)數(shù)值以科學(xué)計(jì)數(shù)法表示，可以設(shè)置小

5、數(shù)位數(shù)文本任何格式數(shù)據(jù)都可以以文本類型處理特殊提供特殊格式的選擇，如郵政編碼、電話號(hào)碼等自定義用戶可以根據(jù)自己需要自定義格式10（2）條件格式設(shè)置 題目：將圖5.1的工作表中，價(jià)格大于50的單元格內(nèi)容以斜體、加粗、紅色顯示，步驟如下：1）選擇要設(shè)置格式的區(qū)域，即選擇D列的第37行單元格，單擊【格式】菜單，選擇【條件格式】選項(xiàng)。在彈出的【條件格式】對(duì)話框中的【條件1】選項(xiàng)區(qū)域中，選擇“單元格數(shù)值”、“大于”，在最后的文本框中輸入“50”，然后單擊【格式】按鈕，如圖5.4所示。圖5.4 條件設(shè)置圖5.5 符合條件的單元格格式設(shè)置 112）在彈出的【單元格格式】對(duì)話框中，選擇【字體】選項(xiàng)卡，將【字形

6、】設(shè)置為“加粗，傾斜”，將【顏色】設(shè)置為“紅色”，設(shè)置效果如圖5.5所示 。 12 （3）自動(dòng)套用格式步驟：選擇單元格區(qū)域第27行有數(shù)據(jù)的區(qū)域，單擊【格式】菜單，選擇【自動(dòng)套用格式】選項(xiàng)，在【自動(dòng)套用格式】對(duì)話框中選擇【古典2】格式（見圖5.6）。最后單擊【確定】按鈕，完成自動(dòng)套用格式設(shè)置，設(shè)置效果如圖5.7所示。 圖5.6 自動(dòng)套用格式選擇圖5.7 自動(dòng)套用格式后效果返回135.1.2 公式和函數(shù)1）相對(duì)引用地址：這種方式的地址引用，會(huì)因?yàn)楣剿诘奈恢玫淖兓l(fā)生對(duì)應(yīng)的變化，例如某公式中引用了“A3”單元格，當(dāng)該公式復(fù)制到其他單元格時(shí)，此公式中的相對(duì)地址會(huì)隨之發(fā)生變化。2）絕對(duì)引用地址：這

7、種方式的地址引用，地址不會(huì)因?yàn)楣剿诘奈恢玫淖兓l(fā)生變化，當(dāng)該公式復(fù)制到其他單元格時(shí)，此公式中的絕對(duì)地址是不會(huì)發(fā)生變化的，其引用方式是在單元格地址的列名、行標(biāo)前都加上“$”符號(hào)，例如某公式中絕對(duì)引用了“A3”單元格，公式中引用的地址應(yīng)寫成“$A$3”。3）混合引用地址：如果我們需要固定某列而變化某行，或是固定某行而變化某列的引用時(shí)，采用混合引用地址，其表示方式為“$A3”或 “A$3”。 141運(yùn)算符 （1）算術(shù)運(yùn)算符（6個(gè)） 算術(shù)運(yùn)算符的作用是完成基本的數(shù)學(xué)運(yùn)算，包括加（）、減（）、乘（*）、除（/）、百分?jǐn)?shù)（）和乘方（）。 （2）比較操作符（6個(gè)） 比較運(yùn)算符的作用是可以比較兩個(gè)值，結(jié)

8、果為一個(gè)邏輯值，是“TRUE”或是“FALSE”。包括等于（=）、大于（）、小于（、大于等于（=）、小于等于（=）和不等于（）。15（3）文本連接符（1個(gè)） 使用文本連接符（）可連接一個(gè)或更多個(gè)字符串以產(chǎn)生一長(zhǎng)文本。例如：“2008年”“北京奧運(yùn)會(huì)”就產(chǎn)生“2008年北京奧運(yùn)會(huì)”。 （4）引用操作符（3個(gè)） 冒號(hào)（：）連續(xù)區(qū)域運(yùn)算符，對(duì)兩個(gè)引用之間（包括兩個(gè)引用在內(nèi)）的所有單元格進(jìn)行引用。如SUM（B5：C10），計(jì)算B5到C10的連續(xù)12個(gè)單元格之和。 逗號(hào)（，）聯(lián)合操作符，可將多個(gè)引用合并為一個(gè)引用。如SUM（B5：B10，D5：D10），計(jì)算B列、D列共12個(gè)單元格之和。 空格取多個(gè)引用

9、的交集為一個(gè)引用，該操作符在取指定行和列數(shù)據(jù)時(shí)很有用。如SUM（B5：B10 A6：C8），計(jì)算B6到B8三個(gè)單元格之和。162常用函數(shù) Excel 2003函數(shù)一共有11類，分別是數(shù)據(jù)庫(kù)函數(shù)、日期與時(shí)間函數(shù)、工程函數(shù)、財(cái)務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計(jì)函數(shù)、文本函數(shù)以及用戶通過(guò)Visual Basic編輯器創(chuàng)建的自定義函數(shù)。 （1）求和函數(shù)SUM 格式：SUM（number1,number2,） 功能：返回參數(shù)對(duì)應(yīng)的數(shù)值的和。17 （2）求平均值函數(shù)AVERAGE 格式：AVERAGE（number1,number2,） 功能：返回參數(shù)對(duì)應(yīng)的數(shù)值的平均值。 （

10、3）求最大值函數(shù)MAX、最小值函數(shù)MIN 格式：MAX（number1,number2,）、MIN（number1,number2,） 功能：返回參數(shù)對(duì)應(yīng)的數(shù)值的最大值、最小值。18 （4）四舍五入函數(shù)ROUND 格式：ROUND（number,num_digits） 功能：返回參數(shù)number按指定位數(shù)num_digits的四舍五入值。如果num_digits=0，則四舍五入到整數(shù)；如果num_digits0,G3-SUM(I3:K3)-2000,0)”，按Enter鍵，Excel 2003會(huì)自動(dòng)計(jì)算出員工“王遐”的4月份應(yīng)稅金額。 416）計(jì)算“個(gè)人所得稅”。與不同的“應(yīng)稅金額”相對(duì)應(yīng)的稅

11、率和速算扣除數(shù)，如表5.3所示。 利用IF函數(shù)的嵌套功能來(lái)實(shí)現(xiàn)個(gè)人所得稅的計(jì)算。在單元格M3中輸入公式“=IF(L3=500,L3*5%,IF(L3=2000,L3*10%-25,IF(L3=5000,L3*15%-125,IF(L3=20000,L3*20%-375,IF(L3=40000,L3*25%-1375,IF(L3=60000,L3*30%-3375,IF(L3=80000,L3*35%-6375,IF(L3100 00045%15 375表5.3 稅率及速算扣除數(shù)計(jì)算規(guī)則表 圖5.56 “個(gè)人所得稅”的計(jì)算42 7）計(jì)算“實(shí)發(fā)金額”?！皩?shí)發(fā)金額”是員工在繳納各類保險(xiǎn)、稅收等各類款

12、項(xiàng)后實(shí)際發(fā)到手里的工資金額。很顯然，“實(shí)發(fā)金額”=“應(yīng)發(fā)金額”“失業(yè)保險(xiǎn)”“醫(yī)療保險(xiǎn)”“養(yǎng)老保險(xiǎn)”“個(gè)人所得稅”。 在N4中輸入公式“=G3-SUM(I3:K3)-M3”，按Enter鍵，Excel 2003會(huì)自動(dòng)計(jì)算出員工“王遐”的實(shí)發(fā)金額為3031.20元。 43 8）完成其他員工工資計(jì)算。因?yàn)槠渌麊T工未計(jì)算的工資分布在兩個(gè)區(qū)域中，我們分兩步完成。 選中G3單元格，按住鼠標(biāo)左鍵向下拖動(dòng)單元格右下角的填充柄，直到最后一位員工所在單元格（“G14”），松開鼠標(biāo)左鍵，完成其他員工“應(yīng)發(fā)金額”的計(jì)算。 選中I3：N3單元格區(qū)域，按住鼠標(biāo)左鍵向下拖動(dòng)單元格右下角的填充柄，直到最后一位員工所在行，松開

13、鼠標(biāo)左鍵，完成其他員工“失業(yè)保險(xiǎn)”、“醫(yī)療保險(xiǎn)”、“養(yǎng)老保險(xiǎn)”、“應(yīng)稅金額”、“個(gè)人所得稅”、“實(shí)發(fā)金額”的計(jì)算，如圖5.57所示。圖5.57 其他員工工資的計(jì)算 返回445.3.4 數(shù)據(jù)排序 要按員工所在部門進(jìn)行統(tǒng)計(jì)，所以我們需要對(duì)“部門”進(jìn)行排序，同一個(gè)部門里的員工，我們按“實(shí)發(fā)金額”由高到低排序。排序步驟如下：1）選取除標(biāo)題行外所有區(qū)域的單元格，在【數(shù)據(jù)】菜單下選擇【排序】選項(xiàng)，彈出【排序】對(duì)話框。2）在【排序】對(duì)話框中，從【主要關(guān)鍵字】的下拉列表中選擇“部門”，并選擇排序方式為“降序”；從【次要關(guān)鍵字】的下拉列表中選擇“實(shí)發(fā)金額”，并選擇排序方式為“降序”；并在【我的數(shù)據(jù)區(qū)域】中選擇“

14、有標(biāo)題行”，被選中的區(qū)域中第一行（即表頭行）就不會(huì)參與排序了，如圖5.58所示。3）單擊【確定】按鈕，完成排序，排序結(jié)果如圖5.59所示。從中可以看到，首先按“部門”的字典音序降序排列，順序?yàn)椤颁N售部”、“網(wǎng)絡(luò)集成部”、“軟件開發(fā)部”；同一部門中，再按“實(shí)發(fā)金額”從高到低降序排列。 圖5.59 進(jìn)行數(shù)據(jù)排序后的結(jié)果 圖5.58 排序關(guān)鍵字的設(shè)置返回455.3.5 進(jìn)行列隱藏 比如“應(yīng)稅金額”項(xiàng)主要是在計(jì)算個(gè)人所得稅時(shí)做輔助用的，我們可以把該列隱藏起來(lái)。步驟如下： 1）選中L列（“應(yīng)稅金額”項(xiàng)所在列），單擊【格式】菜單，在其下拉菜單中選擇【列】|【隱藏】選項(xiàng)。L列被隱藏 2）此時(shí)，L列被隱藏起來(lái)

15、了，效果如圖5.60所示。 L列被隱藏圖5.60 L列被隱藏后的結(jié)果 返回465.3.6 數(shù)據(jù)自動(dòng)篩選 要求在工資表中實(shí)現(xiàn)對(duì)員工的“姓名”、“部門”、“職務(wù)”和“崗位工資”的查詢，操作步驟如下：1）選中B2：E2單元格區(qū)域，單擊【數(shù)據(jù)】菜單，選擇【篩選】|【自動(dòng)篩選】選項(xiàng)，可以看到工作表中B2：E2列（“姓名”、“部門”、“職務(wù)”和“崗位工資”）右側(cè)出現(xiàn)了自動(dòng)篩選箭頭 。2）如果要查看“網(wǎng)絡(luò)集成部”所有員工的工資信息，可單擊C2單元格的篩選箭頭，在其下拉列表中選擇“網(wǎng)絡(luò)集成部”（見圖5.61），得到篩選結(jié)果，如圖5.62所示。圖5.61 選擇自動(dòng)篩選條件 圖5.62 選擇自動(dòng)篩選條件 473）

16、如果要查看職務(wù)中屬于“經(jīng)理”一級(jí)的員工的工資信息，則需要單擊D2單元格的篩選箭頭，選擇“自定義”選項(xiàng)，在彈出的【自定義自動(dòng)篩選方式】對(duì)話框中，在【顯示行】的【職務(wù)】選項(xiàng)區(qū)左邊下拉列表中選擇“包含”，在右邊的下拉列表文本框中輸入“經(jīng)理”。4）單擊【確定】按鈕，進(jìn)行自動(dòng)篩選，篩選結(jié)果將列出“職務(wù)”中包含“經(jīng)理”兩個(gè)字（如部門經(jīng)理、項(xiàng)目經(jīng)理等）的員工的工資信息，如圖5.63所示。5）若要顯示全部的記錄，則單擊被篩選項(xiàng)（篩選箭頭為藍(lán)色，本例中是D2）箭頭，在其下拉列表中選擇“全部”選項(xiàng)即可。圖5.63 自定義篩選的結(jié)果返回485.3.7 數(shù)據(jù)分類匯總1平均值匯總 1）單擊工作表中任一單元格，再選擇【數(shù)

17、據(jù)】|【分類匯總】選項(xiàng)，彈出【分類匯總】對(duì)話框。 2）在【分類字段】下拉列表中選擇“部門”；在【匯總方式】下拉列表中選擇“平均值”；在【選定匯總項(xiàng)】列表中的“實(shí)發(fā)金額”前的復(fù)選框中打上“”，并清除其他復(fù)選框，選中“替換當(dāng)前分類匯總”和“匯總結(jié)果顯示在數(shù)據(jù)下方”復(fù)選框，清除“每組數(shù)據(jù)分頁(yè)”復(fù)選框，如圖5.64所示，即可以進(jìn)行各部門員工“實(shí)發(fā)金額”平均值的分類匯總。 3）單擊【確定】按鈕，即完成部門員工“實(shí)發(fā)金額”平均值的分類匯總，如圖5.65所示。 圖5.64 平均值分類匯總 圖5.65 平均值分類匯總結(jié)果 492求和匯總 如果要在平均值匯總的基礎(chǔ)上，再按部門進(jìn)行“實(shí)發(fā)金額”求和匯總，其操作步驟

18、如下： 1）單擊工作表中任一單元格，再選擇【數(shù)據(jù)】|【分類匯總】選項(xiàng)，彈出【分類匯總】對(duì)話框。 2）在【分類字段】下拉列表中選擇“部門”；在【匯總方式】下拉列表中選擇“求和”；在【選定匯總項(xiàng)】列表中的“實(shí)發(fā)金額”前的復(fù)選框中打上“”，并清除其他復(fù)選框，清除“每組數(shù)據(jù)分頁(yè)”和“替換當(dāng)前分類匯總”復(fù)選框，此時(shí)“匯總結(jié)果顯示在數(shù)據(jù)下方”自動(dòng)變?yōu)榛疑?，即可以得到各部門員工“實(shí)發(fā)金額”求和的分類匯總。 3）單擊【確定】按鈕，即完成部門員工“實(shí)發(fā)金額”平均值、求和的分類匯總，效果如本節(jié)開頭部分的圖5.42所示。返回505.3.8 制作工資條 浙江豐達(dá)計(jì)算機(jī)有限公司還需要把工資表打印成“工資條”發(fā)給每位員工

19、。下面我們用Excel2003的“宏”功能，自動(dòng)將編制好的工作表轉(zhuǎn)換為工資條。 上面我們?cè)?jīng)進(jìn)行過(guò)“排序”、“數(shù)據(jù)自動(dòng)篩選”、“數(shù)據(jù)分類匯總”的步驟，制作工作條之前，我們先把工作表復(fù)原。1）刪除“分類匯總”。單擊工作表中任一單元格，再選擇【數(shù)據(jù)】|【分類匯總】選項(xiàng)，彈出【分類匯總】對(duì)話框中，單擊【全部刪除】按鈕，這樣就取消了“分類匯總”。2）刪除“自動(dòng)篩選”。單擊工作表中任一單元格，再選擇【數(shù)據(jù)】|【篩選】|【自動(dòng)篩選】選項(xiàng)，“自動(dòng)篩選”前面的“”清除了，“自動(dòng)篩選”功能也就被刪除了。3）按“員工編號(hào)”排序。單擊“員工編號(hào)”列任一單元格，再單擊【常用】工具欄中的【降序排列】按鈕，這樣工資表中數(shù)

20、據(jù)就按照“員工編號(hào)”升序排列。514）在工資表中，選擇【工具】|【宏】|【錄制新宏】選項(xiàng)，如圖5.69所示，彈出【錄制新宏】對(duì)話框。5）在【錄制新宏】對(duì)話框中，將【宏名】設(shè)置為“制作工資條”，在【快捷鍵】文本框中輸入“g”，設(shè)定Ctrl+G作為快捷鍵，如圖5.70所示，再單擊【確定】按鈕返回工作表。6）選擇【工具】|【宏】|【停止錄制】選項(xiàng)，這樣就完成了一個(gè)空的宏的錄制。 圖5.69 錄制新宏圖5.70 【錄制新宏】對(duì)話框 527）選擇【工具】|【宏】|【宏】選項(xiàng)，打開【宏】對(duì)話框，選擇剛剛錄制的“制作工資條”宏，然后單擊【編輯】按鈕，如圖5.71所示，打開了Visual Basic編輯器。

21、8）在Visual Basic編輯器中輸入如下代碼：Sub 制作工資條() Selection.CurrentRegion.Offset(1, 0).Select Cells(Selection.Row, Selection.Column).Select Range(Selection, Selection.End(xlToRight).Select Selection.Copy ActiveCell.Offset(2, 0).Range(A1).Select Do Until ActiveCell = Selection.Insert Shift:=xlDown Range(Selectio

22、n, Selection.End(xlToRight).Select Selection.Copy ActiveCell.Offset(2, 0).Range(A1).Select LoopApplication.CutCopyMode = FalseEnd Sub圖5.71 【宏】對(duì)話框 53 9）代碼輸入完畢后，單擊Visual Basic編輯器中【標(biāo)準(zhǔn)】工具欄中的【視圖Microsoft Excel】按鈕 ，返回工作表。 10）選中工作表中任一單元格，選擇【工具】|【宏】|【宏】選項(xiàng)，打開【宏】對(duì)話框。選中剛剛錄制的“制作工作條”宏后，單擊【執(zhí)行】按鈕，完成每位員工自動(dòng)添加表頭的功能，效果如本節(jié)開篇處圖5.43所示。返回545.3.9 為工資表添加密碼 工資表中的信息對(duì)于企業(yè)來(lái)說(shuō)是一個(gè)較為重要的信息，是比較隱私的信息，既不允許隨意被改動(dòng)，也不能輕易被其他員工看到，因此，為其設(shè)置打開權(quán)限密碼是必要的。只有工資表的使用者才可以打開或修改次工作表。1）選擇【工具】菜單下【選項(xiàng)】選項(xiàng)，在彈出的【選項(xiàng)】對(duì)話框中選擇【安全性】選項(xiàng)卡，在其中的【打開權(quán)限密碼】