Kabachenko&Drig

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

Самостоятельная работа.

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

2.Для таблиц начислений за все месяцы добавьте столбец Выдано на руки (=Начислено — Подоходный налог).

3.Создайте три листа: 1-й квартал (2), 2-й квартал (2), 1-е полугодие (2), где разместите консолидированные дан-

ные по трем столбцам - Начислено, Подоходный налог и Выдано на руки.

Практическая работа 11 Связи между листами

Цель работы: Изучить возможности взаимодействия данных на различных листах электронной таблицы.

Задача.

Исходя из данных о среднедушевом потреблении основных продуктов питания населения двух стран - России и Германии,

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

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

·Переименуйте первый лист Рабочей книги Excel, задав ему имя Россия. Для этого воспользуйтесь контекстным меню листа.

·Наберите таблицу потребления продуктов в России, ис- пользуя приведенный на с.83 образец.

·Рассчитайте необходимые данные таблицы в следующей последовательности:

1. Для расчета потребляемой энергии воспользуйтесь фор- мулой Потребляемая энергия = Потребляемые продукты * Калорийность/100. Распространите введенную формулу на ячейки D4 - D12 таблицы.

8 1

2.С помощью кнопки Автосумма рассчитайте суммарную потребляемую энергию в ячейке D14.

3.Для расчета данных ячеек E4 - E12 разделите потребля- емую энергию каждого из продуктов на суммарную потребля- емую энергию. Используйте абсолютную ссылку для знаме-

нателя и распространите введенную формулу на весь столбец E. Задайте для этого столбца процентный формат (0 десятич-

ных знаков).

· Постройте круговую диаграмму, предварительно выде-

лив диапазон ячеек A4 – A12 и (при нажатой клавише Ctrl) со- ответствующий ему диапазон E4 – E12.

·Задайте имя Германия для листа 2.

·Свяжите данные первого столбца листа Германия с со- ответствующими данными листа Россия. Для этого выполни- те следующую последовательность действий:

1. Выделите ячейку A4 на листе Германия.

2.В строке формул наберите =

3.Выделите ячейку A4 на листе Россия.

4.Нажмите кнопку Enter для завершения ввода формулы. В итоге будет введена формула Россия!A4. Распространи-

те ее на нижние ячейки столбца A таблицы.

·Действуя аналогично, свяжите данные второго столбца листа Германия с соответствующими данными листа Россия.

·Введите данные столбца C листа Германия (с. 84), рас-

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

·Задайте имя Сравнение для листа 3.

·Все данные таблицы листа Сравнение свяжите с дан- ными первых двух листов: столбец A - с данными столбца A листа Россия; столбец B - с данными столбца E листа Рос-

сия;	столбец C - с данными столбца E листа Германия.
·	Постройте гистограмму листа Сравнение, предвари-

тельно выделив ячейки A3 – C12 (всю таблицу, включая наи- менования столбцов).

8 2

		A	B	C	D	E
	1	Средний дневной рацион (Россия)
	2		Калорий-	Потреб-	Потребляе
		Наименоваие		ляемые	мая	% в
	3		ность,	продук-
		продукта			энергия,	рационе
			ккал/100 г.	ты,
					ккал/сутки
				г/сутки
	4	Хлебопродукты	250	450
	5	Мясопродукты	500	30
		Молокопродукты	400	100
	6
		Рыба	300	20
	7
		Жиры животные	800	30
	8
		Жиры растительные	200	50
	9
		Овощи	100	100
	10
		Фрукты	150	50
	11
		Углеводы	2300	30
	12

13

14	Всего

Структура потребления продуктов (Россия)

Углеводы
23%		Хлебопродук
		ты
Фрукты		39%
3%
Овощи
3%
Жиры
растительные		Мясопродукты
3%		5%
Жиры		Молокопродук
животные	Рыба	ты
8%	2%	14%

Лист: Россия

8 3

Средний дневной рацион (Германия)

		Калорий-	Потреб-	Потребляе
	Наименоваие		ляемые	мая	% в
		ность,
	продукта		продукты,	энергия,	рационе
		ккал/100 г.
			г/сутки	ккал/сутки

110

120

180

90

20

60

100

110

20

Всего

Структура потребления продуктов (Германия)

	Углеводы	Хлебопродукты
		10%
	16%

Фрукты

6%

			Мясопродукты
	Овощи		21%
	3%
	Жиры
	растительные
	4%
	Жиры		Молокопродук
	животные	Рыба
			ты
	6%	9%	25%

Лист: Германия

8 4

Сравнительная характеристика % соотношения потребляемых продуктов

Наименование продукта Россия

Германия

				Сравнение % потребления продуктов
60																				Германия
50																				Россия
40
30
%
20
10
0
		Хлебопродукты			Мясопродукты		Молокопродукты		Рыба		Жиры животные		Жиры растительные		Овощи			Фрукты			Углеводы

Продукты

Лист: Сравнение

8 5

Самостоятельная работа.

Исходя из приведенной ниже таблицы, иллюстрирующей дви- жение товара со склада в магазин с его последующей продажей,

	Наименование	Цена	Прибыло	Поставлено	Продано
	товара		на склад	в магазин
	Молоко	15,50р.	1200 шт.	150 шт.	86 шт.
	Сметана	45,50р.	1000 шт.	75 шт.	75 шт.
	Сливки	35,20р.	980 шт.	20 шт.	10 шт.
	Йогурт	28,70р.	850 шт.	100 шт.	75 шт.
	Творог	50,30р.	750 шт.	25 шт.	25 шт.

составьте рабочую книгу Excel, состоящую из следующих листов: 1. Лист Склад. Таблица:

·наименование товара;

·прибыло на склад, шт.;

·поставлено в магазин, шт.;

·остаток на складе, шт. 2. Лист Магазин. Таблица:

·наименование товара;

·прибыло в магазин, шт.;

·продано, шт.;

·остаток в магазине, шт.;

·необходимость поставки со склада (ДА или НЕТ. Для расчета использовать функцию ЕСЛИ).

3. Лист Выручка. Таблица:

·наименование товара;

·цена за 1 шт.

·продано, шт.

·сумма выручки за проданный товар, руб. Расчет выпол- ните по формуле Сумма выручки = Продано * Цена за 1 шт.

8 6

·итоговая сумма выручки.

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

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

Практическая работа 12 Базы данных в Excel

Цель работы: Изучить возможности работы с таблицей как с базой данных.

При работе с таблицей MS Excel как с базой данных, с ней можно выполнять следующие специфические для базы данных операции:

-сортировка данных;

-изменение данных в режиме формы;

-фильтрация данных;

-расчет итогов для однотипных данных;

-применение стандартных функций базы данных.

Таблица базы данных должна удовлетворять следующим

условиям:

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

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

3.Никакие ячейки таблицы базы данных не дожны быть объединены.

Задача.

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

8 7

Фамилия И.О.		Дата	Дата	Пол	Оклад
		рождения	приема
Андреева О.Р.		19.10.66	16.01.93	ж	5 300р.
Ерохин А.К.		24.04.51	23.10.84	м	4 900р.
Кубрина К.С.		26.06.61	20.04.93	ж	3 700р.
Ноткин Е.В.		18.08.60	27.08.85	м	5 950р.
Петров С.П.		14.08.51	12.05.97	м	7 200р.
Пешков Р.В.		15.03.49	16.05.74	м	3 200р.

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

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

·Добавьте к таблице еще два столбца с заголовками Возраст и Стаж. Для остальных ячеек этих столбцов установите числовой формат данных (0 десятичных знаков). Рассчитайте возраст сотрудников в годах по формуле (Сегодняшняя дата

—Дата рождения)/365. Для определения сегодняшней даты используйте стандартную функцию Excel СЕГОДНЯ() (кате- гория Дата и Время). Аналогично рассчитайте стаж работы сотрудников.

Работа с таблицей в режиме формы.

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

Задание.

Введите данные двух сотрудников предприятия:

Попова И.К., дата рождения 07.10.56, дата приема 02.05.84 г., оклад 4200 р.

Туньков А.А., дата рождения 19.07.41, дата приема 03.11.88 г., оклад 3390 р.

· В диалоговом окне формы нажмите кнопку Добавить, за- тем введите данные (данные денежного формата введите как обычное число). Завершите ввод нажатием кнопки Закрыть.

8 8

Задание.

Удалите кадровые данные сотрудника Ерохина А.К.

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

Сортировка данных

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

Задание.

Отсортируйте таблицу сотрудников предприятия по полу. Сотрудников одного пола отсортируйте по возрасту.

· В диалоговом окне сортировки выберите Сортировать по/Пол, Затем по/Возраст.

Фильтрация данных

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

Задание.

Выберите сотрудников 40 лет и старше, имеющих оклад ме-

нее 4000 р.

·Нажав маркер фильтрации в заголовке поля Возраст, вы-

берите Условие...

·В диалоговаом окне автофильтра выберите Больше или равно и наберите 40.

·Откройте окно автофильтра поля Оклад и задайте Мень-

ше 4000.

Используя буфер обмена, скопируйте полученную таблицу на Лист 2, затем, вернувшись на Лист 1, снимите возможность фильтрации данных, снова выполнив Данные/Фильтр/Авто-

фильтр.

Стандартные функции работы с базой данных.

MS Excel предоставляет широкий набор стандартных функ-

8 9

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

Задание.

Вычислите средний оклад сотрудников, стаж работы кото- рых менее 15 лет.

·Предварительно следует в свободных ячейках Рабоче- го листа (например, A11 и A12) записать название поля отбора

иусловие отбора: в A11 запишите Стаж, в A12 <15

·Выделите ячейку, в которой будет находиться искомое среднее значение (например, A13) и выполните последователь- но следующие действия:

1. Нажмите кнопку Вставка функции и в категории Рабо- та с базой данных выберите функцию ДСРЗНАЧ.

2. Заполните графу База_данных, выделив всю таблицу, со- держащую данные (вместе с заголовками столбцов).

3. Заполните графу Поле, выделив заголовок столбца Оклад. 4. Заполните графу Критерий, выделив ячейки с названи- ем поля отбора и условием отбора (в нашем случае это A11 и

A12).

5. Нажмите клавишу Enter для завершения ввода формулы.

Самостоятельная работа.

Для таблицы Стоимость ремонта автомобилей (при на-

боре таблицы отделите ее хотя бы одной строкой от предыду- щего текста) выполните следующие действия:

·Вычислите значения последнего столбца таблицы (Цена, руб. = Цена, у.е. Х Текущий курс доллара) и представьте их в денежном формате (0 десятичных знаков).

·В режиме Формы добавьте к таблице следующие строки: Марка а/м: Opel; Код ремонта: 4356; Цена(у.е.): $25. Марка а/м: Opel; Код ремонта: 4383; Цена(у.е.): $20.

·Отсортируйте записи таблицы по марке автомобиля.

·С помощью Автофильтра выберите в таблице данные для всех автомашин марки Ford и скопируйте получившуюся таблицу на Лист 2.

Указание: для этого в графе Условие автофильтра следует

9 0