Bill fez a pergunta desta semana sobre dados redundantes do Excel.
Eu construo uma lista de transações mensais no Excel. No final do mês, preciso eliminar os dados redundantes e chegar a um total por código de conta. Cada código de conta pode ocorrer várias vezes. Bill então descreveu sua metodologia atual do Excel, que é semelhante ao método 1 abaixo, a fim de chegar a uma lista única de códigos de conta, com planos de usar uma matriz de fórmulas CSE para obter os totais. Ele pergunta: existe uma maneira mais fácil de chegar a uma lista exclusiva de códigos de conta com totais para cada conta?
Esta é uma pergunta de férias perfeita. Como usuário do Lotus há 15 anos, reconheço o método de Bill como o método clássico de manipulação de dados "rápida e suja" dos bons e velhos tempos do Lotus versão 2.1. Esta é uma época para contar nossas bênçãos. Quando você pensa sobre essa questão, percebe que o pessoal da Microsoft realmente nos deu uma série de ferramentas ao longo dos anos. Se você estiver usando o Excel 97, existem pelo menos cinco métodos para fazer essa tarefa, todos muito mais fáceis do que o método clássico descrito por Bill. Oferecerei um tutorial sobre os cinco métodos esta semana.
Meu conjunto de dados simplificado tem números de contas na coluna A e valores na coluna B. Os dados vão de A2: B100. Não está classificado no início.
Método 1
Use declarações criativas If em conjunto com Paste Special Values para encontrar a resposta.
Dadas as ferramentas mais recentes oferecidas pelo Excel, não recomendo mais esse método. Eu costumava usar isso muito antes de coisas melhores surgirem e ainda há situações em que é útil. Meu nome alternativo para isso é método "The-Lotus-123-When-You-Not-In-The-Were-Use- @ DSUM". Aqui estão as etapas.
- Classifique os dados pela coluna A.
- Invente uma fórmula na coluna C que manterá um total corrente por conta. A célula C2 é
=IF(A2=A1,C1+B2,B2). - Invente uma fórmula em D que identificará a última entrada para uma conta específica. A célula D2 é
=IF(A2=A3,FALSE,TRUE). - Copie C2: D2 para todas as suas linhas.
- Copiar C2: D100. Faça um Edit - PasteSpecial - Values de volta para C2: D100 para alterar as fórmulas para valores.
- Classifique pela coluna D decrescente.
- Para as linhas que possuem um TRUE na coluna D, você tem uma lista única de números de contas em A e o total final em C.
Prós: é rápido. Tudo que você precisa é um senso apurado de escrever declarações IF.
Contras: Existem maneiras melhores.
Método 2
Use Filtro de dados - Filtro avançado para obter a lista de contas exclusivas.
A pergunta de Bill era realmente como obter uma lista exclusiva de números de contas para que ele pudesse usar as Fórmulas CSE para obter os totais. Este é um método para obter uma lista dos números de conta exclusivos.
- Destaque A1: A100
- No menu, escolha Dados, Filtro, Filtro Avançado
- Clique no botão de opção "Copiar para outro local".
- Clique na caixa de seleção para "Somente registros exclusivos".
- Escolha uma seção em branco da planilha onde deseja que a lista exclusiva apareça. Digite isso no campo "Copiar para:". (Observe que este campo fica esmaecido até que você selecione "Copiar para outro local".
- Clique OK. Os números de conta exclusivos aparecerão em F1.
- Insira quaisquer manipulações de downline, fórmulas de matriz, etc. para obter seus resultados.
Prós: mais rápido do que o método 1. Nenhuma classificação necessária.
Contras: as fórmulas CSE exigidas depois disso farão sua cabeça girar.
Método 3
Use Data Consolidate.
Minha qualidade de vida melhorou quando o Excel ofereceu o Data Consolidate. Isso foi GRANDE! Leva 30 segundos para configurá-lo, mas significa morte para DSUMs e outros métodos. O número da sua conta deve estar à esquerda dos campos numéricos que você deseja totalizar. Você deve ter cabeçalhos acima de cada coluna. Você precisa atribuir um nome de intervalo ao bloco retangular de células que inclui os números das contas na coluna esquerda e os títulos na parte superior. Nesse caso, esse intervalo é A1: B100.
- Destaque A1: B100
- Atribua um nome de intervalo a esta área clicando na caixa de nome (à esquerda da barra de fórmulas) e digitando um nome como "TotalMe". (Alternativamente, use Inserir - Nome).
- Coloque o ponteiro da célula em uma seção em branco da planilha.
- Selecionar dados - consolidar
- No campo de referência, digite o nome do intervalo (TotalMe).
- Na seção Usar rótulos em, marque Linha superior e Coluna esquerda.
- Clique OK
Prós: Este é meu método favorito. Nenhuma classificação necessária. O atalho é alt-D N (rangename) alt-T alt-L enter. É facilmente escalável. Se o seu intervalo inclui 12 colunas mensais, a resposta terá totais para cada mês.
Contras: se você fizer outra consolidação de dados na mesma planilha, será necessário limpar o nome do intervalo antigo do campo Todas as referências usando o botão Excluir. O número da conta deve estar à esquerda de seus dados numéricos. É um pouco mais lento do que as tabelas dinâmicas, o que se torna perceptível para conjuntos de dados com mais de 10.000 registros.
Método 4
Use subtotais de dados.
Este é um recurso interessante. Como os dados resultantes são estranhos de se trabalhar, eu os uso com menos frequência do que o Data Consolidate.
- Classificar pela coluna A crescente.
- Selecione qualquer célula no intervalo de dados.
- Escolha Dados - Subtotais no menu.
- Por padrão, o Excel oferece um subtotal da última coluna de seus dados. Isso funciona neste exemplo, mas geralmente você precisa rolar a lista "Adicionar subtotal a:" para escolher os campos corretos.
- Clique OK. O Excel irá inserir uma nova linha a cada mudança de número de conta com um total.
Depois de inserir os subtotais, você verá um pequeno 123 aparecer abaixo da caixa de nome. Clique em 2 para ver apenas uma linha por conta com os totais. Leia Copiar subtotais do Excel para obter uma explicação das etapas especiais necessárias para copiá-los para um novo local. Clique no 3 para ver todas as linhas. Prós: recurso interessante. Ótimo para imprimir relatórios com totais e quebras de página após cada seção.
Contras: os dados devem ser classificados primeiro. Lento para muitos dados. Você deve usar Goto-Special-VisbileCellsOnly para obter os totais em outro lugar. Você precisa usar Data-Subtotals-RemoveAll para voltar aos dados originais.
Método 5
Use uma tabela dinâmica.
As tabelas dinâmicas são as mais versáteis de todas. Seus dados não precisam ser classificados. As colunas numéricas podem estar à esquerda ou à direita do número da conta. Você pode facilmente fazer com que os números das contas baixem ou percorram a página.
- Selecione qualquer célula no intervalo de dados.
- Escolha Dados - Tabela Dinâmica no menu.
- Aceite os padrões na Etapa 1
- Certifique-se de que o intervalo de dados na etapa 2 está correto (geralmente está)
- Se você estiver usando o Excel 2000, clique no botão Layout na etapa 3. Os usuários do Excel 95 e 97 vão automaticamente para o layout na etapa 3.
- Na caixa de diálogo de layout, arraste o botão Conta do lado direito da caixa de diálogo e solte-o na área Linha.
- Arraste o botão Quantidade do lado direito da caixa de diálogo e solte-o na área de Dados.
- Os usuários do Excel 2000 clicam em OK e os usuários do Excel 95/97 clicam em Avançar.
- Especifique se deseja os resultados em uma nova planilha ou em uma seção específica de uma planilha existente. Leia mais sobre tabelas dinâmicas em Truques avançados de tabelas dinâmicas do Excel.
- As tabelas dinâmicas oferecem funcionalidade incrível e tornam essa tarefa muito fácil. Para copiar os resultados da tabela dinâmica, você precisa fazer um Edit-PasteSpecial-Values, caso contrário, o Excel não permitirá que você insira linhas, etc.
Prós: rápido, flexível, poderoso. Rápido, mesmo para muitos dados.
Contras: Um pouco intimidante.
Bill agora tem quatro novos métodos para eliminar os dados redundantes. Embora esses métodos não estejam disponíveis desde o início dos tempos, o Lotus e o Excel têm sido grandes inovadores para nos oferecer maneiras mais rápidas de realizar essa tarefa mundana.
