工作中最常用的10个excel函数公式 快收藏！

本教程列出了我们常见的Excel电子表格公式和对应的案例，供大家学习。

01

查找重复内容。

我们在C2单元格中输入公式：=IF(COUNTIF(A:A，A2)1，“重复”、“”)，我们可以在a列中找出重复的名称。

02

Excel使用出生日期来计算年龄。

在C2单元格中输入公式：=trunc ((days360 (B2，今日))(false))/360，0)。

虽然上面的方法还可以，但是稍微有点复杂，所以我们可以直接使用DATEDIF函数，公式为：=datedif (b2，today()，“y”)。

03

Excel提取出生日期。

计算出生日期的公式是从输入的18位身份证号码中提取的。我们在C2单元格中输入公式：=concatenate (mid (B2，7，4)，"/，mid (B2，11，2)，"/，mid (B2，13，2))。

虽然该方法可以使用，但它与以前的技术有相同的问题，过于复杂。这里我们可以把TEXT函数和MID函数结合起来，公式为=TEXT(MID(B2，7，8)，“0-00-00”)。是不是更简单？

04

Excel提取性别。

让Excel自动从输入的身份证号中提取性别。我们在C2单元格中输入公式：=if (len (B2)=15，if (mod (mid (B2，15，1)，2)=1，“男”，“女”)，if (mod (mid (B2，17，1)，

这里，我推荐一个比较简单的判断奇偶的方法。其实不需要用到MOD功能。excel有判断奇偶性的特殊功能，即ISODD和ISEVEN。你可以自己研究这两个函数。这里以ISODD为例。当数字为奇数时，返回真。所以这里的公式可以是：=IF(ISODD(MID(B217，1))，“男”，“女”)。

05

Excel总和、平均值、等级、

排名，最高分，最低分。

D2单元格求和公式：=SUM(B2:B12)。

E2单元格平均公式：=AVERAGE(B2:B12)。

寻找F2单元格最高分的公式：=MAX(B2:B12)。

G2单元格最低分公式：=MIN(B2:B12)。

h列的排名公式：=RANK(B2，$B$2:$B$12)。

第一列的秩算法公式：=IF(B2=85，“优秀”，IF(B2=74，“良好”，IF(B2=60，“通过”，“失败”))。

不知道大家是否知道不朽函数——AGGREGATE，它不仅可以实现SUM、AVERAGE、COUNT、LARGE等19个函数的功能，还可以忽略隐藏行、错误值、空值。

06

条件格式的使用。

在excel中，当某个单元格满足特定条件时，如何在另一个单元格中显示特定颜色，如：A11时，C1显示红色；0

方法如下：单击单元格C1，然后单击格式条件格式：

1.条件1设置为：公式=A11，点击格式-字体-颜色，点击红色，然后点击确定。

2.条件2设置为：公式=AND(A10，A11)，单击格式-字体-颜色，单击绿色，然后单击确定。

3.条件3设置为：公式=A10，单击格式-字体-颜色，单击黄色，然后单击确定。

4.设置三个条件后，单击确定。

07

数据有效性的使用。

如何控制每一列数据的长度，避免在EXCEL中重复录入？

1.用数据有效性定义数据长度。

用鼠标选择要输入的数据范围，点击数据-有效性-设置，有效性条件设置为允许，文本长度等于5(具体条件可根据需要更改)。

还可以定义一些提示信息、错误警告信息以及是否打开中文输入法等。然后单击确定。

2.避免条件格式的重复。

选择A列，点击格式-条件格式，将条件设置为公式=COUNTIF($A:$A，$A1)1，点击格式-字体-颜色，选择红色后点击确定两次。

设置完成后，如果输入数据长度不正确，系统会提示，如果数据重复，字体会变红。

08

巧用函数组合多条件计数。

统计“班级”为“二班”，“语文成绩”大于等于104，“录取结果”为“重复”的人数。

公式：=sum (if=SUM(IF((B2:B9999=“二档”)*(c 2: c 999=104)*(d 2:d 999=“重复”)，1，0))。

输入公式后，按Ctrl+Shift+Enter自动添加数组公式符号“{}”。

当然，这里也可以直接使用多条件计数函数，直接得到：=COUNTIFS(B:B，“类2”，c:c，“=104”，d:d，“重复”)。

09

求某一区域内非重复数据的个数。

例如，找出A2:A12范围内不重复的名字数，如果重复出现，则只计算一个名字。有两种计算方法：

首先使用数组公式：=sum (1/countif (a2:a12，a23360a12))，输入公式后按Ctrl Shift Enter，让其自动添加数组公式符号“{}”。

其次，使用乘积求和函数：=sum product(1/countif(a2:a12，a 2: a 12))。

10

制作Excel目录工作表。

Excel工作簿中有许多工作表。如何快速整理目录工作表？

第一步：Ctrl F3显示自定义名称对话框，命名为X，在“参考位置”框中输入：

=mid (get.workbook (1)，find ("]"，get.workbook (1)) 1，100)，并确认。

第二，使用HYPERLINK函数批量插入链接。方法：输入公式：=超链接(“#”)索引(x，row())“！在目录的A2单元格中(通常是第一页)。A1 "，索引(x，row())，向下填充公式，直到出现错误，并生成目录。