我有一组用逗号分隔的数字。每组数字代表与项目相关的一系列唯一数字。我如何编写公式来搜索参考表中列表中的任意数量的项目并返回硬编码值？
我将在下面为每个工作表提供示例（已匿名化）（表 1-5）以及预期输出。我猜想这与 SUM、FREQUENCY、INDEX 或其他数组公式有关，但我搞不懂。您可以查看我的问题这里也是，但是它包含许多相同的信息。
我用过“磷屁股””F“ail”和“米issing”，但一切正常。
逻辑：
该公式应执行以下步骤。
- 从 C 列的每个单元格中提取数字列表表 4。
- 搜索表3（D 列）中出现的数字。2b
. 如果出现列表中的任何数字，请检查审核是否失败（在 D 列中用“失败”文本表示）。表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
此表是粘贴审计结果的地方。它们完全遵循此布局，如果审计未通过，注释列将显示失败字样（条件格式当前突出显示此字样），并且 UID 会通过简单的 LEFT 函数提取。然后，名称和公司列使用 VLOOKUP 针对 UID表格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 表并使用表 [标题] 引用使其更具动态。
尝试一下，看看这对您是否有用。我承认它不是那么好，因为它使用了几个辅助列。这个想法是将逗号分隔的列表转置到连续的单元格，然后应用查找。