Em uma planilha do Excel, tenho dois conjuntos de dados diferentes de três colunas cada, que representam dados muito semelhantes.
Eu gostaria de combinar isso em um conjunto de três colunas.
Cada conjunto de dados tem sua coluna mais à esquerda como a chave pela qual gostaria de agrupar.
Uma chave aparece uma vez ou nunca em cada conjunto de dados. As demais colunas são numéricas e podem estar vazias (interpretadas como zero). As duas séries podem ter comprimentos diferentes.
Igual a:
[Name_A],[Score_A],[Value_A],[Name_B],[Score_B],[Value_B]
Adam,14,20,Johnny,8,
Johnny,11,,Bernice,5,5
,,,Adam,2,8
deve agregar em:
[Name],[Score],[Value]
Adam,16,28
Johnny,19,
Bernice,5,5
A ordem das linhas no resultado final não é importante.
Responder1
Analise seus dados usando texto em colunas e usando a vírgula como delimitador. Em seguida, compile uma lista de nomes distintos (você pode fazer isso usando remover duplicatas na guia de dados do Excel) e use =sumif(Name_Column,Name1,Value1_Column).
Isso deve resumir tudo por nome e valor.
Responder2
A chave para mesclar seus conjuntos de dados é extrair uma lista de nomes exclusivos. Isso émuitomais fácil se os nomes estiverem todos em uma coluna. Se você precisa organizar seus dados conforme mostrado, existe uma solução VBA, então comente abaixo se precisar.
Na primeira tabela abaixo, movi seus dados “B” para os dados “A” e adicionei mais algumas linhas em cada seção.
Esta fórmula, preenchida a partir de E2, lista os nomes e remove duplicatas:
=IFERROR(INDEX((A$2:A$15),MATCH(0,COUNTIF($E$1:E1,A$2:A$15),0)),"")
É uma fórmula de matriz, portanto deve ser inserida com CTRLShiftEnter.
Essas duas fórmulas, preenchidas a partir de F2 e G2, somam a Pontuação e o Valor de cada nome da lista:
=IF(LEN(E2)<1,"",SUMIF(A$2:A$15,E2,B$2:B$15))
=IF(LEN(E2)<1,"",SUMIF(A$2:A$15,E2,C$2:C$15))
______________________________________________________________________
Como funciona:O interno COUNTIF()cria uma matriz com o número de vezes que cada um dos nomes na coluna A aparece na lista crescente da coluna E. O MATCH()encontra a posição do primeiro 0 (correspondente a um nome que ainda não foi listado) nessa matriz , e isso é usado como row_num em um INDEX(), que seleciona o nome da coluna A. Os nomes que já foram listados não aparecem novamente.
O IFERROR()insere espaços em branco para linhas que teriam um #NUM! erro quando a fórmula fica sem valores exclusivos.
As duas SUMIF()fórmulas apenas somam os números de Pontuação e Valor de cada nome. O IF()insere um espaço em branco para linhas onde o comprimento da coluna E é <1 (ou seja, a célula não tem nome e aparece em branco).