☰

在特定行中查找第二次或第三次出现的值 - excel

tag-icon • tag-icon microsoft-excel worksheet-function
在特定行中查找第二次或第三次出现的值 - excel

我正在尝试获取连续出现多次的日期的列位置。

示例数据：
数据示例。

以下公式正确识别了大多数列位置，但如果存在重复，则始终返回第一次出现的位置。

公式 ：
公式

先感谢您。

答案1

以下公式将n-th在一组匹配和不匹配的单元格中选择匹配项。它可以适应各种源数据布局，稍后会详细介绍。它非常简单，因为它根本不提供任何错误检查，甚至您的IF(FL2<>0部分也不提供，因为它适用于广泛的用途，但您可以轻松添加此类检查以适合您的具体情况：

=FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN("¢",FALSE,FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(AW2:CW2),4),ROW(),""),AW2:CW2=B2),ROW(),""),"¢","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner["&C2&"]")

它的设置假设它将出现在行的 A 列中，并且 B 列中的值和n-thC 列中的值相匹配。当然，您可以调整这些值。

LET()将所有变量放在一个易于编辑的位置的版本是：

=LET(
 RangeToExamine, AW2:CW2,  Delimiter, "¢",  ItemToMatch, B2,  InstanceToMatch,  C2,
 FILTERXML("<Outer><Inner>"
 &SUBSTITUTE(TEXTJOIN(Delimiter,FALSE,
 FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(RangeToExamine),4),ROW(),""),
 RangeToExamine=ItemToMatch),
 ROW(),""),  Delimiter,"</Inner><Inner>") & "</Inner></Outer>",
 "/Outer/Inner["&InstanceToMatch&"]"))

这就是全部LET()的目的：使公式更容易编辑。

那么，它的作用是什么呢？首先，虽然您的布局可能是一行，但我不确定，所以我让它在任何行上工作。这是通过在函数ROW()内部使用函数来解决的ADDRESS()，然后在SUBSTITUTE()删除行号的函数中使用函数来解决的（因此您只有列，这是您想要的结果）。如果需要，您可以在两个位置都放置一个数字“1”，以确保不会发生行式活动。如果LET()您希望能够在这里和那里修改它，您甚至可以向中添加一个子句。我在这里没有这样做，因为它会将行滚动LET()到两行，这会让这一切变得混乱。顺便说一句，这就是您将列 NUMBER 转换为列 LETTER 的方法，而不是所有那些带有MOD()（甚至更糟）的有趣公式。不需要 UDF。

因此，ADDRESS()使用Spill功能来创建被检查范围内所有单元格地址的数组。FILTER()然后检查目标范围的匹配项并列出相应的单元格地址。（再次，未设置错误！）SUBSTITUTE()然后删除行号（或常量，如果您进行了该更改）因此 Excel 现在有一个仅包含匹配项所在列的列字母标签的数组。

我应该快速提一下，我在这里使用的分隔符不一定总是好的——在其他情况下。但由于它与底层数据无关，而是与地址数据有关，因此它只需要是返回的地址中从未使用过的东西，ADDRESS()这样它就不会在这里失败。不过，在其他地方使用“很少使用的字符”方法时，您可能需要选择在字符映射中找到的奇怪的东西。

接下来，TEXTJOIN()将地址数组转换为字符串，并SUBSTITUTE()用该</Inner><Inner>字符串替换分隔符，同时在前后添加字符串，将结果转换为可接受的 XML。几乎可以使用任何方法来构造 XML，只要至少有一个包装内容的较高级别和包装字符串每个部分的最低级别即可TEXTJOIN()。我喜欢将其用作<Outer>包装所有内容的较高级别和<Inner>工作级别。

我第一次看到这个是在某个地方，当时的解释很差，但我去了Chandoo.org看看他是否有小窍门，因为他的解释（几乎）总是非常清楚和有用。这非常有用，所以我必须给予赞扬和感谢！

最后，FILTERXML()它还做了最后一件事。它让你选择要返回的匹配实例。在 Chandoo 的提示中，他以这种方式分解一个句子，但正如你所见，它可以分解你得到的任何东西或将其变成字符串，包括列数据。在这种情况下，如果你想要第三个实例，你可以添加[3]一些东西。

但是，Excel 在这方面做得更好。首先，您可以使用数字实例，例如 [3] 表示第三个实例，但您也可以使用它[last()]来查找最后一个实例，而不必同时查找其基数。但令人惊讶的是（因为它似乎不是针对这种事情的），该函数真正出彩的地方在于您可以“构建”该子句，或者换句话说，您可以构建一个包含变量数据的字符串作为部分，这将使您的公式具有响应性，而不必在公式中输入并根据需要进行更改。这意味着您的用户可以使用单元格中的条目而不是编辑公式来获得该功能。或者，如果需要，您可以让它反映其他公式的结果。

最后一部分出现在公式格式化版本的最后一行。您给出“XML 路径”（本例中为 `/Outer/Inner”），后面紧跟着一组方括号（“[ ]”），其中包含实例编号或表达式。

在一般使用中，我建议进行错误检查以查找：

  1. 您要匹配的商品不在检查范围内
  2. 您想要的实例数高于总匹配数：您想要#6，但只存在四个......
  3. 类似地，如果您想从最后一个实例向后找到第 n 个实例，那么它也存在：如果您想从“last()”向后找到六个实例，那么至少还会有另外六个实例……

如果数据不仅仅是字母和数字，那么在选择分隔符时要小心谨慎。

如果您想要一个单元格地址而不仅仅是一列，请删除SUBSTITUTE()从完整地址中剥离行的地址。

好吧，在某种程度上，我很抱歉：这是一个通用的解决方案，而不是完全适合您的帖子，但我不确定您的确切用途，因此这可能也有帮助，而且任何编辑都很容易看到。这里的经验表明，帖子经常会遗漏有用的信息，发帖人必须再问至少一个问题才能得到他需要的答案。试图让你免于经历这个过程。

答案2

您在寻找 COUNTIF 公式吗？=IF(A2<>0,COUNTIF($A$2:A2,A2),0)

在此处输入图片描述

相关内容