軟體:google sheet

版本:google sheet


===「前言」===

最終目的是免除拖曳這個步驟。
資料經由「表單」輸入到「試算表1」裡面，為了避免誤觸試算表1的資料所以後續的計
算都是由「試算表2」處理。

以下皆為「試算表2」
欄首都有「文字標題」，除了標題外下面的資料都是數字。
欄位的順序如下：
A,B,C,D
日期時間,第二資料欄,第三資料欄,運算

A,B,C欄皆為資料欄，諸如:
A1='Sheet1'!A1
A2='Sheet1'!A2
A3='Sheet1'!A3
而B,C兩欄也是依照此模式向下拖曳從「sheet1」擷取資料。

而D欄的運算模式為：
D1=B1
D2=B2-C1
D3=B3-C2
D4=B4-C3
以此類推。

這樣導致每天資料更新時都要手動向下拖曳資料，很麻煩。

===「問題一」===

最近發現有一個函式叫作arrayformula
可以在欄首寫一遍之後就一勞永逸，未來就不用一直拖曳了。
例如：
A1=arrayformula('sheet1'!A:A)
B1=arrayformula('sheet1'!B:B)
C1=arrayformula('sheet1'!C:C)

但是到了D欄就無法那麼簡單寫
D1=arrayformula(B:B-C:C)
這樣子的結果只會出現:
D1=B1-C1
D2=B2-C2
D3=B3-C3
D4=B4-C4
而非我期望的:
D1=B1-C0
D2=B2-C1
D3=B3-C2
D4=B4-C3

也就是說我希望能達成C欄跟B欄相差一列，兩者再相減。

但問題很多:
1.不知道如何將arrayformula算式呈現在D1
2.在D1，計算B1-C0會出現錯誤（沒有C0這個位址）
3.在D1以及D2，因為運算當中有屬於「標題文字」的B1,C1，這也會跳錯誤。

已解決問題:
當無資料時相減顯示0的解法。
當B999是空白而C998也是空白時，兩者相減會顯示0。
這可以用下面的方法解決：
D1=arrayformula (if(isblank(B:B),"",不知道怎麼寫的函式））
讓結果寫成空白。

===問題二===

sheet2還有第二個大魔王

另一個欄位，就算是E欄好了。
E欄是以日為單位對B欄進行累加，但是每個月從頭開始累加，以下舉例：

A欄,B欄,E欄,「說明」
2019/01/30,8,16
2019/01/31,2,18,「16+2=18」
2019/02/01,20,20,「新的一月0+20=20」
2019/02/02,6,26,「20+6=26」
2019/02/03,9,35,「26+9=35」
.
.
.
2019/12/30,2,49
2019/12/31,5,54,「49+5=54」
2020/01/01,7,7,「新的一年0+7=7」
2020/01/02,1,8,「7+1=8」

A欄是按照時序排列的，因此在一般計算時很方便，以E6為例基本上就是:
E6=if(month(A6)=month(A5),E5+B6,0+B6)
接下來就是向下拖曳就好。

但是我也希望將他整合進arrayformula裡面，這又是一堆問題了。

===總結===

我不強求一定要使用arrayformular來進行運算，最終目的是想要在填完表單之後可以馬
上看到sheet2的結果，而非又要拖曳一次。

好像可以在google sheet上面錄製巨集，讓巨集自動在sheet1有由表單輸入的一列新資
料時運作，自動將sheet2也向下拖曳一列。

但因為google sheet的巨集程式實在是沒有經驗，不知道該如何編輯，只好一直研究ar
rayformula，目前花了很多時間卻研究不出一個可行的辦法。

希望可以解決這個問題。


--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 111.241.187.98
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1554694308.A.1CC.html