利用Ｅｘｃｅｌ進行自動分析計算的方法

［摘 要］ 通過對業務數據的歸集整理分析，形成規范化的數據存儲來積累基礎數據，再利用添加的輔助數據項進行引用公式設計，從而實現隨月份而動態變化的分析計算報表，為人們利用Excel報表工具來自動完成復雜的信息化管理工作提供了一個有效案例。

［關鍵詞］ 動態分析計算；數據引用；公式設計

［中圖分類號］F275；F270.7［文獻標識碼］A［文章編號］1673-0194（2007）04-0043-03

一、問題的提出

通常人們利用Excel進行報表編制時，只能實現簡單的數據計算和表間數據引用處理。以財務管理中常見的存貨分析表的編制為例，對于圖1這樣的分析計算要求，一般都是逐月輸入本月數據，而對于實際發生和計劃的兩個“累計”數額還要利用上月表的“累計”與本月表的“本月”進行人工相加后再人工輸入，自動計算只能完成“超降額”和“%”兩項分析計算。許多人都嘗試過編寫公式“=B5+C5”來計算累計發生額單元C5，結果都以造成循環引用而告失敗，最后只好人工計算后輸入。

如果能夠根據報表月份自動從已經存在的“月份計劃表”數據中自動提取“計劃”的“本月”和“累計”數額，利用各個月份已經歸集起來的“實際發生表”數據自動填寫“實際發生”的“本月”和“累計”，不但可以減輕報表的編制工作量，更重要的是通過規范化的“實際發生表”、“月份計劃表”的設計與使用積累了基礎數據，實現了數據的自動引用，從而提高了數據分析計算的準確性，也擴展了應用復雜計算公式進行數據處理的思路。

二、基礎數據存儲表的設計

為了實現上述目的，可以按12個月為表列，按圖1的“項目”為表行來進行“實際發生表”和“月份計劃表”的設計。利用“月份計劃表”存儲編制完成的月份計劃數據，利用“實際發生表”逐月輸入實際發生的數據，這樣既能滿足每月分析計算的要求，又實現了明細計劃和實際發生的電子化數據存儲，“實際發生表”的設計如圖2左側所示。當需要打印“存貨月份實際發生表”時可以把右側N到Q各列隱藏起來。

“月份計劃表”可以參照圖2的結構進行設計，文中不再詳述。

當每月要求編制“存貨分析表”時，只要輸入該月份的“月份實際發生額”即可。

三、動態輸出表的設計

首先確定圖1中的“月份：”數存放在C2單元格，注意要保持數值型，用于輸入編制報表的當前月份，并且在“月份實際發生表”的C2中編寫引用公式“=存貨分析表!C2”，讓圖2中C2單元格也等于報表月份。

然后假定圖2右側N列存放當前月份數據，也就是“本月占用”數據，如C2=8，則N列=I列；O列存放1至當前月份數據，則是1至8月份累計數據，也就是截止本月累計占用。

最后在圖2的“存貨分析表”中編寫相應的引用計算公式如下：

實際發生—本月，B5單元格=月份實際發生表!N4，然后向下自動填充；

實際發生—累計，C5單元格=月份實際發生表!O4，然后向下自動填充；

計劃—本月，B5單元格=月份計劃表!N4，然后向下自動填充；

計劃—累計，C5單元格=月份計劃表!O4，然后向下自動填充。

這樣就可以在C2單元格的“月份”數變化時，實現圖1表格的動態的分析計算效果。

圖3是存貨分析表的公式視圖顯示。

四、動態引用數據的設計

為了實現上述圖2中的假定，還必須在圖2右側的N#65380;O#65380;P#65380;Q各列編寫動態引用公式#65377;

(1)“當前月份所在列”用于存放本月所在列的單元格標號，形成“本月占用”數據的地址標號#65377;P4單元格=CHAR(65+￥C￥2)ROW()，然后向下自動填充#65377;

其中文本函數CHAR(65+$C$2)返回由數字參數所代表的ASCⅡ碼字母，大寫字母A的ASCⅡ碼為65，而1月份所在列為B，例中的當前月份為8，CHAR(65+8)的返回結果為I，正好是8月份所在列，$C$2確保公式自動填充時絕對引用C2單元格的當前月份數，ROW()返回當前引用行的行號數，CHAR(65+$C$2)ROW()實現了字母I與數字4的連接，形成了當前月份第一行的單元格標號I4#65377;

(2)“1至當前月份區域”用于存放1至當前月份的區域標號，形成“截止本月累計占用”數據的地址標號#65377;Q4單元格=CHAR(66)ROW()“:”P4，然后向下自動填充#65377;

同理，公式CHAR(66)ROW()形成了1月份的單元格標號B4，P4中已經形成了當前月份第一行的單元格標號I4，公式CHAR(66)ROW()“:”P4連接形成了1月份至當前月份的區域標號B4:I4#65377;

(3)“本月占用”用于存放當前月份的實際發生額，供存貨分析表公式引用，N4單元格=INDIRECT(P4)，然后向下自動填充#65377;

其中引用函數INDIRECT(P4)返回以P4單元格內容I4為地址的單元格中的值，也就是返回8月份計劃第一行I4單元格的值#65377;

(4)“截止本月累計占用” 用于存放1至當前月份的實際發生額合計，供存貨分析表公式引用，O4單元格=SUM(INDIRECT(Q4))，然后向下自動填充#65377;

同理，引用函數INDIRECT(Q4)返回Q4中的區域B4:I4，SUM(INDIRECT(Q4))實現了等價于SUM(B4:I4)的區域求和計算#65377;

以上公式的設計就實現了圖4中N列#65380;O列的“本月占用”和“截止本月累計占用”的引用計算假設，并且做到了這兩列的內容隨著圖1中月份C2單元格的數值(1到12之間)而動態地引用“月份實際發生表”中某個月份實際發生額和累計發生額，從而構造出“存貨表”中的動態分析計算關系#65377;

五、利用Excel自動進行復雜報表處理的要點

充分運用Excel的各項功能，不但能夠實現復雜的數據計算關系，而且還能夠進行類似于應用軟件功能的綜合數據存儲和處理，滿足廣大管理人員自己動手規劃與設計信息化功能項目的要求。以上面的應用實例為基礎，對于Excel的信息化應用的方法要點總結如下：

（1）規范化輸入。對于基礎數據要按著數據庫應用的思想進行規范化的輸入，如需要進行數學運算的要求確保為數值型，需要按日期時間進行處理的要求保證為日期時間型，對于格式要求嚴格的數據項可以通過“數據—有效性”的設計進行控制；對于需要防止意外改動的數據項可以通過單元格的鎖定和保護來實現。

（2）結構化存儲。對于數據在表格中的存放，如果考慮到公式引用計算的需要，就要注意表格設計的結構化，不要將不相干的內容存放在一個頁面內，最好能夠參照數據庫中表的結構進行設計，也就是滿足Excel中“數據清單”的要求，每個表或表中的特定區域欄目標題是簡單的單層標題，每行也都是簡單的單行數據，這樣才能適應排序、分類匯總、標準公式引用計算的要求。

（3）自動化處理和輸出。在數據計算處理時可以在基本業務數據項目的基礎上增添處理需要的輔助項目，如圖2中的N、O、P、Q等列，這樣有助于數據處理和公式編制的實現。

通過函數與公式的適當應用，還可以構造出動態變化的內容，還可以利用宏來實現數據處理、格式設定、頁面設置等的自動處理，利用自定義函數可以完成特定的計算處理。

對于具備一定程序設計基礎的用戶，可以利用VBA設計自己的應用程序來處理Excel表格內容，實現程序化的數據處理功能。

主要參考文獻

［1］ Bruce Hallberg. Excel 97超級指南［M］. 杭州:浙江科學技術出版社，1997.

［2］ John Walkenbach. Excel 2003 Bible［M］. 北京：電子工業出版社，2004.