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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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