CC BY
187
Семененко М.Г.
Калужский филиал МГТУ им. Н.Э. Баумана, г. Калуга, к.ф.-м.н., доцент кафедры высшей
математики, msemene nko [email protected]
Разработка функций пользователя в Excel 2013: приложения нечеткой логики
КЛЮЧЕВЫЕ СЛОВА
Нечеткая логика, электронные таблицы Excel, VBA. АННОТАЦИЯ
В статье рассматриваются особенности написания функций пользователя в Excel 2013. Рассмотрены примеры разработки приложений нечеткой логики для оценки финансовой устойчивости предприятия и прогноза случайных событий при разряде различных типов аккумуляторов.
Введение
Электронные таблицы Ехсе1 имеют давнюю историю применения в различных сферах. Они отличаются уровнем функциональности, позволяющим рассматривать их как мощные системы поддержки принятия решений. К наиболее существенным достоинствам электронных таблиц для научных исследований следует отнести широкие возможности математического, статистического и графического анализа данных, эффективное моделирование проблем типа «что будет, если», а также возможность разработки собственных приложений на языке программирования высокого уровня Visual Basic for Application (VBA).
В частности, в меню Ехсе1 2013 появилась вкладка «Разработчик», в которой и находится кнопка создания приложений на языке VBA. Если при запуске электронных таблиц вкладка «Разработчик» не отображается, необходимо добавить ее в меню посредством выполнения команд: Файл/Параметры/Настроить ленту. Затем методика разработки приложения практически ничем не отличается от предыдущей версии. Чтобы создать функцию пользователя необходимо использовать пункты меню Insert/Module и в открывшемся окне набрать текст. Например, для создания трапециевидной функции принадлежности, часто появляющейся в теории нечетких множеств, необходимо набрать следующий код: Function Trap(x, a, b, c, d) If (x >= a) And (x < b) Then Trap = (x - a)/(b - a) Else
If (x >= b) And (x < c) Then
Trap = 1
Else
If (x >= c) And (x < d) Then Trap = (c - x)/(d - c) Else
Trap = 0 End If
End If
End If
End Function
Чтобы воспользоваться разработанной функцией, необходимо при вставке функции выбрать тип Определенные пользователем. При сохранении файла нужно выбрать тип файла: Книга Excel с поддержкой макросов. При вызове описанной выше функции появляется окно, в которое необходимо ввести ее параметры.
График трапеции показан на рис. 1, где выделены точки, х-координаты которых соответствуют параметрам трапеции а, Ь, с, d. В данном случае а = 300, b = 500, с = d = 700.
Рис. 1. Пример трапециевидной функции принадлежности
Рассмотрим два примера разработки приложений для проведения вычислений на основе формализма нечеткой логики по следующему алгоритму:
1) определяется система показателей и нечетких переменных, описывающих исследуемый процесс, и формируется соответствующее каждому показателю терм-множество значений;
2) для каждого показателя определяется уровень значимости; если все показатели считаются равнозначными, уровень значимости каждого показателя равен 1/Я где N - число показателей;
3) задаются текущие значения всех показателей и определяются соответствующие им значения функций принадлежности ^ц, где i = 1, ... , Ы; Ц = 1, ... , т; N - число показателей; т - число уровней классификатора;
4) строится классификация результирующего показателя g и вычисляется его значение по формуле:
m N
g=Z gj Z r к.
j=1 i=1
Здесь г - уровень значимости i-го показателя, а g = 0.9 - 0.2(j - 1).
Поскольку наиболее удобным является табличный способ ввода и визуализации вычислений, наиболее подходящим инструментом для вычислений мы считаем электронные таблицы Excel с определенной пользователем трапециевидной функцией принадлежности. Методика разработки приложений нечеткой логики в соответствии с приведенным алгоритмом подробно изложена в [1].
Оценка финансовой устойчивости предприятия
В современных условиях часто возникает необходимость провести комплексный анализ деятельности организации, включая анализ ее финансовой устойчивости. В частности, оценка риска банкротства предприятия существенна для банков при оценке его кредитоспособности. Как показано в [2], все существующие стандартные методики оценки финансовой устойчивости предприятия имеют существенные недостатки и плохо применимы в условиях российской действительности. Поэтому актуальной задачей является разработка и анализ новых подходов на основе экономико-математического моделирования (например, [3]).
Мы провели оценку риска банкротства одного из калужских предприятий на основе данных его балансовой отчетности, пользуясь предложенной в [3] вышеописанной методикой и сформированными там же параметрами трапециевидных функций принадлежности. В качестве показателей экономической деятельности предприятия мы использовали: коэффициент автономии (отношение собственного капитала к валюте баланса, переменная Xi); коэффициент обеспеченности оборотных активов собственными средствами (отношение чистого оборотного капитала к оборотным активам, переменная X2); коэффициент промежуточной ликвидности (отношение суммы денежных средств и дебиторской задолженности к краткосрочным пассивам, переменная Хз); коэффициент абсолютной ликвидности (отношение суммы денежных средств к краткосрочным пассивам, переменная Х4); оборачиваемость всех активов в годовом исчислении (отношение выручки от реализации к средней за период стоимости активов, переменная Х5); рентабельность всего капитала (отношение чистой прибыли к средней за период стоимости активов, переменная Х6). Терм-множество значений уровня показателя: {«Очень низкий», «Низкий», «Средний», «Высокий», «Очень высокий»}, что соответствует значению m = 5.
Фрагмент вычислений показателя g в начале двух следующих друг за другом периодов (лет) приведен на рис. 2.
Из рис. 2 видно, что произошло некоторое улучшение финансовой
устойчивости, но оно крайне незначительно. Аналогичные вычисления за три года показали, что в течение последних трех лет риск банкротства предприятия заметно снизился, хотя и остается достаточно высоким.
Оценка риска развития теплового разгона в никель-кадмиевых аккумуляторах
В настоящее время во многих приборах бытового и специального назначения (мобильные телефоны, резервные источники питания и т.п.) устанавливаются различные типы аккумуляторов. Поэтому крайне важно знать факторы, которые могут нарушить стабильную работу аккумуляторов, а, следовательно, и всей системы. Одним из таких факторов может быть явление теплового разгона [4]. В результате теплового разгона происходит мощное выделение энергии, которое вызывает резкое повышение температуры внутри аккумулятора, что, в свою очередь, приводит к прогоранию сепаратора между пластинами и вскипанию электролита. Тепловой разгон неминуемо приводит к выходу системы из строя или сбоям в работе и, следовательно, является серьезным препятствием в работе очень большого числа современных приборов и систем.
Показатель Т«ку<Ц знлч 1 Текущ анлч II
0,304 0,234?
Х2 0,086 0.2М4
цгалч
И о,ооса 0,0113
И 1,0059 1,0737
0,0«М5 0,00072
Показатель 1НЗЧеНИЛ В Пё|1ИСД£ 1 Значения в периоде II
!ат1 !от? 1атЗ 1ат4 1пт1 1а. гп1 !ат1 1ат1 !ат4 1ат5
С 1 0 _0 0 о^гее 0,73* 0 0
|К2 а 0 0 0 а 0 1 0 0
1 0 0 а 0 1 0 0 0 0
Х4 1 0 0 0 0 1 0 □ О 0
Х.1; а 0 □ а I а 0 О 1
1 0 0 0 0 1 0 0 0
] а 5ит(1зт] а 1 ммм)14ш] а II Степень риска банкротства
1 0,5 2 2 Период V Период 11
2 2 1Г2Ё6 7. □,60ЕЕ67
э 0,5 1 1,734
а 0,3 0 0
5 ОД 1 1
Рис. 2. Фрагмент вычислений показателя финансовой устойчивости предприятия
Как отмечено в [4], тепловой разгон наблюдается в различных типах аккумуляторов: никель-кадмиевых, свинцово-кислотных, литиевых.
Причины теплового разгона в настоящее время неясны, в связи с чем практически отсутствуют попытки математического моделирования этого процесса, что не позволяет надежно предсказать его возникновение, или,
по крайней мере, оценить предрасположенность различных аккумуляторов к тепловому разгону. Однако известно, что это явление происходит, как правило, в аккумуляторах с большим сроком эксплуатации в условиях длительного перезаряда.
Из экспериментальных данных [4] можно сделать следующие выводы:
1. Явление теплового разгона является случайным и достаточно редким.
2. Во всех случаях наблюдения теплового разгона в [4] заряд аккумуляторов выполнялся при напряжениях 2,2 В, что значительно превышает среднее напряжение эксплуатации данных аккумуляторов на объекте 1,35-1,5 В. Таким образом, можно утверждать, что вероятность теплового разгона повышается с ростом напряжения заряда аккумуляторов.
3. Для начала теплового разгона важна общая масса аккумуляторов и общий ток заряда. Вероятность теплового разгона уменьшается с уменьшением емкости аккумулятора.
4. Тепловой разгон никогда не возникает в новых аккумуляторах или в аккумуляторах с небольшим сроком эксплуатации. В данных [4] во всех случаях теплового разгона аккумуляторы имели сроки эксплуатации больше пяти лет при гарантийном сроке службы в три года. Можно считать, что вероятность появления теплового разгона увеличивается с ростом срока эксплуатации аккумуляторов.
Для прогнозирования явления теплового разгона мы ввели следующие нечеткие переменные: «Высокая температура» (функция принадлежности (1)), «Высокое напряжение», «Большая емкость», «Большой срок эксплуатации». Для простоты мы пока не рассматривали другие терм-множества (например, включающие значения «Низкая температура», «Низкое напряжение» и т.п.).
Исходя из сделанных выше выводов, можно определить трапециевидные функции принадлежности переменных следующим образом:
• функция принадлежности переменной «Высокая температура» с параметрами [200; 400; 700; 700];
• функция принадлежности переменной «Высокое напряжение» с параметрами [2; 2,5; 4; 4];
• функция принадлежности переменной «Большая емкость» с параметрами [24; 48; 60; 60];
• функция принадлежности переменной «Срок эксплуатации» с параметрами [5; 8; 15; 15].
Параметр g вычисляется аналогично предыдущему пункту и оценивает степень риска развития теплового разгона. Пример вычислений показан на рис. 3.
1 рнуи^ 1Н.1Ч Г 1 rxyiij чипч Л
Г 200 ЗОО Тцмперарурн.Т d= 300
U 2 3 ь- wo
Q м ¿4
" 5 5 Напряжение, U J- ТОО J- I
Ь- Ü
t Г* КОСТЬ, Q ■j= 4 а= 1
Показатель чнлиен ая в периоде Р Значения в перирдч р| а- 24
1.1 ;м1 IJIII) I.NN4 !.щч4 l;inil l.in: A ljm-1 1.4114 Ь= 4£
а 0 {= И
и а 1 f) 60
и 0 1 pilll пигплулл! 1ИИГ п 1— S
14 0 О D- i
I-
Л-- is
ш ?um(jflm) 5um[lanif в N Степень иска теплового раэгена
1 0.9 О 1 Р1ермод[ 1РГ|1ИПД II
1 0 & 1 р.ммсо о, 22i
: 0,5 О 0
4 0.3 0 0
Рис. 3. Фрагмент вычислений риска теплового разгона в никель-кадмиевых аккумуляторах Результаты вычислений, как видно из рис.3, подтверждают вывод, полученный в экспериментах [4], что в некоторых партиях аккумуляторов тепловой разгон не наблюдается при очень большом числе циклов (несколько тысяч). В другой же партии тех же самых аккумуляторов с тем же сроком эксплуатации тепловой разгон происходит довольно легко, если применять жесткие режимы заряда (т.е. вести заряд при больших напряжениях заряда). В наших вычислениях также на вероятность возникновения теплового разгона существенное влияние при прочих равных условиях оказывало значительное повышение температуры или емкости.
Литература
1. Семененко М.Г., Черняев С.И. Функции пользователя в Excel 2013: разработка приложений нечеткой логики// Успехи современного естествознания, 2014, №3, с. 114-117.
2. Сорокина И. Оценка вероятности банкротства предприятия-заемщика. URL: http://bankir.ru/tehnologii/s/otsenka-veroyatnostibankrotstva-predpriyatiya-zaemshchika-10002719/ (дата обращения: 03.05.2014).
3. Недосекин А.О. Математические основы моделирования финансовой деятельности с использованием нечетко-множественных описаний: дис... докт. экон. наук. СПб., 2003. С. 61-68.
4. Галушкин Д.Н., Галушкин Н.Е., Язвинская Н.Н. Тепловой разгон в никель-кадмиевых аккумуляторах// Фундаментальные исследования, 2012, № 11-1, с. 116-119.
