Как вычислить количество вхождений строки во всей книге?

Question

Я хочу вычислить, сколько раз указанная строка появляется во всей моей книге в каком-либо столбце. Я пробовал эту формулу =COUNTIF(sheet1:sheetn!C2:C150; "*string*") Но он отображает #VALUE! ошибка.

Что не так с моей формулой? Как я могу получить этот счет?

Answer 1

Вот эквивалентный код VBA для подсчета количества ячеек, содержащих хотя бы одну строку

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:

Вот код для подсчета количества строк. Он будет считать несколько строк в одной ячейке:

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:

Математика работает следующим образом:

1. скажем, есть три строки в ячейке, и количество символов в этой ячейке составляет 100
2. замена удаляет все три экземпляра, и новая длина составляет 82
3. результат вычитания в 18
4. деление приводит к 3 (потому что длина строки слова 6 символов)

Answer 2

Вы должны определить каждый диапазон для каждого листа, например

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

Если вы хотите быть умным, вы можете сказать, поместите каждый лист в столбце A, а в столбце B используйте

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

Или то, что соответствует вашей схеме именования. Затем перетащите его вниз для каждого счета и суммируйте это.

Answer 3

Для этого вы можете написать довольно простую пользовательскую функцию в VBA. Вставьте следующий код в новый модуль (инструкции о том, как это сделать, см. В этом посте):

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

Когда у вас есть этот код в модуле, вы можете использовать функцию из рабочего листа. Например, чтобы подсчитать количество экземпляров potato в столбце C на всех листах, вы должны использовать следующий синтаксис.

=WBCountString("potato",C:C)