Kabachenko&Drig

Стоимость ремонта автомобилей

	Текущий курс	31,02
	доллара
	Марка а\м	Код	Цена,	Цена,
		ремонта	у.е.	руб
	Audi	1171	$30
	Audi	1117	$20
	BMW	1551	$30
	BMW	1518	$25
	Ford Escort	2461	$25
	Ford Escort	2474	$20
	Ford Sierra	2514	$25
	Ford Sierra	2521	$20
	Ford Sierra	2597	$25
	VW-Golf	7230	$20
	VW-Golf	7363	$25
	VOLVO	7626	$30
	VOLVO	7629	$30
	VOLVO	7630	$30

записать Ford*

·Используя стандартную функцию БДСУММ вычислите суммарную цену видов ремонта с кодами, превышающими 3000.

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

·Для каждой марки автомашин вычислите наибольшую стоимость ремонта в у.е. и руб.

Указание: для этого следует выполнить Данные/Итоги, в графе Операция выбрать Максимум и указать в окне Доба-

вить итоги по полям Цена, у.е. и Цена, руб.

9 1

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

Наберите таблицу Репертуар театров (с. 93), используя сле- дующие форматы данных: для второго столбца - Дата, для ше- стого столбца - Время.

Выполните задания, описанные ниже. После выполнения каж-

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

Задание 1. Отсортировать данные по датам и затем, для одинаковых дат, по времени начала спектакля.

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

Задание 3. Отсортировать данные по дням недели, затем - по времени началп спектакля.

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

Задание 4. Выбрать из таблицы репертуар Московского театра Сатиры.

Задание 5. Выбрать из таблицы список произведений А.Ос- тровского.

Задание 6. Выбрать из таблицы все спектакли, которые на- чинаются в 12 или 19 часов.

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

1.идут по субботам или воскресеньям;

2.идут в ЛЕНКОМе или МХАТе имени Горького;

3.идут после 18:00.

9 2

Театр	Дата	День недели	Название	Автор	Время
			спектакля	произведения
Государственный
академический Малый театр	10 июн	Среда	"Царь Борис"	А. Толстой	19:00
			"Преступная мать,
Государственный			или
академический Малый театр	11 июн	Четверг	Второй Тартюф"	П. Бом арше	19:00
Государственный
академический Малый театр	12 июн	Пятница	"Волки и овцы"	А. Островский	19:00
Государственный
академический Малый театр	13 июн	Суббота	"Лес"	А. Островский	18:00
Государственный
академический Малый театр	14 июн	Воскресенье	"Дядя Ваня"	А. Чехов	18:00
Государственный			"Тайны	Э. Скриб,
академический Малый театр	11 июн	Четверг	мадридского двора"	Е.Легуве	19:00
Государственный
академический Малый театр			"Свадьба
(филиал)	12 июн	Пятница	Кречинского"	А. Колкер	19:00
Государственный
академический Малый театр
(филиал)	13 июн	Суббота	"Бешеные деньги"	А. Островский	18:00
Государственный
академический Малый театр			"Тайны	Э. Скриб,
(филиал)	14 июн	Воскресенье	мадридского двора"	Е.Легуве	18:00
			"Мы идем смотреть
МХАТ имени Горького	10 июн	Среда	"Чапаева”	О. Данилов	18:30
МХАТ имени Горького	11 июн	Четверг	"Зойкина квартира"	М. Булгаков	18:30
			"Страсти по
МХАТ имени Горького	12 июн	Пятница	Антоше"	А. Чехов	18:30
			"Прощание с
МХАТ имени Горького	13 июн	Суббота	Матерой"	В. Распутин	12:00
			"Мадам
МХАТ имени Горького	13 июн	Суббота	Александра"	Ж. Ануй	18:30
Московский театр Сатиры	10 июн	Среда	"Бешеные деньги"	А. Островский	19:00
			"Молчи, грусть,
Московский театр Сатиры	11 июн	Четверг	молчи"	А. Ширвиндт	19:00
	12 июн		"Очень деловой
Московский театр Сатиры		Пятница	человек"	Л. Вернейль	19:00
Московский театр Сатиры	13 июн	Суббота	"Босиком по парку"	Н. Саймон	19:00
			"Молчи, грусть,
Московский театр Сатиры	14 июн	Воскресенье	молчи"	А. Ширвиндт	19:00
ЛЕНКОМ	10 июн	Среда	"Юнона" и "Авось"	А. Вознесенский	19:00
ЛЕНКОМ	11 июн	Четверг	"Королевские игры"	Г. Горин	19:00
ЛЕНКОМ	12 июн	Пятница	"Королевские игры"	Г. Горин	19:00
ЛЕНКОМ	13 июн	Суббота	"Две женщины"	И. Тургенев	19:00
			"Безумный день или
ЛЕНКОМ	14 июн	Воскресенье	женитьба Фигаро"	П. Бомарше	19:00

9 3

Практическая работа 13 Сводные таблицы

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

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

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

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

Схематически сводная таблица представлена на следующем рисунке.

Поля страниц

(фильтров)

Поля столбцов

Поля строк

Поля данных и итогов

9 4

Образец.

Склад		Товар	День			Цена
			недели				руб.
Восточный		Кефир		12		16,50р.
Восточный		Сметана		34		16,00р.
Восточный		Молоко	среда	50		12,50р.
Западный		Кефир	вторник	10		15,40р.
Западный		Сметана	четверг	45		17,50р.
Западный		Молоко	вторник	60		11,00р.
Западный		Сметана	среда	12		17,50р.
Северный		Сметана	пятница	90		16,00р.
Северный		Молоко	среда	45		12,00р.
Южный		Молоко		45		11,00р.
Южный		Кефир	четверг	32		14,00р.
Южный		Сметана	вторник	95		16,00р.

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

·Рассчитайте значения суммы выручки для каждого по- ставленного продукта по формуле Цена * Поставлено.

Задача.

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

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

·Выделите любую ячейку исходной таблицы и вызовите Ма-

стер сводных таблиц, выполнив Данные/Сводная таблица.

·На первом шаге работы Мастера отметьте, что данные находятся в базе данных Excel и что будет создаваться свод-

ная таблица.

·На втором шаге работы Мастера сделайте, чтобы ука- занный диапазон ячеек совпадал с исходной таблицей.

·На третьем шаге укажите, что созданный отчет нахо-

9 5

дится на новом листе Рабочей книги и нажмите кнопку Макет.

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

1. в область Данные - поле Поставлено (что будет сум- мироваться);

2. в область Строка - поле Товар (какие данные распола- гаются в строках сводной таблицы);

3. в область Столбец - поле Склад (какие данные распо- лагаются в столбцах сводной таблицы);

4. в область Страница - поле День недели (по каким дан- ным будет проводиться фильтрация).

·Завершите создание сводной таблицы и назовите создан-

ный лист Количество.

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

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

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

1.На основе вышеприведенных данных создайте новую сводную таблицу, расположив ее на отдельном листе. С ее по-

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

2.Преобразуйте таблицу, представленную ниже, в базу дан-

ных Excel. На основании сводных таблиц определите:

· Площадь предприятий, подлежащих реконструкции, по

районам.

·Число предприятий, подлежащих реконструкции, по ви- дам деятельности.

·Суммарную площадь и число предприятий по направле-

ниям.

9 6

Список предприятий, подлежащих реконструкции

Район: Запсковье

Направление: Бытовое обслуживание

№	Организация	Вид	Площадь	,
		деятельности	кв.м.
1	ООО Успех	Химчистка	120
2	ЗАО Удача		200
3	ООО Игрок	Химчистка	300
	Направление: Торговля
1	ООО Фиалка	Цветы	30
2	ЗАО Бублик	Хлеб	100
3	ООО Ирис	Цветы	20
	Район: Завеличье
	Направление: Бытовое обслуживание
1	ООО Ботинок	Ремонт обуви	30
2	ООО Волос		50
	Направление: Торговля
1	ООО Мороз		300
2	ЗАО Калач	Хлеб	100
3	ООО Роман	Книги	200
4	ЗАО Селедка	Рыба	100

Практическая работа 14 Шаблоны в MS Excel

Цель работы: Научиться создавать простейшие шаблоны.

Шаблоны в MS Excel во многом подобны шаблонам MS Word: это образцы бланков документов, подготовленные для запол- нения. В шаблонах MS Excel легче осуществлять контроль вво- димых значений и проводить автоматические расчеты на ос- нове исходных данных. Шаблоны MS Excel - обычно докумен- ты, подготовленные в виде таблиц.

9 7

Образец.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

A	B	C	D	E	F	G	H
	СЧЕТ-ФАКТУРА №			от

Поставщик

Адрес

ИНН поставщика

Покупатель

Адрес

ИНН покупателя

КолиЦена за Нало- Наименование товара чество единицу товаров говая ставка

ИТОГО Всего к оплате

Руководитель предприятия

Главный бухгалтер

Сумма товаров с налога учетом

налога

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

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

Создание формы документа.

· Наберите представленный выше образец документа. При наборе используйте размер шрифта 10 пт, за исключением стро- ки 1, где размер - 11пт. При наборе первого столбца таблицы используйте объединение ячеек; знаки перечеркивания в стро-

ке 18 установите, выполнив Формат/Ячейки/Граница/Отдель-

ные. Сходным образом образуйте знаки подчеркивания в стро-

ках 22 и 24.

9 8

Форматирование полей ввода и вычисляемых полей

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

· Установите форматы ячеек формы документа в соответ- ствии с таблицей:

	По ле	Я че йк а	Ф о рм ат
			(т ип, шриф т, в ы рав нив ане )
	Н ом ер сч ета	D 1	Текст, п олуж и р н ы й , п о
			ц ен тр у
	Д ата сч ета	F 1	Д ата, п ол уж и рн ы й , слев а
	П остав щи к	C 3	Текст, слев а
	А дрес п остав щи ка	C 4	Текст, слев а
	И НН п остав щи ка	C 5	Текст, слев а
	П окуп атель	C 7	Текст, слев а
	А дрес п окуп ателя	C 8	Текст, слев а
	И НН п окуп ател я	C 9	Текст, слев а
	Н аи м ен ов ан и е тов ар а	A 1 2:B12	Текст, слев а
		и н и ж е
	K оли честв о	C 12 и	Чи сл ов ой , 0	зн аков , п о
		н и ж е	ц ен тр у
	Ц ен а за 1 ед.	D 12 и	Д ен еж н ы й , 2	зн ака, сп рав а
		н и ж е
	С тои м ость б ез н ало га	E1 2 и	Д ен еж н ы й , 2	зн ака, сп рав а
		н и ж е
	Н алого в ая став ка	F 1 2 и	Про ц ен тн ы й , 0 зн аков , п о
		н и ж е	ц ен тр у
	С ум м а н ало га	G 1 2 и	Д ен еж н ы й , 2	зн ака, сп рав а
		н и ж е
		H 12 и
	С тои м ость с н алого м	н и ж е;	Д ен еж н ы й , 2	зн ака, сп рав а
		C 20
	Н ачальн и к	D 2 2	Текст, слев а
	Гл ав н ы й бухгалтер	D 2 4	Текст, слев а

9 9

Контроль вводимых данных.

На этом шаге вводятся подсказки, которые будет получать пользователь при вводе данных, а также блокируются попытки ввода заведомо неправильных значений. Для этого требуется выделить ячейку или диапазон ячеек, затем выполнить Дан- ные/Проверка. Во вкладке Параметры задать тип и диапа- зон вводимых данных; во вкладке Сообщения для ввода запи- сать сообщение, выводимое при вводе значений.

· Для следующих полей ввода введите подсказки и задай- те диапазон допустимых значений:

	Я че йка	Параме тры	Подсказка
	D1		Номер счета
	F 1		Д ата счета
	C 3		Наи мен ован и е п оставщи ка
	C 4		Адрес поставщи ка
	C 5		ИНН п оставщика
	C 7		Наи мен ван и е п окупателя
	C 8		Адрес покуп ателя
	C 9		ИНН п окуп ателя
	A 12:A 17		Наи мен ован и е товара
	C 12:C 17	Целое больше 0	K оли чество товара
	D 12:D 17	Д ействи тельн ое	Цен а товара
		больше 0
	F1 2:F 17	Д ействи тельн ое	С тавка налога
		меж ду 0 и 1
	D 22		Ф ИО руководи теля
	D 24		Ф ИО главн ого бухгалтера

Расчетные формулы.

На этом шаге в ячейки документа вводятся формулы расче- та зависимых данных.

·введите в ячейки E12 - E17 формулы, рассчитывающие

100