2023年二级MSOfficeExcel函数总结

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