Учёт для тренера в Excel⁠⁠2

Приветствую Вас, читатели Пикабу!

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

К делу.

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

Недавно в разговоре с моим тренером узнала, что ей нужна для учета оплат и посещений табличка в excel, но у неё самой что-то не получается это сделать. Мне данная задача показалась интересной (она такой и была), и я приступила к её выполнению.

Сначала подумала над структурой.

Имеем:

- список клиентов (есть постоянные и разовые);

- несколько групп по разным направлениям;

- 4 типа абонементов (групповые, разовые групповые, индивидуальные), с параметрами по срокам действия и количеству занятий;

- скидки;

Что необходимо было реализовать:

- Учет клиентской базы;

- Учет оплат;

- Табель учета посещений + чтобы оплаченный период в нем подсвечивался цветом;

Процесс реализации (излагаю то, что помню).

1. Для учета клиентов и последующего вызова информации по ним в различных таблицах и формулах решено завести им УИН (уникальные идентификационные номера). Это облегчит поиск. Это было начало таблицы «Клиенты».

2. «Для подтягивания» информации по группам, типам абонементов, был создал лист «Справочники». В таблице «Группы» так же проставляются данные по дням недели, в которые проводятся занятия у группы. Это необходимо для форматирования внешнего вида табеля. Так же тут применена функция «Проверка данных» (чтобы в ячейку можно было внести только значения из списка).

3. Лист «Учет оплаты». Здесь в первой графе в ячейке выбирается УИН (через проверку данных), в ФИО через функцию ВПР подтягиваются данные ФИО. Дата проставляется вручную. Тип абонемента выбирается из списка (проверка данных) и с помощью ВПР подтягивает данные о количестве занятий и сроке действия абонемента в соответствующие ячейки.

4. На основе таблицы на листе «Учет оплаты» сделана сводная таблица (лист «Свод_оплата»)по оплате, с группировкой по месяцам. При желании таблицы и отчет можно дополнить типом оплаты – наличные либо безналичный перевод.

5. «Табель». Вот тут я попыхтела конечно. Если с помощью ВПР легко реализовала подтягивание информации по клиенту в графу ФИО, с помощью СЧЕТЕСЛИ графы сколько занятий посещено, сколько пропущено, то в процессе реализации подсвечивания оплаченного периода я повысила свой уровень знания экселя.

Тут хотела бы остановиться чуть подробнее.

Для подсвечивания актуального оплаченного периода необходимо выбирать последнюю оплату (максимальную дату) из таблицы по учету оплат. Я перерыла свою настольную книгу «Excel 2016 Библия пользователя». Потом я перерывала интернет. Находила аналогичные задачи, которые решали через формулы, при адаптации которых в моей книге ничего не получалось. Так я корпела дня два, в процессе которых нашла формулу для построения горизонтальной диаграммы Ганта через условное форматирование.

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

Попробовала реализовать – ПОЛУЧИЛОСЬ!!!!! Я запрыгала и затанцевала!

Я была счастлива!

Простое и элегантное решение было найдено!

Оставалось только его из сводной таблицы отправлять в табель. Тут в течение дня и докопалась до ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. В книге «Библия пользователя» данная функция описана весьма скудно, и благо, что интернет всегда под рукой и там полно умных людей, кто знает excel гораздо лучше меня.

После этого дело оставалось за малым – оформить книгу под N-е количество групп и сделать условное форматирование.

В итоге получился вот такой табель.

С чем еще предстоит разобраться.

Даты в табель выводятся вообще все подряд. Пришлось их группировать по 10 дней.

Хочу разобраться как на основании заданных даты 1го дня занятий в году и дней недели, по которым проходят занятия, создавать последовательность дат для табеля, чтобы эта «колбаса» уменьшилась в 2-3 раза. Если кто-то знает, как это можно сделать – напишите, пожалуйста, в комментариях. Буду очень благодарна!!!!

Ссылка на скачивание описанного в посте файла

P.S. Есть платные программы для ведения учета в спорте, но они заточены под фитнес-клубы (несколько тренеров, видов оплат, разные там онлайн кассы и личные кабинеты клиентов – это всё лишнее в данной ситуации).