Excel 2013
Eu tenho um grande conjunto de saída do modelo (215K linhas) A coluna A é a data juliana em incrementos de aproximadamente 2 horas, a coluna B é profundidade em profundidades aproximadas de 0,6 m, mas com profundidade variável inicial. (Poderia ser 0.1, 0.7, 1.3... ou poderia seja 0.05, .65, 1.25.... ou poderia começar com um valor pequeno diferente)
Os dados são agrupados com data em ordem crescente e, em seguida, com profundidade em ordem crescente. Para cada data, há sempre o mesmo número de profundidades, mas seus valores precisos variam ligeiramente. Eu quero comparar as linhas para cada data que estão mais próximas da minha data de calibração, que foram coletadas nas profundidades de 1,5, 3, 6, 12, 18 e 55.
Um pensamento é criar a coluna C, que indica qual linha contém um valor no campo de profundidade mais próximo de uma das várias profundidades principais entre todas as medições na mesma data.
Para essa explicação, imagine que eu queira encontrar as linhas com profundidades mais próximas de 1,5 e 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]
Eu tenho uma fórmula que funciona. Aqui está uma versão que identificaria a profundidade mais próxima de 1.5: (versão para a linha 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, "")
Basicamente, se a profundidade de uma determinada linha for menor que 1 de 1,5, e estiver mais próxima de 1,5 do que a linha anterior ou seguinte, obteremos um resultado. Funciona bem, mas se eu configurar o aninhamento para escolher as outras profundidades de tecla, acabo com uma fórmula muito longa:
=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,"")))))))
Esta fórmula funciona, mas é muito feia e um pouco lenta para computar. Estou postando porque me pergunto se alguém tem uma ideia para uma abordagem mais elegante e / ou mais eficiente.