prosdo.ru
добавить свой файл
1 2

Страница из Лекция 7 Тема «Использование функций в Microsoft Excel 2010»




7. Лекция: Использование функций в Microsoft Excel 2010

Лекция посвящена вопросам использования функций в вычислениях. Дано представление о математических функциях и описаны их возможности. Показана возможность выборочного суммирования. Рассмотрена функция для вычисления произведения. Представлены функции для округления, указаны особенности использования различных функций. Показаны функции для тригонометрических вычислений. Представлены функции для преобразования чисел, описаны особенности их использования. Рассмотрены функции для расчета числа комбинаций и факториала. Показана функция для задания случайных значений. Представлены статистические функции для расчета средних значений, поиска наибольших и наименьших значений, расчета количества ячеек. Рассмотрены финансовые функции для расчета амортизационных отчислений при использовании различных методик расчета амортизации. Рассмотрены финансовые функции для анализа инвестиций. Показана возможность расчета суммы вклада и стоимости инвестиции. Рассмотрены функции, отображающие текущие обновляемые значения даты и времени. Рассмотрены функции для преобразования регистра текстовых данных. Показана возможность объединения текстовых данных разных ячеек. Дано представление о логических функциях. Рассмотрены функции для проверки и анализа значений. Показаны возможности условных вычислений. Показаны функции для просмотра и поиска значений в строках и столбцах.

7. Лекция: Использование функций в Microsoft Excel 2010 1

7.1 Математические вычисления 1

7.1.1 О математических и тригонометрических функциях 2

7.1.2 Суммирование 2

7.1.2.1 Простая сумма 2

7.1.2.2 Выборочная сумма 2

7.1.3 Умножение 3

7.1.4 Округление 3

7.1.5 Возведение в степень 5

7.1.6 Тригонометрические вычисления 5

7.1.7 Преобразование чисел 5

7.1.8 Комбинаторика 7

7.1.9 Генератор случайных чисел 7

7.2 Статистические вычисления 7

7.2.1 О статистических функциях 7

7.2.2 Расчет средних значений 7

7.2.3 Нахождение крайних значений 8

7.2.4 Расчет количества ячеек 9

7.3 Финансовые вычисления 10

7.3.1 О финансовых функциях 10

7.3.2 Расчет амортизационных отчислений 11

7.3.3 Анализ инвестиций 12

7.3.4 Расчет суммы вклада (величины займа) 12

7.3.5 Расчет стоимости инвестиции 13

7.4 Функции даты и времени 14

7.4.1 О функциях даты и времени 14

7.4.2 Автоматически обновляемая текущая дата 14

7.4.3 День недели произвольной даты 14

7.5 Текстовые функции 15

7.5.1 О текстовых функциях 15

7.5.2 Преобразование регистра текста 15

7.5.3 Объединение текста 15

7.6 Использование логических функций 16

7.6.1 О логических функциях 16

7.6.2 Проверка и анализ данных 16

7.6.3 Условные вычисления 17

7.7 Функции просмотра и ссылок 17

7.7.1 О функции просмотра и ссылок 17

7.7.2 Поиск значений в таблице 17





7.1 Математические вычисления

7.1.1 О математических и тригонометрических функциях

Математические и тригонометрические функции используют при выполнении арифметических и тригонометрических вычислений, округлении чисел и в некоторых других случаях. Всего в данной категории имеется 64 функции.


7.1.2 Суммирование

7.1.2.1 Простая сумма

Для простейшего суммирования используют функцию СУММ.

Синтаксис функции

СУММ(А) ,

где А - список от 1 до 30 элементов, которые требуется суммировать. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Формула =СУММ(В2:В6) , указанная в ячейке В7 (рис.7.1), тождественна формуле =В2+В3+В4+В5+В6. Однако есть и некоторые отличия. При использовании функции СУММ добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.



Рис. 7.1.  Простое суммирование

7.1.2.2 Выборочная сумма

Иногда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям). В этом случае используют функцию СУММЕСЛИ.

Синтаксис функции

СУММЕСЛИ(А;В;С) ,

где А - диапазон вычисляемых ячеек.

В - критерий в форме числа, выражения или текста, определяющего суммируемые ячейки;

С - фактические ячейки для суммирования.

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

Можно суммировать значения, отвечающие заданному условию. Например, в таблице на рис.7.2 суммированы только студенты по странам, при условии, что число студентов от страны превышает 200.


Рис. 7.2.  Выборочное суммирование

Можно суммировать значения, относящиеся к определенным значениям в смежных ячейках. Например, в таблице на рис.7.3 суммированы только студенты, изучающие курсы со средней оценкой выше 4,1. Критерий можно ввести с клавиатуры или выбрать нужную ячейку на листе.



Рис. 7.3.  Выборочное суммирование

7.1.3 Умножение

Для умножения используют функцию ПРОИЗВЕД.

Синтаксис функции

ПРОИЗВЕД(А) ,

где А - список от 1 до 30 элементов, которые требуется перемножить. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Фактически данная функция заменяет непосредственное умножение с использованием оператора умножения (*). Так же как и при использовании функции СУММ, при использовании функции ПРОИЗВЕД добавление ячеек в диапазон перемножения автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон перемножения. Аналогично формула будет изменяться и при уменьшении диапазона.

7.1.4 Округление

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

Для округления чисел можно использовать целую группу функций.

Наиболее часто используют функции ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.

Синтаксис функции ОКРУГЛ

ОКРУГЛ(А;В) ,

где А - округляемое число;

В - число знаков после запятой (десятичных разрядов), до которого округляется число.

Синтаксис функций ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ точно такой же, что и у функции ОКРУГЛ.

Функция ОКРУГЛ при округлении отбрасывает цифры меньшие 5, а цифры большие 5 округляет до следующего разряда. Функция ОКРУГЛВВЕРХ при округлении любые цифры округляет до следующего разряда. Функция ОКРУГЛВНИЗ при округлении отбрасывает любые цифры. Пример округления до двух знаков после запятой с использованием функций ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ приведен на рис.7.4.



Рис. 7.4.  Округление до заданного количества десятичных разрядов

Функции ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ можно использовать и для округления целых разрядов чисел. Для этого необходимо использовать отрицательные значения аргумента В.

Для округления числа до меньшего целого можно использовать функцию ЦЕЛОЕ.

Синтаксис функции

ЦЕЛОЕ(А),

где А - округляемое число.

Пример использования функции приведен на рис.7.5.



Рис. 7.5.  Округление до целого числа

Наконец, для округления до ближайшего четного или нечетного числа можно использовать функции ЧЕТН и НЕЧЕТН, а для ближайшего кратного большего или меньшего числа - функции ОКРВЕРХ и ОКРВНИЗ.

Синтаксис функции ЧЕТН

ЧЕТН(А) ,

где А - округляемое число.

Функция НЕЧЕТН имеет такой же синтаксис.

Обе функции округляют положительные числа до ближайшего большего четного или нечетного числа, а отрицательные - до ближайшего меньшего четного или нечетного числа.


Синтаксис функции ОКРВВЕРХ

ОКРВВЕРХ(А;В) ,

где А - округляемое число;

В - кратное, до которого требуется округлить.

Функция ОКРВНИЗ имеет такой же синтаксис.

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

7.1.5 Возведение в степень

Для возведения в степень используют функцию СТЕПЕНЬ.

Синтаксис функции

СТЕПЕНЬ(А;В) ,

где А - число, возводимое в степень;

В - показатель степени, в которую возводится число.

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

Для извлечения квадратного корня можно использовать функцию КОРЕНЬ.

Синтаксис функции

КОРЕНЬ(А) ,

где А - число, из которого извлекают квадратный корень.

Нельзя извлекать корень из отрицательных чисел.

7.1.6 Тригонометрические вычисления

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

Синтаксис всех прямых тригонометрических функций одинаков. Например, синтаксис функции SIN

SIN(А) ,

где А - угол в радианах, для которого определяется синус.

Точно так же одинаков и синтаксис всех обратных тригонометрических функций. Например, синтаксис функции АSIN

АSIN(А) ,

где А - число, равное синусу определяемого угла.

Следует обратить внимание, что все тригонометрические вычисления производятся для углов, измеряемых в радианах. Для перевода в более привычные градусы следует использовать функции преобразования ( ГРАДУСЫ, РАДИАНЫ ) или самостоятельно переводить значения используя функцию ПИ().


Функция ПИ() вставляет значение числа (пи). Аргументов функция не имеет, но скобки после названия удалять нельзя.

Например, при необходимости рассчитать значение синуса угла, указанного в градусах, необходимо его умножить на ПИ()/180.



Рис. 7.6.  Вычисление тригонометрических функций для углов, указанных в градусах

7.1.7 Преобразование чисел

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

Для перевода значения угла, указанного в радианах, в градусы используют функцию ГРАДУСЫ.

Синтаксис функции

ГРАДУСЫ(А) ,

где А - угол в радианах, преобразуемый в градусы.

Для перевода значения угла, указанного в градусах, в радианы используют функцию РАДИАНЫ.

Синтаксис функции

РАДИАНЫ(А) ,

где А - угол в градусах, преобразуемый в радианы.

Функции ГРАДУСЫ и РАДИАНЫ удобно использовать с тригонометрическими функциями. Например, при необходимости рассчитать значение синуса угла, указанного в градусах (рис.7.7), или рассчитать в градусах значение арксинуса (рис.7.8).



Рис. 7.7.  Вычисление тригонометрических функций для углов, указанных в градусах



Рис. 7.8.  Вычисление углов в градусах при использовании тригонометрических функций

Для определения абсолютной величины числа используют функцию ABS. Абсолютная величина числа - это число без знака.


Синтаксис функции

ABS(А) ,

где А - число, для которого определяется абсолютное значение.

Функция ABS часто применяется для преобразования результатов вычислений с использованием финансовых функций, которые в силу своих особенностей дают отрицательный результат вычислений. Например, при расчете стоимости инвестиции с использованием функции ПС результат получается отрицательным, поскольку эту сумму необходимо заплатить. Для преобразования результата в положительное число можно использовать функцию ABS (рис.7.9).



Рис. 7.9.  Преобразование в положительное число

Для преобразования числа, записанного арабскими цифрами в число, записанное римскими цифрами, используют функцию РИМСКОЕ.

Синтаксис функции

РИМСКОЕ(А; В) ,

где А - число, записанное арабскими цифрами;

В - форма записи числа.

Если значение аргумента В не указано или указано число 0, то используется классическая форма записи римского числа. При значениях аргумента В от 1 до 4 используются различные формы упрощенной записи римских чисел.

Функцию РИМСКОЕ нельзя использовать для отрицательных чисел, а также для чисел больше 3999.

7.1.8 Комбинаторика

Для расчета числа возможных комбинаций (групп) из заданного числа элементов используют функцию ЧИСЛКОМБ.

Синтаксис функции

ЧИСЛКОМБ(А; В) ,

где А - число элементов;

В - число объектов в каждой комбинации.

Во вспомогательных расчетах в комбинаторике может потребоваться расчет факториала числа. Факториал числа - это произведение всех чисел от 1 до числа, для которого определяется факториал. Например, факториал числа 6 (6!) равен 1*2*3*4*5*6. Для расчета факториала используют функцию ФАКТР.


Синтаксис функции

ФАКТР(А) ,

где А - число, для которого рассчитывается факториал.

Факториал нельзя рассчитать для отрицательных чисел. Факториал число 0 (ноль) равен 1. При расчете факториала дробных чисел десятичные дроби отбрасываются.

7.1.9 Генератор случайных чисел

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

Для создания такого числа используют функцию СЛЧИС(). Функция вставляет число, большее или равное 0 и меньшее 1. Новое случайное число вставляется при каждом вычислении в книге. Аргументов функция не имеет, но скобки после названия удалять нельзя.

7.2 Статистические вычисления

7.2.1 О статистических функциях

Статистические функции используют при анализе данных. Использование большинства функций этой категории требует знания математической статистики и теории вероятностей. Всего в данной категории имеется 98 функций.

7.2.2 Расчет средних значений

В самом простом случае для расчета среднего арифметического значения используют функцию СРЗНАЧ.

Синтаксис функции

СРЗНАЧ(А) ,

где А - список от 1 до 30 элементов, среднее значение которых требуется найти. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Если в диапазон, для которого рассчитывают среднее значение, попадают данные, существенно отличающиеся от остальных, расчет простого среднего арифметического может привести к неправильным выводам. В этом случае следует использовать функцию УРЕЗСРЕДНЕЕ. Эта функция вычисляет среднее, отбрасывая заданный процент данных с экстремальными значениями.

Синтаксис функции

УРЕЗСРЕДНЕЕ(А;В) ,

где А - список от 1 до 30 элементов, среднее значение которых требуется найти. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются;


В - доля данных, исключаемых из вычислений.

Доля данных, исключаемых из вычислений, указывается в процентах от общего числа данных. Например, доля 10 % означает, что из данных, содержащих 20 значений, отбрасываются 2 значения: одно наибольшее, другое - наименьшее. В таблице на рис.7.10 величина брака по одному из товаров (34 %) существенно отличается от остальных значений (8% и 34 %). Среднее арифметическое значение данных составляет 2,56 % (ячейка Е1 ), что дает несколько искаженную картину реальных значений. Расчет среднего значения с использованием функции УРЕЗСРЕДНЕЕ (ячейка Е2 ) дает более правильное представление о средних величинах брака в партиях товаров (0,96 %).



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

При расчете средних темпов изменения какого-либо параметра более верное представление дает не среднее арифметическое, а среднее геометрическое значение. Особенно удобно пользоваться средним геометрическим значением при расчете средних темпов роста производства, среднего процента по вкладу и т. д. Для расчета среднего геометрического значения используют функцию СРГЕОМ.

Синтаксис функции:

СРГЕОМ(А) ,

где А - список от 1 до 30 элементов, среднее геометрическое значение которых требуется найти. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Например, для данных таблицы на рис.7.11 средний прирост реализации (среднее геометрическое) составит 3,46 % (ячейка Е3 ), в то время как среднее значение 4,33 % (ячейка Е2 ).


Рис. 7.11.  Расчет среднего геометрического


7.2.3 Нахождение крайних значений

Для нахождения крайних (наибольшего или наименьшего) значений в диапазоне данных используют функции МАКС и МИН.

Синтаксис функции МАКС:

МАКС(А) ,

где А - список от 1 до 30 элементов, среди которых требуется найти наибольшее значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Функция МИН имеет такой же синтаксис, что и функция МАКС.

Функции МАКС и МИН только определяют крайние значения, но не показывают, в какой ячейке эти значения находятся.

В тех случаях, когда требуется найти не самое большое (самое маленькое) значение, а значение, занимающее определенное положение в диапазоне данных (например, второе или третье по величине), следует использовать функции НАИБОЛЬШИЙ или НАИМЕНЬШИЙ.

Синтаксис функции НАИБОЛЬШИЙ:

НАИБОЛЬШИЙ(А; В),

где А - список от 1 до 30 элементов, среди которых требуется найти значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются;

В - позиция (начиная с наибольшей) в множестве данных. Если требуется найти второе значение по величине, то указывается позиция 2, если третье, то позиция 3 и т. д.

Функция НАИМЕНЬШИЙ имеет такой же синтаксис, что и функция НАИБОЛЬШИЙ.

Например, для данных таблицы на рис.7.12 второе по величине значение составит 16501, а второе из наименьших - 7.



Рис. 7.12.  Нахождение значений по относительному местоположению

7.2.4 Расчет количества ячеек

Для определения количества ячеек, содержащих числовые значения, можно использовать функцию СЧЕТ.


Синтаксис функции:

СЧЕТ(А) ,

где А - список от 1 до 30 элементов, среди которых требуется определить количество ячеек, содержащих числовые значения. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Например, в таблице на рис.7.13 числовые значения в столбце В содержат 320 ячеек.



Рис. 7.13.  Расчет количества ячеек, содержащих числа

Если требуется определить количество ячеек, содержащих любые значения (числовые, текстовые, логические), то следует использовать функцию СЧЕТЗ.

Синтаксис функции:

СЧЕТЗ(А ),

где А - список от 1 до 30 элементов, среди которых требуется определить количество ячеек, содержащих любые значения. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки игнорируются.

Наоборот, если требуется определить количество пустых ячеек, следует использовать функцию СЧИТАТЬПУСТОТЫ.

Синтаксис функции:

СЧИТАТЬПУСТОТЫ(А) ,

где А - список от 1 до 30 элементов, среди которых требуется определить количество пустых ячеек. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на ячейки с нулевыми значениями игнорируются.

Можно также определять количество ячеек, отвечающих заданным условиям. Для этого используют функцию СЧЕТЕСЛИ.

Синтаксис функции:

СЧЕТЕСЛИ(А;В),

где А - диапазон проверяемых ячеек;

В - критерий в форме числа, выражения или текста, определяющего суммируемые ячейки;

Можно найти количество ячеек со значениями, отвечающими заданному условию. Например, в таблице на рис.7.14 подсчитано количество курсов, которые изучают более 1000 студентов.




Рис. 7.14.  Расчет количества ячеек, отвечающих заданным условиям

7.3 Финансовые вычисления

7.3.1 О финансовых функциях

Финансовые функции используют в планово-экономических расчетах. Всего в категории "Финансовые" имеется 53 функции.

7.3.2 Расчет амортизационных отчислений

Для расчета амортизационных отчислений необходимо знать, по крайней мере, три параметра:


  1. начальная стоимость имущества;

  2. остаточная стоимость по окончании эксплуатации;

  3. продолжительность эксплуатации.

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

В простейшем случае амортизация разносится равномерно на каждый год эксплуатации имущества. Для расчета величины амортизационных отчислений в этом случае используют функцию АПЛ.

Синтаксис функции:

АПЛ(А;В;С),

где А - начальная стоимость имущества;

В - остаточная стоимость имущества;

С - продолжительность эксплуатации.

Например, приобретено оборудование стоимостью 97000 руб. Продолжительность эксплуатации оборудования - 8 лет. Остаточная стоимость - 7500 руб. Величина амортизационных отчислений составит 11187,50 руб. за каждый и любой год эксплуатации (рис.7.15).



Рис. 7.15.  Расчет амортизационных отчислений линейным методом

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


Синтаксис функции:

АСЧ(А;В;С;D),

где А - начальная стоимость имущества;

В - остаточная стоимость имущества;

С - продолжительность эксплуатации;

D - год, для которого рассчитывается величина амортизационных отчислений.

Например, приобретено оборудование стоимостью 100000 руб. Продолжительность эксплуатации оборудования - 8 лет. Остаточная стоимость - 12000 руб. Величина амортизационных отчислений за первый год эксплуатации составит 19 555,56 руб., за второй год - 17 111,11 руб. и т. д. (рис.7.16).



Рис. 7.16.  Расчет амортизационных отчислений методом суммы чисел

7.3.3 Анализ инвестиций

Использование сложных процентов необходимо при вычислении доходности вкладов (инвестиций), сумм платежей по кредитам, сумм страховых платежей и т. п.

Во всех этих случаях для расчета необходимо знать, по крайней мере, три параметра:


  1. процентная ставка за период;

  2. общее число периодов платежей;

  3. выплата, производимая в каждый период или общая сумма.

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

7.3.4 Расчет суммы вклада (величины займа)

В простейших случаях для расчета можно использовать функцию БС. Эта функция вычисляет для будущего момента времени величину вложения, которое образуется в результате единовременного вложения и/или регулярных периодических вложений под определенный процент. Эту же функцию можно использовать для вычисления возможной величины займа под определенный процент, при определенных регулярных периодических выплатах по погашению займа.

Синтаксис функции

БС(А;В;С;D;Е),


где А - процентная ставка за период;

В - общее число платежей;

С - выплата, производимая в каждый период и не меняющаяся за все время выплаты;

D - требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0);

Е - число 0 или 1, обозначающее, когда должна производиться выплата. 0 или опущен - в конце периода, 1 - в начале периода.

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

Например, необходимо рассчитать будущую сумму вклада в размере 10000 руб., внесенного на 10 лет с ежегодным начислением 10% (рис.7.17). Или будущую сумму вклада при тех же условиях, но с ежегодным внесением 10000 руб. (рис.7.18).



Рис. 7.17.  Расчет величины вклада с начальным взносом



Рис. 7.18.  Расчет величины вклада с начальным взносом при регулярном пополнении

Результат вычисления: в первом случае - 2593,74 руб., во втором - 18531,17руб.

7.3.5 Расчет стоимости инвестиции

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

Синтаксис функции

ПС(А;В;С;D;Е),

где А - процентная ставка за период.


В - общее число платежей.

С - выплата, производимая в каждый период и не меняющаяся за все время выплаты.

D - значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0.

Е - число 0 или 1, обозначающее, когда должна производиться выплата. 0 или опущен - в конце периода, 1 - в начале периода.

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

Например, необходимо рассчитать величину вложения под 10 % годовых, которое через 10 лет принесет доход 10000 руб. (рис.7.19).



Рис. 7.19.  Расчет стоимости инвестиции

Результат вычисления получается отрицательным (-3855,43 руб.) поскольку эту сумму необходимо заплатить.

7.4 Функции даты и времени

7.4.1 О функциях даты и времени

Функции дат и времени используют для преобразования форматов даты и времени, вычислений промежутков времени, а также для вставки на лист автоматически обновляемых значений даты и времени. Всего в категории "Дата и время" имеется 22 функции.

7.4.2 Автоматически обновляемая текущая дата

Текущая дата

Для вставки текущей автоматически обновляемой даты используется функция СЕГОДНЯ().



Рис. 7.20.  Вставка сегодняшней даты

Функция аргументов не имеет.

Значение в ячейке будет обновляться при открытии файла.

Текущие дата и время

Для вставки текущей даты и времени можно использовать функцию ТДАТА (рис.7.21).




Рис. 7.21.  Вставка текущего значения даты и времени

Функция аргументов не имеет.

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

7.4.3 День недели произвольной даты

Для вычисления дня недели любой произвольной даты можно использовать функцию ДЕНЬНЕД (рис.7.22).

Синтаксис функции

ДЕНЬНЕД(А;В) ,

где А - дата, для которой определяется день недели. Дату можно вводить обычным порядком;

В - тип отсчета дней недели. 1 - отсчет дней недели начинается с воскресенья. 2 - отсчет дней недели начинается с понедельника.



Рис. 7.22.  Вычисления дня недели с использованием функции ДЕНЬНЕД

7.5 Текстовые функции

7.5.1 О текстовых функциях

Текстовые функции используют для преобразования и анализа текстовых значений.

7.5.2 Преобразование регистра текста

Для преобразования регистра текста используются три функции: ПРОПИСН, ПРОПНАЧ, СТРОЧ.

Функция ПРОПИСН преобразует все буквы в прописные, функция ПРОПНАЧ преобразует в прописные только первую букву каждого слова, а функция СТРОЧ преобразует все буквы в строчные.

Синтаксис всех функций одинаков:

ПРОПИСН(А) ,

ПРОПНАЧ(А) ,

СТРОЧ(А) ,

где А - ячейка с преобразуемым текстом.

Примеры использования функций приведены в таблице на рис.7.23. В ячейке С1 формула ПРОПИСН(А1), в ячейке С2 формула ПРОПНАЧ(А1), в ячейке С3 формула СТРОЧ(А1) .




Рис. 7.23.  Преобразование текста

7.5.3 Объединение текста

Для объединения текста из разных ячеек используют функцию СЦЕПИТЬ.

Синтаксис функции:

СЦЕПИТЬ(А) ,

где А - список от 1 до 30 элементов, текст которых требуется объединить. Элемент может быть ячейкой, текстом или числом. Ссылки на пустые ячейки игнорируются. Нельзя использовать ссылки на диапазоны смежных ячеек.

На рис.7.24 показан пример объединения текста. Текст "Студент " и пробел введены с клавиатуры, остальные данные взяты из ячеек таблицы.



Рис. 7.24.  Объединение текста

7.6 Использование логических функций

7.6.1 О логических функциях

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

Вместо функций ЛОЖЬ и ИСТИНА можно непосредственно ввести слово с клавиатуры в ячейку или в формулу.

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

Оператор

Значение




следующая страница >>