Excel按引用列表的大小溢出公式

dy2hfwbg 于 2023-01-18 发布在其他

关注(0)|答案(3)|浏览(133)

我正在尝试构建一个公式，该公式将在3个不同的单元格中查找3个可能的值。我有一个固定结构的表格，无法修改。我正在单元格Y20、AB 20和BC 20中查找AAA BBB CCC值。
我尝试了几种解决方案，如：
搜索（Y20;$CE$1：$CE$3）------其中CE 1：CE 3是包含AAA BBB和CCC计数的范围（Y20：BC 20;$CE$1：$CE$3）
完整公式为IF（SEARCH（Y20;$CE$1：$CE$3）〉0;Y20;如果（搜索（AB 20;$CE$1：$CE$3）〉0;搜索（BC 20;1元计算单位：3元计算单位）;BC 20;“无”）））
问题是这些公式都是按照引用值的范围大小溢出的。例如，如果AAA BBB和CCC是要查看的值，则公式向下溢出2个单元格。如果我添加DDD，则向下溢出3个单元格。
我在r/excel中试过这个：=或（IS编号（搜索（“"，&Y20&"，";“，"$CE$1：$CE$3&"，”））但是当找到值时返回FALSE，当单元格Y20为空时返回TRUE，这无助于构建强大且可读的内容（如果为True，我必须返回原始单元格以检查找到的值）。
我试过了：=文本连接（“，“;TRUE;如果（计数（Y20;“"和加元1：加元3和"”）;1元加共体：3元加共体;“”））但在所有情况下都返回0，0。
我真的很困惑。我知道我可以做连接的if语句，但是用3个可能的值检查3个不同的单元格，这将是一个很长很长的IF。
我尝试实现的是检查单元格Y20、AB 20、BC 20是否包含AAA、BBB或CCC，这些单元格只能包含这些值中的一个。
该检查将包含在单元格CA 20中。因此，例如，如果CA 20在单元格Y20、AB 20或BC 20中发现AAA，则CA 20应当显示AAA。
你能告诉我那件事吗？
先谢谢你的帮助。

excel

来源：https://stackoverflow.com/questions/75132912/excel-spilling-formula-by-the-size-of-a-reference-list

3条答案

按热度按时间

hgb9j2n61#

或不同的方法：

=LET(v,VSTACK(Y20,AB20,BC20),
     m,ISNUMBER(MATCH("*"&CE1:CE3&"*",v,0)),
FILTER(v,m,"None"))

您的三个单元格堆叠为一个数组，并检查是否包含CE1:CE3中的值，如果是，则将数组的值过滤为该值。
考虑到您自己的答案，它不需要包含值，但等于值，请尝试以下操作：
使用ctrl+shift+error输入=TEXTJOIN(",",1,IFERROR(INDEX(Y20:BC20,,MATCH(CE1:CE3,Y20:BC20,0)),""))
如果值相等、逗号分隔或=INDEX(Y20:BC20,,AGGREGATE(15,6,(MATCH(CE1:CE3,Y20:BC20,0)),1))，则返回所有值。（不确定是否需要ctrl+shift+enter
注意，假设范围Y20:BC20不包含可能匹配搜索值的其他单元格。

赞(0）回复(0）举报 2023-01-18

sy5wg1nm2#

我对这个问题的解释是：

CE1:CE3中包含3个值
您希望公式返回在Y20、AB20或BC20中找到一次的第一个值。如果在多个位置找到该值，则该值无效。

此公式可实现此功能（需要Office365，早期版本的Excel不支持这些函数）

=LET(
SearchFor,CE1:CE3,
NumFound,BYROW(SearchFor, LAMBDA(row, SUM(Y20=row,AB20=row,BC20=row))),
FoundFilter, FILTER(SearchFor, NumFound = 1),
INDEX(FoundFilter, 1)
)

赞(0）回复(0）举报 2023-01-18

oyjwcjzk3#

谢谢你的建议。我的excel版本中没有LET或VSTACK函数。我使用了CONCAT（IF（CE 1：CE 3 =Y20）+（CE 1：CE 3 = AB 20）+（CE 1：CE 3 = BC 20）;CE1：CE3;"”））
效果很好。学分：Reddit匿名

赞(0）回复(0）举报 2023-01-18