excel中的公式計(jì)算

1、.公式的靈活性是通過單元格的引用來實(shí)現(xiàn)的。引用的作用在于標(biāo)識(shí)工作表上的單元格或單元格區(qū)域,并指明公式中所使用的數(shù)據(jù)的位置。如上例,單元格Bl中的公式"=A1*A2/2"中出現(xiàn)了其他單元格的引用地址Al和A2,這就是在該公式中引用了其他單元格或區(qū)域中的數(shù)據(jù)。這種方式使得在Excel的公式中不僅可以使用常數(shù)、運(yùn)算符,還可以使用其他單元格或區(qū)域中的數(shù)據(jù)。并且當(dāng)引用的源單元格中的數(shù)據(jù)發(fā)生變化時(shí),公式中的引用數(shù)據(jù)也隨之變化。被引用的單元格中的數(shù)據(jù)中不能含有"$"、"￥"或"，"可以引用同一工作表中的其他單元格或區(qū)域,還可以引

2、用其他工作表甚至其他工作簿的工作表中的單元格或區(qū)域。根據(jù)單元格引用被復(fù)制到其他單元格時(shí)是否會(huì)發(fā)生改變,單元格的引用分為相對(duì)引用、絕對(duì)引用和混合引用。(1)相對(duì)引用相對(duì)引用是指當(dāng)把一個(gè)含有單元格引用的公式復(fù)制或填充到一個(gè)新的位置時(shí),公式中的單元格引用會(huì)隨著目標(biāo)單元格位置的改變而相對(duì)改變。Excel中默認(rèn)的單元格引用為相對(duì)引用。單元格相對(duì)引用:由單元格的列標(biāo)行號(hào)表示。如A1,引用了第A列與第1行交叉處的單元格。單元格區(qū)域相對(duì)引用:由單元格區(qū)域的左上角單元格相對(duì)引用和右下角單元格相對(duì)引用組成,中間用冒號(hào)分隔。如A1:D5,引用了以單元格Al為左上角、以單元格D5為右下角的單元格區(qū)域。示例:在工作表&

3、quot;學(xué)生成績單"的D3單元格中,利用單元格相對(duì)引用計(jì)算第一個(gè)學(xué)生的總成績。除了在單元格D3中直接輸入公式=B3+C3"引用B3和C3以外,還可以用鼠標(biāo)選取要引用的單元格。操作步驟如下:1)選中單元格D3,輸入"="號(hào)。2)單擊單元格肘,B3被虛線框包圍。單元格D3中顯示"=B3"。3)輸入"+"號(hào),單元格D3中顯示"=B奸"。4)單擊單元格口,C3被虛線框包圍。單元格D3中顯示"二B3十。"。5)按回車鍵或單擊編輯欄中的輸入按鈕""這種用鼠標(biāo)點(diǎn)取要引

4、用的單元格來獲得其地址并粘貼到公式中的方法,使公式的輸入和單元格的引用更方便快捷。用這種方沾不僅可以在同一工作表中粘貼其他單元格或區(qū)域的引用地址,還可以粘貼其他工作表甚至其他工作簿的工作表中的單元格或區(qū)域的引用地址。在上面的例子中,如果要在D4單元格中計(jì)算第二個(gè)學(xué)生的總成績,只需把D3單元格中的公式復(fù)制到D4單元格中即可,D4單元格中的公式為:"二B4+C4",所引用的單元格的地址發(fā)生了變化,但所引用的單元格相對(duì)于存放公式的目標(biāo)單元格的相對(duì)位置沒有改變。D3單元格中引用的B叉口,相對(duì)于D3分別位于同一行左邊2列、D4單元格中引用的B4、C4,相對(duì)于D4仍然是分別位于同一行左

5、邊2列、1列。公式從D3復(fù)制劇D4,列不變,行數(shù)增加1,所以公式中引用的單元格也列不變,行數(shù)增加1。如果要計(jì)算每個(gè)學(xué)生的總成績,可用鼠標(biāo)對(duì)準(zhǔn)D3單元格右下角的填充柄,向下拖動(dòng)至D10單元格,D3單元格中的公式即被復(fù)制到了D4:D10單元格區(qū)域中。也可用鼠標(biāo)右鍵拖動(dòng)填充柄,釋放鼠標(biāo)后在快捷菜單中選取"復(fù)制單元格"命令。 (2)絕對(duì)引用絕對(duì)引用是指當(dāng)把一個(gè)含有單元格引用的公式復(fù)制或填充到一個(gè)新的位置時(shí),公式中的單元格引用不會(huì)改變。無論將公式復(fù)制到哪里,都將引用同一個(gè)單元格。單元格絕對(duì)引用的方法是在列標(biāo)和行號(hào)前分別加上"$"符號(hào)。如$A$1,表示單元格Al的絕

6、對(duì)引用;$A$1:$D$5,表示單元格區(qū)域Al:D5的絕對(duì)引用。例如:把圖3-3中D3的公式改為"=$B$3十$C$3",然后將公式復(fù)制到單元格D4中,D4中的值與D3相同,引用地址沒有改變。(3)混合引用混合引用是指在一個(gè)單元格地址中,既有絕對(duì)地址引用又有相對(duì)地址引用。例如,單元格地址、A1"表明保持列不發(fā)生變化,而行隨著新的復(fù)制位置發(fā)生變化;單元格地址"A$1"表明保持行不發(fā)生變化,而列隨著新的復(fù)制位置發(fā)生變化。三種引用輸入時(shí)可以互相轉(zhuǎn)換。在公式中選定引用單元格的部分,反復(fù)按F4鍵可進(jìn)行引用間的轉(zhuǎn)換,轉(zhuǎn)換規(guī)律:Al$A$1A$1$AlAl。

7、3.1.3引用其他王作表數(shù)據(jù)公式中也可以引用其他工作表中的單元格或單元格區(qū)域,甚至是其他工作簿的工作表中的單元格或單元格區(qū)域。(l)引用同一工作簿的其他工作表中的單元格如果用在公式中鍵入引用地址的方法,對(duì)同一工作簿中其他工作表的單元格或單元格區(qū)域進(jìn)行引用,引用格式是:工作表名!單元格(或區(qū)域)的引用地址必須用感嘆號(hào)"!"將工作表名稱和單元格引用分開。如:要引用工作表Sheet2的B3單元格,應(yīng)輸入公式:"=Sheet2!BY。如果引用的工作表名稱中含有空格,必須用單引號(hào)將工作表名稱括住,如:"=MySheet!B3"。如果用鼠標(biāo)引用工作簿中其他

8、工作表的單元格或單元格區(qū)域,可在公式中要輸入引用地址的地方,單擊需要引用的單元格所在的工作表標(biāo)簽,選中需要引用的單元格或單元格區(qū)域,則該引用將顯示在公式中。(2)引用其他工作簿中的單元格在要引用的工作簿已經(jīng)打開的情況下,如果用在公式中鍵入引用地址的方法對(duì)其單元格進(jìn)行引用,引用格式是:工作簿名稱工作表名!單元格(或區(qū)域)的引用地址用中括號(hào)將工作簿名稱括住。如:要引用工作簿Book2中的工作表Sheet2的B3單元格,應(yīng)輸入公式:"=Book2lsheet2!BY。如果工作簿名稱中含有空格,也必須用單引號(hào)將工作簿名稱連同工作表名稱一起括住,例如,"=Book2Sheet2

9、9;!B3"。如果用鼠標(biāo)引用其他工作簿中的單元格或單元格區(qū)域,可在公式中要輸入引用地址的地方,首先選擇需要引用的工作簿為當(dāng)前工作簿,然后單擊需要引用的單元格所在的工作表標(biāo)簽,再選中需要引用的單元格或單元格區(qū)域,貝。該引用將顯示在公式中。如果公式中需要引用的工作簿事先沒有打開,則必須在公式中的工作簿名稱前加入該工作簿的路徑,并在路徑前和工作表名后加上單引號(hào),即路徑、文件名和工作表名要用單引號(hào)括起來,例如:"二D:Book2Sheet2'!B3"。3.1.4日期和時(shí)間的運(yùn)算在很多情況下會(huì)遇到日期和時(shí)間的運(yùn)算,Excel提供了有關(guān)日期和時(shí)間運(yùn)算的若干函數(shù)。此處只

10、討論用算術(shù)運(yùn)算求兩個(gè)日期或時(shí)間之間的差值的方法。下面以例解的形式說明如何進(jìn)行日期和時(shí)間的運(yùn)算。(1)日期運(yùn)算在當(dāng)前單元格Al中利用四則運(yùn)算求出2005年1月1日到2006年1月1日期間的天數(shù)。操作步驟如下:l)單擊單元格Al。2)在單元格Al中輸入:="2006一l一1"一"2005-1-1",日期字符串必須用雙引號(hào)括起來。3)按回車鍵或單擊編輯欄中的輸入按鈕""確認(rèn)。 (2)時(shí)間運(yùn)算在當(dāng)前單元格Al中求同一天上午5:09:00到11:09:00期間的時(shí)間差。操作步驟如下:1)單擊單元格Al。2)在單元格Al中輸入:="11

11、:09:00"一"5:09:00",時(shí)間字符串必須用雙引號(hào)括起來。3)按回車鍵或單擊編輯欄中的輸入按鈕"/"確認(rèn),計(jì)算結(jié)果為0.25天。3.2.4函數(shù)的嵌套當(dāng)以函數(shù)作為參數(shù)的時(shí)候,稱為函數(shù)的嵌套。在公式中最多可以包含七級(jí)嵌套函數(shù)。當(dāng)函數(shù)B用作函數(shù)A的參數(shù)時(shí),函數(shù)B稱為二級(jí)函數(shù)。如果函數(shù)B中還有函數(shù)C作為參數(shù),則函數(shù)C稱為三級(jí)函數(shù)。例如:工作表的E2單元格中輸入公式"=IF(AVERAGE(BLD2)>60,SUM(B2:D2),"不通過")",公式中使用了嵌套的AVERAGE函數(shù)和SUM函數(shù)。在此A

12、VERAGE函數(shù)和SUM函數(shù)為二級(jí)函數(shù)。該公式的含義是,用AVERAGE函數(shù)求出B2:D2單元格區(qū)域數(shù)據(jù)的平均值,并將它與數(shù)值60比較,比較后返回值為TRUE或FALSE。當(dāng)返回值為TRUE時(shí),求解IF函數(shù)的第二個(gè)參數(shù),即用SUM函數(shù)求該區(qū)域數(shù)據(jù)的和并作為IF函數(shù)的結(jié)果顯示在E2單元格,否則求解IF函數(shù)的第三個(gè)參數(shù),在E2單元格顯示"不通過"。使用"插入函數(shù)"對(duì)話框輸入該公式的操作步驟如下:(1)選取要輸入函數(shù)的單元格E2。(2)單擊編輯欄的"插入函數(shù)"按,打開"插入函數(shù)"對(duì)話框。(3)在對(duì)話框的"選擇類

13、別"下拉列表中選擇函數(shù)類型"邏輯"。(4)在"選擇函數(shù)"列表框中選擇函數(shù)"IF"。(5)單擊"確定"按鈕,出現(xiàn)"函數(shù)參數(shù)"對(duì)話框。(6)單擊"Logical-test"文本框,單擊公式選項(xiàng)板右側(cè)的下拉按鈕打開"函數(shù)"列表(7)單擊"AVERAGE"函數(shù),出現(xiàn)該函數(shù)的"參數(shù)"輸入對(duì)話框。(8)在對(duì)話框中輸入?yún)?shù)B232,單擊"確定"按鈕,彈出公式出錯(cuò)"警告"信息框,單擊

14、信息框中的"確定"按鈕,回到公式編輯欄。(9)在編輯欄中繼續(xù)輸入">60",然后再次單擊"插入函數(shù)"按鈕,回到"IF函數(shù)參數(shù)"輸入對(duì)話框。(lO)單擊"Value-if-true"文本框,在公式選項(xiàng)板的"函數(shù)"列表中選取SUM函數(shù)。在"SUM函數(shù)參數(shù)"對(duì)話框中輸入?yún)?shù)B232,單擊"確定"按鈕,回到公式編輯欄。(11)在編輯欄中繼續(xù)輸入IF函數(shù)的第三個(gè)參數(shù)"不通過'",回車確認(rèn);或再次單擊"插

15、入函數(shù)"按鈕,回到"IF函數(shù)參數(shù)"輸入對(duì)話框,在"Value-iLfalse"文本框中輸入"不通過'",單擊"確定"按鈕結(jié)束操作。3.3.3條件檢測函數(shù)IF&      功能:IF函數(shù)具有判斷的能力,通過對(duì)作為第一參數(shù)的條件進(jìn)行判斷,根據(jù)判斷結(jié)果的真假執(zhí)行不同的計(jì)算,返回不同的結(jié)果。IF函數(shù)屬于邏輯函數(shù)。&      格式:IF(logical-test,value-if-true,

16、value-if-false)&      參數(shù):三個(gè)參數(shù),各參數(shù)含義如下:參數(shù)1:LogicalJtest,數(shù)值或表達(dá)式,計(jì)算結(jié)果為邏輯值,表示被判斷的條件。參數(shù)2:Value-if-true,當(dāng)檢測到Logical-test的值為TRUE時(shí)函數(shù)的返回值,或者說參數(shù)1所代表的條件成立時(shí),IF函數(shù)將返回參數(shù)2表達(dá)式的值。參數(shù)3:Value-if-false,當(dāng)檢測到Logical-test的值為FALSE時(shí)函數(shù)的返回值,或者說參數(shù)l所代表的條件不成立時(shí),IF函數(shù)將返回參數(shù)3表達(dá)式的值。簡單地說,參數(shù)1是判斷的條件,條件成立,返回參數(shù)與條

17、件不成立,返回參數(shù)3。參數(shù)2、3也可以是公式或函數(shù),但前面不要等號(hào)"="。當(dāng)參數(shù)2、3是函數(shù)的時(shí)候,就是函數(shù)的嵌套,函數(shù)最多允許嵌套7層。參數(shù)2、3也可以是帶西文雙引號(hào)的文本,當(dāng)該文本就是IF函數(shù)的返回結(jié)果時(shí),則直接顯示該文本。參數(shù)2可以省略。如果省略,應(yīng)該返回參數(shù)2時(shí),則返回0。參數(shù)3也可以省略。當(dāng)參數(shù)3前面的逗號(hào)一起省略時(shí),應(yīng)該返回參數(shù)3,則返回TRUE;當(dāng)參數(shù)3前面的逗號(hào)沒有省略時(shí),則返回0。操作示例:在圖3-26所示的工作表中,將總分大于等于260的學(xué)生的總評(píng)顯示為"優(yōu)秀",否則什么都不顯示。操作步驟:(1)單擊要存放公式的單元格F2。(2)選取

18、"插入"菜單的"函數(shù)"命令,打開"插入函數(shù)"對(duì)話框。(3)在對(duì)話框中選擇"邏輯"函數(shù)類型中的函數(shù)"IF"。(4)單擊"確定"按鈕,出現(xiàn)IF函數(shù)的"函數(shù)參數(shù)"對(duì)話框。(5)在"Logical-test"參數(shù)文本框中輸入第一個(gè)參數(shù):E2>=260。也可以單擊參數(shù)文本框的"折疊"按鈕以暫時(shí)折疊起對(duì)話框(或?qū)?duì)話框移開),顯露出工作表。單擊E2單元格,將其引用地址E2粘貼到文本框,接著鍵入">=260&

19、quot;后,再單擊"折疊"按鈕,還原對(duì)話框。(6)在"Value-if-true"參數(shù)文本框中輸入第二個(gè)參數(shù):"優(yōu)秀"。(7)在"Value-iLfalse"參數(shù)文本框中輸入第三個(gè)參數(shù):111f。因?yàn)楫?dāng)E2<260時(shí),在F2中什么都不顯示,所以輸入1111兩個(gè)緊跟著的西文雙引號(hào)。(8)單擊"確定"按鈕。(9)鼠標(biāo)對(duì)準(zhǔn)F2單元格右下角的填充柄拖曳至F8單元格,完成公式的復(fù)制填充。3.3.4計(jì)數(shù)函數(shù)COUNT&      功能:返回參

20、數(shù)中數(shù)字項(xiàng)的個(gè)數(shù)。COUNT屬于統(tǒng)計(jì)函數(shù)。&      格式:COUNT(value1,value2,.)&      參數(shù):value1,value2,.為包含或引用各種類型數(shù)據(jù)的參數(shù),COUNT函數(shù)最多可以有30個(gè)參數(shù)。數(shù)字、邏輯值、文本數(shù)字、日期值、空單元格、單元格和區(qū)域的引用地址、名稱和標(biāo)志都可以作為參數(shù)。如果參數(shù)直接是數(shù)字、空白、邏輯值、日期或文本數(shù)字,執(zhí)行時(shí)都可以被計(jì)算。如果參數(shù)直接是非數(shù)字的文本或錯(cuò)誤值都將被忽略。如果參數(shù)是單元格或區(qū)域的引用,則只統(tǒng)計(jì)引用中的數(shù)字、

21、日期值;引用的空白單元格、邏輯值、文字或錯(cuò)誤值都將被忽略。例: Al:Fl中的數(shù)據(jù)如圖3-28所示,統(tǒng)計(jì)該區(qū)域中數(shù)字項(xiàng)的個(gè)數(shù)。在Gl單元格中輸入公式:"=COUNT(A1：Fl)",結(jié)果、為2。因?yàn)閰?shù)是引用,所以文本類型的練習(xí)、空單元格、邏輯值TRUE、錯(cuò)誤值#DIV/0!都將被忽略。只有2005-10-1、10被計(jì)算,所以返回值是2。COUNT(練習(xí),2005-10-1,10,TRUE,#DIV/0!)的返回值是4,因?yàn)槭侵苯虞斎?所以空單元格、2005-10-1,10,TRUE被計(jì)算,文本和錯(cuò)誤值被忽略。例:在El單元格中用COUNT函數(shù)統(tǒng)計(jì)工作表中數(shù)字項(xiàng)的個(gè)數(shù)。操作

22、步驟:(1)單擊要存放公式的單元格El。(2)選取"插入"菜單的"函數(shù)"命令,打開"插入函數(shù)"對(duì)話框。(3)在對(duì)話框中選擇"統(tǒng)計(jì)"函數(shù)類型中的函數(shù)"COUNT"。(4)單擊"確定"按鈕,出現(xiàn)COUNT函數(shù)的"函數(shù)參數(shù)"對(duì)話框。(5)在參數(shù)文本框中輸入Al:D6。(6)單擊"確定"按鈕,結(jié)果如圖3-29所示。3.3.5最大值函數(shù)MAX、最小值函數(shù)MIN&      功能:MAX返回

23、參數(shù)包含的數(shù)據(jù)集中的最大數(shù)值,MIN返回參數(shù)包含的數(shù)據(jù)集中的最小數(shù)值。函數(shù)MAX和MIN屬于統(tǒng)計(jì)函數(shù)。&      格式:MAX/MIN(numberl,number2,.)&      參數(shù)：numberl,number2,.為需要求最大值或最小值或包含需要求最大值或最小值數(shù)據(jù)的參數(shù),最多可以有30個(gè)參數(shù)。數(shù)字、邏輯值、文本數(shù)字、空單元格、單元格和區(qū)域的引用地址、名稱和標(biāo)志都可以作為參數(shù)。如果參數(shù)直接是數(shù)字、空單元格、邏輯值或文本數(shù)字,計(jì)算時(shí)都將被包括在內(nèi),空單元格被視為0,T

24、RUE的值為1,FALSE的值為0,文本數(shù)字自動(dòng)轉(zhuǎn)換為數(shù)值數(shù)字。如果參數(shù)直接是非數(shù)字文本或錯(cuò)誤值,將顯示錯(cuò)誤提示。如果參數(shù)是單元格或區(qū)域的引用,只有引用內(nèi)容是數(shù)字的才被計(jì)算,其他值都將被忽略。3.3.6LOOKUP(不含數(shù)組)&      功能:在指定的單行區(qū)域中查找指定的數(shù)值,然后返回在另一個(gè)指定的單行區(qū)域中與該查找到的數(shù)值位于相同列位置的數(shù)值?；蛟谥付ǖ膯瘟袇^(qū)域中查找指定的數(shù)值,然后返回另一個(gè)指定的單列區(qū)域中與該查找到的數(shù)值位于相同行位置的數(shù)值。LOOKUP函數(shù)屬于查找與引用函數(shù)。該函數(shù)有兩種語法形式:向量形式和數(shù)組形式,向量形式的

25、LOOKUP函數(shù)使用三個(gè)參數(shù),數(shù)組形式的LOOKUP函數(shù)使用兩個(gè)參數(shù),這里只介紹向量形式的LOOKUP函數(shù)。&      格式:LOOKIJP(lookup-value,lookup-vector,result-vector)&      參數(shù):三個(gè)參數(shù)第一參數(shù)lookup-value:要在第一個(gè)指定的單行區(qū)域或單列區(qū)域中查找的值,可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。第二參數(shù)looklip-vector:要查找的區(qū)域(行或列),即指定的第一個(gè)單行區(qū)域或單列區(qū)域。在該區(qū)

26、域中查找lookup-value。第三參數(shù)result-vector:要返回的值所在的區(qū)域(行或列),即指定的另一個(gè)單行區(qū)域或單列區(qū)域,其大小必須與lookup-vector所確定的區(qū)域大小相同,位置對(duì)應(yīng)。第二參數(shù)確定的區(qū)域中的數(shù)值必須按升序排.,-2,-l,0,1,2,.,A-Z,FALSE,TRUEg否則,函數(shù)LOOKUP不能返回正確的結(jié)果。文本不區(qū)分大小寫。如果函數(shù)LOOKUP在第二參數(shù)確定的區(qū)域中找不到與第一參數(shù)完全相同的值,則查找該區(qū)域中最接近第一參數(shù)且小于它的值。如果第一參數(shù)小于第二參數(shù)確定的區(qū)域中的最小值,函數(shù)LOOKUP返回錯(cuò)誤值#N/A。例:工作表中,查找成績?yōu)?5分的學(xué)生的

27、姓名,并將查找結(jié)果顯示在Gl單元格中。操作步驟如下:(l)單擊要存放公式的單元格Gl。(2)選取"插入"菜單的"函數(shù)"命令,打開"插入函數(shù)"對(duì)話框。(3)在對(duì)話框中選擇"查找與引用"函數(shù)類型中的函數(shù)"LOOKUP"。(4)單擊"確定"按鈕,出現(xiàn)LOOKUP函數(shù)的"選定參數(shù)"對(duì)話框。(5)選擇默認(rèn)的參數(shù)組合方式,單擊"確定",出現(xiàn)LOOKUP函數(shù)的"函數(shù)參數(shù)"對(duì)話框。(6)在"lookup-value&quo

28、t;參數(shù)文本框中輸入85, A2:A8，在"result-vector"參數(shù)文本框中輸入b2:b(7)回車，產(chǎn)生結(jié)果3.3.7條件計(jì)數(shù)函數(shù)COUNTIF&      功能:返回區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。COUNTIF函數(shù)屬于統(tǒng)計(jì)函數(shù)。·格式:COUNTIF(range,criteria)&      參數(shù):兩個(gè)參數(shù)。第一參數(shù)range:需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。第二參數(shù)criteria:為確定哪些單元格將被計(jì)算在內(nèi)的條件,其形式可以為數(shù)字、表達(dá)式或文本。當(dāng)?shù)诙?shù)為表達(dá)式或文本時(shí)應(yīng)加雙引號(hào);為數(shù)字時(shí)雙引號(hào)可加可不加。例如:條件可以表示為32,"32",">32"或"apples"。簡單地說,COUNTIF函數(shù)的功能是在第一個(gè)參數(shù)指定的范圍中,統(tǒng)計(jì)滿