Для каждой строки вы хотите суммировать диапазон, который является подмножеством этой строки, определенной «Начальным месяцем» в столбце B. Давайте начнем с того, что возьмем ваше текстовое описание диапазона и выразим его формально.
Для этого нам нужно преобразовать названия месяцев в числа от 1 до 12.
Я не знаю способа сделать это напрямую, но легко извлечь номер месяца из даты, и мы можем превратить название месяца в дату, добавив число (день месяца).
Например,
- если
B2 March , то
B2 & "1" - 1 March1 , иMONTH(B2 & "1") равен 3 .- Если это не работает в вашей версии Excel, попробуйте
MONTH(DATEVALUE(B2 & "1")) .
Хорошо, вопрос просит суммировать месяцы с месяца № 3 по месяц №+3.
Например, для пользователя 2 (строка 3), где B3 - «апрель» (т. Е. 4), мы хотим получить сумму с января (1) по июль (7), поскольку 4−3 = 1 и 4+3 = 7 ,
Но есть одна загвоздка: не все необходимые семь месяцев гарантированно присутствуют на листе.
Для пользователя 1 (строка 2) B2 означает «март», поэтому мы хотим, чтобы с декабря (предыдущего года) по июнь.
Данные за предыдущий и следующий год недоступны, поэтому мы сокращаем наш диапазон с января по июнь.
Мы делаем это, заставляя начальный месяц быть не менее 1, а конечный месяц - не более 12.
Если мы сделаем это с помощью одной монолитной формулы, это будет практически нечитаемо.
Гораздо понятнее использовать «вспомогательные столбцы» для хранения промежуточных значений.
Итак, установите
P2 - номер месяца, соответствующий B2:
=MONTH(B2 & "1")Q2 - начальный месяц диапазона:
=MAX(P2-3, 1)R2 - конец месяца диапазона:
=MIN(P2+3, 12)
Выделение ячеек, которые мы выбрали, является хитрым трюком и может помочь нам убедиться в том, что мы делаем то, что хотим.
Установка условного формата с помощью формулы
=AND((COLUMN())-2>=$Q2, (COLUMN()-2)<=$R2)
на нашей матрице данных мы получаем это:
(Изображение является ссылкой на увеличенную копию самого себя.)
Хорошим инструментом для генерации динамических диапазонов в Excel является функция OFFSET :
OFFSET(reference, rows, cols, [height], [width])
который идентифицирует прямоугольную область ячеек (возможно, одну ячейку; т.е. прямоугольник 1 × 1; возможно, больше) по ее положению относительно некоторой другой ячейки.
Например, OFFSET(B2, 0, 1, , 6) определяет диапазон C2:H2 , потому что
- Из ячейки
B2 , спустившись на 0 строк и 1 столбец справа, вы попадете в C2 ,
- Высота по умолчанию равна 1, поэтому диапазон целиком находится в строке 2, и
- Ширина 6:
C2 , D2 , E2 , F2 , G2 и H2 , или C2:H2 для краткости, составляет шесть столбцов.
Ну, ширина диапазона - это просто номер конечного столбца минус номер начального столбца плюс 1.
Таким образом, результат, который вы хотите, это просто
=SUM(OFFSET(B2, 0, Q2, , R2-Q2+1))
И мы можем поместить это в столбец O:
Вот выше в текстовой форме, так что вы можете скопировать и вставить его:
Username StartMo January February March April May June July August September October November December The Answer
Andy March 102 202 402 1002 2002 4002 10002 20002 40002 100002 200002 400002 =SUM(OFFSET(B2,0,Q2,,R2-Q2+1)) =MONTH(B2&"1") =MAX(P2-3,1) =MIN(P2+3,12)
Bob April 103 203 403 1003 2003 4003 10003 20003 40003 100003 200003 400003 =SUM(OFFSET(B3,0,Q3,,R3-Q3+1)) =MONTH(B3&"1") =MAX(P3-3,1) =MIN(P3+3,12)
Charlie April 104 204 404 1004 2004 4004 10004 20004 40004 100004 200004 400004 =SUM(OFFSET(B4,0,Q4,,R4-Q4+1)) =MONTH(B4&"1") =MAX(P4-3,1) =MIN(P4+3,12)
Возможно, вам придется вставить его в Word в виде текста, а затем скопировать его и вставить в Excel.
