學習情境五電子表格Excel2003任務單 5.1體驗Excel2003 5.2案例1—制作企業(yè)員工統(tǒng)計表5.3案例2—工資表的計算 5.4案例3—制作圖表 5.5案例4—工資表的數(shù)據(jù)管理及統(tǒng)計 5.6Excel使用技巧及拓展 評價單任務單

最近，單位領導安排小王到人事科工作，負責制作、統(tǒng)計、匯總單位員工的工資情況，小王通過翻書學習，并在電腦上不斷實踐，功夫不負有心人，從Excel中表格的制作、計算、統(tǒng)計、匯總到圖表處理，他都得心應手，快速準確地完成了任務，終于給領導提交了一份滿意的答卷。下面他把Excel的知識點分解成幾個案例，詳細講解其制作過程，通過以下案例的學習，想必大家也一定能成為Excel高手。5.1體驗Excel2003

一、Excel的基本功能

MicrosoftExcel2003是美國微軟公司Office2003辦公系列軟件的組件之一。

使用Excel2003可以快速創(chuàng)建一個實用的電子表格，可以進行復雜的數(shù)據(jù)計算和數(shù)據(jù)分析，在圖表的制作上更是別具一格。它不但適用于個人事務處理，而且被廣泛應用于財務、統(tǒng)計和分析等領域。

Excel2003不僅功能強大、技術先進，而且可以非常方便地與其他Office組件交換數(shù)據(jù)，并提供完全MicrosoftOffice2003風格的工作環(huán)境和操作方式。

Excel2003的功能主要體現(xiàn)在：

1．快速、方便地建立各種表格

Excel可以根據(jù)需要快速、方便地建立各種電子表格，輸入各種類型的數(shù)據(jù)，并且具有比較強大的自動填充功能，大大地提高了指標的效率。

2．強大的數(shù)據(jù)計算與分析處理功能

Excel提供了數(shù)百個各種類型的函數(shù)和多種數(shù)據(jù)處理工具，可以進行復雜的數(shù)據(jù)計算和數(shù)據(jù)分析，并且支持網(wǎng)絡上的表格數(shù)據(jù)處理。因此它被廣泛地應用于辦公數(shù)據(jù)處理工作。

3．數(shù)據(jù)圖表、圖文并茂

Excel不僅可以進行數(shù)據(jù)計算和分析，還可以把表格數(shù)據(jù)通過各種統(tǒng)計圖、透視圖等形式表示出來，并能進行市場分析和趨勢預測工作。

二、Excel2003的工作窗口簡介

1．Excel2003的啟動和退出

Excel的啟動與退出的方法與Word完全相同，不再贅述。

2．Excel的主窗口組成

與Word等其他Office程序相似，Excel2003的工作窗口包括：標題欄、菜單欄、工具欄、狀態(tài)欄和文檔窗口(工作簿窗口)，另外，它還有較特殊的名稱框和編輯欄，如圖5-1所示。

圖5-1Excel主窗口

1)名稱框

編輯欄的左側是名稱框，用以顯示單元格地址或區(qū)域名稱。如果沒有定義名稱，則名稱框中顯示活動單元格的地址。

如圖5-2所示，名稱框中顯示單元格的地址“A1”。

圖5-2Excel編輯欄

2)編輯欄

3．Excel的基本要素

1)工作簿

每打開一個Excel文檔，就出現(xiàn)一個文檔窗口。一個Excel文檔稱作一個“工作薄”，一個工作薄可包含多達255個工作表。啟動Excel后，系統(tǒng)會自動新建一個空的工作薄文檔，工作薄的默認名稱為Book1，文件擴展名為.xls。

2)工作表

工作表是工作薄窗口中的一張表格，一個新工作薄默認有3張工作表，分別命名為Sheet1、Sheet2、Sheet3。

窗口最下面一行顯示當前在哪張表上工作，稱作“當前工作表”，其名稱帶有下劃線，且為白色填充。要使用其他工作表，可以單擊其他工作表名或者單擊切換按鈕(向右

或向左

的黑三角)。

工作表是由行和列組成的。最多可以包含256列，65536行，列號用A、B、C、…、Z、AA、AB、…、IV表示，行號用數(shù)字1、2、3、…、65536表示。

3)單元格

Excel工作表中行和列交叉形成的每個格子稱為“單元格”。單元格是工作表中用以存儲數(shù)據(jù)的基本單位。單元格的位置有單元格地址標識。一個單元格地址通常由“列號+行號”組成。例如：第一列的單元格地址分別為：A1，A2，A3…；第一行的單元格地址分別為：A1，B1，C1，…。拖動右邊的垂直滾動條可以改變窗口中內(nèi)容的上下位置，拖動下邊的水平滾動條可以改變窗口中內(nèi)容的左右位置。

當前正在使用的單元格稱為“活動單元格”。單擊某個單元格，它便成為活動單元格，可以向活動單元格內(nèi)輸入數(shù)據(jù)，這些數(shù)據(jù)可以是字符串、數(shù)字、日期、公式等?；顒訂卧竦牡刂凤@示在名稱框中。

4)工作簿、工作表、單元格三者之間的關系

在Excel中，一個Excel文檔就是一個工作薄。工作薄是由多張工作表組成的，工作表是由許多單元格組成的，單元格是組成工作薄的最小單位。工作簿、工作表、單元格三者之間的關系如圖5-3所示。

圖5-3Excel中工作簿、工作表、單元格三者之間的關系

5)區(qū)域—多個單元格組成的矩形

為了操作方便，引入了“區(qū)域”的概念。區(qū)域常用左上角、右下角單元格的名稱來標識，中間用“

:

”間隔。比如區(qū)域“A1:B3”，表示的范圍為A1、B1、A2、B2、A3、B3共6個單元格組成的矩形區(qū)域。若需要對很多區(qū)域進行同一操作，可將這一系列區(qū)域定義為數(shù)據(jù)系列，它的引用是由“，”隔開的所有矩形區(qū)域的引用來表示的。比如區(qū)域“A1:B3，C4:D5，E2”表示的區(qū)域包括A1、B1、A2、B2、A3、B3、C4、D4、C5、D5、E2共11個單元格組成的系列。若活動單元是一個區(qū)域(即選中的是一個區(qū)域)，則只顯示左上角單元格的地址。例如選取“A1:B3”區(qū)域，而在名稱欄中只顯示A1的地址。

三、Excel的操作流程

Excel2003操作的一般流程如圖5-4所示。

圖5-4Excel操作流程

一、案例情境

1．案例概述：制作企業(yè)員工統(tǒng)計表

最近，人事部門需要小王制作員工統(tǒng)計表，如圖5-5所示，制作過程中，小王在輸入各種類型的數(shù)據(jù)時碰到了困難，比如如何加入貨幣符號、如何快速輸入同類型或有規(guī)律的數(shù)據(jù)、如何輸入日期型數(shù)據(jù)、如何使表格更美觀等等。小王通過學習及不斷實踐，快速準確地完成了任務，制作出了一份美觀實用的企業(yè)員工統(tǒng)計表。5.2案例1—制作企業(yè)員工統(tǒng)計表學完本案例后可以掌握以下辦公要領：

如何創(chuàng)建電子表格；

如何輸入各種類型的數(shù)據(jù)；

如何對表格數(shù)據(jù)進行快速填充；

如何編輯和修飾電子表格。

2．案例效果及分析

本案例效果如圖5-5所示。制作要點見圖5-5中標注

～

所示。

圖5-5企業(yè)員工統(tǒng)計表二、制作過程

步驟1：啟動Excel2003，新建“員工統(tǒng)計表.xls”文件。

步驟2：輸入數(shù)據(jù)。

1)輸入標題及表頭數(shù)據(jù)

(1)在當前工作表Sheet1中，選擇單元格A1，輸入標題文字“企業(yè)員工統(tǒng)計表”，按Enter鍵。

(2)在A2單元格中輸入“編號”，按Tab鍵。

(3)以此類推，輸入其他內(nèi)容，輸入結果如圖5-6所示。

圖5-6輸入標題及表頭數(shù)據(jù)

Excel默認文本型數(shù)據(jù)在單元格中左對齊。

2)輸入“編號”列數(shù)據(jù)

(1)單擊A3單元格，在A3單元格中輸入“001”，按Enter鍵后會發(fā)現(xiàn)單元格中的內(nèi)容變?yōu)椤?”，說明在自動格式中以數(shù)字方式顯示，所以數(shù)據(jù)前面的“0”被忽略了。正確的輸入方法是：首先輸入西文單引號“’”，然后輸入編號“001”，即輸入“’001”，表示這是一個字符串，而非一般可計算的數(shù)字。

(2)鼠標指針指向A3單元格的“填充柄”(位于單元格右下角的小黑塊)，如圖5-7(a)所示，此時鼠標指針變?yōu)閷嵭氖帧? ”形狀，按住鼠標左鍵向下拖動填充柄，拖動過程中填充柄的右下角出現(xiàn)填充的數(shù)據(jù)，拖至目標單元格時釋放鼠標左鍵即可。填充效果如圖5-7(b)所示。

(3)填充完成后，在右下角可以看到新增的“自動填充選項”標記

，單擊該標記，即可在彈出的下拉菜單中選定單元格的方式。如圖5-7(b)所示為選中“以序列方式填充”單選按鈕得到的填充結果，該序列的步長值為1。如果填充方式選擇“復制單元格”選項，產(chǎn)生的數(shù)據(jù)序列都是相同的數(shù)字“001”。

(a)(b)

圖5-7用“填充柄”填充數(shù)據(jù)

3)輸入“姓名”、“性別”、“部門”列數(shù)據(jù)

(1)選擇單元格B3，輸入“呂新”，按Enter鍵。

(2)在B4單元格中輸入“喬喜蘭”，按Enter鍵。

(3)用同樣的方法依次輸入姓名、性別、部門列的內(nèi)容。

4)輸入“工作時間”列數(shù)據(jù)

(1)選擇單元格E3，輸入“1992/6/l”或“1992-6-1”，按Enter鍵。

(2)以此類推，輸入工作時間列的其余內(nèi)容。

說明：

①日期型數(shù)據(jù)的輸入：可按“年-月-日”、“月-日”、“年/月/日”或“月/日”等形式輸入。

②時間型數(shù)據(jù)的輸入：可按“時：分：秒”或“時：分”的形式輸入。例如：十三點三十分二十五秒，可輸入13:30:25；一分三十秒，可輸入1:30。

③輸入當前日期，可以按<Ctrl+;>組合鍵；輸入當前時間，可以按<Ctr+Shift+;

>組合鍵。

④單元格中輸入“1/2”時，顯示結果為“一月二日”，屬于日期型；若要輸入分數(shù)“1/2”，請先鍵入“0”和“空格”鍵，如輸入“01/2”，則表示分數(shù)1/2。

⑤

Excel默認日期型數(shù)據(jù)在單元格中右對齊。

5)輸入“基本工資”、“績效工資”列數(shù)據(jù)

(1)選擇單元格F3，在英文輸入狀態(tài)下，輸入“2500”。

(2)以此類推，輸入其他數(shù)據(jù)。

輸入完成之后的表格如圖5-8所示。

說明：

①

Excel可以輸入任意的正數(shù)、負數(shù)，如3，3.14，-3，-3.14等；可以輸入百分數(shù)，如10％，12.34％；也可以輸入以科學計數(shù)法表示的數(shù)字，如1e7，-5.2e7等。

②

Excel默認數(shù)值型數(shù)據(jù)在單元格中右對齊。

圖5-8輸入完成之后的表格步驟3：設置單元格的對齊方式及合并單元格。

單元格對齊方式默認是常規(guī)、靠下對齊。其中常規(guī)是水平對齊方式，指文字靠左對

齊、數(shù)字靠右對齊，靠下對齊則為垂直對齊方式，是指所有數(shù)據(jù)都緊鄰單元格的下邊框排放。

這張企業(yè)員工統(tǒng)計表的單元格內(nèi)的文字都是在默認狀態(tài)的情況下輸入的，如圖5-8所示，這里需要將整個表格的文字的“水平對齊”和“垂直對齊”均設置為“居中”，并且將標題行合并。操作步驟如下：

1)將標題行合并居中

(1)在當前工作表Sheet1中，選擇區(qū)域A1:G1的標題行范圍，如圖5-9所示。

(2)執(zhí)行【格式】→【單元格】命令，彈出“單元格格式”對話框，如圖5-10所示。

(3)選擇“對齊”選項卡，出現(xiàn)“對齊”設置頁面，在“水平對齊”中選擇“居中”，在“垂直對齊”中選擇“居中”，在“文本控制”欄中選中“合并單元格”，設置內(nèi)容如圖5-11所示。

圖5-9選取A1:G1的標題行范圍

圖5-10“單元格格式”對話框

圖5-11“合并”及“居中”設置

(4)單擊

按鈕，標題行設置完畢。

2)將2～16行數(shù)據(jù)居中，并設置對齊方式

(1)選擇區(qū)域A2:G16。

(2)執(zhí)行【格式】→【單元格】命令，彈出“單元格格式”對話框。

(3)選擇“對齊”選項卡，在“水平對齊”中選擇“居中”，在“垂直對齊”中選擇“居中”。

(4)單擊

按鈕，設置結果如圖5-12所示。

圖5-12“合并”及“居中”設置后的企業(yè)員工統(tǒng)計表

圖5-13格式工具欄步驟4：設置字體格式。

小王為了使員工統(tǒng)計表美觀，標題醒目，將標題字體設置為隸書、加粗、深藍色、20號且加下劃線，其他的文字字體設置為宋體、常規(guī)、12號。操作如下：

(1)在當前工作表中，選擇區(qū)域A1:G1，執(zhí)行【格式】→【單元格】命令，彈出“單元格格式”對話框。

(2)選擇“字體”選項卡，出現(xiàn)“字體”設置頁面，將“字體”設置為“隸書”，將“字形”設置為“加粗”，將“字號”設置為“20”，“顏色”設置為“深藍”，在下劃線下拉列表框中選擇“單下劃線”，如圖5-14所示，單擊

按鈕即可。

圖5-14標題行字體設置

(3)選擇區(qū)域A2:G16，以同樣的方法將數(shù)據(jù)部分的文字字體格式設置為“宋體”、“常規(guī)”、“12”，單擊

按鈕，完成字體格式設置。

步驟5：設置數(shù)字及日期格式。

小王想將“工作時間”列數(shù)據(jù)設置為“****年**月**日”的格式，“基本工資”和“績效工資”列數(shù)據(jù)保留兩位小數(shù)，并加貨幣符號，這樣使數(shù)據(jù)更精確，版面更美觀，也更整齊。操作如下：

1)設置“工作時間”列數(shù)據(jù)格式

(1)選中“工作時間”列數(shù)據(jù)所在的單元格區(qū)域E3:E16。

(2)執(zhí)行【格式】→【單元格】命令。

(3)在彈出的“單元格格式”對話框中選擇“數(shù)字”選項卡，在“分類”列表框中選擇“日期”，在“類型”中選擇“2001年3月14日”，如圖5-15所示。

(4)單擊

按鈕。

圖5-15“日期”格式設置

2)設置“基本工資”和“績效工資”列數(shù)據(jù)格式

(1)選中“基本工資”和“績效工資”列數(shù)據(jù)所在的單元格區(qū)域F3:G16。

(2)執(zhí)行【格式】→【單元格】命令。

(3)在彈出的“單元格格式”對話框中選擇“數(shù)字”選項卡，在“分類”列表框中選擇“貨幣”，設置“小數(shù)位數(shù)”為“2”，設置“貨幣符號”為“?”，如圖5-16所示。

(4)單擊

按鈕，設置后的表格如圖5-17所示。

圖5-16“貨幣”格式設置圖5-17“日期”和“數(shù)字”格式設置后的企業(yè)員工統(tǒng)計表步驟6：設置邊框、背景圖案。

為了進一步美化表格，小王想為員工統(tǒng)計表添加邊框：深紅色，外框粗線、內(nèi)框細線。為標題行加背景：淺青色；列標頭加背景：象牙色；數(shù)據(jù)加背景：淺蘭色。操作如下：

1)設置邊框格式

(1)選擇員工統(tǒng)計表中的數(shù)據(jù)區(qū)域A1:G16。

(2)執(zhí)行【格式】→【單元格】命令。

(3)在彈出的“單元格格式”對話框中，選擇“邊框”選項卡，先選擇線條“樣式”為“粗線”，“顏色”為“深紅”，在“預置”欄內(nèi)選擇“外邊框”；再選擇線條“樣式”為“細線”，在“預置”欄內(nèi)選擇“內(nèi)部”，如圖5-18所示。

(4)單擊? ?按鈕，完成框線設定。

2)設置背景圖案

(1)選中標題行區(qū)域A1。

(2)執(zhí)行【格式】→【單元格】命令。

(3)在“單元格格式”對話框中，選擇“圖案”選項卡，在“圖案”選項卡中，“顏色”選擇淺青色，如圖5-19所示。

圖5-18“邊框”設置

圖5-19背景“圖案”設置

(4)單擊? ?按鈕。

(5)利用同樣的方法，列標頭加背景：象牙色；數(shù)據(jù)加背景：淺蘭色。

(6)設置后的表格如圖5-20所示。

圖5-20設置“邊框”和“圖案”后的企業(yè)員工統(tǒng)計表步驟7：設置單元格的行高和列寬。

默認情況下，單元格都具有相同的寬度和高度，然而單元格的數(shù)據(jù)有長有短，因此需要經(jīng)常對單元格的尺寸作相應的修改，以保持工作表的美觀，同時也便于查看工作表中的數(shù)據(jù)。

小王將標題欄的行高設置為“35”，數(shù)據(jù)部分的行高設置為“20”，將“編號”、“姓名”列的列寬設置為“8”，其余數(shù)據(jù)部分的列寬設置為“最合適的列寬”。操作如下：

(1)選擇需要調整的列(一列或多列)或行。

(2)執(zhí)行【格式】→【行(或列)】→【行高(或列寬、或最合適的列寬)】命令。

(3)在彈出的“行高(或列寬)”對話框中，“行高(或列寬)”文本框根據(jù)要求設置對應的數(shù)字，如圖5-21所示。單擊??

按鈕即可。

圖5-21“行高”和“列寬”對話框步驟8：設置條件格式。

小王想把員工統(tǒng)計表中基本工資大于或等于2500元的數(shù)據(jù)用紅色、加粗傾斜來標注，低于1500元的數(shù)據(jù)用藍色、加雙下劃線來標注。操作如下：

(1)選取要設置條件格式的單元格區(qū)域F3:F16。

(2)執(zhí)行【格式】→【條件格式】命令，打開“條件格式”對話框。

(3)在“條件1(1)”選項框中選擇“單元格數(shù)值”選項。

(4)單擊第二個框右端的下拉箭頭，從中選擇比較關系為“大于或等于”。

(5)在第三框中鍵入數(shù)值“2500”。

(6)單擊?? 按鈕，打開“單元格格式”對話框。

(7)在對話框中，設置字體格式為加粗、傾斜、紅色。

(8)單擊? ?按鈕，返回到“條件格式”對話框，如圖5-22所示。

(9)在“條件格式”對話框中，單擊? ?按鈕，出現(xiàn)第二個輸入框。

(10)用同樣的方法，設置條件“單元格數(shù)值”“小于”“1500”，在“單元格格式”對話框中設置字體格式藍色、加雙下劃線，如圖5-23所示。

圖5-22“條件格式”對話框中設置“條件1”

圖5-23“條件格式”對話框中設置“條件2”

(11)在“條件格式”對話框中，單擊?? 按鈕，完成設置，完成后的統(tǒng)計表如圖5-24所示。

小王想把設置的條件格式刪除。操作如下：

(1)選取要設置條件格式的單元格區(qū)域F3:F16。

(2)執(zhí)行【格式】→【條件格式】命令，打開“條件格式”對話框。

(3)單擊“條件格式”對話框中的? ?按鈕，彈出“刪除條件格式”對話框。選中要刪除條件的復選框，單擊?? 按鈕完成刪除操作，如圖5-25所示。

圖5-24設置“條件格式”后的員工統(tǒng)計表圖5-25“刪除條件格式”對話框說明：如果要刪除選定單元格的所有條件和其他格式設置，可以執(zhí)行【編輯】→【清除】→【格式】命令。

步驟9：保存員工統(tǒng)計表。

執(zhí)行【文件】→【保存】或【另存為】命令。

步驟10：打印員工統(tǒng)計表。

表格完成之后，小王想要打印輸出，但由于數(shù)據(jù)量大，在進行打印操作時出現(xiàn)分頁問題，小王對表格進行了打印設置，然后打印輸出了一份美觀的員工統(tǒng)計表。操作如下：

1)頁面設置

(1)執(zhí)行【文件】→【頁面設置】命令，打開“頁面設置”對話框。

(2)在“頁面設置”對話框中，將頁面方向設置為“橫向”，紙張大小設置為“A5”，將頁邊距設置為“上”、“下”為“2.5”，“左”、“右”為“2.0”，居中方式選擇“水平”，如圖5-26、圖5-27所示。

圖5-26“頁面設置”對話框的“頁面”選項卡

圖5-27“頁面設置”對話框的“頁邊距”選項卡

2)打印預覽

頁面和頁邊距設置完之后就要打印預覽，查看結果是否是自己所需要的效果，若不理想，可以重新設置，直到效果滿意為止。

執(zhí)行【文件】→【打印預覽】命令，或單擊常用工具欄中的打印預覽按鈕? 。

3)在多頁顯示標題

通過打印預覽，發(fā)現(xiàn)統(tǒng)計表中的內(nèi)容被分成了兩頁，第一頁有標題和表頭，第二頁沒有，若希望每一頁都出現(xiàn)標題和表頭，操作如下：

(1)執(zhí)行【文件】→【頁面設置】命令，打開“頁面設置”對話框。

(2)在對話框中選擇“工作表”選項卡，在“打印區(qū)域”后的文本框中輸入數(shù)據(jù)表區(qū)域，在“頂端標題行”后的文本框中輸入標題及表頭部分的區(qū)域。如圖5-28所示。

圖5-28“頁面設置”對話框的“工作表”選項卡通過以上設置，在所有頁面上都會出現(xiàn)標題和表頭。

4)頁眉和頁腳的設置

(1)在“頁面設置”對話框中，選擇“頁眉/頁腳”選項卡，如圖5-29所示。

(2)單擊圖5-29所示的“自定義頁眉”按鈕，打開“頁眉”設置頁面，在中間文本框中輸入“企業(yè)員工統(tǒng)計表”，如圖5-30所示，單擊 ??按鈕，頁眉設置完成。

(3)單擊圖5-29所示的“頁腳”下拉列表框，選擇“第1頁，共？頁”選項進行設置，頁腳設置完成。

圖5-29“頁面設置”對話框的“頁眉?頁腳”選項卡

圖5-30“頁眉”設置頁面

5)打印輸出

執(zhí)行【文件】→【打印】命令，或單擊常用工具欄中的打印按鈕 ?。

三、相關知識點

1．選定文本、單元格、單元格區(qū)域、行和列

1)選定單元格中的文本

要對單元格進行編輯，雙擊該單元格，然后選擇其中的文本，可以進行插入或修改。

2)選定一個單元格

方法一：單擊相應的單元格。

方法二：用箭頭鍵移動到相應的單元格。

方法三：直接在“編輯欄”的名字框中輸入單元格名字或地址。

3)選定連續(xù)的單元格區(qū)域

方法一：單擊選定該區(qū)域的第一個單元格，然后拖動鼠標直至選定最后一個單元格。

方法二：單擊選定該區(qū)域的第一個單元格，然后按住Shift鍵再單擊區(qū)域中最后一個單元格，通過滾動可以使該單元格區(qū)域可見。

4)選定不連續(xù)的單元格或單元格區(qū)域

先選定第一個單元格或單元格區(qū)域，然后按住Ctrl鍵再選定其他的單元格或單元格區(qū)域。

5)選定工作表中所有單元格

方法一：單擊“全選”按鈕? ?(即行標記和列標記的交叉單元格)。

方法二：執(zhí)行【編輯】→【全選】命令。

方法三：使用<Ctrl+A>組合鍵即可選定整張工作表。

6)選定整行或整列

單擊行號，選定整行。

單擊列號，選定整列。

7)選定相鄰的行或列

方法一：在行號或列號上拖動鼠標進行選取。

方法二：先選定要選定區(qū)域的第一行或第一列，然后按住Shift鍵再選定最后的行或列。

8)選定不相鄰的行或列

先選定要選定區(qū)域的第一行或第一列，然后按住Ctrl鍵再選定其他的行或列。

2．數(shù)據(jù)序列的自動填充

在輸入表格數(shù)據(jù)時往往需要輸入各種序列，例如等差序列、等比序列等。Excel可以自動填充日期、時間和數(shù)字序列，包括數(shù)字和文本的組合序列，如“一”、“二”等，“part1”、“part2”等。利用Excel中的“填充”功能可以快速而又方便地完成這類有序數(shù)據(jù)的輸入，而不必一一重復地輸入這些數(shù)據(jù)。

1)利用菜單輸入等差或等比序列

(1)在起始單元格中輸入序列的起始值。

(2)選定數(shù)列放置的區(qū)域。

(3)執(zhí)行【編輯】→【填充】→【序列】子命令，如圖5-31所示。

(4)在“類型”框中，根據(jù)需要選定“自動填充”、“等差序列”或“等比序列”。

(5)在“步長值”框中，輸入相應的數(shù)列差值或數(shù)列比值。

(6)在“終止值”框中，輸入數(shù)列的終止數(shù)值或不輸入，如圖5-32所示。

(7)單擊??按鈕。

圖5-31“編輯”→“填充”子菜單

圖5-32填充“序列”對話框

2)使用鼠標自動填充數(shù)據(jù)序列

使用鼠標左鍵拖動填充柄，可以在相鄰區(qū)域中自動填充相同的數(shù)據(jù)或具有增序、降序可能的數(shù)據(jù)序列。

(1)單擊填充內(nèi)容的起始單元格，輸入填充內(nèi)容。

(2)選取該單元格，用鼠標對準該單元格右下角的填充柄，對于數(shù)字型以及不具有增序或降序可能的文字型數(shù)據(jù)，可直接沿填充方向拖動填充柄至結束的單元格；而對于日期型以及具有增序或降序可能的文字型數(shù)據(jù)，可按住Ctrl鍵，沿填充方向拖動填充柄至結束的單元格。

(3)釋放鼠標及Ctrl鍵，被拖曳過的單元格都被填充了相同的內(nèi)容或具有增序、降序的數(shù)據(jù)序列。

3．創(chuàng)建自定義填充序列

Excel除本身提供的預定義的序列外，還允許用戶自定義序列，我們可以使用工作表中的已有數(shù)據(jù)，也可以把經(jīng)常用到的一些序列做一個定義，來建立自己常用的自定義序列。例如：建立序列第一名、第二名、第三名、第四名、第五名。操作方法是：

(1)執(zhí)行【工具】→【選項】，彈出“選項”對話框。

(2)選擇“自定義序列”選項卡，“自定義序列”列表框中選擇“新序列”，然后在“輸入序列”編輯列表框中輸入新序列：第一名，第二名，第三名，第四名，第五名(單擊“導入”按鈕，即可使用選定的數(shù)據(jù)清單)，如圖5-33所示。

圖5-33自定義序列“選項”對話框

(3)單擊? ?按鈕，則新序列出現(xiàn)在“自定義序列”列表框中，單擊? ?按鈕，新序列創(chuàng)建成功。

自定義序列建立完成后，可以按照前面介紹的方法使用自定義序列：

(1)選擇單元格，輸入序列中的第一項：第一名。

(2)鼠標指針放在所選單元格右下角的填充柄上，按下鼠標左鍵，此時鼠標指針變?yōu)閷嵭牡男∈帧啊薄?/p>

(3)拖動鼠標左鍵到要復制的單元格上，則自定義序列自動填充完成，如圖5-34所示。

圖5-34自定義序列的輸入

4．行、列、單元格的插入、刪除、清除

根據(jù)需要，可以在當前表中選定的單元格、行、列的位置上插入一整行、一整列、一個新的單元格等。

1)行、列的插入

(1)選定一行或一列(鼠標單擊某行的行號或某列的列標)。

(2)執(zhí)行【插入】→【行】(或【插入】→【列】)命令(或右擊，在彈出的快捷菜單中選擇“插入”命令)。

在選擇的行(列)位置將插入一行空行，原有行(列)的數(shù)據(jù)下(右)移。圖5-35“插入”對話框

2)單元格的插入

(1)選擇單元格。

(2)選擇【插入】→【單元格】命令(或右擊，在彈出的快捷菜單中選擇“插入”命令)。此時出現(xiàn)“插入”對話框，如圖5-35所示。

(3)在對話框中選擇“活動單元格右移”或“活動單元格下移”命令。這時插入了一個單元格。此命令也可以插入“行”或“列”。

3)行、列的刪除

(1)選定要刪除的行或列。

(2)執(zhí)行【編輯】→【刪除】命令(或右擊，在彈出的快捷菜單中選擇“刪除”命令)。此時被選中的行或列將刪除。

4)單元格的刪除

(1)選擇要刪除的單元格。

(2)執(zhí)行【編輯】→【刪除】命令(或右擊，在彈出的快捷菜單中選擇“刪除”命令)。此時出現(xiàn)“刪除”對話框，如圖5-36所示。

(3)在對話框中選擇“右側單元格左移”或“下方單元格上移”命令。這樣就刪除了一個單元格。此命令也可以刪除“整行”或“整列”。圖5-36“刪除”對話框

5)單元格數(shù)據(jù)的清除

(1)選擇要刪除的單元格區(qū)域。

(2)執(zhí)行【編輯】→【清除】命令。出現(xiàn)下級菜單命令，介紹如下：

“全部”：清除選定區(qū)域內(nèi)單元格中的所有屬性，成為空單元格。

“格式”：清除選定區(qū)域內(nèi)單元格中所有設置的格式，內(nèi)容和批注不變。

“內(nèi)容”：清除選定區(qū)域內(nèi)單元格中的內(nèi)容，但設置的格式等不變。

“批注”：清除選定區(qū)域內(nèi)單元格中的批注，其他不變。

(3)根據(jù)需要選擇其中的一項，單擊??按鈕。

5．插入批注

如果要對員工統(tǒng)計表中“喬喜蘭”的部門列加一個批注“曾有過營銷策劃方面的實習經(jīng)驗”。操作如下：

(1)選擇“喬喜蘭”的部門列(市場部)單元格。圖5-37顯示批注圖

(2)在此單元格上右擊，彈出一個快捷菜單，選擇“插入批注”(或單擊【插入】→【批注】命令)。

(3)彈出一個文本框，在此框中輸入“曾有過營銷策劃方面的實習經(jīng)驗”。

批注設置完成后，在右上角顯示一個紅色的三角，當把光標移至此單元格上時，會顯示出批注的內(nèi)容，如圖5-37所示。

說明：刪除批注時，可以在單元格上右擊，選擇快捷菜單中的“刪除批注”選項。圖5-38“自動套用格式”對話框

6．自動套用格式

如果對所建工作表沒有特殊的格式要求，可以直接應用Excel給用戶提供的自動套用格式。操作如下：

(1)選擇工作表區(qū)域。

(2)單擊【格式】→【自動套用格式】命令，彈出“自動套用格式”對話框。如圖5-38所示。

(3)選擇一種合適的格式后，單擊? 按鈕。

7．工作表管理

工作表用于顯示和分析數(shù)據(jù)。用戶可以同時在多張工作表上輸入并編輯數(shù)據(jù)，并且可以對不同工作表的數(shù)據(jù)進行匯總計算。每張工作表與一個工作表標簽相對應，如Sheet1、Sheet2、Sheet3…。

1)選擇工作表

要對某一張工作表進行操作，首先要選擇該表，使其成為當前工作表。要對多張工作表同時進行操作，就要同時選擇這些表，使這些表都成為當前工作表。當前工作表的標簽底色為白色。

(1)選定單張工作表：單擊需要的工作表標簽，該工作表就成為當前工作表。

(2)選定多張連續(xù)工作表：單擊第一張工作表標簽后，按住Shift鍵，再單擊所要選擇的最后一張工作表標簽，即可選定多張相鄰工作表。

(3)選定多張不連續(xù)工作表：單擊第一張工作表標簽后，按住Ctrl鍵，再分別單擊其他工作表標簽，即可選定多張不連續(xù)工作表。

(4)選定工作簿中的所有工作表：用鼠標右鍵單擊工作表標簽，彈出快捷菜單，在快捷菜單中選擇“選定全部工作表”命令，即可選定工作簿中的所有工作表。

2)插入工作表

默認情況下，新創(chuàng)建的工作簿由三張工作表組成，用戶可以根據(jù)需要增減工作表。

方法一：

(1)如果要在某張工作表之前插入一張空白工作表，先選中該工作表。

(2)然后執(zhí)行【插入】→【工作表】命令，新插入的工作表即成為活動工作表。方法二：

(1)先選中該工作表，然后用鼠標右鍵單擊“工作表”標簽，則出現(xiàn)有關工作表操作的快捷菜單。

(2)選擇其中的“插入”命令，將出現(xiàn)一個“插入”對話框。

(3)選擇插入“工作表”并單擊? ?按鈕，Excel會自動插入一張空白工作表，且為其給出默認名字。

3)刪除工作表

當不再需要某張工作表時，可以刪除此表。

方法一：

(1)選定要刪除的工作表為當前工作表。

(2)執(zhí)行【編輯】→【刪除工作表】命令，如果工作表中有數(shù)據(jù)，則出現(xiàn)“警告”對話框，如圖5-39所示。用戶可根據(jù)需要進行刪除或取消。

注意：被刪除的工作表不可恢復。

圖5-39刪除工作表時“警告”對話框方法二：

(1)用鼠標右鍵單擊“工作表”標簽，彈出快捷菜單。

(2)在快捷菜單中選取“刪除”命令，可刪除當前工作表。

4)重命名工作表

當新建一個工作簿時，每一張工作表的名稱Sheet1、Sheet2、…是由系統(tǒng)提供的，用戶可以根據(jù)自己的需要，給工作表取一個見名知意的名字。

方法一：

(1)雙擊要命名的工作表標簽，工作表名字就會處于選中狀態(tài)。

(2)鍵入新的工作表名稱。方法二：

(1)選擇要命名的工作表(單擊工作表標簽)。

(2)執(zhí)行【格式】→【工作表】→【重命名】命令。

(3)工作表標簽此時是反白顯示，鍵入新的工作表名稱。

方法三：

(1)鼠標右鍵單擊要命名的工作表標簽，彈出工作表快捷菜單。

(2)選取“重命名”命令，鍵入新的工作表名稱。

圖5-40“移動或復制工作表”對話框

5)移動和復制工作表

方法一：

(1)選擇所要移動或復制的工作表。

(2)單擊【編輯】→【移動或復制工作表】，打開“移動或復制工作表”對話框。

(3)在對話框中的“下列選定工作表之前”列表框中，選擇工作表移動到的新位置，然后單擊“確定”按鈕，完成移動。若同時選取“建立副本”復選框，然后單擊?按鈕，則可完成工作表的復制。如圖5-40所示。方法二：

(1)用鼠標右鍵單擊“工作表”標簽，彈出快捷菜單。

(2)在快捷菜單中選取“移動或復制工作表”命令，其余與方法一相同。

方法三：

(1)將鼠標指針指向被移動的工作表標簽，按下鼠標，此時鼠標指針變成帶有一頁卷角的圖標，同時旁邊的黑色倒三角用以指示移動的位置。沿著標簽區(qū)域拖動鼠標到達需要的位置之后，釋放鼠標按鈕即可完成對工作表的移動。

(2)將鼠標指針指向被復制的工作表標簽，按下Ctrl鍵，再按下鼠標左鍵，此時鼠標指針變成內(nèi)含“十”字形的圖標，同時旁邊的黑色倒三角用以指示工作表的復制位置。沿著標簽區(qū)域拖動鼠標到達復制點后，同時釋放鼠標和Ctrl鍵，即可完成對工作表的復制。

8．窗口拆分和表頭凍結

1)窗口拆分

窗口的拆分可將窗口最多分成4個，借助滾動條可觀察同一文檔的不同內(nèi)容在窗口的不同部分。其具體操作如下：

(1)選擇工作表的某單元格。

(2)執(zhí)行【窗口】→【拆分】命令，則在相應位置的上面和左面各出現(xiàn)一條雙線形式的窗口邊界線，將窗口分成4個部分。

(3)將鼠標放在拆分線框上，鼠標形狀變?yōu)??或??時，按住鼠標左鍵拖動，分別改變左右或上下窗格的大小，如圖5-41所示。

說明：如果想取消拆分，選擇【窗口】→【取消拆分】命令，或者雙擊拆分線框，取消一個方向的拆分；雙擊拆分線框的交點，可以取消兩個方向的拆分。

圖5-41窗口的拆分

2)表頭凍結

有時工作表太大，往下或往右滾動時會看不到行列標題，這時可以將行列標題凍結在屏幕上，如圖5-42所示，其操作如下：

(1)選定C3單元格。

(2)選擇【窗口】→【凍結窗格】命令，則在該單元格上面和左面各出現(xiàn)一條細線，細線上面和細線左面的單元格被凍結。當向下滾動時，細線上面單元格保持在原位置不動，而向右滾動時，細線左面單元格保持在原位置不動。

圖5-42表頭凍結

四、案例總結

本案例通過制作一個簡單的企業(yè)員工統(tǒng)計表，學會了工作表的一些常用操作，如工作簿的建立，工作表數(shù)據(jù)的輸入，工作表的格式化及打印輸出等。

在Excel中有很多快速輸入數(shù)據(jù)的技巧，如自動填充、自定義序列等，熟練掌握這些技巧可以提高輸入速度。同時，在輸入數(shù)據(jù)時，要注意數(shù)據(jù)單元格的數(shù)據(jù)分類，如對于編號、郵編及電話號碼等數(shù)據(jù)應該設置為文本型。

工作表的格式化及打印輸出則包括字體格式、數(shù)據(jù)的對齊方式、表格的邊框及底紋、行高列寬、頁面設置和打印預覽等。通過本案例的制作，可以舉一反三，對日常學習及工作中的其他表格，如課程表、成績表、銷售表進行建立及設置格式。

一、案例情境

1．案例概述：制作工資表

到了月底，小王需要在員工統(tǒng)計表的基礎上制作工資表，并對工資表進行相關數(shù)據(jù)的計算與統(tǒng)計。在制作過程中，小王又碰到了困難，比如如何輸入公式、如何復制公式、如何統(tǒng)計總工資、如何求平均值、最大值等等。小王虛心學習、向高手請教，最終完成了工資表的制作(如圖5-43所示)。5.3案例2—工資表的計算

圖5-43工資表學完本案例后可以掌握以下辦公要領：

如何在工作表中輸入公式；

如何進行公式填充；

如何對表格數(shù)據(jù)進行求和、求平均值、最大值以及最小值等計算統(tǒng)計；

如何使用Excel常用函數(shù)。

2．案例效果及分析

本案例效果如圖5-43所示。

二、制作過程

步驟1：將案例1制作的員工統(tǒng)計表復制到新表中，并將工作表名改為“工資表”。

操作參見案例1中相關知識點的工作表管理內(nèi)容。

步驟2：在“工資表”中將表頭改為“企業(yè)員工工資表”，刪除“工作時間”列，補充“扣保險”、“應發(fā)工資”等列，增加“總和”、“平均值”、“最高值”以及“最低值”等行。

操作結果如圖5-44所示。

操作參見案例1中相關知識點的行、列的插入與刪除等內(nèi)容。

圖5-44步驟2操作結果步驟3：計算“扣保險”、“應發(fā)工資”列數(shù)據(jù)。

1)計算“扣保險”列數(shù)據(jù)

扣保險?=基本工資?×?保險稅率，保險稅率顯示在H22單元格(即6%)。

操作如下：

(1)選擇G3單元格。

(2)在G3單元格中輸入“=E3*$H$22”，或在編輯欄中輸入“=E3*$H$22”，按Enter鍵，則在G3中顯示計算結果。單擊G3單元格，如圖5-45所示。

圖5-45在G3單元格中輸入公式

(3)鼠標指針指向G3單元格的“填充柄”(位于單元格右下角的小黑塊)時，鼠標指針變?yōu)椤? ”形狀，按住鼠標左鍵向下拖動填充柄，拖至目標單元格G16時釋放鼠標左鍵即可。這樣就利用單元格復制公式的方法計算出每一個員工的扣保險額，如圖5-46所示。

圖5-46計算出的“扣保險”說明：在公式的使用中，需要引用單元格地址來指明運算的數(shù)據(jù)在工作表中的位置。單元格地址的引用分為：相對引用、絕對引用、混合引用。

※相對引用：當公式在復制或填充到新位置時，公式不變，單元格地址隨著位置的不同而變化，它是Excel默認的引用方式，如：“E3”，“H22”。

在計算“扣保險”列數(shù)據(jù)時，若采用相對地址引用，即在G3單元格中輸入公式“=E3*H22”時，在G3單元格中顯示呂新的扣保險額150元，但采用公式填充的方法繼續(xù)向下填充時，發(fā)現(xiàn)G4單元格中的公式變?yōu)椤?E4*H23”，由于H23單元格的內(nèi)容默認為0，故G4的扣保險額為0元，得到錯誤數(shù)據(jù)，如圖5-47所示。

圖5-47相對地址引用得到的“扣保險”額

※絕對引用：指公式復制或填入到新位置時，單元格地址保持不變。設置時只需在行號和列號前加“$”符號，如“$E$3”、“$H$22”。

本例在計算扣保險額時保險稅率的引用方式采用絕對引用，即在G3單元格中輸入公式為“=E3*$H$22”，利用單元格公式復制的方法向下填充時，則G4單元格中的公式變?yōu)椤?E4*$H$22”，符合題意要求，得到正確結果。

※混合引用：指在一個單元格地址中，既有相對引用又有絕對引用，如“$E3”、“$H22”是列絕對引用，行相對引用；“E$3”、“H$22”是列相對引用，行絕對引用。

2)計算“應發(fā)工資”列數(shù)據(jù)

應發(fā)工資?=?基本工資?+?績效工資?-?扣保險，操作如下：

(1)選擇H3單元格。

(2)在H3單元格中輸入“=E3+F3-G3”，或在編輯欄中輸入“=E3+F3-G3”，按Enter鍵，則在H3單元格中顯示呂新的應發(fā)工資為3550元。

(3)單擊H3單元格，鼠標指針指向H3單元格的“填充柄”，此時鼠標指針變?yōu)椤?”形狀，按住鼠標左鍵向下拖動填充柄，拖至目標單元格H16時釋放鼠標左鍵即可。這樣就計算出了每一個員工的應發(fā)工資，如圖5-48所示。

圖5-48計算出的“應發(fā)工資”步驟4：計算表格中各列數(shù)值的總和、平均值、最高值和最低值。

1)計算總和

(1)選擇要存放求和結果的單元格。

(2)單擊“常用”工具欄中的“自動求和”按鈕 ?。

(3)Excel自動在E17單元格中插入SUM函數(shù)，并給出求和范圍，生成相應的求和公式。

(4)按Enter鍵，或單擊編輯欄中的輸入按鈕? ?確認。

(5)其他列的總分可利用公式的自動填充功能(向右拖動“填充柄”)快速完成。

2)計算平均值

(1)選擇要存放平均值的單元格E18。

(2)單擊“常用”工具欄中的“自動求和”按鈕?? 的下拉菜單，選擇“平均值”選項，如圖5-49所示。

(3)?Excel自動在E18單元格中插入AVERAGE函數(shù)，并給出數(shù)據(jù)范圍E3∶E17，生成相應的求平均值公式。用鼠標拖曳選取數(shù)據(jù)區(qū)域或輸入正確的數(shù)據(jù)區(qū)域為E3∶E16，如圖5-50所示。

圖5-49“自動求和”按鈕的下拉菜單

圖5-50求“基本工資”的平均值

(4)按Enter鍵，或單擊編輯欄中的輸入按鈕??確認。

(5)其他列的平均分可利用公式的自動填充功能快速完成。

3)計算最高值

(1)選擇要存放最高值的單元格E19。

(2)單擊“常用”工具欄中的“自動求和”按鈕? ?的下拉菜單，選擇“最大值”選項。

(3)?Excel自動在E19單元格中插入MAX函數(shù)，并給出數(shù)據(jù)范圍E3∶E18，生成相應的求最大值公式。用鼠標拖曳選取數(shù)據(jù)區(qū)域E3∶E16。

(4)按Enter鍵確認。

(5)利用公式的自動填充功能快速完成其他列的最高值計算。

4)計算最低值

(1)單擊要存放最低值的單元格E20。

(2)單擊“常用”工具欄中的“自動求和”按鈕??的下拉菜單，選擇“最小值”選項。

(3)?Excel自動在單元格中插入MIN函數(shù)，并給出數(shù)據(jù)范圍E3∶E19，生成相應的求最小值公式。用鼠標拖曳選取新的數(shù)據(jù)區(qū)域或輸入正確的數(shù)據(jù)區(qū)域為E3∶E16。

(4)按Enter鍵，或單擊編輯欄中的輸入按鈕??確認。

(5)利用公式的自動填充功能快速完成其他列的最低值計算。

說明：函數(shù)的輸入通常有以下三種方式：

(1)直接輸入法：即直接在單元格內(nèi)輸入函數(shù)，適用于比較簡單的函數(shù)。例如，在計算“基本工資”的平均值時，可以直接在E18單元格中輸入“=AVERAGE(E3∶E16)”。

(2)插入函數(shù)法：較第一種方法更常用。例如，在計算“基本工資”的平均值時，可采用以下操作完成計算：①單擊要存放平均值的單元格E18。

②執(zhí)行【插入】→【函數(shù)】命令(或單擊編輯欄上的? 按鈕)，打開“插入函數(shù)”對話框。

③在對話框中，“選擇類型”下拉列表框中選擇“常用函數(shù)”選項，再在“選擇函數(shù)”下拉列表框中選擇“AVERAGE”選項，如圖5-51所示。

圖5-51“插入函數(shù)”對話框

圖5-52AVERAGE“函數(shù)參數(shù)”對話框④單擊?? 按鈕，彈出AVERAGE“函數(shù)參數(shù)”對話框，如圖5-52所示。“Number1”中默認選擇上方所有數(shù)據(jù)單元格，這是不正確的，需要重新選擇，可以輸入正確區(qū)域，或單擊“Number1”參數(shù)框右側的“暫時隱藏對話框”，在工作表上方只顯示參數(shù)編輯框，接著從工作表中選擇相應的單元格區(qū)域E3:E16，再次單擊該按鈕? ，恢復原對話框。

⑤單擊? ?按鈕，完成計算。

(3)自動求和法：此種方法最快捷，使用“常用”工具欄中的“自動求和”按鈕??以及下拉菜單，可實現(xiàn)5種最常用的函數(shù)計算。本例中求和、求平均值、最高值、最低值的運算就是采用此種方法。

步驟5：在“工資表”中補充“稅款”、“實發(fā)工資”等列，增加“總人數(shù)”、“高于3000元人數(shù)”等行的內(nèi)容。

制作結果如圖5-53所示。

操作步驟參見案例1中相關知識點的行、列的插入與刪除等內(nèi)容。

圖5-53“工資表”中補充“稅款”、“實發(fā)工資”列、增加行等內(nèi)容步驟6：計算“稅款”、“實發(fā)工資”列數(shù)據(jù)。

1)計算稅款

稅款計算：2000元以上部分按5%繳納，即應發(fā)工資>=2000，超出部分按5%繳稅，否則不繳稅。這是一個邏輯判斷問題，需要用IF條件函數(shù)解決。

(1)選擇單元格I3。

(2)執(zhí)行【插入】→【函數(shù)】命令(或單擊編輯欄上的?? 按鈕)，打開“插入函數(shù)”對話框。

(3)在對話框中，“選擇類型”下拉列表框中選擇“邏輯”選項，再在“選擇函數(shù)”下拉列表框中選擇“IF”選項。單擊?? 按鈕，彈出IF“函數(shù)參數(shù)”對話框。

(4)在對話框中，“Logical_test”中輸入條件“H3>2000”；“Value_if_true”中輸入“(H3-2000)*0.05”；“Value_if_false”中輸入“0”，如圖5-54所示。

(5)單擊 ??按鈕，I3單元格顯示呂新的稅款77.5元，如圖5-55所示。

(6)用同樣的方法可計算出其他人的稅款，或利用公式的自動填充功能(向下拖動“填充柄”)快速完成其他人的稅款計算。

圖5-54IF“函數(shù)參數(shù)”對話框

圖5-55計算“稅款”說明：

稅款的計算也可采用直接輸入法：即選擇I3單元格，直接在I3單元格中輸入或在編輯欄輸入“=IF(H3>2000,(H3-2000)*0.05,0)”，按Enter鍵(或單擊編輯欄中的輸入按鈕?)確認即可。

2)計算實發(fā)工資

實發(fā)工資?=?應發(fā)工資?-?稅款，操作如下：

(1)選擇J3單元格。

(2)在J3單元格中輸入“=H3-I3”，或在編輯欄中輸入“=H3-I3”，按Enter鍵，則在J3單元格中顯示呂新的實發(fā)工資為3472.50元。

(3)單擊J3單元格，鼠標指針指向J3單元格的“填充柄”，按住鼠標左鍵向下拖動填充柄，拖至目標單元格J16時釋放鼠標左鍵即可。這樣就計算出了每一個員工的實發(fā)工資。步驟7：統(tǒng)計“工資表”中的總人數(shù)以及實發(fā)工資高于3000元的人數(shù)，并分別放于J21、J22單元。

1)統(tǒng)計“工資表”中的總人數(shù)

(1)選擇要存放總人數(shù)的單元格J21。

(2)單擊“常用”工具欄中的“自動求和”按鈕?? 的下拉菜單，選擇“計數(shù)”。

(3)?Excel自動在單元格中插入COUNT函數(shù)，并給出計數(shù)范圍J3∶J20，生成相應的計數(shù)公式。用鼠標拖曳選取新的數(shù)據(jù)區(qū)域或輸入正確的數(shù)據(jù)區(qū)域J3∶J16。

(4)按Enter鍵(或單擊編輯欄中的輸入按鈕 ?)確認完成。

2)統(tǒng)計“工資表”中實發(fā)工資高于3000元的人數(shù)

這是一個帶條件的計數(shù)問題，需要用COUNTIF函數(shù)解決。

(1)選擇單元格J23。

(2)執(zhí)行【插入】→【函數(shù)】命令(或單擊編輯欄上的??按鈕 )，打開“插入函數(shù)”對話框。

(3)在對話框中，“選擇類型”下拉列表框中選擇“統(tǒng)計”選項，再在“選擇函數(shù)”下拉列表框中選擇“COUNTIF”選項。單擊“確定”按鈕，彈出COUNTIF“函數(shù)參數(shù)”對話框。

(4)在對話框中，“Range”中輸入?yún)^(qū)域“J3∶J16”；“Criteria”中輸入“>3000”；如圖5-56所示。

(5)單擊?? 按鈕，J23單元格顯示實發(fā)工資高于3000元的人數(shù)為5。

說明：

實發(fā)工資高于3000元的人數(shù)計算也可采用直接輸入法：即選擇J23單元格，直接在J23單元格中輸入“=COUNTIF(J3:J16,“>3000”)”，按Enter鍵確認即可。

此時工資表的計算已全部完成，完成后的表格如圖5-57所示。

圖5-56COUNTIF“函數(shù)參數(shù)”對話框

圖5-57完成計算后的工資表步驟8：制作工資條。

小王想在工資表的基礎上，制作成工資條，經(jīng)過一番摸索實踐后，終于得以實現(xiàn)。工資條的制作過程如下：

(1)在當前工作簿中插入一個工作表，并將該工作表的名稱改為“工資條”，然后輸入工資條項目，如圖5-58所示。

(2)在單元格A3中輸入第一個員工的編號“’001”。

(3)在單元格B3中輸入公式“=VLOOKUP(A3,工資表!$A$3:$J$16,2,FALSE)”，按Enter鍵(或單擊編輯欄中的輸入按鈕?)確認。該公式的作用是根據(jù)A3中的員工編號從工資表中取出對應員工的姓名，結果如圖5-59所示。

圖5-58輸入工資條項目

圖5-59獲取員工姓名說明：

(1)?VLOOKUP函數(shù)的使用參見本節(jié)相關知識點“常用函數(shù)的使用”內(nèi)容的介紹。

(2)數(shù)據(jù)引用。

①同一工作表中不同單元格數(shù)據(jù)的引用。比如假定當前處于Bookl工作簿的Sheetl表中的A5單元格，想引用該工作表的D7單元格，則在A5單元格中輸入公式“=D7”。

②同一工作簿中工作表之間的引用。假定當前處于Bookl工作簿的Sheetl表中的A5單元格，想引用該工作簿的Sheet2表的D7單元格，則在A5單元格中輸入公式“=sheet2!D7”，注意在表名與單元格名之間用一個“?!?”分隔。本例中“工資表!$A$3:$J$16”表示要引用該工作簿的工資表的$A$3:$J$16單元格區(qū)域。

③不同工作簿中工作表的引用。描述的格式是“[工作簿文件名.xls]工作表名!單元格地址名”。比如，假定當前處于Bookl工作簿的Sheet1表中的A5單元格，想引用工作簿Book2的sheetl表的D7單元格，工作簿Book2已打開，則在Bookl工作簿的Sheet1表中的A5單元格中輸入公式“=[book2.xls]sheetl!D7”即可。同理，在單元格C3、D3、E3、F3、G3、H3、I3、J3中分別輸入如下公式：

C3單元格中輸入：“=VLOOKUP(A3,工資表!$A$3:$J$16,3,FALSE)”。

D3單元格中輸入：“=VLOOKUP(A3,工資表!$A$3:$J$16,4,FALSE)”。

E3單元格中輸入：“=VLOOKUP(A3,工資表!$A$3:$J$16,5,FALSE)”。

F3單元格中輸入：“=VLOOKUP(A3,工資表!$A$3:$J$16,6FALSE)”。

G3單元格中輸入：“=VLOOKUP(A3,工資表!$A$3:$J$16,7,FALSE)”。

H3單元格中輸入：“=VLOOKUP(A3,工資表!$A$3:$J$16,8,FALSE)”。

I3單元格中輸入：“=VLOOKUP(A3,工資表!$A$3:$J$16,9,FALSE)”。

J3單元格中輸入：“=VLOOKUP(A3,工資表!$A$3:$J$16,10,FALSE)”。

最后得到如圖5-60所示的引用結果。

圖5-60引用結果

圖5-61“定義名稱”對話框選擇單元格數(shù)據(jù)區(qū)域A2:J3，鼠標指針指向J3單元格的“填充柄”，如圖5-62所示，此時鼠標指針變?yōu)椤? ”形狀，按住鼠標左鍵向下拖動填充柄，一下復制到第29行，再釋放鼠標。工資條制作完成，效果如圖5-63所示。

步驟9：保存及打印工資表及工資條。

操作參見案例1中步驟9及步驟10的內(nèi)容。

圖5-62選擇數(shù)據(jù)區(qū)域A2:J3，鼠標指針指向J3單元格的“填充柄”

圖5-63工資條最終效果

三、相關知識點

1．公式的組成與計算

Excel的公式由運算符、數(shù)值、字符串、變量和函數(shù)組成。公式必須以等號“=”開始，后面是參與計算的運算數(shù)和運算符。

1)?Excel中的算術運算符、關系運算符、文本運算符

Excel中的算術運算符、關系運算符、文本運算符如表5-1所示。

表5-1Excel中的算術運算符、關系運算符、文本運算符2)引用運算符

引用運算符：引用運算符可以將單元格區(qū)域合并起來進行計算，運算符如表5-2所示。

四類運算符的優(yōu)先級從高到低依次為：引用運算符→算術運算符→文本運算符→關系運算符，當優(yōu)先級相同時，自左向右進行計算。

表5-2引

用

運

算

符

2．常用函數(shù)的使用

Excel中提供了大量的可用于不同場合的各類函數(shù)，分為財務、日期與時間、數(shù)學與三角函數(shù)、統(tǒng)計、查找與引用、數(shù)據(jù)庫、文本、邏輯和信息等9大類。這些函數(shù)極大地擴展了公式的功能，使數(shù)據(jù)的計算、處理更為容易，更為方便，下面介紹幾個常用函數(shù)及其使用方法。

1)

SUM

用途：返回某一單元格區(qū)域中所有數(shù)字之和。

語法：SUM(number1，number2，…)。

參數(shù)：number1，number2，…為1到30個需要求和的數(shù)值(包括邏輯值及文本表達式)、區(qū)域或引用。

實例：如果A1=1、A2=2、A3=3，則公式“=SUM(A1:A3)”返回6。

2)

AVERAGE

用途：計算所有參數(shù)的算術平均值。

語法：AVERAGE(number1，number2，…)。

參數(shù)：number1、number2、…是要計算平均值的1～30個參數(shù)。

實例：如果A1:A5的數(shù)值分別為100、70、92、47和82，則公式“=AVERAGE(A1:A5)”返回78.2。

3)

MAX

用途：返回數(shù)據(jù)集中的最大數(shù)值。

語法：MAX(number1，number2，…)。

參數(shù)：number1，number2，…是需要找出最大數(shù)值的1至30個數(shù)值。

實例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，則公式“=MAX(A1:A7)”返回96。

4)

MIN

用途：返回給定參數(shù)表中的最小值。

語法：MIN(number1，number2，…)。

參數(shù)：number1，number2，…是要從中找出最小值的1到30個數(shù)字參數(shù)。

實例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，則公式“=MIN(A1:A7)”返回49。

5)

COUNT

用途：返回數(shù)字參數(shù)的個數(shù)。它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個數(shù)。

語法：COUNT(value1，value2，…)。

參數(shù)：value1，value2，…是包含或引用各種類型數(shù)據(jù)的參數(shù)(1～30個)，其中只有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計。

實例：如果A1=90、A2=人數(shù)、A3=〞〞、A4=54、A5=36，則公式“=COUNT(A1:A5)”返回3。

6)

COUNTIF

用途：統(tǒng)計某一區(qū)域中符合條件的單元格數(shù)目。

語法：COUNTIF(range，criteria)。

參數(shù)：range為需要統(tǒng)計的符合條件的單元格數(shù)目的區(qū)域；criteria為參與計算的單元格條件，其形式可以為數(shù)字、表達式或文本(如36、">160"和"男"等)。其中數(shù)字可以直接寫入，表達式和文本必須加引號。

實例：假設A1:A5區(qū)域內(nèi)存放的文本分別為女、男、女、男、女，則公式“=COUNTIF(A1:A5，"女")”返回3。

7)

IF

用途：執(zhí)行邏輯判斷，它可以根據(jù)邏輯表達式的真假，返回不同的結果，從而執(zhí)行數(shù)值或公式的條件檢測任務。

語法：IF(logical_test，value_if_true，value_if_false)。

參數(shù)：logical_test計算結果為TRUE或FALSE的任何數(shù)值或表達式；value_if_true是logical_test為TRUE時函數(shù)的返回值，value_if_false是logical_test為FALSE時函數(shù)的返回值。

實例：公式“=IF(C2>=85，"A"，"B")”，若第一個邏輯判斷表達式C2>=85成立，則單元格值為“A”；否則為“B”，該函數(shù)廣泛用于需要進行邏輯判斷的場合。

8)

RANK

用途：返回一個數(shù)值在一組數(shù)值中的排位(如果數(shù)據(jù)清單已經(jīng)排過序了，則數(shù)值的排位就是它當前的位置)。

語法：RANK(number，ref，order)。

參數(shù)：number是需要計算其排位的一個數(shù)字；ref是包含一組數(shù)字的數(shù)組或引用(其中的非數(shù)值型參數(shù)將被忽略)；order為一數(shù)字，指明排位的方式。如果order為0或省略，則按降序排列的數(shù)據(jù)清單進行排位。如果order不為零，ref按升序排列的數(shù)據(jù)清單進行排位。

圖5-64函數(shù)RANK示例注意：函數(shù)RANK對重復數(shù)值的排位相同。但重復數(shù)的存在將影響后續(xù)數(shù)值的排位。如在一列整數(shù)中，若整數(shù)60出現(xiàn)兩次，其排位為5，則61的排位為7(沒有排位為6的數(shù)值)。

實例：如果A1=78、A2=45、A3=90、A4=12、A5=85，則公式“=RANK(A1，$A$1:$A$5)”返回3，如圖5-64所示。

9)

VLOOKUP

用途：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。

語法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)。

參數(shù)：lookup_value為需要在數(shù)據(jù)表第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字串。table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。col_index_num為table_array中待返回的匹配值的列序號。col_index_num為“1”時，返回table_array第一列中的數(shù)值；col_index_num為“2”，返回table_array第二列中的數(shù)值，以此類推。range_lookup為一邏輯值，指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果range_value為FALSE，函數(shù)VLOOKUP將返回精確匹配值。如果找不到，則返回錯誤值#N/A。

實例：如果A1=23、A2=45、A3=50、A4=65，則公式“=VLOOKUP(50，A1:A4，1，TRUE)”返回50。

四、案例總結

Excel最強大的功能是其計算功能，而它強大的計算功能主要是通過公式和函數(shù)來實現(xiàn)的。使用Excel的公式和函數(shù)可以處理日常學習和工作中的一些比較復雜的計算和數(shù)據(jù)處理問題。

本案例通過制作工資表及工資條，學會了在Excel中如何使用公式和函數(shù)，以及公式如何復制等。

在進行公式和函數(shù)計算時，要熟悉公式的輸入規(guī)則、函數(shù)參數(shù)的設置方法及單元格的引用方式，對于常用函數(shù)的使用要熟練掌握。

學完本案例的制作過程，可以對日常學習及工作中的其他表格，如成績表、銷售表以同樣的方法完成計算及統(tǒng)計，聞一知十。

一、案例情境

1．案例概述：制作企業(yè)員工工資圖表

小王為了能更形象、更直觀地揭示員工工資之間的差距，想以表格數(shù)據(jù)為依據(jù)，形成圖形。在制作過程中，小王發(fā)現(xiàn)Excel具有很強的由表作圖功能，系統(tǒng)將作圖過程分為四步，

5.4案例3—制作圖表用戶可以一步步地選擇數(shù)據(jù)源、圖表類型等參數(shù)，作出一個漂亮的統(tǒng)計圖形，對于已經(jīng)作好的圖形，還可以進一步改變其位置、大小和各種參數(shù)，直到滿意為止。

學完本案例后可以掌握以下辦公要領：

如何創(chuàng)建圖表；

如何編輯和修飾圖表。

2．案例效果及分析

制作效果如圖