当我们有比较紧急、常规的数据处理工作,使用Excel很便捷迅速。
下面整理了Excel中的函数,作用大致分为几类:文本清洗、关联匹配、逻辑运算、统计、时间序列。
一、文本清洗函数
用于文本的查找、替换、处理等
很多时候我们在处理日期和时间时,不宜直接进行四则运算,需要使用Text函数保证格式不受影响,这里介绍一下Text函数的几个妙用——
=TEXT(日期,“aaaa”) 返回星期几
=TEXT(列A-列B,“盈利0.00万;亏损0.00万;平衡;”) 相当于IF函数,求某两列差值后,再以0为界限分类
=TEXT(SUM(A1:A20),"[h]:mm:ss") 多个时间相加
=-TEXT(MID(身份证号,7,8),“0-00-00”) 身份证号提取出生日期
=TEXT(MOD(MID(身份证号,17,1),2),“男;;女”) 身份证号提取性别
二、关联匹配函数
关联匹配类函数中最常用的便是Vlookup,批量操作简便高效,比较简单,就不再赘述。
针对反向查找、双向查找等比较复杂的操作,Match函数与Index函数结合使用比vlookup函数更好。
1、通过已知元素来查找未知元素值
一般的方法是:先用Match函数查找已知元素位置,再利用Index函数找到对应位置的元素值。简单的嵌套格式:
=INDEX( 查找区域, MATCH(已知元素值,查找区域,0))
2、双向或多项查找
根据两个已知元素来查找对应的未知元素值,其实就是在上面的公式里多加个Match函数条件:
=INDEX( 查找区域, MATCH(已知元素值A&已知元素B, 查找区域A&查找区域B, 0))
这样看来和lookup、Excel的高级筛选类似。
再补充一下lookup函数的作用
1、模糊查找
=LOOKUP(查找值,查找列,结果列)
2、区间查找匹配
Lookup函数可以定位值所属类别, 这个作用跟IF条件分组类似,但是比IF嵌套的语句更简单,用法如下:
=LOOKUP(查找值,{下限1, 下限2…;类别1,类别2……})
三、逻辑运算函数
在数据清洗过程中,使用逻辑运算函数来筛选脏数据,这里列举了一些常用的函数,其功能和python数据清洗过程类似。
其中,IF函数与其他函数进行嵌套使用,可以较为灵活的编写多种条件,对数据进行筛选清理。
四、统计函数
Excel的统计运算函数很全,使用方便,这里列举了一些基础的统计运算函数。
五、时间序列函数
Excel中的时间函数,这里只列举了一些常见的。
补充一个小小的知识点,有的时候从数据库下载的数据,日期显示的是时间戳的格式,需要进行转换——
时间戳转化日期公式:
=(A1+83600)/86400+70365+19
以上是对Excel基本函数的梳理,这里并没有列出各个函数详细的操作步骤,只起到查找复盘的作用,相信大家试一试便能理解用法。理解了Excel中函数的用法,对于今后学习python编程也有一定的帮助。
虽然使用Excel筛选数据,看起来似乎没有python等编程语言高级,但是个人在数据筛选过程中,使用最多的是逻辑函数和关联匹配函数,在处理数值类题时经常用到统计函数,觉得Excel用起来更方便,可以快速方便地查找,应对不同的问题可随时调整筛选条件。当然,前提是处理的数据量没有几百万那么多。
对于一些重要函数的经典用法,欢迎大家进行补充。