Продажа валюты

Дата

Курс у.е.

Пункт 1

Пункт 2

Пункт 3

Пункт 4

Итого

у.е.

руб.

у.е.

руб.

у.е.

руб.

у.е.

руб.

у.е.

руб.

01.02.2011

3 170

520

_*

2350

=

5330

=

2330

=

∑ECЛИ*

∑ЕСЛИ

02.02.2011

3 170

1390

=

790

=

1470

=

1260

=

∑ЕСЛИ

∑ЕСЛИ

03.02.2011

3 175

1300

=

3750

=

3560

=

1400

=

∑ЕСЛИ

∑ЕСЛИ

04.02 2011

3 170

1400

=

2570

=

3540

=

1210

=

∑ЕСЛИ

∑ЕСЛИ

05.02.2011

3 180

740

=

680

=

3640

=

2480

=

∑ЕСЛИ

∑ЕСЛИ

06.02.2011

3 175

860

=

570

=

890

=

3540

=

∑ЕСЛИ

∑ЕСЛИ

07.02.2011

3 180

520

=

1020

=

2620

=

1170

=

∑ЕСЛИ

∑ЕСЛИ

Итого:

∑

∑

∑

∑

∑

∑

∑

∑

∑

∑

Рис. 4.62. Расчетная таблица задачи

2. В ячейке с символами "=*" подсчитать графы Продажа в рублях по выражению:

Продажа в руб. = Продажа в у.е.*Курс у.е.

3. Копировать данную формулу в ячейки с символом "=".

4. В ячейке с символами "СУММECЛИ*" подсчитать итоговые значе­ния по строкам с помощью функции СУММЕСЛИ.

5. Копировать данную формулу в ячейки с символами "СУММЕСЛИ".

6. В ячейках с символом "СУММ" подсчитать итоговые значения по столбцам.

7. Построить объемную трехмерную гистограмму продажи ва­люты в рублях за каждую дату по всем пунктам.

Выполнить.

1. Расчет продажи в рублях.

2. Расчет итоговых значений.

Использование смешанных ссылок

Задание 4.

Постановка задачи. 1. В табличном процессоре Excel создать расчетную таблицу со­гласно рисунку 4.65.

2. Графу Доля акций в общем объеме рассчитать по выражению:

Доля акций в общем объеме (%) = Количество акций / Итого по количеству акций.

2. В ячейке с символами “=*” подсчитать значение прибыли ак­ционера по выражению:

Прибыль акционера = Доля акций в общем объеме (%) * Прибыль за месяц.

2. Копировать данную формулу в ячейки с символом

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

4. Исправить формулу для расчета ежемесячной прибыли каждо­го акционера для округления результата до тысяч.

5. Построить накопительные графики для прибыли акционеров.

6. Построить накопительную диаграмму с областями для при­были акционеров.

Распределение прибыли акционерного общества

Акцио­	Коли­	Доля	Январь	Февраль	Март	Апрель	Май	Июнь	Итого
неры	чество	акциив	Прибыль по месяцам
		объеме	3 500 000	4 000 000	б 500 000	4 200 000	7 ООО ООО	7 300 ООО	∑
Факел	500	%	=*	=	=	=	=	=	∑
Марс	ЗО0	%	=	=	=	=	=	=	∑
Орбита	400	%	=	=	=	=	=	=	∑
Сож	300	%	=	=	=	=	=	=	∑
Турист	800	%	=	=	=	=	=	=	∑
Глобус	1 000	%	=	=	=	=	=	=	∑
Волна	200	%	=	=	=	=	=	=	∑
Итого	2	%	∑	∑	∑	∑	∑	∑	∑

Рис. Расчетная таблица задачи

Выполнить:

1. Расчет доли акций в общем объеме.

2. Расчет прибыли без округления до тысяч.

2. Расчет прибыли, округленной до тысяч

Практическое занятие № 28

Финансовые, текстовые, логические функции. Функции даты и времени

Цель занятия:

сформировать умения и навыки по работе с функциями

Примеры использования функций MS Excel в экономических расчётах

Задание 1

Постановка задачи

Таблица расчета процентов по вкладу

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

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

● первоначальную сумму вклада;

● начальное значение процентной ставки по депозиту и шаг ее изменения;

● начальное значение периода времени и шаг его изменения.

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

Мы предполагаем, что процент по депозиту сложный и начисляется в конце года (то есть период капитализации равен одному году). В каждом следующем году расчет процентов производится для суммы, положенной на депозит, плюс про­центы, начисленные за предыдущий год.

Сумма вклада на конец периода рассчитывается по такой формуле:

P1= P0*(1+г)^{^}n ,

где РО — сумма, размещенная на депозите, r — ставка по депозиту, п — число периодов (лет).

Рис. 3.1 - Фрагмент таблицы для расчета суммы на депозите (с формулами)

Рис.3.2 - Таблица для расчёта суммы на депозите (с числовыми значениями)

У вас не возникнет проблем с оформлением столбцов и строк, содержащих исходные данные для расчета. Вам требуется вставить две строки (после строк 2 и 8) и ввести имена и значения параметров в область ввода. А вот процесс создания основной расчетной формулы мы опишем более подробно. Вы­делите диапазон В10:К19 и введите в ячейку В10 формулу для расчета, выполнив следующие действия:

1. Введите знак равенства, выделите ячейку D2 и нажатиями функциональной клавиши [F4] задайте абсолютную ссылку.

2. Введите знак «*» (умножить), круглую открывающую скобку, цифру 1 и знак «+».

3. Выделите ячейку А10 и три раза нажмите функциональную клавишу [F4] (бу­дет создана абсолютная ссылка на имя столбца), затем введите круглую за­крывающую скобку.

4. Переключитесь на английский шрифт и введите знак возведения в степень «^{^}» путем нажатия комбинации клавиш [Shift+6],

5. Выделите ячейку В9 и дважды нажмите функциональную клавишу [F4] (будет создана абсолютная ссылка на номер строки). Затем введите круглую откры­вающую скобку.

6. Завершите ввод формулы нажатием комбинации клавиш [Ctrl+Enter].

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

Задание 2

Выбор формата представления для процентных ставок

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

ПРИМЕР

Бухгалтер одного из предприятий при расчете начислений в один из обязатель­ных фондов перепутал ставку 0,06% со ставкой 0,06 и в течение года перевыпол­нил план по данному сбору на 99 лет вперед. А по налогу на прибыль заработал пеню.

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

1. Выделите форматируемую область, нажмите правую кнопку мыши и выберите в контекстном меню команду Формат ячеек.

2. В диалоговом окне Формат ячеек перейдите на вкладку Число. В списке Чи­словые форматы выделите элемент Процентный (рис. 3.3), задайте необходи­мое число десятичных знаков (например, 2) и нажмите кнопку ОК.

Рис.3.3 - Диалоговое окно Формат ячеек, вкладка Число при выборе процентного формата

Таблица определения влияния инфляции на стоимость денег

Действительная стоимость денег зависит от инфляции и определяется по сле­дующей формуле:

P1= P0 : (1+j)^n,

где j — процент инфляции за период (например, за год), п — число периодов.

Она подобна приведенной выше формуле определения суммы денег на депозит­ном счету. Ввод формулы в таблицу производится аналогичным образом. Отли­чие состоит лишь в том, что вместо знака умножения в данном случае применя­ется знак деления. Новую таблицу нетрудно создать на основе предыдущей. Для этого, выделив диапазон В10:К19, отредактируйте формулу и нажмите комбина­цию клавиш [Ctrl+Enter].

Рис. 3.4 – Таблица расчёта реальной стоимости денег с возможностью изменения суммы и процента инфляции

Таблица определения реальной стоимости денег

Давайте усложним нашу задачу, объединив две предыдущие формулы. Напом­ним, что первая формула необходима для расчета суммы денег на депозите, а вторая — для определения влияния инфляции на стоимость денег. В результате их объединения мы получим формулу для вычисления действительной стоимости денег:

FV = P0 x (1+r) ^n: (1+j) ^n

Для решения этой задачи требуется усложнить таблицу, представленную на рис. 3.3. В области ввода необходимо определить значения ячеек ЕЗ и Е5. Первая ячейка должна содержать начальное значение уровня инфляции, а вторая — шаг изме­нения инфляции.

В области вычислений следует добавить ячейки, в которые будут занесены про­центы инфляции. Но поскольку в таблице имеются объединенные ячейки, ни одним из описанных ранее приемов перемещения таблицы воспользоваться нельзя. При попытке их применения Excel выдаст сообщение об ошибке (рис. 3.5).

Рис. 3.5 - Окно с предупреждением о невозможности изменения части объединенных ячеек

Поэтому сначала выделите строки 7:19 и, вызвав диалоговое окно Формат ячеек, отмените опцию Объединение ячеек, а затем выполните следующее:

1.Выделите область А7:А19 и задайте команду Копировать.

2.Переместите табличный курсор в ячейку В7 и щелчком правой кнопки мыши вызовите контекстное меню.

3.Активизируйте в нем команду Добавить скопированные ячейки.

В диалоговом окне Вставка скопированных ячеек (рис. 3.6) отметьте переклю­чатель диапазон, со сдвигом вправо и нажмите кнопку ОК.

Рис. 3.6 - Диалоговое окно Вставка скопированных ячеек

После вставки скопированных ячеек отформатируйте таблицу, а затем, восполь­зовавшись методом заполнения ячеек одинаковой информацией, отредактируйте формулу в ячейках В11 :В 19 следующим образом: =В10+$Е$5.

Теперь измените формулу, которая находится в области вычислений таблицы. Вы­делив диапазон C10:L19, щелкните мышью в строке формул (рис. 3.7) и в формуле

=$D$2*(1+$А10)^{^}С$9 скопируйте фрагмент (1+$А10)^{^}С$9.

Затем, нажав клавишу [End], переместите курсор в конец формулы, введите с клавиатуры знак деления «/» и вызовите команду Вставить. В появившемся фраг­менте формулы замените посредством клавиатуры адрес $А10 адресом $В10.

В результате у вас должна получиться такая формула:

=$D$2 *(1 + $А10)^{^}С$9/(1 + $В10)^{^}С$9.

Рис. 3.7 – Выделение фрагмента формулы в строке формул

Данный пример демонстрирует тот факт, что Excel позволяет производить копи­рование и вставку информации прямо в строке формул, что значительно облег­чает работу.

Перемещать курсор по строке формул в режиме редактирования можно не только посредством мыши. Вы можете использовать клавиши, которые применяются для пе­ремещения по рабочему листу и выделения диапазонов ячеек. В частности, для вы­деления указанного фрагмента в строке формул достаточно поместить курсор в ко­нец этой строки и нажать комбинацию клавиш [Shift+<~].

Рис. 3.8 - Фрагмент таблицы для расчета реальной стоимости денежных средств на депозите (с формулами)

На рис. 3.8 изображен фрагмент таблицы с формулами для расчета реальной стоимости денежных средств, размещенных на депозите, с учетом инфляции, а на рис. 3.9 — та же таблица с числовыми значениями. В таблице, представленной на рис. 3.9, начальная ставка процента по депозиту равна 1%, шаг изменения процента по депозиту — 1% за период. Начальный процент инфляции — 3%, а шаг изменения процента инфляции — 0,5% за период.

Рис 3.9 - Таблица для расчета реальной стоимости денежных средств на депозите

(с числовыми значениями)