小伙伴们好啊,今天咱们分享一组简单实用的公式套路。 1、根据身份证号判断性别 使用以下公式,可以根据身份证号判断对应的性别信息: =IF(MOD(MID(B2,17,1),2),'男','女') 身份证号码中的第17位是性别信息,单数表示男,双数表示女。 首先使用MID函数,从B2单元格的第17位开始,提取出1个字符。 再使用MOD函数计算这个字符与2相除的余数。 最后使用IF函数判断,如果余数不等于0,返回男,如果余数是0,则返回女。 2、计算指定区间的销售额 如下图所示,要计算从1月份到指定月份的累计销售额。 F4单元格公式为: =SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0))) MATCH(F2,A2:A13,0)部分,先使用MATCH函数计算出F2单元格中的月份在A2:A13中的位置,结果为9. OFFSET函数以B2单元格为基点,向下偏移0行,向右偏移0列,以MATCH函数的计算结果作为新引用的行数,最终得到B2:B10单元格区域的引用。 再使用SUM函数计算这个区域中的总和,得到从1月份到指定月份的销售总额。 3、三个条件的判断 如下图所示,要根据B~C列两年的数据,判断变化差异。 D2单元格输入以下公式: =TEXT(C2-B2,'比上年多0元;比上年少0元;与上年相同') TEXT函数第二参数使用'比上年多0元;比上年少0元;与上年相同',意思是: 如果C2-B2的结果大于0,就显示“比上年多n元”。 如果C2-B2的结果小于0,就显示“比上年少n元”。 如果C2-B2的结果等于0,就显示“与上年相同”。 TEXT函数格式代码中的0有特殊含义,通常表示第一参数本身的数值。 4、按部门添加序号 如下图所示,要在A列按部门输入序号,不同部门的序号从1开始。 在A2单元格输入公式,向下复制: =COUNTIF(B$2:B2,B2) COUNTIF函数的统计区域是B$2:B2,第一个B2是行绝对引用,第二个B2,是相对引用。当公式向下复制时,就会变成B$2:B3、B$2:B4……一个不断扩展的区域,从这个动态区域中统计B列部门的个数。 5、模糊查询等级 如下图,要根据B列销售业绩返回对应的评定标准,E~F列为标准对照表。 C2单元格公式为: =LOOKUP(B2,$E$3:$F$6) 这种方法可以取代IF函数完成多个区间的判断查询,前提是对照表的首列必须是升序处理。 好了,今天咱们的分享就是这些,祝大家一天好心情! 图文制作:祝洪忠 |
|