Как создать формулу для вычисления суммы часов в Excel по критериям
Введение в работу с формулами в Excel может быть долгим, но это знание очень полезно в работе с данными. В этой статье мы рассмотрим, как создать формулу для подсчета суммы часов, соответствующих определенным критериям, включая день и код.
Задача: Суммировать часы по дням и кодам
Допустим, у вас есть таблица с четырьмя столбцами: "День", "Треска", "Часы" и "Беговая сумма (часы)". Вам необходимо разработать формулу, которая будет отображать общую сумму часов для каждого дня, если текущая строка является последней в своем разделе. Это может быть полезно для учета рабочего времени или ведения отчетности.
Ожидаемый результат
Рассмотрим пример. При правильной настройке формул, вы можете получить ожидаемый результат, в котором каждая сумма часов будет отображаться только в последнем ряду, соответствующем дню и коду.
Основная формула: SUMIFS
Ваша исходная формула была:
=+SUMIFS([Hours], [DÍA], [@DÍA], [COD.], [@[COD.]])Эта формула использует SUMIFS, чтобы сложить часы, соответствующие конкретным условиям (дню и коду). Однако для решения задачи ей может не хватить возможностей.
Два подходящих варианта решения
Для получения нужного результата вы можете использовать один из следующих методов:
Вариант 1: Вспомогательный столбец
Добавьте вспомогательный столбец, который будет включать номера строк. В этом случае вы сможете использовать следующую формулу:
=SUM(FILTER([hours], ([row] <= [@row]) * ([day] = [@day])))Эта формула фильтрует часы на основании условий и суммирует их в случае выполнения критериев.
Вариант 2: Смешивание относительных и абсолютных ссылок
Если вы хотите обойтись без вспомогательного столбца, используйте следующую формулу:
=SUM(FILTER($C$2:C2, $B$2:B2 = [@day]))Эта формула включает абсолютные ссылки на диапазоны, что позволяет ей выполнять подсчеты до текущей строки.
Дополнительные варианты с использованием LET
Если вы знакомы с функцией LET, вы можете написать более компактную формулу:
=LET(
a, [@DIA],
b, INDEX([DIA], 1):a,
c, [@[COD.]],
d, INDEX([COD.], 1):c,
IF(COUNTIFS(b, a, d, c) = COUNTIFS([DIA], a, [COD.], c),
SUMIFS(INDEX([Hours], 1):[@Hours], b, a, d, c), "")
)Эта конструкция позволяет задавать переменные для упрощения обращения к диапазонам.
Итоги
Правильная работа с формулами в Excel может значительно упростить обработку данных. Используя указанные выше методы – как с вспомогательными столбцами, так и с LET – вы сможете корректно высчитывать суммы часов по дням и кодам. Если у вас возникли сложности, не стесняйтесь обращаться за помощью к сообществу или искать информацию в ресурсах, посвященных Excel.
