|
|
ФИНАНСОВЫЕ ФУНКЦИИ лаб работы. Лабораторная работа 1 Определение будущей стоимости на основе постоянной и переменной процентной ставки Цель работы
Цель работы: освоить применение функций ЕСЛИ, СЧЕТЕСЛИ, СУММ ЕСЛИ для анализа списка и функций ТЕНДЕНЦИЯ и РОСТ для составления прогнозов.
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
Функции для анализа списка – это функции, возвращающие информацию об элементах списка, которые удовлетворяют некоторым условиям.
Функция ЕСЛИ используется при проверке условий для значений и формул, возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Синтаксис: ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ ложь)
Лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае – ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.
Значение_если_истина – это значение, которое возвращается, если лог_вы ражение равно ИСТИНА. Например, если этот аргумент – строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.
Значение_если_ложь – это значение, которое возвращается, если лог_вы ражение равно ЛОЖЬ. Например, если этот аргумент – строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.
Функции ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь для конструирования более сложных проверок.
Для формирования логического выражения используются функции И, ИЛИ, НЕ и операторы.
Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Синтаксис: И(логическое_значение1; логическое_значение2; ...)
Логическое_значение1, логическое_значение2, ... – это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
Функция ИЛИ возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис: ИЛИ(логическое_значение1; логическое_значение2; ...)
Логическое_значение1, логическое_значение2,... – от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
Функция НЕ меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.
Синтаксис: НЕ(логическое_значение)
Логическое_значение – величина или выражение, которые могут принимать два значения: ИСТИНА или ЛОЖЬ.
Microsoft Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий: СЧЁТЕСЛИ и СУММ ЕСЛИ. Эти функции предназначены для работы со списками. Они проще в использовании, чем другие функции, применяемые для анализа списка, поскольку позволяют задавать условия непосредственно в формуле. Но в этих функциях можно использовать только простые условия сравнения.
Функция СЧЕТЕСЛИ имеет синтаксис: = СЧЕТЕСЛИ(диапазон; условие).
Аргумент диапазон задает диапазон, в котором подсчитывается количество значений, удовлетворяющих критерию. Критерий задается вторым аргументом условие, представляющим собой текстовое значение. Эта функция относится к категории статистических функций.
Функция СУММЕСЛИ имеет синтаксис:
= СУММЕСЛИ(диапазон; условие; диапазон_суммирования).
Первые два аргумента, диапазон и условие, используются так же, как и в функции СЧЕТЕСЛИ. Аргумент диапазон_суммирования задает диапазон суммируемых значений. Функция относится к категории математических функций.
Функция РОСТ рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Она возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и y-значений. Функция РОСТ может применяться также для аппроксимации существующих x- и y-значений экспоненциальной кривой.
Синтаксис: РОСТ(известные_значения_y; известные_значения_x; новые_ значения_x; конст)
Известные_значения_y – это множество значений y, которые уже известны в соотношении y = b*m^x.
Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.
Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.
Если какие-либо числа в массиве известные_значения_y равны 0 или отрицательны, то функция РОСТ возвращает значение ошибки #ЧИСЛО!.
Известные_значения_x – это необязательное множество значений x, которые уже известны для соотношения y = b*m^x.
Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).
Если известные_значения_x опущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.
Новые_значения_x – это новые значения x, для которых РОСТ возвращает соответствующие значения y.
Новые_значения_x должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_x. Таким образом, если из вестные_значения_y – это один столбец, то известные_значения_x и новые_зна чения_x должны иметь такое же количество столбцов. Если известные_значе ния_y – это одна строка, то известные_значения_x и новые_значения_x должны иметь такое же количество строк.
Если аргумент новые_значения_x опущен, то предполагается, что он совпадает с аргументом известные_значения_x.
Если оба аргумента известные_значения_x и новые_значения_x опущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.
Конст – логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1.
Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.
Если конст имеет значение ЛОЖЬ, то b полагается равным 1, а значения m подбираются так, чтобы y = m^x.
Функция ТЕНДЕНЦИЯ имеет аналогичный синтаксис и возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией массивы известные_значения_y и известные_значения_x. Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x.
ПРАКТИКУМ
Задача 1. Имеется таблица учета налога на транспорт.
№ п/п
|
Наименование объекта налогообложения
|
Мощность двигателя (л. с.)
|
Налог
|
1
|
Снегоход
|
70
|
|
2
|
Мотосани
|
45
|
|
3
|
Гидроцикл
|
240
|
|
4
|
Самолет с обычным двигателем
|
400
|
|
5
|
Самолет с реактивным двигателем
|
800
|
|
Вычислите с помощью функции ЕСЛИ сумму налога на каждое транспортное средство согласно таблицы (приложение 12).
Алгоритм решения задачи
Рисунок 38 – Пример использования функции
ЕСЛИ
Сначала сформируем таблицу и занесем в нее соответствующие данные (диапазон ячеек A12:D17). Чтобы функция не ссылалась на числа, нужно приложение 12 оформить в виде правильно сформированной информационной базы на листе с решением задачи и обращаться к адресам ячеек. Информационная база сформирована в диапазоне А2:С10. После этого в ячейку D13 занесем расчетную формулу с использованием функции =ЕСЛИ(И(B13=$A$3; C13<=$B$3); C13*$C$3; ЕСЛИ(И(B13=$A$4; C13> $B$4); C13*$C$4; ЕСЛИ(И(B13=$A$5; C13<=$B$5); C13*$C$5; ЕСЛИ(И(B13= $A$6; C13>$B$6); C13*$C$6; ЕСЛИ(И (B13=$A$7; C13<=$B$7); C13*$C$7; ЕСЛИ(И(B13=$A$8; C13>$B$8); C13* $C$8; ЕСЛИ(B13=$A$9; C13*$C$9; C13*$C$10))))))). Чтобы учесть все условия приложения, функция ЕСЛИ шесть раз является вложенной. После этого функцию нужно скопировать в блок ячеек D14:D17. Результат решения задачи показан на рисунке 38.
Задача 2. Определить число сотрудников, у которых оклад не превышает 4 500 руб. (см. рисунок 19).
Алгоритм решения задачи
Задачу решим с помощью функции = СЧЕТЕСЛИ(Е2:Е8; "<=4500").
Ответ: 2.
Задача 3. Подсчитать сумму окладов всех сотрудников, работающих в бухгалтерии (см. рисунок 19).
Алгоритм решения задачи
Задачу решим с помощью функции = СУММЕСЛИ(D2:D8; "Бухгалтерия"; Е2:Е8). Первый аргумент задает диапазон D2:D8, в котором осуществляется проверка условия. Второй аргумент – отдел должен быть «Бухгалтерия». Если условие выполняется, то значение из диапазона Е2:Е8 (третий аргумент) добавляется к наращиваемой сумме.
Ответ: 27940 руб.
Задача 4. Курс акции в 2012 г. составлял значения, представленные в таблице. Определите, какая из функций РОСТ или ТЕНДЕНЦИЯ лучше подходит для прогноза и рассчитайте предполагаемый курс акции на 01.09.2013.
Дата
|
Курс (руб.)
|
Дата
|
Курс (руб.)
|
01.01.2012
|
200
|
01.07.2012
|
245
|
01.02.2012
|
210
|
01.08.2012
|
247
|
01.03.2012
|
221
|
01.09.2012
|
250
|
01.04.2012
|
230
|
01.10.2012
|
254
|
01.05.2012
|
234
|
01.11.2012
|
260
|
01.06.2012
|
240
|
01.12.2012
|
264
|
Алгоритм решения задачи
Пример решения задачи показан на примере (рисунки 39а, 39б). В блок ячеек А3:В14 занесены исходные данные. В ячейки А15:А23 занесены даты (первая дата каждого месяца), включая дату, на которую нужно сделать прогноз. Строим два линейных графика по данным за 2012 год с помощью команд Вставка → Диаграммы → График. Отображаем линии тренда вместе с коэффициентом достоверности аппроксимации: на первом графике линейный тренд, на втором экспоненциальный. Для этого устанавливаем курсор мыши на линию графика и из динамического меню выбираем команду Добавить линию тренда. Поскольку величина достоверности аппроксимации для линейного тренда выше величины достоверности аппроксимации для экспоненциального тренда (0,9513 > 0,9336), то для прогноза нужно выбрать функцию ТЕНДЕНЦИЯ, она даст более точный прогноз. В ячейку В15 заносим функцию: =ТЕНДЕНЦИЯ(B3:B14; A3:A14; А15:А23; 1) и скопируем ее в ячейки В16:В23.
а) б)
Рисунок 39 – Линии тренда и коэффициенты достоверности аппроксимации
|
|
|
Скачать 1.61 Mb.