基于EXCEL函数的地税局管理催报短信模板制作与实现*

严李宏

(江阴职业技术学院计算机科学系，江苏江阴214405)

基于EXCEL函数的地税局管理催报短信模板制作与实现*

严李宏

(江阴职业技术学院计算机科学系，江苏江阴214405)

EXCEL具有强大的数据分析与处理能力，灵活运用EXCEL中的内置函数与VBA宏编辑是提高工作效率的关键.该文就对在地税管理工作中遇到的催报催交税收方面的实际问题加以分析，并基于EXCEL函数功能提出几种可行方案.

EXCEL;函数;地税信息管理

笔者在参与江阴地税信息化管理工作座谈会中，了解到地税管理人员经常要对地税管理平台软件中未按时申报税收的企业逐一进行编辑未申报项目短信通知.每个税收管理员在编辑短信通知工作中做了大量的重复劳动.如果能运用EXCEL的内置函数或宏编辑自定义函数制作一个短信通知模板(实现短信的自动化编辑)，利用短信平台进行发送，那不仅提高了税收管理员的工作效率，而且大大缩短了通知时间.下面就基于EXCEL函数探讨地税管理系统中短信通知模板制作的几种方法.

1 问题分析

实际问题:在地税管理平台软件中，每个税收管理员都能导出各企业各个时期未申报的详细情况(如图1所示)，并根据自己所管理的企业信息表(如图2所示)，对未申报企业进行催报催交.若管理员对根据每个企业的每个未申报项目进行逐条短信编辑的话工作量非常大.如果利用已导出的EXCEL信息表，进行编辑，制作成短信通知模板，再利用短信平台进行统一发送，将能够较大地提高工作效率.下文就此问题进行了详细的分析.

图1 系统导出的未申报企业明细表

图2 企业电话信息表

问题分析:要制作通用的短信通知模板，首先要新建工作表并对相应的表格数据进行整理.由于系统导出的未申报企业明细表中的未申报情况与所属时期是相对应的，为了使短信内容更具体，应包括未申报的项目与所对应的日期.所以，应先将“所属时期”与“未申报情况”这两列内容加以合并，为区分原始表数据，应新建立工作表(合并未申报项目与所属时期表、短信编辑表)，再根据表中纳税人名称查询企业会计电话，对合并的未申报情况与所属时期内容进行编辑.具体方案可归纳为两类:一类，按企业的未申报项目与所属时期情况进行分项目多条信处编辑(即一企如有几条未申报情况，就有相应的几条信息提醒);另一类，按企业纳税人名称对未申报情况与所属时期合并至一单元格中，再进行信息编辑，这样，每个企业就只有一条未申报项目详细情况相对应信息提醒.

2 可行方案

前期工作:整理数据(合并“未申报情况”与“所属时期”两列合并)，如图3所示.

图3 合并未申报项目与所属时期表

具体步骤:

(1)新建“合并未申报项目与所属时期”表.复制“系统导出的未申报企业明细”表内容，在E列中合并D列与C列内容.

(2)在E1中输入列标题:未申报项目与所属时期.

(3)在 E2中输入公式:=D2＆“(“＆$C $1＆C2＆”)”或=D2＆“(“＆”所属时期“＆C2＆”)”

公式分析:主要是用＆符号将C列与D列内容相链接.

2.1 方案一:分项目多条信息编辑

(1)在“短信编辑1”工作表A2中输入公式:

=VLOOKUP(合并未申报项目与所属时期! B2，电话信息表!$B$2:$C$31，2，1)或

=VLOOKUP(合并未申报项目与所属时期! A2，电话信息表!A:C，3，FALSE)

公式分析:vlookup()为纵向查找函数，它与lookup函数和hlookup函数属于一类，vlookup是按列查找，最终返回该列所需查询列序所对应的值;与之对应的hlookup是按行查找的［1］.

VLOOKUP(合并未申报项目与所属时期!A2，电话信息表!A:C，3，FALSE)主要依据“合并未申报项目与所属时期”工作表中税务管理码从“电话信息表”中查找到对应企业的会计电话号码.

(2)在“短信编辑1”工作表B2中输入公式:

=合并未申报项目与所属时期!B2＆“:你好!你公司本月有以下税种未申报:”＆合并未申报项目与所属时期!E2＆“未申报，请在本月15日前完成申报，收到短信请回复.谢谢!无锡市江阴地方税务局.”

公式分析:主要获取“合并未申报项目与所属时期”工作表中“未申报项目与所属时期”内容，并利用＆符号连接所需提醒的内容完成短信的编辑.如图4所示.

图4 分项目多条信息编辑表

2.2 方案二:合并项目单条信息编辑

方法1:利用EXCEL函数根据企业名称合并本企业未申报项目详细情况.如图5所示.

图5 未申报项目合并表

(1)根据前期整理的数据工作表:合并未申报项目与所属时期表，新建未申报项目合并表(A列、B列内容分别为合并未申报项目与所属时期表中的企业名称、未申报项目与所属时期合并内容).C列利用数据菜单中的删除重复选项功能实现每个企业名称只出现一次(首先，在C列中复制A列内容，其次选中C列点击菜单栏中数据——删除重复选项图标，在弹出的删除重复选项警告对话框中选择以当前选定区域排序，然后再点击删除重复选项按钮).

(2)在D列中根据C列企业名称利用函数对相应企业的未申报项目与所属时期数据进行合并.

(3)在D1中输入公式:=SUBSTITUTE(PHONETIC(OFFSET(INDIRECT(“A”＆MATCH(C1，A: A，0))，0，0，COUNTIF(A:A，C1)，2))，C1，“，”)

公式分析:

Countif函数:对指定区域中符合指定条件的单元格计数的一个函数［1］.

COUNTIF(A:A，C1):计算A列中与C1单元格中企业名称相同的个数.

MATCH函数:返回指定数值在指定数组区域中的位置［2］.

MATCH(C1，A:A，0):返回了C1单元格中企业名称在A列中第一次出现的位置(行号).

INDIRECT函数:返回由文本字符串指定的引用.此函数立即对引用进行计算，并显示其内容［3］.

INDIRECT(“A”＆MATCH(C1，A:A，0):表示引用了A1单元格，并显示了A1单元格中的内容.

OFFSET函数:是一个引用函数，表示引用某一个单元格或者区域［2］.

OFFSET(INDIRECT(“A”＆MATCH(C1，A:A，0))，0，0，COUNTIF(A:A，C1)，2):表示引用了A1: B6这个区域.

PHONETIC函数:能够将除纯数据(数字、日期、时间)、公式结果(包括错误信息)外的所有字符进行连接［3］.PHONETIC(OFFSET(INDIRECT(“A”＆MATCH(C1，A:A，0))，0，0，COUNTIF(A:A，C1)，2)):表示将A1B1A2B2A3B3A4B4A5B5A6B6各单元格中的内容连接在一起.

SUBSTITUTE函数:在某一文本字符串中替换指定的文本［3］.

SUBSTITUTE(PHONETIC(OFFSET(INDIRECT(“A”＆MATCH(C1，A:A，0))，0，0，COUNTIF(A: A，C1)，2))，C1，“，”):表示用“，”去替换与重复出现的C1内容相同的企业名称.

从而在D1中结果为:“，城市维护建设税(所属时期2014－06－01/2014－06－30)，地方教育附加(所属时期2014－06－01/2014－06－30)，个人所得税 (所属时期2014－06－01/2014－06 －30)，江苏地方基金(所属时期2014－06－01/ 2014－06－30)，教育费附加(所属时期2014－06 －01/2014－06－30)，印花税(所属时期2014－06－01/2014－06－30)”.这样我们基本上完成了对同一企业多项未申报项目与所属时期的合并.但内容开始有一“，”号我们需加以完善.

(4)隐藏 D列内容，在 E1中输入公式:= RIGHT(D1，LEN(D1)－1)

公式分析:利用文本函数LEN()，获取D1文本内容长度.

再用文本函数IGHT(D1，LEN(D1)－1)，获取D1中除第一个“，”号外的所有内容.

(5)编辑短信内容.

在“短信编辑2”工作表A2中输入公式:= VLOOKUP(未申报项目合并!C1，电话信息表!B: C，2，0).

公式分析:主要依据“未申报项目合并”工作表中企业名称从“电话信息表”中查找到对应企业的会计电话号码.

在“短信编辑2”工作表B2中输入公式:

=未申报项目合并!C1＆“:你好!你公司本月有以下税种未申报:”＆未申报项目合并!E1＆“未申报，请在本月15日前完成申报，收到短信请回复.谢谢!无锡市江阴地方税务局.”

公式分析:主要获取“未申报项目合并”工作表中企业所对应所有未申报项目与所属日期内容，并利用＆符号连接所需提醒的内容完成短信的编辑.

方法2:利用VBA编写代码实现

(1)新建宏LK，在VBAProject中新建模块1，输入以下代码:

Option Explicit

Public Function Link(Fa As Range，Ca As Range，a As Range，b As String)'定义Fa为查找区域，Ca为对比区域，a为查找值，b为连接各个数之间的符号

Dim i As Integer，LZ()'定义i，LZ()

ReDim Preserve LZ(Fa.Cells.Count)'储存循环返回的每个LZ ()结果

For i=1 To Fa.Cells.Count'在Fa范围内循环

If Cells(i+Fa.Row－1，Fa.Column)=a Then'如果查找区域等于查找值，则返回对比区域

LZ(i)=Cells(i+Fa.Row－1，Ca.Column)

End If

Next i

Link=Replace(Application.Trim(Replace(Join(LZ()，“，”)，“，”，“”))，“”，b)'用“，”把生成的数组连接起来在把多余的“，”去掉

End Function

Sub LK()

End Sub

(2)在D1中输入公式:=IF(C1=“”，“”，Link ($A$1:$A$30，$B$1:$B$30，C1，“，”))

公式分析:IF函数主要对数据条件进行检测，从而执行满足条件的数据计算［4］.在此利用IF函数判别C1是否为空，如不为空，则调用已编辑好的函数Link()进行项目的连接，最终可得到与方法1中E列相同的内容.在编辑短信内容上方法与方法1相同.

2.3 方案总结

方案一优缺点:优点在于短信编辑比较容易，对应一个未申报项目就有一条短信提醒.缺点在于如有未申报项目较多的话，对应的短信提醒较多，容易给短信接收者带来不便. 方案二优缺点:优点在于一个企业未申报项目已合并在同一单元格内，对于短信模板管理简洁明了，这样一个企业如有未报项目的话，只需一条短信.缺点在于如有未申报项目较多的话，对应的短信内容较长，利用手机平台发送给企业会计的话，可能造成一条内容分成多条短信.

3 结束语

计算机的广泛应用为企事业信息管理人员的数据处理工作提供了方便.EXCEL不但提供简单的数据处理功能，还可以根据需要利用EXCEL函数及EXCEL VBA设计应用程序解决平时较复杂的问题.本文基于江阴地税信息管理工作中的税收催报催交方面的实际问题，运用EXCEL的内置函数与VBA宏命令提出了两套方案三种方法.这种基于EXCEL的数据处理方法极具有操作性，在实际工作中也成效显著.在一定程度上缓解了地税税收管理员的工作压力，对提高信息处理工作效率、解决实际问题具有实际意义.

［1］常桂英.Excel函数COUNTIF及VLOOKUP在考勤管理中的应用［J］.现代计算机，2011(5):73－75.

［2］刘洪霞.浅谈Excel中MATCH函数的使用［J］.科技信息，2009(9):596－597.

［3］Excel Home.Excel 2010应用大全［M］.北京:人民邮电出版社，2011.

［4］李星月.浅析几种常用的Excel函数在财务管理中的应用［J］.中国管理信息化，2013(7):16－17.

(责任编辑:王前)

TP31

A

1008－7974(2016)06－0066－04

10.13877/j.cnki.cn22－1284.2016.12.021

2016－10－10

严李宏，男，江苏江阴人，讲师.