大學(xué)計(jì)算機(jī)基礎(chǔ)-基于計(jì)算思維（Windows 10+Office 2016）（第2版）課件 第5章 Excel基本應(yīng)用

大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）第5章Excel基本應(yīng)用5.1項(xiàng)目分析張老師需要記錄“大學(xué)計(jì)算機(jī)”課程的考勤情況及成績(jī)。為了便于記錄和計(jì)算成績(jī)，張老師給出了如下的要求：項(xiàng)目分析

（1）制作課堂考勤登記表及課程成績(jī)登記表的表格樣式。（2）快速編排學(xué)生學(xué)號(hào)。（3）為了防止誤輸入，對(duì)于某些信息（比如未出勤記錄應(yīng)該只可以輸入“遲到”、“請(qǐng)假”或者“曠課”），能夠限定其輸入內(nèi)容，在輸入不規(guī)范時(shí)，不允許輸入并給出提示信息。項(xiàng)目分析

（4）統(tǒng)計(jì)每個(gè)學(xué)生的出勤情況，計(jì)算考勤成績(jī)。（5）根據(jù)給定規(guī)則計(jì)算總評(píng)成績(jī)、課程績(jī)點(diǎn)、總評(píng)等級(jí)以及總評(píng)排名。（6）將總評(píng)前10名的名單用特別的顏色標(biāo)識(shí)出來(lái)。（7）對(duì)成績(jī)進(jìn)行統(tǒng)計(jì)。（8）用圖表展現(xiàn)成績(jī)統(tǒng)計(jì)結(jié)果。（9）制作學(xué)生成績(jī)通知單。效果圖

效果圖

效果圖

感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.2.1知識(shí)點(diǎn)解析第5章Excel基本應(yīng)用5.2制作課堂考勤登記表1.什么是Excel

利用它能夠方便的制作出各種電子表格，使用公式和函數(shù)對(duì)數(shù)據(jù)進(jìn)行復(fù)雜的運(yùn)算，用各種圖表來(lái)表示數(shù)據(jù)，利用超級(jí)鏈接功能，用戶可以快速打開網(wǎng)絡(luò)上的Excel文件，與其他用戶實(shí)現(xiàn)共享。Excel是Office工具中的電子表格軟件。Excel1.什么是Excel

每張工作表都是一個(gè)由若干列和行組成的二維表格，Excel2016最多可以有16384列（列標(biāo)題以A、B、……AA、AB、……XFD表示）和1048576行（行標(biāo)題以1、2、……1048576表示），每個(gè)列和行的交叉處所對(duì)應(yīng)的格子稱為單元格。每個(gè)單元格用其所在的列標(biāo)題和行標(biāo)題命名，稱為單元格地址。如工作表的第1列、第1行的單元格用A1表示，第5列第4行的單元格用E4表示。每個(gè)Excel文件也稱之為一個(gè)工作簿工作表單元格

2.Excel工作界面如圖所示，Excel2016的界面與Word2016很類似，由快速訪問(wèn)工具欄、標(biāo)題欄、功能區(qū)、列標(biāo)題、行標(biāo)題、編輯欄、編輯區(qū)、工作表標(biāo)簽和狀態(tài)欄組成。用于顯示當(dāng)前工作簿和程序名稱。單擊快捷圖標(biāo)按鈕，可以快速執(zhí)行相應(yīng)的操作。此外，也可以單擊【自定義快速訪問(wèn)工具欄】按鈕，自行添加或刪除快速訪問(wèn)工具欄中的圖標(biāo)按鈕。

快速訪問(wèn)工具欄2.Excel工作界面標(biāo)題欄由選項(xiàng)卡、組和按鈕組成，每個(gè)選項(xiàng)卡分為不同的組，組是由功能類似的按鈕組成。每單擊一個(gè)選項(xiàng)卡，在其下方就會(huì)出現(xiàn)和選項(xiàng)卡相關(guān)的組。功能區(qū)電子表格的編輯區(qū)域。用于顯示當(dāng)前單元格的數(shù)據(jù)和公式。它由名稱框（顯示當(dāng)前活動(dòng)單元格的名稱）、按鈕組和編輯欄組成。

編輯欄2.Excel工作界面編輯區(qū)用于工作表的視圖切換和縮放顯示比例。單擊，可以彈出【顯示比例】對(duì)話框。狀態(tài)欄用于顯示當(dāng)前工作簿中的工作表名稱，如Sheet1、Sheet2等。工作表標(biāo)簽

3.單元格地址與單元格選擇如工作表的第1列、第1行所對(duì)應(yīng)的單元格地址用A1表示（也稱之為A1單元格），第3列第4行的單元格地址用C4表示。當(dāng)選擇了某個(gè)單元格時(shí)，該單元格對(duì)應(yīng)的行標(biāo)題和列標(biāo)題會(huì)用突出顏色特別標(biāo)識(shí)出來(lái)，名稱框中顯示該選擇的單元格地址，編輯欄中顯示該選擇的單元格中的內(nèi)容，如圖所示。為了便于訪問(wèn)工作表中的單元格，每個(gè)單元格都用其所在的列標(biāo)題和行標(biāo)題來(lái)標(biāo)識(shí)，稱為單元格地址。要在單元格中輸入內(nèi)容，需要先選擇該單元格。默認(rèn)情況下，單元格中輸入的內(nèi)容以1行來(lái)顯示。

3.單元格地址與單元格選擇（1）如果要選中多個(gè)連續(xù)的單元格，可以先選中第1個(gè)單元格（如B2），按下鼠標(biāo)左鍵，拖動(dòng)鼠標(biāo)到最后1個(gè)需要被選擇的單元格（如D5），松開鼠標(biāo)?？梢钥吹?，由第1個(gè)和最后1個(gè)單元格的行、列標(biāo)題所構(gòu)成的矩形區(qū)域中的所有單元格（稱為單元格區(qū)域，標(biāo)記為B2:D5）都被選擇，在名稱框中顯示的是第1個(gè)被選擇的單元格的地址（B2）。而要選擇多個(gè)單元格，可以：（2）而要選擇非連續(xù)單元格區(qū)域（如B2，B4，C3），可以先選中第1個(gè)單元格B2，按下Ctrl鍵不松手，用鼠標(biāo)單擊B4和C3，松開Ctrl鍵，則B2、B4和C3這3個(gè)單元格都被選中了，在名稱框中顯示的是最第1個(gè)被選擇的單元格的地址（C3）。

3.單元格地址與單元格選擇而要選擇多個(gè)單元格，可以：（3）如果要選擇某一列或者某一行，將鼠標(biāo)單擊對(duì)應(yīng)的列標(biāo)題或行標(biāo)題即可。而要同時(shí)選擇多行或多列，其操作方式與連續(xù)單元格區(qū)域和非連續(xù)單元格區(qū)域的選擇方式類似。在使用Excel輸入數(shù)據(jù)的時(shí)候，經(jīng)常要輸入許多連續(xù)或不連續(xù)的數(shù)據(jù)，如星期一到星期日等，如果采用手工輸入的方法，是一件非常麻煩而且容易出錯(cuò)的事情

4.用填充柄快速填充數(shù)據(jù)而通過(guò)使用Excel中“填充柄”的方法即可輕松快速輸入許多連續(xù)或不連續(xù)的數(shù)據(jù)。

4.用填充柄快速填充數(shù)據(jù)如圖所示，可以在第1個(gè)單元格（A1）輸入“星期一”后，選擇A1單元格，移動(dòng)鼠標(biāo)至單元格右下角，在鼠標(biāo)變成黑色填充柄+的情況下，按下鼠標(biāo)左鍵，拖動(dòng)鼠標(biāo)到A7單元格，松開鼠標(biāo)，則在A2:A7單元格里自動(dòng)填充了“星期二”到“星期日”。如果填充數(shù)據(jù)非連續(xù)但又規(guī)律，則可以在第1個(gè)單元格（B1）輸入“第1天”，在第2個(gè)單元格（B2）輸入“第3天”后，選擇B1:B2單元格，移動(dòng)鼠標(biāo)，在鼠標(biāo)變成黑色填充柄+的情況下，按下鼠標(biāo)左鍵，拖動(dòng)鼠標(biāo)到B7單元格，松開鼠標(biāo)，則在B2:B7單元格里自動(dòng)填充了“第5天”到“第13天”。

5.設(shè)置單元格格式主要用于設(shè)置被選擇的單元格（單元格區(qū)域）的字體、字號(hào)、顏色、邊框等?！伴_始”選項(xiàng)卡主要用于設(shè)置單元格格式?！白煮w”組主要用于設(shè)置被選擇的單元格（或單元格區(qū)域）中的內(nèi)容相對(duì)于單元格的對(duì)齊方式、單元格內(nèi)容的多行顯示及合并居中（即將多個(gè)單元格合并成1個(gè)單元格）等?！皩?duì)齊方式”組主要用于設(shè)置被選擇的單元格（或單元格區(qū)域）的數(shù)字顯示格式。“單元格”組主要用于設(shè)置被選擇的單元格（或單元格區(qū)域）的行高和列寬等?！皵?shù)字”組感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.2.2任務(wù)實(shí)現(xiàn)第5章Excel基本應(yīng)用5.2制作課堂考勤登記表1.任務(wù)分析

（1）標(biāo)題為“仿宋”，16號(hào)字，藍(lán)色，加粗，位于A列至O列的中間。（2）學(xué)期及課程信息為“宋體”，10號(hào)字，藍(lán)色，加粗。課程信息為文本右對(duì)齊。（3）表格區(qū)域（A3:O40）為“宋體”，10號(hào)字，自動(dòng)顏色，單元格文本水平居中和垂直居中對(duì)齊。（4）表頭區(qū)域（A3:O4）加粗。（5）A3:A4、B3:B4以及C3:L3分別合并后居中，并設(shè)置為垂直居中。制作如圖所示的表格，要求：

（6）M3:M4、N3:N4、O3:O4分別合并后居中，，并設(shè)置為垂直居中及自動(dòng)換行。（7）表格外邊框?yàn)楹谏?xì)實(shí)線，內(nèi)邊框?yàn)楹谏摼€。（8）調(diào)整單元格為合適的行高和列寬。制作如圖所示的表格，要求：1.任務(wù)分析新建1個(gè)Excel工作簿，將其Sheet1工作表命名為“課堂考勤登記”，并保存到自己的工作目錄，文件名為“大學(xué)計(jì)算機(jī)成績(jī).xlsx”。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

1）新建并保存工作簿①啟動(dòng)MicrosoftExcel2016。②單擊“文件”選項(xiàng)卡/“新建”組，打開新建功能區(qū)。③選擇“空白工作簿”，然后單擊即可創(chuàng)建新文檔。2.實(shí)現(xiàn)過(guò)程

1）新建并保存工作簿④雙擊工作表名稱Sheet1，將工作表重命名為“課堂考勤成績(jī)”。⑤單擊快速訪問(wèn)欄“保存”按鈕，在彈出的“另存為”對(duì)話框中，選擇工作簿的保存位置，并輸入文件名“大學(xué)計(jì)算機(jī)成績(jī)”，單擊“保存”按鈕，將當(dāng)前工作簿保存為“大學(xué)計(jì)算機(jī)成績(jī).xlsx”，如圖所示。在“課堂考勤成績(jī)”工作表中輸入如圖所示的表格項(xiàng)信息。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

2）輸入表格項(xiàng)信息①在A1單元格輸入表格標(biāo)題“課堂考勤登記表”。②在其他表格項(xiàng)單元格輸入如表5-1所示的內(nèi)容。表5-1“課堂考勤登記”工作表表格項(xiàng)內(nèi)容單元格地址單元格內(nèi)容單元格地址單元格內(nèi)容A2學(xué)期：2013至2014第2學(xué)期O2課程：大學(xué)計(jì)算機(jī)基礎(chǔ)A3學(xué)號(hào)B3姓名C3出勤情況M3缺勤次數(shù)N3計(jì)算出勤成績(jī)O3實(shí)際出勤成績(jī)C4出勤1

2.實(shí)現(xiàn)過(guò)程

3）通過(guò)填充柄快速填充考勤序列選擇C4單元格，移動(dòng)鼠標(biāo)，在鼠標(biāo)變成黑色填充柄+的情況下，按下鼠標(biāo)左鍵，拖動(dòng)鼠標(biāo)到L4單元格，松開鼠標(biāo)，則在D4:L4單元格里自動(dòng)填充了“出勤2”到“出勤10”。將A1單元格的內(nèi)容設(shè)置“仿宋”，16號(hào)字，藍(lán)色，加粗，位于A列至O列的中間。學(xué)期及課程信息為“宋體”，10號(hào)字，藍(lán)色，加粗。課程信息為文本右對(duì)齊。表格區(qū)域（A3:O40）為“宋體”，10號(hào)字，自動(dòng)顏色，單元格文本水平居中和垂直居中對(duì)齊。表頭區(qū)域（A3:O4）加粗。A3:A4、B3:B4以及C3:L3分別合并后居中。M3:M4、N3:N4、O3:O4分別合并后居中，并分行顯示。2.實(shí)現(xiàn)過(guò)程

4）格式化單元格2.實(shí)現(xiàn)過(guò)程

4）格式化單元格操作步驟如下：①選擇A1單元格（這時(shí)名稱框顯示為“A1”）。②在“開始”選項(xiàng)卡/“字體”組中，將A1單元格設(shè)置為“仿宋”，“16號(hào)字”，藍(lán)色，字體加粗，如圖所示。2.實(shí)現(xiàn)過(guò)程

4）格式化單元格③選擇A1:O1單元格區(qū)域，單擊“開始”選項(xiàng)卡/“對(duì)齊方式”組/“合并后居中”按鈕，將A1：O1單元格區(qū)域合并為1個(gè)單元格，并將內(nèi)容居中顯示。④選擇非連續(xù)單元格A2和O2，設(shè)置為“宋體”，10號(hào)字，藍(lán)色，加粗。⑤選擇O2單元格，單擊“開始”選項(xiàng)卡/“對(duì)齊方式”組/“文本右對(duì)齊”按鈕，設(shè)置O2的對(duì)齊方式為“文本右對(duì)齊”。⑥選擇A3:O40單元格區(qū)域，設(shè)置為“宋體”，10號(hào)字，自動(dòng)顏色，單元格文本水平居中和垂直居中對(duì)齊。⑦選擇A3:O4單元格區(qū)域，設(shè)置為加粗。將表格的外邊框設(shè)置為細(xì)實(shí)線，內(nèi)邊框?yàn)榧?xì)虛線。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

5）設(shè)置表格邊框①選擇A3:O40單元格區(qū)域，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“設(shè)置單元格格式”，打開“設(shè)置單元格格式”對(duì)話框，選擇“邊框”選項(xiàng)卡，在“線條/樣式”中選擇“細(xì)實(shí)線”，單擊“外邊框”，將表格外邊框設(shè)置為細(xì)實(shí)線，如圖所示。②在“線條/樣式”中選擇“細(xì)虛線”，單擊“內(nèi)部”，將表格內(nèi)部邊框設(shè)置為細(xì)虛線。設(shè)置A列列寬為10.5，B列列寬為6.25，C到L列列寬為“自動(dòng)調(diào)整列寬”，M、N及O列列寬為顯示2個(gè)漢字寬度。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

6）設(shè)置行高和列寬①選擇A列，單擊“開始”選項(xiàng)卡/“單元格”組/“格式”按鈕，在彈出的菜單中選擇“列寬”，打開“列寬”對(duì)話框，設(shè)置為10.5。②選擇B列，將其列寬設(shè)置為6.25。③選擇C到L列，單擊“開始”選項(xiàng)卡/“單元格”組/“格式”按鈕，在彈出的菜單中選擇“自動(dòng)調(diào)整列寬”。2.實(shí)現(xiàn)過(guò)程

6）設(shè)置行高和列寬④將鼠標(biāo)定位在列標(biāo)題M和列標(biāo)題N中間，當(dāng)變成左右雙向箭頭時(shí)，按下鼠標(biāo)左鍵，移動(dòng)鼠標(biāo)，調(diào)整M列的列寬為顯示2個(gè)漢字。調(diào)整N列和O列的列寬為顯示2個(gè)漢字。⑤選擇1到40行，單擊“開始”選項(xiàng)卡/“單元格”組/“格式”按鈕，在彈出的菜單中選擇“自動(dòng)調(diào)整行高”。⑥將光標(biāo)定位在行標(biāo)題4和行標(biāo)題5中間，當(dāng)變成上下雙向箭頭時(shí)，按下鼠標(biāo)左鍵不松手，向下拖動(dòng)，改變第4行的行高，使M3:O3中的內(nèi)容分3行顯示。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.2.3總結(jié)與提高第5章Excel基本應(yīng)用5.2制作課堂考勤登記表1.根據(jù)模板生成Excel文檔

在Excel中，可以根據(jù)系統(tǒng)提供多種模板來(lái)創(chuàng)建自己的工作簿，從而簡(jiǎn)化電子表格的排版工作?？梢詫⒐ぷ鞑颈４鏋镋xcel97-2003工作簿類型，以方便其他低版本的Excel打開。但某些Excel2016所獨(dú)特具備的功能，在低版本中將不能使用。Excel工作簿2.單元格區(qū)域選擇

對(duì)于連續(xù)單元格的選擇，也可以在選擇起始單元格后，按住Shift鍵，選擇最后一個(gè)單元格，就將兩個(gè)單元格為對(duì)角線的單元格區(qū)域選中了。3.設(shè)置單元格格式

在選擇單元格后，可以單擊鼠標(biāo)右鍵，在彈出的菜單中選擇“設(shè)置單元格格式”，在彈出的“設(shè)置單元格格式”對(duì)話框中，通過(guò)切換選項(xiàng)卡來(lái)進(jìn)行單元格格式設(shè)置。格式刷對(duì)于相同格式單元格，可以一起選擇后進(jìn)行設(shè)置，也可以在設(shè)置好第1個(gè)單元格后，利用格式刷進(jìn)行格式復(fù)制。4.對(duì)齊方式

單元格中的內(nèi)容相對(duì)于單元格邊框左右的對(duì)齊方式稱為“水平對(duì)齊”，相對(duì)于單元格邊框上下的對(duì)齊方式稱為“垂直對(duì)齊”。其中，“水平對(duì)齊”方式包括“常規(guī)”、“靠左（縮進(jìn)）”、“居中”、“靠右（縮進(jìn)）”、“填充”、“兩端對(duì)齊”、“跨列居中”、“分散對(duì)齊”8種方式?！按怪睂?duì)齊”方式包括“靠上”、“居中”、“靠下”、“兩端對(duì)齊”和“分散對(duì)齊”5種方式?！白詣?dòng)換行”按鈕“合并后居中”按鈕雙態(tài)按鈕即第1次按時(shí)實(shí)現(xiàn)單元格中的內(nèi)容根據(jù)單元格寬度自動(dòng)調(diào)整成多行顯示，第2次按時(shí)實(shí)現(xiàn)單元格中的內(nèi)容用1行顯示。5.自動(dòng)換行

除了可以通過(guò)單元格格式的“對(duì)齊”設(shè)置“自動(dòng)換行”來(lái)實(shí)現(xiàn)單元格內(nèi)容分行顯示外，也可以通過(guò)在單元格中需要換行顯示的內(nèi)容前同時(shí)按下Alt鍵和回車鍵來(lái)實(shí)現(xiàn)單元格內(nèi)容的分行顯示。只有該單元格的列寬不夠顯示單元格中的內(nèi)容，才將內(nèi)容以兩行（甚至多行）來(lái)顯示。5.自動(dòng)換行

“軟”換行無(wú)論將列調(diào)整到多寬，該單元格總是將“Alt”鍵和回車鍵處開始的內(nèi)容另起一行顯示。“硬”換行感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.3.1知識(shí)點(diǎn)解析第5章Excel基本應(yīng)用5.3課堂考勤成績(jī)計(jì)算excel單元格數(shù)據(jù)可以快速的復(fù)制和粘貼?？梢赃x擇需要復(fù)制的單元格和單元格區(qū)域，按下ctrl+C復(fù)制數(shù)據(jù)，選擇需要復(fù)制到的第1個(gè)單元格位置，按下Ctrl+V就實(shí)現(xiàn)了單元格內(nèi)容的粘貼。也可以通過(guò)“開始”選項(xiàng)區(qū)/“剪貼板”組的“復(fù)制”和“粘貼”按鈕來(lái)實(shí)現(xiàn)。1.單元格內(nèi)容復(fù)制

在粘貼到目標(biāo)位置后，將會(huì)出現(xiàn)粘貼選項(xiàng)按鈕

，可以單擊打開，進(jìn)行各種選擇性的粘貼，如

表示將內(nèi)容及格式都復(fù)制過(guò)來(lái)，而

表示只將單元格內(nèi)容復(fù)制過(guò)來(lái)，而不復(fù)制單元格格式等。Excel2.文本數(shù)據(jù)的輸入

如圖所示，在單元格A1中，輸入你自己的身份證號(hào)碼（如。你會(huì)發(fā)現(xiàn)，輸入的身份證號(hào)碼顯示為4.32302E+17；單元格編輯欄的內(nèi)容變成2.文本數(shù)據(jù)的輸入

這是因?yàn)镋xcel工作表的單元格，默認(rèn)的數(shù)據(jù)類型是“常規(guī)”，在單元格中輸入時(shí)，系統(tǒng)自動(dòng)判斷其為數(shù)字型數(shù)據(jù)，因此以科學(xué)計(jì)算法來(lái)表示該數(shù)據(jù)。要解決以上問(wèn)題，可以通過(guò)如下方式之一來(lái)實(shí)現(xiàn)：（1）在數(shù)字型文本數(shù)據(jù)前添加英文單引號(hào)“’”。（2）將該單元格數(shù)字格式設(shè)置為“文本”。3.數(shù)據(jù)有效性設(shè)置

例如，如果希望在C1單元格中輸入身份證號(hào)碼時(shí)，必須輸入18位字符，可以將其有效性設(shè)置為“允許的文本長(zhǎng)度為18”(選中需要設(shè)置的單元格，單擊數(shù)據(jù)菜單項(xiàng)，在出現(xiàn)的對(duì)話窗中選擇數(shù)據(jù)驗(yàn)證)。如圖所示。在某些時(shí)候，需要將數(shù)據(jù)限制為一個(gè)特定的類型，如整數(shù)、分?jǐn)?shù)或文本，并且限制其取值范圍。4.公式與相對(duì)引用

如圖所示，由于李春的總工資等于B2單元格的值加上C2單元格的值，因此可以在D2單元格中輸入“=B2+C2”?！?B2+C2”就是Excel中的公式，表明D2單元格引用了B2單元格的數(shù)據(jù)和C2單元格的數(shù)據(jù)，將他們的和作為自己?jiǎn)卧竦膬?nèi)容。要計(jì)算許偉嘉的總工資，可以將D2單元格的內(nèi)容復(fù)制到D3單元格?？梢钥吹?，D3單元格的公式為“=B3+C3”。4.公式與相對(duì)引用

相對(duì)引用是指當(dāng)把公式復(fù)制到其它單元格時(shí)，公式中引用的單元格或單元格區(qū)域中代表行的數(shù)字和代表列的字母會(huì)根據(jù)實(shí)際的偏移量相應(yīng)改變。D2中的公式“=B2+C2”表明“D2的值是等于當(dāng)前單元格的前2列單元格加上前1列列單元格的和”，因此，將D2單元格中的公式復(fù)制到D3后，系統(tǒng)認(rèn)為“D3的值也應(yīng)該等于當(dāng)前單元格的前2列單元格加上前1列單元格的值”，因此公式自動(dòng)變化為“=B3+C3”。在進(jìn)行公式復(fù)制時(shí)，公式中引用的地址發(fā)生了變化，這是因?yàn)橐肂3和C3單元格時(shí)，使用的是“相對(duì)引用”，在此公式中，B3和C3稱之為相對(duì)地址。

功能是返回參數(shù)列表中非空值的單元格個(gè)數(shù)。5.函數(shù)與統(tǒng)計(jì)函數(shù)COUNTA

Excel函數(shù)是系統(tǒng)預(yù)先定義的，使用一些稱為參數(shù)的特定數(shù)值來(lái)執(zhí)行計(jì)算、分析等任務(wù)的特殊公式，并返回一個(gè)或多個(gè)值?？梢杂煤瘮?shù)對(duì)某個(gè)區(qū)域內(nèi)的數(shù)值進(jìn)行一系列運(yùn)算。例如，SUM函數(shù)對(duì)單元格或單元格區(qū)域進(jìn)行加法運(yùn)算。COUNTA函數(shù)的語(yǔ)法格式為：COUNTA(value1,value2,...)Value1,value2,...為所要計(jì)算的值，參數(shù)個(gè)數(shù)為1到255個(gè)，代表要計(jì)數(shù)的值、單元格或單元格區(qū)域。COUNTA函數(shù)如圖所示，需要統(tǒng)計(jì)A1:A4單元格中非空單元格的個(gè)數(shù)，可以在B3單元格輸入函數(shù)“=COUNTA(A1:A4)”，則B3單元格中的內(nèi)容為3，即表示A1:A4中有3個(gè)非空的單元格。5.自動(dòng)換行

6.IF函數(shù)

假設(shè)公司本月的績(jī)效工資與出勤有關(guān)：全勤（出勤天數(shù)>=22）員工的績(jī)效工資為2520元，非全勤員工的績(jī)效工資為2000元。為了計(jì)算“李春”的績(jī)效工資，需要根據(jù)其出勤情況（B2）來(lái)進(jìn)行判斷，如果B2中為“全勤”，其績(jī)效工資（C3）為2520，否則為2000?？梢杂脠D來(lái)表示這種邏輯關(guān)系。（1）Logical_test：邏輯表達(dá)式。（2）Value_if_true：如果邏輯表達(dá)式的值為真，則返回該值。（3）Value_if_false：如果邏輯表達(dá)式的值為假，則返回該值。IF(Logical_test,Value_if_true,Value_if_false)6.IF函數(shù)

可以使用IF函數(shù)來(lái)實(shí)現(xiàn)。IF函數(shù)的語(yǔ)法格式為：參數(shù)的含義分別是：（1）如果Logical_test的結(jié)果為TRUE（真），就返回Value_if_true的值作為結(jié)果。（2）如果Logical_test的結(jié)果為FALSE（假），則返回Value_if_false的值作為結(jié)果。

Logical_test是計(jì)算結(jié)果可能為TRUE或FALSE的任意值或表達(dá)式。Logical_test可使用的比較運(yùn)算符有：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）以及<>（不等號(hào)）。因此，“李春”的績(jī)效工資對(duì)應(yīng)的函數(shù)是“=IF(B2="全勤",2520,2000)”。如圖所示。6.IF函數(shù)

其執(zhí)行過(guò)程是：7.條件格式

如果你是一名教師，在分析考卷時(shí)，經(jīng)常要進(jìn)行的工作有：排名前20%的學(xué)生是誰(shuí)，排名后20%的學(xué)生是誰(shuí)？低于平均值的學(xué)生是誰(shuí)？如果你是一名銷售經(jīng)理，你關(guān)心的可能會(huì)是：在過(guò)去五年的利潤(rùn)匯總中，有哪些異常情況？過(guò)去兩年的營(yíng)銷調(diào)查反映出哪些傾向？這個(gè)月誰(shuí)的銷售額超過(guò)￥50,000？哪種產(chǎn)品賣得最好？7.條件格式

因此，在某些時(shí)候，需要將一些特別的數(shù)據(jù)突出顯示出來(lái)。Excel的條件格式有助于快速完成上述要求，它可以預(yù)置一種單元格格式，并在指定的某種條件被滿足時(shí)自動(dòng)應(yīng)用于目標(biāo)單元格?？深A(yù)置的單元格包括：邊框、底紋、字體顏色等。例如，將單元格區(qū)域A1:D1所有大于100的單元格用紅色字體標(biāo)注出來(lái)，可以通過(guò)設(shè)置條件格式來(lái)實(shí)現(xiàn)。如圖所示。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.3.2任務(wù)實(shí)現(xiàn)第5章Excel基本應(yīng)用5.3課堂考勤成績(jī)計(jì)算1.任務(wù)分析

完成課堂考勤成績(jī)計(jì)算，要求：（1）將所有學(xué)生名單復(fù)制到“課堂考勤成績(jī)”工作表中，并不得改變工作表的格式。（2）編排學(xué)生學(xué)號(hào)，起始學(xué)生的學(xué)號(hào)為“01301001”，學(xué)號(hào)自動(dòng)加1遞增。（3）出勤區(qū)域的數(shù)據(jù)輸入只允許為遲到、請(qǐng)假或曠課，并將考勤數(shù)據(jù)復(fù)制到工作表中。（4）計(jì)算學(xué)生的缺勤次數(shù)。（5）計(jì)算學(xué)生的出勤成績(jī)。計(jì)算出勤成績(jī)=100-缺勤次數(shù)×10。（6）計(jì)算學(xué)生的實(shí)際出勤成績(jī)。如果學(xué)生缺勤達(dá)到5次以上（不含5次），實(shí)際出勤成績(jī)計(jì)為0分，否則實(shí)際出勤成績(jī)等于計(jì)算出勤成績(jī)。（7）將80分（不含80）以下的實(shí)際出勤成績(jī)用“淺紅填充色深紅色文本”特別標(biāo)注出來(lái)。（8）隱藏工作表的網(wǎng)格線，設(shè)置出勤表打印在一頁(yè)A4紙上，并水平和垂直居中。將“相關(guān)素材.XLSX”中的學(xué)生名單復(fù)制到“課堂考勤成績(jī)”工作表中。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

1）復(fù)制學(xué)生名單①打開“相關(guān)素材.XLSX”工作簿，選擇“學(xué)生名單”工作表的A2:A37單元格區(qū)域，按下Ctrl+C，對(duì)選擇的單元格區(qū)域進(jìn)行復(fù)制。②切換到“大學(xué)計(jì)算機(jī)成績(jī).XLSX”工作簿的“課堂考勤成績(jī)”工作表，定位到單元格B5，按下Ctrl+V，將姓名粘貼到“課堂考勤成績(jī)”工作表中。③單擊粘貼區(qū)域的粘貼選項(xiàng)按鈕

，選擇粘貼值按鈕

，則在未改變工作表的現(xiàn)有格式的前提下，實(shí)現(xiàn)了學(xué)生名單的復(fù)制。

①由于學(xué)號(hào)數(shù)據(jù)為文本類型，因此，在A5單元格中首先需要輸入英文單引號(hào)“’”，然后輸入學(xué)號(hào)01301001。②選擇A5單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變?yōu)楹谑謺r(shí)，按住鼠標(biāo)左鍵向下拖動(dòng)填充柄，拖動(dòng)過(guò)程中填充柄的右下方出現(xiàn)填充的數(shù)據(jù)，拖至目標(biāo)單元格（A40）時(shí)釋放鼠標(biāo)。③單擊如圖所示的“自動(dòng)填充選項(xiàng)”，擴(kuò)展開選項(xiàng)菜單，選擇“不帶格式填充”，這樣，就即實(shí)現(xiàn)了學(xué)號(hào)的自動(dòng)填充，也沒有破壞其原來(lái)表格樣式。2.實(shí)現(xiàn)過(guò)程2）編排學(xué)號(hào)起始學(xué)生學(xué)號(hào)“01301001”，學(xué)號(hào)自動(dòng)加1遞增進(jìn)行編排。操作步驟如下：

選擇C5:L40單元格區(qū)域。單擊“數(shù)據(jù)”選項(xiàng)卡/“數(shù)據(jù)驗(yàn)證”組的數(shù)據(jù)驗(yàn)證按鈕，彈出如圖所示的對(duì)話框。2.實(shí)現(xiàn)過(guò)程3）設(shè)置出勤數(shù)據(jù)驗(yàn)證出勤區(qū)域的數(shù)據(jù)輸入只允許為遲到、請(qǐng)假或曠課，可以通過(guò)設(shè)置數(shù)據(jù)有效性來(lái)實(shí)現(xiàn)。操作步驟如下：

③在“設(shè)置”選項(xiàng)卡中，允許選擇序列，來(lái)源中輸入“遲到,請(qǐng)假,曠課”，注意分隔符為英文逗號(hào)。④單擊確定按鈕。這樣，對(duì)C5:L40單元格區(qū)域，就只允許選擇性輸入遲到、請(qǐng)假或曠課了。⑤將“相關(guān)素材.XLSX”中的考勤數(shù)據(jù)復(fù)制到“課堂考勤成績(jī)”工作表中，并不改變工作表的格式。2.實(shí)現(xiàn)過(guò)程3）設(shè)置出勤數(shù)據(jù)驗(yàn)證由于學(xué)生如果有缺勤的情況，就會(huì)在對(duì)應(yīng)的單元格標(biāo)記上“遲到”、“請(qǐng)假”或“曠課”，因此，可以通過(guò)COUNTA函數(shù)來(lái)統(tǒng)計(jì)學(xué)生所對(duì)應(yīng)的缺勤區(qū)域中非空值的單元格個(gè)數(shù)，從而得到學(xué)生的缺勤次數(shù)。操作步驟如下：

2.實(shí)現(xiàn)過(guò)程4）計(jì)算缺勤次數(shù)①選擇M5單元格,單擊M5單元格編輯欄區(qū)域的“插入函數(shù)”按鈕，彈出如圖所示的“插入函數(shù)”對(duì)話框，選擇“統(tǒng)計(jì)”類別及COUNTA函數(shù)，單擊確定按鈕。

2.實(shí)現(xiàn)過(guò)程4）計(jì)算缺勤次數(shù)②在圖所示“函數(shù)參數(shù)”對(duì)話框中，將光標(biāo)定位到Value1區(qū)域，用鼠標(biāo)左鍵選擇C5:L5單元格區(qū)域，Value1區(qū)域中顯示“C5:L5”，單擊確定按鈕。這時(shí)，M5單元格的值為3，M5編輯區(qū)的內(nèi)容為“=COUNTA(C5:L5)”。③選擇M5單元格，鼠標(biāo)指針指向M5單元格的填充柄，雙擊鼠標(biāo)左鍵，完成自動(dòng)復(fù)制填充。④單擊“自動(dòng)填充選項(xiàng)”，擴(kuò)展開選項(xiàng)菜單，選擇“不帶格式填充”。由于學(xué)生的計(jì)算出勤成績(jī)=100-缺勤次數(shù)×10，因此可以用公式來(lái)實(shí)現(xiàn)。操作步驟如下：

2.實(shí)現(xiàn)過(guò)程5）計(jì)算出勤成績(jī)①選擇N5單元格，輸入“=100-M5*10”，輸入回車確認(rèn)。這時(shí)，N5單元格的值為70，N5單元格編輯欄的內(nèi)容為“=100-M5*10”。②選擇N5單元格，鼠標(biāo)指針指向N5單元格的填充柄，雙擊鼠標(biāo)左鍵，完成自動(dòng)復(fù)制填充。③單擊“自動(dòng)填充選項(xiàng)”，擴(kuò)展開選項(xiàng)菜單，選擇“不帶格式填充”，完成公式復(fù)制。由于學(xué)生的實(shí)際出勤成績(jī)與缺勤次數(shù)有關(guān)，如果學(xué)生缺勤達(dá)到5次以上（不含5次），實(shí)際出勤成績(jī)計(jì)為0分，否則實(shí)際出勤成績(jī)等于計(jì)算出勤成績(jī)。因此可以用IF函數(shù)進(jìn)行判斷實(shí)現(xiàn)。操作步驟如下：

2.實(shí)現(xiàn)過(guò)程6）計(jì)算實(shí)際出勤成績(jī)①選擇O5單元格,單擊O5單元格編輯欄區(qū)域的“插入函數(shù)”按鈕，彈出如圖所示的“插入函數(shù)”對(duì)話框，在“搜索函數(shù)”區(qū)域輸入“IF”，單擊轉(zhuǎn)到按鈕，選擇“IF”函數(shù)，單擊確定按鈕。②在如圖所示的“函數(shù)參數(shù)”對(duì)話框的Logical_test輸入處，用鼠標(biāo)左鍵單擊N5，這時(shí)，Logical_test顯示N5，從鍵盤輸入>=50，在value_if_true中輸入N5，在value_if_false輸入處，輸入0，單擊確定按鈕。這時(shí)，O5單元格的值為70，O5單元格編輯欄的內(nèi)容為“=IF(N5>=50,N5,0)”。

2.實(shí)現(xiàn)過(guò)程6）計(jì)算實(shí)際出勤成績(jī)③選擇O5單元格，鼠標(biāo)指針指向O5單元格的填充柄，雙擊鼠標(biāo)左鍵，完成自動(dòng)復(fù)制填充。④單擊“自動(dòng)填充選項(xiàng)”，擴(kuò)展開選項(xiàng)菜單，選擇“不帶格式填充”。

2.實(shí)現(xiàn)過(guò)程7）實(shí)際出勤成績(jī)特別標(biāo)注由于需要將80分（不含80）以下的實(shí)際出勤成績(jī)用“淺紅填充色深紅色文本”特別標(biāo)注出來(lái)，可以通過(guò)條件格式來(lái)實(shí)現(xiàn)。操作步驟如下：①選擇實(shí)際出勤成績(jī)單元格區(qū)域O5:O40，單擊“開始”選項(xiàng)卡/“樣式”組/“條件格式”按鈕/“突出顯示單元格規(guī)則”按鈕/“小于”按鈕。②在彈出的如圖所示的“小于”對(duì)話框中，第1個(gè)輸入框輸入80，設(shè)置為“淺紅填充色深紅色文本”，單擊確定按鈕?？梢钥吹剑瑢?shí)際出勤成績(jī)小于80的單元格用“淺紅填充色深紅色文本”特別標(biāo)注出來(lái)。

2.實(shí)現(xiàn)過(guò)程8）打印設(shè)置要設(shè)置出勤表打印在一頁(yè)A4紙上，并水平和垂直居中?？梢酝ㄟ^(guò)“頁(yè)面布局”選項(xiàng)卡的“頁(yè)面設(shè)置”組來(lái)實(shí)現(xiàn)。操作步驟如下：①單擊“頁(yè)面布局”選項(xiàng)卡/“頁(yè)面設(shè)置”組/“紙張大小”按鈕，選擇“A4”。②單擊“頁(yè)面布局”選項(xiàng)卡/“頁(yè)面設(shè)置”組/“頁(yè)邊距”按鈕，選擇“自定義邊距”，在彈出的“頁(yè)面設(shè)置”對(duì)話框的“頁(yè)邊距”選項(xiàng)卡中，設(shè)置上、下頁(yè)邊距為3cm，左、右頁(yè)邊距為2cm，勾選“水平”和“垂直”居中方式。如圖所示。

2.實(shí)現(xiàn)過(guò)程8）打印設(shè)置③單擊上圖中的“打印預(yù)覽”按鈕，可以看到工作表在兩頁(yè)上顯示，如圖所示。單擊“無(wú)縮放”按鈕，在彈出的選項(xiàng)中選擇“將工作表調(diào)整為一頁(yè)”，就可以看到工作表在一頁(yè)上顯示了。為了隱藏工作表的網(wǎng)格線，可以通過(guò)設(shè)置Excel的“選項(xiàng)”來(lái)實(shí)現(xiàn)。操作步驟如下：

2.實(shí)現(xiàn)過(guò)程9）隱藏網(wǎng)格線①選擇“文件”選項(xiàng)卡的“選項(xiàng)”按鈕，在彈出的如圖所示的“Excel選項(xiàng)”對(duì)話框中，單擊“高級(jí)”選項(xiàng)。②滑動(dòng)滾動(dòng)條到“此工作表的顯示選項(xiàng)”，確定當(dāng)前選擇的工作表為“課堂考勤成績(jī)”，清除“顯示網(wǎng)格線”復(fù)選框可以看到，當(dāng)前工作表中不再顯示網(wǎng)格線。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.3.3總結(jié)與提高第5章Excel基本應(yīng)用5.3課堂考勤成績(jī)計(jì)算1.數(shù)據(jù)類型

系統(tǒng)默認(rèn)的單元格數(shù)據(jù)類型為“常規(guī)”，在輸入內(nèi)容后，系統(tǒng)則會(huì)根據(jù)單元格中的內(nèi)容，自動(dòng)判斷數(shù)據(jù)類型。例如，如果A1單元格的類型為“常規(guī)”，則在其輸入“43230219950110001”，系統(tǒng)會(huì)自動(dòng)判斷其為數(shù)字型，因此將其以“4.323E+16”的科學(xué)計(jì)數(shù)數(shù)據(jù)顯示。

可以在輸入數(shù)據(jù)時(shí)通過(guò)前導(dǎo)符等形式，限制輸入的數(shù)據(jù)類型。常用的通過(guò)錄入限制數(shù)據(jù)類型的方法如表所示。表5-2錄入不同數(shù)據(jù)類型說(shuō)明數(shù)據(jù)類型錄入說(shuō)明示例文本（1）當(dāng)輸入的文本超出單元格的寬度時(shí)，需要調(diào)整單元格的寬度才能完整顯示。（2）輸入數(shù)字型文本時(shí)，可以在數(shù)字前輸入單引號(hào)“'”。輸入身份證號(hào)碼：'43230219950110001數(shù)值（1）輸入“3/4”時(shí)，系統(tǒng)自動(dòng)將其轉(zhuǎn)化為日期3月4日。（2）要輸入分?jǐn)?shù)，需要在其前面輸入0和空格。輸入分?jǐn)?shù)：03/4日期年月日之間用“/”或“-”號(hào)分隔。輸入日期：2014/3/21時(shí)間時(shí)分秒間用“：”分隔。輸入時(shí)間：12：32：051.數(shù)據(jù)類型

在Excel2016中，復(fù)制內(nèi)容之后，在目標(biāo)單元格上單擊鼠標(biāo)右鍵，在選擇性粘貼右側(cè)有個(gè)箭頭，單擊箭頭會(huì)出現(xiàn)選擇性粘貼的所有粘貼方式，當(dāng)鼠標(biāo)停在某個(gè)粘貼選項(xiàng)上時(shí)，在單元格中會(huì)出現(xiàn)最終粘貼樣式的預(yù)覽。粘貼分為：2.選擇性粘貼（1）粘貼：將源區(qū)域中的所有內(nèi)容、格式、條件格式、數(shù)據(jù)有效性、批注等全部粘貼到目標(biāo)區(qū)域。（2）公式：僅粘貼源區(qū)域中的文本、數(shù)值、日期及公式等內(nèi)容。（3）公式和數(shù)字格式：除粘貼源區(qū)域內(nèi)容外，還包含源區(qū)域的數(shù)值格式。數(shù)字格式包括貨幣樣式、百分比樣式、小數(shù)點(diǎn)位數(shù)等。

2.選擇性粘貼（4）保留源格式：復(fù)制源區(qū)域的所有內(nèi)容和格式，它與“粘貼”的不同點(diǎn)是：當(dāng)源區(qū)域中包含用公式設(shè)置的條件格式時(shí)，在同一工作簿中的不同工作表之間用這種方法粘貼后，目標(biāo)區(qū)域條件格式中的公式會(huì)引用源工作表中對(duì)應(yīng)的單元格區(qū)域。（5）無(wú)邊框：粘貼全部?jī)?nèi)容，僅去掉源區(qū)域中的邊框。（6）保留源列寬：與保留源格式選項(xiàng)類似，但同時(shí)還復(fù)制源區(qū)域中的列寬。這與“選擇性粘貼”對(duì)話框中的“列寬”選項(xiàng)不同，“選擇性粘貼”對(duì)話框中的“列寬”選項(xiàng)僅復(fù)制列寬而不粘貼內(nèi)容。（7）轉(zhuǎn)置：粘貼時(shí)互換行和列。（8）合并條件格式：當(dāng)源區(qū)域中包含條件格式時(shí)，粘貼時(shí)將源區(qū)域與目標(biāo)區(qū)域中的條件格式合并。如果源區(qū)域不包含條件格式，該選項(xiàng)不可見。

2.選擇性粘貼（9）值：將文本、數(shù)值、日期及公式結(jié)果粘貼到目標(biāo)區(qū)域，不復(fù)制格式。（10）值和數(shù)字格式：將公式結(jié)果粘貼到目標(biāo)區(qū)域，同時(shí)還包含數(shù)字格式。（11）值和源格式：與保留源格式選項(xiàng)類似，粘貼時(shí)將公式結(jié)果粘貼到目標(biāo)區(qū)域，同時(shí)復(fù)制源區(qū)域中的格式。（12）格式：僅復(fù)制源區(qū)域中的格式，而不包括內(nèi)容。（13）粘貼鏈接：在目標(biāo)區(qū)域中創(chuàng)建引用源區(qū)域的公式。（14）圖片：將源區(qū)域作為圖片進(jìn)行粘貼。（15）鏈接的圖片：將源區(qū)域粘貼為圖片，但圖片會(huì)根據(jù)源區(qū)域數(shù)據(jù)的變化而變化。類似于Excel中的“照相機(jī)”功能。

3.自動(dòng)填充使用Excel提供的自動(dòng)填充功能，可以極大地減少數(shù)據(jù)輸入的工作量。通過(guò)拖動(dòng)填充柄，就可以激活自動(dòng)填充功能。利用自動(dòng)填充功能可以進(jìn)行文本、數(shù)字、日期、公式等序列的填充和數(shù)據(jù)的復(fù)制。（1）自動(dòng)填充或復(fù)制，除了之前所示的拖動(dòng)鼠標(biāo)方式外，也可以在鼠標(biāo)變?yōu)楹谑謺r(shí)，雙擊鼠標(biāo)左鍵來(lái)實(shí)現(xiàn)自動(dòng)填充。（2）也可以在鼠標(biāo)變?yōu)楹谑謺r(shí)，單擊鼠標(biāo)右鍵，拖動(dòng)鼠標(biāo)到需要填充的最后位置，松開鼠標(biāo)，在彈出的快捷菜單中選擇需要的填充模式來(lái)實(shí)現(xiàn)自動(dòng)填充。（3）除了可以根據(jù)選擇的單元格或單元格區(qū)域自動(dòng)填充外，還可以通過(guò)“開始”選項(xiàng)卡/“編輯”組/“填充”按鈕/“系列”來(lái)實(shí)現(xiàn)等差、等比等序列的自動(dòng)填充。

通過(guò)“數(shù)據(jù)”選項(xiàng)卡/“數(shù)據(jù)驗(yàn)證”組/“數(shù)據(jù)驗(yàn)證”按鈕設(shè)置了數(shù)據(jù)的有效范圍后，如果在單元格中輸入了無(wú)效數(shù)據(jù)，Excel會(huì)彈出一個(gè)如圖所示的“警告”對(duì)話框，警告用戶輸入的數(shù)據(jù)是非法的。單擊對(duì)話框中的“重試”按鈕，可以重新輸入有效的數(shù)據(jù)。4.數(shù)據(jù)驗(yàn)證默認(rèn)情況下，Excel對(duì)單元格的輸入是不加任何限制的。但為了保證輸入數(shù)據(jù)的正確性，可以為單元格或單元格區(qū)域指定輸入數(shù)據(jù)的有效范圍。

數(shù)據(jù)有效性可以通過(guò)設(shè)置“有效性條件”將數(shù)據(jù)限制為一個(gè)特定的類型并限制其取值范圍，還可以設(shè)置輸入提示等。如圖所示，可以將A1單元格的輸入范圍為限定為0～100，當(dāng)輸入錯(cuò)誤時(shí)，給出“成績(jī)范圍為0～100！”的提示，同時(shí)取消輸入或要求重新輸入。4.數(shù)據(jù)驗(yàn)證

4.數(shù)據(jù)驗(yàn)證操作步驟如下：①選擇A1單元格，在數(shù)據(jù)有效性設(shè)置中，將“有效性條件”的允許設(shè)置為“整數(shù)”，“數(shù)據(jù)”選擇“介于”，最小值設(shè)置為0，最大值設(shè)置為100。②“出錯(cuò)警告”選項(xiàng)卡中，在標(biāo)題欄輸入“你輸入的不合法”，在錯(cuò)誤信息欄輸入“成績(jī)范圍為0～100分！”，單擊確定按鈕。這樣，在輸入錯(cuò)誤時(shí)，將出現(xiàn)自定義的出錯(cuò)警告。

4.數(shù)據(jù)驗(yàn)證此外，還可以將單元格區(qū)域的內(nèi)容作為有效性輸入的數(shù)據(jù)限制。如圖所示。

5.條件格式“條件格式”中最常用的是“突出顯示單元格規(guī)格”與“項(xiàng)目選取規(guī)則”?！巴怀鲲@示單元格規(guī)格”用于突出一些固定格式的單元格，而“項(xiàng)目選取規(guī)則”則用于統(tǒng)計(jì)數(shù)據(jù)，如突出顯示高于/低于平均值的數(shù)據(jù)，或按百分比來(lái)找出數(shù)據(jù)。對(duì)同一個(gè)單元格（或單元格區(qū)域），如果應(yīng)用有兩個(gè)或兩個(gè)以上的不同條件格式，這些條件可能沖突，也可能不沖突：（1）規(guī)則不沖突：例如，如果一個(gè)規(guī)則將單元格格式設(shè)置為字體加粗，而另一個(gè)規(guī)則將同一個(gè)單元格的格式設(shè)置為紅色，則該單元格格式設(shè)置為字體加粗且為紅色。因?yàn)檫@兩種格式間沒有沖突，所以兩個(gè)規(guī)則都得到應(yīng)用。（2）規(guī)則沖突：例如，一個(gè)規(guī)則將單元格字體顏色設(shè)置為紅色，而另一個(gè)規(guī)則將單元格字體顏色設(shè)置為綠色。因?yàn)檫@兩個(gè)規(guī)則沖突，所以只應(yīng)用一個(gè)規(guī)則，應(yīng)用優(yōu)先級(jí)較高的規(guī)則。

5.條件格式因此，在設(shè)置多條件的條件格式時(shí)，要充分考慮各條件之間的設(shè)置順序。若要調(diào)整條件格式的先后順序或編輯條件格式，可以通過(guò)“條件格式”的“管理規(guī)則”來(lái)實(shí)現(xiàn)如果想刪除單元格或工作表的所有條件格式，可以通過(guò)“條件格式”的“清除規(guī)則”來(lái)實(shí)現(xiàn)

5.條件格式例如，希望將計(jì)算考勤成績(jī)?cè)?0分以下（不含80分）的學(xué)生姓名用紅色標(biāo)識(shí)出來(lái)，操作步驟如下：此外，也可以通過(guò)公式來(lái)設(shè)定單元格條件格式。①選擇“課堂考勤成績(jī)”工作表的姓名單元格區(qū)域B5:B40，單擊“開始”選項(xiàng)卡/“樣式”組/“條件格式”按鈕/“新建規(guī)則”。

5.條件格式其中，公式“=N5<80”指明的條件格式為：如果N5中的的內(nèi)容小于80，那么B5單元格的內(nèi)容以“紅色、加粗”突出顯示。此外，也可以通過(guò)公式來(lái)設(shè)定單元格條件格式。②在彈出的如圖所示的“新建格式規(guī)則”對(duì)話框中，選擇規(guī)則類型為“使用公式確定要設(shè)置格式的單元格”，在編輯規(guī)則中輸入公式“=N5<80”（表示條件為“計(jì)算考勤成績(jī)<80”），選擇“格式”，設(shè)置為“紅色，加粗”，單擊確定按鈕?？梢钥吹?，計(jì)算考勤成績(jī)?cè)?0分以下的姓名就以“紅色、加粗”突出顯示了。

6.插入函數(shù)除了可以通過(guò)單元格編輯欄區(qū)域的“插入函數(shù)”按鈕

外，還可以通過(guò)“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組來(lái)選擇插入邏輯類、文本類以及數(shù)字和三角函數(shù)等不同函數(shù)。此外，如果公式的計(jì)算結(jié)果與預(yù)期有誤，可以通過(guò)“公式”選項(xiàng)卡/“公式審核”組的相關(guān)功能來(lái)進(jìn)行錯(cuò)誤檢查。

7.邏輯表達(dá)式在函數(shù)中，經(jīng)常要判斷某個(gè)數(shù)所處的區(qū)域，例如：（1）如果B3單元格中的數(shù)據(jù)大于等于100，C3單元格中的結(jié)果為“GOOD”，否則為“BAD”。其對(duì)應(yīng)的邏輯表達(dá)式為“B3>100”，公式為“=IF(B3>100,"GOOD","BAD")”。（2）如果B4單元格中的數(shù)據(jù)大于100并且小于等于200，C4單元格返回結(jié)果為1，否則為0。其對(duì)應(yīng)的邏輯表達(dá)式為“AND(B4>100,B4<=200)”，公式為“=IF(AND(B4>100,B4<=200),1,0)”。（3）如果B5單元格中的數(shù)據(jù)小于等于100或者大于200，C5單元格返回結(jié)果為“真”，否則為“假”。其對(duì)應(yīng)的邏輯表達(dá)式為“OR(B5<=100,B5>200)”，公式為“=IF(OR(B5<=100,B5>200),"真","假")”。

8.Excel選項(xiàng)“Excel選項(xiàng)”主要用來(lái)更改Excel2016的常規(guī)設(shè)置（如將默認(rèn)使用的“正文11號(hào)字”修改為其他字體字號(hào)）、公式計(jì)算設(shè)置、工作簿保存設(shè)置、高級(jí)設(shè)置（如設(shè)置工作表網(wǎng)格線不顯示）、自定義功能區(qū)等。如圖所示，為了防止由于沒有及時(shí)保存文件，可以通過(guò)“Excel選項(xiàng)”的“保存”選項(xiàng)來(lái)啟用自動(dòng)定時(shí)保存功能。

8.Excel選項(xiàng)自動(dòng)保存的運(yùn)作機(jī)理如下：（1）要讓自動(dòng)保存起作用的文檔必須是至少保存過(guò)一次的文檔（也就是硬盤中存在的文檔），如果是在程序中直接新建的空白文檔，需要先保存為硬盤中的某個(gè)文檔以后才可以啟用此功能。（2）在前一次保存（包括手動(dòng)保存或自動(dòng)保存）后，在文檔發(fā)生新的修改后，系統(tǒng)內(nèi)部的計(jì)時(shí)器開始啟動(dòng)，到達(dá)指定的時(shí)間間隔后發(fā)生一次自動(dòng)保存動(dòng)作。（3）只有在Excel程序窗口被激活的狀態(tài)下，計(jì)時(shí)器才會(huì)工作。假設(shè)打開了Excel，并進(jìn)行了修改，但又切換到了其他應(yīng)用程序，此時(shí)計(jì)時(shí)器將停止工作。（4）在計(jì)時(shí)器工作過(guò)程之中，如果提前發(fā)生了手動(dòng)存檔事件，計(jì)時(shí)器將清零停止工作。

8.Excel選項(xiàng)自動(dòng)保存的運(yùn)作機(jī)理如下：（5）在一次自動(dòng)保存事件發(fā)生過(guò)后，如果文檔沒有新的編輯動(dòng)作產(chǎn)生，計(jì)時(shí)器也不會(huì)開始工作。如圖所示，可以通過(guò)“文件”選項(xiàng)卡/“信息”查看文件自動(dòng)保存的版本信息。

8.Excel選項(xiàng)Excel默認(rèn)的功能區(qū)包括“開始”、“插入”等選項(xiàng)卡，每個(gè)選項(xiàng)卡下都包含相應(yīng)的功能選項(xiàng)，如在“開始”選項(xiàng)卡下有“字體”設(shè)置、“對(duì)齊”等功能。功能區(qū)只是顯示了經(jīng)常使用的功能，如果想使用的功能不在這里，而又經(jīng)常要使用，就可以通過(guò)“Excel選項(xiàng)”對(duì)話框的“自定義功能區(qū)”去修改或添加Excel功能區(qū)。

8.Excel選項(xiàng)如圖所示，可以在“開始”選項(xiàng)卡下“新建組”，并將組重命名為“工作表操作”，然后將“插入工作表行”命令添加到“工作表操作”中。這樣，在“開始”選項(xiàng)卡下就出現(xiàn)了“工作表操作”組，該組下面有一個(gè)“插入工作表行”按鈕。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.4.1知識(shí)點(diǎn)解析第5章Excel基本應(yīng)用5.4課程成績(jī)計(jì)算1.絕對(duì)引用

如果希望公式中引用的單元格或單元格區(qū)域不隨公式位置的變化而變化，公式中引用的單元格或單元格區(qū)域需要使用絕對(duì)地址，而這種引用稱之為絕對(duì)引用。絕對(duì)引用是指當(dāng)把公式復(fù)制到其它單元格時(shí)，公式中引用的單元格或單元格區(qū)域中行和列的引用不會(huì)改變。絕對(duì)引用的單元格名稱中其行和列之前均會(huì)加上$以示區(qū)分，如$A$4表示絕對(duì)引用A4單元格，$A$4稱之為絕對(duì)地址。

1.絕對(duì)引用如圖5-40所示，李明的虛擬貨幣數(shù)=B4*C2，陳思欣的虛擬貨幣數(shù)=B5*C2，…，彭培斯的虛擬貨幣數(shù)=B11*C2，即某人對(duì)應(yīng)的虛擬貨幣數(shù)=他所持有的游戲幣數(shù)*C2。在進(jìn)行公式復(fù)制時(shí)，必須保證C2的引用不隨公式位置的變化而變化，因此需要使用絕對(duì)地址$C$2。指定的位數(shù)，按此位數(shù)進(jìn)行四舍五入。如果Num_digits大于0，則四舍五入到指定的小數(shù)位。如果Num_digits等于0，則四舍五入到最接近的整數(shù)。如果Num_digits小于0，則在小數(shù)點(diǎn)左側(cè)進(jìn)行四舍五入。例如，ROUND(7.351,0)的結(jié)果是7，ROUND(7.351,1)的結(jié)果是7.4，ROUND(7.351,-1)的結(jié)果是10。需要進(jìn)行四舍五入的數(shù)字。

ROUND函數(shù)返回某個(gè)數(shù)字按指定位數(shù)取整后的數(shù)字。ROUND函數(shù)的語(yǔ)法格式為ROUND(Number,Num_digits)。兩個(gè)參數(shù)的含義分別是：2.四舍五入函數(shù)ROUNDNumberNum_digits

3.IF函數(shù)嵌套假設(shè)需要根據(jù)游戲積分來(lái)進(jìn)行獎(jiǎng)勵(lì)，獎(jiǎng)勵(lì)的規(guī)則是：如果游戲積分大于等于20000分，則獎(jiǎng)勵(lì)積分1000分。如果游戲積分大于等于15000分，則獎(jiǎng)勵(lì)積分500分，否則獎(jiǎng)勵(lì)積分0分。要實(shí)現(xiàn)上述計(jì)算，可以用IF函數(shù)嵌套來(lái)實(shí)現(xiàn)多條件判斷。

3.IF函數(shù)嵌套第1層IF第2層IF進(jìn)入該分支，表示B3<15000（B3肯定也<20000）進(jìn)入該分支，表示B3<20000進(jìn)入該分支，表示B3>=20000進(jìn)入該分支，表示B3<20000并且B3>=15000以計(jì)算李明的游戲積分為例，可以描述為圖。

3.IF函數(shù)嵌套因此，李明的游戲積分（C3單元格）里的公式是“=IF(B3>=20000,1000,IF(B3>=15000,500,0))”，計(jì)算出來(lái)的獎(jiǎng)勵(lì)積分為500。如圖所示。需要找到排位位置的數(shù)字或單元格。

4.排位函數(shù)RANK.EQRANK.EQ函數(shù)的功能是返回一個(gè)數(shù)字在數(shù)據(jù)區(qū)域中的排位。其排位大小與數(shù)據(jù)區(qū)域中的其他值相關(guān)。如果多個(gè)值具有相同的排位，則返回該組數(shù)值的最高排位。RANK.EQ函數(shù)的語(yǔ)法格式為RANK.EQ(Number,Ref,Order)，三個(gè)參數(shù)的含義分別是：Number需要參與排位的數(shù)據(jù)區(qū)域，Ref中的非數(shù)值型值將被忽略。Ref如果order為0或省略，是按降序排位，否則為升序排位。Order

4.數(shù)據(jù)驗(yàn)證如圖所示，要計(jì)算B3單元格在B3:B10單元格區(qū)域中排在第幾位（通常，我們認(rèn)為數(shù)值大的排位為1，因此，該排序應(yīng)該是降序排序），其對(duì)應(yīng)的參數(shù)分別是：Number為B3，Ref為$B$3:$B$10，Order為0，C3單元格對(duì)應(yīng)的公式為“=RANKD.EQ(B3,$B$3:$B$10,0)”。函數(shù)RANK.EQ對(duì)重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如B3和B6單元格的數(shù)值一樣，它們對(duì)應(yīng)的排位都為4，B9單元格的排位則為6（沒有排位為5的數(shù)值）。需要特別注意的是，由于每個(gè)人的積分排名都是在B3:B10單元格區(qū)域中比較，因此Ref參數(shù)需要用絕對(duì)地址$B$3:$B$10。

5.復(fù)雜條件格式與混合引用通過(guò)條件格式，可以將某單元格區(qū)域中滿足特定條件的單元格標(biāo)識(shí)出來(lái)。但在很多時(shí)候，往往需要將包含有特定值的單元格所在行的所有信息都標(biāo)識(shí)出來(lái)。要實(shí)現(xiàn)該功能，就要對(duì)需要標(biāo)識(shí)的單元格區(qū)域使用復(fù)雜條件格式。第3行數(shù)據(jù)（A3:C3）的所有單元格對(duì)應(yīng)的條件格式可以描述為：如果第3行中的第C列單元格中的數(shù)據(jù)<=3（C3<=3），那么對(duì)該單元格應(yīng)用“灰色背景”格式。第4行數(shù)據(jù)（A4:C4）的所有單元格對(duì)應(yīng)的條件格式可以描述為：如果第4行中的第C列單元格中的數(shù)據(jù)<=3（C4<=3），那么對(duì)該單元格應(yīng)用“灰色背景”格式。例如，如果需要將排行榜（C列）前3名的整行數(shù)據(jù)（A列至C列）都用“灰色背景”標(biāo)識(shí)出來(lái)，就需要對(duì)A3:C10單元格區(qū)域運(yùn)用復(fù)雜條件格式。

5.復(fù)雜條件格式與混合引用從以上分析可以知道，對(duì)第3到第10行（A3:C10）的所有單元格對(duì)應(yīng)的條件格式的公式為“=$C3<=3”。在該條件表達(dá)式中，地址“$C3”為混合引用單元格地址?；旌弦檬侵竼卧竦刂分屑扔邢鄬?duì)引用，也有絕對(duì)引用?！?C3”標(biāo)識(shí)具有絕對(duì)列C和相對(duì)行，當(dāng)公式在復(fù)制或移動(dòng)時(shí)，保持列不變而行變化。將滿足公式“=$C3<=3”條件的所有單元格用“灰色背景”格式的結(jié)果如圖所示。例如，在用條件“=$C3<=3”對(duì)A3:C10的單元格進(jìn)行條件格式判斷時(shí)，A3、B3、C3（第3行）單元格的格式判斷條件為“C3單元格的內(nèi)容是否<=3”，在A8、B8、C8單元格（第8行）單元格的格式判斷條件為“C8單元格的內(nèi)容是否<=3”。

6.單元格保護(hù)為了防止別人對(duì)工作表的誤操作，可以設(shè)置保護(hù)工作表。但由于默認(rèn)情況下，一旦設(shè)置了保護(hù)工作表，所有單元格都不能被編輯。因此，在必要的時(shí)候，可以通過(guò)設(shè)置單元格的鎖定狀態(tài)，來(lái)確定哪些單元格不允許被編輯。例如，為了防止對(duì)某些單元格（如G6:G41）進(jìn)行編輯，需要：（1）先將所有單元格設(shè)置為不鎖定。（2）將不允許編輯的單元格（G6:G41）設(shè)置為鎖定。這樣，在保護(hù)工作表后，就可以對(duì)未被鎖定的單元格進(jìn)行編輯了。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.4.2任務(wù)實(shí)現(xiàn)第5章Excel基本應(yīng)用5.4課程成績(jī)計(jì)算1.任務(wù)分析

（1）將“相關(guān)素材.xlsx”工作簿中的成績(jī)空白表復(fù)制到“大學(xué)計(jì)算機(jī)成績(jī).xlsx”工作簿中的“課堂考勤成績(jī)”工作表之后，將其命名為“課程成績(jī)”工作表，并設(shè)置出勤成績(jī)、課堂表現(xiàn)、課后實(shí)訓(xùn)以及大作業(yè)的比重分別為20%，20%，20%，40%。并將學(xué)生的出勤成績(jī)、課堂表現(xiàn)、課后實(shí)訓(xùn)、大作業(yè)以及期末考試成績(jī)復(fù)制到相應(yīng)位置。制作如圖所示的表格，要求：（2）計(jì)算學(xué)生的平時(shí)成績(jī)及總評(píng)成績(jī)（平時(shí)成績(jī)和總評(píng)成績(jī)四舍五入為整數(shù)）。平時(shí)成績(jī)=出勤成績(jī)×出勤成績(jī)比重+課堂表現(xiàn)×課堂表現(xiàn)比重+課后實(shí)訓(xùn)×課后實(shí)訓(xùn)比重+大作業(yè)×大作業(yè)比重，總評(píng)成績(jī)=平時(shí)成績(jī)×平時(shí)成績(jī)比重+期末成績(jī)×（1-平時(shí)成績(jī)比重）。1.任務(wù)分析

（3）計(jì)算學(xué)生的課程績(jī)點(diǎn)。課程總評(píng)成績(jī)?yōu)?00分的課程績(jī)點(diǎn)為4.0，60分的課程績(jī)點(diǎn)為1.0，60分以下課程績(jī)點(diǎn)為0，課程績(jī)點(diǎn)帶一位小數(shù)。60分～100分間對(duì)應(yīng)的績(jī)點(diǎn)計(jì)算公式如下：制作如圖所示的表格，要求：

（4）計(jì)算學(xué)生的總評(píng)等級(jí)?？傇u(píng)成績(jī)>=90分計(jì)為A，總評(píng)成績(jī)>=80分計(jì)為B，總評(píng)成績(jī)>=70分計(jì)為C，總評(píng)成績(jī)>=60分計(jì)為D，其他計(jì)為F。（5）根據(jù)學(xué)生的總評(píng)成績(jī)進(jìn)行排名。1.任務(wù)分析

（6）將班級(jí)前10名的數(shù)據(jù)用灰色底紋進(jìn)行標(biāo)注。制作如圖所示的表格，要求：（7）鎖定平時(shí)成績(jī)、總評(píng)成績(jī)、課程績(jī)點(diǎn)、總評(píng)等級(jí)、總評(píng)排名區(qū)域，防止誤輸入。（8）對(duì)“課程成績(jī)”工作表進(jìn)行打印設(shè)置。打印要求為：上、下頁(yè)邊距為3cm，左、右頁(yè)邊距為2cm，紙張采用B5紙橫向、水平居中打印。如果成績(jī)單需要分多頁(yè)打印，則每頁(yè)需要打印標(biāo)題（第1行到第4行）信息。2.實(shí)現(xiàn)過(guò)程

1）工作表制作完成如圖所示的工作表的樣式及內(nèi)容設(shè)置。操作步驟如下：①打開“相關(guān)素材.xlsx”工作簿，選擇“課程成績(jī)空白表”工作表，鼠標(biāo)定位在工作表名稱處，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“移動(dòng)或復(fù)制（M）…”。在如圖所示的“移動(dòng)或復(fù)制工作表”對(duì)話框中，選擇工作表移至“大學(xué)計(jì)算機(jī)成績(jī).xlsx”工作簿的“Sheet2”工作表之前，并勾選“建立副本”。2.實(shí)現(xiàn)過(guò)程

1）工作表制作完成如圖所示的工作表的樣式及內(nèi)容設(shè)置。操作步驟如下：②將“大學(xué)計(jì)算機(jī)成績(jī).xlsx”工作簿的“課程成績(jī)空白表”工作表重命名為“課程成績(jī)”工作表。③選擇“課程成績(jī)”工作表的C5:F5單元格區(qū)域，將其數(shù)據(jù)格式設(shè)為“百分比”，通過(guò)“減少小數(shù)位數(shù)”按鈕，設(shè)置小數(shù)位數(shù)為0，如圖所示。2.實(shí)現(xiàn)過(guò)程

1）工作表制作完成如圖所示的工作表的樣式及內(nèi)容設(shè)置。操作步驟如下：④選擇C6單元格，輸入“=”，切換到“課堂考勤成績(jī)”工作表，選擇O5單元格，按下回車鍵確認(rèn)輸入，C6單元格編輯欄區(qū)域的內(nèi)容為“=課堂考勤成績(jī)!O5”，表示C6單元格中的出勤成績(jī)引用自“課堂考勤成績(jī)”工作表的O5單元格。選擇C6單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成公式的復(fù)制。⑤在不改變表格格式的前提下將“相關(guān)素材.xlsx”工作簿的“成績(jī)數(shù)據(jù)素材”工作表中的課堂表現(xiàn)、課后實(shí)訓(xùn)、大作業(yè)以及期末考試成績(jī)復(fù)制到相應(yīng)位置。平時(shí)成績(jī)=∑?〖各項(xiàng)成績(jī)×各項(xiàng)成績(jī)比重〗，總評(píng)成績(jī)=平時(shí)成績(jī)×平時(shí)成績(jī)比重+期末成績(jī)×（1-平時(shí)成績(jī)比重）。由于在公式復(fù)制時(shí)，各項(xiàng)比重的引用地址不隨公式位置的變化而變化，因此需要使用絕對(duì)地址。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

2）計(jì)算平時(shí)成績(jī)及總評(píng)成績(jī)①選擇G6單元格，輸入“=”，用鼠標(biāo)選取C6單元格，輸入“*”，用鼠標(biāo)選取C5單元格，按下F4鍵，將C5轉(zhuǎn)換為絕對(duì)地址，此時(shí)，G6單元格編輯區(qū)域的內(nèi)容為“=C6*$C$5”。在此基礎(chǔ)上，輸入“+”，重復(fù)前面的操作，完成平時(shí)成績(jī)=∑?〖各項(xiàng)成績(jī)×各項(xiàng)成績(jī)比重〗的計(jì)算。G6單元格編輯區(qū)域的內(nèi)容最終為“=C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5”。②選擇I6單元格，完成總評(píng)成績(jī)的計(jì)算。I6單元格編輯區(qū)域的內(nèi)容最終為“=G6*$L$3+H6*(1-$L$3)”。2.實(shí)現(xiàn)過(guò)程

3）將平時(shí)成績(jī)和總評(píng)成績(jī)四舍五入為整數(shù)①選擇G6單元格編輯欄中“=”之后的內(nèi)容，按下Ctrl+X鍵，將平時(shí)成績(jī)計(jì)算公式（C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5）剪切下來(lái)。單擊“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組/“數(shù)學(xué)與三角函數(shù)”的ROUND函數(shù)，彈出如圖5-48所示的函數(shù)參數(shù)對(duì)話框。將鼠標(biāo)定位在Number區(qū)域，按下Ctrl+V鍵，將平時(shí)成績(jī)計(jì)算公式粘貼到此處。在Num_digits區(qū)域輸入0，單擊確定按鈕。G6單元格編輯區(qū)域的內(nèi)容最終為“=ROUND(C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5,0)”。2.實(shí)現(xiàn)過(guò)程

3）將平時(shí)成績(jī)和總評(píng)成績(jī)四舍五入為整數(shù)②對(duì)I6單元格中的內(nèi)容進(jìn)行四舍五入取整運(yùn)算。I6單元格編輯區(qū)域的內(nèi)容最終為“=ROUND(G6*$L$3+H6*(1-$L$3),0)”。③分別選擇G6和I6單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。課程績(jī)點(diǎn)計(jì)算公式

結(jié)果帶一位小數(shù)（四舍五入）。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

4）計(jì)算成績(jī)績(jī)點(diǎn)

課程績(jī)點(diǎn)計(jì)算公式

結(jié)果帶一位小數(shù)（四舍五入）。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

4）計(jì)算成績(jī)績(jī)點(diǎn)

③選擇J6單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。④選擇J6:J41單元格區(qū)域，設(shè)置其數(shù)值格式為“數(shù)字，帶1位小數(shù)”。由于總評(píng)成績(jī)>=90分計(jì)為A，總評(píng)成績(jī)>=80分計(jì)為B，總評(píng)成績(jī)>=70分計(jì)為C，總評(píng)成績(jī)>=60分計(jì)為D，其他計(jì)為F，因此可以用IF嵌套來(lái)實(shí)現(xiàn)，對(duì)應(yīng)的邏輯結(jié)構(gòu)如圖所示。2.實(shí)現(xiàn)過(guò)程

5）計(jì)算總評(píng)等級(jí)操作步驟如下：①選擇K6單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組/“邏輯”的IF函數(shù)，在對(duì)應(yīng)的參數(shù)區(qū)域輸入如表所示的第1行參數(shù)后，將光標(biāo)定位在Value_if_false區(qū)域，選擇需要嵌入的函數(shù)IF，將彈出第2個(gè)IF函數(shù)，如圖所示。2.實(shí)現(xiàn)過(guò)程

5）計(jì)算總評(píng)等級(jí)IF函數(shù)層次Logical_testValue_if_trueValue_if_false1I6>=90A光標(biāo)定位在此處，選擇第2層IF2I6>=80B光標(biāo)定位在此處，選擇第3層IF3I6>=70C光標(biāo)定位在此處，選擇第4層IF4I6>=60DF光標(biāo)定位在這里后進(jìn)行函數(shù)選擇②在第2個(gè)IF函數(shù)中對(duì)應(yīng)的參數(shù)區(qū)域輸入如表所示的第2行參數(shù)后，再將光標(biāo)定位在Value_if_false區(qū)域，選擇需要嵌入的函數(shù)IF，將彈出第3個(gè)IF函數(shù)。③在第3個(gè)IF函數(shù)中對(duì)應(yīng)的參數(shù)區(qū)域輸入如表所示的第3行參數(shù)后，再將光標(biāo)定位在Value_if_false區(qū)域，選擇需要嵌入的函數(shù)IF，將彈出第4個(gè)IF函數(shù)。④在第4個(gè)IF函數(shù)中對(duì)應(yīng)的參數(shù)區(qū)域輸入如表所示的第4行參數(shù)。K6單元格編輯欄的最終內(nèi)容為“=IF(I6>=90,"A",IF(I6>=80,"B",IF(I6>=70,"C",IF(I6>=60,"D","F"))))”。⑤選擇K6單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。2.實(shí)現(xiàn)過(guò)程

5）計(jì)算總評(píng)等級(jí)根據(jù)學(xué)生的總評(píng)成績(jī)進(jìn)行排名，分?jǐn)?shù)最高的排名第1。可以用RANK.EQ函數(shù)來(lái)實(shí)現(xiàn)，參與排位的數(shù)據(jù)區(qū)域需要使用絕對(duì)地址。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

6）總評(píng)成績(jī)排名①選擇L6單元格，單擊單元格編輯欄前的“插入函數(shù)”按鈕，在彈出的“插入函數(shù)”對(duì)話框中選擇“統(tǒng)計(jì)函數(shù)”的RANK.EQ函數(shù)。2.實(shí)現(xiàn)過(guò)程

6）總評(píng)成績(jī)排名②在如圖所示的RANK.EQ函數(shù)的Number參數(shù)處輸入“I6”，Ref參數(shù)處選擇單元格區(qū)域I6:I41，選擇Ref參數(shù)I6:I41，按下F4鍵，將其轉(zhuǎn)變?yōu)榻^對(duì)地址區(qū)域$I$6:$I$41，在Order參數(shù)處輸入“0”。L6單元格編輯欄的最終內(nèi)容為“=RANK.EQ(I6,$I$6:$I$41,0)”，表示按降序方式計(jì)算I6在單元格區(qū)域$I$6:$I$41中的排名。2.實(shí)現(xiàn)過(guò)程

6）總評(píng)成績(jī)排名③選擇L6單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制?？梢钥吹剑谒蠷ANK.EQ中，Number參數(shù)隨著單元格位置的變化而變化，而Ref的參數(shù)都為$I$6:$I$41，表示要計(jì)算的總是在$I$6:$I$41區(qū)域中的排名。將班級(jí)前10名的數(shù)據(jù)用灰色底紋標(biāo)識(shí)出來(lái)，可以用條件格式來(lái)實(shí)現(xiàn)。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

7）特別標(biāo)注信息①選擇單元格區(qū)域A6:L41，單擊“開始”選項(xiàng)卡/“格式”組/“條件格式”按鈕的“新建規(guī)則”。②在彈出的如圖所示的“新建格式規(guī)則”對(duì)話框中，選擇規(guī)則類型為“使用公式確定要設(shè)置格式的單元格”。單擊“為符合此公式的值設(shè)置格式”區(qū)域，選擇L6單元格，按下F4鍵兩次，編輯規(guī)則公式顯示為“=$L6”，在其后面輸入“<=10”，編輯規(guī)則公式為“=$L6<=10”。選擇“格式”，設(shè)置為“灰色底紋”，單擊“確定”按鈕。將班級(jí)前10名的數(shù)據(jù)用灰色底紋標(biāo)識(shí)出來(lái)，可以用條件格式來(lái)實(shí)現(xiàn)。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

7）特別標(biāo)注信息可以看到，總評(píng)排名為前10名的所有單元格都用灰色底紋進(jìn)行標(biāo)注了。公式“=$L6<=10”指明的條件格式為：如果對(duì)應(yīng)行的第L列的值<=10，則對(duì)所有符合條件的單元格區(qū)域用灰色底紋標(biāo)識(shí)出來(lái)了。由于平時(shí)成績(jī)、期末成績(jī)、總評(píng)成績(jī)、成績(jī)績(jī)點(diǎn)、總評(píng)等級(jí)以及總評(píng)排名等是通過(guò)公式和函數(shù)計(jì)算出來(lái)的，為防止誤修改，可以對(duì)這些指定的單元格進(jìn)行鎖定。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

8）利用審閱功能鎖定單元格①鼠標(biāo)單擊“課程成績(jī)”工作表的行標(biāo)題和列標(biāo)題交接處圖標(biāo)，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)置單元格格式”。②在如圖所示的“設(shè)置單元格格式”對(duì)話框中，切換到“保護(hù)”選項(xiàng)卡，不勾選“鎖定”和“隱藏”復(fù)選框，單擊確定。2.實(shí)現(xiàn)過(guò)程

8）利用審閱功能鎖定單元格③選擇不允許編輯的單元格區(qū)域（G6:G41，I6:L41），單擊鼠標(biāo)右鍵，選擇“設(shè)置單元格格式”，在格式設(shè)置中，勾選“鎖定”復(fù)選框。④選擇當(dāng)前工作表的任一單元格，切換到“審閱”選項(xiàng)卡/“保護(hù)”組/“保護(hù)工作表”按鈕，在彈出的如圖所示的“設(shè)置保護(hù)工作表”對(duì)話框中，選取“保護(hù)工作表及鎖定的單元格內(nèi)容”復(fù)選框，勾選“選取鎖定單元格”和“選定解除鎖定的單元格”復(fù)選框，清除其他復(fù)選框，單擊確定。這樣，平時(shí)成績(jī)等區(qū)域中的單元格就不能被編輯了。①單擊“頁(yè)面布局”選項(xiàng)卡/“頁(yè)面設(shè)置”組/“頁(yè)邊距”按鈕的“自定義邊距”，在彈出的“頁(yè)面設(shè)置”對(duì)話框的“頁(yè)邊距”選項(xiàng)卡中，設(shè)置上、下頁(yè)邊距為3cm，左、右頁(yè)邊距為2cm，勾選“水平”居中方式。②單擊“頁(yè)面布局”選項(xiàng)卡/“頁(yè)面設(shè)置”組/“紙張方向”按鈕的“橫向”。③單擊“頁(yè)面布局”選項(xiàng)框/“頁(yè)面設(shè)置”組/“紙張大小”按鈕的“B5（JIS）”。④單擊“頁(yè)面布局”選項(xiàng)卡/“頁(yè)面設(shè)置”組/“打印標(biāo)題”按鈕，在彈出的“頁(yè)面設(shè)置”對(duì)話框的“工作表”選項(xiàng)卡中，光標(biāo)定位在“頂端標(biāo)題行”輸入框，用鼠標(biāo)選定1-5行，“頂端標(biāo)題行”輸入框出現(xiàn)“$1:$5”，表示在打印的每一頁(yè)中，當(dāng)前工作表的1-5行都會(huì)被打印。⑤單擊“文件”選項(xiàng)卡/“打印”按鈕，可以看到成績(jī)表被分成兩頁(yè)打印，每頁(yè)都帶有工作表的1-5行的標(biāo)題信息。設(shè)置上、下頁(yè)邊距為3cm，左、右頁(yè)邊距為2cm，紙張采用B5紙橫向、水平居中打印。如果成績(jī)單需要分多頁(yè)打印，則每頁(yè)需要打印標(biāo)題（第1行到第5行）信息。操作步驟如下：2.實(shí)現(xiàn)過(guò)程

9）用頁(yè)面布局設(shè)置打印格式感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）5.4.3總結(jié)與提高第5章Excel基本應(yīng)用5.4課程成績(jī)計(jì)算（1）常規(guī)：這種格式的數(shù)字通常以鍵入的方式顯示。如果單元格的寬度不夠顯示整個(gè)數(shù)字，常規(guī)格式會(huì)用小數(shù)點(diǎn)對(duì)數(shù)字進(jìn)行四舍五入，常規(guī)數(shù)字格式還對(duì)較大的數(shù)字（12位或更多位）使用科學(xué)計(jì)數(shù)（指數(shù)）表示法。（2）數(shù)值：這種格式用于數(shù)字的一般表示。可以指定要使用的小數(shù)位數(shù)、是否使用千位分隔符以及如何顯示負(fù)數(shù)。（3）貨幣：這種格式用于一般貨幣值并顯示帶有數(shù)字的默認(rèn)貨幣符號(hào)?？梢灾付ㄒ褂玫男?shù)位數(shù)、是否使用千位分隔符以及如何顯示負(fù)數(shù)。（4）會(huì)計(jì)專用：這種格式也用于貨幣值，但是它會(huì)在一列中對(duì)齊貨幣符號(hào)和數(shù)字的小數(shù)點(diǎn)。（5）日期或時(shí)間：這種格式會(huì)根據(jù)指定的類型和區(qū)域設(shè)置（國(guó)家/地區(qū)），將日期和時(shí)間系列數(shù)值顯示為日期值或時(shí)間值。1.數(shù)字格式設(shè)置

Excel單元格中的數(shù)據(jù)，總是以某一種形式存在，這稱之為數(shù)字格式。Excel中常用的數(shù)字格式有：1.數(shù)字格式設(shè)置

（6）百分比：這種格式以百分?jǐn)?shù)形式顯示單元格的值，可以指定要使用的小數(shù)位數(shù)。（7）分?jǐn)?shù)：這種格式會(huì)根據(jù)指定的分?jǐn)?shù)類型以分?jǐn)?shù)形式顯示數(shù)字。（8）科學(xué)記數(shù)：這種格式以指數(shù)表示法顯示數(shù)字。（9）文本：這種格式將單元格的內(nèi)容視為文本，即使鍵入數(shù)字。（10）特殊：這種格式將數(shù)字顯示為郵政編碼、中文小寫數(shù)字或中文大寫數(shù)字。1.數(shù)字格式設(shè)置

在“開始”選項(xiàng)卡/“數(shù)字”組中提供了若干常用的數(shù)字格式按鈕，通過(guò)它們可以快速設(shè)置被選定單元格的數(shù)字格式：（1）貨幣格式

：將單元格的數(shù)字設(shè)置為會(huì)計(jì)專用格式，并自動(dòng)加上選擇的貨幣符號(hào)。（2）百分比格式

：將原數(shù)字乘以100后，再在數(shù)字后加上百分號(hào)。（3）會(huì)計(jì)分隔符格式：每3位數(shù)字以“，”隔開，并添加兩位小數(shù)。2.函數(shù)ROUND、ROUNDUP與ROUNDDOWN

ROUND函數(shù)按指定位數(shù)對(duì)數(shù)字進(jìn)行四舍五入。如輸入=round(3.158，2），則會(huì)出現(xiàn)數(shù)字3.16，即按兩位小數(shù)進(jìn)行四舍五入。ROUNDUP函數(shù)按指定位數(shù)向上舍入指定位數(shù)后面的小數(shù)。如輸入