Comparando dois intervalos de números e dizer se o primeiro se sobrepõe ao segundo

Question

Sou novo no Excel e preciso usá-lo para um artigo científico. Estou tentando otimizar o fluxo de trabalho de coleta de dados do banco de dados.

Meu arquivo link

Eu preciso comparar o intervalo criado pelas colunas "Start" e "End" para os diferentes intervalos dentro do quadrado amarelo e saída, na coluna "co-localização", se o intervalo Start-End sobrepõe apenas o primeiro value (Yes - Start), apenas o segundo valor (Yes - End), sobrepõe ambos (Yes - both) ou é apenas contido (Yes - Contained). Se nenhuma das condições for satisfeita, apenas imprima "Não".

Isso é muito complexo para o Excel?

Obrigado!

btw: Eu marquei essa pergunta no Excel 2010, porque é isso que eu uso em casa, mas se você souber como fazer isso em uma versão anterior eu aceitaria com prazer essa resposta também.

    

Answer 1

Aqui uma solução:

Primeiro, uma pequena alteração no layout da folha (valores no intervalo F2: G11)

From            To  
                    0  
        0    27800000  
 34400000    46500000  
 51000000   102000000  
107000000   117600000  
120700000   153300000  
154800000   163800000  
171200000   174300000  
184000000   247200000  
999999999   999999999

Adicione 4 colunas de resultados intermediários (usei I, J, K, L)

adicione fórmulas

I2    =MATCH(A2,$F$2:$F$11,1)      
J2    =MATCH(A2,$G$2:$G$11,1)+1
K2    =MATCH(B2,$F$2:$F$11,1)
L2    =MATCH(B2,$G$2:$G$11,1)+1

Copie-os para todas as linhas nos seus dados

Célula de co-localização de fórmula

D2   =IF(AND(I2=J2,I2=K2,I2=L2),"Yes - Contained",IF(AND(I2=J2,K2=L2),"Yes - Both",IF(I2=J2,"Yes - Start",IF(K2=L2,"Yes - End","No"))))

Para lidar com os critérios adicionais para "Sim - Médio", adicione outro critério à fórmula para testar a abrangência de um intervalo de / para

D2 =IF(AND(I2=J2,I2=K2,I2=L2),"Yes - Contained",IF(AND(I2=J2,K2=L2),"Yes - Both",IF(I2=J2,"Yes - Start",IF(K2=L2,"Yes - End",IF(AND(I2+1=J2,K2+1=L2,I2<K2),"Yes - Middle","No")))))