Функции пользователя в Excel 2013: разработка приложений нечеткой логики

Тип работы:
Реферат
Предмет:
ТЕХНИЧЕСКИЕ НАУКИ


Узнать стоимость

Детальная информация о работе

Выдержка из работы

УДК 519. 673
ФУНКЦИИ ПОЛЬЗОВАТЕЛЯ В EXCEL 2013: РАЗРАБОТКА ПРИЛОЖЕНИЙ НЕЧЕТКОЙ ЛОГИКИ
Семененко М. Г., Черняев С. И.
ГОУ ВПО «Калужский филиал МГТУ им. Н.Э. Баумана», Калуга, e-mail: msemenenko@mail. ru
В данной работе рассмотрены некоторые аспекты создания функций пользователя в последней версии электронных таблиц Excel 2013. Подробно описана разработка приложения для анализа финансового состояния предприятия методами нечеткой логики. Продемонстрированы достоинства и недостатки выбора с этой целью электронных таблиц по сравнению с пакетом прикладных программ Mathematica.
Ключевые слова: электронные таблицы, VBA, функции пользователя, нечеткая логика
USER FUNCTIONS IN EXCEL 2013: PROGRAMMING FUZZY LOGIC APPLICATIONS Semenenko M.G., Chernyaev S.I.
Bauman Moscow State Technical University, Kaluga branch, Kaluga, e-mail: msemenenko@mail. ru
Some aspects of creation of user functions in the last version of the electronic spreadsheets Excel 2013 are considered. Programming application for enterprise financial analysis by fuzzy logic methods is described explicitly.
The comparison of electronic spreadsheets and program package Mathematica is made.
Keywords: electronic spreadsheets, VBA, user functions, fuzzy logic
Электронные таблицы Ехсе1 имеют давнюю историю применения в различных сферах. Они отличаются уровнем функциональности, позволяющим рассматривать их как мощные системы поддержки принятия решений.
К наиболее существенным достоинствам электронных таблиц следует отнести широкие возможности математического, статистического и графического анализа данных, эффективное моделирование проблем вида «что будет, если», прямой доступ к внешним базам данных, развитый интерфейс с другими популярными пакетами, поддержка средств мультимедиа, наличие инструментария для работы в сети Интернет.
В настоящее время существуют сотни различных приложений, выполненных в виде надстроек к Excel и предназначенных для решения широкого круга задач — от математического и статистического анализа данных до реализации систем искусственного интеллекта. В качестве примера можно привести программные продукты для построения нейронных сетей фирмы NeuroOK.
Помимо широких функциональных возможностей Ехсе1 позволяет осуществлять разработку собственных приложений на популярном языке программирования высокого уровня Visual Basic for Application (VBA) [1].
В настоящей работе рассмотрены детали создания функций пользователя в электронных таблицах Excel 2013 на примере разработки приложения для оценки финан-
сового состояния предприятия на основе формализма нечеткой логики [2].
Разработка функций пользователя в Office 2013
В [3] приведен пример создания пользовательской функции VBA для реализации логической функции Если-То в версии Office 2003. Для последней версии электронных таблиц в Office 2013 данная методика претерпевает ряд изменений. В частности, в меню появилась вкладка Разработчик, в которой и находится кнопка создания приложений на языке VBA (рис. 1).
Если при запуске электронных таблиц вкладка Разработчик не отображается необходимо добавить ее в меню посредством выполнения команд: Файл/Параметры/Настроить ленту.
Затем методика разработки приложения практически ничем не отличается от предыдущей версии. В частности, чтобы создать функцию пользователя, необходимо использовать пункты меню Insert/Module и в открывшемся окне набрать текст. На рис. 2 показан код функции пользователя для создания трапециевидной функции принадлежности, часто появляющейся в теории нечетких множеств.
Чтобы воспользоваться разработанной функцией, необходимо при вставке функции выбрать тип Определенные пользователем. При сохранении файла нужно выбрать тип файла: Книга Excel с поддержкой макросов. При вызове описанной выше функции появляется окно, показанное на рис. 3.
1 ГЛАВНАЯ ВСТАВКА РАЗМЕТКА СТРАНИЦЫ ФОРМУЛЫ ДАННЫЕ РЕЦЕНЗИРОВАНИЕ ВИД РАЗРАБОТЧИК
|Запись макроса I й «Ц] Относительные ссылки foual Макросы |а!|с ! Безопасность макросов Сод & lt-? 1 Надстройки Надстройки СОМ Надстройки В У Ем™ .1 1=1 Просмотр кода Вставить Режим — конструктора Щ Отобразить окио Элементы управления ||В^ Сопоставить свойства [^Импорт Пакеты расширения & amp- Экспорт Источник С! Обновить данные В Область документа Изменение |
Рис. 1. Вкладка Разработчик в Excel 2013
Рис. 2. Код функции пользователя для создания трапециевидной функции принадлежности
Рис. 3. Окно вызова функции пользователя
Пример: разработка приложения для оценки финансовой устойчивости предприятия методами нечеткой логики
Следуя [2], введем нечеткую переменную g, которая принимает значения в интервале [0, 1] и имеет терм-множество значений {"Предельный риск банкротства», «Риск банкротства высокий», «Риск банкротства средний», «Риск банкротства низкий», «Риск банкротства незначителен"}.
Риск банкротства определяется следующими показателями (нечеткими переменными):
XI — коэффициент автономии (отношение собственного капитала к валюте баланса) —
Х2 — коэффициент обеспеченности оборотных активов собственными средствами (отношение чистого оборотного капитала к оборотным активам) —
Х3 — коэффициент промежуточной ликвидности (отношение суммы денежных средств и дебиторской задолженности к краткосрочным пассивам) —
Х4 — коэффициент абсолютной ликвидности (отношение суммы денежных средств к краткосрочным пассивам) —
Х5 — оборачиваемость всех активов в годовом исчислении (отношение выручки
от реализации к средней за период стоимости активов) —
Х6 — рентабельность всего капитала (отношение чистой прибыли к средней за период стоимости активов).
В наиболее простом случае все шесть показателей считаются равнозначными с уровнем значимости 1/6. Для всех переменных мы использовали трапециевидные функции принадлежности, параметры которых определены в [2]. Проблемы выбора функций принадлежности проанализированы в [5, 6].
Алгоритм вычислений следующий (рис. 4). После задания начальных значений переменных Х1, …, Х6 вычисляются уровни принадлежности 1 нечетким подмножествам из терм-множества значений переменной g (т.е. значения соответствующих функций принадлежности для заданных входных параметров). Степень риска банкротства можно вычислить по формуле [2]:
*=6 X, X V
° 3=1 1=1
где
% = 0,9 — 0,2(7'- - 1).
Показатель Текущ знач 1 Текущ знач II Очень низкий Низкий Средний Высокий О
XI 0,619 0,566 а= 0 ОД 0,25 0,45
Х2 0,294 0,262 Коэфф Ь= 0 0,2 0,3 0. 5
ХЗ 0,67 0,622 автоно с= 0,1 0,25 0,45 0. 6
Х4 0,112 0,048 мии, XI d= 0,2 о. з 0,5 0. 7
Х5 2,876 3,46 КОіфф а= -1 -0,005 0,09 0. 3
Х6 0,113 0,008 обесп ь= -1 0 0Д1 0,35
й } обор акт с= 0,005 0,09 0,3 0,45
А соболе ср. У2 d= 0 ап 0,35 0. 5
Показатель Значения в периоде 1 V Значения в периоде II Коэфф пром а= 0 0,5 0,7 0. 9
Іаїлі Iam2 ІатЗ Іат4 amS Іаті Іат2 ІатЗ Іат4 lamS Ь= 0 0,6 0,8 1
XI 0 0 0 0,81 0,19 0 0 0 1 0 с= 0,5 0,7 0,9 1. 3
Х2 ХЗ 0 0 1 0 0 0 0 і 0 0 пике, ХЗ d= 0,6 0,8 1 1. 5
0 і| 0 0 0 0 1 0 0 0 а= 0 аог 0,08 0. 3
Х4 0 0 1 0 0 0 1 0 0 0 Коэфф Ь= 0 аоз 0,1 0,35
Х5 0 0 0 0 1 0 0 0 0 1 абс с= 0,02 0,08 0,3 0. 5
Хб 0 0 0 1 0 0 0,5 0,5 0 0 пике, Х4 d= 0,03 0,1 035 0. 6
П П 15 П1Й пч
Рис. 4. Визуализация алгоритма вычислений:
1 — задание текущих значений в начале и конце периода- 2 — параметры функций принадлежности- 3 — вычисление уровней принадлежности I
Результаты вычислений показаны на g показывает, что за прошедший период рис. 5. В данном случае динамика значений риск банкротства предприятия повысился.
Показатель Значения в периоде 1 Значения в периоде II
laml Iam2 ІатЗ Іат4 ІатБ laml Iam2 ІатЗ Іат4 lamS
XI Х2 ХЗ Х4 Х5 Хб 0 0 0 0 0 0 0,81 0,19 0 0 0 1 0
0 1 0 0 0 0 1 0 0
1 0 0 0 0 1 0 0 0
0 1 0 0 0 1 0 0 0
0 0 0 0 0 1 0 0 0 0 1
0 0 1 0 0 0,5 0,5 0 0

j 1 gi Sum (lam) в 1 Sum (lam) в II Степень риска бан кротства
0,9 0 0 Период 1 Период II
2 0,7 1 2,5 g 0,393 667 0,483 333
3 0,5 2 1,5
4 0,3 1,81 1
5 0,1 1,19 1
Рис. 5. Оценка риска банкротства
В [4] рассмотрена реализация описанного алгоритма в системе Mathematica. Достоинство электронных таблиц Excel заключается в их доступности, легальности, удобстве визуализации вычислений. К недостаткам можно отнести не всегда корректное отображение графиков (например, функций принадлежности).
Выводы
Наличие встроенного языка программирования высокого уровня VBA делает электронные таблицы удобным легальным средством разработки приложений для научных и технических вычислений. В качестве примера в статье рассмотрено приложение для оценки степени банкротства предприятия на основе элементов нечеткой логики.
Список литературы
1. Белоусова С., Бессонова И. Основные принципы и концепции программирования на языке VBA в Excel. — М: Интернет-университет информационных технологий, Бином. Лаборатория знаний, 2010. — 200 с.
2. Недосекин А. О. Математические основы моделирования финансовой деятельности с использованием нечеткомножественных описаний: дис… докт. экон. наук. — СПб., 2003. — С. 61−68.
3. Образовательный сайт Exponenta. ru [Электронный ресурс]. — Режим доступа: http: //exponenta. ru/educat/systemat/ semenenko/excel/main. asp (дата обращения: 22. 12. 13).
4. Семененко М. Г Разработка приложений нечеткой логики в системе Mathematica// Современные информационные технологии и ИТ-образование: сб. избранных трудов VIII Международной научно-практической конференции. -М.: Интуит-РУ — С. 547−550.
5. Семененко М. Г, Князева И. В., Черняев С. И. Проблемы выбора функций принадлежности нечетких множеств в моделировании систем // Международный журнал прикладных и фундаментальных исследований. 2013. № 8. C. 165−166.
6. Семененко М. Г, Князева И. В., Черняев С. И. Проблемы выбора функций принадлежности нечетких множеств // Современные проблемы науки и образования. — 2013. -№ 5. — URL: www. science-education. ru/111−10 303 (дата обращения: 11. 01. 2014).

ПоказатьСвернуть
Заполнить форму текущей работой