Google spreadsheet: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
mNo edit summary
(31 intermediate revisions by the same user not shown)
Line 1: Line 1:
利用Google spreadsheet的函式,分析及統計可複選的問卷題目結果。
利用[http://docs.google.com Google spreadsheet]的函式,分析及統計 google表單(google form) 問卷的複選題結果。


== 問卷原始結果 與 分析結果 ==
== 測試用問卷 ==
使用函式說明
使用[http://www.google.com/google-d-s/forms/ Google form]快速建立線上問卷。其中題目「喜愛的冰淇淋口味」,設定允許使用者可複選「問題選項」(例如: 香草、牛奶、巧克力)。
<pre>
<div style="float:left; padding-right:5px;">
=IF( REGEXMATCH(C2, "香草") = TRUE, 1, 0)
{{#widget:Iframe
</pre>
|url=https://docs.google.com/spreadsheet/embeddedform?formkey=dGw4MW85MVVMeUhlNWNKekFUZzNBNGc6MQ
|width=100%
|height=300
|border=1
}}




REGEXMATCH(C2, "香草"): 分析某位使用者作答結果(例子是 C2 欄位值),如果是香草則回傳 TRUE ,如果不是香草則會回傳 FALSE 。
<div style="clear:both"></div>
* 函式語法: RegExMatch( 資料來源 , 搜尋條件/regular_expression )
** 資料來源: C2 欄位
** 搜尋條件: "香草"  (說明: 如果文字沒有加上引號,會出現「#NAME? error: Unknown range name」錯誤訊息)
IF( REGEXMATCH(C2, "香草") = TRUE, 1, 0): 如果作答結果中包含香草,欄位值顯示為 1,否則顯示為 0。方便後續的加總。
* 函式語法: IF(判斷條件, 符合判斷條件的回傳值, 不符合判斷條件的回傳值)
** 判斷條件: REGEXMATCH(C2, "香草") = TRUE
** 當結果是 TRUE ,則會回傳 1,如果不是 TRUE 則會回傳 0。
* 最後我們只要把香草分析結果的數字,加總(sum)即可得到喜愛香草的數量。
 


== 問卷作答結果的分析 ==
左邊灰色網底是使用者的問卷作答結果。
<div style="float:left; padding-right:5px;">
<div style="float:left; padding-right:5px;">
{{#widget:Iframe
{{#widget:Iframe
|url=https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0AuFlnVY9TNrsdGw4MW85MVVMeUhlNWNKekFUZzNBNGc&single=true&gid=3&range=A1%3AJ9&output=html&widget=true
|url=https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0AuFlnVY9TNrsdGw4MW85MVVMeUhlNWNKekFUZzNBNGc&single=true&gid=3&range=A1%3AI9&output=html&widget=true
|width=1024
|width=100%
|height=400
|height=400
|border=1
|border=1
}}
}}
</div>
</div>
<div style="clear:both"></div>
=== 步驟1. 先將「問題選項」,逐一複製 ===
將每個「問題選項」例如: 香草、牛奶、巧克力,逐一複製到右邊第一行 D1, E1, F1 儲存格。
<table border="1" style="">
<tr style="background-color: #555555; color: #ffffff;">
  <th></th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  <th>E</th>
  <th>F</th>
</tr>
<tr>
  <td>1</td>
  <td>Timestamp</td>
  <td>喜愛的冰淇淋口味</td>
  <td></td>
  <td><div style="background-color: yellow">香草</div></td>
  <td><div style="background-color: yellow">牛奶</div></td>
  <td><div style="background-color: yellow">巧克力</div></td>
</tr>
<tr>
  <td>2</td>
  <td>作答時間</td>
  <td>牛奶, 巧克力</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr>
  <td>3</td>
  <td>作答時間</td>
  <td>香草, 巧克力</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
</tr>
</table>


=== 步驟2. 使用函式,分析使用者的作答結果 ===
D2, E2, F2 儲存格,輸入的使用函式
<table border="1" style="">
<tr style="background-color: #555555; color: #ffffff;">
  <th></th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  <th>E</th>
  <th>F</th>
</tr>
<tr>
  <td>1</td>
  <td>Timestamp</td>
  <td>喜愛的冰淇淋口味</td>
  <td></td>
  <td>香草</td>
  <td>牛奶</td>
  <td>巧克力</td>
</tr>
<tr>
  <td>2</td>
  <td>作答時間</td>
  <td>牛奶, 巧克力</td>
  <td></td>
  <td><div style="background-color: yellow"> =INT( REGEXMATCH(B2, $D$1) ) </div></td>
  <td><div style="background-color: yellow"> =INT( REGEXMATCH(B2, $E$1) ) </div></td>
  <td><div style="background-color: yellow"> =INT( REGEXMATCH(B2, $F$1) ) </div></td>
</tr>
<tr>
  <td>3</td>
  <td>作答時間</td>
  <td>香草, 巧克力</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
</tr>
</table>


D2 儲存格,輸入的使用函式說明
<pre>
=INT( REGEXMATCH(答案欄位位置, "選項名稱") )
=INT( REGEXMATCH(B2, "香草") )
或改成使用「香草」選項名稱,所在的儲存格的絕對參照位址 $D$1 方便之後的維護
=INT( REGEXMATCH(B2, $D$1) )
</pre>
REGEXMATCH(B2, "香草"): 分析某位使用者作答結果(例子是 B2 欄位值),如果是香草則回傳 TRUE ,如果不是香草則會回傳 FALSE 。
* 函式語法: RegExMatch( 資料來源 , 搜尋條件/regular_expression )
** 資料來源: B2 欄位
** 搜尋條件: "香草"  (說明: 如果文字沒有加上引號,會出現「#NAME? error: Unknown range name」錯誤訊息)
INT( REGEXMATCH(B2, "香草") ): 如果作答結果中包含香草,欄位值顯示為 1,否則顯示為 0。方便後續的加總。
* 函式語法: INT(邏輯值)
** 當邏輯值是 TRUE ,則會回傳 1,如果邏輯值是 FALSE 則會回傳 0。
=== 步驟3. 加總分析結果 ===
最後我們只要把選項名稱那一欄所有分析後的數字加總,例如「香草」D欄,加總(sum)D欄所有的數字,即喜愛香草的數量。
<table border="1" style="">
<tr style="background-color: #555555; color: #ffffff;">
  <th></th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  <th>E</th>
  <th>F</th>
</tr>
<tr>
  <td>1</td>
  <td>Timestamp</td>
  <td>喜愛的冰淇淋口味</td>
  <td></td>
  <td>香草</td>
  <td>牛奶</td>
  <td>巧克力</td>
</tr>
<tr>
  <td>2</td>
  <td>作答時間</td>
  <td>牛奶, 巧克力</td>
  <td></td>
  <td>0</td>
  <td>1</td>
  <td>1</td>
</tr>
<tr>
  <td>3</td>
  <td>作答時間</td>
  <td>香草, 巧克力</td>
  <td></td>
  <td>1</td>
  <td>0</td>
  <td>1</td>
</tr>
<tr>
  <td>4</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr>
  <td>5</td>
  <td></td>
  <td></td>
  <td>小計</td>
  <td><div style="background-color: yellow">1</div></td>
  <td><div style="background-color: yellow">1</div></td>
  <td><div style="background-color: yellow">2</div></td>
</tr>
</table>


<div style="clear:both"></div>
<div style="clear:both"></div>


== 測試用問卷 ==
== 練習區 ==
點選[https://docs.google.com/spreadsheet/ccc?key=0AuFlnVY9TNrsdE1lalJSX3Z2TldMUkVFQVpJdEtfdmc  連結],開啟練習區。點選兩下儲存格,即可編輯儲存格內容。
<div style="float:left; padding-right:5px;">
<div style="float:left; padding-right:5px;">
{{#widget:Iframe
{{#widget:Iframe
|url=https://docs.google.com/spreadsheet/embeddedform?formkey=dGw4MW85MVVMeUhlNWNKekFUZzNBNGc6MQ
|url=https://docs.google.com/spreadsheet/ccc?key=0AuFlnVY9TNrsdE1lalJSX3Z2TldMUkVFQVpJdEtfdmc
|width=550
|width=100%
|height=300
|height=500
|border=0
|border=1
}}
}}




<div style="clear:both"></div>
<div style="clear:both"></div>
當選項文字不相同時,可以採取上面方法,但是如果選項文字相似例如: 巧克力、巧克力脆面等,要如何分辨不同選項?


== 參考資料 ==
== 參考資料 ==
* [https://docs.google.com/support/bin/static.py?page=table.cs&topic=25273 Google Docs Help - Google spreadsheets function list]
* [https://docs.google.com/support/bin/static.py?page=table.cs&topic=25273 Google Docs Help - Google spreadsheets function list]
* [http://tcta01.blogspot.com/2009/11/google-excel_20.html 生活中如何善用資訊科技: Google線上問卷資料分析【複選題分析-EXCEL】]
* [http://tcta01.blogspot.com/2009/11/google-excel_20.html 生活中如何善用資訊科技: Google線上問卷資料分析【複選題分析-EXCEL】]
* [http://briian.com/?p=5438 在Google Docs中建立線上問卷的「統計圖表」(含圓餅圖、長條圖…)] 單選題分析
* [http://briian.com/?p=5438 在Google Docs中建立線上問卷的「統計圖表」(含圓餅圖、長條圖…)] 單選題分析,使用 countif 函式。
 
* [http://blog.xuite.net/yh96301/blog/32644876-Google%E9%9B%B2%E7%AB%AF%E7%A1%AC%E7%A2%9F%E5%95%8F%E5%8D%B7%E8%AA%BF%E6%9F%A5%E8%A4%87%E9%81%B8%E7%B5%B1%E8%A8%88%E8%A1%A8 Google雲端硬碟問卷調查複選統計表 @ 軟體使用教學 :: 隨意窩 Xuite日誌] 使用 arrayformula 與 counif 函式。


[[Category:spreadsheet]]
[[Category:Google]] [[Category:spreadsheet]] [[Category:Data Science]]

Revision as of 01:18, 1 September 2018

利用Google spreadsheet的函式,分析及統計 google表單(google form) 問卷的複選題結果。

測試用問卷

使用Google form快速建立線上問卷。其中題目「喜愛的冰淇淋口味」,設定允許使用者可複選「問題選項」(例如: 香草、牛奶、巧克力)。


問卷作答結果的分析

左邊灰色網底是使用者的問卷作答結果。

步驟1. 先將「問題選項」,逐一複製

將每個「問題選項」例如: 香草、牛奶、巧克力,逐一複製到右邊第一行 D1, E1, F1 儲存格。

A B C D E F
1 Timestamp 喜愛的冰淇淋口味
香草
牛奶
巧克力
2 作答時間 牛奶, 巧克力
3 作答時間 香草, 巧克力

步驟2. 使用函式,分析使用者的作答結果

D2, E2, F2 儲存格,輸入的使用函式

A B C D E F
1 Timestamp 喜愛的冰淇淋口味 香草 牛奶 巧克力
2 作答時間 牛奶, 巧克力
=INT( REGEXMATCH(B2, $D$1) )
=INT( REGEXMATCH(B2, $E$1) )
=INT( REGEXMATCH(B2, $F$1) )
3 作答時間 香草, 巧克力

D2 儲存格,輸入的使用函式說明

 =INT( REGEXMATCH(答案欄位位置, "選項名稱") )

 =INT( REGEXMATCH(B2, "香草") )

 或改成使用「香草」選項名稱,所在的儲存格的絕對參照位址 $D$1 方便之後的維護
 =INT( REGEXMATCH(B2, $D$1) )


REGEXMATCH(B2, "香草"): 分析某位使用者作答結果(例子是 B2 欄位值),如果是香草則回傳 TRUE ,如果不是香草則會回傳 FALSE 。

  • 函式語法: RegExMatch( 資料來源 , 搜尋條件/regular_expression )
    • 資料來源: B2 欄位
    • 搜尋條件: "香草" (說明: 如果文字沒有加上引號,會出現「#NAME? error: Unknown range name」錯誤訊息)

INT( REGEXMATCH(B2, "香草") ): 如果作答結果中包含香草,欄位值顯示為 1,否則顯示為 0。方便後續的加總。

  • 函式語法: INT(邏輯值)
    • 當邏輯值是 TRUE ,則會回傳 1,如果邏輯值是 FALSE 則會回傳 0。

步驟3. 加總分析結果

最後我們只要把選項名稱那一欄所有分析後的數字加總,例如「香草」D欄,加總(sum)D欄所有的數字,即喜愛香草的數量。

A B C D E F
1 Timestamp 喜愛的冰淇淋口味 香草 牛奶 巧克力
2 作答時間 牛奶, 巧克力 0 1 1
3 作答時間 香草, 巧克力 1 0 1
4
5 小計
1
1
2

練習區

點選連結,開啟練習區。點選兩下儲存格,即可編輯儲存格內容。


當選項文字不相同時,可以採取上面方法,但是如果選項文字相似例如: 巧克力、巧克力脆面等,要如何分辨不同選項?

參考資料