office excel最常用函数公式技巧搜集大全(13.12.09更新)导读：请问如何编写公式求出A1到A10单元格中数字4连在一起的次数，[1]A1到A10单元格中,数字4连在一起,最大的连续次数,公式为:，[2]次大的连续长次数,公式为:，用数组公式也可以解决呀:假设你要统计A1到A100可以这样:，公式分别为：=COUNT(A2:A12)=COUNTA(B2:B12)=COUN，输入公式：=B2&C2，合并数据后再利用countif公式对D列统计，选公式( 
如图，请问如何编写公式求出A1到A10单元格中数字4连在一起的次数，本例中答案应为3（A1到A3）和2（A9到A10）。 [1] A1到A10单元格中, 数字4连在一起, 最大的连续次数, 公式为 : {=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),1)} [2] 次大的连续长次数, 公式为 : {=LARGE(FREQUENCY(IF(A1:A10=4,ROW(A1:A10),""),IF(A1:A10<>4,ROW(A1:A10),"")),2)} 3个“不重复”个数统计 =SUM(--IF(MATCH(B$2:B$21,B$2:B$21,0)=ROW(B$2:B$21)-1,B$2:B$21>B2))+1 =SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1 =SUM(--(FREQUENCY(IF(B$2:B$21>B2,B$2:B$21),B$2:B$21)>0))+1 在一列有重复的姓名中，如何统计出具体有几人 如果第一个张三在A1单元格，在B1处输入： =IF(COUNTIF($A$1:A1,A1)>1,"",A1) 向下复制即可 用数组公式也可以解决呀:假设你要统计A1到A100可以这样: =sum(1/countif(a1:a100,a1:a100),然后按住crtl,shift,和回车就可以了。 计数的问题 这个例子主要是计数的问题：共有三列数据，分别统计每列字母的个数、每列有几个不 同的字母，最后把它们分别列出来。对每列字母个数统计，字符用COUNTA()，数字可以用COUNT()和COUNTA()。公式分别为： =COUNT(A2:A12) =COUNTA(B2:B12) =COUNTA(C2:C12) 每列不相同的字母，公式分别为： ｛=SUM(1/COUNTIF(A$2:A$12,A$2:A$12))｝ ｛=SUM(1/COUNTIF(B$2:B$12,B$2:B$12))｝ ｛=SUM(1/COUNTIF(C$2:C$12,C$2:C$12))｝ 分别列出来，公式分别为： ｛=IF(SUM(1/COUNTIF(A$2:A$12,A$2:A$12))>=ROW(A1),INDEX(A$2:A$12,SMALL(IF(ROW(A$2:A$12)-1=MATCH(A$2:A$12,A$2:A$12,0),ROW(A$2:A$12)-1,"0"),ROW(A1))),"END")｝ ｛=IF(SUM(1/COUNTIF(B$2:B$12,B$2:B$12))>=ROW(B1),INDEX(B$2:B$12,SMALL(IF(R
65 OW(B$2:B$12)-1=MATCH(B$2:B$12,B$2:B$12,0),ROW(B$2:B$12)-1,"0"),ROW(B1))),"END")｝ ｛=IF(SUM(1/COUNTIF(C$2:C$12,C$2:C$12))>=ROW(C1),INDEX(C$2:C$12,SMALL(IF(ROW(C$2:C$12)-1=MATCH(C$2:C$12,C$2:C$12,0),ROW(C$2:C$12)-1,"0"),ROW(C1)))," 如何分班统计男女人数
66 =SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1)) 女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1)) 男｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝ 女｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$G$1))｝ 男｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝ 女｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)｝ 增加d列，输入公式：=B2&C2，合并数据后再利用countif公式对D列统计。 =COUNTIF($B$2:$B$446,E2) 在几百几千个数据中发现重复项 我的意思不是查找功能,那个我会用,比如有几百个人的名字输入单元格中,但我面对那么多名字真无法短时间内看出谁重复了,该如何办? 假设判断区域为A1:D10,格式/条件格式，选公式(不是数值)，输入： =COUNTIF($A$1:$D$10,A1)>1 然后在格式中设置一个字体或图案颜色,确定,这样重复数据就变成了有色单元格。 统计互不相同的数据个数 例如,在 3 * 3 的区域中统计互不相同的数据个数, 1 2 3 3 2 1 1 2 0 结果应为 4 (4 个互不相同的数据) 数组公式=sum(1/countif(a1:c3,a1:c3)) 还可以公式: =COUNT(IF(FREQUENCY(A1:C3,A1:C3),1)) 多个工作表的单元格合并计算 =Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4) 单个单元格中字符统计 假设 A1单元格中有数据"sdfsfjksfhweofiefondsfljsdfisdofjei" 如何用公式统计出A1单元格中有多个不重复的字符? =SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1)) 数组公式=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),A1)),,1)) 这个公式只适用单元中的字符为小写字母,给个通用点的 =SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2))))) =SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1)) 数据区包含某一字符的项的总和,该用什么公式 =sumif(a:a,"*"&"某一字符"&"*",数据区)
67 函数如何实现分组编码 对数值进行分组编码 =A2&TEXT(COUNTIF($A$2:A2,A2),"00") ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 【数值取整及进位】 取整数函数 907.5；1034.2；1500要改变为908；1035；1500公式为： =CEILING(A1,1) 907；1034；1500要改变为910；1040；1500公式为： =CEILING(A1,10) 如果要保留到百位数，即改变为1000；1100；1500公式为： =CEILING(A1,100) 数值取整 在单元格中要取整数（只取整数不用考虑四舍五入）用什么函数呀？例如：10/4只要显示2就可以了！要考虑负数的因数呢？例如：(-10/4)要显示－2而不是－3？怎么办？ =TRUNC(A1,0) =ROUNDDOWN(A1,0) 求余数的函数 比如：A1=28，A2=（A1÷6）的余数=4，请问这个公式怎么写？ 解答：=MOD(28,6) 四舍五入公式 =ROUND() =ROUND($B$1*A1,2) =ROUND(B1*A1,2) =round(a1,0) =round(a1,0)*0.95 对数字进行四舍五入 对于数字进行四舍五入,可以使用INT（取整函数），但由于这个函数的定义是返回实数舍入后的整数值。因此，用INT函数进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。公式应写成： =INT(B2*100+0.5)/100 如何实现“见分进元” 在我们的工资中，有一项“合同补贴”，只要计算结果出现“分”值就在整数“元”进一位，也就是说3.01元进到4.00元，3.00元不变，整数“元”不变。 =IF((A3-INT(A3))>=0.3,IF((A3-INT(A3))>=0.8,1,0.5),0)+INT(A3)
68 五星文库包含总结汇报、考试资料、专业文献、IT计算机、计划方案、党团工作、教学教材、旅游景点、出国留学以及office excel最常用函数公式技巧搜集大全(13.12.09更新)等内容。 |
|
|
