用Excel编程简介.doc

用Excel编程简介用Excel编程是一个新的提法。所谓用Excel编程，不是用程序设计语言编写，而是充分利用Excel的功能，在Excel的工作表中通过定义名称、输入计算公式、插入工作表函数、插入图表等操作，完成一系列信息处理（包括工程计算）。程序一般要做三件事：第一，得到数据；第二，处理数据；第三，输出数据。这种程序的输入、输出界面均在Excel的工作表界面。在工作表的单元格中输入数据后，这些数据就被存储在这些单元格中，什么地方需要这些数据就可直接引用。通过一系列的处理，得到用户所需要的结果，直接输出到工作表中。输入、输出的样式可以随意设置。这种程序的运行是立即执行方式。如果计算量不是特别大，当用户输入完要处理的数据后，立即显示结果。在本书的第三章开始,将陆续给出一些实用的数据处理程序。通过实例，加深对用Excel编程的理解，掌握一些编程技巧，提高编程水平。这里仅给出一个简单的例子。例1-3 编写一个计算任意三角形(三边长已知)面积的程序。计算公式为P =（a + b + c）/2解：打开一个新工作簿，以“求三角形面积”为文件名存盘。在工作表Sheet1中输入文本（粗体字）和数据（斜体字），见图1-5。然后操作：命名：选中E5单元格，插入名称定义，在“定义名称”对话框定义下列名称。定义完一个名称别忘记了点击“添加”。“名称”栏输入内容“引用位置”栏输入内容P=SUM(B5:D5)/2面积=SQRT(P*(P-B5)*(P-C5)*(P-D5)公式清单：E5：=面积 显示结果： 见图1-5说明：本程序主体在框线内，使用时，改变a、b、c的值，立即显示面积的结果。其他什么也不必做。另外，在本工作簿的工作表Sheet1中的任何位置按行逐个单元格依次输入a、b、c的值和公式“=面积”，立即显示计算结果(见图1-5的行6、行7)。图1-5 例1-3的计算结果用于公式计算的有关命名及名称的应用用公式计算的有关命名有单元格命名、单元格区域命名、常量命名和公式命名。在用公式计算时,可以直接引用这些名称，使得公式看起来更直观、更容易理解，引用也更方便。1.6.1 命名规则名称必须以字母或下划线开头，后面可以使用除空格和连字符以外的任何字符。但要注意避开使用单元格引用本身的名称，如B12、AY8等。名称使用英文字母时大小写等同。具体命名规则如下：(1)名称的第一个字符必须是字母(包括汉字)或下划线。名称中的字符可以是字母、数字、句号和下划线。(2)名称不能与单元格引用相同，例如 Z$100 、 R1C1、A8、B12。(3)名称中不能有空格。可以用下划线和句号作单词分隔符，例如： Sales_Tax 或 First.Quarter。(4)名称的长度不得超过255个字符，但实际上一般以不超过15个字符为好。(5)名称可以包含大、小写字符。Microsoft Excel 在名称中不区分大小写。例如，如果已经创建了名称 Sales，接着又在同一工作簿中创建了名称 SALES，则第二个名称将替换第一个。1.6.2 命名及名称的使用在工作表中定义名称有两种方法：使用菜单命令(插入名称定义)或使用“公式编辑”栏左边的“名称”框。使用菜单命令的优点是：不必关闭定义“名称”对话框就一次可以定义多个名称。另外，常数和公式的命名只能使用菜单命名法。1使用菜单命令定义名称利用菜单命令定义名称的步骤如下：(1)选定要命名的单元格或单元格区域。(2)操作：插入名称定义，显示定义名称对话框(技巧：按Ctrl+F3组合键可直接显示定义名称对话框)。(3)在“名称”框中输入你想要的名称，然后点击“确定”，定义该名称完毕。(4)若定义多个名称，可以在定义“名称”对话框打开时，在“名称”框中输入一个名称，在“引用位置”框中输入该名称的引用(单元格或单元格区域)，或用鼠标选定单元格(或单元格区域)的方法输入该名称的引用，单击“添加”按钮再定义下一个名称。2使用“名称”框定义名称“名称”框出现在“公式编辑”栏的左边(见图1-1)，常规状态下显示活动单元格的引用名称，或者如果当前选中的单元格(包括单元格区域)已经命名，则显示命名的名称。利用“名称”框定义名称的步骤如下：(1)选定要命名的单元格或单元格区域。(2)单击“名称”框则活动单元格的引用名称突出显示在框内。(3)为选定要命名的单元格或单元格区域输入一个名称。(4)按Enter完成名称定义。3以工作表中的文字创建名称可以用选定区域的首行(首行包括在选定区域之内，下同)或末行或最左列或最右列中的文本指定名称，用这种方法有时一次可定义多个名称。例如在工作表Sheet1中有如下内容：ABCDE1IJKN总和22321219选定A1:E2区域，插入名称指定，显示一个小的指定名称对话框，此时首行已经自动被选中，点击“确定”即可。这一次定义了5个名称。4常量或公式的命名如果为经常用到的常量或公式建立名称，就能使工作表的可读性增强，因而更容易理解。Excel可以命名任何常量或公式，然后就可以在单元格或公式中使用这些名称。而这个常量或公式就没有必要放在单元格里。命名常量或公式与命名单元格、单元格区域有所不同，后者名称是引用工作表中的一个位置，而前者名称是引用工作表中不存在的公式或值。要命名常量或公式，请按以下步骤操作：(1)用菜单命令：插入名称定义，显示“定义名称”对话框。(2)在“名称”框中输入名称。(3)在“引用位置”框中输入常量数值或公式。(4)然后点击“确定”，定义该名称完毕。5工作簿级名称与工作表级名称除非特别指明，使用定义名称对话框或“名称”框定义的名称都是工作簿级名称，或称之为全局名称，可用于工作簿中的所有工作表，如在工作表Sheet1中的A8单元格命名为m，即名称m的引用位置为“=Sheet1!$A$8”,而当前的活动工作表为Sheet3，在这里的任何单元格中使用名称m都代表工作表Sheet1中的A8单元格。如果想要名称m在这里仅代表Sheet3的A8单元格，这就需要定义工作表级名称。方法是：在命名时，名称前加工作表名和感叹号“!”。如定义Sheet3中A8单元格名称为m，其做法是：选定Sheet3中的A8单元格，在“名称”框中输入Sheet3!m，按Enter完成名称定义。在工作表中使用本工作表的表级名称时，不需要指定该名称所属的工作表，可以直接使用名称，而在工作表中使用其他工作表的表级名称时，一定要指定该名称所属的工作表，如在Sheet4中一个单元格使用Sheet3中名称m，应输入：Sheet3!m。另外要注意，表级名称优先于工作簿级名称。所以在定义了同名的表级名称和工作簿级名称时，使用的是表级名称。当打开定义名称对话框时，名称列表显示的是活动工作表的表级名称和与本工作表表级名称不同名的工作簿级名称，同名的工作簿级名称不显示。恰当地定义工作簿级名称和工作表级名称，可以使你更灵活地运用Excel。6应用名称定义名称后，名称不会自动出现在工作表的现有公式中，如果是在定义名称之前创建的公式，就需要将名称应用到这些公式中。方法是：插入名称应用，出现应用名称对话框，用鼠标点击每个要应用的名称，然后点击“确定”。7粘贴名称在公式中可以输入名称，也可用下面的操作粘贴名称：将插入点移动到名称想要出现的位置，然后操作：插入名称粘贴选择想要粘贴的名称确定。或用“名称”框粘贴名称，其方法是：单击“名称”框右端的箭头并选择想要的名称。8命名名称的应用为单元格、单元格区域、常量或公式命名以后，这些将保存在工作簿中，可以随时调用。例如在进行化学、化工方面的计算时，常用到化合物的分子量，可以将常用的元素的原子量以命名常量的方式保存在工作簿中。用菜单命令：插入名称定义，显示定义名称对话框；在“名称”框中输入H_和在引用位置框中输入=1.008；点击“添加”，再在“名称”框中输入C_和在引用位置框中输入=12.011；点击“确定”。如在工作表A8单元格计算C2H4的分子量，输入公式=C_*2+H_*4,按Enter键后显示28.054.对于命名公式，例如某计算中常用到一个公式logP=A-B/(C +T)，在工作表Sheet1中的B2、C2、D2、E2单元格依次输入6.93053、1115.5、231、70(A、B、C、T的值)。如果要在F2单元格计算P的值，此时可命名公式。选中F2单元格，插入名称定义名称输入ATI引用位置输入公式为=10(B2-C2/(D2+E2)，点击“确定”。命名完毕后，在F2单元格输入=ATI，按Enter键，显示1677.065。这时，如果在A8、B8、C8、D8单元格中的数值依次为6.96546、1296.4、221、110，在E8单元格输入=ATI后显示1119.035，此时，如果把光标放在E8单元格，再查看公式ATI的内容(方法是：插入名称定义，出现“定义名称”对话框，用鼠标点击ATI)则不是原来输入的内容，而是：=10(Sheet1!A8-Sheet1!B8/(Sheet1!C8+Sheet1!D8)注意：这里的命名公式中的“引用”为“相对引用”，不管在这个工作表中的任何地方输入公式=ATI，都是用紧靠公式所在单元格的左边的4个单元格中的数据按给定的公式计算。如果这里采用“绝对引用”就得不到这个结果。这一技巧也相当于定义了一个无需输入参数的函数，这个函数将按规则自己选定参数进行计算。另外，上面公式中的工作表名称及其后面的感叹号Sheet1！是计算机自动加入的。再如，给工作表Sheet1中的D2单元格命名为“总和”，当你再进入“定义名称”对话框查看这个名称时，在“引用位置”栏显示=Sheet1!$D$2,这里的Sheet1！和绝对引用记号“$”也是计算机自动加入的。在默认情况下，命名对单元格的引用均为绝对引用。若变为相对引用则需要在“引用位置”栏进行修改。函数这里所说的函数亦称为工作表函数，简称函数。灵活使用函数，是发挥Excel强大功能的关键所在。Excel的工作簿函数有300多个，几乎应有尽有。这使得一般往往需要用程序设计语言编写复杂程序才能实现的计算，采用Excel的函数功能则能够非常轻松地实现。函数是一些事先定义好的公式，或者说是事先编好的程序，而使用者不必关心这些程序的代码(实际上是看不到这些代码的)。函数使用一些称为参数的特定数值按特定的顺序或结构进行计算。例如，SUM() 函数对单元格或单元格区域进行加法运算；AVERAGE()函数先对单元格或单元格区域进行加法运算，再将其结果除以参与加法运算的数据的个数。参数：参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其他函数。1函数的语法函数的语法以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号(=)。在创建包含函数的公式时，公式选向板将提供相关的帮助。如下面是AVERAGE()函数的语法：AVERAGE(number1,number2, .)number1, number2, .要计算平均值的130 个参数。参数说明： (1)参数可以是数字，或者是涉及数字的名称、数组或引用。(2)如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。 2函数的分类函数按其特性分为多种类别，插入函数时，可以依照函数类别来查找所需要的函数。函数类别分为：财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息和工程等。3函数的嵌套在某些情况下,利用函数作为其他函数的参数,此种情况称为“函数的嵌套”。利用函数生成的值作为另一函数的参数。如用IF()函数就经常遇到函数的嵌套。下面的公式使用了嵌套的 AVERAGE 函数，并将结果与 30 相比较。=IF(AVAGE(D2:D5)30,SUM(E2:E5),0)(1)有效的返回值当嵌套