Espero que alguém possa responder minha pergunta!
Tenho muitos valores e preciso saber quais deles estão dentro de uma lista de intervalos e, em caso afirmativo, em qual intervalo eles estão.
Por exemplo, a planilha 1 contém meus valores de interesse na coluna A, enquanto a planilha 2 contém o valor inicial dos intervalos na coluna B e o valor final dos intervalos na coluna C e um nome para cada um desses intervalos na coluna D. Como eu encontraria descubra se um valor fornecido na planilha 1, coluna A, está entre qualquer intervalo descrito na planilha 2, coluna B, até a coluna C e retorne as informações correspondentes da coluna D. Essencialmente, descubra qual intervalo cobre o valor de interesse.
ps: encontrar um intervalo dentro de um intervalo seria ainda melhor se alguém soubesse como fazer isso, mas desisti disso há um tempo!
Responder1
Existem vários sites que discutem correspondências duplas e fórmulas semelhantes com mais detalhes e maneiras mais eficientes de fazer isso, mas aqui está um exemplo.
Suposições: 2 folhas
- Folha 1 = dados
- Coluna A = Furo
- Coluna B = Profundidade
- Coluna C = Valor calculado da planilha 2
- Folha 2 = intervalos
- Poço artesiano
- Profundidade de
- Profundidade até
- Valor (usado para preencher a planilha 1 coluna C)
a fórmula é uma fórmula de matriz, o que significa que precisa ser inserida usando ctrl+ shift+enter
=INDEX(ranges!$D$2:$D$5,MATCH(data!A2,IF(data!B2>ranges!$B$2:$B$5,IF(data!B2<ranges!$C$2:$C$5,ranges!$A$2:$A$5,""),""),0))
Não vou entrar em detalhes sobre como o índice e a correspondência funcionam, pois isso está em toda a web, mas no interior da fórmula de correspondência é isso que está acontecendo.
Os doisseAs funções verificam se a profundidade fornecida está entre cada profundidade de e profundidade até o valor. Se não corresponder a ambos os critérios, devolve um espaço em branco; se corresponder a ambos, devolve o ID do furo da coluna A.
A função match então tenta combinar seu ID de poço desejado com uma matriz que inclui alguns bons IDs e vários espaços em branco. Se corresponder ao ID do poço, esta é a linha que você procura e retorna esse valor para o índice que seleciona a célula de valor correspondente.
Para intervalos correspondentes, depende se os intervalos estão alinhados ou quais intervalos são mais longos, pois você pode estar tentando preencher um valor em um intervalo que atravessa vários compartimentos.