在濾溢查閱外層包裹 IFNA，從第二個參考表填補其 #N/A 結果。此 Mac 版 Excel 範例可比對美術資產標籤、保留主要表中已找到的數量，並清楚標示兩個表中都缺少的標籤。

哪些 Mac 版 Excel 版本支援此方法？

使用 Microsoft 365 版 Mac 版 Excel、Mac 版 Excel 2024 或 Mac 版 Excel 2021。Microsoft 將這些版本列為 XLOOKUP 和 IFNA 的支援版本。其 XLOOKUP 文件明確排除 Excel 2016 和 Excel 2019，儘管這些版本出現在頁面更廣泛的適用性清單中。以下公式以支援的 Mac 版本為目標。 XLOOKUP 函數、IFNA 函數

任務很具體：你有一欄請求的資產標籤，想要每個標籤對應一個數量。主要參考優先；只有在主要查閱產生 #N/A 時，第二個參考才提供數量。下列所有標籤和數量均為合成資料。

設定兩個參考表和請求資產清單

在一個活頁簿的空白工作表上開始。使用下列儲存格位置，以便公式在不變更參考的情況下運作。這些小型參考表是一般工作表範圍；不需要將它們轉換為 Excel 表格物件。

在 D1:E4 輸入主要參考：

D 欄儲存格 資產標籤 E 欄儲存格 數量
D1 Asset E1 Quantity
D2 Leaf outline E2 8
D3 Dot texture E3 0
D4 Tile pattern E4 12

在 G1:H5 輸入第二個參考：

G 欄儲存格 資產標籤 H 欄儲存格 數量
G1 Asset H1 Quantity
G2 Dot texture H2 9
G3 Curve sketch H3 5
G4 Grain texture H4 3
G5 Leaf outline H5 10

在 J1 輸入 Requested asset，然後將這五個標籤依序放入 J2:J6，每個儲存格一個：

  1. Leaf outline
  2. Dot texture
  3. Curve sketch
  4. Grain texture
  5. Arc motif

保持數量為數字，並填滿兩個參考表中的每個數量儲存格。在此範例中，零表示記錄的數量為零，不代表資產遺漏。

建立標籤的濾溢清單

在 A1、B1 和 C1 輸入標題 Asset、Primary quantity 和 Completed quantity。讓 A2:C20 保持空白，然後在 A2 輸入此公式：

=FILTER(J2:J20,J2:J20<>"","")

這會篩選掉請求清單中的空白儲存格。使用提供的資料時，它會將五個資產標籤濾溢至 A2:A6。Microsoft 為上述三個 Mac 版本編寫了 FILTER 文件，並說明其最後一個參數提供在無列符合條件時的結果。此處該結果為空字串。 FILTER 函數

後續公式使用 A2# 參考此整個濾溢範圍。# 運算子會追隨濾溢範圍的大小，避免將目前端點硬式編碼為 A6。Microsoft 在其 濾溢範圍運算子文件 中描述此行為。

從第二個參考填補遺漏數量

首先，在 B2 建立主要查閱：

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

這會搜尋主要資產欄並回傳對應數量。XLOOKUP 預設使用完全比對，若無相符項且未提供替代結果則回傳 #N/A。 XLOOKUP 函數

對於範例資料，B2:B6 應包含 8、0、#N/A、#N/A 和 #N/A。保留那些遺漏比對錯誤供下一步使用。

現在在 C2 輸入完成公式：

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA 會檢查主要濾溢結果。在包含 #N/A 的位置，公式會從第二個查閱提供對應結果。其他主要結果保持不變。Microsoft 專門記載了當第一個參數為陣列公式時，IFNA 會回傳結果陣列。 IFNA 函數

預期工作表結果為：

A 欄資產 B 欄主要數量 C 欄完成數量
Leaf outline 8 8
Dot texture 0 0
Curve sketch #N/A 5
Grain texture #N/A 3
Arc motif #N/A Not listed

將這些列視為優先順序測試。Leaf outline 保持 8，即使第二個參考包含 10。Dot texture 保持 0，即使第二個參考包含 9。Curve sketch 和 Grain texture 取得其次要數量。Arc motif 保持可見的未解決狀態，因為兩個參考都不包含它。

兩個陣列必須以相同順序描述相同的請求資產。在此設定中，兩者都源自 A2#。如果調整現有活頁簿，請勿將以一種方式排序的主要結果與基於不同順序標籤清單的後備查閱結合。

不需要中間欄時使用單一公式

要將主要查閱和完成合併，請將 C2 中的公式替換為此完整替代方案：

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

這使用相同的範例資料，應產生相同的五個完成數量。欄 B 不再為計算所需。在設定活頁簿時保留兩階段版本，可更容易看出每個結果來自哪個參考。

判斷結果是否真正遺漏

在調整公式前，先檢查第一個查閱回傳的內容。遺漏標籤、看似空白的結果、數量為零需要不同的決策。

主要結果 此完成公式的行為 要檢查的項目
數值數量，含 0 保留它 確認它是預期的主要數量。
#N/A 使用次要查閱 確認錯誤代表標籤缺失。
空字串、"" 保留它 檢查主要公式是否隱藏遺漏比對。
其他錯誤 保持可見 修復底層公式或參考。

例如，如果現有主要查閱使用 "" 作為其 if_not_found 參數，IFNA 無法將該空字串識別為 #N/A。從主要查閱移除該參數（如 B2 所示），或使用上述合併公式。完成邏輯依賴保留可識別的遺漏比對結果。 XLOOKUP 函數、IFNA 函數

也要檢查參考數量本身。IFNA 回應錯誤值；它不測試數量儲存格是否已填入。若主要記錄有未完成的數量，請先完成該記錄或為未完成數量定義單獨規則，再套用此工作流程。

還有一項區別：主要數量資料中現有的 #N/A 錯誤也會觸發此後備機制。此範例的先決條件是乾淨的數值數量欄，#N/A 僅來自不成功的查閱。在將每次替換視為遺漏資產比對前，請先檢查此條件。

檢查優先順序並保持濾溢運作

使用範例的五列作為小型驗收測試，再替換為自己的資產標籤。它們共同測試主要比對、有效零、兩次次要比對，以及兩個參考都缺少的標籤。

為了額外檢查，暫時將 Leaf outline 的次要數量從 10 改為 14。完成數量應保持 8。然後將 Curve sketch 的次要數量從 5 改為 6；其完成數量應變為 6。之後恢復原值。這些是為你的活頁簿提供的說明性檢查，非回報的 Excel 測試結果。

在每個參考中，每個資產標籤只保留一筆預期記錄。XLOOKUP 通常從第一項搜尋並回傳第一個相符項，因此重複標籤不應預期會合併數量。在依賴此查閱設計前，先解決重複標籤的含義。 XLOOKUP 函數

修正輸出範圍被封鎖

僅在濾溢公式的起始儲存格輸入。Excel 會自動填滿鄰近儲存格，且只有濾溢範圍的左上角儲存格可編輯。將公式置於 Excel 表格物件之外；Microsoft 支援表格為來源資料，但不支援表格內的濾溢公式。 動態陣列公式與濾溢陣列行為

若看到 #SPILL!，選取公式儲存格以顯示預期輸出邊界。使用錯誤警示的 選取阻礙儲存格 選項（若可用），移動任何阻擋結果的現有項目。合併儲存格也可能阻止濾溢；移動輸出位置或取消合併受影響的儲存格。 如何修正 #SPILL! 錯誤

故意擴充輸入

請求清單公式檢查 J2:J20。在該範圍內新增的標籤會自動包含；低於該範圍輸入的標籤需擴充公式範圍。同樣，參考查閱使用固定範圍，結束於第 4 和 5 列。新增記錄時，請同時擴充每個資產範圍及其對應的數量範圍。

對於較大的工作表，具有結構化參考的 Excel 表格可使來源範圍隨列新增或移除而調整。將輸出公式保留在工作表格線中，置於那些表格之外。 動態陣列公式與濾溢陣列行為

將此範例的輸入保留在同一活頁簿中。Microsoft 記載活頁簿間的動態陣列支援有限：連結的動態陣列要求兩個活頁簿保持開啟，關閉來源後重新整理可能產生 #REF!。 動態陣列公式與濾溢陣列行為

預期列一致後，使用完成欄作為資產數量結果。保留主要優先規則、保留有意義的零，並將兩個參考都缺少的標籤標記為 Not listed，以便你刻意完成參考資料。

來源