范文一:工作中最常用的excel函数公式大全,会计
工作中最常用的excel 函数
公式大全,会计
一、数字处理
1、取绝对值
=ABS(数字)
2、取整
=INT(数字)
3、四舍五入
=ROUND(数字, 小数位数)
二、判断公式
1、把公式产生的错误值显示为空
公式:C2
=IFERROR(A2/B2,"")
说明:如果是错误值则显示为空,否则正常显示。
2、IF 多条件判断返回值
公式:C2
=IF(AND(A2<500,b2="未到期"),">500,b2="未到期"),">
说明:两个条件同时成立用AND, 任一个成立用OR 函数。
三、统计公式
1、统计两个表格重复的内容
公式:B2
=COUNTIF(Sheet15!A:A,A2)
说明:如果返回值大于0说明在另一个表中存在,0则不存在。
2、统计不重复的总人数
公式:C2
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明:用COUNTIF 统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。
四、求和公式
1、隔列求和
公式:H3
=SUMIF($A$2:$G$2,H$2,A3:G3)
或
=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
说明:如果标题行没有规则用第2个公式
2、单条件求和
公式:F2
=SUMIF(A:A,E2,C:C)
说明:SUM IF 函数的基本用法
3、单条件模糊求和
公式:详见下图
说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a 前和后有任意多个字符,即包含A 。
4、多条件模糊求和
公式:C11
=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
说明:在sumifs 中可以使用通配符*
5、多表相同位置求和
公式:b2
=SUM(Sheet1:Sheet19!B2)
说明:在表中间删除或添加表后,公式结果会自动更新。
6、按日期和产品求和
公式:F2
=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
说明:SUMPRODUCT 可以完成多条件求和
五、查找与引用公式
1、单条件查找公式
公式1:C11
=VLOOKUP(B11,B3:F7,4,FALSE)
说明:查找是VLOOKUP 最擅长的,基本用法
2、双向查找公式
公式:
=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
说明:利用MATCH 函数查找位置,用INDEX 函数取值
3、查找最后一条符合条件的记录。
公式:详见下图
说明:
0/(条件) 可以把不符合条件的变成错误值,而lookup 可以忽略错误值
4、多条件查找
公式:详见下图
说明:公式原理同上一个公式
5、指定区域最后一个非空值查找
公式; 详见下图
说明:略
6、按数字区域间取对应的值
公式:详见下图
公式说明:VLOOKUP 和LOOKUP 函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。
六、字符串处理公式
1、多单元格字符串合并
公式:c2
=PHONETIC(A2:A7)
说明:Phonetic 函数只能对字符型内容合并,数字不可以。
2、截取除后3位之外的部分
公式:
=LEFT(D1,LEN(D1)-3)
说明:LEN 计算出总长度,LEFT 从左边截总长度-3个
3、截取-前的部分
公式:B2
=Left(A1,FIND("-",A1)-1)
说明:用FIND 函数查找位置,用LEFT 截取。
4、截取字符串中任一段的公式
公式:B1
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
说明:公式是利用强插N 个空字符的方式进行截取
5、字符串查找
公式:B2
=IF(COUNT(FIND("河南",A2))=0,"否"," 是")
说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT 可以统计出数字的个数,这里可以用来判断查找
是否成功。
6、字符串查找一对多
公式:B2
=IF(COUNT(FIND({"辽宁"," 黑龙江"," 吉林
"},A2))=0,"其他"," 东北") 说明:设置FIND 第一个参数为常量数组,用COUNT 函数统计FIND 查找结果
七、日期计算公式
1、两日期相隔的年、月、天数计算
A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。计算: 相隔多少天?=datedif(A1,B1,"d") 结果:557
相隔多少月? =datedif(A1,B1,"m") 结果:18
相隔多少年? =datedif(A1,B1,"Y") 结果:1
不考虑年相隔多少月?=datedif(A1,B1,"Ym") 结果:6
不考虑年相隔多少天?=datedif(A1,B1,"YD") 结果:192
不考虑年月相隔多少天?=datedif(A1,B1,"MD") 结果:9 datedif 函数第3个参数说明:
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。
"MD" 天数的差。忽略日期中的月和年。
"YM" 月数的差。忽略日期中的日和年。
"YD" 天数的差。忽略日期中的年。
2、扣除周末天数的工作日天数
公式:C2
=NETWORKDAYS.INTL(IF(B2
说明:返回两个日期之间的所有工作日数,使用参数指示。
范文二:会计人员常用excel函数
会计人员常用excel 函数
一、MID (text ,start_num,num_chars)
text :是指包含需要提取字符的文本字符串位置 start_num:需要提取的字符串在文本中开始位置 num_chars:提取的字符串个数。 举例:从以下身份证号中提取出生年月日
1、函数格式:
MID (A2,7,8)
A2:指身份证号位于A2位置。
7:是指从身份证号中第7个位置开始提取 8:是指按顺序一共提取8个数字 结果为:
二、SUMIF (range ,criteria ,sum_range)
range :计算区域
criteria :条件(以数字、表达式或文本形式表示) sum_range:实际参与计算的区域(可省略)
举例,在以下计算区域中对编码为a1的单元格所对应的数据进行求和
函数格式:SUMIF (B3:C10,B3,C3:C10)
在区域B3:C10中把所有编码为a1的单元格的数据进行求和 B3+B5+B8 = 25+33+36 = E3 = 94
结果为
三、COUNTIF (range ,criteria ) range :要进行计算的非空单元格区域 criteria :需要进行计算时满足的条件
举例:对下列区域中大于30的数进行个数统计
函数格式:COUNTIF (B3:C10,>30) B3:C10:进行统计的区域
>30:为表达式,表示大于30的数进行个数统计
结果为:
四、VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) lookup_value:查找的目标 table_array:查找的区域
col_index_num:需要返回的值在查找区域中的列号
range_lookup:默认为TRUE (即1),即模糊查找,false (即0)为精确查找
举例:在表sheet1中查找表sheet2中a3的数据,返回的数据为模糊查找,结果放到表sheet2中。
结果为:
五、DATE(year,month ,day) 举例:DATE(2008,1,
C5)
在E5单元格中显示为2008-2-2,因为C5单元格中的数字为33,比1月份的31天多两天,所以顺延到2月份。
六、NOW( )
该函数没有参数,选择它后直接按回车即可显示,当前电脑的时间。
如果需要更新时间为最新的时间,可以按键盘上的F9。
七、DATEIF (date1,date2,y )、DATEIF (date1,date2,m )、DATEIF (date1,date2,d )
表示返回date1与date2两个日期之间的差,其中y 是指返回年的差,m 表示返回月的差值,d 表示返回日期差值。
八、CONCATENATE(Text1,Text ……)
将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。
举例:将下列表中B4与C4中的内容连接在一起,放到D4中
输入的格式为
在D4中输出的结果为
九、ROW (reference ) 求单元格或连续区域的行号
reference :表示需要求行号的单元格或连续区域
十、COLUMN (reference ) 求单元格或连续区域的列号
reference :表示需要求列号的单元格或连续区域
十一、ROUND (number ,num_digits)
number :表示需要进行四舍五入的单元格,如果单元格内容非数值型,则返回错误。
num_digits:需要保留的小数位数 举例:如ROUND (8.699,1),结果为8.7
十二、INT (number )
将数字number 进行取最接近number 的整数 举例:
对单元格D5中的数字3.2进行取整 INT (3.1)或INT(D5),结果为3 对单元格D6中的数字 -3.2 进行取整 INT (-3.2)或INT(D6),结果为 -4
十三、IF(logical_test,value_if_true,value_if_false)
判断logical_test的值,如果为真,返回value_if_true,如果为假,则返回value_if_false
logical_test:任何一个可以判断真假的数值或表达式
举例:判断B4单元格中的数是否大于5,大于5,则返回单元格B5中的6,否则返回单元格B3中的数。
函数格式:IF(B4>5,B6,
B3)
结果为:
十四、DB(cost,salvage ,life ,period ,month)
用固定余额递减法,返回指定期间内某项固定资产的折旧值。 Cost 为资产原值。
Salvage 为资产在折旧期末的价值(也称为资产残值)。 Life 为折旧期限(有时也称作资产的使用寿命)。
Period 为需要计算折旧值的期间,Period 必须使用与 life 相同的单位。
Month 为第一年的月份数,如省略,则假设为 12。
举例:现有某辆轿车,价值10万元,总的使用时间为20年,报废后的资产残值为10000元,公司使用的时间年限为15年。求在今内该车的折旧值。
结果为:2166元
即在今年内的折旧为2166元
范文三:[训练]会计人员常用excel函数
会计人员常用excel函数
一、MID(text,start_num,num_chars)
text:是指包含需要提取字符的文本字符串位置
start_num:需要提取的字符串在文本中开始位置
num_chars:提取的字符串个数。
举例:从以下身份证号中提取出生年月日
1、函数格式:
MID(A2,7,8)
A2:指身份证号位于A2位置。
7:是指从身份证号中第7个位置开始提取
8:是指按顺序一共提取8个数字
结果为:
二、SUMIF(range,criteria,sum_range)
range:计算区域
criteria:条件(以数字、表达式或文本形式表示)
sum_range:实际参与计算的区域(可省略)
举例,在以下计算区域中对编码为a1的单元格所对应的数据进行求和
函数格式:SUMIF(B3:C10,B3,C3:C10)
在区域B3:C10中把所有编码为a1的单元格的数据进行求和 B3+B5+B8 = 25+33+36 = E3 = 94
结果为
三、COUNTIF(range,criteria)
range:要进行计算的非空单元格区域
criteria:需要进行计算时满足的条件
举例:对下列区域中大于30的数进行个数统计
函数格式:COUNTIF(B3:C10,>30)
B3:C10:进行统计的区域
>30:为表达式,表示大于30的数进行个数统计
结果为:
四、VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
lookup_value:查找的目标
table_array:查找的区域
col_index_num:需要返回的值在查找区域中的列号 range_lookup:默认为TRUE(即1),即模糊查找,false(即0)为精确查找
举例:在表sheet1中查找表sheet2中a3的数据,返回的数据为模糊查找,结果放到表sheet2中。
结果为:
五、DATE(year,month,day)
举例:DATE(2008,1,C5)
在E5单元格中显示为2008-2-2,因为C5单元格中的数字为33,比1月份的31天多两天,所以顺延到2月份。
六、NOW( )
该函数没有参数,选择它后直接按回车即可显示,当前电脑的时间。
如果需要更新时间为最新的时间,可以按键盘上的F9。
七、DATEIF(date1,date2,y)、DATEIF(date1,date2,m)、DATEIF(date1,date2,d)
表示返回date1与date2两个日期之间的差,其中y是指返回年的差,m表示返回月的差值,d表示返回日期差值。
八、CONCATENATE(Text1,Text……)
将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。
举例:将下列表中B4与C4中的内容连接在一起,放到D4中
输入的格式为
在D4中输出的结果为
九、ROW(reference)
求单元格或连续区域的行号
reference:表示需要求行号的单元格或连续区域
十、COLUMN(reference)
求单元格或连续区域的列号
reference:表示需要求列号的单元格或连续区域
十一、ROUND(number,num_digits)
number:表示需要进行四舍五入的单元格,如果单元格内容非数值型,则返回错误。
num_digits:需要保留的小数位数
举例:如ROUND(8.699,1),结果为8.7
十二、INT(number)
将数字number进行取最接近number的整数
举例:
对单元格D5中的数字3.2进行取整
INT(3.1)或INT(D5),结果为3
对单元格D6中的数字 -3.2 进行取整
INT(-3.2)或INT(D6),结果为 -4
十三、IF(logical_test,value_if_true,value_if_false)
判断logical_test的值,如果为真,返回value_if_true,如果为假,则返回value_if_false
logical_test:任何一个可以判断真假的数值或表达式
举例:判断B4单元格中的数是否大于5,大于5,则返回单元格B5中的6,否则返回单元格B3中的数。
函数格式:IF(B4>5,B6,B3)
结果为:
十四、DB(cost,salvage,life,period,month) 用固定余额递减法,返回指定期间内某项固定资产的折旧值。
Cost 为资产原值。
Salvage 为资产在折旧期末的价值(也称为资产残值)。
Life 为折旧期限(有时也称作资产的使用寿命)。 Period 为需要计算折旧值的期间,Period 必须使用与 life 相同的单位。
Month 为第一年的月份数,如省略,则假设为 12。
举例:现有某辆轿车,价值10万元,总的使用时间为20年,报废后的资产残值为10000元,公司使用的时间年限为15年。求在今内该车的折旧值。
结果为:2166元
即在今年内的折旧为2166元
范文四:excel表格的各函数常用函数
excel 表格的各函数的基本操作
常用函数如下:
1、 SUM () 求和、总分函数
例:=SUM(B2:B5)
2、 A VERAGE () 求平均函数
例:=AVERAGE (B2:B5)
3、 MIN () 求最小值函数
例:=MIN(B2:B5)
4、 MAX () 求最大值函数
例:=MAX(B2:B5)
5、 COUNTIF () 求条件统计函数(
例:=COUNTIF(B7:B33, “ >=90” )
6、 COUNT () 求统计函数
例:=COUNT(B7:B33)
7、 IF () 求逻辑函数
例:=IF(G7>=90, “ 优秀” , IF (G7>=80, “ 良好” , IF (G7) >=70,“中等” , IF (AND (G7<70,g7>0) , “差生” , “没参考” ) ) ) )
8、 RANK () 求名次函数
例:=RANK(G7, $G$7:$G$33)
9、 NOW () 求电脑现在日期
例:=NOW()
10、 求“性别”函数
例:=IF(MOD(MID(B6,15,1),2)=0,
11、求“出生日期”函数
例:=CONCATENATE(MID(B6,7,2),
12、求“年龄” 、 “工龄”函数
例:=DATEDIF(E6,NOW(),
13、求“等级”函数
例 :=IF(C6=
14、 SUNIF () 条件求和函数(根据指定的条件求和)
例:=SUMIF(B7:B33,
例:=SUMIF(C7:C36,
范文五:HR常用的excel函数有哪些?
精品文档
HR常用的excel函数有哪些,
我之前实习用到的不多,主要是数据透视表,v-lookup,sumif和round等,还有哪些常用又有点复杂的嘛,加减乘除和求和求平均就不用说了求~今晚要总复习~ 衰莫大于心死,愁莫大于无志。应届.毕业生.网,致力于提高毕业生就业率, 最新评论 第一部分 简单不要说明的函数SUM函数求和AVERAGE函数求平均值COUNT函数计数函数MAX函数求最大值MIN函数求最小值第二部分 较为复杂的函数ROUND函数RANK函数COUNTIF函数SUMIF函数SUMPRODUCT函数 IF函数AND函数 Vlookup函数DATEDIF函数
只用过给定函数,填表就够了
v-lookup网上讲得例子非常仔细,你搜下看看。我以前在网上一看就懂了。谁记得那么多 都是碰到实际情况再解决
谢谢 我会用到concatenate。。。
那天只考了数据分析和数据透视表主要是做社保和工资和绩效的同僚们,平时用到哪些多~八成就是考这方面统计费用分摊时,筛选subtotal很有用的去度娘吧用的最多的是IF AND OR+长公式~~
1 / 2
精品文档
感 谢 阅 读~
2 / 2
转载请注明出处范文大全网 » 工作中最常用的excel函数
70,g7>