任務(wù)2.1 Excel 2016基本操作-制作形成性考核表

辦公自動(dòng)化實(shí)用教程（第三版）經(jīng)濟(jì)科學(xué)出版社任務(wù)2.1Excel2016基本操作——制作形成性考核表

《辦公自動(dòng)化實(shí)用教程》（第三版）主編：石朝暉劉文娟學(xué)習(xí)目標(biāo)認(rèn)識(shí)Excel2016的工作界面工作簿和工作表的基本操作工作表中單元格數(shù)據(jù)的編輯和格式設(shè)置VLOOKUP、COUNTIF、SUM、IF等函數(shù)的應(yīng)用任務(wù)引入在日常辦公中，經(jīng)常要制作一些表格，并對(duì)其中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、計(jì)算、分析，Excel提供了強(qiáng)大的功能足以滿足用戶的需要。某高職院校的劉老師要為自己的任教班級(jí)制作一份“形成性考核成績(jī)表”，如何利用Excel2016快速地實(shí)現(xiàn)數(shù)據(jù)錄入與相關(guān)的數(shù)據(jù)處理呢？任務(wù)分析根據(jù)學(xué)校關(guān)于形成性考核的相關(guān)規(guī)定，“形成性考核成績(jī)表”的組成部分一般要求包含日常教學(xué)中的學(xué)生考勤、課后作業(yè)、平時(shí)測(cè)試等部分。劉老師準(zhǔn)備設(shè)計(jì)多個(gè)獨(dú)立的工作表，將上述成績(jī)數(shù)據(jù)分別放置，最終生成學(xué)期成績(jī)匯總表，這樣做的目的是為了使得多個(gè)工作表之間存在數(shù)據(jù)鏈接關(guān)系，方便信息傳遞。由于表格設(shè)計(jì)的需求，需要進(jìn)行如下的編輯、美化工作：工作表的創(chuàng)建和基本操作在單元格中輸入各種類型數(shù)據(jù)設(shè)置單元格格式設(shè)置表格的邊框和底紋應(yīng)用公式和函數(shù)進(jìn)行計(jì)算任務(wù)效果圖：任務(wù)實(shí)施一、認(rèn)識(shí)Excel2016工作界面1.快速訪問(wèn)工具欄Excel2016文檔窗口中的“快速訪問(wèn)工具欄”用于放置命令按鈕，使用戶快速啟動(dòng)經(jīng)常使用的命令。默認(rèn)情況下，“快速訪問(wèn)工具欄”中只有數(shù)量較少的命令，用戶可以根據(jù)需要添加多個(gè)自定義命令。2.功能區(qū)MicrosoftExcel2016延續(xù)了功能區(qū)和功能組的外觀風(fēng)格，每個(gè)功能區(qū)根據(jù)功能的不同提供了各種不同的命令，并將相關(guān)命令進(jìn)行了分組?！伴_(kāi)始”：此功能區(qū)包含基本的剪貼板命令、格式命令、樣式命令、插入和刪除行或列的命令，以及各種工作表編輯命令?！安迦搿保捍斯δ軈^(qū)提供了可在工作表中插入需要的任何內(nèi)容——表、圖、圖表、符號(hào)等?！绊?yè)面布局”：此功能區(qū)包含的命令可影響工作表的整體外觀，包括一些與打印有關(guān)的設(shè)置?！肮健保菏褂么斯δ軈^(qū)可插入函數(shù)公式、定義名稱、訪問(wèn)公式審核工具，以及控制Excel執(zhí)行計(jì)算的方式。“數(shù)據(jù)”：此功能區(qū)提供了Excel中與數(shù)據(jù)相關(guān)的命令。“審閱”：此功能區(qū)包含的工具用于檢查拼寫(xiě)、翻譯單詞、添加注釋，以及保護(hù)工作表?！耙晥D”：包含的命令用于控制有關(guān)工作表的顯示的各個(gè)方面。此功能區(qū)上的一些命令也可以在狀態(tài)欄中獲取。3.編輯欄編輯欄是Excel2016中比較重要的一個(gè)輸入欄，向單元格內(nèi)輸入公式或內(nèi)容都要經(jīng)過(guò)這里，如圖2.3所示。編輯欄的顯示與隱藏方法：切換到“視圖”功能面板，在“顯示”組中勾選“編輯欄”復(fù)選框就顯示編輯欄，反之，編輯欄就隱藏。擴(kuò)展編輯欄方法：在單元格中輸入多行文字，但是在編輯欄中僅顯示首行文字，單擊編輯欄末尾的下拉箭頭，可以擴(kuò)展編輯欄，擴(kuò)展之后，即可看到所有文字。4.工作表由Excel程序創(chuàng)建的文檔稱為工作簿，一個(gè)工作簿可以包含多個(gè)“頁(yè)面”，這些頁(yè)面稱為工作表，如圖2.4所示，每個(gè)工作表都是相對(duì)獨(dú)立的，可以存放不同的內(nèi)容。在實(shí)際應(yīng)用中，這種結(jié)構(gòu)形式非常好用，例如可以把多個(gè)相關(guān)的表格存放在同一工作簿的各個(gè)工作表中，即方便攜帶（只需攜帶一個(gè)文檔），又可以很方便地在表格之間相互調(diào)用數(shù)據(jù)。默認(rèn)情況下，Excel2016建立的工作簿僅包含“sheet1”（sheet1被稱為“工作表標(biāo)簽”）一個(gè)工作表，單擊該工作表標(biāo)簽右側(cè)的符號(hào)“⊕”，就可以根據(jù)自己的實(shí)際需要添加工作表。還可以對(duì)工作表進(jìn)行刪除、重命名、移動(dòng)或復(fù)制等操作。5.行號(hào)和列標(biāo)每個(gè)工作表都由行和列組成，每一行的行號(hào)由1、2、3……等數(shù)字表示；每一列的列號(hào)由A、B、C……等字母表示，如圖2.4所示。Z列之后依次是AA、AB、AC……依此類推。選擇整行和整列：?jiǎn)螕粜刑?hào)或列標(biāo)可選擇工作表的該行或該列。調(diào)整行高：把鼠標(biāo)放在某行號(hào)（例如：2）下邊的邊緣處，鼠標(biāo)變成雙向箭頭，拖動(dòng)鼠標(biāo)即可改變?cè)撔行懈?。調(diào)整列寬：把鼠標(biāo)放在某列標(biāo)（例如：B）右邊的邊緣處，鼠標(biāo)變成雙向箭頭，拖動(dòng)鼠標(biāo)即可改變?cè)摿辛袑挕６?、制作“形成性考核成?jī)表”表Excel工作表是承載信息數(shù)據(jù)的主體，這里，我們將“形成性考核成績(jī)表”的不同數(shù)據(jù)分別存儲(chǔ)在幾個(gè)工作表中，以便于數(shù)據(jù)之間的傳遞和鏈接。1.制作“考勤”表（1）創(chuàng)建新工作簿依次單擊“開(kāi)始”菜單“所有程序”程序列表中的“MicrosoftOffice2016”/“Excel2016”命令選項(xiàng)，運(yùn)行Excel2016程序，新建一個(gè)“工作簿1”文檔，將其命名為“形成性考核成績(jī)表”。（2）修改工作表標(biāo)簽右單擊sheet1工作表標(biāo)簽，選擇其中的“重命名”命令，將其命名為“考勤”。再次右單擊“考勤”工作表標(biāo)簽，選擇“工作表標(biāo)簽顏色”中的“標(biāo)準(zhǔn)紅色”。（3）制作表頭在“考勤”工作表的A1單元格中輸入“2019-2020學(xué)年第一學(xué)期2019級(jí)會(huì)計(jì)1班考勤記錄表”。（4）制作標(biāo)題行步驟一自A5單元格開(kāi)始向右在連續(xù)單元格區(qū)域中依次輸入“序號(hào)”、“學(xué)號(hào)”、“姓名”和“性別”；然后在E4單元格中輸入“1周”，在E5單元格中輸入“1次”，在F5單元格中輸入“2次”；選中E4:E5單元格，單擊“開(kāi)始”功能區(qū)“對(duì)齊方式”組中的“合并后居中”按鈕。步驟二自動(dòng)填充序列。選擇“1周”單元格區(qū)域，然后用鼠標(biāo)左鍵按住該區(qū)域右下角的小方塊（即：填充句柄）一直向右側(cè)拖動(dòng)，會(huì)出現(xiàn)一個(gè)“1周、2周、3周、……”的遞增序列，看到提示“20周”時(shí)松開(kāi)釋放鼠標(biāo)，即可得到一個(gè)填充序列（按照本學(xué)期20教學(xué)周計(jì)算）；選擇E5：F5單元格區(qū)域（即：“1次”和“2次”），按住Ctrl鍵，再利用上述操作方法拖動(dòng)填充句柄向右復(fù)制直至“20周”結(jié)束，即可得到每周重復(fù)的一個(gè)填充序列。步驟三在標(biāo)題行的最右端（即：?jiǎn)卧駞^(qū)域）依次輸入“正?！?、“遲到”、“早退”、“請(qǐng)假”、“曠課”和“小計(jì)”項(xiàng)目。（5）輸入學(xué)生名單步驟一錄入學(xué)生信息。分別在標(biāo)題行之下依次輸入本班的學(xué)生“學(xué)號(hào)”、“姓名”和“性別”。步驟二自動(dòng)填充“序號(hào)”。分別在A6、A7單元格中輸入“1”和“2”，然后選中A6:A7單元格區(qū)域，雙擊其填充句柄即可根據(jù)學(xué)生信息數(shù)量，自動(dòng)生成一個(gè)“1、2、3、……”的序號(hào)。（6）設(shè)置單元格格式步驟一設(shè)置字體格式。全選整個(gè)表格區(qū)域（包括表頭），在“開(kāi)始”功能區(qū)“字體”組中選擇字體格式為“微軟雅黑10磅”。選擇A1：AX1單元格區(qū)域，設(shè)置表頭格式為“合并后居中16磅”。步驟二設(shè)置對(duì)齊方式。分別選擇A4:A5單元格區(qū)域（“序號(hào)”），在“開(kāi)始”功能區(qū)“對(duì)齊方式”組中分別設(shè)置“合并后居中”和“自動(dòng)換行”。按照上述方法，分別為“性別”、“正?！?、“遲到”、“早退”、“請(qǐng)假”、“曠課”和“小計(jì)”列進(jìn)行相應(yīng)的“合并后居中”和“自動(dòng)換行”設(shè)置。選擇A:AX列，設(shè)置對(duì)齊方式為“居中”。步驟三調(diào)整行高和列寬。選中第1行和第2行行標(biāo)之間的分隔線，用鼠標(biāo)拖拽適當(dāng)增大表頭行行高；選擇A列（“序號(hào)”），在“開(kāi)始”功能區(qū)“單元格格式”組中單擊“格式”按鈕，從下拉列表中選擇“列寬…”命令，在彈出的對(duì)話框中設(shè)置列寬值為“2.7”。重復(fù)上述操作，分別為除了“學(xué)號(hào)”、“姓名”兩列之外的其他數(shù)據(jù)列設(shè)置列寬為“2.7”。（6）設(shè)置單元格格式步驟四選擇“序號(hào)”列和“學(xué)號(hào)”列，在“開(kāi)始”功能區(qū)“數(shù)字”組中打開(kāi)“數(shù)字格式”下拉列表，從中選擇“文本”格式。步驟五添加邊框線和底紋。選擇A4:AX數(shù)據(jù)區(qū)域，在“開(kāi)始”功能區(qū)“字體”組中單擊“邊框”下拉按鈕，從下拉列表中選擇“所有框線”選項(xiàng)命令為其添加邊框線。選擇標(biāo)題行（即：A4：AX5單元格區(qū)域），在“開(kāi)始”功能區(qū)“字體”組中單擊“填充顏色”下拉按鈕，從下拉列表中選擇一種適宜的主題顏色為其添加底紋。重復(fù)上述操作，為最右側(cè)的數(shù)據(jù)統(tǒng)計(jì)區(qū)域（即：AS4：AX47單元格區(qū)域）也填充相同的底紋顏色。在表頭和標(biāo)題行之間合適位置，添加“制表日期”；在表格下方合適位置，添加注釋文本：正常-0、遲到-1、早退-2、請(qǐng)假-3、曠課-4在表格中將使用這些簡(jiǎn)單的數(shù)字作為考勤標(biāo)記。（7）數(shù)據(jù)驗(yàn)證為了防止在“考勤”表中填充無(wú)效數(shù)字，造成統(tǒng)計(jì)信息不準(zhǔn)確，需要為從“1周”到“20周”之間的數(shù)據(jù)區(qū)域設(shè)置“數(shù)據(jù)驗(yàn)證”——僅允許輸入“0,1,2,3,4”數(shù)字，操作方法如下：選擇E6：AR47單元格區(qū)域，單擊“數(shù)據(jù)”功能區(qū)“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗(yàn)證”命令，彈出“數(shù)據(jù)驗(yàn)證”對(duì)話框，從“允許”框中選擇“整數(shù)”，數(shù)據(jù)選擇“介于”，最小值為“0”，最大值為“4”，最后單擊“確定”按鈕。（8）突出顯示“曠課”數(shù)據(jù)為了在考勤記錄中重點(diǎn)突出所有“曠課”的單元格，可以為這些單元格設(shè)置條件格式規(guī)則——當(dāng)這些單元格填充了“4”時(shí)，自動(dòng)顯示為一種特殊的格式（例如：淺紅色填充深紅色文本），操作方法如下：步驟一選擇E6：AR47單元格區(qū)域，單擊“開(kāi)始”功能區(qū)“樣式”組中的“條件格式”命令，從其下拉列表中選擇“突出顯示單元格規(guī)則”/“等于…”命令。步驟二在彈出的“等于”對(duì)話框中，在“為等于以下值的單元格設(shè)置格式：”框中輸入“4”，在“設(shè)置”中選擇“淺紅色填充深紅色文本”，最后單擊“確定”按鈕。（9）考勤信息統(tǒng)計(jì)考勤記錄的數(shù)據(jù)最終需要進(jìn)行匯總統(tǒng)計(jì)，利用單條件計(jì)數(shù)函數(shù)COUNTIF可以完成統(tǒng)計(jì)工作。在AS6單元格中構(gòu)建如下函數(shù)公式：=COUNTIF(E6:AR6,0)按下Enter鍵，得到第一位學(xué)生本學(xué)期考勤中所有“正常”記錄的小計(jì)。利用填充句柄將AS6單元格中的公式向下復(fù)制填充直至最后一行，得到全部學(xué)生的“正?！庇涗浶∮?jì)。按照上述操作方法，分別統(tǒng)計(jì)“遲到”、“早退”、“請(qǐng)假”和“曠課”匯總。單元格位置

統(tǒng)計(jì)項(xiàng)目

函數(shù)公式AS6 正常 =COUNTIF(E6:AR6,0)AT6 遲到 =COUNTIF(E6:AR6,1)AU6 早退 =COUNTIF(E6:AR6,2)AV6 請(qǐng)假 =COUNTIF(E6:AR6,3)AW6 曠課 =COUNTIF(E6:AR6,4)【函數(shù)解讀】：COUNTIF函數(shù)的語(yǔ)法格式為計(jì)算某個(gè)區(qū)域中滿足條件的單元格數(shù)目，在統(tǒng)計(jì)“正?！钡墓街?，它的第一參數(shù)Range是需要統(tǒng)計(jì)的某位學(xué)生20周考勤記錄區(qū)域，第二參數(shù)Criteria是需要滿足的條件“0”。（10）小計(jì)“形成性考核成績(jī)表”按照一學(xué)期20教學(xué)周進(jìn)行設(shè)計(jì)，在總計(jì)100分中，按照“考勤”20分、“作業(yè)”20分、“測(cè)驗(yàn)”60分分配分值。因此，“考勤”表中的“小計(jì)”分?jǐn)?shù)按照如下方法進(jìn)行統(tǒng)計(jì)計(jì)算：=正常*1+遲到*0.3+早退*0.3+請(qǐng)假*0.1+曠課*0在AX6單元格中輸入公式：=AS6*1+AT6*0.3+AU6*0.3+AV6*0.1+AW6*0按下Enter鍵，得到第一位學(xué)生本學(xué)期考勤“小計(jì)”。利用填充句柄將AX6單元格中的公式向下復(fù)制填充直至最后一行，得到全部學(xué)生的考勤“小計(jì)”?！拘√崾尽浚喝∠W(wǎng)格線為了更清晰地顯示表格中的文本和數(shù)字，可以通過(guò)取消“視圖”功能區(qū)中“網(wǎng)格線”復(fù)選框的方法來(lái)實(shí)現(xiàn)，這樣整個(gè)工作表就會(huì)呈現(xiàn)白紙狀態(tài)。2.制作“作業(yè)”表“作業(yè)”表的結(jié)構(gòu)和核算方法與“考勤”表基本相同，因此可以將“考勤”表復(fù)制一份，然后做相應(yīng)修改就可以了。（1）復(fù)制工作表右單擊“考勤”工作表標(biāo)簽，選擇“移動(dòng)或復(fù)制…”命令，在彈出的“移動(dòng)或復(fù)制工作表”對(duì)話框中，選擇放置工作表的位置，勾選“建立副本”復(fù)選框。將得到的“考勤（2）”副本工作表重命名為“作業(yè)”，將工作表標(biāo)簽顏色設(shè)置為“標(biāo)準(zhǔn)橙色”。（2）修改標(biāo)題將表頭修改為“2019-2020學(xué)年第一學(xué)期2019級(jí)會(huì)計(jì)1班作業(yè)記錄表”；將標(biāo)題行右端的五個(gè)統(tǒng)計(jì)項(xiàng)目分別修改為“優(yōu)秀”、“良好”、“一般”、“較差”較差二和“未交”，并將表格下方的注釋文本進(jìn)行修改：優(yōu)秀-0：自主完成，知識(shí)點(diǎn)完整，效果好良好-1：自主完成，知識(shí)點(diǎn)有遺漏一般-2：有抄襲嫌疑較差-3：基本沒(méi)有完成未交-4：未提交作業(yè)保留原有的函數(shù)公式（計(jì)算方法與“考勤”表相同）。3.制作“測(cè)驗(yàn)”表（1）新建工作表單擊“新工作表”按鈕，在“作業(yè)”工作表的的右側(cè)插入一個(gè)新工作表，將其重命名為“測(cè)驗(yàn)”，設(shè)置工作表標(biāo)簽顏色為“標(biāo)準(zhǔn)綠色”。（2）編輯表格數(shù)據(jù)步驟一建立表頭。在A1單元格內(nèi)輸入表頭文本“2019-2020學(xué)年第一學(xué)期2019級(jí)會(huì)計(jì)1班測(cè)驗(yàn)記錄表”。步驟二建立標(biāo)題行。自A3單元格開(kāi)始向右輸入標(biāo)題行文本，然后復(fù)制“考勤”表中的相應(yīng)內(nèi)容粘貼到“序號(hào)”、“學(xué)號(hào)”、“姓名”和“性別”數(shù)據(jù)區(qū)域中。步驟三設(shè)置單元格格式。參照“考勤”表中的操作方法，為“測(cè)驗(yàn)”表進(jìn)行設(shè)置字體格式、對(duì)齊方式、合并單元格、調(diào)整行高和列寬、添加邊框線和底紋等操作。步驟四修改注釋文本。將數(shù)據(jù)區(qū)域下方原有的注釋文本修改為：每次測(cè)驗(yàn)按20分計(jì)，共計(jì)60分。（3）計(jì)算“小計(jì)”在H4單元格中輸入如下公式：=SUM(E4:G4)向下復(fù)制該公式，完成全部“小計(jì)”數(shù)據(jù)列的計(jì)算。4.制作“總評(píng)”表（1）新建工作表新建一個(gè)工作表，命名為“總評(píng)”，設(shè)置工作表標(biāo)簽顏色為“標(biāo)準(zhǔn)藍(lán)色”。（2）編輯數(shù)據(jù)參照“測(cè)驗(yàn)”工作表的制作方法，編輯“總評(píng)”工作表中的相關(guān)內(nèi)容，并為其設(shè)置適宜的單元格格式，在數(shù)據(jù)區(qū)域下方編輯注釋文本：考勤：滿分20分作業(yè)：滿分20分測(cè)驗(yàn)：滿分60分小計(jì)：=考勤+作業(yè)+測(cè)驗(yàn)（3）統(tǒng)計(jì)各項(xiàng)記錄在“總評(píng)”表中，需要將前面的三個(gè)表格中“小計(jì)”數(shù)據(jù)列獲取到相應(yīng)位置。在Excel中，使用查詢函數(shù)VLOOKUP可以很方便地完成表格之間數(shù)據(jù)的傳遞。步驟一統(tǒng)計(jì)“考勤”成績(jī)。在E4單元格內(nèi)構(gòu)建如下函數(shù)公式：=VLOOKUP(B4,考勤!B6:AX47,49,0)按下回車(chē)鍵，得到結(jié)果。將公式向下復(fù)制填充到最后一行，完成“考勤”數(shù)據(jù)的統(tǒng)計(jì)。步驟二統(tǒng)計(jì)“作業(yè)”成績(jī)。在F4單元格內(nèi)構(gòu)建如下函數(shù)公式：=VLOOKUP(B4,作業(yè)!B6:AX47,49,0)按下回車(chē)鍵，得到結(jié)果。將公式向下復(fù)制填充到最后一行，完成“作業(yè)”數(shù)據(jù)的統(tǒng)計(jì)。步驟三統(tǒng)計(jì)“測(cè)驗(yàn)”成績(jī)。在G4單元格內(nèi)構(gòu)建如下函數(shù)公式：=VLOOKUP(B4,測(cè)驗(yàn)!B4:H45,7,0)按下回車(chē)鍵，得到結(jié)果。將公式向下復(fù)制填充到最后一行，完成“測(cè)驗(yàn)”數(shù)據(jù)的統(tǒng)計(jì)?！竞瘮?shù)解讀】：VLOOKUP函數(shù)在統(tǒng)計(jì)“考勤”列時(shí)，VLOOKUP函數(shù)的語(yǔ)法格式為：在“考勤”表中“學(xué)號(hào)”列至“小計(jì)”列的數(shù)據(jù)區(qū)域中，查詢與某個(gè)學(xué)生“學(xué)號(hào)”相對(duì)應(yīng)的“小計(jì)”數(shù)值。其中各項(xiàng)參數(shù)的意義為：第一參數(shù)Lookup_value：要查詢的值（即：“學(xué)號(hào)”）；第二參數(shù)Table_array：查詢的源數(shù)據(jù)區(qū)域（即：“考勤”表中從“學(xué)號(hào)”列至“小計(jì)”列），該區(qū)域必須從第一參數(shù)的值（即：“學(xué)號(hào)”）開(kāi)始；第三參數(shù)Col_index_num：在查詢的源數(shù)據(jù)區(qū)域中“小計(jì)”所在列序號(hào)（注：必須從第一參數(shù)值開(kāi)始計(jì)數(shù)）；第四參數(shù)Range_lookup:精確匹配（即：0或者False）還是模糊匹配（缺省）。步驟四統(tǒng)計(jì)“小計(jì)”。在H4單元格中構(gòu)建如下函數(shù)公式：=SUM(E4:G4)按下回車(chē)鍵，得到結(jié)果。將公式向下復(fù)制填充到最后一行，完成“小計(jì)”數(shù)據(jù)的統(tǒng)計(jì)。（4）給定評(píng)語(yǔ)根據(jù)“小計(jì)”成績(jī)，按照如下分?jǐn)?shù)段給與學(xué)生“五級(jí)制”評(píng)語(yǔ)：大于或等于90分，為“優(yōu)秀”；大于或等于80分，為“良好”；大于或等于70分，為“中等”；大于或等于60分，為“及格”；60分以下，為“不及格”。