《公式與函數(shù)基礎(chǔ)》PPT課件.ppt

1、第2章 公式與函數(shù)基礎(chǔ),2.1 公式,在Excel中，公式是對(duì)工作表中數(shù)據(jù)進(jìn)行計(jì)算的等式，以等號(hào)（=）開始。 例如，=10+6*2 是一個(gè)比較簡(jiǎn)單的公式。,2.1.1 公式的組成,公式通常由運(yùn)算符和參與運(yùn)算的操作數(shù)組成。 操作數(shù)可以是常量、單元格引用、函數(shù)等。 常量：直接輸入到公式中的數(shù)字或者文本，是不用計(jì)算的值。 單元格引用：引用某一單元格或單元格區(qū)域中的數(shù)據(jù)，可以是當(dāng)前工作表的單元格、同一工作簿中其他工作表中的單元格、其他工作簿中工作表中的單元格。 工作表函數(shù)：包括函數(shù)及它們的參數(shù)。 運(yùn)算符：是連接公式中的基本元素并完成特定計(jì)算的符號(hào)，例如“+”、“/”等。不同的運(yùn)算符完成不同的運(yùn)算。,2

2、.1.2 公式的輸入和編輯,Excel公式必須以等號(hào)（=）開始。 1輸入公式 在Excel中輸入公式時(shí)首先應(yīng)選中要輸入公式的單元格，然后在其中輸入“=”，接著根據(jù)需要輸入表達(dá)式，最后按回車鍵對(duì)輸入的內(nèi)容進(jìn)行確定。 例如在單元格D3中輸入公式“=10+5*2 ”，輸入完成按下回車鍵，在該單元格中即可顯示該公式的運(yùn)算結(jié)果。,2顯示公式 輸入公式后，系統(tǒng)將自動(dòng)計(jì)算其結(jié)果并在單元格中顯示出來。如果需要將公式顯示在單元格中，可以通過下列方法: (1) 單擊“公式”選項(xiàng)卡“公式審核”組中“顯示公式”按鈕。 （2）單擊“Office”按鈕，單擊“Excel選項(xiàng)”按鈕，在彈出的對(duì)話框中選擇“高級(jí)”選項(xiàng)卡，在“

3、此工作表的顯示選項(xiàng)”欄中啟用“在單元格中顯示公式而非計(jì)算結(jié)果”復(fù)選框。,3修改公式 在計(jì)算的過程中若發(fā)現(xiàn)某公式有錯(cuò)誤，或者發(fā)現(xiàn)情況發(fā)生改變，就需要對(duì)公式進(jìn)行修改。具體的操作步驟如下： （1）選定包含要修改公式的單元格，這時(shí)在編輯欄中將顯示出該公式； （2）在編輯欄中對(duì)公式進(jìn)行修改； （3）修改完畢按回車鍵即可。 修改公式時(shí)也可在含有公式的單元格上雙擊，然后直接在單元格區(qū)域中對(duì)公式進(jìn)行修改。,4移動(dòng)公式 如果需要移動(dòng)公式到其他的單元格中，那么具體的操作步驟如下。 （1）選定包含公式的單元格，這時(shí)單元格的周圍會(huì)出現(xiàn)一個(gè)黑色的邊框。 （2）要移動(dòng)該單元格中的公式可將鼠標(biāo)放在單元格邊框上，當(dāng)鼠標(biāo)指針變

4、為四向箭頭時(shí)按下鼠標(biāo)左鍵，拖動(dòng)鼠標(biāo)指針到目標(biāo)單元格。 （3）釋放鼠標(biāo)左鍵，公式移動(dòng)完畢。 移動(dòng)公式后，公式中的單元格引用不會(huì)發(fā)生變化。,5復(fù)制公式 在Excel 中，可以將已經(jīng)編輯好的公式復(fù)制到其他單元格中。復(fù)制公式時(shí)，單元格引用將會(huì)根據(jù)所用引用類型而變化。 復(fù)制公式可使用“開始”選項(xiàng)卡中的“復(fù)制”和“粘貼”按鈕來復(fù)制公式。 復(fù)制公式也可以使用“填充柄”，相當(dāng)于批量復(fù)制公式， 根據(jù)復(fù)制的需要，有時(shí)在復(fù)制內(nèi)容時(shí)不需要復(fù)制單元格的格式，或只想復(fù)制公式。這時(shí)可以使用【選擇性粘貼】命令來完成復(fù)制操作。,2.1.3 公式中的運(yùn)算符,Excel 2007公式中，運(yùn)算符有算術(shù)運(yùn)算符、比較運(yùn)算符、文本連接運(yùn)算

5、符和引用運(yùn)算符四種類型。 1、算術(shù)運(yùn)算符,2.1.3 公式中的運(yùn)算符,2、比較運(yùn)算符 該類運(yùn)算符能夠比較兩個(gè)或者多個(gè)數(shù)字、文本串、單元格內(nèi)容、函數(shù)結(jié)果的大小關(guān)系，比較的結(jié)果為邏輯值：TRUE或者FALSE。,2.1.3 公式中的運(yùn)算符,3、文本連接運(yùn)算符 文本連接運(yùn)算符用“&”表示，用于將兩個(gè)文本連接起來合并成一個(gè)文本。例如，公式“中國(guó)”& “北京”的結(jié)果就是“中國(guó)北京”。,2.1.3 公式中的運(yùn)算符,4、引用運(yùn)算符 引用運(yùn)算符可以把兩個(gè)單元格或者區(qū)域結(jié)合起來生成一個(gè)聯(lián)合引用。,2.1.3 公式中的運(yùn)算符,如果在一個(gè)公式中包含了多個(gè)運(yùn)算符，就要按照一定的順序進(jìn)行計(jì)算。公式的計(jì)算順序與運(yùn)算符的優(yōu)

6、先級(jí)有關(guān)。,2.1.4 單元格引用,在使用公式和函數(shù)進(jìn)行計(jì)算時(shí)，往往需要引用單元格中的數(shù)據(jù)。通過引用可以在公式中使用同一個(gè)工作表中不同部分的數(shù)據(jù)，或者在多個(gè)公式中使用同一單元格或區(qū)域的數(shù)值，還可以引用相同工作簿中不同工作表上的單元格和其他工作簿中的單元格數(shù)據(jù)。,1A1引用樣式和R1C1引用樣式,默認(rèn)情況下Excel使用A1引用樣式，就是采用列的字母標(biāo)識(shí)和行的數(shù)字標(biāo)識(shí)。若想引用單元格區(qū)域，則需要輸入?yún)^(qū)域左上角的單元格引用標(biāo)識(shí)，后面跟一個(gè)冒號(hào)，接著輸入?yún)^(qū)域右下角的單元格引用標(biāo)識(shí)。例如，B3:E6引用的是從B3到E6所在區(qū)域的所有單元格。 在Excel中有時(shí)也使用R1C1引用樣式。R1C1引用是使用

7、“R”加行數(shù)字和“C”加列數(shù)字來確定單元格的位置。例如，R3C5引用的是第3行和第5列交叉處的單元格；R2C5引用的是指針?biāo)趩卧竦南旅娴?行右面第6列交叉處的單元格；R3C4:R5C8引用的是第3行4列到第5行8列之間的所有單元格。 在R1C1引用樣式中，中表示的是數(shù)據(jù)的相對(duì)位置，即指針?biāo)谖恢脼楫?dāng)前激活單元格位置，而引用的是移動(dòng)括號(hào)內(nèi)的數(shù)字后的單元格或者區(qū)域。當(dāng)前位置向下向右用正數(shù)表示，向上向左用負(fù)數(shù)表示。 系統(tǒng)默認(rèn)使用的是A1引用樣式。因此在工作表中要使用R1C1引用樣式則需要重新進(jìn)行一些設(shè)置。,2相對(duì)引用,相對(duì)引用是指引用單元格的相對(duì)地址，即被引用的單元格與公式所在的單元格之間的位置

8、是相對(duì)的。如果公式所在的單元格的位置發(fā)生了變化，那么引用的單元格的位置也會(huì)相應(yīng)地發(fā)生變化。所以當(dāng)帶有相對(duì)引用的公式被復(fù)制到其他單元格時(shí)，公式內(nèi)引用的單元格將變成與目標(biāo)單元格相對(duì)位置上的單元格。,3絕對(duì)引用,絕對(duì)引用是指被引用的單元格與公式所在的單元格之間的位置關(guān)系是絕對(duì)的，即不管公式被復(fù)制到什么位置，公式中所引用的還是原來單元格區(qū)域的數(shù)據(jù)。在某些操作中如果不希望調(diào)整引用位置，則可使用絕對(duì)引用。A1樣式的單元格絕對(duì)引用是在單元格的行和列前加上“$”符號(hào)，例如“$B$5”表示B列5行交叉處的單元格；可以通過按“F4”鍵進(jìn)行切換。 R1C1樣式中單元格的絕對(duì)引用是直接在“R”的后面加行號(hào)，在“C”的

9、后面加列號(hào)，如R3C4則表示第3行第4列交叉處的單元格。,4混合引用,混合引用就是在單元格引用中既有絕對(duì)引用又有相對(duì)引用，例如“$A1”、 “A$1”。 列絕對(duì)、行相對(duì)混合引用采用“$A1”、 “$B1”等形式。公式的計(jì)算結(jié)果不隨公式所在單元格的列位置變化而改變，而隨公式所在單元格的行位置變化而改變。 列相對(duì)、行絕對(duì)混合引用采用A$1、B$1等形式。公式的計(jì)算結(jié)果不隨公式所在單元格的行位置變化而改變，而隨公式所在單元格的列位置變化而改變。 在某些情況下，復(fù)制公式時(shí)只需改變行或者只需改變列，這時(shí)就需要使用混合引用。,5引用其他工作表或工作簿中的單元格,在單元格引用中，不僅僅是引用當(dāng)前工作表中的單

10、元格，有時(shí)需要引用同一工作簿中其他工作表中的單元格。這就需要使用嘆號(hào)“！”來實(shí)現(xiàn)，格式為： =工作表名稱！單元格地址 即在單元格地址前加上工作表名稱和嘆號(hào)。 如果要引用不同工作簿中某一工作表的單元格，則需要使用如下格式： =工作簿名稱 工作表名稱！單元格地址 即用將工作簿名稱括起來。,2.1.5 公式的錯(cuò)誤和審核,1、 Excel常見錯(cuò)誤,2.1.5 公式的錯(cuò)誤和審核,2、 使用錯(cuò)誤檢查 Excel使用特定的規(guī)則來檢查公式中的錯(cuò)誤。這些規(guī)則雖然不能保證工作表中沒有錯(cuò)誤，但對(duì)發(fā)現(xiàn)錯(cuò)誤卻非常有幫助。錯(cuò)誤檢查規(guī)則可以單獨(dú)打開或關(guān)閉。 在Excel中可以使用兩種方法檢查錯(cuò)誤：一是像使用拼寫檢查器那樣一

11、次檢查一個(gè)錯(cuò)誤，二是檢查當(dāng)前工作表中的所有錯(cuò)誤。一旦發(fā)現(xiàn)錯(cuò)誤，在單元格的左上角會(huì)顯示一個(gè)三角。這兩種方法檢查到錯(cuò)誤后都會(huì)顯示相同的選項(xiàng)。 當(dāng)單擊包含錯(cuò)誤的單元格時(shí)，在單元格旁邊就會(huì)出現(xiàn)一個(gè)錯(cuò)誤提示按鈕 ，單擊該按鈕會(huì)打開一個(gè)菜單，顯示錯(cuò)誤檢查的相關(guān)命令。使用這些命令可以查看錯(cuò)誤的信息、相關(guān)幫助、顯示計(jì)算步驟、忽略錯(cuò)誤、轉(zhuǎn)到編輯欄中編輯公式，以及設(shè)置錯(cuò)誤檢查選項(xiàng)等。,2.1.5 公式的錯(cuò)誤和審核,3處理循環(huán)引用 如果公式引用自己所在的單元格，則不論是直接引用還是間接引用，都稱為“循環(huán)引用”。例如在A1單元格中輸入公式“=A1+2”，這就是一個(gè)循環(huán)引用。則完成公式輸入后按回車鍵確認(rèn)時(shí)，Excel會(huì)

12、彈出一個(gè)警告對(duì)話框，提示產(chǎn)生了循環(huán)引用。,2.1.5 公式的錯(cuò)誤和審核,4審核公式 使用Excel中提供的多種公式審核功能，可以追蹤引用單元格和從屬單元格，可以使用監(jiān)視窗口監(jiān)視公式及其結(jié)果。 （1）追蹤引用單元格 （2）追蹤從屬單元格 （3）使用監(jiān)視窗口,2.1.6 使用數(shù)組公式,前面介紹的公式都是執(zhí)行單個(gè)計(jì)算并且返回單個(gè)結(jié)果的情況，而數(shù)組公式可以執(zhí)行多個(gè)計(jì)算并且返回多個(gè)結(jié)果。 數(shù)組公式作用于兩組或多組被稱為數(shù)組參數(shù)的數(shù)值，每組數(shù)值參數(shù)必須具有相同數(shù)目的行和列。(ctrl+shift+enter),2.2 函數(shù),函數(shù)是Excel提供的內(nèi)部工具，是一些預(yù)定義的公式。 2.2.1 函數(shù)簡(jiǎn)介 函數(shù)的

13、一般結(jié)構(gòu)是： 函數(shù)名（參數(shù)1，參數(shù)2，） 其中，函數(shù)名是函數(shù)的名稱，每個(gè)函數(shù)名是惟一標(biāo)識(shí)一個(gè)函數(shù)的。參數(shù)就是函數(shù)的輸入值，用來計(jì)算所需的數(shù)據(jù)。參數(shù)可以是常量、單元格引用、數(shù)組、邏輯值或者是其他的函數(shù)。 按照參數(shù)的數(shù)量和使用區(qū)分，函數(shù)可以分為無參數(shù)型和有參數(shù)型。無參數(shù)型如返回當(dāng)前日期和時(shí)間的NOW（）函數(shù)，不需要參數(shù)。大多數(shù)函數(shù)至少有一個(gè)參數(shù)，有的甚至有八九個(gè)之多。這些參數(shù)又可以分為必要參數(shù)和可選參數(shù)。 函數(shù)要求的參數(shù)必須出現(xiàn)在括號(hào)內(nèi)，否則會(huì)產(chǎn)生錯(cuò)誤信息。可選參數(shù)則依據(jù)公式的需要而定。,2.2.2 函數(shù)的輸入,要在工作表中使用函數(shù)必須先輸入函數(shù)。函數(shù)的輸入有兩種常用的方法。 1、手工輸入 2、使用函數(shù)向?qū)л斎?2.3 使用名稱,1、定義名稱常量 在引用一個(gè)常量時(shí)，通常會(huì)想到將這個(gè)常量輸入到某一單元格中，然后在公式中引用這個(gè)單元格。但在Excel中可以給一個(gè)并沒有出現(xiàn)在單元格中的條目命名。 例如，如果在工作表的公式中要用到一個(gè)貸款利率，又不希望使用單元格引用的方式來使用貸款利率的具體數(shù)值，則可以定義一個(gè)常量名稱。,2.3 使用名稱,2、定義公式名稱 在Excel中可以為公式創(chuàng)建名稱，其方法與為常量定義名稱類似。所命名的公式也不會(huì)出現(xiàn)在工作表中，但可以使用“名稱管理器”查看與編輯。 例如，可以創(chuàng)建一個(gè)名為“總銷售額”的命名公式，通過每月的銷售額來計(jì)算一年總的預(yù)銷售額，而每