计算机二级教程
MS Office高级应用
部分EXCEL 函数教程
1、VLOOKUP函数
语法规则
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
参数
简单说明
输入数据类型
lookup_value
要查找的值
数值、引用或文本字符串
table_array
要查找的区域
数据
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
区域
col_index_num
返回数据在查找区域的第几列数
正整数
range_lookup
模糊匹配
TRUE(或不填)/FALSE
参数说明
Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。
Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。
col_index_num为table_array中查找数据的数据列序号。col_index_num为 1 时,返回table_array第一列的数值,col_index_num为 2 时,返回table_array第二列的数值,以此类推。如果col_index_num小于1,函数 VLOOKUP 返回错误值#VALUE!;如果col_index_num大于table_array的列数,函数 VLOOKUP 返回错误值#REF!。
Range_lookup为一逻辑值,指明函数 VLOOKUP 查找时是精确匹配,还是近似匹配。如果为false或0 ,则返回精确匹配,如果找不到,则返回错误值 #N/A。如果range_lookup为TRUE或1,函数 VLOOKUP 将查找近似匹配值,也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值。如果range_lookup省略,则默认为近似匹配。
例如:(新思路第8套)
【第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)
2、SUMPRODUCT函数
说明: 数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
含义:SUM:【数】求和;PRODUCT:【数】(乘)积20 is the product of 5 and 4.二十是五与四的乘积。 SUMPRODUCT:组合的汉语意思是:乘积之和。在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
语法:SUMPRODUCT(array1,array2,array3, ...) Array1,array2,array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和
Excel 函数
实例:
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)
结果:两个数组的所有元素对应相乘,然后把乘积相加
即3*2+4*7+8*6+6*7+1*5+9*3。
说明:
1、SUMPRODUCT函数不支持“*”和“?”通配符。
SUMPRODUCT函数不能象SUMIF、COUNTIF等函数一样使用“*”和“?”等通配符,要实现此功能可以用变通的
方法
快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载
,如使用LEFT、RIGHT、ISNUMBER(FIND())或ISNUMBER(SEARCH())等函数来实现通配符的功能。
2、SUMPRODUCT函数多条件求和时使用“,”和“*”的区别:
当拟求和的区域中无文本时两者无区别,当有文本时,使用“*”时会出错,返回错误值 #VALUE!,而使用“,”时SUMPRODUCT函数会将非数值型的数组元素作为 0 处理,故不会报错。
应用实例:
一、基本功能:函数SUMPRODUCT的功能返回相应的区域或数组乘积
二、用于多条件计数用数学函数SUMPRODUCT计算符合2个及以上条件的数据个数注意:TRUE*1=1,FALSE*1=1*FALSE=0,TRUE*0=0*TRUE=0 。数组中用分号分隔,表示数组是一列数组,分号相当于换行。两个数组相乘是同一行的对应两个数相乘。
三、用于多条件求和对于计算符合某一个条件的数据求和,可以用SUMIF函数来解决。如果要计算符合2个以上条件的数据求和,用SUMIF函数就不能够完成了。这就可以用函数
SUMPRODUCT。用函数SUMPRODUCT计算符合多条件的数据和,其基本格式是: SUMPRODUCT(条件1*条件2*……,求和数据区域)考试题中,求和公式在原来的计数公式中,在相同判断条件下,增加了一个求和的数据区域。也就是说,用函数SUMPRODUCT求和,函数需要的参数一个是进行判断的条件,另一个是用来求和的数据区域。 *1的解释umproduct函数,逗号分割的各个参数必须为数字型数据,如果是判断的结果逻辑值,就要乘1转换为数字。如果不用逗号,直接用*号连接,就相当于乘法运算,就不必添加*1。
例如:
【第1套】
=SUMPRODUCT(1*(订单明细表!E3:E262="《MS Office高级应用》"),订单明细表!H3:H262)
=SUMPRODUCT(1*(订单明细表!C350:C461="隆华书店"),订单明细表!H350:H461) =SUMPRODUCT(1*(订单明细表!C263:C636="隆华书店"),订单明细表!H263:H636)/12
3、条件求和函数SUMIF
SUMIF(range,criteria,[sum_range])
range,必需。用于条件计算的单元格区域。
criteria,必需。用于确定对哪些单元格求和的条件,其形式可以为数字、表达式、单元格引用、文本或函数。例如,条件可以表示为32、">32"、B5、32、"32"、苹果"或TODAY()。
任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号(")括起来。如果条件为数字,则无需使用双引号。
sum_range,可选。要求和的实际单元格(如果要对未在range参数中指定的单元格求和)。如果省略sum_range参数,Excel会对在range参数中指定的单元格(即应用条件的单元格)求和。
a) 使用SUMIF函数可以对区域中符合指定条件的值求和。例如,假设在含有数字的某一列中,需要对大于 5的数值求和。请使用以下公式:=SUMIF(B2:B25,">5");
b) 如果需要,可以将条件应用于某个单元格区域,但却对另一个单元格区域中的对应值求和。
c) 例如,使用公式=SUMIF(B2:B5,"俊元",C2:C5)时,该函数仅对单元格区域C2:C5中与单元格区域B2:B5 中等于“俊元”的单元格对应的单元格中的值求和。
4、多条件求和函数SUMIFS
? SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)
sum_range,必需。对一个或多个单元格求和。
criteria_range1,必需。在其中计算关联条件的第一个区域。
criteria1,必需。条件的形式为数字、表达式、单元格引用或文本,可用来定义将对Criteria_range1参数中的哪些单元格求和。例如,条件可以表示为32、">32"、B4、"苹果"或"32"。
criteria_range2,criteria2,…,可选。附加的区域及其关联条件。最多允许127个区域/条件对。
? 对区域中满足多个条件的单元格求和。例如,如果需要对区域A1:A20中的单元格的数值求和,且需符合以下条件:B1:B20中的相应数值大于零(0)且C1:C20中的相应数值小于10,则可以使用公式:=SUMIFS(A1:A20,B1:B20,">0",C1:C20,"<10")
SUMIFS和SUMIF函数的参数顺序有所不同。具体而言,sum_range参数在SUMIFS中是第一个参数,而在SUMIF中,却是第三个参数。
5、向下取整函数INT
INT(number),Number必需。将数值向下取整为最接近的整数,不进行四舍五入。
6、四舍五入函数ROUND
ROUND(number,num_digits)
● number,必需。要四舍五入的数字。
● num_digits,必需。要进行四舍五入运算的小数位数。
? 如果num_digits大于 0,则将数字四舍五入到指定的小数位数。
? 如果num_digits等于 0,则将数字四舍五入到最接近的整数。
? 如果num_digits小于 0,则将数字四舍五入到小数点左边的相应位数。如,ROUND(32.27,-1)=30
● 例:如果单元格A1包含23.7825,而且您想要将此数值舍入到两个小数位数,可以使用以下公式:ROUND(A1,2)=23.78。
? 若要始终进行向上舍入(远离 0),请使用 ROUNDUP 函数。
? 若要始终进行向下舍入(朝向 0),请使用 ROUNDDOWN 函数。
Roundup:ROUNDUP(number,num_digits),朝着远离0(零)的方向将数字进行向上舍入。
Rounddown:ROUNDDOWN(number,num_digits),朝着零的方向将数字进行向下舍入。
=ROUND(2.151)
将 2.15 四舍五入到一个小数位
2.2
=ROUND(2.1491)
将 2.149 四舍五入到一个小数位
2.1
=ROUND(-1.4752)
将 -1.475 四舍五入到两个小数位
-1.48
=ROUND(21.5-1)
将 21.5 四舍五入到小数点左侧一位
20
7、取整函数TRUNC
? TRUNC(number,[num_digits]),将数字的小数部分截去,返回整数。注意,不进行四舍五入!
继续阅读