公式與函數(shù)基礎

公式與函數(shù)基礎第一頁，共三十頁，2022年，8月28日

2.1

公式在Excel中，公式是對工作表中數(shù)據(jù)進行計算的等式，以等號（=）開始。例如，=10+6*2是一個比較簡單的公式。第二頁，共三十頁，2022年，8月28日

2.1.1公式的組成公式通常由運算符和參與運算的操作數(shù)組成。操作數(shù)可以是常量、單元格引用、函數(shù)等。常量：直接輸入到公式中的數(shù)字或者文本，是不用計算的值。單元格引用：引用某一單元格或單元格區(qū)域中的數(shù)據(jù)，可以是當前工作表的單元格、同一工作簿中其他工作表中的單元格、其他工作簿中工作表中的單元格。工作表函數(shù)：包括函數(shù)及它們的參數(shù)。運算符：是連接公式中的基本元素并完成特定計算的符號，例如“+”、“/”等。不同的運算符完成不同的運算。第三頁，共三十頁，2022年，8月28日

2.1.2公式的輸入和編輯

Excel公式必須以等號（=）開始。1．輸入公式在Excel中輸入公式時首先應選中要輸入公式的單元格，然后在其中輸入“=”，接著根據(jù)需要輸入表達式，最后按回車鍵對輸入的內(nèi)容進行確定。例如在單元格D3中輸入公式“=10+5*2”，輸入完成按下回車鍵，在該單元格中即可顯示該公式的運算結果。第四頁，共三十頁，2022年，8月28日第五頁，共三十頁，2022年，8月28日2．顯示公式

輸入公式后，系統(tǒng)將自動計算其結果并在單元格中顯示出來。如果需要將公式顯示在單元格中，可以通過下列方法:

(1)單擊“公式”選項卡→“公式審核”組中“顯示公式”按鈕。

（2）單擊“Office”按鈕，單擊“Excel選項”按鈕，在彈出的對話框中選擇“高級”選項卡，在“此工作表的顯示選項”欄中啟用“在單元格中顯示公式而非計算結果”復選框。第六頁，共三十頁，2022年，8月28日3．修改公式在計算的過程中若發(fā)現(xiàn)某公式有錯誤，或者發(fā)現(xiàn)情況發(fā)生改變，就需要對公式進行修改。具體的操作步驟如下：（1）選定包含要修改公式的單元格，這時在編輯欄中將顯示出該公式；（2）在編輯欄中對公式進行修改；（3）修改完畢按回車鍵即可。修改公式時也可在含有公式的單元格上雙擊，然后直接在單元格區(qū)域中對公式進行修改。第七頁，共三十頁，2022年，8月28日4．移動公式如果需要移動公式到其他的單元格中，那么具體的操作步驟如下。（1）選定包含公式的單元格，這時單元格的周圍會出現(xiàn)一個黑色的邊框。（2）要移動該單元格中的公式可將鼠標放在單元格邊框上，當鼠標指針變?yōu)樗南蚣^時按下鼠標左鍵，拖動鼠標指針到目標單元格。（3）釋放鼠標左鍵，公式移動完畢。移動公式后，公式中的單元格引用不會發(fā)生變化。第八頁，共三十頁，2022年，8月28日5．復制公式

在Excel中，可以將已經(jīng)編輯好的公式復制到其他單元格中。復制公式時，單元格引用將會根據(jù)所用引用類型而變化。復制公式可使用“開始”選項卡中的“復制”和“粘貼”按鈕來復制公式。復制公式也可以使用“填充柄”，相當于批量復制公式，根據(jù)復制的需要，有時在復制內(nèi)容時不需要復制單元格的格式，或只想復制公式。這時可以使用【選擇性粘貼】命令來完成復制操作。第九頁，共三十頁，2022年，8月28日

2.1.3公式中的運算符

Excel2007公式中，運算符有算術運算符、比較運算符、文本連接運算符和引用運算符四種類型。

1、算術運算符

算術運算符含義示例+加號2+1-減號2-1*乘號3*5/除號9/2%百分號50%^乘冪號3^2第十頁，共三十頁，2022年，8月28日

2.1.3公式中的運算符

2、比較運算符該類運算符能夠比較兩個或者多個數(shù)字、文本串、單元格內(nèi)容、函數(shù)結果的大小關系，比較的結果為邏輯值：TRUE或者FALSE。比較運算符含義示例=等于A1=B1>大于A1>B1<小于A1<B1>=大于等于A1>=B1<=小于等于A1<=B1

不等于A1<>B1第十一頁，共三十頁，2022年，8月28日

2.1.3公式中的運算符

3、文本連接運算符文本連接運算符用“&”表示，用于將兩個文本連接起來合并成一個文本。例如，公式“中國”&“北京”的結果就是“中國北京”。第十二頁，共三十頁，2022年，8月28日

2.1.3公式中的運算符

4、引用運算符引用運算符可以把兩個單元格或者區(qū)域結合起來生成一個聯(lián)合引用。引用運算符含義示例:（冒號）區(qū)域運算符，生成對兩個引用之間所有單元格的引用。A5:A8,（逗號）聯(lián)合運算符，將多個引用合并為一個引用SUM(A5:A10,B5:B10)（引用A5:A10和B5:B10兩個單元區(qū)域）（空格）交集運算符，產(chǎn)生對兩個引用共有的單元格的引用SUM(A1:F1B1:B3)(引用A1:F1和

B1:B3兩個單元格區(qū)域相交的B1單元格)第十三頁，共三十頁，2022年，8月28日

2.1.3公式中的運算符如果在一個公式中包含了多個運算符，就要按照一定的順序進行計算。公式的計算順序與運算符的優(yōu)先級有關。運算符（優(yōu)先級從高到低）說明：區(qū)域運算符，聯(lián)合運算符空格交集運算符-負號%百分比^乘冪*和/乘法和除法+和-加法和減法&文本連接符=，>，<，>=，<=，<>比較運算符第十四頁，共三十頁，2022年，8月28日

2.1.4單元格引用在使用公式和函數(shù)進行計算時，往往需要引用單元格中的數(shù)據(jù)。通過引用可以在公式中使用同一個工作表中不同部分的數(shù)據(jù)，或者在多個公式中使用同一單元格或區(qū)域的數(shù)值，還可以引用相同工作簿中不同工作表上的單元格和其他工作簿中的單元格數(shù)據(jù)。

第十五頁，共三十頁，2022年，8月28日

1．A1引用樣式和R1C1引用樣式默認情況下Excel使用A1引用樣式，就是采用列的字母標識和行的數(shù)字標識。若想引用單元格區(qū)域，則需要輸入?yún)^(qū)域左上角的單元格引用標識，后面跟一個冒號，接著輸入?yún)^(qū)域右下角的單元格引用標識。例如，B3:E6引用的是從B3到E6所在區(qū)域的所有單元格。在Excel中有時也使用R1C1引用樣式。R1C1引用是使用“R”加行數(shù)字和“C”加列數(shù)字來確定單元格的位置。例如，R3C5引用的是第3行和第5列交叉處的單元格；R[2]C[5]引用的是指針所在單元格的下面第3行右面第6列交叉處的單元格；R3C4:R5C8引用的是第3行4列到第5行8列之間的所有單元格。在R1C1引用樣式中，[]中表示的是數(shù)據(jù)的相對位置，即指針所在位置為當前激活單元格位置，而引用的是移動括號內(nèi)的數(shù)字后的單元格或者區(qū)域。當前位置向下向右用正數(shù)表示，向上向左用負數(shù)表示。系統(tǒng)默認使用的是A1引用樣式。因此在工作表中要使用R1C1引用樣式則需要重新進行一些設置。第十六頁，共三十頁，2022年，8月28日

第十七頁，共三十頁，2022年，8月28日

2．相對引用

相對引用是指引用單元格的相對地址，即被引用的單元格與公式所在的單元格之間的位置是相對的。如果公式所在的單元格的位置發(fā)生了變化，那么引用的單元格的位置也會相應地發(fā)生變化。所以當帶有相對引用的公式被復制到其他單元格時，公式內(nèi)引用的單元格將變成與目標單元格相對位置上的單元格。第十八頁，共三十頁，2022年，8月28日

3．絕對引用絕對引用是指被引用的單元格與公式所在的單元格之間的位置關系是絕對的，即不管公式被復制到什么位置，公式中所引用的還是原來單元格區(qū)域的數(shù)據(jù)。在某些操作中如果不希望調(diào)整引用位置，則可使用絕對引用。A1樣式的單元格絕對引用是在單元格的行和列前加上“$”符號，例如“$B$5”表示B列5行交叉處的單元格；可以通過按“F4”鍵進行切換。R1C1樣式中單元格的絕對引用是直接在“R”的后面加行號，在“C”的后面加列號，如R3C4則表示第3行第4列交叉處的單元格。第十九頁，共三十頁，2022年，8月28日

4．混合引用混合引用就是在單元格引用中既有絕對引用又有相對引用，例如“$A1”、“A$1”。列絕對、行相對混合引用采用“$A1”、“$B1”等形式。公式的計算結果不隨公式所在單元格的列位置變化而改變，而隨公式所在單元格的行位置變化而改變。列相對、行絕對混合引用采用A$1、B$1等形式。公式的計算結果不隨公式所在單元格的行位置變化而改變，而隨公式所在單元格的列位置變化而改變。在某些情況下，復制公式時只需改變行或者只需改變列，這時就需要使用混合引用。第二十頁，共三十頁，2022年，8月28日

5．引用其他工作表或工作簿中的單元格在單元格引用中，不僅僅是引用當前工作表中的單元格，有時需要引用同一工作簿中其他工作表中的單元格。這就需要使用嘆號“！”來實現(xiàn)，格式為：

=工作表名稱！單元格地址即在單元格地址前加上工作表名稱和嘆號。如果要引用不同工作簿中某一工作表的單元格，則需要使用如下格式：

=[工作簿名稱]工作表名稱！單元格地址即用[]將工作簿名稱括起來。第二十一頁，共三十頁，2022年，8月28日

2.1.5公式的錯誤和審核

1、Excel常見錯誤

錯誤值產(chǎn)生的原因#####!公式計算的結果太長，單元格容納不下#DIV/O除數(shù)為零。當公式被空單元格除時也會出現(xiàn)這個錯誤#N/A公式中無可用的數(shù)值或者缺少函數(shù)參數(shù)#NAME?公式中引用了一個無法識別的名稱。當刪除一個公式正在使用的名稱或者在使用文本時有不相稱的引用，也會返回這種錯誤#NULL!使用了不正確的區(qū)域運算或者不正確的單元格引用#NUM!在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)，或者公式的計算結果的數(shù)字太大或太小而無法表示#RFF!公式中引用了一個無效的單元格。如果單元格從工作表中被刪除就會出現(xiàn)這個錯誤#VALUE!公式中含有一個錯誤類型的參數(shù)或者操作數(shù)第二十二頁，共三十頁，2022年，8月28日

2.1.5公式的錯誤和審核

2、

使用錯誤檢查

Excel使用特定的規(guī)則來檢查公式中的錯誤。這些規(guī)則雖然不能保證工作表中沒有錯誤，但對發(fā)現(xiàn)錯誤卻非常有幫助。錯誤檢查規(guī)則可以單獨打開或關閉。在Excel中可以使用兩種方法檢查錯誤：一是像使用拼寫檢查器那樣一次檢查一個錯誤，二是檢查當前工作表中的所有錯誤。一旦發(fā)現(xiàn)錯誤，在單元格的左上角會顯示一個三角。這兩種方法檢查到錯誤后都會顯示相同的選項。當單擊包含錯誤的單元格時，在單元格旁邊就會出現(xiàn)一個錯誤提示按鈕

，單擊該按鈕會打開一個菜單，顯示錯誤檢查的相關命令。使用這些命令可以查看錯誤的信息、相關幫助、顯示計算步驟、忽略錯誤、轉到編輯欄中編輯公式，以及設置錯誤檢查選項等。

第二十三頁，共三十頁，2022年，8月28日

2.1.5公式的錯誤和審核

3．處理循環(huán)引用

如果公式引用自己所在的單元格，則不論是直接引用還是間接引用，都稱為“循環(huán)引用”。例如在A1單元格中輸入公式“=A1+2”，這就是一個循環(huán)引用。則完成公式輸入后按回車鍵確認時，Excel會彈出一個警告對話框，提示產(chǎn)生了循環(huán)引用。第二十四頁，共三十頁，2022年，8月28日

2.1.5公式的錯誤和審核

4．審核公式使用Excel中提供的多種公式審核功能，可以追蹤引用單元格和從屬單元格，可以使用監(jiān)視窗口監(jiān)視公式及其結果。（1）追蹤引用單元格（2）追蹤從屬單元格（3）使用監(jiān)視窗口第二十五頁，共三十頁，2022年，8月28日

2.1.6使用數(shù)組公式

前面介紹的公式都是執(zhí)行單個計算并且返回單個結果的情況，而數(shù)組公式可以執(zhí)行多個計算并且返回多個結果。數(shù)組公式作用于兩組或多組被稱為數(shù)組參數(shù)的數(shù)值，每組數(shù)值參數(shù)必須具有相同數(shù)目的行和列。(ctrl+shift+enter)第二十六頁，共三十頁，2022年，8月28日

2.2函數(shù)

函數(shù)是Excel提供的內(nèi)部工具，是一些預定義的公式。2.2.1函數(shù)簡介

函數(shù)的一般結構是：函數(shù)名（參數(shù)1，參數(shù)2，……）其中，函數(shù)名是函數(shù)的名稱，每個函數(shù)名是惟一標識一個函數(shù)的。參數(shù)就是函數(shù)的輸入值，用來計算所需的數(shù)據(jù)。參數(shù)可以是常量、單元格引用、數(shù)組、邏輯值或者是其他的函數(shù)。按照參數(shù)的數(shù)量和使用區(qū)分，函數(shù)可以分為無參數(shù)型和有參數(shù)型。無參數(shù)型如返回當前日期和時間的NOW（）函數(shù)，不需要參數(shù)。大多數(shù)函數(shù)至少有一個參數(shù)，有的甚至有八