excel表格颜色统计公式最新文档.docx
- 文档编号:15876493
- 上传时间:2023-07-08
- 格式:DOCX
- 页数:25
- 大小:1.90MB
excel表格颜色统计公式最新文档.docx
《excel表格颜色统计公式最新文档.docx》由会员分享,可在线阅读,更多相关《excel表格颜色统计公式最新文档.docx(25页珍藏版)》请在冰点文库上搜索。
excel表格颜色统计公式最新文档
excel表格颜色统计公式
EXCEL表格,单元格中除了有相关文字信息外,还有多种颜色表示,我想统计一下每种颜色总共有多少格。
而且随着颜色的变化,统计的结果也随着变化。
我已经找答案找了一年了。
向高手请教了。
先谢谢了。
问题补充:
我说的颜色,是在编辑区域内单元格中的填充颜色,而且颜色分三种吧,有红色,蓝色,无填充色。
比如在80个单元格范围内,有这么多颜色,而且颜色的位置是不固定的,不是整列,也不是整行,是杂乱无序的,而且随时都要撤销颜色,或者更换颜色的位置,或者更换颜色,并且我要随时统计即时的各种颜色单元格的数量,比如,红色?
而且我统计的结果要随着颜色的更换,删除,调整结果要自动更新。
比较麻烦,请大侠给指点指点。
大致意思就如下图。
需随时统计表中蓝色,红色,为填充的单元格数量。
能自动更新的。
谢谢。
问题补充:
详细一点,用什么公式,怎么操作。
谢谢
问题补充:
要步骤,而且按照表格的统计要求,设置自动变化。
谢谢
STEP1:
打开你的excel;
STEP2:
菜单栏:
工具-宏-VisualBasic编辑器;
STEP3:
VisualBasic编辑器菜单栏:
插入-模块
STEP4:
贴入下面这段代码
FunctionCountcolor(colAsRange,countrangeAsRange)
DimicellAsRange
Application.Volatile
ForEachicellIncountrange
Ificell.Interior.ColorIndex=col.Interior.ColorIndexThen
Countcolor=Countcolor+1
EndIf
Nexticell
EndFunction
STEP5:
保存并关闭VisualBasic编辑器
STEP6:
使用函数countcolor(所要统计的颜色所在单元格,统计的区域)
本例中,在C17单元格输入公式:
=Countcolor(B17,$B$7:
$G$15)
然后下拉公式到C20单元格。
在C21单元格输入公式:
=SUM(C17:
C20)
由于在统计区域中有6个单元格是面积,所以,C20单元格在统计可售套数时要减去6。
分享给你的朋友吧:
∙人人网
∙新浪微博
∙开心网
∙MSN
∙QQ空间
对我有帮助
5
Excel表格公式大全
1、查找重复内容公式:
=IF(COUNTIF(A:
A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式:
=TRUNC((DAYS360(H6,"2021/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())) 将公式向下填充,直到出错为止,目录就生成了 ∙新闻 ∙网页 ∙问答 ∙视频 ∙图片 ∙音乐 ∙地图 ∙百科 ∙良医 ∙软件 360新知 登录|注册 ∙ ∙ ∙ ∙ 新知 > 电脑数码 ∙延长40分钟 ∙老公吃了它,一... ∙用好这几招,让... ∙会计学校 ∙一招缓解膝关节... ∙经济期刊 Excel2021如何通过出生日期使用函数计算年龄 创建于2021-04-0616: 05 在excel中,我们只需要知道一个人的出生日期就可以通过公式来计算出他的年龄,到底怎样操作呢? 来看看下面的方法。 材料/工具 电脑,excel 方法 ∙ 打开excel; ∙ 打开需要编辑的表格; ∙ 在年龄下方的表格中输入公式=DATEDIF(C2,TODAY(),"Y"); ∙ 然后点击Enter键就可以得到第一名同学的年龄; ∙ 将鼠标移到表格下角,当光标变成实心的黑色十字的时候,拖动光标到下一个表格,就可以得到第二个同学的年龄了。 如何用Excel电子表格统计学生成绩 【摘要】使用Excel电子表格统计学生成绩不但方便而且快捷,利用它能自动完成成绩各项统计工作如计算总分、排名次、统计各分数段学生人数和所占比例、最高分、最低分等,还能把分数转换成等级分。 用Excel电子表格统计学生成绩,将会大大提高教师的工作效率和工作质量。 【关键词】Excel;电子表格;统计;成绩;名次 统计学生成绩是教导主任和教师每学期都必须做的常规工作。 传统的做法是: 教师在成绩表上填写学生的平时成绩、考试成绩,然后用计算器按百分比计算每个学生的总评成绩,计算科目总分和平均分,有的还需要列出名次,不但量大,而且容易出错,反复验算是常事。 如果用电脑完成,则方便快捷准确得多,而且无需“重算一遍”。 Excel是美国微软公司推出的基于Windows操作系统的电子表格软件,是目前被广泛使用的现代办公软件之一,本文举例使用的版本是MicrosoftOfficeExcel2003。 1如何制作成绩统计表 [1.1]输入列标题 开机启动Windows后进入Excel电子表格窗口,在顶端行依次输入表格的列标题: 考号、姓名、语文、数学、英语、总分、平均分、名次等。 [1.2]输入原始数据 考号、姓名、语文、数学、英语的内容属于原始数据,也要依次输入。 考号的输入可采用“自动填充”方法。 先在1、2单元格输入预先设计好的考号后,再选取1、2号单元格后鼠标移动至选定区域的右下角,当出现“+”后往下拖动,所有的考号会自动出现。 (如图1) 图1 [1.3]数据居中对齐 拖动鼠标选定制表区,单击“格式”,再单击“单元格”,再单击“对齐”,水平和垂直对齐均选“居中”,然后单击“确定”。 [1.4]加表格框线 选取需加边框的单元格,单击菜单栏中的“格式”,选择“单元格”,在对话框中选择“边框线”标签,从边框线型栏内,选择较粗的线形加入到“外框”标志栏内,选择较细的线型分别加入到上、下、左、右栏内,按下“确定”。 (如图2) 第二种方法是选择表格后,单击工具栏上“边框”按钮在弹出的选项中选择“所有框线”则可(如图2)。 至此,除需计算部分外全部输入完成。 图2 2.如何计算总分 [2.1]计算考号1的总分 表格都是由行和列组成的,行由数字表示,列用字母表示。 当活动单元格在F4位置时键入“=”然后输入SUM(C4: E4)(表示从C4单元格加到E4单元格),再回车,即得总分(如图3)。 另一种方法是选择C4到E4这三个单元格后单击工具栏上“自动求和”按钮(如图3右侧图标)。 图3 [2.2]其他总分的得出 用鼠标选取已算出的学号1的总分,当出现“+”符号后往下拖动鼠标,所有其他的总评分会自动出现。 3.如何计算学生平均分 [3.1]学生1的平均分: 活动单元格放在需要填写平均分的位置,输入“=F4/3”(用总分除以科目3)回车,即算出平均分。 (如图4) 图4 [3.2]其他平均分的得出: 用鼠标选取已算出的学生1的平均分,当出现“+”符号后往下拖动鼠标,所有其他的平均分会自动出现。 4.如何排列名次 [4.1]利用菜单排序: 排列名次,首先按总分排序。 从列标题那行一直选取到最后(本表是从第3行到55行),单击菜单“数据”,再单击“排序”,在主要关键字下拉列表中选择“总分”,再选“降序”,按下“确定”键,分数即由高至低排列。 这时再用“自动填充”方法将名次的号(数字)填上。 缺点是并列的名次没有显示出来。 (如图5) 图5 然后再选全表,单击“数据”再单击“排序”,在下拉列表中选主要关键字“考号”,再选“升序”,最后“确定”,即出现按考号排列的名次格式。 [4.2]利用RANK函数排序: 另外一种方法是利用RANK函数进行排名,解决了以上统计方法无并列名次的缺点。 RANK(X,Y,Z)函数,其中有三个参数,第一个参数X为某个学生的总分所在单元格,第二个参数Y为整个班级总分所在的区域,第三个参数Z是可选的,表示统计方式,若省写或写0,则成绩高的名次靠前,一般都使用这种方式,如果写1,则成绩高的名次靠后,这种情况一般较少用。 为了在保持学号顺序不变的前提下进行学生成绩名次排定,可以在F4单元格中输入公式: =RANK(F4,$F$4: $F$55,0)(如图6) 图6 然后,利用自动填充柄将其复制到下方的单元格,统计出所有学生的名次。 注意,这里$F$4: $F$55用的是绝对地址,是为了保证公式在复制时此处不变,因为作为第二个参数,这里都是指整个班级总分所在的区域,这个区域是相同的。 再按名次排序 ,可以看出并列的名次也统计出来了。 (如图7) 图7 5.进行科目统计 [5.1]计算科目总分 选取所要求和的“总分”一栏的数据,单击“Σ”按钮,或者是在其中输入公式“=SUM(C4: C55)”后回车即算出科目总分。 然后,利用自动填充柄将其复制到右方的单元格,数学、英语总分即可得出。 (如图8) 图8 [5.2]计算科目平均分: 方法同上,只是函数不一样 在要填入语文平均分的单元格内输入公式: “=AVERAGE(C4: C55)”即得出语文平均分,然后,利用自动填充柄将其复制到右方的单元格,数学、英语平均分即可得出。 [5.3]统计科目及格人数: 通常,统计不同分数段最好的方法是利用COUNTIF(X,Y)函数。 其中有两个参数,第一个参数X为统计的范围,一般最好用绝对引用;第二个参数Y为统计条件,要加引号。 对于大于等于60分的人数只要用一个COUNTIF()函数,如在C59单元格中输入公式: =COUNTIF($C$4: $C$55,">=60")(如图9) 图9 回车即可得出。 其它两科复制公式后,把统计范围即其中绝对地址分别改为$D$4: $D$55和$E$4: $E$55,数学和英语科目及格人数也统计完成。 [5.4]计算单科及格率: 图10 单科及格率等于及格人数除以参考人数。 语文科目已在C59单元格统计出及格人数,参考人数在列标题上方E2单元格内填入,因此在统计及格率C60单元格内写入公式: =C59/E2回车即得出(如图10)。 单元格格式可设为“百分比”保留2位小数。 其它两科同样的方法可算出。 [5.5]其它分数段的统计 方法同统计及格人数,优生人数的统计只需把统计条件改为“>=80”差生人数的统计把统计条件改为“<40”。 如果要统计80——89、70——79与60——69分数段的人数,需要用两个COUNTIF()函数相减。 如统计80——90分数段人数用公式: =COUNTIF($C$4: $C$55,"<90")-COUNTIF($C$4: $C$55,"<80”) 即用小于90的人数减去小于80的人数。 其它分数段统计方法相同只要把统计条件改一下。 [5.6]统计科目最高分和最低分: 利用函数MAX、MIN分别统计最大值和最小值。 如统计语文科目中最高分在C65单元格内输入公式: =MAX(C4: C55)回车即得到87.00。 最低分在C66单元格内输入公式: =MIN(C4: C55)回车即可。 (如图11) 图11 [5.7]将百分制转换成不同的等级分: 将百分制转换成不同的等级分有多种不同的划分方法,1是将百分制的分数转换成A(90——100)、B(80——89)、C(70——79)、D(60——69)与E(低于60)五个等;2是将百分制的分数转换成优(90——100)、良(75——89)、中(60——74)与不及格(低于60)四个等级。 具体使用哪种等级划分方法可根据实际情况自己确定。 在百分制转换成不同的等级分时,一般使用IF(X,Y,Z)函数。 其中有三个参数,第一个参数X为条件,不能加引号;第二个参数为条件成立时的结果,如果是显示某个值,则要加引号;第三个参数为条件不成立时的结果,如果是显示某个值,同样要加引号。 该函数可以嵌套,即在第二个或第三个参数处可以再写一个IF函数。 为了得到第一种方法所要的等级结果,如语文科目可以在D4单元格中输入公式: =IF(C4>=90,"A",IF(C4>=80,"B",IF(C4>=70,"C",IF(C4>=60,"D","E"))))然后,利用自动填充柄将其复制到下方的所需单元格。 (如图12) 为了得到第二种方法所要的等级结果,语文科目可以在E4单元格中输入公式: =IF(C4>=90,"优",IF(C4>=75,"良",IF(C4>=60,"中","不及格"))) 然后,利用自动填充柄将其复制到下方的所需单元格。 图12 [5.8]使不及格的分数以红色显示 统计学生成绩时经常需要将不及格的分数用红色显示,其结果如上图中红色显示部分。 使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。 该命令会弹出一个对话框,其中要求确认条件与相应的格式。 对于“语文”列,可先选中C4: C55,然后使用“格式”菜单中的“条件格式”命令,在弹出的对话框中,左边使用默认的“单元格数值”,中间选“小于”,右边填写60,然后单击右边的“格式”按钮,从中选择红色,最后单击两次“确定”按钮。 (如图13) 图13 对于“等级一”列,可先选中D4: D55,然后使用“格式”菜单中的“条件格式”命令,在弹出的对话框中,左边使用默认的“单元格数值”,中间选“等于”,右边填写E,然后单击右边的“格式”按钮,从中选择红色,最后单击两次“确定”按钮。 “等级二”列类似。 6、结束语Excel电子表格功能强大,界面直观易学易用,可以轻松地完成学科的总分、平均分的计算;利用RANK函数排序,可以在学生顺序不变的前提下进行学生成绩名次排定,并且可以显示出名次并列的情况,一目了然;COUNTIF(X,Y)函数是统计不同分数段人数的一种好方法;使用IF(X,Y,Z)函数能轻松将百分制转换成不同的等级分;用“条件格式”命令可以用特殊的颜色显示符合条件的分数段或某等级;总之,用Excel统计学生成绩,将会大大提高教师的工作效率和工作质量,让我们的教学工作变得更轻松。 1、sumif: 顾名思义,这个公式是对指定条件的值进行求和。 这个公式共有三个参数,分别为“区域”“条件”“求和区域”,我们可以看下图,A1-A5的考试成绩各科都有统计排名,现在我们想要统计一下他们每个人的总成绩,我们鼠标选中F2单元格,然后选择公式中的sumif公式,区域选中“B: B”列,条件选中“E: E”列,求和区域选中“C: C”列,点击确定,然后按住单元格下边的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 表格 颜色 统计 公式 最新 文档
![提示](https://static.bingdoc.com/images/bang_tan.gif)