我有一組用逗號分隔的數字。每組代表一系列與項目相關的唯一數字。如何編寫公式來在參考表中搜尋列表中任意數量的項目，並傳回硬編碼值？
我將在下面為每張紙添加示例（去識別化）（表1-5）和預期/預期輸出。我想這與 SUM、FREQUENCY、INDEX 或其他一些陣列公式有關，但我無法弄清楚。你可以看看我的問題這裡也，但它有很多相同的訊息。
我用過“磷屁股” “F艾爾”和“中號iss」但一切都有效。
邏輯：
該公式應執行以下步驟。
- 從 C 列的每個儲存格中提取數字列表表4。
- 搜尋表3（D 列）用於列表中數字的實例。
2b.如果清單中出現任何數字，請檢查審核是否失敗（由 B 列中的文字「失敗」表示）表3）。
2c.如果有，則列印F。
2d.如果沒有，則列印磷。 - 如果清單中沒有出現任何數字，則列印中號。
表格：
表格1
該表為主要參考表。它根據名稱、伺服器和項目列出了每個 UID。
Unique ID Name Server Address Project
10000 Company 1 Server 1 Project 1
10001 Company 2 Server 2 Project 2
10002 Company 3 Server 3 Project 3
10003 Company 3 Server 3 Project 4
10004 Company 3 Server 3 Project 5
10005 Company 3 Server 3 Project 6
10006 Company 4 Server 4 Project 7
10007 Company 4 Server 4 Project 7
10008 Company 4 Server 4 Project 7
10009 Company 5 Server 5 Project 8
10010 Company 6 Server 6 Project 9
10011 Company 7 Server 7 Project 10
10012 Company 8 Server 8 Project 11
10013 Company 8 Server 8 Project 11
10014 Company 8 Server 8 Project 11
10015 Company 8 Server 8 Project 11
10016 Company 8 Server 8 Project 11
10017 Company 8 Server 8 Project 11
10018 Company 8 Server 8 Project 11
10019 Company 8 Server 8 Project 11
10020 Company 8 Server 8 Project 11
10021 Company 9 Server 9 Project 12
10022 Company 10 Server 10 Project 13
10023 Company 11 Server 10 Project 14
10024 Company 12 Server 10 Project 15
10025 Company 13 Server 10 Project 16
10026 Company 14 Server 10 Project 17
10027 Company 15 Server 10 Project 18
10028 Company 16 Server 10 Project 19
10029 Company 17 Server 10 Project 20
10030 Company 18 Server 10 Project 21
10032 Company 19 Server 10 Project 23
10033 Company 19 Server 10 Project 24
表2
該表具有與以下所有相同的信息表格1而是將每個 UID 分組到各自的項目中。我目前並沒有實際使用它，但它可能會有幫助？如果沒有我會刪除。
Company Trading Name Project UID UID UID UID
Company 1 Project 1 10000
Company 2 Project 2 10001
-----------------------------------------------------
Company 4 Project 7 10006 10007 10008
表3
該表是貼上審計結果的地方。它們完全遵循此佈局，如果審核未通過，則註釋列中包含“失敗”一詞（條件格式當前突出顯示此內容），並且通過簡單的 LEFT 函數提取 UID。然後，名稱和公司欄位對 UID 使用 VLOOKUP表格1取得名稱/伺服器。
Notes Time Rec. UID Name Server
Audit for company passed:10001 auto 10001 Company 2 Server 2
Audit for company passed:10006 auto 10006 Company 4 Server 4
Audit for company failed:10007 auto 10007 Company 4 Server 4
表4
這是審計表。該公式將進入 D 列，並且每天將輸出複製/貼上為值。我在這裡填寫了一些範例，說明之後的樣子。
Server Name Project (UID) Today's Date
Server 1 Company 1 Project 1 (10000) P
Server 2 Company 2 Project 2 (10001) N
Server 3 Company 3 Project 3 (10002,10003,10004,10005) F
Server 4 Company 4 Project 4 (10006,10007,10008) P
-----------------------------------------------------
Server 10 Company 10 Project 13 (10022) P
Server 10 Company 11 Project 14 (10023) M
Server 10 Company 12 Project 15 (10024) P
Server 10 Company 13 Project 16 (10025) P
Server 10 Company 15 Project 18 (10027) F
Server 10 Company 16 Project 19 (10028) P
Server 10 Company 17 Project 20 (10029) M
Server 10 Company 18 Project 21 (10030) P
Server 10 Company 19 Project 22 (100310) P
Server 10 Company 19 Project 23 (10032) P
Server 10 Company 19 Project 24 (10033) M
或者我有第五張桌子，表5，它會自動尋找任何缺少的 ID表3使用以下公式：
=INDEX(Reference!$A$2:$A$160, SMALL(IF(ISERROR(MATCH(Reference!$A$2:$A$160, Check!$D$2:$D$350, 0)), (ROW(Reference!$A$2:$A$160)-MIN(ROW(Reference!$A$2:$A$160))+1), ""), ROWS($A$1:A1)))
什麼時候表3是空的（每天都會清除），這只是列出了每個項目。當任意數量的審核被貼到表3然而，在列表中表5將更新以顯示缺少的內容。也許有一種方法可以將其合併到最終公式中。
如果有人想要使用的話，我有一個去識別化的版本。
謝謝。
edit1：有人要求更多系統詳細資訊。這是在使用 Excel 365 的 Windows 10 環境中。
答案1
我試圖組合一種方法，我承認這種方法的可擴展性不是很好，並且使用了一些輔助列。我不太確定是否有直接的方法可以一次找到逗號分隔的值。也許有可能，但我不是 Excel 專家。
此外，我認為Office 365桌面版支援VBA巨集。如果您有桌面版本，您可以選擇更精簡的 VBA 巨集解決方案。但我認為線上版本不支援巨集。
請參閱下面的快照。
有 5 個輔助列。 H、M、N、O、P。 H 可能是可選的，但我更喜歡使用簡單的數字序列，因為您的伺服器名稱可能實際上沒有最後一個字元作為數字，例如伺服器 1、伺服器 2 按此順序。
在M4中輸入以下公式並將其向下拖曳。
=SUBSTITUTE(RIGHT(K4,LEN(K4)-FIND("(",K4)),")","")
這將創建一個正確的逗號分隔的 UID 列表
在 N4 中輸入以下公式並將其向下拖曳到臨時儲存格數。當沒有任何內容可顯示時，儲存格內容將為空白，但會向下擴展以供以後配置，因為 UID 計數可能會有所不同。
=VALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$M$4:$M$22),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99)))
在O4中輸入以下公式並將其向下拖曳。
=IF(ISNUMBER(VALUE(N4)),MATCH("*"&N4&"*",$M$4:$M$22,0),"")
這列出了稍後將使用的 UID 的位置。
在P4中輸入以下公式並將其向下拖曳。
=IF(ISNUMBER(VALUE(N4)),IF(ISNUMBER(FIND("passed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"P",IF(ISNUMBER(FIND("failed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"F","M")),"")
這決定了表 4 中每個 UID 的狀態。
現在是我的範例中的輸出列 L。
在L4中輸入以下公式並將其向下拖曳。
=IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"M",IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"P")+COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"P","F"))
這將應用最終邏輯來根據幫助程式列中的轉置清單來決定審核狀態「通過」「失敗」或「缺失」。
您可以將這些資料集轉換為 Excel 表格，並使用表格[標題]引用使其更加動態。
嘗試一下並決定這是否對您有用。我承認它不是那麼好，因為它使用了幾個輔助列。這個想法是將逗號分隔的清單轉置為連續的儲存格，然後套用查找。