Excel新版本,必学的3个函数公式,快快来学!
在最新版本的Excel里面,更新了很多好用的函数公式,今天分享其中3个,打工人必学的,大大的提升工作效率,工作早做完,不加班
它可以用来进行去除重复值,得到唯一值列表
使用的用法是:
=unique(数组区域)
举个例子,左边的城市,希望快速得到不重复的列表
我们只需要输入的公式是:
=UNIQUE(A1:A7)
它会对a1:a7中的数据去除重复,得到如下列表
如果说是多列数据,去除重复值,同样的
我们只需要输入的公式是:
=UNIQUE(A1:B7)
非常实用,易于理解
以前遇到查找匹配问题,第一时间想到的是vlookup,但是对于新手不太友好,容易出错
因此新版本出了一个更简单的Xlookup公式
一次就可以学会
使用的用法是:=xlookup(查找值,查找列,结果列)
遇到上面的问题,我们只需要找到对应3个参数
查找值是d2单元格
查找列是姓名列,即a列
想要的结果是工资,即b列
所以,我们输入的公式是:
=XLOOKUP(D2,A:A,B:B)
对于新手也非常友好,逻辑非常直接了当
Xlookup公式解决了一对一查找匹配的问题,对于一对多匹配的问题
出来了一FIlter筛选函数公式
可以轻松的进行一对多查询匹配
例如,左边是员工档案数据
现在我们需要制作一个筛选器,当我们输入对应部门信息时,把对应的信息给全部列出来
这就是筛选的逻辑了
我们需要对a列的数据进行筛选,里面填写市场部这个值,然后对应a:e列的结果就是我们想要的
所以Filter的用法就是:
=FIlter(结果数据,筛选条件)
这里,我们只需要输入公式:
=FILTER(A:E,A:A=H2)
当我们更改部门的时候,结果也能直接出来:
关于这3个函数公式,你学会了么?动手试试吧!
分享几个常用的Excel函数公式
小伙伴们好啊,今天咱们继续分享几个常用的Excel函数公式。
1、按职务排序
如下图所示,需要根据F列的目标顺序,对B列的职务排序。
D2单元格输入以下公式,向下复制。
=MATCH(B2,F:F,0)
再单击D列任意单元格,【数据】选项卡下点一下升序按钮即可。
MATCH函数,在F列中查询B2单元格职务所处的相对位置,并返回表示位置的数字。最后对这些数字进行升序排序,最终得到和F列相同的顺序。
2、根据日期返回季度
如下图所示,需要根据A列的日期,返回该日期所属的季度。
B2单元格输入以下公式,向下复制。
=MATCH(MONTH(A2),{0,4,7,10})
首先用MONTH函数计算出A2单元格所属的月份,结果为5。
再使用MATCH函数,计算该月份在常量数组{0,4,7,10}中所处的位置。{0,4,7,10},是各个季度的起始月份。
本例中MATCH函数省略了第三参数,其计算规则与使用参数1时相同,当查找不到对应的内容时,会以小于查找值的最接近的一个进行匹配,并返回对应的位置信息。
MATCH函数在常量数组{0,4,7,10}中找不到5,因此以小于5的最接近值4进行匹配,并返回4在常量数组{0,4,7,10}中的位置,结果为2。
3、随机安排顺序
如下图,希望对A列的应聘人员随机安排面试顺序。
先将标题复制到右侧的空白单元格内,然后在第一个标题下方输入公式:
=SORTBY(A2:B11,RANDARRAY(10),1)
RANDARRAY函数的作用是生成随机数数组,本例公式使用RANDARRAY(10),表示生成10个随机数的数组。
SORTBY函数的排序区域为A2:B11单元格中的数据,排序依据是按随机数数组升序排序。因为公式每次刷新所生成的随机数数组是不确定的,所以A2:B11单元格中的数据也会得到随机的排序效果。
4、按条件提取全部记录
如下图所示,左侧是员工信息,D列是经理亲戚。需要在F列提取出所有不是经理亲戚的员工名单。
=FILTER(A2:B11,COUNTIF(D2:D5,A2:A11)=0)
先使用COUNTIF(D2:D5,A2:A11),统计A2:A11单元格区域中的姓名,在D列经理亲戚列表中出现的次数,结果返回1或者0:
{0;0;1;1;0;1;0;0;1;0}
接下来用等式判断以上内存数组结果是否等于0,得到结果TRUE或者FALSE。
FILTER函数筛选出结果为TRUE的全部记录。
5、任意方向查数据
如下图所示,要根据D列的姓名,在B列进行查询并返回A列对应的部门。E2单元格公式为:
=XLOOKUP(D2,B:B,A:A,\”无记录\”)
第一参数是查询的内容,第二参数是查询的区域,第三参数指定要返回哪一列的内容。第四参数指定在查找不到查询值时,返回什么内容。
公式的意思就是在B列单元格区域中查找D2单元格的姓名,并返回A列单元格区域中与之对应的部门。如果找不到某个姓名,就返回“无记录”。
XLOOKUP函数的查询区域和返回区域是分开的两个参数,使用时不需要考虑查询的方向问题,可以实现任意方向的数据查询。
好了,今天咱们分享的内容就是这些,祝各位一天好心情~~
图文制作:祝洪忠
Excel函数公式,IF,IFS,SWITCH三巨头,1分钟学会
Excel里面对于条件判断公式,以前只有IF,最新版本出来了另外两个好用的公式IFS和SWITCH
今天花1分钟时间学会
举个例子:
我们现在有一批单号,希望快速匹配出对应是哪个公司:
我们看单号的首字母可以来判断它是对应是什么公司:
如果我们使用IF函数嵌套使用的话,输入的公式是:
=IF(LEFT(A2,1)=\”T\”,\”AZ\”,IF(LEFT(A2,1)=\”9\”,\”UP\”,IF(LEFT(A2,1)=\”1\”,\”PS\”,\”\”)))
通过3个IF函数嵌套,可以得到对应的结果:
但是IF函数里面的括号位置一不小心弄错了,结果就会出不来
在最新版本里面,对IF函数进行了升级,可以一次性的多次进行判断
使用的用法是:
=IFS(判断1,结果1,判断2,结果2,判断3,结果3…)
所以这里,如果用IFS公式,只需要输入:
=IFS(LEFT(A2,1)=\”T\”,\”AZ\”,LEFT(A2,1)=\”1\”,\”PS\”,LEFT(A2,1)=\”9\”,\”UP\”)
对比IF,简单不少,逻辑更清晰
它也可以进行判断
用法是:
=SWITCH(表达式,值1,结果1,值2,结果2,…)
如果后面的值,等于表达式,那就返回对应的结果
所以这里,我们只需要输入公式:
=SWITCH(LEFT(A2,1),\”1\”,\”PS\”,\”9\”,\”UP\”,\”T\”,\”AZ\”)
是不是更加的简单方便
有的小伙伴可能会说,建立一个匹配参照表,然后再用个VLOOKUP公式不好么
实际工作中,有时候,不能建立辅助列,而且如果新增了一个判断
例如,加了一个首字母Q,对应公司AK,用SWITCH公式,只需要在后面继续添加条件值,结果值,就可以
关于这个函数公式,你学会了么?动手试试吧!
本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com
文章为作者独立观点不代本网立场,未经允许不得转载。