Agrupando colunas de dados por valores compartilhados

Question

Não sei descrever corretamente o que preciso fazer, então darei um exemplo. Um colega tem um conjunto de dados no Excel assim:

Col A    Col B    Col C
aaaaa    aaaaa    bbbbb
bbbbb    ccccc    ccccc
ccccc    ddddd    eeeee

O resultado final deve ser algo assim:

Col A    Col B    Col C
aaaaa    aaaaa
bbbbb             bbbbb
ccccc    ccccc    ccccc
         ddddd
                  eeeee

Ou ainda:

      Col A    Col B    Col C
aaaaa Yes      Yes      No
bbbbb Yes      No       Yes

etc.

(se ajudar, as colunas são métodos de extração de proteínas e as letras são IDs de proteínas - precisamos determinar quais proteínas são extraídas por quais métodos)

Meu colega está fazendo isso manualmente, mas há dados suficientes que seriam realmente úteis para automatizá-lo.

Existe uma fórmula no Excel para fazer isso automaticamente?

Answer 1

Esta não é uma solução “chave na mão”, mas se você tiver milhares de linhas, isso pode poupar algum esforço. (Faça isso em uma cópia de rascunho do seu arquivo, caso algo exploda ou derreta, porque “Desfazer” nem sempre funciona.) Observação: este procedimento foi desenvolvido para Excel 2007 (mas eu o verifiquei novamente em Excel 2013).

Primeiro, copie todos os seus dados em uma coluna de rascunho; vamos chamá-lo de V. Observe que você deve copiar o título da Coluna A, ou então colocar algum valor fictício na célula V1.

Agora vá até a aba “Dados”, grupo “Classificar e Filtrar”, e clique em “Avançado”:

                       

Isso abrirá a caixa de diálogo “Filtro Avançado”:

                 

Verifique se “Intervalo da lista” mostra seus dados na coluna V. Selecione “Copiar para outro local” e “Somente registros exclusivos”. Digite “W1” no campo “Copiar para” — ou clique no campo e depois clique em W1 (existem várias técnicas que obterão o mesmo resultado). Clique em “OK”. Você deve obter algo assim:

                             

ou seja, uma lista de seus valores de dados exclusivos. Pode ser necessário classificar a coluna W.

Agora entre =NOT(ISNA(VLOOKUP($W2,A$2:A$4,1,FALSE)))em X2 (substitua pelo  4número da última linha que contém dados) e arraste/preencha para corresponder à Coluna W (ou seja, uma linha para cada valor exclusivo em seus dados originais) e à direita da Coluna Z ( ou seja, o número de colunas em seus dados).

                              

Isto lhe dá uma tabela verdade correspondente à segunda forma do resultado desejado na questão (mas com “VERDADEIRO” e “FALSO” em vez de “Sim” e “Não”). Por exemplo,

• X2 é VERDADEIRO porque a coluna A contém “aaaaa”,
• X3 é VERDADEIRO porque a coluna A contém “bbbbb”,
• Y2 é VERDADEIRO porque a coluna B contém “aaaaa”,
• Y3 é FALSO porque a Coluna B não contém “bbbbb”, etc.

Exclua a coluna V e corrija os títulos (na linha 1) como desejar. Se você não quiser manter as Colunas AC na planilha, copie as Colunas WZ e cole os valores.

---

Algumas explicações sobre a fórmula: A fórmula que apresentei acima é para uso na Coluna X, que corresponde à Coluna A.  Como usei $W2, esta é uma referência absoluta à coluna W e se referirá à célula quando a fórmula for arrastada/preenchida para a linha Wnnde qualquer coluna. Por outro lado, A$2:A$4é uma referência absoluta às Linhas 2 a 4, mas uma referência relativa à Coluna A. Quando a fórmula é arrastada para a Coluna Y, esta referência mudará automaticamente para  B$2:B$4. Quando a fórmula é arrastada para a Coluna Z, esta referência mudará automaticamente para  C$2:C$4.