Добрый день, уважаемый читатель. В данной статье мне бы хотелось рассмотреть один из вариантов ведения журнала тренировок в Excel с последующей визуализацией этой информации. Готовый файл для ознакомления.
Будут рассмотрены и применены следующие формулы:
СЛУЧМЕЖДУ(«Нижняя граница»; «Верхняя граница»).
СЦЕПИТЬ(«Текст1»; «Текст2»).
ВРЕМЗНАЧ(«Время как текст»).
ТЕКСТ(«Значение»; «Формат»).
На первом этапе необходимо подготовить набор истории тренировок для понимания какую полезную информацию можно будет получить из регулярного ведения журнала. А так же для проверки правильности работы всех формул. Вкладка будет называться «ЖурналТренировок»
Я решил подготовить историю с 1 января 2019 года. Для этого в ячейке A3 указываем 01.01.2019. В ячейке снизу (A4) прописываем формулу для автоматического увеличения даты на случайное значение дней от 1 до 5.
=A3+СЛУЧМЕЖДУ(1;5)
Примеры функции:
СЛУЧМЕЖДУ(1;10) --> Результат случайное целое число из диапазона от 1 до 10
СЛУЧМЕЖДУ(10;100) --> Результат случайное целое число из диапазона от 10 до 100
Далее заполняем вниз (черный крестик в правом нижнем углу ячейки) столбец "Дата" до требуемого окончания истории. У меня это 314 строка, получается второе полугодие 2021 года.
Столбец "Время начала" тоже будем заполнять случайными значениями из диапазона 16:00 до 22:59. Для этого в ячейке B3 укажем следующую формулу (с последующим заполнением вниз):
=ВРЕМЗНАЧ(СЦЕПИТЬ(СЛУЧМЕЖДУ(16;22);":";СЛУЧМЕЖДУ(0;59);":00"))
Примеры функции:
ВРЕМЗНАЧ("10:01") --> 10:01:00. В формате Excel это число 0,417361
Столбец "Время окончания" будет брать время начала тренировки и прибавлять к нему случайное значение от 10 минут до 1 часа 59 минут. Для этого в ячейке C3 укажем следующую формулу (с последующим заполнением вниз):
=B3+ВРЕМЗНАЧ(СЦЕПИТЬ("0";СЛУЧМЕЖДУ(0;1);":";СЛУЧМЕЖДУ(10;59);":00"))
Столбец "Оценка тренировки" будет содержать целое числовое значение от 1 до 5, соответствующее субъективной оценке качества тренировки. Для этого в ячейке D3 укажем следующую формулу (с последующим заполнением вниз):
=СЛУЧМЕЖДУ(1;5)
В реальном журнале нам нужно будет заполнять эти столбцы (A, B, C, D) вручную после каждой тренировки.
Последующие столбцы преобразуют введённую информацию для анализа и визуализации статистики.
Столбец "Продолжительность, мин" будет содержать целое числовое значение от 10 до 119, соответствующее общей продолжительности тренировки в минутах. Для этого в ячейке E3 укажем следующую формулу (с последующим заполнением вниз):
=(ТЕКСТ(C3-B3;"ЧЧ")*60)+ТЕКСТ(C3-B3;"мм")
Примеры функции:
ТЕКСТ("03.08.2021 10:12:15";"ЧЧ") --> 10
ТЕКСТ("03.08.2021 10:12:15";"мм") --> 12
ТЕКСТ("03.08.2021 10:12:15";"ДДДД") --> вторник
Столбец "Час начала" будет содержать целое числовое значение от 16 до 22, соответствующее началу тренировки. Для этого в ячейке F3 укажем следующую формулу (с последующим заполнением вниз):
=ТЕКСТ(B3;"ЧЧ")
Столбец "День недели" будет содержать название дня недели от понедельника до воскресенья, соответствующее началу тренировки. Для этого в ячейке G3 укажем следующую формулу (с последующим заполнением вниз):
=ТЕКСТ(A3;"ДДДД")
Столбец "Дней отдыха" будет содержать целое числовое значение от 1 до 5, соответствующее началу тренировки. Для этого в ячейке H3 укажем любое значение от 1 до 5. А в ячейке H4 укажем следующую формулу (с последующим заполнением вниз):
=A4-A3
Столбец "Месяц начала" будет содержать название месяца от января до декабря, соответствующее началу тренировки. Для этого в ячейке I3 укажем следующую формулу (с последующим заполнением вниз):
=ТЕКСТ(A3;"ММММ")
Столбец "Год начала" будет содержать целое числовое значение от 2019 до 2021, соответствующее началу тренировки. Для этого в ячейке J3 укажем следующую формулу (с последующим заполнением вниз):
=ТЕКСТ(A3;"ГГГГ")
Таким образом получилось заполнить журнал тестовыми данными похожими на реальные значения. В следующей части будет произведён анализ количества тренировок по годам, по месяцам, по дням недели. Найдём затраченное среднее время на тренировку в зависимости от дня недели и времени начала тренировки. Выясним в какое время оценка качества тренировки наиболее высокая.
Спасибо за потраченное время на изучение статьи. Надеюсь, что Вы смогли найти для себя полезные формулы и приёмы их применения. С уважением, Анатолий.