Подготовка расписания уроков с использованием инструментов MS Excel
На этом занятии подготовим шаблон таблицы для заполнения расписание уроков.
Введение
Запустите Excel. Переименуйте "Лист 1" в "Расписание".
На этом листе будем собирать расписание на неделю.
В ячейке A1 введите "Понедельник" и используя "умное копирование" заполните ячейки в первой строке днями недели до "Пятница".
Сделаем ячейки шире, но так, чтобы они были одинаковой ширины. Для этого выделяем колонки от A до E и выставляем нужную ширину.
Обращаю внимание на то, что регулировать ширину следует "цепляясь" за разделитель в заголовке таблицы.
Предметы
Переименуем "Лист 2" в "Предметы", и в колонку A c первой ячейки внесем список:
Русский язык
Литературное чтение
Иностранный язык
Математика
Окружающий мир
Музыка
Изобразительное искусство
Технология
Физическая культура
Разговоры о важном
Классный час
На этой вкладке будет формироваться список предметов, который мы будем использовать как список для добавления в ячейки на листе "Расписание".
Вернитесь на вкладку "Расписание" и выделите диапазон ячеек A2:E7. Это те ячейки, в которых нужно будет разместить предметы.
Далее нужно указать источник данных из которых будет формироваться список значений для каждой ячейки. Для этого открываем вкладку "Данные" и в раздел "Работа с данными", ЛКМ по кнопке "Проверка данных". В появившемся окне выбираем список.
Переходим на лист "Предметы" и выбираем ячейки с предметами.
Другим вариантом указать диапазон ячеек спиcка: в поле ввода набираем формулу =Предметы!$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
В ячейки H3 получите сумму часов, используйте функцию СУММ.
Проверка дневной нагрузки
На листе предметы расставим вес согласно следущей таблице:
Русский язык - 5
Литературное чтение - 4
Иностранный язык - 4
Математика - 5
Окружающий мир - 3
Музыка - 2
Изобразительное искусство - 1
Технология - 2
Физическая культура - 1
Разговоры о важном - 1
Классный час - 1
Вернемся на вкладку "Расписание".
Добавим колонки между днями. В этих ячейках нужно будет раставить вес предмета для расчета дневной нагрузки. Процесс нужно автоматизировать, для того чтобы обрабатывать большее количество классов.
В новых колонках нужно указывать вес предмета, но на данный момент там возможно выбрать только предмет из выпадающего списка. Исправим это поведение. Зажимаем на клавиатуре CTRL и выделяем все новые колонки, которые мы вставили. Открываем вкладку "Данные", ЛКМ по кнопке "Проверка данных" в разделе "Работа с данными".
Выбираем "любое значение". Это действие отменит выпадающий список и даст возможность вводить любые другие значения.
Задача: в ячейке C2 установить значение веса предмета записанного в ячейке B2.
Для решения данной задачи нам понадобиться использовать формулы с функцией ВПР.
Функция ВПР в Excel позволяет искать значения в одной таблице и переносить их в другую. Она находит данные в указанном столбце и возвращает соответствующее значение из той же строки в другом столбце.
Синтаксис функции ВПР в Excel выглядит следующим образом: =ВПР(искомое_значение; диапазон_поиска; номер_столбца; [точное_соответствие])
У нас две таблицы, находящиеся на разных листах, нужно получить значение предмета, найти его в таблице на листе "Предметы" и вернуть значение из столбца B таблицы на листе "Предметы". То есть, в ячейке C2 нужно получить значение ячейки B2, найти его в таблице на листе "Предметы" в колонке A и получить значение правой ячейки.
В ячейке C2 набираем формулу "=ВПР(B2;Предметы!$A$1:$B$11;2;0)" и копируем ее по столбцам.
Самостоятельно: Рассчитайте нагрузку по каждому дню, используя функцию СУММ. Если у Вас есть значения больше 20, это означает что нагрузка в этот день очень большая. Нужно переставить предметы так что бы в день нагрузка не превышала 20, и количество часов по предметам не изменилось.
На картинке выше показана ошибка. Проанализируем таблицу: в пятницу значение 21, а в понедельник 11. Возможно в пятницу заменить Математику или Русский язык на Физическую культуру или Классный час в Понедельник.
Добавляем классы
Загрузить ответ
Сохраните полученную таблицу. Название файла: Группа_Фамилия_расписание.xls. Пример И1К_Иванов_расписание.xls. Прикрепите к форме ниже и нажмите "Загрузить".