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

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

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

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

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

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

可以給一個單元格或單元格區(qū)域取一個名字，這個名字可以出現(xiàn)在公式中，用來指代引用的單元格或單元格區(qū)域，這會使公式的意義更加明確。名字其實是一個標(biāo)識符，由字母或下劃線開頭的一個或多個字符組成。在Excel中，還可以用漢字字符作為名字。名字不區(qū)分字母的大小寫，比如ABC、abc、Abc都是同一個名字。可以為同一單元格或單元格區(qū)域定義多個不同的名字。名字一旦定義就可以在同一工作薄的不同工作表之間共用。名字及其應(yīng)用2、名字的意義在Excel中，同一個工作簿中的名字是共享的，在一個工作表中定義的名字，可以被另一個工作表的公式引用，這使公式具有更強大的功能，它能夠利用單元格的名字在不同的工作表中查找到正確的單元格或單元格區(qū)域。名字給單元格的引用帶來了許多方便，因為它使人們不必記住單元格在工作表中的引用位置，用名字就能找到它。名字及其應(yīng)用名字應(yīng)用案例在下圖中，B、C列是某商場所有產(chǎn)品的當(dāng)日單價，E、F列是產(chǎn)品當(dāng)天的銷售記錄。銷售的產(chǎn)品多達(dá)幾百種，而每天則可能只賣掉其中的幾十種產(chǎn)品，現(xiàn)在要計算當(dāng)日產(chǎn)品的銷售金額。若不用名字，單價要從C列的幾百個數(shù)據(jù)中去查找！

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

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