Outro dia, eu estava prestes a criar uma combinação única de duas colunas não adjacentes no Excel. Normalmente faço isso com Remover Duplicatas ou com Filtro Avançado, mas pensei em tentar fazer isso com a nova função UNIQUE que virá para o Office 365 em 2019. Tentei várias ideias e nenhuma funcionou. Então, procurei o mestre de Dynamic Arrays, Joe McDaid, para obter ajuda. A resposta é muito legal, e tenho certeza que vou esquecê-la, então estou documentando para você e para mim. Tenho certeza que, daqui a dois anos, vou pesquisar no Google como fazer isso e perceber "Oh, olhe! Fui eu que escrevi o artigo sobre isso!"
Antes de chegar à função UNIQUE, dê uma olhada no que estou tentando fazer. Quero cada combinação única de Representante de Vendas da coluna B e Produto da coluna C. Normalmente, eu seguiria estas etapas:
- Copie os títulos de B1 e D1 para uma seção em branco da planilha
- Em B1, escolha Dados, Filtro, Avançado
- Na caixa de diálogo Filtro avançado, escolha Copiar para um novo local
- Especifique os títulos da Etapa 1 como o intervalo de saída
- Escolha a caixa apenas para valores únicos
- Clique OK
O resultado é cada combinação única dos dois campos. Observe que o Filtro avançado não classifica os itens - eles aparecem na sequência original.
Esse processo se tornou mais fácil no Excel 2010 graças ao comando Remover Duplicatas na guia Dados da Faixa de Opções. Siga esses passos:
- Selecione B1: D227 e Ctrl + C para copiar
-
Cole em uma seção em branco da planilha.
Faça uma cópia dos dados, pois Remover Duplicados é destrutivo - Escolha dados, remova duplicatas
- Na caixa de diálogo Remover Duplicados, desmarque Data. Isso informa ao Excel para olhar apenas para representante e produto.
-
Clique OK
Diga para Remover Duplicados para considerar apenas Representante e Data
Os resultados são quase perfeitos - você só precisa excluir a coluna Data.
A pergunta: há alguma maneira de a função UNIQUE olhar apenas para as colunas B e D? (Se você ainda não viu a nova função UNIQUE, leia: Função UNIQUE no Excel.)
Se você pedir =UNIQUE(B2:D227), obteria cada combinação única de representante, data e produto que não é o que procuramos.
Quando os Dynamic Arrays foram introduzidos em setembro, eu disse que nunca mais precisaríamos nos preocupar com as complexidades das fórmulas Ctrl + Shift + Enter. Mas para resolver esse problema, você usará um conceito chamado Lifting. Espero que agora você tenha baixado meu e-book Excel Dynamic Arrays Straight To The Point. Vá para as páginas 31-33 para uma explicação completa de levantamento.
Pegue uma função do Excel que espera um único valor. Por exemplo, =CHOOSE(Z1,"Apple","Banana")retornaria Apple ou Banana, dependendo se Z1 contém 1 (para Apple) ou 2 (para Banana). A função CHOOSE espera um escalar como primeiro argumento.
Mas, em vez disso, você vai passar uma constante de array de (1,2) como o primeiro argumento para CHOOSE. O Excel executará a operação de levantamento e calculará CHOOSE duas vezes. Para o valor de 1, você deseja os representantes de vendas em B2: B227. Para o valor de 2, você deseja os produtos em D2: D227.
Normalmente, no antigo Excel, a interseção implícita teria confundido os resultados. Mas agora que o Excel pode espalhar os resultados para muitas células, a fórmula acima retorna com sucesso uma matriz de todas as respostas em B e D:
Sinto que estaria insultando sua inteligência ao escrever o resto do artigo, porque daqui é super simples.
Enrole a fórmula da captura de tela anterior em UNIQUE e você terá apenas as combinações exclusivas de representante de vendas e produto usando =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).
Para verificar sua compreensão, tente alterar a fórmula acima para retornar todas as combinações exclusivas de três colunas: Representante de Vendas, Produto, Cor.
Primeiro, altere a constante da matriz para se referir a (1,2,3).
Em seguida, adicione um quarto argumento para escolher retornar cor E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).
Seria bom classificar esses resultados, então passamos para Classificar com uma fórmula usando SORT e SORTBY.
Normalmente, a função para classificar pela primeira coluna crescente seria =SORT(Array)ou =SORT(Array,1,1).
Para classificar por três colunas, você precisa fazer alguns levantamentos aos pares com =SORT(Array,(1,2,3),(1,1,1)). Nessa fórmula, quando você chega ao segundo argumento de SORT, o Excel deseja saber por qual coluna classificar. Em vez de um único valor, envie três colunas dentro de uma constante de matriz: (1,2,3). Quando você chegar ao terceiro argumento, onde especifica 1 para Ascendente ou -1 para Descendente, envie uma constante de matriz com três 1's para indicar Ascendente, Ascendente, Ascendente. A seguinte captura de tela mostra =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).
Pelo menos até o final de 2018, você pode baixar o livro Excel Dynamic Arrays gratuitamente usando o link na parte inferior desta página.
Sinto-me encorajado ao descobrir que a resposta à pergunta de hoje é um pouco complicada. Quando Dynamic Arrays foi lançado, pensei instantaneamente em todas as fórmulas incríveis postadas no quadro de mensagens por Aladin Akyurek e outros e como essas fórmulas se tornariam muito mais simples no novo Excel. Mas o exemplo de hoje mostra que ainda haverá uma necessidade para os gênios das fórmulas criarem novas maneiras de usar os Arrays Dinâmicos.
Assistir vídeo
Baixar arquivo Excel
Para baixar o arquivo excel: unique-from-non-adjacente-columns.xlsx
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Regras para listas: sem linhas em branco, sem colunas em branco, cabeçalhos de uma célula, como com como"
Anne Walsh
