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)户传
《香当网》用户分享的内容,不代表《香当网》观点或立场,请自行判断内容的真实性和可靠性!
该内容是文档的文本内容,更好的格式请下载文档