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