財務(wù)人員實戰(zhàn)Excel之三---------應(yīng)收應(yīng)付款表格(未完，接下章)
第1節(jié)、到期示意表的提醒功能
第2節(jié)、承兌匯票到期示意圖
第3節(jié)、應(yīng)付款圖表制作
第4節(jié)、公司應(yīng)付款表制作
第5節(jié)、公司應(yīng)收款表制作
第3章 Excel 應(yīng)收應(yīng)付款表格
第1節(jié)、到期示意表的提醒功能
盡管非常用心，非常在意，你也不可能清楚記得每一件事。但電腦還可以自動對重要的事情進行提示。比如，到期應(yīng)付款項，可以自動填充顏色來提醒財務(wù)人員，更方便查看。
就上面例子，按照給定日期，自動對將要到期的匯票項填充顏色以提醒使用者，更醒目更直接。
步驟01 調(diào)用上例工作表
打開上例工作表，將文件另存為新文件，刪除不用的F列，同時刪除G5、G6單元格內(nèi)容，并去掉G6的填充色。
步驟02 使用條件格式
選中A2:E2單元格區(qū)域，點擊“開始”→“樣式”→“條件格式”→新建規(guī)則，填出“新建格式規(guī)則”對話框。在“選擇規(guī)則類型”中選擇“使用公式確定要設(shè)置格式的單元格”，然后在“編輯規(guī)則說明”的編輯框中輸入公式：“=($D2-$G$2<=30)*($D2-$G$2>0)”。
步驟03 設(shè)置單元格格式
公式輸入完成后，點擊下方的“格式”按鈕，彈出“設(shè)置單元格格式”對話框，在“填充”選項卡中，選擇一個填充顏色，然后按“確定”按鈕返回“條件格式”對話框。再次按確定按鈕，完成條件格式的設(shè)置。
步驟04 復(fù)制條件格式
選中A2:E2單元格區(qū)域，點擊工具欄中的“格式刷”按鈕，當(dāng)光標(biāo)改變形狀，變成空心十字加格式刷的形狀時，按住Shift鍵不放單擊E20單元格，現(xiàn)在，格式全部復(fù)制到A1:E20單元格區(qū)域了。
步驟05 高亮區(qū)分的到期提示項
現(xiàn)在，條件格式設(shè)置完成了，表中30天內(nèi)到期的記錄項就會自動填充橘黃色顯示出來，非常醒目。
第2節(jié)、承兌匯票到期示意圖
在公司財務(wù)工作中，長期會使用承兌匯票，承兌匯票到期后需要用現(xiàn)金償還。這張到期示意圖，可以清楚顯示到期償還的金額，還有到規(guī)定日期還剩余多少天數(shù)。相關(guān)人員從這張表可以隨時掌握承兌匯票的現(xiàn)狀。
步驟01 新建表格并錄入數(shù)據(jù)
啟動Excel2007新建一個工作簿，將Sheet1改名為“到期示意表”。在第一行單元格輸入標(biāo)題，并完成A2:E20單元格區(qū)域的數(shù)據(jù)錄入。
步驟02 輸入給定日期
選中H2單元格，輸入給定日期“
步驟03 編制到期示意公式
選中F2單元格，在編輯欄中輸入公式：“=IF(AND(D2-$H$2<=30,D2-$H$2>0),D2-$H$2,0)”，按回車鍵確認。將光標(biāo)放在F2單元格的右下角，當(dāng)光標(biāo)變成黑十字形狀時，按住鼠標(biāo)左鍵不放，向下拖動鼠標(biāo)到F20單元格松開，就完成了改列公式的復(fù)制。
步驟04 到期示意
以給定日期為標(biāo)準(zhǔn)，未來30天內(nèi)到期的匯票記錄顯示剩余天數(shù)，不滿足條件的匯票記錄，如已經(jīng)過期的則顯示為“0”。
步驟05 插入SUMIF函數(shù)
現(xiàn)在，我們來編制匯總金額的公式。選中H6單元格，點擊“開始”→編輯→Σ符號邊的下拉列表按鈕→其他函數(shù)→彈出“插入函數(shù)”對話框。在“選擇類別”中選擇“數(shù)學(xué)與三角函數(shù)”，在“選擇函數(shù)”列表中選擇“SUMIF”函數(shù)，點擊確定。
步驟06 輸入函數(shù)各項參數(shù)
彈出“函數(shù)參數(shù)”對話框，將光標(biāo)放到“Range”文本框中，用鼠標(biāo)在工作表中選中2:F20單元格區(qū)域，這是條件判斷的區(qū)域。在“Criteria”文本框中輸入“">0"”，這是條件。將光標(biāo)定位到“Sum_range”文本框，然后用鼠標(biāo)在工作表中選定E2:E20單元格區(qū)域，這是求和區(qū)域。點擊確認按鈕。
步驟07 生成匯總金額
這時，H6單元格自動生成了公式：“=SUMIF(F2:F20,">0",E2:E20)”，并計算出匯總金額。
知識點：SUMIF函數(shù)SUMIF函數(shù)將根據(jù)指定的條件對若干個單元格求和。
函數(shù)語法SUMIF(range,criteria,sum_range)range：為用于條件判斷的單元格區(qū)域。
criteria：為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本。
sum_range：是需要求和的實際單元格。
函數(shù)說明只有在區(qū)域中相應(yīng)的單元格符合條件的情況下sum_range中的單元格才可求和。如果忽略了sum_range，則對區(qū)域中的單元格求和。
本例公式說明其各個參數(shù)值指定SUMIF函數(shù)從F2:F20單元格區(qū)域查找大于零的記錄，并對E列中同一行的相應(yīng)單元格的數(shù)值進行匯總。F列是已經(jīng)計算得到的到期剩余天數(shù)，E列是該匯票的金額。通過這樣的條件求和，即可得到30天內(nèi)即將到期的匯票總金額。
步驟08 完善表格
表格到此已經(jīng)實現(xiàn)了功能，現(xiàn)在需要進一步美化。設(shè)置表格標(biāo)題的字體、字號和填充顏色。同時為了突出顯示給定日期和將到期金額，選中H2和H6單元格，在“設(shè)置單元格格式”中為這兩個單元格選擇一個醒目的填充色。表格中的0很影響表格的美觀，可以通過設(shè)置取消零值的顯示。點擊Office按鈕→
Excel選項→高級→此工作表的顯示選項→去掉復(fù)選項“在具有零值的單元格中顯示零”→確定。
步驟09 到期提示的另一種提示方法
在上面得到期提示表中，到期提示是到給定日期剩余的天數(shù)。如果你不想看到還有多少天，而只是需要一個更明顯的字樣，提示到期了。那么，你可以通過下面的設(shè)置實現(xiàn)。選擇F2:F20單元格區(qū)域，點擊鼠標(biāo)右鍵彈出快捷菜單，選擇“設(shè)置單元格格式”→數(shù)字→自定義，然后在右邊的“類型”列表中選擇“[>0]"到""期";G/通用格式”，點擊確定。
步驟10 到期提示
現(xiàn)在，可以看到到期提示發(fā)生了變化。喜歡哪一種方法，全憑個人喜歡。
第3節(jié)、應(yīng)付款圖表制作
對于財務(wù)人員來說，數(shù)字是有魔力的，搭建出一座財務(wù)的高塔，非常神奇。但對于非財務(wù)人員來說，數(shù)字卻是枯燥的、乏味的、死板的。表哥、表姐辛苦匯總而來的表格對于某些人來說，有種遙遠而陌生的感覺，但圖表卻不同，形象且直觀地反應(yīng)出一些客觀事實，對于那些對數(shù)字不感冒的人，圖表是拉近距離非常好的表現(xiàn)形式。Excel就有非常豐富的圖表繪制工具，通過各種幾何圖形和色彩傳達著信息。下面，我們就以應(yīng)付款明細表為例，為大家講解圖表的繪制。
步驟01 指定數(shù)據(jù)源
選中A3單元格，按住Shift鍵盤不放，再點擊B12單元格，就選中了用于繪圖的數(shù)據(jù)。
步驟02 柱形圖
選擇“插入”→“圖表”→“柱形圖”→“簇狀柱形圖”，也就是柱形圖的第一個。
步驟03 標(biāo)準(zhǔn)的柱形圖
這時，Excel自動生成了一個標(biāo)準(zhǔn)的簇狀柱形圖，樣式也非常簡單。仔細觀察，會發(fā)現(xiàn)Excel上方快捷按鈕變成了圖表工具。
步驟04 修改圖表樣式
在圖表工具的“圖表樣式”中，我們可以對柱狀圖的顏色和形狀進行選擇，這里我們選擇了深紫色且柱子呈立體效果的“樣式30”。
步驟05 調(diào)整柱體尺寸
在圖表工具→格式→大小→將柱體的尺寸修改為
步驟06 設(shè)置坐標(biāo)軸格式
在圖表工具→布局→坐標(biāo)軸，對橫豎坐標(biāo)軸的線條顏色、線型、對齊方式等進行簡單設(shè)置。
步驟07 添加數(shù)據(jù)標(biāo)簽
用鼠標(biāo)點擊圖標(biāo)中的柱子，這個時候，所有柱子的四角就多了一些小圓圈。將光標(biāo)放到小圓圈上，單擊鼠標(biāo)右鍵，彈出快捷菜單。在快捷菜單中點擊“添加數(shù)據(jù)標(biāo)簽”，現(xiàn)在，每個柱子的頭頂就多了一行數(shù)據(jù)。這正是我們想要的直觀感覺。
步驟08 修改數(shù)據(jù)列名稱
在步驟七的右鍵菜單中，點擊“選擇數(shù)據(jù)源”，點擊“圖例項（系列）”中的“編輯”按鈕，彈出“編輯數(shù)據(jù)系列”對話框，在系列名稱中，將“系列1”修改為“應(yīng)付金額”，點擊確定按鈕。
步驟09 刪除圖表標(biāo)題
現(xiàn)在，圖表的正上方多了一個標(biāo)題“應(yīng)付金額”，這個標(biāo)題是我們所不需要的，雙擊標(biāo)題選中該標(biāo)題，直接將文字刪除即可。
步驟10 調(diào)整圖表位置
在圖標(biāo)上點擊鼠標(biāo)右鍵，選擇“設(shè)置繪圖區(qū)格式”，在彈出的對話框中，可以為繪圖區(qū)設(shè)置填充色等。現(xiàn)在，圖表已經(jīng)完成了，用鼠標(biāo)拖動到合適的位置，還可以對圖表的寬度和高度進行調(diào)整。
第4節(jié)、公司應(yīng)付款表制作
作為一個有信譽的公司，付款及時，不拖不賴是很重要的。但付款的同時要考慮公司現(xiàn)金流的問題，什么時候付，付多少都需要財務(wù)人員為公司提供數(shù)據(jù)支持?，F(xiàn)在，有一家公司有10個供貨商應(yīng)付款，金額不等。公司計劃近期付款，并有兩種不同的付款方案。方案一：小于或等于1000元的賬戶一次性付清，大于1000元的賬戶償付應(yīng)付金額的50%。方案二：小于或等于3000元的賬戶一次性付清，大于3000元的賬戶償還應(yīng)付金額的40%。現(xiàn)在公司需要財務(wù)人員制作一份表格，按照兩種方案給出支付明細，并計算總還款金額。
步驟01 新建工作表
啟動Excel2007創(chuàng)建新的工作簿，將Sheet1工作表改名為“應(yīng)付款明細”并保存。在A1單元格輸入“應(yīng)付款明細表”，在A2:D2單元格區(qū)域輸入標(biāo)題名稱。
步驟02 錄入數(shù)據(jù)并求和
將供貨單位、應(yīng)付金額按照實際情況一一錄入。在A14輸入“合計”，在B14編制求和公式。選中B14單元格，在編輯欄中輸入求和公式：“=SUM(B3:B12)，按回車鍵確認。
步驟03 編制方案一的公式
現(xiàn)在需要編制公式實現(xiàn)方案一得功能：小于或等于1000元的賬戶一次性付清，大于1000元的賬戶首次支付應(yīng)付金額的50%。選中C3單元格，在編輯欄中輸入公式：“=IF(B3<=1000,B3,ROUND(B3*50%,2))”，按回車鍵確認。
選中C3單元格，將光標(biāo)放在單元格右下角，當(dāng)光標(biāo)變成黑十字形狀時，按住鼠標(biāo)左鍵不放，向下拖動鼠標(biāo)到C12松開，完成C4:C12單元格區(qū)域的公式復(fù)制。
步驟04 方案一求和
選中C13單元格，在編輯欄中輸入公式：“=SUM(C3:C12)，按回車鍵確認?，F(xiàn)在，方案一的應(yīng)付款明細，和公司首付款的總金額就出來了。
步驟05 編輯方案二的公式
選中D3單元格，在編輯欄中輸入公式：“=IF(B3<=3000,B3,ROUND(B3*40%,2))”，按回車鍵確認。選中D3單元格，將光標(biāo)放在單元格右下角，當(dāng)光標(biāo)變成黑十字形狀時，按住鼠標(biāo)左鍵不放，向下拖動鼠標(biāo)到D12松開，完成D4:D12單元格區(qū)域的公式復(fù)制。
步驟06 編制求和公式
選中D13單元格，在編輯欄中輸入公式：“=SUM(D3:D12)，按回車鍵確認?，F(xiàn)在，方案二的應(yīng)付款明細，和公司首付款的總金額也計算好了。
步驟07 完善表格
設(shè)置表格邊框線，適當(dāng)調(diào)整行高列寬，調(diào)整標(biāo)題的字體、字號和文字居中顯示等?，F(xiàn)在，兩種方案的應(yīng)付款表就完成了。
第5節(jié)、公司應(yīng)收款表制作
某公司年終盤點，需要了解當(dāng)年應(yīng)收款的拖欠情況，以此作為第二年銷售計劃調(diào)整的依據(jù)?，F(xiàn)在有8家銷售商有拖欠款項，公司需要財務(wù)部出具一份詳細的應(yīng)收款清單。
步驟01 新建工作表
啟動Excel2007創(chuàng)建新的工作簿，將Sheet1工作表標(biāo)簽改名為“應(yīng)收賬款”。在第一行輸入工作表名稱，選中I4單元格，在右鍵快捷菜單中選擇“設(shè)置單元格格式”→數(shù)字→日期→
步驟02 錄入數(shù)據(jù)
將8家未付款公司的數(shù)據(jù)一一錄入，包括開票日期、發(fā)票號碼、公司名稱、應(yīng)收金額、已收款金額、收款期等數(shù)據(jù)。
步驟03 未收款金額計算
選中F3單元格，在編輯欄中輸入公式：“=D3-E
步驟04 到期日期計算
選中H3單元格，在編輯欄中輸入公式：“=A3+G
步驟05 判斷是否到期
選中I3單元格，在編輯欄中輸入公式：“=IF(H3>$I$1,"否","是"”，按回車鍵確認。將光標(biāo)放在I3的右下角，當(dāng)光標(biāo)變成黑十字形狀時，按住鼠標(biāo)左鍵不放，向下拖動鼠標(biāo)到I10單元格松開，完成該列公式的復(fù)制。
知識點：IF函數(shù)IF函數(shù)用來執(zhí)行真假值判斷，根據(jù)邏輯計算的真假值返回不同的結(jié)果?！ 『瘮?shù)語法IF(logical_test,value_if_true,value_if_false)logical_test：表示計算結(jié)果為TRUE或FALSE的任意值或表達式。
value_if_true：表示logical_test為TRUE時返回的值。
value_if_false：表示logical_test為FALSE時返回的值。
函數(shù)說明IF函數(shù)最多可以嵌套7層，使用value_if_true及value_if_false參數(shù)可以構(gòu)造復(fù)雜的檢測條件。在計算參數(shù)value_if_false及value_if_true后，IF函數(shù)返回相應(yīng)語句執(zhí)行后的返回值。如果IF函數(shù)的參數(shù)包含數(shù)組，在執(zhí)行KF語句時數(shù)組中的每一個元素都將被計算。
步驟06 未到期金額計算
選中J3單元格，在編輯欄中輸入公式：“=IF($I$1-$H3<0,$D3-$E3,0)”，按回車鍵確認。將光標(biāo)放在J3的右下角，當(dāng)光標(biāo)變成黑十字形狀時，按住鼠標(biāo)左鍵不放，向下拖動鼠標(biāo)到J10單元格松開，完成該列公式的復(fù)制。
步驟07 編制逾期天數(shù)計算公式
對于到期未收到得款項，需要對時間段有具體的了解，就可以劃分為幾個時間段來分析，本例中分為四個時間段:0~30天、30~60天、60~90天和90天以上。現(xiàn)在針對每個時間段編制公式如下：
K3單元格中輸入公式：“=IF(AND($I$1-$H3>0,$I$1-$H3<=30),$D3-$E3,0)”；
L3單元格中輸入公式：“=IF(AND($I$1-$H3>30,$I$1-$H3<=60),$D3-$E3,0)”；
M3單元格中輸入公式：“=IF(AND($I$1-$H3>60,$I$1-$H3<=90),$D3-$E3,0)”；
N3單元格中輸入公式：“=IF($I$1-$H3>90,$D3-$E3,0)”。
公式輸入完成后，按回車鍵確認，同時使用上述公式復(fù)制的方法完成該列單元格區(qū)域的公式。
步驟08 合計
針對四個時間段的應(yīng)收款進行合計，選中J11單元格，點擊“開始”→“編輯”→Σ→在J11自動生成求和公式：“=SUM(J3:J10)”。使用同樣的方法，完成K11、L11、M11、N11的合計運算。
聯(lián)系客服
