如何在Excel电子表格列中查找连续数字的特定数据 [英] How to find a specific data of consecutive numbers inside an excel spreadsheet column

问题描述

请帮助我查找电子表格的列中显示的数字序列.

我在excel列中有一个大的1和0数据，我需要在该列中找到连续的1和0的序列.例如，下面是我的excel列:

  001个1个001个01个001个1个01个1个1个00 

请假定这是我的一些列数据，我需要查找列中1和0的序列在哪里.例如，我需要找到:

  001个1个01个 

此列中的数据序列.序列搜索有什么功能吗?请帮忙！

解决方案

在Excel 2019和O365中，使用 CONCAT()相当简单:

  = FIND("001101"，CONCAT(A1:A19)) 

但是，合并这些版本的Excel之前的单元格范围可能很棘手.您将很快陷入语法混乱的局面，例如 = MAX((A1:A19 = 0)*(A2:A20 = 0)*(A3:A21 = 1)*(A4:A22 = 1)*(A5:A23 = 0)*(A6:A24 = 1)*(ROW(A1:A19)))(作为数组公式).在这种情况下，UDF并不是一个不错的选择:

 函数FINDSEQ(seq作为字符串，rng作为范围)FINDSEQ = InStr(1，Join(Application.Transpose(rng.Value)，"")，seq)结束功能 

像这样调用: = FINDSEQ("001101"，A1:A19)

^{注意-由于 Application.Transpose 具有上限，因此可能会找到更好的解决方案.}

Please help me to find sequence of numbers present in column of a spreadsheet.

I have a large data of 1's and 0's in an excel column, I need to find the sequence of consecutive 1's and 0's in the column. For example, my excel column is given below:

0
0
1
1
0
0
1
0
1
0
0
1
1
0
1
1
1
0
0

Please assume this as some of my column data, I need to find where are the sequences of 1's and 0's present in the column. For example, I need to find:

0
0
1
1
0
1

This sequence of data in the column. Is there any function for sequence search or something? Please Help!!

解决方案

In Excel 2019 and O365, it's rather easy using CONCAT():

=FIND("001101",CONCAT(A1:A19))

However, combining a range of cells prior to these versions of Excel can be tricky. You will quickly get tangled up in syntax like =MAX((A1:A19=0)*(A2:A20=0)*(A3:A21=1)*(A4:A22=1)*(A5:A23=0)*(A6:A24=1)*(ROW(A1:A19))) (as an array formula). An UDF is not a bad alternative in this case:

Function FINDSEQ(seq As String, rng as Range) As Long
    FINDSEQ = InStr(1, Join(Application.Transpose(rng.Value), ""), seq)
End Function

Call like: =FINDSEQ("001101",A1:A19)

^{Note - Better solutions may be found since Application.Transpose will have an upperbound limit.}

这篇关于如何在Excel电子表格列中查找连续数字的特定数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！