F0006_Ch06.ppt.ppt

* 建立案序與科目代號查表公式 建立案序欄位的查表公式後, 接著請到儲存格 E5 (科目代號欄位) 建立如下的查表公式： * 建立預算合計金額公式 預算合計金額欄位要記錄每一筆預算在 12 個月份所編列金額的加總結果, 這可利 用 SUM 函數來運算。請選取儲存格 F5, 然後輸入如下的公式, 即可將 12 個月份的金額都加總起來： * 建立整年度及各月預算總額公式 接著我們來建立在 F2：R2 範圍的合計公式, 結算整年度以及各個月份的預算總額。為了讓公式稍微具有彈性, 而不是只有加總功能而已, 這裏我們要運用 SUBTOTAL 函數。 * SUBTOTAL 函數的用法 SUBTOTAL 函數用來傳回某儲存格範圍的小計, 其格式如下： Function_num：指定運算的函數, 參閱下表來指定, 例如指定 9 表示使用 SUM 函數來運算, 指定 1 表示用 AVERAGE 函數算出平均值。 Ref1, Ref2：指定要計算小計的範圍, 最多可指定 254 個範圍。 * SUBTOTAL 函數的用法 * SUBTOTAL 函數的用法 在列編號上面按右鈕執行『隱藏』命令即可將該列內容隱藏起來。當 Function_ num 設為 101~111, 則公式就會略過隱藏列數值不做計算。 * 建立整年度及各月預算總額公式 了解 SUBTOTAL 函數的用途之後, 現在我們來看要怎麼用。請選取 F2 儲存格, 然後輸入如下公式算出整個年度的預算總額 (因為它是加總預算合計金額欄位的值)： * 建立整年度及各月預算總額公式 建好 F2 儲存格的公式之後, 拉曳 F2 儲存格的填滿控點到 R2, 將公式複製到 G2：R2 範圍中, 即可建立 12 個月份的預算合計公式。 * 複製驗證與公式 預算底稿工作表中所需的驗證清單及公式皆已完成, 不過在開始輸入資料之前, 我們要先將之前建立的驗證清單和公式複製到下面的儲存格。 由於無法確定實際上會輸入多少筆預算記錄, 所以我們先預估個 200 列應該綽綽有餘了, 假如最後真的不夠, 到時再複製就可以了。請接續上例繼續如下操作： * 複製驗證與公式 * 複製驗證與公式 現在我們已經建好一份空的預算底稿, 接下來各位就可利用這份預算底稿來編列你的年度預算了。 * 6-2 製作預算彙總表 編列預算並不只是將一筆一筆的預算輸入就了事, 編列完畢之後還有許多的彙總工作在等著我們！這一節我們要告訴您, 如何將預算底稿中的記錄依照預算的類別, 如專案、科目、人員等, 製作成各類的預算彙總表。 * 製作預算彙總表 請開啟範例檔案 Ch06-03, 我們已事先在預算底稿工作表中編列好整年度的各筆預算, 如下表： * 製作預算彙總表 現在請切換到預算彙總表工作表, 裡面包含 3 個預算彙總表： * 製作預算彙總表 * 製作預算彙總表 這一節我們只要設計兩個公式就可完成這 3 張預算彙總表：一個是「各項目的預算總額公式」, 例如計算交通費這個會計科目的預算總額、或是中秋禮品這個專案的預算總額。 還有一個是「各類別所有項目的預算總額公式」, 也就是將該類別所有項目的預算加總起來, 其值應該等於我們之前在預算底稿工作表中所算出的年度預算總額。 * 建立項目的預算總額公式 雖然這 3 張彙總表所要彙整的項目類別不同, 不過它們的運算方式是一致的, 譬如要合計薪資支出這個會計科目總共編列多少預算, 就到預算底稿工作表中將所有屬於薪資支出科目的記錄通通找出來, 然後將這些記錄的預算金額加總起來就知道了。 同樣的, 若要合計葉恩慈總共編列多少預算, 就到預算底稿工作表中找出人員為葉恩慈的所有記錄, 然後再將這些記錄的預算金額加總即可。 既要找出符合某條件的記錄, 又要做加總運算, 有一個函數剛好可以符合這樣的要求, 那就是 SUMIF 函數！ * SUMIF 函數的用法 SUMIF 函數可用來加總符合某搜尋條件的儲存格範圍, 其格式如下： Range：要依據準則進行判斷的儲存格範圍, 也就是要在這個範圍中找出符合準則的儲存格。 Criteria：判斷的準則, 可以是數字、文字、或表示式, 例如 100、蘋果、100。 Sum_range：加總的儲存格範圍。 * SUMIF 函數的用法 舉例來說, 如右這個範例, E2 的公式可以算出 流行 類音樂專輯的 銷售量總和為 1000+2000+3000 = 6000： * 建立項目的預算總額公式 了解項目預算總額的運算方法, 這裏我們就以薪資支出科目為例, 說明如何用 SUMIF 函數建立該項目的預算總額公式。請選取科目別預算總表中的儲存格 C4, 然後輸入如下的公式： * 建立項目的預算總額公式 每個會計科目的公式都一樣, 只是尋找的科目