VLOOKUP函数在编制账簿及报表中的应用

梁明

[摘要] VLOOKUP函数具有查找特定数值的功能，在日常工作中，特别是在日常的账务处理工作中具有较广泛的应用价值。本文对应用VLOOKUP函数编制账簿及报表的方法进行了探讨与阐述，以期能够为用Excel系统进行账务处理的人员提供一种可供参考的账簿和报表的编制方法。

[关键词] VLOOKUP函数；试算平衡表；账簿；报表

[中图分类号] F231.4 [文献标识码] A [文章编号] 1673 - 0194（2013）06- 0022- 02

VLOOKUP函数的功能是搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值，即VLOOKUP函数具有查找特定数值的功能。在实际工作中，特别是在日常的账务处理工作中，经常需要我们查找一些数据，VLOOKUP函数能够帮助我们快速准确地查找到相关的内容。因此，VLOOKUP函数在实际工作中具有较广泛的应用价值。本文对应用VLOOKUP函数编制账簿和报表的方法进行了探讨与阐述，以期能够为用Excel系统进行账务处理的人员提供一种可供参考的账簿和报表的编制方法。

1 VLOOKUP函数在编制账簿中的应用

本文中，假设编制账簿所需数据来源于已生成的试算平衡表（见图1），通过设计账簿样式（本文以总账为例，见图2）、完成有关金额栏目的公式设置，即可生成相应的账簿（见图2）。

图1和图2中的会计科目都分别设置了科目代码和科目名称，在图1中把科目代码设为首列，其原因在于：在使用VLOOKUP函数时，要求第一列中的数值必须按升序排列，如果直接以科目名称作为首列，升序排列后会破坏会计科目习惯性的排列次序；而把科目代码作为首列，因为各科目代码开头的字母1～6分别代表了资产类、负债类、共同类、所有者权益类、成本类和损益类，升序排列不会影响会计科目的习惯性排序，因此本文将科目代码设置为VLOOKUP函数查找区域的首列。

现对总账（图2）中相关单元格公式的设置进行说明：

1.1 科目代码有效性设置[1]

对单元格C1进行科目代码有效性设置，其方法步骤如下：①从试算平衡表（图1）中将科目代码和科目名称两列复制到总账（图2）的M、N两列中；②选择总账（图2）单元格C1，从菜单栏中选择“数据-有效性”，以M列中的科目代码作为选择序列的数据来源即可完成科目代码有效性设置（见图3）。通过科目代码有效性设置可以随时更新不同会计科目的账页。

1.2 其他涉及金额计算的单元格公式的设置

以图1、图2中的数据为例，其他涉及金额计算的单元格在进行公式设置时，都能够通过VLOOKUP函数从试算平衡表中直接查找到或经过公式分析计算得到相应的数值，有关单元格公式设置如下。

1.2.1 期初余额计算公式设置

J3为设置期初余额计算公式的单元格，其公式设置为：

J3=IF（VLOOKUP（C1，试算平衡表！A4：H18，3）-VLOOKUP（C1，试算平衡表！A4：H18，4）>0，VLOOKUP（C1，试算平衡表！A4：H18，3），IF（VLOOKUP（C1，试算平衡表！A4：H18，4）-VLOOKUP（C1，试算平衡表！A4：H18，3）>0，VLOOKUP（C1，试算平衡表！A4：H18，4），0））

此公式设置的基本原理是，通过VLOOKUP函数分别查找得到期初借方余额和期初贷方余额的数值，通过两数值相减确定余额在借方或是在贷方。当借方余额减贷方余额大于0，表示余额在借方，显示期初借方余额数值；当贷方余额减借方余额大于0，表示余额在贷方，显示期初贷方余额数值；如果前两种情况都不是，余额为0。

1.2.2 期末余额计算公式设置

J4为设置期末余额计算公式的单元格，其公式设置原理同单元格J3。将J3单元格公式中期初借、贷方余额的数值分别改为期末借、贷方余额的数值即可完成J4单元公式设置。其具体计算公式为：

J4=IF（VLOOKUP（C1，试算平衡表！A4：H18，7）-VLOOKUP（C1，试算平衡表！A4：H18，8）>0，VLOOKUP（C1，试算平衡表！A4：H18，7），IF（VLOOKUP（C1，试算平衡表！A4：H18，8）-VLOOKUP（C1，试算平衡表！A4：H18，7）>0，VLOOKUP（C1，试算平衡表！A4：H18，8），0））

1.2.3 期初及期末记账方向的设置

单元格I3和单元格I4公式的设置可分别参考单元格J3和单元格J4的设置，余额在借方时，单元格显示“借”；余额在贷方时，单元格显示“贷”，否则单元格显示为“平”。

I3=IF（VLOOKUP（C1，试算平衡表！A4：H18，3）-VLOOKUP（C1，试算平衡表！A4：H18，4）>0，"借"，IF（VLOOKUP（C1，试算平衡表！A4：H18，4）-VLOOKUP（C1，试算平衡表！A4：H18，3）>0，"贷"，"平"））

I4=IF（VLOOKUP（C1，试算平衡表！A4：H18，7）-VLOOKUP（C1，试算平衡表！A4：H18，8）>0，"借"，IF（VLOOKUP（C1，试算平衡表！A4：H18，8）-VLOOKUP（C1，试算平衡表！A4：H18，7）>0，"贷"，"平"））

1.2.4 本期发生额公式设置

G4和H4单元格用于计算本期借、贷方发生额。由于本期发生额不需要判断记账方向，因此可以由试算平衡表直接取数。G4和H4单元格计算公式设置为：

G4=VLOOKUP（C1，试算平衡表！A4：H18，5）

H4=VLOOKUP（C1，试算平衡表！A4：H18，6）

1.2.5 本期合计相关单元格公式的设置

G5=G4；H5=H4；I5=I4；J5=J4

至此，总账设置完毕。当科目代码变动时，系统自动生成与科目相应的总账数据。

2 VLOOKUP函数在编制报表中的应用

本文以利润表的编制为例（见图4），说明VLOOKUP函数在报表编制中的应用。

图4中，A列列示了科目代码，作为查找的对象，在D4单元格中输入公式“D4=VLOOKUP（A4，试算平衡表！■A■4：■H■18，5）”（见图4中编辑框所列示公式），利用VLOOKUP函数即可快捷获取科目对应的本期发生额数值。采用自动填充功能即可快速完成其他科目的取数。

在D列中，某些单元格出现“#N/A”错误值显示，表示在试算平衡表中没能找到与A列列示内容所对应的值，所以这些单元格需要手工调整。如D8单元格计算公式可调整为“D8=D4-D5-D6+D7”，其他显示“#N/A”错误值的单元格作类似处理。为使表格美观，利润表完成表格设置后可将A列隐藏起来。

用VLOOKUP函数编制账簿和报表的优点在于利润表一经设置完毕，以后每期试算平衡表数据更新时，利润表也会自动实时更新，不需要每期进行重新设置；此外，更主要的是当试算平衡表中科目排序发生变化的时候，不会影响对各科目相关数据的取数，因此减少了差错率的发生，这也是VLOOKUP函数在实际工作中得以广泛应用的主要原因。

主要参考文献

[1]付姝宏，梁润平. Excel在会计中的应用[M].北京：中国人民大学出版社，2011：49.