Números médios se outra coluna contiver um determinado texto de subcadeia no Excel

Question

Estou procurando uma maneira de calcular a média dos números numéricos em uma coluna, digamos B, somente se a string na coluna A contiver um determinado texto.

Por exemplo:

(In column A) (In column B)
Hello table . . . 1
Hello desk  . . . 2 
Bye table   . . . 3
Bye desk  . . . . 4

Portanto, se o critério para calcular a média dos números for "desk", a resposta será (2 + 4) / 2 = 3.

O que eu criei até agora é:

=AVERAGEIF(A1:A5, SEARCH("desk",B1:B5),B1:B5) 

mas retorna um erro porque o SEARCH só recebe uma entrada.

Alguma sugestão?

    

Answer 1

Adicione o caractere curinga asterisco * antes e / ou depois da palavra "mesa", como:

=AVERAGEIF(A1:A5,"*desk",B1:B5)

ou

=AVERAGEIF(A1:A5,"*desk*",B1:B5)

Isso diz ao Excel para ignorar qualquer caractere antes / depois da palavra "mesa".

    

Answer 2

Bem, como uma resposta genérica que funcionará para você, outra maneira de fazer uma condição dentro de uma função (no seu caso, um IF dentro de uma média), você sempre pode separá-la. A instrução IF também pode retornar uma matriz e não precisa ser um valor único.

Se você digitar

=AVERAGE(IF(ISERROR(SEARCH("desk",A1:A5))=FALSE,A1:A5))

e pressione Ctrl + Deslocamento + Digite depois de digitá-lo, então ele tratará a entrada como uma matriz em vez de um valor, dando seus resultados.

Espero que isso ajude!

    

Answer 3

AVERAGEIF é a melhor resposta para o Excel 2013+. Para versões mais antigas, você precisará usar uma terceira coluna, C.

Todas as células na coluna C (com um valor à esquerda) têm essa fórmula: =IF(ISNUMBER(SEARCH(C$7,A1)), B1, "")

Cell, C7 só tem a palavra desk digitada para que essas fórmulas não precisem ser corrigidas sempre que você alterar a pesquisa.

Depois disso, você simplesmente faz uma média disso em C5, =AVERAGE(C1:C4) . Isso é tudo que você precisa. Não há muito espaço perdido e você pode se livrar da caixa de pesquisa também se preferir ajustar as funções quando necessário.

    

Answer 4

Não tenho certeza se a coluna C é uma possibilidade para você, mas aqui está uma opção que testei.

Na célula C1, insira a seguinte fórmula: = IF (ISNUMBER (SEARCH ("desk", A1)), B1, "")

Copie / cole esta fórmula para baixo para cada linha na coluna C

Média dos valores exibidos na coluna C.

Se a coluna C já estiver sendo usada, basta inserir outra coluna em seu lugar