Как создать посещаемости Tracker в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: excel-tools

Зачем покупать дорогостоящий инструмент управления посещаемостью для своего стартапа, если вы можете отслеживать посещаемость команды в Excel? Да! Вы можете легко создать трекер посещаемости в Excel. В этой статье мы узнаем, как это сделать.

Шаг 1: Создайте 12 листов на каждый месяц в книге

Если вы планируете отслеживать посещаемость в течение года, вам нужно будет создавать ежемесячный лист в Excel.

Шаг 2: Добавьте столбцы для каждой даты в листе каждого месяца.

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

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

=TEXT(date,»ddd»)

Вы можете прочитать об этом link: / excel-date-and-time-get-day-name-from-date-in-excel [здесь].

Отформатируйте выходные и праздничные дни в темном цвете и заполните их фиксированными значениями, такими как Weekend / Holiday, как показано на изображении ниже.

Сделайте то же самое для каждого листа.

Шаг 3. Исправьте возможные входные данные, используя проверку данных для каждой открытой ячейки. Теперь каждый может указать свое присутствие в таблице, но может вводить произвольный текст. Некоторые могут писать P для настоящего, или Present, или per и т. Д. Единообразие данных является обязательным в любой системе управления посещаемостью. Чтобы позволить пользователям писать только P или A для настоящего и отсутствующего соответственно, мы можем использовать проверку данных.

Выберите любую ячейку, перейдите к данным на ленте и нажмите «Проверка данных».

Выберите список из вариантов и напишите A, P в текстовом поле.

Нажмите ОК. Скопируйте эту проверку для всего открытого диапазона данных (открытый диапазон означает ячейку, в которую пользователь может вставлять значения).

Шаг 3: Заблокируйте все ячейки, кроме тех, где необходимо ввести посещаемость.

Выберите дату в столбце даты. Например, выберите 1 января. Щёлкните прямо сейчас по выбранному диапазону и перейдите к форматированию ячеек. Перейти к защите.

Снимите флажок заблокирован. Нажмите ОК. Теперь скопируйте этот диапазон во все открытые диапазоны дат.

Это позволит войти в эти ячейки только тогда, когда мы защитим рабочие листы с помощью меню защиты рабочих листов. Таким образом, ваши формулы и параметры останутся нетронутыми, и пользователи смогут только изменять свою посещаемость.

Шаг 4: Расчет настоящих дней товарищей по команде Итак, как вы рассчитываете настоящие дни? Ну у каждого свои формулы для расчета посещаемости. Я расскажу о своем здесь. Вы можете внести изменения в соответствии с требованиями вашей ведомости посещаемости.

Я считаю общее количество текущих дней как общее количество дней в месяце за вычетом количества дней отсутствия. Это позволит контролировать праздники и выходные. Они автоматически засчитываются как рабочие дни.

Таким образом, формула Excel для подсчета сегодняшних дней будет выглядеть так:

=COUNT(dates)-COUNTIF(attendance_range,

«A»)

По умолчанию все будут присутствовать в течение всего месяца, пока вы не отметите их отсутствующими на листе.

В этом примере формула:

=COUNT($C$2:$AG$2)-COUNTIF(C3:AG3,»A»)

Я записал эту формулу в ячейку B3, а затем скопировал ее. Вы можете видеть, что 27 дней показаны как подарок. Хотя я заполнил не все ячейки присутствия. Вы можете оставить это так, если хотите, чтобы они присутствовали по умолчанию. Или, если вы хотите, чтобы они по умолчанию отсутствовали, отметьте все ячейки как отсутствующие. При этом в текущем расчете будет учитываться только текущий счет.

Шаг 5: Защитите лист

Теперь, когда мы сделали все на этом листе. Давайте защитим его, чтобы никто не мог изменить формулу или форматирование на листе.

Перейдите на вкладку обзора на ленте. Найдите меню «Защитить лист». Нажмите здесь. Откроется диалоговое окно, в котором будет запрашиваться разрешения, которые вы хотите предоставить пользователям. Отметьте все разрешения, которые вы хотите разрешить. Я только хочу, чтобы пользователь мог заполнить посещаемость ничем другим. Так что я оставлю все как есть.

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

Теперь, если вы попытаетесь изменить ячейки непосещаемости, Excel не позволит вам это сделать. Однако вы можете изменить ячейки посещаемости, так как мы их сняли.

Шаг 6: Выполните описанную выше процедуру для всех листов месяца

Сделайте то же самое для каждого месяца. Лучше всего скопировать тот же лист и сделать из него 12 листов. Снимите с них защиту и внесите необходимые изменения, а затем снова защитите их.

Подготовка основного листа посещаемости. Хотя у нас есть все листы, готовые к использованию для заполнения посещаемости, у нас нет единого места, чтобы контролировать их все.

Администрация хотела бы видеть всю посещаемость в одном месте, а не на разных листах. Нам необходимо создать основной лист посещаемости.

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

Шаг 7: Просмотр посещаемости команды по ежемесячному листу

Чтобы узнать посещаемость по таблице, у нас может быть простая формула ВПР, но тогда нам придется делать это по 12 раз для каждого листа. Но вы знаете, что у нас может быть одна формула для поиска на нескольких листах.

Используйте эту формулу в ячейке C3 и скопируйте на остальных листах.

=VLOOKUP($A3,INDIRECT(C$2&»!$A$3:$B$12″),2,0)

Поскольку мы знаем, что общая посещаемость всех листов находится в диапазоне от B3 до B12, мы используем функцию ДВССЫЛ, чтобы получить значения из нескольких листов. Когда вы копируете эту формулу вправо, она ищет значения в таблицах за февраль.

_Предостережение: убедитесь, что имена листов и заголовки столбцов в главном файле совпадают, иначе эта формула не будет работать. _

Шаг 8: Используйте функцию Sum, чтобы получить все текущие дни года товарища по команде. Это необязательно. Если хотите, вы можете рассчитать общее количество дней ваших сотрудников в течение года, просто используя формулу суммы.

Вот и все. У нас есть готовая система управления посещаемостью Excel.

Вы можете изменить это в соответствии с вашими требованиями. Используйте его для расчета заработной платы, начисления поощрений или чего-нибудь еще. Этот инструмент вас не подведет. Вы можете внести изменения для расчета праздников и выходных отдельно на каждом листе. Затем вычтите их из общего количества текущих дней, чтобы рассчитать общее количество рабочих дней. Вы также можете включить L для отпуска в раскрывающемся списке, чтобы отметить отпуск сотрудников.

Так что да, ребята, вот как вы можете создать превосходную систему управления посещаемостью для своего стартапа. Это дешево и очень гибко. Я надеюсь, что это руководство поможет вам в создании вашей собственной учебной книги по посещаемости. Если у вас есть какие-либо вопросы, дайте мне знать в разделе комментариев ниже.

Статьи по теме:

link: / lookup-formulas-lookup-from-variable-tables-using-косвенный [Поиск из таблиц переменных с помощью ДВССЫЛ]: Для поиска из табличной переменной в Excel мы можем использовать функцию ДВССЫЛ. Функция ДВССЫЛ берет текстовый диапазон и преобразует его в фактический диапазон посещаемости.

link: / lookup-formulas-use-index-and-match-to-lookup-value [Используйте INDEX и MATCH для поиска значения]: * Формула INDEX-MATCH используется для динамического и точного поиска значения в данная таблица. Это альтернатива функции VLOOKUP, которая устраняет недостатки функции VLOOKUP.

link: / lookup-formulas-use-vlookup-from-two-or-more-lookup-tables [Использовать ВПР из двух или более таблиц поиска] | Для поиска из нескольких таблиц мы можем использовать подход ЕСЛИОШИБКА. При поиске из нескольких таблиц ошибка используется как переключатель для следующей таблицы. Другой метод может быть подходом If.

link: / lookup-Formulas-how-to-do-case-sensitive-lookup-in-excel [Как делать поиск с учетом регистра в Excel] | функция ВПР в Excel не чувствительна к регистру и возвращает первое совпадающее значение из списка. INDEX-MATCH не исключение, но его можно изменить, чтобы сделать его чувствительным к регистру. Давайте посмотрим, как… link: / lookup-formulas-lookup-часто-появляющийся-текст-с-критериями-в-excel [Поиск часто появляющегося текста с критериями в Excel] | Поиск наиболее часто появляется в тексте в диапазоне, который мы используем ИНДЕКС-ПОИСКПОЗ с функцией РЕЖИМ. Вот способ.