Procura um número em uma coleção de intervalos no Excel

Question

Como no exemplo abaixo, eu tenho uma tabela contendo uma coleção de intervalos.

Cada intervalo tem uma descrição associada. Desejo procurar a descrição de um número que se encaixa em um dos intervalos, mas não está listado explicitamente como um terminal de intervalo.

Por exemplo : encontre a descrição associada ao valor 208 na tabela abaixo.

--------------------------------------
| Item | Location Description        |
|------------------------------------|  
| 1    | 100 to 103 agenda           |
| 2    | 106 to 111 Notes of Meeting |  
| 3    | 112 to 123 revision         |
| 4    | 200 to 210 factor           |
--------------------------------------

Existe uma maneira de fazer isso?

    

Answer 1

Eu estou supondo que isso é o que você está procurando. Se você criar uma tabela em um formato ligeiramente diferente, poderá usar uma função interna do Excel para fazer isso como uma pesquisa simples:

Emvezdemostrarosintervalosnoformuláriodasuapergunta,useumatabelaquetenhaovalorinicialparacadaintervalo.Euadicioneivaloresparapreencheraslacunasondevocêtemquebras,eatribuiadescriçãocomo"erro" no caso de alguém tentar usar um número indefinido.

Na coluna C, digitei alguns números para mostrar como os valores funcionarão em relação aos intervalos. A fórmula para o resultado está na coluna D. Em D1:

=VLOOKUP(C1,A$1:B$7,2,1)

Isso procura o valor da coluna C na primeira coluna da tabela, procurando o valor mais alto que não exceda o valor de pesquisa. Leva o resultado da segunda coluna da tabela. O último parâmetro 1 indica que os valores estão em ordem crescente.

    

Answer 2

A abordagem

OK, com base no seu comentário, uma abordagem totalmente diferente que não envolve a reconstrução dos dados. Essa abordagem usa algumas colunas auxiliares para realizar o trabalho pesado em seus dados. Estes podem ser escondidos, embora eu os deixe mostrando no exemplo para que você possa ver como isso funciona.

Este exemplo é baseado na suposição de que todos os intervalos envolvem números de três dígitos. Caso contrário, precisaríamos analisar os pontos de extremidade do intervalo de outra forma, como localizar um dos espaços na frase de intervalo e, em seguida, calcular onde estão os valores de intervalo.

EuescolhiB7parainserirovalordepesquisaeC7paraexibiroresultadodapesquisa.AscolunasauxiliaressãoEeF.

Comofunciona

NacolunaE,procuramosverseovalordedestinoestánointervalodessalinha.Sefor,adescriçãodessalinhaéexibida.Casocontrário,acélulaficaráembranco.Portanto,nacolunaE,somentealinhanaqualoalvoéencontradomostraráadescrição.

AcolunaFconcatenasucessivamenteosresultadosdacolunaE.Comoissouneespaçosembrancoparaosintervalosnãocorrespondentesaqualquerdescriçãoexibida,aúltimacélulanacolunaFexibiráadescriçãodeondequerqueapareça.C7apenasexibeaúltimacélulanacolunaF.

Asfórmulas

Meusdadoscomeçamnalinha2,entãoE2contém:

=IF(AND(B$7>=--LEFT(B2,3),B$7<=--RIGHT(B2,3)),C2,"")

Copie essa linha conforme necessário. O AND compara o valor de destino (B7) com as duas extremidades do intervalo da linha. Ele usa as funções LEFT e RIGHT para extrair os três dígitos de cada extremidade da string. Os negativos duplos forçam o Excel a tratar essas cadeias numéricas como números reais. As aspas duplas vazias no final são um valor nulo que deixará a célula em branco se o teste for falso (as citações não devem ter nada entre elas).

F2 contém:

= F1 e E2

Copie essa linha conforme necessário. Note que isso depende de F1 estar vazio. Isso pega o que estiver na célula acima e concatena o que quer que seja o resultado na coluna E da linha.

A célula resultante, C7 contém:

= F5

Neste exemplo, F5 é o resultado na última linha de dados.

Como mencionado anteriormente, as colunas E e F podem estar ocultas, então mostrei a ação em duas colunas para facilitar a visualização. No entanto, E e F podem ser combinados e a ação realizada com uma coluna auxiliar. Por exemplo, faça F a coluna auxiliar e substitua a referência à célula E da coluna pela fórmula E da coluna. Então, deixe de fora a coluna E e F2 seria:

=F1&IF(AND(B$7>=--LEFT(B2,3),B$7<=--RIGHT(B2,3)),C2,"")

Nota

Com esta solução, se for inserido um número de destino que não esteja coberto por um dos intervalos definidos, ele não será encontrado em nenhuma linha de dados, portanto, o resultado ficará em branco.