Excel公式全集：从数据处理到成绩分析

"Excel表格公式大全" 在Excel中，公式是数据分析和处理的关键工具，能够帮助用户快速计算、检查数据并得出有用的信息。本摘要涵盖了多个实用的Excel公式，涉及数据验证、日期处理、身份证信息提取、统计计算等多个方面。 1. **查找重复内容公式**： ```excel =IF(COUNTIF(A:A,A2)>1,"重复","") ``` 这个公式用于检测某一列（如A列）中是否存在重复的值。如果A2单元格的值在A列中出现超过一次，那么结果会显示"重复"，否则显示空字符串。 2. **计算年龄**： ```excel =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0) ``` 该公式基于360天的会计年计算年龄，H6单元格包含出生日期，"2009/8/30"为计算时的日期，FALSE表示不使用美国式的闰年处理。结果将四舍五入到最接近的整数。 3. **从身份证号获取出生日期**： ```excel =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2)) ``` 这个公式从18位的身份证号码中提取出生年月日，年份是7到10位，月份是11到12位，日期是13到14位，然后组合成日期格式。 4. **根据身份证号提取性别**： ```excel =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女")) ``` 公式检查身份证号的第15位（旧版15位身份证）或第17位（新版18位身份证），如果该位置的数字是奇数，则表明是男性，偶数则为女性。 5. **基本统计计算**： - 求和：`=SUM(K2:K56)` 对K2到K56区域求和。 - 平均数：`=AVERAGE(K2:K56)` 计算K2到K56区域的平均值。 - 排名：`=RANK(K2, K$2:K$56)` 对55名学生的成绩进行排名，K2是当前排名的学生分数，K$2:K$56是排名范围。 - 等级：`=IF(K2>=85, "优", IF(K2>=74, "良", IF(K2>=60, "及格", "不及格")))` 根据分数划分等级。 - 学期总评：`=K2*0.3 + M2*0.3 + N2*0.4` 假设K列、M列和N列分别代表平时总评、期中和期末成绩，按照一定权重计算总分。 - 最高分：`=MAX(K2:K56)` 找出K2到K56区域的最高分。 - 最低分：`=MIN(K2:K56)` 找出K2到K56区域的最低分。 6. **分数段人数统计**： - `(1)` 求100分人数：`=COUNTIF(K2:K56, "100")`。 - `(2)` 求95~99.5分人数：`=COUNTIF(K2:K56, ">=95") - K57`，其中K57为100分的人数。 - `(3)` 求90~94.5分人数：`=COUNTIF(K2:K56, ">=90") - SUM(K57:K58)`，K58为95~99.5分的人数。 - `(4)` 求85~89.5分人数：`=COUNTIF(K2:K56, ">=85") - SUM(K57:K59)`，K59为90~94.5分的人数。 - `(5)` 求70~84.5分人数：`=COUNTIF(K2:K56, ">=70") - SUM(K57:K60)`，K60为85~89.5分的人数。 这些公式展示了Excel在处理数据时的强大功能，包括逻辑判断、数值计算、文本操作和统计分析等，为日常的数据管理提供了便利。熟练掌握这些公式可以极大地提高工作效率，使得数据处理更加准确和高效。