Como calcular o número de ocorrências de uma string na pasta de trabalho inteira?

Question

Eu quero calcular o número de vezes que uma string especificada aparece em toda a minha pasta de trabalho em alguma coluna. Eu tentei essa fórmula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") Mas ela renderiza o erro #VALUE! .

O que há de errado com a minha fórmula? Como posso obter essa contagem?

    

Answer 1

Aqui está o código VBA equivalente para contar o número de células contendo pelo menos uma string

Sub StringCounter()
    Dim r As Range, s As String, i As Long
    s = "*string*"
    For Each sh In Sheets
        Set r = sh.Range("C2:C150")
        i = i + Application.WorksheetFunction.CountIf(r, s)
    Next sh
    MsgBox i
End Sub

EDIT # 1:

Aqui está o código para contar o número de strings Ele contará várias string dentro de uma única célula:

Sub StringCounter_II_The_Sequel()
Dim r As Range, s As String, i As Long
Dim rr As Range, L As Long
s = "string"
L = Len(s)
For Each sh In Sheets
    Set r = sh.Range("C2:C150")
        For Each rr In r
            txt = rr.Text
            If InStr(txt, s) > 0 Then
                i = i + (Len(txt) - Len(Replace(txt, s, ""))) / L
            End If
        Next rr
Next sh
MsgBox i
End Sub

EDIT # 2:

A matemática funciona da seguinte forma:

1. dizem que há três string em uma célula e o número de caracteres nessa célula é 100
2. o substituir remove todas as três instâncias e o novo tamanho é 82
3. os resultados da subtração em 18
4. a divisão resulta em 3 (porque a palavra string tem 6 caracteres)

Answer 2

Você precisa definir cada intervalo para cada folha, por exemplo,

COUNTIF(Sheet1!C2:C150,"*string*")+COUNTIF(Sheet2!C2:C150,"*string*") ...

Se você quer ser inteligente, pode colocar cada folha na coluna A e na coluna B usar

=COUNTIF(INDIRECT("Sheet"&A1&"!A1:A5"),"*string*")

Ou o que for adequado ao seu esquema de nomenclatura. Em seguida, arraste-o para baixo para cada contagem e some isso.

    

Answer 3

Você pode escrever uma função definida pelo usuário bastante direta no VBA para fazer isso. Cole o seguinte código em um novo módulo (para obter instruções sobre como fazer isso, consulte este post ):

Public Function WBCountString(SearchFor As String, InRange As Range) As Long
Dim wbcs As Long, rng As Range, addr As String
For Each s In Worksheets
    'Limit search area to specified range.
    addr = InRange.Address
    Set rng = s.Range(addr)
    'Keep a running tally of the number of instances of the word in the specified range on each sheet.
    wbcs = wbcs + Application.WorksheetFunction.CountIf(rng, "*" & SearchFor & "*")
Next s
WBCountString = wbcs
End Function

Depois de ter este código em um módulo, você pode usar a função da planilha. Por exemplo, para contar o número de instâncias de potato na coluna C em todas as planilhas, use a seguinte sintaxe.

=WBCountString("potato",C:C)