巧用EXCEL建立合同管理臺(tái)帳并動(dòng)態(tài)管理合同

1、巧用 EXCEL建立合同管理臺(tái)賬并動(dòng)態(tài)進(jìn)行管理（杭州市鐵路投資有限公司魏強(qiáng)）【摘要】面對(duì)數(shù)量大、類型多、金額巨、涉及廣、收付數(shù)量頻繁的合同管理要求，要做好合同的日常登記和合同執(zhí)行進(jìn)度的動(dòng)態(tài)更新，建立一個(gè)方便易用的合同管理臺(tái)賬將會(huì)明顯提高合同管理的工作效率。筆者就單位實(shí)際情況用EXCEL編制了具備合同總賬、動(dòng)態(tài)明細(xì)賬、 印花稅計(jì)提等功能與一體合同管理臺(tái)賬，以期與讀者交流?！娟P(guān)鍵詞】合同管理臺(tái)賬 印花稅 Excel眾所周知，微軟OFFICE軟件中的 EXCEL在數(shù)據(jù)管理方面的功能十分強(qiáng)大，隨著EXCEL應(yīng)用的大規(guī)模普及，凡是小型數(shù)據(jù)管理或表格制作方面的工作，在應(yīng)用了EXCEL之后 , 效率得到了大

2、幅提高。然而，有些用戶包括筆者在內(nèi)在剛開始使用EXCEL的時(shí)候，面對(duì)滿是表格線的界面會(huì)顯的一籌莫展，甚至覺得畫表格填數(shù)據(jù)還不如WORD方便，這其實(shí)只是因?yàn)檫@些用戶對(duì)陌生的軟件界面產(chǎn)生的恐懼而造成的。然而，當(dāng)有些用戶看到了EXCEL某些簡(jiǎn)單的計(jì)算功能，如 SUM（）求和、 SUMIF（）條件求和及count （）數(shù)字統(tǒng)計(jì)和 countif() 數(shù)字條件統(tǒng)計(jì)函數(shù)等簡(jiǎn)單函數(shù)在日常數(shù)據(jù)管理工作中的方便應(yīng)用，便對(duì)EXCEL產(chǎn)生了依賴，繼而對(duì)EXCEL發(fā)生了興趣。筆者所在一家大型國(guó)有建設(shè)單位，成立于2006 年末，主要從事城東新城 9.3平方公里范圍內(nèi)的基礎(chǔ)設(shè)施的開發(fā)、建設(shè)和管理，并完成區(qū)域內(nèi)農(nóng)民及居民的

3、拆遷安置和各類安置房的建管工作，尤其是杭州東站東西廣場(chǎng)項(xiàng)目，是筆者單位重點(diǎn)建設(shè)管理的項(xiàng)目之一。在財(cái)務(wù)的日常管理中，合同作為一項(xiàng)重要的日常工作，其合同數(shù)量大、類型多、金額巨、涉及廣、收付數(shù)量頻繁，往往一個(gè)合同執(zhí)行期長(zhǎng)達(dá)幾年，每月均涉及到合同款項(xiàng)的支付。面對(duì)如此繁雜的合同日常管理和合同執(zhí)行進(jìn)度的動(dòng)態(tài)更新要求，稍有不甚，就會(huì)出現(xiàn)合同管理偏差甚至超付的情形。為此，筆者就單位實(shí)際情況用EXCEL 編制了具備合同總賬、執(zhí)行動(dòng)態(tài)明細(xì)賬、印花稅計(jì)提等功能與一體合同管理臺(tái)賬。在此，筆者就編制的思路和具體臺(tái)賬作一介紹，以期與讀者交流并相互提高。一、合同管理臺(tái)賬的設(shè)計(jì)思路（一）通過分別登記合同管理臺(tái)賬總賬與明細(xì)賬，

4、并使之通過“合同號(hào)”這一關(guān)鍵字相互關(guān)聯(lián)，再通過EXCEL的相關(guān)數(shù)據(jù)操作技巧，達(dá)到合同管理可合可分的靈活管理目的，并衍生出計(jì)提合同印花稅的功能。（二）合同管理臺(tái)賬應(yīng)具備的幾項(xiàng)功能：1、合同總賬管理，根據(jù)合同流水號(hào)記錄合同簽訂的日期、標(biāo)的或合同內(nèi)容、相對(duì)方、經(jīng)辦人、合同記載的大致完成日期、合同總金額、已付款金額和占比、合同記載的付款方式、合同類型、印花稅率等；2、合同明細(xì)賬管理，根據(jù)日常合同執(zhí)行進(jìn)度，流水記錄每期支付的合同進(jìn)度款，為簡(jiǎn)化錄入并提高錄入準(zhǔn)確性，該臺(tái)賬的合同信息如合同名稱、相對(duì)方、總金額等均可通過vlookup函數(shù)自動(dòng)填充。3、印花稅計(jì)提，印花稅是以經(jīng)濟(jì)活動(dòng)中簽立的各種合同、產(chǎn)權(quán)轉(zhuǎn)移書

5、據(jù)、營(yíng)業(yè)賬簿、權(quán)利許可證照等應(yīng)稅憑證文件為對(duì)象所征的稅。其中合同作為印花稅計(jì)提的重點(diǎn)，可通過合同管理臺(tái)賬準(zhǔn)確計(jì)提，以減少財(cái)務(wù)人員計(jì)提印花稅的失誤可能。（二）建立相關(guān)表格實(shí)現(xiàn)以上功能需建立四張表格：總賬、明細(xì)賬、印花稅計(jì)提、印花稅率，具體可參見后附案例。二、合同管理臺(tái)賬案例介紹1、建立“總賬”表格，字段為“序號(hào)、合同號(hào)、合同簽訂日期、合同名稱及標(biāo)的、合同相對(duì)方、部門、經(jīng)辦人、合同記載完成日期、合同總價(jià)款、合同已支付金額、合同余款、完成情況、付款方式、備注、合同類型、印花稅率、印花稅額、印花稅所屬時(shí)期”，具體內(nèi)容及公式見表1-1 、1-2 、 1-3 及表后說明。表 1-1總賬左側(cè)部分合同基本信息

6、表 1-2總賬中間部分合同已支付信息及完成情況等表 1-3總賬右側(cè)部分印花稅信息總賬用到的公式（均以每表中的第一行數(shù)據(jù)為例進(jìn)行說明）合同已支付金額=SUMIF(明細(xì)賬 !C:C,=&總賬 !B3, 明細(xì)賬 !G:G)合同余款=I3-J3完成情況=IF(AND(I3=J3,J30),完成 ,IF(J30,J3/I3,)印花稅率=VLOOKUP(O3,印花稅率 !A:AI,2,FALSE)印花稅額=ROUND(I3*P3,2)除以上 5 列內(nèi)容外，其余列均根據(jù)合同實(shí)際情況錄入。2、建立“明細(xì)賬”表格，字段為“序號(hào)、日期、合同號(hào)、合同名稱及標(biāo)的、合同相對(duì)方、合同總金額、本期付款金額、累計(jì)已付款占總金

7、額比例、第幾次付款”，通過這張明細(xì)表 2明細(xì)賬賬，可與總賬以“合同號(hào)”為關(guān)鍵字進(jìn)行關(guān)聯(lián)，將總賬中的合同基本信息引入到明細(xì)賬中，又可將本明細(xì)賬中每個(gè)合同的每筆支付金額自動(dòng)統(tǒng)計(jì)到總賬中，實(shí)現(xiàn)了合同進(jìn)度的實(shí)時(shí)把握，同時(shí)，又可通過“篩選”功能篩選出明細(xì)賬每個(gè)合同共有幾筆支付記錄，實(shí)現(xiàn)總分結(jié)合，靈活查詢的目的。具體見表2 及表后說明。明細(xì)賬用到的公式（均以每表中的第一行數(shù)據(jù)為例進(jìn)行說明）合同名稱及標(biāo)的=VLOOKUP(C3,總賬 !B:AN,3,FALSE)合同相對(duì)方=VLOOKUP(C3,總賬 !B:AN,4,FALSE)合同總金額=VLOOKUP(C3,總賬 !B:AN,8,FALSE)累計(jì)已付款占

8、總金額比例=IF(F3,SUMIF($C$3:C3,=&C3,$G$3:G3)/F3,)第幾次付款=IF(G3,第 &COUNTIF($C$1:C3,=&C3)&次 ,)除以上 5 列內(nèi)容外，其余列均根據(jù)合同實(shí)際情況錄入。3、建立“印花稅率”表，定義印花稅種和稅率，為計(jì)提印花稅準(zhǔn)備。見表3表 3印花稅率4、建立“印花稅計(jì)提”表，字段為“申報(bào)所屬時(shí)期、申報(bào)日期、申報(bào)基數(shù)、類型、稅率、稅額”，通過該表與總賬的兩個(gè)關(guān)鍵字即“申報(bào)所屬時(shí)期”和“類型”進(jìn)行關(guān)聯(lián)，用多條件求和函數(shù)自動(dòng)將總賬中合同金額匯總到本表的“申報(bào)基數(shù)”中，根據(jù)相應(yīng)稅率自動(dòng)算出本月需計(jì)提繳納的各類合同印花稅。詳見表4。表 4印花稅計(jì)提印

9、花稅計(jì)提用到的公式=SUM( 總賬 !$R$3:$R$10000=$A4)*(總賬 !$O$3:$O$10000=$D4)* 總申報(bào)基數(shù)賬 !$I$3:$I$10000)稅率=VLOOKUP(D4,印花稅率 !A:AI,2,FALSE)稅額=ROUND(C4*E4,2)說明： 申報(bào)基數(shù)為多條件求和函數(shù)，外部大括號(hào)并非手工錄入，而需要在錄入公式完成后按ctrl+shift+enter組合鍵完成，該函數(shù)應(yīng)用得當(dāng)將在財(cái)務(wù)工作許多方面提高工作效率。三、合同管理臺(tái)賬中的幾個(gè)重要函數(shù)介紹。1、 vlookup()函數(shù) , 具體用法及參數(shù)說明如下：語法： VLOOKUP(關(guān)鍵字，被查找的數(shù)據(jù)范圍，返回?cái)?shù)據(jù)范

10、圍匹配值的列數(shù)， false或 true) 。示例如下，在明細(xì)賬的D3 單元格填寫： =VLOOKUP(C3,總賬 !B:AN,3,FALSE) 。式中：“ C3 ”代表以明細(xì)賬中C3 合同編號(hào)關(guān)鍵字， “總賬 !B:AN ”代表被搜索范圍為總賬中第B 列到 AN列，其中B 列填寫為合同號(hào)， “ 3 ”則代表返回B 至 AN列中的第3 列單元格即“合同名稱及標(biāo)的”的結(jié)果，false代表精確匹配。2、多條件求和函數(shù)=SUM( 總賬 !$R$3:$R$10000=$A4)*(總賬 !$O$3:$O$10000=$D4)* 總賬!$I$3:$I$10000)這個(gè)函數(shù)在印花稅計(jì)提表中申報(bào)基數(shù)單元格中，其中：總賬!$R$3:$R$10000=$A4代表“總賬”與“印花稅計(jì)提”的“印花稅所屬時(shí)期”相匹配；總賬!$O$3:$O$10000=$D4代表“總賬”與“印花稅計(jì)提”的“合同類型”相匹配；總賬!$I$3:$I$10000代表自動(dòng)求和滿足前兩個(gè)匹配條件的S 列，即“合同總金額” 。值得注意的是，在多條件求和函數(shù)公式錄入完成后，直接回車將不會(huì)顯示正確結(jié)果，必須在公式錄入后按ctrl + shift