複選題資料，一格塞了三個答案：Excel、R、Python 各自怎麼攤平？

問卷
資料整理
Excel
R
Google 表單、SurveyCake 匯出的複選題常常長成「收入;經濟自主;打發時間」擠在一格。想算「有多少人選了收入」，COUNTIF 抓不到。這篇講三種常見的展開方式，也順便回到「一欄一變數」的資料整理原則。
發佈於

2026年7月7日

發完問卷、下載回來的 CSV / Excel，複選題那一欄常常長這樣：

受訪者 主要工作原因（複選）
001 收入;經濟自主;打發時間
002 經濟自主;打發時間
003 收入
004 收入;打發時間

一格裡塞了 1 到 3 個答案，用分號分開。你想算「有多少人選了收入」，直接 COUNTIF(範圍, "收入") 抓不到 —— 因為 COUNTIF 預設是完全比對，「收入;經濟自主;打發時間」跟「收入」不是同一件事。

這篇會講三種常見的處理方式（Excel 公式、Power Query、R / Python），也順便回到一個更根本的問題：這種格式為什麼難處理？

為什麼會擠在一格？

Google 表單、SurveyCake、Microsoft Forms 這類線上表單，遇到「勾選方塊」題型（讓受訪者可以選多個），匯出時預設會把所有選項用分號串成一格。原因是這樣才裝得進「一列 = 一位受訪者」的表格結構。

註釋

Google 表單匯出用的是半形分號 ;（有些版本會用逗號 ,）。SurveyCake、Qualtrics 各有各的字元。開始寫公式前先確認一下實際的分隔符號，不要看到中文字就假設。

這種格式為什麼難拿來算？

呼應資料整理的一個基本原則：一欄放一個變數（tidy data 的定義之一）。「主要工作原因」這一欄看起來只是一個變數，其實它裡面裝了三個獨立的變數：

  • 有沒有選「收入」？（是 / 否）
  • 有沒有選「經濟自主」？（是 / 否）
  • 有沒有選「打發時間」？（是 / 否）

每一個都應該獨立成一欄，才能各自拿去算次數、跟性別交叉、放進迴歸模型。

所以核心動作就是：把一欄多值攤成多欄二元（0/1），這在統計上叫 dummy variable（虛擬變數，把類別轉成 0/1 的欄位）。攤完長這樣：

受訪者 收入 經濟自主 打發時間
001 1 1 1
002 0 1 1
003 1 0 0
004 1 0 1

之後 SUM(收入欄) 就是「有多少人選了收入」，一切都回到正常。

解法一｜Excel 公式：FIND 判斷字串

假設原始資料 C 欄是「主要工作原因」，第 2 列開始是資料。在 D 欄開一個新欄「收入」，寫：

=IF(ISERROR(FIND("收入", C2)), 0, 1)

這段在做什麼：

  • FIND("收入", C2) 在 C2 這格裡找「收入」出現的位置。找到會回一個數字（位置）、找不到會回 #VALUE! 錯誤
  • ISERROR(...) 把「找不到」的錯誤包起來變 TRUE
  • 外層 IF(...) 就是：找不到 → 0、找到 → 1

也可以反過來寫（用 IFERROR 包）：

=IFERROR(IF(FIND("收入", C2), 1), 0)

意思一樣，寫起來短一點。兩種都能用，看你順眼。

每個選項各開一欄（「經濟自主」、「打發時間」），把公式複製過去，把裡面的字串換掉。

提示

用 FIND 不用 SEARCH 的理由：FIND 區分大小寫、SEARCH 不區分。如果你的選項字串是中文，兩個沒差；但英文選項要小心「Yes」跟「yes」的問題。中文問卷通常用 FIND 就好。

這個做法的限制：選項要先自己列出來。每有一個選項就多開一欄。如果選項有 20 個，就要開 20 欄公式 —— 手動、容易漏、改一次要改 20 次。

解法二｜Power Query：拆分再展開

Excel 內建的 Power Query（一種內建在 Excel 裡的資料處理工具，選單在「資料 → 從表格 / 範圍」） 可以做動態拆分：

  1. 選整個範圍 → 資料 → 從表格 / 範圍
  2. 進入 Power Query 編輯器，選中「主要工作原因」那欄
  3. 上方選單：「拆分資料行 → 依分隔符號 → 分號」，選「拆分成資料列」（不是資料行）
  4. 這時每一個選項都會變成獨立一列，同一位受訪者會出現多列
  5. 再用「群組依據」或「樞紐資料行」把它變回每個選項一欄的 0/1 形式

這個做法的優點：選項不用先列出來 —— Power Query 拆完自己會知道有哪些選項。新增加的選項也會自動被抓到。

限制：Power Query 的操作步驟比公式多，第一次做要摸一下；但一旦設好，之後每次匯入新資料只要按「重新整理」就會重跑一次。

解法三｜Google Apps Script：資料就留在 Sheets 裡處理

如果你的問卷本身就是 Google 表單、答案自動流進 Google 試算表，那用 Apps Script（Google Sheets 內建的 JavaScript 程式環境，選單在「擴充功能 → Apps Script」） 是最順手的做法 —— 資料不用下載出來、跑完直接寫回同一份試算表。

打開 Apps Script 編輯器，貼這段：

function 展開複選題() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const src = ss.getSheetByName('表單回應 1');       // 表單原始回應那張
  const targetCol = '請問您工作的主要原因';           // 複選題那一欄的欄名
  const sep = ';';                                    // 分隔符號

  const data = src.getDataRange().getValues();
  const header = data[0];
  const idx = header.indexOf(targetCol);

  // 1. 掃全部資料，蒐集出現過的所有選項
  const optionSet = new Set();
  for (let i = 1; i < data.length; i++) {
    String(data[i][idx] || '')
      .split(sep)
      .map(s => s.trim())
      .filter(s => s)
      .forEach(opt => optionSet.add(opt));
  }
  const options = [...optionSet];

  // 2. 每列展成 0/1
  const out = [[...header, ...options]];
  for (let i = 1; i < data.length; i++) {
    const picks = String(data[i][idx] || '').split(sep).map(s => s.trim());
    const dummies = options.map(opt => picks.includes(opt) ? 1 : 0);
    out.push([...data[i], ...dummies]);
  }

  // 3. 寫到「展開後」這張工作表
  const dst = ss.getSheetByName('展開後') || ss.insertSheet('展開後');
  dst.clear();
  dst.getRange(1, 1, out.length, out[0].length).setValues(out);
}

這段在做什麼：

  • 第 1 步先掃一次資料，把每一格拆開，蒐集出現過的所有選項（用 Set 這種不會重複的集合結構）—— 你不用先手寫選項清單
  • 第 2 步再掃第二次，每一列把「有沒有勾這個選項」轉成 0 / 1
  • 第 3 步寫到另一張工作表（找不到就新增），保留原始表不動

執行方式：Apps Script 編輯器左上選 展開複選題 → 按執行。第一次會跳授權視窗（Google 要你確認這支程式能改你的試算表）。

再進一步：讓它自動跑。Apps Script 可以設 trigger（觸發器：在特定事件發生時自動執行的機制）：

function 設每次填答就自動展開() {
  ScriptApp.newTrigger('展開複選題')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onFormSubmit()
    .create();
}

執行一次這個 設每次填答就自動展開，之後每次有人送出表單，「展開後」那張表都會自動重跑。

這個做法的位置：跟 Power Query 類似 —— 不用先列選項、可以反覆更新。差別在 Power Query 是 Excel 的滑鼠操作，Apps Script 是 Google Sheets 的程式。如果你的資料本來就在 Google 表單，這條路省下「下載 CSV」這步。

解法四｜R：一行 dplyr + tidyr

如果你的資料量大、或者已經在 R 裡跑其他分析，R 有更簡潔的做法：

library(dplyr)
library(tidyr)
library(stringr)

# df 是原始資料，主要工作原因 是那一欄
# 做法 A：用 str_detect 檢查每個選項是否出現
df_wide <- df |>
  mutate(
    收入 = as.integer(str_detect(主要工作原因, "收入")),
    經濟自主 = as.integer(str_detect(主要工作原因, "經濟自主")),
    打發時間 = as.integer(str_detect(主要工作原因, "打發時間"))
  )

str_detect 這個函數（stringr 套件裡的字串偵測工具） 傳回 TRUE / FALSE，用 as.integer() 轉成 1 / 0。跟 Excel 的 FIND 邏輯一樣，只是一行寫完。

如果你只想算「哪個選項被選最多次」，不需要每人一列 dummy，可以直接攤成一列一選項：

df |>
  separate_rows(主要工作原因, sep = ";") |>
  count(主要工作原因, sort = TRUE)

separate_rows 這個函數（tidyr 套件裡的「同一格用分隔符號拆成多列」工具） 會把「收入;經濟自主;打發時間」這一格，變成三列 —— 每列的「主要工作原因」是一個乾淨的選項。然後 count() 直接算次數。

同一個問題兩種攤法，看你之後要幹嘛：每人一列 vs 每次勾選一列。

解法五｜Python：pandas 一行 get_dummies

pandas 有一個超短的做法：

import pandas as pd

# df['主要工作原因'] 是那一欄
dummies = df['主要工作原因'].str.get_dummies(sep=';')
df_wide = pd.concat([df, dummies], axis=1)

str.get_dummies(sep=';') 這個方法 專門就是為這個場景做的 —— 傳分隔符號進去，它會自動找出所有出現過的選項，各開一欄，值填 0 / 1。

跟 R 的 separate_rows 對應的做法：

df_long = df.assign(
    主要工作原因 = df['主要工作原因'].str.split(';')
).explode('主要工作原因')

df_long['主要工作原因'].value_counts()

explode 就是 R 的 separate_rows，把「一格多值」攤成「多列一值」。

幾種做法各自的位置

做法 適合什麼 限制
Excel FIND 公式 選項少（3–5 個）、只在 Excel 裡分析 選項要自己列，多了很繁瑣
Power Query 選項多、資料會反覆更新（在 Excel 環境） 操作步驟要學一次
Google Apps Script 資料在 Google 表單、想每次填答就自動展開 要會一點 JavaScript
R str_detect / separate_rows 你已經在 R 裡跑分析 要會 R
Python get_dummies 資料量大、要接後續 ML（machine learning，機器學習）pipeline 要會 pandas

五種做的都是同一件事：把一格多值攤開。差別在你的資料在哪、之後要用什麼工具接。

一開始問卷設計就避開這個問題？

從表單匯出的複選題，格式是線上表單決定的，你控制不了。但有幾個相關的設計選擇：

  • 勾選方塊 vs 網格題：如果選項每個都想單獨追蹤，可以用「多選網格」（每個選項一列，勾「是 / 否」），匯出時就是每欄一個變數。缺點是網格題受訪者填起來比較累
  • 開放式 vs 選項式：如果你允許受訪者「其他，請填寫 ___」，那一格會混進自由填答，處理時要另外抓出來
  • 選項順序：Google 表單匯出的分隔字串裡，選項順序是受訪者勾選的先後，不是原題目的順序。如果你的分析要用順序資訊，得另外處理

沒有一種設定通吃。多數情況下，收下來後再攤，反而是最單純的做法。

小結

複選題「一格塞多個答案」是問卷資料的常見形態，也是資料整理常踩到的一種「一欄多變數」的例子。核心觀念只有一個：每一個選項是獨立的變數，要展成獨立欄位才能算次數、跟其他變數交叉。

三種攤法（Excel FIND、Power Query、R / Python）各有適合的場景，選一個順手的就好。

註釋

本文的示範 Excel 檔（含原始複選題資料 + FIND 公式的展開範例）：google-form-multi-select.xlsx

回到頂端