台灣微軟公司 軟體顧問 台灣微軟公司 軟體顧問 洪士吉 編撰 洪士吉 編撰 台灣微軟公司 軟體顧問 台灣微軟公司 軟體顧問 洪士吉 編撰 洪士吉 編撰 運用 Excel 管理銀行帳戶與應收帳款

概述 應收帳款帳為企業現金來源的源頭，因此企業對應收帳款帳 皆投入甚嚴密的控管注意。應收帳款帳齡分析表經常為企業 控管應收帳款的重要控管工具。藉由帳齡分析表，企業可暸 解各筆應收帳款帳的品質及回收狀況，對超過一定日期的帳 齡，投入更為密切的催收程序與注意。 應收帳款收款活動，皆經由銀行帳戶來完成。而一般企業依 營業之需要，往往會開立多個銀行帳戶，並就各銀行帳戶進 行補同性質的收支交易。企業人員對於各銀行帳戶的收支紀 錄及餘額變化，須作全盤的了解與掌控，方能有利於企業現 金流量的規劃作業及投資與融資活動的進行。 本專題所設計的銀行帳戶管理模式主要在登錄各銀行帳戶的 收支交易紀錄，分類查詢其內容，彙總分析收支內容並以圖 表比較分析之。

帳齡分析表 在帳齡分析表中的 H3 儲存格內輸入編製帳齡分析表時的日期，並於 B 、 C 、 D 三欄中輸入相關的帳款資料，則 E 、 F 、 G 、 H 各欄自動會反映 出各筆記錄所相對應的帳齡金額，並於第 18 及第 19 列中，統計出彙總 資訊。至於『帳齡分配圖』圖表，也會根據各類帳齡資料以立體圓形 圖顯現帳齡分配狀況。

函數與公式 辨識帳齡區間的公式 辨識帳齡區間的公式 帳齡分析表的標頭函數 帳齡分析表的標頭函數 代表報表標頭的 B1 顯示了帳款資料的起訖日期： =" 帳齡日期從 " & TEXT(MIN(C:C),"ee/mm/dd") &" 到 " & TEXT(MAX(C:C),"ee/mm/dd")

繪製帳齡金額分配圖 完成『帳齡分析』工作表的設計後，以圖表精靈繪製帳齡 金額分配圖：

設計銀行帳戶資料清單 建立資料清單 建立資料清單 設定儲存格函數 設定儲存格函數 在代表第一筆資料記錄的銀行名稱欄位中輸入下列公式： = IF(ISNA(VLOOKUP( 銀行帳號, 開戶銀行資料區,2,FALSE)), "", (VLOOKUP( 銀行帳號, 開戶銀行資料區,2,FALSE))) 以 [ 資料 / 驗證 ] 指 令設定常用摘要 以 [ 資料 / 驗證 ] 指 令設定常用摘要

日期表頭的設計 E3 =Now() E3 =Now() E4=VLOOKUP(WEEKDAY( E3,2), 一週名稱,2) E4=VLOOKUP(WEEKDAY( E3,2), 一週名稱,2) WEEKDAY 函數的語法： WEEKDAY(serial_number, return_type) WEEKDAY 函數的語法： WEEKDAY(serial_number, return_type)

銀行帳戶資料之分類、查詢與彙總 自動篩選與進階篩選 自動篩選與進階篩選 樞紐分析表 及圖報表 樞紐分析表 及圖報表

收支及餘額比較圖

結語 本專題的帳齡分析模式掌握了 Excel 以數字型態來表現日期的特性，對帳 款日期的間隔以 IF 與 AND 函數作成分類計算。在報表標題上則運用 MAX 、 MIN 來取得日期欄位的起訖日期，並以 TEXT 函數轉換成文字型態的日期 字串。最後，根據帳齡分析表繪製帳齡金額分配圖。 本專題的帳齡分析模式掌握了 Excel 以數字型態來表現日期的特性，對帳 款日期的間隔以 IF 與 AND 函數作成分類計算。在報表標題上則運用 MAX 、 MIN 來取得日期欄位的起訖日期，並以 TEXT 函數轉換成文字型態的日期 字串。最後，根據帳齡分析表繪製帳齡金額分配圖。 在資料庫中登錄銀行帳戶的收支紀錄，可使用 VLOOKUP 函數或以下拉清 單選項方式來自動登錄銀行帳戶名稱。本章的銀行帳戶管理模式基於銀 行帳戶的特性，使用了 VLOOKUP 函數來參照對應的銀行帳戶名稱，然而 對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。 在資料庫中登錄銀行帳戶的收支紀錄，可使用 VLOOKUP 函數或以下拉清 單選項方式來自動登錄銀行帳戶名稱。本章的銀行帳戶管理模式基於銀 行帳戶的特性，使用了 VLOOKUP 函數來參照對應的銀行帳戶名稱，然而 對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。 在帳戶統計表上，我們使用樞紐分析表來彙集各帳戶的收支金額，並使 用自訂欄位方式添加了餘額計算欄位。最後則根據帳戶統計表繪製了收 入、支出及等餘額立體長條圖。 在帳戶統計表上，我們使用樞紐分析表來彙集各帳戶的收支金額，並使 用自訂欄位方式添加了餘額計算欄位。最後則根據帳戶統計表繪製了收 入、支出及等餘額立體長條圖。 在資料庫中登錄銀行帳戶的收支紀錄，可使用 VLOOKUP 函數或以下拉清 單選項方式來自動登錄銀行帳戶名稱。本專題的銀行帳戶管理模式基於 銀行帳戶的特性，使用了 VLOOKUP 函數來參照對應的銀行帳戶名稱，然 而對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。 在資料庫中登錄銀行帳戶的收支紀錄，可使用 VLOOKUP 函數或以下拉清 單選項方式來自動登錄銀行帳戶名稱。本專題的銀行帳戶管理模式基於 銀行帳戶的特性，使用了 VLOOKUP 函數來參照對應的銀行帳戶名稱，然 而對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。 至於帳戶統計表，我們使用樞紐分析表來彙集各帳戶的收支金額，並使 用自訂欄位方式添加了餘額計算欄位。 至於帳戶統計表，我們使用樞紐分析表來彙集各帳戶的收支金額，並使 用自訂欄位方式添加了餘額計算欄位。 本專題的帳齡分析模式掌握了 Excel 以數字型態來表現日期的特性，對帳 款日期的間隔以 IF 與 AND 函數作成分類計算。在報表標題上則運用 MAX 、 MIN 來取得日期欄位的起訖日期，並以 TEXT 函數轉換成文字型態的日期 字串。最後，根據帳齡分析表繪製帳齡金額分配圖。 本專題的帳齡分析模式掌握了 Excel 以數字型態來表現日期的特性，對帳 款日期的間隔以 IF 與 AND 函數作成分類計算。在報表標題上則運用 MAX 、 MIN 來取得日期欄位的起訖日期，並以 TEXT 函數轉換成文字型態的日期 字串。最後，根據帳齡分析表繪製帳齡金額分配圖。 在資料庫中登錄銀行帳戶的收支紀錄，可使用 VLOOKUP 函數或以下拉清 單選項方式來自動登錄銀行帳戶名稱。本章的銀行帳戶管理模式基於銀 行帳戶的特性，使用了 VLOOKUP 函數來參照對應的銀行帳戶名稱，然而 對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。 在資料庫中登錄銀行帳戶的收支紀錄，可使用 VLOOKUP 函數或以下拉清 單選項方式來自動登錄銀行帳戶名稱。本章的銀行帳戶管理模式基於銀 行帳戶的特性，使用了 VLOOKUP 函數來參照對應的銀行帳戶名稱，然而 對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。 在帳戶統計表上，我們使用樞紐分析表來彙集各帳戶的收支金額，並使 用自訂欄位方式添加了餘額計算欄位。最後則根據帳戶統計表繪製了收 入、支出及等餘額立體長條圖。 在帳戶統計表上，我們使用樞紐分析表來彙集各帳戶的收支金額，並使 用自訂欄位方式添加了餘額計算欄位。最後則根據帳戶統計表繪製了收 入、支出及等餘額立體長條圖。 在資料庫中登錄銀行帳戶的收支紀錄，可使用 VLOOKUP 函數或以下拉清 單選項方式來自動登錄銀行帳戶名稱。本專題的銀行帳戶管理模式基於 銀行帳戶的特性，使用了 VLOOKUP 函數來參照對應的銀行帳戶名稱，然 而對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。 在資料庫中登錄銀行帳戶的收支紀錄，可使用 VLOOKUP 函數或以下拉清 單選項方式來自動登錄銀行帳戶名稱。本專題的銀行帳戶管理模式基於 銀行帳戶的特性，使用了 VLOOKUP 函數來參照對應的銀行帳戶名稱，然 而對於資料庫中的摘要欄位則使用下拉清單選項方式來登錄。 至於帳戶統計表，我們使用樞紐分析表來彙集各帳戶的收支金額，並使 用自訂欄位方式添加了餘額計算欄位。 至於帳戶統計表，我們使用樞紐分析表來彙集各帳戶的收支金額，並使 用自訂欄位方式添加了餘額計算欄位。

Q&A

台灣微軟公司 軟體顧問 洪士吉 編撰 洪士吉 編撰 台灣微軟公司 軟體顧問 洪士吉 編撰 洪士吉 編撰