如何用 Excel 2016 分析問卷專案中的複選選項

摘要： 當我們手邊拿到一份問卷調查的 Excel 檔案，如果問卷題目是「複選」專案，要在 Excel 中分析出每個選項的出現比例，應該沒有比較方便的做法。我自己研究後發現，還是要透過撰寫 VBA 才能快速完成分析工作，否則如果問卷數過多，就會浪費許多時間整理資料。本篇文章將分享我的資料分析方法。 ...

當我們手邊拿到一份問卷調查的 Excel 檔案，如果問卷題目是「複選」專案，要在 Excel 中分析出每個選項的出現比例，應該沒有比較方便的做法。我自己研究後發現，還是要透過撰寫 VBA 才能快速完成分析工作，否則如果問卷數過多，就會浪費許多時間整理資料。本篇文章將分享我的資料分析方法。

可分析的資料格式

我們收到的資料，大概是這種格式：

每個人的選項以逗號分隔，但每個人的選擇專案都不太一樣，這樣的資料格式必須特別整理過，才能方便透過樞紐分析與圖表的方式呈現。

以下是我們預計整理完後的結果：

資料格式轉換

資料轉換的過程有一定的規律性，所以很適合透過 VBA 轉換資料格式，我們預計用以下流程進行資料整理：

1. 結合所有人的選取專案，包含自行填寫「其他」選項(自由填寫)的部分。
2. 將所有逗號分隔的專案先轉換成陣列型態
3. 將陣列物件過濾掉所有重複的專案
4. 最後再將去除重複的專案合併成逗號分隔的字串

以下是完整的 VBA 程式碼：

Function GetItemSize(value As String, Optional separator As String = ",")
Dim DedupeArray As Variant
DedupeArray = RemoveDupes(Split(value, separator))
GetItemSize = UBound(DedupeArray) - LBound(DedupeArray)
End Function

Function RemoveDupStrings(value As String, Optional separator As String = ",")
RemoveDupStrings = Join(RemoveDupes(Split(value, separator)), separator)
End Function

Function RemoveDupes(InputArray) As Variant

Dim OutputArray As Variant
Dim CurrentValue As Variant
Dim A As Variant

On Error Resume Next
OutputArray = Array("")
For Each CurrentValue In InputArray
CurrentValue = Trim(CurrentValue)
Flag = 0

If IsEmpty(CurrentValue) Then GoTo skip

For Each A In OutputArray
If A = CurrentValue Then
Flag = 1
Exit For
End If
Next A

If Flag = 0 Then
ReDim Preserve OutputArray(UBound(OutputArray, 1) + 1)
OutputArray(UBound(OutputArray, 1) - 1) = CurrentValue
End If

skip:
Next
RemoveDupes = OutputArray

End Function

Public Function GetString(ByVal cell As Range) As String
 GetString = cell.value & ""
End Function

加入用來計算複選專案的欄位

1. 建立一個全新的工作表，假設名稱為 工作表1

2. 在 A1 儲存庫透過 RemoveDupStrings 取得不重複選項清單，如下公式：

   =RemoveDupStrings(TEXTJOIN(",", TRUE, '表單回應 1'!D2:D200))

3. 複製 A1 儲存格，然後用「貼上值」的方式，將內容貼到 A2 儲存格

4. 將 A2 儲存格使用 [資料] -> [資料剖析] 功能，解析出所有欄位。此時所有欄位會自動被展開，如果有 6 個選項，就會自動展開 6 個欄位。

5. 刪除 第一列 的內容

6. 將 工作表1 的所有欄位複製，並到原本的工作表欄位後面 插入複製的儲存格

7. 在 E2 儲存格，設定以下公式：

   =IF(ISERROR(SEARCH(E$1, $D2)), 0, 1)

8. 將 E2 儲存格複製到所有複選分析欄位的儲存格

9. 將每個複選選項的欄位進行加總

10. 產生圖表！

操作影片

本篇文章的操作步驟有點繁雜，對 Excel 不太熟悉的人可能會看不懂，所以我特別錄製了一段教學影片，應該可以更容易瞭解完整的操作過程才對。

ofollow,noindex" target="_blank">https://youtu.be/_TexC6h2Bkc

相關連結