|
函数和公式，是Excel中的灵魂，也是非常实用的技巧，如果你对常用的函数公式能够熟练地掌握，对于提高工作效率将会有很大的帮助！ 一、“重复”类。 1、查找重复内容，重复即提示。 方法： 在目标单元格中输入公式：=IF(COUNTIF(E:E,E3)>1,'重复','')。 解读： 先用Countif函数统计当前单元格的值在E列中的数量，然后用If函数进行判断，如果大于1，返回“重复”，否则返回空值。 2、重复内容首次出现时不予提示。 方法： 在目标单元格中输入公式：=IF(COUNTIF(E$3:E3,E3)>1,'重复','')。 解读： 将本示例中的公式和上一示例中的公式进行对比，结果只是参数做了修改，将原来的范围E:E变为E$3：E3，而统计结果却不相同，其实范围E:E是绝对引用，暨统计范围为E列的所有单元格，而为了达到“重复内容”首次出现时不予提示的目的，我们将统计范围定位从开始单元格到当前单元格，所以，重复内容首次出现时不予提示。 3、重复内容首次出现时提示重复。 方法： 在目标单元格中输入公式：=IF(COUNTIF(E3:E99,E3)>1,'重复','')。 解读： 1、从公示的计算过程请参阅示例2的解读。 2、此公式在应用时要区分对待，如果重复内容进出线一次，则重复内容仅在首次出现时提示，如果有多次重复，仅最后一次不提示重复。 二、身份证号类。 1、根据出生年月计算年龄。 方法： 在目标单元格中输入公式：=DATEDIF(E3,TODAY(),'y')。 解读： Datedif函数为系统隐藏函数，其主要作用为按指定的统计方式统计两个日期之间相差的值。其中“y”的意思为按年统计。 2、根据身份证号码提取出生年月。 方法： 在目标单元格中输入公式：=TEXT(MID(D3,7,8),'00-00-00')。 解读： 1、首先利用Mid函数提取D3字符串中从第7位开始，长度为8的字符。 2、利用Text函数将其转换为“00-00-00”的格式。 3、根据身份证号码判断性别。 方法： 在目标单元格中输入公式：=IF(MOD(MID(D3,17,1),2),'男','女')。 解读： 1、首先用Mid函数提取D3字符串中从17位开始，长度为1，暨第17位的数字。 2、利用Mod函数求余。 3、利用If函数判断，余数为1，暨第17位的数为奇数，则返回“男”、如果余数为0，暨第17位的数为偶数，则返回“女”。 三、常用汇总函数。 方法： 在对应的目标单元格中输入公式：=SUM(D:D)、=MAX(D:D)、=MIN(D:D)、=AVERAGE(D:D)、=COUNTA(B3:B9)。 四、成绩排名。 1、美式排名。 方法： 在目标单元格中输入公式：=RANK.EQ(D3,D:D)。 解读： 从排序的结果来看，出现了“跳跃”的情况，如果你对这一结果不满意，请继续往下阅读。 2、中国式排名。 方法： 在目标单元格中输入公式：=SUMPRODUCT(($D$3:$D$9>D3)/COUNTIF($D$3:$D$9,$D$3:$D$9)) 1。 解读： 从排序的结果中我们可以看出，名次没有“跳跃”的情况，是不是更符合我们的实际需要了？ 五、个数类统计。 1、单条件计数。 方法： 在目标单元格中输入公式：=COUNTIF(D3:D9,'>='&G3)。 解读： Countif函数为单条件计数函数，其语法结构为=Countif（统计范围，统计条件）。 2、连续范围计数。
方法： 1、在目标单元格中输入公式：=FREQUENCY(D3:D9,G3:G6)。 2、Ctrl Shift Enter填充。 解读： 1、函数Frequency的作用为：以一列垂直数组返回一组数据的频率分布。公式中D3:D9为数据，G3:G6为垂直数组。 2、因为要循环多次计算，所以填充时用Ctrl Shift Enter，而不是常规的Ctrl Enter哦！ 3、结果解读：30分以下的个数为0；31-40的个数为1；41-50的个数为：0；51-60的个数为：3；61-70的个数为：3人。 3、单条件统计平均值。
方法： 在目标单元格中输入公式：=AVERAGEIF(C3:C9,G3,D3:G9)。 解读： 函数Averageif为按条件计算平均值函数，语法结构为：=Averageif(条件范围，条件，数值范围）。 4、多条件统计平均值。
方法： 在目标单元格中输入公式：=AVERAGEIFS(D3:D9,C3:C9,G3,D3:D9,'>='&H3)。 解读： Averageif是函数为多条件计算平均值，其语法结构为：=Averageifs(数值范围，条件1范围，条件1，条件2范围，条件2……条件N范围，条件N)。 六、统计不重复值。
方法： 在目标单元格中输入公式：=SUMPRODUCT(1/COUNTIF(E3:E9,E3:E9))。 七、提取不重复的内容。
方法： 1、在目标单元格中输入公式：=INDEX(E:E,2 MATCH(,COUNTIF(F$2:F2,E$3:E$9),))&''。 2、用Ctrl Shift Enter填充。 3、拖动填充柄填充其他单元格。 结束语： 本文针对不同的情况，对常用的函数公式进行了罗列，对简单的用法也进行了讲解和示范，如果能够熟练掌握，对于工作效率的提高绝对不是一点点哦！ 学习过程中如果有不同的见解，欢迎大家在留言区留言讨论哦！ |
|
|
