課件與題目空白2excel應(yīng)用技術(shù)

第二章Excel應(yīng)用技術(shù)§2.1公式

Excel中的公式，類似于數(shù)學(xué)中的表達(dá)式，它是一個能夠進(jìn)行運算的計算式。注意：

公式必須以“=”開頭，否則系統(tǒng)將視其為文字，從而失去計算的功能。

當(dāng)公式中含有單元格地址時，由該單元格中的數(shù)據(jù)參與運算。一、Excel運算符

公式由運算符和參與運算的操作數(shù)組成。▲運算符用于完成各種運算的符號。Excel的運算符包括4種：

算術(shù)運算符

比較運算符

文本運算符

引用運算符▲操作數(shù)進(jìn)行計算的元素?？梢允浅?shù)、單元格地址、函數(shù)等?！阈g(shù)運算符用于完成基本的數(shù)學(xué)運算。▲比較運算符用于比較兩個值，其結(jié)果是一個邏輯值，即TRUE或FALSE?！谋具\算符（&）連接兩個字符串?！眠\算符將單元格區(qū)域合并起來進(jìn)行計算。運算符名稱運算符用途區(qū)域運算符:對區(qū)域內(nèi)的所有單元格進(jìn)行引用聯(lián)合運算符,將多個引用合并為一個引用

如果公式中含有多個運算符時，它們運算的先后順序如下表（教材P38表2–4）所列。順序運算符順序運算符1:6^2（空格）7*和/3,8+和-4-（負(fù)號）9&5%二、單元格地址

Excel中，對單元格的引用有兩種方法：

▲A1引用（單元格地址的引用）

▲RxCx引用（相對當(dāng)前單元格位置的引用）引用方式的切換：菜單命令【工具】/【選項】/【常規(guī)】引用含義R[-2]C對同一列、上面兩行的單元格的相對引用R[2]C[2]對下面兩行、右面兩列的單元格的相對引用R2C2對工作表的第二行、第二列的單元格的絕對引用R[-1]對活動單元格整個上面一行單元格區(qū)域的相對引用R對當(dāng)前行的絕對引用

R表示行，C表示列。

數(shù)字是對指定行或列的引用；無數(shù)字則是對當(dāng)前行或列的引用。

方括號內(nèi)的數(shù)字是相對引用，否則為絕對引用。

引用方向向下或向右數(shù)字為正，反之為負(fù)。

單元格地址由列序號和行序號組成。單元格地址有3種不同的形式：▲相對引用▲絕對引用▲混合引用1、相對引用公式中相對引用的單元格地址是基于單元格的相對位置的。

當(dāng)公式所在的單元格位置發(fā)生了改變（如插入或刪除了行或列），單元格的相對引用也將隨之發(fā)生改變；

當(dāng)公式復(fù)制后，相對引用將自動作出調(diào)整。相對引用是直接用列號和行號的組合來表示單元格地址，如：A1、B3。

默認(rèn)情況下，公式使用相對引用。在D1中輸入：=A1+B1，顯示30。那么當(dāng)將D1公式復(fù)制到D2，則D2中公式及顯示值是什么？D2中的公式是：=A2+B2，顯示的值是90ABCDE11020302405060370809049030在D1中輸入：=SUM(A1:B2)，顯示120。那么當(dāng)將D1公式復(fù)制到E2，則E2中公式及顯示值是什么？E2中的公式是：=SUM(B2:C3)顯示的值是280ABCDE1102030240506037080904280120在C1中輸入：=A1+B1，顯示30。那么當(dāng)在A列后插入一個新列，原來的C1成為D1，其中的公式將是什么？顯示值是多少？ABCDE110202405037080ABCDE11020?2405037080D1中的公式是：=A1+C1，顯示的值是303030在C1中輸入：=SUM(A1:B2)，顯示120。那么當(dāng)在A列后插入一個新列，原來的C1成為D1，其中的公式將是什么？顯示值是多少？ABCDE110202405037080ABCDE11020?2405037080D1中的公式是：=SUM(A1:C2)，顯示的值是120120120在C1中輸入：=SUM(A1:B2)，顯示120。那么當(dāng)在B列后插入一個新列，原來的C1成為D1，其中的公式將是什么？顯示值是多少？ABCDE110202405037080ABCDE11020?2405037080D1中的公式是：=SUM(A1:B2)，顯示值仍是120120120在C1中輸入：=SUM(A1:B1)，顯示30。如果將C1的公式復(fù)制到C2，則C2中的公式將是什么？顯示值是多少？C2中公式是：=SUM(A2:B2)，顯示值是90如果將C1的公式復(fù)制到B3，則B3中將顯示什么？B3中顯示：#REF!（單元格引用無效）ABCDE110202405033090#REF!2、絕對引用公式中絕對引用的單元格地址是指定的單元格位置。

當(dāng)公式所在的單元格位置發(fā)生了改變，單元格的絕對引用保持不變；

當(dāng)公式復(fù)制后，絕對引用不作出調(diào)整。絕對引用是在列號和行號前加“$”符號，如：$A$1、$B$3。3、混合引用所謂混合引用是在引用單元格地址時，行和列中的一個是相對引用，而另一個是絕對引用。如：$A1、B$3。

當(dāng)公式所在的單元格位置發(fā)生了改變，相對引用部分將隨之發(fā)生改變，而絕對引用部分將保持不變；

當(dāng)公式復(fù)制后，相對引用部分將自動作出調(diào)整，絕對引用部分不作任何調(diào)整。

如果選中公式中的單元格地址，再按F4功能鍵，即可將該地址在這3種引用中進(jìn)行切換。ABCDE110203024050603708090在D1中輸入：=$A$1+B2，那么當(dāng)將D1的公式復(fù)制到E2，則E2中顯示的是什么？E2中的公式是：=$A$1+C3，顯示的值是100在D1中輸入：=$A1+$B2，那么當(dāng)將D1的公式復(fù)制到E2，則E2中顯示的是什么？E2中的公式是：=$A2+$B3，顯示的值是120在D2中輸入：$A$1+$A$2，那么當(dāng)將D2中的內(nèi)容復(fù)制到E3，則E3中顯示的值是什么？E3中顯示是：$A$1+$A$2100$A$1+$A$26030120

當(dāng)需要引用其它工作表或其它工作簿中的數(shù)據(jù)時，就要使用三維引用。其基本格式為：

[文件名

]工作表名

!單元格地址

引用其它工作簿時，其文件名必須加方括號；源數(shù)據(jù)在同一工作簿內(nèi)則可以省略。

文件名后是工作表名稱，同時引用多個工作表相同位置的數(shù)據(jù)時，用冒號（:）標(biāo)識工作表的范圍。

工作表名稱后必須加感嘆號（!）如：=SUM(sheet1:sheet3!A1)

=[book1]sheet2!A1+sheet3!B2【例】在B2單元格中輸入一個含混合地址的公式，并通過公式復(fù)制可以快速產(chǎn)生一個九九乘法表。在B2單元格中應(yīng)該輸入一個什么公式？（[第2章公式與函數(shù)實例]九九乘法表）B2單元格中的公式為：=$A2*B$1三、數(shù)組公式

通常的公式只返回一個結(jié)果。數(shù)組公式可以同時進(jìn)行多個計算并返回一個或多個結(jié)果。

數(shù)組公式所引用的參數(shù)是數(shù)組參數(shù)，包括區(qū)域數(shù)組(單元格區(qū)域)和常量數(shù)組（一系列常數(shù)）。每個數(shù)組參數(shù)必須有相同數(shù)量的行和列。

數(shù)組公式返回的結(jié)果可以是一維或二維的。

數(shù)組公式輸入時按普通公式輸入，輸入完畢后需要按Ctrl+Shift+Enter組合鍵，系統(tǒng)將自動給公式加上花括號（{}）。

人工在公式外加上花括號是無效的。【例】在B7單元格中求銷售合計金額。

[第2章公式與函數(shù)實例]數(shù)組公式計算在B7單元格中輸入：=SUM(B2:B5*C2:C5)注意：

結(jié)束輸入時必須按Ctrl+Shift+Enter組合鍵。1、數(shù)組常量普通公式中的數(shù)值或單元格引用稱為常量。數(shù)組公式中的數(shù)值數(shù)組和數(shù)組引用稱為數(shù)組常量。數(shù)組常量必須按特定的格式輸入。

數(shù)組常量可以包含數(shù)字、文本、邏輯值（TRUE、FLASE或錯誤值#N/A）。

數(shù)組常量中可以包含不同類型的數(shù)值。

數(shù)組常量中的數(shù)字可以使用整數(shù)、小數(shù)或科學(xué)記數(shù)格式；文本必須包含在西文雙引號內(nèi)。

數(shù)組常量不包含單元格的引用、長度不等的行或列、公式或特殊字符（美元符號、括號或百分號）

數(shù)組常量置于花括號內(nèi)。不同的行用分號（;）隔開不同的列用逗號（,）隔開?！纠繑?shù)組常量：{1,2,

"ABC"

,TRUE;10,20,

"xyz"

,FALSE}相當(dāng)于對一個2行4列的區(qū)域的引用，此區(qū)域為：12ABCTRUE1020xyzFALSE=SUM({1,2,3}*{3,2,1})=SUM({1;2;3}*{3,2,1})2、數(shù)組公式舉例（1）利用數(shù)組公式進(jìn)行分類統(tǒng)計【例】對不同商品分別計算其銷售金額。[第2章公式與函數(shù)實例]數(shù)組公式求和在C12單元格中輸入公式：

=SUM(IF(A2:A10="商品1",B2:B10*C2:C10,0))P41公式分析：C12單元格中的公式：

=SUM(IF(A2:A10="商品1",B2:B10*C2:C10,0))

此公式的功能是：將A2:A10區(qū)域中數(shù)據(jù)為“商品1”的單元格的相應(yīng)B列與C列相乘后求和。

條件判斷使用IF函數(shù)；求和時使用SUM函數(shù)。

A2:A10區(qū)域中數(shù)據(jù)的判斷使用IF函數(shù)，符合條件的則相應(yīng)的B列和C列相乘，否則按零參加求和。

C13單元格按上述方法處理。

C14是對C12和C13求和，即：=C12+C13。IF函數(shù)語法格式：

IF(logical,value1,value2)功能：根據(jù)logical的值，返回value1或value2的值。說明：

如果logical的值為TRUE，則返回value1的值；如果value1為空（logical后的逗號必須存在），則返回0（零）；如果要返回TRUE，則必須要將value1設(shè)置為：TRUE。

如果logical的值為FALSE，則返回value2的值；如果value2為空（value1后的逗號存在），則返回0（零）；如果value2被忽略（即：value1后的逗號不存在），則返回FALSE。P68（2）利用數(shù)組公式排名次【例】根據(jù)平均成績排名次。[第2章公式與函數(shù)實例]利用數(shù)組公式統(tǒng)計名次例題分析：

在I3至I9單元格中輸入了數(shù)組公式：

=MATCH(H3:H9,LARGE(H3:H9,ROW(1:7)),-1)

公式中使用了3個函數(shù)：

用ROW函數(shù)產(chǎn)生數(shù)值1至7

使用LARGE函數(shù)進(jìn)行數(shù)據(jù)的排序

用MATCH函數(shù)來查找指定數(shù)值的位置P41▲ROW函數(shù)語法格式：

ROW(reference)功能：返回引用的行號。說明：

參數(shù)reference為需要得到其行號的單元格或單元格區(qū)域。

如果省略參數(shù)reference，則是對函數(shù)ROW所在行的引用。P70【例】

工作表中的數(shù)據(jù)如下圖所示：ABC1731925320365534896559873620847198988498

如果在C1中輸入：

=ROW(A5)則C1中顯示的值為：

如果在C3中輸入：

=ROW()則C3中顯示的值為：5353▲LARGE函數(shù)語法格式：

LARGE(array,k)功能：返回數(shù)據(jù)集array中第k個最大值。說明：

參數(shù)array為需要從中選擇第k個最大值的數(shù)組或單元格區(qū)域。

參數(shù)k為返回值在區(qū)域中按從大到小排序的排序次序。P53

如果數(shù)組或區(qū)域為空（即無數(shù)據(jù)），則函數(shù)返回錯誤值#NUM!。

如果k小于等于0或k大于區(qū)域中的數(shù)據(jù)個數(shù)，則函數(shù)返回錯誤值#NUM!（公式或函數(shù)中某些數(shù)字有問題）。

若區(qū)域中數(shù)據(jù)個數(shù)為n，則LARGE(array,1)返回最大的值，LARGE(array,n)返回最小的值?！纠?/p>

工作表中的數(shù)據(jù)如下圖所示：ABC1738919253988436520364

如果在B4中輸入：=LARGE(A1:C3,1)則B4中顯示的值為：

如果在C4中輸入：=LARGE(A1:C3,12)則C4中顯示的值為：98#NUM!98#NUM!▲SMALL函數(shù)語法格式：

SMALL(array,k)功能：返回數(shù)據(jù)集array中第k個最小值。說明：

參數(shù)array為需要從中選擇第k個最小值的數(shù)組或單元格區(qū)域。

參數(shù)k為返回值在區(qū)域中按從小到大排序的排序次序。P53【例】

工作表中的數(shù)據(jù)如下圖所示：ABC1738919253988436520364

如果在B4中輸入：=SMALL(A1:C3,1)則B4中顯示的值為：

如果在C4中輸入：=SMALL(A1:C3,12)則C4中顯示的值為：19#NUM!19#NUM!▲MATCH函數(shù)語法格式：

MATCH(value,array,type)功能：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。說明：

參數(shù)value為需要在數(shù)據(jù)表中查找的數(shù)值。可以是數(shù)字、文本或邏輯值，或者是對它們所在單元格的引用。

參數(shù)array為所要查找的數(shù)據(jù)可能所在的數(shù)組或連續(xù)的單元格區(qū)域（必須是一維數(shù)組或同一行或同一列的單元格區(qū)域）。P61

參數(shù)type為查找的方式，為數(shù)字1、0或-1。

如果為1，則查找小于或等于value的最大值，數(shù)組array必須按升序排列；

如果為0，則查找等于value的第一個數(shù)值，數(shù)組array可以按任意順序排列；

如果為-1，則查找大于或等于value的最小值，數(shù)組array必須按降序排列；

參數(shù)type缺省時，系統(tǒng)默認(rèn)為1。

函數(shù)返回的是查找值（value）在區(qū)域（array）中的位置，而不是查找值的本身。

函數(shù)在查找時不區(qū)分字母的大小寫。

如果查找的是文本數(shù)據(jù)，且type是0，則value可以包含通配符（*或?

）。

如果查找不成功，則返回錯誤值#N/A（公式或函數(shù)中沒有可用的數(shù)值）?！纠?/p>

工作表中的數(shù)據(jù)如下圖所示：ABC1731925320365534896559873620847198988498

如果在C1中輸入：=MATCH(98,A1:A8,0)則C1中顯示的值為：

如果在C3中輸入：=MATCH(70,B1:B8,1)則C3中顯示的值為：5454公式分析：例題中，從I3單元格至I9單元格輸入了以下公式：=MARCH(H3:H9,LARGE(H3:H9,ROW(1:7)),-1)計算時的步驟為：①計算公式ROW(1:7)，其功能是在I3:I9區(qū)域內(nèi)產(chǎn)生1至7的數(shù)。②計算公式LARGE(H3:H9,ROW(1:7))其功能是將H3:H9區(qū)域內(nèi)的數(shù)據(jù)從大到小排列，并顯示在I3:I9區(qū)域內(nèi)。③計算公式

MARCH(H3:H9,LARGE(H3:H9,ROW(1:7)),-1)其功能是查找H3:H9區(qū)域內(nèi)的數(shù)（即平均分）在區(qū)域I3:I9內(nèi)的位置（即排在第幾位，也是其名次）。▲RANK函數(shù)語法格式：

RANK(number,ref,order)功能：返回number在ref中的排位。本題也可以使用rank函數(shù)排序說明：

number是要找到排位的數(shù)字；ref為數(shù)字列表或?qū)?shù)字列表的引用。

ref中的非數(shù)值型數(shù)據(jù)將被忽略。

order為一數(shù)字，指明排位的方式。若order為0或省略，對數(shù)字的排位是基于ref的按照降序排列的列表；若order不為0，則數(shù)字的排位是基于ref的按照升序排列的列表?！纠?/p>

工作表中的數(shù)據(jù)如下圖所示：ABC178269365489553694758880

如果在C1中輸入：=RANK(78,A1:A8,1)則C1中顯示的值為：

如果在C3中輸入：=RANK(78,A1:A8)則C3中顯示的值為：5454注意：函數(shù)RANK對重復(fù)數(shù)的排位相同，但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位?！纠?/p>

工作表中的數(shù)據(jù)如下圖所示：ABC178289365489553694

如果在C1中輸入：=RANK(89,A1:A6)則C1中顯示的值為：

如果在C3中輸入：=RANK(78,A1:A6)則C3中顯示的值為：2424四、常見錯誤信息▲####錯誤原因：輸入到單元格中的數(shù)據(jù)長度超過了列的寬度，單元格中無法容納?！?div/0錯誤原因：公式中的分母為零?！?N/A錯誤原因：函數(shù)或公式中沒有可用的數(shù)值?！?

NAME?錯誤原因：公式中使用了Excel不能識別的文本?！?NULL!錯誤原因：試圖為兩個并不相交的區(qū)域指定交叉點?！?NUM!錯誤原因：公式或函數(shù)中的某些數(shù)字有問題?！?

REF!錯誤原因：單元格引用無效?！?VALUE!錯誤原因：公式中的參數(shù)或運算對象的數(shù)據(jù)類型錯誤?！?.2排序與篩選一、排序

Excel中，按遞增方式排序的數(shù)據(jù)類型及其數(shù)據(jù)的順序為：

數(shù)字從小數(shù)到大數(shù)。

文本（包含純數(shù)字文本、符號、字母、漢字）空格數(shù)字符號字母漢字

邏輯值

FALSETRUE

空白單元格（無論升序還是降序，總在最后）▲字母排序時默認(rèn)按字母順序，且不分大小寫。▲漢字可按拼音（默認(rèn)）或筆劃排序。1、利用工具按鈕排序（單字段排序）在對數(shù)據(jù)清單進(jìn)行排序時，只需將當(dāng)前單元格置于要排序的數(shù)據(jù)列，使用【排序】工具按鈕（升序按鈕或降序按鈕）進(jìn)行排序。如果選中整個數(shù)據(jù)列，則只對選中的數(shù)據(jù)進(jìn)行排序，這樣將引起數(shù)據(jù)清單中數(shù)據(jù)的對應(yīng)錯誤。2、利用菜單命令排序（可實現(xiàn)多字段排序）★操作步驟：

①將當(dāng)前單元格置于數(shù)據(jù)清單中的任意位置

②菜單命令：【數(shù)據(jù)】/【排序】

主要、次要、第三關(guān)鍵字及升（降）序

有（無）標(biāo)題行

選項

區(qū)分大小寫

按行（列）排序

按字母（筆劃）排序

自定義排序3、自定義排序

需要首先建立一個自定義序列。思考：

自定義序列如何建立？例：[第2章公式與函數(shù)實例]自定義排序二、篩選

篩選是快速從大量數(shù)據(jù)中獲取所需要的信息。1、數(shù)據(jù)檢索(不常用)★操作步驟：

①菜單命令：【數(shù)據(jù)】/【記錄單】

②單擊【條件】按鈕，輸入檢索的條件后回車

操作前當(dāng)前單元格必須置于數(shù)據(jù)清單的區(qū)域內(nèi)。

輸入的檢索條件中可以使用比較運算符號。

單擊對話框中的按鈕，可以進(jìn)行相應(yīng)的操作2、自動篩選

數(shù)據(jù)檢索是從數(shù)據(jù)清單中逐條查看檢索出的記錄。自動篩選是批量查看符合條件的記錄。★操作步驟：

①菜單命令：【數(shù)據(jù)】/【篩選】/【自動篩選】

②單擊列標(biāo)記下的下拉按鈕，設(shè)置篩選條件

自定義篩選的對話框中可以使用統(tǒng)配符，且最多設(shè)置兩個條件。這兩個條件之間既可以是“與”的關(guān)系，也可以是“或”的關(guān)系。

各列條件之間是“與”的關(guān)系。

不滿足條件的記錄將被隱藏。

菜單命令：【數(shù)據(jù)】/【篩選】/【自動篩選】可以取消自動篩選狀態(tài)。3、高級篩選

高級篩選包括兩個部分：數(shù)據(jù)清單和條件區(qū)域。條件區(qū)域是一組包含篩選條件的單元格區(qū)域，其所在的位置應(yīng)該與數(shù)據(jù)區(qū)域位置無關(guān)。

條件區(qū)域與數(shù)據(jù)清單之間至少要有一個空行或列

條件區(qū)域的首行是數(shù)據(jù)清單的字段名

條件區(qū)域的其它行即為條件行

同一條件行上的各條件之間是“與”的關(guān)系；不同條件行上的各條件之間是“或”的關(guān)系注意：

教材P46上所述的單列多條件篩選方法僅適用于多條件之間的“或”關(guān)系。

多列單條件是指的一行多列，多條件之間是“與”的關(guān)系。

多行多條件★操作步驟：

①建立條件區(qū)域（包括字段行和條件行）

②菜單命令：【數(shù)據(jù)】/【篩選】/【高級篩選】

③作相應(yīng)設(shè)置：

顯示方式

在原有區(qū)域顯示

復(fù)制到其它位置

列表區(qū)域

條件區(qū)域

復(fù)制到

選擇不重復(fù)的記錄▲Excel規(guī)定：篩選結(jié)果只能顯示在數(shù)據(jù)清單所在的工作表。▲選擇“在原區(qū)域顯示”，則篩選后原區(qū)域只顯示符合篩選條件的記錄；可由菜單命令：【數(shù)據(jù)】/【篩選】/【全部顯示】來恢復(fù)所有數(shù)據(jù)。例：[第2章公式與函數(shù)實例]成績表高級篩選§2.4圖表一、基本圖表的制作

圖表用于整理、分析和總結(jié)數(shù)據(jù)的。不同的圖表，對數(shù)據(jù)的展示效果是不同的。1、圖表的種類

▲柱形、折線、散點圖反映數(shù)據(jù)變化趨勢及對比?！鎴D適合進(jìn)行多組數(shù)據(jù)的對比與變化趨勢分析?！瀳D、圓環(huán)圖、雷達(dá)圖用于觀察數(shù)據(jù)之間的比例?！娣e圖反映一組數(shù)據(jù)在全部數(shù)據(jù)中所占的比例?！鴼馀輬D、股價圖氣泡圖可以看成是散點圖的擴(kuò)展，它用氣泡大小反應(yīng)數(shù)據(jù)點的另一個屬性股價圖是反應(yīng)類似股市行情的圖表。2、圖表創(chuàng)建【例】創(chuàng)建一個圖表，以三維簇狀柱形圖顯示各飲料的銷售量?！锊僮鞑襟E：①選擇需要用圖表表示的數(shù)據(jù)區(qū)域②菜單命令：【插入】/【圖表】，啟動圖表向?qū)Б郯磮D表向?qū)У奶崾具M(jìn)行操作

選擇圖表類型

圖表數(shù)據(jù)源選擇

數(shù)據(jù)區(qū)域選擇

系列選擇

所謂系列是在圖表中繪制的相關(guān)數(shù)據(jù)點，這些數(shù)據(jù)來自于數(shù)據(jù)表的行或列（按行時，將第一列的各行作為數(shù)據(jù)系列；按列時，將第一行的各列作為數(shù)據(jù)系列）。

圖表中的每個數(shù)據(jù)系列具有唯一的顏色或圖案并且在圖表的圖例中表示。

可以在圖表中繪制一個或多個數(shù)據(jù)系列。餅圖只有一個數(shù)據(jù)系列。

圖表選項設(shè)置

標(biāo)題

圖例

數(shù)據(jù)標(biāo)志

數(shù)據(jù)標(biāo)志包括：系列名稱、類別名稱、值等。

圖表中的條形、面積、圓點、扇面或其他符號，代表源于數(shù)據(jù)表單元格的單個數(shù)據(jù)點或值。

圖表中的相關(guān)數(shù)據(jù)標(biāo)志構(gòu)成了數(shù)據(jù)系列。

圖表位置設(shè)置（作為新工作表或?qū)ο蟛迦耄?/p>

在圖表制作完成后，可以鼠標(biāo)右擊圖表，并在快捷菜單中選擇有關(guān)命令，以便打開相應(yīng)的對話框?qū)D表的各部分選項進(jìn)行修改或添加。3、圖表修飾

在圖表制作完成后，可以鼠標(biāo)右擊圖表，并在快捷菜單中選擇有關(guān)命令，以便打開相應(yīng)的對話框?qū)D表的各部分選項進(jìn)行修改或添加。

在Excel的圖表制作中，應(yīng)用更為廣泛的是動態(tài)圖表，即能根據(jù)控件的選擇，動態(tài)地改變顯示值的圖表。由于圖表的數(shù)據(jù)區(qū)域是指定不變的，所以為了使圖表達(dá)到動態(tài)的效果，必須通過窗體控件（通常為組合框）來動態(tài)選擇顯示數(shù)據(jù)的區(qū)域。二、常用窗體控件窗體控件是Excel中允許用戶利用圖形界面的工具來顯示或輸出數(shù)據(jù)。在使用窗體控件前，先要將“窗體”工具欄打開，工具欄中灰色的控件表示目前不能使用。注意：千萬不要混淆“窗體”工具欄與“控件”工具欄P311、分組框分組框控件是個容器類控件，它通常與選項按鈕或復(fù)選框組合在一起使用?！纸M框的格式設(shè)置

標(biāo)題（編輯文字）

大小（高度和寬度）

保護(hù)（工作表保護(hù)時才生效）

屬性（位置屬性）2、選項按鈕

選項按鈕可以作為分組框的選項之一。同一分組框中的多個選項按鈕，只有其中的一個可以被選定?！x項按鈕的格式設(shè)置

標(biāo)題（編輯文字）

大?。ǜ叨群蛯挾龋?/p>

保護(hù)（工作表保護(hù)時才生效）

屬性（位置屬性）

控制

值（初始值，可設(shè)置未選擇或已選擇）

單元格鏈接（用于顯示控件值的單元格）

如果選項按鈕作為一個獨立的控件存在于工作表中時，則該控件所鏈接的單元格的值為1時表示按鈕已選中，其它值表示按鈕未選中。

控件的狀態(tài)值與所鏈接的單元格的值之間是雙向傳遞的。3、復(fù)選框

復(fù)選框用于選定某個選項。工作表或同一分組框中的多個復(fù)選框可以同時有多個復(fù)選框被選定?！鴱?fù)選框的格式設(shè)置

標(biāo)題（編輯文字）

顏色與線條（填充色及線條樣式）

大小（高度和寬度）

保護(hù)（工作表保護(hù)時才生效）

屬性（位置屬性）

控制

單元格鏈接（顯示復(fù)選框狀態(tài)值的單元格）

值

未選擇（FALSE、0或空）

已選擇選擇（TRUE或非零）

混合型（#N/A）

如果鏈接的單元格為空，則Excel將復(fù)選框的狀態(tài)解釋為False。4、列表框

列表框用于顯示項目列表，即顯示指定區(qū)域中的數(shù)據(jù)，并從中選擇其中之一?！斜砜虻母袷皆O(shè)置

控制

數(shù)據(jù)源區(qū)域（列表中顯示的數(shù)據(jù)）

單元格鏈接（返回列表框中選定項的編號）

3維陰影

選定類型（指定列表中選定項目的方式）

單選 只能選擇列表中的一項

復(fù)選 允許選擇列表中的多項

擴(kuò)展 通過同時按Ctrl或Shift鍵，可選擇列表中的多項

如果選定類型選擇“復(fù)選”或“擴(kuò)展”，則“單元格鏈接”框中指定的單元格將被忽略。5、組合框

組合框的功能與列表框相同，區(qū)別只在于組合框平常狀態(tài)下是折疊的，即只顯示其中的一項列表項，而列表框?qū)@示所有的列表項?！M合框的格式設(shè)置

控制

數(shù)據(jù)源區(qū)域（列表中顯示的數(shù)據(jù)）

單元格鏈接（返回組合框中選定項的編號）

下拉顯示項數(shù)

3維陰影6、滾動條

滾動條的功能是用鼠標(biāo)拖動滾動條的滾動塊或單擊滾動箭頭來改變控件的值?！鴿L動條的格式設(shè)置

控制

當(dāng)前值（滾動塊在滾動條中的相應(yīng)位置）

最小值（滾動塊處于最上端時對應(yīng)的值）

最大值

步長（單擊箭頭時滾動塊移動的距離）

頁步長（單擊滾動條時滾動塊移動的距離）

單元格鏈接（返回滾動塊的位置值）7、微調(diào)按鈕

微調(diào)按鈕用于增大或減小數(shù)值，其功能與滾動條相似?！⒄{(diào)按鈕的格式設(shè)置

控制

當(dāng)前值（控件當(dāng)前的數(shù)值）

最小值（控件可取的最小的值）

最大值

步長（單擊控件箭頭時增大或減小的值）

單元格鏈接（返回控件當(dāng)前的值）三、動態(tài)圖表制作（控件應(yīng)用舉例）

（詳見教材P34）例1：購買汽車的控件實例例2：素材中動態(tài)圖表模擬題INDEX函數(shù)（P60）語法格式：INDEX(reference,row_num,column_num)功能：返回數(shù)據(jù)清單或者數(shù)組中的指定位置的單元格的值，此單元格的值由行序號和列序號的索引值給定。P34說明：

reference為一個或多個單元格區(qū)域的引用

row_num和column_num為返回元素的行序號和列序號，函數(shù)即從該行和該列返回一個引用

如果單元格區(qū)域的引用只包含一行或一列，則相應(yīng)的參數(shù)row_num或column_num分別為可選項

如果將row_num或column_num設(shè)置為0，函數(shù)INDEX則分別返回整個列或行的引用【例】ABC1水果價格數(shù)量2蘋果.69403香蕉.34384檸檬.55155柑桔.25256梨.5950公式：=INDEX(A2:C6,2,3)返回A2:C6區(qū)域中第2行第3列的引用38

公式：=SUM(INDEX(A2:C6,0,3))返回A2:C6區(qū)域中第3列(即區(qū)域C2:C6)的和168公式：=INDEX(C2:C6,2)返回C2:C6區(qū)域中第2行的引用38

公式：=INDEX(B6:C6,2)返回B6:C6區(qū)域中第2列的引用50

PMT函數(shù)（P58）語法格式：PMT(rat,nper,pv,fv,type)功能：基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。語法：PMT(rat,nper,pv,fv,type)說明：

rate為各期利率，是一固定值

nper為總投資（貸款）期，即該項投資（或貸款）的付款期總數(shù)

pv為現(xiàn)值，或一系列未來付款的當(dāng)前值的累積和，即本金

fv為未來值，或在最后一次付款后希望得到的現(xiàn)金余額。若省略，則假設(shè)其值為零

type為0或1，用以指定各期的付款時間是期初還是期末。若省略，這假設(shè)其值為零【例】計算10個月付清的年利率為6%的10000貸款的月支額。公式：=PMT(6%/12,10,10000)計算值：-1027.71三、數(shù)據(jù)透視分析

所謂數(shù)據(jù)的透視分析是從不同的分析角度，對同一張報表根據(jù)不同的指標(biāo)進(jìn)行分類匯總。數(shù)據(jù)透視分析有：

▲分類匯總

▲數(shù)據(jù)透視表

▲數(shù)據(jù)透視圖1、分類匯總

分類匯總之前必須：▲確定分類的依據(jù)▲按此進(jìn)行排序?！纠糠诸悈R總各種飲料的銷售額?！锊僮鞑襟E：

按飲料的品名進(jìn)行排序

菜單命令：【數(shù)據(jù)】/【分類匯總】

設(shè)置分類匯總的各選項

分類字段

匯總方式（求和、平均值、計數(shù)、標(biāo)準(zhǔn)差等）

選定匯總項（可以是一個或多個）

分類匯總必須按分類的依據(jù)進(jìn)行排序，否則將不能得到正確的結(jié)果。

單擊匯總結(jié)果左側(cè)的“＋”和“－”可實現(xiàn)顯示/隱藏明細(xì)數(shù)據(jù)。

單擊左側(cè)上方的“1”、“2”、“3”可以實現(xiàn)顯示數(shù)據(jù)的級數(shù)：

1：總計數(shù)2：分類匯總數(shù)3：明細(xì)數(shù)據(jù)

取消匯總可以單擊對話框中的【全部刪除】按鈕。2、數(shù)據(jù)透視表使用數(shù)據(jù)透視表可以：▲從不同的角度查看匯總數(shù)據(jù)▲按照不同方式對數(shù)據(jù)進(jìn)行匯總：求和、求平均值、求最大值或最小值、計數(shù)、乘積等▲篩選數(shù)據(jù)或顯示合計值的明細(xì)數(shù)據(jù)▲動態(tài)地改變其版面布置利用數(shù)據(jù)透視表進(jìn)行分類匯總有兩種方法：

在Excel數(shù)據(jù)清單上利用數(shù)據(jù)透視表進(jìn)行匯總

利用數(shù)據(jù)透視表直接從數(shù)據(jù)庫中查詢數(shù)據(jù)并匯總數(shù)據(jù)【例】

制作各地區(qū)不同類別商品的銷售量和銷售額的數(shù)據(jù)透視表（數(shù)據(jù)來源于Excel工作表）?！锊僮鞑襟E：

菜單命令：【數(shù)據(jù)】/【數(shù)據(jù)透視表】

按向?qū)崾驹O(shè)置各選項

數(shù)據(jù)來源

數(shù)據(jù)源區(qū)域

布局（頁、行、列、數(shù)值）

選項（列總計、行總計）

數(shù)據(jù)透視表位置【例】

統(tǒng)計“銷售表”工作表中不同規(guī)模銷售數(shù)量的發(fā)生次數(shù)以及頻率分布（如下圖所示）?！锊僮鞑襟E：

啟動數(shù)據(jù)透視表向?qū)?/p>

將“地區(qū)”、“分類”字段拖至頁區(qū)域，將“數(shù)量”拖至行區(qū)域和數(shù)值區(qū)域，并將數(shù)值區(qū)域的匯總方式改為計數(shù)

取消“列總計”和“行總計”

在數(shù)據(jù)透視表中右擊行字段“銷售數(shù)量”，并調(diào)用快捷菜單命令：【組及顯示明細(xì)數(shù)據(jù)】/【組合】

設(shè)置分組的起始值、終值及步長。【例】

在飲料銷售的數(shù)據(jù)透視表中，將金陵啤酒、青島啤酒合并為啤酒，雪碧、可樂合并為飲料統(tǒng)計其銷售額，并顯示其占總銷售額的百分比（如下圖所示）?！纠?/p>

在“銷售數(shù)據(jù)”工作表中建立一個數(shù)據(jù)透視表（不導(dǎo)入Access數(shù)據(jù)庫中的數(shù)據(jù)），要求：（1）輸出字段為：產(chǎn)品名稱、訂購日期、貨主地區(qū)、銷售額；（2）按年和月統(tǒng)計不同地區(qū)不同產(chǎn)品的銷售總額；（3）顯示每年銷售額最大的3個月。注：銷售額=單價×數(shù)量

×(1－折扣)

首先創(chuàng)建查詢，需要3張表：產(chǎn)品表（產(chǎn)品名稱）、訂單表（訂購日期和貨主地區(qū)）和訂單明細(xì)表（單價、數(shù)量和折扣）。產(chǎn)品表和訂單表示主表，訂單明細(xì)表是子表。

如果需要輸出的數(shù)據(jù)不在查詢數(shù)據(jù)源中，則必須在查詢中添加計算字段；如果在數(shù)據(jù)透視表中添加，可能會導(dǎo)致數(shù)據(jù)的計算錯誤。

字段的組合只能在行區(qū)域內(nèi)完成，如果該字段是位于其它區(qū)域，則必須先將其放置于行區(qū)域并完成組合后，再移至其它區(qū)域。3、數(shù)據(jù)透視圖

數(shù)據(jù)透視圖與數(shù)據(jù)透視表相似，只不過它是以圖表的方式顯示數(shù)據(jù)之間的關(guān)系的。數(shù)據(jù)透視圖的建立方式與數(shù)據(jù)透視表的建立方式完全相同，啟動向?qū)Ш蟀刺崾疽徊揭徊降剡M(jìn)行設(shè)置?！?.3函數(shù)Excel中的函數(shù)實質(zhì)上是一個預(yù)定義的計算公式，它使用一個或多個數(shù)值（參數(shù)）執(zhí)行一系列的運算，然后返回一個運算結(jié)果（函數(shù)值）。一、函數(shù)簡介1、函數(shù)結(jié)構(gòu)函數(shù)由三部分組成：▲函數(shù)名表示函數(shù)要執(zhí)行的運算▲參數(shù)

指定函數(shù)使用的值（可以是常數(shù)、單元格名稱或區(qū)域名稱）▲圓括號將各參數(shù)括于其中（無參數(shù)時也不能缺?。?/p>

當(dāng)函數(shù)中含有多個參數(shù)時，各個參數(shù)之間用逗號隔開

函數(shù)名與括號之間不能有空格或其它字符

當(dāng)函數(shù)的參數(shù)是單元格或區(qū)域地址時，參與運算的是其中的值

不同的函數(shù)，其參數(shù)個數(shù)是各不相同的；同一函數(shù)的各個參數(shù)的數(shù)據(jù)類型也不盡相同。因此，在使用函數(shù)時，一定要正確輸入函數(shù)的參數(shù)。2、使用函數(shù)★操作步驟：①單擊編輯欄中的【插入函數(shù)】按鈕②選擇函數(shù)的類別和函數(shù)③設(shè)置函數(shù)的參數(shù)3、函數(shù)的種類

Excel有數(shù)據(jù)庫函數(shù)、日期與時間函數(shù)、財務(wù)函數(shù)等11種（各種函數(shù)功能詳見教材P49~P50）。▲數(shù)據(jù)庫函數(shù)當(dāng)需要分析數(shù)據(jù)清單中的數(shù)值是否符合特定條件時，將使用數(shù)據(jù)庫函數(shù)。數(shù)據(jù)庫函數(shù)共有12個，這些函數(shù)統(tǒng)稱D函數(shù)。每個數(shù)據(jù)庫函數(shù)都有3個參數(shù)：

database

指定數(shù)據(jù)清單區(qū)域

field

指定數(shù)據(jù)清單中的列

criteria

指定條件區(qū)域二、常用函數(shù)1、日期函數(shù)▲DATE函數(shù)語法：DATE(year,month,day)功能：返回指定的日期。說明：

year可以是1至4位數(shù)。當(dāng)年份是1至2位數(shù)時，返回的是20世紀(jì)的年份。

month的值大于正常的月份數(shù)或為負(fù)，則從指定的年份的一月開始往上加算（或往下減算）。

day的值大于正常的月份數(shù)或為負(fù)，則從指定的月份的1日開始往上加算（或往下減算）?！纠緿ATE(2012,13,34)返回2013年2月3日▲NOW函數(shù)語法：NOW()功能：返回當(dāng)前日期和時間。說明：

可以根據(jù)需要設(shè)置單元格的格式，只顯示日期或時間?！鳷ODAY函數(shù)語法：TODAY()功能：返回今天的日期序號。說明：

如果將單元格的顯示格式設(shè)置為時間，那么顯示為：00:00:00。

這兩個函數(shù)均無參數(shù)（教材P51舉例有錯）。2、數(shù)學(xué)函數(shù)▲FLOOR函數(shù)語法：FLOOR(number,significance)功能：將number沿絕對值減小的方向取值，使其值等于最接近的significance倍數(shù)。說明：

任一參數(shù)為非數(shù)值參數(shù)，則返回：#VALUE！

如果兩個參數(shù)符號相反，則返回：#NUM！

無論參數(shù)的符號是正或負(fù)，取值時，參數(shù)的絕對值都將減小。【例】FLOOR(12.4,3.3)FLOOR(-43,-13.4)9.940.2【例】FLOOR(0,7)FLOOR(0,0)FLOOR(7,0)00#DIV/0!▲CEILING函數(shù)語法：CEILING(number,significance)功能：將number沿絕對值增大的方向取值，使其值等于最接近的significance倍數(shù)。說明：

任一參數(shù)為非數(shù)值參數(shù)，則返回：#VALUE！

如果兩個參數(shù)符號相反，則返回：#NUM！

無論參數(shù)的符號是正或負(fù)，取值時，參數(shù)的絕對值都將增大?！纠緾EILING(12,7)CEILING(-4,-3.4)CEILING(7,0)CEILING(0,7)146.800▲MOD函數(shù)語法：MOD(number,divisor)功能：返回兩個數(shù)相除后的余數(shù)。說明：

number為被除數(shù)，divisor為除數(shù)。

divisor為零時返回：#DIV/0！

結(jié)果的正負(fù)號與divisor相同。

如果兩個數(shù)的符號相同，函數(shù)返回值的絕對值即為兩數(shù)相除后的余數(shù)。

如果兩個數(shù)的符號不相同，則其余數(shù)要被除數(shù)相減?！纠縈OD(12,7) 5MOD(-12,-7)

-5MOD(12.5,7.3)

5.2MOD(-12,7) 2MOD(12,-7)

-2MOD(-12.5,7.3)

2.1

如果兩個數(shù)的符號相同，函數(shù)返回值的絕對值即為兩數(shù)相除后的余數(shù)。

如果兩個數(shù)的符號不相同，則其余數(shù)要與除數(shù)相減。

以下公式成立：MOD(n,d)=n-d*INT(n/d)▲ROUNDUP函數(shù)語法：ROUNDUP(number,num_digits)功能：對number遠(yuǎn)離零值，向上舍入數(shù)字。說明：

取舍位數(shù)由參數(shù)num_digits決定。

ROUNDUP函數(shù)和ROUND函數(shù)的功能基本相同，兩者區(qū)別的是：ROUNDUP函數(shù)在進(jìn)行取舍時總是向上取舍，即進(jìn)行遠(yuǎn)離0的取舍。

當(dāng)取舍位數(shù)為0時，取舍到整數(shù)；取舍位數(shù)為負(fù)值時，即表示小數(shù)點的左側(cè)應(yīng)有多少個零。【例】ROUND(18.435,2) ROUNDUP(18.435,2) 18.44 18.44ROUND(18.435,1) ROUNDUP(18.435,1) 18.4 18.5ROUND(-18.435,1) ROUNDUP(-18.435,1) -18.4 -18.5ROUND(18.435,0) ROUNDUP(18.435,0) 18 19ROUND(18.435,-1) ROUNDUP(18.435,-1) 20 20ROUND(18.435,-2) ROUNDUP(18.435,-2) 0 100▲ROUNDDOWN函數(shù)語法：ROUNDDOWN(number,num_digits)功能：對number進(jìn)行靠近零值，向下舍入數(shù)字。說明：

ROUNDDOWN函數(shù)和ROUNDUP函數(shù)的功能差不多，只不過ROUNDUP函數(shù)在進(jìn)行取舍時總是向上取舍即遠(yuǎn)離0的取舍；而ROUNDDOWN函數(shù)在進(jìn)行取舍時總是向下取舍即進(jìn)行靠近0的取舍?！纠縍OUNDDOWN(18.435,2) 18.43ROUNDDOWN(18.435,1) 18.4ROUNDDOWN(-18.435,2) -18.43ROUNDDOWN(18.435,0) 18ROUNDDOWN(18.435,-1) 10ROUNDDOWN(18.435,-2) 0▲MAX函數(shù)、MIN函數(shù)

MAXA函數(shù)、MINA函數(shù)語法格式：

MAX(number1,number2,…)MIN(number1,number2,…)功能：返回一組值中的的最大值（最小值）。說明：

參數(shù)是數(shù)字或數(shù)字的文本表達(dá)式時：

TRUE作為1計算；FALSE作為0計算

參數(shù)為錯誤值或不能轉(zhuǎn)換為數(shù)字的文本，將產(chǎn)生錯誤?！纠縈AX(12,56,9,48)MAX(-12,-23,TRUE,0)MIN(2,23,FALSE,1)MIN(5^2,4^3,"10")561010

參數(shù)為數(shù)組或引用時：

只有數(shù)組或引用中的數(shù)字將被計算

數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。

如果引用參數(shù)不包含數(shù)字，則結(jié)果返回為0。

如果邏輯值和文本不能忽略，則使用MAXA函數(shù)和MINA函數(shù)。在此兩個函數(shù)中，TRUE作為1計算；FALSE和文本作為0計算。

▲RANK函數(shù)語法格式：

RANK(number,ref,order)功能：返回number在ref中的排位。說明：

number是要找到排位的數(shù)字；ref為數(shù)字列表或?qū)?shù)字列表的引用。

ref中的非數(shù)值型數(shù)據(jù)將被忽略。

order為一數(shù)字，指明排位的方式。若order為0或省略，對數(shù)字的排位是基于ref的按照降序排列的列表；若order不為0，則數(shù)字的排位是基于ref的按照升序排列的列表?！纠?/p>

工作表中的數(shù)據(jù)如下圖所示：ABC178269365489553694758880

如果在C1中輸入：=RANK(78,A1:A8,1)則C1中顯示的值為：

如果在C3中輸入：=RANK(78,A1:A8)則C3中顯示的值為：5454注意：函數(shù)RANK對重復(fù)數(shù)的排位相同，但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。【例】

工作表中的數(shù)據(jù)如下圖所示：ABC178289365489553694

如果在C1中輸入：=RANK(89,A1:A6)則C1中顯示的值為：

如果在C3中輸入：=RANK(78,A1:A6)則C3中顯示的值為：2424思考題：教材P41根據(jù)平均分排名次的例題，是否能用RANK函數(shù)來實現(xiàn)？▲COUNT函數(shù)語法格式：

COUNT(value1,value2,…)功能：返回包含數(shù)字及包含參數(shù)列表中的數(shù)字的單元格的個數(shù)。說明：

參數(shù)value1,value2,…為包含或引用各種類型數(shù)據(jù)的參數(shù)（1至30個），但只有數(shù)字類型的數(shù)據(jù)才被計算。

函數(shù)將把數(shù)字、日期或以文本代表的數(shù)字計算在內(nèi)；但錯誤值或其它無法轉(zhuǎn)換為數(shù)字的文字將被忽略。

如果參數(shù)是一個數(shù)組或引用，那么只統(tǒng)計數(shù)組或引用中的數(shù)字；數(shù)組或引用中的空白單元格、邏輯值、文本或錯誤值都將被忽略。

如果要統(tǒng)計邏輯值、文本或錯誤值需要用COUNTA函數(shù)?！纠?COUNT(23,78,"123",TRUE,2012-5-1,"A")

返回值：5

如果在A6中輸入：

=COUNT(A1:A5)

則A6中顯示： 如果在A6中輸入：

=COUNTA(A1:A5)

則A6中顯示：A12323"123"4TRUE52012-5-162（僅指23和2012-5-1）24（A2單元格除外）SUM、AVERAGE函數(shù)也有類似的性質(zhì)，即：

參數(shù)是常數(shù)時，函數(shù)將把數(shù)字、日期或以文本代表的數(shù)字計算在內(nèi)；但錯誤值或其它無法轉(zhuǎn)換為數(shù)字的文字將被忽略。

如果參數(shù)是一個數(shù)組或引用，那么只統(tǒng)計數(shù)組或引用中的數(shù)字；數(shù)組或引用中的空白單元格、邏輯值、文本或錯誤值都將被忽略?！鳶UMIF函數(shù)語法格式：

SUMIF(range,criteria,sum_range)功能：根據(jù)指定條件對若干單元格求和。說明：

range是需要條件判斷的單元格區(qū)域；criteria為條件，可以是數(shù)字、文本或表達(dá)式且必須加雙引號；sum_range是需要求和的實際區(qū)域。

只有range區(qū)域中符合指定條件的單元格，其在sum_range區(qū)域相應(yīng)的單元格才求和。

如果參數(shù)sum_range缺省，則對range區(qū)域中符合條件的數(shù)據(jù)求和?！纠拷y(tǒng)計奶制品的銷售合計可以使用公式：

=SUMIF(B2:B11,"奶制品",C2:C11)（教材P55有錯誤）

如果要進(jìn)行多條件統(tǒng)計，則需要用數(shù)組公式來完成?！纠拷y(tǒng)計南部肉類銷售合計和東部和南部銷售合計。C13和C14公式分別為：=SUM(IF((A2:A11="南部")*(B2:B11="肉類"),C2:C11))=SUM(IF((A2:A11="東部")+(A2:A11="南部"),C2:C11))

公式中的“*”，相當(dāng)于AND連接；“+”相當(dāng)于OR連接?！鳦OUNTIF函數(shù)語法格式：

COUNTIF(range,criteria)功能：計算區(qū)域中滿足指定條件的單元格數(shù)。說明：

range是需要計算其中滿足條件的單元格數(shù)目的區(qū)域；criteria是指定的條件?！纠拷y(tǒng)計奶制品的銷售記錄數(shù)：

=COUNTIF(B2:B11,"奶制品")統(tǒng)計銷售品種數(shù)：

=SUM(1/COUNTIF(B2:B11,B2:B11))▲SUBTOTAL函數(shù)語法格式：

SUBTOTAL(function_num,ref1,ref2,…)功能：返回數(shù)據(jù)清單或數(shù)據(jù)庫中的分類匯總。說明：

function_num為1到11（或101至111）的數(shù)字，指定使用何種函數(shù)在數(shù)據(jù)清單中進(jìn)行分類匯總。

ref1,ref2,…為要進(jìn)行分類匯總計算的區(qū)域。Function_num函數(shù)Function_num函數(shù)1AVERAGE7STDEV2COUNT8STEDVA3COUNTA9SUM4MAX10VAR5MIN11VARA6PRODUCTfunction_num參數(shù)對照表【例】 =SUBTOTAL(1,A1:A5)

3 =SUBTOTAL(9,A1:B5) 165AB11102220333044405550▲PRODUCT函數(shù)語法格式：

PRODUCT(number1,number2,…)功能：將所有以參數(shù)形式出現(xiàn)的數(shù)字相乘。說明：

當(dāng)參數(shù)是數(shù)字、邏輯值或數(shù)字文本時可以被計算；當(dāng)參數(shù)為錯誤值或是不能轉(zhuǎn)換成數(shù)字的文字時，將會導(dǎo)致錯誤。

當(dāng)參數(shù)是數(shù)組或引用時，只有其中的數(shù)字才被計算；其中的空白單元格、邏輯值、文本或錯誤值將被忽略?！纠?PRODUCT(A1:B2)

400=

PRODUCT(A3:B4) 360=PRODUCT(10,3-1,"3") 60

=

PRODUCT(A3:B4)

120=PRODUCT(10,"2",FALSE) 0=PRODUCT(10,"ABC")

#VALUE!3、查找函數(shù)▲INDEX函數(shù)語法：

INDEX(array,row_num,column_num)功能：返回引用中指定單元格區(qū)域的引用，此單元格區(qū)域由行序號和列序號的索引值給定。說明：

array為單元格區(qū)域或數(shù)組常量。

row_numn和column_numn為引用中的行序號和列序號，函數(shù)即從該行和該列返回一個引用。

如果引用中只包含一行或一列，則相應(yīng)的參數(shù)row_num或column_num分別為可選項。P60

如果將row_num和column_num同時存在，函數(shù)則返回row_num和column_num的交叉單元格中的值。

如果將row_num或column_num設(shè)置為0，函數(shù)INDEX則分別返回整個列或行的數(shù)組數(shù)值?！纠緼BC1水果價格數(shù)量2蘋果.69403香蕉.34384檸檬.55155柑桔.25256梨.5950公式：=INDEX(A2:C6,2,3)返回A2:C6區(qū)域中第2行第3列的引用38

公式：=SUM(INDEX(A2:C6,0,3))返回A2:C6區(qū)域中第3列(即區(qū)域C2:C6)的和168▲CHOOSE函數(shù)語法：

CHOOSE(index_num,value1,value2,…)功能：根據(jù)索引值返回數(shù)值參數(shù)列表中相應(yīng)的數(shù)值。說明：

index_num為索引值。其值必須是從1至29之間的數(shù)字、或包含數(shù)字1至29的單元格引用。

index_num的值若小于1或大于數(shù)值參數(shù)列表中最后一個參數(shù)的序號，則返回錯誤值#VALUE！。

如果index_num為小數(shù)，則在函數(shù)計算前將被截尾取整?！纠?/p>

CHOOSE(2,"ABC",3*4,56,TRUE)

12CHOOSE(2+1,"ABC",3*4,2012-1-1)

2010▲VLOOKUP函數(shù)語法：

VLOOKUP(value,table,c_index,range_lookup)功能：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回該數(shù)值所在行中指定列處的數(shù)值。說明：

value為需要在數(shù)組第一列中查找的數(shù)值。value可以是數(shù)值、引用或文本字符串。

table為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。可以是區(qū)域、數(shù)據(jù)庫或列表。

c_index為table中待返回匹配值的列序號。

如果小于1，函數(shù)返回錯誤值#VALUE!；

如果大于table的列數(shù)，函數(shù)返回錯誤值#REF!。

range_lookup是一個邏輯值，指明函數(shù)查找時，是精確匹配查找還是近似匹配查找。

range_lookup為TRUE（默認(rèn)值）時，函數(shù)為近似匹配值查找，即查找等于或小于value的最大數(shù)值；此時table第一列的數(shù)值必須按升序排序，否則函數(shù)不能返回正確的數(shù)值。

range_lookup為FALSE時，函數(shù)為精確匹配值查找。若找不到，則返回錯誤值#N/A。此時table的第一列可以不排序。【例】ABC1.34551002.37424003.45672504.68323005.79801506.8577700=VLOOKUP(.5,A1:C6,2) 67=VLOOKUP(.5,A1:C6,2,FALSE) #N/A=VLOOKUP(1,A1:C6,3) 700=VLOOKUP(.1,A1:C6,3) #N/A▲HLOOKUP函數(shù)語法：

HLOOLUP(value,table,r_index,range_lookup)功能：在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回該數(shù)值所在列中指定行處的數(shù)值?！鳯OOKUP函數(shù)

LOOKUP函數(shù)具有兩種語法形式：向量形式和數(shù)組形式。

語法一：

LOOKUP(value,lookup_vector,[result_vector])功能：在單行區(qū)域或單列區(qū)域（稱為“向量”）中查找值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的值。說明：

value需要查找的值，可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊谩?/p>

lookup_vector查找的區(qū)域，是單行或單列的區(qū)域，且必須按升序排序。

result_vector是函數(shù)返回值所在區(qū)域。此參數(shù)是一個可選項，且是個只包含一行或一列的區(qū)域。result_vector參數(shù)必須與lookup_vector參數(shù)大小相同。若缺省，則返回所查找的值。

如果函數(shù)找不到value，函數(shù)將與lookup_vector中小于或等于value的最大值進(jìn)行匹配。

如果value小于lookup_vector中的最小值，則函數(shù)返回#N/A錯誤值。【例】ABC1水果價格2柑桔4.503梨2.304檸檬5.005蘋果3.506香蕉3.30=LOOKUP("蘋果",A2:A6,C2:C6) 3.50語法二：

LOOKUP(value,array)功能：在數(shù)組的第一行或第一列中查找指定的值，然后返回數(shù)組的最后一行或最后一列中相同位置的值說明：

value是在數(shù)組中查找的值，可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊谩?/p>

array是包含要與value進(jìn)行比較的文本、數(shù)字或邏輯值的單元格區(qū)域，必須按升序排序。

如果找不到value值，將使用數(shù)組中小于或等于value的最大值。

如果value的值小于第一行或第一列中的最小值（取決于數(shù)組的維數(shù)），函數(shù)返回#N/A錯誤值。

如果數(shù)組包含寬度比高度大的區(qū)域（列數(shù)多于行數(shù)），函數(shù)將在第一行中搜索；反之，在第一列中搜索?！纠緼BC1水果價格2柑桔4.503梨2.304檸檬5.005蘋果3.506香蕉3.30=LOOKUP("香蕉",A2:A6,C2:C6) 3.30=LOOKUP(65,{12,34,65,79;86,50,42,68;93,66,200,99})

200

查找函數(shù)在制作動態(tài)圖表時的作用極為重要。不同的查找函數(shù)其功能是有所不同的。按其查找內(nèi)容及返回值，可以將查找函數(shù)分為：

函數(shù)通過索引號在指定區(qū)域中進(jìn)行查找，查找后返回相應(yīng)單元格的值。如：INDEX函數(shù)、CHOOSE函數(shù)等;

函數(shù)在指定區(qū)域中查找指定的值，查找后返回該值在查找區(qū)域中的位置。如：MATCH函數(shù)；

函數(shù)在指定區(qū)域中查找指定的值，查找后返回與該值在查找區(qū)域中位置相對應(yīng)的另一個區(qū)域中的值。如：LOOKUP函數(shù)（包括HLOOKUP和VLOOKUP函數(shù)）。

按其查找方式，可以將查找函數(shù)分為：精確查找。如LOOKUP函數(shù)（可以使用統(tǒng)配符）、參數(shù)為0的MATCH函數(shù)、LOOKUP函數(shù)以及參數(shù)為FALSE的HLOOKUP和VLOOKUP函數(shù);

模糊查找。如：MATCH函數(shù)、參數(shù)為TRUE的HLOOKUP和VLOOKUP函數(shù)。4、文本函數(shù)▲CHAR函數(shù)語法：

CHAR(number)功能：返回對應(yīng)的ASCII碼的字符?！鳦ODE函數(shù)語法：

CODE(text)功能：返回字符串中第一個字符的ASCII碼?！鳯EFTB和RIGHTB函數(shù)語法：

LEFTB(text,num_bytes)RIGHTB(text,num_bytes)功能：根據(jù)指定的字節(jié)數(shù)返回文本中開始（或最后）的若干個字符。說明：

num_bytes為需要從字符串中提取的字節(jié)數(shù)。

num_bytes必須大于等于0。

如果num_bytes大于字