第3章 Excel公式基礎(chǔ)知識(shí)

1第3章公式基礎(chǔ)知識(shí)Excel是辦公室自動(dòng)化中非常重要的一款軟件，很多巨型國(guó)際企業(yè)都是依靠Excel進(jìn)行數(shù)據(jù)管理。它不僅僅能夠方便的處理表格和進(jìn)行圖形分析，其更強(qiáng)大的功能體現(xiàn)在對(duì)數(shù)據(jù)的自動(dòng)處理和計(jì)算，具有技術(shù)先進(jìn)、使用靈活方便等特點(diǎn)，能夠有效地管理人們?cè)谄髽I(yè)管理中遇到的工資、稅收、財(cái)務(wù)預(yù)算、產(chǎn)品銷售統(tǒng)計(jì)、人員統(tǒng)計(jì)、員工績(jī)效考評(píng)或計(jì)算存款利息等方面的數(shù)據(jù)表。這些數(shù)據(jù)表，簡(jiǎn)單的可能只有幾行或幾十行數(shù)據(jù)，復(fù)雜的則可能有幾千或幾萬(wàn)行數(shù)據(jù)。然而很多缺少理工科背景或是對(duì)Excel強(qiáng)大數(shù)據(jù)處理功能不了解的人卻難以進(jìn)一步深入，人們?cè)趹?yīng)用Excel的深度方面仍然處于低級(jí)水平，往往只用它從事簡(jiǎn)單的表格制作和運(yùn)算，沒有發(fā)揮其強(qiáng)有力的數(shù)據(jù)管理與分析的作用。Excel是一個(gè)功能強(qiáng)大的數(shù)據(jù)分析軟件，它提供了300多個(gè)函數(shù)和公式，可用于解決數(shù)學(xué)、財(cái)務(wù)、會(huì)計(jì)、證券、統(tǒng)計(jì)、工程計(jì)算、線性規(guī)劃、線性分析及數(shù)據(jù)查找等方面的數(shù)學(xué)計(jì)算和數(shù)學(xué)分析問題。如何充分發(fā)揮Excel的功能，高水平地甚至是開發(fā)性地使用Excel，在企業(yè)現(xiàn)代化管理方面就顯得非常重要。2數(shù)據(jù)的運(yùn)算與分析是Excel的特色功能，它不僅能夠建立用戶日常工作中所用的各種數(shù)據(jù)表格，而且可以通過公式、函數(shù)對(duì)這些數(shù)據(jù)進(jìn)行運(yùn)算，并且將運(yùn)算結(jié)果有條理地顯示出來(lái)，這是普通的文本處理軟件所不及的。主要內(nèi)容：輸入和編輯公式

Excel是如何計(jì)算公式的怎樣防止公式被查看循環(huán)引用消息和校正技術(shù)公式中使用的不同的運(yùn)算符公式錯(cuò)誤類型Excel的變量求解功能33.1輸入和編輯公式公式元素

可以輸入到單元格中的公式共有5種元素：★運(yùn)算符：+，-，*，/等★單元格引用：它們包括命名的單元格和范圍(名稱)、指向當(dāng)前工作表的單元格、同一工作簿其它工作表中的單元格，甚至可以是其它工作簿工作表上的單元格?！镏祷蜃址喝?.5，“年末結(jié)果”?！锕ぷ鞅砗瘮?shù)和它們的參數(shù)：如SUM或AVERAGE以及它們的參數(shù)

★括號(hào)：它們可以控制著公式中各種表達(dá)式被處理的次序。

42、輸入公式

有兩種方法：手工輸入和通過制定單元格引用來(lái)輸入。公式一般都是以等號(hào)開頭，以回車結(jié)束。也可以用@作為起始符，但回車后，系統(tǒng)自動(dòng)將@轉(zhuǎn)換成等號(hào)。按回車鍵后，單元格會(huì)顯示出公式的結(jié)果。當(dāng)單元格被激活時(shí)，公式本身只會(huì)顯示在公式欄（編輯欄）中。

注意：

（1）.輸入數(shù)組公式時(shí)，按Ctrl+Shift+Enter組合鍵，而不是單純地按回車鍵；（2）.公式中可以使用被命名的單元格或范圍，即可以用它們的名稱代替單元格地址；（3）.輸入公式時(shí)不需要使用任何空格和空行；（4）.一個(gè)公式最多可以容納1024個(gè)字符，若超過該范圍，就必須把公式分成多個(gè)公式。也可以創(chuàng)建一個(gè)自定義公式（使用VBA).3、編輯公式

讓單元格進(jìn)入編輯狀態(tài)有幾種方法：

★雙擊單元格；★

按F2鍵；★

選擇要編輯公式的單元格，然后單擊公式欄。注意：a靈活使用Shift、Ctrl+Shift、Home、End。

b公式→文本→公式

c可以使用公式欄作為計(jì)算器（F2→編輯→F9）dF9的使用561、運(yùn)算符是公式的基本元素。一個(gè)運(yùn)算符就是一個(gè)符號(hào)，代表著一種運(yùn)算。Excel支持以下運(yùn)算符：3.2在公式中使用的運(yùn)算符

算術(shù)運(yùn)算符：+、-、*、/、^、（%）。

比較運(yùn)算符：=、>、<、<>（不等于），比較結(jié)果為真（True）和假（False）。

文本運(yùn)算符：&

引用運(yùn)算符：它可以將單元格區(qū)域進(jìn)行合并運(yùn)算，包括以下幾種：

區(qū)域運(yùn)算符：：聯(lián)合運(yùn)算符：，

交叉運(yùn)算符：空格

這四種運(yùn)算符的運(yùn)算優(yōu)先順序?yàn)椋阂眠\(yùn)算符、算術(shù)運(yùn)算符、文本運(yùn)算符、比較運(yùn)算符。+、-、*、/，加減乘除符號(hào)%百分號(hào)&文本連接符^求冪=、>、<、>=、<=、<>，邏輯比較符，分別是等于、大于、大于等于，小于等于、不等于。邏輯運(yùn)算符的返回值是True/False：，單個(gè)空格引用運(yùn)算符，分別是范圍（區(qū)域）運(yùn)算符、聯(lián)合運(yùn)算符、交叉運(yùn)算符。72、運(yùn)算符的優(yōu)先級(jí)（除引用運(yùn)算符外）

括號(hào)在Excel的內(nèi)部?jī)?yōu)先次序中排在最前面，且可以嵌套（輸入括號(hào)時(shí)，Excel有一個(gè)自動(dòng)提示）。

優(yōu)先次序：-負(fù)號(hào)%百分號(hào)

^求冪

*和/乘和除

+和-加和減

&文本連接符

=

>

<

>=

<=

<>邏輯比較符8

◆=“Part-”&“23A”

；得到一個(gè)新的字符串Part-23A

◆=A1&A2

；將單元格A1和A2的內(nèi)容連接在一起

◆=6^3；求6的3次方，結(jié)果為216

◆=A1^3

；單元格作為參數(shù)

◆=216^(1/3)

；求216的立方根，結(jié)果為6

◆=A1<A2

；若A1的值小于A2的值，則返回TRUE，否則為FALSE

◆=OR(A1=100,A1=1000)

；如果A1包含100或1000，則返回TRUE

◆=AND(A1<100,A2<100）；如果A1和A2的值小于100，則返回TRUE

◆=（收入-支出）*稅率；名稱作為參數(shù)

◆=（（B2*C2+B3）*C3+B5*C4）*B6

；使用了嵌套括號(hào)

9103.3計(jì)算公式

如果改變了公式中使用的某個(gè)單元格，公式會(huì)顯示一個(gè)新的結(jié)果，不需要自己的任何操作——Excel的自動(dòng)計(jì)算模式當(dāng)選用人工計(jì)算模式時(shí)，如果還有任何沒有計(jì)算的公式，Excel狀態(tài)欄中會(huì)顯示“計(jì)算”?？梢允褂孟旅娴目旖萱I重新計(jì)算公式：

※F9:在所有打開的工作簿中計(jì)算公式

※Sfift+F9：只計(jì)算被激活工作表中的公式，同一工作簿中的其它工作表不被計(jì)算；

※

Ctrl+Shift+F9：強(qiáng)制重新計(jì)算所有打開的工作簿

※Ctrl+Shift+Alt+F9：重新檢查全部從屬公式，然后強(qiáng)迫重新計(jì)算所有打開的工作簿。

◆

注意：選擇“工具”→“選項(xiàng)”命令，可打開一個(gè)對(duì)話框，重新設(shè)置”重新計(jì)算“。113.4單元格和范圍引用

大多數(shù)公式都會(huì)使用單元格和范圍地址（如果只有一個(gè)單元格，可以使用名稱）引用一個(gè)或多個(gè)單元格，單元格引用有4種類型，分別用美元符號(hào)加以區(qū)別

※

相對(duì):是完全相對(duì)的引用。當(dāng)公式被復(fù)制的時(shí)候，單元格引用將調(diào)整到新的位置上。例A1

※

絕對(duì)：是完全絕對(duì)的引用。當(dāng)公式被復(fù)制的時(shí)候，單元格不會(huì)有變化，例如$A$1?！?/p>

行絕對(duì)：是部分絕對(duì)的引用。當(dāng)公式被復(fù)制的時(shí)候，列部分調(diào)整，但是行部分不會(huì)有變化，例如A$1?！薪^對(duì)：是部分絕對(duì)的引用。當(dāng)公式被復(fù)制的時(shí)候，行部分調(diào)整，但是列部分不會(huì)有變化，例如$A1。

1、引用本工作表的單元格①絕對(duì)引用絕對(duì)引用是指被引用的單元格與引用單元格之間的關(guān)系是絕對(duì)的，無(wú)論將該公式復(fù)制到任何單元格，公式中的引用還是原單元格。方法是在引用單元格的行和列標(biāo)題前加上“$”符號(hào)例如：公式“=C3+D3+E3+F3”改為“=$C$3+$D$3+$E$3+$F$3”②相對(duì)引用相對(duì)引用是指被引用的單元格與引用單元格的位置關(guān)系是相對(duì)的，當(dāng)將一個(gè)帶有單元格引用的公式復(fù)制到其它單元格時(shí)，公式中引用的單元格將變成與目標(biāo)單元格（即獲得復(fù)制公式的單元格）一樣相對(duì)位置上的單元格。例如：公式“=C3+D3+E3+F3”若將公式復(fù)制到H3,請(qǐng)仔細(xì)觀察.12③混合引用混合引用是指被引用的單元格與引用單元格之間的關(guān)系既有相對(duì)的,也有絕對(duì)的。例如：公式“=$C3+D$3+$E3+F$3”當(dāng)一個(gè)混合引用的公式復(fù)制到任何單元格時(shí)，絕對(duì)引用的部分將保持絕對(duì)引用的性質(zhì)，而相對(duì)引用的部分依然保持相對(duì)引用的變化規(guī)律。方法是在引用單元格的行和列標(biāo)題前加上“$”符號(hào)例如：將公式“=$C3+D$3+$E3+F$3”復(fù)制到H3,則公式變?yōu)椤?$C3+E$3+$E3+G$3”。13

2、引用其它工作表或工作簿的單元格一個(gè)公式可以引用不同工作表中的單元格和范圍，也可引用不同工作簿中的單元格，方法是：=sheet2!A1+1;

=[Budgt.xls]sheet1!A1+1=‘[Budgt

Analysis.xls]sheet1’!A1+1=‘C:\Excel\[BudgtAnalysis.xls]sheet1’!A1+1注意：引用中出現(xiàn)的連接符“！”、文件名、路徑等14

如公式：＝‘c:\學(xué)生成績(jī)[book1.xls]sheet1’A1+sheet2!B3+sheet3!C4

提示1：若引用的工作簿book1.xls已經(jīng)打開，則在公式中可以省略工作簿文件的路徑部分，直接應(yīng)用＝‘[book1.xls]sheet1’A1+sheet2!B3+sheet3!C4即可。

提示2：在引用其它工作表中的單元格時(shí)，同樣可以使用絕對(duì)引用、相對(duì)引用和混合引用，且各個(gè)引用的性質(zhì)保持不變。1516方法一：1、雙擊A1，激活編輯模式（或按F2)；

2、按End鍵，接著按Shift+Home選中所有公式文本，或者可以使用鼠標(biāo)拖動(dòng)選中全部公式；

3、單擊“常用”工具欄中的“復(fù)制”按鈕（或Ctrl+C），將選中的文本復(fù)制到剪切板上；

4、按回車鍵結(jié)束編輯模式；

5、激活單元格A2；

6、單擊“常用”工具欄上的“粘貼”按鈕（或Ctrl+V），將公式文本粘貼到單元格A2。3.5準(zhǔn)確地復(fù)制公式

有兩種方法：例如：將A1中的公式復(fù)制到A2。方法二：1、先刪除A1公式中的等號(hào)，即將公式轉(zhuǎn)換成了文本；

2、再選擇“復(fù)制”到A2，編輯原始公式，加上等號(hào)即可。3.6把公式轉(zhuǎn)換成值1、選中要轉(zhuǎn)換的一個(gè)或多個(gè)公式；

2、單擊“常用”工具欄中的“復(fù)制”按鈕（或Ctrl+C）；

3、選擇“編輯”→“選擇性粘貼”命令，Excel顯示“選擇性粘貼”復(fù)選框；

4、選擇“數(shù)值”單選項(xiàng)，單擊“確定”按鈕；

5、按回車鍵或ESC鍵，取消粘貼模式。1718方法：

1、選中公式或多個(gè)公式

2、“格式”菜單→“單元格”。在“單元格格式”對(duì)話框中，單擊“保護(hù)”選項(xiàng)卡。

3、選中“隱藏”復(fù)選框。

4、選擇“工具”菜單→“保護(hù)”→“保護(hù)工作表”命令，來(lái)保護(hù)工作表，還可設(shè)置密碼。

注意：默認(rèn)狀態(tài)下，所有單元格都是被“鎖定的”。3.7隱藏公式19Excel錯(cuò)誤值：

#DIV/0：0作為除數(shù)

#NAME?：不能識(shí)別的名稱

#N/A：使用NA函數(shù)所識(shí)別的不能使用的數(shù)據(jù)

#NULL!：公式中使用了一種不允許出現(xiàn)交叉但交叉在一起的兩個(gè)區(qū)域

#NUM!：?jiǎn)栴}與值有關(guān)。如負(fù)值

#REF：公式中引用了一個(gè)無(wú)效的單元格

#VALUE：公式中包含了錯(cuò)誤形式的變量或運(yùn)算對(duì)象3.8公式中的錯(cuò)誤注意：正確處理和識(shí)別循環(huán)引用20很多電子表格包括的公式允許你提出問題。例如：貸款額度問題。

求解：還款期限（月）36，利率為6.08%，如果月還款2000元，貸款額度為多少？3.9單變量求解（后臺(tái)求解）做如下操作：B5輸入貸款額度B6輸入還款期限(月)B7輸入利率(年)B8輸入月還款C5為要求的貸款額度?不用輸入數(shù)據(jù)C6輸入36C7輸入6.08%C8輸入公式=C5/36+C5*C7/12

然后，將光標(biāo)定位于C8中，執(zhí)行Excel菜單“工具”→“單變量求解”,在出現(xiàn)的單變量求解對(duì)話框中選擇1）目標(biāo)單元格中選擇C82）目標(biāo)值中輸入1539.293）可變單元格中選擇C54）按確定，C5將顯示貸款額度為46866.0721為單元格和范圍（圖表、圖形）命名是一個(gè)好辦法，它可以提供很多方便之處：名稱可以增強(qiáng)公式的可讀性，方便使用，容易記憶，不易出錯(cuò)，更為直觀。一、創(chuàng)建名稱方法有3種：使用“定義名稱”對(duì)話框“插入”→“名稱”→“定義”命令（添加）使用名稱框自動(dòng)創(chuàng)建“插入”→“名稱”→“指定”命令

（創(chuàng)建多工作表名稱）名稱的命名規(guī)則……3.10使用名稱22二、一個(gè)名稱的范圍

有：工作簿級(jí)名稱（全局名稱）工作表級(jí)名稱（本地名稱）三、使用范圍和單元格名稱工作

一旦創(chuàng)建了范圍和單元格名稱，就可以以不同方式來(lái)使用它們。

1、創(chuàng)建一個(gè)名稱列表使用“插入”菜單→“名稱”→“粘貼”命令（或F3），若需要粘貼一個(gè)名稱列表，單擊“粘貼列表”按鈕。23

2、在公式中使用名稱因單元格和范圍是工作簿級(jí)名稱，故可以在此工作簿中任何表中使用該名稱，即用名稱代替單元格引用。書寫公式時(shí)使用定義好的表單上工作表級(jí)名稱，就不需要把工作表名包括進(jìn)范圍名稱中。如果在不同的工作表公式中使用這個(gè)名稱，就一定要使用全名（表名、嘆號(hào)和名稱）。例如:=SUM（銷售額）

=SUM（單變量求解！銷售額）在鍵入公式時(shí)可以選擇“插入”→“名稱”→“粘貼命令（或F3），顯示“粘貼名稱”對(duì)話框，選擇列表中的名稱，單擊“確定”按鈕，Excel將插入這個(gè)名稱到公式中。243、使用帶名稱的交叉運(yùn)算符Excel的交叉運(yùn)算符是一個(gè)空格

例如：=SUM(B1:C20A8:D8)；兩個(gè)范圍交叉位置包含2個(gè)單元格：B8和C8.

=SUM(名1名2）

范圍交叉、隱式交叉4、使用帶名稱的范圍運(yùn)算符Excel的范圍運(yùn)算符是一個(gè)冒號(hào)（：）

例如：=SUM(DATA1:DATA2)5、使用多單元格命名范圍中的單個(gè)單元格

假定A1:A50被命名為DataRange，

有公式：=INDEX(DataRange，2);即顯示的是A2的值=INDEX(DATA3，4，5)；即返回第4行和第5列的值6、在現(xiàn)有公式中使用名稱

在示例工作表Sheet2中進(jìn)行操作。7、創(chuàng)建公式時(shí)自動(dòng)使用名稱當(dāng)通過指向的方法插入一個(gè)單元格或范圍引用到一個(gè)公式時(shí)，如果這個(gè)單元格有名稱，Excel可以自動(dòng)地應(yīng)用該單元格和范圍的名稱。258、不應(yīng)用名稱Excel沒有提供直接的方法實(shí)現(xiàn)不應(yīng)用名稱。就是說(shuō)，不能自動(dòng)地使用實(shí)際的單元格引用去替換公式中的名稱。（精簡(jiǎn)版無(wú)此功能）9、刪除名稱選擇“插入”→“名稱”→“定義”命令，顯示“定義名稱”對(duì)話框，在列表中選擇希望刪除的名稱，單擊“刪除”按鈕，即可。注意：刪除名稱的后果。10、刪除命名單元格或范圍

如果刪除包含命名單元格和范圍的行和列，這些名稱不會(huì)被刪除。此外每個(gè)名稱都包含一個(gè)無(wú)效引用。為了避免出錯(cuò)，需手工刪除這個(gè)名稱，或重新定義這個(gè)名稱。11、重新定義名稱

在定義好一個(gè)名稱后，也許希望改變引用的單元格和范圍。選擇“插入”→“名稱”→“定義”命令，顯示“定義名稱”對(duì)話框，適當(dāng)編輯，即可26

12、改變名稱選擇“插入”→“名稱”→“定義”命令，顯示“定義名稱”對(duì)話框，創(chuàng)建新名，刪除舊名。當(dāng)改變名稱的時(shí)候，Excel不能自動(dòng)改變調(diào)整使用名稱的公式，但是可以使用“編輯”→“替換”命令，查找并使用新名稱替換舊名稱。13、查看命名范圍當(dāng)縮放一個(gè)工作表到39%或更小的時(shí)候，你會(huì)看到命名范圍的邊界，同時(shí)包括使用藍(lán)色字體顯示的名稱。邊界和名稱是不能打印出來(lái)的，但它們有助于觀察工作中的命名范圍。14、在圖表中使用名稱當(dāng)創(chuàng)建一個(gè)圖表時(shí)，每個(gè)數(shù)據(jù)系列都有一個(gè)相關(guān)的SERIES公式，這個(gè)SERIES公式包含圖表中使用的范圍引用。如果定義了范圍名稱，就可編輯一個(gè)公式，并替換這個(gè)名稱的范圍引用。27四、Excel如何維護(hù)范圍和單元格名稱

一旦創(chuàng)建了范圍和單元格名稱，Excel在編輯或修改這個(gè)工作表的時(shí)候，可以自動(dòng)地維持你這個(gè)名稱。1、插入行或列

2、刪除一個(gè)行和列

3、剪切和粘貼

注意：如果刪