資源簡介

如何用excel統計調查問卷

有些人可能經常要對調查問卷進行統計，使用手寫的方法（就是在紙上寫正字的方法）雖然很方便，不過不方便在一定條件下進行統計，如在男士的條件下，使用諾基亞的手機有多少人。而用excel可以很方便得出此類統計結果，并且由于數據輸入excel方便備查，也可以導入access等數據庫再進行較復雜的處理。另一方面，因為excel大部分人都會，所以可以幾臺電腦同時錄入數據，匯總時只需將數據復制到一個表中，簡單快捷。
調查問卷的問題可以分為三類：單選題、多選題、主觀題。主觀題比較難統計，本文不涉及這方面的。以下所說的用excel統計只涉及用excel算出答案個數和百分比的最基本的統計。
一、錄入問卷原始數據
下面是一簡化了的調查問卷：
手機使用的調查
1、您的性別是：

1、先生
2、女士
2、您使用過的手機的品牌有：

1、諾基亞 2、摩托羅拉 3、索愛
4、三星
5、聯想
6、其他
假設問卷有10份。錄入數據如下圖：

在此，有幾個要注意的問題：
1、錄入數據前要對每張問卷進行編號，方便錄入后問卷答案的備查，也方便對數據排序后返回到原始錄入的狀態。
2、錄入時將問題的答案如ABCD等替換為1234，如上圖。畢竟錄入數字比錄字母要快得多。因此在設計問卷時可考慮將問題的選項設為1234，避免錄入時頭腦將字母轉化為數字時出現錯誤。
3、單選和多選題每道題的答案均只用一個單元格，這也是為了方便錄入。多選題的答案當然也可以分開，不過會影響錄入速度，分開則會方便以后使用其他軟件對數據進行處理。
4、如上圖，將第一行凍結起來，使問卷數多時，錄入時依然可看到各個字段名，減少出錯。（凍結的方法：選中要凍結行下邊一行和要凍結列右邊一列的那個單元格,再從菜單里點窗口--凍結窗格就可以了!如要凍結第一行，只要點中A2單元格，然后菜單，凍結窗格）
5、統計區暫時放在問卷原始數據的下面。
6、數據錄入的兩種方法。一是直接在單元格中輸入，左手方向鍵，右手小鍵盤。因為問卷的答案統一為數字，所以直接輸入也有較快的速度。二是使用excel記錄單輸入，左手Tab鍵，右手小鍵盤。選中數據區域的任意一個單元格，執行“數據→記錄單”命令，打開“記錄單”窗體（如下圖），點“新建”即可錄入新記錄，Tab鍵向下移動光標，Shift+Tab向上移動光標，Enter鍵完成新記錄的輸入，并自動打開新的錄入窗口，方便下條記錄的輸入。
二、統計單選題
方法1、使用countif函數
如本例中，在單元格B17中輸入=COUNTIF($B$2:$B$11,1)即可求出問題一中選第一個選項的人的個數。使用絕對引用可減少以后復制公式時出錯，當然也可以不使用絕對引用。問題一2的方法同上。
方法2、使用自動篩選功能

點中數據區域的任意單元格，然后菜單，數據--篩選--自動篩選。點中B1單元格右邊的下拉按鈕，選1，此時可在狀態欄看到“在10條記錄中找到5個”（如下圖），將5填入B17就行了，減少了輸入公式的麻煩。然后再篩選2，篩選完改回“全部”，以免影響其他題目的篩選。問卷有幾百份時，需凍結字段行，才能使字段行和統計區處在同一屏幕，方便進行篩選和輸入篩選結果。如下圖
三、統計多選題
方法1、使用countif函數
多選題的答案并不只含有1或2或3，而是幾個，直接模仿使用統計單選題時的公式無效。因為excel默認將12、15、34等字符串看作數字串，countif無法算出數字串中含有幾個1，幾個2，但對文本格式字符串有效。所以使用countif前需將多選題的答案數據轉為文本格式。（轉換方法：突出顯示數字列，然后使用“數據”->“分列”命令。在文本分列向導中，按兩下“下一步”，到達步驟3，單擊“列數據格式”下的“文本”，（如下左圖）完成即可（效果如下右圖）。提示完成此向導后，可能會在一個或更多單元格中看到稱為錯誤指示器的小三角形。如果感覺這些三角形令人分心，則可以選擇此列，然后通過僅刪除一個三角形將其全部刪除。）接著，在B20輸入公式=COUNTIF(C2:C11,"
1
")即可求出問題二的答案中，含有選項1的個數。輸入公式=COUNTIF(C2:C11,"
1
5
")
，可求出含選項1和選項5的答案的個數，包括15，125，153等。
注：只是將多選題答案的單元格格式改為文本，countif函數的結果是不正確的。

方法2、使用自動篩選功能
同方法1，需要先使用“分列”命令將數字轉化為文本。之后在多選題右邊的下拉按鈕中選“自定義”，如下圖，選“包含”，右邊輸入1或2或3可計出相應的個數。要求只含15，不包括135，153，可選擇等于15。要求含15，包括135，153之類，可選擇等于
1
5
。
方法3、使用嵌套函數
不用改數據的格式，直接用公式。

含選項“1”
公式是
=SUMPRODUCT(ISNUMBER(FIND("1",A1:A10))
1)

含選項“2”
公式是
=SUMPRODUCT(ISNUMBER(FIND("2",A1:A10))
1)

含選項“3”
公式是
=SUMPRODUCT(ISNUMBER(FIND("3",A1:A10))
1)

含選項“5”
公式是
=SUMPRODUCT(ISNUMBER(FIND("5",A1:A10))
1)

含選項“15”
公式是
=SUMPRODUCT(ISNUMBER(FIND("15",A1:A10))
1)

將上面公式中的黑體字改為相應的統計選項和統計區域即可。不過上面求“15”的公式只能計算出完全是“15”的單元格的個數，不包括135，156之類。四、有條件的篩選
如要求在男士中使用諾基亞的手機有多少人，同樣使用自動篩選功能，可先對問題一進行篩選，然后再對問題二自定義篩選，選包含1。具體參考上面的方法2。排序，分類匯總，高級篩選，數據透視表等功能也是值得挖掘的功能。
五、百分比算法
這個是最容易的。例：在C17輸入=B17/10或=B17/$B$14回車，得出小數，之后向下拖填充，接著把單元格格式改為百分比就行了。
六、尾聲工作
1、全部統計完后，最好將問卷的統計數字復制到另一個表中單獨保存，復制后，選“選擇性粘貼”將數值保存下來，避免單元格位置變動使公式的結果產生變化。
2、因為在統計時統計結果是豎的，也可能是橫的，本例中為豎，此時可能需要轉換一下橫豎。即在excel
中行轉列,列轉行。方法如下：EXCEL中，首先復制欲轉換的內容，然后選擇編輯－選擇性粘貼－轉置即可；
若在WORD的表格，復制到EXCEL中，按照上述操作，然后復制回來即可。
補充：使用字符串統計單選題。
將多道連續的單選題答案統一輸入一個單元格中，然后使用正文中介紹的分列命令把數字串改為文本串。效果如下圖：
接著使用countif(1
)統計第一題選1的有多少個。用countif( 1
)統計第二題選1的有多少個。如此類推。優點：減少錄入數據時的按鍵數。

展開更多......

收起↑