Методическое пособие по работе в табличном редакторе для студентов СПО

ГБОУ СПО НСО НКТТ им Н. А. Лунина

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

по выполнению практических работ по дисциплине

«ИНФОРМАТИКА И ИКТ»

Работа с электронными таблицами 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. Для выбора варианта заполнения можно протягивать маркер правой кнопкой мыши.

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

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

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

Февраль	Сок	55 Ланта	Май
Март	Сок	55 Парус	Май

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

6

Количество

230

560

320

280

244

488

300

200

576

288

350

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

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

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

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

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

Февраль	Шоколад	Ланта	85	200	Апрель
Февраль	Сок	Парус	45	200	Май
Февраль	Кофе	Марс	400	200	Июнь
Февраль	Печенье	Марс	48	200	Июль

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

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

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

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

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

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

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

7

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

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

Рис. 1.2

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

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

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

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

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

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

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

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

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

8

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

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

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

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

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

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

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

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

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

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

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

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

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

з)  строки 10000.

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

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

29.   Заполните строку значениями от 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

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

1

2

4

8

16

32

64

128

256

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

грессия.

33.	Введите значения элементов матрицы на рабочий лист.
		1	1	1	1	1
		2	2	2	2	2
		3	3	3	3	3
		4	4	4	4	4
34.	Транспонируйте матрицу.
	Указание. Для транспонирования матрицы ее необходимо скопировать в

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

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

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

10

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

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

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

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

Ширина

				Длина
2	3	4	5	6	7	8	9	10

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. Дан протокол соревнования по конькобежному спорту:

Спортсмен	А	Б	В	Г
Старт	10:15	10:10	10:05	10:20
Финиш	10:45	10:25	10:28	10:46
Время (мин)

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

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

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

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

Товар	Цена	Товар	Д	В	А	Е		З	Б	Ж	Г
А	36	Количество	15	50		20	35	72	38	40	65

Б                         30

В                         28

Г                         26

Д                         10

Ж                        48

З56

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

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

А

Д

Ж

Б

В

З

Г

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

13

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

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

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

Рис. 3.1

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

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

[4]  = [2] ∙ k

[5]  = [2] + [4]

[6]  = ([5] – [3]) ∙ n

[7]  = [5] – [6]

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

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

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

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

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

14

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

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

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

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

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

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

Рис. 3.2

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

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

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

лы.

15

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

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

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

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

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

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

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

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

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

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

Рис. 3.3

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

16

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

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

	Задание 1. Построение графиков математических функций
			на отрезке [–3; 3] с шагом 0,5. При заполнении используйте форму-
	лы для вычисления					.
x		-3	-2,5	-2		-1,5	-1	-0,5	0	0,5	1	1,5		2	2,5	3
y1		8	5,6569	4		2,8284	2	1,4142	1	0,7071	0,5	0,3536		0,25	0,1768	0,125
y2		0,125	0,1768	0,25		0,3536	0,5	0,7071	1	1,4142	2	2,8284		4	5,6569	8

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

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

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

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

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

Рис. 4.1

17

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

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

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

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

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

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

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

Рис. 4.2

18

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

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

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

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

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

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

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

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

Рис. 4.3

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

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

19

Рис. 4.4

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

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

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

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

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

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

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

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

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

20

21

Рис. 4.5

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

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

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

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

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

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

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

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

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

Рис. 4.6

22

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

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

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

Рис. 5.1

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

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

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

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

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

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

23

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

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

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Михайлов Н. А.	Кофеварка	17.05.14	12	10	1200	12000
Михайлов Н. А.	Мультиварка	07.06.14	5	5	4200	21000
Михайлов Н. А.	Блендер	29.06.14	10	12	2300	27600

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

более 3000 руб.

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Белых А. П.	Пароварка	24.05.14	12	12	3100	37200
Михайлов Н. А.	Мультиварка	07.06.14	5	5	4200	21000
Седова Н. Р.	Мультиварка	03.06.14	22	20	4200	84000

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

или М.

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Белых А. П.	Тостер	22.04.14	10	8	950	7600
Белых А. П.	Чайник	16.05.14	24	24	2100	50400
Белых А. П.	Пароварка	24.05.14	12	12	3100	37200
Михайлов Н. А.	Кофеварка	17.05.14	12	10	1200	12000
Михайлов Н. А.	Мультиварка	07.06.14	5	5	4200	21000
Михайлов Н. А.	Блендер	29.06.14	10	12	2300	27600

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

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Зотова А. Ф.	Пароварка	06.04.14	10	10	3100	31000
Седова Н. Р.	Пароварка	10.04.14	18	16	3100	49600

24

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

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

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Белых А. П.	Пароварка	24.05.14	12	12	3100	37200
Зотова А. Ф.	Миксер	11.05.14	15	18	600	10800
Михайлов Н. А.	Кофеварка	17.05.14	12	10	1200	12000
Седова Н. Р.	Кофеварка	03.05.14	15	15	1200	18000
Седова Н. Р.	Блендер	27.05.14	16	20	2300	46000

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

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Седова Н. Р.	Мультиварка	03.06.14	22	20	4200	84000

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

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Белых А. П.	Чайник	16.05.14	24	24	2100	50400
Седова Н. Р.	Пароварка	10.04.14	18	16	3100	49600
Седова Н. Р.	Блендер	27.05.14	16	20	2300	46000
Седова Н. Р.	Мультиварка	03.06.14	22	20	4200	84000

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

сти.

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Белых А. П.	Пароварка	24.05.14	12	12	3100	37200
Зотова А. Ф.	Пароварка	06.04.14	10	10	3100	31000
Михайлов Н. А.	Мультиварка	07.06.14	5	5	4200	21000
Михайлов Н. А.	Блендер	29.06.14	10	12	2300	27600
Седова Н. Р.	Пароварка	10.04.14	18	16	3100	49600
Седова Н. Р.	Блендер	27.05.14	16	20	2300	46000
Седова Н. Р.	Мультиварка	03.06.14	22	20	4200	84000

25

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

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

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Седова Н. Р.	Пароварка	10.04.14	18	16	3100	49600
Седова Н. Р.	Блендер	27.05.14	16	20	2300	46000
Седова Н. Р.	Мультиварка	03.06.14	22	20	4200	84000

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

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

Ф.И.О.	Наименование	Количество	Цена
		единиц товара в	единицы
заказчика	товара
		заказе	товара, руб.
Белых А. П.	Тостер	10	950
Белых А. П.	Чайник	24	2100
Зотова А. Ф.	Миксер	15	600
Седова Н. Р.	Пароварка	18	3100
Седова Н. Р.	Миксер	10	600
Седова Н. Р.	Блендер	16	2300
Седова Н. Р.	Кофемолка	8	900
Седова Н. Р.	Мультиварка	22	4200

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

Ф.И.О.	Наименование	Дата	Цена	Стоимость
			единицы
заказчика	товара	заказа		заказа, руб.
			товара, руб.
Зотова А. Ф.	Пароварка	06.04.14	3100	31000
Седова Н. Р.	Пароварка	10.04.14	3100	49600

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

		Количество	Количество
Ф.И.О.	Наименование		оплаченных
		единиц товара
заказчика	товара		единиц товара
		в заказе
			в заказе
Белых А. П.	Тостер	10	8
Михайлов Н. А.	Кофеварка	12	10
Седова Н. Р.	Пароварка	18	16
Седова Н. Р.	Мультиварка	22	20

26

д)  определить заказы за вторую половину мая или заказы,						количество
единиц товара в которых более 15.
			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Белых А. П.	Чайник	16.05.14	24	24	2100	50400
Белых А. П.	Пароварка	24.05.14	12	12	3100	37200
Михайлов Н. А.	Кофеварка	17.05.14	12	10	1200	12000
Седова Н. Р.	Пароварка	10.04.14	18	16	3100	49600
Седова Н. Р.	Блендер	27.05.14	16	20	2300	46000
Седова Н. Р.	Кофемолка	29.05.14	8	8	900	7200
Седова Н. Р.	Мультиварка	03.06.14	22	20	4200	84000
е)  определить заказы,		количество оплаченных единиц товара в которых
менее 16. Из списка исключить кофеварки и кофемолки.
			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Белых А. П.	Тостер	22.04.14	10	8	950	7600
Белых А. П.	Пароварка	24.05.14	12	12	3100	37200
Зотова А. Ф.	Пароварка	06.04.14	10	10	3100	31000
Зотова А. Ф.	Чайник	28.06.14	8	10	2100	21000
Михайлов Н. А.	Мультиварка	07.06.14	5	5	4200	21000
Михайлов Н. А.	Блендер	29.06.14	10	12	2300	27600
Седова Н. Р.	Миксер	26.04.14	10	10	600	6000

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

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

			Количество	Количество	Цена
Ф.И.О.	Наименование	Дата		оплаченных		Стоимость
			единиц товара		единицы
заказчика	товара	заказа		единиц товара		заказа, руб.
			в заказе		товара, руб.
				в заказе
Белых А. П.	Пароварка	24.05.14	12	12	3100	37200
Зотова А. Ф.	Пароварка	06.04.14	10	10	3100	31000
Зотова А. Ф.	Миксер	11.05.14	15	18	600	10800
Михайлов Н. А.	Мультиварка	07.06.14	5	5	4200	21000
Михайлов Н. А.	Блендер	29.06.14	10	12	2300	27600
Седова Н. Р.	Пароварка	10.04.14	18	16	3100	49600
Седова Н. Р.	Миксер	26.04.14	10	10	600	6000
Седова Н. Р.	Блендер	27.05.14	16	20	2300	46000
Седова Н. Р.	Мультиварка	03.06.14	22	20	4200	84000

6.       Сохраните рабочую книгу. Покажите результат Вашей работы преподавате-лю.

27

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

ТАБЛИЦЫ. ПОДВЕДЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ. СВОДНЫЕ ТАБЛИЦЫ

1.       Используя операции копирования и заполнения, введите данные на рабо-чий лист (рис. 6.1).

Рис. 6.1

28

2.       Преобразуйте введенные данные в таблицу (команда Вставка  Таблицы

 Таблица).

3.       Последовательно выполните сортировку в таблице, используя кнопки фильтра:

а)  по регионам в алфавитном порядке;

б)  по плановым показателям от максимального к минимальному;

в) по фактическим показателям от минимального к максимальному; г) по городам в алфавитном порядке.

4.       Добавьте в таблицу столбец Процент выполнения и вычислите значения в

нем по формуле                           . Отобразите результат с двумя знаками после за-

пятой.

5.       В режиме Работа с таблицами с помощью команды Конструктор → Па-

раметры стилей таблицы → ^ Строка итогов вставьте строку с итого-

выми значениями.

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

7.       На Листе 2 создайте таблицу (рис. 6.2).

Город

План

Факт

 Процент

выполнения

Анапа

Владивосток

Красноярск

Москва

Новосибирск

Хабаровск

Рис. 6.2

8.       В исходной таблице, используя кнопки фильтра, последовательно отобра-зите итоги по каждому городу и скопируйте их в новую таблицу на Листе 2. Для вставки из буфера обмена используйте команду Специальная вставка

→ Значения.

9.       Снимите фильтр с поля Город.

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

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

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

29

13.   Уберите строку итогов и преобразуйте таблицу в обычный диапазон с по-мощью команд контекстной вкладки Конструктор.

14.   Удалите столбец Процент выполнения.

15.   Используя  команду  Данные   Структура   Промежуточный  итог,

определите итоговые плановые и фактические продажи для каждого квар-тала (рис. 6.3).

Рис. 6.3

30

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

17.   Отмените вычисление итоговых значений.

18.   Определите итоговые плановые и фактические продажи для каждого города.

19.   С помощью кнопок структуры 1, 2, 3 или +/–, расположенных слева от таб-лицы, установите отображение итогов по городам (рис. 6.4).

Рис. 6.4

20.   Отмените вычисление итоговых значений.

21.   Определите итоговые плановые и фактические продажи для каждого реги-она и количество продаж в регионе (рис. 6.5).

Рис. 6.5

31

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

23.   Отмените вычисление итоговых значений.

24.   На новом листе создайте сводную таблицу (команда Вставка  Таблицы

®      Сводные таблицы) с данными о фактических продажах для каждого

города по кварталам (рис. 6.6).

25.   Для отображения наименования полей используйте команду Конструктор

 Макет отчета  Показать в табличной форме.

Рис. 6.6

26.   Для данных в сводной таблицы установите денежный формат.

27.   Не изменяя структуру сводной таблицы, с помощью команды Параметры  Активное поле  Параметры поля отобразите максимальные факти-ческие продажи для каждого города по кварталам (рис. 6.7).

Рис. 6.7

32

28.   На новом листе рабочей книги создайте сводную диаграмму, отображаю-щую плановые продажи по регионам для каждого месяца (рис. 6.8).

Рис. 6.8

29.   На новом листе рабочей книги создайте сводную таблицу с фильтром по кварталу (рис. 6.9).

Рис. 6.9

33

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

31.   На новом листе рабочей книги создайте сводную таблицу фактических про-даж по месяцам для каждого квартала (рис. 6.10).

32.   Добавьте срез по городам с помощью команды Параметры  Сортиров-

ка и фильтр  Вставить срез.

Рис. 6.10

33.   Используя срез, отобразите фактические продажи для города Хабаровска.

34.   Сохраните рабочую книгу. Покажите результаты Вашей работы преподава-телю.

34

БИБЛИОГРАФИЧЕСКИЙ СПИСОК

1.     Уокенбах, Дж. Microsoft Excel 2010. Библия пользователя. – М. : Вильямс, 2011. – 912 с.

2.     Уокенбах, Дж. Формулы в Microsoft Excel 2010. – М. : Вильямс, 2011. – 704 с.

3.     Иванов, И. Microsoft Excel 2010 для квалифицированного пользователя

/   И. Иванов. – М.: Академия АЙТИ, 2011. – 244 с.

4.     Фрай, К. Д. Microsoft Office 2010. Русская версия / К. Д. Фрай, Дж. Кокс, Дж. Ламберт. – М.: ЭКОМ Паблишерз, 2011. – 800 с. – (Серия «Шаг за ша-гом»).

5.     Пташинский, В. Excel 2010 для начинающих / В. Пташинский. – М. : Эксмо, 2013. – 288 с.

6.     Долженков, В. Microsoft Excel 2010. В подлиннике / В. Долженков, А. Стучен-ков. – СПб. : БХВ-Петербург, 2011. – 816 с.

7.     Долженков, В. Самоучитель Excel 2010 / В. Долженков, А. Стученков. – СПб.: БХВ-Петербург, 2011. – 382 с.

8.     Сергеев, А. Microsoft Office 2010. Самоучитель / А. Сергеев. – М.: Вильямс, 2010. – 624 с.

9.     Волков, В. Понятный самоучитель Excel 2010 / В. Волков. – СПб.: Питер, 2010.– 256 с.

10.   Сурядный, А. Microsoft Office 2010 / А. Сурядный. – М.: АСТ: Астрель, 2011.– 640 с.

11.   Берман, Н. Визуализация данных в MS Excel 2010 : учеб.пособие / Н. Д.

Берман. – Хабаровск: Изд-во Тихоокеан. гос. ун-та, 2014. – 72 с.

12.   Microsoft Office: методические указания по выполнению лабораторных работ по информатике для студентов всех специальностей / сост. Ю. В. Любицкий, Н. И. Шадрина. – Хабаровск: Изд-во Тихоокеан. гос. ун-та, 2009. – 47 с.

13.   Спиридонов, О. Работа в Microsoft Excel 2010 // НОУ «ИНТУИТ» [Электронный  ресурс].  –  Режим  доступа http://www.intuit.ru/studies/courses/613/469/info (дата обращения: 10.07.2014)

14.   Справка и инструкции по Excel // Поддержка по Microsoft Office [Электронный ресурс]. – Режим доступа: http://office.microsoft.com/ru-ru/excel-help (дата обращения: 14.08.2014)

35