Функции в опен офисе кальк. Использование функций OpenOffice Calc. SUMIF — суммирование по условию

51 Примечание. Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно. Пример 3. Какая сумма окажется на счете, если 2000 руб положены на 35 месяцев под 11% годовых? Проценты начисляются в начале каждого месяца. Примечание. Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно. 52 Пример 4. Какая сумма окажется на счете через год, если в начале каждо- го месяца вы будете помещать на счет по 100 руб под 6% годовых? На счете уже имеется 1000 руб. Примечание. Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно 12.2 Функция FVSCHEDULE Служит для расчета накопленной стоимости начального капитала для ряда переменных процентных ставок. Синтаксис FVSCHEDULE(Основной капитал; Ставки) Основной капитал: начальный капитал. Ставки: ряд процентных ставок, например диапазон H3:H5 или список (см. пример). Пример 1000 руб вложены на три года. Годовые процентные ставки составляли 3%, 4% и 5%. Какова будет стоимость инвестиции после трех лет? 53 =FVSCHEDULE(1000;{0,03;0,04;0,05}) также возвращает 1124,76. 12.3 Функция PV Возвращает текущую стоимость инвестиции после ряда платежей. Эта функция служит для расчета суммы, необходимой для инвестиций с фиксированным процентом для получения определенной суммы (годовой рен- ты) за указанное число периодов. Можно также определить, какая сумма будет оставаться после истечения периода. Кроме того, необходимо указать время для выплаты суммы: в начале или в конце каждого периода. Введите значения в виде чисел, выражений или ссылок. Например, если ежегодный процент составляет 8%, но в качестве периода требуется указать ме- сяц, введите 8%/12 в поле Процент и OpenOffice.org Calc автоматически рас- считает правильное значение. Синтаксис PV(Процент; КПЕР; ПЛТ; БС; Тип) Процент: процентная ставка за период. Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента Процент нужно ввести в формулу 10%/12 или 0,83% или 0,0083. КПЕР - общее число периодов (платежный период). Например, если по- лучена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента КПЕР в формулу нужно ввести число 48. Выплата - регулярная выплата за период. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10 000 54 руб. под 12 процентов годовых составит 263,33 руб. В качестве значения аргу- мента выплата нужно ввести в формулу число -263,33. БC (необязательно): будущая стоимость, которая останется после послед- него частичного платежа. Если аргумент опущен, он полагается равным 0 (буду- щая стоимость займа, например, равна 0). Например, если предполагается нако- пить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Можно сделать предположение о сохране- нии заданной процентной ставки и определить, сколько нужно откладывать каждый месяц. Тип (необязательно): срок выплат. "Тип = 1" означает, что срок приходит- ся на начало периода, а "Type = 0" (значение по умолчанию) означает, что срок приходится на конец периода. Замечания В функциях OpenOffice.org Calc необязательные параметры можно оставлять пустыми только в том случае, если за ними не следуют другие пара- метры. Например, если у функции четыре параметра и два последних пара- метра являются необязательными, то можно оставить пустыми параметры 3 и 4 или параметр 4. Оставить пустым параметр 3 невозможно. Убедитесь, что вы последовательны в выборе единиц измерения для за- дания аргументов Процент и КПЕР. Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента Процент и 4*12 для задания аргумента КПЕР. Если Вы делаете ежегодные платежи по тому же займу, то используйте 12% для задания аргумента Процент и 4 для задания аргумента КПЕР. Аннуитет - это ряд постоянных денежных выплат, делаемых в течение длительного периода. Например, заем под автомобиль или заклад являются аннуитетами. В функциях, связанных с аннуитетами, выплачиваемые денежные сред- ства, такие как депозит на сбережения, представляются отрицательным чис- лом; полученные денежные средства, такие как чеки на дивиденды, представ- ляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000 - для вкладчика и аргументом 1000 - для банка. Пример Какова текущая стоимость инвестиций, если ежемесячные выплаты со- ставляют 500 руб, а ежегодная процентная ставка - 8%? Платежный период - 48 месяцев; в конце платежного периода должно остаться 20000 руб. 55 При описанных выше условиях на текущий момент необходимо вложить 35 019,37 руб, чтобы ежемесячные выплаты составляли 500 руб в течение 48 месяцев, а остаток на конец периода был равен 20 000 руб. Перекрестная про- верка показывает, что 48 x 500 руб + 20 000 руб = 44 000 руб. Разница между этой суммой и вложенной суммой, равной 35 000 руб, - выплаченные проценты. Если вместо значений в формулу ввести ссылки, можно рассчитать любое число сценариев типа "если-то". Обратите внимание, что ссылки на константы должны быть определены как абсолютные ссылки. Примеры такого применения функции можно найти в описании функций амортизации. 12.4 Функция NPER Возвращает количество периодов для инвестиции с постоянными выпла- тами и постоянной процентной ставкой. Синтаксис КПЕР(Процент; ПЛТ; ТС; БС; Тип) Процент: процентная ставка за период. ПЛТ: постоянная годовая рента за каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.. ТС: текущая (денежная) стоимость ряда платежей. БС (необязательно): будущая стоимость на конец последнего периода. Тип (необязательно): дата платежа в начале (0) или конце (1) периода. В функциях OpenOffice.org Calc необязательные параметры можно остав- лять пустыми только в том случае, если за ними не следуют другие параметры. Например, если у функции четыре параметра и два последних параметра яв- 56 ляются необязательными, то можно оставить пустыми параметры 3 и 4 или па- раметр 4. Оставить пустым параметр 3 невозможно. Пример 1 Через сколько месяцев вклад размером 1000 руб. достигнет величины 10000 руб, если годовая ставка процента по вкладу 12 % и начисление произво- дится ежемесячно. Каждый месяц производится выплата 100 руб. Платежи осу- ществляются в начале каждого месяца. Пример 2 С целью создания финансового резерва фирма собирается вносить на свой счет по 1575 руб в начале каждого года. Через сколько лет сумма резерва до - стигнет 10000 руб, если предлагаемая банком норма составляет 7% годовых. 57 12.5 Функция RATE Возвращает значение процентной ставки за один период выплат. Синтаксис RATE(КПЕР; ПЛТ; ТС; БС; Тип; Предположение) КПЕР: общее количество периодов для платежей. ПЛТ: Регулярный платеж (один раз в период), величина которого остается постоянной в течение всего срока инвестиции. Обычно ПЛТ состоит из платежа основной суммы и платежа процентов, но не включает других сборов или нало- гов. Если аргумент опущен, должно быть указано значение аргумента БС ТС: Приведенная к текущему моменту стоимость или общая сумма, кото- рая на текущий момент равноценна ряду будущих платежей БС (необязательно): Требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент БС опущен, то он полагается равным 0 (например, БС для займа равно 0) Тип (необязательно): срок выплат в начале или конце периода. Тип Когда нужно платить 0 или опущен В конце периода 1 В начале периода Предположение (необязательно): предполагаемая величина процента при расчете методом итеративных вычислений. В функциях OpenOffice.org Calc необязательные параметры можно остав- лять пустыми только в том случае, если за ними не следуют другие параметры. Например, если у функции четыре параметра и два последних параметра яв- ляются необязательными, то можно оставить пустыми параметры 3 и 4 или па- раметр 4. Оставить пустым параметр 3 невозможно. Замечания Убедитесь, что вы последовательны в выборе единиц измерения для зада- ния аргументов прогноз и кол_пер. Если делаются ежемесячные выплаты по четырехгодичному займу под 12 процентов годовых, используйте 4*12 для зада- ния аргумента КПЕР. Если делаются ежегодные платежи по тому же займу, то используйте 4 для задания аргумента КПЕР. Пример 1 Вы хотите в начале каждого года помещать на счет по 2000 руб, на кото- ром в данный момент имеется 250 руб. Какая процентная ставка позволит через пять лет накопить 15000 руб? 58 Пример 2 В банке взята ссуда в размере 8 000 рублей на 4 года. Ежемесячная сумма платежа составляет 200 рублей. Вычислите месячную процентную ставку по займу. Примечание. Срок займа в годах умножен на 12, чтобы получить число месяцев. 59 12.6 Функция INTRATE Служит для расчета годовой процентной ставки при приобретении ценной бумаги за инвестиционную стоимость с последующей продажей за выкупную стоимость. Процент не выплачивается. Синтаксис INTRATE(Соглашение; Погашение; Инвестиция; Выкупная стоимость; Базис) Соглашение: дата приобретения ценных бумаг. (более поздняя, чем дата выпуска, т. е. когда ценные бумаги были проданы покупателю) Погашение: Эта дата определяет момент истечения срока действия цен- ных бумаг. Инвестиция: сумма приобретения. Выкупная стоимость: цена продажи. Это сумма, которая должна быть получена на момент погашения ценных бумаг. Базис: выбирается из списка и указывает метод вычисления года. Базис Вычисление 0 или отсутствует Американский метод (NASD): 12 месяцев по 30 дней в каждом 1 Точное число дней в месяцах, точное число дней в году 2 Точное число дней в месяцах, число дней в году принимается за 360 3 Точное число дней в месяцах, число дней в году принимается за 365 4 Европейский метод: 12 месяцев по 30 дней в каждом Замечания Дата соглашения является датой продажи покупателю купона, например облигации. Срок платежа представляет собой дату истечения срока действия купона. Пусть, например, облигация со сроком действия 30 лет выпущена 1 января 2008 года и была приобретена покупателем через шесть месяцев после своего выпуска. Датой выпуска будет являться 1 января 2008 года, датой со- глашения - 1 июля 2008 года, а сроком погашения такой облигации - 1 ян- варя 2038 года, то есть дата через 30 лет после даты выпуска. Соглашение, Погашение и базис усекаются до целых. Пример Картина была приобретена 15 января 2008 года за 1 миллион руб и прода- на 15 мая 2008 года за 1 014 420 миллиона руб. В качестве базиса используется расчет ежедневного остатка (Базис = 2). Требуется рассчитать среднюю годовую процентную ставку (ставку дисконтирования). 60 Примечание. Чтобы просмотреть числа в виде процентов, выделите ячей- ку и выберите в меню Формат команду Ячейки. На вкладке Числа выберите в списке Категория вариант Процентный. 12.7 Функция EFFECTIVE Возвращает годовой фактический процент для номинальной процентной ставки. Номинальная процентная ставка - размер процентной ставки на конец рас- четного периода. Фактическая процентная ставка повышается с количеством платежей. Другими словами, проценты часто выплачиваются в виде частичных платежей (например, ежемесячно или ежеквартально) до конца расчетного пе- риода. Синтаксис EFFECTIVE(Номинальная процентная ставка;П) Номинальная процентная ставка - номинальная процентная ставка. П: количество платежных периодов в году. Замечание Аргумент количество платежных периодов усекается до целого. Пример Если ежегодный уровень номинальной процентной ставки равен 5,25% и определено четыре периода выплат, какова фактическая процентная ставка?

Формула - это некое выражение, которое выполняет вычисление между операндами с помощью операторов.

Операнд - это элемент вычисления (константы, функции и ссылки). Константа - постоянное (не вычисляемое) значение. Может быть числом или текстом.

Функция - заранее созданная формула, выполняющая сложные вычисления по введенным значениям (аргументам) в строго определенном порядке. Функции бывают математическими, финансовыми, статистическими и т. п.

Операторы - это знак или символ, определяющий тип вычисления в формуле над операндами.

В Calc используются следующие операторы: математические, текстовые, операторы сравнения и операторы ссылок. Формула всегда начинается со знака равенства (=), а затем следуют операнды и операторы, например =24*3, или =А1+В2*(С10-D8)/ SUM(L12:H4), где 24, 3 - константы; SUM - функция автосуммирования; А1, В2, С10, D8, L12 и H4 - ссылки на адреса ячеек; +, -, / и * - операторы. Формулу можно вводить непосредственно в ячейку или же в окно ввода на строке формул. После вычисления в ячейке отображается полученный результат, а на строке формул в окне ввода - созданная формула.

Если выделить ячейку с формулой, то в самой ячейке будет отображаться результат вычисления, а в строке формул - ее формула. Операторы Математические операторы Математические операторы - это операторы простых действий: сложения, вычитания, умножения и т. д. Вводятся с помощью клавиатуры.

+ (плюс) - сложение;

– (минус) - вычитание или отрицание;

* (звездочка) - умножение;

/ (косая черта) - деление;

% (знак процента) - процент;

^ (знак крышки) - возведение в степень. Операторы сравнения Операторы сравнения - это операторы сравнения разных значений, результатом которого является логическое выражение ИСТИНА или ЛОЖЬ.

= (знак равенства) - показывает значения, равные заданному;

> (знак «больше») - показывает значения больше заданного;

< (знак «меньше») - показывает значения меньше заданного;

>= (знак «больше или равно») - показывает значения, которые больше или равны заданного;

<= (знак «меньше или равно») - показывает значения, которые меньше или равны заданному;

? (знак «не равно») - показывает значения, не равные заданному. текстовый оператор текстовый оператор амперсанд (&) - это оператор объединения нескольких текстовых отрывков в одну строку, например двух последовательностей знаков в одну последовательность. операторы ссылок оператор ссылок - это оператор ссылки на диапазон ячеек.

; (точка с запятой) - оператор объединения нескольких ссылок в одну, например sum(с10:в15; е45:т30);

() знак пробела - оператор пересечения множеств, который используется для ссылки на общие ячейки двух диапазонов. например, в10:d20 c15:c25.

каков приоритет операторов в случае использования в формуле нескольких операторов они выполняются в следующем порядке:

1. операторы ссылок (двоеточие, точка с запятой, пробел).

3. процент.

4. возведение в степень.

5. умножение и деление.

6. сложение и вычитание.

7. объединения нескольких текстовых отрывков в одну строку.

8. операторы сравнения.

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

Логические функции

Информационные функции

Функции для баз данных

Функции для массивов

Функция электронной таблицы - это предопределенное вычисление.

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

В среде Openoffice.org Calc существует множество функций. Стоит разделить их на 12 разделов:

1. Математические функции

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

3. Логические функции

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

5. Функции базы данных

6. Функции преобразования чисел

7. Информационные функции

8. Функции электронных таблиц

9. Функции комплексных чисел

10. Статические функции

11. Финансовые функции

12. Функции массивов

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

Ниже приведена таблица часто используемых математических функций Calc:

Так же, широким распространением пользуются логические функции.

Ниже приведена таблица логических функций Calc:

Рассмотрим несколько примеров использования простых функций.

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

Рис. 9. Мастер функций в Openoffice.org Calc.

Рассмотрим пошаговое его применение на простом примере использования математической функции ABS.

Рис. 10. Окно ввода.

Рис. 11. Использование Мастера функций.

3. В любое из выделенных красным полей необходимо ввести число, адрес ячейки, или же просто выбрать ячейку/диапазон ячеек левой кнопкой мыши. Далее нажимаем кнопку OK (желтое выделение). В нашем случае в ячейке B1 будет выполняться сама функция, а значение будет браться из ячейки A1.

Рис. 12. Использование Мастера функций.

4. Результатом выполнения функции будет следующее окно:

Рис. 13. Результат работы функции ABS.

А теперь рассмотрим использование логических функций на примере такой функции, как IF.

Нам понадобится знание нескольких математических функций.

Для начала сделаем подписи к будущим колонкам:

Ячейка А1 - Число 1;

Ячейка В1 - Число 2;

Ячейка С1 - Сумма;

Ячейка D1 - Функция IF.

1. Ячейки А2:А12 - числа от -5 до 5;

2. Ячейки B2:B12 - случайные числа от 1 до 5 (для простоты воспользуемся математической функцией RANDBETWEEN, получится запись =RANDBETWEEN(1;5), после чего растянем формулу на оставшиеся ячейки );