《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:A100),“闰年“,“平年“)也可以将此功能做成自定义函数，方便以后调用：Function isLeapYear(year As Integer) As Boolean 判断某年份是否为闰年isLeapYear = (year Mod 400 = 0) Or (year Mod 4 = 0) And (year Mod 100 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 值引用的单元格为空白单元格时，函数返回 TRUE3、根据指定的日期，判断其所属月份的上旬、中旬、下旬。如图示例：根据 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 中最大值是 115、在单元格区域生成指定目录下的文件列表。利用 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