基于Excel數(shù)據(jù)源表的記賬憑證打印輸出

張永祿

摘要：在應(yīng)用Excel進(jìn)行會(huì)計(jì)核算的小微企業(yè)，其記賬憑證數(shù)據(jù)以二維表的形式存儲(chǔ)于專門的工作表中。這種存儲(chǔ)方式符合第一范式的要求，因而對(duì)于高效、準(zhǔn)確地進(jìn)行分類計(jì)算，快速生成賬簿文件和編制會(huì)計(jì)報(bào)表極為便利，但也給會(huì)計(jì)核算人員帶來新的技術(shù)難題。將二維袁形式存儲(chǔ)的記賬憑證數(shù)據(jù)，準(zhǔn)確地提取到符合國家標(biāo)準(zhǔn)歸檔格式的記賬憑證打印模板中，供紙質(zhì)打印輸出存檔便是其中之一。通過利用功能相對(duì)獨(dú)立的多個(gè)工作袁函數(shù)配合使用，構(gòu)造打印模板的單元公式，能有效解決這一技術(shù)難題。

關(guān)鍵詞：數(shù)據(jù)源表；數(shù)據(jù)提?。还?；打印模板；會(huì)計(jì)檔案

中圖分類號(hào)：TP37 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2017）18-0201-02

1問題的提出

使用Excel進(jìn)行會(huì)計(jì)核算的小微企業(yè)，其記賬憑證數(shù)據(jù)是存儲(chǔ)在二維表中的，而《會(huì)計(jì)檔案管理辦法》要求輸出符合國家標(biāo)準(zhǔn)歸檔格式的記賬憑證。因此將二維表形式存儲(chǔ)的記賬憑i正數(shù)據(jù)以標(biāo)準(zhǔn)記賬憑證格式打印輸出，以滿足會(huì)計(jì)檔案管理的需要，是這些小微企業(yè)的會(huì)計(jì)核算人員必須解決的技術(shù)難題。在設(shè)計(jì)出符合國家標(biāo)準(zhǔn)歸檔格式的記賬憑證打印模板的基礎(chǔ)上，將多個(gè)工作表函數(shù)配合使用，構(gòu)造單元的“數(shù)據(jù)驗(yàn)證”公式或取數(shù)公式，可解決這一難題。完成公式的構(gòu)造后，可以實(shí)現(xiàn)在指定單元輸入需要打印的記賬憑證編號(hào)后，檢索該編號(hào)是否存在于記賬憑證表中，如果存在，將該憑證編號(hào)對(duì)應(yīng)的日期、附單據(jù)數(shù)、摘要、會(huì)計(jì)科目名稱、借貸方金額提取到打印模板的對(duì)應(yīng)單元中，供打印輸出；如果不存在，則禁止輸入，并彈出錯(cuò)誤提示信息，要求用戶輸入有效的記賬憑證編號(hào)，直至輸入有效的記賬憑證編號(hào)為止。

2數(shù)據(jù)源表（記賬憑證工作表）及打印模板的數(shù)據(jù)結(jié)構(gòu)及格式

小微企業(yè)使用的記賬憑證工作表項(xiàng)目構(gòu)成，應(yīng)符合國家標(biāo)準(zhǔn)歸檔格式的要求。包括憑證編號(hào)、填制日期、附單據(jù)數(shù)、摘要、會(huì)計(jì)科目、借方金額、貸方金額等主要內(nèi)容。記賬憑證工作表的項(xiàng)目構(gòu)成與示例數(shù)據(jù)，以及打印模板格式如圖1、圖2所示。

3處理邏輯及實(shí)現(xiàn)技術(shù)

3.1區(qū)域名稱的定義

為了公式直觀易讀，同時(shí)也便于公式的構(gòu)造，先對(duì)記賬憑證表的數(shù)據(jù)區(qū)域進(jìn)行命名。（本例的數(shù)據(jù)區(qū)域從第3行開始至300行止，日常應(yīng)用中應(yīng)根據(jù)實(shí)際情況確定區(qū)域范圍）

1.將A1單元命名為“年”。

2.將A3：A300區(qū)域命名為“月”。

3.將B3：B300區(qū)域命名為“日”。

4.將C3：C300區(qū)域命名為“憑證編號(hào)”。

5.將D3：D300區(qū)域命名為“摘要”。

6.將F3：F300區(qū)域命名為“科目名稱”。

7.將G3：G300區(qū)域命名為“借方金額”。

8.將H3：H300區(qū)域命名為“貸方金額”。

9.將13：1300區(qū)域命名為“附單據(jù)數(shù)”。

3.2記賬憑證編號(hào)的檢測與“數(shù)據(jù)驗(yàn)證”設(shè)置

1）允許條件公式的構(gòu)造在記賬憑證打印模板的B2單元中輸入記賬憑證編號(hào)時(shí)，需要檢測輸入的編號(hào)在記賬憑證表的C3：C300區(qū)域，即“憑證編號(hào)”區(qū)域中是否存在，如果不存在，則禁止輸入。允許單元數(shù)據(jù)輸入由“數(shù)據(jù)驗(yàn)證”（在Excel2010及之前的版本稱為“數(shù)據(jù)有效性”）功能實(shí)現(xiàn)，通過返回B2單元的數(shù)據(jù)在“憑證編號(hào)”區(qū)域中的位置來檢測輸人的憑證編號(hào)是否存在。在B2單元“數(shù)據(jù)驗(yàn)證”中設(shè)置自定義公式：=NOT（ISNA（MATCH（$B$2，憑證編號(hào)，0）））。該公式先用MATCH函數(shù)檢測B2單元數(shù)據(jù)在“憑證編號(hào)”區(qū)域的位置，然后用ISNA函數(shù)檢測MATCH函數(shù)返回的值是否為“#N/A”，若返回TRUE，說明B2單元數(shù)據(jù)在“憑證編號(hào)”區(qū)域中不存在；若返回FALSE，說明B2單元數(shù)據(jù)在“憑證編號(hào)”區(qū)域中存在。檢測結(jié)果為TRUE時(shí)不允許輸入，為FALSE時(shí)允許輸入，與要求正好相反，因此用NOT函數(shù)對(duì)檢測的結(jié)果求反。

2）錯(cuò)誤提示信息的設(shè)置

將B2單元“數(shù)據(jù)驗(yàn)證”的“出錯(cuò)警告”警告樣式設(shè)置為“停止”，“標(biāo)題”文字設(shè)置為“錯(cuò)誤”，“錯(cuò)誤信息”的文字設(shè)置為“憑證編號(hào)有誤，請核對(duì)后重新輸入！”。達(dá)到禁止輸入無效憑證編號(hào)，且在用戶輸入無效記賬憑證編號(hào)時(shí)彈出提示信息的目的。

3.3單元取數(shù)邏輯與公式的構(gòu)造

由于打印模板數(shù)據(jù)來源于記賬憑證工作表，因此在打印模板的記賬憑證編號(hào)數(shù)據(jù)單元（本例為B2）輸入編號(hào)后，先用MATCH函數(shù)在記賬憑證工作表的記賬憑證區(qū)域（已定義為“憑證編號(hào)”）中定位該編號(hào)第一條記錄的行次，再用INDEX函數(shù)返回該行次對(duì)應(yīng)的其它數(shù)據(jù)項(xiàng)內(nèi)容，完成該編號(hào)第一條打印數(shù)據(jù)的生成。用公式表示：=INDEX（返回指定數(shù)據(jù)項(xiàng)內(nèi)容的區(qū)域，MATCH（$B$2，憑證編號(hào)，0））。

除填制日期“年”、“月”、“日”及“附單據(jù)數(shù)”只需提取指定憑證編號(hào)在“記賬憑證表”對(duì)應(yīng)的第一條記錄外，每一個(gè)記賬憑證編號(hào)都至少對(duì)應(yīng)2條以上的記錄（“有借必有貸，借貸必相等”），所以其它數(shù)據(jù)項(xiàng)在提取第一條記錄后，還需要繼續(xù)檢測下一條記錄的記賬憑證編號(hào)與打印模板B2單元輸入的數(shù)據(jù)是否一致，以確定是否將記錄提取到模板中的相應(yīng)單元。也就是說，若檢測到的憑證編號(hào)與B2單元一致，就提取該條記錄的相關(guān)數(shù)據(jù)到打印模板中，否則返回空。

由于下一條記錄的行次值等于本條記錄的行次值加1，因此每一個(gè)指定的憑證編號(hào)從第2條記錄開始，需在MATCH函數(shù)的計(jì)算結(jié)果后依次加1，2，3……，以保證提取到指定憑證編號(hào)的第2，3，4……條記錄。為在進(jìn)行公式填充時(shí)能動(dòng)態(tài)加數(shù)，可以用獲取行次值的ROW函數(shù)減去打印模板工作表第一條記錄行次值（本例為41的辦法實(shí)現(xiàn)。

3.3.1填制日期及“附單據(jù)數(shù)”的單元公式

1）“年”數(shù)據(jù)單元（D2）公式：=IF（$B$2=""，""，年）。endprint

2）“月”數(shù)據(jù)單元（F2）公式：=IF（$B$2=""，""，INDEX（月，MATCH（$B$2，憑證編號(hào)，0）））。

3）“日”數(shù)據(jù)單元（H2）公式：=IF（$B$2=""，""，INDEx（日，MATCH（$B$2，憑證編號(hào)，0）））。

4）“附單據(jù)數(shù)”數(shù)據(jù)單元（12）公式：=IF（B2=""，""，"附單據(jù)張數(shù)：”&INDEX（附單據(jù)數(shù)，MATCH（$B$2，憑證編號(hào)，0）））。

3.3.2其它數(shù)據(jù)項(xiàng)的單元公式

1）“摘要”數(shù)據(jù)單元（A4～A13）公式：=IF（ISNA（MATCH（$B$2，憑證編號(hào)，0）），""，IFONDEX（憑證編號(hào)，MATCH（$B$2，憑證編號(hào)，0）+ROW（）-4）=$B$2，INDEX（摘要，MATCH（$B$2，憑證編號(hào)，0）+ROW0-4），""））。

2）“科目名稱”數(shù)據(jù)單元（C4～C13）公式：=IF（ISNA（MATCH（$B$2，憑證編號(hào)，0）），""，IF（INDEX（憑證編號(hào)，MATCH（$B$2，憑證編號(hào)，0）+ROW0-4）=$B$2，INDEX（科目名稱，MATCH（$B$2，憑證編號(hào)，0）+ROW0-4），""））。

3）“借方金額”數(shù)據(jù)單元（13～113）公式：=IF（ISNA（MATCH（$B$2，憑證編號(hào)，0）），""，IF（INDEX（憑證編號(hào)，MATCH（$B$2，憑證編號(hào)，0）+ROW（）-4）=$B$2，INDEX（借方金額，MATCH（$B$2，憑證編號(hào)，0）+ROW（）-4），""））。

4）“貸方金額”數(shù)據(jù)單元（J3～J13）公式：=IF（ISNA（MATCH（$B$2，憑證編號(hào)，0）），""，IF（INDEX（憑證編號(hào)，MATCH（$B$2，憑證編號(hào)，0）+ROW（）-4）=$B$2，INDEX（貸方金額，MATCH（$B$2，憑證編號(hào)，0）+ROW（）-4），""））。

上述4個(gè)公式中的表達(dá)式"INDEX（憑證編號(hào)，MATCH（$B$2，憑證編號(hào)，0）+ROW（）-4）=$B$2”用于檢測記賬憑證工作表中的憑證編號(hào)與打印模板的B2單元是否一致；表達(dá)式“ISNA（MATCH（$B$2，憑證編號(hào)，0））”用來檢測是否輸入了記賬憑證工作表中不存在的憑證編號(hào)。

5）“借方金額合計(jì)”數(shù)據(jù)單元（I14）公式：=SUM（I4：I13）。

6）“貸方金額合計(jì)”數(shù)據(jù)單元（J14）公式：=SUM（J4：J13）。

4結(jié)束語

通過MATCH函數(shù)定位指定憑證編號(hào)在記賬憑證表中的位置，進(jìn)而用INDEX函數(shù)返回記賬憑證表中指定憑證編號(hào)對(duì)應(yīng)的各項(xiàng)數(shù)據(jù)，輔之以IF、ISNA、NOT、ROW等工作表函數(shù)進(jìn)行容錯(cuò)處理，在符合標(biāo)準(zhǔn)歸檔格式記賬憑證模板的相應(yīng)單元中進(jìn)行“數(shù)據(jù)驗(yàn)證”設(shè)置或構(gòu)造取數(shù)公式，實(shí)現(xiàn)了基于Excel數(shù)據(jù)源的記賬憑證打印輸出。在滿足高效、準(zhǔn)確地處理會(huì)計(jì)數(shù)據(jù)、產(chǎn)生會(huì)計(jì)信息的同時(shí)，也滿足了紙質(zhì)記賬憑證打印輸出的會(huì)計(jì)檔案管理需求。endprint