Функция автозаполнения не требует правильного повторения чисел при использовании формулы AVERAGE () в Microsoft Excel
У меня есть столбец ежемесячных данных, который я хочу использовать для создания нового квартального столбца данных. Чтобы сделать это, я беру приращение данных за 3 месяца из столбца ежемесячных данных и использую на них формулу AVERAGE (). У меня есть что-то вроде;
СРЕДНЕЕ (D13: D15) Y1 (январь – март)
СРЕДНЕЕ (D16: D18) Y1 (апрель – июнь)
- Сделать ссылку в двух таблицах Excel, чтобы автоматизировать вычисление
- Найти и сохранить совпадения из 2 столбцов в Excel
- Удалить повторяющиеся строки?
- Excel автоматически заполняет столбец с приращением
- Как получить отдельные / уникальные значения в столбце Excel?
СРЕДНЕЕ (D19: D21) Y1 (июль – сентябрь)
СРЕДНЕЕ (D22: D24) Y1 (октябрь – декабрь)
Поскольку данные охватывают почти целый век, я хочу использовать функцию автозаполнения, чтобы сделать мою жизнь намного проще. К моему ужасу, Excel не распознает мою итерацию из трех ячеек за раз и вместо этого дает мне результаты вроде этого;
СРЕДНЕЕ (D13: D15) Y1 (январь – март)
СРЕДНЕЕ (D16: D18) Y1 (апрель – июнь)
СРЕДНЕЕ (D19: D21) Y1 (июль – сентябрь)
СРЕДНЕЕ (D22: D24) Y1 (октябрь – декабрь)
СРЕДНЕЕ (D17: D19) Y1 (май – июль)
СРЕДНЕЕ (D20: D22) Y1 (август – октябрь)
СРЕДНЕЕ (D23: D25) Y1 (ноябрь, декабрь) – Y2 (январь)
СРЕДНЕЕ (D26: D28) Y2 (февраль – апрель)
Скорее, чем:
СРЕДНЕЕ (D13: D15) Y1 (январь – март)
СРЕДНЕЕ (D16: D18) Y1 (апрель – июнь)
СРЕДНЕЕ (D19: D21) Y1 (июль – сентябрь)
СРЕДНЕЕ (D22: D24) Y1 (октябрь – декабрь)
СРЕДНЕЕ (D25: D27) Y2 (январь – март)
СРЕДНЕЕ (D28: D30) Y2 (апрель – июнь)
СРЕДНЕЕ (D31: D33) Y2 (июль – сентябрь)
СРЕДНЕЕ (D34: D36) Y2 (октябрь – декабрь)
Есть ли какое-либо обходное решение для этого или мне придется вручную конвертировать все мои ежемесячные данные в квартальные данные?
- Отключить автоформат в Excel 2010
- Транспонирование столбцов в строки в Microsoft Excel
- Найти самую последнюю дату ID с несколькими записями в листе Excel
- Как генерировать все возможные 3-значные комбинации чисел 0-6
- Рассчитать общее количество дней за определенный месяц
- Как сохранить файл Excel как CSV, не теряя длинный текст?
- Как сохранить часть таблицы, которая всегда видна во время навигации
- Отображение негативных меток времени в Excel 2007chart
Предположим, что ваша первая ежеквартальная ячейка (та, которая содержит =AVERAGE(D13:D15) ) – Q42 . Замените эту формулу с помощью =AVERAGE(OFFSET($D$13, 3*(ROW()-42), 0, 3, 1)) и перетащите / заполните это. Функция OFFSET позволяет вам обращаться к ячейкам без необходимости вводить их адреса буквально (например, D25 ); Вы можете сказать, по сути, 12-я ячейка вниз от ячейки D13 . Эта формула говорит,
- Возьмите текущий номер строки (
ROW()) и вычтите 42 (номер строки ячейкиQ42, где вы хотите получить среднее значение Y1 Q1). Очевидно, что это оценивается 0 в ячейкеQ42. Когда это перетаскивается в ячейкуQ43, она оценивается в 1 и т. Д. ВQ46(где вы хотите получить среднее значение Y2 Q1) вы получаете 4. - Умножьте на 3. Очевидно, это дает вам количество месяцев с начала (январь Y1).
- Начиная с
D13, спустите количество вычисляемых месяцев и перейдите в нулевые столбцы вправо. Затем возьмитеAVERAGEдиапазона, который состоит из трех рядов в высоту и одной колонки.
Автозаполнение будет только увеличивать количество ссылок на 1 строку за строку. Чтобы получить другой интервал, используйте функцию =row() с мультипликатором.
=ROW(A1)*3+10 оценивает число строк A1 * 3, плюс 10, т.е. 13, увеличивая на 3 строки
INDIRECT принимает текстовую строку и превращает ее в ссылку, поэтому
=AVERAGE(INDIRECT("D"&ROW(A1)*3+10&":D"&ROW(A1)*3+12))
Оценивает
average(indirect("D"&13&":D"&15)
Который далее
=AVERAGE(D13:D15)
И это будет выглядеть следующим образом
=AVERAGE(D13:D15) =AVERAGE(D16:D18) =AVERAGE(D19:D21) =AVERAGE(D22:D24) =AVERAGE(D25:D27) =AVERAGE(D28:D30) =AVERAGE(D31:D33) =AVERAGE(D34:D36) =AVERAGE(D37:D39) =AVERAGE(D40:D42) =AVERAGE(D43:D45) =AVERAGE(D46:D48) =AVERAGE(D49:D51)
и т.п.
В качестве бонуса следующая формула перетащит ваши ярлыки, хотя это можно сделать с помощью некоторой умной автозаполнения 2 ячеек (1, содержащей часть года и часть, содержащую месячную часть), соединенных вместе с & )
="Y"&ROUNDUP(ROW(A1)/4,0)&" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MOD(ROW(A1),4),1,"(January - March)"),2,"(April - June)"),3,"(July - September)"),0,"(October - December)")
Будет перетаскиваться как:
Y1 (January - March) Y1 (April - June) Y1 (July - September) Y1 (October - December) Y2 (January - March) Y2 (April - June) Y2 (July - September) Y2 (October - December) Y3 (January - March) Y3 (April - June) Y3 (July - September) Y3 (October - December) Y4 (January - March) Y4 (April - June) Y4 (July - September) Y4 (October - December) Y5 (January - March)
и т.п.