1、www.ExcelP Excel 技术 技巧 资讯 资源 2007 年第1 期 ( 总第1 期) ( 2007 年10 月测试版) Excel 一本免费的电子杂志 内容为Excel 技术 帮助打好Excel 理论功底 汇集Excel 技巧 反映Excel 发展动态 注重Excel 实践与应用 热衷于VBA 知识的讲解 偏好Excel 应用开发 拓展Excel 协同应用 涉及面广 知识量丰富 适用于Excel 爱好者 也适用于Excel 用户 为个人学习资料归纳整理 与使用Excel 者分享 欢迎交流 共同提高 ? Excel 资讯：近期关于Excel 的一些信息 ? Excel 技巧：
2、一些Excel 和VBA 应用技巧 ? Excel 菜单和工具栏操作(主要针对Excel97-2003 版本)：定制Excel 的菜单和工具栏 ? Excel 2007 基础：全面讲解Excel 2007 界面及相关变化，尽快了解Excel 2007 卷 首 语 对Excel 的痴迷由来已久，但先前的接触仅是皮毛而已，对其也只是应用一些基本的功能。虽说知道Excel 功能强大，无奈在工作上还是想不出其进一步应用的地方。这一方面反应了我对Excel的了解甚少，另一方面在工作中也没想到利用手中强大的工具来提高工作效率。 真正认识到Excel 的强大是在加入 Excel 社区论坛 开始的，看到 论坛
3、里面的Excel高手令人称叹的作品，他们对论坛贴子游刃有余的解答，在让人佩服的同时，也激起了自已进一步深入了解、学习以至熟练应用 Excel 的热情。于是乎，自已开始了 对Excel 彻底深入的学习。随着学习持续和众多Excel 爱好者的鼓励，自已逐渐感觉到Excel 水平正日渐提高，也逐步将其应用到了工作中，为提高工作效率和水平提供了强有力的支持。我逐渐认识到， 对一种技术了解得越深入，就越能将其独特的 优势加以运用，自然而然地就想起与工作相结合，从而提升工作效率，并且在运用技 术的同时，也促进了技术的进一步提升，从而更好地服务工作。 在这里，要感谢ExcelHome 技术论坛。ExcelH
4、ome 上不仅有很多Excel 技术能手和专家，而且他们都非常热心，不 遗余力地帮助遇到问题的人，也盈造了一个很好的学习氛围和环境，他们帮助了我在Excel 技术上的成长。 先将对 Excel 的情结暂且 搁下一会儿，谈谈 Excel 本身吧。确切地说，这里的Excel 是指Microsoft Office 家族中的一个软件程序，她为用户提供了强大的数据分析和处理功能，同时利用自身的定制和扩展能力，可以开发出非常有用的应用程序来，而这些程序又充分利用了Excel 自身的优势，因此非常强大。可以说，Excel 工作表中的行列就是一个程序接口，用户在其中输入数据，然后利用Excel 提供的内置命令
5、 对这些数据进行处理，最后输出用户需要的结果。这些操作都无须用户编写任何代码，用户只需按恰当的方式输入数据，并使用合适的命令要求Excel 进行处理，而Excel 提供的这些命令又是相当丰富的。再看看Excel的另一个特点，就是强大的公式处理能力， 这归功于其提供的功能强大的函数，按编程方式思考的公式与函数，太让人着迷了！Excel 的图表功能也引人入胜！还要值得一提的是Excel 中有一种宿主 语言VBA，利用VBA，可以简化重复的工作任务，定制和扩展我们的Excel 应用程序，使得Excel 按照用户实际要求来工作。总之，Excel 是强大的， 让我们在完美Excel 中逐步认识、学习并运
6、用。 Excel 是完美的，而完美的工具只有在会使用它的人手中才能尽显其完美，这也是完美Excel开设的目的。在完美Excel中， 记录下Excel 的点点滴滴，归纳Excel 的方方面面，从而帮助提高Excel 的使用能力，使得Excel 更加完美。 当然，在这里要说明的是，完美Excel是个人在学习Excel 过程中，对Excel知识的整理、归纳和总结，一来是提高自身的Excel 水平；二来可以将所学的知识存档，以方便以后查阅；而更为重要的是，将自已的学习收获奉献出来，与 Excel爱好者共同分享，使得大家能够彼此交流提高。因而，文章中的 错误可能在所难免，希望大家及时予以指正。 另外，本
7、期为测试版，有什么好的意见或建议，也请 提出，多多指教！ 2007 年10 月 www.ExcelP Excel 技术 技巧 资讯 资源 Excel 2007 中的计算问题 .- 1 - 使用Office 2007 情况的调查 .- 1 - Microsoft 发 布Excel 2007 XLL SDK- 2 - Excel 条件格式数据条使用反 馈- 2 - Excel 计算结果出错的情形 - 3 - Excel 基本操作技巧- 4 - 在Excel 工作表中创建关 联列表.- 5 - 去掉Excel 工作表中的无关 对象.- 6 - 创建联合报表- 7 - Excel 日期和时间函数使用技
8、巧两 则- 8 - 使用Rand()函数生成随机数 - 9 - SUMPRODUCT 函数的使用 .- 9 - 创建分离的饼图- 10 - 选取文件夹目录 - 11 - 找出两个单元格区域之间的不同数据- 12 - 安排宏运行的时间- 14 - 一个文本框控件示例- 15 - 在工作表中动态添加组合框- 15 - Excel 菜单和工具栏概要 - 16 - 在Excel 中创建新菜单.- 19 - 禁用菜单和工具栏- 20 - Excel 2007 概览23 ExcelHome 网站 .34 本期杂志资源下载.34 2007 年第1 期 (总第1 期) ( 2007 年10 月测试版) www
9、.ExcelP Excel 技术 技巧 资讯 资源 Excel Excel Excel Excel 2007 Excel Excel Excel VBA Excel www.ExcelP Excel 技术 技巧 资讯 资源 Excel 资讯 - 1 - www.ExcelP Excel 技术 技巧 资讯 资源 Excel 2007 9 月初，暴出了 Excel 2007(和 Excel Services 2007)中一个关于 计算的问题， 该问题涉及到结果约为65535 的计算。 例如，在Excel 2007 的单元格中输入： =77.1*850 =5.1*12850 =10.2*64
10、25 =20.4*3212.5 等公式时，得到的结果为100000。 但并不是所有结果为 65535 的公式结果都有误，例如： =65535*1 =16383.75*4 等公式的结果为65535。 上述这种情况只在Excel 2007 中出现，在其它版本中并未发现。 据Excel 团队博客中的日志介 绍，出 现该问题是在 Office 2007 时间 框架中对 Excel 计算逻辑变化所引入的。确切地说， Excel 2007在 12 种非常特别的情形下不正确地显示计算的结果(详见下面的介绍) 。这里的关键是，问题的事实不在计算本身(存储在 Excel 内存中的计算结果是正确的)，只是显示在工
11、作表中的结果不正确。例如，公式： =850*77.1 在工作表中显示不正确的值，但是如果将结果乘以2，则会得到正确的答案131070。 可以在工作表单元格 A1 中输入公式=850*77.1，然后在单元格 A1 中输入公式=A1*2，A2 中将显示正确的答案131070。 那么是什么导致了值的显示问题呢？Excel 2007可以存储9.214*1018个不同的浮点 数 ， 引 起 这 样 的 问 题 是 在65534.99999999995 和 65535 之间的6 个浮点数以及在65535.99999999995 和65536 这间的6 个浮点数。事实上不能够直接输入这些数字到Excel 中
12、( 因为Excel 将在输入完15 位数字后四舍五入)，任何返回这些结果之一的计算将显示上述的问题(如果计算的结果显示在单元格中的话)。 对于上述问题，Excel 开发团队正在进行修复，并将进行广泛的测试，以避免引入其它的问题，特别是关于计算方面的问题。 Office 2007 下面是最近CDW 对商业机构、政府和教育机构是否更新到 Office 2007 和何时更新到Office 2007 的情况 调查统计 。 图1-1 图1-2 以 上 图 表 摘 自www.microsoft-。 Excel 资讯 - 2 -www.ExcelP Excel 技术 技巧 资讯 资源 Microsoft E
13、xcel 2007 XLL SDK 自从去年年底发布Office 2007 以来，为一些Excel 开发者所期待的 XLL SDK(即Microsoft Office Excel 2007 XLL Software Development Kit)终于发布了。最终发布的2007 版SDK 包括API 文件、框架和代码示例以及详细的文档。 Microsoft Office Excel 2007 XLL SDK 下载地址： h t t p : / / www. mi c r o s o f t . c o m/ d o wn l oa d s / d e t a i l s . a s p x ?
14、 f a mi l y i d = 5 2 7 2 e 1 d 1 -9 3 a b - 4 b d 4 - a f 1 8 - c b 6 b b 4 8 7 e 1 c 4 &d i s p l a yl a n g = e n 在线文档阅读：http:/ 从SDK 文档中摘录的部分内容： Microsoft Office Excel 2007 XLL SDK 被设计来帮助理解与使用Excel C API 为Microsoft Office Excel 2007 创建DLL 加载项相关的概念和技术。C API 能使DLLs 与Excel 2007 紧密整合，并利用Excel 内部的功能。这
15、些DLL 加载项 被称作XLLs ，通常使用 .xll 作为文件扩展名。 编写XLLs 以及使用C API 的主要原因在于可以创建高效的工作表函数。虽然XLL 函数经常被称作用户定义函数，但是为了获取对编写XLLs 所需知识和技能的理解而在时间上的投入，使得该技术对大多数用户来说都是不切实际的。尽管如此，高效函数的应用程序(并且，在Excel 2007 中，对强大服务器资源编写多线程接口的能力)使得该技术成为Excel 扩展性的一个非常重要的部分。XLLs 的性能进一步扩充了Excel 2007 在处理新数据 类型方面的能力，并且最重要的是，支持多线程。 Excel 2007 XLL SDK
16、包括一个框架 库，用来加快XLLs 的编写，同 时还有三个示例工程。Excel 近期，在Excel 开发团队的博客上，由开发组成员Scott Ruble 发布了一篇日志，征求用户对条件格式功能中图形化数据条的反馈意见。 具体地址见： http:/ 在Excel 2007 中，运用条件格式的“ 数据条”规则后，格式如下： 图1-3 在将来的Excel 版本中，开发人员打算对数据条的显示进行一些修改，并希望得到用户的反馈意见。 同时，在该篇日志中，列出了四种不同的情况，用户可以建议数据条的显示方式。 Excel 技术技巧 - 3 - www.ExcelP Excel 技术 技巧 资讯 资源 这里是
17、2007 年9 月1 日收到CPearson. com 的Excel 新闻资讯内容。Excel 的爱好者可以到CPearson. com 中订阅CP Excel Newslet t er ，CPearson. com 将每周为您发送一份关于Excel 知识的技术文章。( 注：CPearson 是Chip Pearson 的简写，Chip Pearson 是一位知名的Excel 专家，在其网站上有很多实用的Excel 技术文章) Excel 下面来看看Excel 可能会返回 错误计算结果的环境。几乎所有情况下，错误可能基于下列两种情形：单元格中显示的值和单元格中实际的值不同、对存储在计算机中数值
18、精度的限制。其中第二种限制并不只是在Excel 或Microsoft 软 件中，它是在任何系统下任何软件产品中都存在。 Excel 总是尽可能在最大精度存 储和计算数字，而不管在输入和输出单元格中设置的显示格式。这意味着Excel 使用15 位数字，?字的总数包括小数点左右边的数字，即使在输入或输出单元格中仅显示两个小数位。例如，在单元格区域A1:A8 中，输入公式=1/8，然后在单元格A9 中输入公式=SUM(A1:A8) ，对单元格区域A1:A9 设置单元格格式为显示3 位小数，则单元格区域A1:A8 中显示0.125， 单元格A9 中显示1.000。结果是正确的并且我 们也接受这个结果。
19、现在，设置单元格区域A1:A9中的格式为两位小数，则单元格区域A1:A8 中将显示的值为0.13，单元格A9 中显示1.00，但是，0.13 乘以8 将是1.04 而不是1.00。 这是Excel 出错了吗？这是一个 Bug 吗？ 不，Excel 没有错，也没有Bug。当 Excel计算公式=SUM(A1:A8)时，它使用单元格区域A1:A8 中的实际值进行计算而不是显示值。不管单元格中格式的设置如何，Excel 都使用其潜在的值，即所有15 位数字。在本例中， Excel使用值0.125 而不是0.13 来进行计算。 也可以强制Excel 使用所 显示的值而不是实际值进行计算，但并不推荐这样
20、做。可以通过在“工具”菜单的“选项”对话框中的“计算”选项卡上的 “将精度设 置为所显示的精度”来控制计算的设置。可以在启用此项设置后，重新试验上面所讲的示例，并试着将单元格格式分别设置为显示三位小数、二位小数和一位小数，将得到的结果分别为1.000、 1.04 和0.8。即会得到不同的值，但所有这些实质上是相同的值1/8 的求和，这三个结 果值正确码？答案是肯定的。但这样会导致混乱和不正确的计算。 如果需要考虑舍入误差，则可以在数组公式中使用ROUND 函数。ROUND 函数将数字舍入为指定的小数数。例如，将公式=SUM(ROUND(A1:A8,2)作为数组公式输入(即输入完公式后按CTRL
21、+SHIFT+ENTER 组合键)，此时在单元格区域A1:A8 中的值为两位小数且SUM 也是这些舍入的数字之和，因此，如果A1:A8 中包含=1/8，Excel 会将这些数字(0.125)四舍五入 为0.13，然后求和， 结果为1.04 而不是1.00。注意，在传递这些值到SUM 函数之前就将每个值四舍五入。这意味着数组公式=SUM(ROUND(A1:A8,2)与公式=ROUND(SUM(A1:A8),2)不同，第一个公式在求和前对单元格区域A1:A8中的值进行四舍五入，而第二个公式中的值则求和后再对结果进行四舍五入。可以为四舍五入使用的函数包括： u ROUNDUP u ROUNDDOWN
22、 u INT u TRUNC u MROUND 在Excel 的在线帮助中有 这些函数的文档说明。 发生明显错误的另一个原因是由于计算机中存储数值的内在限制。与几乎所有其它的软件程序一样，Excel 使用称作双精度浮点型数据格式。这种格式是一种工业标准，并不限于Excel 或其它Microsoft 产品，允许15 位数字精度。术语精度指可以精确呈现在小数点左右两边的数字数。如果小数点左边和右边的数字总数超过15，Excel 将在15 时舍入。例如，在单元格A1 中输入数字123456789012345，Excel 技术技巧 - 4 -www.ExcelP Excel 技术 技巧 资讯 资源 然
23、后在单元格A2 中输入=A1+1，由于单元格A1 中有15 个数字，单元格 A2 中将准确地显示123456789012346。现在将A1 改为1234567890123456，这个数值有16 位，超过了Excel 的15 位的限制，因此，Excel 将该数值舍入为1234567890123450，最后一个数字0 是舍入的结果，单元格A2 也被四舍五入，因此，A1+1 为1234567890123450，这显然是错误的，因为该数值与A1 中的数值相同。在数学上，A1+1 显然不等于A1，从数学的角度讲是错的。但这不是一个Bug 。这种舍入行为是计算机中的一种固有的限制，并且在处理大数值或带有许
24、多小数位时需要考虑这种情形。 这种限制也决定了可以在小数点左边使用的位数。例如，在单元格B1 中，输入123.456789012345，共有15 位数字，小数左边有3 位，右边有12 位。在单元格B2 中输入=B1+0.000000000001，即在小数点右边放置11 个0 和1 个1，这仍然在15 个数字的限制内，因此结果是正确的123.456789012346。现在，在单元格B3 中输入=B1+0.0000000000001，小数点右边有12 个0 和1 个1。在数学上讲，这应该是123.4567890123451，然而，这超过了Excel的15 个数字的限制(它是16 位，小数点左边是3
25、 位，右边是13 位) ，因此，Excel 在小数点右边放置第12 位的舍入结果。 如果需要存储大于15 位的数字，但不需要对这些数字进行计算(例如电话号码或信用卡号)，可以告 诉Excel 不要将这些数字当作数字对待而是作为文本。此时， Excel 显示像与输入的数字位数相同的数字。然而，如果试图使用这些数值进行计算则会进行四舍五入。要将输入的数值作为文本，可以在输入数字前格式化单元格为文本格式，或者可以在数值前输入一个单引号()。这个单引号不会显示在单元格中，但会出现在公式栏中。 值得注意的是，即使Excel 受15 位数字精度的限制，但可以处理大至10308(在小数点左边有308 位数字
26、)或小至10-308(在小数点右边有308 位)。然而，超过15 位精度的都会被四舍五入。例如，Excel 可以显示数值1025，但(1025)+1 不会被正确地计算，因 为它超过了15 位。它将在1015 时被四舍五入。Excel 在“(c)”后面跟一个空格，可以快速输入版权符号“?” ；在“(r)”后面跟一个空格，可以快速输入注册符号“?”。 有时，在粘贴数据时不需要原来的边框，则可以在粘贴时调出“选择性粘贴”对话框，选中“ 边框除外”前的单选按钮(如下图1-4 所示 )。 图1-4 输入函数名和左括号后，按Ctrl+A键，将弹出“ 函数参数”对话框，例如在某单元格中输入“=sum(”，然
27、后按Ctrl+A 键， 则弹出关于sum 函数的“函数参数”对话框。 输入函数名和左括号后，按Ctrl+Shift+A 键，将列出整个函数及其参数，例如在某单元格中输入“=sum(”，然后按Ctrl+Shift+A 键，该单元格将显示“=sum(number1,number2,.)”。 在输入公式时，按F4 键，会在相对或绝对引用之间转换，这样可以快速输入“$”符号。 Excel 技术技巧 - 5 - www.ExcelP Excel 技术 技巧 资讯 资源 将默认的矩形批注框改成其它形状。激活批注框为编辑状态，选择“绘图”工具栏“绘图”旁的箭头并选择“改变自选图形”，然后选择想要使用的形状(
28、如图1-5 所示)。 图1-5 要绘制椭圆的同时，按住Shift 键，则会绘制一个圆；同理，绘制方形时，按住 Shift键，则会绘制一个正方形。 为单元格加入提示以便于理解和使用。要实现这种功能，可以利用“数据有效性”对话框中的“ 输入信息” 选项 卡(如图1-6 所示)，选中单元格后，在该选项卡中输入相应的提示信息，那么每当用户选中该单元格时， 则会显示提示信息(如图1-7 所示)。 图1-6 图1-7 Excel 可以使用Excel 的“数据有效性”功能在工作表中创建关联列表。示例如下： 在一个空的工作表中输入一列数据，分别是“ 电器”、 “家具”、“日用品”，将该数据区域命名为“ 分类
29、”，作 为第一个下拉列表的序列数据源。如图1-8 所示。 图1-8 在另一列中，输入数据“电视”、“冰箱”、 “洗衣机” ，并将数据区域命名为“电器”，作 为“ 电器” 选项的列表，如图1-9所示。 图1-9 依次类推，分别输入作为“家具”和“日用品”选项的列表数据并 进行相应的命名，如图1-10 所示。 图1-10 开始设置相关联的列表，首先设置第一个列表。选中用于显示类别列表的单元格C2，选择菜单“ 数据”“有效性” ，在“数据有效性 ”对话框中，在允许下拉框中选择“序列”，在“ 来源” 框中输 入“= 类别” ，如图1-11 所示。 Excel 技术技巧 - 6 -www.ExcelP
30、Excel 技术 技巧 资讯 资源 图1-11 单击“确定 ”后，在单元格C2 中将产生一个下拉列表，如图1-12 所示。 图1-12 现在设置与第一个列表相关联的第二个列表。在单元格C4 中，选择菜单“数据”“有效性”，在“ 数据有效性 ”对话框中，在允许下拉框中选“序列”，在“来源”框中输入“=INDIRECT(C2)” ，如图1-13 所示。 图1-13 单击“确定 ”按钮完成关 联列表的设置，此时，在单元格C2 的下拉列表中选择某类别项，在单元格C4 下拉列表中将显示出相应的数据。如图1-14 和图1-15 所示。 图1-14 图1-15 Excel 有时，在网上复制表格或者接收到他人
31、传来的电子表格时，工作表中往往包含或隐藏着一些无关的对象，它们有时会影响电子表格的操作或外观。 下面是我在工作中收到的一个电子表格，上面有一些无关的对象，如图1-16 所示。 图1-16 ：红色圈圈里面的是一些无关的对象 当然，您可以一个一个地查找并选择后删除这些对象，但如何快速地去除它们呢？ 首先，选择“ 绘图”工具栏中的“选择对象”命令，如 图1-17 所示。 图1-17 ：“绘图” 工具栏中的 “选择对象”命令 然后，再工作表中相应区域画一个拖放鼠标，画出一个矩形框。此时，矩形框内的所有无关对象应该都被选中。如图1-18 所示。 Excel 技术技巧 - 7 - www.ExcelP E
32、xcel 技术 技巧 资讯 资源 图1-18 ：在激活“ 选择对象 ”命令后，在工作表中拖放鼠标画出一个矩形框，选中矩形框内的所有无关对象 最后，很简单，直接按“Delete”键，删除这些对象。 可以使用Excel 的“粘结图片链接”功能，完成由几个不同部分组成的复杂报表。 首先，创建表头，如图1-19 所示，使用Excel 绘图工具中的艺术 字及线条绘制表头。 图1-19 ：报表的表头 再创建报表的其它组成部分，如图1-20、图1-21 、图1-22 所示。 图1-20 图1-21 图1-22 现在，开始将这3 部分以图片粘贴的形式汇总到第1 部分的工作表中。 选择第2 部分的数据，并复制。 图1-23 ：选择数据并复制 在汇总工作表的相应单元格中，按住Shift键的同时单击“ 编辑” 菜单 ，选择“粘贴图片链接”，如图1-24 所示。 图1-24 ：按住Shift 键，此时编辑 菜单中的命令将不同 执行该命令，则在工作表中将出现第2 部分的图片，将图片拖放到合适的位置，如图1-25 所示。 图1-25 ：粘贴后的效果 对第3 部分和第4 部分重复上述操作，并将工作表相应格式化后，进行预览的结果如图1-26 所示。 Excel 技术技巧 - 8 -www.ExcelP Excel 技术 技巧 资讯 资源 图1-26 ：最终的报表 此时，不同部分的工作表中改变相应的数
