Excel有哪些相见恨晚的技能?
憋了三年,我们整理了300w份Excel图表,发现了这些藏在数据中的秘密!(点赞收藏!)
Excel学的好,升职加薪熬夜少!大多数Excel新手,想学复杂的Excel函数公式,却又不知从何入手。如果是光靠自己学习或者是看书的话,会花费很多时间和力气,而且学着学着,也就没有了耐心。
最好是可以找到一些好的系统教程学习,跟着我一起学习,会事半功倍。你也能迅速抓住Excel的精髓,毕竟功能很多,但是常用的就几种功能,能够熟练灵活运用,你就已经可以解决大部分的问题了!
今天就和大家讨论一下Excel的万能公式套路,需要的赶紧收藏,让你在数据处理中尽显优势~
1.查找重复项
在平时的开发工作中,我们有时会需要从Excel中查找出重复的数据,以便清理业务数据。假如现在有如下图所示的文档,现需要找出“店铺编码”列的重复数据。
2.1 新建规则
选中列“店铺编码”,然后依次点击菜单:开始-->条件格式-->突出显示单元格规则-->重复值。
关公注公纵号:么古 ,回复“表格”领取
全套Excel大神进阶攻略已经为您打包
更多技巧卡片请看文章底部!
2.2 设置重复值格式
在“重复值”弹出框中,按照默认的样式点击确定,会发现重复项被标记出来。
2.3 筛选重复数据
点击菜单:数据-->筛选,然后按照单元格颜色筛选,就可以只查看重复的数据。
筛选后的结果如下所示(只显示了重复的数据,达到了我们的目的):
2.4 清除规则
如果想恢复原来的数据,可以点击开始-->条件格式-->清除规则-->清除整个工作表的规则,清除掉该规则。
3.单元格内容拆分
一般情况下,开发在记录一些日志时,都会比较简单,如:1274206,商品1274206已淘汰,但是发给到运营时,运营一般都关注的比较细,需要明确的表头。
以下为程序中开发记录的日志:
但是发给运营时,运营需要明确的表头,如:商品编码,失败原因等。此时就需要将单元格的内容根据,拆分成多个单元格,操作步骤如下:
3.1 选中需要拆分的数据,点击数据--分列
更多Excel大神技巧文章底部获取!
3.2 选中单元框:分隔符号,点击下一步
3.3 分隔符号选中逗号,点击下一步,然后点击完成
此时会看到单元格的内容自动拆分成两列,如下所示:
4.永久取消超链接
在使用Excel的过程中,Excel会自动将网址转换为超链接,操作不当,容易误点,引起不必要的错误。那么如何在Excel 2013里永久取消超链接呢?
1.依次打开菜单文件--选项,弹出Excel 选项弹出框。
2.选中左侧菜单"校对",点击"自动更正选项"。
3.取消勾选"Internet 及网络路径替换为超链接"。
多条件判断公式
=IF(AND(条件1,条件2...条件n),同时满足条件返回的值,不满足条件返回的值)
=IF(OR(条件1,条件2...条件n),同时满足任一条件返回的值,不满足条件返回的值)
示例:同时满足金额小于500且B列内容为“未到期"时在C列输入”补款“
=IF(AND(A2<500,B2="未到期"),"补款","")
多条件求和、计数公式
Sumproduct(条件1*条件2*条件3...数据区域)
示例:统计A产品3月的销售合计
=SUMPRODUCT((MONTH(A3:A9)=3)*(B3:B9="A")*C3:C9)
注:和sumifs相比速度虽然慢了点,但Sumproduct可以对数组进行处理后再设置条件,同时也可以对文本型数字进行计算,而Sumifs函数则不可。
多条件查找
Lookup(1,1/((条件1*条件2*条件3...)),返回值区域)
示例:如下图所示要求,同时根据姓名和工号查找职位
=LOOKUP(1,0/(B2:B6=B9)*(A2:A6=C9),E2:E6)
提取任意位置字符串的数字
=LOOKUP(9^9,MID(数字,MATCH(1,MID(数字,ROW(1:99),1)^0,0),ROW(1:99))*1)}
(注:数组公式,需要按ctrl+shift+enter三键输入)
示例:如下图所示,提示A列中字符串中的数字
=LOOKUP(9^9,MID(A2,MATCH(1,MID(A1,ROW(1:99),1)^0,0),ROW(1:9))*1)
注:如果字符串超过100位,就把99调大
Sumif和Countif函数
用途:按条件求和,按条件计数,很多复杂的数据核对也需要用到这2个函数。
用法:=Sumif(判断区域,条件,求和区域);=Counif(判断区域,条件)
如图:要求在F2统计A产品的总金额
Sumifs和Countifs函数
用途:多条件求和、多条件计数,数据分类汇总利器
用法:
=Sumifs(求和区域,判断区域1,条件1,判断区域2,条件2…..
=Countifs(判断区域1,条件1,判断区域2,条件2.....)
如图:统计郑州所有电视机的销量之和=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
说明:在sumifs中可以使用通配符*
Left、Right和Mid函数
用途:字符串的截取
用法:
=Left(字符串,从左边截取的位数)
=Right(字符串,从右边截取的位数)
=Mid(字符串,从第几位开始截,截多少个字符)
=left("abcde",2) 结果为 ab
=right("abcde",3) 结果为 cde
=mid("abcde", 2,3) 结果为 bcd
Datedif函数
用途:日期的间隔计算。
用法:
=Datedif(开始日期,结束日期."y") 间隔的年数
=Datedif(开始日期,结束日期."M") 间隔的月份
=Datedif(开始日期,结束日期."D") 间隔的天数
如图:B列为入职日期, 要求计算入职多少月?
最值计算函数
用途:计算最大值,最小值
用法:
=MAX(区域) 返回最大值
=MIN(区域) 返回最小值
=Large(区域,n)返回第n大值
=Small(区域,n)返回第n小值
对D列的数字计算最大值,最小值,第2大值,第2小值。
关注公众号:么古 , 全套Excel大神进阶攻略已经为您打包,回复“表格”领取
更多技巧卡片请看文章底部!
IFERROR函数
用途:把公式返回的错误值转换为提定的值。如果没有返回错误值则正常返回结
用法:
=IFERROR(公式表达式,错误值转换后的值)
如图:要求计算完成率
INDEX+MATCH函数
用途:match查找到行数列数,然后用index根据位置从另一行/列中提取相对应位置的值
用法:=INDEX(区域,match(查找的值,一行或一列,0) )
如图:要求根据产品名称,查找编号。
FREQUENCY函数
统计年龄在30-40岁之间的员工个数。
AVERAGEIFS函数
按多条件统计平均值。
SUMPRODUCT函数
统计不重复的总人数,用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。
閱讀更多 河邊小青青 的文章