当前位置:文档之家› excel函数公式大全YES

excel函数公式大全YES

三、在 EXCEL中 如何把B列 与A列不同 之处标识出 来?
(一)、如 果是要求A 、B两列的 同一行数据 相比较:
假定第一行 为表头,单 击A2单元 格,点“格 式”->“条件 格式”,将 条件设为:
“单元格数 值” “不等于 ”=B2
点“格式”->“ 字体”->“颜 色”,选中 红色,点两 次“确定”。
二、 EXCEL中 如何控制每 列数据的长 度并避免重 复录入
1、用数据 有效性定义 数据长度。
用鼠标选定 你要输入的 数据范围, 点"数据"->" 有效性"->" 设置","有 效性条件" 设成"允许"" 文本长度"" 等于""5" (具体条件 可根据你的 需要改变) 。
还可以定义 一些提示信 息、出错警 告信息和是 否打开中文 输入法等, 定义好后点 "确定"。
(3) =COUNTIF (K2:K56,"> =90")- SUM(K57: K58) —— 求K2到K56 区域90~ 94.5分的人 数;假设把 结果存放于 K59单元 格;
(4) =COUNTIF (K2:K56,"> =85")- SUM(K57: K59) —— 求K2到K56 区域85~ 89.5分的人 数;假设把 结果存放于 K60单元 格;
8、分数段 人数统计:
(1) =COUNTIF (K2:K56,"1 00") —— 求K2到K56 区域100分 的人数;假 设把结果存 放于K57单 元格;
(2) =COUNTIF (K2:K56,"> =95")- K57 ——求 K2到K56区 域95~99.5 分的人数; 假设把结果 存放于K58 单元格;
确定
2、用 HYPERLIN K函数批量 插入连接, 方法:
在目录工作 表(一般为 第一个 sheet)的 A2单元格 输入公式:
#NAME?
将公式向下 填充,直到 出错为止, 目录就生成 了。
2、点“格式 ”->“字体”-> “颜色”,点 击红色后点 “确定”。 条件2设 为:
公式 =AND(A1> 0,A1<1)
3、点“格式 ”->“字体”-> “颜色”,点 击绿色后点 “确定”。 条件3设 为: 公式 =A1<0
点“格式”->“ 字体”->“颜 色”,点击 黄色后点“ 确定”。
4、三个条 件设定好 后,点“确 定”即出。
5、学期总 评: =K2*0.3+M 2*0.3+N2* 0.4 ——假 设K列、M 列和N列分 别存放着学 生的“平时 总评”、“期 中”、“期末” 三项成绩;
6、最高 分: =MAX(K2: K56) —— 求K2到K56 区域(55 名学生)的 最高分;
7、最低 分: =MIN(K2:K 56) ——求 K2到K56区 域(55名 学生)的最 低分;
9、优秀 率: =SUM(K57 :K60)/55*1 00
10、及格 率: =SUM(K57 :K62)/55*1 00
11、标准 差: =STDEV(K 2:K56) — —求K2到 K56区域 (55人)的成 绩波动情况 (数值越 小,说明该 班学生间的 成绩差异较 小,反之, 说明该班存 在两极分 化);
4、 从输入的身 份证号码内 让系统自动 提取性别, 可以输入以 下公式:
=IF(LEN(C 2)=15,IF(M OD(MID(C 2,15,1),2)= 1,"男","女 "),IF(MOD( MID(C2,17 ,1),2)=1," 男","女"))公 式内的“C2” 代表的是输 入身份证号 码的单元格 。
(7) =COUNTIF (K2:K56,"< 60") —— 求K2到K56 区域60分 以下的人 数;假设把 结果存放于 K63单元 格;
说明: COUNTIF 函数也可计 算某一区域 男、女生人 数。
如: =COUNTIF (C2:C351," 男") ——求 C2到C351 区域(共 350人)男 性人数;
由于按行排 序与按列排 序都是只能 有一个主关 键字,主关 键字相同时 才能按次关 键字排序。 所以,这一 问题不能用 排序来解决 。解决方法 如1、下假:定你 的数据在A 至E列,请 在F1单元 格输入公 式:
#NUM!
用填充柄将 公式向右向 下复制到相 应范围。
你原有数据 将按行从大 到小排序出 现在F至J 列。如有需 要可用“选 择性粘贴/ 数值”复制 到其他地方 。
公式如下:
0
输入完公式 后按 Ctrl+Shift+ Enter键,让 它自动加上 数组公式符 号"{}"。
六、如何判 断单元格里 是否包含指 定文本?
假定对A1 单元格进行 判断有无" 指定文本", 以下任一公 式均可:


求某一区域 内不重复的 数据个数
例如求 A1:A100范 围内不重复 数据的个 数,某个数 重复多次出 现只算一个 。有两种计 算方法: 一是利用数 组公式:
注:第1步 的公式可根 据你的实际 情况(数据 范围)作相 应的修改。 如果要从小 到大排序, 公式改 为:=SMAL L($A1:$E1, COLUMN( A1))
五、巧用函 数组合进行 多条件的计 数统计
例:第一行 为表头,A 列是“姓名 ”,B列是“ 班级”,C列 是“语文成 绩”,D列是 “录取结果 ”,现在要 统计“班级” 为“二”,“语 文成绩”大 于等于 104,“录取 结果”为“重 本”的人数 。统计结果 存放在本工 作表的其他 列。
#DIV/0!
输入完公式 后按 Ctrl+Shift+ Enter键,让 它自动加上 数组公式符 号"{}"。 二是利用乘 积求和函 数:
#DIV/0!
七、一个工 作薄中有许 多工作表如 何快速整理 出一个目录 工作表
1、用宏3.0 取出各工作 表的名称, 方法:
Ctrl+F3出 现自定义名 称对话框, 取名为X, 在“引用位 置”框中输 入=M:ID(GET .WORKBO OK(1),FIN D("]",GET. WORKBO OK(1))+1,1 00)
(5) =COUNTIF (K2:K56,"> =70")- SUM(K57: K60) —— 求K2到K56 区域70~ 84.5分的人 数;假设把 结果存放于 K61单元 格;
(6) =COUNTIF (K2:K56,"> =60")- SUM(K57: K61) —— 求K2到K56 区域60~ 69.5分的人 数;假设把 结果存放于 K62单元 格;
连续输入三 个“=”可得 一条双直线 。
一、excel 中当某一单 元格符合特 定条件,如 何在另一单 元格显示特 定的颜色比 如: A1〉1时, C1显示红 色 0<A1<1 时,C1显 示绿色 A1<0时, C1显示黄 色 方法如下:
1、单元击 C1单元 格,点“格 式”>“条件 格式”,条 件1设为: 公式 =A1=1
用格式刷将 A2单元格 的条件格式 向下复制。
B列可参照 此方法设置 。
(二)、如 果是A列与 B列整体比 较(即相同 数据不在同 一行):
假定第一行 为表头,单 击A2单元 格,点“格 式”->“条件 格式”,将 条件设为:
“公式” =COUNTIF
($B:$B,$A 2)=0
点“格式”->“ 字体”->“颜 色”,选中 红色,点两 次“确定”。
1、 查找重复内 容公式: =IF(COUN TIF(A:A,A2 )>1,"重复 ","")。
2、 用出生年月 来计算年龄 公式: =TRUNC(( DAYS360( H6,"2009/8 /30",FALS E))/360,0) 。
3、 从输入的 18位身份 证号的出生 年月计算公 式: =CONCAT ENATE(MI D(E2,7,4)," /",MID(E2, 11,2),"/",MI D(E2,13,2) )。
用格式刷将 A2单元格 的条件格式 向下复制。
B列可参照 此方法设置 。
按以上方法 设置后, AB列均有 的数据不着 色,A列有 B列无或者 B列有A列 无的数据标 记为红色字 体。
四、 EXCEL中 怎样批量地 处理按行排 序
假定有大量 的数据(数 值),需要 将每一行按 从大到小排 序,如何操 作?
1、求和: =SUM(K2: K56) —— 对K2到K56 这一区域进 行求和;
2、平均 数: =AVERAG E(K2:K56) ——对K2 K56这一区 域求平均 数;
3、排名: =RANK(K2 , K$2:K$56) ——对55 名学生的成 绩进行排 名;
4、等级: =IF(K2>=8 5,"优 ",IF(K2>=7 4,"良 ",IF(K2>=6 0,"及格"," 不及格")))
2、用 条件格式避 免重复。
选定A列, 点"格式" Nhomakorabea>" 条件格式 ",将条件 设成“公式 =COUNTIF ($A:$A,$A 1)>1”,点" 格式"->"字 体"->"颜色 ",选定红 色后点两次 "确定"。
相关主题