Excel 实战技巧-个人编辑电子版 第四篇

第四篇 函数 本篇主要讲解函数的结构组成，分类和编辑、查错、审核操作，以及引用、数据类型、运算符、数组、数组公式等一些基础概念，理解和掌握这些概念对下步学习和使用函数公式来解决问题会有很大的帮助。 第13章 函数介绍 技巧141 慧眼识函数 公式是对工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中数值执行计算的等式，函数则是一些预先编写的，按特定的顺序或结构执行计算的特殊公式。根据应用领域的不同。EXCEL函数一般可以分为：逻辑、信息、日期与时间、数学与三角计算、统计、查找与引用、数据库、文本、财务、工程函数等类别。此外还有EXCEL4.0宏表函数、扩展函数和外部函数等。 许多读者在碰到较复杂的函数公式，尤其是函数嵌套公式时，往往不知从何读起，其实只要掌握了函数公式的结构等基本知识，就可以像庖丁解牛一样把公式进行分段解读 141-1 函数公式结构 函数公式是以‘＝’号开始，后面依次是函数名称、左括号、以半角逗号分隔的参数和右括号组成 141-2 函数参数和嵌套 函数的参数可以是：数字、文本、逻辑值数组、错误值或单元格引用，指定的参数都必须为有效参数值。参数也可以是常量、公式或其它函数。有些函数不带参数如：now()、today()、RAND())等，当函数的参数也是函数时，我们称之为函数的嵌套，比如上图中的样式就是一个嵌套函数。其中IF和SUM都是有效的函数名称，例中的IF函数它有三个参数：第一个参数是A1>0的逻辑判断公式；第二个参数是sum(b1：G1)是作为参数形式出现的嵌套函数；第三个参数””则是空方本。这个公式表达的意义为：当A1为大于0时（正数）对B1：G1区域进行求和，否则返回空文本。也可以这样理解：如果A1>0时就对B1到G1区域进行求和，否则显示为空白。 141-3 理解技巧 在函数公式中，最左边的等号是使表达式成为公式。括号用于包含参数，那么括号的配对是让一个函数成为完整个体。参数与参数之间使用逗号进行分隔，逗号的分隔让每个参数成为完整个体，逗号就像是函数公式的关节，由此入手，就可以清楚的解读公式了。 技巧142 函数公式的输入和编辑 142-1 使用工具栏按钮输入函数 许多读者接触EXCEL公式计算都是从求合开始，所以对工具栏上的‘∑’按钮肯定不会陌生，当用鼠标点击此按钮右边的倒三角进将出现求和、求平均、计数、最大值、最小值、其它函数6个选项 142-2 使用插入函数向导 插入函数向导是一个交互式输入函数的对话框，选中任意单元格，按下shift+F3组合键或者单击编辑左侧的按钮，可以调出‘插入函数’对话框，如果你对函数的所属类别不太熟悉，可以在此对话框的‘搜索函数’方本框里输入简单的描述来寻找合适的函数，比如输入‘余数’，然后按enter键则EXCEL会在‘选择函数’列表框中‘推荐’一些函数供用户选择使用如mod 如果你知道所需函数的类别，可以先在‘选择类别’下拉列表中选取分类，然后从‘选择函数’列表中选择函数。当然类别中的函数数量较多时可以移动滚动条或按下函数开头字母来快速定们到你所需要的函数。 选定函数后单击‘确定’按钮EXCEL会将函数写入编辑栏中，同时弹出‘函数’对话框，用户可以方便的输入函数所需要的各项参数，每个参数框右边会显示该参数的当前值，对话框下方有关于所选择函数的一些简单描述文字，以及对各个参数的相关说明 个别函数的参数提示是错误的，如单击IF函数的第2个参数，比如下方提示‘当logical_test为TRUE时返回的值发。如果忽略，则返回TRUE’就是错误的，这个参数不能忽略，与函数帮助文件的描述也不同，在编辑栏输入等号和函数名称后[＝if]，按ctrl+a也可以显示‘函数参数’对话框. 142-3 手工输入 熟练的用户可以直接往单元格中输入函数公式，输入函数公式的方法与输入其它数据没有差别，只是要保证输入的内容符合函数公式的结构即可。 142-4 公式的编辑和复制 当需要修改公式时，可在编辑栏中移动光标到相应的地方直接修改，或是调出‘函数参数’对话框，在对话框中进行修改 公式可以通过双击或拖动单元格右下角的填充柄进行复制，也可以用复制粘贴单元格的方式进行复制。 技巧143 函数工具提示 143-1 设置函数工具提示选项 单击工具栏上的‘工具’－‘选项’，在‘选项’对话框的‘常规’选项卡中勾选‘函数工具提示（U）’复选框，可以使EXCEL在编辑栏编辑公式时提示函数状态 当光标移至提示工具中已输入参数值所对应的参数名称时，光标变为手指形状，此时单击该参数名称则在公式中将以黑色背景突显该名称以加粗字体来突显。有了这个工具，用户在编辑公式时能方便地知道当前正在输入哪个函数的参数，有效的避免了错、漏。 143-2 在单元格中显示函数完整语法 在单元格中输入一个函数公式时，按下ctrl+shift+A组合键可以得到包含该函数完整语法的公式，如键入＝if，然后按下ctrl+shift+A组合键,则可在单元格得到： 如果所输入的函数有多种语法，如lookup函数，此快速键将弹出‘选定参数’的对话框。选择你所需要的一项参数组合所按‘确定’按钮，EXCEL会自动的返回相应的完整语法。 143-3 阅读使用函数帮助文件 EXCEL内置函数多数都有相应的帮助文件，单击‘插入函数’对话框或‘函数参数’对话框左下角‘有关该函数的帮助’的链接，或者单击‘函数提示工具’左边的函数名称，都能够调出相关函数的帮助窗口 一般情况下，函数帮助文件包括函数的简要功能介绍、语法、要点、说明、示例等几部分，用户可以根据帮助中的提示，将示例复制到工作表中进行实践，以加深对函数的理解。 技巧144 函数公式查错与监视 144-1 错误的类型 在使用EXCEL公式进行计算时，可能会因为某种原因而无法得到正确结果，返回一个错误值。 错误值类型 含义 # # # # # 当列不够宽，或者使用了负的日期或负的时间时，出现些错误提示 #VALUE！ 当使用的参数或操作数类型错误时，出现些错误提示 #div/0! 当数字被零（0）除时，出现些错误提示 #name? 当EXCEL未识别公式中的文本时，出现些错误提示 #n/A 当数值对函数或公式不可用时，出现些错误提示 #ref! 当单元格引用无效时，出现些错误提示 #num! 公式或函数中使用无效数字值时，出现些错误提示 #null! 当指定并不相交的两个区域的交点时，用空格表示两个引用单元格之间的运算符, ，出现些错误提示 144-2 使用错误检查工具 当公式的结果返回错误值时，可以使用EXCEL的错误检查工具，快速查找错误原因，为了更好的使用这项功能，首先应该单击工具栏上的‘工具’－‘选项’，在‘选项’对话框的‘错误检查’选项卡选择‘允许后错错误检查’ 如此当单元格内的公出错时，单元格左上角会自动出现一个绿色小三角形，即EXCEL的智能标记。 选定包含错误的单元格，单击出现的按钮 ！ 显示下拉菜单，菜单中包括错误的类型，关于此错误的帮助链接、显示计算步骤、以及在公式编辑栏中编辑等选项，用户可以方便的选择下一步操作： 右键单击工具栏菜单，在弹出的菜单中选择‘公式审核’，可调出公式审核工具条， 单击错误检查按按钮，EXCEL会逐个显示出错单元格，供用户检查。如果公式的错误是由于它引用的单元格错误所引起，哪么在‘错误检查’对话框中将出现‘追踪错误’按钮，单击它可以使EXCEL在工作表中标公式引用中包含错误的单元格及其引用单元格。蓝色箭头表示错误的引用源指向，红色箭头表示错误导致的从属单元格指向 144-3 监视窗口 如果用户创建了一个链接到其它工作簿数据的电子表格，可以利用监视窗口随时查看到工作表、单元格和公式函数在改动时是如何影响当前数据的。 单击公式审核工具栏中的‘显示监视窗口’按钮，将可以调出‘监视窗口’对话框，通过它可以观察单元格有其中的公式，该对话框可以监视单元格的下列属性：所属工作、所属工作表、名称、单元格、值以及公式。每个单元格只可以有一个监视窗口,可以先选择工作表上的一个或多个包含公式的单元格，然后单击‘监视窗口’对话框中的‘添加监视’按钮，监视所选的单元。监视窗口可以移动并改变窗口边界来获取最佳的视图效果。 技巧145 分步查看公式计算结果 选中包含公式的单元格，然后单击‘公式审核’工具栏中的‘公式求值’按钮，会弹出‘公式求值’对话框，通过单击对话框中的‘求值’按钮，‘求值’域中将按公式计算的顺序逐步显示公式的计算过程。在对多个单元格数组公式使用这功能时，有可能无法正确显示选定单元格的计算结果。 145-2 神奇的F9 在编辑栏中选定公式中需要显示计算结果的部分（即用光标‘抹黑’），按下F9键，即可在编辑栏显示该部分的计算结果。在进行选择时，注意要包含整个运算对象，比如选择一个函数时，必须选定整个函数名称、左圆括号、参数和右圆括号。 例如抹黑公式＝IF(A1>0,SUM(B1:g1),””)中的SUM（B！：G1），按下F9键之后公式变成＝IF（A>0,33,””）[这里的33为SUM的计算结果]，若函数公式较长时，可以用F9键对各个部分进行分段计算预演，以便公式查错和更正。注意：抹黑后的参数在公式中只作为一个结果显示，如果源数据再改变时，将不能更新和计算。 另外使用F9键还可以将单元格引用转换为常量数组，利用此技巧，用户可以快速输入常量数组。例如：在A1和B1单元格中分别输入‘WOED’和‘EXCEL’，在C1输入公式＝A1：B1这时再抹黑公式中的A1：B1，按下F9键后，公式则变为 按F9键之后如果要用该计算结果替换原公式选定的部分，请按ENTER或者ctrl+shift+enter组合键来返回普通公式，如果不小心按了enter键，还可以通过‘编辑’－‘撤销’或者按ctrl+Z组合键来取消。 技巧146 保护隐藏工作表中的公式 如果不希望工作表中公式被其它用户看到或修改，可以对其进行保护和隐藏 1、 按F5键调出‘定位’对话框，单击‘定位条件’按钮。在‘定位条件’对话框中选择‘公式’项，按‘确定’按钮后，将选中工作表中所有包含公式的单元格 2、 按ctrl+1组合键，在弹出的‘单元格格式’对话框中，切换到‘保护’选项卡，将‘锁定’、‘隐藏’复选框勾选 3、 单击工具栏上‘工具’－‘保护’－‘保护工作表’，如果需要也可以设置密码，单击‘确定’按钮 4、 但这种方法是锁定了整个工作表，更多有关隐藏单元格数据的内容，请参阅技巧36 第14章 函数基础 技巧147 单元格的引用方法 一个EXCEL工作表由65536行X256列单元格组成，以左角上第一个单元格为原点向下、向右分别为行、列坐标的正方向。在EXCEL中可以使用表示单元格在工作表上所处位置的坐标集来引用单元格 147-1 A1引用样式 默认情况下EXCEL使用A1引用样式，此样式用数字1~~65536表示行号，用字母A~~IV表示列标。例如：第C列和第5行交叉处单元格的引用形式为‘C5’。如果引用整行或整列，可以省去列标或行号比如：1：1表示第一行，即（A1：IV1）；A：A表示A列，即（A1：A65536） 147－2 R1C1引用样式 单击工具栏上的‘工具’－‘选项’，在‘选项’对话框的‘常规’选项卡中，勾选‘R1C1引用样式’复选框，可以使用‘R’与数字的组合来表示行号，‘C’与数字的组合则表示列标，R1C1样式能够更直观地体现单元格的‘坐标’概念 147-3 三维引用 引用单元格区域时，冒号表示冒号两边所引用的单元格为左上角和右下角之间的所有单元格组成的矩形区域。 当右下角单元格与左上角单元格处在同一行或者同一列时，这种引用称为一维引用如：A1：D1或者A1：A5。而类似A1：C5表示从A1单元格左上角，C5单元格为右下角的5行3列的矩形区域，形成了一个二维的面，该引用称为二维引用。当引用区域不只在构成二维平面的方向出现时，其引用就是多维的，是一个由不同层次上多个面组成的空间模型，如公式，表示对工作表sheet1到sheet3的A1：C5单元格区域求和，在此公式的引用范围中，每个工作表的A1：C5都是一个二维平面，多个二维平面在行、列和表三方向上构成了三维引用 技巧1148 快速切换引用类型 148-1 相对引用与绝对引用 当在B3单元格输入＝A1来引用A1单元格的内容，然后向下方拖以复制公式时，各单元格的公式分别自动变为＝A2、＝A3. ……。这因为EXCEL把引用的A1单元格和公式所在的单元格的坐标作比较得出相对位置，当公式复制到其它单元格时，保持相对位置不变 比如A1单元格在B3单元格的左上角2行2列处，把B3的公式复制到B4时，为了保持相对位置不变，B4也去引用自己左上角2行2列的单元格，即A2，所以公式在B4中变成了A2，这就叫相对引用 除非公式中包括其它工作表或工作簿中的单元格，否则EXCEL默认在公式中使用相对引用。如果用户希望在复制公式时能够固定引用某个单元格或区域，则需要使用绝对引用。绝对引用使用两个美元符号来表示，比如B3的公式改为＝$A$1,则无论将B3的公式复制到工作表中何处，公式的引用对象都不会发生变化，仍然为＝$A$1 在相对与绝对引用外，EXCEL还有一种混合引用，在这种引用方式中，只绝对引用列或行中的一项，而相对引用另一项，混合引用只包含一个美元符号如＝$a1、=a$1 值得一提的是R1C1样式的相对符[ ]里面能够用数字来标明相对于当前单元格的位置，比如在R2C2[即B2单元格]中用＝的[－1]c[3]得到的是R1C5[即E1单元格]，表示所引用的单元格在公式所在的单元格的上方1行右边3列的位置上， 不同引用类型的区别 A1样式 R1C1样式 特性 $A$1 R1C1 绝对引用，向右向下拖动时公式不变引用关系 A$1 R1C[*] 行绝对引用，列相对引用，向下拖动公式时公式不改变引用关系， $A1 R[*]C1 行相对引用，列绝对引用，向右拖动公式时不改变引用关系 A1 R[*]C[*] 相对引用，向右或向下拖动时公式会改变引用关系 表中*号表示数字 148-2 快速切换相对引用、绝对引用、混合引用 相对、对对、混合引用大大方便了用户复制公式的操作，不必逐个输入公式，但是使用绝对引用和混合引用时需要手工在相应位置输入美元符号，不太方便。事实上EXCEL提供了快速键F4，可以在编辑栏对引用单元格的引用类型进行快速切换。 比如在B3单元格中引用A1单元格，在编辑栏中选中A1后按F4键，则该引用将依次在绝对，行绝对，列绝对和相对引用间进行循环切换=$A$1、=A$1、=$A1、=A1 技巧149 数据类型区分及转换 149-1 数据类型及排序规则 EXCEL数据一般可以分为文本、数值、日期、逻辑、错误等几种类型，其中日期是数字的一个特殊格式表现方法。此外数字和数值是两个不同的概念，数字可以以文本的形式出现，也可以是数值，日期等。一般在未明确指定的情况下，数字指的是数值型的数字。在EXCEL函数帮助文件中，经常会看到关于升序的描述为‘数值必须按升序排列：-2、-1、0、1、2. ……a~~z、false、true’，这是EXCEL的一个规则，即数值小于文本，文本小 于逻辑值，错误不参与排序、 149-2 逻辑值与数值的关系 EXCEL中逻辑值只有true和false两个，它们与数值的关系为 1、 在数值运算中true=1 false=0 2、 在逻辑判断中0＝false，所有非0数值＝true。这两条准则非常重要，在EXCEL函数公式的简化及计算中用途很广。 149-3 数据类型转换 以文本形式储存的数字，比如在A1中输入”’123”或者将A1的单元格格式设置为文本后输入“123”，则该数字不能直接作为数值参与函数计算，这时公式＝A1＝123将返回false公式=sum(A1:A2)将无法得到正确的结果。通常用以下6种方法可将A1中以文本形式储存的数字转换为数值型： 混淆文本型数字与数值型数字是许多用户常犯而又不容易察觉的错误，通过LEFT、MID、TEXT等文本函数计算到的结果都是文本型，如果未进行转换而代入下一步与之不匹配的计算将得不到正确的结果 149-4 关于空和空文本 在函数公式应用中，经常会用类似下面的公式来屏蔽不希望出现的计算结果＝if(a1=””,””,a1*B2)。这个公式表示，若A1单元格为空时则显示为空，否则返回A1与B2的积。这里涉及的空和空文本有两个概念 1、‘空’就是选中单元在编辑栏什么也没有这种是‘真空’ 2、‘空文本’即一对英文双引号” “。表示文本里面什么也没有，是一个空的字符串，其字符长度为0，一般同公式返回这种为‘假空’ 无论A1为真空还是假空，公式＝A1＝””都返回true。但只有当A1为真空时＝isblank(A1)才返回true，当A1为真空是公式＝A1返回0，当A1为假空时公式＝A1返回的是空文本 149-5 带引号的文本 函数公式中包含文本是用半角双引号，括住文本如‘你好’表示为“你好”，若要表示文本‘你好’时，则必须将该文本的双引号改为2层双引号。比如用find函数在A1单元格查找带引号的文本‘你好’：＝find（””你好””,A1），最外一层双引号表示，括的是文本，里面2层双引号（涂黑部分）表示该文本原来有一对双引号。 技巧150 逻辑关系判断 逻辑判断是指有具体意义并能判定真或假的陈述语句，是函数公式的基础，不仅关系到公式的正确与否，也关系到解题思路的简繁，只有逻辑条理清晰，才能写出简洁有效的公式，常用的逻辑关系有3种，与、或、非。 150-1 与（AND关系） 当两个或多个条件必须同时成立才判定为真时，称判定与条件关系为逻辑与关系，即平时说的‘且’。and函数常用于逻辑与关系运算。 例1：用公式表示当A1单元格的值大于0且小于等10的时候返回true。＝and(a1>0,A1<=10) 例2：B列是性别、C列是年龄、D列是职称。要在E列输入公式筛选40以上的男教授的记录。其中E1的公式为＝and(B1=”男”,C1>40,D1=”教授”) 150-3 或（not关系） 当条件只要成立就判定为假时，称判定与条件的关系为逻辑非关系，NOT函数用于将逻辑值反转 例：A列存放着人员学历，分为中专、高中、大专、本科、硕士等，要在B列输入公式筛选除硕士以外的记录＝not(a1=”硕士”)，也可以用＝a1<>”硕士”表示 150-2 或（or关系） 当条件只要成立就判定为假时，称判定与条件的关系为逻辑或关系，or函数常用于逻辑或关系运算 例：a、B、C三列分别是语文、数学、英语成绩，要在D列输入公式筛选所有不及格的记录（60分）其中公式为：or=(A1<60,B1<60,C1<60) 150-4 逻辑关系的嵌套 函数if(Logical_Test,Value_If_True,Value_If_False)的第一个参数判定真假，为真返回第2个参数，为假返回第3个参数。如果第2个参数和第3个参数还需要进一步判定，这时新的逻辑判定作为参数嵌套于原有判定，在执行原有判断的基础上时行。 例：A1为成绩，要求用公式在B1返回成绩等级：低于60分＝不及格、60-79＝及格、80-89＝良、89-100＝优 1、 简单的堆积条件：如分数在60-79分段时表达为and(A1>=60,A1<80).其它类似。得到分式：＝if(A1<60,”不及格”,if(and(A1>=60,A1<80),”及格”,if(and(A1>=80,A1<90),”良”,if(and(A1>=90,A1<=100,”优”))))，这个公式虽然没有错误但具有多个冗余判断。 2、 归纳整理，如果A1不低于60即第1个参数为假的同时已经包含A1>=60为真的判定了，利用这个逻辑关系的嵌套，正确的公式表达如下：=if(a1<60,”不及格”，if(A1<80,”及格”,if(A<90,”良”,”优”))) 150-5 逻辑关系运算 在实际应用中，逻辑值是可以直接参与函数公式计算的，通常看到如下实例的解法 例1：查找B1在A1：A10数据区域中是否存在的公式为：=if(countif(A1:A10,B1),”存在”,””) 例2：统计A1：A10数据区域中大于‘0’的数值个数年的公式为：=SUM（a1:a10>0）*1) 为什么公式1中IF的条件判断不用‘countif(a1:a10,了)>0’，而公式2中为什么要在sum函数中使用*1。为了解释以上疑惑，先来了解下几种逻辑值之的运算结果。 根据149-2中介绍的准则，以上6项计算简单列举了常逻辑运算的结果，这也是在数组公式运算中常见、常运用的理论关系。 了解了以上的运算后，再来看公式1，由于countIF函数统计结果只能为非负数（结果>=0），那么如果B1存在，couNTIF函数结果必定>0，否则结果＝0，所以就可以不用添加>0进行判断。然而公式2，如果sum函数的参数是数组，而且这个数组是由逻辑值组成，那么要对这些逻辑值求和，就必须先将逻辑值进行运算（*1或+0），sum函数才能正确求和 技巧151 运算符号 151-1 运算符号类型及运算顺序 对公式中的元素进行特定类型的运算，EXCEL包含4种类型的运算符，算术运算符、比较运算符、文本运算符、引用运算符。EXFEL将根据公式中运算符的特定顺序从左到右的计算公式。如果公式中同时用到多个运算符时，EXCEL将根据下表所示的顺序运算： EXCEL的计算公式与我们平时使用的数学计算式相比，除了运算符号不同，括号也不一样，比如数学公式＝(3+2) ×[2+(10-4) ÷3]+32,然而在EXCEL中的表达式为：(3+2)*(2+(10-4) /)+3^2 其计算顺序是由最内层的括号逐级向外，若公式中包含相同优先级的运算符，EXCEL将从左到右进行计算，若要更改求值的顺序，需将公式中要先计算的部分用括号把它括起来，例如公式＝int((a1+1/3)中，首先执行A1+1运算，再将得到的和除以3，最后取整。 151-2 ‘――’减负运算 公式＝（――（left(A1:A10)=”王”））其中的‘――’是什么意思？我们在150-5的实例中使用了‘*1’或‘+0’的方法将逻辑值转换成数值进行运算，事实上，使用‘――’和前者的目的完全相同。‘――’就是简化后的‘减负运算’，它的完整形式应该是0－（－逻辑数据）＝数值，只是公式中省略了前面的0而已，减负运算除了将逻辑值转换为数值外，还可以将‘文本型数值’转换为真正的数值。 例：下面的公式将一个多位数（如9582）的各个数字进行求和，结果为24 ＝sum(――(mid(9582,(1.2,3,4),1))) 151-3 运算符巧替逻辑数 在逻辑值与数值的关系中，利用‘在逻辑判断中，0＝false，所有非0数值＝true’这条规则，可以在IF函数的第1个条件参数中使用算术运算符*和+来替换and和or函数 例1：要判断A1是否大于60且小于70，并返回‘符合’和‘不符合’。通常使用公式＝if(and(a1>60,A1<=70),‘符合’,‘不符合’)其中当AND函数的各参数都返回TRUE时，该公式返回TRUE。利用逻辑值与数值转换的规则可以使用*替代AND函数，公而公式改为：＝if(a1>60)*(A1<=70,‘符合’,‘不符合’),基于相同的原理，还可以使用+替代or函数。 *和+还可以替换一些需要用if函数判断返回数值的问题，比如要根据B列的性别来判断退休年龄＝if(B2=”男”,65,55)或=（B2=”男”）5*+55，当然这些公式只是为了说明‘+ － * /’运算符在逻辑判断中的使用，而不建议用户常常这样使用，因为公式＝if(B2=”男”,65,55) 比=（B2=”男”）5*+55更有可读性，只有当用户能够熟练的编写公式以后，再考虑对公式进行简化、优化。 151 通配符 EXCEL中的*（星号）、？（问号）和~（波形符）可作为通配符用于查找、统计等运算的比较条件中。 ‘*’表示任何字符， 例1：计算A1：A10中以A开头的记录个数＝COUNTIF（A1：A10，”*A*”） 例2：计算A1：A10中包含字母A的记录个数＝COUNTIF（A1：A10，”*A*”） 例3：查找A列中以字母A开头的记录对应在B列的值＝vlookup(“A*” ‘？’表示任何单个字符 例4：计算A1：A10中第2个字母是A的记录个数公式＝＝COUNTIF（A1：A10，”?A*”） 例5：计算A1：A10中以字母A开头的3个字符的记录个数公式＝COUNTIF（A1：A10，”*A？？”） ‘~’用于解除字符的通配性，即当要查找或统计的是*、？、~本身时，须在其前面加上~以通知EXCEL不将后面所跟的‘*、？、~’作为通配符处理 例6：计算A1：A10中‘1*1’的个数公式＝COUNTIF（A1：A10，”1~*1”），其中~*表示查找*本身 例7：计算A1：A10中第2个为*的记录个数公式＝COUNTIF（A1：A10，”？~**”）其中第一个？和最后一个*为通配符，~*表示*本身。这里主要是为了说明通配符的用法，当然了像例7中的公式我们还可以写成＝sum(--(mid(A1:A10,2,1)=”*”)) 技巧152 函数参数的处理技巧 在函数的实际使用中，并非总是需要把一个函数的所有参数都写完整才能计算，可以根据实际需要对参数进行省略和简化，以达到缩短公式长度或减少计算步骤的目的： 152-1 函数参数的省略 函数帮助文件会将其中各个参数表达的意思和要求列出来，仔细看看就会发现，有很多参数的描述包括‘忽略’、‘省略’、‘默认’等词，而且会注册如果省略该参数则表示默认该参数为某个值。参数的省略，是指该参数连同该参数存在所需的逗号间隔都不出现在函数中， 例：判断B2是否与A2的值相等产，如果是则返回TRUE，否则返回FALSE，＝if(B2=a1,true,false)可以省略为=if(b2=a1,true) 常见的参数省略情况还有： 1、 indirect函数的a1如果不指定，则默认为A1引用样式 2、 offset函数的height和width如果不指定，则默认与reference的尺寸一致 3、 find函数、search函数的start_num如果不指定，则默认为1 4、 left函数、right函数的unm_chars如果未指定，则默认为1 5、 sumif函数的sum_range如果未指定，则默认对第一个参数range进行求和 152-2 函数参数的简写 部分函数中的参数为TRUE或FALSE的时候，可以用0来代替，甚至连0也不写，而只用逗号占据参数位置，下面3个公式是等价的： 1、＝vlookup(a1,b1:c10,2,palse) 2、＝vlookup(a1,b1:c10,2,0) 3、＝vlookup(a1,b1:c10,2,) 此外还有很多要指定参数0的也可以简写为只保留该参数位置的逗号： 1、=max(a1,0) or =max(a1) 2、=if(b2=a1,1,0) or =if(b2=a1,1,) 3、=offset(a1,0,0,10) or =offset(a1,,,10) 但是并非所有这种简写都表示该参数为0。比如在文本函数substitute和replace中new_text是希望替换的新字符，如果只保留它前面的逗号，则表示用””空文本而不是0 注意：省略参数是根据函数的默认规则将整个参数（包括它所需的逗号间隔）从函数公式中移除，所以只能按照从函数右括号一侧向左的顺序依次省略，参数的简写则用逗号保留了参数的对应位置，表示该参数的常量0、false或空文本等 152-3 函数参数的简化 有些针对数值的逻辑判断可以利用‘0＝false’和‘非0数值＝true’的规则来简化，比如已知a1单元格的数据可能是数值的前提下可以将公式＝if(a1<>0，B1/A1，””)简化为＝if(a1,b1/a1,””) 技巧153 函数公式的限制与突破 1531-1 EXCEL关于公式计算方面的限制 EXCEL在公式计算方面有其自身的标准与规范，这些规范对公式的编写有一定的限制，它们主要是： 1、 公式内容的长度不能超过1024个字节； 2、 公式中函数的嵌套不能超过7层 3、 公式中函数的参数不能超过30个 在实际工作中，常常需要突破这种限制才能编写出满足计算要求的公式，下面的两个技巧介绍了这方面的应用： 153-2 突破函数的7层嵌套限制 当函数B在函数A中用作参数时，函数B为第2级函数，＝if(a1>0,sum(b1:G1),””) 其中sum函数是第2级函数，因为它是if函数的参数，如果在sum函数中继续嵌套函数则为第3级函数，以此类推可以包含多达7层的函数嵌套。尽管如此，还可以使用定义名称的方法来突破函数嵌套级数的限制， 例：要将a1中字符串‘我113爱322学43习7E53x6c47e8i9!220’的数字去掉，用substitute函数来解决。在B1中输入公式：＝substitute（substitute（substitute（substitute（substitute（substitute（substitute（substitute（A1，0），1，），2，），3，），4，），5，），6，），7，）之后就不能直接再套函数了，因为从第2个substitute开始，每一个都是前一个的参数，已经达到7级嵌套，此时可以按ctrl+F3组合键，弹出定义名称对话框，将上面这个公式复制到‘引用位置’并定义为名称‘X’，然后在B1中输入＝substitute（substitute（X，8，），9）如此就可以得到去掉数字的字串‘我爱学习EXCEL！’了，同理，可以用定义名称的方法解决用IF判定以及其它函数因嵌套层数超过7层而导致公式无法输入的问题，[有关定义名称请参阅第七章] 153-3 突破30个函数参数的限制 EXCEL规定的参数个数量多为30个（用29个逗号隔开），如sum函数、COUNT函数、COUNTA函数、AVERAGE函数、CHOOSE函数等，比如要计算（下图）表格中蓝色背景单元格中数值的平均值，这些单元格的数量超过30个，这时可以这样输入：＝AVERAGE（(A1,B3,c6,d4…….)）即在函数的两边加上一对括号，形成联合区域来作为参数。相当于只有1个参数，这样公式就只受到字符个数的限制了 技巧154 函数的易失性 有时，当用户打开一个工作簿但不做任何更改就关闭时，ＥＸＣＥＬ却提醒是否保存，这是因为ＥＸＣＥＬ文件用到了一些具有volatile特性的函数，即‘易失性函数’。这种特性的表现是‘使用这些函数后，会引发工作表的重新计算’，因此每激活一个单元格或者在一个单元格输入数据，甚至只是打开工作簿，具有易失性的函数都会自动重新计算。 　常见的易失性函数有：now( )、toady( )、cell( )、offset( )、sumif( )、countif( )、inirect( )、info( )、randbetween( )等。 　比如使用：＝inT(rand( )*100)，在Ａ１：Ｄ１０单元格区域得到一组100以内的随机整数，任何对于工作表的编辑操作都会引发公式重算。 　 另外在技巧１６１中也介绍了一个借助易失性函数实现取得工作表名称的实例。虽然易失性函数在实际应用中非常有用，但如果大量使用易失性函数，则会因重算工作量大而影响表格的运行速度 第15章　数组公式入门 技巧155　理解数组 155-1 数组概念和分类 数组（array繁体版称作阵列），是由文本、数值、日期、逻辑、错误值、等元素组成的集合。它是一个以行和列来确定位置、以行数和列数作为高度和宽度的数据矩形，因此，数组中不能存在长度不等的行、列的出现。在ＷＸＣＥＬ中，根据构成元素的不同，可以把数组，分为常量数和单元格区域数组 　常量数组可以包含数字、文本、逻辑值、错误值等，而且可以同时包含不同的数据类型，它用{　}将构成数组的常量括起来，各个元素之间分别用分号[；]和逗号[，]来间隔行和列。单元格区域数组则是通过一组连续的单元格区域进行引用而得到的数组 　例：在数组公式{Ａ１：Ｂ４}是一个4行2列的单元格区域数组，而{1，2，”你好”；true，#N/A，”我爱ＥＸＣＥＬ！”}则是一个2行3列的常量数组 155-2 数组的维数和尺寸 　数组还可以分为一维和二维数组，一维数组可以存在于1行[或1列]范围内，二维数组则存在于一个矩形范围内，在实际应用中，由于ＥＸＣＥＬ不支持显示三维数组，所以用户在一般情况下都是使用一维和二维数组进行运算，然而ＥＸＣＥＬ完全支持三维及多维数组的计算[相关内容参阅第25章多维引用] 　在一维数组中，根据数组的方向不同通常又可以分为垂直数组[行数组]或水平数组[列数组]。其中垂直数组用半角分号间隔，水平数组用半角逗号间隔 例：{1；2；3；4；5}、ＲＯＷ（1；10）属于行数组；{“A”,”B”,”C”,”D”}、（1,2,3,4,5）属于列数组，常量数组只具有行、列[水平、垂直]两个方向，因此只能是一维或二维的。另外，数组的行数和列数的多少被称之为数组尺寸的大小。在实际数组运算中，用户必须注意参与运算的数组之间的尺寸关系 155-3　内存数组 数组通过数组公式运算后所生成的新数组，通常称为‘内存数组’，它是在内在中提供给其它函数公式进行再次运算的一类特殊数组，内存数组与普通数组一样，也存在一维或二维形式，内存数组在实际应用中常常被广泛使用 例1：用户计算1~100的自然数求和 {＝sum(row(1：100))},其中ＲＯＷ（1：100）生成了内存数组{1；2；3；4；~~~89；99；100}＝5050 例２：求两个数组{1，2}和{10；20；30}相乘后的总和 {＝(sum{1，2}*{10；20；30}),其中两个一维数组运算结果生成一个新的3行2列数组{10，20；20，40；30，60}，该数组只存在于内存中，成为sum函数的参数，此公式最后结果为180 例3：下面的公式将下列不连续的单元格引用Ａ１：Ａ１０、Ｃ１：Ｆ１、Ｆ１：Ｆ１0组合成一个10行3列的内存数组， ＝choose({1，2，3}，Ａ１：Ａ１０，Ｃ１：Ｃ１０，Ｆ１：Ｆ１０)公式根据常量数组{1，2，3}，分别将3个区域进行组合得到新数组： {1，10，100；2，10，100；3，10，100；4，10，100；5，10，100；6，10，100；7，10，100；8，10，100；9，10，100；10，10，100；} 注意：在生成内存数组时，一定在注意数组的方向 1、 如果是同方向的一维数组时行运算，就要求数组的尺寸必须相同，否则运算结果中会包含#Ｎ/Ａ错误值。 2、 如果不同方向的一维数组进行运算，如Ｍ行数组与Ｎ列数组进行运算，结果将生成Ｍ*Ｎ的矩阵 3、 如果是一维数组（如5行1列数组）与二维数组（5行3列数组）之间进行运算，则要求在一维数组方向的尺寸必有相同，否则运算结果会包含#Ｎ/Ａ错误值，运算结果为二维数组（5行3列数组） 下面就上述注意事项介绍几种常用的方法检测公式生成的数组是否是内存数组，首先需要为数组公式定义名称如（data） 方法一：在任意单元格中输入＝data,再按Ｆ９键，查看结果是否生成用{}外套的数据，如＝{“张三”；”李四”}、＝{1；2；3；4；5}等 方法二：使用counta（data,）进行计数统计，通常情况下结果值大于１（当然只有1个元素的数组除外） 方法三：使用index(data,k)等方法检查（Ｋ为自然数），看公式结果是否不正常。 对于方法２和方法３用户还可以通过公式求值的方法（请参阅技巧１４５）查看公式运算的结果，如果生成{　}形式的数据，则表明原公式生成的是内存数组。 技巧156　理解多重计算及数组公式 如：在Ａ１：Ａ５中分别输入-1、0、2、142、-33这５个数字，求所有正数之和 方法一：输入＝sumproduct((a1:a5>0)*a1:a5)按enter键结束。 方法二：输入＝sum((a1:A5>0)*A1:a5)按下ctrl+shift+enter组合键 以上两个方法都得到正确的结果144，但这两个公式到底有什么不同之处呢?这里有‘多重计算’和‘数组公式’两个概念。 156-1　多重计算 例中（a1:a5>0）*a1:a5相当于执行了a1>0,a2>0,a3>0,A4>0,A5>0的５个比较运算，再将产生的逻辑值分别乘以Ａ１~Ａ５的值，这种过程就是多重计算： 在excel帮助中关于数组公式的说明是‘对一组或多组值执行多重计算，并返回一个或多个结果，数组公式括于大括号中{　}’。按ctrl+shift+enter可以输入数组公式，但并未明确的定义执行多重计算就是数组公式，执行单个计算就不算数组公式，事实上也并如此。 　比如输入＝A1并按ctrl+shift+enter组合键结束也会得到{＝A１}形式的公式，但它只执行了单个计算。而上述SUMPRODUCT公式并未按ctrl+shift+enter组合键，也没有将公式内容括于大括号中，但它执行的却是多重计算。 156-2　数组公式 　为了便于统一理解，不管公式是否执行多重计算，只要是输入公式时以按下ctrl+shift+enter组合键结束，就称之为‘数组公式’，即‘数组公式’只是一个名称代号，用以区分‘普通公式’输入后不按三键结果的操作。按下ctrl+shift+enter组合键的意义在于给EXCEL下达执行多重计算的命令。 　本书所讲数组公式，要求公式输入后按下ctrl+shift+enter组合结束，并用带{　}的公式表示： {=SUM(A1:A5>0*A1:A5)} 技巧157　多单元格数组公式 选择Ａ１：Ａ２单元，然后在编辑栏输入＝{1，2，”你好”；TRUE，#N/A，”我爱EXCEL!”}并按下ctrl+shift+enter组合键结束操作，则能将它在这个单元格区域所示的对应位置显示出来： 这种产生多个计算结果并在多个单元格显示出来的单一数组公式，称为‘多单元格数组公式’ 如：对数组{1,4,5,10,7,6}求第2、第3小的值，在工作表中显示结果 解：选中A1:Ｂ１单元格区域，输入=SMALL({1,4,5,10,7,6},{2,3})并按下ctrl+shift+enter组合键结束，则在A1显示4，在B2中显示5 多单元格数组公式需要用与结果行列数一致的单元格区域才能显示全部结果，在上例中如果选中Ａ１单元格输入上数组公式则结果只显示１，而如果先选中Ａ１：Ｂ１单元格区域，再Ａ１和Ｂ１只显示 使用多单元格组公式的优势在于： 1、 它能够保证在同一个范围内的公式具有同一性，创建此类公式后，公式所在的任何单元格都不能被单独编辑，就连关闭也不能。 2、 它能够在一个较大范围内快速生成大量具有某种规律的数据 例如：使用{＝row(1:10)}在工作表中快速生成连续整数，此公式利用ROW函数，生成一个拥有整数元素的数组，并填充到相应连续单元格中 　 技巧158　数组的转置和变换 158-1 数组的转置 数组的转置就是将数组的行列进行互换，形成一个新数组，即把数组在平面上旋转了90度，数组的转置要借助transpose函数来完成，比如对一个3列2行的数组{1，2，3；4，5，6}进行转置，可以使用公式：＝transpose（ {1，2，3；4，5，6}） 除了常量数组，单元格区域数组的转置也可用transpose函数实现。 注意：包含了transpose函数的数组公式是多单元格数组公式，使用前需要先选中与目标数组的行列数一致的单元格区域，才能实现完整的转置。 158-2 数组元素的位置变换 如果因为各种原因不能改变表格结构,但又需要变换其中的数据集团时,可以用IF函数或者COOSE函数来重新计算新构建区域。比如在表中的A2：C7单元格区域中存放着源数据，现在希望从源数据得到一个新表格：‘第一列为姓名，第二列为学号’：选择E2：E7单元格，在编辑栏中输入公式：＝if({true,false},B2:B7,A2:A7),输入完成后按ctrl+shift+enter组合键完成数组公式的输入，这时公式简化为：{if=({1,2},B2:B7,A2:A7)} 思路解析： 当IF第1个参数为TRUE时返回第2参数，为false时返回第3个参数，所以{1，0}的1对应的是第2个参数B2：B7；0所对应的是第3个参数A2：A7。{1，0}是一个两列的数组，因此公式返回了两列结果} 当然还可以使用＝choose({1,2},B2:B7,A2:A7)来表达表1的效果，用＝choose({1,2，3},B2:B7,c2:c7,A2:A7)来表达表2的效果。相比之下choose能更容易变换各列之间的位置 技巧159 为何不能用and、or替代*、+ 在技巧151中提到过*、+可以与逻辑判断函数and和or相互替换，在数组公式中*、+能够替换and、or函数，但是反之则不可，因为and和or函数返回的是单值true或false，而如果数组公式需要执行多重计算，单值不能形成数组公式各参数间的一一对应关系。例如，要统计一个表格中政治成绩为70~79分的人，即统计大于70且小于80的人。判断的条件是一个逻辑关系比如使用公式：{＝sum(and(c3:C7>=70,c3:C7<80)*1)},则返回结果是0，这是因为公式中C3:C7>=70返回{true,false;true;true;true},c3:C7<80返回{true;true;false;true;false},然而，and({true;false;true;true;true},{true;true;false;true;false})返回false 注意：and函数当所有参数都为true时才返回true;所以下一步计算结果为=sum(false*1)=sum(0*1)=0 如果使用公式：{＝sum((c3:C7>=70)*(c3:C7<80))} 则公式返回结果为：(c3:C7>=70)*(c3:C7<80) ＝{true;false;true;true;true;}*{true;true;fa;se;true;fa;se} ＝{1；0；0；1}*{1；1；；0；1；0} ={1;0;0;1;0} 数组相乘的过程如下： 所以公式计算结果为： ＝SUM({1;0;0;1;0})=2 +号运算符与OR函数之间的关系也是同样的道理，此处就不再赘述 第5步计算 第4步计算 第3步计算 第2步计算 第1步计算