Excel 常函数公式全
1 查找重复容公式： IF(COUNTIF(AAA2)>1 重复 )
2 出生年月计算年龄公式： TRUNC((DAYS360(H62009830FALSE))3600)
3 输入 18 位身份证号出生年月计算公式： CONCATENATE(MID(E274)MID(E2112)MID(E2132))
4 输入身份证号码系统动提取性输入公式：
IF(LEN(C2)15IF(MOD(MID(C2151)2)1 男 女 )IF(MOD(MID(C2171)2)1 男 女 )) 公式 C2 代表输入身份证号码单元格
1 求： SUM(K2K56) —— K2 K56 区域进行求
2 均数： AVERAGE(K2K56) —— K2 K56 区域求均数
3 排名： RANK(K2 K2K56) —— 55 名学生成绩进行排名
4 等级： IF(K2>85 优 IF(K2>74 良 IF(K2>60 格 格 )))
5 学期总评： K2*03+M2*03+N2*04 —— 假设 K 列 M 列 N 列分存放着学生 时总评 期中 期末 三项成绩
6 高分： MAX(K2K56) —— 求 K2 K56 区域（ 55 名学生）高分
7 低分： MIN(K2K56) —— 求 K2 K56 区域（ 55 名学生）低分
8 分数段数统计：
（ 1 ） COUNTIF(K2K56100) —— 求 K2 K56 区域 100 分数假设结果存放 K57 单元格
（ 2 ） COUNTIF(K2K56>95) － K57 —— 求 K2 K56 区域 95 ～ 995 分数假设结果存放 K58 单元格
（ 3 ） COUNTIF(K2K56>90) － SUM(K57K58) —— 求 K2 K56 区域 90 ～ 945 分数假设结果存放 K59 单元格
（ 4 ） COUNTIF(K2K56>85) － SUM(K57K59) —— 求 K2 K56 区域 85 ～ 895 分数假设结果存放 K60 单元格
（ 5 ） COUNTIF(K2K56>70) － SUM(K57K60) —— 求 K2 K56 区域 70 ～ 845 分数假设结果存放 K61 单元格
（ 6 ） COUNTIF(K2K56>60) － SUM(K57K61) —— 求 K2 K56 区域 60 ～ 695 分数假设结果存放 K62 单元格
（ 7 ） COUNTIF(K2K56<60) —— 求 K2 K56 区域 60 分数假设结果存放 K63 单元格
说明： COUNTIF 函数计算某区域男女生数
： COUNTIF(C2C351 男 ) —— 求 C2 C351 区域（ 350 ）男性数
9 优秀率： SUM(K57K60)55*100
10 格率： SUM(K57K62)55*100
11 标准差： STDEV(K2K56) —— 求 K2 K56 区域 (55 ) 成绩波动情况（数值越说明该班学生间成绩差异较反说明该班存两极分化）
12 条件求： SUMIF(B2B56 男 K2K56) —— 假设 B 列存放学生性 K 列存放学生分数函数返回结果表示求该班男生成绩
13 条件求： ｛ SUM(IF(C3C322 男 IF(G3G322110))) ｝ —— 假设 C 列（ C3C322 区域）存放学生性 G 列（ G3G322 区域）存放学生班级代码（ 1 2 3 4 5 ）函数返回结果表示求班男生数数组函数输完 Ctrl ＋ Shift ＋ Enter 组合键 ( 产生 ｛ …… ｝ ) ｛｝ 手工输入组合键产生
14 根出生日期动计算周岁： TRUNC((DAYS360(D3NOW( )))3600)
——— 假设 D 列存放学生出生日期 E 列输入该函数产生该生周岁
15 Word 中三窍门：
① 连续输入三 ~ 条波浪线
② 连续输入三 条直线
连续输入三 条双直线
excel 中某单元格符合特定条件单元格显示特定颜色：
A1 〉 1 时 C1 显示红色
0A1<0 时 C1 显示黄色
方法：
1 单元击 C1 单元格点 格式 > 条件格式 条件 1 设：
公式 A11
2 点 格式 > 字体 > 颜色 点击红色点 确定
条件 2 设：
公式 AND(A1>0A1<1)
3 点 格式 > 字体 > 颜色 点击绿色点 确定
条件 3 设：
公式 A1<0
点 格式 > 字体 > 颜色 点击黄色点 确定
4 三条件设定点 确定 出
二 EXCEL 中控制列数长度避免重复录入
1 数效性定义数长度
鼠标选定输入数范围点 数 > 效性 > 设置 效性条件 设成 允许 文长度 等 5 （具体条件根需改变）
定义提示信息出错警告信息否开中文输入法等定义点 确定
2 条件格式避免重复
选定 A 列点 格式 > 条件格式 条件设成 公式 COUNTIF(AAA1)>1 点 格式 > 字体 > 颜色 选定红色点两次 确定
样设定输入数果长度会提示果数重复字体会变成红色
三 EXCEL 中 B 列 A 列处标识出？
（）果求 A B 两列行数相较：
假定第行表头单击 A2 单元格点 格式 > 条件格式 条件设
单元格数值 等 B2
点 格式 > 字体 > 颜色 选中红色点两次 确定
格式刷 A2 单元格条件格式复制
B 列参方法设置
（二）果 A 列 B 列整体较（相数行）：
假定第行表头单击 A2 单元格点 格式 > 条件格式 条件设
公式 COUNTIF(BBA2)0
点 格式 > 字体 > 颜色 选中红色点两次 确定
格式刷 A2 单元格条件格式复制
B 列参方法设置
方法设置 AB 列均数着色 A 列 B 列者 B 列 A 列数标记红色字体
四 EXCEL 中样批量处理行排序
假定量数 ( 数值 ) 需行排序操作？
行排序列排序关键字 关键字相时次关键字排序问题排序解决解决方法：
1 假定数 A E 列请 F1 单元格输入公式：
LARGE(A1E1COLUMN(A1))
填充柄公式右复制相应范围
原数行排序出现 F J 列需 选择性粘贴 数值 复制方
注：第 1 步公式根实际情况（数范围）作相应修改果排序 公式改 SMALL(A1E1COLUMN(A1))
五巧函数组合进行条件计数统计
例：第行表头 A 列 姓名 B 列 班级 C 列 语文成绩 D 列 录取结果 现统计 班级 二 语文成绩 等 104 录取结果 重 数统计结果存放工作表列
公式：
SUM(IF((B2B9999 二 )*(C2C9999>104)*(D2D9999 重 )10))
输入完公式 Ctrl+Shift+Enter 键 动加数组公式符号 {}
六判断单元格里否包含指定文？
假定 A1 单元格进行判断 指定文 公式均
IF(COUNTIF(A1*& 指定文 &*)1 )
IF(ISERROR(FIND( 指定文 A11)) )
求某区域重复数数
例求 A1A100 范围重复数数某数重复次出现算两种计算方法：
利数组公式：
SUM(1COUNTIF(A1A100A1A100))
输入完公式 Ctrl+Shift+Enter 键 动加数组公式符号 {}
二利积求函数：
SUMPRODUCT(1COUNTIF(A1A100A1A100))
七工作薄中许工作表快速整理出目录工作表
1 宏 30 取出工作表名称方法：
Ctrl+F3 出现定义名称话框取名 X 引位置 框中输入：
MID(GETWORKBOOK(1)FIND(]GETWORKBOOK(1))+1100)
确定
2 HYPERLINK 函数批量插入连接方法：
目录工作表（般第 sheet ） A2 单元格输入公式：
HYPERLINK(#＇&INDEX(XROW())&＇A1INDEX(XROW()))
公式填充直出错止目录生成
文档香网(httpswwwxiangdangnet)户传
《香当网》用户分享的内容，不代表《香当网》观点或立场，请自行判断内容的真实性和可靠性！
该内容是文档的文本内容，更好的格式请下载文档
Excel 常函数公式全
1 查找重复容公式： IF(COUNTIF(AAA2)>1 重复 )
2 出生年月计算年龄公式： TRUNC((DAYS360(H62009830FALSE))3600)
3 输入 18 位身份证号出生年月计算公式： CONCATENATE(MID(E274)MID(E2112)MID(E2132))
4 输入身份证号码系统动提取性输入公式：
IF(LEN(C2)15IF(MOD(MID(C2151)2)1 男 女 )IF(MOD(MID(C2171)2)1 男 女 )) 公式 C2 代表输入身份证号码单元格
1 求： SUM(K2K56) —— K2 K56 区域进行求
2 均数： AVERAGE(K2K56) —— K2 K56 区域求均数
3 排名： RANK(K2 K2K56) —— 55 名学生成绩进行排名
4 等级： IF(K2>85 优 IF(K2>74 良 IF(K2>60 格 格 )))
5 学期总评： K2*03+M2*03+N2*04 —— 假设 K 列 M 列 N 列分存放着学生 时总评 期中 期末 三项成绩
6 高分： MAX(K2K56) —— 求 K2 K56 区域（ 55 名学生）高分
7 低分： MIN(K2K56) —— 求 K2 K56 区域（ 55 名学生）低分
8 分数段数统计：
（ 1 ） COUNTIF(K2K56100) —— 求 K2 K56 区域 100 分数假设结果存放 K57 单元格
（ 2 ） COUNTIF(K2K56>95) － K57 —— 求 K2 K56 区域 95 ～ 995 分数假设结果存放 K58 单元格
（ 3 ） COUNTIF(K2K56>90) － SUM(K57K58) —— 求 K2 K56 区域 90 ～ 945 分数假设结果存放 K59 单元格
（ 4 ） COUNTIF(K2K56>85) － SUM(K57K59) —— 求 K2 K56 区域 85 ～ 895 分数假设结果存放 K60 单元格
（ 5 ） COUNTIF(K2K56>70) － SUM(K57K60) —— 求 K2 K56 区域 70 ～ 845 分数假设结果存放 K61 单元格
（ 6 ） COUNTIF(K2K56>60) － SUM(K57K61) —— 求 K2 K56 区域 60 ～ 695 分数假设结果存放 K62 单元格
（ 7 ） COUNTIF(K2K56<60) —— 求 K2 K56 区域 60 分数假设结果存放 K63 单元格
说明： COUNTIF 函数计算某区域男女生数
： COUNTIF(C2C351 男 ) —— 求 C2 C351 区域（ 350 ）男性数
9 优秀率： SUM(K57K60)55*100
10 格率： SUM(K57K62)55*100
11 标准差： STDEV(K2K56) —— 求 K2 K56 区域 (55 ) 成绩波动情况（数值越说明该班学生间成绩差异较反说明该班存两极分化）
12 条件求： SUMIF(B2B56 男 K2K56) —— 假设 B 列存放学生性 K 列存放学生分数函数返回结果表示求该班男生成绩
13 条件求： ｛ SUM(IF(C3C322 男 IF(G3G322110))) ｝ —— 假设 C 列（ C3C322 区域）存放学生性 G 列（ G3G322 区域）存放学生班级代码（ 1 2 3 4 5 ）函数返回结果表示求班男生数数组函数输完 Ctrl ＋ Shift ＋ Enter 组合键 ( 产生 ｛ …… ｝ ) ｛｝ 手工输入组合键产生
14 根出生日期动计算周岁： TRUNC((DAYS360(D3NOW( )))3600)
——— 假设 D 列存放学生出生日期 E 列输入该函数产生该生周岁
15 Word 中三窍门：
① 连续输入三 ~ 条波浪线
② 连续输入三 条直线
连续输入三 条双直线
excel 中某单元格符合特定条件单元格显示特定颜色：
A1 〉 1 时 C1 显示红色
0
方法：
1 单元击 C1 单元格点 格式 > 条件格式 条件 1 设：
公式 A11
2 点 格式 > 字体 > 颜色 点击红色点 确定
条件 2 设：
公式 AND(A1>0A1<1)
3 点 格式 > 字体 > 颜色 点击绿色点 确定
条件 3 设：
公式 A1<0
点 格式 > 字体 > 颜色 点击黄色点 确定
4 三条件设定点 确定 出
二 EXCEL 中控制列数长度避免重复录入
1 数效性定义数长度
鼠标选定输入数范围点 数 > 效性 > 设置 效性条件 设成 允许 文长度 等 5 （具体条件根需改变）
定义提示信息出错警告信息否开中文输入法等定义点 确定
2 条件格式避免重复
选定 A 列点 格式 > 条件格式 条件设成 公式 COUNTIF(AAA1)>1 点 格式 > 字体 > 颜色 选定红色点两次 确定
样设定输入数果长度会提示果数重复字体会变成红色
三 EXCEL 中 B 列 A 列处标识出？
（）果求 A B 两列行数相较：
假定第行表头单击 A2 单元格点 格式 > 条件格式 条件设
单元格数值 等 B2
点 格式 > 字体 > 颜色 选中红色点两次 确定
格式刷 A2 单元格条件格式复制
B 列参方法设置
（二）果 A 列 B 列整体较（相数行）：
假定第行表头单击 A2 单元格点 格式 > 条件格式 条件设
公式 COUNTIF(BBA2)0
点 格式 > 字体 > 颜色 选中红色点两次 确定
格式刷 A2 单元格条件格式复制
B 列参方法设置
方法设置 AB 列均数着色 A 列 B 列者 B 列 A 列数标记红色字体
四 EXCEL 中样批量处理行排序
假定量数 ( 数值 ) 需行排序操作？
行排序列排序关键字 关键字相时次关键字排序问题排序解决解决方法：
1 假定数 A E 列请 F1 单元格输入公式：
LARGE(A1E1COLUMN(A1))
填充柄公式右复制相应范围
原数行排序出现 F J 列需 选择性粘贴 数值 复制方
注：第 1 步公式根实际情况（数范围）作相应修改果排序 公式改 SMALL(A1E1COLUMN(A1))
五巧函数组合进行条件计数统计
例：第行表头 A 列 姓名 B 列 班级 C 列 语文成绩 D 列 录取结果 现统计 班级 二 语文成绩 等 104 录取结果 重 数统计结果存放工作表列
公式：
SUM(IF((B2B9999 二 )*(C2C9999>104)*(D2D9999 重 )10))
输入完公式 Ctrl+Shift+Enter 键 动加数组公式符号 {}
六判断单元格里否包含指定文？
假定 A1 单元格进行判断 指定文 公式均
IF(COUNTIF(A1*& 指定文 &*)1 )
IF(ISERROR(FIND( 指定文 A11)) )
求某区域重复数数
例求 A1A100 范围重复数数某数重复次出现算两种计算方法：
利数组公式：
SUM(1COUNTIF(A1A100A1A100))
输入完公式 Ctrl+Shift+Enter 键 动加数组公式符号 {}
二利积求函数：
SUMPRODUCT(1COUNTIF(A1A100A1A100))
七工作薄中许工作表快速整理出目录工作表
1 宏 30 取出工作表名称方法：
Ctrl+F3 出现定义名称话框取名 X 引位置 框中输入：
MID(GETWORKBOOK(1)FIND(]GETWORKBOOK(1))+1100)
确定
2 HYPERLINK 函数批量插入连接方法：
目录工作表（般第 sheet ） A2 单元格输入公式：
HYPERLINK(#＇&INDEX(XROW())&＇A1INDEX(XROW()))
公式填充直出错止目录生成
文档香网(httpswwwxiangdangnet)户传
《香当网》用户分享的内容，不代表《香当网》观点或立场，请自行判断内容的真实性和可靠性！
该内容是文档的文本内容，更好的格式请下载文档