筆記: 用 offset 進行查詢指定欄(用在自動計算 Google 表單回應)

　　之前稍微提過, 可以用 countif 等函式(function), 進行依條件計數, 或是對個人記帳加上"食、衣、住、行"等分類, 就可以用 sumif 計算出該分類總額. (原文)

　　如果資料填寫時有一定的規則, countif 或 sumif 是個很好用的工具, 但遇到要加總的範圍會異動, 或是要被加總的條件會異動時, 就要再使用其他公式來指定範圍.

　　跟查詢範圍有關的函式有幾種: index, indirect, offset, row, column 等, 這次應用是為了配合 Google 表單, 最後決定用 offset, row, column 來搭配, 概述如下:

條件

  1. 民眾可以透過網路線上登記: 使用 Google 表單. (略, 不在本次主題討論)
  2. 工作人員可以查詢到報名人次狀況. (後述)
  3. 可以提供民眾查詢是否已經登記成功. (並隱藏部份個資, 使用 left, right, len 等函式, 本次略)

　　看起來只要用 countif 對表單的結果統計就好啦?

　　如果表單的問題選項都不會變, 的確是可以不用再加工, 但是電腦應用是為了解決問題, 有時候表單需要修改回答的選項, 比如這次是活動的報名, 會隨時異動相關場次的說明, 而造成民眾填寫網路表單時, 問題選項是會變動的. 例如:
  • 手工皂1
  • 手工皂1(過敏體質請留意)
  • 手工皂1(過敏體質請留意)(已額滿, 我想候補)
　　雖然 countif 也可以用部份條件來處理, 像是 =countif(a:a,">手工皂1") 可以應付以上狀況, 但如果活動有多場: "手工皂1", "手工皂2", "手工皂無雙"等, 這種條件反而要先人工判斷, 甚至要用 =countif(a:a,"條件1")-countif(a:a,"條件2")-countif(a:a,"條件3") , 整體來說反而更複雜.

　　第一個可以改善的作法, 就是加上具有唯一值特性的前置碼, 姑且也稱為 PK(Primary Key) , 比如把活動編碼, 命名為 A01, A02, A03, B01, B02, B03, etc. 報名表單的選項就用 A01 , 雖然解決了工作人員的困擾, 卻增加了民眾的困擾, 因為不小心看錯或選錯, 則工作人員還要花時間確認, 所以也有不足之處.

　　周星星: 爭甚麼爭! 混在一起做....不就好了!

　　於是選項就變成: "A01手工皂1", 過幾天因為有民眾說會過敏, 選項變成 "A01手工皂1(過敏體質請留意)", 再過幾天額滿了, 選項再變成: "A01手工皂1(過敏體質請留意)(已額滿, 我想候補)", 工作人員就準備寫 =countif(A:A,"A01手工皂1")+countif(A:A,"A01手工皂1(過敏體質請留意)")+.... 加到天荒地老了. (我連複製/貼上都懶了)

　　既然剛剛有設定 PK了, 那只計算 "A01" 這個 PK 總不會錯了吧, 另外建立一個工作表(sheet), 用 =left('表單回應'!$A$1,3) 就可以抓出 PK 嘍~ 原理是沒錯啦, 但是這麼簡單就可以下班了? 不可能....因為填寫 Google 表單, 是用"新增列"的處理方式, 而不是"在原列填上資料", 所以原本期望在新工作表的 A10 , 是抓表單回應的 A10 , 但是有民眾報名時, 原本表單回應的 A10 會被新增列擠到下面, 而新工作表一直「沒有對應到新增的回應, 所以統計結果是錯的」....

(邵庭, 圖片引用自 http://images.gamme.com.tw/news/2012/08/3/o5iYnqGXkZ6VqQ.jpg )

　　好了, 終於到了要解釋 offset 的用法了, 剛剛的心路歷程是為了解釋為什麼要選 offset 來使用, 因為電腦軟/硬體只是工具, 要視場合來用, 所以如果需求並沒有這麼複雜, 就選簡單的工具就好, 不要為了手段(工具)而忘了目的.

　　剛剛總共有幾個條件:
  1. 民眾填寫網路登記表單. (工作人員設計)
  2. 選項本身會變, 不過最前面有放 PK 以便統計. (工作人員設計, 應用一點資料庫概念)
  3. 新增一個工作表, 來處理 PK (目前需要克服的問題: 對照欄位的處理)
  4. 以處理過的 PK 來計算. (用 countif 函式)
　　而 offset 有幾個參數, 第一個參數是基準儲存格, 第二、三個參數是相對於基準儲存格的列跟行, 比如 A10 是 A1 往下 9 列, 則要找 A10 的資料時, 可以用 =offset(A1,9,0) 來處理, 跟剛剛的 =A10 雖然很像, 但是差別就在於是否會受到"插入列"的影響, 也就是 Google 表單新增一筆回應時, 用 =offset(A1,9,0) 才能對應到新增加的資料.

　　突破了這個表單新增記錄的問題之後, 後面就只是再組合成想要的資料啦:
  1. =left(offset('表單回應'!A1,9,0),3) 可以抓到'表單回應'這個工作表的 A10 儲存格內容前三碼(這案例設定的 PK )
  2. 用 =countif('新工作表'!Z:Z,"A01") 計算 A01 活動的人次.
　　最後應用的結果就像這樣: http://kh1cu.blogspot.tw/2014/02/103.html

　　公式不難, 難的是為什麼要用這公式, 以及如何組合出這公式, 才不會「只知其然而不知其所以然」, 希望這樣的分析過程, 能讓更多人活用相關工具, 而簡化工作嘍~ (好像忘記介紹 row 跟 column ....)













於