Procurando uma matriz de baixo para cima

Question

Eu tenho uma matriz de tabela $E$14:$G$851 . A coluna E contém apenas identificadores únicos, digamos a / b / c / d etc, a coluna F contém centenas de números menores ou iguais a 20 e a coluna G contém os valores que eu preciso retornar.

Eu preciso de uma fórmula que pesquise o identificador exclusivo na coluna E, pesquise UP coluna F e encontre o PRIMEIRO valor que é MENOR QUE o resultado da coluna F e use esse resultado para retornar o valor adjacente na coluna G.

Portanto, se eu tivesse pesquisado "g", a fórmula procuraria a coluna UP F para o primeiro número MENOS QUE 16. Isso seria "d" com 12. a fórmula retornaria 25010 da coluna G.

Eu tenho procurado por uma resposta para isso há semanas e não encontrei nenhum exemplo de excel pesquisando um array de baixo para cima. Eu preferiria não alterar o layout dos dados (como invertê-lo).

    

Answer 1

Você pode obtê-lo com a seguinte fórmula de matriz (pressione CTRL + SHIFT + ENTER depois de inseri-lo na célula:

=INDEX(C:C,MAX(IF(OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(B1,0,0,MATCH(E1,A:A,0)-1)),"")))

Como funciona:

• OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1) - obtém uma referência da coluna B sobre o ID sendo pesquisado
• IF(OFFSET(...)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(...),"") - retorna uma matriz contendo o número da linha para linhas com valor em B menor que a linha pesquisada e em branco para as outras
• MAX(IF(...)) - selecione o valor máximo (última linha com valor em critérios de preenchimento B
• =INDEX(C:C,MAX(...)) - selecione a célula na coluna C

    

Answer 2

No exemplo, a coluna com o resultado é G, mas qual é a linha correta? Se conseguirmos encontrar a linha, então INDIRECT("G"&row) obterá o resultado.

UmasoluçãoéadicionarumacolunadeponteiroscomocolunaH.CadaponteiroéalinhaparaINDIRECTeVLOOKUPpodeencontrá-lo.AcélulaC2temafórmula.

=INDIRECT("G"&VLOOKUP(B2,$E$1:$H$9,4,FALSE))

Agora, a pesquisa para trás é reduzida para VLOOKUP de um ponteiro para o resultado. Como gerar a coluna H? Eu ainda não encontrei um jeito apenas com as fórmulas do Excel, mas aqui estão algumas idéias.

Se a coluna F raramente muda, algumas linhas de perl ou python com um copiar e colar são tudo o que é necessário. Eu usei o perl. Se a coluna F for atualizada com mais frequência, o VBA poderá funcionar. Como alternativa, o perl pode ler os dados do arquivo principal do Excel e gerar uma segunda pasta de trabalho para usar como uma fonte externa.

Os ponteiros evitam a necessidade de inverter os dados ou alterar o layout dos dados. A difícil pesquisa para trás é feita uma única vez por perl ou VBA, e VLOOKUP é rápido, simples e fácil de testar.