怎样用Excel做账——第七节对进项税信息的统计

来源：网络收集点击：时间：2024-03-01

【导读】：

在“怎样用Excel做账——第六节增值税申报表设置”中，我们知道了怎样用Excel自动生成增值税的主表、附表一和附表二。在申报时，我们可以参考每月抄报税时打印出的开票信息，那么，怎样才能得到进项税信息呢，固然可以通过认证后月底打印的认证清单，但它满足不了我们进一步的需要。我们还要对进项票据进行进一步的统计，以便准确地申报增值税，除此外，还可以用统计出的信息与原料供应商进行对账，与银行支出款项进行对账。方法/步骤1/4分步阅读

第一步，添加“发票”工作表。

（1）添加新工作表。在打开的Excel工作簿中，用鼠标选择菜单（即左键单击）“插入”--“工作表”（如下图一），这时Excel就会在打开的工作簿中添加一个空白工作表（比如“sheet3”），并打开。

（2）重命名新工作表。下图二中的sheet3就是新添加的工作表名称，刚开始添加时，我们知道是为了对进项票据进行统计才添加的这个表，3个月或半年后，当这个工作簿添加了很多的工作表后，你还一眼能看出哪一个工作表是用来对进项票据进行统计的吗？

因此，有必要把工作表的名称改为自己能一眼就能看明白的名称，具体操作方法如下：

把光标移动到工作簿的左下角新添加的工作表标签处，按右键，在弹出的快捷菜单中选择“重命名”（见下图二），这时被命名的工作表标签名称会处以选中状态，以黑底白字显示，等待你的修改操作（如下图三），这时我们就可以直接输入文字“发票”，按回车键完成重命名操作。

2/4

第二步，设置“进项税企业名录”工作表。

（1）添加“进项税企业名录”工作表。用与第一步同样的方法，添加另一个新工作表，并命名为“进项税企业名录”（如下图一），便于我们对原料供应商进行管理和引用。

（2）设置“进项税企业名录”工作表。本表是对与本企业有业务往来的原料供应商的信息采集。采集依据是对方开给本企业的进项发票，采集项目包括对方的单位名称、税号、开户行、地址、联系电话等，以方便管理和引用。

在第一行由左到右分别在单元格A1、B1、C1、D1……输入“序号“、”单位名称“、”税号“、”开户行“……

输入完毕后，选定第一行的行标”1“（即用左键单击第一行左侧的数字1），选定后，把鼠标移到Excel上方的工具栏，找到居中图标后单击（如下图二），已输入的”序号“、”单位名称“……会被置于各自单元格的中间，这样看起来会美观一些，当然也可以不设置

（3）采集原材料供应商数据。当原材料供应商开来专项发票时，我们可以据此把此企业的信息录入到设置好的工作表中（如下图一）。

采集信息的工作不可能一蹴而就，需要在对方开来进项票时进行，工作量不大。

3/4

第三步，设置“发票”工作表。

在统计进项税票据信息时，我们只录入供应商企业的名称、票据中显示的金额、税额和合计。

1、”单位“设置。单位指的是供应商名称。如果是按照进项票的单位进行打字输入，一是加大了工作量，二是容易出错。这时引用“进项税企业名录”表中的单位名称就成了减轻工作量的巧妙办法。有人会说，难道名录表中的单位名称不需要录入吗？其实，企业的供应商一般都是固定的，在每个企业第一次开票进来，录入进名录表后，第二次以及以后的都不用录入了，无论是名录表还是录入发票的工作表中，都不用再录入了。不录入怎么办？引用呀！

设置单位的目的，是要达到下列效果：输入该单位在名录表中对应的序号，该单位的名称自动填写。公式设置后，每月的操作是相同的，以录入3月份进项税票为例，在单元格T3中输入公式如下：

=IF(S3,INDIRECT(进项税企业名录!BMATCH(S3,进项税企业名录!$A:$A,0)),T2)

在实际录入时，第一张票看了单位后知道其序号是6，就在S3中输入“6”，本行“单位”列T3中自动引用“进项税企业名录”表中对应的供应商企业的全称。

第二张进项票，其单位与第一张相同，选定T3单元格，把鼠标移到该单元格的右下角，当光标变成黑“十”字框（即填充柄）时，按住左键向下拖动，公式将自动填充到拖动经过的单元格，可以看到这些填充过的单元格显示的名称与第一张的相同，除非你在前面输入了不同的序号，名称才变得不同。因为第二张与第一张相同，所以就连序号“6”这个数字也不用输了，直接输入金额和税额就行了。

最后一张进项票，其单位与上一张相同（见下图一），选定T18，按组合键Ctrl+D向下填充，T18被自动填入公式，内容显示为“单位2”。

2、“金额”设置。直接按票据上的金额输入即可。

3、“税额”设置。直接按票据上的税额输入即可。

金额和税额从认证文档中导出来也是可以的，这样就免去了输入的麻烦。

4、合计设置。此处的合计，指的是对一张进项票据上金额和税额的合计。合计＝金额+税额。

公式设置的具体操作是：选定单元格X3，输入下列公式（如下图二）：

=W3+V3

输入完毕按回车键确认。

设置了公式以后，当我们输入完金额和税额时，合计数会自动计算并显示出来，与票据上的合计数核对后，可以知道金额和税额是否输入得正确。

5、分企业分类别合计设置。这个合计，是对本月所有单位和类别的进项税票的合计，也是分企业分类别的合计。分企业分类别合计有三个目的，一是为与申报表附表二核对提供依据；二是为做会计分录提供数据，因为进项税票有关于原材料购进的，有关于固定资产购进的，有为管理费用支付的……业务不同，分录也不同，分企业分类别合计可以满足要求；三是为与相关企业对帐提供数据；四是与银行对账时提供数据，与分企业支付的银行存款核对。这个分企业分类别合计的设置在下面第五步详细说明。

4/4

第四步，分企业分类别合计设置。

这个合计设置有两层意思：一是分企业合计设置，此设置是指对当月所有进项票所涉及的开票企业进行合计设置，以便统计出各企业当月对本企业所开票的总和，包括金额、税额、张数。这个设置相对复杂，本步作重点介绍；二是分类别合计设置，是指对当月进项票分原材料、低值易耗品、固定资产等类别进行合计设置。这个设置相对简单，就是直接用输入公式“＝sum()”,然后用鼠标选择要合计类别的全部数据即可。

分企业合计，就要依据不同的企业名称进行合计。因为在一个月内一个企业向本企业开的进项票不止一张，所以有必要进行合计。具体合计设置的方法如下：

1、提取企业名称。既然是合计，在下方的合计栏区域U60:Y72中（见下图一），各企业名称中出现一次，无论这个企业本月开了几张票，就只占一行。有了企业名称，我们可以根据企业名称进行条件求和。所以，从上方数据区中，把很多重复的、杂乱无章的企业名称全部自动提取出来，既不遗漏也不重复，是很不容易的。

在这里用数组公式可以实现这个神奇的效果。在单元格U60输入下列公式：

＝INDEX(T:T,MIN(IF(COUNTIF(U$56:U59,T$3:T$30),4^8,ROW($3:$30))))

输入完毕后，按Ctrl+Shift+Enter组合键进行确认。这时，系统自动在公式两侧加入大括号，形式如下：

{＝INDEX(T:T,MIN(IF(COUNTIF(U$56:U59,T$3:T$30),4^8,ROW($3:$30))))}

注意:两端的大括号是系统自动加上的，不是手工输入的！

然后向下拖动填充柄，填充公式。

讲一下上述公式的运行过程（初学者可跳过下述内容），先说第一个企业的提取：

（1）首先运行的是条件计数函数Countif()。它根据T$3:T$30区域中的数值（即企业名称），在单元格区域U$56:U59中进行查找并计数，找不到，返回0。找到的则返回65536，这是Excel2003的最大行标。

（2）Countif()函数的返回值0，作为条件函数IF的条件参数，使得IF函数直接返回条件为假时的参数ROW($3:$30)，即返回下述一列数，3、4、5、……30。

（3）这一列数尤如接力棒，此刻被传到Min()函数手里，它的职责是从给定的参数中找到最小的那一个，并返回那个最小的值。此处返回的显然是3。

（4）索引函数Index（）闪亮登场，它有两个参数，分别从纵横两个方面指定位置，而Index（）的作用就是找到纵横交叉点处的数据并返回。此处返回的是交叉点处T3单元格的数据。

再说第二个企业的提取（初学者可跳过下述内容）：

此时公式变了（是向下拖动填充时，系统自动变化的，不需要人工干预），形式如下（如下图一）：

＝{INDEX(T:T,MIN(IF(COUNTIF(U$56:U60,T$3:T$30),4^8,ROW($3:$30))))}

（1）首先运行的依然是条件计数函数Countif()。它根据T$3:T$30区域中的数值（即企业名称），在单元格区域U$56:U60中进行查找并计数，找不到，返回0。找到的（比如第一个企业）则返回1(如下图二)。这样，Countif()就返回了一列由1和0组成的数组，其中1代表提取出的第一个企业，0表示未提取出的企业。数组共28个数，或是1，或是0，因为我们引用的是从第3行到第30行共28行的原始数据。

（2）其次运行的是条件函数If。Countif()函数的返回值，作为条件函数IF的条件参数，28个数中，是0的，由IF函数直接返回条件为假时的参数ROW($3:$30)，即返回相应的行标；是1的，返回65536，即4的8次方。返回值也是一列数，也是28个数，或是65536，或是相应的行次(如下图三)。

（3）这一列数尤如接力棒，此刻被传到Min()函数手里，它的职责是从给定的参数中找到最小的那一个，并返回那个最小的值。所有的65536这个数，即已经提取出的企业所对应的数，肯定不是最小的，都被毫不留情地过滤掉了，没有彷徨，没有犹豫，也没有悲伤；返回的是那个排在第二的最小行次，一切尽在不言中，不曾心动，不曾钟情，也不曾欣喜(如下图三)。

（4）索引函数Index（）粉墨登场，它有两个参数，分别从纵横两个方面指定位置，而Index（）的作用就是找到纵横交叉点处的数据并返回。此处返回的是T列和第二个企业所对应的最小行次交叉处单元格的数据(如下图四)。

再说第N个企业的提取：参见第二个企业的提取。

2、计算票据张数。有了既不重复也不遗漏的企业名称，接下来的工作就好做了，要计算每个企业当月向本企业所开进项税票据的张数，只需要使用条件计数公式即可。在单元格V60中输入下列公式(如下图五)：

＝COUNTIF(T$3:T$30,U60)

按回车键确认输入后，向下拖动填充柄填充公式。

3、计算金额。使用多条件求和公式。在单元格W60中输入下列公式(如下图六)：

＝SUMPRODUCT((T$3:T$30=U60)*V$3:V$30)

按回车键确认输入后，向下拖动填充柄填充公式。

4、计算税额。与计算金额相同，用多条件求和公式。在单元格X60中输入下列公式：

=SUMPRODUCT((T$3:T$30=U60)*W$3:W$30)

按回车键确认输入后，向下拖动填充柄填充公式。

5、计算合计数。