網友根據這篇:Google表單-題目選項對照儲存在試算表的資料格式

想要知道如何將 Google 表單產生在試算表中的結果還原為 Google 表單問卷的樣子?

如下圖上方是一個複選題,使用者填答完的試算表內容如下圖左下,如何產生如下圖右下的結果?(並且可以動態選取多個資料來轉換)

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

當你利用 Google 表單設計了單選和複選的問題(利用單選方格和複選方格):

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

使用者看到「圓形」選項為「單選」,看到「方形」選項為「複選」。

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

搜集好使用者的填答結果,下載成 Excel 檔案,會看到如下的結果:

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

以下來練習各種轉換。

1. 還原一筆單選的結果

儲存格F2:=IF(A$2=$E2,"○","")

複製儲存格F2,貼至儲存格F2:H4。

本例以第 1 筆資料為例,注意儲存格的參照方式(絶對參照、相對參照、混合參照)。

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

 

2. 還原多筆單選的結果

因為資料表中會有多筆填答結果,為了方便看到結果,在此使用「開發人員/控制項」功能表中的「微調按鈕」來動態選取多筆資料。

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

本例中,設定控制項格式:

最小值:1(第 1 筆)

最大值:11(最後 1 筆)

遞增值:1(按一次按鈕增加 1 筆)

儲存格連結:$D$1(在儲存格D1中顯示所選取的數字)

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

設計公式:

儲存格F2:=IF(OFFSET($A$1,$D$1,COLUMN(A:A)-1)=$E2,"○","")

複製儲存格F2,貼至儲存格F2:H4。

OFFSET 函數中,以儲存格A1為原點,透過儲存格D1的內容來控制垂直(列)的移位:透過 COLUMN(A:A)-1 來控制水平(欄)的移位。

其中 COLUMN(A:A) 向右複製時會產生:COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

只要使用微調按鈕,即可檢視每一筆單選的填答結果。

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

 

3. 還原一筆複選的結果

接著來處理複選的問題,本例以第 1 筆資料為例。

儲存格F2:=IF(SUBSTITUTE(A$2,$E2,"")<>A$2,"○","")

複製儲存格F2,貼至儲存格F2:H4。

利用 SUBSTITUTE 函數在儲存格A2中將含有儲存格E2的內容置換為空字串。再和原來內容(儲存格A2)比較是否相同,若是相同,則不含儲存格E2的內容;反之則有,並且以「○」顯示。

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

 

4. 還原多筆複選的結果

儲存格F2:=IF(SUBSTITUTE(OFFSET($A$1,$D$1,COLUMN(A:A)-1),$E2,"")<>
OFFSET($A$1,$D$1,COLUMN(A:A)-1),"○","")

複製儲存格F2,貼至儲存格F2:H4。

這是「2.還原多筆單選的結果」和「3. 還原一筆複選的結果」公式的整合應用。

只要使用微調按鈕,即可檢視每一筆單選的填答結果。

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

 

5. 還原一筆單選的結果

其實「4. 還原多筆複選的結果」的公式,也可以套用在「2. 還原多筆單選的結果」的公式中。

儲存格F2:=IF(SUBSTITUTE(OFFSET($A$1,$D$1,COLUMN(A:A)-1),$E2,"")<>
OFFSET($A$1,$D$1,COLUMN(A:A)-1),"○","")

複製儲存格F2,貼至儲存格F2:H4。

Excel-還原Google表單結果為單選方格和複選方格的矩陣表示方式(OFFSET,SUBSTITUTE)

arrow
arrow
    文章標籤
    Excel OFFSET SUBSTITUTE
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()