Электронные таблицы Ехсеl имеют давнюю историю применения в различных сферах. Они отличаются уровнем функциональности, позволяющим рассматривать их как мощные системы поддержки принятия решений.
К наиболее существенным достоинствам электронных таблиц следует отнести широкие возможности математического, статистического и графического анализа данных, эффективное моделирование проблем вида «что будет, если», прямой доступ к внешним базам данных, развитый интерфейс с другими популярными пакетами, поддержка средств мультимедиа, наличие инструментария для работы в сети Интернет.
В настоящее время существуют сотни различных приложений, выполненных в виде надстроек к Excel и предназначенных для решения широкого круга задач – от математического и статистического анализа данных до реализации систем искусственного интеллекта. В качестве примера можно привести программные продукты для построения нейронных сетей фирмы NeuroOK.
Помимо широких функциональных возможностей Ехсеl позволяет осуществлять разработку собственных приложений на популярном языке программирования высокого уровня Visual Basic for Application (VВA) [1].
В настоящей работе рассмотрены детали создания функций пользователя в электронных таблицах Excel 2013 на примере разработки приложения для оценки финансового состояния предприятия на основе формализма нечеткой логики [2].
Разработка функций пользователя в Office 2013
В [3] приведен пример создания пользовательской функции VBA для реализации логической функции Если-То в версии Office 2003. Для последней версии электронных таблиц в Office 2013 данная методика претерпевает ряд изменений. В частности, в меню появилась вкладка Разработчик, в которой и находится кнопка создания приложений на языке VBA (рис. 1).
Если при запуске электронных таблиц вкладка Разработчик не отображается необходимо добавить ее в меню посредством выполнения команд: Файл/Параметры/Настроить ленту.
Затем методика разработки приложения практически ничем не отличается от предыдущей версии. В частности, чтобы создать функцию пользователя, необходимо использовать пункты меню Insert/Module и в открывшемся окне набрать текст. На рис. 2 показан код функции пользователя для создания трапециевидной функции принадлежности, часто появляющейся в теории нечетких множеств.
Чтобы воспользоваться разработанной функцией, необходимо при вставке функции выбрать тип Определенные пользователем. При сохранении файла нужно выбрать тип файла: Книга Excel с поддержкой макросов. При вызове описанной выше функции появляется окно, показанное на рис. 3.
Рис. 1. Вкладка Разработчик в Excel 2013
Рис. 2. Код функции пользователя для создания трапециевидной функции принадлежности
Рис. 3. Окно вызова функции пользователя
Пример: разработка приложения для оценки финансовой устойчивости предприятия методами нечеткой логики
Следуя [2], введем нечеткую переменную g, которая принимает значения в интервале [0, 1] и имеет терм-множество значений {«Предельный риск банкротства», «Риск банкротства высокий», «Риск банкротства средний», «Риск банкротства низкий», «Риск банкротства незначителен»}.
Риск банкротства определяется следующими показателями (нечеткими переменными):
X1 – коэффициент автономии (отношение собственного капитала к валюте баланса);
X2 – коэффициент обеспеченности оборотных активов собственными средствами (отношение чистого оборотного капитала к оборотным активам);
X3 – коэффициент промежуточной ликвидности (отношение суммы денежных средств и дебиторской задолженности к краткосрочным пассивам);
X4 – коэффициент абсолютной ликвидности (отношение суммы денежных средств к краткосрочным пассивам);
X5 – оборачиваемость всех активов в годовом исчислении (отношение выручки от реализации к средней за период стоимости активов);
X6 – рентабельность всего капитала (отношение чистой прибыли к средней за период стоимости активов).
В наиболее простом случае все шесть показателей считаются равнозначными с уровнем значимости 1/6. Для всех переменных мы использовали трапециевидные функции принадлежности, параметры которых определены в [2]. Проблемы выбора функций принадлежности проанализированы в [5, 6].
Алгоритм вычислений следующий (рис. 4). После задания начальных значений переменных Х1, … , Х6 вычисляются уровни принадлежности l нечетким подмножествам из терм-множества значений переменной g (т.е. значения соответствующих функций принадлежности для заданных входных параметров). Степень риска банкротства можно вычислить по формуле [2]:
,
где
gj = 0,9 – 0,2(j – 1).
Рис. 4. Визуализация алгоритма вычислений: 1 – задание текущих значений в начале и конце периода; 2 – параметры функций принадлежности; 3 – вычисление уровней принадлежности l
Результаты вычислений показаны на рис. 5. В данном случае динамика значений g показывает, что за прошедший период риск банкротства предприятия повысился.
Рис. 5. Оценка риска банкротства
В [4] рассмотрена реализация описанного алгоритма в системе Mathematica. Достоинство электронных таблиц Excel заключается в их доступности, легальности, удобстве визуализации вычислений. К недостаткам можно отнести не всегда корректное отображение графиков (например, функций принадлежности).
Выводы
Наличие встроенного языка программирования высокого уровня VBA делает электронные таблицы удобным легальным средством разработки приложений для научных и технических вычислений. В качестве примера в статье рассмотрено приложение для оценки степени банкротства предприятия на основе элементов нечеткой логики.