Материал из Linuxformat.

Пользовательские функции Добавьте электронным таблицам недостающий функционал

Содержание 1 OOo Calc: Ваши функции 1.1 Функции пользователя 1.2 Массивы 1.3 Читаем разметку 1.4 Место для функции 1.5 Немного про отладку

OOo Calc: Ваши функции

OOo Calc
OOo Calc: Ваши функции OOo Calc: Без исключений OOo Calc: Оптимизация OOo Calc:На конвейере

Любимая электронная таблица не желает округлять числа до нужного числа знаков? Леонид Алифанов и Александр Маджугин научат ее этому и многим другим трюкам!

Многие виды деятельности (инженерные расчеты, статистика, финансы и т. д.) сопряжены с необходимостью периодически выполнять однотипные расчеты высокой сложности. Электронные таблицы – один из самых удобных и распространенных инструментов для решения данной задачи. Этим уроком мы открываем цикл статей, посвященных деталям создания сложных расчетных шаблонов и использования функций Basic для расширения функциональности Calc.

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

Функции пользователя

При проведении сложных специализированных вычислений встроенных возможностей Calc зачастую оказывается недостаточно, или конвейеры, построенные из стандартных функций, получаются чересчур сложными. В этом случае на помощь приходит язык для расширения OpenOffice.org – StarBasic, позволяющий создавать пользовательские функции, в том числе и для электронных таблиц.

Функции StarBasic универсальны – они могут возвращать не только числовые значения, но и многострочные текстовые отчеты, а также массивы смешанных данных. То же можно сказать и о переменных, которыми оперируют эти функции.

Почему функции предпочтительней макросов, обрабатывающих данные, например, по нажатию кнопки? Во-первых, привычностью: пользователь электронной таблицы уже знаком с данной концепцией. Во-вторых, универсальностью: простой, однажды написанный код на Basic, не содержащий фрагментов, взаимодействующих с ячейками рабочей книги и не использующий специфических API, почти не зависит от изменений в языке или библиотек и может быть использован в любом компиляторе или интерпретаторе Basic. При необходимости, его несложно адаптировать для Excel и потом перейти обратно.

Пожалуй, достаточно вступительных слов – давайте рассмотрим пример простейшей функции:

Function HelloWord As String
	 HelloWord = “Hello Word!”
End Function

Она вообще не имеет никаких входящих параметров и просто возвращает строку «Hello Word!». Чтобы воспользоваться ею в Calc, достаточно вписать в ячейку =HelloWord(), как и в случае со встроенными функциями (рис. 1).

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

Реализуем это – напишем функцию, принимающую одно числовое значение (сумму) и возвращающую его же, но с учетом НДС:

Function NDS (ByVal cInSumm As Double) As Double
	 Const cStavka = 18
	 NDS = cInSumm/(100 + cStavka)
	 NDS = NDS * cStavka
End Function

В этом случае синтаксис вызова функции также не отличается от традиционного: набрав =NDS(B3), вы получите сумму с учетом НДС для числа, содержащегося в ячейке B3. Нужно больше входящих переменных? Просто перечислите их все в скобках через запятую. Пример такой функции можно найти на диске в файле Листинг 1, а ее использование проиллюстрировано на рис. 2.

Можно заметить, что в листинге 1 присутствуют две функции – TempConvert() и Round(). Первая из них вызывается из Calc, а вторая необходима для её вычисления. Функция округления Round(x,a) есть в VBA для Excel, но отсутствует в OpenOffice.org Basic, поэтому нам пришлось написать свою. Функция TempConvert() не типизирована – по умолчанию ей присваивается тип Variant (универсальный тип, который может принимать любое значение). К слову, переменные можно вообще не объявлять: Basic сам будет распознавать их и возвращать значения по смыслу написанного вами кода. Но такой подход не вполне профессионален и в некоторых случаях может приводить к ошибкам или уменьшать быстродействие.

Изменим функцию TempConvert() так, чтобы она выдавала не значение, а текстовый отчет. Просто добавим в конце строку:

TempConvert=”Температура воздуха “ & Str(TempConvert) & “º.”

Неизменяемый текст берется в кавычки, символ & обозначает сцепку; Str() преобразует число в текст. Естественно, результат можно было сделать и вариантным, с разными пояснениями при разных значениях ключа.

Массивы

А что если вам потребуется передать в функцию произвольный диапазон значений, как это делается, например, в стандартной SUM()? В данном случае соответствующую входящую переменную следует объявить как Variant. При этом необходимый диапазон может быть задан как обычно (текстом через двоеточие) или выделен при вводе с помощью мыши.

Однако, если ваша функция ожидает видеть массив, а пользователь укажет не диапазон, а только одну ячейку, не ждите, что вы получите массив с одним элементом – на входе будет простая переменная. Перед вычислением такой функции следует проверить, что именно (массив или переменная) было ей передано:

If IsArray (aData) Then
Else
	 'Код обработки ошибки
End If

Встроенная функция IsArray() возвращает True, если её параметр – массив. Более подробно о перехвате и обработке ошибок мы поговорим в следующий раз.

Наконец, некоторые функции могут даже возвращать массив. Они называются «функциями массива» (подробности ищите в справке по OpenOffice.org) и вводятся в ячейку с одновременным нажатием клавиш Ctrl+Shift+Enter, а результаты размещаются на заданной области рабочей книги. Пример простейшей функции массива есть на диске – это Листинг 2 (рис. 3).

Чтобы отредактировать функцию массива, изменить значения её аргументов или удалить её с листа, нужно выделить занимаемую её результатом область вручную, либо отметить одну из ячеек диапазона и нажать Ctrl+/, где / – клавиша деления на цифровой клавиатуре. В данном примере результатом функции является массив чисел, но ничто не мешает возвращать массив фрагментов текста, или смешанный – из строк и чисел.

Читаем разметку

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

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

Рассмотрим для примера функцию, которая принимает два массива (диапазона), содержащих данные о торгах по двум финансовым инструментам, и вычисляет некоторый финансовый индикатор – в данном случае не важно, как и какой. Такой функции необходимо получать данные из двух диапазонов с непредсказуемым размером, так как при импорте он будет постоянно изменяться. Мы не будем каждый раз редактировать ячейку с вызовом функции, указывая новый диапазон – вместо этого будем разбирать разметку электронной таблицы.

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

Здесь ячейки A2 и I2 содержат метки для первого и второго диапазона данных: STOCK1 и STOCK2, соответственно. По правилам разметки для описываемой функции, метка диапазона находится над его крайней правой верхней ячейкой и диапазоны имеют постоянную ширину – то есть количество столбцов; а количество строк может изменятся в любых пределах. Кроме того, очевидно, что метки должны быть уникальны и встречаться на листе только один раз. Давайте рассмотрим работу функции, способной идентифицировать и загрузить данные из описанных диапазонов.

В первую очередь, сделаем лист с диапазонами непредопределённым – это значительно упростит использование функции. Для этого добавим входной параметр nSheets, в котором будем передавать имя нужного нам листа. Кроме того, дополним функцию ещё одним параметром, ListenRange – для чего он нужен, мы обсудим чуть ниже:

Public Function ArbitraryAnalysis(ByVal nSheets As String, ByVal ListenRange As Variant) As Integer

В первую очередь необходимо на листе, имя которого передано в переменную nSheets, найти метки. Естественно, мы не будем перебирать поочерёдно все ячейки листа в цикле, а воспользуемся стандартным поисковым интерфейсом OpenOffice.org – com.sun.star.util.XSearchable:

oSheet = ThisComponent.Sheets.getByName(nSheets) ' лист на который ссылается функция
oSearchDesc = oSheet.createSearchDescriptor() ' создаём дескриптор поиска
oSearchDesc.SearchString = “STOCK1” ' настраиваем дескриптор
oFound = oSheet.findAll(oSearchDesc) ' получаем результат поиска
oCellAdr0 = oFound(0).CellAddress ' получаем адрес первого вхождения

Следующий этап – определение границ диапазона. Так как мы условились, что наши диапазоны не имеют пустых ячеек, тут всё достаточно просто – обнаружение первой же незаполненной ячейки ниже ячейки с меткой будет означать, что диапазон завершился.

SCA = oCellAdr0.Column
SRA = oCellAdr0.Row + 1
ECA = SCA + 7 - 1 ‘ количество столбцов фиксированное, нумерация с 0
ERA = SRA ‘ предполагаем что в диапазоне одна строка
Do While oSheet.getCellByPosition(SCA,ERA).getString <> “” 'пока ячейка не пуста...
	 ERA=ERA+1 ‘ увеличиваем индекс
Loop
ERA=ERA-1 ‘ отступаем назад в диапазон

Пожалуйста, имейте в виду, что определение границ диапазона – задача достаточно длительная, и затрачиваемое на неё время напрямую зависит от размера диапазона.

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

oTargetRange = oSheet.getCellRangeByPosition(SCA,SRA,ECA,ERA) ‘ получаем диапазон с данными
stock1 = oTargetRange.getDataArray ‘ извлекаем данные в массив

На рис. 5, демонстрирующем состояние массивов stock1 и stock2 в режиме отладки, видно, что оба диапазона данных успешно считаны и загружены в соответствующие переменные.

Поиск второго диапазона осуществляется аналогичным образом. Причём мы можем использовать тот же дескриптор поиска, заменив в нём только свойство SearchString: oSearchDesc.SearchString = «STOCK2”. Полный исходный текст функции можно найти на прилагаемом к журналу диске в файле Листинг 3.

Теперь самое время объяснить, для чего был добавлен второй аргумент функции – ListenRange. Если вы уже просмотрели полный листинг примера, то, вероятно, обратили внимание, что нигде в теле функции эта переменная не используется. Так для чего же она нужна?

Чтобы дать ответ на этот вопрос, необходимо пояснить, как Calc пересчитывает формулы. Это происходит лишь в том случае, если изменились какие-либо входящие в нее аргументы, и это вполне разумно – зачем тратить время на пересчёт функций, значения которых не изменится? Так как после загрузки диапазонов аргумент функции, принимающей лишь имя листа, не изменится, функция не будет пересчитана. Для целей запуска пересчёта мы и вводим ещё один аргумент – «прослушиваемый» диапазон. Им может быть любой диапазон, изменение в котором должно приводить к пересчёту функции; но наиболее интересным для нас будет указание диапазона, пересекающегося с диапазонами данных – например, ЛистСДанными.A3:O6, где ЛистСДанными (естественно) – имя листа с данными.

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

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

Например, если в вышеописанном случае создать стили STOCK1 и STOCK2, для форматирования первого и второго диапазона с данными соответственно, то от использования текстовых меток можно отказаться совсем. Сама же функция при этом очень упростится, так что получать диапазон теперь можно будет непосредственно как результат поиска, что одновременно снимает и проблему нахождения в нём пустых ячеек. Для поиска диапазона, отформатированного заданным стилем, достаточно установить его имя в качестве свойства SearchString дескриптора поиска, а свойство SearchStyles выставить в значение True, что активирует поиск по стилям:

oSearchDesc.SearchStyles = 1 ' устанавливаем флаг стилей
oSearchDesc.SearchString = “STOCK1” ' устанавливаем имя стиля
oFound = oSheet.findAll(oSearchDesc) ' получаем диапазон с данными
oTargetRange = oFound(0)
stock1 = oTargetRange.getDataArray ' извлекаем данные в массив

Ещё одним преимуществом разметки стилями является значительный прирост производительности, так как больше нет необходимости искать границы диапазона данных в цикле. Это особенно ощутимо, если размер диапазона велик и непостоянен в обоих измерениях.

Конечно, можно придумать ещё более изощрённые и сложные методы разметки, но это уже частные вопросы реализации, которых мы касаться не будем.

Место для функции

Теперь, когда мы в принципе представляем, как выглядят пользовательские функции OOo Calc, у вас может возникнуть резонный вопрос: а где их вводить? Если обычные макросы могут размещаться в любых библиотеках Basic, то функции, особенно используемые в Calc, в этом вопросе несколько более прихотливы.

Обратите внимание на рис. 6. На нём представлено диалоговое окно Макрос из OpenOffice.org Basic. Если посмотреть повнимательнее, то можно заметить, что у библиотек, представленных в списке «Макрос из», используются собственные значки. Они показывают, загружена ли библиотека. На данном рисунке загружены библиотеки Standard, OceanStarter и OceanRuntimeEnvironment.

Загрузка библиотеки происходит при первом обращении к ней, которое может происходить при запуске из неё макроса или просмотре её модулей.

Загрузку также можно выполнить и принудительно из кода на Basic:

BasicLibraries.LoadLibrary(“MyLibrary”)

Библиотека 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:library xmlns:library=”http://openoffice.org/2000/library”
library:name=”Standard” library:readonly=”false” library:passwordprotected=”false”>

изменить в ней значение library:readonly с false на true. Это можно проделать и напрямую из Basic, установив для библиотеки флаг ReadOnly:

ThisComponent.BasicLibraries.setLibraryReadOnly(“Standard”, True)

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

Помните: максимальный размер модуля библиотеки Basic составляет 65535 байт. Если вы превысите его, то ваш код будет оставаться работоспособным только до следующего перезапуска OpenOffice.org, так как при сохранении длина всех модулей будет урезана до 64К знаков.

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

Немного про отладку

Читая эту статью, вы наверняка набросали для себя несколько примеров функций на OpenOffice.org Basic и теперь хотите убедиться в их работоспособности. Что же, и к этой задаче существует несколько подходов.

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

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

Не стоит и забывать о стандартных правилах проверки возвращаемых результатов. Для каждой функции очень желательно проверить результаты, выдаваемые для крайних значений из области определения функции и некоторых стандартных критичных значений. Например, если область определения вашей функции от -1 до 1, то обязательно проверьте значения, возвращаемые ею при -1 и 1, а также при 0. Ноль, как правило, всегда является критичным значениям. Другие неплохие кандидаты – -1 и 1, если, конечно, они не являются крайними для области определения, как в примере выше. Также критичными являются такие значения аргументов, при которых значение функции обращается в -1, 0 или 1 или же достигает своих экстремумов. По возможности, желательно проверить работу функции и с этими входящими параметрами.