COUNTIF函数的用法及实例

第一篇：COUNTIF函数的用法及实例

Excel统计函数COUNTIF的用法及实例

一：首先介绍COUNTIF函数的用法

英文通用格式是：COUNTIF（range,criteria)

中文通用格式是：COUNTIF（数据区域,条件表达式)=COUNTIF（A$2：A$55，A2）(注释：A2单元格在A2：A55区域中出现的次数（0：没有；1：出现1次；2：出现2次。）)

1、返加包含值12的单元格数量

=COUNTIF（range,12）

2、返回包含负值的单元格数量

20-屯昌电视台MSTP（总表处理后重复303条

=COUNTIF（range，“<0”）

3、返回不等于0的单元格数量

=COUNTIF（range，“<>0”）

4、返回大于5的单元格数量

=COUNTIF（range，“>5”）

5、返回等于单元格A1中内容的单元格数量

=COUNTIF（range，A1）

6、返回大于单元格A1中内容的单元格数量

=COUNTIF（range，“>”&A1）

7、返回包含文本内容的单元格数量

=COUNTIF（range，“*”）

8、返回包含三个字符内容的单元格数量

=COUNITF（range，“???”）

9、返回包含单词“GOOD”(不分大小写)内容的单元格数量

=COUNTIF（range，“GOOD”）

10、返回在文本中任何位置包含单词“GOOD”字符内容的单元格数量

=COUNTIF（range，“*GOOD*”）

11、返回包含以单词“AB”(不分大小写)开头内容的单元格数量

=COUNTIF（range，“AB*”）

12、返回包含当前日期的单元格数量

=COUNTIF（range，TODAY()）

13、返回大于平均值的单元格数量

=COUNTIF（range，“>”&AVERAGE(DATA))

14、返回平均值上面超过三个标准误差的值的单元格数量

=COUNTIF（range，“>"&AVERAGE(DATA)+STDEV(DATA)*3)

15、返回包含值为或-3的单元格数量

=COUNTIF（range，3）+COUNIF（DATA，-3）

16、返回包含值;逻辑值为TRUE的单元格数量

=COUNTIF（range，TRUE）

二：COUNTIF函数应用举例

假设存在如下图的学生成绩表，现在，让我们一起来使用COUNTIF函数进行统计，下面统计的是不及格的人数！

如下图，首先，选择F列中的F3单元格，之后，在其上方fx函数输入区域，直接输入=COUNTIF(E3:E55,“<60“)，之后按回车键确认，这样，就得到不及格的人数了！

=COUNTIF(E3:E55,“<60“)分析：

E3:E55代表统计的数据区域是E3到E55，“<60“统计的条件是成绩小于60；

知识扩展：

“<60“改为：“<80“是什么意思呢？

表示的是，统计分数小于80的人数；

“<60“改为“>60“是什么意思呢？

表示的是：统计及格的人数！

英文通用格式是：COUNTIF（range,criteria)

中文通用格式是：COUNTIF（数据区域,条件表达式)上述格式中，数据区域和条件表达式，可根据需要自行构造，灵活性非常大；

第二篇：EXCEL函数实例活用100

第一章 函数基础知识

1、按shift+F3是插入弹出“插入函数”对话框的快捷键。

2、当在单元格中键入“=”号时，在“名称框”中会显示出常用的函数列表，可以选择所需要的。

3、在单元格中输入公式时，当键入函数名称时，或者键入函数名称和左括号时，可按ctrl+shift+A组合键显示函数的参数说明。

4、如果在单元格中输入的公式返回错误的信息，想了解这个错误信息的含义，如“#NAME”，可以选中此单元格，鼠标光标移动到紧挨此单元格左侧的智能标记图标上，就会出现“公式中包含不可识别的文本”之类的错误信息说明。

第二章 数学和三角函数

1、SUM函数的参数不能超过30个，如果需要30个以上参数时，可以在引用的参数两边多加一对括号，这样就突破了这个限制。如：计算A1：A32的和可以用公式：

=SUM((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30,A31,A32))

2、AVERAGE函数是求平均值的函数，如果参数引用中包含0值，则也会算在内，可以使用下面的公式实现求平均数时，只对不等于0的值求平均数： 用数组公式： {=AVERAGE(IF(A1:A10<>0,A1:A10))} 提示：计算时会先得到一个含有数值和逻辑值的数组，再对这个数组求平均，由于AVERAGE函数会忽略逻辑值，所以就只对不等于0的数值求平均数。

3、INT（）是向下取整函数。即向数轴向左的方向取整。例如：=INT(9.9)结果是 9

4、TRUNC（数值或单元格引用，指定取整精度）函数是取整函数，且是真正的取整函数，即截取数字的整数部分，正数、负数同样对待。如：=TRUNC(8.4)结果是8 =TRUNC(-8.4)结果是-8 ,而如果是=INT(-8.4)则结果就是-9 =INT(-9.9)结果是-10 注意INT（）函数和TRUNC（）函数的区别。注意：

（1）TRUNC（）函数和INT（）函数的区别。

（2）取整精度默认为0，也可以指定，如： =TRUNC（4.867,2）结果是4.86 =TRUNC(-9.2389,3)结果是-9.238

5、CEILING（要四舍五入的数值，是需要四舍五入的乘数）函数

用法：此函数是将第一个参数向上舍入（沿绝对值增大的方向）为最接近的第二个参数的倍数。

注意：第一个参数和第二个参数的正负号必须统一；无论数字符号如何，都按远离 0 的方向向上舍入;最终结果肯定是第2个参数的整数倍。

例1：=CEILING(0.234, 0.01)结果是将0.234向上舍入到最接近的0.01的24倍，即0.01*24等于0.24,0.234向上舍入到0.24 例2：=CEILING(5.7,4)结果是将5.7舍入到4的2倍，即4*2等于8，5.7向上舍入到8。而不能是4的1倍，因为4*1等于4，而4小于5.7 例3：=CEILING(4.42,0.1)结果是将4.42舍入到0.1的45倍，即0.1*45等于4.5，4,42向上舍入到4.5 例4：=CEILING(1.5, 0.1)结果是1.5，因为1.5已经是0.1的15倍了，所以保持不变。

6、COMBIN（对象的总数量，为每一组合中对象的数量）用法：求数学当中的组合数。

注意：第2个参数应当小于等于第1个参数；两个参数都必须大于等于0。例1：求从8个对象中取2个对象进行的组合数 =COMBIN(8,2)结果是28 例2：求从4个对象中取3个对象的组合数 =COMBIN(4,3)结果是4

7、删除单元格中文本中的空格符，可以用=SUBSTITUTE（Text,” ”,””）函数，但是文本中含有ASCII码为160的空格符,公式要变为: =SUBSTITUTE(SUBSTITUTE(Text,” ”,””),CHAR(160),””)

8、删除空白行的一种方法。选中要操作的区域，执行“编辑”/“定位”/“定位条件”/选“空值”，“确定”后即可将选中区域中的空白单元格选中，再执行“编辑”/“删除”/“整行”即可。

注意：此操作要确保其他非空行中的所有单元格内均有数据，否则会出现误删除记录的现象。

9、INDIRECT（引用的文本，a1）函数 注意：

（1）如果引用的文本是对另一个工作簿的引用，则该工作簿必须被打开，否则函数返回#REF！

（2）a1参数是一个逻辑值，表示引用类型是A1引用样式还是R1C1引用样式，为TRUE或省略时表示A1引用样式

10、EXP(number)函数计算e的number次幂。其中e为2.7***04 例1：exp(1)结果是2.7***04，表示e的1次幂 例2：exp(2)结果是 7.389056099，表示e的2次幂

第三章 统计函数

11、MAX（）函数和MIN（）函数的参数最多为30个。

12、计算指定区域的最大值。

比如数据在A1：A10，计算此区域中的最大值 方法一： =MAX(a1:a10)方法二： =SMALL(A1:A10,COUNTA(A1:A10))注意：MAX函数的参数引用如果是逻辑值、文本、空白单元格，则将被忽略。如果要求参数引用不能忽略逻辑值、文本，则要用MAXA()函数。

13、RAND（）函数返回0到1之间的随即数，每次工作表计算都返回一个新的值。要生成a与b之间的随机实数，可以用公式=RAND（）*（b-a）+a

14、ROUNDUP（数值，四舍五入后的数字的位数）函数将指定数值返回为向上舍入的数值。

例1：=roundup(4.982,1)结果为5.0 例2：=roundup(3.14159,3)结果为3.142 例3：=ROUNDUP(-3.14159, 1)结果为-3.2 注意：这里的向上舍入指远离0值。

15、FREQUENCY（）函数 语法：

FREQUENCY(数据源,分段点)结果：

以分段点为间隔，统计数据源值在各段出现的频数 其中：

数据源：为对一行/一列单元格或一个连续的单元格区域的引用；也可以是对一个单元格引用。

分段点：为对一行/一列单元格或一个连续的单元格区域的引用；也可以是对一个单元格引用。

数据引用支持跨工作表、工作簿 公式输入方法：

以多单元格数组方式输入，且必须是纵向数组；

所选单元格数比分段点个数大1，以统计数据源大于分段点最大值的频数

16、RANK（）函数

RANK（）函数对重复数的排位是相同的，如果两个相同的数值出现时，它们的排名是相同的，比如都是第5位，而 不会是第5位和第6位，这里的第6位将被忽略，而直接跳到第7位。

17、利用SMALL（区域，COUNT(区域)）函数可以统计区域中的最大值。注意：SMALL（）函数忽略被统计区域中的空白单元格、逻辑值、文本。

18、FORECAST（）函数是根据已有的数值来计算或预测未来值。

19、TRIMMEAN（数组或引用，要去除的数据点比例）函数

例如：左边的示例，（1）求A1：A12中去掉一个最高分、去掉一个最低分，然后求平均值： 常规做法是：

=(SUM(A1:A12)-MAX(A1:A12)-MIN(A1:A12))/(COUNT(A1:A12)-2)而利用TRIMMEAN函数就方便多了，因为一个最高和一个最低是2个数，占总个数12的百分比是2/12，即1/6，所以公式可以写成： =TRIMMEAN(A1:A12,1/6)结果和上面的公式相同。

(2)如果要去掉两个最好分和两个最低分，对剩下数求平均值，则可以直接用公式：

=TRIMMEAN(A1:A12,4/12)

20、DCOUNT（）函数

返回数据库或数据清单的列中满足指定条件并且包含数字的单元格个数。

21、DMAX（数据列表或单元格区域，要统计的列名称或列序号，条件）例如：

上海员工原工资总数是：=SUMIF(E3:E18,“上海”,G3:G18)上海员工原工资最高的是：=DMAX(B2:H18,“原工资”,E20:E21)也可以用数组公式：{=MAX((E3:E18=“上海”)*(G3:G18))} 上海员工原工资最低的是：=DMIN(B2:H18,G2,E20:E21)也可以用数组公式：{=MIN(IF(((E3:E18=“上海”)*(G3:G18))=0,FALSE,(E3:E18=“上海”)*(G3:G18)))} 提示：加这句IF(((E3:E18=“上海”)*(G3:G18))=0,FALSE是利用MIN（）函数忽略逻辑值的的原理。

22、求众数函数MODE（）众数即出现频率最高的数值。如下图示例：

求A1：A12中出现频率最高数值可以用公式 =mode(a1:a12)结果是6 注意：MODE参数中的数组或引用中的文本、空白单元格、逻辑值将被忽略，但含有零值的单元格将被计算在内，解决的方法如下：

例如：要统计A1：A12中出现频率最高的数值，但零值不计算在内： 用数组公式 {=MODE(IF(A1:A12=0,FALSE,A1:A12))} 即利用了MODE函数忽略逻辑值的原理。

3、求几何平均数GEOMEAN（）函数 几何平均数的计算公式如下：

提示：可以用公式 =product(区域)^(1/count(区域))代替GEOMEAN（）函数。

第四章 日期与时间函数

1、求两个日期之间的天数差。

假设在A1填入2006-12-1,在A2填入2006-12-31，则公式：Datedif(a1,a2,”d”)即可。

当然最简单的方法是直接用公式： =a2-a1即可。

2、DATE（年，月，日）函数

参数中的年可以为1至4位数值默认情况下EXCEL使用1900日期系统：（1）如果 year 位于 0（零）到 1899（含）之间，则 Excel 会将该值加上 1900，再计算年份。例如，DATE(100,1,2)将返回 2000(1900+100)年 1 月 2 日。（2）如果 year 位于 1900 到 9999（含）之间，则 Excel 将使用该数值作为年份。例如，DATE(2000,1,2)将返回 2000 年 1 月 2 日。

（3）如果 year 小于 0 或大于等于 10000，则 Excel 将返回错误值 #NUM!。

第五章 文本和数据函数

1、CELL（信息类型，引用）

函数返回某一个引用区域的左上角的单元格格式、位置或内容等信息。如果“引用”忽略，则返回最后更改的单元格所对应的信息。

如：=mid(CELL(“filename”),find（”[“, CELL(“filename”)）+1，255）返回最后修改的单元格所在的工作表，而如果想返回当前单元格所在工作表应该用：

=MID(CELL(“filename”,A1),FIND(“)”,CELL(“filename”,A1))+1,255)

2、CELL（）、MID（）、LEFT（）、RIGHT（）等函数

=CELL(“filename”)'获取当前工作簿的路径、文件名、工作簿名称 =LEFT(CELL(“filename”),FIND(“[”,CELL(“filename”))-1)‘获取路径 =MID(CELL(“filename”),FIND(“[”,CELL(“filename”))+1,FIND(“]”,CELL(“filename”))-FIND(“[”,CELL(“filename”)]-1)‘获取文件名 =RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND(“)”,CELL(“filename”)))’获取当前工作表名称

3、DOLLAR（）函数和RMB（）函数

DOLLAR（）函数可以在数值前添加美元标识＄ RMB（）函数可以在数值前添加人民币标识￥

注意：使用“格式”菜单中的“单元格”命令来设置包含数字的单元格的格式与使用 DOLLAR 函数直接设置数字的格式之间的区别在于：DOLLAR 函数将结果转换为文本，而使用“单元格”命令设置格式的数字仍为数字。但可以继续在公式中使用由 DOLLAR 函数设置了格式的数字，因为 Microsoft Excel 在计算公式时会将以文本值输入的数字转换为数字。

4、PHONETIC（）函数

要在EXCEL中的汉字添加拼音，按以下步骤操作：

选中要设置拼音的单元格区域，执行“格式”/“拼音信息”/“显示或隐藏”，使输入拼音的区域显示出来，再执行“格式”/“拼音信息”/“编辑”，就可以在单元格的上半部分输入相应的拼音了。但一般不建议这样做，因为无法输入带声调的拼音，可以先在WORD中输入相应汉字，选中汉字并执行“格式”/“中文版式”/“拼音指南”，打开“拼音指南”对话框，单击“组合”按钮，将拼音字符组合在一起，选中拼音字符串并按CTRL+C，将拼音字符串复制下来，切换到EXCEL中，将带声调的拼音字符串粘贴到EXCEL中要编辑拼音的上半部空白处即可。如果要对拼音做调整，可以依次单击“格式”/“拼音信息”/“设置”。如果要将单元格中的拼音字符串提取出来，可以用PHONETIC（）函数，假设提示：如果用PHONETIC（）函数提取出来的拼音信息含有汉字，解决的方法是手工清除源单元格中汉字之间的空格即可。

如果PHONETIC（）函数的参数为单元格区域，则返回单元格区域左上角单元格中的拼音字符串。

5、如何清除单元格中用ALT+回车键进行的回车换行符？ 可以按以下步骤操作： 含有汉语拼音的字符串在A1单元格，则在C1输入公式=PHONETIC（A1）即可。编辑/替换，在“查找内容”框中键入 ALT键的同时，从小键盘输入10这两个数值，输入完毕后，查找框中不会出现什么内容，然后直接单击“全部替换”按钮，即可将所有通过ALT+回车组合产生的换行符删除了。

6、MID（文本，开始位置，字符数）函数

用途：返回文本字符串从指定位置开始的特定数目的字符。注意：

（1）如果 开始位置 大于文本长度，则 MID 返回空文本(“")。

（2）如果 开始位置 小于文本长度，但 开始位置 加上 字符数 超过了文本的长度，则 MID 只返回最多到文本末尾的字符。

（3）如果 开始位置 小于 1，则 MID 返回错误值 #VALUE!。（4）如果 字符数 是负数，则 MID 返回错误值 #VALUE!。

7、PROPER（文本）函数

用途：将文本字符串的首字母或任何非字母字符之后的首字母转换成大写，而将其余字母转换成小写。

8、ASC（）函数

用途：将全角（双字节）字符变为半角（单字节）字符。

9、WIDECHAR（）函数

用途：将字符串中的半角（单字节）字符变为全角（双字节）字符。

第七章 逻辑函数

1、公历纪年中如何判断某年份是否为闰年。

公历纪年中闰年的判断方法是：如果年份能被400整除或者年份能被4整除但却不能被100整除，则此年份为闰年，否则此年份为平年。根据此判断方法，在EXCEL中可以编公式判断某年份是否为闰年： 假设在A1单元格存放年份，则在B1单元格写公式：

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),”闰年“,”平年“)

也可以将此功能做成自定义函数，方便以后调用：

Function isLeapYear(year As Integer)As Boolean '判断某年份是否为闰年 isLeapYear =(year Mod 400 = 0)Or((year Mod 4 = 0)And(year Mod 100 <> 0))End Function 调用时，假设年份在A1，在B1单元格输入 =isleapyear(A1)返回结果为TRUE表示是闰年，返回为FALSE表示不是闰年

2、中国式排名的方法

使用RANK对区域排名时，如果出现相同的数据时，排名是按1、1、3、4、5、5、7„这样的形式，请问，如何才能实现依然按1、1、2、3、4、4、5„这样的顺序排名？ 比如：

B列为销售数量，C列是用RANK（）函数做的排名，现在想实现D列式的排名，可以在D2单元格输入数组公式：

=SUM(IF(B$2:B$9>B2,1/COUNTIF(B$2:B$9,B$2:B$9),”“))+1 输入完毕后按CTRL+SHIFT+回车 表示作为数组公式输入 然后将D2公式向下拖动复制到D9即可。

第八章 查找和引用函数

1、如何禁止在单元格输入数据时出现空格？

可以通过数据有效性来实现。比如以在A列输入数值并禁止输入空格为例，具体方法是：

选中A列，执行“数据”/“有效性”/在“设置”选项卡的“允许”下拉框中选择“自定义”，在下面的公式框中输入公式 =ISERROR(FIND(” “,A1))，然后可以在“出错警告”选项卡里输入出现错误时弹出的自定义提示信息。

2、ISNONTEXT（value）函数

如果value的值不是文本，则此函数返回TRUE，如果value值引用的单元格为空白单元格时，函数返回TRUE

3、根据指定的日期，判断其所属月份的上旬、中旬、下旬。

如图示例：根据C2单元格的日期判断是上旬、中旬、下旬。可以用LOOKUP函数和DAY（）函数，在D2单元格输入公式：=LOOKUP(DAY(C2),{1,11,21},{”上旬“,”中旬“,”下旬“})比如C4单元格的2005-12-19，DAY（C4）结果为19，然后在{1,11,21}里查找19，因为没找到19，所以查找小于等于19的值，结果找到11，最后返回11对应的数组{”上旬“,”中旬“,”下旬“}中的第2个值“中旬”。

4、MAX（）函数的参数如果是逻辑值、文本、空白单元格，则将被忽略。如果要求参数引用不能忽略逻辑值、文本，则要用MAXA()函数。例如：

统计A1：A12中最后出现数值为65的单元格所在行号,结果是11 {=MAX(IF(A1:A12=65,ROW(A1:A12),”“))} 公式中间过程IF(A1:A12=65,ROW(A1:A12),”“)的结果是”“,2, ”“,”“,”“,6, ”“,”“,”“,”“,11, ”“ 然后对这个数组求最大值，因为MAX（）忽略空白字符串，所以2、6、11中最大值是11

5、在单元格区域生成指定目录下的文件列表。

利用EXCEL中的宏表函数FILES（）可以生成指定目录下的文件列表，结果是一个水平文本数组。

例如：想在A列填充“D:mp3音乐书香音乐河月”文件夹下的文件名列表，可以执行“插入”/“名称”/“定义”，在名称框中填写FILELISTS，在引用位置填=FILES(”D:mp3音乐书香音乐河月*.*“)，然后再添加一个名称ML，引用位置填写=TRANSPOSE(FILELISTS)现在关闭定义名称对话框，选中A列，输入ML，然后按CTRL+SHIFT+回车结束输入。

这样会在A列显示出指定目录下的文件名列表，A列出现的#N/A表示的是选中的单元格个数大于文件名列表的个数。

6、MOD（被除数，除数）函数

可以返回两数相除的余数，结果的正负号与除数相同。MOD（）函数可以用INT函数取代： mod(n,d)=n-d*int(n/d)

7、ROW（引用）函数

返回引用的行号，如果省略“引用”，则对ROW（）所在单元格的引用，如果“引用”是一个单元格区域，则结果是一个垂直

第九章 工程和数据库函数

1、单位换算函数CONVERT（引用单元格，原单位，新单位）

例如：单元格A1的数值单位是“磅”，现在想转换成单位“公斤”，则公式： =CONVERT(A1,”lbm“,”kg“)例如：单元格A2是“英尺”数据，单元格B2是“英寸”数据，现在想将A2和B2数据同时转换成“米”，并求和，则C2填公式： =CONVERT(A2,”ft“,”m“)+CONVERT(B2,”in“,”m“)提示：如果结果为#NAME，则要先加载分析工具库，步骤：工具/加载宏/“分析工具库”打勾。

2、用DSUM（）函数实现多条件求和

例如：下图是一份原料采购清单，现在想对采购时间在2005-12-10日以前（含2005-12-10日），并且商品名称为显示器的采购金额进行求和，首先要在E2：F3单元格建立条件区域，接着在G3单元格输入公式：

=DSUM(A2:D20,”金额",E2:F3)其中A2：D20是整个数据区域，“金额”是要计算的列字段标题，必须要与数据区域的列字段标题保持一致，E2：F3是条件区域，条件区域也要有字段标题。

第三篇：关于VLOOKUP函数的用法

关于VLOOKUP函数的用法

“Lookup”的汉语意思是“查找”，在Excel中与“Lookup”相关的函数有三个：VLOOKUP、HLOOKUO和LOOKUP。下面介绍VLOOKUP函数的用法。

一、功能

在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。

二、语法

标准格式：

VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

三、语法解释

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以写为：

VLOOKUP(需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False)

1.Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。

2.Table_array 为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。

⑴如果 range_lookup 为 TRUE或省略，则 table_array 的第一列中的数值必须按升序排列，否则，函数 VLOOKUP 不能返回正确的数值。

如果 range_lookup 为 FALSE，table_array 不必进行排序。

⑵Table_array 的第一列中的数值可以为文本、数字或逻辑值。若为文本时，不区分文本的大小写。

3.Col_index_num 为table_array 中待返回的匹配值的列序号。

Col_index_num 为 1 时，返回 table_array 第一列中的数值；

Col_index_num 为 2 时，返回 table_array 第二列中的数值，以此类推。

如果Col_index_num 小于 1，函数 VLOOKUP 返回错误值 #VALUE!；

如果Col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。

4.Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。

四、应用例子

A B C D编号 姓名 工资 科室2005001 周杰伦 2870 办公室2005002 萧亚轩 2750 人事科2005006 郑智化 2680 供应科2005010 屠洪刚 2980 销售科2005019 孙楠 2530 财务科2005036 孟庭苇 2200 工 会

A列已排序（第四个参数缺省或用TRUE）

VLOOKUP(2005001,A17,2,TRUE)等于“周杰伦”

VLOOKUP(2005001,A17,3,TRUE)等于“2870”

VLOOKUP(2005001,A17,4,TRUE)等于“办公室”

VLOOKUP(2005019,A1:D7,2,TRUE)等于“孙楠”

VLOOKUP(2005036,A1:D7,3,TRUE)等于“2200”

VLOOKUP(2005036,A1:D7,4,TRUE)等于“工 会”

VLOOKUP(2005036,A1:D7,4)等于“工 会”

若A列没有排序，要得出正确的结果，第四个参数必须用FALAEVLOOKUP(2005001,A1:D7,2,FALSE)等于“周杰伦”VLOOKUP(2005001,A1:D7,3,FALSE)等于“2870”

VLOOKUP(2005001,A1:D7,4,FALSE)等于“办公室”VLOOKUP(2005019,A1:D7,2,FALSE)等于“孙楠”VLOOKUP(2005036,A1:D7,3,FALSE)等于“2200”

VLOOKUP(2005036,A1:D7,4,FALSE)等于“工 会”

五、关于TRUE和FALSE的应用

先举个例子，假如让你在数万条记录的表格中查找给定编号的某个人，假如编号已按由小到大的顺序排序，你会很轻松地找到这个人；假如编号没有排序，你只好从上到下一条一条地查找，很费事。

用VLOOKUP查找数据也是这样，当第一列已排序，第四个参数用TRUE（或确省），Excel会很轻松地找到数据，效率较高。当第一列没有排序，第四个参数用FALSE，Excel会从上到下一条一条地查找，效率较低。

笔者觉得，若要精确查找数据，由于计算机运算速度很快，可省略排序操作，直接用第四个参数用FALSE即可。

最近爱上了VLOOKUP，有人还对它进行了更新。因为它的漏洞就是只能返回重复值得第一个值。下面就详细来叙述一下吧！

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value 为需要在Table_array第一列中查找的数值。

可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。

Table_array 为需要在其中查找数据的数据表。

可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。对区域引用时，可以引用整列,excel会自动判断使用区域。

该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。

Col_index_num 为table_array中待返回的匹配值的列序号。

如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。

如果col_index_num小于1，函数 VLOOKUP 返回错误值值 #VALUE!；

如果col_index_num大于table_array的列数，函数 VLOOKUP 返回错误值 #REF!。

Range_lookup 为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。

如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；

近似匹配查询一般用于数值的查询，table_array的第一列必须按升序排列；否则不能返回正确的结果。

如果range_value为FALSE（或0），函数VLOOKUP将返回精确匹配值。

此时，table_array不必进行排序。如果找不到，则返回错误值#N/A；可isna检测错误后使用if判断去除错误信息。

VLOOKUP 经常会出现错误的#N/A,下面是几种可能性： 数据有空格或者数据类型不一致。

可以在lookup_value 前用TRIM（）将空格去除。

如果格式不一致，可以将数值强制转换成文本，lookup_value之后用&跟""表示的空字符串。将文本转换成数值，lookup_value*1进行运算。假设有以下两个表格：表1：

姓名 身份证 学科 成绩

张三 310111 物理 A

李斯 310112 化学 B王五 310113 政治 A赵六 310114 物理 B

张三 310111 政治 C

赵六 310114 语文 B刘备 310115 英语 D

表2：

姓名 身份证 住址 学科 成绩

曹操 319723 陕西马超 310197 甘肃赵云 319784 黑龙江

李斯 310112 浙江刘备 310115 江苏张三 310111 上海

请问：如何快速的将表2的学科和成绩自动填入？直接使用VLOOKUP即可。示例如下：

=VLOOKUP($B1,Sheet1!$B$1C$5,2,FALSE)

涵义：在Sheet1的单元格区间B1:C5，查找符合条件为：第一列与当前工作表单元格B1的值相等的第二列的值。

$B1 条件值

Sheet1!$B$1C$5 要查找区间（注意：请使用绝对引用）2 查找的结果为区域中的第几列FALSE 表示精确查找

*具体应用中大家注意各个参数的意义！调整参数来满足个人需要。

第四篇：HEX2DEC函数的用法

HEX2DEC函数的用法

来源： http://excel表格制作excel表格的基本操作

HEX2DEC函数：将十六进制数转换为十进制数

功能说明：将十六进制数转换为十进制数。

语法表达式：HE2XDEC(number)

参数说明：number：表示需要转换为十进制的数。使用函数时，该参数可以直接输入数值，也可以指定单元格。参数位数不能多于10位(40位二进制)，其中最高位是符号位，其余39位是数字位。如果是负数，那么用二进制数的补码表示。

使用说明：如果参数number是不合法的十六进制数，函数将返回错误值#NUM!。

如果参数number中包含字母，那么需要加上半角双引号，否则函数将返回错误值#NAME!。如果参数number的位数多于10位，函数也将返回错误值#NUM!。

如果参数number中全是数字时，参数中加或不加半角双引号，返回的结果都相同。如果函数HEX2DEC转换后的数值的位数比places指定的位数多，函数将返回错误值#NUM!。

实际应用：将十六进制数转换为十进制数。

在单元格B2中输入公式“=HEX2DEC(A2)”，转换为十进制数，然后利用自动填充功能，转换其他数值，如图11.14所示。

应用说明：在进制转换的计算中，HEX2DEC函数应用比较广泛。

上一篇：HEX2BIN函数：将十六进制数转换为二进制数

下一篇：在excel中如何按分类合并计算

第五篇：EXCEL查找与引用函数VLOOKUP的应用法及实例 详解

EXCEL查找与引用函数VLOOKUP的应用法及实例详解

在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOKUP。

在 VLOOKUP 中的 V 代表垂直。语法

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP（查找XX，在YY区域中，与查找值相对的第ZZ列的值，方式）lookup_value

为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。

如下图所示，C3单元格即为lookup_value

名称的引用，例如数据库或列表。

如下图所示：C6：F10即为

table_array

为需要在其中查找数据的数据表。可以使用对区域或区域

table_array

col_index_num

为table_array中待返回的匹配值的列序号。col_index_num为1时，返回table_array第一列中的数值； col_index_num为2，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数VLOOKUP返回错误值值#VALUE!； 如果col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!。

如下图所示，4表示数据表的第4列，col_index_num

range_lookup

如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列：„、-

2、-1、0、1、2、„、-Z、FALSE、TRUE；否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE，table_array 不必进行排序。

（通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。）为了方便起见，一般选用 FALSE，即表格不必要再按升序又排列一次。

table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。

如下图所示，FALSE 即为range_lookup表示第一列的数值不必进行排序

range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。

说明

如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。

VLOOKUP 返回错误值 #N/A。

如果 lookup_value 小于 table_array 第一列中的最小数值，函数

如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值 #N/A。

----------------------------对于可能产生错误值的防范 将公式进行以下修改

=IF(C3=“",”查询值为空，请填写查询值“,IF(ISERROR(VLOOKUP(C3,C6:F10,4,FALSE))=TRUE,”请检查参数是否正确",VLOOKUP(C3,C6:F10,4,FALSE)))如下图所示，如果查找时未指定名称，则公式的计算结果为“查询值为空，请填写查询值”

如下图所示，因使用的是FALSE，精确查找，当查找值为“西瓜”时，因该值不在列表中，会产生#N/A 错误，但使用公式用，可提示有参数错误

上述公式，使用了函数ISERROR检查是否有错误值，如果有兴趣你也可以使用函数ERROR.TYPE返回错误值对应的数字，从而明确显示是哪一类型的错误，错误值对应的数字如下表所示

呵呵，关于VLOOKUP就介绍到这里，祝大家工作愉快！