Existe uma fórmula matadora para extrair os valores exclusivos que correspondem a um critério. Requer Ctrl + Shift + Enter, mas é poderoso e legal.
Este exemplo está muito além do escopo deste livro. Existe um tipo secreto de fórmula no Excel que requer que você pressione Ctrl + Shift + Enter para desbloquear os poderes da fórmula.
Se você ou eu precisássemos obter uma lista exclusiva de valores da coluna B, faríamos algo como usar um filtro avançado ou uma tabela dinâmica ou copiar os dados e usar Remover duplicatas. Esses métodos levam cinco segundos e são fáceis para você ou para mim.
O problema surge quando você precisa que o gerente do seu gerente use a planilha. Você não pode esperar que o vice-presidente de vendas domine a cópia e remoção de duplicatas. Você precisa de uma forma de ter fórmulas ativas que sempre extrairão listas exclusivas de valores.
As fórmulas para fazer isso são absolutamente insanas. Mas eles funcionam. Na figura abaixo, uma fórmula longa em D2 calcula quantos valores exclusivos existem na lista. Uma fórmula ainda mais longa em D5 que é copiada extrai a lista exclusiva.
Aqui está a fórmula. Não vou tentar explicar para você.
Mas farei a próxima melhor coisa. Vou apresentá-lo a alguém que pode explicar isso a você. Mike Girvin produziu milhares de vídeos Excel no YouTube sob o canal ExcelisFun. Ele também escreveu alguns livros do Excel, incluindo Ctrl + Shift + Enter - o guia completo para essas fórmulas incríveis. No livro, Mike explica esta fórmula e muitas outras fórmulas em detalhes para que você possa entender como elas funcionam e escrever a sua própria.
Se você está prestes a desistir de uma fórmula porque ela não pode ser feita, há uma boa chance de as fórmulas do livro de Mike resolverem o problema.
Ctrl + Shift + Enter »
Agradecimentos a Mike Girvin, Olga Kryuchkova e @canalyze_it por sugerirem este recurso.
Enquanto estou promovendo o livro de Mike Girvin, devo mencionar que você deve verificar o canal ExcelisFun no YouTube, onde ele tem milhares de vídeos incríveis gratuitos. Mike e eu fizemos uma série de vídeos divertidos de duelos no Excel, onde mostramos várias maneiras de resolver problemas no Excel.
Você poderia dizer que Mike é o Elvis do Excel.
Assistir vídeo
- Como obter uma lista dos valores únicos
- Filtro avançado apenas com valores únicos
- Tabela Dinâmica
- Fórmula de formatação condicional
=COUNTIF(G$1:G1,G2)=0 - Remover Duplicados
- Fórmula de matriz do livro de Mike Girvin
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2045 - Extraia Únicos!
Clique no “i” no canto superior direito para acessar a lista de reprodução, colocando em podcast todas as minhas dicas neste livro!
Tudo bem, nosso objetivo hoje, temos um banco de dados de clientes aqui, e quero obter uma lista exclusiva de clientes, apenas os clientes dessa lista. E a maneira antiga de fazer isso é usar um Filtro avançado, selecionar os dados, ir para Dados, Filtro, Avançado, dizer que queremos copiá-lo para o local. O local para o qual queremos copiar tem o título que queremos, e eu quero Unique Records Only, clique em OK e lá estão os clientes, uma lista exclusiva de clientes encontrada nessa lista, incrível, certo? Caminho mais rápido, Inserir, Tabela Dinâmica, Planilha Existente, clique em OK e marque Cliente, BAM, há uma lista exclusiva de clientes.
Ei, a formatação condicional, a formatação condicional supostamente faz isso. Formatação condicional, Células de destaque, Valores duplicados, selecione os valores únicos, clique em OK e absolutamente nada acontece. Isso porque quem criou esse recurso não usa a mesma versão em inglês que eu. Para eles, um valor único é um valor que aparece exatamente uma vez e apenas uma vez, tudo bem, então eles vão marcá-lo, se aparecer 2-3-4-5 vezes, eles não marcam nada, isso não é útil em todos. Mas hey, vou pressionar Tab aqui, se você realmente quiser fazer isso com a formatação condicional, e não sei por que faria, poderíamos ter feito isso facilmente. alt = "" OD, crie uma nova regra, use uma fórmula, e a fórmula diz “Conte tudo desde a linha 1 até a linha logo acima de nós, veja se é igual a isso,se este item nunca apareceu antes, marque-o em vermelho. ” e obtemos os valores exclusivos no topo. Você pode então ir para Dados - na verdade aqui, vamos apenas clicar com o botão direito do mouse em Sort e dizer Put Selected Cell Color on Top, e isso trará essa lista exclusiva para o topo. Todos esses empalidecem em comparação com o que nos deram um Excel 2010. Certifique-se de fazer uma cópia dos dados, deixe-me fazer uma cópia dos dados, Ctrl + C, venha aqui, Ctrl + V e, em seguida, remova as duplicatas , clique em OK, BAM, nada é mais rápido do que isso!deixe-me fazer uma cópia dos dados, Ctrl + C, venha aqui, Ctrl + V, e a seguir remova as duplicatas, clique em OK, BAM, nada é mais rápido que isso!deixe-me fazer uma cópia dos dados, Ctrl + C, venha aqui, Ctrl + V, e a seguir remova as duplicatas, clique em OK, BAM, nada é mais rápido que isso!
Mas aqui está o problema, se você deseja usar Filtro avançado, tabelas dinâmicas, formatação condicional rígida ou Remover duplicatas, tudo isso está além da capacidade do gerente do seu gerente. Certo, você nunca conseguirá fazer com que eles façam isso. Então, às vezes, você precisa ser capaz de fazer isso com uma fórmula, e esta fórmula é do meu livro atual “Ctrl + Shift + Enter”, é necessário pressionar Ctrl + Shift + Enter para fazer funcionar, é uma fórmula insanamente incrível. E, em meu livro “MrExcel XL”, eu só queria salientar que há um conjunto incrível de fórmulas neste livro. Se você tiver que fazer algo, como extrair uma lista exclusiva de clientes ou extrair todos os clientes que correspondem a uma determinada lista, há um capítulo inteiro no livro de Mike sobre como fazer isso. Normalmente, eu pediria para você comprar meu livro hoje, eu nãot acho que você deveria comprar meu livro, acho que deveria comprar o livro de Mike, clique no “i” no canto superior direito para acessá-los.
Hoje falamos sobre como obter os valores exclusivos, é Filtro Avançado, Tabela Dinâmica, Fórmula de Formatação Condicional ou o mais fácil, Remover Duplicatas. Mas se você precisar obter uma lista de valores exclusivos sem que nenhum gerente execute nenhuma etapa no Excel, existe uma fórmula de matriz que resolverá esse problema.
Bem, ei, quero agradecer a sua visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2045.xlsm
