免費軟體與線上工具1,417影音、圖片與 PDF9153C 開箱評測895生活、旅遊與遊戲798iPhone 與 Mac549LINE 與社群533Office 教學492Google 服務470Windows 與電腦461Android 手機402網路新知374AI 工具133
Google 服務

Google 試算表 ARRAY_CONSTRAIN 函數用法:限制陣列回傳大小

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 試算表函數」。