В Excel, как мне получить доступ к {значению в столбце B} той же строки, чье {значение в столбце A} равно {{значению в строке x столбца A} минус 365}?

Question

В Excel, как мне получить доступ к {значению в столбце B} той же строки, чье {значение в столбце A} равно {{значению в строке x столбца A} минус 365}? Мое намерение состоит в том, чтобы рассчитать среднюю (годовую) эффективность использования топлива автомобиля за предыдущие 12 месяцев, используя неравномерно распределенные данные о расходе топлива и пробеге.

В столбце A содержатся возрастающие даты с большими разрывами в датах между соседними ячейками. Col B содержит соответствующие показания одометра, а Col C - соответствующее топливо, которое было приобретено в этот день.

Начиная, по крайней мере, через год после первой записи, я хочу вычислить текущее среднегодовое значение для миль на галлон и поместить его, скажем, в столбец D строки x. Строка x соответствует дате, по крайней мере, через год после первой записи.

Числителем будет разница между содержимым ячейки Bx и соответствующим значением, находящимся в ячейке столбца B, строка которого соответствует дате (с соответствующим округлением) в столбце A, которая раньше на 365 дней от даты в строке x Полковник А.

Знаменатель будет суммой содержимого ячеек из ячейки Col C, чья строка соответствует дате (то же самое подходящее округление, что и выше) в Col A, что на 365 дней от даты в строке x Col A до ячейки Cx.

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

Answer 1

Я сделал некоторые предположения о том, как могут выглядеть данные, поэтому может потребоваться некоторая подстройка в зависимости от того, насколько нерегулярным является ваше "нерегулярное расстояние".

Я предположил, что есть строка заголовка, поэтому первый раз, когда вы можете получить данные за 365 дней, это строка 366. Я предположил, что есть ряд для каждого дня, без каких-либо пробелов. Если дни не являются последовательными без пробелов, формула принимает совершенно новый уровень уродливости, чтобы вычислить 365 дней до текущего дня, а затем перейти к поиску этой даты или ближайшей даты после, просто чтобы установить верхний предел диапазон, на котором нужно делать SUMS.

Я также предположил, что Col A - это дата, Col B - галлоны, использованные в тот день, а Col C - это мили, пройденные в тот день. В столбцах B и C могут быть пробелы, но это не должно повлиять на мой ответ.

В ячейке D366 вы бы получили формулу:

=SUM(OFFSET(D366,-364,-1,365,1))/SUM(OFFSET(D366,-364,-2,365,1))

Это даст вам сумму миль из предыдущих 364 строк (плюс мили дней), один столбец слева, деленный на сумму галлонов из предыдущих 364 строк (плюс галлы дней), два столбца слева.