差異處

這裏顯示兩個版本的差異處。

--- tech:google_spreadsheets_tips [2019/05/15 11:31] – 建立 jonathan_tsai
+++ tech:google_spreadsheets_tips [2021/12/21 08:57] (目前版本) – [原始資料彙整與排序] jonathan
@@ 行 1: / 行 1: @@
+====== Google Spreadsheets(試算表) 使用技巧 ======
+===== 取排序後的第一筆資料 =====
+  * 參考 - https://stackoverflow.com/questions/50019391/last-row-in-google-sheets
+  * 使用 Google 表單回報, 可能先前回報錯誤會在回報一次更正, 所以彙整資料時取出相同回報人的最後一筆回報資料就變得很需要
+  * Exp. <cli>array_constrain(sort(filter('日期資料篩選表'!$A:$G, '日期資料篩選表'!$D:$D=D3),1,false),1,7</cli>
+  * 主要使用 array_constrain 這個 function 來處理, 配合 sort 與 filter 將最後一筆資料取出來
+    * '日期資料篩選表'!$A 是表單的回報時戳
+    * array_constrain(資料清單,筆數,欄位數) -> 取 1 筆, 7 個欄位(A..G)
+===== 對多欄位進行 or 篩選 =====
+  * 參考 - https://yagisanatode.com/2020/02/15/google-sheets-how-to-use-or-inside-a-filter/
+  * 因為預設 filter 後面的多條件是 and 的篩選方式, 如果要進行 or 的篩選要改用以下的方式處理<cli>
+=FILTER(A1:C8,((A1:A8 ="Goat") + (C1:C8 = "Handsome")))
+</cli>
+===== 讀取另一個 Google Spreadsheets 內容 =====
+  * 參考 - https://stackoverflow.com/questions/26234212/google-sheet-pulling-data-from-another-spreadsheet-if-match
+  * Exp. 假設有一個來源試算表 SrcFile 的 [工作清單]分頁內將 分派目錄的狀態 可以在目標試算表 DstFile 的 [工作清單]分頁看到狀態欄位可以同步
+  * SrcFile - {{:tech:2019051501.png}}  DstFile - {{:tech:2019051502.png}}
+  * 主要是在 DstFile 狀態欄位類似輸入以下語法 <code>
+=vlookup(A33,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1lttxxxxxxxxxxxxxxxxxxxvhs/edit","工作清單!A1:B"),2,false)
+</code>
+    - 先使用 IMPORTRANGE("https://docs.google.com/spreadsheets/d/1lttxxxxxxxxxxxxxxxxxxxvhs/edit","工作清單!A1:B") 確認讀取權限
+    - 讀取來源權限沒問題才使用 vlookup 去對應
+===== 原始資料彙整與排序 =====
+  * 參考 - https://support.google.com/docs/table/25273?hl=zh-Hant&ref_topic=3105474
+  * Exp. 假設有一個來源分頁紀錄原始資料, 希望可以彙整排序並產生圖表
+  * 原始資料 - {{:tech:2019051503.png}}
+  * 彙整圖表 - {{:tech:2019051504.png}}
+  * 主要是在工作區分頁將
+    - 姓名欄位以 UNIQUE('原始資料'!B1:B) 找出來
+    - 彙整每個人的費用以 sumif('原始資料'!$B1:$B,$A2,'原始資料'!$D1:$D) 計算出來
+    - 彙整表分頁再透過 sort('工作區'!A:B,2,false) 進行排序
+  * 範例檔案 - https://docs.google.com/spreadsheets/d/1VeSoR9muNzVQy8eqSJsvvdwc_wgZh1zarl3JiaawE_o/edit?usp=sharing
+===== 使用 Query 來篩選彙整資料表內的資料 =====
+  * 參考 - https://developers.google.com/chart/interactive/docs/querylanguage
+  * Exp. 假設有一個來源分頁紀錄原始資料, 希望可以針對特定欄位進行加總排序產生彙整後的資料
+  * <cli>
+=query('(DB)專案資訊'!A3:EK, "
+select A , B, M, sum(O), sum(P), sum(Q), sum(R)
+where (M!='' and M!='#N/A')
+group by M, A, B
+order by A, B, M
+label A '部門', B '中心', M '六碼', sum(O) 'T.建立', sum(P) 'T.更新', sum(Q) 'T.Git', sum(R) 'T.Pipe'
+", -1)
+</cli>
+{{tag>GoogleDocs Spreadsheets 試算表 tips}}