excel常用函数公式及技巧搜集5.docx
- 文档编号:18413890
- 上传时间:2023-08-16
- 格式:DOCX
- 页数:29
- 大小:30.10KB
excel常用函数公式及技巧搜集5.docx
《excel常用函数公式及技巧搜集5.docx》由会员分享,可在线阅读,更多相关《excel常用函数公式及技巧搜集5.docx(29页珍藏版)》请在冰点文库上搜索。
excel常用函数公式及技巧搜集5
excel常用函数公式及技巧搜集5
博客分类:
∙ExcelVBA及公式应用
对带有单位的数据如何进行求和
在数据后必须加入单位,到最后还要统计总和,请问该如何自动求和?
(例如:
A1:
2KG,A2:
6KG.....,在最后一行自动计算出总KG数)。
=SUMPRODUCT(--LEFT(A1:
A5,(LEN(A1:
A5)-2)))&”KG”
对a列动态求和
可以随着a列数据的增加,在“b1”单元格=sum(x)对a列动态求和。
=SUM(OFFSET(A1,0,0,COUNTA(A:
A),1))
动态求和公式
自A列A1单元格到当前行前面一行的单元格求和。
=SUM(INDIRECT("A1:
A"&ROW()-1))
列的跳跃求和
若有20列(只有一行),需没间隔3列求和,该公式如何做?
假设a1至t1为数据(共有20列),在任意单元格中输入公式:
=SUM(IF(MOD(TRANSPOSE(ROW(1:
20)),3)=0,(a1:
t1))
按ctrl+shift+enter结束即可求出每隔三行之和。
跳行设置:
如有12行,需每隔3行求和
=SUM(IF(MOD((ROW(1:
12)),3)=0,(A1:
A12)))
有规律的隔行求和
要求就是在计划、实际、差异三项中对后面的12个月求和。
=SUMPRODUCT(--(MOD(COLUMN(F3:
AO3)-CELL("Col",F3)+0,3)=0),F3:
AO3)
=SUMIF($F$2:
$AO$2,C$2,$F3:
$AO3)
=SUMPRODUCT((MOD(COLUMN($F3:
$AO3),3)=MOD(COLUMN(F3),3))*$F3:
$AO3)
也可以拖动填充,插入行、列也不影响计算结果。
如何实现奇数行或偶数行求和
假设数据在A1:
A100
奇数行:
=SUMPRODUCT(MOD(ROW($A$1:
$A$100),2)*$A$1:
$A$100)
偶数行:
=SUMPRODUCT((MOD(ROW($A$1:
$A$100),2)=0)*($A$1:
$A$100))
奇数行求和=SUMPRODUCT((A1:
A100)*MOD(ROW(A1:
A100),2))
偶数行求和=SUMPRODUCT((A1:
A100)*NOT(MOD(ROW(A1:
A100),2)))
单数行求和
隔行求和用什么函数,即:
A1+A3+A5+A7+A9…公式如何用。
{=SUM(N(OFFSET(A1,ROW(1:
50)*2-2,)))}
{=SUM(IF(MOD(ROW(A1:
A100),2)=1,A1:
A100,0))}
统计偶数单元格合计数值
统计F4到F62的偶数单元格合计数值。
{=SUM(IF(MOD(ROW(F4:
F62),2)=0,F4:
F62))}
隔行求和公式设置
均为数组公式:
=SUM(IF(MOD(ROW(A1:
A110),2),A1:
A110,0))
=SUM(N(OFFSET($A$1,ROW(1:
55)*2-2,,,)))
=SUM((MOD(ROW(A1:
A100),2)=1)*(A1:
A100))
=SUM((MOD(ROW(A1:
A100),2)=0)*(A1:
A100))
=SUMPRODUCT((MOD(ROW(A1:
A100),2)=0)*A1:
A100)
隔列将相同项目进行求和
隔列将出勤日和工资分别进行求和
数组公式=SUM(IF(($B$4:
$B$25)=B26,($C$4:
$C$25),0))
或;
=SUMPRODUCT(--(MOD(ROW(C5:
C25),2)<>0),C5:
C25)
隔行或隔列加总
隔2列加总
=SUM((MOD(ROW($A$1:
$A$25),2)=0)*$A$1:
$A$25)
隔2栏加总
=SUM((MOD(COLUMN($B$1:
$T$1),2)=0)*$B$1:
$T$1)
请问如何在一百行内做隔行相加
数组公式
A1+A3+……+A99 单
=SUM(N(OFFSET(A1,ROW(1:
50)*2-2,)))
A2+A4+……+A100 双
=SUM(N(OFFSET(A1,ROW(1:
50)*2-1,)))
如何将间隔一定的列的数据相加呢
碰到100多列的数据将间隔一定的数据用手工相加太烦了,也容易出错。
如果需要相加的数据均有相同的名称(字段),可以用Sumif()来求解,如果没有,就需要用数组公式来解决了。
{=SUM((MOD(ROW(A1:
A18),3)=1)*A1:
A18)} 1、4、7……行相加。
隔列求和(A、B列)
=SUM(A:
A,B:
B)
=SUM(A:
A,B:
B,C:
C) (统计A、B、C列)
隔列求和的公式
品种及日期
1月1日
1月2日
1月3日
1月4日
1月5日
余额
进
出
进
出
进
出
进
出
进
出
A
1
1
2
5
3
2
7
9
8
1
3
=SUMIF($B$2:
$K$2,"进",B3:
K3)-SUMIF($B$2:
$K$2,"出",B3:
K3)
=SUM(SUMIF(B$2:
K$2,{"进","出"},B3:
K3)*{1,-1})
隔列求和
类别
成品代码
单价
安贞
北辰
长安
长春
合计
库存
销售
库存
销售
库存
销售
库存
销售
库存
销售
皮带
V19201
270.00
1
2
1
2
1
2
1
2
库存合计=SUMIF($D$3:
$BS$3,"库存",$D$4:
$BT$4),
销售合计=SUMIF($D$3:
$BS$3,"销售",$D$4:
$BT$4)
=SUMIF($D$3:
$BS$3,BT$3,$D4:
$BS4)
=SUMPRODUCT((MOD(COLUMN($D4:
$BS4),2)=0)*$D4:
$BS4)
关于隔行、隔列求和的问题
隔2列加总
=SUM((MOD(ROW($A$1:
$A$25),2)=0)*$A$1:
$A$25)
隔2行加总
=SUM((MOD(COLUMN($B$1:
$T$1),2)=0)*$B$1:
$T$1)
均为数组公式。
EXCEL中求两列的对应元素乘积之和
如:
a1*b1+a2*b2+b3*b3...的和
=SUM(A1:
A3*B1:
B3)(数组公式)
=SUMPRODUCT(A1:
A10,B1:
B10)
计算900~1000之间的数值之和
sumif函数的计算格式为:
=sumif($a$1:
$a$20,">1000")。
即返回$a$1:
$a$20中大于1000的数值的和,但如果想计算900~1000之间的数值之和,应该如何编写。
请参考:
{=SUM(IF((A1:
A20>900)*(A1:
A20<1000),A1:
A20))}或{=SUM((900 A20)*(A1: A20<1000)*A1: A20)} 2、=SUMIF(A1: A20,">900")-SUMIF(A1: A20,">1000") 双条件求和 1、求一班女生的个数: =SUMPRODUCT((A2: A9=1)*(B2: B9=""女"")) 2、求一班成绩的和: =SUMIF(A2: A9,1,C2: C9)" 3、求一班男生成绩的和: =SUMPRODUCT((A2: A9=1)*(B2: B9=""男""),C2: C9)" 如何实现这样的条件求和 求型号中含BC但不含ABC的量: A B C 型号 数量 1 CRVABC12 100 2 CVABC13 102 3 CVBC12 104 4 CNVBC13 106 =SUMIF($A$2: $A$12,"*"&"bc"&"*",$B$2: $B$12)-SUMIF($A$2: $A$12,"*"&"abc"&"*",$B$2: $B$12) =SUMPRODUCT((ISNUMBER(FIND("BC",A2: A12))<>ISNUMBER(FIND("ABC",A2: A12)))*B2: B12) A1: A10数字显为文本格式时,如何求和 =SUMPRODUCT(A1: A10) 求和 所有本范例所使用的数据都为引用以下绿色区域,并定义为对应的标题。 Name Sex Age Position Salary 张无忌 男 26 主角 10000 韦小宝 男 16 主角 13000 灭绝 女 55 配角 3000 周芷若 女 22 主角 8000 鳌拜 男 62 普通演员 2000 仪琳 女 18 配角 5000 岳灵珊 女 19 配角 4500 令狐冲 男 27 主角 15000 性空 男 88 普通演员 2200 东方不败 不详 45 主角 9000 A 求所有演员工资总额 71700 =SUM($G$7: $G$16) 简单求和 B 求男演员工资总额 42200 =SUMIF($D$7: $D$16,"男",$G$7: $G$16) 单条件求和.1 C 求年龄在20岁以下的演员工资 22500 =SUMIF($E$7: $E$16,"<20",$G$7: $G$16) 单条件求和.2 D 求主角和配角的工资(不是普通演员) 67500 =SUMIF($F$7: $F$16,"*角",$G$7: $G$16) 单条件求和.3 E 求20岁以下女演员工资 9500 {=SUM(($D$7: $D$16="女")*($E$7: $E$16<20)*$G$7: $G$16)} 多条件求和-同时满足条件 F 求男性或主角的工资 59200 {=SUM(IF(($D$7: $D$16="男")+($F$7: $F$16="主角"),$G$7: $G$16))} 多条件求和-只须满足条件之一 G 求男性非主角或主角非男性的工资(即除男主角外的男性和主角) g.1 21200 {=SUM(IF(($D$7: $D$16="男")-($F$7: $F$16="主角"),$G$7: $G$16))} g.2 21200 {=SUM(IF(($F$7: $F$16="主角")-($D$7: $D$16="男"),$G$7: $G$16))} 多条件求和-只满足条件之一而不能同时满足 H 啊~~~你不知道什么是数组函数啊,可是你有时候也要用多条件求和? 不要紧,教你用另外的方法: SUBTOTAL 求20岁以下女演员工资 71700 =SUBTOTAL(9,$G$7: $G$16) 现在你看到的还不是最后结果,请按如下操作 1、把数据区域设置成可筛选 2、把SEX筛选成"=女",把年龄筛选成<20 3、你再看上面的公式结果… 去掉其中两个最大值和两个最小值,再求和 请问如何去掉两个最高分,两个最低分,剩余人员的分数求和,例如A1-A7中的7个分,去掉两个最高分,两个最低分,剩余人员的分数求和。 =SUM(A1: A50)-MAX(A1: A50)-LARGE(A1: A50,2)-MIN(A1: A50)-SMALL(A1: A50,2) =SUM(A1: A20)-SUM(LARGE(A1: A20,{1,2}))-SUM(SMALL(A1: A20,{1,2})) =TRIMMEAN(A1: A7,4/7)*(7-4) =SUMPRODUCT(LARGE(A1: A7,ROW(A1: A7))*(ROW(1: 7)>2)*(ROW(1: 7)<6)) =SUMPRODUCT((A$1: A$7 A$7,2))*(A$1: A$7>SMALL(A$1: A$7,2))*A$1: A$7) =SUM(A! : A7)-LARGE(A! : A7,1)-LARGE(A! : A7,2)-SMALL(A! : A7,1)-SMALL(A! : A7,2) 将此函数横着使用(A1-G1) =TRIMMEAN(A1: G1,4/7)*(7-4) =SUMPRODUCT(LARGE(A1: G1,COLUMN(A1: G1))*(COLUMN(A: G)>2)*(COLUMN(A: G)<6)) 去掉两个最高分、最低分,显示出被去掉的分数 被去掉的分数: 最大两个: =large(data,{1;2}) 最小两个: =small(data,{1;2}) 永恒的求和 1、=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以对A列数值自动求和。 2、=SUM(INDIRECT("R2C: R[-1]C",FALSE)) 3、=SUM(INDIRECT("A2: A"&ROW()-1)) =SUM(INDIRECT(ADDRESS(1,COLUMN())&": "&ADDRESS(ROW()-1,COLUMN()))) 按字体颜色求和 做法: G3={SUM(IF(($A$2: $A$19=E3)*($B$2: $B$19=F3),$C$2: $C$19))} G4: G11公式为G3公式下拖. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 如何分班统计男女人数 男=SUMPRODUCT(($B$2: $B$446=$E2)*($C$2: $C$446=F$1)) =SUMPRODUCT(($B$2: $B$446=E3)*($C$2: $C$446=F$1)) =SUMPRODUCT(($B$2: $B$446=$E2)*($C$2: $C$446=F$1)) {=SUM(($B$2: $B$446=$E2)*($C$2: $C$446=$F$1))} {=SUM(($B$2: $B$446=F2)*($C$2: $C$446=$G$1)*$D$2: $D$446)} 女=SUMPRODUCT(($B$2: $B$446=$E2)*($C$2: $C$446=G$1)) 合计=COUNTIF($B$2: $B$446,E2) 统计数值大于等于80的单元格数目 在C17单元格中输入公式: =COUNTIF(B1: B13,">=80") 确认后,即可统计出B1至B13单元格区域中,数值大于等于80的单元格数目。 计算出A1里有几个abc A1: abc-ded-abc-def-abc-ded-ded-abc,如何计算出A1里有几个abc 公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc") 有条件统计 如何统计当A1<=15时,统计B列中<=8.5的累加值和个数,而>15时不进行统计? 个数: =IF(A1>15,"",COUNTIF(B2: B10,"<=8.5")) 累加值(求和): =IF(A1>15,"",SUMIF(B2: B10,"<=8.5")) 如何统计各年龄段的数量 需分别统计20岁以下、21-30岁、31-40岁、41-50岁、50岁以上年龄段的数量。 根据“出生日期”用以下公式,得到“自动显示年龄”。 先将F列的出生日期设置为“1976年5月”格式,在G列公式为: =DATEDIF(F2,TODAY(),"Y") (周岁,自动显示年龄) =YEAR(TODAY())-YEAR(F2) 再根据年龄段: 20岁以下、21-30岁、31-40岁、41-50岁、50岁以上,用以下公式,求出不同年龄段人数。 在J2公式为: =SUMPRODUCT(($G$2: $G$34>$H1)*($G$2: $G$34<=$H2)*($C$2: $C$34=J$1)) {=SUM(($G$2: $G$34<=VALUE(MID(I2,1,2)))*1)} 或数组公式: {=SUM(($G$2: $G$34<=VALUE(MID(I3,4,2)))*1)-SUM($J$2: J2)} 如何计算20-50岁的人数? =COUNTIF(C3: C17,">=20")-COUNTIF(C3: C17,">50") =SUMPRODUCT((C3: C17>=20)*(C3: C17<=50)) =FREQUENCY(C3: C17,50)-FREQUENCY(C3: C17,19) {=SUM(COUNTIF(C3: C17,">="&{20,51})*{1,-1})} 如何统计40-50岁的人的个数 =countif(a: a,">40")-countif(a: a,">50") =SUM(COUNTIF(a: a,">"&{40,50})*{1,-1}) 数组公式{=sum((a1: a7>40)*(a1: a7<50))} =SUMPRODUCT((A1: A7>40)*(A1: A7<50)) 要统计出7岁的女生人数 =COUNTIF(D2: D12,D2) =SUMPRODUCT((B2: B12="女")*(D2: D12=7)) 统计人数 =COUNTA(A: A) =COUNTIF(A: A,">") 如何统计A1: A10,D1: D10中的人数? =COUNTA(A1: A10,D1: D10) 如何让EXCEL自动从头统计到当前单元格 情况如下: C列要根据A列的内容来统计B列的数据,范围从A1: An,即当A列中An有数据时,Cn自动根据An的值,统计B1: Bn的数据。 {=SUM(INDIRECT("B1: B"&LARGE((A1: A65535<>"")*(ROW(A1: A65535)),1)))} 统计人数 建议 提建议人员姓名 提建议人数 建议1 王、李、赵、孙、钱、胡 6 建议2 张、王、李、赵、孙、钱、胡 7 建议3 张、王、李、孙、钱、胡 6 =LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1 =LEN(SUBSTITUTE(B2,"、","")) 统计人数 见表: 性别 年龄 男 6 女 35 男 3 男 55 男 21 男 53.5 女 55 女 56 男 65 女 45 女 53 男 51 如何计算20-50岁的人数? =COUNTIF(C3: C17,">=20")-COUNTIF(C3: C17,">50") =SUMPRODUCT((C3: C17>=20)*(C3: C17<=50)) =FREQUENCY(C3: C17,50)-FREQUENCY(C3: C17,19) {=SUM(COUNTIF(C3: C17,">="&{20,51})*{1,-1})} 如何计算男20-50岁的人数? =SUMPRODUCT((B3: B17="男")*(C3: C17>=20)*(C3: C17<=50)) 求各分数段人数 90—100 =COUNTIF(B2: B43,">=90") 80—89 =COUNTIF(B2: B43,">=80")-COUNTIF(B2: B43,">=90") 70—79 =COUNTIF(B2: B43,">=70")-COUNTIF(B2: B43,">=80") 60—69 =COUNTIF(B2: B43,">=60")-COUNTIF(B2: B43,">=70") 50—59 =COUNTIF(B2: B43,">=50")-COUNTIF(B2: B43,">=60") 有什么方法统计偶数 例如: A1到E1有5个数如何统计着五个数中有几个是偶数 ABCDEF 15015811153 在F1中的3要用什么公式能统计出来 统计偶数的个数 {=COUNT(1/MOD(A1: E1-1,2))} {=Sum(Mod(a1: e1+1,2))} 将偶数转化成奇数,再求奇数的个数。 请在编辑栏中选择部分公式按F9观察每一步的计算过程。 {=SUM(--((A1: F1)/2=INT((A1: F1)/2)))} 算是一法,长了点 =SUMPRODUCT((MOD(A1: E1,2)=0)*1) =SUMPRODUCT(1-MOD(A1: E1,2)) 如何显示 如果D2>20那E2就显示$200、如果D2>30那E2就显示$300依此类推 解答: =INT(D2/10)*100,当然,你的单元格格式设置成$格式就可以了。 否则用,="$"&INT(D2/10)*100 则该单元格成字符型。 当然,你也可以用IF函数,但它有7层的限制。 =IF(D2>30,"300",IF(D2>20,"200")) 工资统计中的问题 问题: 表一和表二中的职工姓名相同,但不在同一个位置上。 怎样用公式求出表一中职工在表二中对应的工资、奖金和值班费的总额。 要求,不能用表二中先加入一列,然后求和,再用公式导入表一的方法。 我想知道能否在表一中用一个公式就可实现,而表二不动。 =SUMPRODUCT((表二! $B$3: $B$42=A3)*(表二! $C$3: $E$42)+(表二! $G$3: $G$42=A3)*(表二! $H$3: $J$42)) =IF(COUNTIF(表二! $B$3: $B$42,A3),SUMPRODUCT(VLOOKUP(A3,表二! $B$3: $E$42,{2;3;4},)),SUMPRODUCT(VLOOKUP(A3,表二! $G$3: $J$42,{2;3;4},))) =IF(ISERROR(MATCH(A3,表二! $B$3: $B$42,0)),SUM(OFFSET(表二! $G$2,MATCH(A3,表二! $G$3: $G$42,0),1,,3)),SUM(OFFSET(表二! $B$2,MATCH(A3,表二! $B$3: $B$42,0),1,,3))) =IF(ISERROR(VLOOKUP(A3,表二! $B$3: $F$42,4,0)),SUM(INDIRECT("表二! H"&MATCH(A3,表二! $G$3: $G$42,0)+2&": J"&MATCH(A3,表二! $G$3: $G$42,0)+2)),SUM(INDIRECT("表二! C"&MATCH(A3,表二! $B$3: $B$42,0)+2&": J"&MATCH(A3,表二! $B$3: $B$42,0)+2))) =IF(ISERROR(VLOOKUP(A3,表二! $B$3: $F$42,4,0)),VLOOKUP(A3,表二! $G$3: $J$42,4,0),VLOOKUP(A3,表二! $B$3: $F$42,4,0)) 统计数据问题一例 如果我想统计50个数据中大于某个值的数据个数,(这个值是在使用时才输入某个单元格的),请问用什么函数。 如数据单元格为A1: E10,值的单元格为A11。 1、使用下面的数组公式: {=SUM(IF($A$1
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 常用 函数 公式 技巧 搜集
![提示](https://static.bingdoc.com/images/bang_tan.gif)