b62

6、COUNTIF函数

函数名称：COUNTIF

主要功能：统计某个单元格区域中符合指定条件的单元格数目。

使用格式：COUNTIF(Range,Criteria)

参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表达

式。

应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,">=80")，确认

后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。

特别提醒：允许引用的单元格区域中有空白单元格出现。

13、INDEX函数

函数名称：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）中的内容。

特别提醒：此处的行序号参数（row_num）和列序号参数（column_num）是相对于所引用的

单元格区域而言的，不是Excel工作表中的行或列序号。

14、INT函数

函数名称：INT

主要功能：将数值向下取整为最接近的整数。

使用格式：INT(number)

参数说明：number表示需要取整的数值或包含数值的引用单元格。

应用举例：输入公式：=INT(18.89)，确认后显示出18。

特别提醒：在取整时，不进行四舍五入；如果输入的公式为=INT(-18.89)，则返回结果为

-19。

15、ISERROR函数

函数名称：ISERROR

主要功能：用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回

FALSE。

使用格式：ISERROR(value)

参数说明：Value表示需要测试的值或表达式。

应用举例：输入公式：=ISERROR(A35/B35)，确认以后，如果B35单元格为空或“0”，则

A35/B35出现错误，此时前述函数返回TRUE结果，反之返回FALSE。

特别提醒：此函数通常与IF函数配套使用，如果将上述公式修改为：

=IF(ISERROR(A35/B35),"",A35/B35)，如果B35为空或“0”，则相应的单元格显示为空，反之

显示A35/B35的结果。

17、LEN函数

函数名称：LEN

主要功能：统计文本字符串中字符数目。

使用格式：LEN(text)

参数说明：text表示要统计的文本字符串。

应用举例：假定A41单元格中保存了“我今年28岁”的字符串，我们在C40单元格中输入

公式：=LEN(A40)，确认后即显示出统计结果“6”。

特别提醒：LEN要统计时，无论中全角字符，还是半角字符，每个字符均计为“1”；与之相对

应的一个函数——LENB，在统计时半角字符计为“1”，全角字符计为“2”。

19、MAX函数

函数名称：MAX

主要功能：求出一组数中的最大值。

使用格式：MAX(number1,number2„„)

参数说明：number1,number2„„代表需要求最大值的数值或引用单元格（区域），参数不

超过30个。

应用举例：输入公式：=MAX(E44:J44,7,8,9,10)，确认后即可显示出E44至J44单元和区

域和数值7，8，9，10中的最大值。

特别提醒：如果参数中有文本或逻辑值，则忽略。

20、MID函数

函数名称：MID

主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。

使用格式：MID(text,start_num,num_chars)

参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要

截取的数目。

应用举例：假定A47单元格中保存了“我喜欢天极网”的字符串，我们在C47单元格中输

入公式：=MID(A47,4,3)，确认后即显示出“天极网”的字符。

特别提醒：公式中各参数间，要用英文状态下的逗号“,”隔开。

21、MIN函数

函数名称：MIN

主要功能：求出一组数中的最小值。

使用格式：MIN(number1,number2„„)

参数说明：number1,number2„„代表需要求最小值的数值或引用单元格（区域），参数不

超过30个。

应用举例：输入公式：=MIN(E44:J44,7,8,9,10)，确认后即可显示出E44至J44单元和区

域和数值7，8，9，10中的最小值。

特别提醒：如果参数中有文本或逻辑值，则忽略。

24、NOW函数

函数名称：NOW

主要功能：给出当前系统日期和时间。

使用格式：NOW()

参数说明：该函数不需要参数。

应用举例：输入公式：=NOW()，确认后即刻显示出当前系统日期和时间。如果系统日期和

时间发生了改变，只要按一下F9功能键，即可让其随之改变。

特别提醒：显示出来的日期和时间格式，可以通过单元格格式进行重新设置。

25、OR函数

函数名称：OR

主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假

（FALSE）”，否则都返回逻辑“真（TRUE）”。

使用格式：OR(logical1,logical2,...)

参数说明：Logical1,Logical2,Logical3„„：表示待测试的条件值或表达式，最多这30

个。

应用举例：在C62单元格输入公式：=OR(A62>=60,B62>=60)，确认。如果C62中返回TRUE，

说明A62和B62中的数值至少有一个大于或等于60，如果返回FALSE，说明A62和B62中的数

值都小于60。

特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或

“#NAME”。

26、RANK函数

函数名称：RANK

主要功能：返回某一数值在一列数值中的相对于其他数值的排位。

使用格式：RANK（Number,ref,order）

参数说明：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表

排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果

为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。

应用举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出丁1同

学的语文成绩在全班成绩中的排名结果。

特别提醒：在上述公式中，我们让Number参数采取了相对引用形式，而让ref参数采取了

绝对引用形式（增加了一个“$”符号），这样设置后，选中C2单元格，将鼠标移至该单元格

右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快

速复制到C列下面的单元格中，完成其他同学语文成绩的排名统计。

30、SUMIF函数

函数名称：SUMIF

主要功能：计算符合指定条件的单元格区域内的数值和。

使用格式：SUMIF（Range,Criteria,Sum_Range）

参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range

代表需要计算的数值所在的单元格区域。

应用举例：如图7所示，在D64单元格中输入公式：=SUMIF(C2:C63,"男",D2:D63)，确认

后即可求出“男”生的语文成绩和。

特别提醒：如果把上述公式修改为：=SUMIF(C2:C63,"女",D2:D63)，即可求出“女”生的

语文成绩和；其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号（"男"、"女

"）中。

VALUE函数

函数名称：VALUE

主要功能：将一个代表数值的文本型字符串转换为数值型。

使用格式：VALUE(text)

参数说明：text代表需要转换文本型字符串数值。

应用举例：如果B74单元格中是通过LEFT等函数截取的文本型字符串，我们在C74单元格

中输入公式：=VALUE(B74)，确认后，即可将其转换为数值型。

特别提醒：如果文本型数值不经过上述转换，在用函数处理这些数值时，常常返回错误

34、VLOOKUP函数

函数名称：VLOOKUP

主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。

使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的

单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当

Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值„„）；

Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精

确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找

不到，则返回错误值#N/A。

应用举例：参见图7，我们在D65单元格中输入公式：=VLOOKUP(B65,B2:D63,3,FALSE)，确

认后，只要在B65单元格中输入一个学生的姓名（如丁48），D65单元格中即刻显示出该学生

的语言成绩。

特别提醒：Lookup_value参见必须在Table_array区域的首列中；如果忽略Range_lookup

参数，则Table_array的首列必须进行排序；在此函数的向导中，有关Range_lookup参数的用

法是错误的。

5．计数函数COUNT

语法：COUNT（value1，value2，．．．）。

参数：value1，value2．．．是包含或引用各类数据的1～30个参数。

注意：COUNT函数计数时数字、日期或文本表示的数字会参与计数，错误值或

其他无法转换成数字的文字被忽略。如果参数是一个数组或引用，那么只有数组

或引用中的数字参与计数；其中的空白单元格、逻辑值、文字或错误值均被忽略。

应用实例：及格率统计

假如C1:G42存放着42名学生的考试成绩，在一个空白单元格内输入公式

“=COUNTIF（C1:C42，″>=60″）/COUNTA（C1:C42）”回车，即可计算出该列

成绩的及格率（即分数为60及以上的人数占总人数的百分比）

12．条件计数函数COUNTIF

语法：COUNTIF（range，criteria）。

参数：range为需要统计的符合条件的单元格区域；criteria为参与计算的单元格

条件，其形式可以为数字、表达式或文本（如36、″>160″和″男″等）。条件中的

数字可以直接写入，表达式和文本必须加引号。

应用实例：男女职工人数统计

假设A1:A58区域内存放着员工的性别，则公式“=COUNTIF（A1:A58，″女″）”

统计其中的女职工数量，“=COUNTIF（A1:A58，″男″）”统计其中的男职工数量。

COUNTIF函数还可以统计优秀或及格成绩的数量，假如C1：G42存放着42名

学生的考试成绩，则公式“=COUNTIF（C2：G2，″>=85″）”可以计算出其中高

于等于85分的成绩数目。如将公式改为“=COUNTIF（C2：G2，″>=60″）”，则

可以计算出及格分数的个数。

1统计一列数据（a列）中出现相同次数的数据的个数

=SUMPRODUCT(--(COUNTIF($A$1:$A$10,$A$1:$A$10)=COLUMN(A:A)))/COLUMN(A:A)

输入到b1，向右拖拉，依次能够得到出现1、2、3次等等的数据统计。这里，用COLUMN(A:A)来控制

统计的次数，以便实现向右拖拉时可以自动增加。

2统计一列数据中数字出现的频率

比如1234，和4567，其中在出现一次一栏中显示123567等几个数字。

需借助辅助列，在e:l行里面输入公式

=MID($A2,COLUMN(A:A),1)

拖拉到所有区域，最大可以统计8位数据。

然后下面为主公式。

=SUMPRODUCT(LARGE(IF(FREQUENCY(LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1

,0)*IF(($E$1:$L$1000)="",0,$E$1:$L$1000),ROW(1:10)),LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$10

00)=COLUMN(A:A),1,0)*IF(($E$1:$L$1000)="",0,$E$1:$L$1000),ROW(1:10)))=0,0,LARGE(IF(COUNTIF($

E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)*IF(($E$1:$L$1000)="",0,$E$1:$L$1000),ROW(1:10))),

ROW(1:10))*10^(ROW(1:10)-1))

上面公式输入到出现一次的栏里面，然后向右拖拉，一次可以显示出现两次、三次，等等

3将“001”“001-107”数字前后都加上2，变成“003-109”

=IF(ISNUMBER(A1),A1+2,TEXT(LEFT(A1,3)+2,"000")&"-"&TEXT(RIGHT(A1,3

)+2,"000"))

如果有8位数字，则需要进行为数判断，使用find()函数

4excel转置自动填充的问题

1)A列为a、b、c，b列为1、2、3，c列为11、22、33，d列为111、222、333，e列为

aa、bb、cc

效果为

A

1

11

111

Aa

依此类推。

=OFFSET(Sheet2!$A$1,INT((ROW()-1)/5),MOD(ROW()-1,5))

然后向下拖拉

2)A列为a、b、c、d、e、f、g、h、i，b列为1、2、3、4、5、6、7、8、9

效果为：

abc

123

def

456

ghi

789

公式

=OFFSET(Sheet3!$A$1,COLUMN(A:A)-1+3*INT((ROW(1:1)-1)/2),MOD((ROW(1:1)-1),2))

先向右拖拉3列，再向下拖拉

5excel计算分别满足多个条件的单元格数

例一：

有四列数据，如ABCD，A与B是对应的，C与D是对应的

要求当C=A时，D=B列中“X”的个数

问题补充：B列有“X”，有的没有，只用统计"X"的个数！不是显示“X”

比如

ABCD

1X1

2X2

33

14

2X5

1X

2X

3X

现在要D中显示“1”对应的“X”有多少个

我的回答:D1=COUNTIFS(B:B,"=X",A:A,C1)此公式的缺点是只能用在office2007以上的

版本。

别人的回答:D1=SUMPRODUCT(($A$1:$A$100=C1)*($B$1:$B$100="X"))

例二：

计算出A列姓名如"张三"，在B列状态如所有显示“完成”的行项目数.

用countifs。A2为张三。

C2=COUNTIFS(A:A,A2,B:B,"完成")

用sumproduct函数：=SUMPRODUCT((a2:a100="张三")*(b2:b100="完成"))

解析：countifs是多条件统计，第一个参数是范围一，第二个参数是条件一，

第三个参数是范围二，第四个参数是条件二，一次类推，可以多条件选择统计。

此函数在2003版本中没有。

Sumproduct函数是返回对应的数组元素的成绩的和，即{1，2，3}{4，5，6}，

结果是1*4+2*5+3*6=32。

这里用来多条件统计的原理是，把每个条件作为乘积的因子，函数用数组来处

理，和对应区域中的每个单元格对比，如果条件成立时，返回true，如果不成

立返回fal，这样得到一对数组{fal，true，fal}{true，true，fal}，接

着进行计算fal*true+truetrue+fal*fal=0+1+0,因为两个逻辑函数相乘，

只有当两个都是true的时候乘积为1，否则都为0，依此计算出来的结果，就

是这些条件都满足的单元格的行数。

因此上述两行的数组元素不但要维数相等，而且一定要对应。即选择范围是要

在行间对应，否则求出的值就会出错。如条件一从A1到A10，条件二从B2到

B11，虽然也是维数相同的数组，但是得数不正确，因为错行，它会把A1和

B2相比较。

上述公式因为是直接定义的数组，所以输入公式时不用单独应用数组公式。

6Sumif

Sumif是对符合条件地单元格求和，第一个参数为需要符合条件的单元格区域范围，第

二个参数是条件，第三个参数是实际需要计算的单元格区域，按与第一参数相等的区域进行

计算。

这里需要注意的是，参数一满足条件后会返回区域里面单元格所在的行数，求和时，按

照参数三里面区域对应行数进行。

如参数一区域选的A3:A10，参数三是B4到B11，假如a4，a5符合条件，那么将返回的

行数是2、3，而不是4、5，然后，将对b5、b6进行求和，而不是b4、b5。

=SUMIF(B:B,"条件",A:A)

本公式的意义是，当b列中有单元格等于“条件”两个字时，计算对应a列单元格的和。

其中条件参数可以使用通配符，*，？，实现模糊查找。

7隐藏函数

1)Numberstring(value,type)，阿拉伯数字转换成中文数字，type为1时，转换为小写

中文数字（带千百万等等），2转换为大写中文数字（带千百万等），3转换为小写中文数字，

不含千百万。

2)Datedif(start_date,end_date,unit)，计算时间差，第一参数为开始时间，第二参数为

结束时间，unit为返回的类型，”y”、”m”、”d”分别为年、月、日，这些参数返回的是总体

差值，比如2008年1月1日到2009年1月1日，返回年数为整年数1年，返回天数是整个

期间的总天数366天。”yd”、”md”这两个参数分别返回除去年或年月的天数，既不包括其

中的年数或年月数所得到的天数。”ym”指出去年数的整月份数，多余的天数向下舍入。

8隔行和跳行填充的问题（隔行表示引用为连续，填充不连续；跳行表示引用不连续，

填充连续）

思路方法：（1）column(a:a)*2-2利用列号得到包括0在内的偶数，可以实现行向拖拉。行

号同理。Row(1:1)*2-1利用行号得到奇数，可以实现列向拖拉。列号同理。（2）mod(row(),2)，

用以判断当前行是奇数还是偶数行，等于0为偶数，等于1为奇数。（3）int((row(1:1)-1)/n)

得数每n行增加1，解决跳行填充的思路之一。

示例：

隔行

1)如果须填充的数据一样,可以输入下面公式:

=IF(MOD(ROW()-3,30)=0,"√","")每隔30个填充一个.

2)如果数据来自引用，隔行填充可以输入下面公式：

=IF(MOD(ROW(),2)=1,INDIRECT("d"&INT(ROW()/2)+1),"")

单数单元格填充，双数为空值。

如果从第5个单元格填充第六行的数字，公式变成：

=IF(MOD(ROW(),2)=1,INDIRECT("d"&INT(ROW()/2)+4),"")

后面的相加的数字等于公式输入行的行数除以2再加1，row()/2+1，最后引用的行必须等于公式输入

行。如果不等于，则公式后面要加上错位的行数（区分正负数）。

上面公式可以变成：

=IF(MOD(ROW(),2)=1,INDIRECT("d"&INT(ROW()/2)+row()/2+1),"")

跳行

3)如果填充连续，但引用需要跳行。比如在引用的单元格区域，每隔五行填充进来一个数据：

=INDEX(a:a,row(1:1)*5)

=offt($a1,,column(a:a)*2-2)行填充，引用区域为第一行，填充奇数列

=INDIRECT(ADDRESS(1,COLUMN(a:a)*2-1))行填充，引用区域为第一行，填充奇数列

=INDIRECT(ADDRESS(ROW(1:1)*2-1,1))列填充，引用区域为第一列a列，填充奇数行