Начисление денежных средств по вкладам

№ счета	ФИО	Вид вклада	Сумма вклада (в руб.)	Годовой процент	Сумма по процентам за месяц	Сумма к выплате
	Сидоров В.И.	Срочный	3 500р.
	Андреева И.Т.	Депозит	1 000р.
	Ковалева О.А.	Срочный	5 001р.
	Лобанов А.О.	Пенсионный	129р.
	Петров В.Х.	Пенсионный	550р.
	Морозов П.С.	Пенсионный	250р.
	Пулит А.В.	Срочный	2 300р.
	Шанина Е.П.	Срочный	7 800р.
	Сидоров В.И.	Пенсионный	10 000р.
	Андреева И.Т.	Пенсионный	20 000р.
	Петров В.Х.	Депозит	133р.
		Итого

2. Рассчитать значения столбцов с учетом заданного процента, при этом:
Годовой процент (от суммы вклада) равен: 12% для пенсионного вклада, 24% для срочного вклада, 30% для депозита. Для расчета годового процента депозита за год использовать функцию ЕСЛИ.

3. Сделать графу Сумма по процентам за месяц невидимой.

4. Закрепить для просмотра на экране шапку таблицы и первый столбец таблицы.

5. Определить итоговую сумму к выплате на конец года на основании суммы вклада и годового процента.

6. Выделить цветом шапку таблицы и итоговую строку (заливкой).

7. Определить среднюю сумму вклада.

8. Диапазону Сумма вклада присвоить имя.

Методические рекомендации.
Этот пункт следует выполнить в 2 этапа:

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

· затем выполнить команду "Вставка-имя-присвоить" и в появившемся диалоговом окне ввести в верхнее текстовое поле нужное имя, скажем, Вклад).

9. Определить максимальную Сумму вклада.

10. Присвоить текущему листу рабочей книги имя 2006 год.

11. Скопировать таблицу на чистый лист; графу Вид вклада заполнить с использованием технологии проверки ввода данных; графу Годовой процент заполнить с использованием функций Просмотр или ВПР (для этого ввести дополнительную справочную таблицу процентов выплаты по депозиту). Предварительно ознакомиться с работой функций по Справке приложения Excel.

Методические рекомендации.

Выполнение этого пункта предусматривает предварительное самостоятельное изучение по Справке или учебнику работы функций Просмотр и ВПР. Кратко говоря, они применяются для выборки нужных значений из дополнительной (справочной) таблицы. При этом задается входной параметр-ключ, например номер банковского счета, а результат выполнения функции – значение, которое Excel подставляет из некоторого столбца этой справочной таблицы. Например, пусть такая таблица в диапазоне F1:G4 содержит данные в столбцах: Счет и Фамилия:

	F	G
	Счет	Фамилия
		Петрова П.П.
		Иванова И.И.
		Сидоров С.С.

Тогда функция ВПР(2000, F1:G4, 2) вернет значение (Иванова И.И.) из ячейки G3 (результат во 2-м столбце таблицы, в первом стоит число-ключ "2000"). Естественно, что в качестве аргументов функции могут стоять любые допустимые значения: ссылки, имена, диапазоны и др., а число столбцов справочной таблицы может быть достаточно большим.

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

12. Сравнить обе технологии заполнения таблиц.

Задание 3.

1. Заполнить графы таблицы, используя следующие формулы для расчетов:

ДН=О-Р-А Н=Налоговая ставка фирмы*ДН ЧД=ДН-Н

Отчет о прибылях и убытках, предоставленный акционерам

Налоговая ставка фирмы:	34%
Количество акций в обращении (в млн.):

 

В млн. долларов	Год
Объем продаж (О)	100,00	100,00	100,00	100,00	100,00	100,00	100,00	100,00	100,00	100,00
Расходы (Р)	50,00	50,00	50,00	50,00	50,00	50,00	50,00	50,00	50,00	50,00
Амортизация (А)	20,00	32,00	19,00	12,00	11,00	6,00	0,00	0,00	0,00	0,00
Доход до уплаты налогов (ДН)	?	?	?	?	?	?	?	?	?	?
Налоги, подлежащие оплате по ставке 34% (Н)	?	?	?	?	?	?	?	?	?	?
Чистый доход после уплаты налогов (ЧД)	?	?	?	?	?	?	?	?	?	?
Прибыль на одну акцию (ПА)	?	?	?	?	?	?	?	?	?	?

2. Рассчитать среднюю прибыль на акцию за рассматриваемый период.

ПА=ЧД / количество акций

 

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

?

 

Задание 4.

№п/п	Таб. номер	Фамилия	Имя	Отчество	Отдел	Должность	Дата приема на работу	Дата увольнения	Пол	Кол-во иждивенцев	Оклад
1.		Иванов	Иван	Иванович	Плановый	начальник	10.01.1996		м
2.		Иваненко	Иван	Петрович	Маркетинга	экономист	10.04.1998		м
3.		Петров	Петр	Петрович	Маркетинга	секретарь	21.07.1998		м
4.		Петренко	Петр	Иванович	Бухгалтерия	?	10.10.1998		м
5.		Сидоров	Сидор	Сидорович	?	?	10.01.1999	10.10.2001	м
6.		Седов	Кузьма	Фомич	?	?	12.04.1999		м
7.		Фомин	Фома	Фомич	?	?	26.07.1995		м
8.		Фоменко	Сидор	Кузьмич	?	?	10.11.1999		м
9.		Кукина	Юлия	Петровна	?	?	10.01.2000	21.12.2001	ж
10.		Макова	Алина	Игоревна	?	?	10.04.2000		ж
11.		Сушкина	Алла	Вадимовна	?	?	10.07.2000	12.12.2000	ж
12.		Кротова	Инна	Павловна	?	?	21.10.1997		ж
13.		Бойцов	Семен	Семенович	?	?	10.01.2001		м
14.		Гайдай	Иван	Михайлович	?	?	30.04.2001		м
15.		Краснов	Павел	Павлович	?	?	10.07.2001		м

1. Заполнить графы № п/п., Отдел, Должность, используя оптимальные технологии ввода (заполнить ячейки, отмеченные знаком "?").

2. Скрыть столбцы "Дата увольнения" и "Отчество".

3. Закрепить шапку таблицы.

4. Увеличить всем сотрудникам оклад в 1,37 раза. Коэффициент повышения оклада хранить в отдельной ячейке.

5. Для каждого сотрудника определить стаж работы на предприятии.

6. Начислить всем сотрудникам премию, пропорциональную стажу работы: 1000р.x стаж работы.

7. Всем сотрудникам, проработавшим более 5 лет начислить дополнительную премию, в размере 500 р.

Величину премии хранить в отдельной ячейке.

8. Найти величину максимального оклада на предприятии.

9. Дать листу имя “ 2006 год ”.

10. Скопировать таблицу на другой лист и изменить условные данные (добавить новых сотрудников).

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

 

Задание 5.

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

Наращенная сумма = первоначальный капитал*(1+процент)^срок

 

Первоначальный капитал	1000 р.
	Процент
срок (лет)	1%	2%	3%	4%	5%	6%
	1 010,00р.	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?
	?	?	?	?	?	?

Рекомендация. Создайте формулу только для срока, равного1 и для процентной ставки, равной 1 % и скопируйте формулу во все остальные ячейки.

Работа № 2: Создание и редактирование диаграмм в Excel

Перечень заданий

Цель работы: освоение приемов создания и редактирования диаграмм

Состав работы (быстрый переход по ссылкам):

1. Задание 1: построение графика зарплаты.

2. Задание 2: прибыль по акциям.

3. Задание 3: анализ заемного капитала.

 

Задание 1

Исходные данные

Таб. номер	ФИО	Тарифная ставка (руб.)	Отработано часов	Начислено
	Сидоров В.И.	120,00р.		15000р.
	Андреева И.Т.	50,00р.		32664р.
	Ковалева О.А.	70,00р.		85316р.
	Лобанов А.О.	100,00р.		30000р.
	Евдокимов В.Х.	250,00.		61650р.
	Морозова Н.С.	120,00р.		94320р.
	Пулит А.В.	243,76р.		37783р.
	Шанина Е.П.	120,00р.		56040р.
	Итого			412773р.

Порядок выполнения

На основании исходных данных, приведенных в таблице, выполнить следующее.

1. Построить диаграмму, отражающую начисленную сумму каждому из сотрудников.

2. Изменить цвет ряда данных.

3. Изменить цвет области построения диаграммы.

4. Подписать столбец, соответствующий максимальной сумме.

5. Добавить на диаграмму ряд данных «Отработано часов».

6. Настроить его на вспомогательную ось.

7. С помощью диаграммы увеличить величину отработанных часов Андреевой И.Т. до 200 часов.

8. С помощью диаграммы увеличить сумму, начисленную Сидорову В.И. до 30 000р.

Задание 2

Исходные данные