第五章

電子表格處理軟件應(yīng)用5.41日常生活中，我們經(jīng)常需要處理大量數(shù)據(jù)，例如：學(xué)生學(xué)習(xí)成績總分和平均分的計(jì)算；銷售部門、財(cái)會(huì)部門的統(tǒng)計(jì)報(bào)表等，應(yīng)該怎樣通過電腦來幫助我們處理這些大量的數(shù)據(jù)呢？5.4計(jì)算數(shù)據(jù)2數(shù)據(jù)計(jì)算的兩種方法：使用自定義公式計(jì)算1使用函數(shù)計(jì)算231.公式的概念Excel的公式由運(yùn)算符、數(shù)值、字符串、變量和函數(shù)組成。公式必須以等號(hào)“=”開頭，即在Excel的單元格中，凡是以等號(hào)開頭的輸入數(shù)據(jù)都被認(rèn)為是公式。在等號(hào)的后面可以跟數(shù)值、運(yùn)算符、變量或函數(shù)，在公式中還可以使用括號(hào)。例如：=10+4*6/2+(2+1)*50就是公式，可以在任何單元格中輸入此公式．Excel會(huì)把公式的計(jì)算結(jié)果顯示在相應(yīng)的單元格中．使用自定義公式計(jì)算142、運(yùn)算符在Ｅxcel公式中可以使用運(yùn)算符。運(yùn)算符運(yùn)算功能優(yōu)先級(jí)()括號(hào)1-負(fù)號(hào)2%百分號(hào)3^乘方4*與/乘、除法5+與-加、減法6&文本連接7=、<、>、<=、>=、<>等于、小于、大于、小于等于、大于等于、不等于853、說明括號(hào)的運(yùn)算級(jí)別最高，在Excel的公式中只能使用小括號(hào)，無中括號(hào)和大括號(hào)。小括號(hào)可以嵌套使用，當(dāng)有多重小括號(hào)時(shí)，最內(nèi)層的表達(dá)式優(yōu)先運(yùn)算。同等級(jí)別的運(yùn)算符從左到右依次進(jìn)行?！?”為字符連接運(yùn)算，其作用是把前后的兩個(gè)字符串連接為一串。例如，"ABC"&"DEF"的結(jié)果為“ABCDEF”，"財(cái)務(wù)"&"經(jīng)濟(jì)"的結(jié)果為“財(cái)務(wù)經(jīng)濟(jì)”。6使用自定義公式計(jì)算1相對(duì)引用的例子【例1】某班某次期末考試成績?nèi)缦聢D所示，計(jì)算各同學(xué)的總分，即F列的數(shù)據(jù)。在F2計(jì)算公式=C2+D2+E2然后向下復(fù)制該公式！7使用自定義公式計(jì)算1單元格公式的引用：相對(duì)引用和絕對(duì)引用和混合引用相對(duì)引用是基于包含公式和引用的單元格的相對(duì)位置而言的。如果公式所在單元格的位置改變，引用也將隨之改變，如果多行或多列的復(fù)制公式，引用會(huì)自動(dòng)調(diào)整。默認(rèn)情況下，新公式使用相對(duì)引用。絕對(duì)引用則總是在指定位置引用單元格(例如＄F＄5).如果公式所在單元格的位置改變，絕對(duì)引用的單元格也始終保持不變，如果多行多列地復(fù)制公式，絕對(duì)引用將不作調(diào)整。混合引用在復(fù)制公式時(shí)，如果要求行不變但列可變或者列不變而行可變，那么就要用到混合引用。例如：＄F1則表示對(duì)F列的絕對(duì)引用和對(duì)第一行的相對(duì)引用

F＄1則表示對(duì)第一行的絕對(duì)引用和對(duì)A列的相對(duì)引用81.相對(duì)引用相對(duì)引用也稱為相對(duì)地址引用，是指在一個(gè)公式中直接用單元格的列標(biāo)與行號(hào)來取用某個(gè)單元格中的內(nèi)容。92.絕對(duì)引用絕對(duì)引用總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對(duì)引用保持不變。

絕對(duì)引用的形式是在引用單元格的列號(hào)與行號(hào)前面加“$”符號(hào)。比如，$A$1就是對(duì)A1單元格的絕對(duì)引用。10絕對(duì)引用案例【例1.3】某品牌皮鞋批發(fā)商3月份的銷售數(shù)據(jù)如圖所示，每雙皮鞋的單價(jià)相同，計(jì)算各皮鞋代銷商場(chǎng)應(yīng)支付的總金額，。在D6輸入公式：=$c$3*C6然后向下復(fù)制此公式！113.混合引用混合引用具有絕對(duì)列和相對(duì)行，或是絕對(duì)行和相對(duì)列。比如，$A1、$B1案例124.三維引用對(duì)同一工作薄內(nèi)不同工作表中相同引用位置的單元格或區(qū)域的引用稱為三維引用。引用形式為：Sheet1:Sheetn!單元格（區(qū)域）例如，Sheet1:sheet8!C5和Sheet1:Sheet8!B2:D6都是三維引用，前者包括Sheet1~sheet8這8個(gè)工作表中每個(gè)工作表的C5單元格，后者包括此8個(gè)工作表中每個(gè)工作表的B2:D6區(qū)域。135、內(nèi)部引用與外部引用

1）引用相同工作表中的單元格例：=G3+G5+G10*102）引用同一工作簿不同工作表中的單元格例=Sheet1!G3+Sheet2!G5+Sheet2!E273）引用已打開的不同工作簿中的單元格例：=[Book1]Sheet1!$IL$4+[Book2]Sheet2!$E$74）引用未打開的不同工作薄中的單元格，='C:\dk\[Book1.xlsx]Sheet2'!$B$4+'C:\dk\[Book1.xlsx]Sheet1'!$C$65）同一公式中存在幾中不同的引用，例：=[Book1]Sheet1!$A$4+Sheet1!G7+F9141、函數(shù)的概念函數(shù)是Excel已經(jīng)定義好了的一些特殊公式，它們可以對(duì)一個(gè)或多個(gè)數(shù)據(jù)進(jìn)行計(jì)算，然后把計(jì)算的結(jié)果存放在某個(gè)單元格中。2、Excel函數(shù)類型工作表函數(shù)、財(cái)務(wù)函數(shù)、日期函數(shù)、時(shí)間函數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)類函數(shù)、數(shù)據(jù)庫管理函數(shù)、文本函數(shù)及信息類函數(shù)等。使用函數(shù)計(jì)算2151、SUM和Average用法：sum(x1,x2,x3……x255)Average(x1,x2,x3……x255)其中，x1,x2……x255可以是數(shù)字，單元格或單元格區(qū)域的引用，也可以是表達(dá)式。功能：

sum求所有參數(shù)x1,x2……x255的總和．Average求所有參數(shù)x1,x2……x255的平均值常用工作表函數(shù)16Sum和Average的應(yīng)用案例某單位的工資數(shù)據(jù)如下圖所示，已知基本工資、獎(jiǎng)金、水費(fèi)、電費(fèi)，計(jì)算總額、總計(jì)與平均數(shù)17２、max和min用法：max(x1,x2,x3……x255)min(x1,x2,x3……x255)其中，x1,x2……x255可以是數(shù)字，單元格或單元格區(qū)域的引用，也可以是表達(dá)式。功能：max求所有參數(shù)x1,x2……x255的最大值．min求所有參數(shù)x1,x2……x255的最小值常用工作表函數(shù)18３、count用法：count(x1,x2,x3……x255)其中，x1,x2……x255可以是數(shù)字，單元格或單元格區(qū)域的引用，也可以是表達(dá)式。功能：統(tǒng)計(jì)x1,x2……x255中數(shù)字的個(gè)數(shù)．常用工作表函數(shù)191、要是寫成=COUNT(B1,D1)，那就是計(jì)算機(jī)B1和D1兩個(gè)單元格中有幾個(gè)數(shù)字（不包括C1單元格），2、但是如果我寫成=COUNT(B1:D1)，注意，中間用冒號(hào)了，那就是計(jì)算機(jī)從B1單元格到D1單元格中數(shù)字的個(gè)數(shù)了，（這就包括數(shù)字單元格了）3、再有，我寫成=COUNT("B1","D1","123","hello")，那結(jié)果就是1,因?yàn)橹挥?123"一個(gè)數(shù)字，B1和D1因?yàn)榧恿艘?hào)，所以是字符了，不是單元格。4、如果A1為1，A5為3，A7為2，其他均為空，則：COUNT(A1:A7)等于3備注：計(jì)算出A1到A7中，數(shù)字的個(gè)數(shù)COUNT(A4:A7)等于2備注：計(jì)算出A4到A7中，數(shù)字的個(gè)數(shù)COUNT(A1:A7,2)等于4備注：計(jì)算A1到A7單元格和數(shù)字2一起，一共是多少個(gè)數(shù)字（A1到A7中有3個(gè)，加上數(shù)字2，一共4個(gè)）count例：20count與counta都是：返回非空單元格的個(gè)數(shù)區(qū)別：count當(dāng)單元格內(nèi)容是數(shù)值時(shí)起作用；counta無論單元格是什麼內(nèi)容都起作用；例：AB1-12a2-12asd3-13a4-14asd5-15a6-16asd7-17a8-18asd9-1912310asdasd1121aCOUNT(A1:A11)=10COUNTA(B1:B11)=11214、countif

用法：COUNTIF(單元格區(qū)域,“條件”)其中條件可以是比較式，但必須用雙引號(hào)括起來．如“>90”，“<=60”

功能:

統(tǒng)計(jì)指定單元格區(qū)域中滿足條件的數(shù)字的個(gè)數(shù)．COUNTIF函數(shù)應(yīng)用舉例假設(shè)存在如下圖的學(xué)生成績表，現(xiàn)在，讓我們一起來使用COUNTIF函數(shù)進(jìn)行統(tǒng)計(jì)，下面統(tǒng)計(jì)的是不及格的人數(shù):22如下圖，首先，選擇F列中的F3單元格，之后，在其上方fx函數(shù)輸入?yún)^(qū)域，直接輸入=COUNTIF(E3:E55,“<60“)，之后按回車鍵確認(rèn)，這樣，就得到不及格的人數(shù)了！=COUNTIF(E3:E55,“<60“)分析：E3:E55代表統(tǒng)計(jì)的數(shù)據(jù)區(qū)域是E3到E55，“<60“統(tǒng)計(jì)的條件是成績小于60；知識(shí)擴(kuò)展：“<60“改為：“<80“是什么意思呢？表示的是，統(tǒng)計(jì)分?jǐn)?shù)小于80的人數(shù)；“<60“改為“>60“是什么意思呢？表示的是：統(tǒng)計(jì)及格的人數(shù)！23５、IF用法IF(條件,表達(dá)式1,表達(dá)式2)其中表達(dá)式可以是數(shù)字、函數(shù)、單元格或單元格區(qū)域。功能條件成立時(shí)，函數(shù)結(jié)果為表達(dá)式１的值；條件不成立時(shí)，函數(shù)的結(jié)果為表達(dá)式２的值．常用工作表函數(shù)24

excel函數(shù)中if函數(shù)的使用非常廣泛，特別是在單條件判斷的時(shí)候，用好if函數(shù)可以幫我們完成很多功能。最簡單的excelif函數(shù)應(yīng)用例：又圖數(shù)據(jù)在d列顯示如下結(jié)果：如果數(shù)據(jù)1大于60則顯示合格，否則顯示不合格。那么在d2單元格輸入以下公式：=if(a2>60,"合格","不合格")然后向下拖拽，自動(dòng)生成數(shù)據(jù)，如下圖D列效果。25if函數(shù)必須的條件：1.每一個(gè)excelif函數(shù)必須使用英文的括號(hào)括起來；2.括號(hào)內(nèi)為三個(gè)數(shù)據(jù)，第一個(gè)數(shù)據(jù)是條件（如上例中的a2>60），第二數(shù)據(jù)為滿足第一個(gè)數(shù)據(jù)后返回的結(jié)果，通常使用英文的引號(hào)括起來，第三個(gè)數(shù)據(jù)是不滿足第一個(gè)數(shù)據(jù)時(shí)需要返回的結(jié)果；經(jīng)常出現(xiàn)的錯(cuò)誤：其中的符號(hào)如逗號(hào)和引號(hào)皆為英文（也就是所謂的半角），若輸入中文符號(hào)則錯(cuò)誤；if的右括號(hào)放在了條件的后面；（這是在多個(gè)條件使用if函數(shù)進(jìn)行嵌套時(shí)非常容易犯的錯(cuò)誤）26if函數(shù)嵌套用法（七層）例：下圖數(shù)據(jù)，在e列顯示如下結(jié)果：如果A列的數(shù)據(jù)1小于60則顯示不合格，如果大于等于60而小于80則顯示合格，如果大于等于80而小于90顯示良好，如果大于等于90則顯示優(yōu)秀。這是經(jīng)典的if嵌套應(yīng)用例子，需要我們使用if函數(shù)的嵌套。if嵌套書寫前，首先你要理解要求，并將要求數(shù)學(xué)化，也就是使用數(shù)學(xué)的模式表達(dá)出來，if函數(shù)多重嵌套一般情況下我們可以將它看做分段函數(shù)，那么問題就很容易解決了。例子可以在E2單元格使用如下代碼：=if(a2<60,"不合格",if(a2<80,"合格",if(a2<90,"良好","優(yōu)秀")))當(dāng)數(shù)據(jù)1小于60時(shí)，顯示不合格，這時(shí)在不合格逗號(hào)的右側(cè)默認(rèn)就是>=60的情況，那么根據(jù)題意，只需再滿足<80即可顯示合格，于是我們將最簡單的if函數(shù)的第三個(gè)數(shù)據(jù)變成了一個(gè)if函數(shù)，依次類推，每一次可以將一個(gè)if函數(shù)作為每一個(gè)基本函數(shù)的第三個(gè)數(shù)據(jù)，從而形成多種嵌套。（圖例中多余在最后一個(gè)if前后加了一個(gè)括號(hào)，當(dāng)然這種方法也正確，但不是最簡單的。）27其實(shí)還有另一種寫法，也就是將嵌套的if寫在基本if函數(shù)的第二個(gè)數(shù)據(jù)的位置，如下圖，不過這種寫法不常用，也比較不好理解，并且容易寫錯(cuò)，不推薦大家使用。28INT函數(shù)的用法INT函數(shù)的用法是將數(shù)值向下取整為最接近的整數(shù).使用INT函數(shù)注意三點(diǎn)：第一，INT函數(shù)是取整；第二，小數(shù)部分不進(jìn)行四舍五入，直接去掉。第三，INT函數(shù)處理負(fù)數(shù)的小數(shù)時(shí)總是向上進(jìn)位，這一點(diǎn)和TRUNC函數(shù)不同。例如：=INT(18.89)，結(jié)果為18。=INT(-5.8)，結(jié)果為-6。=TRUNC(-5.8)，結(jié)果為-5。29INT函數(shù)具體使用舉例：取整求和對(duì)上表中員工的工資小數(shù)部分直接去掉，不進(jìn)行四舍五入，取整然后求總共需要發(fā)放的工資是多少。解題思路：先用INT函數(shù)對(duì)數(shù)據(jù)進(jìn)行取整，然后再求和。在B6單元格輸入下面公式之一均可：普通公式：=SUM(INT(B2),INT(B3),INT(B4),INT(B5))數(shù)組公式：=SUM(INT(B2:B5))數(shù)組公式需要按下ctrl+shift+enter三鍵結(jié)束。顯然數(shù)組公式比普通公式簡潔許多。30Excel中mod函數(shù)是一個(gè)求余函數(shù)。返回兩數(shù)相除的余數(shù)。MOD函數(shù)一、兩個(gè)異號(hào)整數(shù)求余1.函數(shù)值符號(hào)規(guī)律(余數(shù)的符號(hào))mod(負(fù),正)=正mod(正,負(fù))=負(fù)結(jié)論：兩個(gè)整數(shù)求余時(shí)，其值的符號(hào)為除數(shù)的符號(hào)。

2.取值規(guī)律先將兩個(gè)整數(shù)看作是正數(shù)，再作除法運(yùn)算①能整除時(shí)，其值為0②不能整除時(shí)，其值=除數(shù)×(整商+1)-被除數(shù)例：mod(5,-4)=-3即：5除以4的整數(shù)商為1，加1后為2;其與除數(shù)之積為8;再與被除數(shù)之差為(5-8=-3);取除數(shù)的符號(hào)。所以值為-3。MOD函數(shù)使用大致分為下面兩種情況。31二、兩個(gè)小數(shù)求余取值規(guī)律：被除數(shù)-(整商×除數(shù))之后在第一位小數(shù)位進(jìn)行四舍五入。例：mod(9,1.2)=1即：9除1.2其整商為7;7與除數(shù)1.2之積為8.4;8.4四舍五入之后為8;被除數(shù)9與8之差為1。故結(jié)果為1。例：mod(9,2.4)=0即：9除2.2其