Excel在调查统计中的应用.docVIP

Excel在調查統計中的應用 尤嘉慶 馬鋼股份有限公司設計院電腦室【摘 要】本文結合《馬鋼職工在當前改革中思想狀況調查統計表》的實例，介紹使用Excel 97在調查統計表的應用，使廣大應用人員能夠自己動手參與調查統計工作，從而進一步減少對高級程式師的依賴性。【關鍵字】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 區域.RowsIf r.Hidden = False ThenFor 計數 = 1 To Len(r.Value)If Mid$(r.Value, 計數, 1) = 模糊條件 Then求和 = 求和 + 1Exit ForEnd IfNextEnd IfNextuCountIF = 求和End Function注：此處只給出了最常用的模糊條件=即單格中存在某值條件，其他模糊條件如〈〉不存在、〉=存在大於且等於某值等，如需要可與筆者聯繫。由上述自定義函數可見，Excel巨集編寫相當簡單，而且除關鍵字或保留詞之外，均可使用漢字。統計工作函數定義完畢，就可使用，使用時應注意題目號、答題號與公式之間的關係，如在統計表中的第6題①號答題單格，即D18單格輸入=uCountIF（資料庫!F3：K32，1）/ SUBTOTAL(3，資料庫!F3:F32)，即F18單格輸入=uCountIF(資料庫!K3:K32，2)/ SUBTOTAL(3，資料庫!F3:F32)。以此類推，分別填入相應的單格中即可。採用指定名稱可大大簡化公式輸入，具體作法是：選擇資料庫表單中的題目1到題目10資料區