Excel、Google Sheet：改善計算效能

 

Excel 效能：改善計算效能

https://docs.microsoft.com/zh-tw/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance

指定試算表的位置和計算設定

https://support.google.com/docs/answer/58515?co=GENIE.Platform%3DDesktop&hl=zh-Hant#zippy=%2C%E9%81%B8%E6%93%87%E5%85%AC%E5%BC%8F%E8%A8%88%E7%AE%97%E9%A0%BB%E7%8E%87

Google 雲端硬碟可存放的檔案（試算表欄位上限）

https://support.google.com/drive/answer/37603?visit_id=637456858488425003-3884791923&rd=1

Google 試算表的大小和複雜度上限（裡面描述欄位上限資料是舊的，僅參考其他資料）

http://blog.ilc.edu.tw/blog/index.php?op=printView&articleId=281359&blogId=18181

Excel：index+small+if陣列公式，查詢條件後回傳多個值（彌補vlookup或hlookup只能回傳單一值的遺憾）

要回傳值通常會用vlookup或hlookup函數，但只能回傳一個值。要回傳多個值，就必須用上index+small+if再加上陣列公式。

例如：

=INDEX('工作表'!$B:$B,SMALL(IF(('工作表'!$A$2:$A$9998='1221~1225'!C$2)*('工作表'!$D$2:$D$9998='1221~1225'!$B3),ROW('工作表'!$2:$9998),4^8),ROW(indirect("'工作表'!A"&$A3))))

（以上須使用ctrl+shift+enter作為陣列公式，google sheet則是要加ArrayFormula公式） 

「'工作表'!$B:$B」是要回傳的欄。

「('工作表'!$A$2:$A$9998='1221~1225'!C$2)*('工作表'!$D$2:$D$9998='1221~1225'!$B3)」是條件，這邊是透過布林值計算。。

符合條件會回傳對應的行號。不符合條件時，則直接返回4^8，也就是65536，一般的工作表這裏就沒有數據。

在Excel 2003版本，65536就是一列中的最大行號。2010以上是4^10，也就是1048576行， 

至於要回傳值而不是設定空的原因，是因為要供SMALL作排序。

所以數列的結果就會類似：

{1;65536;……;65536;12;13;65536;65536;65536}

參考：

https://ccenjor.pixnet.net/blog/post/222354923-493%E4%BD%BF%E7%94%A8vlookup%E5%87%BD%E6%95%B8%E5%82%B3%E5%9B%9E%E6%9F%A5%E6%89%BE%E5%88%B0%E7%9A%84%E5%A4%9A%E5%80%8B%E5%80%BC%E7%9A%84%E6%96%B9%E6%B3%95

https://www.itread01.com/content/1548424693.html

Google Sheet：巨集自動執行

可以實現Google Sheet巨集自動執行，條件如定時（依據日曆或週期）或試算表動作（開啟、編輯、內容變更、提交表單）。 網路上已有說明，僅作紀錄。 【Google Sheet】巨集自動更新 https://medium.com/@wendyhsu/google-sheet-%E5%B7%A8%E9%9B%86%E8%87%AA%E5%8B%95%E6%9B%B4%E6%96%B0-b1f26928e4a3

Excel、Google Sheet：陣列公式

假設一個資料長這樣：

A	B	C
4	4	5
3	3	5
4	4	5
3	3	5
2	2	5
2	2	3

想要的目標是如果A等於B的時候，加總C。

Excel雖然有SUMIF函數，但如果公式是：

=SUMIF(A:A,B:B,C:C)

這樣是不能運作的，因為SUMIF的條件是等於某個值或字串。

同理，如果公式是：

=sum(if(A:A=B:B,C:C,""))

，這樣也不能運作

所以這時候可以改用陣列公式：

=sum(if(A:A=B:B,C:C,""))

陣列公式輸入後要按Ctrl+Shift+Enter才能作用。

至於在Google試算表上，按Ctrl+Shift+Enter則會自動將上述公式改為：

=ArrayFormula(=sum(if(A:A=B:B,C:C,"")))