第六章 逻辑函数
（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即可。
7、第七章 查找和引用函数
（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（）函数
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（）所在单元格的引用，如果“引用”是一个单元格区域，则结果是一个垂直
8、第八章 工程和数据库函数
（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是条件区域，条件区域也要有字段标题。
Excel函数应用实例：按性别统计职工数
2008-1-4
a.函数分解
COUNTIF函数计算区域中满足给定条件的单元格的个数。
语法：COUNTIF(range,criteria)
Range为需要计算其中满足条件的单元格数目的单元格区域;Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。
b.实例分析
假设上面使用的人事管理工作表中有599条记录，统计职工中男性和女性人数的方法是:选中单元格D601(或其他用不上的空白单元格)，统计男性职工人数可以在其中输入公式“="男"&COUNTIF(D2:D600,"男")&"人"”;接着选中单元格D602，在其中输入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回车后即可得到“男399人”、“女200人”。
上式中D2:D600是对“性别”列数据区域的引用，实际使用时必须根据数据个数进行修改。“男”或“女”则是条件判断语句，用来判断区域中符合条件的数据然后进行统计。“&”则是字符连接符，可以在统计结果的前后加上“男”、“人”字样，使其更具有可读性。
七、Excel函数：IF函数
1、IF函数说明
IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数 IF 对数值和公式进行条件检测。
它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式。本参数可使用任何比较运算符。
Value_if_true显示在logical_test 为 TRUE 时返回的值，Value_if_true 也可以是其他公式。Value_if_false logical_test 为 FALSE 时返回的值。Value_if_false 也可以是其他公式。
简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。IF函数可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。
Excel 还提供了可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用 SUMIF 工作表函数。
2、IF函数应用
（1） 输出带有公式的空白表单
由于各部门关于人员的组成情况的数据尚未填写，在总计栏（以单元格G5为例）公式为：=SUM(C5:F5)
我们看到计算为0的结果。如果这样的表格打印出来就页面的美观来看显示是不令人满意的。是否有办法去掉总计栏中的0呢？你可能会说，不写公式不就行了。当然这是一个办法，但是，如果我们利用了IF函数的话，也可以在写公式的情况下，同样不显示这些0。如何实现呢？只需将总计栏中的公式（仅以单元格G5为例）改写成：
=IF(SUM(C5:F5),SUM(C5:F5),"")
通俗的解释就是：如果SUM(C5:F5)不等于零，则在单元格中显示SUM(C5:F5)的结果，否则显示字符串。
几点说明：
a. SUM(C5:F5)不等于零的正规写法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；
b. ""表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。
如果对上述例子有了很好的理解后，我们就很容易将IF函数应用到更广泛的领域。比如，在成绩表中根据不同的成绩区分合格与不合格。现在我们就以某班级的英语成绩为例具体说明用法。
某班级的成绩如图6所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的，否则记作不合格。
根据这一规则，我们在综合评定中写公式（以单元格B12为例）： =IF(B11>60,"合格","不合格")
语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。
在综合评定栏中可以看到由于C列的同学各科平均分为54分，综合评定为不合格。其余均为合格。
(3) 多层嵌套函数的应用
在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。有办法一次性区分吗？可以使用多层嵌套的办法来实现。仍以上例为例，我们设定综合评定的规则为当各科平均分超过90时，评定为优秀。
说明：为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意Excel的IF函数最多允许七重嵌套。
根据这一规则，我们在综合评定中写公式（以单元格F12为例）：
=IF(F11>60,IF(AND(F11>90),"优秀","合格"),"不合格")
语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90（为了让大家体会一下AND函数的应用，写成AND(F11>90)，实际上可以仅写F11>90），如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。
在综合评定栏中可以看到由于F列的同学各科平均分为92分，综合评定为优秀。
八、Excel查询与引用函数：HLOOKUP、LOOKUP、MATCH、VLOOKUP
1、LOOKUP函数与MATCH函数
LOOKUP函数可以返回向量（单行区域或单列区域）或数组中的数值。此系列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数 HLOOKUP。当比较值位于要进行数据查找的左边一列时，使用函数 VLOOKUP。
如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用函数 MATCH 而不是函数 LOOKUP。MATCH函数用来返回在指定方式下与指定数值匹配的数组中元素的相应位置。从以上分析可知，查找函数的功能，一是按搜索条件，返回被搜索区域内数据的一个数据值；二是按搜索条件，返回被搜索区域内某一数据所在的位置值。利用这两大功能，不仅能实现数据的查询，而且也能解决如"定级"之类的实际问题。
2、LOOKUP用于返回向量（单行区域或单列区域）或数组中的数值。
函数 LOOKUP 有两种语法形式：向量和数组。
（1） 向量形式
函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。
其基本语法形式为LOOKUP(lookup_value,lookup_vector,result_vector)
Lookup_value为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。
Lookup_vector为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。
需要注意的是Lookup_vector 的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。
Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。
如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。
如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。
（2） 数组形式
函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。通常情况下，最好使用函数 HLOOKUP 或函数 VLOOKUP 来替代函数 LOOKUP 的数组形式。函数 LOOKUP 的这种形式主要用于与其他电子表格兼容。关于LOOKUP的数组形式的用法在此不再赘述，感兴趣的可以参看Excel的帮助。
3、 HLOOKUP与VLOOKUP
HLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。
VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。
当比较值位于要进行数据查找的左边一列时，请使用函数 VLOOKUP。
语法形式为：
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
其中，Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。Lookup_value 可以为数值、引用或文字串。
Table_array查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。
Row_index_num为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。
Col_index_num为相对列号。最左列为1，其右边一列为2，依此类推.
Range_lookup为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。
下面详细介绍一下VLOOKUP函数的应用。
简言之，VLOOKUP函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数VLOOKUP的查找可以达到两种目的：一是精确的查找。二是近似的查找。下面分别说明。
（1） 精确查找--根据区域最左列的值，对其它列的数据进行精确的查找
示例：创建工资表与工资条
首先建立员工工资表
