資料會長大時，範圍要怎麼寫：開放式範圍、整欄、表格與 FILTER

每週我都把新的銷量接在表格最下面，總計那格寫的是 =SUM(B2:B6)，結果新資料進來，總計永遠停在 242。

資料會長大的時候，公式的範圍有好幾種寫法：固定範圍、開放式範圍 B2:B、整欄 B:B、表格參照，還有 FILTER。這篇用同一份資料新增三筆，看每一種算出什麼，也看各自會在哪裡出狀況。為什麼要動態化的想法在 動態資料設計，這篇只比寫法。

同一份資料，四種總和

A1:B6 是原本的銷量表：

手算一下，37 + 112 + 8 + 64 + 21 等於 242。接著在第 7 到 9 列接上三筆：青茶 15、烏龍奶茶 46、蘋果紅茶 9，共 70 杯，新的總數是 312。

下面四種寫法，各放在一格，來看看結果：

四種寫法一開始都是 242，新增資料之後，只有固定範圍沒有跟上。表格參照那一列要先把 A1:B6 轉成表格才有，後面會講。

固定範圍：插在中間會長，接在後面不會

B2:B6 的終點是第 6 列。在第 4 列和第 5 列之間插入一列，這列就落在範圍裡面，Sheets 會把範圍改成 B2:B7。如果在新插入的列填 20，總和預期是 242 + 20 = 262。

但是接在第 6 列的下面打字，第 7 列在範圍外面，公式不會變。很多人是這樣新增資料的，所以固定範圍最容易漏。

開放式範圍：B2:B

B2:B 只寫起點，一路算到 B 欄最後一列。起點寫 B2 是為了跳過標題。寫法與搭配 ARRAYFORMULA 的用法在 ARRAYFORMULA 入門，這裡看三個用法的結果。

筆數用 COUNTA：

新增前是 5，新增後是 8。要取最後一筆，可以把筆數丟給 INDEX：

新增前是 21，新增後是 9。這個寫法要求資料中間沒有空白格，否則 COUNTA 數出來的筆數會比實際列數少。

開放式範圍有兩個容易踩的地方。第一個是 COUNTBLANK，它會把範圍裡所有空白格都數進去：

新工作表預設 1,000 列，B2:B 是 999 格，扣掉 5 筆資料等於 994；新增三筆後是 991。這個數字會隨工作表有幾列而變，拿來判斷「有幾筆沒填」是不對的。

第二個是總計格不要放在同一欄的下面。=SUM(B2:B) 如果寫在 B 欄，範圍會包到自己，Sheets 會回報循環依附性。總計放在欄位上方、旁邊，或另一個工作表。

整欄：B:B

B:B 連第 1 列也算進去。標題是文字，SUM 會略過，所以上面的 312 一樣對。可是換成 COUNTA，差別就出來了：

多的 1 就是標題那格。如果標題剛好是數字，例如欄名是年份 2026，SUM(B:B) 也會把它加進去。

整欄範圍會不會拖慢試算表，常有人提，這篇不下結論，重算怎麼運作在 試算表為什麼越來越慢。

表格參照

選取 A1:B6，點「格式」>「轉換為表格」，預設的表格名稱是「表_1」，公式就能寫成：

在表格最後一列的下面接著輸入，表格會跟著延伸，依官方的說明，這種參照會在表格新增或移除資料時更新。所以新增三筆後預期是 312，公式不用改。建立表格的步驟在 Google 試算表的「表格」功能，語法與限制在 表格參照進階篇。

要注意表格參照只能用在公式裡。圖表、條件式格式、樞紐分析表選範圍的時候，官方說目前不能填表格參照，還是得寫 A1:B9 這樣的範圍。

FILTER：結果自己重算

如果我要的是「杯數 20 以上的品項」，而不是一個總和，那就用 FILTER。D2 輸入：

兩個範圍都寫成開放式，起點相同，大小就一致。新增前結果是 4 列，從 D2 展開到 E5：

新增三筆後，只有烏龍奶茶 46 達標，多一列出現在 D6:E6。青茶 15 和蘋果紅茶 9 沒有達標，不會出現。

結果是往下展開的，所以展開的路徑上不能有東西。如果 D4 有人打了一個字，D2 會顯示 #REF!，提示是「系統未展開陣列結果，以免覆蓋 D4 中的資料。」FILTER 的條件怎麼組在 FILTER 入門。

我怎麼選

• 要在別處放總計、平均，資料欄也不會再往下放東西，我用開放式範圍 B2:B。
• 資料有欄名、常常有人新增，我先轉成表格，公式讀得出來是哪一欄。
• 要依條件挑出一部分資料，用 FILTER。
• 整欄 B:B 我只在確定標題不會被算進去的時候用，因為標題那一列常有人改成數字或日期。

固定範圍並不是不能用，一份寫完就不會再動的表，用 B2:B6 一眼就看得出算到哪。

我是喜特先生，Mr. Sheet，我們下個教學見！