VLOOKUP函數在編制賬簿及報表中的應用

梁明

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

[關鍵詞] VLOOKUP函數；試算平衡表；賬簿；報表

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

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

1 VLOOKUP函數在編制賬簿中的應用

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

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

現對總賬（圖2）中相關單元格公式的設置進行說明：

1.1 科目代碼有效性設置[1]

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

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

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

1.2.1 期初余額計算公式設置

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

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））

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

1.2.2 期末余額計算公式設置

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

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 期初及期末記賬方向的設置

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

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 本期發生額公式設置

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

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

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

1.2.5 本期合計相關單元格公式的設置

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

至此，總賬設置完畢。當科目代碼變動時，系統自動生成與科目相應的總賬數據。

2 VLOOKUP函數在編制報表中的應用

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

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

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

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

主要參考文獻

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