# 如何用 Excel 2016 分析問卷項目中的複選選項

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

### 可分析的資料格式

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

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/5bc3a6b3a5b0c23a4adc-image_3141.png)

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

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

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/d1eb283a95ea79a00410-image_3142.png)

### 資料格式轉換

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

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

以下是完整的 VBA 程式碼：

```vbs
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` 取得不重複選項清單，如下公式：
    
    ```vbs
    =RemoveDupStrings(TEXTJOIN(",", TRUE, '表單回應 1'!D2:D200))
    ```
    
3.  複製 `A1` 儲存格，然後用「貼上值」的方式，將內容貼到 `A2` 儲存格
    
4.  將 `A2` 儲存格使用 \[資料\] -> \[資料剖析\] 功能，解析出所有欄位。此時所有欄位會自動被展開，如果有 6 個選項，就會自動展開 6 個欄位。
    
5.  刪除 `第一列` 的內容
    
6.  將 `工作表1` 的所有欄位複製，並到原本的工作表欄位後面 **插入複製的儲存格**
    
7.  在 `E2` 儲存格，設定以下公式：
    
    ```vbs
    =IF(ISERROR(SEARCH(E$1, $D2)), 0, 1)
    ```
    
8.  將 `E2` 儲存格複製到所有複選分析欄位的儲存格
    
9.  將每個複選選項的欄位進行加總
    
10.  產生圖表！
     

### 操作影片

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

[https://youtu.be/\_TexC6h2Bkc](https://youtu.be/_TexC6h2Bkc "https://youtu.be/_TexC6h2Bkc")

<iframe src="https://www.youtube.com/embed/_TexC6h2Bkc" width="560" height="315" frameborder="0" allowfullscreen="allowfullscreen"></iframe>

**相關連結**

-   [本篇文章原始碼: Module1.vba](https://gist.github.com/doggy8088/96aee1271688ac5f6894a3f70b0b68b3)
-   [The VBA Array tutorial - The Analyst Cave | Excel, VBA, programming and more](https://analystcave.com/vba-vba-array/)
-   [Split text into different columns with the Convert Text to Columns Wizard - Excel](https://support.office.com/en-us/article/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7)
-   [使用資料剖析精靈將文字分割成不同的欄](https://support.office.com/zh-tw/article/%E4%BD%BF%E7%94%A8%E8%B3%87%E6%96%99%E5%89%96%E6%9E%90%E7%B2%BE%E9%9D%88%E5%B0%87%E6%96%87%E5%AD%97%E5%88%86%E5%89%B2%E6%88%90%E4%B8%8D%E5%90%8C%E7%9A%84%E6%AC%84-30b14928-5550-41f5-97ca-7a3e9c363ed7?ui=zh-TW&rs=zh-TW&ad=TW)