實(shí)訓(xùn)- 公式與函數(shù)應(yīng)用

實(shí)訓(xùn)七公式與函數(shù)應(yīng)用一、什么是函數(shù)與公式１、公式Excel的公式由運(yùn)算符、數(shù)值、字符串、變量和函數(shù)組成。公式必須以等號(hào)“=”開頭，即在Excel的單元格中，凡是以等號(hào)開頭的輸入數(shù)據(jù)都被認(rèn)為是公式。在等號(hào)的后面可以跟數(shù)值、運(yùn)算符、變量或函數(shù)，在公式中還可以使用括號(hào)。例如：=10+4*6/2+(2+1)*50就是一個(gè)公式，可以在任何單元格中輸入此公式．Excel會(huì)把公式的計(jì)算結(jié)果顯示在相應(yīng)的單元格中．2、運(yùn)算符在公式中可以使用運(yùn)算符。Excel中的運(yùn)算符運(yùn)算符運(yùn)算功能優(yōu)先級(jí)()括號(hào)1-負(fù)號(hào)2%百分號(hào)3^乘方4*與/乘、除法5+與-加、減法6&文本連接7=、<、>、<=、>=、<>等于、小于、大于、小于等于、大于等于、不等于8說(shuō)明：括號(hào)的運(yùn)算級(jí)別最高，在Excel的公式中只能使用小括號(hào)，無(wú)中括號(hào)和大括號(hào)。小括號(hào)可以嵌套使用，當(dāng)有多重小括號(hào)時(shí)，最內(nèi)層的表達(dá)式優(yōu)先運(yùn)算。同等級(jí)別的運(yùn)算符從左到右依次進(jìn)行。“&”為字符連接運(yùn)算，其作用是把前后的兩個(gè)字符串連接為一串。例如，"ABC"&"DEF"的結(jié)果為“ABCDEF”，"財(cái)務(wù)"&"經(jīng)濟(jì)"的結(jié)果為“財(cái)務(wù)經(jīng)濟(jì)”。1、引用的概念在公式中用到了其他單元格在表格中的位置。引用的作用在于標(biāo)識(shí)工作表中的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的單元格位置。引用不同工作簿中的單元格稱為鏈接。2、引用的類型A1引用RC引用二、引用3、相對(duì)引用相對(duì)引用也稱為相對(duì)地址引用，是指在一個(gè)公式中直接用單元格的列標(biāo)與行號(hào)來(lái)取用某個(gè)單元格中的內(nèi)容。相對(duì)引用的例子某超市3月份的進(jìn)貨單如下圖所示，計(jì)算“金額”。在G3計(jì)算公式=C3*D3*F3然后向下復(fù)制該公式！4、絕對(duì)引用絕對(duì)引用總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對(duì)引用保持不變。

絕對(duì)引用的形式是在引用單元格的列號(hào)與行號(hào)前面加“$”符號(hào)。比如，$A$1就是對(duì)A1單元格的絕對(duì)引用。絕對(duì)引用的案例假設(shè)LG空調(diào)在重慶各商場(chǎng)的銷售價(jià)格相同，重慶各商場(chǎng)的銷售數(shù)據(jù)如下圖所示。計(jì)算各商場(chǎng)的銷售總額。在E8輸入公式：=$c$5*C8然后向下復(fù)制此公式！5、混合引用混合引用具有絕對(duì)列和相對(duì)行，或是絕對(duì)行和相對(duì)列。比如，$A1、$B1

案例引用6、內(nèi)部引用與外部引用(提示，可在＝后直接點(diǎn)擊引用的內(nèi)容)1）引用相同工作表中的單元格，例：

=G3+G5+G10*102）引用同一工作簿的不同工作表中的單元格，例 =Sheet1!G3+Sheet1!G5+Sheet1!E273）引用不同工作簿中的單元格，例：

=[Book1]Sheet1!$IL$4+[Book1]Sheet2!$E$74）同一公式中存在幾中不同的引用，例： =[Book1]Sheet1!$A$4+Sheet1!G7+F9P86頁(yè)利用公式混合引用在B2單元格輸入公式＝$a2*B$1后拖拉引用7、公式的使用中的錯(cuò)誤信息錯(cuò)誤值錯(cuò)誤原因########單元格所含的數(shù)字、日期或時(shí)間比單元格寬，或者單元格的日期、時(shí)間公式產(chǎn)生了一個(gè)負(fù)值，就會(huì)產(chǎn)生#####錯(cuò)誤。#VALUE!1.在需要數(shù)字或邏輯值時(shí)輸入了文本，Excel不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類型。2.輸入或編輯數(shù)組公式時(shí)，按了Enter鍵。3.把單元格引用、公式或函數(shù)作為數(shù)組常量輸入。#DIV/O!1.輸入的公式中包含明顯的除數(shù)為零（0）#NAME?1.在公式中輸入文本時(shí)沒(méi)有使用雙引號(hào)。但這些文本又不是名字。2.函數(shù)名的拼寫錯(cuò)誤。3.刪除了公式中使用的名稱。4.名字拼寫有錯(cuò)#N/A1.在調(diào)用函數(shù)時(shí)參數(shù)。2.在數(shù)組的參數(shù)的行數(shù)或列數(shù)與包含數(shù)組公式的區(qū)域的行數(shù)或列數(shù)不一致。3.在沒(méi)有排序的數(shù)據(jù)表中使用了VLOOKUP、HLOOKUP或MATCH工作表函數(shù)查找數(shù)值#REF!刪除了公式中所引用的單元或單元格區(qū)域#NUM!1.由公式產(chǎn)生的數(shù)字太大或太小，Excel不能表示。2.在需要數(shù)字參數(shù)的函數(shù)中使用了非數(shù)字參數(shù)#NULL!在公式的兩個(gè)區(qū)域中加入了空格從而求交叉區(qū)域，但實(shí)際上這兩個(gè)區(qū)域無(wú)重迭區(qū)域條件格式的意義對(duì)于不同的數(shù)據(jù)，可按不同的條件設(shè)置其顯示格式。比如在一個(gè)學(xué)生成績(jī)表中，把所有不及格的成績(jī)顯示為紅色，就能夠很容易找到有不及格科目的學(xué)生。下面是條件格式運(yùn)用的一個(gè)例子：當(dāng)單元格的數(shù)值大于200時(shí)，用黑色顯示；當(dāng)小于100時(shí)用紅色顯示；100~200之間的數(shù)據(jù)用藍(lán)色顯示。將該單元格的格式設(shè)置為如下的自定義數(shù)字格式：[黑色][>=200]#,###;[紅色][<=100]#,###;[藍(lán)色]#,###

三、條件格式2、條件設(shè)置案例某公司的工資表，為了查看電話費(fèi)的補(bǔ)貼情況，用條件格式將電話費(fèi)在2000元以上的單元格設(shè)置成了紅字和淺綠色的背景；將1000元~2000元之間的單元格設(shè)置成了黑字和淺藍(lán)色的背景；將1000元以下的單元格設(shè)置成了深藍(lán)色的字體和淺青色的背景。3、案例結(jié)果：E列數(shù)據(jù)1.對(duì)于身份證長(zhǎng)度通過(guò)條件格式來(lái)提醒（在條件格式，公式中設(shè)定＝OR(LEN(A2)=15,LEN(A2)=18）,設(shè)為其他顏色，這樣當(dāng)輸入的長(zhǎng)充不是15位或18位時(shí)，顯示的格式顏色就不同（注：07版的條件格式有所不同）2.均值以上的單元格數(shù)據(jù)紅色顯示（直接設(shè)定格式條件，根據(jù)條件顯示顏色）3.最大值紅色、最小值藍(lán)色（選定區(qū)域后，設(shè)定條件＝C35=max($C$35:$F$47)）4.間隔行條紋（選定后公式輸入＝mod(row(),2）=0,設(shè)定條紋格式07版可以對(duì)多規(guī)則管理四、數(shù)組公式的應(yīng)用

1、數(shù)組公式的含義數(shù)組公式能夠?qū)山M或兩組以上的數(shù)據(jù)（兩個(gè)或兩個(gè)以上的單元格區(qū)域）同時(shí)進(jìn)行計(jì)算。在數(shù)組公式中使用的數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個(gè)數(shù)據(jù)區(qū)域，也可以是數(shù)組常量。2、數(shù)組公式的建立方法（1）選中需要保存數(shù)組公式結(jié)果的單元格或單元格區(qū)域。（2）輸入公式的內(nèi)容。（3）按Ctrl+Shift+Enter鍵。數(shù)組公式及其應(yīng)用用數(shù)組公式計(jì)算兩個(gè)數(shù)據(jù)區(qū)域的乘積【例4-1】某超市3月份進(jìn)購(gòu)的產(chǎn)品圖4-5所示，已知各種產(chǎn)品的數(shù)量、單價(jià)和折扣，要計(jì)算各種產(chǎn)品的應(yīng)付金額。數(shù)組公式及其應(yīng)用數(shù)組擴(kuò)展在公式中用數(shù)組作為參數(shù)時(shí)，所有的數(shù)組必須是同維的。如果數(shù)組參數(shù)或數(shù)組區(qū)域的維數(shù)不匹配，Excel會(huì)自動(dòng)擴(kuò)展該參數(shù)。比如，某商場(chǎng)29寸電視機(jī)售價(jià)為3128元，有一個(gè)10天的銷售記錄，如下圖的B、C兩列所示?，F(xiàn)要計(jì)算每天的銷售額。數(shù)組公式及其應(yīng)用1、選中F8:F14；2、輸入公式：=C8:C14*C53、按Ctrl+Shift+Enter操作題用數(shù)組計(jì)算總股本可以用sumproduct函數(shù)來(lái)實(shí)現(xiàn)數(shù)組計(jì)算功能數(shù)組公式及其應(yīng)用二維數(shù)組涉及許多行(或列)數(shù)據(jù)處理的數(shù)組公式，就是所謂的二維數(shù)組。合理地運(yùn)用二維數(shù)組的運(yùn)算功能，會(huì)提高數(shù)據(jù)處理的能力，有時(shí)在不同工作表之間進(jìn)行數(shù)據(jù)匯總時(shí)會(huì)特別有效。數(shù)組公式及其應(yīng)用案例某食品商店將每個(gè)月的食品銷售資料統(tǒng)計(jì)在一個(gè)工作表中，1～6月的銷售數(shù)據(jù)分別記錄在Book1工作簿的Sheet1～Sheet6中，現(xiàn)在要統(tǒng)計(jì)每種商品前6個(gè)月的銷售總量和銷售金額。見下圖：（注意是同一區(qū)域選擇）四、名字應(yīng)用1、名字概述

可以給一個(gè)單元格或單元格區(qū)域取一個(gè)名字，這個(gè)名字可以出現(xiàn)在公式中，用來(lái)指代引用的單元格或單元格區(qū)域，這會(huì)使公式的意義更加明確。名字其實(shí)是一個(gè)標(biāo)識(shí)符，由字母或下劃線開頭的一個(gè)或多個(gè)字符組成。在Excel中，還可以用漢字字符作為名字。名字不區(qū)分字母的大小寫，比如ABC、abc、Abc都是同一個(gè)名字?？梢詾橥粏卧窕騿卧駞^(qū)域定義多個(gè)不同的名字。名字一旦定義就可以在同一工作薄的不同工作表之間共用。名字及其應(yīng)用2、名字的意義在Excel中，同一個(gè)工作簿中的名字是共享的，在一個(gè)工作表中定義的名字，可以被另一個(gè)工作表的公式引用，這使公式具有更強(qiáng)大的功能，它能夠利用單元格的名字在不同的工作表中查找到正確的單元格或單元格區(qū)域。名字給單元格的引用帶來(lái)了許多方便，因?yàn)樗谷藗儾槐赜涀卧裨诠ぷ鞅碇械囊梦恢?，用名字就能找到它。名字及其?yīng)用名字應(yīng)用案例在下圖中，B、C列是某商場(chǎng)所有產(chǎn)品的當(dāng)日單價(jià)，E、F列是產(chǎn)品當(dāng)天的銷售記錄。銷售的產(chǎn)品多達(dá)幾百種，而每天則可能只賣掉其中的幾十種產(chǎn)品，現(xiàn)在要計(jì)算當(dāng)日產(chǎn)品的銷售金額。若不用名字，單價(jià)要從C列的幾百個(gè)數(shù)據(jù)中去查找！

名字及其應(yīng)用3、名字的定義在Excel中，可以為一個(gè)獨(dú)立的單元格、連續(xù)的單元格區(qū)域或許多不連續(xù)的單元格構(gòu)成的單元格組合定義一個(gè)名字（或多個(gè)名字）定義名字有多種方法，如定義、粘貼、指定或標(biāo)志等名字及其應(yīng)用4、定義單元格或單元格區(qū)域的名字D3單元格的名字定義方法如下：名字及其應(yīng)用（1）選擇“插入”|“名稱”|“定義”菜單命令.（2）在彈出的“定義名字”對(duì)話框的“引用位置”輸入要定義名字的單元格引用位置。（3）在“在當(dāng)前工作簿中的名稱”中輸入名字名字及其應(yīng)用5、將工作表的首行（或首列）定義為名字“指定”的方式一次可定義多個(gè)名字：可以將表格的首行或首列指定為相應(yīng)的列或行的名字。方法（1）選中要指定名字的單元格區(qū)域。（2）選擇“插入”|“名稱”|“指定”菜單項(xiàng)。（3）在“指定名稱”對(duì)話框中，選中“首行”、“最左列”的復(fù)選框。這樣就把該表的首行、首列指定成了相應(yīng)的列、行的名字名字及其應(yīng)用指定首行、首列為名字的案例（注意選擇區(qū)域需要包括行與列的范圍，不可間隔）名字及其應(yīng)用6、名字的應(yīng)用1）通過(guò)名字在不同的工作表之間傳遞數(shù)據(jù)

通過(guò)名字查找蔬菜單價(jià)指定A2:B12最左邊為名字操作題將“薪資表中的調(diào)底薪記錄”區(qū)域定義名稱，并將其應(yīng)用于實(shí)際計(jì)算平均值名字及其應(yīng)用2）名字與Indirect函數(shù)結(jié)合應(yīng)用Indirect函數(shù)與名字相結(jié)合，可使數(shù)據(jù)查找更方便。Indirect函數(shù)的用法是：Indirect(x)其中的X可以是單元格引用或名字。若X為單元格引用，它必須用“”引起來(lái)，如：=INDIRECT(“A1”)，將返回A1單元格中的內(nèi)容（注意加””是對(duì)單元格里內(nèi)容進(jìn)行引用，不加引號(hào)是對(duì)其單元格內(nèi)名字）若X為單元格名字，它將返回該名字所對(duì)應(yīng)的單元格的內(nèi)容。名字及其應(yīng)用某蔬菜商在一個(gè)工作表中保存蔬菜的單價(jià)，在另一工作表中保存銷售記錄，現(xiàn)要查找銷售記錄表中各蔬菜的單價(jià)。指定A2:B11區(qū)域的“最左列”為名字在D2中輸入公式：=INDIRECT(B2)向下復(fù)制該公式！名字及其應(yīng)用3．應(yīng)用行列交叉點(diǎn)查找數(shù)據(jù)所謂行列交叉點(diǎn)指的是由[行的名字列的名字]確定的單元格。把一個(gè)工作表的首行、首列指定為名字之后，可以使用名字引用行、列交叉點(diǎn)對(duì)應(yīng)單元格中的數(shù)據(jù)。案例某電信公司的電話用戶檔案，其中有成千上萬(wàn)個(gè)電話用戶的電話號(hào)碼。如果知道某個(gè)用戶的名字，那么要在這樣的表中查找該用戶的電話號(hào)碼、電話類型，或申請(qǐng)日期等數(shù)據(jù)就顯得很困難，因?yàn)楸碇械馁Y料太多了。通過(guò)行、列交叉點(diǎn)的名字引用能很輕松地找到所需的數(shù)據(jù)。名字及其應(yīng)用1指定A2:E17的“首行，最左列”為名字。2、在任空白單元格中輸入“客戶名字庫(kù)第二行任一單元格標(biāo)題”，就能找到該客戶的對(duì)應(yīng)數(shù)據(jù)。（注意，其首行首列定義后是可用于交叉查找，但不能用于名字引用）Trimmean（Array,percent）功能：將數(shù)據(jù)區(qū)域頭尾除去一定百分比的數(shù)據(jù)點(diǎn)，然后再求平均值。Array為需要篩選并求平均值的數(shù)組或數(shù)據(jù)區(qū)域；Percent為計(jì)