使用 FILTER 篩選出完全相符的美術資產類別，再使用 XLOOKUP 傳回不超過計數的最大數量門檻所對應的等級。本逐步指南將以合成標籤和邊界測試，建立完整的 Excel for Mac 範例。

哪些 Excel for Mac 版本支援此公式？

下方的公式使用 LET、FILTER 和 XLOOKUP。Microsoft 文件列出這三個函數適用於 Excel for Microsoft 365 for Mac、Excel 2024 for Mac 和 Excel 2021 for Mac。Microsoft 也在Excel 2021 for Mac 的新功能概覽中介紹這些函數。

Excel 2016 和 Excel 2019 無法計算 XLOOKUP；Microsoft 明確說明了此限制。若要使用這個確切公式，請使用上述其中一個 Mac 版本。在較舊版本中開啟含有該函數的活頁簿，並不會因此取得函數支援。請參閱 Microsoft 的 XLOOKUP 文件。

如果不確定安裝的是哪個版本，請在應用程式的「關於」對話方塊中查看版本和授權類型。Microsoft 在關於 Office：我使用的是哪個 Office 版本？中說明如何查看這些資訊。

設定各類別的最低數量

假設你要統計可重複使用的圖形資產，並希望為每個類別指定描述性的庫存等級。這項工作有兩個規則：類別必須相符，且數量必須達到某個等級的最低門檻。以下所有標籤和數量僅供示範。

在空白工作表的 A1:C7 輸入下表。標題位於第 1 列，六筆資料位於第 2 至 7 列。最低數量請輸入數字，並填妥每個類別和等級儲存格。

類別 最低數量 等級標籤
紋理圖塊 5 精簡
紋理圖塊 20 擴充
紋理圖塊 50 大量
形狀印章 5 精簡
形狀印章 30 擴充
形狀印章 80 大量

接著，在 E1 輸入「要檢查的類別」，在 F1 輸入「數量」，在 G1 輸入「庫存等級」。在 E2 輸入「紋理圖塊」，並在 F2 輸入數字 34。D 欄保持空白，將參照資料與輸入項目隔開。

將最低數量視為包含該數值的下限。對於紋理圖塊，精簡從 5 開始，擴充從 20 開始，大量從 50 開始。因此，數量 34 屬於擴充。對於形狀印章，相同標籤的起始數量不同：數量 20 仍屬於精簡。

撰寫公式前，先決定你自己的邊界代表最低值還是最高值。本範例全程使用最低值。低於 5 的數量不屬於任何等級，最後一個等級則適用於所有高於其起始數量的數值。這些是本範例明確採用的設計選擇；如果你的規則不同，請調整參照資料。

輸入完整的類別與數量公式

選取 G2，將以下完整公式貼到公式列，然後按 Return。公式使用上述確切的工作表配置，不需要定義名稱或輔助欄。

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

對於「紋理圖塊」和 34，預期結果是「擴充」。公式會傳回單一標籤，儘管其中間計算使用了陣列。現代動態陣列公式可直接輸入；Microsoft 在動態陣列公式與溢出陣列行為中說明，按 Enter 即可確認公式。

類別篩選器如何讓陣列保持對齊

運算式 categories=category 建立名為 keep 的包含陣列。對於本範例的輸入，它會選取三列紋理圖塊資料。兩個 FILTER 呼叫都使用同一個陣列，依序產生最低數量 5、20 和 50，以及相對應的標籤「精簡」、「擴充」和「大量」。

Microsoft 說明，FILTER 使用維度與來源範圍相符的布林包含陣列來選取資料。兩欄使用相同的包含陣列，可維持兩者的對應關係。請參閱 FILTER 函數。

外層的 LET 為來源範圍、輸入項目和包含陣列指定易讀的名稱。這些名稱只在此公式內存在；你不需要在活頁簿中另外建立它們。Microsoft 在 LET 函數文件中說明此行為。

數量如何決定等級

第五個 XLOOKUP 引數 -1 會要求精確比對數量，或比對下一個較小的值。在篩選後的紋理圖塊資料列中，34 會選取最低值 20，並傳回「擴充」。精確數量 50 則會選取「大量」。Microsoft 在 XLOOKUP 函數文件中定義了這些模式。

公式也會區分未知類別與低於第一個等級門檻的數量。SUM(--keep) 會計算符合的類別資料列數。如果沒有符合項目，公式會在嘗試篩選查詢前傳回「找不到類別」。如果類別存在，但所有最低數量都高於計數，查詢就會傳回「低於第一個等級」。

測試邊界與類別隔離

一次將 E2 和 F2 替換為以下配對之一。這些檢查比只測試中間值更實用，因為它們會測試第一個門檻、精確邊界、類別差異和找不到類別的情況。

E2 中的類別 F2 中的數量 預期結果
紋理圖塊 0 低於第一個等級
紋理圖塊 5 精簡
紋理圖塊 19 精簡
紋理圖塊 20 擴充
紋理圖塊 34 擴充
紋理圖塊 50 大量
形狀印章 20 精簡
形狀印章 34 擴充
形狀印章 100 大量
色票 34 找不到類別

兩筆數量為 20 的測試尤其值得注意。紋理圖塊必須傳回「擴充」，而形狀印章必須傳回「精簡」。如果兩者都傳回「擴充」，請檢查數量查詢是否使用了篩選後的類別資料列。

以上十個預期結果已使用實作所述最低門檻規則的簡短 Python 計算進行核對。Excel 公式本身已對照 Microsoft 文件所述的語法檢查，但未在 Excel 中執行。

也請檢查輸入提示：類別空白或數量以文字輸入時，應傳回「請輸入類別和數字數量」；數值為負數時，應傳回「請使用零或正數」。資產數量請使用整數。公式會檢查數量是否為數字且非負，但不會限制為整數。

診斷非預期結果並擴充工作表

請依序進行以下檢查，以免把格式問題誤當成需要變更等級規則。

  1. 檢查類別文字。 比對輸入值與參照標籤，包括空格。Microsoft 在查詢疑難排解指南中指出，非預期字元和隱藏空格是常見的查詢問題。
  2. 檢查數值儲存格。 數量輸入值和最低數量應為數字，而非看似數字的文字。同一份 Microsoft 指南也指出，不一致的資料類型會造成查詢問題。
  3. 檢查範圍是否對齊。 類別、最低數量和標籤範圍必須涵蓋相同的資料列。本範例中的所有範圍都從第 2 列開始，到第 7 列結束。
  4. 檢查重複規則。 每組類別和最低數量只能對應一個等級標籤。如果兩列為相同邊界指定不同標籤，請在參照資料中消除這項歧義。
  5. 檢查邊界慣例。 如果結果看起來高或低了一個等級，可能表示你的參照值是最高值，而此公式預期使用最低值。

如要目視檢查，請在未使用的儲存格（例如 I2）輸入以下診斷公式。當 E2 中為「紋理圖塊」時，它應顯示完整的三列紋理圖塊資料。這是獨立的診斷公式，並非 G2 的替代公式。

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

請預留足夠空間，讓診斷結果向右擴展三欄、向下擴展三列。獨立的陣列結果會溢出到相鄰儲存格；若儲存格已有內容，就可能出現 #SPILL!。Microsoft 在動態陣列公式與溢出陣列行為中說明此行為。

新增參照資料列時，請在主公式中一併延伸所有三個來源範圍。例如，若最後一列資料變成第 10 列，請將定義改為 categories,$A$2:$A$10、minimums,$B$2:$B$10 和 labels,$C$2:$C$10。然後針對新增類別重做邊界測試。

若要處理多列輸入，請在 E2:F2 下方輸入更多類別和數量配對，然後向下複製 G2。來源範圍因參照中含有貨幣符號而保持固定；輸入項目的列號則會遞增。請明確設定各類別的邊界，並使用成對的類別測試確認每個傳回標籤都符合預期的庫存規則。

來源