Формула процента заполненных ячеек в open office. Использование функций OpenOffice Calc. О чем будем говорить
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% и определено четыре периода выплат, какова фактическая процентная ставка?
Использование имен ячеек и диапазонов в формулах
Если в формуле используются имена ячеек иди диапазонов, вы можете набрать имя вместо адреса или выбрать имя из списка, чтобы OpenOffice Calc вставила его автоматически.
Задание:
Перейдите в ячейку F8.
В строку формул методом указания ячеек введите формулу (суммируйте итоговые значение окладов и надбавок) . Убедитесь, что вместо адресов ячеек отображаются их имена рис. 16.
Рис. 16
Перейдите в ячейку F9.
В строке формул наберите выражение =SUM(.
Рис. 17
Выберите команду «Вставкаð Названиеð Вставить». В диалоговом окне «Вставка имени», в котором перечислены все определенные в данной рабочей книге имена, выберите имя «Суммы» и нажмите кнопку «ОК» рис. 17.
Закройте скобку.
Рис. 18.
Нажмите клавишу
Сохраните рабочую книгу.
Логические функции. Функция IF (ЕСЛИ)
Логические функции выполняют условное вычисление по формулам и позволяют осуществить реализацию простых алгоритмов принятия решений.
Функции IF (ЕСЛИ) возвращает одно значение, если заданное условие при вычислении дает значение TRUE (ИСТИНА), и другое значение, если FALSE (ЛОЖЬ). Функция IF (ЕСЛИ) используется при проверке условий для значений и формул.
Синтаксис функции:
IF (ЕСЛИ) (Лог_выражение; Значение_если_истина; Значение_если_ложь)
Предположим, что вам нужно подсчитать в рабочей таблице комиссионные по результатам продаж какого-либо товара. Если продавец продал продукции более, чем на $100 000, то ставка его комиссионных составляет 7,5%, если меньше, то 5%. Без использования функции вам пришлось бы создавать две разные формулы и правильно употреблять их для каждого значения объема продаж. Ниже приведена функция, которая позволяет рассчитать комиссионные с учетом суммы продаж.
IF (А1<100000; A1*0,05; A1*0,075), где
IF (ЕСЛИ) – функция, которая проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет;
А1<100000 – условие;
A1*0,05 – значение, которое возвращается, если условие выполняется;
A1*0,075 – значение, которое возвращается, если условие не выполняется.
Таким образом, если значение ячейки А1 (сумма продаж) будет меньше 100000 (условие выполняется) программа умножит значение ячейки А1 на 0,05 (ставка комиссионных), если же сумма продаж будет больше 100000 (условие не выполняется), программа умножит значение ячейки А1 на 0,075.
Задание:
Откройте окно Мастер функций, выберите категорию Логические функции.
На Листе3 создайте таблицу табл.3.
Таблица 3
| Норма продаж | 150 000,00 | ||
| Ставка комиссионных | 0,055 | ||
| Ставка премиальных | 0,075 | ||
| Менеджеры | Продажи | Комиссионные | |
| Орлов | 149 823,00 | ||
| Воробьев | 162 023,00 | ||
| Скворцов | 209 123,00 | ||
| Синицын | 122 354,00 | ||
| Грачев | 83 351,00 | ||
| Петухов | 204 861,00 | ||
| Ганиев | 150 000,00 | ||
| Самойлов | 110 500,00 | ||
| Дроздов | 220 120,00 | ||
| Кротов | 170 450,00 |
Отформатируйте таблицу согласно рисунку
Рис. 19.
Вычислите комиссионные от продаж, если ставка комиссионных составляет 5,5%, ставка премиальных – 7,5%, норма продаж – 150 000р. Для этого выполните следующие действия.
Активизируйте ячейку С6 (комиссионные Орлова), вызовите Мастер функций, найдите в нем функцию IF (ЕСЛИ).
В поле Логическое выражение введите условие B6<В1 (продажи Орлова меньше нормы продаж).
В поле Значение если true (истина) введите выражение B6*B2 (если продажи Орлова меньше нормы продаж, то для расчета комиссионных необходимо величину продаж умножить на ставку комиссионных).
В поле Значение если false (ложь) введите выражение B6*B3 (если продажи Орлова больше нормы продаж, то для расчета комиссионных необходимо величину продаж умножить на ставку премиальных).
Проверьте правильность ввода аргументов рис.20
Рис. 20
Рассчитайте комиссионные по остальным продавцам. Для этого используйте функцию Автозаполнение, предварительно предусмотрев абсолютные ссылки.
Рис. 21
Сохраните рабочую книгу.
Статистические функции. Функция COUNTIF (СЧЕТЕСЛИ) (СЧЕТЕСЛИ)
К категории статистических функций отнесено огромное количество функций – 80, позволяющих выполнить разнообразные расчеты. Многие из этих функций достаточно специализированные, но некоторые из них полезны и для тех, кто мало знаком со статистикой.
Функция COUNTIF (СЧЕТЕСЛИ) подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
Синтаксис функции: COUNTIF (СЧЕТЕСЛИ) (Диапазон; Критерий).
Диапазон - диапазон, в котором нужно подсчитать ячейки.
Критерий - критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".
Задание:
Откройте окно Мастер функций, выберите категорию Статистические.
Просмотрите список функций, ознакомьтесь с их описанием.
Дополните электронную таблицу согласно рис. 22.
Рис. 22.
Вычислите количество менеджеров, продавших больше нормы. Для этого выполните следующие действия.
Перейдите в ячейку Е7. Вызовите Мастер функций, найдите в нем функцию COUNTIF (СЧЕТЕСЛИ).
В поле Диапазон укажите диапазон ячеек В6:В15 (в этом диапазоне ведется поиск значений, превышающих 150 000).
В поле Критерий введите ячейку Е6 (в этой ячейке содержится условие, выполнение которого проверяется в диапазоне В6:В15).
Проверьте правильность ввода аргументов.
Рис. 23.
Вычислите количество менеджеров, которые имеют объем продаж, равный норме или превышающих норму.
Рис. 24
Сравните полученные результаты с рисунком.
Сохраните рабочую книгу.
Математические функции. Функция SUMIF (СУММЕСЛИ)
В программу OpenOffice Calc включены 50 функций данной категории, что вполне достаточно для выполнения сложных математических расчетов.
Функция SUMIF (СУММЕСЛИ) суммирует ячейки, заданные критерием.
Синтаксис функции: SUMIF (СУММЕСЛИ)(Диапазон;Критерий;Диапазон_суммирования).
Диапазон - диапазон вычисляемых ячеек.
Критерий - критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".
Диапазон_суммирования - фактические ячейки для суммирования.
Ячейки в Диапазон_суммирования суммируются, только если соответствующие им ячейки в аргументе Диапазон удовлетворяют критерию. Если Диапазон_суммирования опущен, то суммируются ячейки в аргументе Диапазон.
Задание:
Откройте окно Мастер функций, выберите категорию математических функций.
Просмотрите список функций, ознакомьтесь с их описанием.
На Листе3 создайте таблицу табл. 4. Верхняя левая ячейка таблицы соответствует ячейке А20.
Таблица 4
| Месяц | Регион | Продажи | Итоги по регионам | |||
| Январь | Север | Север | ||||
| Февраль | Юг | Юг | ||||
| Январь | Запад | Запад | ||||
| Февраль | Восток | Восток | ||||
| Март | Север | ВСЕГО | ||||
| Январь | Юг | |||||
| Февраль | Запад | |||||
| Март | Восток | Итоги по месяцам | ||||
| Февраль | Север | Январь | ||||
| Март | Юг | Февраль | ||||
| Март | Запад | Март | ||||
| Январь | Восток | ВСЕГО | ||||
| ИТОГО |
Отформатируйте таблицу согласно рисунку.
Рис. 25.
Вычислите итоговые значения по регионам и по месяцам, а также суммарные значения продаж. Для этого выполните следующие действия.
Активизируйте ячейку F21 (итоги по северу), вызовите Мастер функций, найдите в нем функцию SUMIF (СУММЕСЛИ).
В поле Диапазон укажите диапазон ячеек В21:В32 (в этом диапазоне ведется поиск критерия «Север»).
В поле Критерий введите ячейку Е21 (с этим значением происходит сравнение содержимого диапазона В21:В32).
В поле Диапазон суммирования укажите диапазон С21:С32 (при нахождении в диапазоне В21:В32 значения, отвечающего критерию, происходит суммирование соответствующих значений из диапазона С21:С32).
Проверьте правильность ввода аргументов рис.26.
Рис. 26.
Рассчитайте итоги по остальным регионам и месяцам (используйте Автозаполнение и абсолютные ссылки).
Вычислите итоговые значения.
Сравните полученные результаты с рис. 27.
Рис. 27.
Сохраните рабочую книгу.
Задание для самостоятельной работы:
Вставьте новый лист, выполнив команду Вставкаð Лист, создайте таблицу табл. 5
Таблица 5
| Фамилия | Имя | Отчество | Отдел | Оклад | Премия | Ставки | |
| Андреева | Анна | Семеновна | Бухгалтерия | 0,2 | |||
| Бутаков | Андрей | Викторович | Сбыт | 0,15 | |||
| Горбатов | Иван | Андреевич | Склад | ||||
| Ерохин | Иван | Олегович | Склад | ||||
| Иванов | Сергей | Александрович | Бухгалтерия | ||||
| Крылова | Ольга | Сергеевна | Кадров | ||||
| Маметов | Иван | Алексеевич | Сбыт | ||||
| Петрова | Мария | Павловна | Кадров | ||||
| Чарушин | Семен | Максимович | Склад | ||||
| Яровцева | Елена | Викторовна | Бухгалтерия | ||||
| ИТОГО | |||||||
| Кол-во сотрудников | Суммы окладов | ||||||
| Бухгалтерия | Бухгалтерия | ||||||
| Сбыт | Сбыт | ||||||
| Склад | Склад | ||||||
| Кадров | Кадров | ||||||
| ИТОГО | |||||||
| >6000 | |||||||
| Иван |
Отформатируйте таблицу согласно рис. 28.
Рис. 28.
Определите общее количество сотрудников по каждому из отделов.
Подсчитайте количество сотрудников, имеющих оклад больше 6000 р.
Определите количество сотрудников с именем «Иван».
Подсчитайте общую сумму окладов сотрудников каждого отдела.
Рассчитайте величину премии: для сотрудников, имеющих оклад меньше 5000 р., премия равна 20% от оклада, для остальных сотрудников – 15% от оклада.
Сохраните рабочую книгу.
При задании формул наряду с основными арифметическими операциями, OpenOffice Calc предоставляет множество специальных функций, которые можно ввести в интерактивном режиме при помощи Автопилота функций . OpenOffice Calc поддерживает, в частности, многие статистические методы: от регрессионных расчетов до доверительных интервалов. Особенно интересна возможность изменять отдельные параметры в вычислениях, зависящих от многих факторов, и прослеживать, как это влияет на результат. Это так называемые расчеты "что было бы, если". Например, при расчете кредита путем простого изменения периода, процентной ставки или сумм выплаты можно сразу же увидеть, как изменяются остальные факторы.
Как и все другие средства работы с электронными таблицами, OpenOffice Calc позволяет использовать относительные и абсолютные ссылки. Перед каждым значением, которое должно использоваться как абсолютное, ставится знак доллара $. Для превращения текущей ссылки, в которой находится курсор в строке ввода, из относительной в абсолютную и наоборот, следует использовать комбинацию клавиш Shift+F4.
Практически все функции (за исключением математических и некоторых статистических) в программе OpenOffice Calc локализованы, т. е. используют русскоязычные имена. К таковым относятся и все функции из раздела Дата&Время (однако функция ПАСХАЛЬНОЕВОСКРЕСЕНЬЕ() определяет дату католической, а не православной Пасхи).
Для ввода функций в ячейку можно воспользоваться мастером функций. Выберите ячейку, в которую надо ввести функцию, и нажмите кнопку , которая находится на панели инструментов, или выберите команду Функция... из меню Вставка . Перед вами появится окно Автопилот функций , в котором надо выделить функцию и нажать кнопку Далее>> или OK , после чего появится окно ввода аргументов выбранной функции. Функцию можно вводить не только с помощью мастера функций, но и вручную, если вы помните, как она называется и сколько у нее параметров.
Для задания суммы чисел, находящихся в столбце или строке следует использовать кнопку . В ячейке появится формула вида =СУММ(...). Программа пытается догадаться, каков интервал суммирования. Если вас не устраивает предложенный диапазон, то выделите левой кнопкой мыши требуемую область ячеек так, чтобы вокруг нее появилась красная рамка. То же самое можно сделать и вручную, указав диапазон ячеек в строке формул. Отметим, что пустые ячейки при суммировании трактуются как содержащие нулевые значения.
Познакомимся поподробнее с функцией ДАТА, обеспечивающей ввод дат в электронную таблицу. OpenOffice Calc хранит такие данные в виде чисел, но отображает в ячейке в формате даты, выравнивая их (аналогично числам) по правому краю. Конечно, можно отформатировать ячейку, содержащую дату, и в числовом формате. Синтаксис функции - ДАТА(Год; Месяц; День). Год - целое число от 1600 до 3000, при вводе от 0 до 29 добавляется 2000, а при вводе числа от 30 до 99 - 1900. Месяц - число от 1 до 12, задающее номер месяца. День - число от 1 до 31, которое устанавливает день месяца. Когда значения месяца и дня больше допустимых, они пересчитываются на следующую позицию (год, месяц) с переполнением. Формула =ДАТА(00;12;31) дает 31.12.2000, а при вводе =ДАТА(00;13;31) получится дата 31.01.2001.
В программе OpenOffice Calc можно просто ввести даты в формате "месяц.число.год" (без указания кавычек, иначе ввод будет интерпретирован как текст), например, "5.17.2" для 17 мая 2002 года. В этом случае любой ввод со значениями, выходящими за границы допустимых, трактуется не как дата, а как текст. Функция ТДАТА() возвращает дату и время в соответствии с системным временем компьютера, которые обновляются при каждом пересчете документа.
Пример
Вычислим количество дней, часов, минут и секунд, прошедших с 0 часов некоторой даты. Откройте новый документ электронной таблицы и введите в ячейку B1 дату, например, "1.17.84". В ячейку B3 введите формулу =ТДАТА()-A1, которая по умолчанию отформатируется как дата. Поскольку нам нужно получить разницу между двумя датами в виде числа дней, то необходимо отформатировать ячейку B3 как целое число. Введите остальные формулы: в B4 =B3*24 для часов, в B5 =B4*60 для минут и в B6 =B5*60 для секунд.
Если все выполнено правильно, то время, прошедшее с указанного момента, вычислено и отображено в различных единицах. При этом значение, выраженное в секундах, относится к тому моменту времени, когда была зафиксирована формула в ячейке B6. Нажмите на клавишу F9 (для принудительного пересчета таблицы) и не отпускайте ее - вы увидите как изменяется вывод.
Для отображения формул, примененных при этих вычислениях, в столбец D помещена функция ФОРМУЛА(), аргументом которой является адрес соответствующей ячейки.
При создании сложных таблиц удобно использовать именованные ячейки. Если нужно составить, к примеру, формулу для вычисления НДС, то она будет более понятной в виде "= Сумма * Размер_налога", чем в форме "= A5 * B12", где ячейка A5 содержит данные о сумме, а ячейка B12 - о размере налога.
Для того чтобы задать имя области (ячейки), необходимо эту область сначала выделить и с помощью пункта Имена - Задать меню Вставка (или при помощи комбинации клавиш Ctrl+F3) вызвать диалоговое окно Присвоить имя . Имя должно начинаться с буквы, отличаться от стандартных имен ячеек и не может содержать пробелов. После ввода имени следует нажать кнопку Добавить . В этом же диалоге можно задать имена других областей, введя сначала имя, а затем выделив ячейки листа, которые должны получить это имя. В нем можно присвоить имена даже часто используемым формулам или элементам формул.
Мощным инструментом при работе с электронными таблицами является подбор параметра . С помощью этого инструмента можно узнать значение, которое при подстановке в формулу дает желаемый результат. Для того чтобы воспользоваться подбором параметра нужно иметь формулу с несколькими постоянными значениями и одним переменным. Лучше всего можно объяснить подбор параметра на примере.
Для вычисления годовых процентов создайте небольшую таблицу, в которой из значений капитала (Капитал), количества лет (Количество_лет) и процентной ставки (Ставка) получается результат - сумма процентов за год (Процент). Формула для вычисления результата имеет вид
Процент = Капитал * Количество_лет * Ставка / 100
Ячейкам лучше всего дать имена, чтобы формула имела осмысленный вид. В примере сначала вычисляется, какова будет сумма процентов при вложенном капитале 150 000 р. и процентной ставке 7,5% за год.
Теперь, считая, что процентная ставка (7,5%) и количество лет (1) не изменяются, определим, какого размера должен быть капитал, чтобы давать определенные годовые проценты, скажем, 15 000 р. Выделите ячейку Проценты и выберите в меню Сервис пункт Подбор параметра . Появится одноименное окно, в поле Ячейка формул которого уже указана нужная ячейка.
Поместите курсор в поле диалога изменяемая ячейка. В таблице выделите ячейку, значение которой изменяется, т. е. B2. В текстовом поле Конечное значение диалога введите желаемый результат формулы и нажмите кнопку OK. Если параметр был успешно подобран, то отобразится результат, который можно занести в таблицу.
Функция электронной таблицы - это предопределенное вычисление.
Calc имеет расширенные средства анализа, построения диаграмм и возможности принятия решений, ожидаемые от высококачественных электронных таблиц. Он включает более чем 300 функций, в том числе для финансовых, статистических и математических операций.
Функция, введенная в ячейку, помогает анализировать или управлять данными в электронной таблице. Все что необходимо сделать - добавить аргументы и вычисление выполнится автоматически. Именно в функциях сосредоточена вся мощь электронных таблиц.В среде Openoffice.org Calc существует множество функций. Стоит разделить их на 12 разделов:
1. Математические функции
2. Текстовые функции
3. Логические функции
4. Функции даты и времени
5. Функции базы данных
6. Функции преобразования чисел
7. Информационные функции
8. Функции электронных таблиц
9. Функции комплексных чисел
10. Статические функции
11. Финансовые функции
12. Функции массивов
Наиболее востребованными в электронных таблицах являются математические функции, которые подразделяются на арифметические, тригонометрические, гиперболические и логарифмические.
Ниже приведена таблица часто используемых математических функций Calc:
| Имя функции | Описание |
| ABS | Функция ABS возвращает абсолютное значение числа |
| COUNTIF | Функция подсчитывает количество ячеек в диапазоне, которые удовлетворяют заданному условию |
| SIN | Функция возвращает синус заданного угла (в радианах) |
| COS | Функция возвращает косинус заданного угла (в радианах) |
| EXP | Функция возвращает математическую константу "e" возведенную в степень числа |
| RAND | Функция возвращает случайное число между 0 и 1 |
| RANDBETWEEN | Функция возвращает целое случайное число в указанном диапазоне |
| SQRT | Функция возвращает положительное значение квадратного корня числа |
| SUM | Функция суммирует содержимое ячеек |
| SUMIF | Функция суммирует содержимое ячеек в диапазоне, которые удовлетворяют заданному условию |
| SUMSQ | Функция возвращает сумму квадратов аргументов |
Так же, широким распространением пользуются логические функции.
Логические функции оперируют логическими («булевыми») значениями , то есть TRUE или FALSE. Спецификация OpenDocument упоминает «логические операторы»; это просто другое название для логических функций. Это не поразрядные операции, например, AND(12;10), возвращает TRUE, а не 8.Ниже приведена таблица логических функций Calc:
| Имя функции | Описание |
| AND | Функция возвращает TRUE, если все аргументы определяются как TRUE, и FALSE в противном случае |
| FALSE | Функция возвращает логическое значение FALSE |
| IF | Функция возвращает одно из двух значений, в зависимости от результатов проверки условия |
| NOT | Функция меняет на противоположное логическое значение своего аргумента. Возвращается TRUE, если аргумент FALSE, и FALSE, если аргумент TRUE |
| OR | Функция возвращает TRUE, если любой из аргументов определяется как TRUE, и FALSE в противном случае |
| TRUE | Функция возвращает возвращает логическое значение TRUE |
Рассмотрим несколько примеров использования простых функций.
Для того, чтобы не писать функции в ячейки вручную, существует Мастер функций.
Рис. 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), после чего растянем формулу на оставшиеся ячейки );
В OpenOffice Calc реализована возможность вычисления нескольких простых показателей без применения функций и формул.
К таким показателям относятся:
- Сумма . Вычисляется только сумма чисел диапазона или ячеек. Аналогична функции SUM .
- Количество . Подсчитывается количество ячеек диапазона, содержащих только числовые данные (кроме текста).
- Количество2 . Подсчитывается количество ячеек диапазона, содержащих любые значения (текст, числа, даты и т.д.). Похож на показатель «Количество», но с более расширенным функционалом.
- Максимум . Показывает максимальное значение в диапазоне.
- Минимум . Показывает минимальное значение в диапазоне.
- Среднее значение . Вычисляется среднее арифметическое значение в диапазоне.
Вычисление данных показателей в OpenOffice Calc без применения функций и формул происходит следующим образом.
- В табличном редакторе Calc бесплатного офисного приложения OpenOffice, выделяется диапазон, в котором нужно подсчитать один из перечисленных ранее показателей.
- Курсор мыши наводится на панель в правой половине нижней границы рабочего листа, и нажимается правая кнопка мыши. Из списка со всеми показателями выбирается один показатель.
В результате проделанных действий, в нижней правой панели появляется значение выбранного показателя.
При изменении выделенного диапазона будет происходить автоматический пересчет результатов выбранного показателя.
К достоинства данного способа вычисления можно отнести оперативность и простоту. К недостаткам как невозможность отображения и закрепления результатов в ячейках, так и небольшое количество доступных показателей.
Следует отметить, что хотя данный способ получения значений не заменяет вычислений в Calc с помощью функций и формул, а лишь дополняет их, но он все же служит хорошим инструментом для быстрого анализа исходных данных и дополнительной проверки корректности ввода формул и функций.
Практикум
Рассчитаем в Calc, без применения функций и формул, сумму, минимальный, максимальный и средний ежедневный приход денежных средств, а также количество дней отчетного периода, используя условные исходные данные по приходу денежных средств в кассу розничного магазина за период с 1 по 15 ноября 2012 года.
- Выделим диапазон C2:C16.
- Наведем курсор на нижнюю правую панель под рабочим листом. В появившемся списке выберем «сумма». В результате появится суммой диапазона 12 843 191.
Минимум , максимум , среднее значение вычисляются аналогично.
Для знакомства с функциональными возможностями показателя «количество2» выделим диапазон B2:B16. Результат будет равен 15. В отличие от показателя «количество», показатель «количество2» более расширен по функционалу и подсчитывает количество любых значений в ячейках диапазона.
| Дата | День недели | Оборот, руб. |
| 01.11.2012 | Четверг | 689 585 |
| 02.11.2012 | Пятница | 897 123 |
| 03.11.2012 | Суббота | 854 789 |
| 04.11.2012 | Воскресенье | 956 871 |
| 05.11.2012 | Понедельник | 874 965 |
| 06.11.2012 | Вторник | 788 924 |
| 07.11.2012 | Среда | 789 634 |
| 08.11.2012 | Четверг | 875 614 |
| 09.11.2012 | Пятница | 879 321 |
| 10.11.2012 | Суббота | 879 219 |
| 11.11.2012 | Воскресенье | 951 478 |
| 12.11.2012 | Понедельник | 889 743 |
| 13.11.2012 | Вторник | 873 215 |
| 14.11.2012 | Среда | 858 147 |
| 15.11.2012 | Четверг | 784 563 |
