подсчет текстовой строки в непоследовательных листах

Question

В Excel 2003 у меня есть вопрос о подсчете текстовой строки Man в листах с именами от 001R до 900R, в одной и той же ячейке C8 всех листов

Я получил следующий ответ

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT(001&":"&900))&"R"&"'!C8"),"*Man*"))

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

001R 002R 003S 004R 101R 102S 103R 210R 211R 305R 306S 307R 401R 402R 403S 404R

и так далее для примерно 300 листов, и недостающие номера листов будут добавлены позже, когда будут доступны их данные.

Есть ли способ использовать формулу, которая охватывает все числа, чтобы включать в себя настоящие и возможные будущие имена листов, чтобы избежать редактирования формулы при каждом добавлении нового листа?

Студент @ Гари имеет очень полезный ответ. Но я хочу посчитать текст в раскрывающемся списке со многими элементами, и я хочу подсчитать вхождение каждого из них. С вашей техникой, я должен сделать модуль UDF для каждого элемента; или есть способ сделать это.

Кроме того, у меня есть листы с S не R, прикрепленные к их номеру. Есть ли способ подсчитать их тоже? Я имею в виду, есть ли способ сделать

shName = Format(i, "000") & "R"

а также

If InStr(1, cel, "Man") as variables entered in =SpecialSum(C8).

Answer 1

Следующая пользовательская функция проверит ваш блок рабочих листов. Если лист существует, то исследуемая ячейка проверяется на наличие строки Man. Если строка присутствует, счетчик увеличивается:

Public Function SpecialSum(rin As Range) As Long
   Dim addy As String, i As Long, shName As String
   Dim cel As String
   Application.Volatile

   addy = rin.Address

   For i = 1 To 900
      shName = Format(i, "000") & "R"
      On Error Resume Next
         cel = Sheets(shName).Range(addy).Text
         If Err.Number = 0 Then
            If InStr(1, cel, "Man") > 0 Then SpecialSum = SpecialSum + 1
         Else
            Err.Number = 0
         End If
      On Error GoTo 0
   Next i

End Function

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

=SpecialSum(C8)

где C8 - интересующая клетка. Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!

Это позволит вам добавлять / удалять таблицы без редактирования формулы.