“DIY”智能收文登记表

        2014年，笔者曾在《秘书工作》第12期上发表过一篇《秘书室的“奇思妙想”》，文中提到利用Excel表格制作“智能收文登记表”。文章刊出后，接到不少秘书同行的电话，询问“智能收文登记表”的制作方法。这里，介绍一下该表格的“DIY”，与大家交流。

      第一步：设计好收文登记表标题栏

      下面的设计介绍都以Excel表格2003版为例作介绍（由于Excel表格更高级版本的功能有向下兼容的特性，故不对其他高级版本作介绍）。我们秘书室当前使用的收文登记表标题栏设置有17列（如图1）。

     A列开始，从左到右每列的标题内容依次为：编号（收文登记号）、来文单位（文件字号）、文件来源（从OA传来或是寄来、派人取来等）、收文时间（含年月日）、密级、紧急程度、文件标题、拟办意见、经办人、核稿人、领导阅处意见、传阅情况（用于记录文件传阅过程中到了哪一个领导、部门及传递到每一环节的时间）、最后阅处时间、办理时限（对于有完成时限要求的文件进行时限标注，以利于自动提醒）、今天距离收文天数、紧急标注（需要紧急尽快办理的标注“紧急”，没有办结前每天跟踪）、办结归档（全部办完后标上“办结”）。这些列数及内容可以根据实际进行调整。

 

     第二步：设计自动填写时间功能

      这个功能的作用，主要是方便在收文登记表的“收文时间”一列快速准确地输入当前日期，有了这个功能，只要在该列所在行一点，然后一点按钮“收文日期”，在所激活的单元格马上就自动输入了当前的日期。

     实现这个功能，要用到简单的VBA代码编程。打开Excel表格，点击菜单栏“视图”→“工具栏”→“控件工具箱” （如图2），出现“控件工具箱”（如图3）。

点击长方形的“命令按钮”（鼠标移到该按钮上会出现“命令按钮”四个字，如果使用的是2007版Excel，可点击左上角圆形的Office按钮→Excel选项→常用，勾选“在功能区显示‘开发工具’选项卡”，确定后点击功能区“开发工具”→“插入”，“ActiveX控件”下长方形的即为“命令按钮”）。这时表格页面上的鼠标出现“十字”形状，单击鼠标左键按住一划，就出现一个按钮的形状，这时鼠标指中按钮并点击右键，会出现一系列菜单，点击“查看代码”，可以进入“代码编辑窗口”（如图4）。

可以看见已经有两行代码：

Private Sub CommandButton1_Click()

（光标已停在此行首位）

End Sub

然后，在两行代码之间（上述括号标注处），加上以下一行代码即可：

ActiveCell.FormulaR1C1 = Format(Date, "yyyy年mm月dd日")

之后，在“代码编辑窗口”菜单栏下一行显示的操作按钮中找到一个绿色三角形按钮（用鼠标指中该按钮，会显示“运行子过程/用户窗体”），点击一下这个按钮，然后关闭“代码编辑窗口”，回到表格页面，在要输入日期的单元格一点，再点“命令按钮”（这时按钮的默认名字为“CommandButton1”），刚才的激活单元格就输入了当前日期。

最后，为便于今后操作时知道该“命令按钮”到底是执行什么命令，可以对该按钮重命名为“收文日期”。方法是：右键单击该按钮，在下拉菜单中选“命令按钮对象”→“ 编辑”，这时按钮的原始英文名“CommandButton1”后有光标闪烁，这时可删去英文名，输入中文名“收文日期”，再点其他任意单元格，即完成了按钮重命名操作。

这里提一下，这个按钮是可以移动的，尽管如此，文件多了，表格往下走了就看不见这个按钮了，要从上面移下这个按钮也可以，但挺费事。可以这样处理：将这个按钮放在标题栏一行“收文时间”所在的单元格，之后，将第一行选中，在菜单栏“窗口”的下拉菜单中选“冻结窗口”即可。这样，不管表格往下走多少，都可以始终看见第一行（包括“收文时间”按钮），这样对输入时间十分便利。

还有一个是自动计算“今天距离收文天数”的功能。我们在收文登记表第15列（O列）设置了“今天距离收文天数”列，用于自动计算每份文件收到有多少天了。实现这个功能方法如下：点中该列第2行单元格（即“O2”单元格，因为“O1”单元格有标题栏内容，故不选“O1”单元格）。在编辑栏（即表格与工具栏之间的一行，符号“fx”右侧的输入框处）粘贴公式：

“=DAYS360($D2,TODAY())”

然后按回车键，即可在“O2”单元格自动计算出“今天距离收文天数”（如图5）。

注意，该行对应的收文日期要存在，否则会计算出一个特别大的错误数值。再点击“O2”单元格，将光标移到“O2”单元格的右下角，这时光标由“空心十字形”变为“实心十字形”，按住鼠标左键，往同列下面的单元格拖鼠标，所拖到的单元格，都会自动填写公式，计算出“今天距离收文天数”。

     第三步：设计自动提醒功能

      目前设计了三类提醒功能：

      第一类是紧急文件在办结前每天突出显示提醒。设置如下：将A列至Q列全部选定，在菜单栏“格式”下拉菜单中点击“条件格式”，出现“条件格式”设置对话框（如图6）。

在左上角“单元格数值”框右边有个下拉菜单符号，点击出现两个选项：“单元格数值”“公式”，点选“公式”，之后在右边的条件输入框输入：

“=($P1="紧急")*($Q1<>"办结")=1”

再点击对话框右下“格式”按钮，出现“单元格格式”设置对话框（如图7）。

可以按自己的喜好设定突显的格式，我们设置为“红色加粗字体，浅蓝色底色”，点击“确定”按钮，完成设置。上面的公式设置表示：只要在某个文件的P列输入了“紧急”两字，而Q列又没有出现“办结”两字，那么，该份文件所在一行则始终突出显示。如果使用的是2007版Excel，点击菜单栏“开始”→“条件格式”→“新建规则”，出现“新建格式规则”对话框，选择“使用公式确定要设置格式的单元格”，在下面的输入框输入公式并设置格式即可。

第二类是文件有办理时限，并且当前距时限比较长，容易忘记的，在时限到来前3天（这个时间可以往前或往后改变设置）开始突出显示提醒。调出“条件格式”对话框，点击对话框下面一排的“添加”按钮，出现第二个条件设置框。之后的设置步骤基本与第一类提醒功能相同，最后在条件输入框输入：

“=(DAYS360(TODAY(),$N1)<=3)*($Q1<>"办结")*($N1<>"")=1”

这个公式表示文件办理时限与当前日期比较，如果天数小于或等于3天，而且又没有办理完结，则突出显示。

第三类是防止一个文件长时间没有办结而开始突出显示提醒，我们秘书室设置为10天，以防止遗忘，此类提醒特别适合文件传阅领导比较多的情况。设置步骤基本与第二类提醒功能相同，最后在条件输入框输入：

“=(DAYS360($D1,TODAY())>=10)*($Q1<>"办结")*(($D1)*1>1)=1”

这个公式表示文件收文日期与当前日期比较，如果天数大于或等于10天，而且又没有办理完结，则突出显示。

设置完成三类提醒功能的对话框如图8。

     第四步：设计好《文件呈批传阅表》及自动填写功能

     （一）设计好呈批表（如图9）。

（二）利用查询引用函数（VLOOKUP），自动填写呈批传阅表。只要呈批表上要填写的要素是收文表上已经登记过的内容，我们就可以利用首列查询引用函数（VLOOKUP）依次将这些要素查找出来并自动填写到相应的单元格中。

（三）自动填写功能的实现步骤：点击要自动填写的单元格，比如，要将登记表中“文件来源”查找到并自动填写到呈批表中“此件”右边的单元格，就点击“此件”右边的单元格，然后在编辑栏点击光标，粘贴公式（如图10）：

“=VLOOKUP(L2,收文登记表!$A:$Q,3,FALSE)”

公式中有四个参数：“L2”表示要查找的文件编号，“收文登记表!$A:$Q”表示查找的范围，“3”是列参数值，表示要自动填写的内容在第几列，“FALSE”表示准确或模糊查找方式。将上述公式复制到需要自动填写内容的单元格，然后修改相应的列参数值，如“来文单位（文号）”的列参数值为2，“文件标题”的列参数值为7，“拟办意见”的列参数值为8。

（四）这里要说明几点：一是我们已经先将表格“Sheet1”更名为“收文登记表”；二是呈批传阅表中的“编号”内容必须手工输入，即上面公式中“L2”的值，这是各个公式查找的根据，“编号”内容一输入进去，其他填写了公式的单元格就能够自动查找填写登记表中的内容了；三是如果各人做的呈批表中“编号”所在的单元格位置不同，如输入单元格为“K10”，则公式中“L2”要相应改为“K10”；四是自动填写的内容必须是“收文登记表”中存在的内容；五是只需在设计呈批表时在每个单元格填写一次公式就可以了，以后只要改变“编号”，其他内容就会自动搜索填写。

（文章摘自《秘书工作》杂志2015年第11期；作者：邓国柱）