Лабораторная работа 3 MS Excel

Лабораторная работа 3 MS Excel

Задание 1

Создайте текстовый файл в блокноте, содержащий информацию о студентах и набранных ими баллах. Импортируйте его в Excel. Добавьте новые записи, и выберите записи, в которых есть данных о набранных баллах. Отсортируйте список: фамилии в алфавитном порядке, а баллы, набранные студентами, в порядке убывания. Подведите итог по каждому студенту по количеству набранных им баллов. Оставьте на экране только итоговые данные по студентам, получившим более 100 баллов.

На Листе 2 создайте таблицу по образцу рисунка 3.5. Подсчитайте чистую прибыль фирмы и покажите зависимость прихода, расхода и прибыли на графике в зависимости от месяца (постройте отдельно диаграммы для прибыли, расхода и прибыли на имеющемся листе и сводную диаграмму по всем трем категориям на отдельном листе). Замените цвет фона диаграмм на голубой, цвет шрифта легенд, заголовков и подписей по оси Y - на синий, 10пт.

Выполнение задания

1. Создайте в Блокноте следующий файл и сохраните его в папке Мои документы под именем Список.

фамилия имя баллы

Иванов Иван 23

Дятлова Анна 6

Петрова Нина 56

Михайлова Евгения

Сидоров Андрей 4

Дятлова Анна 6

Михайлова Евгения 67

Самсонов Игорь 45

Петрова Нина 23

Иванов Иван 45

Сидоров Андрей

Петрова Нина 56

Дятлова Анна 8

Михайлова Евгения 23

Самсонов Игорь 1

Иванов Иван 45

2. Откройте файл Список.txt в Excel.

• В окне Открытие файла в списке Тип файла выберите Все файлы, после этого станут видны все файлы папки Мои документы, в том числе и файл Список.txt.

• На 1-ом шаге Мастера текстов в группе Формат исходных данных установите переключатель в положение С разделителями. Нажмите кнопку Далее.

• На 2-ом шаге в группе Символом разделителем является установите флажок Пробел. Нажмите кнопку Далее.

• На 3-ем шаге в группе Формат данных столбца переключатель установите в положение Общий. Нажмите кнопку Готово.

3. Добавьте данные в список при помощи формы.

• Выделите список - A1:C18. В меню Данные выберите пункт Форма.

• В форме Список нажмите кнопку Добавить.

• Заполните пустые поля последовательно данными: в поле Фамилия введите фамилию Иванов, в поле Имя – Иван, в поле Баллы – 56. Нажмите кнопку Добавить. Введите остальные данные. (После каждой следующей записи нажимайте кнопку Добавить, после последней – кнопку Закрыть):

Иванов Иван 56

Дятлова Анна 23

Петрова Нина 41.

3. Удалите записи, не содержащие данные о баллах с использованием расширенного фильтра.

• Введите в ячейку E1 название столбца по которому будет производиться отбор – Баллы, в ячейку E2 – условие отбора - >0.

• Выделите список A1:C20. В меню Данные выберите Фильтр/Расширенный фильтр.

• В группе Обработка установите переключатель в положение Фильтровать список на месте (после отбора данных старый список заменится на новый).

• Щелкните в поле Диапазон условий. Выделите блок E1:E2. Нажмите Ok.

3. Отсортируйте полученный список.

• Выделите список. Выполните Данные/Сортировка.

• В поле Сортировать по выберите Фамилия, переключатель установите – по возрастанию; в поле Затем по: - Баллы, переключатель – по убыванию. Нажмите Ok.

3. Подведите итоги.

• Выделите список. Выполните Данные/Итоги.

• В списке При каждом изменении в: укажите Фамилии. В списке Операция: - сумма. В списке Добавить итоги по установите флажок Баллы. Установите также флажок Итоги под данными. Нажмите Ok.

3. Создайте структуру списка.

• Выделите список. Выполните Данные/Группа и структура/Создание структуры. При этом с правой стороны окна рядом с номерами строк появятся кнопки с уровнями структуры: 1- самый общий уровень - Общий итог, 2- более детализированный – представляет итоги по каждому студенту, 3 – детальный уровень.

• Щелкните на 2, чтобы оставить только итоги по каждому студенту.

3. Примените Автофильтр для выборки тех студентов, которые получили более 100 баллов.

• Выделите список и выполните Данные/Фильтр/Автофильтр.

• В первой строке в ячейках заголовков (справа) появятся кнопки Нажмите кнопку в ячейке Баллы и выберите пункт Условие.

• В окне Пользовательский автофильтр в первом поле установите - больше, во втором - 100. Нажмите Ok.

Перейдите на Лист 2. Создайте таблицу по образцу рисунка 3.6. Прибыль вычислите как разницу между приходом и расходом.

Рисунок 3.6. Таблица к заданию 1

3. Переименуйте Лист 1 в Фирма.

4. Постройте диаграмму Приход.

• Выделите блок данных, по которым будет строится диаграмма - A2:G3.

• Щелкните кнопку Мастера диаграмм или Вставка/Диаграмма.

• На 1-ом шаге Мастера диаграмм на вкладке Стандартная выберите Тип диаграммы - график, Вид и нажмите Далее.

• На 2-ом шаге убедитесь, что на вкладке Диапазон данных в поле Диапазон указано =фирма!$A$2:$G$3 (т.е. для построения диаграммы используется диапазон A2:G3 листа с именем Фирма) и переключатель Ряды в установлен в положение в строках. Нажмите кнопку Далее.

• На 3-ем шаге на вкладке Заголовки в поле Название диаграммы введите Приход, перейдите на вкладку Легенда и установите переключатель в положение Внизу. (Легенда – подписи к линиям данных). Нажмите кнопку Далее.

• На 4-ом шаге установите переключатель Расположение диаграммы в положение В имеющемся, что означает, что диаграмма будет размещена на том же листе, что и таблица. Щелкните кнопку Готово.

Измените формат шрифта элементов диаграммы.

• Выполните 2 щелчка на шрифте заголовка диаграммы. В диалоговом окне Формат названия диаграммы на вкладке Шрифт установите Цвет – синий, Размер – 10. Нажмите Ok.

• Выполните два щелчка на легенде (позже на подписях по оси Y) в диалоговом окне Формат легенды (для подписей по оси Y окно называется Формат оси) на вкладке Шрифт установите синий цвет и 10-ый размер.

• Аналогично шрифту измените форматы и других деталей диаграммы: выполните два щелчка на элементе диаграммы и в раскрывшемся окне поменяйте значения параметров на желаемые.

Измените цвет фона.

• Выполните два щелчка на фоне диаграммы. В диалоговом окне Формат области построения на вкладке Вид выберите цвет Заливки – голубой.

Постройте диаграммы Расход и Прибыль аналогично построению диаграммы Приход, используя клавишу Ctrl для выделения несмежных блоков.

Постройте сводную диаграмму по всем трем показателям.

• Выделите блок данных A2:G5. Вызовите Мастер диаграмм и производите построение по шагам аналогично предыдущим, за исключением нескольких моментов: на 3-ем шаге в поле Название диаграммы введите Показатели фирмы, легенду разместите справа; на 4-ом шаге выберите размещение диаграммы на отдельном листе. В рабочую книгу будет вставлен лист с диаграммой.

Сохраните документ и покажите выполненное задание преподавателю.

Задание 2

Таблица 3.2 Список пекарен

Пекарня	кол-во
Весна	13
Улыбка	45
Булочка	3
Весна	15
Весна	34
Мастер	23
Улыбка	12
Мастер	26
Булочка	4
Весна	34
Улыбка	18

1. Дан список пекарен и количество производимых ими изделий (таблица 3.5). Отсортируйте список по алфавиту.

2. Выберите максимальное количество изделий, производимое каждой пекарней, и общий максимальный показатель для всех пекарен.

3. Оставьте на экране данные только по тем пекарням, которые произвели более 20 изделий.

4. Постройте диаграмму показателей каждого учащегося на Листе 2 и сводную диаграмму успеваемости всех учащихся на отдельном листе согласно таблице 3.3:

Таблица 3.3. Показатели успеваемости учащихся за учебный год

Показатели успеваемости за учебный год
фамилия	Сен	Окт	ноя	дек	янв	фев	мар	апр	Май
Дорина	4,5	3	3,2	4,1	4,5	5	4,9	4,8	4,7
Кичеева	2,3	3	3,7	4	4,1	3,9	3,7	4	3,9
Лосев	4,9	4,7	4,6	4,5	4,2	4,1	4,8	4,5	4,3
Саможикова	5	4,7	4,5	4	3,9	3,9	4,5	4,7	5
Усольцев	3,1	3,6	3,7	2,9	3,8	4,1	4,1	4,1	4,5