VLOOKUP函數(shù)在編制賬簿及報表中的應(yīng)用

梁明

[摘要] VLOOKUP函數(shù)具有查找特定數(shù)值的功能，在日常工作中，特別是在日常的賬務(wù)處理工作中具有較廣泛的應(yīng)用價值。本文對應(yīng)用VLOOKUP函數(shù)編制賬簿及報表的方法進行了探討與闡述，以期能夠為用Excel系統(tǒng)進行賬務(wù)處理的人員提供一種可供參考的賬簿和報表的編制方法。

[關(guān)鍵詞] VLOOKUP函數(shù)；試算平衡表；賬簿；報表

[中圖分類號] F231.4 [文獻標識碼] A [文章編號] 1673 - 0194（2013）06- 0022- 02

VLOOKUP函數(shù)的功能是搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在區(qū)域中的行序號，再進一步返回選定單元格的值，即VLOOKUP函數(shù)具有查找特定數(shù)值的功能。在實際工作中，特別是在日常的賬務(wù)處理工作中，經(jīng)常需要我們查找一些數(shù)據(jù)，VLOOKUP函數(shù)能夠幫助我們快速準確地查找到相關(guān)的內(nèi)容。因此，VLOOKUP函數(shù)在實際工作中具有較廣泛的應(yīng)用價值。本文對應(yīng)用VLOOKUP函數(shù)編制賬簿和報表的方法進行了探討與闡述，以期能夠為用Excel系統(tǒng)進行賬務(wù)處理的人員提供一種可供參考的賬簿和報表的編制方法。

1 VLOOKUP函數(shù)在編制賬簿中的應(yīng)用

本文中，假設(shè)編制賬簿所需數(shù)據(jù)來源于已生成的試算平衡表（見圖1），通過設(shè)計賬簿樣式（本文以總賬為例，見圖2）、完成有關(guān)金額欄目的公式設(shè)置，即可生成相應(yīng)的賬簿（見圖2）。

圖1和圖2中的會計科目都分別設(shè)置了科目代碼和科目名稱，在圖1中把科目代碼設(shè)為首列，其原因在于：在使用VLOOKUP函數(shù)時，要求第一列中的數(shù)值必須按升序排列，如果直接以科目名稱作為首列，升序排列后會破壞會計科目習慣性的排列次序；而把科目代碼作為首列，因為各科目代碼開頭的字母1～6分別代表了資產(chǎn)類、負債類、共同類、所有者權(quán)益類、成本類和損益類，升序排列不會影響會計科目的習慣性排序，因此本文將科目代碼設(shè)置為VLOOKUP函數(shù)查找區(qū)域的首列。

現(xiàn)對總賬（圖2）中相關(guān)單元格公式的設(shè)置進行說明：

1.1 科目代碼有效性設(shè)置[1]

對單元格C1進行科目代碼有效性設(shè)置，其方法步驟如下：①從試算平衡表（圖1）中將科目代碼和科目名稱兩列復制到總賬（圖2）的M、N兩列中；②選擇總賬（圖2）單元格C1，從菜單欄中選擇“數(shù)據(jù)-有效性”，以M列中的科目代碼作為選擇序列的數(shù)據(jù)來源即可完成科目代碼有效性設(shè)置（見圖3）。通過科目代碼有效性設(shè)置可以隨時更新不同會計科目的賬頁。

1.2 其他涉及金額計算的單元格公式的設(shè)置

以圖1、圖2中的數(shù)據(jù)為例，其他涉及金額計算的單元格在進行公式設(shè)置時，都能夠通過VLOOKUP函數(shù)從試算平衡表中直接查找到或經(jīng)過公式分析計算得到相應(yīng)的數(shù)值，有關(guān)單元格公式設(shè)置如下。

1.2.1 期初余額計算公式設(shè)置

J3為設(shè)置期初余額計算公式的單元格，其公式設(shè)置為：

J3=IF（VLOOKUP（C1，試算平衡表！A4：H18，3）-VLOOKUP（C1，試算平衡表！A4：H18，4）>0，VLOOKUP（C1，試算平衡表！A4：H18，3），IF（VLOOKUP（C1，試算平衡表！A4：H18，4）-VLOOKUP（C1，試算平衡表！A4：H18，3）>0，VLOOKUP（C1，試算平衡表！A4：H18，4），0））

此公式設(shè)置的基本原理是，通過VLOOKUP函數(shù)分別查找得到期初借方余額和期初貸方余額的數(shù)值，通過兩數(shù)值相減確定余額在借方或是在貸方。當借方余額減貸方余額大于0，表示余額在借方，顯示期初借方余額數(shù)值；當貸方余額減借方余額大于0，表示余額在貸方，顯示期初貸方余額數(shù)值；如果前兩種情況都不是，余額為0。

1.2.2 期末余額計算公式設(shè)置

J4為設(shè)置期末余額計算公式的單元格，其公式設(shè)置原理同單元格J3。將J3單元格公式中期初借、貸方余額的數(shù)值分別改為期末借、貸方余額的數(shù)值即可完成J4單元公式設(shè)置。其具體計算公式為：

J4=IF（VLOOKUP（C1，試算平衡表！A4：H18，7）-VLOOKUP（C1，試算平衡表！A4：H18，8）>0，VLOOKUP（C1，試算平衡表！A4：H18，7），IF（VLOOKUP（C1，試算平衡表！A4：H18，8）-VLOOKUP（C1，試算平衡表！A4：H18，7）>0，VLOOKUP（C1，試算平衡表！A4：H18，8），0））

1.2.3 期初及期末記賬方向的設(shè)置

單元格I3和單元格I4公式的設(shè)置可分別參考單元格J3和單元格J4的設(shè)置，余額在借方時，單元格顯示“借”；余額在貸方時，單元格顯示“貸”，否則單元格顯示為“平”。

I3=IF（VLOOKUP（C1，試算平衡表！A4：H18，3）-VLOOKUP（C1，試算平衡表！A4：H18，4）>0，"借"，IF（VLOOKUP（C1，試算平衡表！A4：H18，4）-VLOOKUP（C1，試算平衡表！A4：H18，3）>0，"貸"，"平"））

I4=IF（VLOOKUP（C1，試算平衡表！A4：H18，7）-VLOOKUP（C1，試算平衡表！A4：H18，8）>0，"借"，IF（VLOOKUP（C1，試算平衡表！A4：H18，8）-VLOOKUP（C1，試算平衡表！A4：H18，7）>0，"貸"，"平"））

1.2.4 本期發(fā)生額公式設(shè)置

G4和H4單元格用于計算本期借、貸方發(fā)生額。由于本期發(fā)生額不需要判斷記賬方向，因此可以由試算平衡表直接取數(shù)。G4和H4單元格計算公式設(shè)置為：

G4=VLOOKUP（C1，試算平衡表！A4：H18，5）

H4=VLOOKUP（C1，試算平衡表！A4：H18，6）

1.2.5 本期合計相關(guān)單元格公式的設(shè)置

G5=G4；H5=H4；I5=I4；J5=J4

至此，總賬設(shè)置完畢。當科目代碼變動時，系統(tǒng)自動生成與科目相應(yīng)的總賬數(shù)據(jù)。

2 VLOOKUP函數(shù)在編制報表中的應(yīng)用

本文以利潤表的編制為例（見圖4），說明VLOOKUP函數(shù)在報表編制中的應(yīng)用。

圖4中，A列列示了科目代碼，作為查找的對象，在D4單元格中輸入公式“D4=VLOOKUP（A4，試算平衡表！■A■4：■H■18，5）”（見圖4中編輯框所列示公式），利用VLOOKUP函數(shù)即可快捷獲取科目對應(yīng)的本期發(fā)生額數(shù)值。采用自動填充功能即可快速完成其他科目的取數(shù)。

在D列中，某些單元格出現(xiàn)“#N/A”錯誤值顯示，表示在試算平衡表中沒能找到與A列列示內(nèi)容所對應(yīng)的值，所以這些單元格需要手工調(diào)整。如D8單元格計算公式可調(diào)整為“D8=D4-D5-D6+D7”，其他顯示“#N/A”錯誤值的單元格作類似處理。為使表格美觀，利潤表完成表格設(shè)置后可將A列隱藏起來。

用VLOOKUP函數(shù)編制賬簿和報表的優(yōu)點在于利潤表一經(jīng)設(shè)置完畢，以后每期試算平衡表數(shù)據(jù)更新時，利潤表也會自動實時更新，不需要每期進行重新設(shè)置；此外，更主要的是當試算平衡表中科目排序發(fā)生變化的時候，不會影響對各科目相關(guān)數(shù)據(jù)的取數(shù)，因此減少了差錯率的發(fā)生，這也是VLOOKUP函數(shù)在實際工作中得以廣泛應(yīng)用的主要原因。

主要參考文獻

[1]付姝宏，梁潤平. Excel在會計中的應(yīng)用[M].北京：中國人民大學出版社，2011：49.