使用IF函数时,如何在“Logical_test”中使用动态集进行比较争论?
我使用数组公式(在 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
数组公式执行多次计算并返回单个结果或数组。公式中的所有数组参数必须具有相同的大小。
在第一个示例中,您将某个值的单个值(因为 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 位数字,该方法就有效。如果数字超过 1 位,则需要在 MID_BLA 和 CONDX_STR 中的数字前添加一个空格;也不是太难,但增加了公式的复杂性。
如果要执行 VBA,可以使用 Split() 函数从分隔的字符串创建一个从零开始的数组
,并用 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
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
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