Создание собственных шаблонов

1. Создать свои шаблоны по предлагаемым образцам 1–3; защитить ячейки с формулами.

2. Сохранить их в своей папке как шаблоны.

3. Заполнить таблицы для вновь созданных шаблонов по предлагаемым образцам и сохранить результаты в файлах Excel.

Формулы для образца 2:

Расчет осуществляется по следующим формулам:

Формула 1=(конец сеанса – начало сеанса)*24 (должен быть числовой формат!);

Формула 2=(Если(И(начало сеанса>=7/24; начало сеанса<22/24); время сеанса*2,4; время сеанса*1,4);

Формула 3=сумма всех столбцов в этой категории (т. е. по данной оплате)

Формула 4=(число оплаты в долл.) умножить на (число = курс доллара). Здесь используется абсолютная адресация.

Формула 5=(количество килобайт) умножить на (Если(ячейка=«По Беларуси»);0,01;(Если(ячейка=«По СНГ»;0,02;0,04);

Формула 6=(абонентная плата)+(Итого за Internet)+(Итого за электронную почту).

Замечание.В образце 2 для ячейки F5 используется «денежный» формат в единицах «Английский долл. (США)».

Формулы для образца 3:

Расчет осуществляется по следующей схеме:

ЕСЛИ (количество дней проката<31, то вычисления по формуле 3);

ЕСЛИ (И(количество дней проката>=31; количество дней проката<61), то вычисления по формуле 4; иначе вычисления по формуле 5).

Формула 1: =С4 – С3;

Формула 2: =D9+D10+D11;

Формула 3: =В9;

Формула 4: =В10*количество дней;

Формула 5: =B11*количество дней.

Образец 1

Калькуляция

№	Наименование	Обозначение (имена ячеек)	Вычисления по формуле (вводить, начиная с символа «=»)
	Объем работ	О	Сюда ввести любое число, равное объему работ
		Х	Формула для Х=О-(К+М)
	Зарплата	ЗРП	Формула для ЗРП=Х/1,71
	Фонд занятости	ФЗ	Формула для ФЗ=ЗРП*0,01
	Фонд страхования	ФС	Формула для ФС=ЗРП*0,35
	Материалы	М	Сюда ввести число меньше О
	Командировки	К	Сюда ввести число меньше О
	Накладные расходы	Р	Формула для Р=ЗРП*0,35
	Итого		=(ЗРП+ФЗ+ФС+М+К+Р) сумма должна быть равна О

Образец 2

Оплата за услуги по использованию электронной почты и Интернета

		A	B	C	D	E	F	G
		Оплата за услуги по использованию электронной почты и Интернета
			Курс долл. на текущий день	Число – курс долл. на сегодня
		№	Услуга	Дополнительные данные		Оплата в долл.	Оплата в бел. руб.
				Абонентная плата			Формула 4
				Итого за абонемент		Формула 3	Формула 3
			Плата за Интернет	Время начала сеанса	Время конца сеанса	Используемое время	Оплата в долл.	Оплата в бел. руб.
				7:00	8:30	1,50	3,6
				14:00	15:00	Формула 1	Формула 2	Формула 4
				23:30	0:00	Формула1	Формула 2	Формула 4
				0:45	2:30	Формула 1	Формула 2	Формула 4
			Итого за Интернет				Формула 3	Формула 3
			Плата за электронную почту	Количество килобайт	Регион		Оплата в долл.	Оплата в бел. руб.
					По Беларуси		0,22	Формула 4
					По СНГ		Формула 5	Формула 4
					Дальнее зарубежье		Формула 5	Формула 4
			Итого за электронную почту		Формула 3	Формула 3
			ИТОГО:		Формула 6	Формула 6

Образец 3

Бланк абонентной платы за телевизионную антенну

		A	B	C	D
			Бланк абонентной платы за телевизионную антенну
			Абонентная плата	Телевизионная антенна
			c:	ввести дату начала проката
			по:	ввести дату конца проката
			Итого:	Формула 1
			Стоимость проката:	Формула 2
		Стоимость абонентной платы
		Тариф		Ячейки	Расчет
		До 1 месяца	5 000		по схеме, описанной в задании
		До 2 месяцев	8 000
		Более	7 000

Лабораторная работа № 8
ФИЛЬТРАЦИЯ ДАННЫХ. ПОДВЕДЕНИЕ ИТОГОВ

Задания

Задание 1. Сортировка данных:

1. Открыть файл Книги в библиотеке.doc, подготовленный преподавателем.

2. Скопировать текст в буфер обмена и вставить на лист книги Excel, распределив данные по столбцам с помощью Мастера текстов.

2. Дать имя листу1 Исходная таблица.

3. Скопировать эту таблицу на лист 2. Дать имя листу 2 Сортировка 1.

4. Отсортировать таблицу на листе «Сортировка 1» по трем столбцам «Фамилия», «Имя», «Отчество».

5. Скопировать эту таблицу на лист 3. Дать имя листу 3 «Сортировка 2».

6. Отсортировать таблицу на листе «Сортировка 2» по столбцу «Наименование издательства».

7. Результат сохранить в своей папке в файле с именем Книги в библиотеке.xls.

Задание 2. Фильтрация данных. Подведение итогов:

А. Книги в библиотеке

1. Открыть файл Книги в библиотеке.xls,созданный в начале данной лабораторной работы.

2. Выделить таблицу на листе Исходная таблицаи скопировать ее на лист Фильтрация 1.

3. Выполнить фильтрацию данной таблицы по фамилии – Толстой, по имени – Алексей, по отчеству – Николаевич.

4. Выделить исходную таблицу на листе Исходная таблицаи скопировать ее на лист Фильтрация 2.

5. Выполнить фильтрацию по наименованию издательства «Мастацкая лiтаратура».

6. Выделить исходную таблицу на листе Исходная таблицаи скопировать ее на лист Фильтрация 3.

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

8. Добавить в эту таблицу столбцы: Стоимость одного экземпляра, Количество экземпляров, Сумма.

9. Ввести для каждого произведения соответствующую информацию в эти столбцы.

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

11. Результат сохранить в файле с именем Фильтрация.xls.

Б. Работа с кадрами

1. На листе 1 составить таблицу и отформатировать ее по предложенному образцу 1.

2. Сохранить ее в файле с именем Работа с кадрами.xls.

3. Переименовать лист 1 с этой таблицей, дать ему имя Исходная таблица 1.

4. Скопировать эту таблицу в буфер обмена, перейти на лист 2 и вставить туда таблицу из буфера обмена.

5. Переименовать лист 2 с этой таблицей, дать ему имя Копия таблицы 1.

6. Выделить столбцы «Должность» и «Суммарная зарплата» и составить по этой информации диаграмму, поместив ее на новый лист, дав листу имя Диаграмма 1.

7. Сформировать список фамилий медсестер больницы, используя команды Сервис – Параметры – Списки.

8. Перейти на лист 3, дать ему имя Ведомость ЗАРПЛАТА.

9. Создать на нем шапку таблицы по образцу 2.

10. Вставить в столбец «Фамилия» подготовленный список фамилий медсестер.

11. Отсортировать таблицу по алфавиту фамилий.

12. Ввести для каждой фамилии коэффициенты Аи C по своему усмотрению.

13. Ввести в графу «Зарплата» формулу 4:

Коэффициент А * 150000 + Коэффициент C.

14. Ввести в графу «ИТОГО» формулу 5: