Вопрос или проблема
Я застрял на написании этой формулы.
В основном, я пытаюсь сложить общее количество пропусков, которые студент будет иметь по часам.
Так что если студент (A) находится на 6-м часу (B), их пропуски (C) делятся на “Неосвобожденные” или “Пропуск с контактом родителей.” Оба этих пункта считаются пропуском, и количество пропущенных дней для каждого типа пропуска указано в столбце (F).
Я пытаюсь написать формулу, которая будет определять, если JOHN DOE (A) пропустил 3 дня, а затем 2 дня (F) на 6-м часу (B), то она покажет мне, что всего 5 дней…как показано в примере в столбце K. Затем я напишу аналогичную формулу для часов 5, 4, 3, 2, 1.
Вы можете увидеть некоторые образцы данных ниже:
Я написал формулу, которая будет СЧИТАТЬ количество раз их отсутствия на 6-м часу (как показано в столбце I)
=COUNTIFS($B$3:$B$1801,B3,$A$3:$A$1801,A3,$B$3:$B$1801,"6")
Я просто не могу понять, как сложить два числа в F к этой формуле.
Единственная дополнительная проблема заключается в том, что она должна начинаться снова с следующего студента в списке. Так что она должна затем показывать, что SUE SMITH имела 4 пропуска на 6-м часу, как показано в столбце K.
Я думал, что могу быть на правильном пути с формулой sumifs. Это формула, которую я попробовал в столбце J, но я получаю только 0.
=SUMIFS($F$3:$F$1801,$B$3:$B$1801,B3,$A$3:$A$1801,A3) + SUMIFS($F$3:$F$1801,$B$3:$B$1801,"6")
Возможно, есть более простой способ сделать это, но я как бы застрял в одном способе мышления и не могу разобраться, я был бы признателен за любую помощь.
Как прокомментировал cybernetic.nomad, убедитесь, что значения, введенные за дни, являются числами (а не текстом) – тогда ваша формула будет работать как ожидалось.
Вот альтернатива для получения количества для всех часов с помощью одной формулы (с Excel для Microsoft 365):
Введите в K2
=LET(
d, A2:F14,
name, INDEX(d, , 1),
hour_, INDEX(d, , 2),
days_, INDEX(d, , 6),
for_hours, K1:P1,
absences_for_hour, LAMBDA(acc, h,
LET(
h, --SUBSTITUTE(h, "Hour ", ""),
absences, IF(
hour_ = h,
SUMIFS(days_, name, name, hour_, h),
""
),
HSTACK(acc, absences)
)
),
DROP(REDUCE("Пропуски по часам", for_hours, absences_for_hour), , 1)
)
Ответ или решение
Для решения поставленной задачи необходимо использовать формулу, которая будет вычислять сумму отсутствий не только по конкретному ученику, но и по указанному часу. Данная ситуация требует использования функций SUMIFS, которая позволяет суммировать значения по нескольким критериям. Давайте рассмотрим пошагово, как можно добиться желаемого результата.
Шаги для создания формулы в Excel
-
Определите ваши данные: Предположим, у вас есть следующая структура данных:
- Столбец A: Имя ученика
- Столбец B: Час
- Столбец F: Количество дней отсутствия
-
Создание формулы:
Чтобы подсчитать общее количество отсутствий для учеников по заданному часу, мы будем использовать функцииSUMIFS. Эта функция будет суммировать значения в столбце F, если имя ученика в столбце A совпадает с заданным именем, а также если час из столбца B равен указанному. -
Пример формулы:
Для подсчета общего количества отсутствий ученика по имени "JOHN DOE" в часе 6, формула будет выглядеть следующим образом:=SUMIFS($F$3:$F$1801, $A$3:$A$1801, "JOHN DOE", $B$3:$B$1801, 6)Объяснение:
$F$3:$F$1801: диапазон, в котором находятся данные о количестве отсутствий.$A$3:$A$1801: диапазон, в котором находятся имена учеников."JOHN DOE": конкретное имя ученика, суммирование будет производиться только для данного имени.$B$3:$B$1801: диапазон для проверки часов.6: конкретный час, присутствие которого необходимо учитывать.
-
Копирование формулы для других учеников и часов:
Если вам нужно рассчитывать отсутствия для других учеников или для других часов, просто измените параметры в формуле. Вы также можете использовать ссылки на ячейки для имени ученика и часа, чтобы формула работала динамически, например:=SUMIFS($F$3:$F$1801, $A$3:$A$1801, A3, $B$3:$B$1801, B3)Здесь A3 и B3 содержат имя ученика и час соответственно, формула автоматически будет подставлять значения из указанных ячеек.
Убедитесь в корректности данных
Перед применением формул убедитесь, что:
- Значения в столбце F являются числами, а не текстом, чтобы формулы работали корректно.
- В диапазонах (например, A3:F1801) содержатся все необходимые данные без пропусков.
Заключение
Таким образом, применяя метод SUMIFS, вы сможете легко подсчитать общее количество отсутствий для каждого ученика по конкретному часу. Не забывайте периодически проверять формулы и диапазоны, особенно при добавлении новых данных или изменений в структуре таблицы. Надеюсь, данное объяснение поможет вам решить вашу задачу, и вы сможете успешно вывести необходимые результаты в Excel.