Вопрос или проблема
| Имя | Имя | Часть | Вместимость | Влияние | Начало | Конец | Недели | Влияние (неделя) | Влияние (в день) |
|---|---|---|---|---|---|---|---|---|---|
| Джейн | Джейн | 4.45 | 4 | 19 Июл 24 | 25 Июл 24 | 1 | 4 | 0.6 | |
| Джевел | Джевел | T1 | 5.2 | 3 | 21 Июл 24 | 23 Июл 24 | 0.4 | 1 | |
| Плутон | Плутон | 7.7 | 7.7 | 30 Июл 24 | 6 Авг 24 | 1.1 | 7 | 0.96 | |
| Гранат | Гранат | 5 | 0.8 | 1 Авг 24 | 25 Авг 24 | 3.6 | 0.2 | 0.03 | |
| Сабина | Сабина | 11 | 3 | 3 Авг | 15 Авг 24 | 1.9 | 1.6 | 0.2 | |
| Дейзи | Дейзи | 3.5 | 0.2 | 10 Авг 24 | 1 Сен 24 | 3.1 | 0.06 | 0.01 | |
| Квини | Квини | P2 | 4 | 2 | 4 Сен 24 | 15 Сен 24 | 1.7 | 1.2 | 0.2 |
| Джейн | Джейн | 4.45 | 0.4 | 20 Июл 24 | 12 Авг 24 | 2 | 0.2 | 0.03 | |
| Плутон | Плутон | 7.7 | 3 | 5 Авг 24 | 10 Сен 24 | 5.1 | 0.6 | 0.08 |
Как я могу создать формулу, которая будет суммировать влияние события, когда у меня может быть несколько событий для переменной, и даты часто начинаются и заканчиваются в течение двух или более месяцев? В основном мне нужно суммировать влияние для ‘Джейн’ в июне, июле и т.д., что будет учитывать все записи, которые имеют влияние в течение календарного месяца.
Я пробовал две формулы Excel, которые не сработали:
=SUMIFS(N2:N14,B2:B14,A28,G2:G14,“>”&B26,H2:H14,“<”&C26)
Это просто вернуло ноль
=LET(sday,B26,eday,C26,proj,A27,arr,FILTER(B2:E14,A2:A14=proj),SUM(BYROW(arr,LAMBDA(arrb,DAYS(MIN(eday,INDEX(arrb,,2)),MAX(0,sday,INDEX(arrb,,1)))*INDEX(arrb,,4)))))
Это вернуло ошибку CALC
SUMIFS() должно работать. Попробуйте-
=SUMIFS(D:D,A:A,K2,E:E,">="&L2,F:F,"<="&M2)
Ответ или решение
Для подсчета воздействия событий в Excel, когда даты событий охватывают несколько месяцев, вам понадобится создать формулу, которая будет учитывать диапазоны дат. Это можно сделать с помощью функций SUMIFS или LET, которые позволяют обрабатывать условия для суммирования данных.
Шаги для подсчета воздействия событий
1. Структура данных
Предположим, у вас есть таблица с данными в следующем формате:
- Имя (A): Чей проект или дело.
- Начало (F): Дата начала события.
- Конец (G): Дата окончания события.
- Воздействие (E): Значение воздействия события.
2. Определите параметры
Убедитесь, что у вас есть вспомогательные ячейки для:
- Имени (например, K2)
- Начала месяца (например, L2)
- Конца месяца (например, M2)
3. Формула SUMIFS
Формула для вычисления суммарного воздействия событий ‘Jane’ в указанные рамки времени может выглядеть следующим образом:
=SUMIFS(E:E, A:A, K2, F:F, ">="&L2, G:G, "<="&M2)В этой формуле:
E:E– диапазон, который вы хотите суммировать (воздействие).A:A– диапазон с именами, например, ‘Jane’.F:F– диапазон с датами начала.G:G– диапазон с датами окончания.K2– ячейка с именем (например, ‘Jane’).L2– ячейка с началом месяца.M2– ячейка с концом месяца.
4. Как работает формула
Формула SUMIFS суммирует только те значения из столбца воздействия, которые соответствуют следующим критериям:
- Имя проекта соответствует указанному в K2.
- Дата начала больше или равна началу месяца.
- Дата конца меньше или равна концу месяца.
5. Обработка ошибок
Если вы столкнулись с ошибками, такими как предоставленный CALC, убедитесь, что:
- Даты указаны в правильном формате (даты должны быть числовыми значениями).
- Вы используете правильную ссылку на ячейки.
- Все диапазоны имеют одинаковую длину и формат.
6. Использование функции LET
Если вы хотите использовать более сложные формулы, вы можете использовать функцию LET для определения формул для использования в SUM:
=LET(
startDay, L2,
endDay, M2,
projName, K2,
filteredData, FILTER(A:F, A:A=projName),
impactSum, SUMPRODUCT((filteredData[1]>=startDay)*(filteredData[2]<=endDay)*filteredData[3]),
impactSum
)Заключение
Эти формулы позволяют эффективно суммировать значение воздействия в зависимости от заданного периода времени. Убедитесь, что диапазоны и формулы корректно настроены для избежания ошибок. С помощью такого подхода вы сможете получить точное представление о воздействии каждого события в установленный вами временной период, независимо от того, охватывают ли даты несколько месяцев.