4.1　公式和函數(shù)基礎(chǔ)

在開始介紹特定的函數(shù)之前，需要先了解公式和函數(shù)的一些基本概念和知識(shí)，它們是深入學(xué)習(xí)公式和函數(shù)的基礎(chǔ)。

4.1.1　公式的組成

Excel中的公式由等號(hào)、常量、運(yùn)算符、單元格引用、函數(shù)、定義的名稱等內(nèi)容組成，在一個(gè)公式中可以包含這些內(nèi)容中的部分或全部。Excel中的任何公式都必須以等號(hào)開頭，然后輸入公式包含的其他內(nèi)容。

常量就是字面量，它是一個(gè)值，可以是文本、數(shù)值或日期，例如Excel、666、2020年10月1日。單元格引用就是單元格地址，可以是單個(gè)單元格地址，也可以是單元格區(qū)域的地址，例如A1、A2:B6。函數(shù)通常是指Excel內(nèi)置函數(shù)，例如SUM、LEFT、LOOKUP。名稱是用戶在Excel中創(chuàng)建的，可以將名稱看作是命名的公式，因此在公式中包含的內(nèi)容也可以出現(xiàn)在名稱中。使用名稱可以簡(jiǎn)化公式的輸入量，并使公式易于理解。

運(yùn)算符用于連接公式中的各個(gè)部分，并執(zhí)行不同類型的計(jì)算，例如“+”運(yùn)算符用于計(jì)算兩個(gè)數(shù)字的和，“*”運(yùn)算符用于計(jì)算兩個(gè)數(shù)字的積。不同類型的運(yùn)算符具有不同的運(yùn)算次序，將這種次序稱為運(yùn)算符的優(yōu)先級(jí)。

4.1.2　運(yùn)算符及其優(yōu)先級(jí)

Excel中的運(yùn)算符包括算術(shù)運(yùn)算符、文本連接運(yùn)算符、比較運(yùn)算符、引用運(yùn)算符4種類型。表4-1列出了按優(yōu)先級(jí)從高到低的順序排列的運(yùn)算符，即引用運(yùn)算符的優(yōu)先級(jí)最高，比較運(yùn)算符的優(yōu)先級(jí)最低。

表4-1　Excel中的運(yùn)算符及其說明

如果一個(gè)公式中包含多個(gè)不同類型的運(yùn)算符，Excel將按照這些運(yùn)算符的優(yōu)先級(jí)對(duì)公式中的各個(gè)部分進(jìn)行計(jì)算；如果一個(gè)公式包含多個(gè)具有相同優(yōu)先級(jí)的運(yùn)算符，Excel將按照運(yùn)算符在公式中出現(xiàn)的位置，從左到右對(duì)各部分進(jìn)行計(jì)算。

例如，下面公式的計(jì)算結(jié)果為11，由于“*”和“/”這兩個(gè)運(yùn)算符的優(yōu)先級(jí)高于“+”運(yùn)算符，因此先計(jì)算10×3，再將得到的結(jié)果30除以6，最后將得到的結(jié)果5加6，最終結(jié)果為11。

     =6+10*3/6

如果想要先計(jì)算低優(yōu)先級(jí)的加法，即6+10部分，則可以使用圓括號(hào)提升運(yùn)算符的優(yōu)先級(jí)，使低優(yōu)先級(jí)的運(yùn)算符先進(jìn)行計(jì)算。下面的公式將6+10放到一對(duì)圓括號(hào)中，使“+”運(yùn)算符先于“*”和“/”運(yùn)算符進(jìn)行計(jì)算，因此該公式的計(jì)算結(jié)果為8，即6+10=16，16*3=48，48/6=8。

     =(6+10)*3/6

提示：當(dāng)公式中包含嵌套的圓括號(hào)時(shí)，即一對(duì)圓括號(hào)位于另一對(duì)圓括號(hào)的內(nèi)部。在這種情況下，嵌套圓括號(hào)的計(jì)算順序是從最內(nèi)層的圓括號(hào)逐級(jí)向外層圓括號(hào)進(jìn)行計(jì)算。

4.1.3　輸入和修改公式

輸入公式與輸入普通數(shù)據(jù)的方法類似，可以參考第2.1.2節(jié)中的內(nèi)容。輸入公式時(shí)可以在“輸入”“編輯”和“點(diǎn)”3種模式之間隨意切換。輸入公式中的所有內(nèi)容后，按Enter鍵結(jié)束輸入，將得出計(jì)算結(jié)果。

如果要輸入新的公式代替單元格中的現(xiàn)有公式，只需選擇包含公式的單元格，然后輸入新的公式并按Enter鍵。如果要修改公式中的部分內(nèi)容，則應(yīng)先選擇包含公式的單元格，然后使用以下方法進(jìn)入“編輯”模式：

·　按F2鍵。

·　雙擊單元格。

·　單擊編輯欄。

修改公式后，按Enter鍵保存修改結(jié)果。如果在修改時(shí)按Esc鍵，則會(huì)放棄當(dāng)前做的所有修改并退出“編輯”模式。

4.1.4　移動(dòng)和復(fù)制公式

用戶可以將單元格中的公式移動(dòng)或復(fù)制到其他位置，方法類似于移動(dòng)和復(fù)制普通數(shù)據(jù)。填充數(shù)據(jù)的方法也同樣適用于公式，通過拖動(dòng)包含公式的單元格右下角的填充柄，可以在一行或一列中復(fù)制公式。也可以雙擊填充柄，將公式快速復(fù)制到與相鄰的行或列中最后一個(gè)連續(xù)數(shù)據(jù)相同的位置上。

如果在復(fù)制的公式中包含單元格引用，那么單元格引用的類型將影響復(fù)制后的公式。Excel中的單元格引用類型分為相對(duì)引用、絕對(duì)引用、混合引用3種，通過單元格地址中是否包含“$”符號(hào)，可以從外觀上區(qū)分3種單元格引用類型。

如果同時(shí)在單元格地址的行號(hào)和列標(biāo)的左側(cè)添加“$”符號(hào)，則該單元格的引用類型是絕對(duì)引用，例如$A$1。如果在單元格地址的行號(hào)和列標(biāo)的左側(cè)都沒有“$”符號(hào)，則該單元格的引用類型是相對(duì)引用，例如A1。如果只在單元格地址的行號(hào)的左側(cè)添加“$”符號(hào)，則該單元格的引用類型是混合引用，即列相對(duì)引用、行絕對(duì)引用，例如A$1。如果只在單元格地址的列標(biāo)的左側(cè)添加“$”符號(hào)，則該單元格的引用類型也是混合引用，即列絕對(duì)引用、行相對(duì)引用，例如$A1。

用戶可以在單元格地址中通過手動(dòng)輸入“$”符號(hào)改變單元格的引用類型。更簡(jiǎn)單的方法是在單元格或編輯欄中選中單元格地址，通過反復(fù)按F4鍵在各個(gè)引用類型之間切換。如果A1單元格最初為相對(duì)引用，使用下面的方法將在不同的引用類型之間切換：

·　按1次F4鍵，將相對(duì)引用轉(zhuǎn)換為絕對(duì)引用，即A1→$A$1。

·　按2次F4鍵：將相對(duì)引用轉(zhuǎn)換為行絕對(duì)引用、列相對(duì)引用，即A1→A$1。

·　按3次F4鍵：將相對(duì)引用轉(zhuǎn)換為行相對(duì)引用、列絕對(duì)引用，即A1→$A1。

·　按4次F4鍵：?jiǎn)卧竦囊妙愋突謴?fù)為最初的相對(duì)引用。

在將公式從一個(gè)單元格復(fù)制到另一個(gè)單元格時(shí)，公式中的絕對(duì)引用單元格地址不會(huì)改變，而相對(duì)引用單元格地址則會(huì)根據(jù)公式復(fù)制到目標(biāo)單元格與原始單元格之間的相對(duì)位置，自動(dòng)調(diào)整復(fù)制公式后的單元格地址。

例如，如果B1單元格中的公式為“=A1+6”，將公式復(fù)制到C3單元格后，公式變?yōu)椤?B3+6”，原來的A1自動(dòng)變?yōu)锽3，如圖4-1所示。這是因?yàn)楣接葿1復(fù)制到C3，相當(dāng)于從B1向下移動(dòng)2行，向右移動(dòng)1列，從而到達(dá)C3。由于公式中的A1是相對(duì)引用，因此該單元格也要向下移動(dòng)2行，向右移動(dòng)1列，最終到達(dá)B3。

圖4-1　相對(duì)引用對(duì)復(fù)制公式的影響

如果單元格的引用類型是混合引用，則在復(fù)制公式時(shí)，只改變相對(duì)引用的部分，絕對(duì)引用的部分保持不變。繼續(xù)使用上面的示例進(jìn)行說明，如果B1單元格中的公式為“=A$1+6”，將該公式復(fù)制到C3單元格后，公式將變?yōu)椤?B$1+6”，如圖4-2所示。由于原來的A$1是行絕對(duì)引用、列相對(duì)引用，因此復(fù)制后只改變列的位置。

圖4-2　混合引用對(duì)復(fù)制公式的影響

4.1.5　更改公式的計(jì)算方式

在修改公式中的內(nèi)容后，按Enter鍵將得到最新的計(jì)算結(jié)果。如果工作表中包含使用隨機(jī)數(shù)函數(shù)的公式，則在編輯其他單元格并結(jié)束編輯后，隨機(jī)數(shù)函數(shù)的值會(huì)自動(dòng)更新。這是因?yàn)镋xcel的計(jì)算方式默認(rèn)為“自動(dòng)”。

如果工作表中包含大量的公式，這種自動(dòng)重算功能將會(huì)嚴(yán)重影響Excel的整體性能。此時(shí)，可以將計(jì)算方式改為“手動(dòng)”，只需在功能區(qū)的“公式”選項(xiàng)卡中單擊“計(jì)算選項(xiàng)”按鈕，然后在彈出的菜單中選擇“手動(dòng)”，如圖4-3所示。

提示：如果將計(jì)算方式設(shè)置為“除模擬運(yùn)算表外，自動(dòng)重算”，則在Excel重新計(jì)算公式時(shí)會(huì)自動(dòng)忽略模擬運(yùn)算表的相關(guān)公式。

將計(jì)算方式設(shè)置為“手動(dòng)”后，如果工作表中存在任何未計(jì)算的公式，則會(huì)在狀態(tài)欄中顯示“計(jì)算”，此時(shí)可以使用以下方法對(duì)公式執(zhí)行計(jì)算：

·　在功能區(qū)的“公式”選項(xiàng)卡中單擊“開始計(jì)算”按鈕，或按F9鍵，將重新計(jì)算所有打開工作簿中的所有工作表的未計(jì)算的公式，如圖4-4所示。

·　在功能區(qū)的“公式”選項(xiàng)卡中單擊“計(jì)算工作表”按鈕，或按Shift+F9快捷鍵，將重新計(jì)算當(dāng)前工作表中的公式。

·　按Ctrl+Alt+F9快捷鍵，將重新計(jì)算所有打開工作簿中所有工作表的公式，包括已計(jì)算和未計(jì)算的所有公式。

·　按Ctrl+Shift+Alt+F9快捷鍵，將重新檢查相關(guān)的公式，并重新計(jì)算所有打開工作簿中所有工作表的公式，無論這些公式是否需要重新計(jì)算。

圖4-3　更改公式的計(jì)算方式

圖4-4　單擊“開始計(jì)算”按鈕

4.1.6　在公式中輸入函數(shù)及其參數(shù)

Excel提供了內(nèi)置函數(shù)，用于執(zhí)行不同類型的計(jì)算，表4-2列出了Excel中的函數(shù)類別及其說明。為了使函數(shù)的名稱可以準(zhǔn)確地描述函數(shù)的功能，從Excel 2010開始微軟公司修改了Excel早期版本中的一些函數(shù)名稱，并改進(jìn)了一些函數(shù)的性能和計(jì)算精度。后來的Excel版本仍然沿用Excel 2010的函數(shù)命名方式。

表4-2　Excel中的函數(shù)類別及其說明

為了保持與Excel早期版本的兼容性，在Excel 2010及更高版本的Excel中保留了重命名前的函數(shù)，可以在功能區(qū)的“公式”選項(xiàng)卡中單擊“其他函數(shù)”按鈕，然后在彈出的菜單中選擇“兼容性”命令，在打開的下拉列表中可以找到這些函數(shù)，如圖4-5所示。

圖4-5　兼容性函數(shù)

重命名后的函數(shù)名稱通常是在原有函數(shù)名稱中間的某個(gè)位置添加半角句點(diǎn)（.），有的函數(shù)會(huì)在其原有名稱的結(jié)尾添加包含半角句點(diǎn)在內(nèi)的擴(kuò)展名。例如，NORMSDIST是Excel 2003中的標(biāo)準(zhǔn)正態(tài)累積分布函數(shù)，在Excel 2010及更高版本的Excel中將該函數(shù)重命名為NORM.S.DIST。

在關(guān)閉一些工作簿時(shí)，可能會(huì)顯示用戶是否保存工作簿的提示信息。即使在打開工作簿后未進(jìn)行任何修改，關(guān)閉工作簿時(shí)仍然會(huì)顯示這類提示信息。出現(xiàn)這種情況通常是由于在工作簿中使用了易失性函數(shù)。在工作表的任意一個(gè)單元格中輸入或編輯數(shù)據(jù)，甚至只是打開工作簿這樣的簡(jiǎn)單操作，工作表中的易失性函數(shù)都會(huì)自動(dòng)重新計(jì)算。此時(shí)關(guān)閉工作簿，Excel會(huì)認(rèn)為工作簿處于未保存狀態(tài)，因此會(huì)顯示是否保存的提示信息。常見的易失性函數(shù)有TODAY、NOW、RAND、RANDBETWEEN、OFFSET、INDIRECT、CELL等。

下面的操作不會(huì)觸發(fā)易失性函數(shù)的自動(dòng)重算：

·　將計(jì)算方式設(shè)置為“手動(dòng)計(jì)算”。

·　設(shè)置單元格格式或其他顯示方面的選項(xiàng)。

·　輸入或編輯單元格時(shí)，按Esc鍵取消本次輸入或編輯操作。

·　使用除鼠標(biāo)雙擊外的其他方法調(diào)整單元格的行高和列寬。

無論在Excel中使用哪個(gè)函數(shù)，首先都需要掌握在公式中輸入函數(shù)的基本方法，有以下幾種：

·　手動(dòng)輸入函數(shù)。

·　使用功能區(qū)中的函數(shù)命令。

·　使用“插入函數(shù)”對(duì)話框。

1．手動(dòng)輸入函數(shù)

如果知道要使用的函數(shù)的完整拼寫，則可以直接在公式中輸入函數(shù)。當(dāng)用戶在公式中輸入函數(shù)的前幾個(gè)字母時(shí)，Excel將顯示與用戶輸入相匹配的函數(shù)列表。用戶可以滾動(dòng)鼠標(biāo)滾輪或使用鍵盤上的方向鍵選擇所需的函數(shù)，然后按Tab鍵將該函數(shù)添加到公式中，如圖4-6所示。

將函數(shù)添加到公式后，Excel將自動(dòng)在函數(shù)名的右側(cè)添加一個(gè)左圓括號(hào)，并在函數(shù)名的下方以粗體格式顯示當(dāng)前需要輸入的參數(shù)信息，方括號(hào)包圍的參數(shù)是可選參數(shù)，如圖4-7所示。輸入函數(shù)的所有參數(shù)后，需要輸入一個(gè)右圓括號(hào)作為函數(shù)的結(jié)束標(biāo)志。

圖4-6　輸入函數(shù)時(shí)自動(dòng)顯示匹配的函數(shù)列表

圖4-7　將函數(shù)輸入到公式中

提示：無論用戶在輸入函數(shù)時(shí)使用的是大寫字母還是小寫字母，只要輸入拼寫正確的函數(shù)名，按下Enter鍵后，函數(shù)名會(huì)自動(dòng)轉(zhuǎn)換為大寫字母形式。

2．使用功能區(qū)中的函數(shù)命令

在功能區(qū)的“公式”選項(xiàng)卡的“函數(shù)庫”組中，每一類函數(shù)都作為一個(gè)按鈕顯示在該組中。單擊這些按鈕，可以在彈出的菜單中選擇特定類別的函數(shù)。如圖4-8所示為從“文本”函數(shù)類別中選擇的LEFT函數(shù)，當(dāng)鼠標(biāo)指針指向某個(gè)函數(shù)時(shí)，將自動(dòng)顯示關(guān)于該函數(shù)的功能及其包含的參數(shù)的簡(jiǎn)要說明。

選擇一個(gè)函數(shù)后，將打開“函數(shù)參數(shù)”對(duì)話框，其中顯示了函數(shù)包含的各個(gè)參數(shù)，用戶需要在相應(yīng)的文本框中輸入?yún)?shù)的值，可以單擊文本框右側(cè)的按鈕在工作表中選擇單元格或區(qū)域，每個(gè)參數(shù)的值顯示在文本框的右側(cè)，下方顯示使用當(dāng)前函數(shù)對(duì)各個(gè)參數(shù)計(jì)算后的結(jié)果，如圖4-9所示。輸入?yún)?shù)值后，單擊“確定”按鈕，即可將包含參數(shù)的函數(shù)添加到公式中。

圖4-8　在功能區(qū)中選擇要使用的函數(shù)

圖4-9　設(shè)置函數(shù)的參數(shù)值

3．使用“插入函數(shù)”對(duì)話框

單擊編輯欄左側(cè)的按鈕，打開“插入函數(shù)”對(duì)話框，在“搜索函數(shù)”文本框中輸入關(guān)于計(jì)算目的或函數(shù)功能的描述信息，然后單擊“轉(zhuǎn)到”按鈕，Excel將顯示與輸入內(nèi)容相匹配的函數(shù)，如圖4-10所示。

圖4-10　通過輸入描述信息找到匹配的函數(shù)

在“選擇函數(shù)”列表框中選擇所需的函數(shù)，然后單擊“確定”按鈕，在打開的“函數(shù)參數(shù)”對(duì)話框中輸入?yún)?shù)的值即可。

在前面介紹輸入函數(shù)時(shí)，都涉及了函數(shù)的參數(shù)。每個(gè)函數(shù)由函數(shù)名、一對(duì)圓括號(hào)以及位于圓括號(hào)中的一個(gè)或多個(gè)參數(shù)組成，各個(gè)參數(shù)之間使用半角逗號(hào)分隔，形式如下：

     函數(shù)名(參數(shù)1,參數(shù)2,…,參數(shù)n）

參數(shù)為函數(shù)提供要計(jì)算的數(shù)據(jù)，用戶需要根據(jù)函數(shù)語法中的參數(shù)位置，依次輸入相應(yīng)類型的數(shù)據(jù)，才能使函數(shù)正確計(jì)算并得出結(jié)果，否則將返回錯(cuò)誤值或根本無法計(jì)算。在輸入不包含參數(shù)的函數(shù)時(shí)，需要輸入函數(shù)名和一對(duì)圓括號(hào)。

參數(shù)的值可以有多種形式，包括以常量形式輸入的數(shù)值或文本、單元格引用、數(shù)組、名稱或函數(shù)。將一個(gè)函數(shù)作為另一個(gè)函數(shù)的參數(shù)形式稱為嵌套函數(shù)。

在為某些函數(shù)指定參數(shù)值時(shí)，并非必須提供函數(shù)語法中列出的所有參數(shù)，這是因?yàn)閰?shù)分為必選參數(shù)和可選參考兩種。

·　必選參數(shù)：必須指定必選參數(shù)的值。

·　可選參數(shù)：可以忽略可選參數(shù)的值。在單元格輸入函數(shù)時(shí)顯示的函數(shù)語法中，使用方括號(hào)標(biāo)記的參數(shù)就是可選參數(shù)，如圖4-11所示。例如，SUM函數(shù)最多可以包含255個(gè)參數(shù)，只有第一個(gè)參數(shù)是必選參數(shù)，其他參數(shù)都是可選參數(shù)，因此可以只指定第一個(gè)參數(shù)的值，而省略其他254個(gè)參數(shù)。

圖4-11　使用方括號(hào)標(biāo)記可選參數(shù)

對(duì)于包含可選參數(shù)的函數(shù)，如果在可選參數(shù)之后還有參數(shù)，則在不指定前一個(gè)可選參數(shù)而直接指定其后的可選參數(shù)時(shí)，必須保留前一個(gè)可選參數(shù)的逗號(hào)占位符。例如，OFFSET函數(shù)包含5個(gè)參數(shù)，前3個(gè)參數(shù)是必選參數(shù)，后兩個(gè)參數(shù)是可選參數(shù)，當(dāng)不指定該函數(shù)的第4個(gè)參數(shù)而指定第5個(gè)參數(shù)時(shí)，必須保留第4個(gè)參數(shù)與第5個(gè)參數(shù)之間的半角逗號(hào)，此時(shí)Excel自動(dòng)為第4個(gè)參數(shù)指定默認(rèn)值，通常為0。

4.1.7　在公式中引用其他工作表或工作簿中的數(shù)據(jù)

公式中引用的數(shù)據(jù)可以來自于公式所在的工作表，也可以來自于公式所在的工作簿中的其他工作表，甚至是其他工作簿，對(duì)于后兩種情況，需要使用特定的格式在公式中輸入所引用的數(shù)據(jù)。此外，在公式中還可以引用多個(gè)工作表中的相同區(qū)域。

1．在公式中引用其他工作表的數(shù)據(jù)

如果要在公式中引用同一個(gè)工作簿的其他工作表的數(shù)據(jù)，則需要在單元格地址的左側(cè)添加工作表名稱和一個(gè)半角感嘆號(hào)，格式如下：

     =工作表名稱!單元格地址

例如，在Sheet2工作表的A1單元格中包含數(shù)值100，如圖4-12所示。如果要在該工作簿的Sheet1工作表的A1單元格中輸入一個(gè)公式，計(jì)算Sheet2工作表的A1單元格中的值與6的乘積，則需要在Sheet1工作表的A1單元格中輸入以下公式，如圖4-13所示。

圖4-12　Sheet2工作表中的數(shù)據(jù)

圖4-13　Sheet1工作表中的公式

     =Sheet2!A1*6

注意：如果工作表的名稱以數(shù)字開頭，或其中包含空格、特殊字符（例如$、%、#等），則必須使用一對(duì)單引號(hào)將工作表名稱包圍起來，例如“='Sheet 2'!A1*6”。以后如果修改工作表的名稱，公式中工作表名稱會(huì)同步更新。

2．在公式中引用其他工作簿的數(shù)據(jù)

如果要在公式中引用其他工作簿中的數(shù)據(jù)，則需要在單元格地址的左側(cè)添加使用方括號(hào)括起的工作簿名稱、工作表名稱和一個(gè)半角感嘆號(hào)，格式如下：

     =[工作簿名稱]工作表名稱!單元格地址

如果工作簿名稱或工作表名稱以數(shù)字開頭，或其中包含空格、特殊字符，則需要使用一對(duì)單引號(hào)同時(shí)將工作簿名稱和工作表名稱包圍起來，格式如下：

     ='[工作簿名稱]工作表名稱'!單元格地址

如果公式中引用的數(shù)據(jù)所在的工作簿已經(jīng)被打開，則只需按照上面的格式輸入工作簿的名稱，否則必須在公式中輸入工作簿的完整路徑。為了簡(jiǎn)化輸入，通常在打開工作簿的情況下創(chuàng)建這類公式，關(guān)閉工作簿后，其路徑會(huì)被自動(dòng)添加到公式中。

下面的公式引用“銷售數(shù)據(jù)”工作簿Sheet2工作表中的A1單元格的數(shù)據(jù)，并計(jì)算它與5的乘積，如圖4-14所示。

圖4-14　在公式中引用其他工作簿中的數(shù)據(jù)

     =[銷售數(shù)據(jù).xlsx]Sheet2!A1*5

3．在公式中引用多個(gè)工作表的相同區(qū)域

如果要在公式中引用多個(gè)相鄰工作表的相同區(qū)域的數(shù)據(jù)，則可以使用工作表的三維引用，以簡(jiǎn)化對(duì)每一個(gè)工作表的單獨(dú)引用，格式如下：

     起始位置的工作表名稱:結(jié)束位置的工作表名稱!單元格地址

下面的公式是計(jì)算Sheet1、Sheet2和Sheet3三個(gè)工作表A1:A6單元格區(qū)域中的數(shù)值總和：

     =SUM(Sheet1:Sheet3!A1:A6)

如果不使用三維引用，則需要在公式中重復(fù)引用每一個(gè)工作表中的單元格區(qū)域：

     =SUM(Sheet1!A1:A6,Sheet2!A1:A6,Sheet3!A1:A6)

下面列出的函數(shù)支持工作表的三維引用：

SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV.P、STDEV.S、STDEVA、STDEVPA、VAR.P、VAR.S、VARA和VARPA。

如果改變公式中引用的多個(gè)工作表的起始工作表或結(jié)束工作表，或在引用的多個(gè)工作表的范圍內(nèi)添加或刪除工作表，Excel將自動(dòng)調(diào)整公式中引用的多個(gè)工作表的范圍及其中包含的工作表。

技巧：如果要引用除了當(dāng)前工作表之外的其他所有工作表，則可以在公式中使用通配符“*”，形式如下：

     =SUM('*'!A1:A6)

4.1.8　創(chuàng)建數(shù)組公式

Excel中的數(shù)組是指排列在一行、一列或多行多列中的一組數(shù)據(jù)的集合。數(shù)組中的每一個(gè)數(shù)據(jù)稱為數(shù)組元素，數(shù)組元素的數(shù)據(jù)類型可以是Excel支持的任意數(shù)據(jù)類型。數(shù)組的維數(shù)是指數(shù)組具有的維度個(gè)數(shù)，維度是指數(shù)組的行、列方向。按數(shù)組的維數(shù)，可以將Excel中的數(shù)組分為以下兩類：

·　一維數(shù)組：數(shù)組中的元素排列在一行或一列中。數(shù)組元素排列在一行的數(shù)組是水平數(shù)組（或橫向數(shù)組），數(shù)組元素排列在一列的數(shù)組是垂直數(shù)組（或縱向數(shù)組）。

·　二維數(shù)組：數(shù)組中的元素排列在多行多列中。

數(shù)組的尺寸是指數(shù)組各行各列的元素個(gè)數(shù)。一行N列的一維水平數(shù)組尺寸為1×N，一列N行的一維垂直數(shù)組尺寸為N×1，M行N列的二維數(shù)組尺寸為M×N。

按數(shù)組的存在形式，可以將Excel中的數(shù)組分為以下3類：

·　常量數(shù)組：常量數(shù)組是直接在公式中輸入數(shù)組元素，并使用一對(duì)花括號(hào)將這些元素包圍起來。如果數(shù)組元素是文本型數(shù)據(jù)，則需要使用半角雙引號(hào)包圍每一個(gè)數(shù)組元素。

·　區(qū)域數(shù)組：區(qū)域數(shù)組是公式中引用的單元格區(qū)域，例如“=SUM（A1:B6）”公式中的A1:B6就是區(qū)域數(shù)組。

·　內(nèi)存數(shù)組：內(nèi)存數(shù)組是在公式的計(jì)算過程中，由中間步驟返回的多個(gè)結(jié)果臨時(shí)構(gòu)成的數(shù)組，它存在于內(nèi)存中，通常作為一個(gè)整體繼續(xù)參與下一步計(jì)算。

無論哪種類型的數(shù)組，數(shù)組中的元素都遵循以下格式：水平數(shù)組中的各個(gè)元素之間使用半角逗號(hào)分隔，垂直數(shù)組中的各個(gè)元素之間使用半角分號(hào)分隔。

如圖4-15所示，A1:F1單元格區(qū)域中包含一個(gè)一維水平的常量數(shù)組：

     ={1,2,3,4,5,6}

如圖4-16所示，A1:A6單元格區(qū)域中包含一個(gè)一維垂直的常量數(shù)組：

     ={"A";"B";"C";"D";"E";"F"}

圖4-15　一維水平數(shù)組

圖4-16　一維垂直數(shù)組

在輸入上面兩個(gè)常量數(shù)組時(shí)，需要先選擇與數(shù)組方向及元素個(gè)數(shù)完全一致的單元格區(qū)域，然后輸入數(shù)組公式并按Ctrl+Shift+Enter快捷鍵，Excel會(huì)自動(dòng)添加一對(duì)花括號(hào)將整個(gè)公式包圍起來。

根據(jù)數(shù)組公式占據(jù)的單元格數(shù)量，可以將數(shù)組公式分為單個(gè)單元格數(shù)組公式和多個(gè)單元格數(shù)組公式（或稱為多單元格數(shù)組公式）。如果要修改多單元格數(shù)組公式，則需要選擇數(shù)組公式占據(jù)的整個(gè)單元格區(qū)域，然后按F2鍵，在“編輯”模式下修改數(shù)組公式，修改完成后按Ctrl+Shift+Enter快捷鍵。刪除多單元格數(shù)組公式的方法與此類似，需要選擇數(shù)組公式占據(jù)的整個(gè)單元格區(qū)域，然后按Delete鍵。無法單獨(dú)修改或刪除多單元格數(shù)組公式中的部分單元格。

如圖4-17所示，使用下面的數(shù)組公式計(jì)算所有商品的銷售額。按照常規(guī)方法需要兩步，首先分別計(jì)算每種商品的銷售額，然后將各個(gè)商品的銷售額匯總求和。使用數(shù)組公式可以一步完成，簡(jiǎn)化了計(jì)算的中間步驟。

     {=SUM(B2:B11*C2:C11)}

圖4-17　使用數(shù)組公式可以簡(jiǎn)化計(jì)算步驟

4.1.9　處理公式錯(cuò)誤

當(dāng)單元格中的公式出現(xiàn)無法被Excel識(shí)別時(shí)，將在單元格中顯示錯(cuò)誤值，它們以“#”符號(hào)開頭，每個(gè)錯(cuò)誤值表示特定類型的錯(cuò)誤。表4-3列出了Excel中的7種錯(cuò)誤值及其說明。

表4-3　Excel中的7種錯(cuò)誤值及其說明

除了表4-3列出的7種錯(cuò)誤值外，另一種經(jīng)常出現(xiàn)的錯(cuò)誤是單元格自動(dòng)被“#”符號(hào)填滿，出現(xiàn)該錯(cuò)誤有以下兩個(gè)原因：

·　單元格的列寬太小，導(dǎo)致無法完全顯示其中的內(nèi)容。

·　使用1900日期系統(tǒng)時(shí)在單元格中輸入了負(fù)的日期或時(shí)間。

當(dāng)Excel檢測(cè)到單元格中的錯(cuò)誤時(shí)，將在該單元格的左上角顯示一個(gè)綠色的三角，單擊這個(gè)單元格將顯示按鈕，單擊該按鈕將彈出如圖4-18所示的菜單，其中包含用于檢查和處理錯(cuò)誤的相關(guān)命令。

菜單頂部的文字說明了錯(cuò)誤的類型，例如此處的“數(shù)字錯(cuò)誤”，菜單中的其他命令的功能如下：

·　關(guān)于此錯(cuò)誤的幫助：打開“幫助”窗口并顯示相關(guān)錯(cuò)誤的幫助主題。

·　顯示計(jì)算步驟：通過分步計(jì)算檢查發(fā)生錯(cuò)誤的位置。

·　忽略錯(cuò)誤：保留單元格中的當(dāng)前值并忽略錯(cuò)誤。

·　在編輯欄中編輯：進(jìn)入單元格的“編輯”模式，在編輯欄中可以修改單元格中的內(nèi)容。

·　錯(cuò)誤檢查選項(xiàng)：打開“Excel選項(xiàng)”對(duì)話框中的“公式”選項(xiàng)卡，在該選項(xiàng)卡中設(shè)置錯(cuò)誤的檢查規(guī)則，只有選中“允許后臺(tái)錯(cuò)誤檢查”復(fù)選框，才會(huì)啟用Excel錯(cuò)誤檢查功能，如圖4-19所示。

圖4-18　包含用于檢查和處理錯(cuò)誤的相關(guān)命令

圖4-19　設(shè)置錯(cuò)誤檢查選項(xiàng)

如果公式比較復(fù)雜，則在查找出錯(cuò)原因時(shí)可能比較費(fèi)時(shí)。使用Excel中的分步計(jì)算功能，可以將復(fù)雜的計(jì)算過程分解為單步計(jì)算，提高錯(cuò)誤排查的效率。選擇公式所在的單元格，然后在功能區(qū)的“公式”選項(xiàng)卡中單擊“公式求值”按鈕，打開“公式求值”對(duì)話框，如圖4-20所示。單擊“求值”按鈕將得出下畫線部分的計(jì)算結(jié)果，如圖4-21所示。繼續(xù)單擊“求值”按鈕，依次計(jì)算公式中的其他部分，直到得出整個(gè)公式的最終結(jié)果。完成整個(gè)公式的計(jì)算后，可以單擊“重新啟動(dòng)”按鈕重新對(duì)公式執(zhí)行分步計(jì)算。

圖4-20　“公式求值”對(duì)話框

圖4-21　計(jì)算公式中的每個(gè)部分

在“公式求值”對(duì)話框中還有“步入”和“步出”兩個(gè)按鈕。當(dāng)公式中包含多個(gè)計(jì)算項(xiàng)且其中含有單元格引用時(shí)，“步入”按鈕將變?yōu)榭捎脿顟B(tài)，單擊該按鈕會(huì)顯示分步計(jì)算中當(dāng)前下畫線部分的值。如果下畫線部分包含公式，則會(huì)顯示具體的公式。單擊“步出”按鈕將從步入的下畫線部分返回到整個(gè)公式中。