ARRAY_CONSTRAIN 函數的作用是限制陣列或範圍的輸出大小。指定所需的列數與欄數後,就能從原本的資料中擷取左上角開始的特定區塊,常與其他陣列函數搭配使用。
Google 試算表 ARRAY_CONSTRAIN 函數語法
=ARRAY_CONSTRAIN(輸入範圍, 列數, 欄數)| 參數 | 必填 | 說明 |
|---|---|---|
| 輸入範圍 | 是 | 要限制大小的原始範圍或陣列公式結果。 |
| 列數 | 是 | 最終結果要保留的列數。 |
| 欄數 | 是 | 最終結果要保留的欄數。 |
回傳:限制為指定列數與欄數後的新陣列區塊。
ARRAY_CONSTRAIN 函數範例
範例 1:基本範圍限制
取出業績表的前 3 筆資料(包含標題列)與前 2 欄。
| 業務員 | 產品 | 業績 |
|---|---|---|
| 王小明 | 滑鼠 | 5000 |
| 陳怡君 | 鍵盤 | 8000 |
| 林志明 | 螢幕 | 15000 |
| 張雅婷 | 耳機 | 3000 |
在 E1 輸入公式:
=ARRAY_CONSTRAIN(A1:C5, 3, 2)結果:業務員。公式會從 A1:C5 的範圍中,擷取前 3 列與前 2 欄的資料。儲存格 E1 會顯示「業務員」,並自動向下與向右溢出,最終顯示 3 列 2 欄的區塊。
範例 2:搭配陣列公式使用
計算前 3 名員工的獎金(業績乘上 10%),忽略後續名單。
| 姓名 | 業績 |
|---|---|
| 王小明 | 50000 |
| 陳怡君 | 80000 |
| 林志明 | 150000 |
| 張雅婷 | 30000 |
在 D2 輸入公式:
=ARRAY_CONSTRAIN(ARRAYFORMULA(B2:B5 * 0.1), 3, 1)結果:5000。ARRAYFORMULA 會計算所有人的獎金陣列,ARRAY_CONSTRAIN 則將結果限制為 3 列 1 欄。D2 顯示王小明的獎金 5000,並向下溢出顯示前三筆結果。
範例 3:搭配拆分函數限制欄數
將以逗號分隔的字串拆分,但只保留前兩個欄位(姓氏與名字),忽略後續資料。
| 原始資料 |
|---|
| 王,小明,台北,工程師 |
| 陳,怡君,台中,設計師 |
在 C2 輸入公式:
=ARRAY_CONSTRAIN(SPLIT(A2, ","), 1, 2)結果:王。SPLIT 函數會將字串拆分成 4 個欄位,ARRAY_CONSTRAIN 將其限制為 1 列 2 欄。C2 顯示「王」並向右溢出顯示「小明」,忽略後續的城市與職稱。
使用 ARRAY_CONSTRAIN 的技巧與注意事項
- 當使用的陣列公式會產生極大量的運算結果,但實際只需要前幾筆時,使用 ARRAY_CONSTRAIN 可以減少試算表的運算負擔。
- 如果指定的列數或欄數大於原始資料的範圍,函數不會報錯,而是直接回傳原始範圍的最大可用大小。
- 在處理單一欄或單一列的陣列時,仍必須同時指定列數與欄數,不可省略任何一個參數。
ARRAY_CONSTRAIN 常見錯誤與解決方法
| 錯誤 | 原因 | 解決方法 |
|---|---|---|
| #REF! | 公式所在儲存格的下方或右方已有其他資料,導致陣列無法溢出。 | 清除阻擋陣列展開的儲存格內容,讓函數有足夠的空白範圍顯示結果。 |
| #VALUE! | 列數或欄數設定為 0 或負數。 | 檢查公式參數,將列數與欄數修改為大於 0 的正整數。 |
Excel 有 ARRAY_CONSTRAIN 嗎?
Excel 沒有 ARRAY_CONSTRAIN 函數。在 Excel 中若要限制陣列大小,通常會使用 TAKE 函數(例如 =TAKE(範圍, 列數, 欄數))來達成完全相同的擷取效果。
常見問題
可以只限制列數,不限制欄數嗎?
可以,只要將欄數參數設定為一個極大的數字,或者設定為原始資料的總欄數即可。這樣就能在限制列數的同時,保留所有的欄位資料。
為什麼公式結果只顯示一個值,沒有出現陣列?
如果輸入範圍本身不是陣列,或者沒有搭配 ARRAYFORMULA 等會產生陣列的函數,ARRAY_CONSTRAIN 就只會處理單一儲存格。請確認輸入來源是否為有效的陣列。
ARRAY_CONSTRAIN 和 SORTN 有什麼不同?
ARRAY_CONSTRAIN 只是單純從左上角開始截斷陣列的列數與欄數;而 SORTN 則是可以先將資料排序後,再回傳指定數量的前幾名列數資料。
相關函數
這篇收錄在「Google 試算表函數」。




