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

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: