На этом занятии подготовим шаблон таблицы, в котором соберём расписание занятий для класса на одну неделю. Это занятие будет полезно учителям и ученикам/студентам, как простая практическая работа.
Введение
Задача данного урока состоит в том, чтобы показать очень простой вариант автоматизации составления расписания и анализа расписания на основе введенных данных. Шаблон поможет заполнить расписание занятиями, посчитать часы по каждому предмету и проверить дневную нагрузку.
Запустите Excel. Переименуйте "Лист 1" в "Расписание".
Для того чтобы переименовать лист нужно: ПКМ по названию листа, выберите в появившемся списке "Переименовать" и введите новое название "Расписание".
На этом листе будем собирать расписание на неделю.
В Excel есть функционал, который позволяет ускорить работу в несколько раз. Он включает в себя различные приемы копирования и вставки, такие как копирование только выделенных ячеек, использование функции мгновенного заполнения, а также копирование формул, которое изменяет ссылки в формулах и выстроит правильную систему адресов или заполнит ячейки логически верными данными.
Если ввести в ячейку слово "Понедельник", зацепиться мышью за правый нижний угол этой ячейки и протянуть в любую сторону, то Excel логически заполнит следующую ячейку словом "Вторник" и так далее.
В ячейке A1 введите "Понедельник" и используя "умное копирование" заполните ячейки в первой строке днями недели до "Пятница".
Сделаем ячейки шире, но так, чтобы они были одинаковой ширины. Для этого выделяем колонки от A до E и выставляем нужную ширину. Обращаю внимание на то, что выделять колонки следует на линейке буквенных обозначений, а регулировать ширину следует "цепляясь" за разделитель на линейке буквенных обозначений колонок.
Предметы
Переименуем "Лист 2" в "Предметы", и в колонку A c первой ячейки введем список:
Русский язык
Литературное чтение
Иностранный язык
Математика
Окружающий мир
Музыка
Изобразительное искусство
Технология
Физическая культура
Разговоры о важном
Классный час
На этом листе будет формироваться список предметов, который будет использоваться как список для добавления в ячейки на листе "Расписание".
Вернитесь на вкладку "Расписание" и выделите диапазон ячеек A2:E7. Это те ячейки, в которых нужно разместить предметы.
Далее нужно указать источник данных из которых будет формироваться список значений для каждой ячейки. Для этого открываем вкладку "Данные" и в раздел "Работа с данными", ЛКМ по кнопке "Проверка данных". В появившемся окне выбираем список. Указываем источник: Переходим на лист "Предметы" и выбираем ячейки с предметами.
Можно воспользоваться другим вариантом указать диапазон ячеек списка. В текстовом поле ввести формулу: =Предметы!$A$1:$A$11.
В этой формуле, электронной таблице указывается, что нужно выбрать ячейки диапазона A1:A11 на листе "Предметы" и сделать их абсолютными ($A$1:$A$11).
Теперь в каждой ячейке в которой вы указали "использовать список", появляется выпадающее меню, из которого можно выбирать значения.
Соберите первое расписание, при условии, что в день может быть проведено не более пяти занятий. Добавьте колонку слева и пронумеруйте занятия.
Проверка количества часов по предметам
Следующая задача состоит в том, чтобы проверить количество часов по предметам. Например: на предмет "Математика" в неделю должно отводиться 6 часов.
Для этого будем использовать функцию =СЧЁТЕСЛИ(диапазон; искомое значение). Эта функция подсчитает количество значения в выбранном диапазоне.
Формула в электронных таблицах начинается со знака "=", а текст заключается в двойные кавычки.
Переходим на вкладку "Предметы", выделяем и копируем в буфер обмена названия всех дисциплин. Возвращаемся на вкладку расписание и нажимаем ПКМ на ячейке H1. В контекстном меню находим кнопку "Транспонировать" в разделе "Специальная вставка".
Транспонирование - это операция, при которой строки и столбцы таблицы меняются местами.
Это действие вставит данные скопированные из колонки в строку.
В ячейку H2 введем формулу "=СЧЁТЕСЛИ(B2:F6;"Русский язык")" и нажмем Enter.
Мы получили значения сколько раз встречается ячейка со значением "Русский язык" в выбранном диапазоне.
Теперь сделаем формулу "копируемой", а значения названий предметов возьмем из ячейки выше. Для этого перепишем формулу в ячейке H2 "=СЧЁТЕСЛИ($B$2:$F$6;H1)".
Мы заменили текст "Русский язык" на H1 для того что бы формула использовала значения из этой ячейки, а при умном копировании по строке, меняла значения на L1, J1 и т.д. Так же мы добавили в имена ячеек диапазона знак "$", для того что бы диапазон не менял своего значения.
Теперь, используя умное копирование, заполните значениями строку и получите количество часов по каждому предмету.
Самостоятельно исправьте расписание согласно следующим значениям:
Русский язык - 6 часов
Литературное чтение - 2 часа
Иностранный язык - 1 час
Математика - 6 часов
Окружающий мир - 2 часа
Музыка - 1 час
Изобразительное искусство - 2 часа
Технология - 1 час
Физическая культура - 2 часа
Разговоры о важном - 1 час
Классный час - 1 час
Проверка дневной нагрузки
На листе предметы расставим вес, в колонке B, согласно следующим значениям:
Русский язык - 5
Литературное чтение - 4
Иностранный язык - 4
Математика - 5
Окружающий мир - 3
Музыка - 2
Изобразительное искусство - 1
Технология - 2
Физическая культура - 1
Разговоры о важном - 1
Классный час - 1
Вернемся на вкладку "Расписание". Добавим колонки между днями. В этих ячейках нужно будет расставить вес предмета для расчета дневной нагрузки. Для того чтобы обрабатывать большее количество классов процесс нужно автоматизировать. В новых колонках нужно указывать вес предмета, но на данный момент там возможно выбрать только предмет из выпадающего списка. Исправим это поведение. Зажимаем на клавиатуре CTRL и выделяем все новые колонки, которые мы вставили. Открываем вкладку "Данные", ЛКМ по кнопке "Проверка данных" в разделе "Работа с данными". Выбираем "любое значение". Это действие отменит выпадающий список и даст возможность вводить любые другие значения.
Задача: на листе Расписание, в ячейке C2 установить значение веса предмета название которого расположено в ячейке B2.
Для решения данной задачи нам понадобиться использовать формулу с функцией ВПР.
Функция ВПР в Excel позволяет искать значения в одной таблице и переносить их в другую. Она находит данные в указанном столбце и возвращает соответствующее значение из той же строки в другом столбце. Синтаксис функции ВПР в Excel выглядит следующим образом: =ВПР(искомое_значение; диапазон_поиска; номер_столбца; [точное_соответствие]).
В ячейке C2 набираем формулу "=ВПР(B2;Предметы!$A$1:$B$11;2;0)" и копируем ее по столбцу.
С помощью функции СУММ посчитаем нагрузку. В ячейки C7 введем формулу: "=СУММ(C2:C6)", самостоятельно посчитайте нагрузку по остальным дням.
Если у Вас есть значения больше 20, это означает что нагрузка в этот день очень большая. Нужно переставить предметы так что бы в день нагрузка не превышала 20, и количество часов по предметам не изменилось.
На картинке выше показана ошибка. Проанализируем таблицу: в пятницу значение 21, а в понедельник 11. Одним из решений будет замена Математики в пятницу на Классный час, а в понедельник Классный час заменить на Математику.
Добавляем классы
Добавьте строку в самом верху страницы. Объедините ячейки B1:J1 и внесите данные о классе и классном руководители.
Самостоятельно: создайте расписание для классов 1Б и 1В.
Вывод
На этом занятии мы подготовили простой шаблон для создания расписания на неделю. Познакомились с возможностью создавать выпадающие списки, с функциями СЧЁТЕСЛИ, ВПР и СУММ, так же автоматизировали некоторые рутинные действия, например когда выбираем из списка предмет таблица считает количество часов предмета в неделю, и автоматически подставляет вес предмета для подсчета дневной нагрузки.
