Excel: как построить степенной полином функцией ЛИНЕЙН

Мы уже строили аналогичный интерполирующий полином в Excel, и в Mathcad "вручную", и стандартными функциями Mathcad тоже можно это сделать.

Сейчас мы хотим, во-первых, построить в Excel интерполирующий полином тоже стандартной функцией, во-вторых, не вдаваясь в детали теории, понять смысл этой простой задачи - как построить кривую, проходящую через несколько известных точек на плоскости.

Итак, по известному набору из N значений функции f(xi)=yi, заданному парой векторов xi, yi=f(xi), i=1, 2, ..., N, нужно построить кривую, проходящую через все точки.

Через N различных между собой по оси x точек всегда можно построить кривую, зависящую от xN-1, её уравнение будет иметь общий вид

f(x)=c₀+c₁*x+c₂*x²+...+с_N-1*x^N-1 (1)

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

c0 +c1x1 +c2x12 +...+cN-1x1N-1 =y1

c0 +c1x2 +c2x22 +...+cN-1x2N-1 =y2

...

c0 +c1xN +c2xN2 +...+cN-1xNN-1 =yN

или, в матричном виде

Система линейных алгебраических уравнений, записанная в матричном виде

Решив эту систему уравнений, то есть, найдя обратную к матрице Вандермонда матрицу и умножив её на вектор y, найдём коэффициенты сi. Теперь, подставив их в уравнение (1), мы можем аналитически оценить значение функции в произвольной точке x.

Ниже показано "ручное" решение в Excel и решение с помощью стандартной функции ЛИНЕЙН.

Скриншот файла Excel с решением

Вот пояснения к формулам:

• C2 - формируем матрицу из степеней значений x; избегаем при этом возведения нуля в нулевую степень, заменяя любое число, возводимое в нулевую степень, единицей; ввести формулу в ячейку C2; затем растягиваем формулу на ячейки C2:C5, отпускаем левую кнопку мыши и, не снимая выделения, растягиваем на столбцы D:F (см. Пояснение 1 ниже);
• G2:G5 - вычисляем коэффициенты полинома ci "вручную", обратив матрицу и умножив её на вектор значений yi; выделить диапазон G2:G5; не снимая выделения, ввести формулу в ячейку G2; не снимая выделения, нажать комбинацию клавиш Crl+Shift+Enter (см. Пояснение 2 ниже);
• I2 - вычисляем полином третьей степени в точках, не обязательно совпадающих с исходными; по выделенным жирным шрифтом значениям полинома видно, что он прошёл через исходные точки; ввести формулу в ячейку I2, растянуть за уголок до I8;
• J2:J5 - вычисляем коэффициенты полинома c_i с помощью функции ЛИНЕЙН, пример в справке (пример 2), к сожалению, прямо ошибочен, плюс не показывает вычисление нескольких коэффициентов полинома; выделить диапазон J2:J5; не снимая выделения, ввести формулу в ячейку G2; не снимая выделения, нажать комбинацию клавиш Crl+Shift+Enter; коэффициенты возвращаются в "перевёрнутом" по отношению к нашему ручному расчёту виде;
• K2 - для единообразия расчёта переворачиваем массив коэффициентов, готовой функции для этого нет, показан образец, как перевернуть диапазон в Excel; ввести формулу в ячейку K2, растянуть за уголок до K5;
• L2 - вычисляем полином третьей степени в тех же точках H2:H8, в которых вычисляли его значения первым способом; ввести формулу в ячейку L2, растянуть за уголок до L8; видно, что кривая также прошла через исходные точки данных.

 Скачать файл Excel (2007 и выше, делался в Excel 2016) в архиве .zip (13 Кб)

Пояснение 1. Как растянуть формулу на матрицу значений

1. Введите требуемую формулу и нажмите Enter, на рисунке показан вид экрана перед нажатием:

Ввод "матричной" формулы со смешанными ссылками

2. Подведите курсор мыши к нижнему правому уголку ячейки C2, уголок превратился в чёрный крестик, зажмите левую кнопку мыши и растяните формулу вниз до ячейки C5.

Курсор для растягивания в Excel, "чёрный крестик"

Формула растянута вниз

3. Отпустите кнопку мыши, снова так же подведите курсор к уголку ячейки C5 (опять чёрный крестик) и при зажатой левой кнопке мыши растяните выделение вправо до столбца F.

Заполнение таблицы формулой в Excel

Пояснение 2. Как ввести формулу массива

1. Выделить диапазон ячеек, в которые будет помещён результат матричной или векторной операции (мышкой при зажатой левой кнопке за любое место, на котором курсор имеет вид по умолчанию или при зажатой Shift клавишами со стрелками):

Вид курсора по умолчанию в Excel

Мы сами отвечаем за правильность выделения ячеек диапазона результата, например, Excel не обязан знать, что в результате обращения матрицы размерностью 3x3 получится тоже матрица размерностью 3x3:

Выделение диапазона ячеек результата в Excel

2. Не снимая выделения, ввести формулу массива в первую ячейку выделенного диапазона, это можно сделать "вручную", просто нажав клавишу F2 и начав набирать формулу со знака "=", или с помощью Мастера Функций (см. п.3 документа по Excel здесь).

Ввод формулы массива в первую ячейку выделенного диапазона

3. При зажатых клавишах Ctrl и Shift, нажать клавишу Enter, то есть, ввести комбинацию клавиш Ctrl+Shift+Enter.

22.02.2020, 18:58 [5206 просмотров]