МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ ПРАКТИЧЕСКИХ РАБОТ ПО ПРЕДМЕТУ «ИНФОРМАТИКА »

КГУ «Комплекс средняя школа-детский сад им. Т. Клименко»

МЕТОДИЧЕСКИЕ указания 

по выполнению практических работ по предмету «ИНФОРМАТИКА »

Работа с электронными таблицами Microsoft Excel 2010

Разработал: Горбанёв Евгений Владимирович

ОГЛАВЛЕНИЕ

Цель и задачи практического практикума. Методические указания……………………..……4

Практическая работа № 1. Создание и редактирование таблицы……………………………...6

Практическая работа № 2. Вычисления в MS Excel…………………………………………...…………11

Практическая работа № 3. Выполнение расчетов и оптимизация изображения таблицы…..14

Практическая работа № 4. Визуализация данных…………………………………………………….…..17

Практическая работа № 5. Использование таблицы в качестве базы данных…………………...23

Практическая работа № 6. Таблицы. Подведение промежуточных итогов. Сводные таблицы…28

Список Источников………………………………………………………………………………….…………..35

3

ЦЕЛЬ И ЗАДАЧИ ПРАКТИКУМА.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Приложение Excel входит в состав всех выпусков пакета Microsoft Office 2010 и предназначено для работы с электронными таблицами. Специалисты считают MS Excel лидером среди программных средств, позволяющих созда-вать и обрабатывать электронные таблицы. За почти тридцать лет своего суще-ствования (первая версия приложения была выпущена в 1985 году) Excel полу-чил значительное расширение функциональных возможностей, а его внешний вид не раз перевоплощался. В настоящей версии интерфейс построен с исполь-зованием ленты и вкладок команд, оформленных в едином стиле с другими программами пакета Microsoft Office 2010.

Excel 2010 — самая мощная и интуитивно понятная версия приложения Excel за всю историю развития приложения, разработанная специально для то-го, чтобы предоставлять разнообразные инструменты для вычислений, анализа и управления данными, а также визуализации результатов.

Возможности электронных таблиц Excel очень широки. К их числу относят-

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

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

Основной целью практических работ является формирование практиче-ских умений и навыков, необходимых для эффективной работы в MS Excel 2010.

Основными задачами лабораторного практикума является изучение ос-новных методов и приемов редактирования и форматирования данных, ис-пользования формул и функций, построения диаграмм и анализа данных. Со-держание практикума охватывает все темы, связанные с приобретением знаний, умений и навыков работы с Microsoft Excel 2010.

Практикум состоит из 6 работ. Первая работа посвящена изучению базовых приемов создания, заполнения и редактирования таблиц Excel.

4

Во второй работе приведены задания для проведения различных вычислений с использованием библиотечных функций. В этой же работе осваивается важное понятие электронных таблиц – адресация ячеек. Третья работа нацелена на выполнение экономических расчетов и форматирование таблиц. В четвертой работе изучаются приемы визуализации данных: построение диаграмм и графиков, а также создание спарклайнов – нововведение Microsoft Excel 2010. Пятая и шестая работы посвящены организации информации в электронной таблице в форме простой однотабличной базы данных и дальнейшей ее обработке: сортировке, фильтрации, подсчету итогов, построению сводных таблиц и диаграмм. Отдельные задания лабораторных работ снабжены указа-ниями, в которых приводится вспомогательная информация для выполнения этих заданий.

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

До занятия необходимо ознакомиться с соответствующим теоретическим материалом по теме практических работы. Во время занятия в компьютерном классе самостоятельно выполняются задания, производится разбор ситуаций, вызвавших затруднения. Результаты работы сохраняются в виде файла в папке студента. После выполнения всех заданий лабораторной работы и собеседова-ния по ней с преподавателем студенту выставляется зачет по данной работе.

5

Практическая РАБОТА № 1

СОЗДАНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦЫ

1. Введите данные на рабочий лист (рис. 1.1).

Рис. 1.1

Указание. Для копирования и заполнения данных в смежных ячейках мож-но воспользоваться маркером заполнения. Это черный квадрат в правом ниж-

нем углу выделенных ячеек . При наведении на маркер указатель

мыши принимает вид черного креста. Для заполнения выделите ячейки, кото-рые станут источником данных, а затем протяните маркер вниз, вверх или в стороны на ячейки, которые необходимо заполнить. Для копирования элемен-тов списка (месяцы, дни недели и др.) при протаскивании мышью маркера удерживайте нажатой клавишу Ctrl. Для выбора варианта заполнения можно протягивать маркер правой кнопкой мыши.

1. Отредактируйте заголовки колонок: Категория измените на Товар, Цена измените на Цена, р.

1. Разместите между строками с информацией о шоколаде и кофе две пустых

строки и введите в них данные (диапазон А6:Е7):

1. Вставьте между колонками Цена и Поставщик колонку Количество и запол-ните ее данными:

6

Количество

230

560

320

280

244

488

300

200

576

288

350

1. Разместите колонку Поставщик после колонки Товар.

Указание. Выделите столбец Поставщик, наведите указатель мыши на гра-

ницу выделения, когда он примет вид , перетащите этот столбец правой

кнопкой мыши на столбец Цена и в появившемся меню выберите команду Сдвинуть вправо и переместить.

6.   Дополните таблицу (диапазон A13:F16) следующей информацией:

7.    Вставьте перед колонкой Поступление пустую колонку и введите заголовок № п/п.

8.    Используя маркер заполнения, пронумеруйте строки таблицы цифрами от 1 до 15 в колонке № п/п.

9.    Удалите из таблицы строку под номером 4 в колонке № п/п и исправьте нумерацию строк в данной колонке.

10. Используя команду Главная  Редактирование  Найти и выде-лить  Заменить, в колонке Поставщик замените Ланта на Лавита.

11. Разместите над заголовками колонок две пустые строки и введите в ячейку А1 название таблицы: Реализация товаров со склада № 22.

12. Используя команду Главная  Выравнивание  Объединить и поме-стить в центре, разместите заголовок по центру колонок.

13. В ячейку А2 введите слово Дата, в ячейку В2 введите текущую дату, в ячейку Е2 введите слово Время, в ячейку F2 введите текущее время.

7

1. Нарисуйте границы в таблице.

1. Сравните созданную Вами таблицу с таблицей, представленной на рис. 1.2. При наличии расхождений внесите исправления.

Рис. 1.2

16. Установите параметры страницы: ориентация – альбомная; верхнее и ниж-нее поле – 2 см, левое поле – 3 см, правое поле – 1 см, центрирование на странице – горизонтальное и вертикальное.

17. С помощью команды Вставка  Текст  Колонтитулы создайте для рабоче-го листа верхний и нижний колонтитулы. В верхнем колонтитуле в левой ча-сти напечатайте название лабораторной работы, а в правой Вашу фамилию

• инициалы. В нижнем колонтитуле в центре укажите текущую страницу из общего количества страниц.

18. Выведите таблицу на экран в режиме предварительного просмотра (коман-да Файл  Печать).

1. Переименуйте Лист 1 на Таблица.

1. Выделите колонки Товар, Цена, р., Количество и скопируйте их на Лист 2.

1. После Листа 3 вставьте новый лист.

1. Создайте копию рабочего листа Таблица.

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

8

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

Переместить или скопировать, в раскрывающемся списке Переместить выбранные листы в книгу укажите Новая книга, ^ Создать копию.

1. Сохраните созданную рабочую книгу в своей папке на диске под именем

Фамилия_Работа_1.

1. Перейдите на Лист 3 рабочей книги.

1. Переместите табличный курсор:

а)  в последнюю строку рабочего листа (сочетание клавиш Ctrl + );

б) в последний правый столбец рабочего листа (Ctrl + ) и запишите в ак-тивную ячейку ее адрес (для возвращения в начало рабочего листа нажмите Ctrl + Home);

в)  в ячейку S3456 (клавиша F5).

1. Выполните поочередно выделение с помощью мыши: а) диапазона C3:H9;

б) диапазонов A1:A5, C3:E3, H2:I8; в) строк 4,5,6,7;

г) столбцов B, C, F, G; д) строк с 18 по 48;

е) всех ячеек рабочего листа; ж) столбца XEV;

з)   строки 10000.

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

Главная  Редактирование  Найти и выделить  Выделение груп-пы ячеек.

1. Заполните строку значениями от 0 до 0,5 с шагом 0,05, используя маркер

заполнения.

0       0,05         0,1       0,15     0,2       0,25     0,3       0,35     0,4       0,45     0,5

Указание. Введите в соседние ячейки два первых значения. Выделите их и протяните за маркер заполнения.

30. Заполните строку значениями арифметической прогрессии от –1 до 0 с ша-гом 0,1, используя команду Главная  Редактирование  Заполнить  Прогрессия.

–1           –0,9      –0,8      –0,7      –0,6      –0,5      –0,4      –0,3      –0,2      –0,1        0

9

1. Заполните столбец значениями геометрической прогрессии:

1

2

4

8

16

32

64

128

256

1. Заполните данными Лист 4, используя маркер заполнения и команду Про-

грессия.

буфер обмена и вставить в произвольном месте рабочего листа с помощью ко-манды Главная  Буфер обмена  Вставить  Специальная вставка.

1. Сохраните рабочую книгу.

36. Покажите результат Вашей работы преподавателю.

10

Практическая РАБОТА № 2

ВЫЧИСЛЕНИЯ В MS EXCEL

Задание 1. Заполните данными таблицу и выполните вычисление в ней.

Задание 2. Заполните исходными данными таблицу. Вычислите площади прямоугольников по заданным ширине и длине сторон.

2

3

4

5

6

7

8

9

10

Задание 3. В ячейках введены Фамилия, Имя, Отчество. Напишите формулу для вывода в ячейке фамилии и инициалов в виде Фамилия И. О.

Указание. В формуле используйте операцию объединения строк & и функ-цию ЛЕВСИМВ().

11

Задание 4. Вычислите сумму и произведение цифр двузначного числа.

Двузначное число

1-я цифра

2-я цифра

Сумма цифр

Произведение цифр

Указание. Используйте функции ЦЕЛОЕ() для вычисления количества десят-ков в двузначном числе (1 цифра) и ОСТАТ() для вычисления единиц (2 цифра).

Задание 5. Используя функцию СЛУЧМЕЖДУ(), заполните диапазон из 4 строк и 5 столбцов случайными числами от –20 до 20.Ниже полученного диапа-зона вычислите:

а)   сумму всех чисел диапазона;

б)   сумму чисел второй строки;

в)   среднее значение третьего столбца;

г)    минимальное значение первой строки;

д)   максимальное значение пятого столбца;

е)   количество чисел в диапазоне;

ж) сумму квадратов чисел первого столбца.

Задание 6. Определите, в какой день недели (понедельник, вторник, …) Вы родились.

Указание. 1-й способ. В ячейку введите дату. В контекстном меню ячейки выберите команду Формат ячеек…  Число  (все форматы) и в поле Тип введите ДДДД.

2-й способ. В ячейку введите дату. В соседней ячейке воспользуйтесь функ-цией ТЕКСТ().

12

Задание 7. Вычислите количество полных прожитых лет на текущий день.

Дата рождения

Возраст

Указание. Для вычисления возраста человека, день рождения которого за-писан в ячейке А1, используется формула:

Задание 8. Дан протокол соревнования по конькобежному спорту:

По данному протоколу определите время пробега дистанции для каждого спортсмена в минутах.

Указание. Для отображения количества минут между двумя моментами времени (аналогичный прием подходит для часов и секунд) установите формат [мм]. В контекстном меню ячейки выберите команду Формат ячеек  Число

• (все форматы) и в поле Тип введите [мм].

Задание 9. Имеются 2 таблицы с данными.

• 30

• 28

• 26

• 10

• 48

З56

Используя функции вертикального поиска ВПР() для цены и горизонтально-го поиска ГПР() для количества, сформируйте следующую таблицу и вычислите стоимость товара.

Товар   Цена Количество Стоимость

А

Д

Ж

Б

В

З

Г

Покажите результат Вашей работы преподавателю.

13

Практическая РАБОТА № 3

ВЫПОЛНЕНИЕ РАСЧЕТОВ И ОПТИМИЗАЦИЯ ИЗОБРАЖЕНИЯ ТАБЛИЦЫ

1. Введите данные на рабочий лист (рис. 3.1).

Рис. 3.1

1. Вставьте формулы для вычислений в столбцах Районный коэффициент,

Начислено, Подоходный налог, Сумма к выдаче (в квадратных скобках указаны номера столбцов):

1. = [2] ∙ k

1. = [2] + [4]

1. = ([5] – [3]) ∙ n

1. = [5] – [6]

1. В последней строке вставьте формулы для вычисления итоговых сумм по столбцам Подоходный налог и Сумма к выдаче.

1. Ниже таблицы вставьте формулы для вычисления: а) максимальной суммы к выдаче; 

                               б) среднего оклада; 

                               в) минимального налога;

                        г) количества рабочих, оклады которых превышают 16 000 руб. (функция СЧЁТЕСЛИ());

14

д) суммарный подоходный налог рабочих, имеющих налоговые вычеты (функция СУММЕСЛИ());

е) суммарный подоходный налог рабочих, оклады которых превышают 16 000 руб. и не имеющих налоговые вычеты (функция СУМ-МЕСЛИМН()).

1. Введите поясняющую информацию к формулам.

1. Отобразите значения во всей таблице в денежном формате с двумя знака-ми после десятичной запятой.

1. Установите в итоговой строке заливку ячеек черным цветом, белый цвет шрифта, полужирное начертание.

1. Отформатируйте таблицу согласно образцу, представленному на рис. 3.2.

Рис. 3.2

1. Сохраните созданную Вами рабочую книгу в своей папке на рабочем диске под именем Фамилия_Работа_3.

1. Скопируйте лист с именем Лист 1.

1. Переименуйте Лист 1 на лист с именем Ведомость, а Лист 1(2) на Форму-

лы.

15

1. На листе Формулы отобразите формулы в ячейках таблицы.

1. Скопируйте с листа Ведомость на Лист 3 столбцы Ф.И.О., Сумма к выдаче.

Для вставки из буфера обмена используйте специальную вставку (команда

Главная → Буфер обмена → Вставить → Специальная вставка → ^ значения).

1. Добавьте к таблице поля Сообщение о надбавке, Величина надбавки, Ито-

говая сумма. Введите заголовок таблицы Расчет надбавки. Введите нуме-рацию столбцов (рис. 3.3).

1. Введите в столбец Сообщение о надбавке формулу, которая выводит со-общение Да, если сумма к выдаче составляет менее 20 000 р., и Нет в про-тивном случае: =ЕСЛИ(В4<20000;"Да";"Нет").

1. Введите в столбец Величина надбавки формулу, которая выводит сумму надбавки равную 20% от суммы к выдаче, если данная сумма составляет менее 20 000 р., и 0 в противном случае.

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

1. Сравните полученную Вами таблицу с таблицей, представленной на рис. 3.3. При расхождении откорректируйте таблицу.

Рис. 3.3

1. Покажите результат Вашей работы преподавателю.

16

Практическая РАБОТА № 4

ВИЗУАЛИЗАЦИЯ ДАННЫХ

1. Ниже таблицы вставьте диаграмму. Тип диаграммы – точечная с гладкими кривыми и маркерами.

3.    Установите цвета линий графика и маркеров: для – черный, для – темно-синий. Измените тип маркеров на графиках (рис. 4.1).

1. Добавьте название диаграммы. Отобразите вертикальные и горизонталь-ные линии сетки.

1. Установите отображение значений горизонтальной оси на отрезке от –3 до 3.

1. Сравните построенную Вами диаграмму с представленной на рис. 4.1. При наличии расхождений между ними внесите в Вашу диаграмму необходи-мые изменения.

Рис. 4.1

17

Задание 2. Построение диаграмм

1. Введите данные на Лист 2.

1. Скопируйте их на Лист 3.

1. На Листе 2 ниже таблицы постройте диаграмму график с маркерами.

1. Увеличьте размер диаграммы.

1. Измените для ряда Продукты питания тип диаграммы на гистограмму с группировкой (рис. 4.2).

1. Установите для гистограммы ряда Продукты питания градиентную заливку «Рассвет».

Рис. 4.2

18

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

1. Вставьте название диаграммы «Динамика расходов за первое полугодие».

1. Установите вертикальное выравнивание подписей на горизонтальной оси категорий.

1. Сравните построенную Вами диаграмму с представленной на рис. 4.2. При наличии расхождений между ними внесите в Вашу диаграмму необходи-мые изменения.

1. На этом же рабочем листе для исходных данных постройте линейчатую диаграмму с накоплениями.

1. Установите размеры диаграммы: высота – 8 см., ширина – 20 см.

1. Вставьте название диаграммы и подписи данных (рис. 4.3).

1. Сравните построенную Вами диаграмму с представленной на рис. 4.3. При наличии расхождений между ними внесите в Вашу диаграмму необходи-мые изменения.

Рис. 4.3

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

1. Вставьте название диаграммы и подписи данных.

19

Рис. 4.4

1. Сравните построенную Вами диаграмму с представленной на рис. 4.4. При наличии расхождений между ними внесите в Вашу диаграмму необходи-мые изменения.

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

1. С помощью команды Конструктор  Переместить диаграмму располо-

жите ее на отдельном листе.

1. Отформатируйте область диаграммы: граница – сплошная линия темно-синего цвета, шириной 2пт. с тенью.

1. Удалите легенду.

1. Измените подписи данных: у каждого сектора диаграммы отобразите название месяца и долю в процентах от общих расходов за первое полуго-дие (рис. 4.5).

1. Сектор с максимальными расходами расположите отдельно от остальных секторов.

1. Сравните построенную диаграмму с рис. 4.5. Покажите результаты Вашей работы преподавателю.

20

Рис. 4.5

Задание 3. Построение спарклайнов

1. В таблице на Листе 3 вычислите ежемесячные расходы, добавьте строку ежемесячных доходов и определите ежемесячные накопления (рис. 4.6).

1. Добавьте в таблицу столбец Тенденции и постройте в ячейках этого столбца спарклайны следующих типов: для расходов – спарклайн График, для до-ходов – спарклайн Столбец (Гистограмма), для накоплений – спарклайн

Выигрыш/проигрыш.

1. Измените высоту строк и ширину столбца со спарклайнами для наглядного отображения тенденций.

1. Отметьте маркерами на графиках спарклайнов минимальные и максималь-ные значения.

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

1. Сравните построенный Вами результат с представленным на рис. 4.6. При наличии расхождений между ними внесите необходимые изменения.

1. Покажите результаты Вашей работы преподавателю.

Рис. 4.6

22

Практическая РАБОТА № 5

ИСПОЛЬЗОВАНИЕ ТАБЛИЦЫ В КАЧЕСТВЕ БАЗЫ ДАННЫХ

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

Рис. 5.1

1. Сохраните созданную рабочую книгу в своей папке на рабочем диске под именем Фамилия_Работа_5.

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

ные  Сортировка и фильтр  Сортировка):

а) по фамилиям заказчиков в алфавитном порядке; б) по стоимости заказов в убывающем порядке;

в)  по наименованию товаров в алфавитном порядке, а внутри каждой по-лученной группы по количеству единиц товара в заказе по возрастанию; г)  по фамилиям заказчиков в алфавитном порядке, а внутри каждой полу-

ченной группы по дате заказа.

23

1. С помощью фильтра (команда Данные  Сортировка и фильтр  Фильтр) получите выборку данных в таблице по следующим условиям отбора:

а)   определить все заказы Михайловой Н. А.

б)   определить заказы за период с 03.05.14, цена единицы товара в которых

более 3000 руб.

в)   определить записи с фамилиями заказчиков, начинающихся на букву Б

или М.

г)   выбрать заказы пароварок за апрель.

24

д)   определить заказы за месяц май, количество единиц товара в которых

составляет от 10 до 20.

е)   определить заказ с максимальной стоимостью.

ж) определить первые четыре заказа с наибольшей стоимостью.

з)   выбрать заказы, цена товаров которых выше средней цены по ведомо-

сти.

25

1. С помощью расширенного фильтра (команда Данные → Сортировка и фильтр → Дополнительно), получите выборку данных в таблице согласно приведенным условиям (критерии отбора расширенного фильтра и резуль-таты фильтрации сохраните на рабочем листе):

а) определить заказы Седовой Н. Р., цена за единицу товара в которых бо-лее 2000 руб.

б)   определить заказчиков, у которых в заказе количество единиц товара

более 15 или цена единицы товара менее 1000 руб.

в)   выбрать заказы пароварки за апрель.

г)   определить заказы, в которых количество единиц товара больше коли-чества оплаченных единиц.

26

ж) определить заказы, цена единицы товара в которых превышает сред-

нюю цену по ведомости или меньше 800 руб.