Excel: soma valores correspondentes a vários números correspondentes ou individuais

Question

esperando que alguém possa ajudar com isso:

Os números à esquerda (números de telefone) serão individuais ou repetidos. Cada linha tem um número na segunda coluna que está associado a esse número de telefone.

Eu preciso da soma dos valores na segunda coluna correspondente a cada valor único na primeira coluna. Atualmente, seleciono autosum, manualmente faço minha seleção e obtenho o valor somado.

Minha lista geralmente é de 2500 linhas, então isso pode levar algum tempo. Existe alguma maneira de automatizar isso?

    

Answer 1

Três soluções:

^{(suponho que seus dados estejam nas células A1:B22 .)}

1. Formatação Condicional

• Defina C1 para =B1 .
  (Se seus dados não começarem na coluna 1, e a linha antes da primeira linha de dados não tem números, você pode usar a próxima fórmula para a coluna inteira.
• Defina C2 para =IF(A1=A2, C1+B2, B2) e arraste / preencha para C22 .
  Isso definirá a coluna C como um total em execução para os números correspondentes.
  ou seja, C1 = 50, C2 = 220, C3 = 320, C4 = 900, C5 = 500, etc.
• Selecione os resultados (por exemplo, células C1:C22 ) e faça “Formatação Condicional” → “Nova Regra”. Selecione "usar uma fórmula para determinar quais células devem ser formatadas", insira a fórmula =A1=A2 e formate a célula para ficar invisível. (Maneiras comuns de fazer isso são para definir a cor da fonte para branco ou para aplicar um formato numérico personalizado de ;;; .

Caso o acima não esteja claro: Isso coloca um número em cada célula do intervalo, mas apenas esconde aqueles que você não quer.

2. Coluna Auxiliar

• Escolha uma coluna que esteja fora do caminho; por exemplo, coluna Z . Defina-o da mesma forma que definimos a coluna C , acima.
• Defina C1 para =IF(A1=A2, "", Z1) e arraste / preencha para C22 .

3. All-in-One

• Defina C1 para =IF(A1=A2, "", SUMIF(A$1:A$22, A1, B$1:$B22)) e arraste / preencha para C22 .

Observe que, se os números de telefone não estiverem classificados corretamente, e não são divididos em grupos únicos, esses métodos produzem resultados diferentes. Rotulando os métodos da seguinte forma:

1. Formatação condicional
2. Coluna auxiliar
3. All-in-One

considere estes dados:

phone   value   method1   method2   method3
 ︙       ︙       ︙        ︙        ︙
 95      800     1500      1500      1500
 42        1                                ← First block of data for phone # 42
 42        2        3         3        99   ← Note that methods 1 and 2 yield 1 + 2 = 3
 17        4                                ↖ but method 3 yields 1 + 2 + 32 + 64 = 99
 17        8
 17       16       28        28        28
 42       32                                ← Second block of data for phone # 42
 42       64       96        96        99   ← Note that methods 1 and 2 yield 32 + 64 = 96
 83     1000                                ↖ but method 3 yields 1 + 2 + 32 + 64 = 99
 83     2000                                                                       (again)
 83     4000     7000      7000      7000
 ︙       ︙

    

Answer 2

Ao usar o SUMIF, aqui vai o seu exemplo

Seu exemplo

você pode usar essa fórmula para uma coluna vazia correspondente à coluna de números de telefone

=SUMIF($B$5:$B$14,E5,$C$5:$C$14)

    

Answer 3

O comprimento não deve ser um problema - esta é uma atividade muito comum que eu tive que fazer muitas vezes durante a minha carreira. Eu uso a função SUMIF da seguinte forma:

1. Pegue toda a primeira coluna e coloque-a em uma nova planilha (col A) e classifique-a como A-Z.
2. Coloque em uma célula B2 =IF(A1=A2,1,0) e preencha as fórmulas automaticamente.
3. Filtre todos os valores '1' e exclua essas linhas - você fica com números exclusivos (números de telefone)
4. elimine os valores da coluna B e utilize SUMIF em cada número (telefone) exclusivo. O intervalo é fixado no 1º col, primeira folha. Critérios é cada número (número de telefone) do Col A. O intervalo de soma é o 2º col, primeira folha.

Pode haver uma maneira de transformar isso em uma macro para você mesmo.