若要讓 Excel 庫存查找選取最符合條件的規則，請先找出符合資格的列，再計算這些列指定了多少欄位。傳回得分唯一最高之列的數量，並標示平手情況。本指南將示範如何在 Mac 版 Excel 中，為合成美術資產建立這項結果。

哪些 Mac 版 Excel 支援這種做法？

以下公式使用 LET、XLOOKUP 和動態陣列計算。Microsoft 說明，Microsoft 365 Mac 版 Excel、2024 Mac 版 Excel 和 2021 Mac 版 Excel 都支援這些功能。其 2021 Mac 版 Excel 功能指南明確列出 LET、XLOOKUP 和動態陣列。2021 Mac 版 Excel 的新增功能、LET 函數。

根據 Microsoft 明確的相容性說明，2016 和 2019 版 Excel 不支援 XLOOKUP。這個公式必須使用上述支援的 Mac 版之一；改變公式的輸入方式，無法補上缺少的函數。本文稍後的選用檢查公式使用 FILTER，上述三種支援的 Mac 版也都有相關文件說明。XLOOKUP 函數、FILTER 函數。

在庫存中，「最具體」代表什麼？

在這個範例中，你要根據三個屬性查找指派給美術資產的數量：資產標籤、表面處理和尺寸。規則欄位空白代表該規則接受該屬性的任何值。欄位有值時，則必須與對應的輸入相符。在符合資格的規則中，填入欄位最多的規則勝出。

這是本範例明確採用的活頁簿政策。Excel 不會根據你的描述自行推斷這項政策。在此，每個屬性的權重相同：指定資產和表面處理的規則，具體程度與指定資產和尺寸的規則相同。如果兩者都符合且得分最高，公式會回報平手。

單純的 XLOOKUP 本身無法決定這種優先順序。Microsoft 說明，該函數預設會傳回第一個符合的項目。反轉搜尋只會改變搜尋起點，不會依規則的具體程度排序。因此，公式會先計算勝出的列，再用 XLOOKUP 取得該列的數量。XLOOKUP 函數。

設定合成美術資產規則

在空白工作表的 A1:D6 輸入以下標題和值。Excel 中必須讓下表的空白儲存格保持空白。所有標籤和數量僅供示範。

工作表列 A：資產 B：表面處理 C：尺寸 D：數量
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

第 2 列的規則是刻意設定的預設值：它沒有指定任何屬性，具體程度為零。第 3 列指定一項屬性，第 4 列指定兩項，第 5 列指定三項。這些數量是彼此替代的指派數量，因此查找會傳回一個值，不會把預設值和例外規則的數量相加。

在 H1:K1 輸入標題 Asset、Finish、Size 和 Result。接著在 H2 輸入 Tile，在 I2 輸入 Matte，並在 J2 輸入 Small。三個輸入屬性都要填入值。在這種設計中，只有規則表中的空白才代表不限制條件；輸入不完整時會顯示訊息。

使用數值型數量，適用時也要輸入數值型的零。範圍只限於實際的規則列。若將範圍延伸到未使用的空白列，依照此比對政策，就會多出全空白規則。

輸入選取勝出規則的公式

選取 K2，輸入以下完整公式，然後按 Return。此公式會使用上方定義的規則表和輸入儲存格。動態陣列計算會在公式內執行，最終結果只佔用一個儲存格。Microsoft 的文件說明，動態陣列公式可直接正常輸入，不需要使用舊式的陣列輸入按鍵組合。動態陣列公式與溢出陣列行為。

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

使用範例輸入時，K2 應傳回 7。四條規則都符合資格，但只有第 5 列指定了全部三項屬性。

資格判定與具體程度的運作方式

LET 為範圍和中間結果指定區域名稱，讓選取流程更容易檢查。Microsoft 說明，這些名稱只在公式的範圍內有效。LET 函數。

每個屬性的資格判定運算式都會檢查規則儲存格是否空白，或是否等於輸入值。相加會合併這兩種可能，而比較運算會將結果轉換為邏輯測試。將三項屬性測試相乘，表示所有條件都必須成立。Microsoft 在 FILTER 範例中也使用相同的相乘方式，以 AND 邏輯合併陣列條件。FILTER 函數。

specificity 會計算規則中有值的欄位數量。雙重一元運算子會將每個非空白測試轉換為數字，產生零到三的分數。IF 在排名計算中將不符合資格的列指定為負一分；接著，MAX 會找出符合資格列中的最高分。負一可確保不符合資格的列，分數低於具體程度為零的不限條件預設規則。IF 函數、MAX 函數。

winner 只會標記符合資格且得分最高的列。公式會先檢查是否沒有符合項目，以及是否有多個勝出項目，之後才傳回數量。最後的數值檢查會刻意排除空白數量或以文字儲存的數字。Microsoft 說明，ISNUMBER 不會將文字格式的數字轉換為數值。數值零仍是有效結果。IS 函數。

擴充工作表前先檢查結果

將 H2:J2 依序改成下列各種組合。這些預期結果是根據範例規則推導而來，並非實際的庫存觀察值。

資產 表面處理 尺寸 預期結果 勝出條件
Tile Matte Small 7 指定三個欄位
Tile Matte Large 12 資產和表面處理
Tile Gloss Small 18 僅資產
Disc Gloss Large 10 資產和尺寸
Arc Matte Small 30 不限條件的預設規則

接著，暫時將第 6 列改為資產 Tile、表面處理 Matte、尺寸 Small，數量設為 9。還原原本的輸入後，結果應變成 Tie: review rules：現在有兩條規則都指定了三項相符屬性。即使數量相同，仍會出現平手，因為規則選擇有歧義。之後請還原原本的第 6 列。

若要測試無符合項目的分支，請暫時將公式中的四個規則範圍改成從第 3 列開始，排除預設規則。輸入 Arc、Matte、Small 時，應傳回 No matching rule。完成後將範圍還原為從第 2 列開始。若要測試數量檢查，請在使用原本輸入時清除 D5；預期結果為 Check rule quantity，然後還原 7。

檢查重疊規則並解決平手

若要查看目前輸入符合的所有規則，請在 M2 輸入這個獨立公式。事先清空 M2:P6。輸出會依原有順序顯示符合的規則列，不會為它們排序。

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER 會傳回其布林值包含陣列所選取的列；最後一個引數則會在沒有符合項目時提供訊息。在這個檢查輸出中，來源儲存格的空白可能會顯示為零；請繼續將原始規則表中的空白儲存格視為不限條件。FILTER 函數。

檢查公式會溢出到相鄰儲存格。請將它放在 Excel 表格之外，並保持輸出區域空白。Microsoft 說明，溢出陣列公式不支援在表格內使用，而輸出儲存格已有內容時可能會造成 #SPILL!。動態陣列公式與溢出陣列行為。

解決平手的方法包括更正重複規則、縮小重疊規則的適用範圍，或定義額外的優先政策。在此範例中，「資產加表面處理」和「資產加尺寸」的規則都得兩分。除非你將偏好編入公式，否則活頁簿無法判定哪個屬性較重要。依本文採用的政策，移動列的位置不應影響答案。

擴充查找而不改變其含義

若要查找另一項資產，請在 H3:J3 輸入其三項屬性，並將 K2 複製到 K3。輸入參照會隨公式移動，而美元符號會固定規則範圍。若在第 6 列之後新增規則，請一致地擴充主公式中的四個範圍，以及檢查公式的兩個維度。

每項屬性都放在各自的欄位中，並使用一致的標籤，不要帶入多餘空格。此公式會比對完整欄位值，不會在組合而成的資產描述中搜尋片段。數量檢查會確認選取的值是數值；因此，請將其他庫存要求（例如數量必須為非負整數）套用在來源資料上。

這項查找採用明確的判斷順序：要求輸入完整、找出符合資格的規則、找出具體程度最高者、排除平手，並傳回選定的數值型數量。這個順序讓一般預設規則和詳細例外規則可以搭配使用，同時清楚呈現尚未解決的選擇。

來源