最新EXCEL表格中公式大全1资料.docx
- 文档编号:24835135
- 上传时间:2023-06-01
- 格式:DOCX
- 页数:21
- 大小:1.27MB
最新EXCEL表格中公式大全1资料.docx
《最新EXCEL表格中公式大全1资料.docx》由会员分享,可在线阅读,更多相关《最新EXCEL表格中公式大全1资料.docx(21页珍藏版)》请在冰豆网上搜索。
最新EXCEL表格中公式大全1资料
窗体底端
Excel常用电子表格公式大全
1、查找重复内容:
(格式—条件格式)公式=IF(COUNTIF($D$2:
D2,D2)>1,"重复","不重复")
或
=IF(COUNTIF($D$2:
D2,D2)>1,"重复","")
=COUNTIF($D$2:
$D$500,$D2)>=2
2、数据--筛选--高级筛选--输入数据区域--勾选"选择不重复记录"--确定
3、=COUNTIF(D$2:
D2,D2)>1这个是提示一列中第二个重复。
EXCEL一列内如何区分出重复的文字
问:
举报违规检举侵权投诉|uk问EXCEL表的客户名录,如果区分是否重复
答:
在客户名录后面一列(假设为B列)输入=if(countif($B$1:
B1,B1)>1,"重复","不重复")
4、
EXCEL条件格式教程:
用条件格式辨别重复数值
在excel2003中,我们会发现辨别一个范围中的重复数值是非常有帮助的。
可以使用下面的条件格式公式。
在该例子中,格式应用于范围A1:
G2中所有非惟一单元格中
=COUNTIF($A$1:
$G$12,A1)>1
在一个范围中只对非重复数值应用格式,使用下面的公式
=COUNTIF($A$1:
$G$12,A1)=1
实例:
将A1:
G2中的重复值添加特殊的格式。
1.选择要设置交替行阴影的单元格区域A1:
G2,点击菜单栏的“格式”——“条件格式”,将弹出“条件格式”对话框。
2.在“条件格式”对话框中的“条件1”下拉列表框中选择“公式”,然后在公式输入框中输入公式“=COUNTIF($A$1:
$G$12,A1)>1”,并设置其格式为蓝色加粗。
然后添加“条件2”公式为“=COUNTIF($A$1:
$G$12,A1)=1”,并设置其格式为红色加粗。
3.点击“确定”按钮关闭“条件格式”对话框。
效果如图所示
2、用出生年月来计算年龄公式:
=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式:
=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。
1、求和:
=SUM(K2:
K56)——对K2到K56这一区域进行求和;
2、平均数:
=AVERAGE(K2:
K56)——对K2K56这一区域求平均数;
3、排名:
=RANK(K2,K$2:
K$56)——对55名学生的成绩进行排名;
4、等级:
=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))
5、学期总评:
=K2*0.3+M2*0.3+N2*0.4——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩;
6、最高分:
=MAX(K2:
K56)——求K2到K56区域(55名学生)的最高分;
7、最低分:
=MIN(K2:
K56)——求K2到K56区域(55名学生)的最低分;
8、分数段人数统计:
(1)=COUNTIF(K2:
K56,"100")——求K2到K56区域100分的人数;假设把结果存放于K57单元格;
(2)=COUNTIF(K2:
K56,">=95")-K57——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;
(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单元格;
(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单元格;
(7)=COUNTIF(K2:
K56,"<60")——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格;
说明:
COUNTIF函数也可计算某一区域男、女生人数。
如:
=COUNTIF(C2:
C351,"男")——求C2到C351区域(共350人)男性人数;
9、优秀率:
=SUM(K57:
K60)/55*100
10、及格率:
=SUM(K57:
K62)/55*100
11、标准差:
=STDEV(K2:
K56)——求K2到K56区域(55人)的成绩波动情况(数值越小,说明该班学生间的成绩差异较小,反之,说明该班存在两极分化);
12、条件求和:
=SUMIF(B2:
B56,"男",K2:
K56)——假设B列存放学生的性别,K列存放学生的分数,则此函数返回的结果表示求该班男生的成绩之和;
13、多条件求和:
{=SUM(IF(C3:
C322="男",IF(G3:
G322=1,1,0)))}——假设C列(C3:
C322区域)存放学生的性别,G列(G3:
G322区域)存放学生所在班级代码(1、2、3、4、5),则此函数返回的结果表示求一班的男生人数;这是一个数组函数,输完后要按Ctrl+Shift+Enter组合键(产生“{……}”)。
“{}”不能手工输入,只能用组合键产生。
14、根据出生日期自动计算周岁:
=TRUNC((DAYS360(D3,NOW()))/360,0)
———假设D列存放学生的出生日期,E列输入该函数后则产生该生的周岁。
15、在Word中三个小窍门:
①连续输入三个“~”可得一条波浪线。
②连续输入三个“-”可得一条直线。
连续输入三个“=”可得一条双直线。
一、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比如:
A1〉1时,C1显示红色
0 A1<0时,C1显示黄色 方法如下: 1、单元击C1单元格,点“格式”>“条件格式”,条件1设为: 公式=A1=1 2、点“格式”->“字体”->“颜色”,点击红色后点“确定”。 条件2设为: 公式=AND(A1>0,A1<1) 3、点“格式”->“字体”->“颜色”,点击绿色后点“确定”。 条件3设为: 公式=A1<0 点“格式”->“字体”->“颜色”,点击黄色后点“确定”。 4、三个条件设定好后,点“确定”即出。 二、EXCEL中如何控制每列数据的长度并避免重复录入 1、用数据有效性定义数据长度。 用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置","有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。 还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"。 2、用条件格式避免重复。 选定A列,点"格式"->"条件格式",将条件设成“公式=COUNTIF($A: $A,$A1)>1”,点"格式"->"字体"->"颜色",选定红色后点两次"确定"。 这样设定好后你输入数据如果长度不对会有提示,如果数据重复字体将会变成红色。 三、在EXCEL中如何把B列与A列不同之处标识出来? (一)、如果是要求A、B两列的同一行数据相比较: 假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为: “单元格数值”“不等于”=B2 点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 (二)、如果是A列与B列整体比较(即相同数据不在同一行): 假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为: “公式”=COUNTIF($B: $B,$A2)=0 点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。 四、EXCEL中怎样批量地处理按行排序 假定有大量的数据(数值),需要将每一行按从大到小排序,如何操作? 由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。 所以,这一问题不能用排序来解决。 解决方法如下: 1、假定你的数据在A至E列,请在F1单元格输入公式: =LARGE($A1: $E1,COLUMN(A1)) 用填充柄将公式向右向下复制到相应范围。 你原有数据将按行从大到小排序出现在F至J列。 如有需要可用“选择性粘贴/数值”复制到其他地方。 注: 第1步的公式可根据你的实际情况(数据范围)作相应的修改。 如果要从小到大排序,公式改为: =SMALL($A1: $E1,COLUMN(A1)) 五、巧用函数组合进行多条件的计数统计 例: 第一行为表头,A列是“姓名”,B列是“班级”,C列是“语文成绩”,D列是“录取结果”,现在要统计“班级”为“二”,“语文成绩”大于等于104,“录取结果”为“重本”的人数。 统计结果存放在本工作表的其他列。 公式如下: =SUM(IF((B2: B9999="二")*(C2: C9999>=104)*(D2: D9999="重本"),1,0)) 输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。 六、如何判断单元格里是否包含指定文本? 假定对A1单元格进行判断有无"指定文本",以下任一公式均可: =IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","无") =IF(ISERROR(FIND("指定文本",A1,1)),"无","有") 求某一区域内不重复的数据个数 例如求A1: A100范围内不重复数据的个数,某个数重复多次出现只算一个。 有两种计算方法: 一是利用数组公式: =SUM(1/COUNTIF(A1: A100,A1: A100)) 输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。 二是利用乘积求和函数: =SUMPRODUCT(1/COUNTIF(A1: A100,A1: A100)) 七、一个工作薄中有许多工作表如何快速整理出一个目录工作表 1、用宏3.0取出各工作表的名称,方法: Ctrl+F3出现自定义名称对话框,取名为X,在“引用位置”框中输入: =MID(GET.WORKBOOK (1),FIND("]",GET.WORKBOOK (1))+1,100) 确定 2、用HYPERLINK函数批量插入连接,方法: 在目录工作表(一般为第一个sheet)的A2单元格输入公式: =HYPERLINK("#'"&INDEX(X,ROW())&"'! A1",INDEX(X,ROW())) 将公式向下填充,直到出错为止,目录就生成了。 在excel表格里面输入很多人名,我想问下怎么做才知道我输入的人名是否重复 就是筛选一下,把重复的人名给调出来 满意答案 独行13级2009-11-10 假设你输入的姓名在A列, 选择A列,格式-条件格式,选择公式,输入公式: =COUNTIF(A: A,A1)>1 点开下面的“格式”,设置一个填充颜色,如图: 在A列里有重复的姓名就自动的填充颜色了 补充: 上面的设置是只要重复,都填充颜色 下图的设置为如果重复,第一个数据不提示,第二个重复出现的才填充颜色 公式为 =COUNTIF(A$1: A1,A1)>1 追问: 你好,你说的我试了,可是不行啊 追问: 你说的我试了,专门弄几个重复的,可是还是不会选出重复的 真的 回答: 第一种效果: 第二种: 补充: 是提取重复的姓名? 那得用公式 如图,B2的输入数组公式 =IF(ISERROR(INDEX($A$2: $A$15,SMALL(IF(COUNTIF($A$2: $A$15,$A$2: $A$15)>1,ROW($A$2: $A$15)-1,4^8),ROW(1: 1)))),"",INDEX($A$2: $A$15,SMALL(IF(COUNTIF($A$2: $A$15,$A$2: $A$15)>1,ROW($A$2: $A$15)-1,4^8),ROW(1: 1)))) 注: 公式输入后按CTRL+SHIFT+ENTER三键结束 向下复制公式 此公式里的单元格引用区域请根据实际区域修改 3 满意答案 ∙软件之星 ∙向该团提问 ∙采纳率: 34.8% ∙标签: photoshopproeCAD ∙宣言: 我们的爱好,就是帮助他人! 软件之星初级团合作回答者: 1人2009-11-11 设数据如图: 将公式 =COUNTIF(A: A,A2) 复制后粘贴到“B2”单元硌,对准右下角,光标成“+”状时双击,如图: 筛选结果大于1的为重复,如图: 如果第二个才显示重复(二个只显示一个重复),则公式为: =COUNTIF($A$1: A2,A2) 如图: 还没有解决吗? 有什么问题,请追问。 追问: 你这个方法我用了,还是不管,不知道是操作不正确还是怎么回事 回答: 先将公式 =COUNTIF($A$1: A2,A2) 复制后粘贴到后面空白列的第二行 再将公式中的三个“A”改为姓名所在列的列标,然后拖下去(或双击)。 试试,看不行不行。 东方S! 的感言: 谢谢2009-11-12 其他回答(4) 小新|4732个满意答案2009-11-10向TA提问 具体操作步骤如下: 1.打开一份带有重复记录的Excel文档。 2.选中图表中的所有记录(注意,此时应将每列的标题行也选择上,否则筛选完的数据表中将不再包含有该标题行),执行“数据”菜单→“筛选→高级筛选”命令 3.在弹出的“高级筛选”对话框中选择“将筛选结果复制到其他位置”一项,并点击“复制到”选择框后面的范围按钮来选择一块区域以存放筛选后的数据(注意: 此处千万不要与原数据所处的单元格相重合,否则数据表将会混乱),最后,勾选“选择不重复的记录”复选框后,点击“确定”按钮即可。 4.此时,Excel便会将所有的重复记录自动删除掉,确认无误后,您就可以把新生成的数据清单拷贝到新的工作表中继续使用了。 补充: 具体做法参照 追问: 不好意思,我想知道都那个重复,可是我怎么操作不对? 麻烦再说下,谢谢了哦 回答: 你可以使用宏来实现 PrivateSubWorksheet_SelectionChange(ByValTargetAsRange) Rows().Font.ColorIndex=0'字体颜色全设为自动 x=ActiveCell.Value Fori=1To4'第i行,4可改为实际数据行数。 Forj=1To4'第j列,可改为数据总列数。 IfCells(i,j)=xThen'如果等于活动单元格的值 Cells(i,j).Font.ColorIndex=3'颜色设为红色 EndIf Next Next EndSub ALT+F11,复制以上代码到框框里,ALT+F11返回工作表。 追问: 没有过这个,有点看不大明白,你说的前面的字母是什么意思? 谢谢你了,麻烦了,解释下 回答: PrivateSubWorksheet_SelectionChange(ByValTargetAsRange) Rows().Font.ColorIndex=0'字体颜色全设为自动 x=ActiveCell.Value Fori=1To4'第i行,4可改为实际数据行数。 Forj=1To4'第j列,可改为数据总列数。 IfCells(i,j)=xThen'如果等于活动单元格的值 Cells(i,j).Font.ColorIndex=3'颜色设为红色 EndIf Next Next EndSub 运行“工具——宏——宏”将上面这些内容复制到框内。 雨夜流星8级2009-11-10 在输入过程中会有提示, 例如: 你输入张或张晓后单元格出现张晓明(其中晓明或明字是选中状态),那么张晓明这个名字在当前表中已经存在; 如果是已输入名字可以使用高级筛选找出重复的数据 过河卒13级2009-11-10 很简单 先对名字那个列排序 是□否□ 因为是连锁店,老板的“野心”是开到便利店那样随处可见。 所以办了积分卡,方便女孩子到任何一家“漂亮女生”购物,以求便宜再便宜。 排序后 然后插入一行标题名字辅助,数据--自动筛选 自制饰品一反传统的饰品消费模式,引导的是一种全新的饰品文化,所以非常容易被我们年轻的女生接受。 在b2,输入=if(a2=a2,1,0) 我们长期呆在校园里,没有工作收入一直都是靠父母生活,在资金方面会表现的比较棘手。 不过,对我们的小店来说还好,因为我们不需要太多的投资。 这样重复的,会得出结果1, 在上海,随着轨道交通的发展,地铁商铺应运而生,并且在重要商圈已经形成一定的气候,投资经营地铁商铺逐渐为一大热门。 在人民广场地下的迪美购物中心,有一家DIY自制饰品店--“碧芝自制饰品店”然后自动筛选 选定,名字那一行,数据,自动筛选,辅助=1的,删掉下面这些行,即可 标题: 大学生究竟难在哪? —创业要迈五道坎2004年3月23日 300元以下□300~400元□400~500□500元以上□ 我们女生之所以会钟爱饰品,也许是因为它的新颖,可爱,实惠,时尚,简单等。 的确,手工艺品价格适中。 也许还有更多理由和意义。 那么大学生最喜欢哪种手工艺品呢? 此次调查统计如下图(1-3) GGYY5级2009-11-10 LZ,你用的excel2003还是2007版本? 如果顾客在消费中受到营业员的热情,主动而周到的服务,那就会有一种受到尊重的感觉,甚至会形成一种惠顾心理,经常会再次光顾,并为你介绍新的顾客群。 而且顾客的购买动机并非全是由需求而引起的,它会随环境心情而转变。 如果是2007版本就很简单 上述所示的上海经济发展的数据说明: 人们收入水平的增加,生活水平的提高,给上海的饰品业带来前所未有的发展空间,为造就了一个消费额巨大的饰品时尚市场提供了经济基础。 使大学生对DIY手工艺品的时尚性消费,新潮性消费,体验性消费成为可能。 追问: 呵呵是2003所以很麻烦
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 最新 EXCEL 表格 公式 大全 资料