contando uma cadeia de texto em folhas não consecutivas

Question

no Excel 2003 Eu tenho uma pergunta sobre a contagem de uma seqüência de texto Man em planilhas chamadas 001R a 900R, na mesma célula C8 de todas as planilhas

Eu tenho a seguinte resposta

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

Isso funciona perfeitamente quando as folhas estão todas presentes. Mas ao usar a fórmula acima quando nem todas as folhas estão presentes ainda, dá um erro, porque eu tenho nomes de planilha como

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

e assim por diante, para cerca de 300 folhas, e os números de folhas que faltam serão adicionados mais tarde, conforme seus dados estarão disponíveis.

Existe uma maneira de usar uma fórmula que abranja todos os números para incluir os nomes de folhas presentes e futuros possíveis, para evitar editar a fórmula toda vez que uma nova planilha for adicionada?

@ Gary's Student tem uma resposta muito útil. Mas quero contar o texto em uma lista suspensa com muitos itens e quero contar a ocorrência de cada um deles. Com sua técnica, devo fazer um módulo UDF para cada item; ou existe uma maneira de fazer isso.

Além disso, tenho folhas com S não R anexadas ao seu número. Existe uma maneira de contar isso também? Quer dizer, há uma maneira de fazer

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

e

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

    

Answer 1

A seguinte função definida pelo usuário examinará seu bloco de planilhas. Se a folha existe, então a célula de interesse é examinada para ver se ela contém a string Man . Se a string estiver presente, a contagem será incrementada:

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

Funções Definidas pelo Usuário (UDFs) são muito fáceis de instalar e usar:

1. ALT-F11 exibe a janela do VBE
2. ALT-I ALT-M abre um novo módulo
3. cole o material e feche a janela do VBE

Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx

Para remover o UDF:

1. abrir a janela do VBE como acima
2. limpe o código
3. feche a janela do VBE

Para usar o UDF do Excel:

=SpecialSum(C8)

em que C8 é a célula de interesse. Para saber mais sobre macros em geral, consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!

Isso permitirá adicionar / remover planilhas sem editar a fórmula.