王蘭蘭
關(guān)鍵字: VLOOKUP 函數(shù);個人所得稅計算;近似匹配
隨著我國大數(shù)據(jù)時代的到來,職業(yè)教育信息化教學(xué)的普及,教育部也發(fā)布了《教育信息化2.0 行動計劃》。廣東職業(yè)技術(shù)教育學(xué)會也于2019 年6 月開始舉辦教師的“廣東省財務(wù)數(shù)據(jù)處理邀請賽”。之后廣東省職業(yè)院校學(xué)生專業(yè)技能大賽也新增了“財務(wù)數(shù)據(jù)處理”項目作為競賽內(nèi)容,占總分的20%,其中個人所得稅計算又占該項比賽100 分的40%。傳統(tǒng)方式是采用財務(wù)人員逐個數(shù)據(jù)人工判斷其累計應(yīng)納稅所得額的適用預(yù)扣率和速算扣除數(shù),再進行計算,工作量大且易錯。本文采用VLOOKUP 函數(shù)近似匹配,并且根據(jù)出現(xiàn)的臨界點問題進行優(yōu)化改進,巧妙運用,可以即準(zhǔn)確又快速找到其適用預(yù)扣率和速算扣除數(shù),快速計算出每個人的準(zhǔn)確個人所得稅,也希望拋磚引玉,對此類為題的相關(guān)人員有所幫助。
在表格的首列或數(shù)值數(shù)組中搜索值,然后返回表格或數(shù)組中指定列的所在的值, 可使用 VLOOKUP。
如下所示VLOOKUP 函數(shù)各參數(shù)表示為:
= VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
表1 構(gòu)建新的稅率表
= VLOOKUP (要查找的值,包含查找值的區(qū)域,包含返回值的區(qū)域中的列號,近似匹配 1/TRUE 或完全匹配0/FALSE)。
1.1 VLOOKUP 函數(shù)需注意的問題
(1)要查找的值應(yīng)該始終位于所在區(qū)域的第一列,這樣 VLOOKUP 函數(shù)才能正常工作。
(2)如果需要返回值的近似匹配(如果找不到精確匹配值,則返回小于“要查找的值”的最大數(shù)值),可以指定 1 或者TRUE。如果需要返回值的精確匹配,則指定0 或者 FALSE。 如果沒有指定任何內(nèi)容,默認(rèn)值將始終為 TRUE 或近似匹配。
(3)如果查找值有相同的值,則只能顯示并找到第一個值。
2019 年個人工資薪金所得的稅率表中,累計應(yīng)納稅所得額在介于兩個臨界點的之間時,有不同的稅率和速算扣除數(shù)。對于這類近似匹配問題,完成可以采用VLOOKUP 函數(shù)進行檢索,可大大提供工作效率和準(zhǔn)確率。
按VLOOKUP 函數(shù)的近視匹配原理和實務(wù)需要,可構(gòu)建稅率表,見表1 左表。并且可定義I2:K9 區(qū)域名稱為“稅率”,方便以后的公式調(diào)用和解讀。
如表2 所示,要計算宋江的累計應(yīng)納稅額,必須先檢索其累計應(yīng)納稅所得額匹配的預(yù)扣率和速算扣除數(shù)。本文采用VLOOKUP 函數(shù)近似匹配,在“稅率”名稱區(qū)域第一列中查找,返回小于其累計應(yīng)納稅所得額的最大臨界點值,顯示相應(yīng)的“稅率”區(qū)域中第2 列的預(yù)扣率的值。具體公式如表2 所示,在E3 單元格中輸入公式:“= VLOOKUP($D3, 稅率,2,1)”,即可自動匹配其適用預(yù)扣率。
由于之前采用了混合引用D4 單元格,固定在D 列不變,并且使用名稱“稅率”,故拖動E3 公式至F3 單元格,可自動復(fù)制公式。修改F3 單元格公式中第三個參數(shù)值“2”為“3”即可,即“=VLOOKUP($D3,稅率,3,1)”。在G3 單元格中輸入公式:“=D3*E3-F3”。
這樣基本能匹配常規(guī)的預(yù)扣率和速算扣除數(shù),并進行計稅。但是對于臨界點和無需納稅的累計應(yīng)納稅所得額還是會存在問題,如上表2 中的盧俊義和吳用的預(yù)扣率和速算扣除數(shù)是不正確的。因此公式還需進一步優(yōu)化。
2019 年個人所得稅稅率表中的累計預(yù)扣預(yù)繳應(yīng)納稅所得額的判斷標(biāo)準(zhǔn)是以不超過最高臨界點,對應(yīng)預(yù)扣率和速算扣除數(shù)。比如,盧俊義的累計應(yīng)納稅所得額36000 元,他的預(yù)扣率應(yīng)該是3%,但是使用VLOOKUP函數(shù)近似匹配,會自動匹配最接近的值,即36000,顯示其對應(yīng)預(yù)扣率為10%,其速算扣除數(shù)為2520.00,結(jié)果見表2。
如表2 所示,對于臨界點的數(shù)據(jù),采用VLOOKUP函數(shù)近似匹配會存在錯誤。實務(wù)中累計應(yīng)納稅所得額最多兩位小數(shù),即角分為止。本文巧用任一單元格輸入0.001,復(fù)制該單元格數(shù)值,然后選中I3:I9 區(qū)域,右擊鼠標(biāo)使用選擇性粘貼的加選項,使臨界點數(shù)值都增加0.001,構(gòu)建新的稅率表,見表3 所示,巧妙回避臨界點近似匹配錯誤問題。
表2 初試自動匹配預(yù)扣率
表3 回避臨界點問題方案
在個人累計應(yīng)納稅所得額小于等于零時,該個人是無需納稅的。同時VLOOKUP 函數(shù)在“稅率”區(qū)域中也是找不到小于等于零的累計應(yīng)納稅所得額匹配的相應(yīng)數(shù)據(jù),這時函數(shù)會返回錯誤值#N/A。
本文采用IFERROR 函數(shù)和VLOOKUP 函數(shù)嵌套使用,可以完美的解決這一問題。即在E3 單元格中輸入公式:“=IFERROR(VLOOKUP($D3, 稅率,2,1),0)”。拖動E3 公式至F3,自動復(fù)制公式。修改F3 公式為“=IFERROR(VLOOKUP($D3,稅率,3,1),0) ”。這樣就可以回避無需納稅的情況。
在實務(wù)工作,類似于個人所得稅計算中需要的近似匹配預(yù)扣率和速算扣除數(shù)的問題比比皆是。比如匹配不同工齡的年休假天數(shù)、不同等級的銷售提成計算,以及一定條件的近似匹配等等都可以使用VLOOKUP 函數(shù)進行快速準(zhǔn)確的檢索查詢。筆者認(rèn)為VLOOKUP 函數(shù)還有很多的妙用有待進一步的探究。