оздание кнопочного интерфейса.

оздание файла.

На рабочем столе создадим файл Microsoft Office Excel, имя файла соответствует фамилии студента, выполняющего лабораторную работу.

 

2. Создание рабочего листа «Интерфейс».

 

На листе 1 выделите ячейки таблицы и примените к ним цвет заливки ячеек «Желтый».

Введите следующий текст: Экономический факультет приемная комиссия. Примените форматирование ячеек: шрифт Courier, начертание полужирный, размер 25 и расположите по центру.

Страница должна выглядеть так:

Присвойте рабочему листу Лист 1 имя Интерфейс. Для этого щелкните дважды на ярлычке Лист1. Строка Лист1 станет выделенной. Наберите Интерфейс и нажмите Enter.

Сохранить изменения в файле.

3. Создание рабочего листа «Список».

Перейдем на рабочий лист Лист2. Присвоим ему имя «Список». Данный рабочий лист должен содержать таблицу.

Таблицу можно скопировать и воспользоваться меню Вставка.

Обратите внимание на формат ячеек. Первый и пятый столбец таблицы должен иметь числовой формат ячеек с числом десятичных знаков 0, а столбцы со второго по четвертый – общий формат ячеек.

Скопируйте таблицу. Таблица должна начинаться с ячейки А3.

 

Список абитуриентов
№ п/п	Фамилия	Имя	Отчество	Экзаменац. лист
	Антонова	Ирина	Васильевна
	Афонин	Сергей	Анатольевич
	Булгакова	Ольга	Петровна
	Васильева	Татьяна	Игоревна
	Величко	Владимир	Иванович
	Ермолаев	Максим	Викторович
	Иванов	Павел	Юрьевич
	Качалов	Игорь	Олегович
	Кольцова	Анастасия	Дмитриевна
	Краснова	Ольга	Ивановна
	Кузенков	Сергей	Сергеевич
	Кузьмин	Андрей	Николаевич
	Кукушкин	Андрей	Петрович
	Кукушкина	Наталья	Петровна
	Кулагин	Андрей	Сергеевич
	Леонидов	Александр	Сергеевич
	Леонова	Мария	Антоновна
	Маланов	Алексей	Иванович
	Мамаев	Игорь	Геннадьевич
	Марков	Михаил	Михайлович
	Морозова	Лидия	Семеновна
	Немов	Леонид	Петрович
	Петракова	Альбина	Евгеньевна
	Петров	Иван	Геннадьевич
	Петухова	Анна	Андреевна
	Пименова	Жанна	Аркадьевна
	Ростова	Вика	Сергеевна
	Рузин	Константин	Валерьевич
	Сапожкова	Юлия	Дмитриевна
	Семенова	Ольга	Михайловна
	Сидоров	Михаил	Васильевич
	Сидорова	Светлана	Николаевна
	Симонова	Ольга	Сергеевна
	Смуров	Максим	Артемьевич
	Сокурова	Татьяна	Борисовна
	Ушаков	Сергей	Олегович
	Федоров	Михаил	Геннадьевич
	Шаравин	Федор	Иванович
	Шарипов	Эльдар	Алмазович
	Шишков	Дмитрий	Дмитриевич

 

После ввода данных отформатируйте, отредактируйте и расчертите таблицу.

В результате должна получиться следующая таблица:

Сохраните изменения в файле.

 

4. Создание рабочих листов «Абитуриенты» и «Проходной балл».

 

Переходим на рабочий лист Лист3. Присваиваем ему имя Абитуриенты. Данный рабочий лист должен содержать таблицу.

В начале создайте шапку таблицы с ячейки А7. Ячейки с А7 по С7 пустые, а ячейки с D7 по H7 объедините и вставьте «Экзаменационные оценки». Далее в ячейки А8 по Н8 вставьте: № П/п, Фамилия, Специальность, Математика, Информатика, Русский язык, Суммарный балл, Отметка о зачислении. Введите название таблицы – «Результаты вступительных экзаменов».

В результате должно получиться следующее:

 

 

В строку № п/п введите числа от 1 до 40.

В столбец Фамилия данные попадают из таблицы Список абитуриентов столбец Фамилия.

Данные в столбцы Специальность, Экзаменационные оценки по математике, информатике, русскому языку вводятся с клавиатуры.

Суммарный балл представляет собой сумму экзаменационных оценок по математике, информатике и русскому языку.

Значение одной ячейки столбца Отметка о зачисление вычисляется следующим образом: абитуриент зачисляется в том случае, когда сумма набранных баллов не менее проходного балла по специальности.

Для вычисления этих данных применяются формулы показанные в таблице

 

 

Для того чтобы заполнить столбец Отметка о зачислении необходимо создать новый рабочий лист. Щелкните правой кнопкой мыши на ярлычок любого рабочего листа и нажмите Добавить. Появиться форма, выберите добавить Лист и нажмите OK. Переместите новый рабочий лист после рабочего листа Абитуриенты и присвойте ему имя «Проходной балл» (Лист 4). На рабочем листе «Проходной балл» создайте таблицу

 

ПРОХОДНЫЕ БАЛЛЫ ПО СПЕЦИАЛЬНОСТЯМ
Специальность	Проходной балл
ЭФ-1
ЭФ-2
ЭФ-3
ЭФ-4

Расчертите, отформатируйте и отредактируйте таблицу рабочего листа «Проходной балл».

 

Вернемся с рабочему листу «Абитуриенты». Для определения значения ячейки столбца Отметка о зачислении воспользуемся функциями ЕСЛИ и ВПР, а также данными из таблицы рабочего листа Проходной балл. Если абитуриент зачисляется, то на против его фамилии ставиться отметка – Зачислен, если не зачисляется, то на против его фамилии ставится отметка – Не зачислен.

Вставьте значения в столбцы с третьего по шестой из следующей таблице. Суммарный балл и Отметка о зачислении рассчитайте по формулам, указанным выше.

 

Результаты вступительных экзаменов
			Экзаменационные оценки
№ п/п	Фамилия	Специально-сть	Математика	Информатика	Русский язык	Суммарный балл	Отметка о зачислении
	Антонова	ЭФ-3					Зачислен
	Афонин	ЭФ-1					Зачислен
	Булгакова	ЭФ-3					Зачислен
	Васильева	ЭФ-1					Зачислен
	Величко	ЭФ-2					Зачислен
	Ермолаев	ЭФ-2					Не зачислен
	Иванов	ЭФ-1					Зачислен
	Качалов	ЭФ-3					Зачислен
	Кольцова	ЭФ-3					Зачислен
	Краснова	ЭФ-1					Зачислен
	Кузенков	ЭФ-4					Зачислен
	Кузьмин	ЭФ-3					Зачислен
	Кукушкин	ЭФ-3					Зачислен
	Кукушкина	ЭФ-4					Зачислен
	Кулагин	ЭФ-2					Зачислен
	Леонидов	ЭФ-4					Зачислен
	Леонова	ЭФ-2					Не зачислен
	Маланов	ЭФ-1					Зачислен
	Мамаев	ЭФ-1					Не зачислен
	Марков	ЭФ-3					Зачислен
	Морозова	ЭФ-2					Не зачислен
	Немов	ЭФ-1					Зачислен
	Петракова	ЭФ-2					Зачислен
	Петров	ЭФ-3					Не зачислен
	Петухова	ЭФ-1					Зачислен
	Пименова	ЭФ-4					Зачислен
	Ростова	ЭФ-2					Зачислен
	Рузин	ЭФ-4					Зачислен
	Сапожкова	ЭФ-2					Не зачислен
	Семенова	ЭФ-3					Зачислен
	Сидоров	ЭФ-1					Не зачислен
	Сидорова	ЭФ-4					Не зачислен
	Симонова	ЭФ-1					Не зачислен
	Смуров	ЭФ-3					Зачислен
	Сокурова	ЭФ-1					Зачислен
	Ушаков	ЭФ-4					Не зачислен
	Федоров	ЭФ-3					Зачислен
	Шаравин	ЭФ-1					Зачислен
	Шарипов	ЭФ-4					Зачислен
	Шишков	ЭФ-2					Не зачислен

 

В конце таблице необходимо выполнить следующие вычисления:

1. рассчитать среднюю оценку по каждому предмету по всем студентам при помощи функции СРЗНАЧ;

2. определить максимальный и минимальный суммарный балл при помощи функций МАКС и МИН;

3. определить количество зачисленных студентов на факультет при помощи функции СЧЁТЕСЛИ.

 

В результате должно получиться следующее:

 

Расчертите, отредактируйте и отформатируйте таблицу.

После выполнения данного упражнения должно получиться две следующие таблицы:

 

 

Сохранить изменения в файле.

 

оздание сводной таблицы.

 

Создайте новый рабочий лист «Сводная таблица» (Лист 5) и поместите его после рабочего листа Проходной балл.

Воспользуйтесь мастером сводных таблиц (Вставка→Сводная таблица):

- на 1-м шаге – «Создать таблицу на основе списка или базы данных
Excel»;

 

- на 2-м шаге - задать в качестве области данных диапазон "$A$8:$H$48" листа «Абитуриенты» (выделить со второй строки шапки таблицы и до последнего в списке абитуриента);

 

- на 3-м шаге - определить структуру будущей сводной таблицы. Нажимаем на кнопку Макет и перетаскиваем кнопки полей в нужные области как показано на рисунке.

В область диаграммы Строка перетаскиваем кнопки Специальность, № п/п, Фамилия.

В область диаграммы Столбец перетаскиваем кнопку Отметка о зачислении.

В область диаграммы Данные перетаскиваем кнопку Суммарный балл, щелкаем по ней левой кнопкой мыши два раза. Из появившейся формы «Вычисление формы сводной таблицы» выбираем операцию «Среднее» и нажимаем OK.

На форме Макет тоже нажимаем OK.

 

- на 4-м шаге - определяем параметры таблицы. Нажимаем на кнопку Параметры. Открывается форма «Параметры сводной таблицы». Необходимо указать следующие параметры:

- на 5-м шаге - нажать кнопку "Готово".

 

В результате должна получиться таблица

 

Далее выполните «Вычисление поля сводной таблицы» для поля «Специальность». Для этого дважды щелкнуть мышью по полю «Специальность» и в открывшемся диалоге «Вычисление поля сводной таблицы» установить следующие параметры:

- имя: «Специальность»;

- итоги «Количество».

Остальные параметры диалога оставить без изменения. В результате получится следующая сводная таблица

 

Сохранить изменения в файле.

 

6. Создание рабочих листов «ЭФ-1», «ЭФ-2», «ЭФ-3», «ЭФ-4».

 

Присвоим новому рабочему листу имя «ЭФ-1» (Лист 6) и создадим следующие таблицы:

 

 

Данные для первой таблицы в столбец Фамилия берем из таблицы рабочего листа Абитуриенты. Для этого из таблицы на листе Абитуриенты с помощью автофильтра найдем зачисленных абитуриентов на специальность ЭФ-1. Для этого выделим ячейки А8:Н8 на листе Абитуриенты и воспользуемся командой меню Данные – Фильтр – Автофильтр. Нажмем на кнопку сначала в ячейки специальность выберем ЭФ-1, затем в ячейки Отметка о зачислении Зачислен.

 

Скопируем их фамилии на лист ЭФ-1 в столбец Фамилия.

Остальные ячейки таблиц вычисляемые. Для их вычисления воспользуйтесь следующими формулами:

 

 

 

Ячейки Экзаменационные оценки (математика, информатика, Русский язык) и Номер экзаменационного листа вычисляются по формуле ВПР. Ячейки Фамилия И.О. вычисляются по формулам СЦЕПИТЬ, ЛЕВСИМВ, ВПР. Посмотрите справку по этим формулам (команда Вставка – Функции).

 

Затем расчертите, отредактируйте и отформатируйте таблицу.

 

Далее самостоятельно создайте рабочие листы «ЭФ-2» (Лист 7), «ЭФ-3» (Лист 8) и «ЭФ-4» (Лист 9), оформите их также как лист ЭФ-1. Используйте для каждого листа данные соответствующих специальностей.

 

Сохранить изменения в файле.

оздание диаграмм.

 

Создайте два новых рабочих листа и присвойте им следующие имена: ДиаграммаСумма и ДиаграммаРезультаты.

Для каждого рабочего листа «ЭФ-1», «ЭФ-2», «ЭФ-3» и «ЭФ-4» создайте по две диаграммы:

 

 

1. обычная диаграмма,

 

трехмерная гистограмма.

 

Обычные диаграммы, созданные при помощи значений взятых из таблиц рабочих листов «ЭФ-1», «ЭФ-2», «ЭФ-3» и «ЭФ-4», сохраните на листе ДиаграммаСумма. Трехмерные гистограммы – на листе ДиаграммаРезультаты. Таким образом, на листах ДиаграммаСумма и ДиаграммаРезультаты получится по четыре диаграммы.

 

Сохранить изменения в файле.

 

оздание кнопочного интерфейса.

 

Переходим на лист «Интерфейс». На данном листе создадим 7 кнопок. Для этого необходимо сделать активной панель инструментов «Элементы управления». Данная панель будет активной, если выполнить следующие действия в меню Вид выберите пункт Панель инструментов, а затем команду – Элементы управления. На экране появиться панель инструментов «Элементы управления», содержащая элемент управления – Кнопка.

 

Переходим далее в режим Конструктор. Для этого на панели «Элементы управления» щелкнем на значок .

После этого щелкните на элемент управления Кнопка левой кнопкой мыши и, перетаскивая указатель мыши, нарисуйте рамку кнопки на рабочем листе. В дальнейшем размер кнопки и ее расположение можно будет изменить. На рабочем листе появилась Кнопка с названием Command Button1. Далее подведите к ней курсор мыши и щелкните правую кнопку. Из раскрывающегося списка выберите Объект Command Button → Edit. Теперь имеется возможность поменять название кнопки. Сотрите старое название и введите новое название – Список абитуриентов. Данную кнопку будем использовать для того, чтобы при нажатие на нее автоматически открывался лист Список.

Далее поменяем цвет кнопки и размер шрифта. Для этого подведите к ней курсор мыши и щелкните правую кнопку. Из раскрывающегося списка выберите Свойства. На экране появится окно Properties (Свойства). Измените следующие свойства:

- BackColor (Цвет кнопки): бледно розовый;

- Font (Шрифт): Courier New, полужирный, 12 размер.

Далее два раза мышкой нажимаем на кнопку Список абитуриентов в режиме конструктор или на кнопку на панели инструментов . Откроется окно редактора Visual Basic. Оно имеет вид:

 

Слева в окне редактора Visual Basic располагается Окно проекта, а справа – Окна редактора кода. В Окне проекта расположены проекты всех открытых рабочих книг. А Окно редактора кода служит редактором для ввода и изменения кода процедур.

Как определялось выше, при нажатии на кнопку «Список абитуриентов» на листе Интерфейс, должен автоматически открываться лист Список с таблицей Список абитуриентов. Для этого в проекте Visual Basic в объектах на листе 1 должна быть следующая команда

 

'Кнопка Список абитуриентов открывает Список

Private Sub CommandButton1_Click()

Лист2.Activate

End Sub

 

Окна редактора кода Лист1 должно содержать следующую команду

Пояснение. Вторая и четвертая строка появляются автоматически при первом нажатие на кнопку. Первая строка служит пояснением, поэтому начинать ее следует с ‘. Третья строка определяет, что при нажатие на кнопку1 будет активен Лист2 (Лист2.Activate). В Окне проекта видно, что Лист2 – лист Список.

Private Sub – начало подпрограммы;

CommandButton1_Click() – при нажатии на кнопку CommandButton1 на экране открывается Лист2 – Лист2.Activate;

End Sub – конец подпрограммы.

 

Далее на листе Интерфейс создадим еще 6 кнопок:

- Кнопка2 «Результаты экзаменов» открывает лист Абитуриенты;

- Кнопка3 «ЭФ-1» открывает лист ЭФ-1;

- Кнопка4 «ЭФ-2» открывает лист ЭФ-2;

- Кнопка5 «ЭФ-3» открывает лист ЭФ-3;

- Кнопка6 «ЭФ-4» открывает лист ЭФ-4;

- Кнопка7 «Выход» служит для выхода из ИС.

Первые 5 кнопок создаются аналогично кнопке «Список абитуриентов». Поместите их недалеко друг от друга и объедините общей рамкой при помощи меню Формат пункт Ячейки вкладка Границы.

Кнопка «Выход» в Окне редактора кода Лист1 должна содержать следующую команду:

‘ Кнопка Выход

Private Sub CommandButton7_Click()

Workbooks.Close

End Sub

 

Пояснение. Workbooks.Close означает закрыть рабочую книгу (Workbooks).

 

Сохранить изменения в файле.

 

После проделанных действий рабочий лист «Интерфейс» должен выглядеть следующим образом:

 

Далее создадим на других листах кнопки.

При нажатии на кнопку «Список абитуриентов» открывается лист Список (лист 2). Создадим на листе Список две кнопки:

- Кнопку1 «Интерфейс», которая будет возвращать нас на лист Интерфейс;

- Кнопку2 «Результаты экзаменов», при помощи которой открывается лист Абитуриенты.

Для этого в окне редактора кода Лист2 необходимо ввести соответствующие команды.

 

Лист будет выглядеть так

 

При нажатии на кнопку «Результаты экзаменов» открывается лист Абитуриенты (лист 3). Создадим на этом листе:

- Кнопку1 «Интерфейс», которая будет возвращать нас на лист Интерфейс;

- Кнопку2 «Сводная таблица», при помощи которой открывается лист Сводная таблица;

- Кнопку3 «Список абитуриентов», открывающая лист Список.

Для этого в окне редактора кода Лист3 необходимо ввести соответствующие команды.

 

Лист будет выглядеть так:

 

При нажатии на кнопку «ЭФ-1» открывается лист ЭФ-1 (лист 6), на кнопку «ЭФ-2» - лист ЭФ-2 (лист 7), на кнопку «ЭФ-3» - лист ЭФ-3 (лист 8), на кнопку «ЭФ-4» - лист ЭФ-4 (лист 9). Создадим на этих листах:

- Кнопку1 «Интерфейс», которая будет возвращать нас на лист Интерфейс;

- Кнопку2 «Диаграмма сумма балов», при помощи которой открывается лист ДиаграммаСумма;

- Кнопку3 «Диаграмма результаты экзаменов», при помощи которой открывается лист ДиаграммаРезультаты.

 

Для этого в окне редактора кода Лист6, Лист7, Лист8, Лист9 необходимо ввести соответствующие команды.

 

Например, лист ЭФ-1 будет выглядеть так:

 

На листе «Сводная таблица» создадим кнопку «Назад», которая будет возвращать нас на лист Абитуриенты.

 

На листах ДиаграммаСумма и ДиаграммаРезультаты создадим

- Кнопку1 «Назад ЭФ-1» около диаграммы, относящейся к ЭФ-1, которая будет возвращать на лист ЭФ-1;

- Кнопку2 «Назад ЭФ-2» около диаграммы, относящейся к ЭФ-2, которая будет возвращать на лист ЭФ-2;

- Кнопку3 «Назад ЭФ-3» около диаграммы, относящейся к ЭФ-3, которая будет возвращать на лист ЭФ-3;

- Кнопку4 «Назад ЭФ-4» около диаграммы, относящейся к ЭФ-4, которая будет возвращать на лист ЭФ-4.

 

Например, лист ДиаграммаСумма может выглядеть так:

Сохранить изменения в файле.

 

Таким образом, в процессе выполнения лабораторной работы был создан файл, содержащий следующие листы:

- Лист 1 «Интерфейс»;

- Лист 2 «Список»;

- Лист 3 «Абитуриенты»;

- Лист 4 «Проходной балл»;

- Лист 5 «Сводная таблица»;

- Лист 6 «ЭФ-1»;

- Лист 7 «ЭФ-2»;

- Лист 8 «ЭФ-3»;

- Лист 9 «ЭФ-4»;

- Лист 10 «ДиаграммаСумма»;

- Лист 11 «ДиаграммаРезультаты».

Каждый лист, кроме «Проходной балл», должен содержать элемент управления «Кнопка».

 

Предъявить полученные результаты преподавателю.