首页系统excel函数与公式实战技巧精粹 excel公式不会用怎么办

excel函数与公式实战技巧精粹 excel公式不会用怎么办

编程之家2024-06-102次浏览

一、Excel函数那么多怎么才能吾透举一反三

我是头条号:“Excel小技巧”的小编,分享一些办公常用的技能,希望有我的分享,能提高大家的工作效率,如果觉得文章对你有用,请在下方点个赞,让小编高兴下,如果没有解决你的问题,请给我留言,我们进一步探讨函数分类

在Excel中又好几百个函数,可以分为12个类别,有一些函数是比较通用的,肯定是都要掌握的,而有一些函数是比较专业的,就根据个人工作需要的不同,掌握特定函数就可以了;

excel函数与公式实战技巧精粹 excel公式不会用怎么办

函数也遵循2-8原则,也就是说,你最常用的函数只占20%,但是可以解决你工作中80%的问题;

如何理解函数

其实函数可以理解成一个黑箱,我们不需要了解实现过程,只需要知道功能,输入,输出就可以了,那么选择什么样的功能,就是函数分类的问题了,输入是有严格限制的,每个函数要求可能都不一样;

常用函数

函数功能,可以根据函数名看出来,基本上都是其英文意义,高频函数如下:

文本类:LEFT,RIGHT,MID,LEN,TEXT

查询类:MATCH,INDEX、VLOOKUP,LOOKUP

统计类:SUM,SUMIF,AVERAGE,AVERAGEIF,MAX,MIN,RANK,SUBTOTAL

excel函数与公式实战技巧精粹 excel公式不会用怎么办

日期时间类:MONTH,YEAR,DATEDIF,NOW

逻辑类:IF,OR,AND

二、excel公式不会用怎么办

理解公式的基本功能和参数,是掌握Excel公式的基本功。

首先要知道Excel公式的要素:等号开头,参数用括号括起来,参数之间用逗号隔开,所有符号英文输入。举例

1.将日期标注为星期几(自定义——yyyy年m月d日)

2.or("条件一","条件二"……)

excel函数与公式实战技巧精粹 excel公式不会用怎么办

例如:通过星期几计算是否加班:=if(or(weekday(A2,2)=6,weekday(A2,2)=7),"是","否")【A2为要求日期】

3.求和函数

①求和函数SUM

=SUM(数值,单元格,单元格区域,另一个函数)

适用场景:最简单形式的求和

②条件求和函数SUMIF

=SUMIF(条件所在的单元格区域,条件,实际求和的单元格区域)

适用场景:具有单个条件的求和

③sumifs(求和区域,"条件区域一""条件一","条件区域二""条件二"……)

适用场景:两个及以上条件的求和

④积和函数SUMPRODUCT

=SUMPRODUCT(第一组数据,第二组数据。。。)

适用场景:求销售总额

4.将单元格中1——001:首先选中所有单元格,设置单元格格式,文本,再输入001即可。

5.在做完一系列题目后,若题目要求分类汇总,而选项卡下不能点分类汇总则:选择设计——工具——转化为区域

6.rank(要排名的单元格,在哪排名,排名方式0降序/1升序)注意:在哪排名的区域需要绝对引用

7.text将数值转化为自己想要的文本

例如十五套从身份证号中提取出生年月日格式为×年×月×日:=text(mid(f3,7,8),"0000年00月00日")

8多条件if

例如十五套通过学号求班级,3,4位位班级,01=一班,02=2班…:100286即:=if(mid("学号",3,2)="01","一班",if(mid("学号",3,2)="02","二班","三班"))

9.int取整函数

例如十八套根据月份求季度:=int((month(A3)-1)/3+1)

10.=now()

=today()

计算一个日期距离现在有几年:=int((today()-f3)/365)

=year()

=weekday()

=Datedif(起始日期,结束日期,差值类型)

"D/M/Y"

注意事项:日期函数是可以进行加减运算的

11.平均值函数(可通过其他函数转化)

①平均值函数AVERAGE

②条件平均函数AVERAGEIF

③多条件平均函数AVERAGEIFS

12.VLOOKUP函数(必考)

=vlookup(通过谁来查找,在哪查找的绝对区域,返回值在所选中区域的列数,0)

适用场景:多表对照

★注意事项:第二个参数必须绝对引用

例如:通过图书名称填充图书编号:=VLOOKUP("图书名称",既有图书名称又有图书编号的对照表,图书编号在你所选区域第几列,。

13.计数函数

①Count函数,适用场景:数值计数

②Counta函数,适用场景:非空计数

③Countif函数,=countif(区域,条件),适用场景:单条件计数

④Countifs函数,=countifs(区域,条件,区域,条件)

14.字符串截取函数

①左侧截取函数LEFT

=LEFT(要截取的字符串,从左侧开始截取几位)

适用场景:左侧规则数量字符截取

②右侧截取RIGHT

③中间截取字符串MID

=MID(要截取的字符串,从第几位开始,截取几位)

三、你认为Excel最强大的公式是哪个,为什么

曾经VLOOKUP说他是最强的,LOOKUP表示不服!

说起查找与引用函数,大家最熟悉的莫过于VLOOKUP了。其实它一个家族中的另一个兄弟,LOOKUP函数比它的功能更为强大,想要更好的处理数据的查找与引用,千万不要错过它!

01、LOOKUP函数语法

LOOKUP函数的语法有两种:

1、向量式:

向量式是使用的更多的一种形式:

第一参数是要查找的值;

第二参数为一行或者一列,是被查找的区域。

第三参数是返回哪行或者哪列的结果,这里第三参数要求与第二参数有一样的尺寸。

比如第二参数是一列,B2到B5,第三参数是C1到C6,这样就是不可以的。

2、数组式:

=LOOKUP(lookup_value,array)

第二参数array,是一个多行多列的区域。这个区域的首列须包含要查找的值,返回对应最后一列的值。

这两种方式,有的时候可以实现相同的功能,区别只是写法不同;也有的功能只能用其中一种的写法。下面用栗子来给大家详细展现。

02、LOOKUP模糊查找

个人所得税税率查询:

(模糊匹配,类似VLOOKUP)

应纳税的工资(减去3500之后),查找应使用的税率时,没有完全一样的数值,将返回小于它的最大值。

F2单元格中的公式为(向量式):

=LOOKUP(E2,$A$2:$A$5,$C$2:$C$5)

$A$2:$A$5是要查找的区域;

$C$2:$C$5是对应返回的结果。

例子中工资5000在左边的表格中找不到,将返回小于它的最大值,也就是4500对应的结果,20%(虚构数值,仅供参考)。

这个例子中,还可以使用数组的方式,结果是一样的:

=LOOKUP(E2,$A$2:$C$5)

注意:在用LOOKUP进行模糊匹配的查询时,第二参数须以升序排列。

03、LOOKUP反向查找

由于LOOKUP函数向量式的写法中,被查找的区域,和结果返回的区域是分开写的,所以可以应用在,要返回的结果在被查找区域之前的情况。

例如,知道员工工号的情况下,要在左边的表中查找员工的姓名。

E2中的公式为:

=LOOKUP(D2,$B$2:$B$8,$A$2:$A$8)

B2至B8是要查找的区域,返回A2至A8对应的结果。

需要注意的是,LOOKUP函数默认在找不到完全一致的值时,会按模糊匹配来处理,返回一个接近的值(小于它的最大值对应的结果)。

所以,如果要查找的值,可能在被查找区域不存在,又需要精确匹配时,不要使用这种方式。

04、条件查询

0/条件其实是一大类用法的总结。

公式写作:=LOOKUP(1,0/(条件),目标区域或数组)

这里的“条件”可以是多种多样的。我们可以根据不同的需求,构建不同的“条件”。利用“0除以任何数都得0”和“0除以错误值还是错误值”的原理,得到一串0和错误值组成的数组。

我们要找的是“1”,在找不到“1”时,返回最后一个“0”对应的值。(LOOKUP自动屏蔽错误值)

“条件”如何构建,决定能发挥怎样的作用。下面几种常见用法的举例。

①求同一值最后一次出现的情况:

我们想求某值最后一次出现的结果,可以先通过使用一个条件,将出现这个值的情况挑选出来。

这个例子中用到的“条件”就是:=FIND(E3,$B$3:$B$11)

这个FIND函数的作用是,在B3到B11区域中,挨个单元格去查找,E3的值在这个单元格中的位置。

E3中是“张三”,B3中“张三”是从第一个字符开始的,所以返回1.

同理,B4和B5都会返回1。在找不到我们要的结果的时候,会返回错误值:#VALUE!.

也就是,符合条件时会返回一个非零数字,不符合条件时会返回错误值。

所以刚才的“条件”就会得到:

=FIND(E3,$B$3:$B$11)

={1,1,1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

=0/FIND(E3,$B$3:$B$11)

={0,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

错误值会被LOOKUP函数排除掉,剩下就是在符合条件的值中进行运算。

最原始的公式就会变成:

=LOOKUP(1,0/FIND(E3,$B$3:$B$11),$C$3:$C$11)

=LOOKUP(1,{0,0,0},$C$3:$C$11)

由于我们要找1,而查找区域只有0,找不到1时,会返回最后一个0对应的结果。这个案例中就是第3个0对应的值,也就是B5单元格对应的结果C5。

总结一下0/[条件]的用法就是,通过构建一个“条件”,所有符合条件的值返回一个非零数字,不符合条件的值返回一个错误值。

②多条件查找:

多条件查找其实就是上面公式的一个变种。

写作:

=LOOKUP(1,0/((条件1)*(条件2)*……*(条件N)),目标区域)

多个条件之间使用乘号连接,表示当列出的条件同时满足时的结果。如果有多个行出现同时满足条件,则返回最后一个。

下面这个例子中,我们需要班级和姓名两个条件同时满足时,对应的结果。

I2中的公式为:

=LOOKUP(1,0/(($B$3:$B$8=F3)*($C$3:$C$8=G3)),$D$3:$D$8)

这两个条件分别是:

B3到B8中等于F3,和C3到C8中等于G3。

同时满足时,将返回D3到D8对应的结果。

能实现这么多功能,你说LOOKUP是不是最强大的函数?

电脑密钥在哪找到(电脑密钥怎么获取)ui设计软件?ui设计用什么软件做