Excel常用电子表格公式大全

2011-04-24 08:53:41

1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。

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) ——对K2 K56这一区域求平均数；

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显示红色

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()))

将公式向下填充，直到出错为止，目录就生成了。

源地址：

https://www.360docs.net/doc/0e4881833.html,/GetEntry.do?id=716894565&owner=230043605

excel中不重复提取数字的问题

a1到a10分别是1111222334（每个单元格一个数字），要求在b1到b4中得到1234。一定要用公式才行哟。即最后得到的形式是：A B 1 1

1 2

1 3

1 4

B1单元格输入公式

数据范围A1:A20必须有数据,视具体单元格数据区域自己调整A1:A20,注意绝对引用

数组公式,输入后按SHIFT+CTRL+ENTER结束,然后下拉填充

=INDEX($A:$A,SMALL(IF(MATCH($A$1:$A$20,$A$1:$A$20,)=ROW($A$1:$A$20),ROW($A$1:$A$20),4^8),ROW()))&""

=IF(ROW()>SUM(1*(COUNTIF($A$1:$B$9,$A$1:$B$9)=1)),"",INDIRECT("R"&RIGHT(SMALL(IF(COUNTIF($A$1:$B$9,$A$1:$B$9)>1,"",ROW( $A$1:$B$9)+100*COLUMN($A$1:$B$9)),ROW()),1)&"C"&LEFT(SMALL(IF(COUNTIF($A$1:$B$9,$A$1:$B$9)>1,"",ROW($A$1:$B$9)+100*CO LUMN($A$1:$B$9)),ROW()),1),))

把上面公式中的row() 替换成column() 可在同一行右拉公式

统计不同数值的数量：= SUM(1/COUNTIF(区域,区域))

Excel中提取不重复值函数

=IF(COUNTIF(区域,第一个单元格)>=1,第一个单元格,"") 例：=IF(COUNTIF($I10:$U10,I10)>=1,I10,"")

按大小重排数字：=SMALL(区域,COLUMN(A1)) 只调区域，A1不能动。例：=SMALL($A3:$F3,COLUMN(A1))

excel提取一列中不重复的数据到别一处

1.｛=INDEX(A:A,SMALL(IF(MATCH(A$1:A$7,A$1:A$7,)=ROW($1:$7),ROW($1:$7),4^8),ROW(A1)))&""

目前这样的公式不能理解是正常的，会用就行了，但要记得这是个数组公式。

注解：

函数名称：INDEX

主要功能：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。

使用格式：INDEX(array,row_num,column_num)

参数说明：Array代表单元格区域或数组常量；Row_num表示指定的行序号（如果省略row_num，则必须有 column_num）；Column_num 表示指定的列序号（如果省略column_num，则必须有 row_num）。

应用举例：如图3所示，在F8单元格中输入公式：=INDEX(A1:D11,4,3)，确认后则显示出A1至D11单元格区域中，第4行和第3列交叉处的单元格（即C4）中的内容。

------------------------------

SMALL(array,k)

Array为需要找到第 k 个最小值的数组或数字型数据区域。

K为返回的数据在数组或数据区域里的位置（从小到大）。

注解

如果 array 为空，函数 SMALL 返回错误值 #NUM!。

?如果 k ≤ 0 或 k 超过了数据点个数，函数 SMALL 返回错误值 #NUM!。

?如果 n 为数组中的数据点个数，则 SMALL(array,1) 等于最小值，SMALL(array,n) 等于最大值。

------------------------------

我们常常需要对两个排列不一样的数据表进行比较，查找出它们之间的不同，那么可以用match()函数来完成。

MATCH( )

用途：

返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH 函数。注意：MATCH函数返回lookup_array 中目标值的位置，而不是数值本身。

语法：

MATCH(lookup_value，lookup_array，match_type)。

参数：

Lookup_value为需要在数据表中查找的数值，它可以是数值(或数字、文本或逻辑值)、对数字、文本或逻辑值的单元格引用。

Lookup_array是可能包含所要查找的数值的连续单元格区域，Lookup_array可以是数组或数组引用；

Match_type 为数字-1、0或1 ，它说明Excel 如何在lookup_array 中查找lookup_value。

如果match_type 为1，函数MATCH 查找小于或等于lookup_value 的最大数值。如果match_type 为0，函数MATCH 查找等于lookup_value 的第一个数值。

如果match_type 为-1，函数MATCH 查找大于或等于lookup_value 的最小数值。

如果match_type 为0 且lookup_value 为文本，lookup_value可以包含通配符(“*”和“?”)。星号可以匹配任何字符序列，问号可以匹配单个字符。

实例：如果A1=68、A2=76、A3=85、A4=90，则公式“=MATCH(90，A1:A5，0)”返回4。

--------------------------------

拓展：

Excel中Small和Column函数配合实现排序

2010-08-26 22:45:52 来源：IT部落窝浏览：2258次

在前面教程讲过一些使用RANK函数和“数据/排序”菜单来实现excel中的排序操作。使用排序的方法通常只能解决一行或一列数字的排序。

如果是多行或多列区域数据排序，可以参考下面的方法来实现。

下面我们以一个实例加以讲解。详见下图。

其中A到E列为原始数据，如何将A1至E8区域的数据，让每行的数据按照从小到大的顺序重新排序？

excel数据排序的操作步骤是这样的：

单击G1单元格，在编辑栏输入公式：=SMALL($A1:$E1,COLUMN(A1))，确定。并再次选中G1单元格，向右复制至K1单元格。然后再选中G1:K1单元格区域,向下复制公式至K8单元格结束。

通过公式的形式，即可完成数据从小到大的排序。注：如果是将数据按照从大到小来排序，只需将SMALL变为LARGE即可。

完成排序操作后，也可以根据实际需要隐藏原数据列A:E区域；也可以将G:K列的数据复制后，进行选择性粘贴－数值，覆盖原始数据列。

excel表格中从多个单元格里提取相同的数字的公式(从小到大排序)

①从A1,B1二个单元格中提取相同的数字的公式

{=IF(COUNT(FIND(0,A1:B1))>1,0,)&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A1)*FIND(ROW($1:$9),B1))*ROW($1:$9)*10^(9-RO W($1:$9))),0,)}

②从A1,B1,C1三个单元格中提取相同的数字的公式

{=IF(COUNT(FIND(0,A1:C1))>2,0,)&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A1)*FIND(ROW($1:$9),B1)*FIND(ROW($1:$9),C1))*R OW($1:$9)*10^(9-ROW($1:$9))),0,)}

③从A1,B1,C1,D1四个单元格中提取相同的数字的公式

{=IF(COUNT(FIND(0,A1:D1))>3,0,)&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A1)*FIND(ROW($1:$9),B1)*FIND(ROW($1:$9),C1)*FI ND(ROW($1:$9),D1))*ROW($1:$9)*10^(9-ROW($1:$9))),0,)}

用EXCEL提出一区域中的数字为一列且各数字只提取一次(重复数也只提一次)并排大小

=IF(COUNTIF($A$1:$AN$11,COLUMN(A1))>0,COLUMN(A1),"")

excel中，统计区域内出现的数字个数（重复的除外），并显示

如：

一行：1、2、3

二行：2、3、4

三行：5、6、7

d列显示7（1、2、3、4、5、6、7）

如果数据很多，且数据不断增多怎么办

解答

1:统计个数用数组公式

=SUM(1/COUNTIF(A1:C3,A1:C3))

按CTRL+SHIFT+ENTER三键结束

2:显示分两步

2.1:

D1输入公式

=OFFSET($A$1,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3))

公式下拉到D9,然后复制D列为数值

2.2:

E1输入：

=IF(SUM(1/COUNTIF($D$1:$D$9,$D$1:$D$9))>=ROW(A1),INDEX($D$1:$D$9,SMALL(IF(ROW($D$1:$D$9)=MATCH($D$1:$D$9,$D$1:$D$9, 0),ROW($D$1:$D$9),"0"),ROW(A1))),"")

数组公式，按CTRL+SHIFT+ENTER三键结束，往下拉，就能出现了

可以用函数CEILING()——向上四舍五入取整数。格式——CEILING（被四舍五入的数,保留小数的精度）。

举例：

A列B列C列

5 3 2

5 3 1.7

5 3 1.67

5 3 1.667

C1：C4中依次的公式及精度：

=CEILING(A1/B1,1)——精确到个位

=CEILING(A9/B9,0.1)——精确到十分位

=CEILING(A9/B9,0.01)——精确到百分位

=CEILING(A9/B9,0.001)——精确到千分位

为了保证显示结果与计算结果一致，在“工具/选项/重新计算”“工作簿选项”中设定“以显示精度为准”。

=ROUND(BO$17,1)

=ROUND(BO$17,0)

=ROUND(BO$17,-1)

ROUNDDOWN(number,num_digits)

Number 为需要向下舍入的任意实数。

Num_digits 四舍五入后的数字的位数。

提取最大、最小值

=MAX(K31,L31,M31,N31,J31)

=MIN(K31,L31,M31,N31,J31)

四、如何只显示（筛选）奇数行或偶数行？

在后面空白列(假定为F列)的第2行输入筛选条件:

等号MOD(ROW(A2),2)=0

选定该表所有数据列,点"数据"->"筛选"->"高级筛选",条件区域选择: 等号$F$1:$F$2

点"确定"。这样就只显示偶数行。

如果要只显示奇数行，将公式改为：

等号MOD(ROW(A2),2)

显示整公式（计算用时仍为小数）

等号INT(计算公式)

小数转换为整数（4舍5入）

如果是有公式的单元格取整用＝round(公式，0)，要是只是数值的话，点右键在选单元格式，接着选数量，接着会看到小数位数，改为0就行了

等号rounddown(a1,0) 这是全舍；4舍5入是:=roundup(a1,0) 请选择使用

怎样设置EXCEL单元格里的数字当大于某一数字（10）时自动减去一个数（10）

IF(A1+B1>10,A1+B1-10,A1+B1)

MoD函数

当单元格数字大于16 时，减少到16内

mod(公式，16）

excle中单元格内的数字大于10,如何自动减12，直至数字小于12

IF(OR(A1<=10,A1>11),MOD(A1,12),A1-12)

个位数+十位数

MOD(INT(MOD(单元格,100)/10)+MOD(单元格,10),10)

（注释：公式===十位数+个位数）

MID函数

从一个字符串中取子字符串，比如a="aabbcc",我们想取出"bb"就可以用

Mid("aabbcc",3,2)

Mid有3个参数，第一参数是要从哪个字符串中取。

第二个参数是指从第几个开始取。

第三个参数是指取几个。

例如：Mid("aabbcc",3,2)就是指从"aabbcc"的第3个字符开始，取2个字符，因此返回值为"bb"。

count 数值单元格数量

counta 非空单元格数量

countblank 空单元数

countif(区域,"*") 文本单元格数量

假设字符串写在A2单元格中而公式写在B2单元格，统计“1”的个数的公式可以这样写：B2=LEN(A2)-LEN(SUBSTITUTE(A2,"1",""))

如何统计一行数字中的大于或小于条件？

例：

A B C D E F G

1 10 20 35 15 4 57 需要显示的地方

可能是我表述错误，现更改一下：

我现在要的结果是：如果A1到F1这一行其中任意一列（这里改了一下）的数字小于5，那么G1显示NO，如果都大于5，就显示OK，

数组公式=IF(SUM(IF(A1:G1>5,1,0)),"no","ok")

输入完之后同时按Ctrl+Shift+enter

在EXCEL表中怎么统计出大于几到小于几的数有多少个，是一张表但不是一列数字

等号=COUNTIF(范围,">1000")-COUNTIF(范围,">=1150")

在EXCEL中如何统计与上行相同的数字，并判断非机动奇偶？

M4=IF(AND(SUMPRODUCT((B4=$B$2:B3)*1)>0,B4<>0),B4,"")

X4=IF(M4<>"",IF(AND(MOD(M4,2)=0,M4<>0),"偶","奇"),"")你原始数据有十列，

后面的两块区域也需要十列。

M-V列为与上行相同数。

X-AG列为与上行相同数的奇偶。

在操作excel中的除法时，不要取小数，而是取整数和余数，需要怎么操作

比如：A1是被除数，B1是除数，结果显示在C1，

可以C1中输入公式：

公式：=INT(A1/B1)&"余"&MOD(A1,B1)

这样比如A1中数字是5，B1中是2时，C1中显示的是：2余1

excel函数，想对12求余，得到余数设：a为除数，b为被除数

公式：=MOD(a,b)

Excel 2003中怎么样统计相同颜色单元格的数量

按下alt+f11,插入模块

写下过程

Function SUMColor(rag1 As Range, rag2 As Range)

Application.Volatile

For Each i In rag2

If i.Interior.ColorIndex = rag1.Interior.ColorIndex Then

SUMColor = SUMColor + 1

End If

End Function

然后在你需要统计的工作表里面找个空白的单元格（例如D2）填充你要统计个数的颜色，然后在D2隔壁（+_+）单元格写下：=SUMColor(D2,$A$1:$C$22)。其中$A$1:$C$22是需要统计的区域，根据你的需求自己改。

其中$A$1的A是要统计区域开始的列号，1是行号；$C$22里的C,22同样是行列号。

EXCEL中一列数中有整数又有小数，怎么算出小数个数

等号=sumproduct((int(a1:a100)<>a1:a100)*1),根据实际调整区域

EXCEL中一列数中有整数又有小数，怎么算出整数个数

'=SUM((MOD(A1:A60*100/100,1)=0)*(MOD(A1:A60*100/100,1)=0))

按组数键结束,但有个问题,空白的单元格也会是"0".

或者=COUNTA(D1:D60)-SUM(--ISNUMBER(FIND(".",D1:D60)))

数组公式

或者=SUM(IF(D1:D60=INT(D1:D60),1))三键结束

空白的单元格也统计拉!

或者=SUM((A1:A100=INT(A1:A100))*(A1:A100<>""))

奇、偶数统计方法

.*.*.* A B C

.*.*.*数值，偶数，奇数

2 123

3 1

4 8

5 98

6 17

在B2单元格：=VALUE(IF((MOD(VALUE(RIGHT(A2,1)),2)=0),"1","0"))

在C2单元格：=VALUE(IF((MOD(VALUE(RIGHT(A2,1)),2)=1),"1","0"))

在B列偶数列，无论A2单元格是数值或是文本型的数字

如果，取A2的数字右边的一位数除以2，余数为0，B2=1，否则B2=0

在C列奇数列，无论A2单元格是数值或是文本型的数字

如果，取A2的数字右边的一位数除以2，余数为1，C2=1，否则C2=0

偶数的总数=B列之和

奇数的总数=C列之和

判断A1:A100中奇数的个数:

等号=SUMPRODUCT(MOD(A1:A100,2))

判断A1:A100中偶数的个数:

等号=SUMPRODUCT(1*(MOD(A1:A100,2)=0))

EXCEL中有多个时间数，如：23：00、22：00、12：00......

我想求出等于或大于23：00的个数

等号=SUMPRODUCT(1*(A1:A100>=TIME(23,0,0)))

什么公式可以统计出一列或一行单元格数中有多少个质数、合数和奇数、偶数数据在A1:A6

质数

=SUMPRODUCT(--ISNUMBER(FIND(A1:A6&",","1,2,3,5,7,11,13,17,19,23,29,31,")))

合数，可直接引用质数的单元格

=6-SUMPRODUCT(--ISNUMBER(FIND(A1:A6&",","1,2,3,5,7,11,13,17,19,23,29,31,")))奇数

=SUMPRODUCT(--MOD(A1:A6,2))

偶数

=SUMPRODUCT(--MOD(A1:A6+1,2))

小数

=COUNTIF(A1:A6,"<17")

大数

=COUNTIF(A1:A6,">=17")

----------

A1:A6 改为实际的数据区

如何统计不同数据的总个数

假设上面的数据在A列分别是A1至A8

可以用以下公式：=SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8))

在excle中提取一列中不重复的数据，并统计出现的次数

假设你的数据A2:A11

定义名称x=Sheet1!$A$2:$A$11

B2输入数组公式

{=IF(ROW()-1>SUM(1/COUNTIF(x,x)),"",INDEX(x,SMALL(IF(MATCH(x,x,0)=ROW(x)-1,R OW(x)-1),ROW(1:1))))}

向下拖拉

C2=COUNTIF(x,B2)

向下拖拉

在excel表中统计不重复的数值的个数

SUM(1/COUNTIF(A1:A100,A1:A100))

输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。

但是我的数据里面有些表格是空的，怎么办计算跳过空的不计算在内呢

SUM(IF(A1:A100<>"",1/COUNTIF(A1:A100,A1:A100)))

数组公式，按Ctrl+Shift+Enter键

统计上下两行相同数字的个数

SUMPRODUCT(COUNTIF(上行,下行))

公式说明

=COUNTIF(A2:A5,"apples") 计算第一列中苹果所在单元格的个数(2)

=COUNTIF(A2:A5,A4) 计算第一列中桃所在单元格的个数(2)

=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) 计算第一列中柑桔和苹果所在单元格的个数(3)

=COUNTIF(B2:B5,">55") 计算第二列中值大于55 的单元格个数(2)

=COUNTIF(B2:B5,"<>"&B4) 计算第二列中值不等于75 的单元格个数(2)

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85") 计算第二列中值大于或等于32 且小于或等于85 的单元格个数(3)

统计单元格内大于0小于2的单元格个数的函数公式

“=COUNTIF(AY87:BD87,">0")-COUNTIF(AY87:BD87,">2"）”

一个数大于等于12的数字乘0.7,小于3的乘以1,5-11的乘以0.8,3-4的乘以0.9

IF(A1>=12,A1*0.7,IF(A1>=5,A1*0.8,IF(A1>=3,A1*0.9,A1)))

求一横中c5到AW5的空白格数量

公式为：=COUNTIF(C5:AW5,"")

求一横中c5到AW5的某个符合数值5的个数

公式为：=COUNTIF(C5:AW5,5)

统计区间数值公式

countif是单条件统计，2007版增加了一个countifs进行多条件统计。

一般情况下，进行多条件统计可用SUMPRODUCT函数。

如=SUMPRODUCT((A1:A100>2)*(A1:A100<5))

=COUNTIF(A2:A5,"apples") 计算第一列中苹果所在单元格的个数(2)

=COUNTIF(A2:A5,A4) 计算第一列中桃所在单元格的个数(2)

=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) 计算第一列中柑桔和苹果所在单元格的个数

(3)

=COUNTIF(B2:B5,">55") 计算第二列中值大于55 的单元格个数(2)

=COUNTIF(B2:B5,"<>"&B4) 计算第二列中值不等于75 的单元格个数(2)

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85") 计算第二列中值大于或等于32 且小于或等于85 的单元格个数(3)

用countif不好实现

用sumproduct函数

公式为：=SUMPRODUCT((条件1)*(条件2))

统计某一列相同数据的个数统计个数

公式：=COUNTIF(c2:c1000,"100")

相同数据的定位

公司=IF(COUNTIF(有重复的数据范围,当前行单元)>1,"重复","")

计算单元格A2:A10 中不重复的数字值的个数，但不计算空白单元格或文本值的个数(4) COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))

SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))

计算单元格B2:B10（不能包含空白单元格）中不重复的文本和数字值的个数(7)

SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

计算单元格A2:A10 中不重复的文本和数字值的个数，但不计算空白单元格或文本值的个数(6)

SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""),

IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

百分比函数

如果名称是单一的（即没有重复的），E2输入公式：=D2/$D$6，设置E2单元格为百分比

格式。

如果名称有重复，E2输入公式：=SUMIF($A$2:$A$5,"油料",$D$2:$D$5)/$D$6，设置E2单元格为百分比格式

提取区间内数字公式

假设数据在列A1:A10，在B1输入公式后按CTRL+SHIFT+ENTER结束，向下复制：gs:

=INDEX(A:A,SMALL(IF((A$1:A$10>=3)*(A$1:A$10<20),ROW($1:$10),4^8),ROW(1:1)))& ""

可以得到A列大于等于3且小于20的数字。

AC值公式（数组3键回车）为：{=SUM((FREQUENCY(ABS($B928:$G928-TRANSPOSE($B928:$G928)),ABS($B928:$G928-

TRANSPOSE($B928:$G928)))>0)*1)-6} 统计整数的个数，区间（Z138，AB138）

17 20 24.5 2

{=COUNTA(Z138:AB138)-SUM(--ISNUMBER(FIND(".",Z138:AB138)))}

统计同一区间不同数字的个数，区间（T736，AB736）

{=SUM(IF(T736:AB736<>"",1/COUNTIF(T736:AB736,T736:AB736)))}

统计一行与一列重复数字的个数。区间：行（B740：G740）列（K736：K740）

=IF(A740="","",SUMPRODUCT(--ISNUMBER(MATCH(B740:G740,K736:K740,))))

提取整数，区间（AB32，AP32）

5.5 8

6.5 9 16.5 8 10.5 18 11.5 19 21.5 3 8 9 8 18 19

=IF(ISERROR(FIND(".",AB32)=TRUE),AB32,"")

删除重复数字

看似简单,还是有些难度,B1中输入

这个不保留重复的都删除

=IF(LEN(SUBSTITUTE(A1,0,))=(LEN(A1)-1),0,"")&SUBSTITUTE(SUM(IF(LEN(SUBSTITUTE(A1,ROW($1:$10)-1,))=(LEN(A1)-1),ROW($1:$10)-1)*1 0^(10-ROW($1:$10))),0,)

同时按下CTRL+SHIFT+ENTER输入数组公式,再用自动填充柄将公式下拉。就是出现顺序为升序，不是数字本身出现的先后顺序

这个保留一个重复的

=LEFT(SUM((0&MID(H2,SMALL(FIND(ROW($1:$10)-1,H2&1/17),ROW($1:$10)),1))*10^(10-ROW($1:$10))),COUNT(FIND(ROW($1:$10)-1,H2)))

同时按下CTRL+SHIFT+ENTER输入数组公式

第二个就可以的...

=LEFT(SUM((0&MID(H2,SMALL(FIND(ROW($1:$10)-1,H2&1/17),ROW($1:$10)),1))*10^(10-ROW($1:$10))),COUNT(FIND(ROW($1:$10)-1,H2)))

同时按下CTRL+SHIFT+ENTER输入数组公式