七、其他实用公式
（一）判断性别
在 Excel 中，可以利用 MID 和 MOD 函数根据身份证号码判断性别。众所周知，身份证第 17 位数字表示性别：奇数表示男性，偶数表示女性。方法一：直接在单元格输入公式=IF(MOD(MID(A2,17,1),2),"男","女")即可。其中MID(A2,17,1)说明截取 A2 单元格中第 17 位数；MOD(MID(A2,17,1),2)说明 MOD 求余函数，MOD(MID(A2,17,1),2)表示中间截取的这个数除以 2 后是不是奇数；IF：若为奇数，则为男，否则为女。
方法二：直接在单元格输入公式=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")。其中LEFT说明取 A2 单元格的左边 17 位；RIGHT取上一步结果的右边 1 位；MOD 取上一步结果除以 2 的余数；IF 判断结果是奇数是男，否则是女。
方法三：输入公式=TEXT(-1^MID(A2,17,1),"女;男")。这里MID从身份证号码第 17 位的数字开始提取 1 个数字，TEXT利用 TEXT 函数将数字，如果是奇数则转化为男，如果是偶数转换为“女”。
（二）提取不重复名单
UNIQUE 和 FILTER 函数可以提取指定条件不重复名单。按单个条件提取不重复数据：例如有一个假期值班表格，需要根据所属“门店”这个条件，筛选出不重复的“值班经理”名单。在目标单元格中输入公式=UNIQUE(FILTER(B2:B9,A2:A9=E2,"无数据"))然后点击回车即可。公式中首先通过 FILTER 函数，按条件筛选出指定门店的值班经理名单，然后再通过 UNIQUE 函数提取出不重复的名单数据即可。
多条件提取不重复数据：如果需要多个条件同时满足，就用*把多个条件连接，例如(A2:A9=E2)*(C2:C9=D2)；如果需要多个条件满足任意一个，就用+把多个条件连接，例如(A2:A9=E2)+(C2:C9=D2)。
（三）XLOOKUP 公式
XLOOKUP 公式是最新版的 Excel 出来的新公式，用来替换原有的 Vlookup 公式，非常强大，而且简单易学。基本用法：XLOOKUP 函数公式是由 6 个参数组成，前 3 个参数必填，后 3 个参数选填，使用用法=xlookup(查找值，查找区域，结果区域，查找不到，匹配模式，搜索模式)。例如，根据姓名，查找工资数据，查找值是 F2 单元格，查找区域是 A 列，结果区域是 D 列，只需要输入公式=XLOOKUP(F2,A:A,D:D)。
逆向查找匹配：因为它在查找时，是没有方向的，所以也可以轻松进行逆向查找匹配，例如，当我们需要根据姓名匹配岗位时，只需要输入公式=XLOOKUP(F2,B:B,A:A)。
一次性匹配多列数据：例如，想根据条件，一次性的匹配多列结果数据，结果不需要输入多条公式，只需要将结果列参数设置为多列即可，输入公式=XLOOKUP(F2,A:A,B:D)，第 3 个参数输入 B:D，3 列的结果，所以会一次性匹配出 3 个数据。
多条件查找匹配：例如，需要根据两个条件，姓名和月份，来查找匹配工资数据，只需要把查找数据相连，查找区域相连，输入公式=XLOOKUP(F2&G2,A:A&C:C,D:D)。
模糊查找匹配：例如，想根据公司 kpi 得分情况，给员工进行评级，建立辅助列，把每个档位最低标准分列出来，然后在第 5 个参数补上 -1，表示模糊查找，即=XLOOKUP(B2,E:E,G:G,,-1)。Vlookup 公式在模糊查找的时候，需要升序排列，但是 xlookup 公式是不需要的。
从下向上匹配：举个例子，有一项考试，可以多次参加，但是我们只取最后一次考试成绩，默认是从上向下查找匹配，返回第一次出现的结果，如果想从下向上查找，返回最后一次出现的结果，那可以在第 6 参数，填写 -1，表示从下向上匹配，输入公式=XLOOKUP(E2,A:A,C:C,,,-1)。
（四）Filter 公式
Filter 函数虽然已经出来很久了，但目前仅存在于 office2021 和 365 版本。FILTER 单是“过滤、筛选”的意思，顾名思义，FILTER 函数的作用是：根据给定的条件筛选出对应的数据。一对多查询：如下图所示，希望根据 F2 单元格中指定的“课程”，提取出左侧列表中“金融学”的所有人员姓名。G2 单元格输入公式=FILTER(A2:A12,B2:B12=F2)，按回车，公式结果会自动溢出到其他单元格。
多对多查询：希望提取出课程为“金融学”，并且人数为“45”的所有记录。H2 单元格输入公式=FILTER(A2:A12,(B2:B12=F2)*(C2:C12=G2))，按回车。
提取包含关键字的记录：希望查询部门中包含关键字“习”的所有姓名。F2 单元格输入公式=FILTER(A2:A12,ISNUMBER(FIND(E2,C2:C12)))，按回车。提示：Isnumber 函数是 Excel 中一个重要且常用的函数。它的作用是判断一个值是否为数值，并返回逻辑值结果（TRUE 或 FALSE）。
提取另一列没有出现的人员：希望从 A 列的姓名列表中，提取出没有在 C 列出现的姓名。E2 单元格输入公式=FILTER(A2:A12,COUNTIF(C2:C4,A2:A12)=0)，按回车。
提取指定条件的不重复名单：某学院组织体育比赛。希望从左侧的列表中，提取出宣传部的参赛人员名单。在 F2 单元格输入公式=UNIQUE(FILTER(A2:A12,C2:C12=E2))，按回车。提示：UNIQUE 函数从单元格区域中提取出不重复的记录。
（五）UNQIUE 公式
UNIQUE 函数可以去除重复值保留唯一值。语法=UNIQUE(数组，[按列]，[仅出现一次])。提取一行中的不重复记录：如下图，要从左侧的值班表中，提取出各部门的值班人员名单。H2 输入以下公式，向下复制到 H4 单元格即可。=UNIQUE(B2:F2,TRUE)。UNIQUE 函数的第二参数使用 TRUE，表示在同一行中提取不重复值。
提取一列中的不重复值：如下图所示，希望从 B 列的值班名单中提取出不重复记录。D2 单元格输入以下公式即可。=UNIQUE(B2:B6)。UNIQUE 函数第二参数使用 FALSE 或者省略参数，表示在同一列中提取不重复值。
提取一列中的唯一值：如下图所示，希望从 B 列的值班名单中提取出仅出现一次的记录。=UNIQUE(B2:B6,,TRUE)。UNIQUE 函数第二参数省略参数，第三参数使用 TRUE，表示在同一列中提取仅出现一次的值。
在多列姓名中提取人员名单：如下图所示，需要从 B~F 列的值班名单中提取出员工名单。H2 单元格输入以下公式=UNIQUE(TOCOL(B2:F7,1))。首先使用 TOCOL 函数将 B2:F7 中的姓名转换为一列，TOCOL 函数的第二参数使用 1，表示忽略空白单元格。目前该函数仅支持 Excel 365 用户使用。接下来使用 UNIQUE 函数提取出不重复的记录。
计算参赛人数：如下图所示，AB 列是参赛名单，有部分人员参加了多个项目，需要计算参赛人数。D2 单元格输入以下公式。=COUNTA(UNIQUE(A2:A9))。先使用 UNIQUE 函数提取出不重复的人员名单，再使用 COUNTA 函数对人员名单计数。
按条件提取不重复记录：如下图所示，希望从左侧的值班名单中提取出“A区”的不重复记录。F2 单元格输入以下公式。=UNIQUE(FILTER(C2:C14,A2:A14="A区"))。首先使用 FILTER 函数，筛选出所有 A 区的值班经理名单，再使用 UNIQUE 函数提取出不重复的记录。
中式排名：中式排名的特点是相同成绩不占用名次。如下图所示，E2 单元格输入以下公式，能够根据 C 列的比赛成绩计算出中式排名。=SUM((UNIQUE(C$2:C$9)>C2)*1)+1。上图中 C2 和 C3 单元格都是 99.5，并列名次为 3，E6 和 C7 单元格中的 95，名次为 4。公式先使用 UNIQUE 函数提取出 C 9 单元格区域中的不重复记录，再判断去重后的成绩是否大于 C2。SUM 函数计算出大于 C2 的不重复个数，结果加上 1 就是当前成绩的中式排名。
（六）CHOOSECOLS 公式
CHOOSECOLS 公式在选择列方面具有实用价值。它可以根据给定的索引选择工作表中的列。例如，如果要从一个包含多列数据的工作表中选择特定的几列，可以使用 CHOOSECOLS 函数。其语法为CHOOSECOLS(array, index1, [index2],...)，其中array是要从中选择列的数组或范围，index1、index2等是要选择的列的索引。这个函数在处理大型数据集时特别有用，可以快速选择特定的列进行分析或操作。例如，如果有一个包含销售数据的工作表，其中包含日期、产品名称、销售数量、销售金额等列，现在想要选择销售数量和销售金额两列进行分析，可以使用公式=CHOOSECOLS(A1:D10,3,4)，其中 A1:D10 是数据范围，3 和 4 分别是销售数量和销售金额的列索引。这样就可以快速选择所需的列进行进一步的计算或分析。想了解更多精彩内容，快来关注