如何查找Excel列中是否有序列? [英] How to find if excel column has a sequence?

问题描述

如何查找excel列中是否包含我要查找的序列?

How to find if excel column contains the sequence i am looking for?

例如，列1包含A B C D A B B C C D A B，我想查找列是否包含A B并想知道其起始行号.类似于此处的示例.

for example column 1 contains A B C D A B B C D A B and i would like to find if the column contains A B and would like to know the row number in which it starts from. similar to the example here.

我从网站 http找到了解决方案://www.get-digital-help.com/2014/10/28/find-a-sequence/?但这似乎不起作用.

I found a solution from the website http://www.get-digital-help.com/2014/10/28/find-a-sequence/? but it doesn't seem to work.

推荐答案

这是一个很好的问题-本质上，我们如何将MATCH用于2个条件，而不仅仅是一个.我们可以通过数组公式来做到这一点.从本质上讲，我们将创建一个新数组，该数组连接顺序条目的每种可能组合(因此在示例中，它看起来像AB，BC，CD，DA，AB，BB，BC ...).然后，我们可以根据需要搜索"AB".

This is a great question -- essentially, how do we use MATCH for 2 criteria, not just one. We can do this via array formulas. In essence, we're going to make a new array, that concatenates each possible combination of sequential entries (so in the example, it would look like AB,BC,CD,DA,AB,BB,BC...). Then we can search that as need be for 'AB'.

公式如下:

=MATCH("A"&"B",A1:A100&A2:A101,0)

或更笼统地说:

=MATCH([Criteria 1]&[Criteria 2],[Data 1st val]:[Data last val]&[Data 2nd val]:[Data last + 1 val],0)

一个非常重要的提示:在编写公式后，请不要按 Enter ，不要按 Ctrl + Shift + 输入.这告诉Excel将其视为数组公式.如果操作正确，您应该在公式周围看到花括号.

A very important note: after writing in the formula, do not press Enter, press Ctrl+Shift+Enter. This tells Excel to treat it as an array formula. If done correctly, you should see curly braces around the formula.

这篇关于如何查找Excel列中是否有序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！