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

嚴(yán)李宏

(江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)科學(xué)系，江蘇江陰214405)

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

嚴(yán)李宏

(江陰職業(yè)技術(shù)學(xué)院計(jì)算機(jī)科學(xué)系，江蘇江陰214405)

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

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

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

1 問(wèn)題分析

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

圖1 系統(tǒng)導(dǎo)出的未申報(bào)企業(yè)明細(xì)表

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

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

2 可行方案

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

圖3 合并未申報(bào)項(xiàng)目與所屬時(shí)期表

具體步驟:

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

(2)在E1中輸入列標(biāo)題:未申報(bào)項(xiàng)目與所屬時(shí)期.

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

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

2.1 方案一:分項(xiàng)目多條信息編輯

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

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

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

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

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

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

=合并未申報(bào)項(xiàng)目與所屬時(shí)期!B2＆“:你好!你公司本月有以下稅種未申報(bào):”＆合并未申報(bào)項(xiàng)目與所屬時(shí)期!E2＆“未申報(bào)，請(qǐng)?jiān)诒驹?5日前完成申報(bào)，收到短信請(qǐng)回復(fù).謝謝!無(wú)錫市江陰地方稅務(wù)局.”

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

圖4 分項(xiàng)目多條信息編輯表

2.2 方案二:合并項(xiàng)目單條信息編輯

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

圖5 未申報(bào)項(xiàng)目合并表

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

(2)在D列中根據(jù)C列企業(yè)名稱(chēng)利用函數(shù)對(duì)相應(yīng)企業(yè)的未申報(bào)項(xiàng)目與所屬時(shí)期數(shù)據(jù)進(jìn)行合并.

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

公式分析:

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

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

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

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

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

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

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

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

PHONETIC函數(shù):能夠?qū)⒊償?shù)據(jù)(數(shù)字、日期、時(shí)間)、公式結(jié)果(包括錯(cuò)誤信息)外的所有字符進(jìn)行連接［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，“，”):表示用“，”去替換與重復(fù)出現(xiàn)的C1內(nèi)容相同的企業(yè)名稱(chēng).

從而在D1中結(jié)果為:“，城市維護(hù)建設(shè)稅(所屬時(shí)期2014－06－01/2014－06－30)，地方教育附加(所屬時(shí)期2014－06－01/2014－06－30)，個(gè)人所得稅 (所屬時(shí)期2014－06－01/2014－06 －30)，江蘇地方基金(所屬時(shí)期2014－06－01/ 2014－06－30)，教育費(fèi)附加(所屬時(shí)期2014－06 －01/2014－06－30)，印花稅(所屬時(shí)期2014－06－01/2014－06－30)”.這樣我們基本上完成了對(duì)同一企業(yè)多項(xiàng)未申報(bào)項(xiàng)目與所屬時(shí)期的合并.但內(nèi)容開(kāi)始有一“，”號(hào)我們需加以完善.

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

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

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

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

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

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

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

=未申報(bào)項(xiàng)目合并!C1＆“:你好!你公司本月有以下稅種未申報(bào):”＆未申報(bào)項(xiàng)目合并!E1＆“未申報(bào)，請(qǐng)?jiān)诒驹?5日前完成申報(bào)，收到短信請(qǐng)回復(fù).謝謝!無(wú)錫市江陰地方稅務(wù)局.”

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

方法2:利用VBA編寫(xiě)代碼實(shí)現(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為對(duì)比區(qū)域，a為查找值，b為連接各個(gè)數(shù)之間的符號(hào)

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

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

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ū)域等于查找值，則返回對(duì)比區(qū)域

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

End If

Next i

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

End Function

Sub LK()

End Sub

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

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

2.3 方案總結(jié)

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

3 結(jié)束語(yǔ)

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

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

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

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

［4］李星月.淺析幾種常用的Excel函數(shù)在財(cái)務(wù)管理中的應(yīng)用［J］.中國(guó)管理信息化，2013(7):16－17.

(責(zé)任編輯:王前)

TP31

A

1008－7974(2016)06－0066－04

10.13877/j.cnki.cn22－1284.2016.12.021

2016－10－10

嚴(yán)李宏，男，江蘇江陰人，講師.