[原创]《Excel专业开发》解析之工作表设计

fanjy

谨以此文献给八一建军节!献给伟大的中国人民解放军!

分类：《Excel专业开发》

1 前言
不可否认，《Excel专业开发》是一本真正意义上的Excel专业开发图书，作者以其丰富的实践经验和技术功底，从应用程序开发的角度全面讲解了使用Excel开发应用程序的最佳实践。但是，正如该书开头所述，“本书不是一本入门级读物”，事实也是如此！阅读该书需要读者有一定的Excel使用经验以及进行过VBA编程，而且对Excel和VBA越熟悉，越能顺利地理解书中的内容，当然，随着对该书内容的理解，Excel技术和VBA编程水平也会有相当大的提高。在该书中，读者也会看到真正意义上的使用Excel开发的应用程序。在感叹Excel强大功能的同时，也会激发读者深入学习Excel开发的热情。
然而，任何事物到达一定的高度后必然会产生一些不利后果，这可能是物及必反的道理吧。对于这本书也是一样，如果是Excel高手阅读本书，肯定会有一种如鱼得水、如获至宝、相见恨晚的感觉，巴不得快点阅读完，早日领会其中的精髓并应用到实际中；但如果是Excel初学者，或者是VBA初学者阅读本书，那就会觉得相当的晦涩难懂，特别是从第4章开始，越往后越会觉得不知所云，需要花费相当多的时间和精力，这样，不仅不会让读者感受到Excel开发的便捷，反而会觉得使用Excel开发很难，产生畏惧心理，甚至打消进一步探究Excel高效应用的念头。
在这里，笔者试图对《Excel专业开发》的各章进行解析，目的是引导读者完成对本书的阅读，使读者更容易地理解书中的内容，从而提高Excel的应用能力。也不想让一本好书埋没掉，而是通过对本书的解析使得更多的Excel爱好者能领会到书中的内容，领略到专业级别的Excel技术。由于笔者也是在学习过程中，水平有限，错误之处尽请指正。
下面依照本章的顺序进行解读，然后对本章的实例分析进行具体讲解。

[此贴子已经被作者于2007-8-1 14:29:45编辑过]

fanjy

2 “工作表设计”一章的主要内容和知识点
“工作表设计”是《Excel专业开发》的第4章，该章主要介绍了如何充分利用Excel的内置功能来设计功能强大的用户接口。简单地说，用户接口实现了用户和Excel应用程序之间的连接，用户在其能够看到的Excel应用程序界面中进行操作，Excel通过接口接收和验证用户的操作，然后实现所需的结果并呈现给用户。在本章的开头介绍了工作表用户接口设计的原则，实质上大部分是工作表用户界面的设计，以保证界面清晰、有条理、一目了然。然后，依次介绍了工作表设计的一系列良好的技术。
2.1 通过隐藏的行和列来验证在工作表可见部分的输入。这些验证的结果可以用来给用户某种提示，也可供后面的VBA编程提供判断条件。
2.2 定义名称
(1) 这里介绍了一个新的知识点：相对命名区域，也就是说，名称所定义的区域会随着单元格位置的变化而变化，因此，该名称所代表的区域不是一个固定区域。因为命名相对命名区域则是通过行列的相对引用来实现的，所以一定要注意在命名时所选择的起始点。
(2) 书中图4-3所示的实例中出现的问题，在Excel 2003中似乎不会发生。
(3) 命名公式的作用主要有两点：一是方便维护复杂的公式，另一个是可以创建动态命名区域，实现动态功能。一般公式中会涉及到Offset函数和CountA函数。
(4) 工作簿级的名称和工作表级的名称。顾名思义，两类名称的作用范围不同，工作簿级的名称能应用于整个工作簿，而工作表级的名称只在该工作表中使用。这样，允许多个工作表中有相同的名称定义。定义工作簿级的名称，在名称框或“定义名称对话框”中输入名称即可，而定义工作表级的名称，则需要在名称前面加上相应的工作表的名称和一个“!”号，如下图1所示。

图1：定义工作表级的名称
2.3 样式。通过样式可以很方便快捷地设置多个格式特征相同的单元格区域，因此，对于多个不同区域中有相同格式的工作表，建议设置样式。
2.4 用户接口绘图技术，用来设计Excel表格的界面。
(1) 使用单元格边框来创建特殊的效果，如凹和凸的效果，形如按钮。
(2) 表格中的每一部分应使用单独的样式进行格式，以保持界面中格式的一致性。
(3) 在对表格中的每一部分应用了单独的样式后，再使用单元格边框创建特殊效果，以及调整行高和列宽增加视觉效果。
(4) 对用户表格界面中不同区域的单元格进行批注，使表格更易理解，且助于导航。
(5) 使用图形可以创建功能强大的用户接口，增强用户界面效果。
2.5 通过数据有效性功能进行数据的检验。
(1) 检验单元格区域中输入的数据是否满足条件和要求。
(2) 创建级联列表，即某一单元格中的数据是根据另一单元格中相应的数据来显示的，且能设置用户只能在提供的列表中进行选择所需的数据。但是，当用户修改级联列表相关的单元格中的数据时，与之相关的单元格中的数据不会自动改变。正如书中所提到的，这可以借助于条件格式来解决。
(3) 勘误：本章第69页的公式中多出了一个括号。
2.6 条件格式功能在Excel中非常强大。在用户界面开发时，可以使用条件格式来创建动态表和以突出的格式表明数据处于错误状态。
(1) 创建动态表是本章讲解的实用技巧之一，也就是说，当用户需要时，可供输入数据的表格行自动显现。
(2) 正如上文在级联列表中所提到的，当第二列中的数据由第一列决定，而在使用过程中，改变了其中一列的数据时，另一列不会自动改变，此时，可使用条件格式来突出显示错误状态。
2.7 本章并没有具体介绍控件的应用，但讲解了窗体控件和ActiveX控件的区别。注意，通常应为ActiveX控件，但书中却写为了Active控件。

[此贴子已经被作者于2007-8-1 14:18:30编辑过]

fanjy

3 实例分析
由于图书篇幅的关系，因此书中的实例只是讲解了一些关键点，并没有讲解完整的创建过程，这可能不利于初学者学习。(特别是后续章节，因为章节中前面的介绍也与后面的实例相关，所以往往让人捉摸不透)
这里，将创建该实例的完整过程进行讲解，以助于读者对前面所述知识点的学习和理解。
3.1 实例的结构
实例为一个工作簿，该工作簿包括两个工作表，其中一个为时间输入表TimeEntry，提供给用户输入界面；另一个为基础数据表wksProgramData，提供有效性验收的数据，在最终的界面中，该表是隐藏的。
下图2和图3是最终完成但行列和工作表还没有隐藏时的工作簿。

图2：设计完成的TimeEntry工作表，其中部分行列还未被隐藏。
在图2所示的工作表中，用户在背景为白色的单元格中进行输入。背景为浅绿色的单元格将自动计算总时间。输入单元格均设置了数据有效性检验，部分单元格提供下拉列表辅助用户的输入工作。例如，单击单元格F7后会出现下拉列表，可从中选择工作日，当试图在该单元格中输入数据时，会弹出错误提示。同时，工作表中也设置了条件格式，用于特殊提示和错误提示。例如，当工作日为星期六和星期日时，单元格背景颜色为浅黄色，表明这两天是周末；当客户和项目之间的级联关系被破坏时，也就是当选择客户和对应的项目后，又修改了客户单元格或项目单元格，此时单元格背景颜色为红色，表明这行输入的数据有误。
 
图3：设计完成的wksProgramData工作表，该工作表最终会被隐藏
上述两个工作表的具体设计方法将在下面详细介绍。
3.2 实例的创建过程
该实例大致按以下过程进行创建：设计表格及其样式和外观——设置单元格格式——输入基础数据——定义名称及设计相应的公式——对输入工作表TimeEntry设置数据有效性并将其与基础数据工作表wksProgramData相关联——设置验证公式——设置条件格式进行表格检验——进行批注说明——隐藏相应的行列和工作表

[此贴子已经被作者于2007-8-1 14:19:32编辑过]

fanjy

3.3 设计表格及其样式和外观
表格的最终样式和外观如图2和图3所示，下面来进行表格设计。
(1) 全选工作表中的所有单元格(可单击工作表中左上角行列交叉的空白处来全选单元格)，然后选择菜单“格式>单元格”，在“单元格格式”对话框中的“图案”选择卡上，选择灰色，将单元格的底纹变为灰色。
(2) 定义样式，以便于重复应用在工作表中相同格式的区域，使得工作表界面相同功能的区域有统一的格式。选择菜单“格式>样式”，在弹出的“样式”对话框中，对样式进行定义。在本例中，定义了三种样式，分别应用在表格的标题单元格、输入单元格和公式单元格区域，如图4、图5、图6所示。
 
图4：标题单元格区域的样式，单元格背景色设置为水绿色。
 
图5：输入单元格区域的样式，单元格背景色设置为白色，保护设置为无保护，以备后面设置只能在该区域进行输入。
 
图6：公式单元格的样式，单元格背景色设置为浅绿色。
(3) 在工作表中应用刚才定义的样式，以TimeEntry工作表为例，如图7所示。
先选择要应用样式的单元格区域，然后单击“格式>样式”，在“样式”对话框中选择要应用的样式，单击“确定”。
 
图7：在工作表单元格中应用相应样式后的效果
(4) 使用用户接口绘图技术。在标题单元格区域和表格单元格区域使用边框设置来创建特殊效果，同时在表格内设置边框模拟网格线。设置后的工作表效果如图8所示。
 
图8：使用用户接口绘图技术后的工作表效果
按照书中所讲的方法进行用户界面设置时，要注意在使用“单元格格式”对话框的“图案”选项卡时，应先选择“线条”、“颜色”后再选相应的边框，这样设置才有效。
同理，设置wksProgramData工作表及其样式和外观，如上图3所示。
3.4 设置TimeEntry工作表中的单元格格式。各单元格区域的格式如下：
单元格G4：自定义日期格式mmmm d,yyyy
单元格区域F7：F20：自定义日期格式dddd
单元格区域J7：K20：自定义时间格式h:mm AM/PM
单元格区域L7：L20：自定义时间格式[h]:mm
单元格区域E7：E20：自定义日期格式mm-dd-yy
单元格区域A23：A29：自定义日期格式dddd
3.5 在工作表wksProgramData中输入基础数据，即单元格区域A2：A10、C2：C10、E2：E10中的数据，如图3所示。在单元格G1中输入数据，同时在单元格区域H1：P1中输入“---”，以备在以后的公式中使用。在G3：J10中输入相应的数据。
3.6 定义名称及设计相应的公式
(1) 定义工作簿级的名称
工作簿级的名称= 定义
setIsTimeSheet =TRUE
setVersion =1
tblTimeSheet =TimeEntry!$D$6:$L$20
valActivitiesList =OFFSET(wksProgramData!valActivitiesTop,1,0,COUNTA(wksProgramData!valActivitiesCol)-1,1)
valClientsList =OFFSET(wksProgramData!valClientsTop,1,0,COUNTA(wksProgramData!valClientsCol)-1,1)
valConsultantsList =OFFSET(wksProgramData!valConsultantsTop,1,0,COUNTA(wksProgramData!valConsultantsCol)-1,1)
valProjectRows =wksProgramData!$G:$G
valProjectsTop =wksProgramData!$G$2
(2) 定义工作表TimeEntry中的名称
工作表TimeEntry级的名称= 定义
TimeEntry!errHasErrors =TimeEntry!$A$2
TimeEntry!inpDay =TimeEntry!$F25
TimeEntry!inpEmployee =TimeEntry!$G$3
TimeEntry!inpEntryRow =TimeEntry!$F27:$K27
TimeEntry!inpStart =TimeEntry!$J28
TimeEntry!inpStop =TimeEntry!$K29
TimeEntry!inpWeekEnding =TimeEntry!$G$4
TimeEntry!prtTotalHours =TimeEntry!$L31
TimeEntry!setHideCols =TimeEntry!$D$1:$E$1
TimeEntry!valClient =TimeEntry!$G37
TimeEntry!valDayList =TimeEntry!$A$23:$A$29
TimeEntry!forTimeDif =IF(COUNTA(TimeEntry!inpEntryRow)<6,"",IF(TimeEntry!inpStop>TimeEntry!inpStart,TimeEntry!inpStop-TimeEntry!inpStart,(1+TimeEntry!inpStop)-TimeEntry!inpStart))
    (3) 定义工作表wksProgramData中的名称
工作表wksProgramData级的名称= 定义
wksProgramData!valActivitiesCol =wksProgramData!$C:$C
wksProgramData!valActivitiesTop =wksProgramData!$C$1
wksProgramData!valClientsCol =wksProgramData!$E:$E
wksProgramData!valClientsTop =wksProgramData!$E$1
wksProgramData!valConsultantsCol =wksProgramData!$A:$A
wksProgramData!valConsultantsTop =wksProgramData!$A$1
注：有些名称本例中未使用，这些名称是为以后对本例进行扩展而准备的。
其中，动态名称为：valActivitiesList、valClientsList、valConsultantsList，设置的动态名称可以自动调整所引用区域的范围。
相对引用区域的名称为：TimeEntry!inpDay、TimeEntry!inpEntryRow、TimeEntry!inpStart、TimeEntry!inpStop、TimeEntry!prtTotalHours、TimeEntry!valClient。在命名相对引用区域时，一定要注意参照单元格的位置，例如，如果当前单元格在第2行，则TimeEntry!inpDay 应为=TimeEntry!$F2。
命名的常量为：setIsTimeSheet、setVersion，这些常量本例中未使用，留待以后对实例进行扩展。
(4) 在工作表wksProgramData中的G2：P2区域输入数组公式：
=TRANSPOSE(valClientsList)
注意，输入完后，应按Ctrl+Shift+Enter组合键。此时，该区域中的数据为区域valClientsList中数据的转置。
(5) 在工作表TimeEntry中的A29中输入公式：
=inpWeekEnding
即将该单元格与单元格G4相关联。然后，在单元格A23输入公式：
=A24-1
选择单元格A23，将光标放置在该单元格的右下角，待光标变为黑十字后向下拖至单元格A28，即上方的单元格的值为下方单元格的值减1。

[此贴子已经被作者于2007-8-1 14:22:58编辑过]

fanjy

3.7在工作表TimeEntry中设置数据有效性并将其与基础数据工作表wksProgramData相关联
设置数据有效性的方法：先选择要设置数据有效性的单元格或单元格区域，然后再选择菜单“数据>有效性”，在“数据有效性”对话框中的“设置”选项卡内进行设置条件，在“出错警告”选项卡内设置出错信息。
(1) 设置单元格G3，如图9所示。
 
图9：单元格G3的数据有效性设置
同时，在“出错警告”选项卡中设置相应的出错警告信息。
(2) 设置单元格G4，如图10所示。
 
图10：单元格G4的数据有效性设置
同时，在“出错警告”选项卡中设置相应的出错警告信息。
(3) 设置单元格区域F7：F20，如图11所示。

图11：单元格区域F7：F20的数据有效性设置
其中“来源”文本框中的公式为“=IF(ISBLANK(inpWeekEnding),$G$1,valDayList)”。
(4) 设置单元格区域G7：G20，如图12所示。
 
图12：单元格区域G7：G20的数据有效性设置
(5) 设置单元格区域H7：H20，如图13所示。
 
图13：单元格区域H7：H20的数据有效性设置
其中“来源”文本框中的公式为
“=OFFSET(valProjectsTop,1,MATCH(valClient,valClientsList,0)-1,COUNTA(OFFSET(valProjectRows,0,MATCH(valClient,valClientsList,0)-1))-2,1)”
(6) 设置单元格区域I7：I20，如图14所示。
 
图14：单元格区域I7：I20的数据有效性设置
(7) 设置单元格区域J7：K20，如图15所示。
 
图15：单元格区域J7：K20的数据有效性设置

[此贴子已经被作者于2007-8-1 14:24:57编辑过]

fanjy

3.8 设置验证公式
如图2所示，工作表TimeEntry中在设置了样式的区域之外，在A列、B列、D列、E列都设置有数据，这些数据由公式生成，用于验证输入的结果是否符合要求，也为该实例的扩展做准备。这4列以及第1行在最终完成的工作表中将被隐藏。
(1) 单元格A2中的公式为：=OR(A3:A4,A6:B6)，将错误检查的结果进行合并。
(2) 单元格A3中的公式为：=ISBLANK(inpEmployee)，检查单元格G3是否有输入。
(3) 单元格A4中的公式为：=ISBLANK(inpWeekEnding)，检查单元格G4是否有输入。
(4) 单元格A6中的公式为：=OR(A7:A20)，汇总单元格区域A7：A20中的结果。
(5) 单元格B6中的公式为：=OR(B7:B20)，汇总单元格区域B7：B20中的结果。
(6) 单元格区域A7：A20中的公式为：
=IF(COUNTA(inpEntryRow)=0,FALSE,COUNTA(inpEntryRow)<>6)
用于判断命名区域inpEntryRow(即表格中对应行输入部分的前6列：工作日、客户、项目、活动、开始时间、结束时间)是否全部输入完毕，输入完成为False，否则为True。
(7) 单元格区域B7：B20中的公式为：
=IF(ISBLANK(H7),FALSE,ISERROR(MATCH(H7,OFFSET(valProjectsTop,1,MATCH(valClient,valClientsList,0)-1,COUNTA(OFFSET(valProjectRows,0,MATCH(valClient,valClientsList,0)-1))-2,1),FALSE)))
判断同一行中的客户列与项目列中的级联数据是否相匹配，匹配为False，不匹配为True。以此结果作为条件格式的依据。
注：本文并没有解释相对较为复杂的公式。建议对于复杂的公式，可以根据最外层函数的参数和括号配对，拆成一个个小公式，然后再进行组合，这样使复杂的公式更容易理解。
(8) 单元格区域D7：D20中的公式为：
=IF(LEN(prtTotalHours)>0,inpEmployee,"")
单元格区域E7：E20中的公式为：
=IF(LEN(prtTotalHours)>0,inpWeekEnding,"")
这两个区域在本实例中暂时未用到，留待今后的扩展。
从上面四个区域中所输入的公式中可以看出命名名称的好处，即区域中每个单元格中的公式一样，实现相同的结果。
在工作表中输入公式时，如果是已命名的名称，则该名称会显示不同的颜色。利用这个特点，可以判断输入是否正确。
3.9 设置条件格式进行表格检验
对单元格区域F7：L20设置条件格式，使得工作日为星期六和星期天时，相应的行能突出显示，以有明该工作日为休息日；当客户列中的数据与项目列中的数据不匹配时，相应的行能突出显示，以警示错误。
选择单元格区域F7：L20，然后选择菜单“格式>条件格式”，在“条件格式”对话框中进行设置，如图16所示。
 
图16：对单元格区域F7：L20设置条件格式
其中条件2的公式为：
=IF(ISBLANK(inpDay),FALSE,OR(WEEKDAY(inpDay)=1,WEEKDAY(inpDay)=7))
此时，若输入的数据满足条件，则会显示条件格式设置的结果，如图17所示。
 
图17：条件格式所显示的结果
3.10 进行批注说明
对单元格和相应的行进行批注，以说明其代表的相关功能，辅助用户理解和使用表格。如工作表中左上角红色小三角所在的单元格。
3.11 隐藏相应的行列和工作表
最后，隐藏TimeEntry工作表中的列A、列B、列D、列E、以及第1行，同时，隐藏工作表wksProgramData。最后的结果如图18所示。
 
图18：最终完成的工作簿

[此贴子已经被作者于2007-8-1 14:26:38编辑过]

fanjy

4 小结
至此，已按照书中的介绍完成了本章的实例，但该实例还有未完善的，例如，在表格中不同的行除了客户、项目、活动中对应的单元格外输入同样的数据不会提示错误(如图19所示)，这不符合实际情况，因为同一个人同一天同一时间不会同时做几件事情。
iamzZi3y.rar (8.39 KB, 下载次数: 226)

2007-8-1 14:27 上传

点击文件名下载附件

[此贴子已经被作者于2007-8-1 14:27:55编辑过]

晋级菜鸟

这么好的帖子为什么没有人顶?我顶顶顶

jsxlop

这本书挺好的，我昨天刚买了本，回家后大致浏览了一下，不错。

红金鱼

范版推荐的书,定要买本看看!