基于Excel数据源表的记账凭证打印输出

张永禄

摘要：在应用Excel进行会计核算的小微企业，其记账凭证数据以二维表的形式存储于专门的工作表中。这种存储方式符合第一范式的要求，因而对于高效、准确地进行分类计算，快速生成账簿文件和编制会计报表极为便利，但也给会计核算人员带来新的技术难题。将二维袁形式存储的记账凭证数据，准确地提取到符合国家标准归档格式的记账凭证打印模板中，供纸质打印输出存档便是其中之一。通过利用功能相对独立的多个工作袁函数配合使用，构造打印模板的单元公式，能有效解决这一技术难题。

关键词：数据源表；数据提取；公式；打印模板；会计档案

中图分类号：TP37 文献标识码：A 文章编号：1009-3044（2017）18-0201-02

1问题的提出

使用Excel进行会计核算的小微企业，其记账凭证数据是存储在二维表中的，而《会计档案管理办法》要求输出符合国家标准归档格式的记账凭证。因此将二维表形式存储的记账凭i正数据以标准记账凭证格式打印输出，以满足会计档案管理的需要，是这些小微企业的会计核算人员必须解决的技术难题。在设计出符合国家标准归档格式的记账凭证打印模板的基础上，将多个工作表函数配合使用，构造单元的“数据验证”公式或取数公式，可解决这一难题。完成公式的构造后，可以实现在指定单元输入需要打印的记账凭证编号后，检索该编号是否存在于记账凭证表中，如果存在，将该凭证编号对应的日期、附单据数、摘要、会计科目名称、借贷方金额提取到打印模板的对应单元中，供打印输出；如果不存在，则禁止输入，并弹出错误提示信息，要求用户输入有效的记账凭证编号，直至输入有效的记账凭证编号为止。

2数据源表（记账凭证工作表）及打印模板的数据结构及格式

小微企业使用的记账凭证工作表项目构成，应符合国家标准归档格式的要求。包括凭证编号、填制日期、附单据数、摘要、会计科目、借方金额、贷方金额等主要内容。记账凭证工作表的项目构成与示例数据，以及打印模板格式如圖1、图2所示。

3处理逻辑及实现技术

3.1区域名称的定义

为了公式直观易读，同时也便于公式的构造，先对记账凭证表的数据区域进行命名。（本例的数据区域从第3行开始至300行止，日常应用中应根据实际情况确定区域范围）

1.将A1单元命名为“年”。

2.将A3：A300区域命名为“月”。

3.将B3：B300区域命名为“日”。

4.将C3：C300区域命名为“凭证编号”。

5.将D3：D300区域命名为“摘要”。

6.将F3：F300区域命名为“科目名称”。

7.将G3：G300区域命名为“借方金额”。

8.将H3：H300区域命名为“贷方金额”。

9.将13：1300区域命名为“附单据数”。

3.2记账凭证编号的检测与“数据验证”设置

1）允许条件公式的构造在记账凭证打印模板的B2单元中输入记账凭证编号时，需要检测输入的编号在记账凭证表的C3：C300区域，即“凭证编号”区域中是否存在，如果不存在，则禁止输入。允许单元数据输入由“数据验证”（在Excel2010及之前的版本称为“数据有效性”）功能实现，通过返回B2单元的数据在“凭证编号”区域中的位置来检测输人的凭证编号是否存在。在B2单元“数据验证”中设置自定义公式：=NOT（ISNA（MATCH（$B$2，凭证编号，0）））。该公式先用MATCH函数检测B2单元数据在“凭证编号”区域的位置，然后用ISNA函数检测MATCH函数返回的值是否为“#N/A”，若返回TRUE，说明B2单元数据在“凭证编号”区域中不存在；若返回FALSE，说明B2单元数据在“凭证编号”区域中存在。检测结果为TRUE时不允许输入，为FALSE时允许输入，与要求正好相反，因此用NOT函数对检测的结果求反。

2）错误提示信息的设置

将B2单元“数据验证”的“出错警告”警告样式设置为“停止”，“标题”文字设置为“错误”，“错误信息”的文字设置为“凭证编号有误，请核对后重新输入！”。达到禁止输入无效凭证编号，且在用户输入无效记账凭证编号时弹出提示信息的目的。

3.3单元取数逻辑与公式的构造

由于打印模板数据来源于记账凭证工作表，因此在打印模板的记账凭证编号数据单元（本例为B2）输入编号后，先用MATCH函数在记账凭证工作表的记账凭证区域（已定义为“凭证编号”）中定位该编号第一条记录的行次，再用INDEX函数返回该行次对应的其它数据项内容，完成该编号第一条打印数据的生成。用公式表示：=INDEX（返回指定数据项内容的区域，MATCH（$B$2，凭证编号，0））。

除填制日期“年”、“月”、“日”及“附单据数”只需提取指定凭证编号在“记账凭证表”对应的第一条记录外，每一个记账凭证编号都至少对应2条以上的记录（“有借必有贷，借贷必相等”），所以其它数据项在提取第一条记录后，还需要继续检测下一条记录的记账凭证编号与打印模板B2单元输入的数据是否一致，以确定是否将记录提取到模板中的相应单元。也就是说，若检测到的凭证编号与B2单元一致，就提取该条记录的相关数据到打印模板中，否则返回空。

由于下一条记录的行次值等于本条记录的行次值加1，因此每一个指定的凭证编号从第2条记录开始，需在MATCH函数的计算结果后依次加1，2，3……，以保证提取到指定凭证编号的第2，3，4……条记录。为在进行公式填充时能动态加数，可以用获取行次值的ROW函数减去打印模板工作表第一条记录行次值（本例为41的办法实现。

3.3.1填制日期及“附单据数”的单元公式

1）“年”数据单元（D2）公式：=IF（$B$2=""，""，年）。endprint

2）“月”数据单元（F2）公式：=IF（$B$2=""，""，INDEX（月，MATCH（$B$2，凭证编号，0）））。

3）“日”数据单元（H2）公式：=IF（$B$2=""，""，INDEx（日，MATCH（$B$2，凭证编号，0）））。

4）“附单据数”数据单元（12）公式：=IF（B2=""，""，"附单据张数：”&INDEX（附单据数，MATCH（$B$2，凭证编号，0）））。

3.3.2其它数据项的单元公式

1）“摘要”数据单元（A4～A13）公式：=IF（ISNA（MATCH（$B$2，凭证编号，0）），""，IFONDEX（凭证编号，MATCH（$B$2，凭证编号，0）+ROW（）-4）=$B$2，INDEX（摘要，MATCH（$B$2，凭证编号，0）+ROW0-4），""））。

2）“科目名称”数据单元（C4～C13）公式：=IF（ISNA（MATCH（$B$2，凭证编号，0）），""，IF（INDEX（憑证编号，MATCH（$B$2，凭证编号，0）+ROW0-4）=$B$2，INDEX（科目名称，MATCH（$B$2，凭证编号，0）+ROW0-4），""））。

3）“借方金额”数据单元（13～113）公式：=IF（ISNA（MATCH（$B$2，凭证编号，0）），""，IF（INDEX（凭证编号，MATCH（$B$2，凭证编号，0）+ROW（）-4）=$B$2，INDEX（借方金额，MATCH（$B$2，凭证编号，0）+ROW（）-4），""））。

4）“贷方金额”数据单元（J3～J13）公式：=IF（ISNA（MATCH（$B$2，凭证编号，0）），""，IF（INDEX（凭证编号，MATCH（$B$2，凭证编号，0）+ROW（）-4）=$B$2，INDEX（贷方金额，MATCH（$B$2，凭证编号，0）+ROW（）-4），""））。

上述4个公式中的表达式"INDEX（凭证编号，MATCH（$B$2，凭证编号，0）+ROW（）-4）=$B$2”用于检测记账凭证工作表中的凭证编号与打印模板的B2单元是否一致；表达式“ISNA（MATCH（$B$2，凭证编号，0））”用来检测是否输入了记账凭证工作表中不存在的凭证编号。

5）“借方金额合计”数据单元（I14）公式：=SUM（I4：I13）。

6）“贷方金额合计”数据单元（J14）公式：=SUM（J4：J13）。

4结束语

通过MATCH函数定位指定凭证编号在记账凭证表中的位置，进而用INDEX函数返回记账凭证表中指定凭证编号对应的各项数据，辅之以IF、ISNA、NOT、ROW等工作表函数进行容错处理，在符合标准归档格式记账凭证模板的相应单元中进行“数据验证”设置或构造取数公式，实现了基于Excel数据源的记账凭证打印输出。在满足高效、准确地处理会计数据、产生会计信息的同时，也满足了纸质记账凭证打印输出的会计档案管理需求。endprint