☰

embargo:

tag-icon • tag-icon google-spreadsheets
embargo:

Eu tenho um conjunto de células que possuem números de classificação de um dígito misturados com texto próximo a eles (classificações de 1 a 9). O que eu gostaria de fazer é, para cada uma das células, extrair os números e produzir uma média. Conheço funções como Search e RegexExtract, mas não entendo como usá-las para obter todos os números, pois parece retornar apenas a primeira correspondência.

Aqui está um exemplo do texto em uma única célula:

A - 1
B - 5
C - 9
D -4
E -7
F -  2

Para o texto acima, pretendo criar um array {1,5,9,4,7,2} e passá-lo para a função média.

Também estou usando o Planilhas Google, mas a maioria das funções padrão do Excel devem funcionar da mesma forma. Obrigado.

Responder1

Eu gostaria de sugerir que uma solução em duas etapas resolverá o problema:

embargo:

  • O método mostrado abaixo é para usuários do Excel, já que o VBA não funciona para o Planilhas Google.
  • O arquivo Excel pode ser convertido em um XLSXformato de arquivo adequado/editável com o Planilhas Google.
  • SUMPRODUCTtambém funciona com planilhas do Google.

insira a descrição da imagem aqui

Passo 1:

  • PressioneAlt+F11 ouRcerto Clique noSheet TAB e no menu exibido, cliqueVVisualize o código para obter o editor VB.

  • Copa ePaste este código comoModule e, em seguida, retorne à planilha.

    Public Function SplitNum(pWorkRng As Range, pIsNumber As Boolean) As String

Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
    SplitNum = SplitNum + xStr
End If
 Next
End Function

  • Insira esta fórmula na célula AI5:=SplitNum(AH1,TRUE)

Passo 2:

  • ObterAVERAGE insira esta fórmula em Cell AJ5.

    =SUMPRODUCT(--MID(AI5,ROW(INDIRECT("1:" & LEN(AI5))),1))/Len(AI5)

Os usuários do Planilhas Google podem usar esta fórmula:

=AVERAGE(SPLIT(AH1,CONCATENATE(SPLIT(AH1,".0123456789"))))

Observação -Ajuste as referências de células na fórmula conforme necessário.

Responder2

Se o que você deseja é AVERAGEesses valores, no Planilhas, tente (com seus dados em A1:

=average(split(trim(REGEXREPLACE(A1,"\D+"," "))," "))

Em vez de tentar extrair cada número, apenas removemos os não-dígitos; em seguida, divida e calcule a média deles. Às vezes é uma técnica útil remover o que não queremos, em vez de tentar extrair o que queremos.

insira a descrição da imagem aqui

ParaExcelusuários, se você tiver Excel 2016+ com a TEXTJOINfunção, poderá usar estevariedadeFórmula:

=AVERAGE(ABS(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(" ",TRUE,A1:A6)," ","</s><s>") & "</s></t>","//s[number()=number()]")))

Como esta é uma fórmula de matriz, você precisa “confirmá-la” mantendo pressionado ctrl+ shiftenquanto pressiona enter. Se você fizer isso corretamente, o Excel colocará colchetes {...}ao redor da fórmula, conforme observado na barra de fórmulas

informação relacionada