Excel 2013
У меня большой набор выходных данных модели (215 тыс. Строк). Столбец A - это юлианская дата с шагом примерно в 2 часа, столбец B - это глубина приблизительно на 0,6 м глубины, но с переменной начальной глубиной ((Может быть 0.1, 0.7, 1.3... или может быть 0.05, .65, 1.25.... или может начинаться с другого небольшого значения)
Данные сгруппированы по дате в порядке возрастания, а затем по глубине в порядке возрастания. Для каждой даты всегда одинаковое количество глубин, но их точные значения немного различаются. Я хочу сравнить строки для каждой даты, которые являются ближайшими к моей дате калибровки, которые были собраны на глубинах 1,5, 3, 6, 12, 18 и 55.
Одна мысль состоит в том, чтобы создать столбец C, который указывает, какая строка содержит значение в поле глубины, ближайшем к одной из нескольких ключевых глубин среди всех измерений в одну и ту же дату.
Для этого объяснения представьте, что я хочу найти строки с глубиной, близкой к 1,5 и 3:
ColA ColB ColC
70.001 0.322 -
70.001 0.949 -
70.001 1.559 1.5 <--Selected because 1.559 is closer to 1.5 than is any other depth with date=70.001
70.001 2.169 -
70.001 2.779 3 <--Selected because 1.2.779 is closer to 3 than is any other depth with date=70.001
70.001 3.389 -
70.001 3.999 -
[54 rows trimmed]
70.084 0.443 -
70.084 1.191 -
70.084 1.801 1.5
70.084 2.411 -
70.084 3.021 3
70.084 3.631 -
70.084 4.241 -
70.084 4.851 -
[54 rows trimmed]
70.167 0.382 -
70.167 1.069 -
70.167 1.679 1.5
70.167 2.289 -
70.167 2.899 3
70.167 3.509 -
70.167 4.119 -
70.167 4.729 -
[54 rows trimmed]
70.25 0.485 -
70.25 1.276 1.5
70.25 1.886 -
70.25 2.496 -
70.25 3.106 3
70.25 3.716 -
70.25 4.326 -
70.25 4.936 -
[215200 rows trimmed]
У меня есть формула, которая работает. Вот версия, которая будет просто определять глубину, ближайшую к 1,5: (версия для строки 6)IF(AND(ABS(B6-1.5)<1,ABS(B6-1.5)<ABS(B7-1.5),ABS(B6-1.5)<ABS(B5-1.5)),1.5, "")
По сути, если глубина данной строки меньше 1 от 1,5, а она ближе к 1,5, чем предыдущая или следующая строка, мы получаем удар. Это работает нормально, но если я настроил вложение, чтобы выбрать другие ключевые глубины, я получу очень длинную формулу:
=IF(AND(ABS(B6-1.5)<1,ABS(B6-1.5)<ABS(B7-1.5),ABS(B6-1.5)<ABS(B5-1.5)),1.5, IF(AND(ABS(B6-3)<1,ABS(B6-3)<ABS(B7-3),ABS(B6-3)<ABS(B5-3)),3, IF(AND(ABS(B6-6)<1,ABS(B6-6)<ABS(B7-6),ABS(B6-6)<ABS(B5-6)),6, IF(AND(ABS(B6-12)<1,ABS(B6-12)<ABS(B7-12),ABS(B6-12)<ABS(B5-12)),12, IF(AND(ABS(B6-18)<1,ABS(B6-18)<ABS(B7-18),ABS(B6-18)<ABS(B5-18)),18, IF(AND(ABS(B6-30)<1,ABS(B6-30)<ABS(B7-30),ABS(B6-30)<ABS(B5-30)),30, IF(AND(ABS(B6-55)<1,ABS(B6-55)<ABS(B7-55),ABS(B6-55)<ABS(B5-55)),55,"")))))))
Эта формула работает, но она очень уродливая и немного медленная для вычисления. Я пишу, потому что мне интересно, есть ли у кого-нибудь идея для более элегантного и / или более эффективного подхода.