1、【本章知識點(diǎn)】,公式的規(guī)則與編輯 公式本身及結(jié)果的查看 常見公式錯誤信息及原因 名稱的含義、作用與用法 建立名稱的幾種不同方式 單元格地址引用的三種方式 多表單元格之間的多維引用 數(shù)組公式的建立規(guī)則與應(yīng)用,本章目錄,3.1 公式及其應(yīng)用 3.2 單元格的引用 3.3名稱及其應(yīng)用 3.4數(shù)組公式及其應(yīng)用 小結(jié) 習(xí)題,3.1 公式及其應(yīng)用,3.1.1 公式的規(guī)則 所有的Excel公式都具有相同的基本結(jié)構(gòu)：一個等號（=）后面跟隨一個或多個運(yùn)算碼，運(yùn)算碼可以是值、單元格引用、單元格區(qū)域、區(qū)域名稱或者函數(shù)名，其間以一個或多個運(yùn)算符連接。這些運(yùn)算符包括算術(shù)運(yùn)算符、邏輯運(yùn)算符或者字符連接符（&）等。 Exc

2、el在一個公式中允許使用的最大字符數(shù)為1024個。公式中運(yùn)算符與運(yùn)算碼之間可以加入空格，以便分隔公式中的各個部分，從而提高公式的可讀性。 說明：在寫長公式時，可以將公式多行顯示，操作時只要單擊希望斷行的位置，然后按下【Alt+Enter】組合鍵即可。,3.1.2 公式的編輯,1公式的輸入 （1）選中要輸入公式的單元格； （2）輸入“=”作為準(zhǔn)備輸入公式的開始； （3）輸入組成該公式的所有運(yùn)算碼和運(yùn)算符； （4）按回車鍵對輸入的公式表示確認(rèn)。 2公式的修改 （1）按F2鍵，可以直接編輯修改單元格中的內(nèi)容； （2）雙擊該單元格，可以直接編輯修改單元格中的內(nèi)容； （3）選擇需要進(jìn)行編輯的單元格，然后

3、單擊編輯欄，在編輯欄中對公式進(jìn)行修改。 （4）如果公式的單元格返回一個錯誤，Excel會在單元格的左上角顯示一個小方塊。激活單元格，可以看到一個智能標(biāo)簽。單擊該智能標(biāo)簽，可選擇一個選項來更正錯誤。 當(dāng)編輯結(jié)束后按【Enter】鍵即可完成操作。如果要取消編輯操作，可以按Esc鍵。 3公式的移動和復(fù)制 公式移動和復(fù)制的操作方法與單元格移動和復(fù)制類似，,3.1.3 公式的運(yùn)算符號與優(yōu)先順序,Excel公式根據(jù)其運(yùn)算符的類型可以分為四組：數(shù)學(xué)公式、比較公式、字符公式和引用公式。每組公式都有其自己的運(yùn)算符和使用方式 。 數(shù)學(xué)公式 比較公式 字符公式 引用公式,3.1.4 公式結(jié)果的查看,1.查看公式的結(jié)

4、果 Excel中，公式輸入完成后，按下【Enter】，公式的結(jié)果會直接在單元格中顯示，需要查看公式時，選中單元格，在編輯框中可以看到公式。 2.公式和結(jié)果的切換 通過單擊“工具”/“選項”命令，在出現(xiàn)的“選項”對跨框中選擇“視圖”標(biāo)簽，選中“公式”復(fù)選框可以來顯示所有的公式本身，而不是在單元格顯示公式結(jié)果。 3.以結(jié)果替換公式 在Excel中，公式和結(jié)果是兩種不同的類型，當(dāng)希望把公式得到的結(jié)果固定下來時，可以把公式轉(zhuǎn)換成它的值，這樣可以提高大型工作表重新計算的速度，并可以釋放此工作表使用的一部分內(nèi)存空間 。,3.1.5 公式返回的錯誤信息返回值,3.1.6 公式的循環(huán)引用問題,在輸入公式的過程

5、中，有時候會引起一個循環(huán)引用。不管是直接引用或者是間接引用，當(dāng)一個公式引用自己的單元格里面的數(shù)據(jù)時往往會發(fā)生循環(huán)引用。比如，在單元格A6里面輸入=Sum（A1:A6）就會創(chuàng)建一個循環(huán)引用，因為該公式引用了包含該公式的單元格。 大多數(shù)循環(huán)引用錯誤是由簡單排版錯誤或者不正確的范圍定義引起的 。某些情況下，循環(huán)引用設(shè)置的合適，可以相當(dāng)于一個函數(shù)，它等同于VBA編程語言中的Do-Loop語句。 有意圖的循環(huán)引用把遞歸或者迭代引入到問題中。每一個循環(huán)引用計算的中間“答案”都會作用于后續(xù)的計算，最后結(jié)果收斂到最終值。,3.2 單元格的引用,3.2.1 對單元格地址的三種引用方式 絕對引用 相對引用 混合引

6、用 （F4切換） 3.2.2 跨表格數(shù)據(jù)的引用 1.引用同一工作薄其他工作表中的單元格或區(qū)域 工作表標(biāo)簽名!單元格地址 2.引用同一工作薄多張工作表的相同單元格或區(qū)域 第一個工作表名：最后一個工作表名! 單元格地址 3.不同工作薄之間的數(shù)據(jù)引用 工作簿名工作表標(biāo)簽名! 單元格地址,3.2.3 單元格引用的綜合應(yīng)用舉例,實(shí)例1：計算提成 絕對引用與相對引用的適當(dāng)選取 實(shí)例2：產(chǎn)品費(fèi)用分配 混合引用的巧妙使用 實(shí)例3：學(xué)生成績計算 跨工作表/簿之間的單元格引用,3.3名稱及其應(yīng)用,3.3.1 使用名稱的好處 1. 使公式含義更容易理解。 2. 提高公式編輯的準(zhǔn)確性。 3. 快速定位到特定位置。 4

7、. 名稱可以作為變量來使用。 5.可以方便地應(yīng)用于所有的工作表。 6.可以方便地使用另一個工作薄中的定義名稱，或者定義一個引用了其他工作薄中單元格的名稱。 7.使用區(qū)域名稱比單元格地址更容易創(chuàng)建和保持宏。,3.3.2 名稱的命名規(guī)則,（1）名稱的第一個字符必須是字母或下劃線，不能使用單元格地址或阿拉伯?dāng)?shù)字。 （2）名稱中的字符可以是字母、數(shù)字，不能含有空格，可以使用下劃線和句點(diǎn)。 （3）名稱的長度不能超過255個字符。 （4）名稱中不能使用除下劃線和句點(diǎn)以外的其它任何符號。 （5）名稱中的字母不區(qū)分大小寫。例如，如果已經(jīng)創(chuàng)建了名稱Sales，接著又在同一工作薄中創(chuàng)建了名稱SALES，則第二個名

8、稱將替換第一個。 （6）命名時，不要使用Excel內(nèi)部名稱。,3.3.3 定義單元格區(qū)域的名稱,Excel中名稱的定義有三種基本方法，分別是：利用“插入”“名稱”“定義”命令；利用“插入”“名稱”“指定”命令；利用名稱框；另外還可以對已經(jīng)定義的名稱進(jìn)行合并。 1. 使用定義命令：可以任意定義名稱 .使用指定命令：根據(jù)表格的行標(biāo)題或列標(biāo)題定義名稱 3.使用名稱框：定義名稱的快捷方式 4.合并名稱：將多個已有的名稱合并為一個新名稱,3.3.4 定義常量名稱,在Excel中，除了為單元格或區(qū)域命名以外，還可對常量和公式定義名稱，以便使用。 1.為常量數(shù)值定義名稱 2.為公式定義名稱 3. 為文本常數(shù)

9、定義名稱 說明：沒有引用范圍的名稱無法在名稱框或“定位”對話框（按【F5】可以進(jìn)入）中顯示出來，因為這些常數(shù)不能駐留在任何實(shí)質(zhì)性的位置上。但是它們可以出現(xiàn)在“粘貼名稱”對話框中，因為以后需要在公式中使用這些名稱。,3.3.5 名稱的查看、修改和刪除,1.名稱清單的查看 定義了很多名稱之后，有時可能弄不清楚這些名稱所引用的到底是哪些單元格或者單元格區(qū)域。為此，可以將工作表中定義的所有名稱清單輸入到某一工作表上，以備檢查這些名稱所引用的單元格區(qū)域。 2.名稱的修改 隨著工作的進(jìn)行，有時會發(fā)現(xiàn)需要對單元格的名稱進(jìn)行修改操作。這里對名稱的修改操作包括名稱的重命名和更改名稱指代的區(qū)域兩種。 3.名稱的刪

10、除 對于工作表中無用的名稱，可以進(jìn)行刪除操作。如果要刪除一個名稱，只需要在“定義名稱”對話框中選擇一個名稱項目，然后點(diǎn)擊“刪除”按鈕就可以了。,3.3.6 名稱的使用,1利用名稱快速定位單元格區(qū)域 方法一：在名稱框中選擇某個名稱，按回車鍵，。 方法二：單擊【F5】或者【Ctrl+G】鍵，打開“定位”對話框，選擇某個名稱，然后單擊“確定”按鈕。 2在建立公式或函數(shù)時使用名稱 可以直接在公式或者函數(shù)中輸入時使用已經(jīng)定義好的名稱。在公式中使用名稱時，先輸入等號（），再在公式中輸入名稱；在函數(shù)中使用名稱時，先輸入函數(shù)名以及左括號，然后輸入名稱作為函數(shù)的某個參數(shù)。 3在現(xiàn)有的公式上套用事后定義的名稱 4

11、 .使用名稱查詢數(shù)據(jù) 5 .將整行或整列定義名稱,3.3.7 名稱應(yīng)用的綜合舉例,1學(xué)生成績計算 為單個單元格定義名稱 2節(jié)日倒計時 為常量和公式定義名稱的使用 3庫存計算 為整列定義名稱并為相對單元格定義名稱 4公司上半年產(chǎn)品匯總計算 為多張工作表的相同單元格區(qū)域定義名稱,3.4數(shù)組公式及其應(yīng)用,3.4.1 數(shù)組公式的建立 1.數(shù)組公式的輸入 數(shù)組公式與一般公式不同之處在于它被括在大括號（）中，其輸入步驟如下： （1）選中一個單元格或者單元格區(qū)域。 說明：如果數(shù)組公式只是返回一個結(jié)果，需要選擇保存用來保存結(jié)果的那一個單元格；如果數(shù)組公式返回多個結(jié)果，則需要選中需要保留數(shù)組公式計算結(jié)果的單元格

12、區(qū)域。 （2）按照前面介紹的公式輸入規(guī)則，輸入公式的內(nèi)容。 （3）公式輸完后，按【Ctrl+Shift+Enter】組合鍵結(jié)束操作。,2.使用數(shù)組公式的好處,使用數(shù)組公式具有以下優(yōu)越性： （1）數(shù)組公式可以節(jié)省存儲空間。 （2）可以保證在同一個范圍內(nèi)的公式具有同一性。 （3）減少了意外覆蓋公式的可能性，因為數(shù)組公式中無法修改一個單元格的內(nèi)容。 （4）可以完全防止一位新手篡改你的公式，因為無法刪除數(shù)組公式中的一部分。,3.數(shù)組常量的輸入,在數(shù)組公式中，通常都使用單元格區(qū)域引用，但是也可以直接鍵入數(shù)值數(shù)組，這樣輸入的數(shù)值數(shù)組被稱為數(shù)組常量。 數(shù)組常量的中輸入內(nèi)容要遵守一定的規(guī)則，具體包括：數(shù)組常量

13、中可包含數(shù)字、文本、邏輯值（如 TRUE、FALSE 或錯誤值 #N/A）。例如，1，3，4；TRUE，F(xiàn)ALSE，TRUE。 另外，數(shù)組常量中的數(shù)字可以是整數(shù)、小數(shù)或科學(xué)記數(shù)格式，而文本必須包含在半角的雙引號之內(nèi)，如Tuesday。數(shù)組常量中不能含有單元格引用，不能含有特殊字符#、）分開。 （3）數(shù)值輸完后，按【Ctrl+Shift+Enter】組合鍵結(jié)束操作。,3.4.2 數(shù)組公式的編輯,數(shù)組公式在編輯方面，與一般公式的一大不同之處在于：不能單獨(dú)對數(shù)組公式所涉及的單元格區(qū)域中的某一單元格進(jìn)行編輯、清除或者移動操作。具體來說，包括以下問題： （1）不能修改組成數(shù)組公式的任一個別單元格的內(nèi)容（

14、但可修改整個數(shù)組公式）。 （2）不能移動組成數(shù)組公式某一部分的單元格（但可以移動整個數(shù)組公式）。 （3）不能刪除組成數(shù)組公式某一部分的單元格（可以刪除整個數(shù)組公式）。 （4）不能向一個數(shù)組范圍內(nèi)插入新的單元格，包括插入新行或新列。,1. 修改數(shù)組公式,數(shù)組公式輸入完畢后，如果又發(fā)現(xiàn)錯誤需要進(jìn)行修改，則需按以下步驟操作： （1）選中任意一個包含數(shù)組公式的單元格； （2）單擊編輯欄或按【F2】鍵，使編輯欄處于活動狀態(tài)，此時大括號（ ）會從數(shù)組公式上去掉； （3）編輯修改數(shù)組公式內(nèi)容； （4）修改完畢后，按【Ctrl+Shift+Enter】組合鍵結(jié)束操作。,1. 修改數(shù)組公式,2. 移動數(shù)組公式

15、要把數(shù)組公式移到另一個位置，需要先選中整個數(shù)組公式所包括的范圍，然后把整個區(qū)域拖放到目標(biāo)位置，也可通過“剪切”和“粘貼”菜單命令進(jìn)行。 3. 刪除數(shù)組公式 刪除數(shù)組公式的方法是：首先選定存放數(shù)組公式的所有單元格，單后按【Delete】鍵。,3.4.3 二維數(shù)組的應(yīng)用,在實(shí)際應(yīng)用中，往往會涉及到多行或者多列的數(shù)據(jù)處理，這就是所謂的二維數(shù)組。 Excel支持二維數(shù)組的各種運(yùn)算，如加、減、乘、除等。在實(shí)際應(yīng)用中，二維數(shù)組的應(yīng)用是非常廣泛的。 合理的應(yīng)用二維數(shù)組運(yùn)算，能夠提高數(shù)據(jù)處理的能力，特別是在不同工作表之間的數(shù)據(jù)匯總時非常高效。,3.4.4 數(shù)組公式的擴(kuò)展,在公式中使用數(shù)組作為參數(shù)時，一般來說所

16、用的數(shù)組必須是同維的。但是，如果數(shù)組參數(shù)或數(shù)組區(qū)域的維數(shù)不匹配，Excel也會自動擴(kuò)展該參數(shù)。如圖3-53所示， A1：D9單元格區(qū)域為某公司銷售產(chǎn)品的原價格，目前因為原材料漲價，公司準(zhǔn)備為所有產(chǎn)品漲價，其中漲價幅度存儲在F2單元格中（目前為5%）；圖中H1：K9就是利用數(shù)組公式計算出來的漲價后的價格。,3.4.5 數(shù)組公式的應(yīng)用舉例,1.實(shí)例1：單個單元格使用數(shù)組公式 詳見本書教材和相關(guān)素材 2. 實(shí)例2：用數(shù)組公式計算多個數(shù)據(jù)區(qū)域的和 詳見本書教材和相關(guān)素材 3. 實(shí)例3：數(shù)組公式的擴(kuò)展應(yīng)用 詳見本書教材和相關(guān)素材 數(shù)組的應(yīng)用非常靈活，尤其是可以作為很多函數(shù)的參數(shù)，它在簡化數(shù)據(jù)運(yùn)算，提高數(shù)

17、據(jù)處理效率方面能發(fā)揮很大的作用，這在后續(xù)相關(guān)章節(jié)會有更為詳細(xì)的應(yīng)用和說明。,小 結(jié),本章介紹Excel中公式輸入、編輯、修改與保密，引用的各種方式以及名稱的定義、修改、刪除與應(yīng)用等相關(guān)知識。 輸入公式之前必須先輸入“=”；如果公式中有多個運(yùn)算符的話，Excel按照預(yù)定的運(yùn)算符優(yōu)先級順序進(jìn)行計算。 公式編輯中，經(jīng)常會因為各種不同原因，產(chǎn)生各種不同錯誤，必須掌握常見錯誤信息的返回值，并根據(jù)實(shí)際錯誤原因進(jìn)行修改。 在Excel中，一般情況下要避免在公式中出現(xiàn)“循環(huán)引用”，但是，有時候也故意利用公式中循環(huán)引用，實(shí)現(xiàn)某些特定功能，注意此時需要把“選項”/“重新計算”/“迭代計算”復(fù)選框選中。 Excel提供了三種引用方式：絕對引用、相對引用和混合引用。 單元格的引用也可以跨表格，可以引用同一工作薄中不同工作表的數(shù)據(jù)，也可以引用不同工作薄中的數(shù)據(jù)。,小 結(jié) （續(xù)）,Excel中可以使用名稱來表示單元格、單元格區(qū)域、常量或公式，創(chuàng)建名稱時必須遵循名稱的命名規(guī)則。名稱一旦定義之后，可以在公式、運(yùn)算符、圖表中使用名稱。 Excel可以在公式中使用數(shù)組，數(shù)組公式建立完之后一定要按【Ctrl+Shift+Enter】組合鍵確定，使用數(shù)組公式一定要注意不能像單個公式那樣修改和刪