函数表格公式大全,函数表格公式大全隐藏数字怎么设置 收藏 0

Excel工作表中最常用的10个函数，中文解读，动图演示

Excel工作表中的函数是非常的繁多的，如果要全部掌握，几乎是不可能的，也没有这个必要，不用行业，不同部门对函数需求都不同，所以，只需要掌握自己常用的部分函数即可，但是，下文中的10个函数是部分行业和部门的，所有的从业人员必须100%全部掌握！

一、Excel工作表函数：Sum。

功能：求和

语法结构：=Sum(值或单元格区域)。

目的：计算总“月薪”。

方法：

在目标单元格中输入公式：=SUM(1*G3:G12)，并用Ctrl+Shift+Enter填充即可。

解读：

因为“月薪”为文本型数值，所以直接用Sum函数求和时，得到的结果为0，此时我们需要将每个值转换为数值，所以给每个值乘以1，然后用Sum函数求和即可。

二、Excel工作表函数：If

功能：判断是否满足某个条件，如果满足则返回一个值，如果不满足则返回另一个值。

语法结构：=IF(判断条件,条件为真时的返回值,条件为假时的返回值)。

目的：“月薪”＞4000，返回“高”，＞3000，返回“中”，否则返回“底”。

方法：

在目标单元格中输入公式：=IF(G3>4000,\”高\”,IF(G3>3000,\”中\”,\”低\”))。

解读：

If函数除了常规的判断之外，还可以嵌套使用，公式的函数以为：如果当前单元格的值＞4000，则直接返回“高”，终止判断，否则继续执行当前单元格的值是否＞3000，如果大于，返回“中”，否则返回“低”。

三、Excel工作表函数：Lookup

功能：从单行或单列或数组中查找一个值。

Lookup具有两种形式：向量形式和数组形式。

（一）向量形式

功能：从单行或单列中查找查找指定的值，返回第二个单行或单列中相同位置的值。

语法结构：=Lookup(查找值,查找值所在的范围，[返回值所在的范围])。

当“查找值所在的范围”和“返回值所在的范围”相同时，可以省略“返回值所在的范围”。

目的：查询员工的“月薪”。

方法：

1、选定数据源区域，以“员工姓名”为主要关键字“升序”排序。

2、在目标单元格中输入公式：=LOOKUP(J3,B3:B12,G3:G12)。

解读：

如果未对数据源以查询关键在所在列进行升序排序，则查询的结果是不准确的，甚至返回错误代码，所以在使用Lookup函数时，先对查询关键字所在的列为主要关键字升序排序，然后再查询。

（二）数组形式

功能：从指定的范围第一列或第一行中查询指定的值，返回指定范围中最后一列或最后一行对应位置上的值。

语法结构：=Lookup(查询值,查询范围)。

解读：

从从“功能”中可以看出，Lookup函数的数组形式，查找值必须在查询范围的第一列或第一行中，返回的值必须是查询范围的最后一列或最后一行对应的值。即：查找值和返回值在查询范围的“两端”。

目的：查询员工的“月薪”。

方法：

1、选定数据源区域，以“员工姓名”为主要关键字“升序”排序。

2、在目标单元格中输入公式：=LOOKUP(J3,B3:G12)。

解读：

数据范围B3:G12中，B列为查询值J3所在的列，G列为返回值所在的列。

（三）优化形式（单条件查询）

在使用Lookup函数时，如果每次都要排序，会非常的麻烦，所以我们可以对其进行优化处理。

目的：查询员工的“月薪”。

方法:

在目标单元格中输入公式：=LOOKUP(1,0/(B3:B12=J3),G3:G12)

解读：

1、仔细分析公式=LOOKUP(1,0/(B3:B12=J3),G3:G12)，不难发现，其本质还是为向量形式，查询值为1，查询范围为“0”和“错误值”组成的新数组……。

2、查询范围：0/(B3:B12=J3)，如果J3和B3:B12范围中的值相等，则返回1,如果不相等，则返回0,0/1=0,0/0则返回错误。而Lookup函数在查询时，如果找不到对应的查询值，则自动“向下匹配”，其原则为：小于或等于查询值的最大值作为当前的查询值。即只有0符合条件，返回0所对应位置的值。得到查询结果。

（四）优化形式（多条件查询）

目的：查询员工在“已婚”和“未婚”时的工资。

方法：

在目标单元格中输入公式：=LOOKUP(1,0/((J3=B3:B12)*(K3=E3:E12)),G3:G12)。

解读：

当两个条件都为真时，其乘积也为真，其中一个为假或两个都为假时，其乘积也为假。所以多条件查询和单条件查询的原理是相同的。

（五）多层区间查询

目的：查询“月薪”对应的等级，≥4000的为“高”；≥3000且＜4000的为“中”，＜3000的为“低”。

方法：

在目标单元格中输入公式：=LOOKUP(G3,$J$3:$K$5)。

解读：

此方法主要应用了Lookup函数的数组形式和“向下匹配”的特点。

四、Excel工作表函数：Vlookup

功能：搜索工作表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。

语法结构：=Vlookup(查询值，数据范围，返回值列数，匹配模式)。

其中匹配模式有两种，分别为“0”或“1”。其中“0”为精准匹配，“1”为模糊匹配。

（一）常规查询

目的：查询员工的“月薪”。

方法：

在目标单元格中输入公式：=VLOOKUP(J3,B3:G12,6,0)。

解读：

由于“月薪”在数据范围B3:G12的第6列，所以参数“返回值列数”为6。

（二）反向查询

目的：根据“身份证号码”查询“员工姓名”。

方法：

在目标单元格中输入公式：=VLOOKUP(J3,IF({1,0},C3:C12,B3:B12),2,0)。

解读：

公式中的IF({1,0},C3:C12,B3:B12)的作用为形成一个以C3:C12为第一列、B3:B12为第二列的临时数组。

（三）多条件查询

目的：根据“员工姓名”和\”婚姻”查询对应的“月薪”。

方法：

在目标单元格中输入公式：=VLOOKUP(I3&J3,IF({1,0},B3:B12&D3:D12,F3:F12),2,0)，并用Ctrl+Shift+Enter 填充。

解读：

1、当有多个查询的条件时，用连接符“&”连接在一起，对应的数据区域也用“&”连接在一起。

2、公式中IF({1,0},B3:B9&C3:C9,D3:D9)的作用为形成一个以B3:B9和C3:C9为第一列，D3:D9为第二列的临时数组。

五、Excel工作表函数：Match

功能：返回符合特定值特定顺序的值在数组中的位置。

语法结构：=Match(定位值,定位范围,[匹配模式])，其中“匹配模式”有-1、0、1三种，分别为：“大于”、“精准”、“小于”。

目的：根据“员工姓名”定位其在对应列中的相对位置。

方法：

在目标单元格中输入公式：=MATCH(I3,B3:B12,0)。

解读：

此处的位置相对而言的，具体要看“定位范围”的大小。

六、Excel工作表函数：Choose

功能：根据给定的索引值，从参数中选取相应的值或操作。

语法结构：=Choose(索引值,表达式1,表达式2……表达式N)。

如果参数“索引值”超出“表达式”的个数，则返回错误值。

目的：根据“索引值”返回相应的“员工姓名”。

方法：

在目标单元格中输入公式：=CHOOSE(I3,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12)。

七、Excel工作表函数：Datedif

功能：以指定的方式计算两个日期之间的差值。

语法结构：=Datedif(开始日期,结束日期,统计方式)，常用的统计方式有“Y”、“M”、“D”，即“年”、“月”、“日”。

目的：计算距离2021年元旦的天数。

方法：

在目标单元格中输入公式：=DATEDIF(TODAY(),\”2021-1-1\”,\”D\”)。

解读：

“开始日期”用函数Today（），而不用指定日期的原因在于，其值会随着日期的变化自动更新。

八、Excel工作表函数：Days

作用：返回两个日期之间的天数。

语法结构：=Days(结束日期，开始日期)。

目的：计算距离2021年元旦的天数。

方法：

在目标单元格中输入公式：=DAYS(\”2021-1-1\”,TODAY())。

解读：

此函数的依次为“结束日期”、“开始日期”，而并不是“开始日期”、“结束日期”，和Datedif函数的参数顺序要区别对待。

九、Excel工作表函数：Find

功能：返回一个字符串在另一个字符串中出现的起始位置（区分大小写）。

语法结构：=Find(查找字符串,源字符串,[起始位置])；当省略“起始位置”时，默认从第一个字符串开始。

目的：提取“员工编号”中“—”的位置。

方法：

在目标单元格中输入公式：=FIND(\”-\”,C3,1)。

解读：

也可以用公式：=FIND(\”-\”,C3)来实现，省略参数“起始位置”时，默认从第一个字符开始。

十、Excel工作表函数：Index

功能：返回指定区域中指定行和列交汇处的值或引用。

语法结构：=Index(数据范围,行,[列])，当省略参数“列”时，默认值为1。

目的：返回相应行的“员工姓名”。

方法：

在目标单元格中输入公式：=INDEX(B3:B12,J3,1)。

12组Excel函数公式，可直接套用，小白也能变大神

Hello，大家好，今天跟大家分享12组Excel公式组合的用法，都是一些工作中比较常见问题，以后如果遇到此类问题，直接套用即可，废话不多说，让我们直接开始吧

在这里要求出勤天数大于等于24天，考核得分大于等于85分同时满足才能获得500奖金，否则的话没有奖金，公式为：=IF(AND(B3>=24,C3>=85),500,0)

在这里将and函数作为了if函数的第一参数，and函数的结果是逻辑值，只有条件全部正确才会返回true，否则的话就会返回false

如果我们将上述条件更改为出勤天数大于等于24天，考核得分大于等于85分满足任意一个即可获得500奖金，公式为：=IF(OR(B3>=24,C3>=85),500,0)

OR函数他的结果也是一个逻辑值，不同的是OR函数的特点是只要有一个条件是正确的他就会返回true这个结果，只有条件全部是错的才会返回false这个结果

公式：=TEXT(MID(B3,7,8),\”0-00-00\”)

在这里我们是使用mid函数提取身份证号码中出生年月的位置，然后再利用text函数来设置数字的显示格式。如果你需要使用这个公式，只需要将B3更改为你需要单元格位置即可

公式：=IF(MOD(MID(B3,17,1),2),\”男\”,\”女\”)

身份证号码的第十七位是性别代码，偶数为女性，奇数为男性，在这里先利用mid函数将第十七位提取出来，然后利用mod函数判断奇偶性，最后利用if函数输出结果，如果你需要使用这个函数，只需要将B3单元格更改为你需要的单元格位置即可

公式：=DATEDIF(TEXT(MID(B3,7,8),\”0-00-00\”),TODAY(),\”Y\”)

Datedif函数的作用计算两个日期的差值，在这里我们先利用TEXT(MID(B3,7,8),\”0-00-00\”)）将出生日期提取出来将其作为第一参数开始的时间，利用today函数来获取当天的日期作为第二参数，第三参数为y表示计算类型为年，如果需要套用公式，只需更改b3单元格即可

公式：=IF(B3=\”\”,\”\”,IF(COUNTIF($B$3:$B$9,B3&\”*\”)>1,\”重复\”,\”\”))

在这里如果身份证号码不重复，函数会返回空值，如果重复的话就会返回重复两个字，在这里需要注意的是countif函数中的数据区域一定要进行绝对引用

公式：=SUMPRODUCT(1/COUNTIF(A3:A13,A3:A13))

首先函数会利用countif函数计算出重复的个数，然后再用1除以这个结果，最后利用sumproduct函数求和，比如一个数据重复3次，那么countif就会得到三个3，然后用1除以countif就会得到三个1/3，相加的结果就是1，想要使用函数只需要将A3:A13替换为你需要的数据区域即可

公式：=LEFT(C3,LEN(C3)-4)

在这里C3是单元格的位置，4就是单位（kg/元）所占的字符数个数

比如在这里想要求一下鲁班总和的数据，公式：=SUMIF(A3:A13,D4,B3:B13)

A3:A13是条件求和判断的数据区域，D4是求和条件，B3:B13是条件求和的区域

比如在这里想要鲁班姓名出现的次数，公式：=COUNTIF(A3:A13,D4)

A3:A13是条件计数用于判断的数据区域，D4是条件计数条件

最大值公式：=MAXIFS(B3:B12,A3:A12,D4)

最小值公式： =MINIFS(B3:B12,A3:A12,D4)

这两个函数的参数都是一样的，第一参数：B3:B12要确定最大值的单元格，第二参数：A3:A12用于判断的数据区域，第三参数：D4判断的条件

公式：=SUBSTITUTE(B3,MID(B3,4,4),\”****\”)

Subtitute这个函数的作用是将字符串的某一部分替换为另一部分，在这里我们利用mid函数将手机号码中的一部分数据提取数据，然后再替换为四个星号

以上就是今天分享的12个公式组合，公式组合还有很多，你想要学了解哪些，不妨留言告诉我~

（此处已添加圈子卡片，请到今日头条客户端查看）

最常用的10个Excel函数，中文解读，动图演示，易学易用

在Excel的应用中，离不开函数或公式，如果对一些常见的函数或公式能够熟练地掌握，对于工作效率的提高绝对不是一点点！

一、Excel函数：Sumif

功能：对满足条件的单元格进行求和。

语法结构：=Sumif(条件范围,条件,[求和范围])。

当参数“条件范围”和“求和范围”相同时，可以省略“求和范围”。

目的：按“性别”统计“月薪”。

方法：

在目标单元格中输入公式：=SUMIF(D3:D12,I3,G3:G12)。

二、Excel函数：Vlookup

功能：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。

语法结构：=Vlookup(查询值,数据范围,返回列数,[匹配模式])。

其中匹配模式有“0”和“1”两种，“0”为精准匹配，“1”为模糊匹配，省略该参数时，默认为模糊匹配。

目的：根据“员工姓名”查询“月薪”。

方法：

在目标单元格中输入公式：=VLOOKUP(I3,B3:G12,6,0)。

解读：

1、查询值所在的列必须为查询数据范围的首列。

2、“返回列数”是相对于“数据范围”而言的，并不是依据数据源而言的。

三、Excel函数：Text

功能：根据指定的数字格式将数值转换成文本。

语法结构：=Text(值或单元格引用,格式代码)。

目的：计算员工的工作时长。

方法：

在目标单元格中输入公式：=TEXT(F3-E3,\”[m]分钟\”)。

四、Excel函数：Networkdays.intl

功能：使用自定义周末参数返回两个日期之间完整的工作日数。

语法结构：=Networkdays.intl(开始日期,结束日期,[统计方式],[节假日])。

当省略参数“统计方式”和“节假日”时，默认按正常的工作日计算。

目的：计算员工的“工作日天数”。

方法:

在目标单元格中输入公式：=NETWORKDAYS.INTL(E3,F3,1,)。

解读：

如果有其他节假日，只需在第四个参数中添加单元格区域即可。

五、Excel函数：Countifs

功能：返回一组给定条件所指定的单元格数。

语法结构：=Countifs(条件1范围,条件1,条件2范围,条件2……条件N范围,条件N）。

目的：按“性别”统计“月薪”＞2000的人数。

方法：

在目标单元格中输入公式：=COUNTIFS(D3:D12,I3,G3:G12,\”>\”&J3)。

解读：

条件范围和条件必须成对出现。

六、Excel函数：Datedif

功能：按照指定的统计方式计算两个日期之间的差值。

语法结构：=Datedif(开始日期,结束日期,统计方式)。

常见的统计方式有“Y”、“M”、“D”，分别为“年”、“月”、“日”。

目的：根据出生日期计算年龄。

方法：

在目标单元格中输入公式：=DATEDIF(C3,TODAY(),\”y\”)。

解读：

1、Datedif函数为系统隐藏函数，在说明文档中是不存在的，各位亲在理解语法结构之后直接使用即可。

2、函数Today()的作用为获取系统当前的日期，所以上述公式的年龄是自动更新的，是最新的年龄，而不是不定不变的。

七、Excel函数：Rank

功能：返回某数字在一组数字中相对于其他值的大小排名，如果有多个值相同，则返回平均值排名。

语法结构：=Rank(值或单元格引用,数据范围,[排序方式])。

排序方式分为“0”和“1”两种，即“降序”和“升序”，如果省略，则默认为“降序”。

目的：对“月薪”进行排序。

方法：

在目标单元格中输入公式：=RANK(G3,G$3:G$12)。

解读：

省略第3个参数时，默认为降序排序。

八、Excel函数：Sumproduct

功能：返回相应的数组区域乘积的和。

语法结构：=Sumproduct(数组1或单元格区域1……数组N或单元格区域N)。

当只有一个数组或单元格区域时，对元素值进行求和运算。

目的：计算商品的总销售额。

方法：

在目标单元格中输入公式：=SUMPRODUCT(C3:C12,D3:D12)。

解读：

公式的计算过程为：C3*D3+C12*D12，即对应元素乘积的和。

九、Excel函数：Numberstring

功能：按照指定的格式对数值进行大写。

语法结构：=Numberstring(值或单元格引用,格式代码)。格式代码分为1、2、3三种。

目的：对“月薪”进行大写。

方法：

在目标单元格中输入公式：=NUMBERSTRING(C3,1)、=NUMBERSTRING(C3,2)、=NUMBERSTRING(C3,3)。

十、Excel函数：RMB

功能：用货币格式将数值转换为文本字符。

语法结构：=RMB(值或单元格引用,[小数位数])。

当省略“小数位数”时，默认保留2位小数。

目的：将“月薪”转换为货币格式。

方法：

在目标单元格中输入公式：=RMB(C3)、=RMB(C3,1)。

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com