Функция
Открывает Мастер функций , который интерактивно поможет вам создать формулы.
Перед тем, как вы откроете Мастер, выделите ячейку или диапазон ячеек на текущем листе, чтобы определить позицию, в которую будет вставлена формула.
Доступ к этой команде
Choose Insert — Function .
COMMAND CTRL +F2
On the Formula bar, click
Вы можете сказать полную спецификацию ODFF (OpenDocument Format Formula) с сайта OASIS .
Мастер функций содержит две вкладки: Функции , которая служит для создания формул, и Структура , которая служит для проверки структуры формул.
Вкладка «Функции»
Поиск
Поиск для части имени функции.
Список всех категорий, которым назначены различные функции. Выберите категорию для просмотра соответствующих функций в поле списка ниже. Выберите «Все», чтобы увидеть все функции в алфавитном порядке, независимо от категории. Список «Последние использованные» содержит список функций, которые вы чаще всего используете.
Вы можете просмотреть полный Список категорий и функций .
Функция
Служит для отображения функций, соответствующих выбранному типу. Чтобы выбрать функцию, дважды щёлкните её. Если щёлкнуть функцию один раз, отображается её краткое описание.
Массив
Указывает, что выбранная функция вставляется в выбранный диапазон ячеек как формула массива. Формулы массива оперируют несколькими ячейками. Каждая ячейка в массиве содержит формулу, но не как копию, а как общую формулу для всех ячеек матрицы.
Опция Массив идентична команде Command Ctrl +Shift+Enter , которая используется для ввода и подтверждения формул на листе. Формула вставится, как формула массива, обозначенная двумя фигурными скобками : < >.
Максимальный размер массива — 128 на 128 ячеек.
Поля ввода аргументов
When you double-click a function, the argument input field(s) appear on the right side of the dialog. To select a cell reference as an argument, click directly into the cell, or drag across the required range on the sheet while holding down the mouse button. You can also enter numerical and other values or references directly into the corresponding fields in the dialog. When using date entries , make sure you use the correct format. Click OK to insert the result into the spreadsheet.
Shrink / Expand
Click the Shrink icon to reduce the dialog to the size of the input field. It is then easier to mark the required reference in the sheet. The icons then automatically convert to the Expand icon. Click it to restore the dialog to its original size.
Диалоговое окно будет автоматически свернуто, если щёлкнуть мышью внутри листа. После отпускания кнопки мыши диалоговое окно будет восстановлено, и диапазон ссылок, определенный с помощью мыши, будет выделен в документе синей рамкой.
Что такое функции ooo calc
Функции – это встроенные элементы, которые используются в формулах.
Функции реализуют следующие возможности OOo Calc.
5.1. Упрощение формул
Например, среднее значение чисел, находящихся в десяти ячейках диапазона А1:А10 можно вычислить по следующей формуле:
Согласитесь, что это не самая удобная формула. Данную формулу можно заменить встроенной функцией рабочего листа OOo Calc:
AVERAGE – функция вычисления среднего значения чисел диапазона;
А1:А10 – диапазон ячеек, содержащий числа, среднее значение которых требуется найти.
5.2. Выполнение по формулам таких вычислений, которые невозможно сделать по-другому
Например, вам необходимо найти наибольшее значение диапазона ячеек. Без использования функции произвести такое вычисление невозможно. Ниже приведена функция, которая позволяет отыскать наибольшее значение в диапазоне ячеек:
MAX – функция определения наибольшего значения;
А1:А100 – диапазон ячеек, среди которых требуется отыскать наибольшее значение.
5.3. Ускорение выполнения некоторых задач редактирования
Иногда функции позволяют избежать ручного редактирования. Допустим, что в вашей рабочей таблице содержится 1 тысяча фамилий, причем все они введены прописными буквами, например, ИВАНОВ И.И..
Ваш начальник, просматривая список, замечает, что прописные буквы не подходят для указания фамилий в списке адресов, который будет связан с формализированным письмом. Таким образом, вы должны заменить ИВАНОВ И.И. на Иванов И.И..
Для переоформления списка вручную, вам понадобилось бы несколько часов. Но вы можете значительно сэкономить время благодаря функции для изменения регистра букв текста.
PROPER – функция, делающая прописной первую букву в каждом слове текста и преобразующая все другие буквы в строчные;
А1 – ячейка, содержащая строку текста.
Выполнение условного вычисления по формулам, позволяющего осуществить реализацию простых алгоритмов принятия решений
Предположим, что вам нужно подсчитать в рабочей таблице комиссионные по результатам продаж какого-либо товара. Если продавец продал продукции более, чем на $100 000, то ставка его комиссионных составляет 7,5%, если меньше, то 5%. Без использования функции вам пришлось бы создавать две разные формулы и правильно употреблять их для каждого значения объема продаж. Ниже приведена функция, которая позволяет рассчитать комиссионные с учетом суммы продаж.
=IF(А1<100000; A1*0,05; A1*0,075) , где
IF – функция, которая проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет;
A1*0,05 – значение, которое возвращается, если условие выполняется;
A1*0,075 – значение, которое возвращается, если условие не выполняется.
Таким образом, если значение ячейки А1 (сумма продаж) будет меньше 100000 (условие выполняется) программа умножит значение ячейки А1 на 0,05 (ставка комиссионных), если же сумма продаж будет больше 100000 (условие не выполняется), программа умножит значение ячейки А1 на 0,075 .
Все функции OOo Calc имеют аргументы (данные внутри круглых скобок). В зависимости от того, какие аргументы используются, различают следующие типы функций.
Функции без аргументов. Например, функция RAND , которая генерирует случайное число в интервале между 0 и 1 . Даже если функция не использует аргумент, необходимо набирать пустые круглые скобки: = RAND( ) .
Функции с одним аргументом: = PROPER(А1) .
Функции с фиксированным числом элементов:
Функции с неопределенным числом аргументов: =MAX(А1:А100) .
Функции с необязательными аргументами.
Примечание: Если в функции используется несколько аргументов, то каждый из них отделяется точкой с запятой.
5.1. Ввод функций вручную
Метод ввода функций вручную заключается в том, что нужно ввести с помощью клавиатуры имя функции и список ее аргументов. Однако часто этот метод оказывается самым эффективным.
При вводе функций OOo Calc всегда преобразует символы в их именах к верхнему регистру. Поэтому рекомендуется вводить функции строчными буквами. Если программа не преобразует введенный текст к верхнему регистру, значит, она не распознала запись как функцию, т.е. вы ввели ее неправильно.
Установите табличный курсор в ячейку общего количества изделий – В15 .
В строке формул наберите следующее выражение: =SUM(В2:В11)
Нажмите клавишу <Enter> .
Убедитесь, что в ячейке В15 появилось числовое значение.
5.2. Использование средства Мастер функций
Для ввода функции и ее аргументов в полуавтоматическом режиме предназначено средство Мастер функций, которое обеспечивает правильное написание функции, соблюдения необходимого количества аргументов и их правильную последовательность.
Перейдите в ячейку С13 – эта ячейка будет содержать формулу для определения средней цены изделий.
Вызовите диалоговое окно Мастер функций . Для этого выберите команду Вставка => Функция или щелкните на кнопке Вставка функции , которая находится около строки формул.
В поле Категория выберите Все .
В поле Выберите функцию найдите функцию AVERAGE (СРЗНАЧ). Обратите внимание, что при выборе функции в левой части диалогового окна появляется список ее аргументов вместе с кратким описанием.
Ознакомьтесь с описанием функции, затем нажмите кнопку ОК
В окне Аргументы функции в поле Число 1 введите диапазон ячеек, среди значений которых требуется найти среднее значение.
Щелкните на кнопке сворачивания диалогового окна, расположенной в правой части поля Число 1 . Это приведет к временному сворачиванию окна, в результате чего будет виден весь рабочий лист.
В рабочем листе выделите диапазон ячеек, среди значений которых требуется найти среднее: С2:С11 .
Снова щелкните на кнопке сворачивания диалогового окна. Отобразится окно, в ее нижней части будет представлен результат вычисления функции
Поскольку вы собираетесь вычислить среднее значение только одного диапазона значений, нет необходимости вводить какие-нибудь дополнительные аргументы. Щелкните на кнопке ОК .
Убедитесь, что в ячейке С13 появилось числовое значение.
На панели инструментов Стандартная расположена кнопка инструмента Автосумма , имеющая раскрывающийся список. Данный инструмент обеспечивает быстрый доступ к таким математическим функциям, как SUM , AVERAGE, COUNT, МАХ и MIN.
Задание для самостоятельной работы:
Используя соответствующие функции, рассчитайте значения суммарного дохода и суммы налога.
LXF132:OOo Calc
Многие виды деятельности (инженерные расчеты, статистика, финансы и т. д.) сопряжены с необходимостью периодически выполнять однотипные расчеты высокой сложности. Электронные таблицы – один из самых удобных и распространенных инструментов для решения данной задачи. Этим уроком мы открываем цикл статей, посвященных деталям создания сложных расчетных шаблонов и использования функций Basic для расширения функциональности Calc.
Наш сегодняшний урок затрагивает самые базовые вещи и ориентирован в первую очередь на начинающих. В частности, мы рассмотрим пользовательские функции и их применение в прикладных расчетах в электронных таблицах.
Функции пользователя
При проведении сложных специализированных вычислений встроенных возможностей Calc зачастую оказывается недостаточно, или конвейеры, построенные из стандартных функций, получаются чересчур сложными. В этом случае на помощь приходит язык для расширения OpenOffice.org – StarBasic, позволяющий создавать пользовательские функции, в том числе и для электронных таблиц.
Функции StarBasic универсальны – они могут возвращать не только числовые значения, но и многострочные текстовые отчеты, а также массивы смешанных данных. То же можно сказать и о переменных, которыми оперируют эти функции.
Почему функции предпочтительней макросов, обрабатывающих данные, например, по нажатию кнопки? Во-первых, привычностью: пользователь электронной таблицы уже знаком с данной концепцией. Во-вторых, универсальностью: простой, однажды написанный код на Basic, не содержащий фрагментов, взаимодействующих с ячейками рабочей книги и не использующий специфических API, почти не зависит от изменений в языке или библиотек и может быть использован в любом компиляторе или интерпретаторе Basic. При необходимости, его несложно адаптировать для Excel и потом перейти обратно.
Рис. 1. Пользовательская функция в таблице Calc.
Рис. 2. Пример использования функции с двумя переменными.
Пожалуй, достаточно вступительных слов – давайте рассмотрим пример простейшей функции:
Она вообще не имеет никаких входящих параметров и просто возвращает строку «Hello Word!». Чтобы воспользоваться ею в Calc, достаточно вписать в ячейку =HelloWord(), как и в случае со встроенными функциями (рис. 1).
Однако в реальности функции обычно принимают какие-то параметры (скажем, диапазон ячеек, подлежащих обработке).
Реализуем это – напишем функцию, принимающую одно числовое значение (сумму) и возвращающую его же, но с учетом НДС:
В этом случае синтаксис вызова функции также не отличается от традиционного: набрав =NDS(B3), вы получите сумму с учетом НДС для числа, содержащегося в ячейке B3. Нужно больше входящих переменных? Просто перечислите их все в скобках через запятую. Пример такой функции можно найти на диске в файле Листинг 1, а ее использование проиллюстрировано на рис. 2.
Можно заметить, что в листинге 1 присутствуют две функции – TempConvert() и Round(). Первая из них вызывается из Calc, а вторая необходима для её вычисления. Функция округления Round(x,a) есть в VBA для Excel, но отсутствует в OpenOffice.org Basic, поэтому нам пришлось написать свою. Функция TempConvert() не типизирована – по умолчанию ей присваивается тип Variant (универсальный тип, который может принимать любое значение). К слову, переменные можно вообще не объявлять: Basic сам будет распознавать их и возвращать значения по смыслу написанного вами кода. Но такой подход не вполне профессионален и в некоторых случаях может приводить к ошибкам или уменьшать быстродействие.
Изменим функцию TempConvert() так, чтобы она выдавала не значение, а текстовый отчет. Просто добавим в конце строку:
Неизменяемый текст берется в кавычки, символ & обозначает сцепку; Str() преобразует число в текст. Естественно, результат можно было сделать и вариантным, с разными пояснениями при разных значениях ключа.
Массивы
Рис. 3. Результат вычисления функция массива занимает заданную область на листе рабочей книги.
А что если вам потребуется передать в функцию произвольный диапазон значений, как это делается, например, в стандартной SUM()? В данном случае соответствующую входящую переменную следует объявить как Variant. При этом необходимый диапазон может быть задан как обычно (текстом через двоеточие) или выделен при вводе с помощью мыши.
Однако, если ваша функция ожидает видеть массив, а пользователь укажет не диапазон, а только одну ячейку, не ждите, что вы получите массив с одним элементом – на входе будет простая переменная. Перед вычислением такой функции следует проверить, что именно (массив или переменная) было ей передано:
Встроенная функция IsArray() возвращает True, если её параметр – массив. Более подробно о перехвате и обработке ошибок мы поговорим в следующий раз.
Наконец, некоторые функции могут даже возвращать массив. Они называются «функциями массива» (подробности ищите в справке по OpenOffice.org) и вводятся в ячейку с одновременным нажатием клавиш Ctrl+Shift+Enter, а результаты размещаются на заданной области рабочей книги. Пример простейшей функции массива есть на диске – это Листинг 2 (рис. 3).
Чтобы отредактировать функцию массива, изменить значения её аргументов или удалить её с листа, нужно выделить занимаемую её результатом область вручную, либо отметить одну из ячеек диапазона и нажать Ctrl+/, где / – клавиша деления на цифровой клавиатуре. В данном примере результатом функции является массив чисел, но ничто не мешает возвращать массив фрагментов текста, или смешанный – из строк и чисел.
Читаем разметку
Рис. 4. Наша функция в состоянии разобрать такой лист с диапазонами данных.
Можно подумать, что любая функция, кроме «Привет, мир!» и ей подобных, обязывает нас вводить данные в ячейки листа рабочей книги, а потом указывать ссылки на них или их диапазоны. Это правда, но не вся правда.
Можно, например, вводить нужные данные в заранее определенные ячейки на заранее определенные листы и считывать их в теле функции. Вполне допустимо получать данные из внешних текстовых файлов. Однако все эти варианты лишают функции универсальности, и применять данный подход целесообразно только в том случае, если вы пишете шаблон заранее определенного конкретного назначения. В некоторых случаях функцию удобно реализовать таким образом, чтобы она не брала данные из строго определенных мест, а разыскивала их по некоторым признакам самостоятельно.
Рассмотрим для примера функцию, которая принимает два массива (диапазона), содержащих данные о торгах по двум финансовым инструментам, и вычисляет некоторый финансовый индикатор – в данном случае не важно, как и какой. Такой функции необходимо получать данные из двух диапазонов с непредсказуемым размером, так как при импорте он будет постоянно изменяться. Мы не будем каждый раз редактировать ячейку с вызовом функции, указывая новый диапазон – вместо этого будем разбирать разметку электронной таблицы.
Самый простой способ реализации означенного механизма – использование на листе текстовых меток (ключевых слов), указывающих на начало таблицы. При этом функция должна ссылаться на заранее известный лист, содержащий диапазоны с данными. Фрагмент такого листа показан на рис. 4.
Здесь ячейки A2 и I2 содержат метки для первого и второго диапазона данных: STOCK1 и STOCK2, соответственно. По правилам разметки для описываемой функции, метка диапазона находится над его крайней правой верхней ячейкой и диапазоны имеют постоянную ширину – то есть количество столбцов; а количество строк может изменятся в любых пределах. Кроме того, очевидно, что метки должны быть уникальны и встречаться на листе только один раз. Давайте рассмотрим работу функции, способной идентифицировать и загрузить данные из описанных диапазонов.
В первую очередь, сделаем лист с диапазонами непредопределённым – это значительно упростит использование функции. Для этого добавим входной параметр nSheets, в котором будем передавать имя нужного нам листа. Кроме того, дополним функцию ещё одним параметром, ListenRange – для чего он нужен, мы обсудим чуть ниже:
В первую очередь необходимо на листе, имя которого передано в переменную nSheets, найти метки. Естественно, мы не будем перебирать поочерёдно все ячейки листа в цикле, а воспользуемся стандартным поисковым интерфейсом OpenOffice.org – com.sun.star.util.XSearchable:
Следующий этап – определение границ диапазона. Так как мы условились, что наши диапазоны не имеют пустых ячеек, тут всё достаточно просто – обнаружение первой же незаполненной ячейки ниже ячейки с меткой будет означать, что диапазон завершился.
Рис. 5. Так выглядят массивы данных после анализа листа.
Пожалуйста, имейте в виду, что определение границ диапазона – задача достаточно длительная, и затрачиваемое на неё время напрямую зависит от размера диапазона.
Теперь, когда диапазон найден, извлечь требуемые данные в виде массива из него не представляет никакой сложности:
На рис. 5, демонстрирующем состояние массивов stock1 и stock2 в режиме отладки, видно, что оба диапазона данных успешно считаны и загружены в соответствующие переменные.
Поиск второго диапазона осуществляется аналогичным образом. Причём мы можем использовать тот же дескриптор поиска, заменив в нём только свойство SearchString: oSearchDesc.SearchString = «STOCK2”. Полный исходный текст функции можно найти на прилагаемом к журналу диске в файле Листинг 3.
Теперь самое время объяснить, для чего был добавлен второй аргумент функции – ListenRange. Если вы уже просмотрели полный листинг примера, то, вероятно, обратили внимание, что нигде в теле функции эта переменная не используется. Так для чего же она нужна?
Чтобы дать ответ на этот вопрос, необходимо пояснить, как Calc пересчитывает формулы. Это происходит лишь в том случае, если изменились какие-либо входящие в нее аргументы, и это вполне разумно – зачем тратить время на пересчёт функций, значения которых не изменится? Так как после загрузки диапазонов аргумент функции, принимающей лишь имя листа, не изменится, функция не будет пересчитана. Для целей запуска пересчёта мы и вводим ещё один аргумент – «прослушиваемый» диапазон. Им может быть любой диапазон, изменение в котором должно приводить к пересчёту функции; но наиболее интересным для нас будет указание диапазона, пересекающегося с диапазонами данных – например, ЛистСДанными.A3:O6, где ЛистСДанными (естественно) – имя листа с данными.
Такая конструкция будет охватывать несколько строк одновременно из обоих загружаемых функцией диапазонов данных, и соответственно, её значение будет пересчитываться при всяком изменении в них.
Указание и поиск меток на листе не всегда приемлемы, так как это может приводить к коллизиям: например, если диапазоны данных могут содержать непредсказуемый текст, в том числе и совпадающий с меткой, или если диапазоны, по условию, могут иметь пустые ячейки, что затруднит поиск их границ. В этом случае правила разметки придётся усложнить. Можно ввести, например, проверку на стиль форматирования метки и просматривать найденные вхождения на предмет метки, имеющей заданный стиль, а диапазон разбирать не по одному, а по всем столбцам сразу. Но если уж мы используем стили, почему бы не отформатировать заданным стилем сам диапазон с данными?
Например, если в вышеописанном случае создать стили STOCK1 и STOCK2, для форматирования первого и второго диапазона с данными соответственно, то от использования текстовых меток можно отказаться совсем. Сама же функция при этом очень упростится, так что получать диапазон теперь можно будет непосредственно как результат поиска, что одновременно снимает и проблему нахождения в нём пустых ячеек. Для поиска диапазона, отформатированного заданным стилем, достаточно установить его имя в качестве свойства SearchString дескриптора поиска, а свойство SearchStyles выставить в значение True, что активирует поиск по стилям:
Ещё одним преимуществом разметки стилями является значительный прирост производительности, так как больше нет необходимости искать границы диапазона данных в цикле. Это особенно ощутимо, если размер диапазона велик и непостоянен в обоих измерениях.
Конечно, можно придумать ещё более изощрённые и сложные методы разметки, но это уже частные вопросы реализации, которых мы касаться не будем.
Место для функции
Рис. 6. Диалоговое окно макросов и библиотеки Basic.
Теперь, когда мы в принципе представляем, как выглядят пользовательские функции OOo Calc, у вас может возникнуть резонный вопрос: а где их вводить? Если обычные макросы могут размещаться в любых библиотеках Basic, то функции, особенно используемые в Calc, в этом вопросе несколько более прихотливы.
Обратите внимание на рис. 6. На нём представлено диалоговое окно Макрос из OpenOffice.org Basic. Если посмотреть повнимательнее, то можно заметить, что у библиотек, представленных в списке «Макрос из», используются собственные значки. Они показывают, загружена ли библиотека. На данном рисунке загружены библиотеки Standard, OceanStarter и OceanRuntimeEnvironment.
Загрузка библиотеки происходит при первом обращении к ней, которое может происходить при запуске из неё макроса или просмотре её модулей.
Загрузку также можно выполнить и принудительно из кода на Basic:
Библиотека Standard загружается при старте OpenOffice.org автоматически.
Если вы откроете файл .ods, в котором используются функции Basic из незагруженной на данный момент библиотеки, то результатом всех таких функций будет ошибка #ИМЯ, так как Calc просто не сможет их найти. Более того, в ветке OOo 3.x функции Calc не будут работать в библиотеках, отличных от Standard, даже если такие библиотеки загружены. В этом случае все функции будут возвращать ошибку #ЗНАЧЕН!!. Таким образом, наилучшим местом размещения пользовательских функций Calc в профиле пользователя будут модули библиотеки Standard.
Однако, если вам необходимо отправить файл Calc, использующий ваши функции, другому пользователю, и вы не уверены, есть ли у респондента необходимые модули Basic, или даже знаете, что их нет, тогда этот способ вам не подходит. В этом случае лучше воспользоваться внутренним контейнером библиотек самого файла. Все, что сказано выше, остается справедливым и в данном случае, за исключением того, что функции, размещенные вне библиотеки Standard, будут доступны и в OOo 3.x – после загрузки.
Передавая кому-либо файл, содержащий функции Calc, вы, вероятно, захотите защитить ваш код от случайного или даже преднамеренного изменения. Сделать это можно, просто открыв сохранённый файл .ods любым архиватором и, отыскав в /Basic/Standard/script-lb.xml строку
изменить в ней значение library:readonly с false на true. Это можно проделать и напрямую из Basic, установив для библиотеки флаг ReadOnly:
Кроме того, нужно предостеречь пользователей, любящих размещать в одном модуле библиотеки сразу много функций.
Помните: максимальный размер модуля библиотеки Basic составляет 65535 байт. Если вы превысите его, то ваш код будет оставаться работоспособным только до следующего перезапуска OpenOffice.org, так как при сохранении длина всех модулей будет урезана до 64К знаков.
Полагаю, вы уже поняли, что с помощью пользовательских функций Calc можно значительно расширить функционал электронных таблиц в самых различных направлениях. Этим-то мы и займемся через месяц.
Немного про отладку
Читая эту статью, вы наверняка набросали для себя несколько примеров функций на OpenOffice.org Basic и теперь хотите убедиться в их работоспособности. Что же, и к этой задаче существует несколько подходов.
Один из наиболее удобных способов отладки функций, возвращающих численное значение – это построение диаграмм. Пользуясь графиком, удобно отслеживать зависимости между переменными и результатами, сообразуясь с их физическим смыслом.
Если вы воспроизводите в Calc функцию какого-либо внешнего пакета, логичным будет принять предположение о том, что в данном пакете функция реализована без ошибок. В этом случае можно воспользоваться заранее сгенерированным массивом её результатов, сопоставленных массиву аргументов, и сравнить его с тем, что выдает ваша функция при тех же самых входных величинах.
Не стоит и забывать о стандартных правилах проверки возвращаемых результатов. Для каждой функции очень желательно проверить результаты, выдаваемые для крайних значений из области определения функции и некоторых стандартных критичных значений. Например, если область определения вашей функции от -1 до 1, то обязательно проверьте значения, возвращаемые ею при -1 и 1, а также при 0. Ноль, как правило, всегда является критичным значениям. Другие неплохие кандидаты – -1 и 1, если, конечно, они не являются крайними для области определения, как в примере выше. Также критичными являются такие значения аргументов, при которых значение функции обращается в -1, 0 или 1 или же достигает своих экстремумов. По возможности, желательно проверить работу функции и с этими входящими параметрами.