淺析VLOOKUP 函數(shù)在個(gè)人所得稅計(jì)算中的妙用

王蘭蘭

關(guān)鍵字： VLOOKUP 函數(shù)；個(gè)人所得稅計(jì)算；近似匹配

隨著我國(guó)大數(shù)據(jù)時(shí)代的到來(lái)，職業(yè)教育信息化教學(xué)的普及，教育部也發(fā)布了《教育信息化2.0 行動(dòng)計(jì)劃》。廣東職業(yè)技術(shù)教育學(xué)會(huì)也于2019 年6 月開(kāi)始舉辦教師的“廣東省財(cái)務(wù)數(shù)據(jù)處理邀請(qǐng)賽”。之后廣東省職業(yè)院校學(xué)生專業(yè)技能大賽也新增了“財(cái)務(wù)數(shù)據(jù)處理”項(xiàng)目作為競(jìng)賽內(nèi)容，占總分的20%，其中個(gè)人所得稅計(jì)算又占該項(xiàng)比賽100 分的40%。傳統(tǒng)方式是采用財(cái)務(wù)人員逐個(gè)數(shù)據(jù)人工判斷其累計(jì)應(yīng)納稅所得額的適用預(yù)扣率和速算扣除數(shù)，再進(jìn)行計(jì)算，工作量大且易錯(cuò)。本文采用VLOOKUP 函數(shù)近似匹配，并且根據(jù)出現(xiàn)的臨界點(diǎn)問(wèn)題進(jìn)行優(yōu)化改進(jìn)，巧妙運(yùn)用，可以即準(zhǔn)確又快速找到其適用預(yù)扣率和速算扣除數(shù)，快速計(jì)算出每個(gè)人的準(zhǔn)確個(gè)人所得稅，也希望拋磚引玉，對(duì)此類為題的相關(guān)人員有所幫助。

1 VLOOKUP 函數(shù)語(yǔ)法簡(jiǎn)介

在表格的首列或數(shù)值數(shù)組中搜索值，然后返回表格或數(shù)組中指定列的所在的值, 可使用 VLOOKUP。

如下所示VLOOKUP 函數(shù)各參數(shù)表示為：

= VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

表1 構(gòu)建新的稅率表

= VLOOKUP (要查找的值，包含查找值的區(qū)域，包含返回值的區(qū)域中的列號(hào)，近似匹配 1/TRUE 或完全匹配0/FALSE)。

1.1 VLOOKUP 函數(shù)需注意的問(wèn)題

（1）要查找的值應(yīng)該始終位于所在區(qū)域的第一列，這樣 VLOOKUP 函數(shù)才能正常工作。

（2）如果需要返回值的近似匹配（如果找不到精確匹配值，則返回小于“要查找的值”的最大數(shù)值），可以指定 1 或者TRUE。如果需要返回值的精確匹配，則指定0 或者 FALSE。 如果沒(méi)有指定任何內(nèi)容，默認(rèn)值將始終為 TRUE 或近似匹配。

（3）如果查找值有相同的值，則只能顯示并找到第一個(gè)值。

2 VLOOKUP 函數(shù)近似匹配相應(yīng)的預(yù)扣率和速算扣除數(shù)

2019 年個(gè)人工資薪金所得的稅率表中，累計(jì)應(yīng)納稅所得額在介于兩個(gè)臨界點(diǎn)的之間時(shí)，有不同的稅率和速算扣除數(shù)。對(duì)于這類近似匹配問(wèn)題，完成可以采用VLOOKUP 函數(shù)進(jìn)行檢索，可大大提供工作效率和準(zhǔn)確率。

2.1 構(gòu)建稅率表

按VLOOKUP 函數(shù)的近視匹配原理和實(shí)務(wù)需要，可構(gòu)建稅率表，見(jiàn)表1 左表。并且可定義I2:K9 區(qū)域名稱為“稅率”，方便以后的公式調(diào)用和解讀。

2.2 近似匹配相應(yīng)的預(yù)扣率

如表2 所示，要計(jì)算宋江的累計(jì)應(yīng)納稅額，必須先檢索其累計(jì)應(yīng)納稅所得額匹配的預(yù)扣率和速算扣除數(shù)。本文采用VLOOKUP 函數(shù)近似匹配，在“稅率”名稱區(qū)域第一列中查找，返回小于其累計(jì)應(yīng)納稅所得額的最大臨界點(diǎn)值，顯示相應(yīng)的“稅率”區(qū)域中第2 列的預(yù)扣率的值。具體公式如表2 所示，在E3 單元格中輸入公式：“= VLOOKUP($D3, 稅率,2,1)”，即可自動(dòng)匹配其適用預(yù)扣率。

2.3 近似匹配相應(yīng)的速算扣除數(shù)并計(jì)稅

由于之前采用了混合引用D4 單元格，固定在D 列不變，并且使用名稱“稅率”，故拖動(dòng)E3 公式至F3 單元格，可自動(dòng)復(fù)制公式。修改F3 單元格公式中第三個(gè)參數(shù)值“2”為“3”即可，即“=VLOOKUP($D3,稅率,3,1)”。在G3 單元格中輸入公式：“=D3＊E3-F3”。

這樣基本能匹配常規(guī)的預(yù)扣率和速算扣除數(shù)，并進(jìn)行計(jì)稅。但是對(duì)于臨界點(diǎn)和無(wú)需納稅的累計(jì)應(yīng)納稅所得額還是會(huì)存在問(wèn)題，如上表2 中的盧俊義和吳用的預(yù)扣率和速算扣除數(shù)是不正確的。因此公式還需進(jìn)一步優(yōu)化。

3 回避VLOOKUP 函數(shù)近似匹配臨界點(diǎn)問(wèn)題

2019 年個(gè)人所得稅稅率表中的累計(jì)預(yù)扣預(yù)繳應(yīng)納稅所得額的判斷標(biāo)準(zhǔn)是以不超過(guò)最高臨界點(diǎn)，對(duì)應(yīng)預(yù)扣率和速算扣除數(shù)。比如，盧俊義的累計(jì)應(yīng)納稅所得額36000 元，他的預(yù)扣率應(yīng)該是3%，但是使用VLOOKUP函數(shù)近似匹配，會(huì)自動(dòng)匹配最接近的值，即36000，顯示其對(duì)應(yīng)預(yù)扣率為10%，其速算扣除數(shù)為2520.00，結(jié)果見(jiàn)表2。

如表2 所示，對(duì)于臨界點(diǎn)的數(shù)據(jù)，采用VLOOKUP函數(shù)近似匹配會(huì)存在錯(cuò)誤。實(shí)務(wù)中累計(jì)應(yīng)納稅所得額最多兩位小數(shù)，即角分為止。本文巧用任一單元格輸入0.001，復(fù)制該單元格數(shù)值，然后選中I3:I9 區(qū)域，右擊鼠標(biāo)使用選擇性粘貼的加選項(xiàng)，使臨界點(diǎn)數(shù)值都增加0.001，構(gòu)建新的稅率表，見(jiàn)表3 所示，巧妙回避臨界點(diǎn)近似匹配錯(cuò)誤問(wèn)題。

表2 初試自動(dòng)匹配預(yù)扣率

表3 回避臨界點(diǎn)問(wèn)題方案

4 結(jié)合IFERROR 函數(shù)回避無(wú)需納稅的情況

在個(gè)人累計(jì)應(yīng)納稅所得額小于等于零時(shí)，該個(gè)人是無(wú)需納稅的。同時(shí)VLOOKUP 函數(shù)在“稅率”區(qū)域中也是找不到小于等于零的累計(jì)應(yīng)納稅所得額匹配的相應(yīng)數(shù)據(jù)，這時(shí)函數(shù)會(huì)返回錯(cuò)誤值#N/A。

本文采用IFERROR 函數(shù)和VLOOKUP 函數(shù)嵌套使用，可以完美的解決這一問(wèn)題。即在E3 單元格中輸入公式：“=IFERROR(VLOOKUP($D3, 稅率,2,1),0)”。拖動(dòng)E3 公式至F3，自動(dòng)復(fù)制公式。修改F3 公式為“=IFERROR(VLOOKUP($D3,稅率,3,1),0) ”。這樣就可以回避無(wú)需納稅的情況。

5 結(jié)語(yǔ)

在實(shí)務(wù)工作，類似于個(gè)人所得稅計(jì)算中需要的近似匹配預(yù)扣率和速算扣除數(shù)的問(wèn)題比比皆是。比如匹配不同工齡的年休假天數(shù)、不同等級(jí)的銷售提成計(jì)算，以及一定條件的近似匹配等等都可以使用VLOOKUP 函數(shù)進(jìn)行快速準(zhǔn)確的檢索查詢。筆者認(rèn)為VLOOKUP 函數(shù)還有很多的妙用有待進(jìn)一步的探究。