Как подсчитать график смен на нескольких листах с помощью выпадающего списка в Excel?

Содержание

Вопрос или проблема
Ответ или решение
Решение задачи подсчета смен по нескольким листам в Excel
Шаги для реализации:
Заключение

Вопрос или проблема

Я создал простой график смен в Excel.

В нем есть выпадающий список, где мы можем выбрать имя, и я хотел бы суммировать общее количество смен из нескольких листов (каждый месяц).

Я следовал этому ответу с небольшой модификацией.

Это моя формула, поэтому я не уверен, почему она здесь не работает. Общая сумма для Ричарда должна быть только одна. Но показывает 30.

ИЗМЕНЕНИЕ 1: Я снова провел испытание и ошибку. Кажется, я нашел решение, но я не уверен, что это лучший способ сделать это, потому что это означает, что я должен добавить COUNTIF двенадцать раз, потому что я хочу суммировать после подсчета на каждом месячном листе

=SUM(COUNTIF(INDIRECT($H$4&"!$C$7:$G$17"),C4),COUNTIF(INDIRECT($H$5&"!$C$7:$G$17"),C4))

Это результат моего изменения 1

Есть ли способ это исправить?

Вам не нужно добавлять функцию COUNTIF 12 раз, потому что вы можете использовать нотацию с двоеточием:

=SUM(COUNTIF(INDIRECT($H$4:$H$5&"!$C$7:$G$17"),C4))

Вы не предоставили версию Excel. В старых версиях это требует использования Ctrl+Shift+Enter, в новых версиях достаточно Enter.
Если у вас старая версия, то используйте SUMPRODUCT вместо SUM, и Enter будет достаточно.
В новых версиях Excel это также может быть вычислено по-другому, без перечисления всех листов, достаточно крайних (первого и последнего).

Если вы хотите продолжать использовать один лист на месяц, вероятно, придется использовать INDIRECT. Это известно как ‘изменчивая’ формула, т.е. Excel трудно предсказать, когда ее нужно пересчитывать. Если вы используете много таких формул, это может замедлить вашу рабочую книгу. Но в данном случае, у вас, я полагаю, довольно легкая рабочая книга, так что это, наверное, не имеет значения.

Я бы использовал некоторые вспомогательные ячейки. Сделайте строку в верхней части вашей таблицы, состоящую из “ЯНВ”, “ФЕВ”, “МАР” и т.д. Затем используйте формулу INDIRECT ниже, чтобы вычислить общее количество смен для каждого работника в каждом месяце. Вы можете просто сделать одну такую формулу и заполнить ее по горизонтали. Наконец, используйте SUM, чтобы сложить все месяца. Таким образом, у вас будут гораздо более короткие формулы в каждой ячейке, что сделает ваш лист более легким в управлении. Вы всегда можете скрыть вспомогательные столбцы, если не хотите их показывать.

Ответ или решение

Создание расписания смен в Excel с использованием выпадающих списков и их подсчет с нескольких листов может быть задачей, требующей немного продуманного подхода. В данной ситуации важно правильно использовать функции Excel для достижения цели. Давайте разберем, как это можно сделать эффективно и оптимально.

Решение задачи подсчета смен по нескольким листам в Excel

При разработке расписания смен на нескольких листах (каждый для отдельного месяца) с суммированием смен для каждого сотрудника, необходимо решить несколько задач:

Определение исходных данных: Имеются несколько листов, каждый из которых представляет отдельный месяц. На каждом листе определена таблица расписания смен с выпадающими списками, где можно выбрать сотрудника.
Правильное использование формул: Формулы нужно настраивать таким образом, чтобы они корректно считали количество смен для каждого сотрудника по всем листам.

Шаги для реализации:

Использование функции INDIRECT:
- Функция INDIRECT позволит вам ссылаться на динамические диапазоны ячеек и учитывать данные с нескольких листов.
- Однако, как вы уже заметили, использование множества функций INDIRECT может замедлить работу большой таблицы, так как они относятся к ‘нестабильным’ формулам, что усложняет их обработку Excel.
Вспомогательные ячейки:
- Создайте строку с названиями месяцев (например, "ЯНВ", "ФЕВ", "МАР") в начале таблицы.
- Под каждой из них рассчитайте количество смен для каждого сотрудника за месяц, используя более простые формулы. Это можно сделать с помощью INDIRECT для каждого отдельного месяца и сотрудника:
```
=COUNTIF(INDIRECT("'" & A1 & "'!$C$7:$G$17"), C4)
```
- Здесь A1 содержит название листа (например, "ЯНВ").
Суммирование данных:
- После расчета количества смен для каждого месяца используйте функцию SUM для суммирования значений по всем месяцам:
```
=SUM(B2:M2)
```
- В этой формуле B2:M2 – диапазон ячеек, где посчитано количество смен за каждый месяц.
Оптимизация производительности:
- Возможно использование SUMPRODUCT вместо SUM совместно с INDIRECT, если версия Excel поддерживает такой подход и позволяет отказаться от использования массивов (Ctrl+Shift+Enter).
- В новых версиях Excel можно также программировать на языке Excel, используя Lambda-функции, чтобы упростить комплексные вычисления.
Упрощение и удобство работы:
- Вспомогательные ячейки можно скрыть, чтобы они не загромождали рабочую область.
- Структурирование данных должно быть интуитивно понятным и легко поддерживаемым.

Заключение

Правильная организация таблицы с использованием вспомогательных ячеек и осмысленных формул с учетом производительности – это ключ к созданию эффективного и быстродействующего Excel-листа. Следуя вышеописанным шагам, вы сможете создать простую и надежную систему учета смен для сотрудников на каждом месяце.