Сравнивая два диапазона чисел и скажем, перекрывает ли первое второе

Question

Я новичок в Excel, и мне нужно использовать его для научной работы. Я пытаюсь оптимизировать рабочий процесс сбора данных в базе данных.

Мой файл http://cid-1662ba2be7d0ee4e.skydrive.live.com/redir.aspx?resid=1662BA2BE7D0EE4E!144

Мне нужно сравнить диапазон, созданный столбцами "Начало" и "Конец", с различными диапазонами внутри желтого квадрата и вывода в столбце "Совместная локализация", если диапазон Начального конца перекрывает только первое значение (Да - Начало), только второе значение (Да - Конец), перекрывает оба (Да - оба) или просто содержится (Да - Содержится). Если ни одно из условий не выполнено, просто выведите "Нет".

Это слишком сложно для Excel?

Спасибо!

Кстати, я пометил этот вопрос Excel 2010, потому что это то, что я использую дома, но если вы знаете, как сделать это в более ранней версии, я бы с радостью принял этот ответ тоже.

Answer 1

Вот решение:

Сначала небольшое изменение макета вашего листа (значения в диапазоне F2:G11)

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

Добавить 4 промежуточных столбца результата (я использовал I, J, K, L)

добавить формулы

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

Скопируйте их для всех строк в ваших данных

Формула совместной локализации ячейки

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"))))

Чтобы справиться с дополнительными критериями «Да - Средний», добавьте еще один критерий в формулу для проверки на охват диапазона от / до

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")))))