基于EXCEL函數(shù)的地稅局管理催報短信模板制作與實現(xiàn)*

嚴李宏

(江陰職業(yè)技術學院計算機科學系，江蘇江陰214405)

基于EXCEL函數(shù)的地稅局管理催報短信模板制作與實現(xiàn)*

嚴李宏

(江陰職業(yè)技術學院計算機科學系，江蘇江陰214405)

EXCEL具有強大的數(shù)據(jù)分析與處理能力，靈活運用EXCEL中的內(nèi)置函數(shù)與VBA宏編輯是提高工作效率的關鍵.該文就對在地稅管理工作中遇到的催報催交稅收方面的實際問題加以分析，并基于EXCEL函數(shù)功能提出幾種可行方案.

EXCEL;函數(shù);地稅信息管理

筆者在參與江陰地稅信息化管理工作座談會中，了解到地稅管理人員經(jīng)常要對地稅管理平臺軟件中未按時申報稅收的企業(yè)逐一進行編輯未申報項目短信通知.每個稅收管理員在編輯短信通知工作中做了大量的重復勞動.如果能運用EXCEL的內(nèi)置函數(shù)或宏編輯自定義函數(shù)制作一個短信通知模板(實現(xiàn)短信的自動化編輯)，利用短信平臺進行發(fā)送，那不僅提高了稅收管理員的工作效率，而且大大縮短了通知時間.下面就基于EXCEL函數(shù)探討地稅管理系統(tǒng)中短信通知模板制作的幾種方法.

1 問題分析

實際問題:在地稅管理平臺軟件中，每個稅收管理員都能導出各企業(yè)各個時期未申報的詳細情況(如圖1所示)，并根據(jù)自己所管理的企業(yè)信息表(如圖2所示)，對未申報企業(yè)進行催報催交.若管理員對根據(jù)每個企業(yè)的每個未申報項目進行逐條短信編輯的話工作量非常大.如果利用已導出的EXCEL信息表，進行編輯，制作成短信通知模板，再利用短信平臺進行統(tǒng)一發(fā)送，將能夠較大地提高工作效率.下文就此問題進行了詳細的分析.

圖1 系統(tǒng)導出的未申報企業(yè)明細表

圖2 企業(yè)電話信息表

問題分析:要制作通用的短信通知模板，首先要新建工作表并對相應的表格數(shù)據(jù)進行整理.由于系統(tǒng)導出的未申報企業(yè)明細表中的未申報情況與所屬時期是相對應的，為了使短信內(nèi)容更具體，應包括未申報的項目與所對應的日期.所以，應先將“所屬時期”與“未申報情況”這兩列內(nèi)容加以合并，為區(qū)分原始表數(shù)據(jù)，應新建立工作表(合并未申報項目與所屬時期表、短信編輯表)，再根據(jù)表中納稅人名稱查詢企業(yè)會計電話，對合并的未申報情況與所屬時期內(nèi)容進行編輯.具體方案可歸納為兩類:一類，按企業(yè)的未申報項目與所屬時期情況進行分項目多條信處編輯(即一企如有幾條未申報情況，就有相應的幾條信息提醒);另一類，按企業(yè)納稅人名稱對未申報情況與所屬時期合并至一單元格中，再進行信息編輯，這樣，每個企業(yè)就只有一條未申報項目詳細情況相對應信息提醒.

2 可行方案

前期工作:整理數(shù)據(jù)(合并“未申報情況”與“所屬時期”兩列合并)，如圖3所示.

圖3 合并未申報項目與所屬時期表

具體步驟:

(1)新建“合并未申報項目與所屬時期”表.復制“系統(tǒng)導出的未申報企業(yè)明細”表內(nèi)容，在E列中合并D列與C列內(nèi)容.

(2)在E1中輸入列標題:未申報項目與所屬時期.

(3)在 E2中輸入公式:=D2＆“(“＆$C $1＆C2＆”)”或=D2＆“(“＆”所屬時期“＆C2＆”)”

公式分析:主要是用＆符號將C列與D列內(nèi)容相鏈接.

2.1 方案一:分項目多條信息編輯

(1)在“短信編輯1”工作表A2中輸入公式:

=VLOOKUP(合并未申報項目與所屬時期! B2，電話信息表!$B$2:$C$31，2，1)或

=VLOOKUP(合并未申報項目與所屬時期! A2，電話信息表!A:C，3，F(xiàn)ALSE)

公式分析:vlookup()為縱向查找函數(shù)，它與lookup函數(shù)和hlookup函數(shù)屬于一類，vlookup是按列查找，最終返回該列所需查詢列序所對應的值;與之對應的hlookup是按行查找的［1］.

VLOOKUP(合并未申報項目與所屬時期!A2，電話信息表!A:C，3，F(xiàn)ALSE)主要依據(jù)“合并未申報項目與所屬時期”工作表中稅務管理碼從“電話信息表”中查找到對應企業(yè)的會計電話號碼.

(2)在“短信編輯1”工作表B2中輸入公式:

=合并未申報項目與所屬時期!B2＆“:你好!你公司本月有以下稅種未申報:”＆合并未申報項目與所屬時期!E2＆“未申報，請在本月15日前完成申報，收到短信請回復.謝謝!無錫市江陰地方稅務局.”

公式分析:主要獲取“合并未申報項目與所屬時期”工作表中“未申報項目與所屬時期”內(nèi)容，并利用＆符號連接所需提醒的內(nèi)容完成短信的編輯.如圖4所示.

圖4 分項目多條信息編輯表

2.2 方案二:合并項目單條信息編輯

方法1:利用EXCEL函數(shù)根據(jù)企業(yè)名稱合并本企業(yè)未申報項目詳細情況.如圖5所示.

圖5 未申報項目合并表

(1)根據(jù)前期整理的數(shù)據(jù)工作表:合并未申報項目與所屬時期表，新建未申報項目合并表(A列、B列內(nèi)容分別為合并未申報項目與所屬時期表中的企業(yè)名稱、未申報項目與所屬時期合并內(nèi)容).C列利用數(shù)據(jù)菜單中的刪除重復選項功能實現(xiàn)每個企業(yè)名稱只出現(xiàn)一次(首先，在C列中復制A列內(nèi)容，其次選中C列點擊菜單欄中數(shù)據(jù)——刪除重復選項圖標，在彈出的刪除重復選項警告對話框中選擇以當前選定區(qū)域排序，然后再點擊刪除重復選項按鈕).

(2)在D列中根據(jù)C列企業(yè)名稱利用函數(shù)對相應企業(yè)的未申報項目與所屬時期數(shù)據(jù)進行合并.

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

公式分析:

Countif函數(shù):對指定區(qū)域中符合指定條件的單元格計數(shù)的一個函數(shù)［1］.

COUNTIF(A:A，C1):計算A列中與C1單元格中企業(yè)名稱相同的個數(shù).

MATCH函數(shù):返回指定數(shù)值在指定數(shù)組區(qū)域中的位置［2］.

MATCH(C1，A:A，0):返回了C1單元格中企業(yè)名稱在A列中第一次出現(xiàn)的位置(行號).

INDIRECT函數(shù):返回由文本字符串指定的引用.此函數(shù)立即對引用進行計算，并顯示其內(nèi)容［3］.

INDIRECT(“A”＆MATCH(C1，A:A，0):表示引用了A1單元格，并顯示了A1單元格中的內(nèi)容.

OFFSET函數(shù):是一個引用函數(shù)，表示引用某一個單元格或者區(qū)域［2］.

OFFSET(INDIRECT(“A”＆MATCH(C1，A:A，0))，0，0，COUNTIF(A:A，C1)，2):表示引用了A1: B6這個區(qū)域.

PHONETIC函數(shù):能夠?qū)⒊償?shù)據(jù)(數(shù)字、日期、時間)、公式結果(包括錯誤信息)外的所有字符進行連接［3］.PHONETIC(OFFSET(INDIRECT(“A”＆MATCH(C1，A:A，0))，0，0，COUNTIF(A:A，C1)，2)):表示將A1B1A2B2A3B3A4B4A5B5A6B6各單元格中的內(nèi)容連接在一起.

SUBSTITUTE函數(shù):在某一文本字符串中替換指定的文本［3］.

SUBSTITUTE(PHONETIC(OFFSET(INDIRECT(“A”＆MATCH(C1，A:A，0))，0，0，COUNTIF(A: A，C1)，2))，C1，“，”):表示用“，”去替換與重復出現(xiàn)的C1內(nèi)容相同的企業(yè)名稱.

從而在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)”.這樣我們基本上完成了對同一企業(yè)多項未申報項目與所屬時期的合并.但內(nèi)容開始有一“，”號我們需加以完善.

(4)隱藏 D列內(nèi)容，在 E1中輸入公式:= RIGHT(D1，LEN(D1)－1)

公式分析:利用文本函數(shù)LEN()，獲取D1文本內(nèi)容長度.

再用文本函數(shù)IGHT(D1，LEN(D1)－1)，獲取D1中除第一個“，”號外的所有內(nèi)容.

(5)編輯短信內(nèi)容.

在“短信編輯2”工作表A2中輸入公式:= VLOOKUP(未申報項目合并!C1，電話信息表!B: C，2，0).

公式分析:主要依據(jù)“未申報項目合并”工作表中企業(yè)名稱從“電話信息表”中查找到對應企業(yè)的會計電話號碼.

在“短信編輯2”工作表B2中輸入公式:

=未申報項目合并!C1＆“:你好!你公司本月有以下稅種未申報:”＆未申報項目合并!E1＆“未申報，請在本月15日前完成申報，收到短信請回復.謝謝!無錫市江陰地方稅務局.”

公式分析:主要獲取“未申報項目合并”工作表中企業(yè)所對應所有未申報項目與所屬日期內(nèi)容，并利用＆符號連接所需提醒的內(nèi)容完成短信的編輯.

方法2:利用VBA編寫代碼實現(xiàn)

(1)新建宏LK，在VBAProject中新建模塊1，輸入以下代碼:

Option Explicit

Public Function Link(Fa As Range，Ca As Range，a As Range，b As String)'定義Fa為查找區(qū)域，Ca為對比區(qū)域，a為查找值，b為連接各個數(shù)之間的符號

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

ReDim Preserve LZ(Fa.Cells.Count)'儲存循環(huán)返回的每個LZ ()結果

For i=1 To Fa.Cells.Count'在Fa范圍內(nèi)循環(huán)

If Cells(i+Fa.Row－1，F(xiàn)a.Column)=a Then'如果查找區(qū)域等于查找值，則返回對比區(qū)域

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

End If

Next i

Link=Replace(Application.Trim(Replace(Join(LZ()，“，”)，“，”，“”))，“”，b)'用“，”把生成的數(shù)組連接起來在把多余的“，”去掉

End Function

Sub LK()

End Sub

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

公式分析:IF函數(shù)主要對數(shù)據(jù)條件進行檢測，從而執(zhí)行滿足條件的數(shù)據(jù)計算［4］.在此利用IF函數(shù)判別C1是否為空，如不為空，則調(diào)用已編輯好的函數(shù)Link()進行項目的連接，最終可得到與方法1中E列相同的內(nèi)容.在編輯短信內(nèi)容上方法與方法1相同.

2.3 方案總結

方案一優(yōu)缺點:優(yōu)點在于短信編輯比較容易，對應一個未申報項目就有一條短信提醒.缺點在于如有未申報項目較多的話，對應的短信提醒較多，容易給短信接收者帶來不便. 方案二優(yōu)缺點:優(yōu)點在于一個企業(yè)未申報項目已合并在同一單元格內(nèi)，對于短信模板管理簡潔明了，這樣一個企業(yè)如有未報項目的話，只需一條短信.缺點在于如有未申報項目較多的話，對應的短信內(nèi)容較長，利用手機平臺發(fā)送給企業(yè)會計的話，可能造成一條內(nèi)容分成多條短信.

3 結束語

計算機的廣泛應用為企事業(yè)信息管理人員的數(shù)據(jù)處理工作提供了方便.EXCEL不但提供簡單的數(shù)據(jù)處理功能，還可以根據(jù)需要利用EXCEL函數(shù)及EXCEL VBA設計應用程序解決平時較復雜的問題.本文基于江陰地稅信息管理工作中的稅收催報催交方面的實際問題，運用EXCEL的內(nèi)置函數(shù)與VBA宏命令提出了兩套方案三種方法.這種基于EXCEL的數(shù)據(jù)處理方法極具有操作性，在實際工作中也成效顯著.在一定程度上緩解了地稅稅收管理員的工作壓力，對提高信息處理工作效率、解決實際問題具有實際意義.

［1］常桂英.Excel函數(shù)COUNTIF及VLOOKUP在考勤管理中的應用［J］.現(xiàn)代計算機，2011(5):73－75.

［2］劉洪霞.淺談Excel中MATCH函數(shù)的使用［J］.科技信息，2009(9):596－597.

［3］Excel Home.Excel 2010應用大全［M］.北京:人民郵電出版社，2011.

［4］李星月.淺析幾種常用的Excel函數(shù)在財務管理中的應用［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

嚴李宏，男，江蘇江陰人，講師.