一起学Excel专业开发13：Excel工时报表与分析系统开发（1）
一起学Excel专业开发13：Excel工时报表与分析系统开发（1）
通过前面的学习，我们已经了解了Excel专业开发的一些原则、应用程序结构、以及一些常用技巧，下面我们以开发一个Excel应用程序着手，来加深对Excel专业开发的理解。随着后面的学习，这个应用程序将逐步成为一个功能完善的Excel实用程序。
根据要实现的功能，我们将这个Excel应用程序命名为“Excel工时报表与分析系统”，简称PETRAS（Professional Excel TimesheetReporting and Analysis System）。
现在的PETRAS是一个简单的工作簿，包括一个用来进行工时输入的工作表和一个隐藏的用于存放数据有效性列表的工作表。用户每周进行一次工时输入，然后手工将该工作簿复制到某中心位置以便进一步处理。
下图1是PETRAS应用程序的第一个版本的用户界面。
图1
PETRAS应用程序运用的技术有：预定义名称、不同区域使用不同样式、格式化技术、批注、数据验证、条件格式，等等。
隐藏行和列
将用于错误检查和后台数据处理的行和列隐藏起来，不让最终用户看见。如下图2所示的列A、列B、列D和列E，以及行1。
图2
其中，列A和列B为前面文章介绍的程序列，用于错误检查。列D和列E用于创建数据表，并且可以使数据合并操作更简单，避免让用户重复输入每行的数据。
样式
在工作表中，将不同的样式应用于行列标题、输入区、公式结果区、以及用户界面以外的区域，使工作表一目了然。
三维效果
在工作表中，设置边框颜色来模拟表格网格线，并使之具有三维外观效果。
单元格批注
单元格批注帮助用户了解该单元格和相关单元格的作用，提示用户正确的操作，上图2中右上方有红色小三角形的即为含有批注的单元格，当光标移动到该单元格上时会自动显示批注信息，其中一个单元格批注如下图3所示。
图3
预定义名称
在工作表的“总小时数”列，使用了一个名为forTimeDiff的命名公式，如下图4所示。
图4
命名forTimeDiff的公式如下：
=IF(COUNTA(inpEntryRow)<6,"",IF(inpStop>inpStart,inpStop-inpStart,(1+inpStop)-inpStart))
1.公式中引用的名称如下：
inpEntryRow=工时输入!$F7:$K7
inpStart=工时输入!$J7
inpStop=工时输入!$K7
可以看出，这些定义名称的行引用是相对引用，而列引用是绝对引用。
2.公式表明，如果当前行中输入的数据少于6个，则返回空字符串，即当前行中的数据未全部输入时，不允许计算总时间。当前行中所需数据全部输入完成后，还需要对开始时间和结束时间进行比较；当结束时间大于开始时间时，表明这两个时间在同一天，直接用结束时间减去开始时间得到工作时间；如果结束时间小于开始时间，则表明工作开始于前一天，而结束于后一天，即工作跨了一天，需要将结束时间加上1再减去开始时间，便得到工作时间。
数据验证
在PETRAS中，每个输入单元格都使用了数据验证。其大多数用于数据验证列表内容都存储在隐藏的工作表wksProgramData中，如下图5所示。
图5
其中，“顾问”列为“工时输入”工作表的“顾问”单元格提供数据验证列表项，同样其“活动”列为“工时输入”工作表的“活动”列提供数据验证列表项。详细技术可以研究本文后面提供的示例程序并参考本系列前面关于数据验证技术的文章：《一起学Excel专业开发11：2个常用的数据验证技巧》。
条件格式
从图1中可以看出，周末所在的数据行添加了特殊的背景色，这是使用条件格式实现的。此外，条件格式还可以给出错误提示，例如当客户与项目不匹配时，就会对该行添加红色背景色提示该行有错误，如下图6所示。
图6
详细技术可以研究本文后面提供的示例程序并参考本系列前面关于条件格式技术的文章：《一起学Excel专业开发12：条件格式的2个应用技巧》。