Conteúdo
Todas as funções clássicas de pesquisa e substituição de tipo VPR (PROCV), GPR (PROCURAR), MAIS EXPOSTOS (COMBINE) e aqueles como eles têm uma característica importante – eles pesquisam do início ao fim, ou seja, da esquerda para a direita ou de cima para baixo nos dados de origem. Assim que a primeira correspondência for encontrada, a pesquisa para e apenas a primeira ocorrência do elemento que precisamos é encontrada.
O que fazer se precisarmos encontrar não a primeira, mas a última ocorrência? Por exemplo, a última transação do cliente, o último pagamento, o pedido mais recente, etc.?
Método 1: Encontrando a última linha com uma fórmula de matriz
Se a tabela original não tiver uma coluna com data ou número de série de uma linha (pedido, pagamento...), nossa tarefa é, de fato, encontrar a última linha que satisfaça a condição dada. Isso pode ser feito com a seguinte fórmula de matriz:
Aqui:
- função IF (E SE) verifica todas as células em uma coluna uma por uma Experiência e dinâmica de loja e exibe o número da linha se contiver o nome que precisamos. O número da linha na folha nos é dado pela função LINHA (FILEIRA), mas como precisamos do número da linha na tabela, também temos que subtrair 1, porque temos um cabeçalho na tabela.
- Então a função MAX (MÁX) seleciona o valor máximo do conjunto formado de números de linha, ou seja, o número da linha mais recente do cliente.
- função ÍNDICE (ÍNDICE) retorna o conteúdo da célula com o último número encontrado de qualquer outra coluna da tabela necessária (Código de encomenda).
Tudo isso deve ser inserido como fórmula de matriz, ou seja:
- No Office 365 com as atualizações mais recentes instaladas e suporte para matrizes dinâmicas, você pode simplesmente pressionar Entrar.
- Em todas as outras versões, depois de inserir a fórmula, você deverá pressionar o atalho de teclado Ctrl+Shift+Entrar, que adicionará automaticamente chaves na barra de fórmulas.
Método 2: Pesquisa reversa com a nova função LOOKUP
Já escrevi um longo artigo com um vídeo sobre um novo recurso VEJA (XLOOKUP), que apareceu nas versões mais recentes do Office para substituir o antigo VLOOKUP (PROCV). Com a ajuda do BROWSE, nossa tarefa é resolvida de maneira bastante elementar, porque. para esta função (ao contrário de VLOOKUP), você pode definir explicitamente a direção da pesquisa: de cima para baixo ou de baixo para cima – seu último argumento (-1) é responsável por isso:
Método 3. Procure uma string com a data mais recente
Se nos dados de origem tivermos uma coluna com um número de série ou uma data que desempenha um papel semelhante, a tarefa será modificada - precisamos encontrar não a última linha (mais baixa) com uma correspondência, mas a linha com a última ( máximo) data.
Já discuti em detalhes como fazer isso usando funções clássicas, e agora vamos tentar usar o poder das novas funções dinâmicas de array. Para maior beleza e conveniência, também convertemos a tabela original em uma tabela “inteligente” usando um atalho de teclado Ctrl+T ou comandos Home – Formatar como uma tabela (Início — Formatar como Tabela).
Com a ajuda deles, esse “casal assassino” resolve nosso problema com muita graça:
Aqui:
- Função primeiro FILTRO (FILTRO) seleciona apenas as linhas da nossa tabela onde na coluna Experiência e dinâmica de loja – o nome que precisamos.
- Então a função GRADE (ORDENAR) classifica as linhas selecionadas por data em ordem decrescente, com o negócio mais recente no topo.
- função ÍNDICE (ÍNDICE) extrai a primeira linha, ou seja, retorna a última negociação que precisamos.
- E, finalmente, a função FILTER externa remove as 1ª e 3ª colunas extras dos resultados (Código de encomenda и Experiência e dinâmica de loja) e deixa apenas a data e o valor. Para isso, um array de constantes é usado. {0;1;0;1}, definindo quais colunas queremos (1) ou não queremos (0) para exibir.
Método 4: Encontrando a última correspondência no Power Query
Bem, por uma questão de integridade, vamos ver uma solução para nosso problema de pesquisa reversa usando o suplemento Power Query. Com a ajuda dela, tudo é resolvido de forma muito rápida e bonita.
1. Vamos converter nossa tabela original em uma “inteligente” usando um atalho de teclado Ctrl+T ou comandos Home – Formatar como uma tabela (Início — Formatar como Tabela).
2. Carregue-o no Power Query com o botão Da tabela/intervalo aba Data (Dados — Da Tabela/Intervalo).
3. Classificamos (através da lista suspensa do filtro no cabeçalho) nossa tabela em ordem decrescente de data, para que as transações mais recentes fiquem no topo.
4… Na aba Transformação escolha um time Agrupar por (Transformar — Agrupar por) e defina o agrupamento por clientes, e como função agregadora, selecione a opção Todas as linhas (Todas as linhas). Você pode nomear a nova coluna como quiser - por exemplo Adicionar ao carrinho.
Após o agrupamento, obteremos uma lista de nomes exclusivos de nossos clientes e na coluna Adicionar ao carrinho – tabelas com todas as transações de cada uma delas, onde a primeira linha será a última transação, que é o que precisamos:
5. Adicione uma nova coluna calculada com o botão Coluna personalizada aba Adicionar coluna (Adicionar coluna — Adicionar coluna personalizada)e digite a seguinte fórmula:
Aqui Adicionar ao carrinho – esta é a coluna da qual tiramos tabelas por clientes, e 0{} é o número da linha que queremos extrair (a numeração de linha no Power Query começa em zero). Obtemos uma coluna com registros (Registro), onde cada entrada é a primeira linha de cada tabela:
Resta expandir o conteúdo de todos os registros com o botão com setas duplas no cabeçalho da coluna Última oferta selecionando as colunas desejadas:
… e, em seguida, exclua a coluna que não é mais necessária Adicionar ao carrinho clicando com o botão direito do mouse em seu título – Remover colunas (Remover colunas).
Depois de carregar os resultados para a planilha por meio de Home — Fechar e carregar — Fechar e carregar (Início — Fechar e carregar — Fechar e carregar para…) obteremos uma tabela tão bonita com uma lista de transações recentes, como queríamos:
Ao alterar os dados de origem, não se esqueça de atualizar os resultados clicando com o botão direito do mouse neles – o comando Atualizar e salvar (Atualizar) ou atalho de teclado Ctrl+outro+F5.
- A função LOOKUP é descendente de VLOOKUP
- Como usar as novas funções de matriz dinâmica SORT, FILTER e UNIC
- Encontrando a última célula não em branco em uma linha ou coluna com a função LOOKUP