12 / 18
Парная регрессия в
Microsoft Excel
с использованием Р-сплайнов
ISSN 0236-3933. Вестник МГТУ им. Н.Э. Баумана. Сер. Приборостроение. 2017. № 5
125
4. Именованные ячейки модели:
base = R3C21, x0 = R9C6, n = R2C6, h = R4C6, K = R3C9, target = R6C10, tblBase =
= R9C2.
5. Начальные значения параметров
перед поиском оптимального реше-
ния:
нач
= (R2C13, 0, …, 0).
6. Настройки диалогового окна «Параметры поиска решения»:
target — целевая ячейка; R3C21:R3C47 — изменяемые ячейки; R3C21:R3C47
50,
R3C21:R3C47 ≥ −50 — ограничения; поиск решения задачи выполняется мето-
дом ОПГ.
Отметим, что табличные модели парной параметрической регрессии очень
похожи на описанную модель и отличаются от нее параметрами, отсутствием
вектор-столбцов
i
k
,
k
,
k
тек
и видом формул в вектор-столбцах
ŷ
и M(
x
).
Работа пользователя по оптимизации созданной P-сплайновой табличной
модели сводится к следующим действиям:
1)
задать требуемое число точек между узлами P-сплайна в ячейке R2C9;
2)
скопировать вектор-строку начальных значений
нач
из ячеек
R5C21:R5C122 в ячейки R3C21:R3C122 вектор-строки
;
3)
выполнить команду меню «Данные
Поиск решения», убедиться в
правильности настроек диалогового окна «Параметры поиска решения», при
необходимости исправить их и щелкнуть кнопку «Найти решение».
Следует отметить, что ввиду наличия случайной добавки значения ячеек
вектор-столбца
y
rnd
изменяются при каждом пересчете рабочего листа с таблич-
ной моделью. Поэтому значения (не формулы) ячеек из столбца
y
rnd
необходимо
один раз скопировать в ячейки вектор-столбца
y
и затем работать с этой фикси-
рованной выборкой данных. То же самое требуется выполнить при изменении
значения параметров
a
i
во входных ячейках R2C3:R4C3, параметра ε в ячейке
R3C6 и вида функции M(
x
) в ячейках R9C11:R109C11.
Изменение значений входных параметров
n
и
h
в ячейках R2C6, R4C6 тре-
бует следующей более серьезной модификации модели:
1)
заменить значения
n
или
h
в ячейках R2C6, R4C6 требуемыми;
2)
удалить лишние (при уменьшении
n
) или добавить с помощью маркера за-
полнения новые (при увеличении
n
) строки во все вектор-столбцы табличной мо-
дели;
3)
скопировать значения ячеек из измененного вектор-столбца
y
rnd
в ячей-
ки вектор-столбца
y
;
4)
скорректировать границы диапазонов данных, выводимых на графики, в
диалоговом окне «Выбор источника данных»;
5)
скорректировать диапазоны изменяемых ячеек и ограничений в диало-
говом окне «Параметры поиска решения».
Для автоматизации работы пользователя в коде
VBA
были разработаны об-
работчик события
Worksheet_Change
(), выполняющий перечисленные действия
(кроме действия 5) при любом изменении содержимого ячеек
x
0
,
n
или
h
, а так-