11VL00KUP函數

語法規則

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）

參數簡樸闡明輸入數據類型

bokup_value要查找的值數值、引用或文本字符串

table_array要查找的區域數據農區域

col_index_num返回數據在查找區域的第幾列數正整數

rangejookup模糊匹配TRUE（或不填）/FALSE

參數闡明

Lookup_value為需要在數據表第一列中進行查找的數值。Lookup_value可認為數值、引用或文本

字符串。

Table_array為需要在其中查找數據的數據表。使用對區域或區域名稱的引用。

col_index_numtable_array中查找數據的數據列序號。col_index_num為1時，返回

table_array第一列的數值，col_index_num為2時，返回table_array第二列的數值，以此類推。假

如col_index_num不不小于1,函數VLOOKUP返回錯誤值#VALUE!；假如col_index_num不小

于table_array歐I列數，函數VLOOKUP返回錯誤值#REF!。

Range」ookup為一邏輯值，指明函數VLOOKUP查找時是精確匹配，還是近似匹配。假如為false或0,

則返回精確匹配，假如找不到，則返回錯誤值#N/A。假如rangejookup為TRUE或1,函數

VLOOKUP將查找近似匹配值，也就是說，假如找不到精確匹配值，則返回不不小于lookup_valueK

最大數值。假如range」ookup省略，則默認為近似匹配。

例如：

【第1套】

=VL00KUP（D3,編號對照!$A$3:$C$19,2,FALSE）

【第5套】

=VLOOKUP（E3,費用類別!$A$3:$B$12,2,FALSE）

【第9套】

=VLOOKUP（D3,圖書編目表!$A$2:$B$9,2,FALSE）

【第10套】

=VL00KUP（A2,初三學生檔案!$A$2:$B$56,2,0）

SUMPRODUCT函數

闡明：數組參數必須具有相似的維數，否則，函數SUMPRODUCT將返回錯誤值#VALUE!O函數

SUMPRODUCT將非數值型的數組元素作為0處理。

含義:SUM:【數】求和；PRODUCT:【數】(乘)積20istheproductof5and

4.二十是五與四B勺乘積。SUMPRODUCT:組合的漢語意思是:乘積之和。在給

定日勺幾組數組中，將數組間對應的元素相乘，并返回乘積之和。

語法：SUMPRODUCT(arrayl,array2,array3,...)

Arrayl,array2,array3，…為2到30個數組,其對應元素需要進行相乘并求和

實

例：BCDE

1ArraylArraylArray2Array2

23427

38667

41953

公式：=SUMPRODUCT（B2:C4*D2:E4）

成果：兩個數組的所有元素對應相乘，然后把乘積相加，即3*2+4*7+8*6+6*7+l*5+9*3o

闡明

LSUMPRODUCT函數不支持“*”和“？”通配符。

SUMPRODUCT函數不能象SUMIF、COUNTIF等函數同樣使用和“？”等通配符，要實現此功

能可以用變通H勺措施，如使用LEFT、RIGHT.ISNUMBER(FIND(|)或ISNUMBER(SEARCH())等函數來實現通

配符H勺功能。

2、SUMPRODUCT函數多條件求和時使用“，”和“*”的區別：當擬求和的區域中無文本時兩者無

區別，當有文本時，使用時會出錯，返回錯誤值#VALUE!,而使用“，”時SUMPRODUCT函數會

將非數值型的數組元素作為0處理，故不會報錯。

應用實例

一、基本功能：函數SUMPRODUCT的功能返回對應的區域或數組乘積二、用于多條件計數用數學函數

SUMPRODUCT計算符合2個及以上條件的數據個數

a?:TRUE*1=1,FALSER=1*FALSE=0,TRUE*0=0*TRUE=0。數組中用分號分隔，表達數組是一列數

組.分號相稱于換行。兩個數組相乘是同一行的對應兩個數相乘。

三、用于多條件求和對于計算符合某一種條件的數據求和，可以用SUMIF函數來處理。假如要計算符合2

個以上條件的數據求和，用SUMIF函數就不可以完畢了。這就可以用函數SUMPRODUCTo用函數

SUMPRODUCT計算符合多條件的數據和，其基本格式是：

SUMPRODUCT(條件1*條件2,……,求和數據區域)考試題中，求和公式在本來的計數公式中，在相似

判斷條件下，增長了一種求和的數據區域。也就是說，用函數SUMPRODUCT求和，函數需要的參數一種

是進行判斷的條件，另一種是用來求和的數據區域。

*1時解釋

umproduct函數，逗號分割的各個參數必須為數字型數據，假如是判斷的成果邏輯值，就要乘1轉換為數

字、假如不用逗號，直接用*號連接，就相稱于乘法運算，就不必添加*。

例如:

【第1套】

=SUMPRODUCT（1*（訂單明細表!E3:E262="《MSOffice高級應用》”）,訂單明細表!H3:H262）

1

=SUMPRODUCT（1*（訂單明細表!C350:C461="隆華書店”），訂單明細表!H350:H461）

=SUMPRODUCT（1*（訂單明細表!C263:C636="隆華書店”），訂單明細表!H263:H636）/12

【第5套】

=SUMPRODUCT（1*（費用報銷管理!D74:D340="北京市"），費用報銷管理!G74:G340）

=SUMPRODUCT（1*（費用報銷管理!B3:B401="錢順卓"），1*（費用報銷管理!F3:F401="火車票"），費用報銷管

理!G3:G401）

=SUMPRODUCT（1*（費用報銷管理!F3:F401=*飛機票”費用報銷管理!G3:G401）/SUM（費用報銷管

理!G3:G401）

=SUMPRODUCT（（費用報銷管理！H3:H401="是"）*（費用報銷管理！F3:F401/通訊補助"）,費用報銷管

理!G3:G401）

【第7套】

=SUMPRODUCT（1*（D3:D17="管理"），13:117）

=SUMPRODUCT（1*（D3:D17="管理"），M3:M17）

IF函數

IF函數，根據指定的條件來判斷其嗔”（TRUE）、“假”（FALSE）；根據邏輯計算的真假值，從而返回對應的內

容.用途：執行真假值判斷

函數使用方法

1.IF函數的語法構造

IF(logical_testzvalue_if_truezvalue_if_false)

即：IF函數的I語法構造：IF(條件，成果1,成果2)。

2.IF函數的J功能

對滿足條件口勺數據進行處理，條件滿足則輸出成果1,不滿足則輸出成果2。可以省略成果1或成果2,但

不能同步省略。

3.條件體現式

把兩個體現式用關系運算符(直要有:，<>,>,<,>=,<=等6個關系運算符)連接起來就構成條件體現

式,

4.IF函數嵌套的執行過程

假如按等級來判斷某個變量，1F函數U勺格式如下：

IF(E2>=85,“優”,IF(E2>=75,”良“,IF(E2>=60,“及格”，“不及格")))

函數從左向右執行。首先計算E2>=85,假如該體現式成立，則顯示“優"假如不成立就繼續計算

E2>=75z假如該體現式成立，則顯示“良”，否貝IJ繼續計算E2>=60,假如該體現式成立，則顯示“及格”，

否則顯示“不及格”。

例如：

【第5套】

=IF(WEEKDAY(A3,2)>5,〃是〃「否”)

【第7套】

=R0UND(IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100-555

,IF(K3<=35000,K3*25%-1005,IF(K3<=5500,K3*30%-2755,IF(K3<=80000,K3*35%-5505,IF(K

3>80000,K3*45%-13505))))))),2)

【第10套】

=IF(M0D(MID(C2,17,1),2)=1,〃男〃女")

=IF(F2>=102,"優秀"，IF(F2>=84,"良好”,IF(F2>=72,"及格”，IF(F2>72,"及格"，"不及格"))))

=IF(F2〉=90,"優秀”，IF(F2>=75,"良好”，IF(F2>=60,"及格”，IF(F2>60,"及格"，"不及格”))))

【第10套】

=IF(MID(A3,4,2)-01","1班〃，IF(MID(A3,4,2)="02","2班〃，"3班〃))

SUMIFS函數

根據多種指定條件對若干單元格求和。

函數使用方法

SUMIFS(sum_range,criteria_range1,criterial,[criteria_range2,criteria2],...)

1)sum.range是需規定和H勺實際單元格。包括數字或包括數字的名稱、區域或單元格引用。忽視空白

值和文本值。

2)criteria_range1為計算關聯釜件的J第一種區域。

3)criterial為條件1,條件日勺形式為數字、體現式、單元格引用或者文本，可用來定義將對criteria」ange1

參數中的哪些單元格求和。例如,條件可以表達為32.“>32"、B4.“蘋果"、或“32”.

4)criteria_range2為用于條件2判斷日勺單元格區域。

5)criteria2為條件2,條件口勺形式為數字、體現式、單元格引用或者文本，可用來定義將對criteria_range2

參數中的哪些單元格求和。

4)和5)最多容許127個區域/條件對，即參數總數不超255個。

【第9套】

=SUMIFS（銷售訂單!$H$3:$H$678,銷售訂單!$E$3:$E$678,A4,銷售訂單!$C$3:$C$678,1）

=SUMIFS（銷售訂單!$H$3:$H$678,銷售訂單!$E$3:$E$678,A4,銷售訂單!$C$3:$C$678,2）

=SUMIFS（銷售訂單!$H$3:$H$678,銷售訂單!$E$3:$E$678,A4,銷售訂單!$C$3:$C$678,3）

【第20套】

=SUMIFS（表1［銷售額小計］，表1［日期］<>=2023-1-1”,表1［日期］,"<=2023-12-31"）

=SUMIFS（表1［銷售額小計］,表1［圖書名稱］,訂單明細！D7,表日期］,”>=2023-1-1”,表1［日

期］,"〈=2023-12-31"）

=SUM1FS（表”銷售額小計］,袤1［書店名稱］,W單明細！C14,表1［日期］,”>=2023-7-1”,表1［日

期］,”<=2023-9-30”）

:SUMIFS（表1［銷售額小計］,表1［書店名稱］,訂單明細！C14,表1［日期］,〃>=2023-1-1〃，表1［日

?］,*<=2023-12-31*）/12

=SUMIFS（表1［銷售額小計］,表1［書店名稱］,訂單明細！C14,表1［日期］,”>=2023-1-1”,表1［日

期］,“<=2023-12-31"）/SUMIFS（表1［銷售額小計］，表1［日期］,"）=2023-1-1”,表1［日期］,“<=2023-12-31"）

-TEXT函數

符數值轉換為按指定數字格式表達的文本,,

函數使用方法

TEXT（value,format_text）

Value為數值、計算成果為數字值的公式，或對包括數字值口勺單元格的引用。

Formatjext為"單兀格格式”對詁框中“數字"選項卡上"分類"框口的文本形式的數字格式。

例如：

【第8套】

="法律"&TEXT(MID(B3,3,2),“[DBNuml]")&”班〃

“LDBNuml」"：數字格式轉換

【第10套】

=--TEXT(MID(C2,7,8),*0-00-00*)在二代身份證號碼中提取出生年月；

例:TEXT("19900502"；0-00-00")

將“19900502"設轉為"0-00-00"格式="1990-05-02"

DATEDIF函數

重要用于計算兩日期相差年月日數，運用該函數可計算相差的天數、月數和年數。

DATEDIF(start_date,end_date,unit)

Start_date為時間段內口勺起始日期。End_date為時間段內的結束日期。

Unit為所需信息的返回類型。“丫”時間段中的整年數。“M”時間段中的整月數。“D"時間段中的

天數。實例1：計算出生日期為1973-4-1人口勺年齡；

公式：=DATEDIF("1973-4-1"JODAYO/'Y")成果：33簡要闡明當單位代碼為"丫"時，計算成果是兩

個日期間隔的年數.

【第10套】

=DATEDIF(--TEXT(MID(C2,7,8)/0-00-00"),TODAY(),V)

【第10套】

=DATED1F(F2,H2,"YD")*24+(I2-G2)

MID函數

Mid是一種字符串函數，作用是從一種字符串中截取出指定數量的字符。

函數使用方法

MID(text,start_num,num_chars)

Text:字符串體現式，從中返回字符。

start_num:text中被提取日勺字符部分的開始位置。

num_chars:要返回口勺字符數。

例：M=4100

A1=Mid(M,1,1)A1=4

A2=Mld(M,2,2)A2=10

例如：

【第2套】MID(A2,3,2)【第8套】MID(B3,3,2)【第10套】MID(C2,17,1)

MOD函數

是一種求余函數，即是兩個數值體現式作除法運算后的余數。

函數使用方法

MOD(number,divisor)

Number為被除數。

Divisor為除數。假如divisor為零，函數MOD返回值為本來number

例：MOD(-3,2)等于1(與背面的數符號相似)驗證mod(3,-2)；MOD(3,-2)等于-1(與背面日勺數符號相似);

mod(3,0)則出錯#DIV/0!

例如：

【第10套】MOD(MID(C2,17,1),2)=1

RANK函數

rank函數是排名函數。最常用U勺是求某一種數值在某一區域內的排名。

函數使用方法

rank函數語法形式：rank(number,ref,[order])

number為需規定排名I向那個數值或者單元格名稱(單元格內必須為數字)

ref為排名的參照數值區域

orderH勺為0和1,默認不用輸入，得到的就是從大到小的排名，若是想求倒數第幾,order的值請使用1。

【第3套】=RANK(D2,$D$2:$D$21,0)

【第8套】=RANK(M3,M$3:M$102,0)

【第10套】="第"&RANK(F2,$F$2:$F$45)&〃名”

LOOKUP函數

返回向量或數組中的數值。函數LOOKUP有兩種語法形式:向量和數組。函數LOOKUP的向量形式是

在單行區域或單列區域(向量)中查找數值，然后返回第二個單行區域或單列區域中相似位置的I數值;函數

LOOKUP的數組形式在數組的第一行或第一列查找指定的數值，然后返回數組的最終一行或最終一列中

相似位置的數值。

函數LOOKUP有兩種語法形式:向量和數組。

使用措施

(1)向量形式：公式為=LOOKUP(lookup_value,lookup_vector,result_vector)

Iookup_value：函數LOOKUP在第一種向量中所要查找日勺數值，它可認為數字、文本、邏輯值或包拈數值

H勺名稱或引用；

lookup_vector:只包括一行或一列H勺區域lookup_vector的數值可認為文本、數字或邏輯值;

result_vector:只包括一行或一列口勺區域其大小必須與lookup_vector相似。

(2)數組形式：公式為=LOOKUP(lookup_value,array)

Array:包括文本、數字或邏輯值的單元格區域或數組它的值，月于與lookup_value進行比較。例如:

LOOKUP(5.2,{4.2,5,7,9,10})=5<,

注意：lookup_vectorH勺數值必須按升序排列，否則函數LOOKUP不能返I口I對於刪成果。文本不辨別大小寫。

假如函數LOOKUP找不至lookup一value,貝ij查找lookup_vector中不不小于或等于lookup_value的最大數

值,假如lookup_value不不小于lookup_vector中的I最小值，函數LOOKUP返回錯誤值#N/A。

【第2套】

=LOOKUP(MID(A2,3,2),{"01","02","03"},{"1班","2班"，"3班"})

ROUND函數

EXCEL中的基本函數，作用按指定的位數進對數值進行四舍五入。

函數使用方法

ROUND(number,numdigits)

number,要四舍五入的數字。

num_digits,位數，

按此位數對

number參數進行將2.15四舍五入到一種小數位2.2

四舍五入。

=ROUND(2.15,1)

=ROUND(2.149,1)將2.149四舍五入到一種小數位2.1

=ROUND(-1.475,2)將-1475四舍五入到兩個小數位-1.48

將四舍五入到小數點左側一位

=ROUND(21.5Z-1)21.520

【第7套】

=R0UND(IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/l00-555,

TF(K3<=35000,K3*25%-1005,TF(K3<=5500,K3*30%-2755,TF(K3<=80000,K3*35%-5505,TF(K3>

80000,K3*45%-13505))))))),2),

WEEKDAY函數

返回某日期的I星期數。在默認狀況下，它的值為1(星期天)到7(星期六)之間的一種整數。

WEEKDAY(serial_number,return_type)

serial_number是要返回日期數的日期，它有多種輸入方式：帶引號口勺文本串(如2023/02/26),序列號(如

35825表達1998年1月30日)或其他公式或函數W、J成果(如DATEVALUE(202M30))。

return_type為確定返回值類型『、J數字，

數字1或省略，則1至7代表星期天到星期六，

數字2則1至7代表星期一到星期天，

數字3則。至6代表星期一到星期天。

實例返回星期二)

=WEEKDAY(2023/8/28f2)2(

=WEEKDAY(202y)223,3)返回6(星期口)。

【第5套】=IF(WEEKDAY(A3,2)>5,"是〃，"否")

MONTH函數

Month函數指返回一種Variant(Integer),其值為1到12之間的整數，表達一年中的某月。

函數使用方法

Month(date)

必要的date參數，可以是任何可以表達日期的Variant,數值體現式、字符串體現式或它們的組合。假如

date包括Null,則返回NulL

【第9套】

=M0NTH($B$3:$B$678)

HOUR函數

Hour(time)

必要的time參數，可以是任何可以表達時刻的Variant,數值體現式、字符串體現式或它們的組合。假如

time包括Null,則返回NulL

返回一種Variant(Integer),其值為0到23之間的整數，表達一天之中的某一鐘點。

【第19套】

=H0UR(J2)

MINUTE函數

用途：返回時間值中日勺分鐘，即介于0到59之間日勺一種整數。

語法：MINUTE(serial_numbe「)參數：Serial_number是一種時間值，其中包括著要杳找時分鐘數。時間

有多種輸入方式:帶引號曰勺文本串(如"6:45PM")、十進制數(如0.78125表達6:45PM)或其他公式或函數的

成果(如TIMEVaLUEC'6:45PM"))。

實例：公式“=MINUTE("15:30:00")”返回30,=MINUTE(0.06j返回26

=MINUTE(TIMEVaLUE("9:45PM"))返回45。

【第19套】

MINUTE(J2)

LEFT函數

得到的串左部指定個數的字符。

LEFT(string,n)

string指定要提取子串口勺字符串。

n指定子串長度返回值String。

例假如人1=安徽省蚌埠市固鎮縣楊廟鄉,則公式“=LEFT(A1,FIND("省",A1))”返回安徽省。

=Left("HelloWorld",7)'返回"HelloW"。

【第5套】

二LEFT(C3,3),表達取目前文字左側B勺前三個字符

SUBTOTAL函數

語法：SUBTOTAL(function_num,ref1,ref2,...)

Function_num為1至U11(包括隱藏值)或101至U111(忽視隱藏值)之間的數字，指定使用何種函數在

列表中進行分類匯總計算。

ref1……refn參數為要而其進行分類匯總計算的第1至29個命名區域或引用。必須是對單元格區域的引用。

Function_num(包括隱藏值)為1到11之間的自然數，用來指定分類匯總計算使用II勺函數

【第9套】

二SUBTOTAL(109,B4:B11)

INT函數

將數值向下取整為最靠近的整數,

函數使用方法

INT(number)

Number需要進行向下舍入取整的實數。

所謂“向下舍入”就是當計算時，假如計算成果不為整數時，取不不小于該計算成果的整數

相反口勺，“向上舍入”就是計算成果不為整數時，取不小于該計算成果的整數

【第16套】

=INT((TODAY0-13)/365)

【第18套】

="第〃&INT(1+(MONTH(A3)-l)/3)也〃季度〃

TRUNC函數

TRUNC?數返回以指定兀索格式截去?部分的IF1期值。

TRUNC(number,[num_digits])

Number必需。需要截尾取整的數字。

Num_digits可選。用于指定取整精度的J數字。

Num_digitsU勺默認值為0(零)。

闡明：TRUNC和INT類似，都返回整數。TRUNC直接清除數字口勺小數部分，而INT則是根據給定數

H勺小數部分的值，將其向小方向到最靠近日勺整數。INT和TRUNC在處理負數時有所不一樣:

TRUNC(-4.3)返回-4,而INT(43)返回-5,由于-5是較小的數。

說胡(絡娛)

=TRUNC(8.9)8.9的50收詡(8)

aTRUNC(-8.9)?8.9的5?海分卜8)

-TRUNC(PIO)pi&^gK曲(3)

【第19套】

=E2*(TRUNC((HOUR(J2)*6O+MINUT