第3章 Excel公式基礎知識

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

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

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

★括號：它們可以控制著公式中各種表達式被處理的次序。

42、輸入公式

有兩種方法：手工輸入和通過制定單元格引用來輸入。公式一般都是以等號開頭，以回車結束。也可以用@作為起始符，但回車后，系統自動將@轉換成等號。按回車鍵后，單元格會顯示出公式的結果。當單元格被激活時，公式本身只會顯示在公式欄（編輯欄）中。

注意：

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

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

★雙擊單元格；★

按F2鍵；★

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

b公式→文本→公式

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

算術運算符：+、-、*、/、^、（%）。

比較運算符：=、>、<、<>（不等于），比較結果為真（True）和假（False）。

文本運算符：&

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

區(qū)域運算符：：聯合運算符：，

交叉運算符：空格

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

括號在Excel的內部優(yōu)先次序中排在最前面，且可以嵌套（輸入括號時，Excel有一個自動提示）。

優(yōu)先次序：-負號%百分號

^求冪

*和/乘和除

+和-加和減

&文本連接符

=

>

<

>=

<=

<>邏輯比較符8

◆=“Part-”&“23A”

；得到一個新的字符串Part-23A

◆=A1&A2

；將單元格A1和A2的內容連接在一起

◆=6^3；求6的3次方，結果為216

◆=A1^3

；單元格作為參數

◆=216^(1/3)

；求216的立方根，結果為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

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

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

；使用了嵌套括號

9103.3計算公式

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

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

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

※

Ctrl+Shift+F9：強制重新計算所有打開的工作簿

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

◆

注意：選擇“工具”→“選項”命令，可打開一個對話框，重新設置”重新計算“。113.4單元格和范圍引用

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

※

相對:是完全相對的引用。當公式被復制的時候，單元格引用將調整到新的位置上。例A1

※

絕對：是完全絕對的引用。當公式被復制的時候，單元格不會有變化，例如$A$1。※

行絕對：是部分絕對的引用。當公式被復制的時候，列部分調整，但是行部分不會有變化，例如A$1。※列絕對：是部分絕對的引用。當公式被復制的時候，行部分調整，但是列部分不會有變化，例如$A1。

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

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

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

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

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

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

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

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

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

4、按回車鍵結束編輯模式；

5、激活單元格A2；

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

有兩種方法：例如：將A1中的公式復制到A2。方法二：1、先刪除A1公式中的等號，即將公式轉換成了文本；

2、再選擇“復制”到A2，編輯原始公式，加上等號即可。3.6把公式轉換成值1、選中要轉換的一個或多個公式；

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

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

4、選擇“數值”單選項，單擊“確定”按鈕；

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

1、選中公式或多個公式

2、“格式”菜單→“單元格”。在“單元格格式”對話框中，單擊“保護”選項卡。

3、選中“隱藏”復選框。

4、選擇“工具”菜單→“保護”→“保護工作表”命令，來保護工作表，還可設置密碼。

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

#DIV/0：0作為除數

#NAME?：不能識別的名稱

#N/A：使用NA函數所識別的不能使用的數據

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

#NUM!：問題與值有關。如負值

#REF：公式中引用了一個無效的單元格

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

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

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

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

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

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

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

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

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

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

=SUM(名1名2）

范圍交叉、隱式交叉4、使用帶名稱的范圍運算符Excel的范圍運算符是一個冒號（：）

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

假定A1:A50被命名為DataRange，

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

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

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

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

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

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

2、刪除一個行和列

3、剪切和粘貼

注意：如果刪