使用IF函数时,如何在“Logical_test”中使用动态集进行比较争论?

发布于 2024-09-13 17:38:34 字数 800 浏览 4 评论 0原文

我使用数组公式(在 Excel 2003 中)来计算满足特定条件的字符串数量。由于文件大小限制,我使用一列字符串而不是单元格表来存储数据。

公式如下:

{=SUM(IF((VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1))*VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)))=VLOOKUP(D2,t.lkup,2,FALSE),1,0))}

表达式 VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1)) 查找指定区域中的单元格以返回值。该值与表达式 VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)) 返回的另一个值相乘。然后在 VLOOKUP(D2,t.lkup,2,FALSE) 给出的一组数字中查找结果乘积,该结果返回类似“{1,2,3,4}”的字符串。如果乘积是 set 的元素,则将 1 添加到总和中,否则将 0 添加到总和中。

当我使用上面的数组公式(使用动态查找集)时,返回值为零。如果我使用以下公式,

{=SUM(IF((VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1))*VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)))={1,2,3,4},1,0))}

则会返回正确的总和。有谁知道如何让 Excel 将集合查找视为静态集合而不是字符串?

I am using an array formula (in Excel 2003) to count the number of strings meeting a specific condition. I am using a column of strings instead of a table of cells to store my data because of file size limitations.

The formula is below:

{=SUM(IF((VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1))*VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)))=VLOOKUP(D2,t.lkup,2,FALSE),1,0))}

The expression VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1)) looks through the cells in a named range to return a value. This value is multiplied by another value returned by the expression VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)). The resulting product is then looked for in a set of numbers given by VLOOKUP(D2,t.lkup,2,FALSE), which returns a string like "{1,2,3,4}". If the product is an element of set, then 1 is added to the sum, else 0 is added to the sum.

When I use the array formula above (with the dynamic lookup set), a value of zero is returned. If I use the following formula,

{=SUM(IF((VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),6,1))*VALUE(MID(INDIRECT(CONCATENATE(D1,"test")),1,1)))={1,2,3,4},1,0))}

then the correct sum is returned. Does anyone know how to get Excel to treat the set lookup as a static set instead of a string?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

锦欢 2024-09-20 17:38:34

数组公式执行多次计算并返回单个结果或数组。公式中的所有数组参数必须具有相同的大小。

在第一个示例中,您将某个值的单个值(因为 IF 中最外层的函数是 VALUE() 函数)与返回字符串的 VLOOKUP 进行比较...这一定会出错...您的字符串包含卷曲的事实括号不会将其转换为数组。

在第二个示例中,您将单个值与包含元素 {1,2,3,4} 的数组进行比较,因此实际上您进行了四次比较,如果其中之一解析为 TRUE,则添加 1。

我不知道有什么办法将逗号分隔的字符串“{1,2,3,4}”转换为数组{1,2,3,4}而不使用VBA,但也许您可以通过转换您的VALUE(...)来改变您的策略*VALUE(...) 将数字转换为字符串,并使用 FIND() 函数将数字识别为条件字符串的子字符串。

例如,假设您的 MID_CONCATENATE_TIMES_BLA_BLAH 结果为 7,并且您的

  • 条件字符串 = "{1, 3, 5, 7, 9}",FIND(MID_BLA, CONX_STR) = TRUE
  • 条件字符串 = "{1, 2, 3, 4}" , a FIND(MID_BLA, CONDX_STR) = FALSE

只要您的结果是 1 位数字,该方法就有效。如果数字超过 1 位,则需要在 MID_BLA 和 CONDX_STR 中的数字前添加一个空格;也不是太难,但增加了公式的复杂性。

如果要执行 VBA,可以使用 Split() 函数从分隔的字符串创建一个从零开始的数组

Function StrToArray(Arg As String) As Variant
    StrToArray = Split(Arg, ",")
End Function

,并用 StrToArray(VLOOKUP(...)) 将 VLOOKUP 包围在 (1) 中,在这种情况下,您必须从条件字符串中删除花括号。

希望有帮助 - 祝你好运

An array formula performs multiple clalculations and returns either a single result or an array. All array arguments in the formula must be of equal size.

in your first example you compare a single value of something (as the most outer function within IF is a VALUE() function) to a VLOOKUP which returns a string ... that must go wrong ... the fact that your string contains curly brackets does not convert it into an array.

in your second example you compare a single value to an array containing elements {1,2,3,4}, so actually you do four comparisons, and if one of them resolves to TRUE you add 1.

I don't know any way to convert a comma delimited string "{1,2,3,4}" into an array {1,2,3,4} without use of VBA, but maybe you can change your tactic by converting your VALUE(...)*VALUE(...) number into a string and use the FIND() function to identify number as a substring of the condition string.

e.g. say your MID_CONCATENATE_TIMES_BLA_BLAH results in 7, and your

  • condition string = "{1, 3, 5, 7, 9}", a FIND(MID_BLA, CONX_STR) = TRUE
  • condition string = "{1, 2, 3, 4}", a FIND(MID_BLA, CONDX_STR) = FALSE

This will work as long as your results are 1 digit. With more than 1 digit you would need to include a SPACE before the number in both MID_BLA and CONDX_STR; also not too difficult, but adding even more complexity to the formulae.

If you want to do VBA, you can use the Split() function to create a zero-based array from a seperated string

Function StrToArray(Arg As String) As Variant
    StrToArray = Split(Arg, ",")
End Function

and surrond your VLOOKUP in (1) by StrToArray(VLOOKUP(...)), in which case you must remove the curly braces from your condition strings.

Hope that helps - good luck

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文