Добрый день, уважаемый читатель. Это продолжение предыдущей части, где мы заполнили историю тренировок похожей на реальный журнал. В этой статье мы проведём анализ количества и качества тренировок. Готовый файл для ознакомления.
Будут рассмотрены и применены следующие формулы:
СЧЁТЕСЛИМН(«Диапазон условия1»; «Условие1»;«Диапазон условия2»; «Условие2»)
СРЗНАЧЕСЛИ(«Диапазон условия»; «Условие»; «Диапазон усреднения»)
ОКРУГЛ(«Число для округления»;«Знаков после запятой»)
Создадим новую вкладку «АнализМесяц», в которой посмотрим распределение тренировок каждый месяцам. Таблица будет иметь следующий вид:
В ячейке A6 введём следующую формулу: =ГОД(СЕГОДНЯ())
В ячейке A5 введём следующую формулу: =A6-1
В ячейке A4 введём следующую формулу: =A5-1
Таким образом наша таблица будет автоматически показывать актуальную информацию за последние три года.
Теперь займемся подсчётом тренировок каждый месяц в течении года. Для этого в ячейке В4 будем использовать формулу:
=СЧЁТЕСЛИМН(ЖурналТренировок!$J:$J;$A4;ЖурналТренировок!$I:$I;B$3), где
ЖурналТренировок!$J:$J - первый диапазон для первого условия.
$A4 - первое условие.
ЖурналТренировок!$I:$I - второй диапазон для второго условия.
B$3 - второе условие.
Символ $ означает, что при автоматическом заполнении ячеек формулой столбец или строка, в зависимости от указания, буду неизменными.
Заполняем этой формулой остальную таблицу. Теперь у нас есть информация, относительно каждого месяца, сколько было тренировок.
Далее необходимо наглядно визуализировать эту информацию. Для этого воспользуемся диаграммой. Выделяем таблицу
Переходим на вкладку вставить и выбираем "Рекомендуемые диаграммы"
На выбор будет предоставлены наиболее подходящие диаграммы. Мне показалось, что наиболее информативно будет "Гистограмма с группировкой". Можно наблюдать, каким образом меняется количество тренировок.
Получившееся "Гистограмма с группировкой"
Аналогично делаем остальные вкладки с анализом. Меня только диапазоны условий и сами условия.
Теперь посчитаем среднее время затраченное на тренировку в зависимости от дня недели. Вкладка «АнализПродолжительность»
В ячейке В4 будем использовать две формулы СРЗНАЧЕСЛИ получим среднее число, а ОКРУГЛ округлим до целого числа:
=ОКРУГЛ(СРЗНАЧЕСЛИ(ЖурналТренировок!$G:$G;$A4;ЖурналТренировок!$E:$E);0), где
ЖурналТренировок!$G:$G - диапазон для проверки условия.
$A4 - условие.
ЖурналТренировок!$E:$E - диапазон усреднения.
0 - число знаков после запятой.
Определения средней оценки тренировки делается аналогично. Только диапазон усреднения будет другой.
Таким образом с помощью простых и доступных каждому формул мы смогли сделать журнал истории тренировок, который позволяет отслеживать периодичность и затраченное время.
Спасибо за потраченное время на изучение статьи. Надеюсь, что Вы смогли найти для себя полезные формулы и приёмы их применения. С уважением, Анатолий.