Google Sheets: Freeze Volatile Function by Circular Reference: 用循環參考，凍結變動函式

每當 Google Sheets 試算表的內容有任何變更，所有變動函式 (Volatile Function)，像是 NOW(), TODAY(), RAND(), RANDBETWEEN()，都會產生新值。透過循環參考 (Circular Reference)，定義自我保持記憶的 IF() 條件，可以只在特定儲存格有變更時，才產生新值。

1 循環參考

內定情況下，試算表的公式，不可有循環參考。公式直接或間接參考到自己 (公式所在儲存格)，稱為循環參考。此時會顯示錯誤訊息 #REF!，表示偵測出循環相依性。

1.1 反覆運算

開啟反覆運算，次數上限1次

要在公式中使用循環參考，而不會顯示錯誤訊息，需要開啟反覆計算功能。

• 選擇試算表的功能表「檔案/試算表設定.../計算」。
• 反覆運算：開啟。
• 反覆運算次數上限：1次就好。

2 記憶與變動偵測

2.1 記憶

把自己的值，交給自己，就形成自我記憶的公式。例如：儲存格 A1 如果設定成公式 =A1，就是自我記憶：

A1 =A1

自我記憶公式，是循環參考，具有記憶功能，但不是很有用。除非，幫它加上條件。

2.2 條件式記憶

在特定條件下，才把自己的值交給自己，否則賦予新值，就形成條件式記憶公式。例如：

A1 =IF( ... , A1, ... )

或者

A1 =IF( ... , ... , A1 )

條件式記憶公式，既能記憶，也能變動。

2.3 變動偵測

如果改成在 IF() 的條件中參考自己，就形成能夠偵測變動的 IF() 公式。例如：

A1 =IF( A1= ... , ... , ... )

變動偵測公式，也是循環參考。

3 變動時，才變動

用變動偵測，啟動變動函式。再用條件式記憶，凍結變動函式。

3.1 自保持亂數

儲存格 B2 用來偵測 B1 的值是否有改變：

B2 =IF(B2=B1, {B1;FALSE}, {B1;TRUE})

• 如果 B1 沒改變，B2 記憶 B1 的值，B3 設為 FALSE。
• 如果 B1 有改變，B2 記憶 B1 的值，B3 設為 TRUE。

儲存格 B4 根據 B3 控管變動函式：

B4 =IF(B3, RANDBETWEEN(1,100), B4)

• B3 = TRUE (B1 有改變)，B4 產生新的亂數值 (啟動變動函式)。
• B3 = FALSE (B1 沒改變)，B4 保持 B4 的值 (記憶，凍結變動函式)。

3.2 自保持時間

所有的變動函式 (Volatile Function)，都可以用類似的方法，凍結它們。例如：取得目前時間的 NOW() 函式，變成只在 B1 有改變時，才會重新取得時間。

4 小結

透過條件式循環參考，形成自我記憶，解決變動函式一直變個不停的問題。讓變動函式的效用大增，不再是令人又愛又恨的小淘氣。

5 相關連結

1. Google Sheets: Array Lesson 1: 建立陣列 (Creating an array)
2. Volatile Excel Functions