全國計算機等級考試 二級MS Office高級應用Excel函數(shù)總結(jié)材料
word
VLOOKUP函數(shù)
語法規(guī)如此
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
參數(shù)
簡單說明
輸入數(shù)據(jù)類型
lookup_value
要查找的值
數(shù)值、引用或文本字符串
table_array
要查找的區(qū)域
數(shù)據(jù)表區(qū)域
col_index_num
返回數(shù)據(jù)在查找區(qū)域的第幾列數(shù)
正整數(shù)
range_lookup
模糊匹配
TRUE〔或不填〕 /FALSE
參數(shù)說明
Lookup_value為需要在數(shù)據(jù)表第一列中進展查找的數(shù)值。Lookup_value 可以為數(shù)值、引用或文本字符串。
Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。使用對區(qū)域或區(qū)域名稱的引用。
col_index_num為table_array 中查找數(shù)據(jù)的數(shù)據(jù)列序號。col_index_num 為 1 時,返回 table_array 第一列的數(shù)值,col_index_num 為 2 時,返回 table_array 第二列的數(shù)值,以此類推。如果 col_index_num 小于1,函數(shù) VLOOKUP 返回錯誤值#VALUE!;如果 col_index_num 大于 table_array 的列數(shù),函數(shù) VLOOKUP 返回錯誤值#REF!。
Range_lookup為一邏輯值,指明函數(shù) VLOOKUP 查找時是準確匹配,還是近似匹配。如果為false或0 ,如此返回準確匹配,如果找不到,如此返回錯誤值 #N/A。如果 range_lookup 為TRUE或1,函數(shù) VLOOKUP 將查找近似匹配值,也就是說,如果找不到準確匹配值,如此返回小于 lookup_value 的最大數(shù)值。如果range_lookup 省略,如此默認為近似匹配。
例如:
【第1套】
=VLOOKUP(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套】
=VLOOKUP(A2,初三學生檔案!$A$2:$B$56,2,0)
SUMPRODUCT函數(shù)
說明:數(shù)組參數(shù)必須具有一樣的維數(shù),否如此,函數(shù) SUMPRODUCT 將返回錯誤值 #VALUE!。 函數(shù) SUMPRODUCT 將非數(shù)值型的數(shù)組元素作為 0 處理。
含義:SUM:【數(shù)】求和;PRODUCT:【數(shù)】(乘)積20 is the product of 5 and 4.二十是五與四的乘積。 SUMPRODUCT:組合的漢語意思是:乘積之和。在給定的幾組數(shù)組中,將數(shù)組間對應的元素相乘,并返回乘積之和。
語法:SUMPRODUCT〔array1,array2,array3, ...〕
Array1,array2,array3, ... 為 2 到 30 個數(shù)組,其相應元素需要進展相乘并求和
實例 :
B
C
D
E
1
Array1
Array1
Array2
Array2
2
3
4
2
7
3
8
6
6
7
4
1
9
5
3
公式:=SUMPRODUCT(B2:C4*D2:E4)
結(jié)果:兩個數(shù)組的所有元素對應相乘,然后把乘積相加,即3*2+4*7+8*6+6*7+1*5+9*3。
說明
1、SUMPRODUCT函數(shù)不支持“*〞和“?〞通配符。
SUMPRODUCT函數(shù)不能象SUMIF、COUNTIF等函數(shù)一樣使用“*〞和“?〞等通配符,要實現(xiàn)此功能可以用變通的方法,如使用LEFT、RIGHT、ISNUMBER(FIND())或ISNUMBER(SEARCH())等函數(shù)來實現(xiàn)通配符的功能。
2、SUMPRODUCT函數(shù)多條件求和時使用“,〞和“*〞的區(qū)別:當擬求和的區(qū)域中無文本時兩者無區(qū)別,當有文本時,使用“*〞時會出錯,返回錯誤值 #VALUE!,而使用“,〞時SUMPRODUCT函數(shù)會將非數(shù)值型的數(shù)組元素作為 0 處理,故不會報錯。
應用實例
一、根本功能:函數(shù)SUMPRODUCT的功能返回相應的區(qū)域或數(shù)組乘積
二、用于多條件計數(shù)用數(shù)學函數(shù)SUMPRODUCT計算符合2個與以上條件的數(shù)據(jù)個數(shù)
注意:TRUE*1=1,F(xiàn)ALSE*1=1*FALSE=0,TRUE*0=0*TRUE=0 。數(shù)組中用分號分隔,表示數(shù)組是一列數(shù)組,分號相當于換行。兩個數(shù)組相乘是同一行的對應兩個數(shù)相乘。
三、用于多條件求和對于計算符合某一個條件的數(shù)據(jù)求和,可以用SUMIF函數(shù)來解決。如果要計算符合2個以上條件的數(shù)據(jù)求和,用SUMIF函數(shù)就不能夠完成了。這就可以用函數(shù)SUMPRODUCT。
用函數(shù)SUMPRODUCT計算符合多條件的數(shù)據(jù)和,其根本格式是:
SUMPRODUCT〔條件1*條件2*……,求和數(shù)據(jù)區(qū)域〕
考試題中,求和公式在原來的計數(shù)公式中,在一樣判斷條件下,增加了一個求和的數(shù)據(jù)區(qū)域。也就是說,用函數(shù)SUMPRODUCT求和,函數(shù)需要的參數(shù)一個是進展判斷的條件,另一個是用來求和的數(shù)據(jù)區(qū)域。
*1的解釋
umproduct函數(shù),逗號分割的各個參數(shù)必須為數(shù)字型數(shù)據(jù),如果是判斷的結(jié)果邏輯值,就要乘1轉(zhuǎn)換為數(shù)字。如果不用逗號,直接用*號連接,就相當于乘法運算,就不必添加*1。
例如:
【第1套】
=SUMPRODUCT(1*(訂單明細表!E3:E262="《MS Office高級應用》"),訂單明細表!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="管理"),I3:I17)
=SUMPRODUCT(1*(D3:D17="管理"),M3:M17)
IF函數(shù)
IF函數(shù),根據(jù)指定的條件來判斷其"真"(TRUE)、"假"(FALSE);根據(jù)邏輯計算的真假值,從而返回相應的內(nèi)容。用途:執(zhí)行真假值判斷
函數(shù)用法 1.IF函數(shù)的語法結(jié)構(gòu) IF(logical_test,value_if_true,value_if_false)
即:IF函數(shù)的語法結(jié)構(gòu):IF(條件,結(jié)果1,結(jié)果2)。 2.IF函數(shù)的功能 對滿足條件的數(shù)據(jù)進展處理,條件滿足如此輸出結(jié)果1,不滿足如此輸出結(jié)果2??梢允÷越Y(jié)果1或結(jié)果2,但不能同時省略。 3.條件表達式 把兩個表達式用關(guān)系運算符(主要有=,<>,>,<,>=,<=等6個關(guān)系運算符)連接起來就構(gòu)成條件表達式?! ?.IF函數(shù)嵌套的執(zhí)行過程 如果按等級來判斷某個變量,IF函數(shù)的格式如下: IF(E2>=85,"優(yōu)",IF(E2>=75,"良",IF(E2>=60,"與格","不與格"))) 函數(shù)從左向右執(zhí)行。首先計算E2>=85,如果該表達式成立,如此顯示“優(yōu)〞,如果不成立就繼續(xù)計算E2>=75,如果該表達式成立,如此顯示“良〞,否如此繼續(xù)計算E2>=60,如果該表達式成立,如此顯示“與格〞,否如此顯示“不與格〞。
例如:
【第5套】
=IF(WEEKDAY(A3,2)>5,"是","否")
【第7套】
=ROUND(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(K3>80000,K3*45%-13505))))))),2)
【第10套】
=IF(MOD(MID(C2,17,1),2)=1,"男","女")
=IF(F2>=102,"優(yōu)秀",IF(F2>=84,"良好",IF(F2>=72,"與格",IF(F2>72,"與格","不與格"))))
=IF(F2>=90,"優(yōu)秀",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函數(shù)
根據(jù)多個指定條件對假如干單元格求和。
函數(shù)用法SUMIFS(sum_range, criteria_range1,criteria1, [criteria_range2, criteria2], ...)
1〕sum_range 是需要求和的實際單元格。包括數(shù)字或包含數(shù)字的名稱、區(qū)域或單元格引用。忽略空白值和文本值。
2) criteria_range1為計算關(guān)聯(lián)條件的第一個區(qū)域。
3) criteria1為條件1,條件的形式為數(shù)字、表達式、單元格引用或者文本,可用來定義將對criteria_range1參數(shù)中的哪些單元格求和。例如,條件可以表示為32、“>32〞、B4、"蘋果"、或"32"。
4〕criteria_range2為用于條件2判斷的單元格區(qū)域。
5) criteria2為條件2,條件的形式為數(shù)字、表達式、單元格引用或者文本,可用來定義將對criteria_range2參數(shù)中的哪些單元格求和。
4〕和5〕最多允許127個區(qū)域/條件對,即參數(shù)總數(shù)不超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[日期],">=2013-1-1",表1[日期],"<=2013-12-31")
=SUMIFS(表1[銷售額小計],表1[圖書名稱],訂單明細!D7,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")
=SUMIFS(表1[銷售額小計],表1[書店名稱],訂單明細!C14,表1[日期],">=2013-7-1",表1[日期],"<=2013-9-30")
=SUMIFS(表1[銷售額小計],表1[書店名稱],訂單明細!C14,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")/12
=SUMIFS(表1[銷售額小計],表1[書店名稱],訂單明細!C14,表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")/SUMIFS(表1[銷售額小計],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")
--TEXT函數(shù)
將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本。
函數(shù)用法TEXT(value,format_text)
Value 為數(shù)值、計算結(jié)果為數(shù)字值的公式,或?qū)Π瑪?shù)字值的單元格的引用。
Format_text 為"單元格格式"對話框中"數(shù)字"選項卡上"分類"框中的文本形式的數(shù)字格式。
例如:
【第8套】
="法律"&TEXT(MID(B3,3,2),"[DBNum1]")&"班"
"[DBNum1]":數(shù)字格式轉(zhuǎn)換
【第10套】
=--TEXT(MID(C2,7,8),"0-00-00")在二代某某中提取出生年月;
例:TEXT("19900502" ," 0-00-00"〕
將"19900502"設轉(zhuǎn)為"0-00-00"格式-->="1990-05-02"
DATEDIF函數(shù)
主要用于計算兩日期相差年月日數(shù),利用該函數(shù)可計算相差的天數(shù)、月數(shù)和年數(shù)。
DATEDIF(start_date,end_date,unit)
Start_date 為時間段內(nèi)的起始日期。End_date 為時間段內(nèi)的完畢日期。
Unit 為所需信息的返回類型。〞Y〞 時間段中的整年數(shù)。〞M〞時間段中的整月數(shù)。〞D〞 時間段中的天數(shù)。實例1: 計算出生日期為1973-4-1人的年齡;
公式: =DATEDIF("1973-4-1",TODAY(),"Y")結(jié)果: 33簡要說明 當單位代碼為"Y"時,計算結(jié)果是兩個日期間隔的年數(shù).
【第10套】
=DATEDIF(--TEXT(MID(C2,7,8),"0-00-00"),TODAY(),"y")
【第10套】
=DATEDIF(F2,H2,"YD")*24+(I2-G2)
MID函數(shù)
Mid是一個字符串函數(shù),作用是從一個字符串中截取出指定數(shù)量的字符。
函數(shù)用法
MID(text,start_num,num_chars)
Text: 字符串表達式,從中返回字符。
start_num:text 中被提取的字符局部的開始位置。
num_chars: 要返回的字符數(shù)。
例:M=4100
A1=Mid(M,1,1) A1=4
A2=Mid(M,2,2) A2=10
例如:
【第2套】MID(A2,3,2)【第8套】MID(B3,3,2)【第10套】MID(C2,17,1)
MOD函數(shù)
是一個求余函數(shù),即是兩個數(shù)值表達式作除法運算后的余數(shù)。
函數(shù)用法MOD(number,divisor)
Number 為被除數(shù)。
Divisor 為除數(shù)。如果 divisor 為零,函數(shù) MOD 返回值 為原來number
例:MOD(-3, 2) 等于1(與后面的數(shù)符號一樣)驗證 mod(3,-2);MOD(3, -2) 等于-1(與后面的數(shù)符號一樣);mod〔3,0〕如此出錯#DIV/0!
例如:
【第10套】MOD(MID(C2,17,1),2)=1
RANK函數(shù)
rank函數(shù)是排名函數(shù)。最常用的是求某一個數(shù)值在某一區(qū)域內(nèi)的排名。
函數(shù)用法rank函數(shù)語法形式: rank(number,ref,[order])
number 為需要求排名的那個數(shù)值或者單元格名稱(單元格內(nèi)必須為數(shù)字)
ref 為排名的參照數(shù)值區(qū)域
order的為0和1,默認不用輸入,得到的就是從大到小的排名,假如是想求倒數(shù)第幾,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函數(shù)
返回向量或數(shù)組中的數(shù)值。函數(shù) LOOKUP 有兩種語法形式:向量和數(shù)組。函數(shù) LOOKUP 的向量形式是在單行區(qū)域或單列區(qū)域(向量)中查找數(shù)值,然后返回第二個單行區(qū)域或單列區(qū)域中一樣位置的數(shù)值;函數(shù) LOOKUP 的數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值,然后返回數(shù)組的最后一行或最后一列中一樣位置的數(shù)值。
函數(shù) LOOKUP 有兩種語法形式:向量和數(shù)組。
使用方法
〔1〕向量形式:公式為 = LOOKUP(lookup_value,lookup_vector,result_vector)
lookup_value:函數(shù)LOOKUP在第一個向量中所要查找的數(shù)值,它可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用;
lookup_vector:只包含一行或一列的區(qū)域lookup_vector 的數(shù)值可以為文本、數(shù)字或邏輯值;
result_vector:只包含一行或一列的區(qū)域其大小必須與 lookup_vector 一樣。
〔2〕數(shù)組形式:公式為= LOOKUP(lookup_value,array)
Array:包含文本、數(shù)字或邏輯值的單元格區(qū)域或數(shù)組它的值,用于與 lookup_value 進展比擬。例如:LOOKUP(5.2,{4.2,5,7,9,10})=5。
注意:lookup_vector的數(shù)值必須按升序排列,否如此函數(shù)LOOKUP不能返回正確的結(jié)果。文本不區(qū)分大小寫。如果函數(shù)LOOKUP找不到lookup_value,如此查找lookup_vector中小于或等于lookup_value的最大數(shù)值。如果lookup_value小于lookup_vector中的最小值,函數(shù)LOOKUP返回錯誤值#N/A。
【第2套】
=LOOKUP(MID(A2,3,2),{"01","02","03"},{"1班","2班","3班"})
ROUND函數(shù)
EXCEL中的根本函數(shù),作用按指定的位數(shù)進對數(shù)值進展四舍五入。
函數(shù)用法ROUND(number,num_digits)
number ,要四舍五入的數(shù)字。
num_digits ,位數(shù),按此位數(shù)對 number 參數(shù)進展四舍五入。
=ROUND(2.15, 1)
將 2.15 四舍五入到一個小數(shù)位
=ROUND(2.149, 1)
將 2.149 四舍五入到一個小數(shù)位
=ROUND(-1.475, 2)
將 -1.475 四舍五入到兩個小數(shù)位
=ROUND(21.5, -1)
將 21.5 四舍五入到小數(shù)點左側(cè)一位
20
【第7套】
=ROUND(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(K3>80000,K3*45%-13505))))))),2),
WEEKDAY函數(shù)
返回某日期的星期數(shù)。在默認情況下,它的值為1(星期天)到7(星期六)之間的一個整數(shù)。
WEEKDAY(serial_number,return_type)
serial_number 是要返回日期數(shù)的日期,它有多種輸入方式:帶引號的文本串(如2001/02/26)、序列號(如35825 表示1998 年1 月30 日) 或其他公式或函數(shù)的結(jié)果(如DATEVALUE(2000/1/30))。
return_type為確定返回值類型的數(shù)字,
數(shù)字1 或省略,如此1 至7 代表星期天到星期六,
數(shù)字2 如此1 至7 代表星期一到星期天,
數(shù)字3如此0至6代表星期一到星期天。
實例=WEEKDAY(2001/8/28,2) 返回2(星期二)
=WEEKDAY(2003/02/23,3) 返回6(星期日)。
【第5套】=IF(WEEKDAY(A3,2)>5,"是","否")
MONTH函數(shù)
Month函數(shù)指返回一個 Variant (Integer),其值為 1 到 12 之間的整數(shù),表示一年中的某月。
函數(shù)用法Month(date)
必要的 date 參數(shù),可以是任何能夠表示日期的 Variant、數(shù)值表達式、字符串表達式或它們的組合。如果 date 包含 Null,如此返回 Null。
【第9套】
=MONTH($B$3:$B$678)
HOUR函數(shù)
Hour(time)
必要的 time 參數(shù),可以是任何能夠表示時刻的 Variant、數(shù)值表達式、字符串表達式或它們的組合。如果 time 包含 Null,如此返回 Null。
返回一個 Variant (Integer),其值為 0 到 23 之間的整數(shù),表示一天之中的某一鐘點。
【第19套】
=HOUR(J2)
MINUTE函數(shù)
用途:返回時間值中的分鐘,即介于0到59之間的一個整數(shù)。
語法:MINUTE(serial_number)參數(shù):Serial_number是一個時間值,其中包含著要查找的分鐘數(shù)。時間有多種輸入方式:帶引號的文本串(如"6:45 PM")、十進制數(shù)(如 0.78125表示6:45 PM)或其他公式或函數(shù)的結(jié)果(如TIMEVaLUE("6:45 PM"))。
實例:公式“=MINUTE("15:30:00")〞返回30,=MINUTE(0.06)返回26
=MINUTE(TIMEVaLUE("9:45 PM"))返回45。
【第19套】
MINUTE(J2)
LEFT函數(shù)
得到字符串左部指定個數(shù)的字符。
LEFT( string, n )
string 指定要提取子串的字符串。
n 指定子串長度返回值String。
例:如果A1=某某省某某市固鎮(zhèn)縣楊廟鄉(xiāng),如此公式“=LEFT(A1,FIND("省",A1))〞返回某某省。
= Left("Hello World", 7) '返回 "Hello W"。
【第5套】
=LEFT(C3,3),表示取當前文字左側(cè)的前三個字符
SUBTOTAL函數(shù)
語法:SUBTOTAL(function_num,ref1,ref2, ...)
Function_num 為 1 到 11(包含隱藏值)或 101 到 111(忽略隱藏值)之間的數(shù)字,指定使用何種函數(shù)在列表中進展分類匯總計算。
ref1……refn參數(shù)為要對其進展分類匯總計算的第1至29個命名區(qū)域或引用。必須是對單元格區(qū)域的引用。
Function_num (包含隱藏值)為1到11之間的自然數(shù),用來指定分類匯總計算使用的函數(shù)
【第9套】
=SUBTOTAL(109,B4:B11)
INT函數(shù)
將數(shù)值向下取整為最接近的整數(shù)。
函數(shù)用法INT〔number〕
Number 需要進展向下舍入取整的實數(shù)。
所謂“向下舍入〞就是當計算時,如果計算結(jié)果不為整數(shù)時,取小于該計算結(jié)果的整數(shù)
相反的,“向上舍入〞就是計算結(jié)果不為整數(shù)時,取大于該計算結(jié)果的整數(shù)
【第16套】
=INT((TODAY()-I3)/365)
【第18套】
="第"&INT(1+(MONTH(A3)-1)/3)&"季度"
TRUNC函數(shù)
TRUNC函數(shù)返回以指定元素格式截去一局部的日期值。
TRUNC〔number,[num_digits]〕
Number 必需。需要截尾取整的數(shù)字。
Num_digits 可選。用于指定取整精度的數(shù)字。
Num_digits 的默認值為 0〔零〕。
說明: TRUNC 和 INT 類似,都返回整數(shù)。TRUNC 直接去除數(shù)字的小數(shù)局部,而 INT 如此是依照給定數(shù)的小數(shù)局部的值,將其向小方向到最接近的整數(shù)。INT 和 TRUNC 在處理負數(shù)時有所不同:TRUNC(-4.3) 返回 -4,而 INT(-4.3) 返回 -5,因為 -5 是較小的數(shù)。
示例
【第19套】
=E2*(TRUNC((HOUR(J2)*60+MINUTE(J2))/15)+1)
=E2*TRUNC((HOUR(J2)*60+MINUTE(J2))/15)
12 / 12