Gostaria de postar uma solução para uma pergunta interessante que me deparei em um fórum:
Tenho uma planilha com aproximadamente 1000 linhas com 15 colunas cada.
Preciso saber em qual linha se encontra a maior quantidade de números em sequencia. Tem alguma fórmula pra isso, que possa ser colocada em toda planilha para obter a resposta que preciso?
É possível se arriscar para achar uma solução usando somente funções nativas do Excel, mas garanto que não será algo simples. Por isso, vou propor uma solução com o desenvolvimento de uma UDF, ou seja, uma função personalizada construída através do VBA.
Public Function MAIOR_SEQ(vArray As Range) As Integer Dim v As Range Dim lCount As Long, n As Long, lSum As Long, i As Long Dim asOut() As Integer lCount = vArray.Columns.Count ReDim Preserve asOut(1 To lCount) If vArray.Rows.Count > 1 Then MAIOR_SEQ = CVErr(xlErrNum) Exit Function End If lSum = 1 i = 1 For Each v In vArray n = 1 For n = 1 To lCount - 1 If v + n = v.Offset(0, n).Value Then lSum = lSum + 1 Else Exit For End If Next n asOut(i) = lSum lSum = 1 i = i + 1 lCount = lCount - 1 Next v MAIOR_SEQ = WorksheetFunction.Max(asOut) End Function
Pressionando ALT+F11 para abrir o editor do Visual Basic, inserimos um novo módulo na pasta de trabalho e colamos o código acima que ainda pode ser melhorado e simplificado, mas no momento irá atender esta necessidade.
Fechando o editor agora podemos inserir a fórmula usando esta função, que só tem um argumento que é o intervalo que deseja ver a maior sequencia, que deve estar em uma única linha, caso contrário retornará erro. Por exemplo:
Assim fica fácil identificar a maior sequência, basta usar a função MÁXIMO na coluna com a fórmula (Coluna X no exemplo acima).
Como foi criada uma UDF o arquivo precisará ser salvo como .xlsm, ou adicionado como um suplemento ou salvo na pasta de macros pessoais.
Abraços!