Estou acostumado a trabalhar com PROCV mas desta vez tenho um desafio.Não quero o primeiro valor correspondente, mas odurar.Como? (Estou trabalhando com o LibreOffice Calc, mas uma solução MS Excel deve ser igualmente útil.)
A razão é que tenho duas colunas de texto com milhares de linhas, digamos que uma seja uma lista de beneficiários da transação (Amazon, Ebay, empregador, supermercado, etc.) e a outra seja uma lista de categorias de gastos (salários, impostos, casa, aluguel, etc.). Algumas transações não têm sempre a mesma categoria de gastos, e quero pegar a usada mais recentemente. Observe que a lista não é classificada por nenhuma coluna (na verdade, por data) e não quero alterar a ordem de classificação.
O que eu tenho (excluindo o tratamento de erros) é a fórmula usual de "primeira correspondência":
=VLOOKUP(
[payee field] , [payee+category range] , [index of category column] ,
0 )
eu tenho vistosoluçõesassim, mas recebo #DIV/0!erros:
=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )
A solução pode ser qualquer fórmula, não necessariamente VLOOKUP. Também posso trocar as colunas de beneficiário/categoria. Apenas nenhuma mudança na coluna de classificação, por favor.
Pontos de bônus para uma solução que escolhe omais frequentevalor e não o último!
Responder1
Você pode usar uma fórmula de matriz para obter dados do último registro correspondente.
=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))
Insira a fórmula usando Ctrl+ Shift+ Enter.
Isso funciona como a construção INDEX/ MATCHde a VLOOKUP, mas com uma condicional MAXusada em vez de MATCH.
Observe que isso pressupõe que sua tabela comece na linha 1. Se seus dados começarem em uma linha diferente, você precisará ajustar a ROW(...)parte subtraindo a diferença entre a linha superior e 1.
Responder2
(Respondendo aqui como nenhuma pergunta separada para dados classificados.)
Se os dadoseramclassificado, você poderia usar VLOOKUPcom o range_lookupargumento TRUE(ou omitido, já que é o padrão), que é oficialmente descrito para Excel como "pesquisa por correspondência aproximada".
Em outras palavras, para dados classificados:
- definir o último argumento para
FALSEretornar oprimeirovalor, e - definir o último argumento para
TRUEretornar odurarvalor.
Isso é em grande parte não documentado e obscuro, mas data do VisiCalc (1979) e hoje é válido pelo menos no Microsoft Excel, no LibreOffice Calc e no Planilhas Google. Em última análise, isso se deve à implementação inicial de LOOKUPno VisiCalc (e daí VLOOKUPe HLOOKUP), quando não havia um quarto parâmetro. O valor é encontrado porpesquisa binária, usando limite esquerdo inclusivo e limite direito exclusivo (uma implementação comum e elegante), o que resulta neste comportamento.
Tecnicamente, isso significa que se inicia a pesquisa com o intervalo candidato [0, n), onde né o comprimento da matriz, e a condição invariante do loop é que A[imin] <= key && key < A[imax](o limite esquerdo é <= o alvo, o limite direito, que começa um após o final, é > o alvo a ser validado, verifique os valores nos pontos finais antes ou verifique o resultado depois) e dividindo sucessivamente e escolhendo o lado que preserva essa invariante: por exclusão, um lado irá, até chegar a um intervalo com 1 termo, [k, k+1)e o algoritmo então retorna k. Não precisa ser uma correspondência exata (!): é apenas a correspondência mais próxima abaixo. No caso de correspondências duplicadas, isso resulta no retorno dodurarcorrespondência, pois exige que o próximo valor sejamaiordo que a chave (ou o final da matriz). Em caso de duplicatas você precisaalgunscomportamento, e isso é razoável e fácil de implementar.
Esse comportamento é declarado explicitamente neste artigo antigo da Base de Conhecimento da Microsoft (ênfase adicionada): "XL: Como retornar a primeira ou a última correspondência em uma matriz" (Q214069):
Você pode usar a função LOOKUP() para procurar um valor dentro de uma matriz de dados classificados e retornar o valor correspondente contido naquela posição dentro de outra matriz. Se o valor de pesquisa for repetido dentro da matriz,ele retorna a última correspondência encontrada. Esse comportamento é verdadeiro para as funções VLOOKUP(), HLOOKUP() e LOOKUP().
Segue documentação oficial para algumas planilhas; em nenhum dos casos o comportamento da "última correspondência" é declarado, mas está implícito na documentação do Planilhas Google:
-
verdadeiroassume que a primeira coluna da tabela está classificada numericamente ou alfabeticamente e, em seguida, pesquisará poro valor mais próximo.
-
Se
is_sortedforTRUEou omitido,a partida mais próxima(Menor ou igualpara a chave de pesquisa) é retornado
Responder3
Se os valores na matriz de pesquisa forem sequenciais (ou seja, você está procurando o maior valor, como a data mais recente), você nem precisa usar a função INDIRETO. Experimente este código simples:
=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)
Novamente, insira a fórmula usando CTRL + SHIFT + ENTER
Responder4
Experimentei o valor mais frequente. Não tenho certeza se funcionaria no libreOffice, mas parece funcionar no Excel
=ÍNDICE($B$2:$B$9,MATCH(MAX(--($A$2:$A$9=D2)*CONTARSE($B$2:$B$9,$B$2:$B$9,$A$2 :$A$9,D2)),--($A$2:$A$9=D2)*CONTARSE($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2 ),0))
A coluna A seria o beneficiário, a coluna B seria a categoria, D2 seria o beneficiário pelo qual você deseja filtrar. Não sei por que está colocando quebras de linha extras na função acima.
Minha função para encontrar a última célula seria a seguinte:
=INDIRETO("B" & MAX(--($A$2:$A$9=D2)*LINHA($A$2:$A$9)))
Indireto me permite especificar a coluna que desejo retornar e encontrar a linha diretamente (para não precisar subtrair o número de linhas de cabeçalho).
Ambas as funções precisam ser inseridas usandoCtrl+shift+enter