Excel在調(diào)查統(tǒng)計(jì)中的應(yīng)用

    一、 前 言

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

　　二、 數(shù)據(jù)庫的建立

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

　　Excel數(shù)據(jù)清單合并相當(dāng)簡單，在兩個(gè)數(shù)據(jù)清單間執(zhí)行\(zhòng)"復(fù)制\"與\"粘貼\"操作即可。故可以幾個(gè)操作員同時(shí)進(jìn)行錄用數(shù)據(jù)工作，最后合并到一張數(shù)據(jù)清單即可。

　　三、 統(tǒng)計(jì)表的制作

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

　　\’模糊條件求和

　　\’用于多項(xiàng)選擇數(shù)據(jù)統(tǒng)計(jì)求和

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

　　Function uCountIF(區(qū)域, 模糊條件)

　　For Each r In 區(qū)域.Rows

　　If r.Hidden = False Then

　　For 計(jì)數(shù) = 1 To Len(r.Value)

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

　　求和 = 求和 + 1

　　Exit For

　　End If

　　Next

　　End If

　　Next

　　uCountIF = 求和

　　End Function

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

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

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

　　四、 保護(hù)公式

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

　　五、 統(tǒng)計(jì)自動(dòng)化

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

　　步驟1：在\"統(tǒng)計(jì)表\"中的篩選條件區(qū)：填寫條件，如在性別欄下填寫\"男\(zhòng)"。

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

　　步驟3：點(diǎn)取\"數(shù)據(jù)庫\"表單→點(diǎn)取數(shù)據(jù)清單中任意單元格→[數(shù)據(jù)]→[篩選]→[高級(jí)篩選]→確認(rèn)數(shù)據(jù)區(qū)域無誤，否則重新選擇數(shù)據(jù)區(qū)域→點(diǎn)入條件區(qū)域→點(diǎn)取\"統(tǒng)計(jì)表\"表單→選擇B3:F4條件區(qū)域→確定。

　　步驟4：點(diǎn)取\"統(tǒng)計(jì)表\"→點(diǎn)\"打印預(yù)覽\"按鈕→點(diǎn)\"關(guān)閉\"按鈕。

　　步驟5：點(diǎn)\"停止記錄\"按鈕，宏錄制完畢。

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

　　Sub 宏1()

　　\’

　　\’ 宏1 宏表

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

　　\’

　　\’ 快捷鍵: Ctrl+s

　　Application.ScreenUpdating = False

　　Sheets(\"數(shù)據(jù)庫\").Select

　　Range(\"E15\").Select

　�、� Application.Calculation = xlCalculationManual

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

　　CriteriaRange:=Sheets(\"統(tǒng)計(jì)表\").Range(\"B3:F4\"),Unique:=False

　�、� Application.Calculation = xlCalculationAutomatic

　　Sheets(\"統(tǒng)計(jì)表\").Select

　　ActiveWindow.SelectedSheets.PrintPreview

　　End Sub

　　注意：　①②均為人工增加的語句，由于宏1是自動(dòng)記錄，每次記錄均不完全雷同，但只要抓住標(biāo)有\(zhòng)"*\"的高級(jí)篩選這條語句即可。禁止\"自動(dòng)重算\"語句放在高級(jí)篩選語句前，重置\"自動(dòng)重算\"\’語句放在高級(jí)篩選語句后。為了防止屏幕閃爍，在宏的第一行增加關(guān)閉屏幕更新語句Application.ScreenUpdating = False。關(guān)閉屏幕更新可以加快宏的執(zhí)行速度，而且看不到宏的執(zhí)行過程。

　　六、 結(jié)束語

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

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