vlookup函數(shù)在編制賬簿及報表中的應用

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

2、OOKUP函數(shù)；試算平衡表；賬簿；報表 </p><p>　　[中圖分類號] F231.4 [文獻標識碼] A [文章編號] 1673 - 0194（2013）06- 0022- 02 </p><p>　　VLOOKUP函數(shù)的功能是搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在區(qū)域中的行序號，再進一步返回選定單元格的值，即VLOOKUP函數(shù)具有查找特定數(shù)值的功能。在實際工作中，特別是在

3、日常的賬務處理工作中，經(jīng)常需要我們查找一些數(shù)據(jù)，VLOOKUP函數(shù)能夠幫助我們快速準確地查找到相關的內(nèi)容。因此，VLOOKUP函數(shù)在實際工作中具有較廣泛的應用價值。本文對應用VLOOKUP函數(shù)編制賬簿和報表的方法進行了探討與闡述，以期能夠為用Excel系統(tǒng)進行賬務處理的人員提供一種可供參考的賬簿和報表的編制方法。 </p><p>　　1 VLOOKUP函數(shù)在編制賬簿中的應用 </p><p&

4、gt;　　本文中，假設編制賬簿所需數(shù)據(jù)來源于已生成的試算平衡表（見圖1），通過設計賬簿樣式（本文以總賬為例，見圖2）、完成有關金額欄目的公式設置，即可生成相應的賬簿（見圖2）。 </p><p>　　圖1和圖2中的會計科目都分別設置了科目代碼和科目名稱，在圖1中把科目代碼設為首列，其原因在于：在使用VLOOKUP函數(shù)時，要求第一列中的數(shù)值必須按升序排列，如果直接以科目名稱作為首列，升序排列后會破壞會計科目習慣性的

5、排列次序；而把科目代碼作為首列，因為各科目代碼開頭的字母1～6分別代表了資產(chǎn)類、負債類、共同類、所有者權益類、成本類和損益類，升序排列不會影響會計科目的習慣性排序，因此本文將科目代碼設置為VLOOKUP函數(shù)查找區(qū)域的首列。 </p><p>　　現(xiàn)對總賬（圖2）中相關單元格公式的設置進行說明： </p><p>　　1.1 科目代碼有效性設置[1] </p><p>

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

7、gt;<p>　　以圖1、圖2中的數(shù)據(jù)為例，其他涉及金額計算的單元格在進行公式設置時，都能夠通過VLOOKUP函數(shù)從試算平衡表中直接查找到或經(jīng)過公式分析計算得到相應的數(shù)值，有關單元格公式設置如下。 </p><p>　　1.2.1 期初余額計算公式設置 </p><p>　　J3為設置期初余額計算公式的單元格，其公式設置為： </p><p>　　J3

8、=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）） </p><p>　　此公式設置的基本原理是，通過

9、VLOOKUP函數(shù)分別查找得到期初借方余額和期初貸方余額的數(shù)值，通過兩數(shù)值相減確定余額在借方或是在貸方。當借方余額減貸方余額大于0，表示余額在借方，顯示期初借方余額數(shù)值；當貸方余額減借方余額大于0，表示余額在貸方，顯示期初貸方余額數(shù)值；如果前兩種情況都不是，余額為0。 </p><p>　　1.2.2 期末余額計算公式設置 </p><p>　　J4為設置期末余額計算公式的單元格，其公式設

10、置原理同單元格J3。將J3單元格公式中期初借、貸方余額的數(shù)值分別改為期末借、貸方余額的數(shù)值即可完成J4單元公式設置。其具體計算公式為： </p><p>　　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

11、（C1，試算平衡表！A4：H18，7）>0，VLOOKUP（C1，試算平衡表！A4：H18，8），0）） </p><p>　　1.2.3 期初及期末記賬方向的設置 </p><p>　　單元格I3和單元格I4公式的設置可分別參考單元格J3和單元格J4的設置，余額在借方時，單元格顯示“借”；余額在貸方時，單元格顯示“貸”，否則單元格顯示為“平”。 </p><p&

12、gt;　　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，"貸"，"平"）） </p><p>　　I4=IF（VLOOKUP（C1，試算平衡

13、表！A4：H18，7）-VLOOKUP（C1，試算平衡表！A4：H18，8）>0，"借"，IF（VLOOKUP（C1，試算平衡表！A4：H18，8）-VLOOKUP（C1，試算平衡表！A4：H18，7）>0，"貸"，"平"）） </p><p>　　1.2.4 本期發(fā)生額公式設置 </p><p>　　G4和H4單元格

14、用于計算本期借、貸方發(fā)生額。由于本期發(fā)生額不需要判斷記賬方向，因此可以由試算平衡表直接取數(shù)。G4和H4單元格計算公式設置為： </p><p>　　G4=VLOOKUP（C1，試算平衡表！A4：H18，5） </p><p>　　H4=VLOOKUP（C1，試算平衡表！A4：H18，6） </p><p>　　1.2.5 本期合計相關單元格公式的設置 </p&

15、gt;<p>　　G5=G4；H5=H4；I5=I4；J5=J4 </p><p>　　至此，總賬設置完畢。當科目代碼變動時，系統(tǒng)自動生成與科目相應的總賬數(shù)據(jù)。 </p><p>　　2 VLOOKUP函數(shù)在編制報表中的應用 </p><p>　　本文以利潤表的編制為例（見圖4），說明VLOOKUP函數(shù)在報表編制中的應用。 </p>&l

16、t;p>　　圖4中，A列列示了科目代碼，作為查找的對象，在D4單元格中輸入公式“D4=VLOOKUP（A4，試算平衡表！■A■4：■H■18，5）”（見圖4中編輯框所列示公式），利用VLOOKUP函數(shù)即可快捷獲取科目對應的本期發(fā)生額數(shù)值。采用自動填充功能即可快速完成其他科目的取數(shù)。 </p><p>　　在D列中，某些單元格出現(xiàn)“#N/A”錯誤值顯示，表示在試算平衡表中沒能找到與A列列示內(nèi)容所對應的值，所以

17、這些單元格需要手工調(diào)整。如D8單元格計算公式可調(diào)整為“D8=D4-D5-D6+D7”，其他顯示“#N/A”錯誤值的單元格作類似處理。為使表格美觀，利潤表完成表格設置后可將A列隱藏起來。 </p><p>　　用VLOOKUP函數(shù)編制賬簿和報表的優(yōu)點在于利潤表一經(jīng)設置完畢，以后每期試算平衡表數(shù)據(jù)更新時，利潤表也會自動實時更新，不需要每期進行重新設置；此外，更主要的是當試算平衡表中科目排序發(fā)生變化的時候，不會影響對各