МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ СТУДЕНТАМ ПО ВЫПОЛНЕНИЮ ПРАКТИЧЕСКИХ РАБОТ по дисциплине ОУД 0.9 Информатика

Министерство образования и науки Самарской области

Министерство имущественных отношений Самарской mобласти

Государственное бюджетное профессиональное образовательное учреждение

Самарской области

«Чапаевский губернский колледж им. О.Колычева»

МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ СТУДЕНТАМ ПО ВЫПОЛНЕНИЮ ПРАКТИЧЕСКИХ РАБОТ

В ТАБЛИЧНОМ ПРОЦЕССОРЕ MSEXCEL

по дисциплине ОУД 0.9 Информатика

специальность 09.02.07 Информационные системы и программирование

Чапаевск, 2022 год

Публикуется на основании решения

Методического совета ГБПОУ СОЧГК им. О.Колычева,

Протокол № 2 от 20.09. 2022г.

Составитель:Дикова В.Г., преподаватель

общепрофессиональных и специальных

дисциплин образовательной программы среднего

профессионального образования ППССЗ ГБПОУ

СОЧГК им. О.Колычева

Редактор:Захарова Е.М., заместитель директора по учебно-

методической работе образовательной программы

среднего профессионального образования ППССЗ

ГБПОУ СОЧГК им. О.Колычева

Рецензент: Денисов А.В., заместитель главного специалиста

отдела АСУ начальника отдела АО «Промсинтез»

г.о. Чапаевск

Методические рекомендации предназначены для студентов специальности 09.02.07 Информационные системы и программирование и нацелены на освоение навыков практического применения информационных технологий в профессиональной деятельности. Методические рекомендации содержат практические работы, задания в которых снабжены подробными указаниями для исполнения и экранными формами для наглядности. При разработке пособия использовались учебники, рекомендуемые программой, и информационные ресурсы сети Интернет

Содержание

Стр.

Пояснительная записка

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

Методические рекомендации предназначены для студентов специальности 09.02.07 Информационные системы и программирование для приобретения практических навыков в табличном процессоре MSExcel.

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

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

При разработке методических рекомендаций учитывались требования к результатам освоения учебной дисциплины, сформулированные в ФГОС СПО III поколения.

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

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

ОК 5. Использовать информационно-коммуникационные технологии в профессиональной деятельности.

Методические рекомендации могут быть использованы студентами и преподавателями данной дисциплины при подготовке к учебным занятиям.

Практическая работа №1.Организация расчетов в табличном процессоре MS Excel

Цель:Изучение информационной технологии исполь­зования встроенных вычислительных функцийExcel для финансово­го анализа.

Теоретическая справка

Программа MSEXCEL предназначена для управления электронными таблицами. Основное назначение электронных таблиц – хранение и обработка числовой информации. В них удобно реализовыватьавтоматические вычисления значений в ячейках. Ячейки, введенные пользователем, называются основными. Те данные, которые получаются в результате расчетов с использованием основных данных, называются произвольными.

Документ EXCEL называетсярабочей книгой. Она состоит израбочих листов. На рабочем листе можно создать одну или несколько таблиц. Таблица состоит из ячеек, которые образуются в результате пересечения строк и столбцов. Каждая ячейка имеет адрес, состоящий изимени столбца и номера строки. В ячейке могут храниться данные трех типов: числовые, текстовые и формулы.Формула- это математическое выражение, начинающееся со знака «=». Для автоматизации вычислительных операций можно работать с группой ячеек, которая называется диапазоном. Диапазон задается адресами первой и последней входящих в него ячеек.. Для записи формул используется Мастер функций .

Задание1. Создайте таблицу финансовой сводки за неделю

1. Введите заголовок таблицы «Финансовая сводка за неделю (тыс. руб.)»

2. Создание стиля для оформления заголовка

• Поставьте курсор в ячейку А3. Формат/Стиль. В открывшемся окне Стиль наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне ФОРМАТ ЯЧЕЕК на вкладке Выравнивание задайте Переносить по словам , выберитегоризонтальное и вертикальное выравнивание – по центру, на вкладкеЧисло укажите формат текстовый , на вкладке Шрифт- полужирный. ОК. Нажмите кнопку Добавить в окне СТИЛЬ. Выделите диапазон A3:D3. Формат/Стиль/Шапка таблиц (определили стиль для всех ячеек заголовка)

• В третьей строке введите название колонок таблицы.

• Заполните таблицу исходными данными. При вводе дней недели воспользуйтесь автозаполнением.

1. Произведите расчеты в графе Финансовый результат по формуле: финансовый результат=доход-расход (только в ячейке D3, в остальных строках-автокропированием. Ввод формулы начинается знаком «=» )

2. Для ячеек с результатами расчетов задайтеформат-«Денежный», с выделением отрицательных чисел красным цветом (Формат/Ячейка/Число/Формат-денежный, отрицательные числа-красные. Число десятичных знаков –2)

3. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопкаf_x, функцияСРЗНАЧ в категориистатистические).

4. В ячейкеD13 выполните расчет общего финансового результата (функция СУММ категории Математические или кнопка).

5. Отформатируйте заголовок таблицы кнопкой ОБЪЕДИНИТЬ И ПОМЕСТИТЬ В ЦЕНТРЕ или Формат/Ячейки/Вкладка Выравнивание/отображение- объединение ячеек. Начертание шрифта-полужирное.(предварительно выделите диапазон A3:D3)

6. Оформите границы ячеек таблицы (Формат/Ячейки/Граница).

7. Произведите Фильтрацию значений дохода, превышающих 4000р.

• Установите курсор внутри таблицы (или выделите таблицу)

• Данные /Фильтр/ Автофильтр.

• Щелкните по стрелке в столбце «ДОХОД»

• Выберите команду для фильтрации -Условие

• В окне Пользовательский автофильтр задайте условие «Больше 4000» .

• Проанализируйте изменение диаграммы

1. Проверьте результаты работы на первом листе рабочей книги Файл/Предварительный просмотр

2. Сохраните созданную электронную книгу на диске Н.Файл/Сохранить как Имя файла –Практическая работа №1.

Задание 2. Использование созданного Стиля.

Заполните таблицу. Произведите расчеты. Столбец Е – формат денежный. Выделите максимальную и минимальную суммы покупки. (Диапазон при поиске максимального и минимального значенияне включает ячейку Е11 –всего) По результатам расчета постройтекруговую диаграмму суммы продаж.

Замечание:Круговаядиаграмма служит для сравнения нескольких величин в одной точке. Полезна, если величины в сумме составляют 100%

1. Перейдите на второй лист электронной книги Практическая работа №1, щелкнув по ярлычку Лист 2.

2. Выделите ячейки шапки таблицы

3. Для оформления шапки таблицы используйте созданный в задании 1 стиль Формат/Стиль/Шапка таблицы. После этого осуществляйте ввод заголовков столбцов.

4. Расчет

• Суммы: Сумма=Цена*количество

• Всего: Сумма значений столбца «Сумма,руб»

• Для расчета максимального/минимального значения воспользуйтесь функциями МАХ/МИН из категории Статистические

1. Перед обращением кМастеру диаграмм выделите данные столбца «Сумма,руб». Для построения диаграммы воспользуйтесь подсказками построения диаграммы на стр. 32

2. Проверьте результаты работы на втором листе рабочей книги Файл/Предварительный просмотр

3. Файл/Сохранить. (Вы Сохранили созданный второй лист электронной книги на диске Н. Имя файла –Практическая работа №1)

Практическая работа №2. Использование логических функций

Цель: Изучение технологии использования логических функций. Использование абсолютной адресации.

Задание1. Создайте таблицу для расчета основного размера стипендии

1. Введите заголовок таблицы «Расчет размеров стипендии»

2. Отформатируйте заголовок таблицы кнопкой ОБЪЕДИНИТЬ И ПОМЕСТИТЬ В ЦЕНТРЕ или Формат/Ячейки/Вкладка Выравнивание/отображение- объединение ячеек. Начертание шрифта - полужирное.

3. Измените ширину столбцов

• Выделите столбцы A:E

• Формат/Столбец/ширина –11,71

1. Создание стиля для оформления заголовка

• Поставьте курсор в ячейку А2. Формат/Стиль. В открывшемся окне Стиль наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне ФОРМАТ ЯЧЕЕК на вкладке Выравнивание задайте Переносить по словам , выберитегоризонтальное и вертикальное выравнивание – по центру, на вкладкеЧисло укажите формат текстовый , на вкладке Шрифт- полужирный. ОК. Нажмите кнопку Добавить в окне СТИЛЬ. Выделите диапазон A2:E2. Формат/Стиль/Шапка таблиц (определили стиль для всех ячеек заголовка)

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

1. ЗадайтеДенежный формат ячейкам A3:E3 Выделите ячейки A3:E3.Формат/Ячейки/Число/ Формат-Денежный

2. Заполните таблицу исходными данными.

• В столбец «Базовое. знач» (базовое значение ) поместите значение 210.

• Уральский коэффициент основной стипендии составляет 15% основного размера стипендии УК=РС*15%

• Стипендия студентов, обучающихся на «4 и 5»Стипендия "4-5"=РС+УК

• Стипендия студентов, обучающихся на «5» на 10 % больше
  Стипендия «5»=Стипендия «4-5»*110%

• Социальная стипендия на 50% больше стипендии на «4и 5»
  Социальная стип.= Стипендия «4-5»*150%

Задание 2. Составьте ведомость на выдачу стипендии

1. Введите заголовок таблицы

2. Выделите ячейки шапки таблицы

3. Для оформления шапки таблицы используйте созданный стильФормат/Стиль/Шапка таблицы. После этого осуществляйте ввод заголовков столбцов.

4. Во второй столбец внесите фамилии

5. В третий столбец- вид получаемой стипендии

• 4 - если студент учится на «4 и 5»

• 5- если студент учится на «5»

• 6- если студент получает социальную стипендию

1. В четвертый столбец введите формулу, по которой определяется размер стипендии. При составлении формулы учитывайте условия :

Е сли вид стипендии =4,то взять размер стипендии из ячейки ,где указан размерстипендии «4-5» ( например,C3), иначе, если вид стипендии =5,то взять размер стипендии из ячейки, где указан размер стипендии «5» ( например,D3),иначе,если вид стипендии =6. то взять размер стипендии из ячейки, где указан размер «социальная стипендия»(например,E3),иначе 0.

1. При составлении формул используйте Мастер функций (кнопкуf_x)категория логические, функция ЕСЛИ

Замечание: Для продолжения условия не забывайте ставить курсор в окошко Значение, если ложь

ЕСЛИ (С7=4;С3;ЕСЛИ(С7=5;D3;ЕСЛИ(С7=6;E3;0)))

1. Скопируйте полученную формулу в ячейкиD8-D16 (Вы увидите, что результаты расчетов неверны)

2. Исправьте формулу в ячейке D7,предварительно «заморозив» ячейкиC3, D3, E3, т.к. адреса этих ячеек должны быть абсолютными, т. е. не должны менятьсяпри копировании . Можно воспользоватьсяклавишейF4 (поставить курсор перед именем ячейки и нажать на клавишу F4) или вручную в имя ячейки добавить знак $. Имена ячеек с абсолютной адресацией $C$3, $D$3, $E$3

3. Скопируйте исправленную формулу в ячейкиD8-D16 (Вы увидите, что результаты расчетов верны)

Практическая работа №3. Работа с базой данных

в MS EXCEL

Цели: Изучение технологии работы с базой данных в MSEXCEL: Сортировка данных в таблице по одному ключу, работа с несколькими ключами, работа с АВТОФИЛЬТРОМ. Составление итоговых отчетов

Задание 1.Создайте таблицу. Заполните столбец «средний балл», воспользовавшись функцией СРЗНАЧ категории СТАТИСТИЧЕСКИЕ мастера функций.

Задание 2. Сортировка по одному ключу. Отсортируйте данные в таблице по фамилиям (по возрастанию)

1. Выделите таблицу, включая заголовки

2. В меню Данныевыберите команду Сортировка

3. В раскрывающемся списке Сортировать по выберитеФамилия И.О.

4. Переключатель поставьте в положение По возрастанию

5. Нажмите ОК

Задание 3.Самостоятельно отсортируйте поиностранному языку (по возрастанию).

1. Скопируйте отсортированную таблицу на второй лист

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

Задание 4. Использование автофильтра. Заполните столбец «результаты зачисления» , считая проходным 3,5 балла

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

2. В менюДанные выберите команду Фильтр и в дополнительном меню Автофильтр. В каждой ячейке первой строки появитсястрелка, обозначающая раскрывающийся список

3. Щелкните по стрелке Средний балл

4. В раскрывающемся списке выберите Условие

5. В окне диалога Пользовательский фильтр установите больше или равно 3,5.

6. В столбце «Результат зачисления» внесите зачислен.

7. Распространите запись с помощью маркера заполнения

8. Завершите работу Автофильтра (Для завершения работы Автофильтра выберите в менюДанные команду Фильтр и дополнительное меню Автофильтр (отожмите кнопку))

9. В пустые ячейки столбца «Результат зачисления» внесите незачислен (или воспользуйтесь Автофильтром. Выделите таблицу на листе 1 Данные/Фильтр/Автофильтр. Средний балл Условие/меньше 3,5. Внесите незачислен и распространите . Завершите работу Автофильтра.)

Задание 5.Используя Автофильтр, получите список студентов , сдавших математику на «5».

1. Результат скопируйте на второй лист и введите название таблицы Сдавшие на 5

Задание 6.Составьте итоговый отчет «Количество поступающих на каждую специальность». Постройте и заполните полученными результатами таблицу.

MSEXCEL позволяет автоматически вычислять промежуточные итоги, т.е составлять итоговые отчеты. Предварительно данные должны быть отсортированы по какому-то столбцу.

1. Постройте таблицу «Итоговый отчет» на листе 3

Итоговый отчет

1. Отсортируйтеисходную таблицу на листе 1 в порядке возрастания кода специальности.

2. Выделитеотсортированную таблицу на листе1.

3. В менюДанные выберите Итоги. Откроется диалоговое окно Промежуточные итоги.

4. Для того, чтобы подвести итоги по каждому коду специальности отдельно в раскрывшемся списке При каждом изменении выберитекод специальности.

5. Убедитесь, что в поле Операция выбраноколичество.

6. Для того , чтобы найти количество поступающих на каждую специальность в поле Добавить итоги по установите флажок напротивкод специальности (УБЕРИТЕ НЕНУЖНЫЕ ФЛАЖКИ).

7. Проверьте, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки.

8. Нажмите ОК

9. Скопируйте таблицу с итоговым отчетом по количеству поступающих на лист 3. Введите название таблицы Итоговый отчет количество поступающих

10. Внеситеполученные промежуточные итоги в таблицу «Итоговый отчет» на листе 3

11. Удалите введенные промежуточные итоги в исходной таблице на листе 1 Для этого выделите исходную таблицу, выберитеДанные-Итоги и щелкните кнопкуУбрать все

Задание 7. Составьте итоговый отчет по среднему баллу по каждой специальности.

1. Выделите таблицу на листе1

Подсказка: Данные- Итоги -Промежуточные итоги. При каждом изменении Код специальности операция среднее . Добавить итоги по средний балл.

Проверить, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки. Нажать ОК. Скопировать таблицу с итоговым отчетом по среднему баллуна лист 3. Ввести название таблицыИтоговый отчет по среднему баллу

Внести полученные промежуточные итоги в таблицу «Итоговый отчет» на листе 3 . Удалить введенные промежуточные итоги в исходной таблице на листе 1. Для этого выделитьисходную таблицу, выбрать Данные-Итоги и щелкнуть кнопку Убрать все.

Задание 8.Используя Автофильтр, создайте список зачисленных.

1. Вставьтелист 4

2. В исходной таблице на листе 1 получите список зачисленных Данные/Фильтр/Автофильтр

3. Скопируйте и поместите на лист 4 . Полученную на четвертом листе таблицу озаглавьте «Список зачисленных». (Выделить первую строку- Вставка/Строка)

Задание 9.Сортировка по нескольким ключам

Отсортируйте данные таблицы «Список зачисленных» по кодуспециальности (по возрастанию), затем по убыванию среднего балла.

1. Выделите таблицу «Список зачисленных» на листе 4, включая заголовки

2. Выполните команду Данные-Сортировка

3. В окне диалога Сортировка выберите первый ключ сортировки (Сортировать по)код специальности (по возрастанию) и второй ключ (Затем по) – средний балл (по убыванию).

4. Вставьте лист 5

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

Задание 10.Отсортируйте таблицу «Список зачисленных» на листе 4 по трем ключам :

1 ключ-код специальности (по возрастанию), 2 ключ—Иностранный язык (по возрастанию), 3 ключ-Фамилия И.О(по возрастанию).

1. Скопируйте отсортированную таблицу на лист 5. Ведите название таблицы Сортировка по трем ключам

Задание 11. С помощью Автофильтра создайте ираспечатайте список нуждающихся в общежитии (из таблицы зачисленных на листе4), расположив их в алфавитном порядке (В полученном после Автофильтра списке сделать сортировку по фамилиям по возрастанию по первому ключу, по второму и третьему ключу- не сортировать).

1. Скопируйте отсортированную таблицу на лист 5. Ведите название таблицы Список нуждающихся в общежитии

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

1. Вставьте лист 6

2. Постройте таблицу Распределение по языкам на листе 6

Распределение по языкам

1. ОтсортируйтеСписок зачисленных на листе 4 по иностранному языку

2. Подведите итоги. Данные/Итоги. При каждом изменении иностранный язык,операция-количество, добавить итоги по иностранный язык.

3. Скопируйте таблицу с итогами распределения по языкам на лист 6 Введите название таблицы Итоги распределения по языкам

4. Внесите полученные результаты в таблицу Распределение по языкам

Практическая работа №4. Связи между файлами и консолидация данных в MSExcel

Цель: Изучение технологии связей между файлами и консолидации данных в MS Excel.

Задание 1.Подготовить файл по образцу и выполнить зада­ния, следуя указаниям:

Указания к выполнению

1.Заполнить столбец Выручка.

2.Подсчитать общую выручку для каждой турбазы, используя подведение итогов. Для этого: [Данные - Итоги]. В диалоговом окне Промежуточные итоги в поле При каждом изменении в указать Название турбазы, в поле Операция — Сумма, а в поле Добавить итоги по поставить флажок Выручка. При этом слева автоматически будет создана структура документа.

3.На рабочем листе отобразить общий итог по каждой турбазе. Для этого нажать кнопку Скрыть детали.

4. Отобразить все записи таблицы. Для этого нажать кнопку Отобразить детали.

5.Убрать промежуточные итоги. Для этого [Данные - Итоги]. В диалоговом окнеПромежуточные итоги нажать кнопкуУбрать все.

6.Сохранить работу в файле с именемТурбаза.

Задание 2. Открыть файл Турбаза и выполнить задания, следуя указаниям.

Указания к выполнению

1.Перенести данные по каждой турбазе на отдельный лист и преобразовать в соответствии с образцом.

2.Переименовать чистый лист в Отчет.

3. На этом листе подготовить консолидирующий отчет в следу­ющей последовательности:

– Внести заголовокОтчет о работе турбаз;

–Скопировать с любого листа на листОтчетстроку 3;

–На листе Отчетсделать активной ячейку АЗ (месяц);

–[Данные - Консолидация] в полеФункциявыбрать Сумма. Установить курсор на строку Ссылкаи добавить данные для консо­лидации. Для этого указать на лист для первой турбазы и выделить таблицу вместе с заголовками столбцов. Щелкнуть по кнопке Доба­витьв диалоговом окне Консолидация.Аналогично выбрать дан­ные для второй и третьей турбаз. В группеИспользовать в каче­стве именустановить флажки подписи верхней строки, значения левого столбцаисоздавать связи с исходными данными.

Отчет примет вид:

Задание 2.Открыть файл Турбаза и выполнить задания, следуя указаниям.

Указанияквыполнению

1. Подготовить сводную таблицу о работе трех турбаз. Для этого: [Данные–Своднаятаблица]. В диалоговом окне мастера сводных таблиц:

• Шаг 1 из 3 – убедиться, что выбран переключатель в списке или базе данных Microsoft Excel.

• Шаг 2 из 3 – выделить данные, используемые для построе-

ния сводных таблиц.

• Шаг 3 из 3 – нажать кнопку Макет.

• В открывшемся диалоговом окне Мастерсводныхтаблиц

и диаграмм – макет определить структуру сводной таблицы. Для этого перетащить кнопки Наименование в область Страница, Месяц – в область Строка, кнопку Цена – в область Столбец, а кнопку Выручка – в область Данные.

2.Выполнить группировку данных в сводной таблице. Для этого выделить строки июнь,июльавгуст, которая войдет в группу 1. В главном меню выбрать пункт[Данные–Группаиструктура – Группировать]. Появится группа 1, переименовать ее влето. Переименовать в столбце Месяц 2 май – весна, а сентябрь – осень.

Сводная таблица примет вид:

Список источников информации

1. Экономическая информатика./Под ред. В.В. Евдокимова.-СПб: Питер, 2018

2. Информатика для юристов и экономистов./Под ред. Симоновича С.В.-Учебник.-СПб: Питер, 2018

3. Левин А. Самоучитель работы на компьютере. – М.: Нолидж, 2017.

4. Левин А. Самоучитель работы в Windows. – М.: Нолидж, 2016.

5. Левин А. Самоучитель полезных программ. – СПб.: Питер, 2016.

6. Могилев А., Пак Н., Хеннер Е. Практикум по информатике. – М.: ИЦ «Академия», 2017.