在 Microsoft 365 版 Excel for Mac 或 Excel 2024 for Mac 中，結合 CHOOSECOLS、TOCOL、HSTACK 和 FILTER，將重複的美術素材標籤與數量轉換為庫存列。以下範例公式會保留配對順序、零數量和未完成的項目。
本指南適用於使用 Mac 桌面版的使用者，其工作表中重複出現屬性欄，後面接著數量欄。此處的每個屬性都是美術素材標籤，例如 Circle Tile。目標是產生兩欄的庫存清單，然後查詢數量，同時確保數量與標籤仍保持配對。
哪些 Mac 版 Excel 支援此公式？
Microsoft 明確列出 Microsoft 365 版 Excel for Mac 和 Excel 2024 for Mac 支援全部三個重整函數：CHOOSECOLS、TOCOL 和 HSTACK。主要公式請使用其中一個版本。
Excel 2021 for Mac 包含動態陣列、FILTER、LET 和 XLOOKUP，但這並不能證明它支援較新的重整函數。Microsoft 的 Excel 2021 for Mac 功能說明文件列出其可用的陣列與查閱功能；上述三個函數頁面並未列出 Excel 2021。
請在應用程式的「關於」對話方塊中查看您的版本和版本類型。Microsoft 的 Office 版本指南說明，Mac 對話方塊會顯示版本與授權類型。如果您的版本不包含重整函數，請使用 Microsoft 365 版 Excel for Mac 或 Excel 2024 for Mac 來採用自動方法，或使用下方說明的手動堆疊方式。
建立完整的合成範例
在空白工作表的 A1:F4 輸入下列資料。第 3 列中間的兩個儲存格請保持真正空白。所有標籤和數量僅供示範。
| 屬性 1 | 數量 1 | 屬性 2 | 數量 2 | 屬性 3 | 數量 3 |
|---|---|---|---|---|---|
| Circle Tile | 4 | Line Texture | 2 | Dot Pattern | 0 |
| Arc Motif | 3 | Grid Texture | 5 | ||
| Circle Tile | 7 | Wave Pattern | 6 | Square Tile | 1 |
每組相鄰的配對代表一筆庫存項目。預期輸出會先讀取每個來源列，再往下讀取下一列。Circle Tile 出現兩次，因為這是兩筆不同的項目；合併重複標籤則是另一項工作。
此方法假設每個標籤後面緊接著其數量，而且所選欄位都符合這種重複模式。如果您的工作表還包含系列名稱或其他必須與每筆項目一併保留的欄位，這個兩欄輸出不會保留該欄位。套用公式前，請先決定庫存需要哪些欄位。
將重複的配對轉換為庫存列
在 H1 輸入 Asset，並在 I1 輸入 Quantity。請保持這兩個標題下方的輸出區域空白。將完整公式貼到 H2，然後按 Return。如 Microsoft 的陣列公式指南所述，動態陣列公式只需輸入一次，便會展開至相鄰儲存格。
=LET(
source,A2:F4,
clean,IF(source="","",source),
labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
keep,((labels<>"")+(quantities<>""))>0,
FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)
預期結果會占用 H2:I9：
| 素材 | 數量 |
|---|---|
| Circle Tile | 4 |
| Line Texture | 2 |
| Dot Pattern | 0 |
| Arc Motif | 3 |
| Grid Texture | 5 |
| Circle Tile | 7 |
| Wave Pattern | 6 |
| Square Tile | 1 |
此範例的配對順序、八筆輸出項目、零值保留情況，以及總數 28，均已透過小型確定性 Python 計算檢查。Excel 公式則已根據 Microsoft 說明的函數語法檢視，但未在 Excel 中執行。
公式如何維持配對順序
LET 會為中間結果命名，讓公式可以重複使用這些結果。名稱 source、clean、labels、quantities 和 keep 只存在於此公式中；您不需要建立具名範圍。Microsoft 在 LET 參考資料中說明了此行為。
CHOOSECOLS 會選取第 1、3、5 欄的標籤，以及第 2、4、6 欄的數量。這些數字是所選來源範圍內的位置。Microsoft 的 CHOOSECOLS 說明文件確認，此函數會從陣列傳回指定欄。
兩個 TOCOL 函數呼叫都使用 0 保留每個位置，並使用 FALSE 逐列掃描。由於兩者以相同順序遍歷對應位置，每個標籤都會與其數量保持配對。Microsoft 在 TOCOL 參考資料中說明了這兩個引數。
HSTACK 會將產生的兩欄並排放置。篩選前兩欄都包含九個位置，因此長度相同。Microsoft 說明，HSTACK會水平附加陣列；若陣列的列數不同，較短的陣列會以錯誤值填補。
最後，keep 會選取包含標籤、數量或兩者的位置。FILTER 只會移除完全空白的配對。其最後一個引數會在沒有剩餘項目時提供訊息，語法依據 Microsoft 所說明的 FILTER 語法。
保留空白和零值，並顯示未完成的項目
空白數量和數值零代表不同資訊。零是已輸入的計數；空白則仍需要判讀。Microsoft 的 TOCOL 範例顯示，未處理的空白儲存格可能會傳回零。因此，clean 步驟會使用 IF，在重整前將空白來源儲存格以空文字傳回。Microsoft 在 IF 參考資料中說明了條件式傳回值。
此公式會刻意保留未完成的配對，讓它們清楚顯示。依賴結果前，請先執行下列檢查：
| 來源配對 | 輸出行為 | 處理方式 |
|---|---|---|
| 標籤和數值數量 | 兩者都保留 | 檢查數量是否正確 |
| 標籤和零值 | 保留零值 | 若零是刻意輸入的計數，請保留 |
| 兩個儲存格都空白 | 移除配對 | 不需修正 |
| 有標籤但數量空白 | 保留標籤和空白數量 | 填入數量 |
| 標籤空白但有數量 | 保留空白標籤和數量 | 填入標籤 |
避免分別從標籤陣列和數量陣列移除空白。如果一個位置缺少標籤，另一處又缺少數量，分別移除空白可能會讓陣列錯位，造成錯誤配對。先保留所有位置，直到合併之後才篩選，是此公式的關鍵設計選擇。
含有空格的儲存格即使看似空白，此公式仍不會將其視為空白。請在來源中處理這類項目。同樣地，公式會保留以文字輸入的數量，不會自動轉換。計算總數前，請先檢查這些值。
查詢單筆項目或傳回所有相符項目
在 K2 輸入 Circle Tile。若要擷取第一筆數量，請在 L2 輸入下列公式：
=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)
H2# 指的是從 H2 開始的整個溢出結果，因此查閱範圍會隨其大小變化。Microsoft 在溢出範圍運算子說明文件中解釋了此標記。
結果為 4。明確的比對模式 0 會要求完全相符，而預設搜尋會傳回第一筆相符項目。Microsoft 的 XLOOKUP 參考資料說明了這兩種行為。該資料也確認，Excel 2016 和 Excel 2019 不提供 XLOOKUP。
如果重複標籤具有意義，只查找第一筆就不夠。若要傳回所有 Circle Tile 項目，請將下列完整替代公式放在 N2，並在 N:O 欄預留空間：
=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")
此公式會傳回兩列：Circle Tile 的數量分別為 4 和 7。Microsoft 的 FILTER 參考資料說明，FILTER 會傳回符合指定條件的列。如果只需一筆相符項目來回答問題，請使用第一筆查閱公式；若需檢視每次出現的項目，則使用篩選結果。
驗證結果並調整來源範圍
針對範例，請確認輸出有八列，並檢查 Dot Pattern 的數量仍為零。接著比較來源數量總和與重整後的數量總和。在兩個空白儲存格中輸入以下公式：
=SUM(B2:B4,D2:D4,F2:F4)
=SUM(CHOOSECOLS(H2#,2))
兩者都應傳回 28。Microsoft 的 SUM 說明文件支援加總多個範圍，並說明文字值會被忽略。因此，總和相同並不能證明所有數量都是數值，也無法偵測數量是否與標籤對調。請同時檢查標籤與數量的配對。
若來源列數較多，請將主公式中的 A2:F4 改為實際範圍，例如 A2:F100。參照範圍以外的列不會納入。若有四組重複配對，請將來源改為八欄，例如 A2:H100，並將標籤位置更新為 1,3,5,7，數量位置更新為 2,4,6,8。
擴增至八個來源欄時，請將輸出移至空白區域，例如 J2，並將下游溢出參照更新為 J2#。請將來源與輸出分開。無效的欄位置會產生錯誤，詳見 CHOOSECOLS 錯誤說明。
排除輸出受阻問題或使用手動堆疊方式
如果主公式傳回 #SPILL!，請選取公式儲存格並檢查預定輸出區域。將該區域中的既有內容移開，並檢查是否有合併儲存格。請將公式放在 Excel 表格之外：Microsoft 在 #SPILL! 疑難排解指南中說明，溢出陣列公式無法在表格內溢出，也無法溢出至合併儲存格。
如果來源中含有錯誤，請先修正來源項目，再使用庫存清單。公式不會略過錯誤；Microsoft 指出，FILTER 的 include 陣列若有錯誤，函數就會傳回錯誤。若將整個轉換公式包在錯誤抑制函數中，可能會隱藏需要修正的項目。
若是在 Excel 2021 for Mac 中處理較小的工作表，可手動建立相同的兩欄結構：將 A2:B4 複製到 H2:I4，將 C2:D4 複製到 H5:I7，再將 E2:F4 複製到 H8:I10。只移除兩個儲存格都空白的列。這種方式會依配對區塊堆疊項目，因此順序與主公式的來源列順序不同；來源變更後也需要重做。
完成手動設定後，Excel 2021 for Mac 可搭配固定範圍使用其已說明的 XLOOKUP 功能：
=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)
實用的最終庫存清單會保留所有有意義的配對、顯示未完成項目，並採用適合重複標籤的查閱方式。請先用範例確認這些特性，再擴大來源範圍以符合您的工作表。
來源
CHOOSECOLS 函數 — Microsoft 支援。
TOCOL 函數 — Microsoft 支援。
HSTACK 函數 — Microsoft 支援。
FILTER 函數 — Microsoft 支援。
LET 函數 — Microsoft 支援。
IF 函數 — Microsoft 支援。
XLOOKUP 函數 — Microsoft 支援。
SUM 函數 — Microsoft 支援。
溢出範圍運算子 — Microsoft 支援。
陣列公式的指引與範例 — Microsoft 支援。
如何修正 #SPILL! 錯誤 — Microsoft 支援。
Excel 2021 for Mac 新增功能 — Microsoft 支援。
關於 Office：我使用的是哪個 Office 版本？ — Microsoft 支援。