温故而知新,经典案例再现,重温对excel的学习!
1、判断是否重复
判断姓名是否重复:
输入公式:
=IF(COUNTIF(A:A,A2)>1,”重复”,””)
COUNTIF(A:A,A2)部分计算A2单元格内容“仰望~星空”在A列的个数2
与1比较,2>1条件成立,返回TRUE
用函数IF判断,如果条件成立,就返回“重复”,否则就返回空。
判断银行账号是否重复:
输入公式:
=IF(COUNTIF(B:B,B2&”*”)>1,”重复”,””)
银行账号超过15位数了,以文本存储
函数COUNTIF在运算时,将文本型数字当做数值来处理,所以连接星号&”*”将其强行识别成文本进行计算。
2、隔行填充颜色
条件格式的方法:
第一步:选中要设置的区域A1:F9单元格
第二步:【开始】→【条件格式】→【新建规则】
输入公式:=MOD(ROW(),2)=0
第三步:【格式】
在弹出的【设置单元格格式】对话框中点【填充】→选择颜色→【确定】
【确定】
效果如下:
插入表格的方法:
按<CTRL+T>键创建表
【确定】
3、快速批量求和
ALT+=:快速批量求和
合计总计求和:
选中C2:G11单元格区域
按F5键→定位条件→定位空值→确定
按<ALT+=>键
选中C2:H12单元格区域
按<ALT+=>键
4、保留两位小数
实发工资保留两位小数:
若通过设置单元格格式,只是看到的是保留两位小数
该怎么办呢?
输入公式:=ROUND(F2,2)
函数ROUND:按指定的位数对数值进行四舍五入。
5、条件汇总
单条件汇总(根据科目汇总金额):
输入公式:
=SUMIF(B:B,F2,D:D),下拉填充。
SUMIF(条件区域,条件,求和区域)
或用透视:
多条件汇总(根据科目和明细科目汇总金额):
输入公式:
=SUMIFS(D:D,B:B,F6,C:C,G6)
SUMIFS(求和区域,条件区域1,条件1,…,条件区域N,条件N)
或用透视:
6、从左往右查找
查找小鱼儿的入职日期:
输入公式:=VLOOKUP(G2,A:D,4,0)
VLOOKUP(要查找的值,查找区域,要返回的结果在查找区域的第几列,精确匹配或近似匹配)
7、计算工龄
根据入职日期计算工龄:
输入公式:
=DATEDIF(B2,NOW(),”y”)&”年”&DATEDIF(B2,NOW(),”ym”)&”个月”&DATEDIF(B2,NOW(),”md”)&”天”
NOW()返回当前日期和时间
函数DATEDIF:计算两个日期间的年、月、天数。
DATEDIF(开始日期,结束日期,为所需信息的返回时间单位代码)
DATEDIF(B2,NOW(),”y”)返回从“2016-11-15”到当前日期的整年数2
DATEDIF(B2,NOW(),”ym”)部分忽略日期中的日和年,返回从“2016-11-15”即11月到当前日期的整月数1
DATEDIF(B2,NOW(),”md”)部分忽略日期中的月和年,返回从“2016-11-15”即15日到当前日期“2019-1-6”的整日数22
字符串间的连接用“&”
2&”年”&1&”个月”&22&”天”即返回2年1个月22天
8、工资条的制作
操作步骤:
第一步:复制表头
第二步:复制A3:F3单元格→选择性黏贴→黏贴链接
第三步:等号(=)替换成:空格等号( =)
点【全部替换】后效果:
第四步:选中A12:A15单元格区域下拉
第五步:空格等号( =)替换成:等号(=)
操作演示:
9、下拉菜单的制作
操作步骤:
第一步:选中要设置的区域,比如E2:E5单元格区域,【数据】→【数据验证】
效果:
第二步:选中A1:C5单元格区域,按F5键→定位条件→定位常量→确定
第二步操作是为了排除空格,没有这一步的话,下拉菜单中会出现空白,如下图所示:
第三步:【公式】→【根据所选内容创建】→【确定】
第四步:选中F2:F5单元格区域,【数据】→【数据验证】
效果如下图所示:
操作演示:
打开微信扫一扫,Office自学网 专注分享软件安装和视频教程,让你一号在手,办公无忧!
Office自学网公众号
Office自学网客服