Excel2007表格制作培訓教程 第6課 用公式和函數(shù)計算數(shù)據(jù)

1、Excel 2007 表格制作培訓教程第第6課課 用公式和函數(shù)計算數(shù)據(jù)用公式和函數(shù)計算數(shù)據(jù)u本課要點本課要點u具體要求具體要求u本課導讀本課導讀u上機練習上機練習Excel 2007 表格制作培訓教程本課要點本課要點w 使用公式計算使用公式計算w 單元格和區(qū)域的引用單元格和區(qū)域的引用w 使用函數(shù)計算使用函數(shù)計算 Excel 2007 表格制作培訓教程具體要求具體要求w 熟悉輸入公式、復制公式、刪除公式的方法熟悉輸入公式、復制公式、刪除公式的方法w 了解公式的常見錯誤了解公式的常見錯誤w 掌握相對引用、絕對引用和混合引用掌握相對引用、絕對引用和混合引用w 掌握引用工作簿中元格的方法掌握引用工作簿

2、中元格的方法w 熟悉函數(shù)的格式、類型熟悉函數(shù)的格式、類型w 掌握插入函數(shù)及嵌套函數(shù)的方法掌握插入函數(shù)及嵌套函數(shù)的方法w 掌握常用函數(shù)的使用掌握常用函數(shù)的使用Excel 2007 表格制作培訓教程本課導讀本課導讀Excel 2007提供了強大的計算功能，既包括基本的加減乘除運算，也包括復雜的函數(shù)計算。Excel 2007的計算功能是通過公式和函數(shù)來實現(xiàn)的。為了更好地使用它們，必須掌握單元格的相對引用和絕對引用。Excel 2007 表格制作培訓教程6.1 使用公式計算使用公式計算 u6.1.1 知識講解知識講解u6.1.2 典型案例典型案例計算計算“年度費用支出年度費用支出表表”中的數(shù)據(jù)中的數(shù)據(jù)

3、 Excel 2007 表格制作培訓教程6.1.1 知識講解知識講解 公式是對工作表中的數(shù)據(jù)進行計算的等式，它遵循一個特定的語法或次序。公式以等號“=”開頭，其中包括以下部分內(nèi)容或全部內(nèi)容：w引用：引用單元格中的數(shù)據(jù)。w運算符：一個標記或符號，指定表達式的計算類型，有數(shù)學、比較、邏輯和引用運算符等。w常量：直接輸入公式中的數(shù)字或文本值。w函數(shù)：預先編寫的公式，可以對一個或多個值進行計算，并返回一個或多個值。Excel 2007 表格制作培訓教程6.1.1 知識講解知識講解 u1.輸入公式輸入公式u2.復制公式復制公式u3.刪除公式刪除公式u4.公式常見錯誤解析公式常見錯誤解析 Excel 20

4、07 表格制作培訓教程1.輸入公式輸入公式的操作與輸入文本和數(shù)據(jù)類似，同的是在輸入一個公式時首先要輸入“=”，以此作為開頭，然后才是公式的表達式。Excel 2007 表格制作培訓教程2.復制公式復制公式的方法有如下兩種：w選擇【開始】【剪貼板】組，通過單擊【復制】、【粘貼】按鈕，或者通過【Ctrl+V】和【Ctrl+C】組合鍵進行復制。w 通過拖動鼠標光標的方法。Excel 2007 表格制作培訓教程3.刪除公式公式的刪除方法與數(shù)據(jù)的刪除方法不太一樣。如果直接按【Delete】鍵刪除公式，則單元格中的數(shù)據(jù)也將被刪除。要刪除單元格中不需要的公式且保留其中的數(shù)據(jù)，打開【選擇性粘貼】對話框，在【粘

5、貼】欄中選中單選項，如右圖所示。Excel 2007 表格制作培訓教程4.公式常見錯誤解析 在單元格中輸入錯誤的公式可能產(chǎn)生以下錯誤值：w“#”，列寬不足。 w“#VALUE!”，參數(shù)或操作數(shù)類型錯誤。 w“#DIV/0!”，當公式被0除時，會產(chǎn)生該錯誤值。 w“#NAME?”，使用Excel 2007不能識別的文本。 w“#N/A”，公式中沒有可用數(shù)值。 w“#REF!”，單元格引用無效。w“#NUM!”，公式或函數(shù)中使用了無效數(shù)字。 w“#NULL!”，指定并不相交的兩個區(qū)域的交點。 Excel 2007 表格制作培訓教程6.1.2 典型案例典型案例計算計算“年度費用支出表年度費用支出表”

6、中的數(shù)據(jù)中的數(shù)據(jù) u 案例目標案例目標本案例將使用公式對“年度費用支出表”中的數(shù)據(jù)進行計算，在計算過程中使用到輸入和復制公式的方法，計算后的效果如右圖所示。Excel 2007 表格制作培訓教程u 制作思路制作思路（1）在G3單元格中輸入公式計算所有部門的費用支出總額。（2）將G3單元格中的公式復制到其他需要計算數(shù)據(jù)的單元格中。6.1.2 典型案例典型案例計算計算“年度費用支出表年度費用支出表”中的數(shù)據(jù)中的數(shù)據(jù)Excel 2007 表格制作培訓教程u 案例小結(jié)案例小結(jié)本案例快速計算了工作表中的數(shù)據(jù)，練習了在工作表中使用公式的方法。 6.1.2 典型案例典型案例計算計算“年度費用支出表年度費用支

7、出表”中的數(shù)據(jù)中的數(shù)據(jù)Excel 2007 表格制作培訓教程6.2 單元格和區(qū)域的引用單元格和區(qū)域的引用 在Excel 2007中使用公式和函數(shù)時需要對單元格地址進行引用，一個引用地址代表工作表中的一個或者多個單元格或單元格區(qū)域。 Excel 2007 表格制作培訓教程6.2 單元格和區(qū)域的引用單元格和區(qū)域的引用u6.2.1 知識講解知識講解u6.2.2 典型案例典型案例在在“成本計算表成本計算表”中中引用單元格引用單元格 Excel 2007 表格制作培訓教程6.2.1 知識講解知識講解 單元格和單元格區(qū)域引用的作用在于標識工作表中的單元格或單元格區(qū)域，并告知Excel在何處查找要使用的數(shù)據(jù)

8、。通過單元格引用，可以在一個公式中使用工作表中不同單元格的數(shù)據(jù)，或者在多個公式中使用同一個單元格的數(shù)值；還可以引用同一個工作簿中其他工作表中的單元格的數(shù)據(jù)及其他工作簿中的數(shù)據(jù)。Excel 2007 表格制作培訓教程6.2.1 知識講解知識講解 u1.相對引用相對引用 相對引用是基于包含公式和單元格引用的單元格的相對位置。如果公式所在單元格的位置改變，引用也隨之改變。 u2.絕對引用絕對引用將公式復制到新位置時，公式中的單元格地址始終保持固定不變，結(jié)果與包含公式的單元格位置無關(guān)，這就是絕對引用。 Excel 2007 表格制作培訓教程6.2.1 知識講解知識講解 u3.混合引用混合引用 混合引用

9、具有絕對列和相對行或絕對行和相對列。絕對引用列采用$A1、$B1等形式，絕對引用行采用A$1、B$1等形式。 u4.在同一工作薄中引用其他工作表中的單元格在同一工作薄中引用其他工作表中的單元格 如果工作表中的單元格需要與同一工作簿中其他工作表中的單元格進行計算，可以使用在單元格引用的前面加上工作表的名稱和感嘆號“!”的方法。 Excel 2007 表格制作培訓教程6.2.1 知識講解知識講解 u5.引用其他工作簿中的單元格引用其他工作簿中的單元格 如果引用其他工作簿中工作表的單元格，一般格式為“工作簿存儲地址工作簿名稱工作表名稱！單元格地址”。 Excel 2007 表格制作培訓教程6.2.2

10、 典型案例典型案例在在“成本計算表成本計算表”中引中引用單元格用單元格 u 案例目標案例目標本案例將在“成本計算表”中運用公式進行計算，通過練習掌握相對引用、絕對引用和混合引用單元格的方法。Excel 2007 表格制作培訓教程6.2.2 典型案例典型案例在在“成本計算表成本計算表”中引中引用單元格用單元格u 制作思路制作思路（1）運用相對引用計算總成本和單位成本。（2）運用絕對引用計算清倉消耗。（3）運用混合引用計算出庫費用。Excel 2007 表格制作培訓教程6.2.2 典型案例典型案例在在“成本計算表成本計算表”中引中引用單元格用單元格u 案例小結(jié)案例小結(jié)本案例主要練習了單元格的相對引

11、用、絕對引用和混合引用，這是用公式或函數(shù)計算數(shù)據(jù)時常用知識；而引用同一工作簿或其他工作簿中的數(shù)據(jù)一般多在制作大型表格時使用。 Excel 2007 表格制作培訓教程6.3 使用公式計算使用公式計算 u6.3.1 知識講解知識講解u6.3.2 典型案例典型案例計算計算“聘用人員考核聘用人員考核評分表評分表”的數(shù)據(jù)的數(shù)據(jù) Excel 2007 表格制作培訓教程6.3.1 知識講解知識講解 函數(shù)是一種預定義的公式，通過使用一些稱為參數(shù)的數(shù)值以特定的順序或結(jié)構(gòu)進行計算。函數(shù)可用于進行簡單或復雜的計算，下面介紹它的具體使用方法。 Excel 2007 表格制作培訓教程6.3.1 知識講解知識講解 u1.

12、函數(shù)的格式函數(shù)的格式u2.函數(shù)的類型函數(shù)的類型u3.插入函數(shù)插入函數(shù)u4.嵌套函數(shù)嵌套函數(shù)u5.常用函數(shù)的使用常用函數(shù)的使用 Excel 2007 表格制作培訓教程1.函數(shù)的格式函數(shù)一般由等號、函數(shù)名稱和參數(shù)3部分構(gòu)成。以函數(shù)名稱開始，然后是括號，括號里是以逗號隔開的計算參數(shù)。 Excel 2007 表格制作培訓教程2.函數(shù)的類型函數(shù)的主要類型有如下幾種：w加載宏和自動化函數(shù)，使用加載項程序加載。w數(shù)據(jù)庫函數(shù)，用于對存儲在列表或數(shù)據(jù)庫中的數(shù)據(jù)進行分析。w日期和時間函數(shù)，用于計算工作表中日期和時間的函數(shù)。w工程函數(shù)，用于一些復數(shù)和進制轉(zhuǎn)換的函數(shù)。w財務(wù)函數(shù)，用于在財務(wù)方面進行計算的函數(shù)。Exce

13、l 2007 表格制作培訓教程2.函數(shù)的類型w信息函數(shù)，針對工作表中數(shù)據(jù)信息的函數(shù)。w邏輯函數(shù)，包括一些邏輯運算符的函數(shù)。w查找和引用函數(shù)，對工作表中數(shù)據(jù)進行查找或引用的函數(shù)。w數(shù)學和三角函數(shù)，在數(shù)學和三角函數(shù)中進行計算的函數(shù)。w統(tǒng)計函數(shù)，對工作表中數(shù)據(jù)進行統(tǒng)計的函數(shù)。w文本函數(shù)，針對工作表中文本字符的函數(shù)。Excel 2007 表格制作培訓教程3.插入函數(shù)插入相應(yīng)的函數(shù)，方法有如下兩種： w選擇【公式】【函數(shù)庫】組，打開【插入函數(shù)】對話框，在其中選擇函數(shù)，然后打開【函數(shù)參數(shù)】對話框，選擇參數(shù)單元格。w選擇【公式】【函數(shù)庫】組，在其中單擊對應(yīng)函數(shù)的下拉按鈕，在彈出的下拉菜單中選擇需要插入的函數(shù)

14、即可。Excel 2007 表格制作培訓教程4.嵌套函數(shù)有些函數(shù)可以用做其他函數(shù)的參數(shù)，這稱為嵌套函數(shù)。將函數(shù)嵌套使用能夠簡化函數(shù)參數(shù)，使數(shù)據(jù)的處理更清晰明了，也能實現(xiàn)更多的功能。嵌套函數(shù)時，將首先計算最深層的嵌套表達式，然后逐步向外擴展。 Excel 2007 表格制作培訓教程5.常用函數(shù)的使用選擇了【常用函數(shù)】類別之后，列表框中就將列出一般常用函數(shù)。wSUM函數(shù) ，對選擇的單元格或單元格區(qū)域進行加法運算。wAVERAGE函數(shù)，對選擇的單元格或單元格區(qū)域進行算術(shù)平均值運算。wIF函數(shù)，執(zhí)行真（true）假（false）值判斷，根據(jù)邏輯計算的真假值，返回不同的結(jié)果。 wMAX函數(shù)，對選擇的單元

15、格區(qū)域中的數(shù)據(jù)進行比較，找到其中最大的數(shù)字并返回到目標單元格中。 Excel 2007 表格制作培訓教程6.3.2 典型案例典型案例計算計算“聘用人員考核評聘用人員考核評分表分表”的數(shù)據(jù)的數(shù)據(jù) u 案例目標案例目標本案例將使用公式對“年度費用支出表”中的數(shù)據(jù)進行計算，在計算過程中使用輸入和復制公式的方法，計算后的效果如右圖所示。Excel 2007 表格制作培訓教程u 制作思路制作思路（1）運用AVERAGE函數(shù)計算平均分。（2）運用SUM函數(shù)計算總分。（3）運用IF函數(shù)判斷是否聘用員工。（4）運用MAX函數(shù)求出各項最高分。6.3.2 典型案例典型案例計算計算“聘用人員考核評聘用人員考核評分表

16、分表”的數(shù)據(jù)的數(shù)據(jù)Excel 2007 表格制作培訓教程u 案例小結(jié)案例小結(jié)本案例主要練習了插入函數(shù)的多種方法，以及4個常用函數(shù)的使用方法。 6.3.2 典型案例典型案例計算計算“聘用人員考核評聘用人員考核評分表分表”的數(shù)據(jù)的數(shù)據(jù)Excel 2007 表格制作培訓教程6.4 上機練習上機練習 u6.4.1 用公式計算“獎金計算器”的數(shù)據(jù) u6.4.2 用函數(shù)計算“往來賬款處理”中的數(shù)據(jù) Excel 2007 表格制作培訓教程6.4.1 用公式計算用公式計算“獎金計算器獎金計算器”的數(shù)的數(shù)據(jù)據(jù) 本次練習將計算“獎金計算器”工作簿中的數(shù)據(jù)，練習公式的使用及單元格的引用方法，制作效果如右圖所示。 E

17、xcel 2007 表格制作培訓教程6.4.1 用公式計算用公式計算“獎金計算器獎金計算器”的數(shù)的數(shù)據(jù)據(jù)制作思路：w在D6:D10單元格區(qū)域中，用“個人銷售總額*獎金比例”的公式計算每位員工的獎金。由于獎金比例是固定的，因此采用絕對引用。w在C11單元格中，將每位員工的銷售總額加起來，計算銷售一部的個人銷售總額。w在D11單元格中，將每位員工的獎金加起來，計算銷售一部的獎金總額。w按同樣的方法計算銷售二部的獎金。將兩個部門的數(shù)據(jù)相加，在B3和C3單元格中分別計算總銷售額和已發(fā)獎金。Excel 2007 表格制作培訓教程6.4.2 用函數(shù)計算用函數(shù)計算“往來賬款處理往來賬款處理”中中的數(shù)據(jù)的數(shù)據(jù)本次練習將在“往來賬款處理”工作簿中用函數(shù)處理往來賬款，完成效果如右圖所示。 Excel 2007 表格制作培訓