Excel 應(yīng)收應(yīng)付款表格

1、excel 應(yīng)收應(yīng)付款表格第1節(jié)、到期示意表的提醒功能 盡管非常用心，非常在意，你也不可能清楚記得每一件事。但電腦還可以自動(dòng)對(duì)重要的事情進(jìn)行提示。比如，到期應(yīng)付款項(xiàng)，可以自動(dòng)填充顏色來(lái)提醒財(cái)務(wù)人員，更方便查看。就上面例子，按照給定日期，自動(dòng)對(duì)將要到期的匯票項(xiàng)填充顏色以提醒使用者，更醒目更直接。步驟01 調(diào)用上例工作表打開(kāi)上例工作表，將文件另存為新文件，刪除不用的f列，同時(shí)刪除g5、g6單元格內(nèi)容，并去掉g6的填充色。步驟02 使用條件格式選中a2:e2單元格區(qū)域，點(diǎn)擊“開(kāi)始”“樣式”“條件格式”新建規(guī)則，填出“新建格式規(guī)則”對(duì)話(huà)框。在“選擇規(guī)則類(lèi)型”中選擇“使用公式確定要設(shè)置格式的單元格”，然

2、后在“編輯規(guī)則說(shuō)明”的編輯框中輸入公式：“=($d2-$g$20)”。步驟03 設(shè)置單元格格式公式輸入完成后，點(diǎn)擊下方的“格式”按鈕，彈出“設(shè)置單元格格式”對(duì)話(huà)框，在“填充”選項(xiàng)卡中，選擇一個(gè)填充顏色，然后按“確定”按鈕返回“條件格式”對(duì)話(huà)框。再次按確定按鈕，完成條件格式的設(shè)置。步驟04 復(fù)制條件格式選中a2:e2單元格區(qū)域，點(diǎn)擊工具欄中的“格式刷”按鈕，當(dāng)光標(biāo)改變形狀，變成空心十字加格式刷的形狀時(shí)，按住shift鍵不放單擊e20單元格，現(xiàn)在，格式全部復(fù)制到a1:e20單元格區(qū)域了。步驟05 高亮區(qū)分的到期提示項(xiàng)現(xiàn)在，條件格式設(shè)置完成了，表中30天內(nèi)到期的記錄項(xiàng)就會(huì)自動(dòng)填充橘黃色顯示出來(lái)，非常

3、醒目。第2節(jié)、承兌匯票到期示意圖 在公司財(cái)務(wù)工作中，長(zhǎng)期會(huì)使用承兌匯票，承兌匯票到期后需要用現(xiàn)金償還。這張到期示意圖，可以清楚顯示到期償還的金額，還有到規(guī)定日期還剩余多少天數(shù)。相關(guān)人員從這張表可以隨時(shí)掌握承兌匯票的現(xiàn)狀。步驟01 新建表格并錄入數(shù)據(jù)啟動(dòng)excel2007新建一個(gè)工作簿，將sheet1改名為“到期示意表”。在第一行單元格輸入標(biāo)題，并完成a2:e20單元格區(qū)域的數(shù)據(jù)錄入。步驟02 輸入給定日期選中h2單元格，輸入給定日期“2009-2-28”，作為到期示意的日期判斷標(biāo)準(zhǔn)。適當(dāng)調(diào)整列寬，保證所有數(shù)據(jù)完整顯示。步驟03 編制到期示意公式選中f2單元格，在編輯欄中輸入公式：“=if(an

4、d(d2-$h$20),d2-$h$2,0)”，按回車(chē)鍵確認(rèn)。將光標(biāo)放在f2單元格的右下角，當(dāng)光標(biāo)變成黑十字形狀時(shí)，按住鼠標(biāo)左鍵不放，向下拖動(dòng)鼠標(biāo)到f20單元格松開(kāi)，就完成了改列公式的復(fù)制。步驟04 到期示意以給定日期為標(biāo)準(zhǔn)，未來(lái)30天內(nèi)到期的匯票記錄顯示剩余天數(shù)，不滿(mǎn)足條件的匯票記錄，如已經(jīng)過(guò)期的則顯示為“0”。步驟05 插入sumif函數(shù)現(xiàn)在，我們來(lái)編制匯總金額的公式。選中h6單元格，點(diǎn)擊“開(kāi)始”編輯符號(hào)邊的下拉列表按鈕其他函數(shù)彈出“插入函數(shù)”對(duì)話(huà)框。在“選擇類(lèi)別”中選擇“數(shù)學(xué)與三角函數(shù)”，在“選擇函數(shù)”列表中選擇“sumif”函數(shù)，點(diǎn)擊確定。步驟06 輸入函數(shù)各項(xiàng)參數(shù)彈出“函數(shù)參數(shù)”對(duì)話(huà)

5、框，將光標(biāo)放到“range”文本框中，用鼠標(biāo)在工作表中選中2:f20單元格區(qū)域，這是條件判斷的區(qū)域。在“criteria”文本框中輸入“0”，這是條件。將光標(biāo)定位到“sum_range”文本框，然后用鼠標(biāo)在工作表中選定e2:e20單元格區(qū)域，這是求和區(qū)域。點(diǎn)擊確認(rèn)按鈕。步驟07 生成匯總金額這時(shí)，h6單元格自動(dòng)生成了公式：“=sumif(f2:f20,0,e2:e20)”，并計(jì)算出匯總金額。知識(shí)點(diǎn)：sumif函數(shù)sumif函數(shù)將根據(jù)指定的條件對(duì)若干個(gè)單元格求和。函數(shù)語(yǔ)法sumif(range,criteria,sum_range)range：為用于條件判斷的單元格區(qū)域。criteria：為確定

6、哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。sum_range：是需要求和的實(shí)際單元格。函數(shù)說(shuō)明只有在區(qū)域中相應(yīng)的單元格符合條件的情況下sum_range中的單元格才可求和。如果忽略了sum_range，則對(duì)區(qū)域中的單元格求和。 本例公式說(shuō)明其各個(gè)參數(shù)值指定sumif函數(shù)從f2:f20單元格區(qū)域查找大于零的記錄，并對(duì)e列中同一行的相應(yīng)單元格的數(shù)值進(jìn)行匯總。f列是已經(jīng)計(jì)算得到的到期剩余天數(shù)，e列是該匯票的金額。通過(guò)這樣的條件求和，即可得到30天內(nèi)即將到期的匯票總金額。步驟08 完善表格表格到此已經(jīng)實(shí)現(xiàn)了功能，現(xiàn)在需要進(jìn)一步美化。設(shè)置表格標(biāo)題的字體、字號(hào)和填充顏色。同時(shí)為了突出顯

7、示給定日期和將到期金額，選中h2和h6單元格，在“設(shè)置單元格格式”中為這兩個(gè)單元格選擇一個(gè)醒目的填充色。表格中的0很影響表格的美觀，可以通過(guò)設(shè)置取消零值的顯示。點(diǎn)擊office按鈕excel選項(xiàng)高級(jí)此工作表的顯示選項(xiàng)去掉復(fù)選項(xiàng)“在具有零值的單元格中顯示零”確定。步驟09 到期提示的另一種提示方法在上面得到期提示表中，到期提示是到給定日期剩余的天數(shù)。如果你不想看到還有多少天，而只是需要一個(gè)更明顯的字樣，提示到期了。那么，你可以通過(guò)下面的設(shè)置實(shí)現(xiàn)。選擇f2:f20單元格區(qū)域，點(diǎn)擊鼠標(biāo)右鍵彈出快捷菜單，選擇“設(shè)置單元格格式”數(shù)字自定義，然后在右邊的“類(lèi)型”列表中選擇“0到期;g/通用格式”，點(diǎn)擊確定

8、。步驟10 到期提示現(xiàn)在，可以看到到期提示發(fā)生了變化。喜歡哪一種方法，全憑個(gè)人喜歡。第3節(jié)、應(yīng)付款圖表制作 對(duì)于財(cái)務(wù)人員來(lái)說(shuō)，數(shù)字是有魔力的，搭建出一座財(cái)務(wù)的高塔，非常神奇。但對(duì)于非財(cái)務(wù)人員來(lái)說(shuō)，數(shù)字卻是枯燥的、乏味的、死板的。表哥、表姐辛苦匯總而來(lái)的表格對(duì)于某些人來(lái)說(shuō)，有種遙遠(yuǎn)而陌生的感覺(jué)，但圖表卻不同，形象且直觀地反應(yīng)出一些客觀事實(shí)，對(duì)于那些對(duì)數(shù)字不感冒的人，圖表是拉近距離非常好的表現(xiàn)形式。excel就有非常豐富的圖表繪制工具，通過(guò)各種幾何圖形和色彩傳達(dá)著信息。下面，我們就以應(yīng)付款明細(xì)表為例，為大家講解圖表的繪制。步驟01 指定數(shù)據(jù)源選中a3單元格，按住shift鍵盤(pán)不放，再點(diǎn)擊b12單元

9、格，就選中了用于繪圖的數(shù)據(jù)。步驟02 柱形圖選擇“插入”“圖表”“柱形圖”“簇狀柱形圖”，也就是柱形圖的第一個(gè)。步驟03 標(biāo)準(zhǔn)的柱形圖這時(shí)，excel自動(dòng)生成了一個(gè)標(biāo)準(zhǔn)的簇狀柱形圖，樣式也非常簡(jiǎn)單。仔細(xì)觀察，會(huì)發(fā)現(xiàn)excel上方快捷按鈕變成了圖表工具。步驟04 修改圖表樣式在圖表工具的“圖表樣式”中，我們可以對(duì)柱狀圖的顏色和形狀進(jìn)行選擇，這里我們選擇了深紫色且柱子呈立體效果的“樣式30”。步驟05 調(diào)整柱體尺寸在圖表工具格式大小將柱體的尺寸修改為22厘米。步驟06 設(shè)置坐標(biāo)軸格式在圖表工具布局坐標(biāo)軸，對(duì)橫豎坐標(biāo)軸的線(xiàn)條顏色、線(xiàn)型、對(duì)齊方式等進(jìn)行簡(jiǎn)單設(shè)置。步驟07 添加數(shù)據(jù)標(biāo)簽用鼠標(biāo)點(diǎn)擊圖標(biāo)中的

10、柱子，這個(gè)時(shí)候，所有柱子的四角就多了一些小圓圈。將光標(biāo)放到小圓圈上，單擊鼠標(biāo)右鍵，彈出快捷菜單。在快捷菜單中點(diǎn)擊“添加數(shù)據(jù)標(biāo)簽”，現(xiàn)在，每個(gè)柱子的頭頂就多了一行數(shù)據(jù)。這正是我們想要的直觀感覺(jué)。步驟08 修改數(shù)據(jù)列名稱(chēng) 在步驟七的右鍵菜單中，點(diǎn)擊“選擇數(shù)據(jù)源”，點(diǎn)擊“圖例項(xiàng)（系列）”中的“編輯”按鈕，彈出“編輯數(shù)據(jù)系列”對(duì)話(huà)框，在系列名稱(chēng)中，將“系列1”修改為“應(yīng)付金額”，點(diǎn)擊確定按鈕。步驟09 刪除圖表標(biāo)題現(xiàn)在，圖表的正上方多了一個(gè)標(biāo)題“應(yīng)付金額”，這個(gè)標(biāo)題是我們所不需要的，雙擊標(biāo)題選中該標(biāo)題，直接將文字刪除即可。步驟10 調(diào)整圖表位置在圖標(biāo)上點(diǎn)擊鼠標(biāo)右鍵，選擇“設(shè)置繪圖區(qū)格式”，在彈出的對(duì)話(huà)

11、框中，可以為繪圖區(qū)設(shè)置填充色等。現(xiàn)在，圖表已經(jīng)完成了，用鼠標(biāo)拖動(dòng)到合適的位置，還可以對(duì)圖表的寬度和高度進(jìn)行調(diào)整。第4節(jié)、公司應(yīng)付款表制作 作為一個(gè)有信譽(yù)的公司，付款及時(shí)，不拖不賴(lài)是很重要的。但付款的同時(shí)要考慮公司現(xiàn)金流的問(wèn)題，什么時(shí)候付，付多少都需要財(cái)務(wù)人員為公司提供數(shù)據(jù)支持?，F(xiàn)在，有一家公司有10個(gè)供貨商應(yīng)付款，金額不等。公司計(jì)劃近期付款，并有兩種不同的付款方案。方案一：小于或等于1000元的賬戶(hù)一次性付清，大于1000元的賬戶(hù)償付應(yīng)付金額的50%。方案二：小于或等于3000元的賬戶(hù)一次性付清，大于3000元的賬戶(hù)償還應(yīng)付金額的40%?，F(xiàn)在公司需要財(cái)務(wù)人員制作一份表格，按照兩種方案給出支付明

12、細(xì)，并計(jì)算總還款金額。步驟01 新建工作表啟動(dòng)excel2007創(chuàng)建新的工作簿，將sheet1工作表改名為“應(yīng)付款明細(xì)”并保存。在a1單元格輸入“應(yīng)付款明細(xì)表”，在a2:d2單元格區(qū)域輸入標(biāo)題名稱(chēng)。步驟02 錄入數(shù)據(jù)并求和將供貨單位、應(yīng)付金額按照實(shí)際情況一一錄入。在a14輸入“合計(jì)”，在b14編制求和公式。選中b14單元格，在編輯欄中輸入求和公式：“=sum(b3:b12)，按回車(chē)鍵確認(rèn)。步驟03 編制方案一的公式現(xiàn)在需要編制公式實(shí)現(xiàn)方案一得功能：小于或等于1000元的賬戶(hù)一次性付清，大于1000元的賬戶(hù)首次支付應(yīng)付金額的50%。選中c3單元格，在編輯欄中輸入公式：“=if(b3=1000,b

13、3,round(b3*50%,2)”，按回車(chē)鍵確認(rèn)。選中c3單元格，將光標(biāo)放在單元格右下角，當(dāng)光標(biāo)變成黑十字形狀時(shí)，按住鼠標(biāo)左鍵不放，向下拖動(dòng)鼠標(biāo)到c12松開(kāi)，完成c4:c12單元格區(qū)域的公式復(fù)制。步驟04 方案一求和選中c13單元格，在編輯欄中輸入公式：“=sum(c3:c12)，按回車(chē)鍵確認(rèn)。現(xiàn)在，方案一的應(yīng)付款明細(xì)，和公司首付款的總金額就出來(lái)了。步驟05 編輯方案二的公式選中d3單元格，在編輯欄中輸入公式：“=if(b3$i$1,否,是”，按回車(chē)鍵確認(rèn)。將光標(biāo)放在i3的右下角，當(dāng)光標(biāo)變成黑十字形狀時(shí)，按住鼠標(biāo)左鍵不放，向下拖動(dòng)鼠標(biāo)到i10單元格松開(kāi)，完成該列公式的復(fù)制。知識(shí)點(diǎn)：if函數(shù)i

14、f函數(shù)用來(lái)執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值返回不同的結(jié)果。函數(shù)語(yǔ)法if(logical_test,value_if_true,value_if_false)logical_test：表示計(jì)算結(jié)果為true或false的任意值或表達(dá)式。value_if_true：表示logical_test為true時(shí)返回的值。value_if_false：表示logical_test為false時(shí)返回的值。函數(shù)說(shuō)明if函數(shù)最多可以嵌套7層，使用value_if_true及value_if_false參數(shù)可以構(gòu)造復(fù)雜的檢測(cè)條件。在計(jì)算參數(shù)value_if_false及value_if_true后，if函數(shù)返回相應(yīng)語(yǔ)句執(zhí)行后的返回值。如果if函數(shù)的參數(shù)包含數(shù)組，在執(zhí)行kf語(yǔ)句時(shí)數(shù)組中的每一個(gè)元素都將被計(jì)算。步驟06 未到期金額計(jì)算選中j3單元格，在編輯欄中輸入公式：“=if($i$1-$h