5.4公式與函數

5.4.1自動計算

利用工具欄的自動求和按鈕

或在狀態欄上單擊鼠標右鍵，無須公式即可自動計算一組數據的累加和、平均值、統計個數、求最大值和最小值等。

【例題5-10】對“銷售單”工作表中的數據進行自動計算。計算1：計算A001型號產品三個月的銷售總和（置E3單元格）；計算2：計算A001、A002、A003、A004四個型號產品各自三個月的銷售總和（置E3:E6單元格區域）以及每個月四個型號產品銷售的合計（置B7:E7單元格）；計算3：計算一月份和三月份四種產品銷售的平均數量（置F7單元格）。具體操作步驟：

計算1：

（1）選定B3:E3單元格區域。

（2）選擇“開始/編輯”，單擊“套用表格格式”單擊

按鈕右側的向下箭頭，單擊“求和”，計算結果顯示在E3單元格，如圖5-27所示，此時，單擊E3單元格，編輯欄顯示：=SUM（B3:D3）。計算2：

（1）選定B3:E7單元格區域。

（2）選擇“開始/編輯”，單擊“套用表格格式”單擊

按鈕右側的向下箭頭，單擊“求和”，計算結果顯示在E3:E6單元格區域和B7:E7單元格，如圖5-28所示，此時，單擊E3:E6和B7:E7區域任一單元格，數據編輯區均有求和公式顯示。計算3：

（1）選定F7單元格。

（2）選擇“開始/編輯”，單擊

按鈕右側的向下箭頭，選擇“平均值”命令，此時，F7單元格有公式出現，選定B3:B6區域，按住Ctrl鍵，再選定D3:D6單元格區域，如圖5-29所示，單擊Enter鍵，結果顯示在F7單元格內，結果如圖5-30所示。

5.4.2輸入公式

Excel可以使用公式對工作表中的數據進行各種計算，如算術運算、關系運算和字符串運算等。1．公式的形式

公式的一般形式為：＝<表達式>表達式可以是算術表達式、關系表達式和字符串表達式等；表達式可由運算符、常量、單元格地址、函數及括號等組成，但不能含有空格；公式中<表達式>前面必須有“=”號。2．運算符

用運算符將常量、單元格地址、函數及括號等連接起來組成了表達式。常用運算符有算術運算符、字符運算符和關系運算符三類。運算符具有優先級，表5-1按運算符優先級從高到低列出各運算符及其功能。3．公式的輸入

選定要放置計算結果的單元格后，公式的輸入可以在數據編輯區中進行，也可以雙擊該單元格在單元格中進行。在數據編輯區輸入公式時，單元格地址可以通過鍵盤輸入，也可以直接單擊該單元格，單元格地址即自動顯示在數據編輯區。輸入后的公式可以進行編輯和修改，還可以將公式復制到其他單元格。公式計算通常需要引用單元格或單元格區域的內容，這種引用是通過使用單元格的地址來實現的。

【例題

5-11】利用公式計算“銷售單”工作表中各型號產品三個月銷售的平均數量，置F3:F6單元格區域（數值型，保留小數點位數為“0”）。具體操作步驟：

（1）選定F3單元格，在數據編輯區輸入公式：“=(B3+C3+D3)/3”，單擊工作表任意位置或按Enter鍵，結果顯示在F3單元格內。

（2）用鼠標拖動F3單元格的自動填充柄至F6單元格，放開鼠標，計算結果顯示在F3：F6單元格區域。

（3）選擇“開始/數字”，單擊“數字格式”下拉箭頭，選擇“其他數字格式”，在彈出的“設置單元格格式”對話框選擇“數值”，“小數位數”輸入“0”。單擊確定。結果如圖5-31所示。

5.4.3復制公式

1．公式復制的方法

方法1：選定含有公式的被復制公式單元格，單擊鼠標右鍵，在彈出的菜單中選擇“復制”命令，鼠標移至復制目標單元格，單擊鼠標右鍵，在彈出的菜單中選擇“粘貼”或“粘貼公式”（選中復制公式）命令，即可完成公式復制。

方法2：選定含有公式的被復制公式單元格，拖動單元格的自動填充柄，可完成相鄰單元格公式的復制。2．單元格地址的引用Excel中單元格的地址分相對地址、絕對地址、混合地址三種。根據計算的要求，在公式中會出現相對地址、絕對地址和混合地址以及它們的混合使用。（1）相對地址

相對地址的形式為：D3、A8等。表示在單元格中當含有相對地址的公式被復制到目標單元格時，公式不是照搬原來單元格的內容，而是根據公式原來位置和復制到的目標位置推算出公式中單元格地址相對原位置的變化，使用變化后的單元格地址的內容進行計算。

例如：在Sheet1工作表D1單元格有公式“=(A1+B1+C1)/3”，如圖5-32所示；當將公式復制到D2單元格時，公式變為：“=(A2+B2+C2)/3”，如圖5-33所示。而當將公式復制到E3單元格時，公式將變為：“=(B3+C3+D3)/3”，原因是當D1單元格公式“=(A1+B1+C1)/3”復制到D2單元格時，列號不變，行號加1，因此，D2單元格的公式為：“=(A2+B2+C2)/3”；而當D1單元格公式“=(A1+B1+C1)/3”復制到E3單元格時，列號加1，行號加2，因此，E3單元格的公式為：“=(B3+C3+D3)/3”。（2）絕對地址

絕對地址的形式為：$D$3、$A$8等。表示在單元格中當含有絕對地址的公式無論被復制到哪個單元格，公式永遠是照搬原來單元格的內容。例如：D1單元格中公式“=($A$1+$B$1+$C$1)/3”，復制到E3單元格公式仍然為“=($A$1+$B$1+$C$1)/3”，公式中單元格引用地址也不變。（3）混合地址

混合地址的形式為：D$3、$A8等，表示在單元格中當含有混合地址的公式被復制到目標單元格時，相對部分會根據公式原來位置和復制到的目標位置推算出公式中單元格地址相對原位置的變化，而絕對部分地址永遠不變，之后，使用變化后的單元格地址的內容進行計算。如：D1單元格中公式“=（$A1+B$1+C1）/3”，復制到E3單元格，公式為“=($A3+C$1+D3)/3”。（4）跨工作表的單元格地址引用

單元格地址的一般形式為：[工作簿文件名]工作表名!單元格地址

在引用當前工作簿的各工作表單元格地址時，當前“[工作簿文件名]”可以省略，引用當前工作表單元格的地址時“工作表名!”可以省略。例如，單元格F4中的公式為：“=(C4+D4+E4)*Sheet2!Bl”，其中“Sheet2!Bl”表示當前工作簿Sheet2工作表中的Bl單元格地址，而C4表示當前工作表C4單元格地址。

用戶可以引用當前工作簿另一工作表的單元格，也可以引用同一工作簿中多個工作表的單元格。例如“=SUM([Book1.xls]Sheet2:Sheet4!$A$5)”表示Book1工作簿的Sheet2到Sheet4共3個工作表的A5單元格內容求和。這種引用同一工作簿中多個工作表上的相同單元格或單元格區域中數據的方法稱為三維引用。

【例題5-12】利用公式計算圖5-31“銷售單”工作表中各型號產品三個月銷售數量的總和以及每個月各型號產品銷售數量的合計；計算每種產品銷售數量占總銷售數量的百分比并放在G3:G6單元格區域（總銷售數量為E7單元格的值）。具體操作步驟：

（1）選定E3單元格，在數據編輯區輸入公式：“=B3+C3+D3”，單擊工具欄的“輸入”按鈕或按Enter鍵，計算結果顯示在E3單元格。

（2）用鼠標拖動E3單元格的自動填充柄至E6單元格，放開鼠標，“總和”計算結果顯示在E3:E6單元格區域。

（3）選定B7單元格，在數據編輯區輸入公式：“=B3+B4+B5+B6”，單擊工具欄的“輸入”按鈕或按Enter鍵，計算結果顯示在B7單元格。

（4）用鼠標拖動B7單元格的自動填充柄至E7單元格，放開鼠標，計算結果顯示在B7：E7單元格區域，如圖5-34所示。

（5）選定G3單元格，在數據編輯區輸入公式：“=E3/$E$7”，單擊工具欄的“輸入”按鈕或按Enter鍵，“百分比”計算結果顯示在F3單元格。

（6）用鼠標拖動G3單元格的自動填充柄至G6單元格，放開鼠標，“百分比”計算結果顯示在G3:G6單元格區域，如圖5-35所示。5.4.4函數應用1．函數形式

函數一般由函數名和參數組成，形式為：

函數名（參數表）

其中：函數名由Excel提供，函數名中的大小寫字母等價，參數表由用英文逗號分隔的參數1，參數2，…，參數N（N≤30）構成，參數可以是常數、單元格地址、單元格區域、單元格區域名稱或函數等。2．函數引用

若要在某個單元格輸入公式：“=AVERAGE(A2:A10)”，可以采用如下方法：

方法1：直接在單元格中輸入公式：“=AVERAGE(A2:A10)”。

方法2：用“插入函數”快速輸入函數，其方法如下：

（1）選定單元格，單擊

按鈕右側的向下箭頭，選擇“其他函數”，彈出“插入函數”對話框，在“選擇函數”列表中選中函數“AVERAGE”，如圖5-36所示。單擊“確定”按鈕，打開“函數參數”對話框，如圖5-37所示。

（2）可在“函數參數”對話框第一個參數Value1框內用輸入選定A2:A10，單擊“確定”按鈕；也可以單擊“切換”按鈕

，然后在工作表上選定A2:A10區域，單擊“切換”按鈕

，單擊“確定”按鈕。3．函數嵌套

函數嵌套是指一個函數可以作為另一函數的參數使用。例如公式：ROUND(AVERAGE(A2:C2)，1)

其中，ROUND為一級函數，AVERAGE為二級函數。先執行AVERAGE函數，再執行ROUND函數。一定要注意，AVERAGE作為ROUND的參數，它返回的數值類型必須與ROUND參數使用的數值類型相同。Excel函數嵌套最多可嵌套七級。4.Excel函數1）常用函數

（1）SUM(參數1，參數2，…)：求和函數，求各參數的累加和。

（2）AVERAGE(參數1，參數2，…)：算術平均值函數，求各參數的算術平均值。

（3）MAX(參數1，參數2，…)：最大值函數，求各參數中的最大值。

（4）MIN(參數1，參數2，…)：最小值函數，求各參數中的最小值。2）統計個數函數

（1）COUNT(參數1，參數2，…)：求各參數中數值型數據的個數。

（2）COUNTA(參數1，參數2，…)：求“非空”單元格的個數。

（3）COUNTBLANK(參數1，參數2，…)：求“空”單元格的個數。3）四舍五入函數ROUND(數值型參數，n)

返回時對“數值型參數”進行四舍五入到第n位的近似值。

當n>0時，對數據的小數部分從左到右的第n位四舍五入。

當n=0時，對數據的小數部分最高位四舍五入取數據的整數部分。

當n<0時，對數據的整數部分從右到左的第n位四舍五入。4）條件函數IF(邏輯表達式，表達式1，表達式2)

若“邏輯表達式”值為真，函數值為“表達式1”的值；否則為“表達式2”的值。5）條件計數COUNTIF(條件數據區，"條件")統計“條件數據區”中滿足給定“條件”的單元格的個數。COUNTIF函數只能對給定的數據區域中滿足一個條件的單元格統計個數，若對一個以上的“條件”統計單元格的個數，用數據庫函數DCOUNT或DCOUNTA實現。6）條件求和函數SUMIF(條件數據區，"條件"，［求和數據區］)

在“條件數據區”查找滿足“條件”的單元格，計算滿足條件的單元格對應于“求和數據區”中數據的累加和。如果“求和數據區”省略，統計“條件數據區”滿足條件的單元格中數據的累加和。SUMIF函數中的前兩個參數與COUNTIF中的兩個參數的含義相同，如果省略SUMIF中的第3個參數，SUMIF是求滿足條件的單元格內數據的累加和，COUNTIF是求滿足條件的單元格的個數。Excel的其他函數以及詳細應用請查看Excel幫助信息。

【例題5-13】對“人力資源情況表”工作表，利用函數計算開發部職工人數置于D4單元格（利用COUNTIF函數），計算開發部職工平均工資置于D6單元格（利用SUMIF函數和已求出的計算開發部職工人數）。具體操作步驟：

（1）選定D4單元格，在數據編輯區輸入“=”，單擊“名稱框”右側的下拉按鈕，選擇“COUNTIF”函數，在彈出的“函數參數”對話框中輸入“Range”參數和“Criteria”參數（可利用“切換”按鈕

），此時，數據編輯區出現公式：“=COUNTIF(B3:B8,"開發部")”,單擊“確定”按鈕，按Enter鍵或工具欄的“確認”按鈕，此時，D4單元格顯示開發部職工人數，如圖5-38所示。

（2）選定D6單元格，在數據編輯區輸入“=”，單擊“名稱框”右側的下拉按鈕，選擇“SUMIF”函數，在彈出的“函數參數”對話框中輸入“Range”參數、“Criteria”參數和Sum_Range參數（可利用“切換”按鈕

），單擊“確定”按鈕，此時，數據編輯區出現公式：“=SUMIF(B3:B8,"開發部"，C3:C8)”，將數據編輯欄的公式編輯為：“=SUMIF(B3:B8,"開發部"，C3:C8)／$D$3”，按Enter鍵或工具欄的“確認”按鈕，此時，D6單元格顯示開發部職工平均工資，如圖5-39所示。5．關于錯誤信息

在單元格輸入或編輯公式后，有時會出現諸如“####！”或“#VALUE！”的錯誤信息，錯誤值一般以“#”符號開頭，出現錯誤值有如表5-2所示幾種原因。下面簡要說明各錯誤信息可能產生的原因。1）####！

若單元格中出現“####！”錯誤信息，可能的原因是：單元格中的計算結果太長，該單元格寬度小，可以通過調整單元格的寬度來消除該錯誤；或者，日期或時間格式的單元格中出現負值。2）#DIV/0！

若單元格中出現“#DIV/0！”錯誤信息，可能的原因是：該單元格的公式中出現被零除問題，即輸入的公式中包含“0”除數，也可能在公式中的除數引用了零值單元格或空白單元格（空白單元的值被解釋為零值）。

解決辦法是修改公式中的零除數或零值單元格或空白單元格引用，或者在用除數的單元中輸入不為零的值。

當做除數的單元格為空或含的值為零時，如果不希望顯示錯誤，可以使用IF函數。例如,如果單元格B5包含除數，而A5包含被除數，可以使用“=lF（B5=0，""，A5/B5）”（兩個連續引號代表空字符串），表示B5值為“0”時，什么也不顯示，否則顯示A5/B5的商。

3）#N/A

在函數或公式中沒有可用數值時，會產生這種錯誤信息。4）#NAME