并提前感谢您提供的任何帮助。
背景:我试图计算有多少指标连续具有“R”(红色)值,直到该值更改为“A”(琥珀色)或“G”(绿色)。
我想创建一个函数,该函数可以从右到左计数值为“R”的时间,直到它发生变化。如果第一个单元格不是“R”,则返回 0。
下面的示例 1,单元格 {Jul-22} 应返回“3”,因为从插入函数的位置开始有 3 个“R”。
示例 1 - 3 红色
下面的示例 2,单元格 {Jul-22} 应返回“1”,因为插入函数的位置有 1 个“R”。 示例 2 - 1 红色
下面的示例 3,单元格 {Jul-22} 应返回“0”,因为插入函数的位置有 0 个“R”。 示例 3 - 0 红色
下面的示例 4,单元格 {Jul-22} 应返回“8”,因为插入函数的位置有 8 个“R”。 示例 4 - 8 红色
我尝试使用 Power Query 和 COUNTIF 以及 OFFSET。我觉得 COUNTIF/OFFSET 限制我从左到右开始计数,我不想这样做。我也在使用 COUNTIF/OFFSET 计数文本值时遇到问题,所以我尝试首先使用基本的 IF/THEN 语句转换 RAG,以将“R”返回为 1,将其余部分返回为 0。
有时使用 COUNTIF/OFFSET 函数我会收到“Volatile”错误消息。
下面是我尝试的函数和使用的表格的示例。单元格 A2 包含此函数“=COUNTIF(OFFSET(A2,0,1,MATCH(0,B2:V2,0)),"<>0")”单元格 B2 --> V2 分别包含此函数“=IF(OR(B1="G",B1="A"),0,1)”
答案1
以下公式将给出结果,并且一开始的两个可变值使得更改其参数变得容易:
=LET(Range, B2:M2, CountValue, "R", Columns, COLUMNS(Range), Number, SEQUENCE(1,Columns,Columns,-1),
String, MID(TEXTJOIN("",FALSE, INDEX(Range,1,Number) ),1,Number),
ComparisonString, MID(REPT(CountValue,Columns),1,SEQUENCE(1,Columns,Columns,-1)),
SUM( IF( String=ComparisonString, 1, 0) )
)
是的,它看起来更令人生畏,但随后可以处理需要计数的范围和字母。
诀窍是让单元格按相反顺序排列。(我使用了旧版本,INDEX
以便它适合您的更多版本的 Excel,但您可以将其更改。)之后,它只是通过将范围的各种长度字符串与由函数的REPT
“无论多少个”单元格组成的等长版本进行比较,来简化对需要计数的即时值和连续值的检查。将范围长度增加到 100 列,它将形成一到 100 个 R 的字符串。这没有什么巧妙之处,但它确实有效。如果要检查更长的字符串,不仅如果它们的大小不同,它可能会失败,而且即使所有单元格的大小相同,形成的字符串可能会开始超过函数的最大字符串长度并切断字符串,从而冒着产生错误结果的风险。但是,每个字符串之间只有一个字母和逗号,并且随着范围的扩大,长度会算术增加……好吧,无论如何都可能发生这种情况,但很快就会发生,比如说,每个单元格中有四个字母字符,因此将上面的数字乘以四。
因此,它具有人们可能认为有利的特点(我认为如此),但也存在风险,因此也有局限性。