Use LEFT no parâmetro sum_range da função SUMIF (Excel)

Question

Eu tenho várias colunas uma em cima da outra, assim:

Eu gostaria de encontrar todas as células nesse intervalo que começam com 1a e, em seguida, resumir o que segue. Portanto, neste exemplo, encontramos 1a10 e 1a20 , então nosso resultado seria 10 + 20 ou 30 .

O difícil é que depois do a , pode haver um ou dois dígitos, e eu também gostaria de expandi-lo para trabalhar com números de dois dígitos antes do a como bem. Então poderíamos ter:

1a10 1a1 11a1 11a10

Eu tentei experimentar o que aprendi sobre SUMIF , SUMIFS e SEARCH , mas embora eu tenha conseguido (até certo ponto) detectar quais células selecionar, cortando o início de isso, especialmente quando a duração do final e do início pode variar, me deixou com uma mistura de instruções IF empilhadas e uma fórmula não funcional.

Infelizmente, não posso usar macros e minha escola está apenas no Excel 2013, mas espero que isso ainda seja possível.

Obrigado.

    

Answer 1

Você pode fazer isso com uma fórmula de matriz

=SUM((LEFT($A$2:$A$8,2)="1a")*(IF(ISNUMBER(--RIGHT($A$2:$A$8,LEN($A$2:$A$8)-2)),RIGHT($A$2:$A$8,LEN($A$2:$A$8)-2),0)))

Como esta é uma fórmula de array, você precisa usar Control + Deslocar + Enter em vez de apenas Enter .

Além disso, como é uma fórmula de matriz, você deseja evitar referências completas a colunas, como A:A

Você saberá que entrou corretamente quando vir {} a sua fórmula. Observe que {} não pode ser adicionado manualmente.

UPDATE

Sequisertornarafórmulaumpoucomaisrobustaparalidarcomsequênciasdepesquisamaislongas,como22a,epermitirquevocêuseumacélulaparaastringdeIDquedesejasomar,useaseguintefórmula.

=SUM((LEFT($A$1:$A$8,LEN(TRIM(C2)))=C2)*(IF(ISNUMBER(--RIGHT($A$1:$A$8,LEN($A$1:$A$8)-LEN(C2))),RIGHT($A$1:$A$8,LEN($A$1:$A$8)-LEN(C2)),0)))

Observequeessaaindaéumafórmuladematriz.Alémdisso,ointervalofoiaumentadoparaincluirovalordedadosdeumúnicodígitoapósachavequevocêestápesquisando.

atualização menor, alterou o -RIGHT para --RIGHT ... mas aparentemente o - pode nem ser necessário.

    

Answer 2

Eu não quero fazer o seu trabalho para você, mas posso dar algumas sugestões ...

Existem maneiras de fazer isso com uma ou duas fórmulas grandes e longas, mas você pode dividi-las em várias fórmulas em colunas separadas para facilitar o entendimento. Por exemplo, em B1 você poderia usar

=IF(LEFT(A2,2)="1a",1,0)) Isso retornará um 1 para suas células que começam com "1a" e um 0 para as que não o fizerem.

Em C1, você poderia usar

=LEN(A2) Isso retornará o número de caracteres em A2.

A partir daí, comece a escrever instruções IF com fórmulas (pseudo código abaixo, não fórmulas reais).

"If A2=1 AND C1=4, return RIGHT(A2,2)"

"If A2=1 AND C1=3, return RIGHT(A2,1)"

Tudo o que estou fazendo é configurar a planilha para obter o "10" de 1a10 em sua própria coluna. Você pode então fazer qualquer álgebra que quiser nos números resultantes.

    

Answer 3

Aqui está outra abordagem com SUBSTITUTE :

=SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))

Como funciona:

• =SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))
• SUBSTITUTE(A1:A7,C1,"") - remove 1a de todas as linhas
• SUBSTITUTE(...)*1 - converte entradas para números sempre que possível (somente linhas em que 1a foi removido anteriormente), outras resultarão em erros
• IFERROR(...,0) - converte erros em 0

Além disso, essa é uma fórmula de matriz, portanto, é necessário pressionar CTRL + SHIFT + ENTER depois de digitá-la.