- ?
Excel如何计算符合条件不重复的个数
伴红尘
展开
今天分享计算符合条件的不重复的数值的个数,例如以下例子,销售每天都会拜访客户,我们现在需要统计本周之内所有销售的总的拜访次数和拜访的客户数:
1、拜访次数,即销售出访的次数,也就是说一家客户拜访两次也是算2,所以我们只需要用条件计数求每个销售的名字的次数即可,函数公式:=COUNTIF(B:B,F2)
2、拜访家数,即,如果一家客户本周销售拜访了2次则算1,所以此时我们就需要计算出满足条件的非重复数值,函数公式:=SUMPRODUCT((1/COUNTIF(C2:C38,C2:C38))*(B2:B38=F2))
2.1、我们计算出H2的次数,下边的书我们直接下拉即可,但因为我们选定的区域,所以,我们需要绝对引用,这个时候我们需要把函数修改成:=SUMPRODUCT((1/COUNTIF($C$2:$C$38,$C$2:$C$38))*($B$2:$B$38=F2)),$这个符号在Excel中是绝对引用,我们选中函数重的对应的按F4即可
2.2、函数解释:
SUMPRODUCT是乘积求和,可以翻看之前文章的关于此函数的介绍
COUNTIF(C2:C38,C2:C38)是分别对C2:C38的每个数求其重复次数,用1除以这个数就得到重复次数的倒数。如果一个数出现两次,重复次数是2,倒数就是1/2,两个1/2加起来不刚好是1么,也就是说一个数出现两次,计数并求和后得到1。
如果想要练习表,可私信发
- ?
Excel用快捷键及自动求平均值与Average函数与IF多重嵌套应用
Stade
展开
在 Excel 中,求平均值用Average函数;它可分用快捷键求平均值、自动求平均值和输入公式求平均值,其中用快捷键求平均值速度最快,并且三种方法都可以对行列、任意单元格、指定区域甚至整个表格求平均值。如果要求满足一定条件的平均值,需要用Average函数与IF函数组合,既可以只有一个条件,也可以有多个条件。另外,在默认情况下,Excel 保留小数位与参加运算的数值个数有关,如果参加运算的数值比较多,会保留八位小数,这种情况需要设置保留小数位数。以下是它们的具体操作方法,操作中所用版本为 Excel 2016。
一、Excel求平均值基本操作
(一)对行求平均值
1、选中用于存放求平均值结果的单元格 K2,输入公式 =AVERAGE(B2:J2),按回车,则对第二行所有数值求出平均值,结果为 78.89444444,自动保留了八位小数;操作过程步骤,如图1所示:
图12、求平均值的结果保留的的小数位数太多,就成绩而言,一般保留一位小数即可,当前的保留小数位数是 Excel 默认的,但可以修改,修改方法在下文介绍。
(二)对指定任意单元格求平均值
1、假如对第二行的理科成绩求平均值,即对 B2、E2、F2 和 G2 求平均值。选中 K2,输入公式 =Average(B2,E2,F2,G2),如图2所示:
图22、按回车,则对第二行理科成绩求出平均值,如图3所示:
图3提示:Excel求平均值时,保留小数位数与参与求平均值的个数有关,这里并未设置保留小数位数,但只保留了两位小数,而上面保留了八小数,所不同的是:这里参与求平均值的只有四个数,而上面有九个数。另外,如果参与求平均值的有五个数,则会保留三位小数。
(三)对列求平均值
以对高数成绩求平均分为例。选中第二列的单元格 B30,输入公式 =average(B2:B29),按回车,则对高数成绩求出平均值,结果为 83.96428571,也保留了八位小数,操作过程步骤,如图4所示:
图4二、Excel用快捷键快速求平均值
假如用快捷键快速求第二行的平均值。选中 K2 单元格,按 Alt 键,按一次 M 键,按一次 U 键,按一次 A 键,则自动在 K2 中输入求平均值公式 =AVERAGE(B2:J2),并框选住第二行所有数据单元格,按回车,则对第二行求出平均值,操作过程步骤,如图5所示:
图5三、Excel设置保留几位小数
1、右求出平均值的单元格 K2,在弹出的菜单中选择“设置单元格格式”,在打开的窗口中选择“数字”选项卡,再选择左边“分类”下面的“数值”,“小数位数”保持默认的 2,“负数”选择第一项,单击“确定”,则 78.89444444 由八位小数变为两位小数 78.89,操作过程步骤,如图6所示:
图62、以上只是设置了一个单元格,如果要设置多个单元格,选中它们,按照上面的操作方法把小数位数设置为两位即可。若要保留其他的小数位数,在“设置单元格格式”窗口中的“小数位数”右边输入具体的数字即可。
四、Excel自动求平均值
1、对行自动求平均值
选中用于存放平均值的单元格 K2,选择“公式”选项卡,单击“自动求和”,在弹出的选项中选择“平均值”,则第二行所有数值单格被彩色线框框选,按回车,则求出李子和所有科目成绩的平均分;单击 K2 再次选中它,用鼠标按住单元格填充柄,并往下拖,则经过的单元格全被填上 K2 的值,按 Ctrl + S 组合键保存,则经过单元格的值变为对应成绩的平均值;操作过程步骤,如图7所示:
2、对列自动求平均值
假如要求第二列高数成绩的平均分。选中用于保存结果的单元格 B30,选择“公式”选项卡,单击“自动求和”,则自动在 B30 中输入求平均值公式 =AVERAGE(B2:B29),同时第二列所有数值单元格被彩线框选,按回车,则求出高数成绩的平均分;用鼠标按住单元格填充柄,并往右拖,则所有经过的列自动求出平均分,即求出其它各科的平均分,但值还是 B30 的值,按 Ctrl + S 组合键保存,则所有科目的平均分变为对应科目的平均分;操作过程步骤,如图8所示:
图8五、Excel区域求平均值
(一)框选指定区域法
假如要对“数电、模电和C语言”前三行求平均值。选择“公式”选项卡,选中用于保存求平均值结果的单元格 K6,依次选择“自动求和 → 平均值”,则求平均值公式 =AVERAGE(B4:J4) 被填充到 K4,并选中 B4:J4,按 Delete 把 B4:J4 删除,用鼠标框选“数电、模电和C语言”前三行的单元格,则所框选的区域自动填到 K4 中,公式随即变为 =AVERAGE(E2:G4),按回车,则求出“数电、模电和C语言”前三行的平均值,操作过程步骤,如图9所示:
图9(二)输入指定区域法
假如要对某班级所有学生全部科目的成绩求平均值。选中用于保存结果的单元格 K30,输入公式 =AVERAGE(B2:J29),按回车,则求出某班级所有学生全部科目的平均分,操作过程步骤,如图10所示:
图10六、Excel Average函数与条件IF单条件与多条件嵌套应用
(一)单条件求平均值
1、假如要求统计一个班级的优秀学生,当学生的平均分在90以上为优秀。选中 K2 单元格,把公式 =IF(AVERAGE(B2:J2) >= 90,"优秀","待定") 复制到 K2,按 Ctrl + Shift + Enter 组合键执行公式,则返回结果“待定”;用鼠标按住单元格填充柄,往下拖,则经过单元格都用“待定”填充,按 Ctrl + S 组合键保存把它们更新,则平均分大于等于90的学生用“优秀”填充,操作过程步骤,如图11所示:
图112、公式说明:AVERAGE(B2:J2) >= 90 先求出平均分,如果平均分大于等于 90,则显示“优秀”,否则显示“待定”。
(二)多条件求平均值
1、假如要求统计一个班级的优秀和优良学生,当学生的平均成分在90以上为优秀,成绩在80到90之间为优良。在 K2 中输入公式
=IF(AVERAGE(B2:J2)>=90,"优秀",IF(AVERAGE(B2:J2)>=80,IF(AVERAGE(B2:J2)<=90,"优良"),"待定"))
如图12所示:
图122、按 Ctrl + Shift + Enter 组合键执行公式,则返回结果“待定”;同样用往下拖的方法求出其他同学是否达到优秀或优良水平,往下拖后,别忘了按 Ctrl + S 组合键保存更新,最终求得结果,如图13所示:
图133、公式说明:
公式中共有三个 IF,其中嵌套了两个 IF;AVERAGE(B2:J2)>=90 为第一个 IF 的判断条件,它为真时,显示“优秀”;它为假时,执行第二个 IF;当第二个 IF 判断条件 AVERAGE(B2:J2)>=80 为真时,执行第三个 IF;为假时,显示“待定”;当第三个 IF 的判断条件为真时,显示“优良”;为假时,什么也不显示。
- ?
用Excel表格做工程量计算表
潘不悔
展开
对于我们做预算的来说,有一些工程如果用图形算量去算还不如手算来的快,手算的话,繁重的计算图纸工程量工作,往往耗去较多的时间和精力,难免还要在计算中出错,何况在核对之后又必须进行修改,重新进行加减乘除。而且,现在很多单位不仅要计算结果,还要求提供计算稿,如果手算的话总要输入电脑,有没有更方便的方法呢?
有一款软件——“工程量计算稿”(网上有这款软件——“工程量计算稿安装程序1.54+注册机”):工程量计算稿主要用于辅助建筑概预结算人员完成项目工程量的计算。完全模拟手工计算底稿,高效的汇总功能独具匠心。 计算速度比手工计算快1/3,避免了手工计算容易漏算的弊端,核对后修改计算底稿比手工重新计算快95%,打印的底稿非常清晰,易于核对和找出差错。不过如果提供电子版对方也得装有此软件,不太方便。还是Excel表格的通用。怎样用Excel表格做工程量计算表呢?下面一一述来。
第一步:建立表格
第二步:输入计算式
输入计算式后最笨的方法求计算式的值就是在工程量一栏输入等号,然后把计算式复制过去
第三步:定义个值等于前面的计算式
1、“插入——名称——定义”
2、输入定义的值,比如说X:
3、点击引用位置右下角的区域选择控件
4、选中计算式所在单元格
5、输入函数
输入函数时切记将$符号删除,两个$符号的意思是固定单元格数据。
6、点击确定
7、在单元格中输入“=X”,就出结果了
8、下面的计算式可以直接拉下去,格式化公式:
- ?
掌握这10个EXCEL求和技巧,从此再无求和难事
祖夏彤
展开
求和,是最常见的统计工作,但花样繁多的求和要求和条件,常常让不少人限入苦思冥想不得其解的困惑之中,今天运用EXCEL函数与公式,总结从初级到高级的10个求和技巧,与大家一起学习和交流。
初级篇
1、运用“=”、“+”来计算
这是最基础的求和方法,适合无规律或指定单元格求和。如下图:计算1、3、5月份的销售量。J2单元格:=E2+G2+I2,然后,向下复制
2、运用函数SUM求和
这是基础求和函数,适合连续区域求和,高级应用多为与其他函数结合形成SUM数组公式。说明一点,SUM函数的参数,可以是单元格区间,也可以是单个单元格。如下图:计算1、3、5月份的销售量。J2单元格:=SUM(E2,G2,I2),然后,向下复制
中级篇
3、忽略文本跨列求和
如下图,计算全部销售人员的销售额。C10单元格:=SUM(A2:I7)
利用Excel中sum函数本身对文本类型数值自动忽略的规定,所以,可以用来直接对跨列的数值型数据求和。
4、单条件求和,运用求和函数:SUMIF。
如图,计算1月份冰箱的销售量。K2单元格:=SUMIF($D$2:$D$18,"冰箱",$E$2:$E$18)
SUMIF函数基本结构:SUMIF(条件区域,条件,求和区域)
5、多条件求和,运用求和函数:SUMIFS。
如图,计算销售一部1月份相机销售量。L2单元格:=SUMIFS($E$2:$E$18,$A$2:$A$18,"销售一部",D$2:D$18,"相机")
SUMIFS函数基本结构:SUMIF(求和区域,条件区域1,条件1,条件区域2,条件2...)
6、避开错误值求和。
如下图:计算1-5月份总销售量。C12单元格:=SUMIF(A2:E10,"<9.9E+307")
应用函数SUMIF设置条件完成,其中设置条件"<9.9E+307",其含义是,9.9*10^307,即9.9乘10的307次方,9.9E+307是一个极大的数值,根据EXCEL的通过规则:数值<文本
7、通配符求和
如下图:计算1月份包含电脑的的总销量。G2单元格:=SUMIF(C2:C18,"*电脑",D2:D18)
应用函数SUMIF设置条件完成,通配符*代表任何字符或字符串,只要总字符不超过255个,设置条件"*电脑",或“*电脑*”,在选定区域中查找包含“电脑”的所有商品,在计算区域求和。
8、快速累计求和
利用“$”对求和区域首个单元格进行绝对引用,马上就能对一列数值快速累计求和,如下图,C2单元格:=SUM($B$2:B2),向下复制即可。
9、求和的快捷键:"Alt + =",选中要求和的区域和拟计算结果的空白区域,一键即可在空白区域产生求和结果
高级篇
10、求和之王:SUMPRODUCT
SUMPRODUCT函数跟SUM函数的数组公式条件求和语法一样。
=SUMPRODUCT((条件区域=条件)*求和区域)
如上面案例,计算1月份冰箱的销售量。K2单元格:=SUMIF($D$2:$D$18,"冰箱",$E$2:$E$18),也可以写成:=SUMPRODUCT((D2:D18="冰箱")*E2:E18)
如上面案例,计算销售一部1月份相机销售量。L2单元格:=SUMIFS($E$2:$E$18,$A$2:$A$18,"销售一部",D$2:D$18,"相机"),也可写成:=SUMPRODUCT((A2:A18="销售一部")*(D2:D18="相机")*E2:E18)
下面,举个复杂一点点的例子,昨天,世界杯开始,首场比赛,俄罗斯队5:0大胜沙特队,拟统计本次世界杯,各小组球队进、失球队总数。
以A组为例,第一场有真实比分,其余为模拟分。就可以用SUMPRODUCT计算,进球数:J2单元格:=SUMPRODUCT(($B$2:$B$7=$I3)*($C$2:$C$7))+SUMPRODUCT(($F$2:$F$7=$I3)*($E$2:$E$7));失球数:K2单元格:=SUMPRODUCT(($B$2:$B$7=$I3)*($E$2:$E$7))+SUMPRODUCT(($F$2:$F$7=$I3)*($C$2:$C$7))
SUMPRODUCT与其他函数结合还可以解决你意想不到的求和需求,以后我们会专门进行分享。请关注后,期待好文。
每天学习一点点,每天进步一点点,积硅步,必能致千里,得大成。
- ?
技巧 | 4个小技巧解决你Excel求和总不对的困扰
雷阁
展开
Hi,大家好,我是胖斯基
周末闲暇,整理了下那铺满灰尘的书架。
透过尘埃,在故纸堆中发现这么一本连环画图册,脑海中瞬间回忆起那真假美猴王的剧幕,那一幕幕,完美的演绎了真亦假时假亦真,无为有处有还无……
其实,在Excel中,这种真真假假也同样存在,也一样带来了求和困扰!
求和,在Excel中很简单,相信小白都可以脱口而出,直接函数SUM即可,然后有时候,事实并非如此:
当采用最傻瓜的方式直接逐个相加时,发现结果正确;
但是当采用SUM求和时,结果却错误。
不明其中缘由的你,可能会非常困扰,明明就这几个数相加,怎么就不对了呢?
其源头就是“真假数据”,在Excel中的专业叫法是文本型数据(不参与直接运算)。
So,当这类“假数据”参与SUM时,自然不进行正确的求和运算。
1
那如何判定文本型的数值参与了运算?
技巧1:观察法
如果是文本型的数据,则在其单元格的左上角,会有一个三角符号提示
相信只要视力没问题,基本可以快速排查出所谓的“假数据”
当然,更BT的一种就是其单元格上没有小三角符合,那如何快速判定这一系列数据是否有问题呢?
依旧采用观察法:
选中这一列数据,然后观察Excel的右下方,会有文字提示:计数:6,数值计数:5,很明显,这其中有1个数字为“假数据”,So,找到Ta,解决Ta即可!
那如何从茫茫人海中找到这个“假数据”呢?
技巧2 : 函数判断法
在Excel中,判断一个数字是否是真正的数值,有一个函数ISNUMBER,当其结果为TRUE的时候,其为真数字,当其结果为FALES时,其为假数字(文本型数字)
从函数检测结果来看,很容易就分辨出了那些数据有问题
发现问题很重要,解决问题更重要
2
如何解决文本型数值参与的计算?
技巧3 : 文本转数值法
如果单元格里面的数值已经明确判定为文本型的“假数据”了,那可以采用文本转数值的模式,如下:
选中数据列,在右侧的提示框中选择转化为数值即可
技巧4 : 公式计算法
当然,如果单元格里面的数据是从其他表,通过公式引入过来的,又该如何处理呢?
无论单元格里面公式是什么,在其后乘以1(*1)即可,通过数据运算让文本型转化为数值型数据
当然,也许发懒的你,什么都不想改,想直接在求和中自动计算,又该如何处理呢?
公式1:=SUM(1*(A2:A7)) , 输入公式后,三键一起按(数组公式)
公式2:=SUMPRODUCT(1*(A2:A7))
其核心内涵,还是采用数据乘以1的方式,将文本转化为数值。
胖斯基|说
无论文本型数据也好,还是数值型数据也好,当掌握了上面4个小技巧时,真真假假,假假真真,便不那么重要了。
- ?
使用Excel2007进行简单求和运算
应天薇
展开
大家都知道Word里面也可以制作表格,但是在Word表格里进行一些运算和不方便,所以需要进行运算还是要用专业的Excel表格软件,Excel软件在运算方面功能非常强大,今天我们就来简单的学习一下使用Excel2007进行简单求和运算。
图1为我们的今天进行求和运算的Excel示例图,是一张简单的2013年年度销售表,我们把的数据进行求和运算,从而计算出来2013年的总销售额。
图1
首先我们需要把Excel表中的第二列从B2到B13选中,然后在导航栏选择"公式",在公式工具栏选择“自动求和”按钮,在弹出的下拉框里选择“求和”选项。
图2
图3
这样在Excel表的总计后面的单元格就计算出来了2013年从1月到12月的总销售额了。同时我们也可以在编辑栏对1到12月进行求和运算。
图4
我们首先选中总计后面的单元格,然后在编辑栏输入=SUM(B2:B13),然后敲回车键,2013年的总总销售额就计算出来了。解释:SUM是Excel中求和运算的一个函数,括号里的B2:B13意思就是计算从B2单元格到B13单元格的总和。
今天给大家讲了使用Excel2007进行简单求和运算的两种方法,其中第二种,我们用到SUM函数。Excel表格中计算求和运算非常的简单,如果我们不想计算出来结果,只想看看总和是多少的话,我们只需要选中需要计算的数据,然后看状态栏,状态栏就会计算出来选中数据的平均值和总和。
- ?
wps表格(excel)的自动求差(减法计算和公式)
红尘笑
展开
首先,给各位朋友说一句,我用的是WPS表格,wps表格和excel的功能基本上差不多,我感觉用WPS快些。
wps表格(excel)的自动求差(减法计算和公式)教程:
一、直接在单元格输入【=】,然后鼠标选中单元格,再输入【-】,最后按回车键
计算出来的差如图:
二、怎么让一列计算呢,可以直接复制公式,也可以:把鼠标放到单元格右下角会有一个+号 ,我们可以往下拖动,就会出现相应的公式了。
这是最简单的计算,用不到公式函数,但是有些新手朋友还是不会的,所以,我还是把教程写出来,教大家一下。给大家看一段我用的较为复杂的跨表多条件求各函数:=SUMIFS(日报表!$I:$I,日报表!$A:$A,$C$2,日报表!$D:$D,$A11)
关注我,以后我会慢慢的都把教程以最简单的方式写出来。
- ?
excel自动计算年龄
木棉花
展开
作为一名人力资源部的工作者,厂里的大部分人的名字我都记得,每到年初我都要重新统计下厂里员工的资料档案,以前都是手动计算输入员工的年龄的,其实利用excel自动计算年龄速度要比我们手动计算的快,而且精准。
1、打开要编辑的excel表格;
图1
2、把年龄列选中;
图2
3、按鼠标右键选择“设置单元格格式”;
图3
4、在数字选项中,选择数值型,小数位数选择“0”,单击确定;
图4
5、定位到E2单元格;
图5
6、输入“=(today()-D2)/365”;
图6
7、按键盘上的回车键或者点击如图的对勾;
图7
8、重新选择这个单元格;
图8
9、将光标移到单元格的右下角,当光标变成黑色十字时,双击;
图9
10、这样就计算出了所有人的年龄了,如图是最终效果。
图10
通过使用excel自动计算年龄我在制作excel员工档案表的时间比以前提高了2天啊,2天多么的恐怖啊,我不得不重新学习excel,因为我感觉到了以后一个企业如果不重视excel应该多么的可怕。
- ?
EXCEL带单位的数据进行运算,哪一招最好用呢?
埃莉卡
展开
场景:EXCEL中带单位的不能自动求和,但带单位的数据看起来更习惯,那么这样的数据要怎么求和呢?
可选方法:
1、分列法:用分列或快速填充方式将带单位的数据分成数据和单位两列,再进行计算。
2、公式法:用SUBSTITUTE函数将单位替换掉,然后再用SUMPRODUCT数组函数求和。
公式=SUMPRODUCT(--SUBSTITUTE(B2:B11,"瓶",""))
3、自定义格式法:
单元格实际只有数字,运用自定义格式让单元格看起来有单位。
选中单元格C2-C12,CTRL+1设置单元格格式→自定义→类型输入0“瓶”,这样即满足看起来有单位,又可以直接计算。
如果你还有更好更简便的方法,欢迎留言分享!
我是EXCEL学习微课堂,如果我的分享对您有帮助,欢迎点赞、收藏、评论、转发,更多的EXCEL技能,可以关注“EXCEL学习微课堂”。
- ?
Excel如何从身份证号码中提取年龄
谢洙
展开
因为年龄每年都会改变,所以Excel表格中储存的人员年龄每年都会不同,要保证表格中人员年龄的准确性,需要每年对表格内容进行更新,但要是每年都手动逐个更新表格中的年龄,工作量会比较大。如果表格中存有身份证号码,我们可以根据身份证号码中的出生年月信息计算出这个人的实际年龄,而且这个年龄会根据电脑中的时间在第二年自动更新,从而可以避免手动更新的麻烦。这里介绍一下操作方法,以供参考。
例如要根据下图表格中的身份证号码计算出实际年龄:
先在“年龄”列的第一个单元格中点击鼠标左键,选中这个单元格 。
选中单元格后,在Excel的编辑栏中输入 =YEAR(NOW())-MID((需要时可以把以上要输入的内容复制粘贴到Excel中,以避免输入字符的麻烦。)
输入以上字符后,用鼠标点击同一行左侧包含身份证号码的单元格。
点击单元格后,编辑栏中会自动输入该单元格的名称。
然后在单元格名称后再输入 ,7,4)这样即输入完成了根据身份证号码自动计算年龄的公式,整个公式为“=YEAR(NOW())-MID(C3,7,4)”。其中的“YEAR(日期值)”可以得到某个日期中的年份;“NOW()”可以得到当前电脑系统中的日期;“YEAR(NOW())”即可得到当前电脑系统中的年份;“MID”可以从字符串中得到指定数量的字符,“MID(C3,7,4)”表示得到从C3单元格中第7位字符开始的共4位字符,即身份证中的出生年份信息。这样,用“电脑中的年份”减去“身份证中的出生年份”即得到该人员的实际年龄。当然,前提是电脑系统的时间要准确。
输入公式后按键盘的回车键或者鼠标点击编辑栏左侧的对号按钮。
这样,输入公式的单元格中就会自动显示出对应身份证的实际年龄。
再将鼠标指针指向该单元格的右下角,当鼠标指针变成十字形状时双击鼠标左键或者向下拖动鼠标至列尾。
这样,年龄列中就会自动计算并且显示出各身份证对应的年龄了。
注:要保证计算年龄结果的正确性,必须让电脑系统的时间保持准确。