|
1. 查找重复值 公式：=IF(COUNTIF(A$2:A2,A2)=1,"","重复") 首选我们利用countif函数进行条件计数，然后使用if函数进行判断当其结果等于1时代表不重复，当不等于1时候代表重复  2. 身份证号码提取出生日期 公式：=--TEXT(MID(A2,7,8),"0-00-00") 首先利用mid函数提取出身份证号码中的出生年月，然后利用text函数设置为日期格式，最后利用两个减号，将文本格式转换为日期格式，为什么还需要转换格式呢？因为text是文本函数，经过text函数转换过的数据都是文本格式  3. 身份证号码中提取性别 公式：=IF(MOD(MID(A2,17,1),2)=1,"男","女") 性别只与身份证号码第17位有关系，当第17位为奇数则为男，为偶数则为女，所以我们利用mid函数提取第17位数字，然后利用mod函数判断奇偶，最后利用if函数判断  4. 身份证号码中提取年龄 公式：=DATEDIF(B2,TODAY(),"y") DATEDIF函数是一个隐藏函数，用于计算两个日期时间差  5. vlookup进行多条件查找 公式：｛=VLOOKUP(E2&F2,IF({1,0},A2:A10&B2:B10,C2:C10),2,0)｝ 第一参数：我们将查找值通过使用&字符合并为一项 第二参数：我们利用if函数和数组构建一个二维数组，如下图所示 第三参数：2，在二维数组中得分在第二列，所以为2， 第四参数为0，为精确匹配  6. 使用vlookup进行反向查找 公式：=VLOOKUP(E2,IF({1,0},C2:C10,A2:A10),2,0) 我们都知道使用vlookup函数一般是从左往右查找，当我们想从右往左查找就要用到vlooup函数的反向查找，与多条件查找类似，都是构建二维数组进行查找  7. 隔行求和 公式：=SUMPRODUCT((MOD(ROW(C2:L7),2)=1)*C2:L7) 首先我们利用mod函数判断为奇数列的行号，然后在利用SUMPRODUCT的数组特性进行求和  8. 隔列求和 公式：=SUMPRODUCT((MOD(COLUMN(B3:G12),2)=1)*B3:G12) 跟隔行求和类似，先判断奇数列号，然后进行求和  9. 统计不重复个数 公式：=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)) 首先利用countif条件计数，算出重复次数，然后利用1除以重复数，这里为什么用1除以呢，比如当数据出现两次，countif函数就会算出两个2，然后用1分别除以两个2结果为1/2最后利用SUMPRODUCT函数，两个1/2会相加得到1  10. 中国式排名 公式：=SUMPRODUCT(($B$2:$B$8>B2)*1)+1 使用SUMPRODUCT判断成绩大于它自己本身的数据，因为没有等于所有加1  11，单条件计数 公式：=SUMPRODUCT((B2:B16=$G$3)*1) 首先判断部门所在区域等于成型车间的单元格，当正确时返回true可以看做是1，错误时返回false可以看做是0，最后在结果后乘以1  12. 可见单元格求和 公式：=SUBTOTAL(109,B2:B10) 当SUBTOTAL的第一参数为100以上的时候，就会仅对可见区域求和 13. 双向查找 公式：=INDEX(A1:E10,MATCH(G2,A1:A10,0),MATCH(H2,A1:E1,0)) 利用match函数分别找到姓名以及科目所在的行列标号，然后利用index函数取出结果 14. 提取左边的字符串 公式：=-LOOKUP(1,-LEFT(D2,ROW($1:$30))) 首先我们使用ROW($1:$30)构建一个1到30的序列数组，如果你的数据比较长可以适当增大，然后利用left函数对字符串提取30次，我们又在left函数前面添加了负号，而又在其前面添加了负号将提取数据转换为数值当提取出来的数据为文本是会返回错误值，因为使用lookup函数函数默认是升序排列的所以函数会返回最后一个正确的值，而最后一个正确值恰恰是我们要提取的值，最后添加负号将负数转换为正数 15. 提取右边的数据 公式：=-LOOKUP(1,-RIGHT(A2,ROW($1:$30))) 这个跟从左区域数据是一样的，只不过是将left函数换成了right函数 怎么样，这15个函数套路你你都知道吗 我是Excel从零到一，关注我持续分享更多excel技巧 你们的点赞关注和转发是对我最大的肯定 |
|
|
