У Джеффа есть рабочий лист с небольшим количеством текста в столбце C. Он хотел бы подсчитать вхождения определенной строки в столбце C, но только для нечетных строк (1, 3, 5 и т. Д.) В данных. Строка, которую он ищет, может быть не всем содержимым ячейки, и она может встречаться несколько раз в пределах одной ячейки. (Если это встречается 2 или 3 раза в ячейке, это должно быть засчитано как 2 или 3 вхождения.) Он задается вопросом, есть ли шаблонный способ сделать это, не прибегая к макросу.

Один из способов добиться этого — использовать вспомогательный столбец. Например, предположим, что вы можете использовать столбец D в качестве вспомогательного столбца, а первая ячейка, содержащая данные, — это ячейка C2. (Возможно, в ячейке C1 есть заголовок столбца.) Вы можете использовать следующую формулу в ячейке D2:

=IF(MOD(ROW(),2)=1,(LEN(C2)-LEN(SUBSTITUTE(C2,"my text","")))/LEN("my text"),"")

Все, что вам нужно сделать, это заменить строку поиска («мой текст») тем, что вы ищете. Функция LEN используется дважды: сначала для нахождения длины того, что находится в ячейке C2, а затем для вычитания из нее длины текста с удалением всех экземпляров «my text». Затем это значение делится на длину того, что вы ищете, в результате чего получается, сколько экземпляров искомого текста было в ячейке. Обратите внимание, что функция ЕСЛИ гарантирует, что числовое значение будет возвращено только в том случае, если строка является строкой с нечетным номером.

Вы можете скопировать эту формулу вниз на необходимое количество строк, а затем просуммировать столбец. Результат — количество раз, когда строка появляется в строках с нечетными номерами.

Если макет вашего рабочего листа не позволяет использовать вспомогательный столбец, вы можете положиться на формулу, которая работает с массивами данных.

Вот один из них:

=SUMPRODUCT((LEN(C:C)-LEN(SUBSTITUTE(C:C,"my text","")))/LEN("my text")*ISODD(ROW(C:C)))

Эта формула, по сути, делает то же самое, что и предыдущая формула, за исключением того, что функция СУММПРОИЗВ выполняет внутренние вычисления для каждой ячейки в столбце C. Вы должны понимать, что, поскольку формула проверяет весь столбец C, это означает, что ваш поисковый текст («мой текст «) встречается в любых ячейках столбца, не содержащих данных (например, в заголовке столбца), то он также будет включен в возвращаемый итог.

Если вы все же решите использовать макрос, вы можете легко создать определяемую пользователем функцию, которая проверяет диапазон ячеек и определяет их количество. Ниже приводится пример типа макроса, который вы можете использовать:

Function CountInst(rSource As Range, sSearch As String, bCaseInsensitive As Boolean) As Integer     Dim c As Range     Dim iCount As Integer     Dim sTemp1 As String     Dim sTemp2 As String

sTemp2 = sSearch     If bCaseInsensitive Then sTemp2 = LCase(sTemp2)

iCount = 0

For Each c In rSource         If c.Row Mod 2 = 1 Then             sTemp1 = c.Text             If bCaseInsensitive Then sTemp1 = LCase(sTemp1)



iCount = iCount + (Len(sTemp1) - _               Len(Replace(sTemp1, sTemp2, ""))) / Len(sTemp2)

End If     Next c

CountInst = iCount End Function

Чтобы использовать это, все, что вам нужно сделать, это указать диапазон, что вы хотите искать, и хотите ли вы, чтобы соответствие было нечувствительным к регистру или нет. Например, если вы хотите найти «мой текст» и не учитывать регистр, вы должны использовать следующее:

=CountInst(C2:C99,"my text",TRUE)

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (1514) применим к Microsoft Excel 2007, 2010, 2013 и 2016.