Excel在調查統計中的應用

    一、 前 言

　　目前，市場調查、思想狀況調查、民意測驗等各種調查統計十分流行，怎樣將大量的調查表，歸納成有價值的統計表呢？對于某些大公司或企業，常采納大型數據庫 + 程序來完成，對于小公司這樣的開銷太大了，也不值得。美國微軟公司開發的Excel電子表格軟件，其強大的統計功能和編程能力，完全可滿足各類調查統計表的生成，其簡單易用性更能為廣大辦公自動化人員所接受，從而進一步減少對高級程序員依賴性。本文結合《馬鋼職工在當前改革中思想狀況調查統計表》的實例，介紹使用Excel 97在調查統計表的應用。為了節省篇幅從1000份調查表中只選區30份數據，同時基本條件及調查內容也只選取了一小部分，但這并不影響說明問題。

　　二、 數據庫的建立

　　目前調查表多采用選擇答案方式讓被調查人員填寫，這樣做有兩個好處，一是節省被調查人員的時間；二是限制了答題范圍，避免了答非所問的情況，同時也便于統計。在《馬鋼職工在當前改革中思想狀況調查統計表》中就采用了類似于多項選擇題和單項選擇題混合方式，從附表2統計表中可看出。Excel中可將數據清單用作數據庫即數據清單中的列是數據庫中的字段，數據清單中的每一行對應數據庫中的一個記錄。因此只要將題目號作為列標志。用1 ～ 9數字作為選中的答題號，填寫在題目號下即可。對于多種答案可采用\"*\"分隔，參見附表1所示，采用這種方式對操作員來說非常簡單，只要選擇小鍵盤即可操作，在錄用數據時，使用Excel的[凍結拆分窗口]命令是必要的，可大大減少行列錯位錯誤。

　　Excel數據清單合并相當簡單，在兩個數據清單間執行\"復制\"與\"粘貼\"操作即可。故可以幾個操作員同時進行錄用數據工作，最后合并到一張數據清單即可。

　　三、 統計表的制作

　　統計表一般需要作兩件事，一是根據篩選條件，進行數據篩選；二是將篩選出來的數據進行統計處理，如：求和、求最大值、求平均值等，應用最廣泛的是求和統計，以便查看某項所占的比例，便于決策人員作出決策。在本例中需要分別統計各層次職工對改革的看法，在統計表中為了能表達篩選條件，故采用高級篩選比較適用，這樣篩選條件與統計結果可在一張表中表達出來，便于人員查看分析，如附表2所示。數據的統計是本例中的關鍵，Excel中提供大量的工作表函數，其中CountIF(Range，Criteria)工作表函數就是計算某個區域中滿足給定條件單元格的數目，使用它可以完成本例中單項選擇答題的統計，如：在統計表中第一題目第①答題單元格(即D8 單元格中)輸入=CountIF(數據庫!F3:F32，1)/SUBTOTAL(3，數據庫!F3:F32)，由于Criteria準則項只允許以數字表達式或以字符串形式來精確地表達條件，如\"=1\"或\"> 1\"或\"1\"等，不能表達模糊的條件，即字符串有\"1\"存在的概念。這樣就不能將\"1*2*3\"多項選擇表示方式統計出來，因此，CountIF工作函數在本例中不能完全應用，為了使統計工作簡單這就需要我們自定義工作函數uCountIF(區域，模糊條件)如下：

　　\’模糊條件求和

　　\’用于多項選擇數據統計求和

　　\’---------------------------------------

　　Function uCountIF(區域, 模糊條件)

　　For Each r In 區域.Rows

　　If r.Hidden = False Then

　　For 計數 = 1 To Len(r.Value)

　　If Mid$(r.Value, 計數, 1) = 模糊條件 Then

　　求和 = 求和 + 1

　　Exit For

　　End If

　　Next

　　End If

　　Next

　　uCountIF = 求和

　　End Function

　　注：此處只給出了最常用的模糊條件\"=\"即單元格中存在某值條件，其它模糊條件如\"〈〉\"不存在、\"〉=\"存在大于且等于某值等，如需要可與筆者聯系。

　　由上述自定義函數可見，Excel宏編寫相當簡單，而且除關鍵詞或保留詞之外，均可使用漢字。統計工作函數定義完畢，就可使用，使用時應注意題目號、答題號與公式之間的關系，如在\"統計表\"中的第6題①號答題單元格，即D18單元格輸入=uCountIF(數據庫!F3：K32，1)/ SUBTOTAL(3，數據庫!F3:F32)，即F18單元格輸入=uCountIF(數據庫!K3:K32，2)/ SUBTOTAL(3，數據庫!F3:F32)。以此類推，分別填入相應的單元格中即可。采用指定名稱可大大簡化公式輸入，具體作法是：選擇\"數據庫\"表單中的題目1到題目10數據區，即(F2:O32)→[插入]→[名稱]→[指定]→選擇名稱在首行→確定。

　　SUBTOTAL(3，數據區)工作表函數是求篩選區總數，由于篩選記錄總數對所有列來說是相同的，故我們只需求\"題目1\"的篩選記錄總數即可。在數據清單下空一行的單元格中即F34單元格中輸入= SUBTOTAL(3，題目1)，并定義此單元格名稱為\"篩選總數\",其操作為：選F34單元格→[插入]→[名稱]→[定義]輸入→\"篩選總數\"→\"確定\"。則上述第6題①號答題D18單元格公式輸入= uCountIF(題目6，1)/篩選總數,F18單元格公式為= uCountIF(題目6，2)/篩選總數。

　　四、 保護公式

　　為了防止我們的統計公式不小心被修改，在公式驗證無誤時，可采用單元格保護功能將其保護。具體作法是：選擇要輸入的區域如：附表2中的篩選條件區B4：F4→[格式]→[單元格]→\"保護\"卡→去\"鎖定\"復選框→\"確定\"，[工具] →[保護] →[保護工作表] →\"確定\"，注意如果輸入\"口令\"一定要記住。由于Excel在缺省情況下，所有單元格均被\"鎖定\"，所以除篩選條件區外所有單元格均被鎖定不可更改數據，從而保護了數據公式的修改。如需修改公式，可先撤消保護工作表，在修改公式數據，最后別忘了保護。

　　五、 統計自動化

　　Excel中的宏是你的不知疲倦的助手，它記錄你的操作，并按你的操作過程工作，由于我們統計匯總常需要變換不同的篩選條件，來達到我們所需的統計數據，每次變化條件都要重復高級篩選操作過程，即易出錯，又使人厭煩，我們可以將此過程交給Excel宏去處理。具體作法如下：

　　步驟1：在\"統計表\"中的篩選條件區：填寫條件，如在性別欄下填寫\"男\"。

　　步驟2：[工具]→[宏]→[錄制新宏]→宏名：宏1(也可自己取名)；快捷鍵輸入\"S\"；→確定。

　　步驟3：點取\"數據庫\"表單→點取數據清單中任意單元格→[數據]→[篩選]→[高級篩選]→確認數據區域無誤，否則重新選擇數據區域→點入條件區域→點取\"統計表\"表單→選擇B3:F4條件區域→確定。

　　步驟4：點取\"統計表\"→點\"打印預覽\"按鈕→點\"關閉\"按鈕。

　　步驟5：點\"停止記錄\"按鈕，宏錄制完畢。

　　由于自定義工作函數uCountIF與宏1均使用\"數據庫\"表單中的數據清單，所以在執行宏時，如果Excel的[工具]菜單中[選項]中\"重新計算\"卡中設置為自動重算，就會因宏在執行高級篩選過程中，改變了數據清單的排列方式，導致uCountIF自動更新，從而發生函數與過程沖突。因此要對宏1進行修改。在高級篩選前禁止自動重算，高級篩選完畢后在重置自動重算，從而避免在高級篩選處理過程中進入uCountIF函數。修改后的程序如下：

　　Sub 宏1()

　　\’

　　\’ 宏1 宏表

　　\’ Jarking Ule記錄的宏1998-10-2

　　\’

　　\’ 快捷鍵: Ctrl+s

　　Application.ScreenUpdating = False

　　Sheets(\"數據庫\").Select

　　Range(\"E15\").Select

　�、� Application.Calculation = xlCalculationManual

　　* Range(\"a2:o32\").AdvancedFilter Action:=xlFilterInPlace, _ 

　　CriteriaRange:=Sheets(\"統計表\").Range(\"B3:F4\"),Unique:=False

　�、� Application.Calculation = xlCalculationAutomatic

　　Sheets(\"統計表\").Select

　　ActiveWindow.SelectedSheets.PrintPreview

　　End Sub

　　注意：�、佗诰鶠槿斯ぴ黾拥恼Z句，由于宏1是自動記錄，每次記錄均不完全雷同，但只要抓住標有\"*\"的高級篩選這條語句即可。禁止\"自動重算\"語句放在高級篩選語句前，重置\"自動重算\"\’語句放在高級篩選語句后。為了防止屏幕閃爍，在宏的第一行增加關閉屏幕更新語句Application.ScreenUpdating = False。關閉屏幕更新可以加快宏的執行速度，而且看不到宏的執行過程。

　　六、 結束語

　　本文給出Excel 97在調查統計中的應用簡例，從上述例子中可以看出Excel 97 的簡單容易性和強大的編程能力，雖然使用了一些少量的技巧，那只不過是使操作過程更加自動化而已，即使不使用這些技巧，完全可以通過手工方式來完成。我們可以看出Excel 97在調查統計中的應用，是一個隨著用戶對Excel 97的操作熟練程度，逐步從手工→半自動化→全自動化的漸變的過程，這就是Excel 97軟件的魅力所在。

　　Excel 97是一個集數據表、工作函數、VBA應用程序和強大的報表處理于一身的強大工具。從理論上說，它完全可以完成統計領域95% 以上的工作，而且使你用最段時間和最少的精力去完成你的工作。在本例中也可以只使用工作表函數如=IF(ISERROR(FVINDL\"2\"，題目1)，0，1)，將數據分離成新的數據清單，然后進行篩選統計，完全可以完成本例統計，只不過麻煩一點而已，你不妨試一試。