計(jì)算機(jī)應(yīng)用基礎(chǔ)課件第5章Excel基本應(yīng)用課件

5.1項(xiàng)目分析張老師需要用表格來(lái)記錄他班上全部學(xué)生的基本信息。（1）能夠按教務(wù)處學(xué)號(hào)編排要求，為學(xué)生快速編排學(xué)號(hào)。（2）根據(jù)學(xué)生身份證號(hào)碼自動(dòng)計(jì)算出生日期。（3）能夠某些單元格的輸入內(nèi)容。（4）計(jì)算每個(gè)學(xué)生的總成績(jī)及新生獎(jiǎng)學(xué)金。（5）將某些特別關(guān)注的信息進(jìn)行特殊標(biāo)識(shí)。5.1項(xiàng)目分析學(xué)號(hào)自動(dòng)編排從身份證號(hào)碼中提取自動(dòng)計(jì)算特別標(biāo)注選擇輸入錯(cuò)誤提示5.1項(xiàng)目分析

此外，張老師本學(xué)期還擔(dān)任該班的“計(jì)算機(jī)應(yīng)用”課程教學(xué)，他需要制作學(xué)生成績(jī)登記表。（1）根據(jù)考勤情況，自動(dòng)計(jì)算考勤成績(jī)。（2）根據(jù)考核方式，自動(dòng)計(jì)算出總評(píng)成績(jī)。（3）將總評(píng)成績(jī)轉(zhuǎn)換為對(duì)應(yīng)等級(jí)。（4）根據(jù)總評(píng)成績(jī)，計(jì)算排名。（5）將不及格及曠考名單特別標(biāo)注。5.1項(xiàng)目分析自動(dòng)計(jì)算信息統(tǒng)計(jì)自動(dòng)排名整行數(shù)據(jù)特別標(biāo)注5.2制作班級(jí)信息登記表

5.2.1知識(shí)點(diǎn)解析認(rèn)識(shí)EXCEL2010編輯框名稱框選項(xiàng)卡組按鈕按鈕組5.2制作班級(jí)信息登記表

5.2.1知識(shí)點(diǎn)解析單元格地址與單元格選擇C4單元格如果要選擇多個(gè)連續(xù)的單元格，可以先選擇第1個(gè)單元格（如B3），按下鼠標(biāo)左鍵，拖動(dòng)鼠標(biāo)到最后1個(gè)需要被選擇的單元格（如E4），松開(kāi)鼠標(biāo)?？梢钥吹?，由第1個(gè)和最后1個(gè)單元格的行、列標(biāo)題所構(gòu)成的矩形區(qū)域中的所有單元格（稱為單元格區(qū)域）都被選擇，而在名稱框中顯示的是第1個(gè)被選擇的單元格的地址。標(biāo)記為B3:E45.2制作班級(jí)信息登記表

5.2.1知識(shí)點(diǎn)解析設(shè)置單元格格式可以通過(guò)“開(kāi)始”選項(xiàng)卡/“字體”組來(lái)設(shè)置被選擇的單元格（或單元格區(qū)域）的字體、字號(hào)、顏色、邊框等。可以通過(guò)“開(kāi)始”選項(xiàng)卡/“對(duì)齊方式”組來(lái)設(shè)置被選擇的單元格（或單元格區(qū)域）中的內(nèi)容相對(duì)于單元格的對(duì)齊方式，單元格內(nèi)容的多行顯示、合并居中（即將多個(gè)單元格合并成1個(gè)單元格）等?？梢酝ㄟ^(guò)“開(kāi)始”選項(xiàng)卡/“數(shù)字”組來(lái)設(shè)置被選擇的單元格（或單元格區(qū)域）的數(shù)字顯示格式?？梢酝ㄟ^(guò)“開(kāi)始”選項(xiàng)卡/“單元格”組來(lái)設(shè)置被選擇的單元格（或單元格區(qū)域）的行高和列高等。字體字號(hào)顏色邊框垂直居中水平居中自動(dòng)換行合并后居中數(shù)字格式行高列高5.2制作班級(jí)信息登記表

5.2.2任務(wù)實(shí)現(xiàn)要求與效果制作如圖所示的表格，要求：（1）標(biāo)題位于表頭的中間。（2）表格邊框?yàn)楹谏p細(xì)實(shí)線，內(nèi)框?yàn)楹谏摼€。（3）列標(biāo)題文字居中對(duì)齊。（4）單元格為合適的行高和列寬。（5）打印表格時(shí)頁(yè)面水平居中。5.2制作班級(jí)信息登記表

5.2.2任務(wù)實(shí)現(xiàn)制作如圖所示的表格，要求：（1）標(biāo)題位于表頭的中間。（2）表格邊框?yàn)楹谏p細(xì)實(shí)線，內(nèi)框?yàn)楹谏摼€。（3）列標(biāo)題文字居中對(duì)齊。（4）設(shè)置單元格的行高和列寬。請(qǐng)參照書上步驟，自行完成5.2制作班級(jí)信息登記表

5.2.2總結(jié)與提高1.根據(jù)模板生成Excel文檔在Excel中，可以根據(jù)系統(tǒng)提供多種模板來(lái)創(chuàng)建自己的工作簿，從而簡(jiǎn)化電子表格的排版工作。可以將工作簿保存為Excel97-2003工作簿類型，以方便其他低版本的Excel打開(kāi)。但某些Excel2010所獨(dú)特具備的功能，在低版本中將不能使用。模板5.2制作班級(jí)信息登記表

5.2.2總結(jié)與提高2.單元格區(qū)域選擇對(duì)于連續(xù)單元格的選擇，也可以在選擇起始單元格后，按住Shift鍵，選擇最后一個(gè)單元格。對(duì)于非連續(xù)單元格的選擇，可以在選擇起始單元格后，按下Ctrl鍵不松手，再用鼠標(biāo)選擇其他單元格，直到選擇完全部單元格后，松開(kāi)Ctrl鍵。非連續(xù)單元格區(qū)域5.2制作班級(jí)信息登記表

5.2.2總結(jié)與提高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ù)制。格式刷1.選擇第1個(gè)單元格（B3)2.按下“格式刷”按鈕3.在需要設(shè)置的單元格（C3)上單擊鼠標(biāo)左鍵（或按下鼠標(biāo)左鍵，不松開(kāi)，選擇需要設(shè)置格式的單元格區(qū)域）5.2制作班級(jí)信息登記表

5.2.2總結(jié)與提高4.對(duì)齊方式單元格中的內(nèi)容相對(duì)于單元格邊框左右的對(duì)齊方式稱為“水平對(duì)齊”，相對(duì)于單元格邊框上下的對(duì)齊方式稱為“垂直對(duì)齊”。“自動(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.2制作班級(jí)信息登記表

5.2.2總結(jié)與提高5.數(shù)字格式設(shè)置在“開(kāi)始”選項(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ù)字以“，”隔開(kāi)，并添加兩位小數(shù)。5.2制作班級(jí)信息登記表

5.2.3總結(jié)與提高6.自動(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)容的分行顯示。軟換行硬換行5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析文本數(shù)據(jù)的輸入在單元格A1中，輸入你自己的身份證號(hào)碼（如。你會(huì)發(fā)現(xiàn)，輸入的身份證號(hào)碼顯示為4.32302E+17；單元格編輯欄的內(nèi)容變成是因?yàn)镋xcel工作表的單元格，默認(rèn)的數(shù)據(jù)類型是“常規(guī)”，在單元格中輸入時(shí)，系統(tǒng)自動(dòng)判斷其為數(shù)字型數(shù)據(jù)，因此以科學(xué)計(jì)算法來(lái)表示該數(shù)據(jù)。5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析文本數(shù)據(jù)的輸入解決方案1）在數(shù)字型文本數(shù)據(jù)前添加英文單引號(hào)“’”。（2）將該單元格數(shù)字格式設(shè)置為“文本”。（5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析公式與單元格引用由于李春的總工資等于B2單元格的值加上C2單元格的值，因此D2單元格輸入的公式為“=B2+C2”。這表明D2單元格引用了B2單元格的數(shù)據(jù)和C2單元格的數(shù)據(jù)，將他們的和作為自己?jiǎn)卧竦膬?nèi)容。公式是Excel工作表中進(jìn)行數(shù)值計(jì)算的等式。5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析公式與單元格引用要計(jì)算許偉嘉的總工資，可以將D2單元格的內(nèi)容復(fù)制到D3單元格?？梢钥吹?，D3單元格的公式為“=B3+C3”。在進(jìn)行公式復(fù)制時(shí)，公式中引用的地址發(fā)生了變化，這是因?yàn)橐肂3和C3單元格時(shí)，使用的是“相對(duì)引用”。“相對(duì)引用”就是公式中引用的單元格隨公式所在單元格位置的變化而變化。這是因?yàn)镈2中的公式“=B2+C2”表明“D2的值是等于當(dāng)前行所對(duì)應(yīng)的B列單元格加上C列單元格的和”，因此，將D2單元格中的公式復(fù)制到D3后，系統(tǒng)認(rèn)為“D3的值也應(yīng)該等于當(dāng)前行所對(duì)應(yīng)的B列單元格加上C列單元格的值”，因此公式自動(dòng)變化為“=B3+C3”。5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析MID函數(shù)如果C3單元格中存的是身份證號(hào)碼，提取身份證號(hào)碼的7～14位出生日期的函數(shù)是：MID(C3,7,8)5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析MID函數(shù)Excel函數(shù)是系統(tǒng)預(yù)先定義的，使用一些稱為參數(shù)的特定數(shù)值來(lái)執(zhí)行計(jì)算、分析等任務(wù)的特殊公式，并返回一個(gè)或多個(gè)值。MID函數(shù)是從文本字符串中的指定位置返回指定長(zhǎng)度的字符。語(yǔ)法格式為：

MID(Text,Start_num,Num_chars)所對(duì)應(yīng)的參數(shù)含義分別是： Text：準(zhǔn)備從中提取字符串的文本單元格或文本內(nèi)容； Start_num：準(zhǔn)備從Text提取字符串的起始位置； Num_chars：所要提取的字符個(gè)數(shù)。5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析IF函數(shù)公司本月的績(jī)效工資與出勤有關(guān)：全勤（出勤天數(shù)>=22）員工的績(jī)效工資為2520元，非全勤員工的績(jī)效工資為100元/天×出勤天數(shù)。5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析IF函數(shù)IF函數(shù)的語(yǔ)法格式為： IF(Logical_test,Value_if_true,Value_if_false)三個(gè)參數(shù)的含義分別是： Logical_test：判斷邏輯表達(dá)式； Value_if_true：如果邏輯表達(dá)式的值為真，則返回該值； Value_if_false：如果邏輯表達(dá)式的值為假，則返回該值。5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析IF函數(shù)根據(jù)給定的條件Logical_test:(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)）。5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析IF函數(shù)

1.如果B3單元格中的數(shù)據(jù)大于等于100，返回結(jié)果為“GOOD”,否則為“BAD”的公式是？

2.如果B3單元格中的數(shù)據(jù)是“男”，返回結(jié)果為“male”,否則為“female”的公式是的公式是？

3.如果B3單元格中的數(shù)據(jù)大于100并且小于等于200，返回結(jié)果為1,否則為0的公式是？

4.如果B3單元格中的數(shù)據(jù)小于等于100或者大于200，返回結(jié)果為“真”,否則為“假”的公式是？=IF(B3>100,"GOOD","BAD")=IF(B3="男","male","female")=IF(AND(B3>100,B3<=200),1,0)=IF(OR(B3<=100,B3>200),"真","假")5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析有效性設(shè)置將數(shù)據(jù)限制為一個(gè)特定的類型，如整數(shù)、分?jǐn)?shù)或文本，并且限制其取值范圍。例如，如果希望在C3單元格中輸入身份證號(hào)碼時(shí)，必須輸入18位字符，可以將其有效性設(shè)置為“允許的文本長(zhǎng)度為18”。通過(guò)“數(shù)據(jù)”選項(xiàng)卡/“數(shù)據(jù)工具”組/“數(shù)據(jù)有效性”按鈕設(shè)置5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析自動(dòng)填充當(dāng)表格中的行或列的部分?jǐn)?shù)據(jù)形成了一個(gè)序列時(shí),以使用Excel提供的自動(dòng)填充功能來(lái)快速填充數(shù)據(jù)。選擇可以形成序列的單元格或單元格區(qū)域后，移動(dòng)鼠標(biāo)到最后1個(gè)選擇單元格的右下角，當(dāng)鼠標(biāo)變成“填充句柄”時(shí)，就可以用鼠標(biāo)拖動(dòng)它來(lái)進(jìn)行自動(dòng)填充填充句柄5.3班級(jí)信息錄入設(shè)置

5.3.1知識(shí)點(diǎn)解析條件格式如果需要根據(jù)單元格或單元格區(qū)域的內(nèi)容有選擇地自動(dòng)應(yīng)用格式,可以通過(guò)“開(kāi)始”選項(xiàng)卡/“樣式”組/“條件格式”按鈕

來(lái)進(jìn)行設(shè)置。例如，將單元格區(qū)域A1:D1中所以大于100的單元格用紅色字體標(biāo)注出來(lái)5.3班級(jí)信息錄入設(shè)置

5.3.2任務(wù)實(shí)現(xiàn)5.3班級(jí)信息錄入設(shè)置

5.3.3任務(wù)實(shí)現(xiàn)為“班級(jí)信息登記表”中添加下面功能，要求：（1）在表中輸入身份證號(hào)碼，“出生日期”欄中便自動(dòng)計(jì)算出生日期。（2）“性別”欄只能選擇輸入“男”或“女”。（3）“宿舍”欄中只能選擇輸入該班級(jí)已分配的宿舍。（4）“高考成績(jī)”和“入學(xué)成績(jī)”輸入分值范圍為0~750。（5）用公式計(jì)算總成績(jī)（=高考成績(jī)+入學(xué)成績(jī)）。（6）自動(dòng)計(jì)算新生獎(jiǎng)學(xué)金（總成績(jī)?cè)?200分以上的學(xué)生獲新生獎(jiǎng)學(xué)金1500元）。（7）將每個(gè)學(xué)生對(duì)應(yīng)的“出生日期”、“總成績(jī)”以及“新生獎(jiǎng)學(xué)金”項(xiàng)，設(shè)置為不允許編輯狀態(tài)。（8）將排名前10名的總成績(jī)單元格和性別為“女”的單元格用突出顏色標(biāo)注出來(lái)。（9）當(dāng)新生獎(jiǎng)學(xué)金為0時(shí)，不顯示0值。請(qǐng)參照書上步驟，自行完成5.3班級(jí)信息錄入設(shè)置

5.3.3總結(jié)與提高1.出生日期格式如果希望出生日期顯示的樣式為“1994.10.08”，怎么實(shí)現(xiàn)？（1）取出“1994”（2）在“1994”后面加入“.”（3）取出“10”（4）在“10”后面加入“.”（5）取出“08”（6）在“08”后面加入“.”=MID(C3,7,4)&"."&MID(C3,11,2)&"."&MID(C3,13,2)文本連接符請(qǐng)將班級(jí)信息表中的出生日期顯示為“10-08-1994”格式5.3班級(jí)信息錄入設(shè)置

5.3.3總結(jié)與提高2.選擇性粘貼格式：僅復(fù)制源區(qū)域中的格式，而不包括內(nèi)容。值：將文本、數(shù)值、日期及公式結(jié)果粘貼到目標(biāo)區(qū)域，不復(fù)制格式。無(wú)邊框：粘貼全部?jī)?nèi)容，僅去掉源區(qū)域中的邊框。5.3班級(jí)信息錄入設(shè)置

5.3.3總結(jié)與提高3.自動(dòng)填充方法1：黑色十字，拖動(dòng)鼠標(biāo)方法2：黑色十字，雙擊鼠標(biāo)左鍵方法3：“開(kāi)始”選項(xiàng)卡/“編輯”組/“填充”按鈕/“系列”5.3班級(jí)信息錄入設(shè)置

5.3.3總結(jié)與提高4.條件格式EXCEL2010中，對(duì)每個(gè)工作表最多可以設(shè)置64個(gè)條件格式。對(duì)同一個(gè)單元格（或單元格區(qū)域），如果應(yīng)用有兩個(gè)或兩個(gè)以上的不同條件格式，則：（1）條件格式中沒(méi)有一個(gè)條件為真，單元格格式不發(fā)生變化；（2）條件格式中不止一個(gè)條件為真，單元格格式設(shè)置為第一個(gè)條件為真的格式。可以通過(guò)公式來(lái)設(shè)定更為復(fù)雜的單元格條件格式。例如，希望將性別為女，總成績(jī)?cè)?200分以上的學(xué)生姓名標(biāo)識(shí)出來(lái)。對(duì)于第3行的姓名，如果性別單元格E3為女，并且總成績(jī)單元格I3>1200，就需要將B3單元格標(biāo)注處理。因此，應(yīng)該針對(duì)B3單元格應(yīng)用條件格式，條件為：AND(E3="女",I3>=1200)操作請(qǐng)參見(jiàn)書上步驟5.3班級(jí)信息錄入設(shè)置

5.3.3總結(jié)與提高5.數(shù)據(jù)導(dǎo)入將文本文件內(nèi)容導(dǎo)入工作表需要注意的幾點(diǎn)：（1）文本是用什么進(jìn)行分隔的？（2）導(dǎo)入到的單元格的數(shù)據(jù)類型？5.4班級(jí)信息統(tǒng)計(jì)

5.4.1知識(shí)點(diǎn)解析常用函數(shù)要將表格標(biāo)題和“學(xué)號(hào)”列一直在屏幕顯示，應(yīng)該將光標(biāo)定位在那個(gè)單元格，然后進(jìn)行“凍結(jié)拆分窗口”操作？第1-2行及第A列一直顯示，所以應(yīng)定位在第2行和第1列交接處的右下方單元格—B35.4班級(jí)信息統(tǒng)計(jì)

5.4.1知識(shí)點(diǎn)解析常用函數(shù)開(kāi)始”選項(xiàng)卡/“編輯”組/“求和”按鈕

中提供了函數(shù)的快速調(diào)用求和函數(shù)SUM(Number1[,number…])求平均值函數(shù)AVERAGE(Number1[,number…])求最大值函數(shù)MAX(Number1[,number…])求最小值函數(shù)AVERAGE(Number1[,number…])5.4班級(jí)信息統(tǒng)計(jì)

5.4.1知識(shí)點(diǎn)解析常用單元格統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)包含數(shù)字的單元格數(shù)目函數(shù)COUNT(Value1[,Value2…])統(tǒng)計(jì)非空單元格數(shù)目函數(shù)COUNTA(Value1[,Value2…])統(tǒng)計(jì)符合條件的單元格數(shù)目COUNTIF(Range,Criteria)，其中Range為要統(tǒng)計(jì)的單元格區(qū)域，Criteria為指定的條件。（1）針對(duì)B3:B30姓名區(qū)域統(tǒng)計(jì)學(xué)生人數(shù)的公式是?（2）針對(duì)E3:E30性別區(qū)域統(tǒng)計(jì)女生的公式是?（3）針對(duì)J3:J30新生獎(jiǎng)學(xué)金區(qū)域統(tǒng)計(jì)獲得獎(jiǎng)學(xué)金人數(shù)的公式是?=COUNTA(B3:B30)=COUNTIF(E3:E30,"女")=COUNTIF(J3:J30,">0")針對(duì)J3:J30新生獎(jiǎng)學(xué)金區(qū)域統(tǒng)計(jì)獲得獎(jiǎng)學(xué)金人數(shù)的公式可以用“=COUNT(J3:J30)”或“=COUNTA(J3:J30)”?為什么5.4班級(jí)信息統(tǒng)計(jì)

5.4.1知識(shí)點(diǎn)解析保護(hù)工作表如果你制作好了一張工作表，能否做到只讓別人查看而無(wú)法修改？如果你是老板，是否希望員工只填寫他們可以填寫的區(qū)域？通過(guò)“審閱”選項(xiàng)卡/“更改組”“保護(hù)工作表”按鈕

，可以讓整個(gè)工作表處于“只讀”狀態(tài)，也可以讓工作表的部分區(qū)域可以編輯，而其他區(qū)域被保護(hù)為“只讀”狀態(tài)。由于在設(shè)置“保護(hù)工作表”時(shí)，Excel會(huì)根據(jù)每個(gè)單元格是否被鎖定來(lái)確定是否允許編輯。而所有單元格默認(rèn)是被鎖定的，因此，為了只對(duì)某些單元格進(jìn)行鎖定，需要：（1）先將所有單元格設(shè)置為不鎖定。（2）將不允許編輯的單元格設(shè)置為鎖定。