《計算機文件基礎 Windows 7+Office +Internet項目式教程》課件-第4章

項目4表格處理任務4.1制作工資表任務4.2格式化消費調查表任務4.3制作學生成績表任務4.4制作汽車銷售表Excel是微軟公司推出的辦公軟件Office中的一個重要組成成員，也是目前最流行的關于電子表格處理的軟件之一，具有強大的數據計算、分析能力，其內置的公式和函數可以讓用戶隨心所欲地進行各種數據運算、統(tǒng)計分析和輔助決策操作，并且還可以用各種統(tǒng)計圖將數據表示出來。它廣泛地應用于財務、行政、金融、統(tǒng)計等領域，是目前最常用的辦公數據表格軟件。

與之前的其他版本相比，Excel2010擁有全新的用戶界面，它用簡單明了的功能區(qū)取代了Excel2003版本中的菜單和工具欄。本章將介紹Excel2010電子表格處理的相關知識。

任務4.1制作工資表

【任務描述】制作一個酒店員工工資表，練習數據的錄入，效果如圖4-1所示。【學習目的】(1)掌握啟動和退出Excel2010的操作方法。(2)熟悉Excel2010的工作界面，理解工作簿、工作表和單元格的基本概念。(3)熟練掌握Excel2010工作簿的建立和保存方法。(4)熟練掌握工作表的基本操作。(5)熟練掌握數據錄入和填充的方法?！救蝿找蟆?1)新建一個工作簿文件，將新建的工作簿文件保存在“D:\班級-學號-姓名”文件夾中，命名為“實訓一：酒店員工工資表.xlsx”，關閉該文件。(2)重新打開創(chuàng)建的“實訓一：酒店員工工資表.xlsx”文件，在Sheet1工作表中輸入圖4-1所示內容。①在A1單元格中輸入表格標題。②在A2單元格中輸入“制表日期”，在B2單元格中輸入系統(tǒng)當前日期。③在第3行中輸入“序號”、“姓名”、“性別”、“職務”、“基本工資”、“補貼”、“房貼”。④在A4～A10單元格中使用自動填充功能輸入員工序號(該序號是數字字符串)。⑤參照效果圖圖4-1輸入員工姓名及基本工資等。(3)將Sheet1中的表格內容復制到Sheet2的相同區(qū)域中，并將Sheet2重新命名為“工資表”。【任務實現】(1)雙擊桌面上已有的【MicrosoftExcel2010】快捷方式圖標，啟動Excel2010，新建一個空白工作簿。(2)按下Ctrl+S鍵，將新建的工作簿保存在“D:\班級-學號-姓名”文件夾中，并命名為“實訓一：酒店員工工資表.xlsx”，然后單擊標題欄右側的“關閉”按鈕【】，退出Excel2010。(3)重新啟動Excel2010，在【文件】選項卡中將光標指向【最近所用文件】命令，在其子菜單中選擇【實訓一：酒店員工工資表.xlsx】命令，打開剛才創(chuàng)建的新文件。(4)在A1單元格中單擊鼠標，輸入表格標題：“某酒店3月份員工工資表(單位：元)”。(5)在A2單元格中輸入文字“制表日a期”，然后按下鍵盤中的Tab鍵，進入B2單元格，按下Ctrl+;鍵輸入系統(tǒng)當前日期。(6)在第3行的單元格中分別輸入“序號”、“姓名”、“性別”、“職務”、“基本工資”、“補貼”和“房貼”。(7)在A4單元格中輸入“’9901001”，則輸入的數字為數字字符串，并在單元格中居左顯示。用同樣的方法，在A5單元格中輸入“’9901002”。(8)拖動鼠標同時選擇A4和A5單元格，然后拖動填充柄至A10單元格處，自動填充其他產品編號。(9)參照效果圖圖4-1輸入姓名、基本工資等。輸入時通過按鍵盤中的Tab鍵或方向鍵跳轉到相應的單元格中。(10)單擊工作表左上角的全選按鈕【】，選擇工作表中的所有內容，按下Ctrl+C鍵復制選擇的內容。(11)單擊工作簿左下方的【Sheet2】工作表標簽，切換到該工作表中。這時A1單元格自動處于選擇狀態(tài)，按下Ctrl+V鍵粘貼復制的內容。(12)在工作表標簽【Sheet2】名稱上雙擊鼠標，激活工作表名稱，輸入新名稱“工資表”，然后按下回車鍵確認。(13)按下Ctrl+S鍵保存對工作簿所做的修改。

【相關知識】4.1.1Excel2010概述大家都在紙上畫過表格，當你不得不拿起筆、尺子、橡皮的時候，是否有一些無奈。尤其是表格比較大時，工作量更是可觀，如果表格中再有大量的數據需要計算，那就更是頭疼了。隨著信息時代的來臨，大量的表格已由計算機來處理，電子表格成為了我們工作中重要的手段。Excel2010是一款功能完整、操作簡易的電子表格軟件。電子表格(Spreadsheet)，又稱電子數據表，是一類模擬紙上計算表格的計算機程序，它會顯示由一系列行與列構成的網格。單元格內可以存放數值、公式或文本。只要將數據輸入到規(guī)律排列的單元格中，便可依據數據所在單元格的位置，利用公式進行算術運算和邏輯運算，分析匯總各單元格中的數據信息，并且可以把相關數據用各種統(tǒng)計圖直觀地表示出來。4.1.2Excel2010啟動與退出與啟動Word2010一樣，啟動Excel2010也有兩種方法：一是通過【開始】菜單；二是通過快捷方式圖標。方法一：執(zhí)行【開始】—【所有程序】—【MicrosoftOffice】—【MicrosoftExcel2010】命令，如圖4-2所示，就可以啟動Excel2010應用程序，進入編輯狀態(tài)。方法二：如果在桌面上創(chuàng)建了【MicrosoftExcel2010】的快捷方式，可以雙擊該快捷方式圖標，快速啟動Excel2010應用程序，如圖4-3所示。圖4-2啟動Excel2010應用程序圖4-3MicrosoftExcel2010快捷方式圖標圖4-4執(zhí)行【關閉】命令在工作完成后需要退出Excel2010，退出時可采用以下三種方法：方法一：單擊標題欄右側的“關閉”按鈕【】。方法二：使用鍵盤上的Alt+F4快捷鍵。方法三：切換到【文件】選項卡，然后單擊【關閉】命令，如圖4-4所示。4.1.3Excel2010界面簡介啟動Excel2010以后，可以看到Excel2010的工作窗口與Word2010工作窗口很相似，很多組成部分的功能和用法與Word2010完全一樣，所以不再贅述。下面針對Excel2010特有的組成部分進行介紹，主要包括：快速訪問工具欄、功能選項卡、功能區(qū)、編輯欄、全選按鈕、行號與列標、工作表標簽、活動單元格、顯示比例工具等，如圖4-5所示。圖4-5Excel2010的界面組成1.快速訪問工具欄快速訪問工具欄，顧名思義，就是將常用的工具擺放于此，幫助使用者快速完成操作。預設的快速訪問工具欄只有3個常用工具，分別是【保存】、【撤消】和【恢復】。如果想將自己常用的工具也顯示在此處，請按“自定義快速訪問工具欄”按鈕【】進行設定，如圖4-6所示。2.功能選項卡Excel2010中默認情況下的功能選項卡分為八大類，包括【文件】、【開始】、【插入】、【頁面布局】、【公式】、【數據】、【審閱】和【視圖】。各選項卡中收錄相關的功能群組，方便使用者切換、選用。例如，【開始】選項卡中就是基本的操作功能，像【字體】、【對齊方式】等設置。只要切換到【開始】選項卡即可看到其中包含的功能。除了常規(guī)選項卡之外，Excel2010還包含許多附加選項卡。它們只在特定的操作中才會顯示出來，因此被稱為【上下文選項卡】。例如，在工作表中插入圖表，此時與圖表相關的選項卡才會顯示出來，如圖4-7所示。圖4-7上下文選項卡3.功能區(qū)Excel2010工作窗口的功能區(qū)放置了編輯工作表時需要使用的工具按鈕。啟動Excel2010時會默認顯示【開始】選項卡下的工具按鈕。當點擊其他的功能選項卡時，便會切換到相應的功能區(qū)。除了使用鼠標來點擊選項卡及功能區(qū)的按鈕外，也可以按鍵盤上的Alt鍵。它也可以顯示各個選項卡的快捷鍵提示信息，如圖4-8所示。當按下選項卡的快捷鍵后，會顯示功能區(qū)中的各功能按鈕的快捷鍵，方便以鍵盤的方式來進行操作，如圖4-9所示。圖4-8選項卡的快捷鍵提示信息圖4-9功能按鈕的快捷鍵提示信息在功能區(qū)中，按下【】按鈕，可以打開對應的對話框來做相應的設置。例如，我們想要設置單元格中字體的樣式，就可以切換到【開始】選項卡，找到【字體】組右下角的【】按鈕，如圖4-10所示。打開【設置單元格格式】對話框的【字體】選項卡，如圖4-11所示。圖4-10【字體】組右下角按鈕的提示信息圖4-11【設置單元格格式】對話框隱藏與顯示功能區(qū)：如果使用者覺得功能區(qū)占用太大的版面位置，可以將【功能區(qū)】隱藏起來。點擊【功能區(qū)最小化】按鈕即可，如圖4-12所示。4.編輯欄編輯欄是Excel2010特有的工具欄，主要由名稱框、按鈕和編輯框三部分組成。名稱框顯示的是當前單元格或區(qū)域的名稱，默認為“列字母+行數字”。編輯欄用于編輯單元格的內容，也可以編輯各種復雜的公式或函數。當進入活動單元格進行編輯時，會增加【取消】、【提交】、【插入函數】三個按鈕。名稱框位于左側。如圖4-13所示，名稱框中顯示的是單元格區(qū)域的名稱；如圖4-14所示，名稱框中顯示的是當前單元格的地址。編輯框位于右側。如圖4-15所示，編輯框中輸入的是文字；如圖4-16所示，編輯框中輸入的是公式。此外，編輯欄中還有三個按鈕，分別是“取消”按鈕【】、“輸入”按鈕【】、“插入函數”按鈕【】。如果數據輸入不正確，可以單擊【】按鈕來取消輸入的數據；如果數據輸入正確，則單擊【】按鈕來確認輸入的數據。單擊【】按鈕則可以插入函數。5.行號與列標工作表是一個由若干行與列交叉構成的表格，每一行與每一列都有一個單獨的標號來標識。用于標識行的稱為行號，由阿拉伯數字表示；用于標識列的稱為列標，由大寫英文字母表示。6.工作表標簽默認情況下，一個工作簿中包含3個工作表，每一個工作表都有自己的名稱，默認名稱分別為Sheet1、Sheet2、Sheet3。它們顯示在工作界面的左下角，稱為“工作表標簽”，通過鼠標單擊工作表標簽可以在不同的工作表之間進行切換。7.活動單元格當前正在使用的單元格稱為“活動單元格”，外觀上顯示一個帶黑色粗線的方框指示其位置。鼠標單擊任意單元格，它便成為活動單元格，可以向活動單元格內輸入數據?；顒訂卧竦牡刂凤@示在名稱框中。8.顯示比例工具窗口右下角是“顯示比例”區(qū)，可以查看目前工作表的顯視比例。按下【】按鈕可放大工作表的顯示比例，每按一次放大10%；反之，按下【】按鈕會縮小顯示比例，每按一次則會縮小10%。另外，也可以直接拉拽中間的滑動桿放大或縮小顯示比例。4.1.4Excel2010的基本要素在開始學習Excel2010之前，必須要搞清工作簿，工作表，單元格與單元格地址，單元格區(qū)域等概念以及工作簿、工作表、單元格三者之間的關系。1.工作簿工作簿(Book)是工作表的集合，以文件的形式存放在計算機的磁盤中，每一個Excel文檔就是一個工作簿。對于新創(chuàng)建的工作簿，系統(tǒng)會自動將其命名為工作簿1、工作簿2、工作簿3等。用戶可以在保存文件時對它進行重命名，其默認文件擴展名為“.xlsx”。這里提示一下，Excel2003及以前版本，其文件的擴展名為“.xls”。2.工作表工作簿相當于一個帳冊，工作表(Sheet)相當于帳冊中的一頁。默認情況下，每一個工作簿中包含3個工作表。理論上，一個工作簿中可以包含無數個工作表。工作表是用于輸入、編輯、顯示和分析數據的表格，由行和列組成，最多可以包含1048576行和16384列。行號用數字1，2，3，……，1048576表示，列號用A，B，C，……，Z，AA，AB，……，XFD表示。3.單元格與單元格地址Excel2010工作表中行和列交叉形成的小方格稱為“單元格”。單元格是構成工作表的基本元素，是Excel2010中的最小“存儲單元”，用于輸入、顯示和計算數據。這些數據可以是文字、字符串、數字、公式等內容。如果輸入的是文字或數字，則原樣顯示；如果輸入的是公式或函數，則顯示其結果。單元格地址是用來標識一個單元格的坐標的，常用“列標+行號”來表示。例如，A1表示第A列第1行的單元格，故將A1稱為該單元格的地址。單元格地址有多種表示方法：C2表示第C列第2行的單元格。B1:B6表示第B列第1行到第6行之間的單元格區(qū)域。A2:F2表示在第2行中第A列到第F列之間的單元格區(qū)域。4:4表示第4行中的全部單元格。F:F表示第F列中的全部單元格。1:5表示第1行到第5行之間的全部單元格。D:E表示第D列到第E列之間的全部單元格。C3:F10表示第C列第3行到第F列第10行之間的單元格區(qū)域。每一個單元格都對應一個固定的地址，當前正在使用的單元格稱為“活動單元格”。17所示。

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

Excel2010對數據的組織方式是通過工作簿、工作表和單元格來完成的，它們三者之間的關系是包含關系。一個Excel文檔就是一個工作簿，工作簿中包含一個或多個工作表(不能沒有工作表)；而工作表又是由許多單元格構成，用于輸入、顯示和計算數據。當操作Excel時，我們是直接與單元格打交道的。工作簿、工作表、單元格三者之間的關系如圖4-17所示。

5.單元格區(qū)域單元格區(qū)域是由多個單元格組成的矩形區(qū)域，其表示方法是“區(qū)域左上角單元格地址:區(qū)域右下角單元格地址”。其中，“:”為英文狀態(tài)下的冒號。例如，單元格區(qū)域“A1:B3”表示的范圍為A1、B1、A2、B2、A3、B3共6個單元格組成的矩形區(qū)域，如圖4-18所示。如果需要對很多單元格區(qū)域進行同一操作，可以將這一系列區(qū)域定義為數據系列，由“，”隔開。例如，區(qū)域“A1:B3，C4:D5”表示的區(qū)域包括A1、B1、A2、B2、A3、B3、C4、D4、C5、D5共10個單元格，如圖4-19所示。4.1.5工作簿的基本操作我們在使用Excel2010處理數據之前，首先要學會工作簿的創(chuàng)建、打開、保存與關閉等基本操作，下面分別進行介紹。1.創(chuàng)建工作簿新建一個工作簿主要有以下四種方法：(1)啟動Excel2010后，系統(tǒng)會自動創(chuàng)建一個名為“工作簿1”的空白工作簿文件。(2)在已經啟動的Excel2010程序中，按下Ctrl+N組合鍵。(3)在Excel2010程序中，單擊快速訪問工具欄中的“新建”按鈕【】，也可以快速地創(chuàng)建新的工作簿，如圖4-20所示。圖4-20創(chuàng)建新的工作簿(4)在Excel2010程序中，切換到【文件】選項卡，單擊【新建】命令，然后在中間的【可用模板】列表中選擇“空白工作簿”，再單擊右下角的【創(chuàng)建】按鈕，即可創(chuàng)建一個空白的工作簿，如圖4-21所示。圖4-21創(chuàng)建空白工作簿若要基于模板創(chuàng)建工作簿，可以在【文件】選項卡中的【新建】命令下找到【可用模板】列表。在列表中單擊“樣本模板”選項，打開系統(tǒng)提供的模板，然后在其中選擇要使用的模板，如“個人月預算”，如圖4-22所示。最后，單擊右下角的【創(chuàng)建】按鈕，就可以基于模板創(chuàng)建工作簿，如圖4-23所示。新的工作簿中會含有一些數據、工作表等信息，使用的時候修改這些數據即可。2.保存工作簿為了使工作簿中的數據不丟失，便于在以后的工作中使用，Excel2010提供了多種保存工作簿的方法。(1)在【文件】選項卡中，單擊【保存】命令(也可以按下Ctrl+S鍵或Shift+F12鍵)就可以保存工作簿，如圖4-24所示。如果該工作簿是第一次保存，將彈出【另存為】對話框。在該對話框中指定保存文件的位置，并在【文件名】文本框中輸入名稱，在【保存類型】下拉框中選擇“Excel工作簿(*.xlsx)”，如圖4-25所示。最后，單擊【】按鈕即可保存工作簿。(2)對于已經保存過的工作簿，在修改數據以后，直接單擊快速訪問工具欄中的【保存】按鈕即可進行保存，而且將不再彈出【另存為】對話框，而是直接保存并覆蓋原來的文件。

3.打開工作簿

當需要使用存儲在計算機中的工作簿時，用戶只需要找到該文件，雙擊工作簿文檔圖標，就可以啟動Excel2010并打開該工作簿。另外，也可以通過【打開】命令打開工作簿，具體操作步驟如下：

(1)在【文件】選項卡中，選擇【打開】命令(或者按下Ctrl+O鍵)，如圖4-26所示。

(2)在彈出的【打開】對話框中的文件列表下選擇要打開的工作簿，單擊【】按鈕即可將其打開，如圖4-27所示。在【另存為】對話框里的【保存類型】下拉框中，可以選擇自己需要的保存類型。若想要保存的文件使用低版本的Excel也可以打開，則可以在下拉框中選擇“Excel97-2003工作簿(*.xls)”，以方便多個版本的Excel程序打開該文件。小貼士4.1.6工作表的基本操作啟動Excel2010后，默認情況下，工作簿中只有3個工作表，即Sheet1、Sheet2、Sheet3。用戶可以根據需要對工作表進行插入、刪除、移動、復制等操作。1.工作表的切換與選定鼠標左鍵單擊工作表標簽，可以在不同的工作表之間進行切換。如果看不到所需的工作表標簽，可以單擊工作表標簽左側的標簽滾動按鈕，以顯示所需工作表標簽。也可以使用快捷鍵Ctrl+PageUp(切換到上一個工作表)和Ctrl+PageDown(切換到下一個工作表)快速進行工作表切換。如果用戶需要同時對多個工作表進行操作，可以選定多個工作表，其方法如下：(1)如果要選定兩個或多個相鄰的工作表，先單擊第一個工作表標簽，然后按住Shift鍵單擊要選定的最后一個工作表標簽。(2)如果要選定兩個或多個不相鄰的工作表，先單擊第一個工作表標簽，然后按住Ctrl鍵依次單擊其他工作表標簽。(3)如果要選定工作簿中所有的工作表，可以在任意工作表標簽上單擊鼠標右鍵，在彈出的快捷菜單中選擇【選定全部工作表】命令。(4)選定多個工作表以后，Excel2010的工作簿標題欄中將出現“工作組”字樣，如圖4-28所示。此時在“工作組”中的任意一個工作表中輸入文本，其他選定的工作表中也將出現該文本。圖4-28標題欄中出現“工作組”字樣2.重命名工作表默認情況下，工作表標簽為Sheet1、Sheet2、Sheet3等。如果工作簿中的工作表很多，使用這種默認的命名方式可能會造成工作上的不便。為了解決這個問題，可以為每個工作表重新命名。例如，若Sheet1工作表中是學生學籍信息，則可以將該工作表重新命名為“學生學籍信息表”。重命名工作表有兩種方法：一是在工作表標簽上單擊鼠標右鍵，在彈出的快捷菜單中選擇【重命名】命令，如圖4-29所示；二是直接雙擊工作表標簽，如圖4-30所示。不論哪一種方法，激活工作表名稱后，輸入新名稱按下回車鍵即可。3.插入與刪除工作表默認情況下，一個新工作簿中只有3個工作表，而在實際工作中我們可能需要更多的工作表，此時就需要在工作簿中插入工作表。新插入的工作表會在原工作表數目的基礎上依次命名。例如，若原來有3個工作表，則新工作表名稱為“Sheet4”。插入新工作表的具體操作步驟如下：(1)在工作表標簽上單擊鼠標右鍵，在彈出的快捷菜單中選擇【插入】命令，如圖4-31所示。(2)在彈出的【插入】對話框中的【常用】選項卡下，選擇“工作表”選項，然后單擊【】按鈕，如圖4-32所示，即可在當前工作表的前面插入一張新的工作表。除了使用上述方法插入工作表外，Excel2010還提供了其他三種插入工作表的方法：一是單擊工作表標簽右側的“插入工作表”按鈕【】(或者按快捷鍵Shift+F11)，如圖4-33所示。二是在【開始】選項卡的“單元格”組中打開“插入”按鈕【】下方的下拉列表，選擇【插入工作表】選項，如圖4-34所示。

圖4-33插入工作表 圖4-34插入工作表三是在【文件】選項卡中選擇【選項】命令，這時會彈出工作簿的【Excel選項】對話框，如圖4-35所示。在彈出的【Excel選項】對話框中，選擇并點擊左側窗格的【常規(guī)】選項卡。在【常規(guī)】選項卡對應的右側窗口中，找到【新建工作簿時】組中的【包含的工作表數(S)】選項，并將其修改為“5”，然后再點擊【確定】按鈕即可。再次啟動Excel2010程序，工作簿中將有5個工作表，如圖4-36所示。如果工作簿中包含多余的工作表，可以將多余的表刪除。刪除工作表的具體方法為：在要刪除的工作表標簽上單擊鼠標右鍵，從彈出的快捷菜單中選擇【刪除】命令，如圖4-37所示。也可以在【開始】選項卡的“單元格”組中打開“刪除”按鈕【】下方的下拉列表，選擇【刪除工作表】選項，如圖4-38所示。在刪除工作表時，如果工作表非空(即含有數據)，系統(tǒng)會提示是否要真正刪除。此時要根據實際需要進行操作，以避免出現誤刪除。4.移動或復制工作表如果已有工作表中的數據與正要制作的工作表所需的數據有許多相同的，那么可以通過移動或復制工作表來提高工作效率。Excel2010為移動或復制工作表提供了兩種操作方法：(1)使用鼠標拖動。選擇要復制的工作表標簽，按住鼠標左鍵的同時按住Ctrl鍵，這時鼠標指針上方顯示一個內含小黑十字的白色信箋圖標，該工作表標簽左上角會出現一個黑色的小三角形，指示工作表復制的目標位置。沿著標簽左右拖動鼠標，當黑色小三角形到達目標位置時，先松開鼠標左鍵，再放開Ctrl鍵，此時便在指定位置出現一個選定的工作表的副本。(2)使用快捷菜單中的【移動或復制】命令。在要移動或復制的工作表標簽上單擊鼠標右鍵，從彈出的快捷菜單中選擇【移動或復制】命令，如圖4-39所示。在打開的【移動或復制工作表】對話框中選擇工作表移動或復制的目標位置。如果要復制工作表，需要勾選【建立副本】選項，單擊【】按鈕，即可完成工作表的移動或復制操作，如圖4-40所示。4.1.7保護工作表和工作簿財務辦公人員在編輯工作表時常常需要防止報表中的信息被修改，以避免造成不必要的數據丟失或破壞，此時可以對工作表和工作簿進行保護。保護工作表和工作簿的基本操作步驟如下：(1)打開要保護的工作表。(2)切換到【審閱】選項卡，單擊“更改”組中的“保護工作表”按鈕【】，如圖4-41所示。(3)此時將彈出【保護工作表】對話框，在該對話框中選中【保護工作表及鎖定的單元格內容】復選框，然后在【取消工作表保護時使用的密碼】文本框中輸入密碼，如“123456”(密碼長度不能超過255個字符)，并選中【允許此工作表的所有用戶進行】列表框中的“選定鎖定單元格”和“選定未鎖定的單元格”復選框，然后單擊【】按鈕，如圖4-42所示。(4)在彈出的【確認密碼】對話框中重新輸入密碼，單擊【】按鈕進行確認，如圖4-43所示。這樣就完成了對工作表的密碼保護設置。(5)當用戶需要編輯該工作表時，系統(tǒng)會彈出如圖4-44所示的提示對話框，單擊【】按鈕關閉該對話框。圖4-43【確認密碼】對話框圖4-44提示對話框(6)單擊“更改”選項組中的“撤消工作表保護”按鈕【】，如圖4-45所示。此時，系統(tǒng)將彈出【撤消工作表保護】對話框，如圖4-46所示。然后，在【密碼】文本框中輸入保護的密碼，單擊【】按鈕即可編輯該工作表。(7)對工作簿的結構和窗口進行保護，可以在【審閱】選項卡的“更改”組中單擊“保護工作簿”按鈕【】，如圖4-47所示。(8)在彈出的【保護結構和窗口】對話框中選擇【結構】或【窗口】選項，并在【密碼(可選)(P)】文本框中輸入密碼，如“111”，單擊【】按鈕，如圖4-48所示。(9)在彈出的【確認密碼】對話框中輸入確認密碼，單擊【】按鈕，如圖4-49所示。這樣就完成了對工作簿結構的保護。圖4-47保護工作當我們再次打開該文件，或者其他用戶需要修改該文件內容的時候，就必須添加密碼才能對其進行編輯了4.1.8數據的輸入創(chuàng)建好一個工作簿后，首先要向工作表中輸入數據，而工作表中的數據存放在單元格中，這些輸入的數據可以是數字、文本、日期、時間、公式、函數和邏輯值等。在單元格中輸入數據時，系統(tǒng)會自動判斷所輸入的數據是哪一種類型，并進行適當的處理。1.文本的輸入在Excel2010中，文本可以是任何字符串(包括字符或任何數字與字符的組合)。輸入文本時，首先要選定單元格，然后再輸入字符串。默認的單元格寬度是8個12磅的英文字符，一個單元格內最多可以存放3200個字符。當輸入的文本超過默認的寬度時，如果其右側的單元格中沒有內容，則該單元格的內容將顯示到其右側的單元格中，但這只是一種表象，事實上它仍然在本單元格中，如圖4-50所示。如果右側的單元格中有數據，則該單元格中的文字只能顯示一部分，但其內容依然完好無損地存在，用戶可以從編輯框中查看全部內容，如圖4-51所示。系統(tǒng)默認所有文本數據在單元格中均為左對齊顯示。輸入文本時應該注意以下幾點：(1)一般地，字母和漢字這類文本可以直接輸入。(2)如果想將數字作為文本輸入，需要在數字前面加上英文狀態(tài)下的單引號“’”，如’123；或者在數字前面加上一個等于號，并用英文狀態(tài)下的雙引號把輸入的數字括起來，如="123"。這兩種方法都可以讓輸入的數字左對齊顯示。2.數字的輸入如果在單元格中輸入的數字是直接參與工作表運算的數字型數據，那么這種數字可以直接輸入到單元格中。默認情況下，數字在單元格中右對齊顯示，如圖4-52所示。如果輸入的數字是不能參與運算的數字字符串，如區(qū)號、郵政編碼等，這時就需要將數字作為文本輸入，否則容易被Excel認為是數字型數據。例如，輸入區(qū)號“012”時，如果直接輸入，第一個數字“0”就會被忽略掉。把數字作為文本輸入的輸入方法上一部分已經講解，如圖4-53所示。在Excel2010中輸入數據要掌握以下原則：輸入的數字較大時，可以在數字中使用千分號“，”隔開，如12，345，678。在輸入負數時要在前面加上一個減號或將數字用圓括號括起來，如“(12.3)”和“-12.3”均顯示為-12.3。輸入分數時要避免和日期型數據混淆，如輸入1/2時，要先輸入0和1個空格，再輸入“1/2”。當輸入的整數、小數或當數字的長度超出單元格的寬度時，Excel2010將自動使用科學記數法來表示輸入的數字，如輸入“12345678912345”時，Excel2010會用“1.23457E+13”來表示該數字。3.日期和時間的輸入在Excel2010中，日期和時間數據在單元格中右對齊顯示。在Excel2010中，可以使用下列快捷鍵設置日期和時間格式：若只輸入月和日，則系統(tǒng)以計算機內部時鐘的年份作為默認值。如輸入5/4，按回車后顯示5月4日，當再把剛才的單元格選為活動單元格時，在編輯框中將顯示2017-5-4。時間分隔符一般使用冒號“：”。輸入系統(tǒng)當前日期，使用Ctrl+;快捷鍵。輸入系統(tǒng)當前時間，使用Shift+Ctrl+;快捷鍵。使用12小時計時方式，需要在時間后輸入一個空格，然后輸入“am”或“pm”，如“2:00pm”表示下午14：00。系統(tǒng)默認使用24小時制，不需要輸入“am”或“pm”。4.1.9快速填充在Excel2010中，對于一些規(guī)律性比較強的數據，可以使用快速填充的方法輸入，從而提高工作效率，減少出錯率，如使用等差序列、等比序列可以自動填充一個序列。1.填充相同數據如果要在多個單元格中輸入相同的數據，可以先選定單元格區(qū)域(選定方法見4.2.1節(jié)單元格的基本操作)，如圖4-54所示。在單元格中輸入想要填充的數字或文字等內容，例如輸入“荷塘月色”。輸入文字后，按住Ctrl鍵的同時按下回車鍵，則選定的單元格全部填上了同樣的內容，如圖4-55所示。2.填充序列填充序列在實際工作中運用范圍很廣，用戶可以按照等比序列、等差序列等類型進行手動填充。下面以創(chuàng)建等比序列為例介紹填充數據的操作方法。(1)首先在一個單元格中輸入一個起始值，如圖4-56所示。(2)在【開始】選項卡的“編輯”組中單擊【】按鈕，在打開的下拉列表中選擇【系列】選項，如圖4-57所示。(3)在打開的【序列】對話框中選擇【等比序列】選項，并設置步長值、終止值以及序列產生在行或列，如圖4-58所示。(4)單擊【】按鈕，則在單元格中的A列填充了等比序列，如圖4-59所示。

圖4-58【序列】對話框 圖4-59填充的等差序列在【序列】對話框中，各選項的作用如下：【序列產生在】：用于設置按行或按列填充數據?！绢愋汀浚河糜诖_定系列填充的4種類型?！绢A測趨勢】：選擇該選項，Excel2010自動預測數據變化的趨勢，并根據此趨勢進行填充。【步長值】：用于設置等差序列的公差或等比序列的公比?！窘K止值】：用于設置序列的結束值。3.自動填充Excel2010有一個特殊的工具——填充柄，如圖4-60所示，即選擇活動單元格后黑框右下角的小黑方塊。將鼠標移到填充柄上時，光標會變成一個實心的黑十字形，這時拖動鼠標即可填充相應的內容，這種操作稱為“自動填充”。對于一些有規(guī)律的數據，比如1，2，3，4，……，a1，a2，a3……等，用戶可以使用自動填充的方法快速完成，無需逐個輸入。使用填充柄填充單元格時有以下兩種情況需要分別說明：第一，如果選定單元格中的內容是不存在序列狀態(tài)的文本，拖動填充柄時將對文本進行復制，如圖4-61所示。第二，如果選定單元格中的內容是序列，拖動填充柄時將自動填充序列。例如，在A1單元格中輸入“一月”，選定該單元格后拖動填充柄，將依次填充“二月，三月，……”，如圖4-62所示。如果按住鼠標右鍵進行拖動，釋放鼠標后將彈出一個快捷菜單，通過這個菜單可以確定填充方式，如圖4-63所示。通常情況下，可以自動填充的序列類型有以下四種：時間序列：包括年、月、日、時間、季度等增長或循環(huán)的序列，如“一月，二月，三月，……”。等差序列：即兩兩差值相等的數字，如“1，4，7，10……”。等比序列：即兩兩比例因子相等的數字，如“2，4，8，16……”。擴展序列：由文本與數字構成的序列，如“客戶1，客戶2，客戶3……”。4.自定義序列除了可以使用系統(tǒng)提供的序列以外，用戶還可以利用自定義序列功能添加新數據序列，以用于自動填充復制，這可以大大提高錄入數據的工作效率，以減少重復工作。下面以序列“信息學院、經管學院、機電學院、汽車學院”為例，介紹自定義序列的方法，具體操作步驟如下：(1)在【文件】選項卡中，單擊【選項】命令。(2)在彈出的【Excel選項】對話框中的左窗格中選擇【高級】，然后在右窗格中單擊【】按鈕，如圖4-64所示。(3)在打開的【自定義序列】對話框中輸入自定義的序列，如“信息學院、經管學院、機電學院、汽車學院”，單擊【】按鈕，即可將其加入到【自定義序列】列表中，如圖4-65所示。(4)單擊【】按鈕，返回【Excel選項】對話框，再單擊【】按鈕，關閉該對話框，即完成了自定義序列的設置。(5)在單元格中輸入“信息學院”，然后拖動填充柄到相應的單元格，則Excel會自動填充自定義的序列，如圖4-66所示。任務4.2格式化消費調查表【任務描述】制作一個消費調查表，對表格進行格式化設置，效果如圖4-67所示。圖4-67消費調查表效果【學習目的】(1)掌握單元格區(qū)域的合并方法。(2)掌握單元格格式的設置方法。(3)熟練設置行高與列寬。(4)掌握設置單元格邊框與填充顏色的方法?！救蝿找蟆?1)新建一個工作簿文件，將新建的工作簿文件保存在“D:\班級-學號-姓名”文件夾中，命名為“實訓二：消費調查表.xlsx”。(2)參照圖4-67在Sheet1工作表中輸入文字內容。(3)設置行高與列寬。①行高：第1行為23，第2～15行為15。②列寬：第1列為10，第2列為8，第3～5列為12。③將單元格區(qū)域A1:E1合并為一個單元格。(4)設置單元格內容格式。①第1行：隸書、18磅、加粗。②其他文字：楷體_GB2312、12磅、加粗、居中顯示。③參照效果圖設置數字格式。(5)設置單元格邊框與底紋。①外邊框與第1行下邊框：粗線。②內邊框：細線。③填充設置：第1行淺綠色、第2行藍色、其他行淡藍色?！救蝿諏崿F】(1)啟動Excel2010。(2)按下Ctrl+S鍵，將工作簿保存在“D:\班級-學號-姓名”文件夾中，名稱為“實訓二：消費調查表.xlsx”。(3)在Sheet1工作表中輸入表格內容，如圖4-68所示。(4)在行號1上單擊鼠標右鍵，在彈出的快捷菜單中選擇【行高】命令，在打開的【行高】對話框中設置行高為23，如圖4-69所示。(5)在行號2～15上拖動鼠標選擇多行，參照步驟(4)中的方法，設置行高為15。(6)在列標A上單擊鼠標右鍵，在彈出的快捷菜單中選擇【列寬】命令，在打開的【列寬】對話框中設置列寬為10；用同樣的方法，設置B列為8，C～E列為12。(7)拖動鼠標選擇單元格區(qū)域A1:E1，在【開始】選項卡的“對齊方式”組中單擊【合并后居中】按鈕，將其合并為一個單元格，并使內容居中顯示。(8)選擇A1單元格，在【開始】選項卡的“字體”組中設置字體為隸書，字號為18磅，然后單擊【】按鈕將字體加粗，如圖4-70所示。(9)選擇第2行，在“字體”組中設置字體為楷體_GB2312，字號為12磅、單擊【】按鈕將字體加粗，然后在“對齊方式”組中單擊【】按鈕，將文字居中顯示。(10)拖動鼠標選擇單元格區(qū)域A3:E15，設置字體為楷體_GB2312，字號為12磅，單擊【】按鈕將字體加粗。然后，在“對齊方式”組中單擊【】按鈕，將文字居左顯示，此時的表格效果如圖4-71所示。(11)拖動鼠標選擇單元格區(qū)域C3:D15，單擊鼠標右鍵，在彈出的快捷菜單中選擇【設置單元格格式】命令，在打開的【設置單元格格式】對話框中設置格式如圖4-72所示。(12)單擊【確定】按鈕，則設置了數字格式為貨幣，如圖4-73所示。(13)選擇A1單元格，在【開始】選項卡的“字體”組中單擊【】按鈕，在打開的下拉列表中選擇【粗匣框線】選項；再單擊【】按鈕，在下拉列表中選擇淺綠色作為填充色。(14)選擇單元格區(qū)域A2:E15，在【開始】選項卡的“字體”組中單擊【】按鈕，在打開的下拉列表中選擇【所有框線】選項，然后再次單擊【】按鈕，在打開的下拉列表中選擇【粗匣框線】選項。(15)選擇單元格區(qū)域A2:E2，在【開始】選項卡的“字體”組中單擊【】按鈕，在下拉列表中選擇藍色作為填充色；繼續(xù)選擇單元格區(qū)域A3:E15，單擊【】按鈕，在下拉列表中選擇淡藍色作為填充色。(16)按下Ctrl+S鍵保存文件?！鞠嚓P知識】

控制工作表數據外觀的信息稱為格式。單純地在工作表中輸入數據是沒有任何特殊的格式的。為了更加直觀、有效地表達工作表中的數據，使其美觀化、條理化、清晰化，我們可以通過改變工作表上單元格的格式，使工作表中的表格美觀大方，賞心悅目，便于閱讀。

4.2.1單元格的基本操作

1.選擇單元格

在Excel2010中，用戶在對單元格進行操作之前，必須先選定單元格。用戶可以一次只選擇一個單元格，也可以選擇多個連續(xù)的單元格，還可以同時選擇多個不連續(xù)的單元格。表4-1列出了各種不同的選擇方法。

表4-1選擇單元格的六種方法

2.修改單元格內容數據輸入時難免會出現輸入錯誤的現象，這時需要修改已經輸入的數據。在Excel2010中，修改單元格內容的方法有兩種：在單元格內直接修改和在編輯欄中進行修改。在單元格內直接修改內容的操作步驟如下：(1)選擇要修改內容的單元格。(2)輸入新的數據，則新輸入的數據將替換掉原來的內容。(3)如果只需要修改原來內容中的個別字，可以雙擊單元格或按下F2鍵，進入可編輯狀態(tài)，將插入點光標移動到要修改的位置處進行修改，然后單擊編輯欄中的【】按鈕確認操作。通過編輯欄修改內容的操作步驟如下：(1)選擇要修改內容的單元格。(2)這時單元格中的內容將顯示在編輯欄中，在編輯欄中單擊鼠標定位插入點光標，或者選擇要修改的內容，如圖4-74所示。(3)輸入新的內容后單擊編輯欄中的【】按鈕確認操作即可，如圖4-75所示。3.清除單元格內容如果要清除單元格中的內容，可以按以下三種方法進行操作：(1)選擇要清除內容的單元格，按下Delete鍵。(2)選擇要清除內容的單元格，在【開始】選項卡的“編輯”組中單擊【】按鈕，在打開的下拉列表中選擇【清除內容】選項，如圖4-76所示。(3)選擇要清除內容的單元格，單擊鼠標右鍵，從彈出的快捷菜單中選擇【清除內容】命令，如圖4-77所示。4.移動或復制單元格內容在Excel2010中，可以很方便地對單元格內的數據進行移動或復制操作。通常情況下，可以使用鼠標移動或復制單元格數據，也可以使用工具按鈕完成移動或復制操作。使用鼠標移動或復制單元格數據的操作步驟如下：(1)選擇要移動或復制的單元格或單元格區(qū)域。(2)如果要進行移動，可以將光標指向所選單元格的邊框上，當光標變成箭頭形狀時按住鼠標左鍵拖動到新位置，如圖4-78所示。(3)釋放鼠標后，則移動了所選單元格中的內容，如圖4-79所示。(4)如果要進行復制，可以將光標指向所選單元格的邊框上，當光標變成箭頭形狀時，先按住Ctrl鍵，再按住鼠標左鍵拖動到新位置即可。使用工具按鈕移動或復制單元格數據的操作步驟如下：(1)選擇要移動或復制的單元格或單元格區(qū)域，在【開始】選項卡的“剪貼板”組單擊【】按鈕或【】按鈕。(2)選擇目標單元格或單元格區(qū)域，在【開始】選項卡的“剪貼板”組單擊【】按鈕，即可以完成移動或復制單元格內容的操作。5.插入與刪除單元格用戶可以根據需要在工作表中插入空的單元格，操作步驟如下：(1)選擇單元格或單元格區(qū)域，要插入多少單元格就選擇多少單元格。(2)在【開始】選項卡的“單元格”組中打開【】按鈕下方的下拉列表，選擇【插入單元格】選項，如圖4-80所示。(3)在打開的【插入】對話框中選擇一種插入方式，如圖4-81所示。(4)單擊【】按鈕，即可插入單元格。在【插入】對話框中，各選項的作用如下：【活動單元格右移】：選擇該選項，則插入單元格后原單元格向右移動?！净顒訂卧裣乱啤浚哼x擇該選項，則插入單元格后原單元格向下移動?！菊小浚哼x擇該選項，將插入整行單元格?！菊小浚哼x擇該選項，將插入整列單元格。刪除單元格的操作與插入單元格類似：選擇要刪除的單元格，在【開始】選項卡的“單元格”組中打開【】按鈕下方的下拉列表，選擇【刪除單元格】選項，如圖4-82所示；在彈出的【刪除】對話框中選擇一種刪除方式，單擊【】按鈕即可，如圖4-83所示?！緞h除】對話框中四種選項的作用如下：【右側單元格左移】：表示刪除選中單元格后，該單元格右側的整行向左移動一格;【下方單元格上移】：表示刪除選中單元格后，該單元格下方的整列向上移動一格;【整行】：表示刪除該單元格所在的一整行;【整列】：表示刪除該單元格所在的一整列;6.合并單元格制作表格時，如果表格的標題內容較長，而且居中顯示需要占用多個單元格，這時就需要合并單元格。合并單元格的具體操作步驟如下：(1)選擇要合并的多個單元格，如圖4-84所示。(2)在【開始】選項卡的“對齊方式”組中單擊【】按鈕，即可合并單元格，并使內容居中顯示，如圖4-85所示。4.2.2行與列的設置每一個工作表都是由若干行和若干列組成的。操作工作表時，經常需要對行與列進行操作。例如，我們要按照學號錄入一個學生名單，錄入完成后卻發(fā)現漏掉了兩名學生，這時就可以根據學號所在的位置插入行，然后再補錄到工作表中。1.插入行和列下面結合實例講解插入行和列的方法，具體操作步驟如下：(1)單擊行號4選擇該行，即序號“2”所在的行，如圖4-86所示。(2)在【開始】選項卡的“單元格”組中打開【】按鈕下方的下拉列表，選擇【插入工作表行】選項，如圖4-87所示。(3)這時將在當前行的上方插入新行，同時Excel2010自動將行號依次順延，如圖4-88所示。插入新行以后，就可以在其中輸入內容了。插入列與插入行操作類似，新插入列將位于選定列的左側，且Excel2010自動將右側的列標向后順延，但工作表的總列數保持不變。2.刪除行和列在編輯Excel工作表的過程中，如果遇到多余的行或列，可以將其刪除。具體操作步驟如下：(1)選擇要刪除的行，可以是一行或多行。(2)在【開始】選項卡的“單元格”組中打開【】按鈕下方的下拉列表，選擇【刪除工作表行】選項。刪除行以后，其下方的行號將自動減少以順延編號。也就是說，刪除行以后，行號并不會少，仍然依次排列。另外，刪除行以后，行中的內容也一并被刪除。刪除列與刪除行的操作類似，刪除列后，其右側的列標會自動向前順延。3.移動行和列編輯工作表時，經常需要調整某些行之間或某些列之間的順序。例如，學生名單中的“姓名”與“性別”位置錯了，需要進行調整，可以按如下步驟進行操作：(1)選擇“姓名”所在的列。(2)在【開始】選項卡的“剪貼板”組中單擊【】按鈕，剪切該列中的內容，如圖4-89所示。(3)選擇“性別”所在的列，在該列任意單元格上單擊鼠標右鍵，在彈出的快捷菜單中選擇【插入剪切的單元格】選項，即可在“性別”列前插入“姓名”列，如圖4-90所示。4.調整行高、列寬編輯工作表時經常會遇到一些麻煩，如有些單元格中的文字只顯示了一半，有些單元格只顯示“#######”等。這些情況都是因為單元格的行高或列寬不滿足要求而造成的，需要通過調整行高或列寬來解決。將光標指向兩行之間，當光標變成雙向箭頭時拖動鼠標，即可調整行高，如圖4-91所示；同理，將光標指向兩列之間，當光標變成雙向箭頭時拖動鼠標，即可調整列寬，如圖4-92所示。4.2.3設置單元格格式要美化工作表，首先應該從設置單元格的格式開始。單元格格式的設置包括設置數字格式、對齊方式、字體、字號、邊框、背景等。1.設置數字格式通常情況下，輸入到單元格中的數字不包含任何特定的數字格式。由于Excel是一種電子表格，它的主要操作對象是數字，因此經常需要將數字設置為一定的數字格式，以方便用戶識別與操作。設置數字格式的操作步驟如下：(1)選擇要設置格式的單元格區(qū)域。(2)在【開始】選項卡的“數字”組中單擊【】按鈕右側的三角箭頭，在打開的下拉列表中選擇【貨幣】選項，如圖4-93所示。(3)選擇了數字格式后，馬上得到相應的數字效果，如圖4-94所示。(4)如果要設置更多的數字格式，除了選擇下拉列表中的格式以外，還可以選擇列表中的【其他數字格式】選項，然后在打開的【設置單元格格式】對話框中設置其他格式，如圖4-95所示。(5)單擊【】按鈕，則所選單元格中的數字將按指定的格式顯示。2.設置字符格式為了美化工作表，可以設置字符的格式，這也是美化工作表外觀的最基本方法。字符格式主要包括字體、字號、顏色等。設置字符格式的基本操作步驟如下：(1)選擇要設置字符格式的單元格區(qū)域。(2)在【開始】選項卡的“字體”組中可以設置字體、字形、字號、顏色及特殊效果等屬性，如圖4-96所示。具體方法可以參照Word2010部分的內容。(3)如果要進行更多的設置，可以單擊“字體”組右下角的【】按鈕，打開【設置單元格格式】對話框，在【字體】選項卡中可以設置字符格式，這里有更多的設置，如“上標”或“下標”等，如圖4-97所示。(4)單擊【】按鈕，即可完成字符格式的設置。3.設置對齊格式默認情況下，輸入到單元格中的文字居左顯示，而數字、日期和時間則居右顯示。如果要改變單元格內容的對齊方式，可以按如下步驟進行操作：(1)選擇要設置對齊方式的單元格區(qū)域。(2)在【開始】選項卡的“對齊方式”組中可以設置水平方向與垂直方向的對齊，如圖4-98所示。(3)單擊這些按鈕，就可以設置相應的對齊效果。圖4-99所示為居中對齊后的效果。

圖4-99居中對齊后的效果

(4)選擇單元格區(qū)域，例如選擇第2行的表頭，在【開始】選項卡的“對齊方式”組中單擊【】按鈕，在打開的下拉列表中選擇【順時針角度】選項，可以改變文字的方向，如圖4-100所示。

在“方向”下拉列表中可以選擇其他文本方向。如果選擇【設置單元格對齊方式】選項，可以打開【設置單元格格式】對話框，以進行更加詳細的設置。4.設置邊框和底紋

在Excel2010中，默認情況下網格線是不打印的，因此制作完表格后需要添加表格邊框。另外，為了美化裝飾表格，還可以為部分單元格添加底紋或圖案，以突出顯示單元格內容。為表格或單元格添加邊框的操作步驟如下：

(1)選擇要添加邊框的表格或單元格。

(2)在【開始】選項卡的“字體”組中單擊【】按鈕，在打開的下拉列表中選擇【所有框線】選項，則為表格添加了邊框，如圖4-101所示。(3)如果要進行更豐富的邊框設置，可以在下拉列表中選擇【其他邊框】選項，打開【設置單元格格式】對話框。然后，在【邊框】選項卡中進行設置，如圖4-102所示。(4)單擊【】按鈕，即可完成表格邊框的設置。

設置了表格邊框以后，還可以為表格或單元格添加底紋，使其更加美觀，具體操作步驟如下：

(1)選擇要添加底紋的單元格區(qū)域，如A2:E2。

(2)在【開始】選項卡的“字體”組中單擊【】按鈕，在打開的下拉列表中選擇一種顏色，則添加了底紋效果，如圖4-103所示。

(3)用同樣的方法，可以為其他單元格區(qū)域添加底紋效果，如圖4-104所示。5.自動套用格式

在制作表格的過程中，用戶往往先在表格中輸入數據，然后再逐項設置表格的格式，這樣制作出來的表格具有鮮明的個性，但這會浪費大量的時間。在Excel2010中，系統(tǒng)預設了多種表格樣式，用戶可以根據需要選擇一種表格樣式，然后直接將其應用到表格中，這樣可以大大提高工作效率。自動套用格式的基本操作步驟如下：

(1)選擇要套用格式的單元格區(qū)域。

(2)在【開始】選項卡的“樣式”組中打開【】按鈕下方的下拉列表，選擇要套用的單元格樣式，如圖4-105所示。(3)如果要對整個表格套用格式，則在“樣式”組中打開【】按鈕下方的下拉列表，選擇一種表格格式即可，如圖4-106所示。

自動套用表格格式以后，將出現【設計】選項卡。在該選項卡中可以修改表樣式、清除表樣式、添加表樣式等。任務4.3制作學生成績表【任務描述】

制作一個學生成績表，并對其中的數據進行計算，效果如圖4-107所示。

【學習目的】

(1)學習單元格的引用方法。

(2)掌握公式的表達方法。

(3)掌握常用函數的使用方法。

(4)了解圖表的制作。【任務要求】

(1)打開“學生成績表.xlsx”，計算出每個人的總成績、平均分和總評。

①總成績?=?C語言+文化基礎+專業(yè)英語+應用數學+應用文寫作 。

②平均分?=?總成績÷5。

③總評：平均分≥90，優(yōu)秀；平均分≥80，良好；平均分≥60，及格；60≥平均分≥0，不及格。

(2)對整體成績進行分析。

①計算每一科的平均分、最高分、最低分。

②計算參加考試學生的“總人數”以及各科的“不及格人數”。

(3)根據“學生成績表”制作圖表，并將其放置在Sheet2工作表中。圖表布局使用“布局5”，圖表標題為“學生成績圖表”，縱坐標軸標題為“分數”，如圖4-108所示?！救蝿諏崿F】

(1)啟動Excel2010，按下Ctrl+O鍵，打開“素材”文件夾中的“學生成績表.xlsx”，如圖4-109所示。

(2)選擇I3單元格，在I3單元格中定位光標，輸入公式“=D3+E3+F3+G3+H3”，按下回車鍵顯示求和結果。

(3)拖動I3單元格的填充柄至I13單元格處，則自動填充公式并顯示計算結果。

(4)選擇J3單元格，將光標定位在J3單元格中，輸入公式“=I3/5”，按下回車鍵顯示求平均分結果。

(5)拖動J3單元格的填充柄至J13單元格處，則自動填充公式并顯示計算結果。

(6)選擇K3單元格，將光標定位在K3單元格中，輸入公式“=IF(J3>=90,"優(yōu)秀",IF(J3>=80,"良好",IF(J3>=60,"及格","不及格")))”，按下回車鍵，則單元格中將直接顯示總評結果，而編輯框中顯示的是公式，如圖4-110所示。

(7)拖動K3單元格的填充柄至K13單元格處，則自動填充公式并顯示計算結果。

(8)將光標定位在D15單元格中，在【公式】選項卡的“函數庫”組中單擊【】按鈕下方的三角箭頭，在打開的列表中選擇【平均】選項，則對其上方的數據區(qū)域進行求平均，按下回車鍵得到C語言的平均分。

(9)拖動D15單元格的填充柄至H15單元格處，則自動填充函數并顯示各學科的平均分。(10)選擇單元格區(qū)域D15:H15，在【開始】選項卡的“數字”組中單擊【】按鈕右側的三角箭頭，在打開的下拉列表中選擇【數字】選項，所選單元格區(qū)域的數值保留兩位小數顯示。(11)將光標定位在D16單元格中，在“函數庫”組中單擊【】按鈕下方的三角箭頭，在打開的列表中選擇【最大值】選項，然后選擇D3:D13單元格區(qū)域，按下回車鍵則顯示C語言的最高分。(12)拖動D16單元格的填充柄至H16單元格處，則自動填充函數并顯示各學科的最高分。(13)光標定位在D17單元格中，在“函數庫”組中單擊【】按鈕下方的三角箭頭，在打開的列表中選擇【最小值】選項，然后選擇D3:D13單元格區(qū)域，按下回車鍵則顯示C語言的最低分。(14)拖動D17單元格的填充柄至H17單元格處，則自動填充函數并顯示各學科的最低分。(15)將光標定位在D18單元格中，輸入公式“=COUNTIF(D3:D13,"<60")”，按下回車鍵，則單元格中將直接顯示C語言的不及格人數。(16)拖動D18單元格的填充柄至H18單元格處，則自動填充函數并顯示各學科的不及格人數，如圖4-111所示。(17)將光標定位在D19單元格中，在“函數庫”組中單擊【】按鈕下方的三角箭頭，在打開的列表中選擇【計數】選項，然后選擇D3:D13單元格區(qū)域，按下回車鍵則顯示學生的總人數。(18)在成績表中同時選擇B2:H13區(qū)域，在【插入】選項卡的“圖表”組中單擊【】按鈕，在打開的下拉列表中選擇第1種二維柱形圖，則生成了一個圖表，如圖4-112所示。(19)在【設計】選項卡的“圖表布局”組中單擊右下角的【】按鈕，在打開的下拉列表中選擇“布局5”。(20)在圖表中輸入圖表標題“學生成績圖表”，再輸入縱坐標標題為“分數”，然后將圖表適當放大。(21)在【設計】選項卡的“位置”組中單擊【移動圖表】按鈕，則彈出【移動圖表】對話框，在【對象位于】下拉列表中選擇“Sheet2”，如圖4-113所示。然后單擊【確定】按鈕，將圖表移動到Sheet2工作表中。(22)按下Ctrl+S鍵保存文件?！鞠嚓P知識】Excel2010為用戶提供了強大的數據處理能力，既可以對數據進行運算，也可以進行數據的統(tǒng)計與分析。其中，最常用的數據運算方式是公式和函數。公式可以更加方便地計算保存在工作表中的數據；函數功能最強大，用戶可以使用函數快速地解決各種各樣的數據運算問題。4.3.1單元格引用單元格引用通常是指單元格地址引用，即用單元格地址引用單元格到公式中。例如，顯示在B列8行交叉處的單元格，其引用形式為“B8”，與單元格地址的名稱完全相同。在Excel2010中使用函數或公式需要借助單元格引用完成數據運算。1.相對地址引用除非特別指明，Excel2010一般使用相對地址來引用單元格位置。所謂相對地址引用，是指將一個含有單元格地址的公式復制到新的位置時，公式中的單元格地址會自動隨之發(fā)生變化，即單元格地址的行號和列標都會隨著橫向或縱向的移動而呈等差序列增減。下面以“學生成績表”為例，利用相對地址引用的方式進行填充，操作步驟如下：(1)選擇要輸入公式的單元格(這里選擇I3)，然后輸入“=D3+E3+F3+G3+H3”。(2)將光標指向單元格(I3)右下角的填充柄上，當光標變?yōu)楹谑中螤顣r按住左鍵沿I列垂直向下拖動鼠標復制公式，這里拖動到I13單元格即可。(3)釋放鼠標，則復制了公式后的單元格中自動填充了計算結果，如圖4-114所示，并且單元格地址的行號隨著縱向移動而呈等差序列遞增。下面，我們看一看復制公式后的單元格內到底發(fā)生了什么變化。首先在【文件】選項卡中單擊【選項】命令，在打開的【Excel選項】對話框中選擇【在單元格中顯示公式而非其計算結果】選項，如圖4-115所示。確認設置后，則I列中會顯示出公式，如圖4-116所示。從圖中可以看出，公式中引用的單元格地址相應地發(fā)生了變化，這就是相對地址引用的特點。2.絕對地址引用所謂絕對地址引用，是指將公式復制到新的位置時那些被引用的單元格地址固定不變。書寫絕對地址引用的時候需要在單元格的行號和列標前面加一個“$”符號，如$B$8，表示該引用是絕對地址引用。下面以“銷售明細表”為例，計算出各月交納的稅金，以此比較相對地址引用與絕對地址引用的不同。具體操作步驟如下：(1)在B8單元格中輸入公式“=B6*B7”，得到結果“8826.96”。(2)將光標指向B8單元格右下角的填充柄，按住鼠標左鍵向右拖動鼠標復制公式到D8單元格，這時將得到錯誤的結果，如圖4-117所示。出現上述錯誤的原因是公式中使用了相對地址引用，將公式復制到D8單元格時，原公式就變成“=D6*D7”，而D7單元格內容為零，因此D8單元格內容為“0”。(3)修改B8單元格公式為絕對地址引用，即“=B6*$B$7”，得到相同結果“8826.96”。(4)將光標指向B8單元格右下角的填充柄，按住鼠標左鍵向右拖動鼠標復制公式到D8單元格，這時就得到了正確的結果，如圖4-118所示。由于修改后的公式中將B7單元格改成了絕對地址引用，所以當公式復制到D8單元格時，原公式中的“$B$7”不發(fā)生改變，因此D8單元格內容為“=D6*$B$7”。3.混合引用與三維引用混合引用是指同一個公式中既有相對地址引用，又有絕對地址引用。如果公式所在單元格的位置發(fā)生變化，相對地址引用將改變，絕對地址引用則不變。例如，公式“=B$6*B$7”中對單元格的引用就是混合引用，復制公式后，列標改變是因為對列標使用了相對地址引用，行號不發(fā)生變化是因為對行號使用了絕對地址引用，這種引用就是混合引用，如圖4-119所示。三維引用是指對跨越工作簿中兩個或多個工作表區(qū)域的引用。三維引用的格式為“工作表名稱!單元格地址”。例如，公式“=Sheet1!B3+Sheet2!B1”表示計算Sheet1工作表中的B3單元格與Sheet2工作表中的B1單元格內容的和，如圖4-120所示。4.3.2公式在Excel2010中，公式是一個很重要的角色，可以說沒有公式的工作表只能算作是一個“表格”，不能稱其為“電子表格”。Excel2010中的公式是由數字、運算符、括號、單元格引用位置、工作表函數等組成的有效字符串，使用公式可以計算工作表中的各種數據，而且計算結果準確無誤，使我們從繁雜無序的數字中解放出來，大大提高了工作效率。1.公式的輸入輸入公式的操作類似于輸入字符型數據，不同的是輸入公式時要以等號“=”開頭。等號指明后面的字符串是一個公式，而等號后才是公式的表達式。通過以下兩種方式可以輸入公式：(1)直接在單元格中輸入公式。(2)在編輯欄右側的編輯框中輸入公式。下面以“成績表”為例，介紹在單元格中輸入公式的基本操作步驟：(1)單擊要輸入公式的單元格，使其成為活動單元格，如F2。(2)在活動單元格中輸入“=”，然后輸入公式內容，如“=B2+C2+D2+E2”。(3)輸入完公式后按下回車鍵，或者單擊編輯欄中的【】按鈕，這時單元格中將直接顯示計算結果，而編輯框中顯示的是公式，如圖4-121所示。(4)如果要修改公式，可以選擇含有公式的單元格，在編輯欄中進行修改，如圖4-122所示。2.關于公式的計算1)計算結果的顯示在Excel2010中使用公式時，單元格中將直接顯示運算結果，一旦公式使用有誤，就不會顯示正確的結果，而是在單元格中給出錯誤提示。錯誤提示和產生的原因如下：·#DIV/0!表示除數為零。·#N/A表示引用了當前不能使用的值?！?NAME?表示使用了Excel不能識別的名字。·#NULL!表示指定了無效的“空”內容?！?NUM!表示使用數字的方式不正確?！?REF!表示引用了無效的單元格?！?VALUE!表示使用了不正確的參數或運算對象?！?####表示運算結果太長，應增加列寬。2)運算符與運算順序Excel2010中有多種運算符，主要分為4種類型，分別是引用運算符、算術運算符、文本運算符和比較運算符。如果一個公式中同時使用了多個運算符，Excel2010會按照“括號優(yōu)先、從左到右、根據運算符的特定順序進行計算”的原則進行計算。表4-2所示是運算順序。4.3.3函數函數就是預先編寫的公式，它可以對一個或多個單元格數據進行運算，并返回一個或多個值。函數可以簡化和縮短工作表中的公式。例如，對于公式“=A1+A2+A3+A4+A5”，使用函數表示則為“=SUM(A1:A5)”，顯示既簡短又直觀。1.插入函數函數的結構為=函數名稱(參數，參數，…)其中，參數可以是數字、文本、邏輯值、數組、錯誤值或單元格引用，也可以是常量、公式或其他函數。如果函數以公式的形式出現，需要在函數名稱前面輸入等號。(1)手工輸入函數。在工作表中使用函數計算數據時，如果對所使用的函數和該函數的參數類型非常熟悉，可以直接在單元格中輸入函數。要注意，函數是嵌套在公式中使用的，參數使用括號“()”括起來，如“=SUM(B3:F6)”。(2)使用分類向導。Excel2010將函數分為若干類型，分布在功能區(qū)【公式】選項卡的“函數庫”組中，使用它可以快速地插入函數，前提需要明確所使用的函數屬于哪一類。下面以邏輯函數IF為例介紹插入函數的方法。(1)選擇要插入函數的單元格，這里選擇G2。(2)在【公式】選項卡的“函數庫”組中打開【】按鈕下方的下拉列表，選擇【IF】函數，如圖4-123所示。(3)在打開的【函數參數】對話框中設置函數的參數，如圖4-124所示。(4)單擊【】按鈕，完成插入函數操作。(5)參照前面的操作方法，利用填充柄將G2單元格中的函數向下復制到G5單元格，判斷結果如圖4-125所示。插入函數時，也可以在【公式】選項卡的“函數庫”組中單擊【插入函數】按鈕【】，如圖4-126所示。此時，將打開【插入函數】對話框，如圖4-127所示。在該對話框中選擇要使用的函數，這里的函數與功能區(qū)中的分類函數完全一樣。2.常用函數Excel2010提供了大量的函數，如常用函數、統(tǒng)計函數、三角函數、時間函數等。在日常表格計算中，最常用的是求和函數“SUM(參數1,參數2,...)”。因此，Excel2010將其設定成一個自動求和按鈕【】，存放在【公式】選項卡的“函數庫”組中。下面利用自動求和函數來計算“成績表”中的總分，具體操作步驟如下：(1)選擇要存放總分的單元格，如G3單元格。(2)在【公式】選項卡的“函數庫”組中單擊【】按鈕，則對其左側的數據區(qū)域進行自動求和，并在G3單元格中產生了一個函數公式，如圖4-128所示。(3)按下回車鍵即可得到求和結果。(4)利用填充柄將G3單元格中的函數向下復制到G14單元格，如圖4-129所示。(3)條件求和函數。SUMIF(range,criteria,sum_range)功能：根據range區(qū)域中的指定條件criteria，對sum_range相應的單元格求和。注意三個參數的意義。說明：range——用于條件判斷的區(qū)域。criteria——判定條件，可以是數字、表達式或字符形式。sum_range——進行求和運算的實際區(qū)域。4.邏輯函數邏輯函數如表4-4所示。IF(logical_test,v1,v2)功能：logical_test為“TRUE”時存放v1值；當邏輯表達式logical_test為“FALSE”時存放v2值。注意：IF函數可嵌套，以完成復雜的條件測試，如圖4-131所示。5.字符函數字符函數如表4-5所示。(1)?LEFT(text,number)。功能：返回字符串text左邊的number個字符。(2)?RIGHT(text,number)。功能：返回字符串text右邊的number個字符。(3)?MID(text,number1,number2)。功能：返回字符串text中，從第number1個字符開始的number2個字符。(4)?LEN(text)。功能：返回字符串text的字符個數。(5)?LOOKUP(value,vector1,vector2)。功能：在VECTOR1中查找并返回數值value對應的vector2的值。(數值value必須是升序排列)(6)?VLOOKUP(value,array,num,range)。功能：VLOOKUP函數是Excel2010中的一個縱向查找函數，VLOOKUP是按列查找，最終返回該列所需查詢列序所對應的值。與該函數對應的HLOOKUP是按行查找的。value—查找的值