Как построить полином в excel

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

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

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

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

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

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

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

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

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

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

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

C2 — формируем матрицу из степеней значений x ; избегаем при этом возведения нуля в нулевую степень, заменяя любое число, возводимое в нулевую степень, единицей; ввести формулу в ячейку C2 ; затем растягиваем формулу на ячейки C2:C5 , отпускаем левую кнопку мыши и, не снимая выделения, растягиваем на столбцы D:F (см. Пояснение 1 ниже);
G2:G5 — вычисляем коэффициенты полинома c_i «вручную», обратив матрицу и умножив её на вектор значений y_i ; выделить диапазон 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 ; видно, что кривая также прошла через исходные точки данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Полиномиальная аппроксимация excel

Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью полинома (до 6-й степени включительно).

В основной статье про МНК было рассмотрено приближение линейной функцией. В этой статье рассмотрим приближение полиномиальной функцией (с 3-й до 6-й степени) следующего вида: y=b +b ₁ x+b ₂ x 2 +b ₃ x 3 +…+b ₆ x 6

Примечание : В инструменте MS EXCEL Линия тренда , который доступен для диаграмм типа Точечная и График , можно построить линию тренда на основе полинома с максимальной степенью 6. В файле примера продемонстрировано полное совпадение линии тренда диаграммы и линии, вычисленной с помощью формул.

Покажем, как вычислить коэффициенты b линии тренда, заданной полиномом.

Как известно, квадратичная зависимость y=b +b ₁ x+b ₂ x 2 , подробно рассмотренная в статье МНК: Квадратичная зависимость в MS EXCEL , является частным случаем полиномиальной y=b +b ₁ x+b ₂ x 2 +b ₃ x 3 +… зависимости (в этом случае степень полинома равна 2). Соответственно, используя тот же подход (приравнивание к 0 частных производных), можно вычислить коэффициенты любого полинома.

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

Для нахождения m+1 коэффициента полинома m-й степени составим систему из m+1 уравнения и решим ее методом обратной матрицы . Для квадратного уравнения (m=2) нам потребовалось вычислить сумму значений х с 1-й до 4-й степени, а для полинома m-й степени необходимо вычислить значения х с 1-й до 2*m степени.

Примечание : Для удобства суммы степеней значений х можно вычислить в отдельном диапазоне ( файл примера столбцы К:М).

В файле примера создана универсальная форма для вычисления коэффициентов полиномов.

Выбрав с помощью элемента управления Счетчик нужную степень полинома, автоматически получим аппроксимацию наших данных выбранным полиномом (будет построен соответствующий график).

Примечание: При использовании полиномов высокой степени необходимо следить за тем, чтобы количество пар значений (х _i ; y _i ) превышало степень полинома хотя бы на несколько значений (для обеспечения точности аппроксимации). Кроме того, график функции полинома степени m имеет m-1 точку перегиба. Понятно, что точек данных должно быть гораздо больше, чем точек перегиба, чтобы такой изменчивый тренд стал очевидным (если утрировать, то бессмысленно строить по двум точкам параболу, логичнее построить прямую).

Как видно из расчетов, в MS EXCEL этот путь является достаточно трудоемким. Гораздо проще в MS EXCEL реализовать другой подход для вычисления коэффициентов полинома — с помощью замены переменных.

С помощью замены переменных x i =x _i полиномиальную зависимость y=b +b ₁ x+b ₂ x 2 +b ₃ x 3 +… можно свести к линейной. Теперь переменная y зависит не от одной переменной х в m разных степенях, а от m независимых переменных x _i . Поэтому для нахождения коэффициентов полинома мы можем использовать функцию ЛИНЕЙН() . Этот подход также продемонстрирован в файле примера .

Урок 4. Виды аппроксимации в Excel

Текст урока с работающими фрагментами расчетов в файле uroki-approksimacii.xls

Как и предыдущие, этот урок с аналогичным текстом лучше смотреть не листе Excel (см. Уроки аппроксимации.xls, Лист1)

Аппроксимация в Excel проще всего реализуется с помощью программы построения трендов. Для выяснения особенностей аппроксимации возьмем какой-либо конкретный пример. Например, энтальпию насыщенного пара по книге С.Л.Ривкина и А.А.Александрова «Теплофизические свойства воды и водяного пара», М., «Энергия», 1980г. В колонке P поместим значения давления в кгс/см2, в колонке i» — энтальпию пара на линии насыщения в ккал/кг и построим график с помощью опции или кнопки «Мастер диаграмм».

Щелкнем правой кнопкой по линии на рисунке, затем левой кнопкой по опции «Добавить линию тренда» и смотрим — какие услуги предлагаются нам этой опцией в части реализации аппроксимации в Excel.

Нам предлагается на выбор пять типов аппроксимации: линейная, степенная, логарифмическая, экспоненциальная и полиноминальная. Чем они хороши и чем могут нам помочь? — Нажимаем кнопку F1, затем щелкаем по опции «Мастер ответов» и в появившееся окошко вводим нужное нам слово «аппроксимация», после чего щелкаем по кнопке «Найти». Выбираем в появившемся списке раздел «Формулы для построения линий тренда».

Получаем следующую информацию в несколько измененной нами

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где b — угол наклона и a — координата пересечения оси абсцисс (свободный член).

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где c и b — константы.

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где a и b — константы.

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где b и k — константы.

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где a, b1, b2, b3. b6 — константы.

Снова щелкаем по линии рисунка, затем по опции «Добавить линию тренда», далее по опции «Параметры» и ставим флажки в окошках слева от записей: «показывать уравнение на диаграмме» и «поместить на диаг- рамму величину достоверности аппроксимации R^2, после чего щелкаем по кнопке OK. Пробуем все варианты аппроксимации по порядку.

Линейная аппроксимация дает нам R^2=0.9291 — это низкая достоверность и плохой результат.

Для перехода к степенной аппроксимации щелкаем правой кнопкой по линии тренда, затем левой кнопкой — по опции «Формат линии тренда», далее по опциям «Тип» и «Степенная». На этот раз получили R^2=0.999.

Запишем уравнение линии тренда в виде, пригодном для расчетов на листе Excel:

В результате имеем:

Максимальная погрешность аппроксимации получилась на уровне 0.23 ккал/кг. Для аппроксимации экспериментальных данных такой результат был бы чудесным, но для аппроксимации справочной таблицы это не слишком хороший результат. Поэтому попробуем проверить другие варианты аппроксимации в Excel посредством программы построения трендов.

Логарифмическая аппроксимация дает нам R^2=0.9907 — несколько хуже, чем по степенному варианту. Экспоненнта в том варианте, который предлагает программа построения трендов, вообще не подошла — R^2=0.927.

Полиноминальная аппроксимация со степенью 2 (это y=a+b1*x+b2*x^2) обеспечила R^2=0.9896. При степени 3 получили R^2=0.999, но с явным искажением аппроксимируемой кривой, в особенности при P>0.07 кгс/см2. Наконец, пятая степень нам дает R^2=1 — это, как утверждается, максимально тесная связь между исходными данными и их аппроксимацией.

Перепишем уравнение полинома в пригодном для расчетов на листе Excel виде:

и сравним результат аппроксимации с исходной таблицей:

Оказалось, что R^2=1 в данном случае лишь блестящая ложь. Реально, самый лучший результат полиноминальной аппроксимации дал самый простой полином вида y=a+b1*x+b2*x^2. Но его результат хуже, чем в варианте степенной аппроксимации y=634.16*x^0.012, где максимальная погрешность аппроксимации находилась на уровне 0.23 ккал/кг. Это все, что мы можем выжать из программы построения трендов. Посмотрим, что мы можем выжать из функции Линейн. Для нее попробуем вариант степенной аппроксимации.

Примечание. Обнаруженный дефект связан с работой программы построения трендов, но не с методом МНК.

Аппроксимация в Excel

(Обратите внимание на дополнительный раздел от 04.06.2017 в конце статьи.)

Учет и контроль! Те, кому за 40 должны хорошо помнить этот лозунг из эпохи построения социализма и коммунизма в нашей стране.

Но без хорошо налаженного учета невозможно эффективное функционирование ни страны, ни области, ни предприятия, ни домашнего хозяйства при любой общественно-экономической формации общества! Для составления прогнозов и планов деятельности и развития необходимы исходные данные. Где их брать? Только один достоверный источник – это ваши статистические учетные данные предыдущих периодов времени.

Учитывать результаты своей деятельности, собирать и записывать информацию, обрабатывать и анализировать данные, применять результаты анализа для принятия правильных решений в будущем должен, в моем понимании, каждый здравомыслящий человек. Это есть ничто иное, как накопление и рациональное использование своего жизненного опыта. Если не вести учет важных данных, то вы через определенный период времени их забудете и, начав заниматься этими вопросами вновь, вы опять наделаете те же ошибки, что делали, когда впервые этим занимались.

«Мы, помню, 5 лет назад изготавливали до 1000 штук таких изделий в месяц, а сейчас и 700 еле-еле собираем!». Открываем статистику и видим, что 5 лет назад и 500 штук не изготавливали…

«Во сколько обходится километр пробега твоего автомобиля с учетом всех затрат?» Открываем статистику – 6 руб./км. Поездка на работу – 107 рублей. Дешевле, чем на такси (180 рублей) более чем в полтора раза. А бывали времена, когда на такси было дешевле…

«Сколько времени требуется для изготовления металлоконструкций уголковой башни связи высотой 50 м?» Открываем статистику – и через 5 минут готов ответ…

«Сколько будет стоить ремонт комнаты в квартире?» Поднимаем старые записи, делаем поправку на инфляцию за прошедшие годы, учитываем, что в прошлый раз купили материалы на 10% дешевле рыночной цены и – ориентировочную стоимость мы уже знаем…

Ведя учет своей профессиональной деятельности, вы всегда будете готовы ответить на вопрос начальника: «Когда. ». Ведя учет домашнего хозяйства, легче спланировать расходы на крупные покупки, отдых и прочие расходы в будущем, приняв соответствующие меры по дополнительному заработку или по сокращению необязательных расходов сегодня.

В этой статье я на простом примере покажу, как можно обрабатывать собранные статистические данные в Excel для возможности дальнейшего использования при прогнозировании будущих периодов.

Аппроксимация в Excel статистических данных аналитической функцией.

Производственный участок изготавливает строительные металлоконструкции из листового и профильного металлопроката. Участок работает стабильно, заказы однотипные, численность рабочих колеблется незначительно. Есть данные о выпуске продукции за предыдущие 12 месяцев и о количестве переработанного в эти периоды времени металлопроката по группам: листы, двутавры, швеллеры, уголки, трубы круглые, профили прямоугольного сечения, круглый прокат. После предварительного анализа исходных данных возникло предположение, что суммарный месячный выпуск металлоконструкций существенно зависит от количества уголков в заказах. Проверим это предположение.

Прежде всего, несколько слов об аппроксимации. Мы будем искать закон – аналитическую функцию, то есть функцию, заданную уравнением, которое лучше других описывает зависимость общего выпуска металлоконструкций от количества уголкового проката в выполненных заказах. Это и есть аппроксимация, а найденное уравнение называется аппроксимирующей функцией для исходной функции, заданной в виде таблицы.

1. Включаем Excel и помещаем на лист таблицу с данными статистики.

2. Далее строим и форматируем точечную диаграмму, в которой по оси X задаем значения аргумента – количество переработанных уголков в тоннах. По оси Y откладываем значения исходной функции – общий выпуск металлоконструкций в месяц, заданные таблицей.

О том, как построить подобную диаграмму, подробно рассказано в статье «Как строить графики в Excel?».

3. «Наводим» мышь на любую из точек на графике и щелчком правой кнопки вызываем контекстное меню (как говорит один мой хороший товарищ — работая в незнакомой программе, когда не знаешь, что делать, чаще щелкай правой кнопкой мыши…). В выпавшем меню выбираем «Добавить линию тренда…».

4. В появившемся окне «Линия тренда» на вкладке «Тип» выбираем «Линейная».

5. Далее на вкладке «Параметры» ставим 2 галочки и нажимаем «ОК».

6. На графике появилась прямая линия, аппроксимирующая нашу табличную зависимость.

Мы видим кроме самой линии уравнение этой линии и, главное, мы видим значение параметра R 2 – величины достоверности аппроксимации! Чем ближе его значение к 1, тем наиболее точно выбранная функция аппроксимирует табличные данные!

7. Строим линии тренда, используя степенную, логарифмическую, экспоненциальную и полиномиальную аппроксимации по аналогии с тем, как мы строили линейную линию тренда.

Лучше всех из выбранных функций аппроксимирует наши данные полином второй степени, у него максимальный коэффициент достоверности R 2 .

Однако хочу вас предостеречь! Если вы возьмете полиномы более высоких степеней, то, возможно, получите еще лучшие результаты, но кривые будут иметь замысловатый вид…. Здесь важно понимать, что мы ищем функцию, которая имеет физический смысл. Что это означает? Это означает, что нам нужна аппроксимирующая функция, которая будет выдавать адекватные результаты не только внутри рассматриваемого диапазона значений X, но и за его пределами, то есть ответит на вопрос: «Какой будет выпуск металлоконструкций при количестве переработанных за месяц уголков меньше 45 и больше 168 тонн!» Поэтому я не рекомендую увлекаться полиномами высоких степеней, да и параболу (полином второй степени) выбирать осторожно!

Итак, нам необходимо выбрать функцию, которая не только хорошо интерполирует табличные данные в пределах диапазона значений X=45…168, но и допускает адекватную экстраполяцию за пределами этого диапазона. Я выбираю в данном случае логарифмическую функцию, хотя можно выбрать и линейную, как наиболее простую. В рассматриваемом примере при выборе линейной аппроксимации в excel ошибки будут больше, чем при выборе логарифмической, но не на много.

8. Удаляем все линии тренда с поля диаграммы, кроме логарифмической функции. Для этого щелкаем правой кнопкой мыши по ненужным линиям и в выпавшем контекстном меню выбираем «Очистить».

9. В завершении добавим к точкам табличных данных планки погрешностей. Для этого правой кнопкой мыши щелкаем на любой из точек на графике и в контекстном меню выбираем «Формат рядов данных…» и настраиваем данные на вкладке «Y-погрешности» так, как на рисунке ниже.

10. Затем щелкаем по любой из линий диапазонов погрешностей правой кнопкой мыши, выбираем в контекстном меню «Формат полос погрешностей…» и в окне «Формат планок погрешностей» на вкладке «Вид» настраиваем цвет и толщину линий.

Аналогичным образом форматируются любые другие объекты диаграммы в Excel!

Окончательный результат диаграммы представлен на следующем снимке экрана.

Итоги.

Результатом всех предыдущих действий стала полученная формула аппроксимирующей функции y=-172,01*ln (x)+1188,2. Зная ее, и количество уголков в месячном наборе работ, можно с высокой степенью вероятности (±4% — смотри планки погрешностей) спрогнозировать общий выпуск металлоконструкций за месяц! Например, если в плане на месяц 140 тонн уголков, то общий выпуск, скорее всего, при прочих равных составит 338±14 тонн.

Для повышения достоверности аппроксимации статистических данных должно быть много. Двенадцать пар значений – это маловато.

Из практики скажу, что хорошим результатом следует считать нахождение аппроксимирующей функции с коэффициентом достоверности R 2 >0,87. Отличный результат – при R 2 >0,94.

На практике бывает трудно выделить один самый главный определяющий фактор (в нашем примере – масса переработанных за месяц уголков), но если постараться, то в каждой конкретной задаче его всегда можно найти! Конечно, общий выпуск продукции за месяц реально зависит от сотни факторов, для учета которых необходимы существенные трудозатраты нормировщиков и других специалистов. Только результат все равно будет приблизительным! Так стоит ли нести затраты, если есть гораздо более дешевое математическое моделирование!

В этой статье я лишь прикоснулся к верхушке айсберга под названием сбор, обработка и практическое использование статистических данных. О том удалось, или нет, мне расшевелить ваш интерес к этой теме, надеюсь узнать из комментариев и рейтинга статьи в поисковиках.

Затронутый вопрос аппроксимации функции одной переменной имеет широкое практическое применение в разных сферах жизни. Но гораздо большее применение имеет решение задачи аппроксимации функции нескольких независимых переменных…. Об этом и не только читайте в следующих статьях на блоге.

Подписывайтесь на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы.

Не забывайте подтверждать подписку кликом по ссылке в письме, которое придет к вам на указанную почту (может прийти в папку «Спам»).

С интересом прочту Ваши комментарии, уважаемые читатели! Пишите!

P.S. (04.06.2017)

Высокоточная красивая замена табличных данных простым уравнением.

Вас не устраивают полученные точность аппроксимации (R 2 2 =0,9963.

Полиномиальная аппроксимация excel

Есть 3 способа расчета значений полинома в Excel:

1-й способ с помощью графика;
2-й способ с помощью функции Excel =ЛИНЕЙН();
3-й способ с помощью Forecast4AC PRO;

Подробнее о полиноме и способе его расчета в Excel далее в нашей статье.

Полиномиальный тренд применяется для описания значений временных рядов, попеременно возрастающих и убывающих. Полином отлично подходит для анализа большого набора данных нестабильной величины (например, продажи сезонных товаров).

Что такое полином? Полином — это степенная функция y=ax 2 +bx+c (полином второй степени) и y=ax 3 +bx 2 +cx+d (полином третей степени) и т.д. Степень полинома определяет количество экстремумов (пиков), т.е. максимальных и минимальных значений на анализируемом промежутке времени.

У полинома второй степени y=ax 2 +bx+c один экстремум (на графике ниже 1 максимум).

У Полинома третьей степени y=ax 3 +bx 2 +cx+d может быть один или два экстремума.

Один экстремум

Два экстремума

У Полинома четвертой степени не более трех экстремумов и т.д.

Как рассчитать значения полинома в Excel?

Есть 3 способа расчета значений полинома в Excel:

1-й способ с помощью графика;
2-й способ с помощью функции Excel =ЛИНЕЙН;
3-й способ с помощью Forecast4AC PRO;

1-й способ расчета полинома — с помощью графика

Выделяем ряд со значениями и строим график временного ряда.

На график добавляем полином 6-й степени.

Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»

После этого уравнение выводится на график y = 3,7066x 6 — 234,94x 5 + 4973,6x 4 — 35930x 3 — 7576,8x 2 + 645515x + 5E+06 . Для того чтобы последний коэффициент сделать читаемым, мы зажимаем левую кнопку мыши и выделяем уравнение полинома

Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»

В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».

Получаем уравнение полинома в читаемом формате:

y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35

Из этого уравнения берем коэффициенты a, b, c, d, g, m, v, и вводим в соответствующие ячейки Excel

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение вместо X.

Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

Теперь протягиваем формулу до конца временного ряда и получаем рассчитанные значения полиномиального тренда для каждого периода.

2-й способ расчета полинома в Excel — функция ЛИНЕЙН()

Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel =ЛИНЕЙН()

Для расчета коэффициентов в формулу =ЛИНЕЙН(известные значения y, известные значения x, константа, статистика) вводим:

«известные значения y» (объёмы продаж за периоды),
«известные значения x» (порядковый номер временного ряда),
в константу ставим «1»,
в статистику «0»

Получаем следующего вида формулу:

Теперь, чтобы формула Линейн() рассчитала коэффициенты полинома, нам в неё надо дописать степень полинома, коэффициенты которого мы хотим рассчитать.

Для этого в часть формулы с «известными значениями x» вписываем степень полинома:

^ — для расчета коэффициентов полинома 6-й степени
^ — для расчета коэффициентов полинома 5-й степени
^ — для расчета коэффициентов полинома 2-й степени

Получаем формулу следующего вида:

Вводим формулу в ячейку, получаем 3,71 —- значение (a) для полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v

Для того, чтобы Excel рассчитал все 7 коэффициентов полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v, необходимо:

1. Установить курсор в ячейку с формулой и выделить 7 соседних ячеек справа, как на рисунке:

2. Нажать на клавишу F2

3. Затем одновременно — клавиши CTRL + SHIFT + ВВОД (т.е. ввести формулу массива, как это сделать читайте подробно в статье «Как ввести формулу массива»)

Получаем 7 коэффициентов полиномиального тренда 6-й степени.

Рассчитаем значения полиномиального тренда с помощью полученных коэффициентов. Подставляем в уравнение y=3,7* x ^ 6 -234,9* x ^ 5 +4973,5* x ^ 4 -35929,9 * x^3 -7576,7 * x^2 +645514,7* x +4693169,3 номера периодов X, для которых хотим рассчитать значения полинома.

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение полинома вместо X.

Рассчитаем значения полиномиального тренда для каждого периода. Для этого вводим формулу полинома в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

2-й способ точнее, чем первый, т.к. коэффициенты тренда мы получаем без округления, а также этот расчет быстрее.

3-й способ расчета значений полиномиальных трендов — Forecast4AC PRO

Устанавливаем курсор в начало временного ряда

Заходим в настройки Forecast4AC PRO, выбираем «Прогноз с ростом и сезонностью», «Полином 6-й степени», нажимаем кнопку «Рассчитать».

Заходим в лист с пошаговым расчетом «ForPol6», находим строку «Сложившийся тренд»:

Копируем значения в наш лист.

Получаем значения полинома 6-й степени, рассчитанные 3 способами с помощью:

Коэффициентов полиномиального тренда выведенных на график;
Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast Lite — автоматический расчет прогноза в Excel .
4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

АППРОКСИМАЦИЯ ЗАВИСИМОСТЕЙ В EXCEL

Решить задачу аппроксимации экспериментальных данных – значит построить уравнение регрессии. Задача аппроксимации возникает в случае необходимости аналитически, то есть в виде математической зависимости, описать реальные явления, наблюдения за которыми заданы в виде таблицы, содержащей значения показателя в разные моменты времени или при разных значениях независимого аргумента. Например,

— известны показатели прибыли (их можно обозначить Y) в зависимости от размера капиталовложений (X);

— известны объемы реализации фирмы (Y) за шесть недель ее работы. В этом случае, X – это последовательность недель.

Иногда говорят, что требуется построить эмпирическую модель. Эмпирической называется модель, построенная на основе реальных наблюдений. Если модель удается найти, можно сделать прогноз о поведении исследуемого явления и процесса в будущем и, возможно, выбрать оптимальное направление ее развития.

В общем случае задача аппроксимации экспериментальных данных имеет следующую постановку:

Пусть известны данные, полученные практическим путем (в ходе n экспериментов или наблюдений), которые можно представить парами чисел (х_i; у_i). Зависимость между ними отражает таблица:

Имеется класс разнообразных функций F. Требуется найти аналитическое (т.е. математическое) выражение зависимости между этими показателями, то есть надо подобрать из множества функций F функцию f, такую что . которая наилучшим образом сглаживала бы экспериментальную зависимость между переменными и по возможности точно отражала общую тенденцию зависимости между X и Y, исключая погрешности измерения и случайные отклонения.

Выяснить вид функции можно либо из теоретических соображений, либо анализируя расположение точек (х_i; у_i) на координатной плоскости.

Графически решить задачу аппроксимации означает, провести такую кривую , точки которой (х_i; ŷ_i) находились бы как можно ближе к исходным точкам (х_i; у_i), отображающим экспериментальные данные.

Для решения задачи аппроксимации используют метод наименьших квадратов.

При этом функция считается наилучшим приближением к , если для нее сумма квадратов отклонений «теоретических» значений , найденных по эмпирической формуле, от соответствующих опытных значений , имеет наименьшее значение по сравнению с другими функциями, из числа которых выбирается искомое приближение.

Математическая запись метода наименьших квадратов имеет вид:

где n — количество наблюдений показателей.

Таким образом, задача аппроксимации распадается на две части.

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

После этого определяются численные значения неизвестных параметров выбранной эмпирической формулы, для которых приближение к заданной функции оказывается наилучшим.

Простейшим видом эмпирической модели с двумя параметрами, используемой для аппроксимации результатов экспериментов, является линейная регрессия, описываемая линейной функцией:

где а, b — искомые параметры.

Для модели линейной регрессии метод наименьших квадратов (1) запишется :

Для решения (2) относительно а и b приравнивают к нулю частные производные:

В итоге для нахождения a и b надо решить систему линейных алгебраических уравнений вида:

Реализовать метод наименьших квадратов в случае линейной регрессии в Excel можно различными способами.

1 способ. Построить систему линейных алгебраических уравнений, подставив в (3) все известные значения, и решить ее, например, матричным методом (см. зад. 4).

В формульном виде элемент расчетной таблицы приведен на рис. 26.

2 способ. Решить в Excel задачу оптимизации (2), применив для этого Поиск решения (см. зад. 5).

Замечание 1. Следует обратить внимание, что для целевой функции S удобно применить встроенную математическую функцию СУММКВРАЗН(массив1;массив2), в результате которой как раз и вычисляется сумма квадратов разностей двух массивов. В нашем случае следует в качестве массива1 указать диапазон исходных значений , а в качестве массива2 – «теоретические» значения , рассчитанные по формуле , где a и b – это адреса ячеек с искомыми значениями.

Замечание 2. В диалоговом окне команды Поиск решения следует задать целевую ячейку, направление цели – на минимум и изменяемые ячейки (рис. 28). Данная задача ограничений не содержит.

Замечание3. В качестве эмпирических моделей с двумя параметрами могут использоваться и нелинейные модели вида:

Описанный способ решения метода наименьших квадратов применим и для нелинейных зависимостей.

3 способ. Для нахождения значений параметров a и b в случае линейной регрессии можно использовать следующие встроенные в Excel статистические функции:

ЛИНЕЙН (известные_значения_У; известные_значения_Х)

Причем, функция НАКЛОН ( ) возвращает значение параметра а, функция ОТРЕЗОК( ) возвращает значение параметра b. Функция ЛИНЕЙН( ) возвращает одновременно оба параметра линейной зависимости, так как является функцией массива. Поэтому для ввода функции ЛИНЕЙН( ) в таблицу надо соблюдать следующие правила:

· выделить две рядом стоящие ячейки

· по окончании нажать одновременно комбинацию клавиш Ctrl+ Shift+Enter.

В результате в левой ячейке получится значение параметра а, а в правой – значение параметра b.

Для решения задачи аппроксимации графическим способом в Excel надо построить по исходным данным график, например, точечную диаграмму со значениями, соединенными сглаживающими линиями (см.зад.1). На эту диаграмму Excel может нанести Линию тренда. Линию тренда можно добавить к любому ряду данных, использующему следующие типы диаграмм: диаграммы с областями, графики, гистограммы, линейчатые или точечные диаграммы.

При создании линии тренда в Excel на основе данных диаграммы применяется та или иная аппроксимация. Excel позволяет выбрать один из пяти аппроксимирующих линий или вычислить линию, показывающую скользящее среднее.

Кроме того, Excel предоставляет возможность выбирать значения пересечения линии тренда с осью Y, а также добавлять к диаграмме уравнение аппроксимации и величину достоверности аппроксимации (R 2 ). Также, можно определять будущие и прошлые значения данных, исходя из линии тренда и связанного с ней уравнения аппроксимации.

Чтобы добавить линию тренда к ряду данных надо:

1. Активизировать щелчком мыши диаграмму.

2. Выполнить команду Диаграмма, Добавить линию тренда или переместить указатель на ряд данных, щелкнуть правой кнопкой мыши, а затем в контекстном меню выбрать команду Добавить линию тренда. В появившемся окне Линия тренда раскрыть вкладку Тип (рис. 29)

3. В списке Построен на ряде – выделить ряд данных, к которому нужно добавить линию тренда (Рис.29).

4. В группе Построение линии тренда (аппроксимация и сглаживание) выбрать один из шести типов аппроксимации (сглаживания). – линейная, логарифмическая, полиномиальная, степенная, экспоненциальная, скользящее среднее (Рис.29)

5. Чтобы установить параметры линии тренда надо раскрыть вкладку Параметры диалогового окна Линия тренда(рис. 30)

Показывать уравнение на диаграмме – осуществляет вывод уравнения аппроксимации на диаграмму в виде текстового поля.

Поместить на диаграмму величину достоверности аппроксимации R 2 – осуществляет вывод на диаграмму достоверности аппроксимации в виде текста.

Полиномиальное уравнение регрессии в excel

Есть 3 способа расчета значений полинома в Excel:

1-й способ с помощью графика;
2-й способ с помощью функции Excel =ЛИНЕЙН();
3-й способ с помощью Forecast4AC PRO;

Подробнее о полиноме и способе его расчета в Excel далее в нашей статье.

У полинома второй степени y=ax 2 +bx+c один экстремум (на графике ниже 1 максимум).

У Полинома третьей степени y=ax 3 +bx 2 +cx+d может быть один или два экстремума.

Один экстремум

Два экстремума

У Полинома четвертой степени не более трех экстремумов и т.д.

Как рассчитать значения полинома в Excel?

Есть 3 способа расчета значений полинома в Excel:

1-й способ с помощью графика;
2-й способ с помощью функции Excel =ЛИНЕЙН;
3-й способ с помощью Forecast4AC PRO;

1-й способ расчета полинома — с помощью графика

Выделяем ряд со значениями и строим график временного ряда.

На график добавляем полином 6-й степени.

Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»

Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»

В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».

Получаем уравнение полинома в читаемом формате:

y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35

Из этого уравнения берем коэффициенты a, b, c, d, g, m, v, и вводим в соответствующие ячейки Excel

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

2-й способ расчета полинома в Excel — функция ЛИНЕЙН()

Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel =ЛИНЕЙН()

«известные значения y» (объёмы продаж за периоды),
«известные значения x» (порядковый номер временного ряда),
в константу ставим «1»,
в статистику «0»

Получаем следующего вида формулу:

Для этого в часть формулы с «известными значениями x» вписываем степень полинома:

^ — для расчета коэффициентов полинома 6-й степени
^ — для расчета коэффициентов полинома 5-й степени
^ — для расчета коэффициентов полинома 2-й степени

Получаем формулу следующего вида:

Вводим формулу в ячейку, получаем 3,71 —- значение (a) для полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v

Для того, чтобы Excel рассчитал все 7 коэффициентов полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v, необходимо:

1. Установить курсор в ячейку с формулой и выделить 7 соседних ячеек справа, как на рисунке:

2. Нажать на клавишу F2

Получаем 7 коэффициентов полиномиального тренда 6-й степени.

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

2-й способ точнее, чем первый, т.к. коэффициенты тренда мы получаем без округления, а также этот расчет быстрее.

3-й способ расчета значений полиномиальных трендов — Forecast4AC PRO

Устанавливаем курсор в начало временного ряда

Заходим в лист с пошаговым расчетом «ForPol6», находим строку «Сложившийся тренд»:

Копируем значения в наш лист.

Получаем значения полинома 6-й степени, рассчитанные 3 способами с помощью:

Коэффициентов полиномиального тренда выведенных на график;
Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast Lite — автоматический расчет прогноза в Excel .
4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Множественная регрессия в EXCEL

history 26 января 2019 г.

Статистический анализ

Рассмотрим использование MS EXCEL для прогнозирования переменной Y на основании нескольких переменных Х, т.е. множественную регрессию.

Перед прочтением этой статьи рекомендуется освежить в памяти простую линейную регрессию – прогнозирование на основе значений только одного фактора.

Disclaimer : Данную статью не стоит рассматривать, как пересказ главы из учебника по статистике. Статья не обладает ни полнотой, ни строгостью изложения положений статистической науки. Эта статья – о применении MS EXCEL для целей Множественного регрессионного анализа. Теоретические отступления приведены лишь из соображения логики изложения. Использование данной статьи для изучения Регрессии – плохая идея.

Статья про Множественный регрессионный анализ получилась большая, поэтому ниже для удобства приведены ее разделы:

Прогнозирование единственной переменной Y на основании значений 2-х или более переменных Х называется множественной регрессией .

Множественная линейная регрессионная модель (Multiple Linear Regression Model) имеет вид Y=β ₀ +β ₁ *X ₁ +β ₂ *X ₂ +…+β _k *X _k +ε. В этом случае переменная Y зависит от k поясняющих переменных Х, т.е. регрессоров . ε — случайная ошибка . Модель является линейной относительно неизвестных параметров β.

Оценка неизвестных параметров

В этой статье рассмотрим модель с 2-мя регрессорами. Сначала введем необходимые обозначения и понятия множественной регрессии.

Для описания зависимости Y от 2-х переменных линейная модель имеет вид:

Параметры этой модели β _i нам неизвестны, но их можно оценить, используя случайную выборку (измеренные значения переменной Y от заданных Х). Оценки параметров модели (β ₀ , β ₁ , β ₂ ) обычно вычисляются методом наименьших квадратов (МНК) , который минимизирует сумму квадратов ошибок прогнозирования (критерий минимизации в англоязычной литературе обозначают как SSE – Sum of Squared Errors).

Ошибка ε имеет случайную природу и имеет свою функцию распределения со средним значением =0 и дисперсией σ 2 .

Оценки b ₁ и b ₂ называются коэффициентами регрессии , они определяют влияние соответствующей переменной X, когда все остальные независимые переменные остаются неизменными .

Сдвиг (intercept) или постоянный член b ₀ , определяет прогнозируемое значение Y, когда все поясняющие переменные Х равны 0 (часто сдвиг не имеет физического смысла в рамках модели и обусловлен лишь математическими вычислениями МНК ).

Вычислив оценки, полученные методом МНК, позволяют прогнозировать значения переменной Y:

Примечание : Для случая 2-х регрессоров, все спрогнозированные значения переменной Y будут лежать в плоскости (в плоскости регрессии ).

В качестве примера рассмотрим технологический процесс изготовления нити:

Инженер, на основе имеющегося опыта, предположил, что прочность нити Y зависит от концентрации исходного раствора (Х ₁ ) и температуры реакции (Х ₂ ), и соответствует модели линейной регрессии. Для нахождения комбинации переменных Х, при которых Y принимает максимальное значение, необходимо определить коэффициенты регрессии, сделав выборку.

В MS EXCEL коэффициенты множественной регрессии удобнее всего вычислить с помощью функции ЛИНЕЙН() . Это сделано в файле примера на листе Коэффициенты . Чтобы вычислить оценки:

выделите 3 ячейки в одной строке (т.к. мы рассматриваем случай 2-х регрессоров, то будут вычислены 2 коэффициента регрессии + величина сдвига = 3 значения, для вывода которых понадобится 3 ячейки). Пусть это будет диапазон С8:Е8 ;
в Строке формул введите = ЛИНЕЙН(D20:D50;B20:C50) . Предполагается, что в столбце В содержатся прогнозируемые значения Y (в нашей модели это Прочность нити), в столбцах С и D содержатся значения контролируемых параметров Х (Х1 – Концентрация в столбце С и Х2 – Температура в столбце D).
нажмите CTRL+SHIFT+ENTER (т.к. это формула массива ).

В левой ячейке будет рассчитано значение коэффициента регрессии b 2 для переменной Х2, в средней ячейке — значение коэффициента регрессии b 1 для переменной Х1, в правой – сдвиг . Обратите внимание, что порядок вывода коэффициентов регрессии обратный по отношению к расположению столбцов с данными соответствующих переменных Х (вычисленный коэффициент b 2 располагается левее по отношению к b 1 , тогда как значения переменной Х2 располагаются правее значений переменной Х1). Это может привести к путанице, поэтому лучше разместить коэффициенты над соответствующими столбцами с данными, как это сделано в строке 17 файла примера .

Примечание : В принципе без функции ЛИНЕЙН() можно обойтись, записав альтернативные формулы. Для этого в файле примера на листе Коэффициенты в столбцах I : K вычислены отклонения значений переменных Х _1i , Х _2i , Y _i от их средних значений , т.е.:

Далее коэффициенты регрессии рассчитываются по следующим формулам (эти формулы справедливы только при прогнозировании по 2-м независимым переменным Х):

При прогнозировании по 3-м и более независимым переменным Х формулы для вычисления коэффициентов регрессии значительно усложняются, поэтому следует использовать матричный подход.

В файле примера на листе Матричная форма выполнены расчеты коэффициентов регрессии с помощью матричного подхода.

Расчет можно произвести как пошагово, так и одной формулой массива :

Коэффициенты регрессии (вектор b ) в этом случае вычисляются по формуле b =(X T X) -1 (X T Y) или в другом виде записи b =(X ’ X) -1 (X ’ Y)

Под Х подразумевается матрица, состоящая из столбцов значений переменной Х с дополнительным столбцом единиц, а под Y – вектор-столбец значений Y.

Диаграмма рассеяния

В случае простой линейной регрессии (один регрессор, т.е. одна переменная Х) для визуализации связи между прогнозируемым значением Y и переменной Х строят диаграмму рассеяния (двумерную).

В случае множественной линейной регрессии двумерную диаграмму рассеяния можно построить только для анализа влияния каждого отдельного регрессора на Y (при этом остальные Х не меняются), т.е. так называемую Матричную диаграмму рассеивания (См. файл примера лист Диагр расс (матричная) ).

К сожалению, такую диаграмму трудно интерпретировать.

Более того, матричная диаграмма может вводить в заблуждение (см. Introduction to linear regression analysis / D . C . Montgomery , E . A . Peck , G . G . Vining , раздел 3.2.5 ), демонстрируя наличие или отсутствие линейной взаимосвязи между отдельным регрессором X _i и Y.

Для случая с 2-мя регрессорами можно предложить альтернативный вид матричной диаграммы рассеяния . В стандартной диаграмме рассеяния строятся проекции на координатные плоскости Х1;Х2, Y;X1 и Y;X2. Однако, если взглянуть на точки относительно плоскости регрессии , то картину, на мой взгляд, будет проще интерпретировать.

Сравним две матричные диаграммы рассеяния (см. файл примера на листе «Диагр расс (в плоск регрессии)» , построенные для одних и тех же наблюдений. Первая – стандартная,

вторая представляет собой вид сверху на плоскость регрессии и 2 вида вдоль плоскости.

На второй диаграмме становится очевидно, что разброс точек относительно плоскости регрессии совсем не большой и поэтому, скорее всего, построенная модель является полезной, а выбранные 2 переменные Х позволяют прогнозировать Y (конечно, для подтверждения этой гипотезы нужно провести процедуру F-теста ).

Несколько слов о построении альтернативной матричной диаграммы рассеяния:

Перед построением необходимо нормировать значения наблюдений (для каждой переменной вычесть среднее и разделить на стандартное отклонение ). В этом случае практически все точки на диаграммах будут находится в диапазоне +/-3 (по аналогии со стандартным нормальным распределением , 99% значений которого лежат в пределах +/-3 сигма). В этом случае, на диаграмме можно фиксировать мин/макс значений осей, чтобы EXCEL автоматически не модифицировал масштаб осей при изменении данных (это не всегда удобно);
Теперь координаты точек необходимо рассчитать в системе отсчета относительно плоскости регрессии (в которой плоскость Оху’ совпадает с плоскостью регрессии). Для этого необходимо найти матрицу вращения , например, через вращение приводящее к совмещению нормали к плоскости регрессии и вектора оси Z (0;0;1);
Новые координаты позволяют построить альтернативную матричную диаграмму. Кроме того, для удобства можно вращать систему координат вокруг новой оси Z, чтобы нагляднее представить себе распределение точек относительно плоскости регрессии (для этого использована Полоса прокрутки в ячейках Q31:S31 ).

Вычисление прогнозных значений Y (отдельное наблюдение и среднее значение) и построение доверительных интервалов

После того, как нами были найдены тем или иным способом коэффициенты регрессии можно приступать к вычислению прогнозных значений Y на основе заданных значений переменных Х.

Уравнение прогнозирования или уравнение регрессии в случае 2-х независимых переменных (регрессоров) записывается в виде:

Примечание: В MS EXCEL прогнозное значение Y для заданных Х ₁ и Х ₂ можно также предсказать с помощью функции ТЕНДЕНЦИЯ() . При этом 2-й аргумент будет ссылкой на столбцы, содержащие все значения переменных Х ₁ и Х ₂ , а 3-й аргумент функции должен быть ссылкой на диапазон ячеек, содержащий 2 значения Х (Х _1i и Х _2i ) для выбранного наблюдения i (см. файл примера, лист Коэффициенты, столбец G ). Функция ПРЕДСКАЗ() , использованная нами в простой регрессии, не работает в случае множественной регрессии .

Найдя прогнозное значение Y, мы, таким образом, вычислим его точечную оценку. Понятно, что фактическое значение Y, полученное при наблюдении, будет, скорее всего, отличаться от этой оценки. Чтобы ответить на вопрос о том, на сколько хорошо мы можем предсказывать новые значения Y, нам потребуется построить доверительный интервал этой оценки, т.е. диапазон в котором с определенной заданной вероятностью, скажем 95%, мы ожидаем новое значение Y.

Доверительные интервалы построим при фиксированном Х для:

нового наблюдения Y;
среднего значения Y (интервал будет уже, чем для отдельного нового наблюдения)

Как и в случае простой линейной регрессии , для построения доверительных интервалов нам потребуется сначала вычислить стандартную ошибку модели (standard error of the model) , которая приблизительно показывает насколько велика ошибка предсказания значений переменной Y на основании значений переменных Х.

Для вычисления стандартной ошибки оценивают дисперсию ошибки ε, т.е. сигма^2 (ее часто обозначают как MS Е либо MSres ) . Затем, вычислив из полученной оценки квадратный корень, получим Стандартную ошибку регрессии (часто обозначают как SEy или sey ).

где SSE – сумма квадратов значений ошибок модели ei=yi — ŷi ( Sum of Squared Errors ). MSE означает Mean Square of Errors (среднее квадратов ошибок, точнее остатков).

Величина n-p – это количество степеней свободы ( df – degrees of freedom ), т.е. число параметров системы, которые могут изменяться независимо (вспомним, что у нас в этом примере есть n независимых наблюдений переменной Y, р – количество оцениваемых параметров модели). В случае простой множественной регрессии с 2-мя регрессорами число степеней свободы равно n-3, т.к. при построении плоскости регрессии было оценено 3 параметра модели b (т.е. на это было «потрачено» 3 степени свободы ).

В MS EXCEL стандартную ошибку SEy можно вычислить формулы (см. файл примера, лист Статистика ):

Стандартная ошибка нового наблюдения Y при заданных значениях Х (вектор Хi) вычисляется по формуле:

x _i — вектор-столбец со значениями переменных Х (с дополнительной 1) для заданного наблюдения i.

Соответствующий доверительный интервал вычисляется по формуле:

где α (альфа) – уровень значимости (обычно принимают равным 0,05=5%)

р – количество оцениваемых параметров модели (в нашем случае = 3)

n-p – число степеней свободы

– квантиль распределения Стьюдента (задает количество стандартных ошибок , в +/- диапазоне которых вероятность обнаружить новое наблюдение равно 1-альфа). Т.е. если квантиль равен 2, то диапазон шириной +/- 2 стандартных ошибок относительно прогнозного значения Y будет с вероятностью 95% содержать новое наблюдение Y (для каждого заданного Хi). В MS EXCEL вычисления квантиля производят по формуле = СТЬЮДЕНТ.ОБР.2Х(0,05;n-p) , подробнее см. в статье про распределение Стьюдента .

– прогнозное значение Yi вычисляемое по формуле Yi= b 0+ b 1* Х1i+ b 2* Х2i (точечная оценка).

Стандартная ошибка среднего значения Y при заданных значениях Х (вектор Хi) будет меньше, чем стандартная ошибка отдельного наблюдения. Вычисления производятся по формуле:

x _i — вектор-столбец со значениями переменных Х (с дополнительной 1) для заданного наблюдения i.

Соответствующий доверительный интервал вычисляется по формуле:

Прогнозное значение Yi (точечная оценка) используется тоже, что и для отдельного наблюдения.

Стандартные ошибки и доверительные интервалы для коэффициентов регрессии

В разделе Оценка неизвестных параметров мы получили точечные оценки коэффициентов регрессии . Так как эти оценки получены на основе случайных величин (значений переменных Х и Y), то эти оценки сами являются случайными величинами и соответственно имеют функцию распределения со средним значением и дисперсией . Но, чтобы перейти от точечных оценок к интервальным , необходимо вычислить соответствующие стандартные ошибки (т.е. стандартные отклонения ) коэффициентов регрессии .

Стандартная ошибка коэффициента регрессии b _j (обозначается se ( b _j ) ) вычисляется на основании стандартной ошибки по следующей формуле:

где C _jj является диагональным элементом матрицы (X ’ X) -1 . Для коэффициента сдвига b ₀ индекс j=1 (верхний левый элемент), для b ₁ индекс j=2, b ₂ индекс j=3 (нижний правый элемент).

SEy – стандартная ошибка регрессии (см. выше ).

В MS EXCEL стандартные ошибки коэффициентов регрессии можно вычислить с помощью функции ЛИНЕЙН() :

Примечание : Подробнее о функции ЛИНЕЙН() см. статью Функция MS EXCEL ЛИНЕЙН() .

Применяя матричный подход стандартные ошибки можно вычислить и через обычные формулы (точнее через формулу массива , см. файл примера лист Статистика ):

= КОРЕНЬ(СУММКВРАЗН(E13:E43;F13:F43) /(n-p)) *КОРЕНЬ (ИНДЕКС (МОБР (МУМНОЖ(ТРАНСП(B13:D43);(B13:D43)));j;j))

При построении двухстороннего доверительного интервала для коэффициента регрессии его границы определяются следующим образом:

где t – это t-значение , которое можно вычислить с помощью формулы = СТЬЮДЕНТ.ОБР.2Х(0,05;n-p) для уровня значимости 0,05.

В результате получим, что найденный доверительный интервал с вероятностью 95% (1-0,05) накроет истинное значение коэффициента регрессии b _j . Здесь мы считаем, что коэффициент регрессии b _j имеет распределение Стьюдента с n-p степенями свободы (n – количество наблюдений, т.е. пар Х и Y).

Проверка гипотез

Когда мы строим модель, мы предполагаем, что между Y и переменными X существует линейная взаимосвязь. Однако, как это иногда бывает в статистике, можно вычислять параметры связи даже тогда, когда в действительности она не существует, и обусловлена лишь случайностью.

Единственный вариант, когда Y не зависит X, возможен, когда все коэффициенты регрессии β равны 0.

Чтобы убедиться, что вычисленная нами оценка коэффициентов регрессии не обусловлена лишь случайностью (они не случайно отличны от 0), используют проверку гипотез . В качестве нулевой гипотезы Н ₀ принимают, что линейной связи нет, т.е. ВСЕ β=0. В качестве альтернативной гипотезы Н ₁ принимают, что ХОТЯ БЫ ОДИН коэффициент β <>0.

Процедура проверки значимости множественной регрессии, приведенная ниже, является обобщением дисперсионного анализа , использованного нами в случае простой линейной регрессии (F-тест) .

Если нулевая гипотеза справедлива, то тестовая F -статистика имеет F-распределение со степенями свободы k и n — k -1 , т.е. F _{k, n-k-1} :

Проверку значимости регрессии можно также осуществить через вычисление p -значения . В этом случае вычисляют вероятность того, что случайная величина F примет значение F ₀ (это и есть p-значение ), затем сравнивают p-значение с заданным уровнем значимости α (альфа) . Если p-значение больше уровня значимости , то нулевую гипотезу нет оснований отклонить, и регрессия незначима.

В MS EXCEL значение F ₀ можно вычислить на основании значений выборки по вышеуказанной формуле или с помощью функции ЛИНЕЙН() :

В MS EXCEL для проверки гипотезы через p -значение используйте формулу =F.РАСП.ПХ(F ₀ ;k;n-k-1) файл примера лист Статистика , где показано эквивалентность обоих подходов проверки значимости регрессии).

В MS EXCEL критическое значение для заданного уровня значимости F _{1-альфа, k, n-k-1} можно вычислить по формуле = F.ОБР(1- альфа;k;n-k-1) или = F.ОБР.ПХ(альфа;k; n-k-1) . Другими словами требуется вычислить верхний альфа- квантиль F -распределения с соответствующими степенями свободы .

Таким образом, при значении статистики F ₀ > F _{1-альфа, k, n-k-1} мы имеем основание для отклонения нулевой гипотезы.

В программах статистики результаты процедуры F -теста выводят с помощью стандартной таблицы дисперсионного анализа . В файле примера такая таблица приведена на листе Надстройка , которая построена на основе результатов, возвращаемых инструментом Регрессия надстройки Пакета анализа MS EXCEL .

Генерация данных для множественной регрессии с помощью заданного тренда

Иногда, бывает удобно сгенерировать значения наблюдений, имея заданный тренд.

Для решения этой задачи нам потребуется:

задать значения регрессоров в нужном диапазоне (значения переменных Х);
задать коэффициенты регрессии ( b );
задать тренд (вычислить значения Y= b₀ +b₁ * Х ₁ + b₂ * Х ₂ );
задать величину разброса Y вокруг тренда (варианты: случайный разброс в заданных границах или заданная фигура, например, круг)

Все вычисления выполнены в файле примера, лист Тренд для случая 2-х регрессоров. Там же построены диаграммы рассеяния .

Коэффициент детерминации

Коэффициент детерминации R 2 показывает насколько полезна построенная нами линейная регрессионная модель .

По определению коэффициент детерминации R 2 равен:

R 2 = Изменчивость объясненная моделью ( SSR ) / Общая изменчивость ( SST ).

Этот показатель можно вычислить с помощью функции ЛИНЕЙН() :

При добавлении в модель новой объясняющей переменной Х, коэффициент детерминации будет всегда расти. Поэтому, рост коэффициента детерминации не может служить основанием для вывода о том, что новая модель (с дополнительным регрессором) лучше прежней.

Более подходящей статистикой, которая лишена указанного недостатка, является нормированный коэффициент детерминации (Adjusted R-squared):

где p – число независимых регрессоров (вычисления см. файл примера лист Статистика ).

Нелинейная регрессия в Excel

Добрый день, уважаемые читатели блога! Сегодня мы поговорим о нелинейных регрессиях. Решение линейных регрессий можно посмотреть по ССЫЛКЕ.

Данный способ применяется, в основном, в экономическом моделировании и прогнозировании. Его цель – пронаблюдать и выявить зависимости между двумя показателями.

Основными типами нелинейных регрессий являются:

полиномиальные (квадратичная, кубическая);
гиперболическая;
степенная;
показательная;
логарифмическая.

Также могут применяться различные комбинации. Например, для аналитики временных рядов в банковской сфере, страховании, демографических исследованиях используют кривую Гомпцера, которая является разновидностью логарифмической регрессии.

В прогнозировании с помощью нелинейных регрессий главное выяснить коэффициент корреляции, который покажет нам есть ли тесная взаимосвязь меду двумя параметрами или нет. Как правило, если коэффициент корреляции близок к 1, значит связь есть, и прогноз будет довольно точен. Ещё одним важным элементом нелинейных регрессий является средняя относительная ошибка (А), если она находится в промежутке

На этом, пожалуй, теоретический блок мы закончим и перейдём к практическим вычислениям.

У нас имеется таблица продаж автомобилей за промежуток 15 лет (обозначим его X), количество шагов измерений будет аргумент n, также имеется выручка за эти периоды (обозначим её Y), нам нужно спрогнозировать какова будет выручка в дальнейшем. Построим следующую таблицу:

Для исследования нам потребуется решить уравнение (зависимости Y от X): y=ax 2 +bx+c+e. Это парная квадратичная регрессия. Применим в этом случае метод наименьших квадратов, для выяснения неизвестных аргументов — a, b, c. Он приведёт к системе алгебраических уравнений вида:

Для решения этой системы воспользуемся, к примеру, методом Крамера. Видим, что входящие в систему суммы являются коэффициентами при неизвестных. Для их вычисления добавим в таблицу несколько столбцов (D,E,F,G,H) и подпишем соответственно смыслу вычислений — в столбце D возведём x в квадрат, в E в куб, в F в 4 степень, в G перемножим показатели x и y, в H возведём x в квадрат и перемножим с y.

Получится заполненная нужными для решения уравнения таблица вида.

Далее посчитаем суммы по каждому столбцу — воспользуемся ∑ в программе Excel.

Сформируем матрицу A системы, состоящую из коэффициентов при неизвестных в левых частях уравнений. Поместим её в ячейку А22 и назовём «А=«. Следуем той системе уравнений, которую мы избрали для решения регрессии.

То есть, в ячейку B21 мы должны поместить сумму столбца, где возводили показатель X в четвёртую степень — F17. Просто сошлёмся на ячейку — «=F17». Далее нам необходима сумма столбца где возводили X в куб — E17, далее идём строго по системе. Таким образом, нам необходимо будет заполнить всю матрицу.

В соответствии с алгоритмом Крамера наберём матрицу А1, подобную А, в которой вместо элементов первого столбца должны размещаться элементы правых частей уравнений системы. То есть сумма столбца X в квадрате умноженная на Y, сумма столбца XY и сумма столбца Y.

Также нам понадобятся ещё две матрицы — назовём их А2 и А3 в которых второй и третий столбцы будут состоять из коэффициентов правых частей уравнений. Картина будет такова.

Следуя избранному алгоритму, нам нужно будет вычислить значения определителей (детерминантов, D) полученных матриц. Воспользуемся формулой МОПРЕД. Результаты разместим в ячейках J21:K24.

Расчёт коэффициентов уравнения по Крамеру будем производить в ячейках напротив соответствующих детерминантов по формуле: a (в ячейке M22) — «=K22/K21»; b (в ячейке M23) — «=K23/K21»; с (в ячейке M24) — «=K24/K21».

Получим наше искомое уравнение парной квадратичной регрессии:

y=-0,074x 2 +2,151x+6,523

Оценим тесноту линейной связи индексом корреляции.

Для вычисления добавим в таблицу дополнительный столбец J (назовём его y*). Расчёта будет следующей (согласно полученному нами уравнению регрессии) — «=$m$22*B2*B2+$M$23*B2+$M$24». Поместим её в ячейку J2. Останется протянуть вниз маркер автозаполнения до ячейки J16.

Для вычисления сумм (Y-Y усредненное) 2 добавим в таблицу столбцы K и L с соответствующими формулами. Среднее по столбцу Y посчитаем с помощью функции СРЗНАЧ.

В ячейке K25 разместим формулу подсчёта индекса корреляции — «=КОРЕНЬ(1-(K17/L17))».

Видим, что значение 0,959 очень близко к 1, значит между продажами и годами есть тесная нелинейная связь.

Осталось оценить качество подгонки полученного квадратичного уравнения регрессии (индекс детерминации). Он рассчитывается по формуле квадрата индекса корреляции. То есть формула в ячейке K26 будет очень проста — «=K25*K25».

Коэффициент 0,920 близок к 1, что свидетельствует о высоком качестве подгонки.

Последним действием будет вычисление относительной ошибки. Добавим столбец и внесём туда формулу: «=ABS((C2-J2)/C2), ABS — модуль, абсолютное значение. Протянем маркером вниз и в ячейке M18 выведем среднее значение (СРЗНАЧ), назначим ячейкам процентный формат. Полученный результат — 7,79% находится в пределах допустимых значений ошибки

Если возникнет необходимость, по полученным значениям мы можем построить график.

Excel. Долгая дорога оцифровки. Часть 3. Апроксимация простых графиков полиномом средствами Excel ⁠ ⁠

Итак, мы имеем набор точек XY и нам требуется определить значение между заданными (опорными) точками. Начнём с самого простого варианта — набор точек позволяет найти уравнение полиномиального вида, которое с достаточной нам точностью описывает поведение функции с учётом имеющихся точек. Это будет в 90% апроксимация т.к. помним про погрешности связанные со снятием точек. Т.е. значения полученные по данной функции будут отличаться от заданных изначально. Кроме вариантов при степени полинома на 1 меньше количества точек (например полином 5-й степени, а известных точек 6-ть).

Excel. Долгая дорога оцифровки. Часть 3. Апроксимация простых графиков полиномом средствами Excel Microsoft Excel, Vba, Прост, Длиннопост

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

— зависимость явно полиномиальная.

Итак, в общем случае всё сводится к пяти шагам:

1. По имеющимся данным построить точечный график.

2. По построенным точкам выполнить построение линии тренда.

3. Подобрать степень полиномиальной зависимости таким образом, чтобы внешний вид (прохождение около/через заданные точки) соответствовал изначальному графику (тот что был на картинке). Проверить, возможно полином не лучший вариант.

4. Отобразить уравнение линии тренда на диаграмме и, если зависимость полиномиальная, становить формат чисел «Экспоненциальный» Число знаков — не менее 3х знаков.

5. Скопировать полученное уравнение и использовать в дальнейшем.

Есть ли способы без построения? Естественно есть (о них чуть подальше), но не видя как расположена линия тренда можно нарваться на неприятности.

Ещё одно заблуждение — «чем больше степень полинома, тем точнее». К сожалению, если по оси Х значения в десятках тысяч, а по оси Y в единицах фактически не реально найти полином выше 5-й степени. Точнее определить с достаточной достоверностью его коэффициенты (просто не хватает 15-ти знаков).

Определение коэффициентов полинома.

Как видно на скриншоте выше извлечение коэффициентов полинома происходит совсем не сложно.

где <1;2;3;4;5;6>— степень полинома, 7 — порядковый номер коэффициента.

И об этом написано в многих местах. Но вот то что не написано — извлечённые таким способом иногда не совсем соответствуют коэф-ам уравнения на диаграмме, а иногда совсем не соответствуют.

Это внутренняя математика Excel и может быть вызвано целым рядом причин. Основные:

— Значительный разрыв исходных данных. Например есть несколько сот снятых точек от 0 до 10, затем отсутствие снятых точек от 10 до 20, затем несколько сот точек с 20 до 50.

— Значительные степени чисел (как на скрине выше).

Выходом из данной ситуации является следующий макрос-костыль который забирает данные из указанного диапазона ( в примере — «B2:B6» Данные Х и «C2:C6» — Данные Y), строит график, на графике строит линию тренда с заявленной степенью (в примере вторая — Order = 2), копирует строку уравнения, распарсивает её и выкидывает в столбец ( в примере — начиная с ячейки E2 и вниз) коэффициенты полин.уравнения. Построенный график удаляется.

Sub Polynomial()

Dim rX As Range

Dim rY As Range

Dim rOut As Range

Dim dataLabelText As String

Dim coefficients As Variant

Set rX = ActiveSheet.Range(«B2:B6») ‘ Данные Х

Set rY = ActiveSheet.Range(«C2:C6») ‘ Данные Y

Set rOut = ActiveSheet.Range(«E2») ‘ Место выгрузки коэф-в

dataLabelText = Извлечение_Полинома(rX, rY)

coefficients = Извлечение_коэффициентов(dataLabelText)

With rOut.Resize(UBound(coefficients, 1) + 1, UBound(coefficients, 2))

‘назначаем формат для избежания ошибок при вставке получившихся формул

.NumberFormat = «#.####E+00»

.Value = coefficients

End WithEnd Sub

Private Function Извлечение_коэффициентов(dataLabelText As String) As Variant

Dim i As Integer

Dim rez() As Variant, txt As Variant

txt = Split(dataLabelText, «x»)

ReDim rez(LBound(txt) To UBound(txt), 1 To 2)

For i = LBound(txt) To UBound(txt)

txt(i) = Right(txt(i), IIf(i = LBound(txt), (Len(txt(i)) — 2), (Len(txt(i)) — 1)))

rez(i, 1) = i: rez(i, 2) = txt(i)

Извлечение_коэффициентов = rez

End Function

Function Извлечение_Полинома(rX As Range, rY As Range) As String

Dim MyChart As Chart

Dim text As String

Dim dt As Date

Set MyChart = ActiveSheet.Shapes.AddChart2(, , , , 450, 300).Chart

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(1).XValues = rX

.SeriesCollection(1).Values = rY

.ChartType = xlXYScatter

.FullSeriesCollection(1).Trendlines.Add

With .FullSeriesCollection(1).Trendlines(1)

.Type = xlPolynomial

.Order = 2 ‘ Указываем степень полинома

.DisplayEquation = True

.DataLabel.NumberFormat = «#.####E+00»

DoEvents ‘ Задержка. См. ниже

If .DataLabel.text <> «» Then Exit Do

If dt < Now — TimeSerial(0, 1, 0) Then Exit Do

For i = 1 To 100: DoEvents: Next

text = .DataLabel.text

Извлечение_Полинома = text

MyChart.Parent.Delete

End Function

Т.е. делает то, что можно сделать и руками с наглядным выбором вида апроксимации.

Быстрое определение искомого Y по заданному X.

Если Вы уверены в своём глазомере, не боитесь подводных камней и хотите быстро получить значение, то можно воспользоваться вот таким макросом.

Сокращённый вид макроса за авторством БМВ расположен ниже. Расширенный частично на скрине выше. Он понадобится нам в следующем посте, когда будем делать макрос по созданию макросов ), и там будет представлен полностю.

Public Function polinomEx(xVal As Range, yVal As Range, X As Single, stepen As Integer)

Dim I As Integer

Seria = Array(1, 2, 3, 4, 5, 6, 7)

If stepen > 7 Then stepen = 7

If xVal.Count < stepen + 1 Then stepen = xVal.Count — 1

polinomEx = 0#

ReDim Preserve Seria(stepen — 1)

For I = 1 To stepen + 1

polinomEx = polinomEx + _

(X ^ (stepen + 1 — I)) * _

Application.Index(WorksheetFunction.LinEst(yVal, _

IIf(stepen = 1, xVal, Application.Power(xVal, Seria)), _

True, True), 1, I)

End Function

Т.е. в функцию передаются столбцы исходных данных, значение Х, при котором требуется найти Y и степень полинома линии тренда.

И да, все заметили что посредством макроса есть возможность построить полином 7-й степени, тогда как линия тренда позволяет выполнять это только до 6-й?

Дальнейшее использование уравнения апроксимации.

Существует всего два подхода:

— Хранить исходные данные на листе. Или в виде таблицы, или в виде уравнения в ячейке.

— Хранить уравнение в виде макроса.

Первый подход удобен при разовом использовании. Если возможно неоднократное использование зависимости, или возможна её модификация, или зависимостей больше десятка — макрос предпочтительнее.

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

Теория вкратце [ Часть 1. ]

Забираем данные с листа. [ Часть 2. ]

Апроксимация простых графиков полиномом средствами Excel [ Часть 3.] Этот пост

Макрос по созданию макросов апроксимации простых графиков полиномом [ Часть 4.]

Апроксимация графиков двух аргументов полиномом [ Часть 5.]

Кусочная интерполяция простых графиков [ Часть 6.]

635 постов 14.5K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Я немного разбираюсь в этом, но все похоже на фокус, особенно конструкция =ИНДЕКС(ЛИНЕЙН(F4:F13;E4:E13^<1;2;3;4;5;6>);1;7). Я месяц потратил на макрос расчета коэфф. полинома МНК, чтобы не копировать их с графика, а тут вывод штатной функцией.

Буду разбирать не торопясь.

Помощь с макросом⁠ ⁠

Дамы и господа, добрый день.

Есть макрос который берет выделенные ячейки и сохраняет их в папку с файлом в виде картинки JPG. Мне надо чтоб сохранял заранее заданный диапазон. Сердцем чую, что ответ довольно прост, но мозгом пока не допер. Вся надежда на ваши светлые умы.

Помощь с макросом Windows, Компьютерная помощь, Microsoft Excel, Vba, Программирование

Весёлые маркеры графиков⁠ ⁠

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

Весёлые маркеры графиков Microsoft Excel, Vba, Прост, Длиннопост

Установка рисунков в качестве маркеров позволяет разнообразить внешний вид документации, сделав её нагляднее. Установка смайлов (© http://www.kolobok.us/ ) сделана в качестве примера (помните про Aiwan то? Или забыли. ).

Для гармоничного отображения требуется проредить количество маркеров, в противном случае произойдёт наложение рисунков друг на друга. О прореживании писал ранее.

Весёлые маркеры графиков Microsoft Excel, Vba, Прост, Длиннопост

Заменить маркеры на рисунки, в данном случае они представлены смайлами, можно при помощи не сложного макроса

Sub Markers_Smiles()

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

For Each icell In [C2:C102]

ActiveChart.FullSeriesCollection(1).Points(icell.Row — 1).Select

‘ Убираю рамки вокруг маркеров

Selection.MarkerForegroundColorIndex = xlNone

‘ Установка типа маркера «Рисунок»

Selection.MarkerStyle = -4147

Selection.Format.Fill.UserPicture «D:\4.gif»

If icell.Value = 0 Then Selection.Format.Fill.UserPicture «D:\1.gif»

If icell.Value = 1 Then Selection.Format.Fill.UserPicture «D:\2.gif»

If icell.Value = 2 Then Selection.Format.Fill.UserPicture «D:\3.gif»

[C2:C102] — столбец с признаками маркера. Число элементов равно числу данных (Х или Y). Может как заполняться вручную, так и быть расчётным (см.рисунок ниже).

D:\1.gif . D:\4.gif — пути к рисункам.

Аналогично производится заполнение рисунками нескольких графиков на диаграмме

Весёлые маркеры графиков Microsoft Excel, Vba, Прост, Длиннопост

Sub Прореживание_маркеров()

‘ Активируем диаграмму

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

‘ Перебор по всем графикам диаграммы

For k = 1 To ActiveChart.FullSeriesCollection.Count

‘ Удаляем все маркеры на линии

For i = 1 To ActiveChart.SeriesCollection(k).Points.Count

ActiveChart.FullSeriesCollection(k).Points(i).Select

Selection.MarkerStyle = -4142

‘ Выставляем маркеры с требуемым шагом.

For i = 1 To ActiveChart.SeriesCollection(k).Points.Count Step 4

ActiveChart.FullSeriesCollection(k).Points(i).Select

With Selection

.MarkerStyle = 8

.MarkerSize = 15

Public Sub color_graph()

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

For k = 1 To ActiveChart.FullSeriesCollection.Count ‘ Перебор по всем графикам

For Each icell In [C2:C102]

ActiveChart.FullSeriesCollection(k).Points(icell.Row — 1).Select

Selection.MarkerStyle = -4147

Selection.Format.Fill.UserPicture «D:\4.gif»

If icell.Value = 0 Then Selection.Format.Fill.UserPicture «D:\1.gif»

If icell.Value = 1 Then Selection.Format.Fill.UserPicture «D:\2.gif»

If icell.Value = 2 Then Selection.Format.Fill.UserPicture «D:\3.gif»

Аналогично разным графикам одной диаграммы можно присвоить уникальные маркеры

Весёлые маркеры графиков Microsoft Excel, Vba, Прост, Длиннопост

Sub Markers()

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

For i = 1 To ActiveChart.FullSeriesCollection.Count ‘ Перебор по всем графикам

ActiveChart.FullSeriesCollection(i).Select

Selection.MarkerForegroundColorIndex = xlNone

Selection.MarkerStyle = -4147

If i = 1 Then Selection.Format.Fill.UserPicture «D:\1.gif»

If i = 2 Then Selection.Format.Fill.UserPicture «D:\2.gif»

If i = 3 Then Selection.Format.Fill.UserPicture «D:\3.gif»

If i = 4 Then Selection.Format.Fill.UserPicture «D:\4.gif»

If i = 5 Then Selection.Format.Fill.UserPicture «D:\5.gif»

Ну или просто разными штатными маркерами разные графики. Но в автоматическом режиме — очень сокращает время подготовки документации. Полезно при подготовке к печати в чёрно-белом варианте.

Весёлые маркеры графиков Microsoft Excel, Vba, Прост, Длиннопост

Sub Установка_разных_маркеров()

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

For i = 1 To ActiveChart.FullSeriesCollection.Count ‘ Перебор по всем графикам

ActiveChart.FullSeriesCollection(i).Select

Selection.Format.Line.ForeColor.RGB = RGB(0, 0, 0) ‘ Цвета линий и маркера

Selection.Format.Line.Weight = 0.75 ‘ Установка толщины линии

Selection.MarkerStyle = i ‘ Установка типа маркера

Selection.MarkerSize = 4 ‘ Установка размера маркера

Selection.Format.Fill.ForeColor.RGB = RGB(255, 255, 255) ‘ Установка заливки маркера

Можно ли это сделать без макросов? Несомненно. Долго и нудно кликать кнопочки.

Весёлые маркеры графиков Microsoft Excel, Vba, Прост, Длиннопост

Но как по мне — проще скопировать и немного поправить код простого макроса. А в остальном — ваш выбор.

Excel триальный⁠ ⁠

Немного отвлечённый пост о защите своей работы.

Итак, общеизвестны способы закрытия информации в Excel, а именно:

1. Защита листа/книги

Excel триальный Microsoft Excel, Vba, Прост, Длиннопост

Выбираем разрешения/допуски, вводим пароль, сохраняем файл.

Дополнительно для каждой ячейки можно указать защищается ли она или нет. По умолчанию — защищается.

2. Защита кода

Excel триальный Microsoft Excel, Vba, Прост, Длиннопост

Точно так же вводим пароль (с повторением), ок, сохраняем.

Но все эти способы не более чем игрушки, и вскрываются совершенно не сложно при наличии некоторых минимальных навыков и особенно при сохранении в *.xlsm (файл с поддержкой макросов). Сохраняйте в *.xlsb (двоичный код), если хотите хоть немного защитить свою работу.

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

Каким же образом можно ещё затруднить использование вашей работы, кроме как не давать её?

Ну для начала надо определиться что защищаем. Если это просто текст, и вы его кому то отдали, то забудьте о защите — он общедоступен. Но документ Excel это, прежде всего расчёты. Своей масштабируемостью они и ценны — при изменении исходных данных пересчёт произойдёт автоматически.

Этим можно воспользоваться выполнив передачу результатов расчёта в виде статических таблиц. Да, можно распечатать/сохранить в pdf, или банальным Ctrl+А / Ctrl+C / Ctrl+V /только значения/. А можно просто воспользоваться простым макросом:

‘ Замена всех формул на листе в значения

Sub Form_2_Dan()

Dim a As Integer

‘ Запрашиваем подтверждение

a = MsgBox(«Внимание!» & _

Chr(10) & «Вы точно хотите заменить все формулы на листе на значения?» & _

Chr(10) & «Это необратимо!», _

52, «Замена формул на значения.»)

‘ Если OK, то замену производим

If a = 6 Then

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Расположение макроса — модуль.

Макрос сохраняется в личный набор/надстройку и кнопка запуска выводится на панель.

Внимание! Действие макроса необратимо!

Можно сделать «триальным» расчёт разместив в модулях листов вот такого вида макрос.

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False

If Date >= #10/6/2022# Then ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Application.ScreenUpdating = True

Т.е. после 10.06.2022 все расчёты с листа исчезнут. А цифры останутся.

Можно заменить проверку на заполнение ячейки, например проверить что в определённой ячейке записан автор труда «Вася Пупкин». 🙂 При смене которого всё превратится в набор цифр..

Естественно доступ к макросам должен быть закрыт/запаролен.

Ещё вариант — ввод пароля на саму книгу:

Private Sub Workbook_Open()

Dim i&, n&, P As Variant

Application.ScreenUpdating = False

If Date >= #1/2/2022# Then

For i = 1 To Sheets.Count

Sheets(i).Activate

Sheets(i).Protect «1234»

P = InputBox(«Время использования книги истекло, для продолжения введите пароль», «ВВОД ПАРОЛЯ»)

If P = «°0176» Then

For i = 1 To Sheets.Count

Sheets(i).Activate

Sheets(i).Unprotect «1234»

If n = 0 Then

Application.DisplayAlerts = False

ThisWorkbook.Close

Application.DisplayAlerts = True

MsgBox «Пароль не верный, у вас еще » & n & » попытки»

Application.ScreenUpdating = True

Расположение макроса — «Эта книга».

#1/2/2022# — дата с которой будет запрашиваться пароль

«°0176» – правильный пароль

Excel триальный Microsoft Excel, Vba, Прост, Длиннопост

И при открытии файл будет встречать весёлым окошком:

Excel триальный Microsoft Excel, Vba, Прост, Длиннопост

Естественно можно открыть файл без выполнения макросов, но если расчёт в экселе построен на использовании макросов, то цель достигнута — расчёт производиться не будет.

И да, это всё игрушки — серьёзные дяденьки с тётеньками при необходимости поломают сие поделия, и узнают как вы определяли дискриминант. (0_о). Даже если Вы применили обфускацию кода или перенос кода в dll.

Построение графиков⁠ ⁠

Сегодня разберём задачку, которая вставала перед каждым пользователем Excel — необходимость построить график функции.

Построение графиков Microsoft Excel, Vba, Плюшка, Длиннопост

Любой, кто решал эту задачку — действовал следующим способом:

1. Создаётся столбец Х;

2. Создаётся столбец Y, котором происходит расчёт согласно заданной функции;

3. Выделяются два созданных столбца и вставляется график.

Но это просто и скучно. Есть другой способ. Построить график непосредственно из макроса.

Начнём с простого — у нас есть набор точек соответствия X и Y.

Sub Построй_график_по_точкам()

Dim MyChart As Chart

Set MyChart = ActiveSheet.Shapes.AddChart2.Chart

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(1).Name = «xlXYScatterSmoothNoMarkers»

.SeriesCollection(1).XValues = Array(0#, 0.5, 1#, 1.5, 2#, 2.5, 3#, 3.5, 4#, 4.5, 5#)

.SeriesCollection(1).Values = Array(0#, 0.4794, 0.8415, 0.9975, 0.9093, 0.5985, 0.1411, -0.3508, -0.7568, -0.9775, -0.9589)

.ChartType = xlXYScatterLines ‘ Соединение точек прямыми

.SetElement msoElementLegendNone

Другие варианты отображения линии графика:

.ChartType = xlXYScatterLinesNoMarkers ‘ Соединение точек прямыми без маркеров

.ChartType = xlXYScatterSmoothNoMarkers ‘ Сглаженная линия

Более подробно о типах — тут

Сборка Array(. ) может быть выполнена с использованием программы, которую я выкладывал в 7-й части темы про оцифровку, ну или заполнить руками.

Как не трудно догадаться — вовсе не обязательно иметь готовый набор данных.

Рассмотрим ситуацию, когда требуется построить два графика на одной диаграмме.

Для упрощения восприятия использую две простые функции линий y1 = x — 20, y2 = x + 20.

Sub Создать_диаграмму()

Dim MyChart As Chart

Dim i As Integer, Xmin As Single, dX As Single, Xmax As Single, _

Ymin As Single, Ymax As Single, dY As Single

Dim X() As Single

Dim Y() As Single

Dim Yp() As Single

Xmin = 0: Xmax = 300: dX = 20 ‘ Сие больше нужно для осей и оформления

Ymin = 0: Ymax = 160: dY = 20

ReDim X(0 To Xmax — Xmin): ReDim Y(0 To Xmax — Xmin, 1 To 2)

ReDim Yp(0 To Xmax — Xmin)

For i = 0 To Xmax — Xmin Step 1

X(i) = Xmin + i

‘ Заполнение данных первого графика

Y(i, 1) = X(i) — 20

‘ Заполнение данных второго графика

Y(i, 2) = X(i) + 20

‘ создадим новую диаграмму и зададим ей габаириты

Set MyChart = ActiveSheet.Shapes.AddChart2(, , , , 300, 200).Chart

For i = 1 To 2

For j = 0 To Xmax — Xmin Step 1

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(i).XValues = X

.SeriesCollection(i).Values = Yp

.ChartType = xlXYScatterSmoothNoMarkers

При задании новой диаграммы можно задать в том числе и положение диаграммы на листе

AddChart2(Стиль,XlChartType,слева,сверху,ширина,высота,NewLayout)

В итоге получим вот такую диаграмму:

Построение графиков Microsoft Excel, Vba, Плюшка, Длиннопост

В дальнейшем можно обработать её как обычную — задать цвета, толщины и т.д. Но можно это сразу поручить нашему макросу:

Sub Создать_диаграмму()

Dim MyChart As Chart

Dim i As Integer, Xmin As Single, dX As Single, Xmax As Single, _

Ymin As Single, Ymax As Single, dY As Single

Dim X() As Single

Dim Y() As Single

Dim Yp() As Single

Xmin = 0: Xmax = 300: dX = 20 ‘ Сие больше нужно для осей и оформления

Ymin = 0: Ymax = 340: dY = 20

ReDim X(0 To Xmax — Xmin): ReDim Y(0 To Xmax — Xmin, 1 To 2)

ReDim Yp(0 To Xmax — Xmin)

For i = 0 To Xmax — Xmin Step 1

X(i) = Xmin + i

Y(i, 1) = X(i) — 20

Y(i, 2) = X(i) + 20

Set MyChart = ActiveSheet.Shapes.AddChart2(, , 0, 0, 400, 230).Chart

For i = 1 To 2

For j = 0 To Xmax — Xmin Step 1

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(i).XValues = X

.SeriesCollection(i).Values = Yp

.ChartType = xlXYScatterSmoothNoMarkers

With MyChart

.SetElement (msoElementPrimaryCategoryGridLinesMajor)

‘ Включаю отображение названия осей

.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Text = «Расход Go т/ч»

.Axes(xlValue, xlPrimary).AxisTitle.Text = «Давление кгс/кв.см.»

‘ Выключаю отображение легенды

.SetElement (msoElementLegendNone)

‘ Выключаю отображения заголовка диаграммы

.SetElement (msoElementChartTitleNone)

‘ Выставляем параметры осей

.Axes(xlCategory).MinimumScale = Xmin

.Axes(xlCategory).MaximumScale = Xmax

.Axes(xlCategory).MajorUnit = dX

.Axes(xlValue).MinimumScale = Ymin

.Axes(xlValue).MaximumScale = Ymax

.Axes(xlValue).MajorUnit = dY

‘ Оформление гризонтальной оси

MyChart.Axes(xlCategory).Select

With Selection.Format.Line

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 0, 0)

.ForeColor.TintAndShade = 0

.ForeColor.Brightness = 0

.Transparency = 0

.Visible = msoTrue

.Weight = 1.25

‘ Оформление вертикальной оси

MyChart.Axes(xlValue).Select

With Selection.Format.Line

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 0, 0)

.ForeColor.TintAndShade = 0

.ForeColor.Brightness = 0

.Transparency = 0

.Visible = msoTrue

.Weight = 1.25

‘ Оформление горизонтальной сетки

MyChart.Axes(xlValue).MajorGridlines.Select

With Selection.Format.Line

.Visible = msoTrue

.DashStyle = msoLineDash

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 176, 240)

.Transparency = 0

‘ Оформление вертикальной сетки

MyChart.Axes(xlCategory).MajorGridlines.Select

With Selection.Format.Line

.Visible = msoTrue

.DashStyle = msoLineDash

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 176, 240)

.Transparency = 0

По итогу диаграмма будет выглядеть так:

Построение графиков Microsoft Excel, Vba, Плюшка, Длиннопост

Как не трудно понять, данных, по которым построена диаграмма, на листе нет. И после удаления макроса останется только итоговый результат.

Кому то это покажется слишком сложным, однако открою маленький секрет — очень редкие люди пишут макрос с нуля. В 90% достаточно иметь готовый макрос (см листинг выше), заменить в нём пару строк (сменить функции, изменить диапазоны. ) и всё. По итогу построение занимает меньше времени чем построение классическим способом.

Такое построение позволит извлечь данные промежуточного расчёта, построить массово однотипные диаграммы и. и дальнейшее применение зависит только от фантазии.

Ну и всегда есть вариант удивить преподавателя (0_о).

Excel. Долгая дорога оцифровки. Часть 9. Оформление графиков, или отображение поиска решения⁠ ⁠

Итак, мы с вами имели рисунок на бумажке, перевели его в цифру (сняли точки), написали макрос, позволяющий определить значение Y по известным аргументам. В некоторых случаях этого достаточно, однако не всегда. Например для отчёта требуется указать поиск решения в графическом виде, поскольку заказчика «я фсио оцифровал! Вы не пониаити, у меня макрос!» не устраивает. Особенно когда речь идёт о больших деньгах, и проводятся гарантийные испытания с определением поправочных коэффициентов (например.). Или преподаватель в институте будет приятно удивлён красивому графику в курсовом проекте/дипломе.

Итак, по сути потребуется решить два вопроса:

1. Построить ход поиска с помощью стрелки/стрелок.

2. Совместить построенный график с изначальным рисунком.

Т.е. получить что то похожее на вот это:

Excel. Долгая дорога оцифровки. Часть 9. Оформление графиков, или отображение поиска решения Microsoft Excel, Vba, Плюшка, Длиннопост

На самом деле нет принципиальной разницы в начале построить поиск решения или в начале совместить рисунок с диаграммой. Но начну с построения, т.к. при этом меньше мусора на рисунках.

Часть 1. Построение поиска решения.

Итак, у нас есть заданные аргументы (G2, t1в) и результат расчёта Р2. На графике сие будет выглядеть как одна точка с координатами X = G2 = 200 (в нашем примере) и Y = Р2 = 0,065

Существуют минимум три метода построения стрелки поиска:

Вариант 1. Для вертикальной и горизонтальной части строим независимые линии.

После построения настраиваем цвета, указываем наличие стрелки, и т.д.

Для вертикальной линии второй точкой указывается точка с равным значением по Х и минимумом по бумажному графику Y.

Для горизонтальной линии второй точкой указывается точка с равным значением по Y и минимумом по бумажному графику X.

Минимумы и максимумы диаграммы выставляются равными минимумам и максимумам бумажного рисунка.

Хоть данный вариант и кажется наиболее раздутым, но на практике, когда линий поиска десяток, он наиболее удобен и понятен.

Вариант 2. Единая линия поиска.

Выставление значений дополнительных точек, и значений осей аналогично Варианту 1.

Вариант 3. Использование погрешностей для указания поиска решения.

Если точка одна, то для отображения линий погрешности необходимо перейти в настройки предела погрешности по Х и по Y поочерёдно и.

— величина погрешности «пользовательская».

В качестве отрицательной величины погрешности указываем соответственно значение X и Y

Если есть желание получить стрелку направленную к оси Y, а ось Х начинается не с 0 (в нашем случае с 2-ти), то потребуется сделать ячейку рассчитывающую смещение относительно 0.

В нашем примере сделаем такое и для X и Y:

ось Х сдвинута на 20. Соответственно имеем ячейку Хзаданное — Хсмещения = 200 — 20

ось Y сдвинута на 0,02 Соответственно имеем ячейку Yзаданное — Yсмещения

Это значения не статичны, т.е. они пересчитаются при изменении исходных данных.

При указании отображения погрешностей ссылаемся на данные ячейки.

Аналогично первым вариантам указываются свойства линий.

На самом деле третий способ самый быстрый и лёгкий, это описание сложновато. При наличии необходимости указания поиска для группы точек, и особенно отсутствии смещения 0, вот такие диаграммы делаются наиболее просто именно третьим способом.

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

Однако можно сделать выноску для той самой, единственной точки.

Результаты всех трёх способов не сильно отличаются:

Часть 2. Совмещение построенного графика с изначальным рисунком.

И опять есть минимум три варианта.

Вариант 1. Использование рисунка в качестве подложки под областью построения (то, что расположено внутри границ осей). Для этого рисунок сначала подготавливается (обрезается по размерам построения, при этом подписи осей оказываются обрезанными), а затем вставляется по пути: Формат области построения – Заливка – Рисунки и текстура – Файл / из буфера обмена;

Вариант 2. Использование рисунка в качестве подложки области диаграммы (вкладка Формат области диаграммы – Заливка – Рисунки и текстура — Файл) вставляется рисунок графика (предварительно подготовленный и очищенный. Необходимо также учитывать, что потребуется некоторая ширина полей для выставления подписей). Совмещаются границы графика Excel с границами графика рисунка перетягиванием за маркеры границы графика (перемещение указал стрелками).

выставляются границы осей графика Excel в соответствии с границами графика (если не выставили ранее). При необходимости производится отключение отображения подписей осей, сетка и название диаграммы.

в качестве свойств графика линии указывается её цвет, отсутствие маркеров и окончание графика в виде стрелки и т.д.. т.е. наводится окончательный лоск обеспечивающий хорошую читабельность диаграммы.

при необходимости можно построить дополнительную линию. В качестве примера построена дополнительная кривая при 40°С при помощи созданной пользовательской функции при заданной температуре 40°С и переменной влажности. Аналогично построена дополнительная линия на первом рисунке

Вариант 3. При третьем варианте рисунок вставляется на лист Excel, построенный график/ подготовленная диаграмма размещается над рисунком, при этом заливка поля построения и самой диаграммы «отсутствует» или «прозрачная». После совмещения изображение и диаграмма фиксируются между собой как это было указано в посте «Нестандартные заголовки диаграмм».

Третий вариант позволяет разместить отображение поиска решения для нескольких диаграмм расположенных на одном листе, если таковое требуется заказчиком. Например на рисунке ниже на одном листе 7-мь диаграмм, и в дальнейшем данный рисунок пошёл в отчёт скомпонованный в таком виде.

Отдельно стоят диаграммы состоящие из расположенных рядом двух и более диаграмм.

Их оформление, опять же, может быть реализовано тремя способами.

Способ 1 — применение третьего варианта наложения диаграмм на рисунок (описано выше). Т.е. строим два независимых графика для левой и правой части, делаем их прозрачными и накладываем на рисунок.

Способ 2 — применение первого варианта, наложение графика на область построения (описано выше). Т.е. строим два независимых графика для левой и правой части, накладываем области построения и размещаем взаимно друг другу до совпадения минимума и максимума.

Способ 3. — пригоден только для расположенных рядом двух диаграмм. Данный способ позволяет избавится от стыка, неизбежно возникающего при первых двух способах. Основано как правило на применении второго варианта описанного выше, а именно использовании рисунка как подложки под диаграммой.

Рассмотрим один из вариантов построения стрелки на диаграмме, состоящей из двух диаграмм, при этом ширина клеток и величина шага для правого и левого графика разная.

Для наглядности оси были ярко выражены и отодвинуты относительно области построения, а графики разнесены по цветам.

Шаг 1. Построение левого графика (синий график, синяя ось, синие данные).

1. Построить точечный график по исходным данным, причём заложить небольшой перехлёст по Х (установлено 80 вместо 70-ти по рисунку);

2. Сделать подложку под диаграмму (используется весь рисунок, без обрезок или разделения на две части);

3. Растянуть область построения на рисунок;

4. Задать значения оси (диапазон) Y в соответствии с оцифровкой;

5. Задать значения оси (диапазон) Х таким образом, чтобы Хмин было равно минимальному значению на рисунке (30), а Хмакс подобрать таким образом, чтобы совпали значения рисок (40=40, 50=50, 60=60, 70=70).

Шаг 2. Построение правого графика (красный график, красный ось, красный данные).

1. Построить точечный график по исходным данным, причём минимум Х заложить равным минимуму по второй оси (0);

2. Указать построение по вспомогательным осям;

3. Задать значения вспомогательной оси (диапазон) Y в соответствии с оцифровкой;

4. Задать значения оси (диапазон) Х таким образом, чтобы Хмакс было равно максимальному значению по второй оси рисунка, а Хмин подбрать таким образом, чтобы начало второго графика легло на минимум второй оси Х рисунка.

Шаг 3. Убрать отображение подписей осей, сетки и т.д. Настроить цвета линий.

Для кого то это покажется элементарным, но я на своей практике не один раз ломал голову как выполнить графическое оформление поиска решения. Базовыми знаниями поделился. Всё дальнейшее зависит от вас. Будут вопросы — помогу по мере сил.

Пожалуй на этом закончим и серию Excel. Долгая дорога оцифровки. Всё обещанное показал, а именно:

9. Отображение поиска решения (данный пост).

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции⁠ ⁠

По аналогии с Excel. Долгая дорога оцифровки. Часть 4. Макрос по созданию макросов апроксимации простых графиков полиномом и Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция не сложно выполняется макрос по созданию макросов оцифровки простых графиков с использованием кусочной интерполяции.

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции Microsoft Excel, Vba, Плюшка, Длиннопост

Описание вводимых данных аналогично ранее изложенному.

Если ещё немного развить тему, то и макрос создания макросов функции с двумя аргументами не проблема:

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

Основное нововведение — определение количества графиков. Если вспомните ещё в Excel. Долгая дорога оцифровки. Часть 2. Забираем данные с листа я писал, что что «снятие точек производить от меньшего Х к большему. При наличии диаграммы зависимости от двух аргументов типаY(X1, X2) начиная с графика меньшего Х2. С обязательным условием — каждая следующая линия должна начинаться с Х меньшего, чем закончилась предыдущая.«. И теперь можно этим воспользоваться — определить количество переходов на новую линию по уменьшению Х по сравнению с предыдущим.

For i = 2 To xVal.Count

If i = xVal.Count Then

Nkon(Ndiap) = i

If xVal.Rows(i) < xVal.Rows(i — 1) Then

Nkon(Ndiap) = i — 1

Ndiap = Ndiap + 1

Nna4(Ndiap) = i

Ну а дальше просто — перебираем поочерёдно все диапазоны, для каждого определяем уравнение апроксимации.

Результирующий макрос будет иметь вид:

‘ Поправки Сербия Панчево Страница 34 из 77 Нижний рисунок

Public Function ТЭХ_ПТ80_Рис3(ByRef Go As Single, ByRef CkH As Single) As Single

Dim krit_kriv As Variant

krit_kriv = array(2.96,3.06,3.15)

Dim kriv As Variant

kriv = Array(-0.00271242 * Go + 0.100817, _

-0.00252906 * Go + 0.230858, _

0.000276671 * Go ^ 2 -0.203078 * Go + 31.5862)

ТЭХ_ПТ80_Рис3= kus_interp(krit_kriv, kriv, CkH, 2)

End Function

Не забываем удалять кавычки в начале и конце макроса при копировании в модуль.

Давайте так, чтобы не утомлять читателя выкопировкой текстовок макросов — выкладываю сие для свободной скачки/использования/модернизации

Если возникнут вопросы как сие работает, распишу. Если у кого то что то не заработает — обращайтесь, посмотрю.

В программе есть не описанный мной макрос кубического сплайна, но т.к. автор не я, и макрос выложен в общественный доступ, для ознакомления с остальными сплайнами переходите по приведённой в макросе ссылке

~~За сим тему с оцифровкой считаю закрытой.~~ Все базовые функции показал. С помощью данных функций, а так-же их комбинаций и расширений можно сделать автоматическую оцифровку совершенно разнообразных конфигураций диаграмм.

Например диаграмма с несколькими независимыми графиками типа такой. Можно либо сделать 3 независимых макроса, либо один с выбором графика.

С помощью автоматического создания макросов

Позволит получить (текстовка от графика отличного от представленного выше рисунка)

‘ ТЭХ ПТ80 Рис.3 Давление в отборах при конденсационном режиме [МПа]

Public Function ТЭХ_ПТ80_Рис3(ByRef Go As Single, ByRef Название_графика As Variant) As Variant

Dim krit_graph As Variant

krit_graph = array(1,2,3)

Select Case Название_графика

Case krit_graph(0)

ТЭХ_ПТ80_Рис3 = -0.0027124 * Go ^ 1 + 0.10082

Case krit_graph(1)

ТЭХ_ПТ80_Рис3 = -0.0025397 * Go ^ 1 + 0.23509

Case krit_graph(2)

ТЭХ_ПТ80_Рис3 = -0.0026659 * Go ^ 1 + 0.4529

ТЭХ_ПТ80_Рис3 = 999999999999999

End Function

При желании указывать название графика правится krit_graph = array(«Go»,»Qo»,»qt»).

Ну и гораздо более сложноподчинённые, например что реализовано у меня:

Создание макроса для варианта когда критерий зависит от своего критерия

Диаграммы режимов ПТ типа ПТ-80

Диаграммы режимов типа Т-250

Нормативной температуры сетевого подогревателя.

Все вышеперечисленные сложные диаграммы можно разбить на простые, и сделать в ручном режиме с помощью тех программ создания макросов что я дал. А можно потратить пару вечеров и создать удобный инструмент под свои задачи.

Из того на что стоит обратить внимание, или маленькие лайфхаки:

1. Не всегда есть разметка осей. Например на диаграмме на последнем скрине вертикальная ось не размечена. Но она в данном случае не нужно. Важно иметь одинаковое значение для левого и правого графиков. Как правило я принимаю в качестве минимального значения оси — 0, в качестве максимального — число клеток (например 12-ть).

2. Внимание! Ось Х не обязательно горизонтальная при «снятии точек»! Например на диаграмме на последнем скрине для правого графика удобно взять в качестве оси Х вертикальную ось а в качестве Y — горизонтальную. Тогда результат обработки левой номограммы будет сразу выступать в качестве аргумента для правой номограммы.

3. Есть варианты оцифровки, когда лучше привязываться не к значениям осей, а к клеточкам 🙂 Да, звучит дико, но иногда проще внести пересчёт внутри макроса, чем реализовать оцифровку по данным осей. Например диаграмма ниже — обратите внимание, что вертикальная ось не обозначена, зато горизонтальная в левой диаграмме разбита на 3 участка с разным масштабом.

Упд. Вспомнил ещё про важную часть — обратные функции. Т.е. есть макрос (готовый!), который по известным Х1, Х2. находит Y. Иногда требуется с использованием данного макроса и известных Y и X1 найти X2. Но об этом в следующий раз. А то и так пост разросся.

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция⁠ ⁠

Ну теперь пора перейти именно к интерполяции исходных данных. Итак, я напомню — у нас был лист с распечатанным графиком, мы его отсканировали, получили набор точек ХY и. имеем вот такую (в лучшем случае) картину (см.первый скрин). Т.е. по данному набору точек невозможно сделать корректную апроксимацию полиномом.

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

Выходом из данной ситуации является разбиение данных на несколько частей, в данном случае 2 с общей точкой Do=428, создание кусучнозаданной функции (ЕСЛИ меньше 428 одна функция, если больше — вторая функция). Но так в данном случае, а если надо сделать два, три. и больше разбиений? Кропотливая работа. Но зачем, если можно заставить Excel в автоматическом режиме выбирать малое количество точек и проводить через них интерполяционную функцию.

Отчасти кусочную интерполяцию показывал в прошлом посте серии ( Excel. Долгая дорога оцифровки. Часть 5. Создание пользовательской функции для двух аргументов. Ручной вариант ) при поиске решения между заданных критериев.

Как простые варианты рассмотрим кусочную интерполяцию по двум и по четырём точкам для заданных ниже данных.

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

Допустим нужно определить значение Y при X = 2.5.

При кусочной интерполяции по двум точкам используются две ближайшие заданные точки к X = 2.5. , т.е. 2 и 3, через данные точки провидится линия,и по ней находится Y при X = 2.5..

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

При кусочной интерполяции по четырём точкам используются две ближайшие заданные точки к X = 2.5. справа и две две ближайшие заданные точки к X = 2.5. слева, т.е. 1 и 2 и 3 и 4, через данные точки провидится кривая (полином 3-й степени),и по ней находится Y при X = 2.5.

Это справедливо для данных за 2-й и перед предпоследней известной точкой. Для данных отрезков и для экстраполяции использую линейную интерполяцию (по 2-м точкам).

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

Как видно использование кусочной интерполяции по 4-м точкам (голубая линия)немного сглаживает итоговую функцию, что позволяет снимать при оцифровке чуть меньше точек 🙂 .

Вообще правило такое, в зависимости от вида графика:

Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция Microsoft Excel, Vba, Плюшка, Длиннопост

Ну и собственно с помощью чего сие выполняется:

Макрос кусочной интерполяции при использовании данных с листа

‘ Интерполяция по 2-м, 3-м или 4-м ближайшим (до и после) к заданной (Xisk) точке

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

‘ Интерполяция происходит косочно, по количеству заданных точек с учётом расположения заданного Х

‘ Данные из листа Excel.

Public Function kus_interp_Ex(Xt As Range, Yt As Range, Xisk As Single, Optional ByVal toch As Integer = 2) As Variant

Dim i As Long

Dim xd() As Double

Dim yd() As Double

Dim cd() As Double

‘ toch — указание поиска решения с использованием количества точек (2, 3, 4).

Select Case toch

Case 2 ‘ Уравнение а·х+b

kus_interp_Ex = linterp(Xt.Rows(Xt.Count — 1), Xt.Rows(Xt.Count), Yt.Rows(Xt.Count — 1), Yt.Rows(Xt.Count), Xisk)

For i = 1 To Xt.Count — 1

If Xisk < Xt.Rows(i + 1) Then

kus_interp_Ex = linterp(Xt.Rows(i), Xt.Rows(i + 1), Yt.Rows(i), Yt.Rows(i + 1), Xisk)

Case 3 ‘ Уравнение а·х^2+b·x+c Интерполяция по принципу х1 Х х2 х3

kus_interp_Ex = kubterp(Xt.Rows(Xt.Count — 2), Xt.Rows(Xt.Count — 1), Xt.Rows(Xt.Count), _

Yt.Rows(Xt.Count — 2), Yt.Rows(Xt.Count — 1), Yt.Rows(Xt.Count), Xisk)

For i = 1 To Xt.Count — 2

If Xisk < Xt.Rows(i + 1) Then

kus_interp_Ex = kubterp(Xt.Rows(i), Xt.Rows(i + 1), Xt.Rows(i + 2), _

Yt.Rows(i), Yt.Rows(i + 1), Yt.Rows(i + 2), Xisk)

Case 4 ‘ Уравнение а·х^3+b·x^2+c·x+d Интерполяция по принципу х1 х2 X х3 x4

ReDim xd(1 To 4) As Double

ReDim yd(1 To 4) As Double

If Xisk < Xt.Rows(2) Then ‘ Экстраполяция ДО и интерполяция ДО второй известной точки — линейна

kus_interp_Ex = linterp(Xt.Rows(1), Xt.Rows(2), Yt.Rows(1), Yt.Rows(2), Xisk)

If Xisk >= Xt.Rows(Xt.Count — 1) Then ‘ Экстраполяция ЗА и интерполяция ПОСЛЕ второй известной точки — линейна

kus_interp_Ex = linterp(Xt.Rows(Xt.Count — 1), Xt.Rows(Xt.Count), Yt.Rows(Xt.Count — 1), Yt.Rows(Xt.Count), Xisk)

Else ‘ Между ними считаю по интерполяции полиномом с расположением заданного икса между двух пар точек

For i = 3 To Xt.Count — 1

If Xisk < Xt.Rows(i) Then

xd(1) = Xt.Rows(i — 2): xd(2) = Xt.Rows(i — 1): xd(3) = Xt.Rows(i): xd(4) = Xt.Rows(i + 1)

yd(1) = Yt.Rows(i — 2): yd(2) = Yt.Rows(i — 1): yd(3) = Yt.Rows(i): yd(4) = Yt.Rows(i + 1)

Linia_trenda yd, xd, 3, cd

kus_interp_Ex = cd(1) * Xisk ^ 3 + cd(2) * Xisk ^ 2 + cd(3) * Xisk ^ 1 + cd(4)

End Function

Xt — Столбец исходных Х

Yt — Столбец исходных Y

Xisk — X при котором требуется определить Y

toch — количество используемых точек при интерполяции.

Наблюдательный заметит, что в макросе присутствует и интерполяция с использованием 3-х точек. (0_о)

Дополнительная функция, требуемая для макроса кусочной интерполяции — определение коэффициентов полинома линии тренда:

‘ Проведение интерполяции с использованием функционала Excel

‘ На выходе — коэффициенты полинома. Число точек должно быть минимум на одну больше, чем степень полинома.

‘ Данные берутся из программы

Public Sub Linia_trenda(ByRef Y() As Double, ByRef x() As Double, ByVal PolyStep As Integer, ByRef c() As Double, Optional ByRef r2 As Double)

Dim stepen As Long

‘ Ввожу проверку не превышения степени массива

If (UBound(Y) — LBound(Y) — 1) < PolyStep Then

stepen = UBound(Y) — LBound(Y)

stepen = PolyStep

‘ Объявляю переменные, создаю матрицы под размер данных и степень полинома.

Dim X1() As Double, Y1() As Double

ReDim X1(LBound(Y) To UBound(Y), 1 To stepen) As Double

ReDim Y1(LBound(Y) To UBound(Y), 1 To 1) As Double

ReDim c(1 To stepen + 1) As Double

‘ Заполню массив Х в соответствии со степенью уравнения.

For i = LBound(x) To UBound(x)

For N = 2 To stepen

X1(i, N) = X1(i, 1) ^ N

‘ Нахожу уравнение.

Dim Coefs As Variant

Coefs = WorksheetFunction.LinEst(Y1, X1, True, True)

‘ Вытаскиваю коэффициенты полинома.

For i = 1 To stepen + 1

c(i) = Coefs(1, i)

‘ Вытаскиваю величину достоверности апроксимации.

r2 = Coefs(3, 1)

Макрос linterp был представлен в прошлый раз.

Одним из замечательных применений кусочной интерполяции является возможность автоматического создания макросов функций без проблем с невозможностью достоверной апроксимации исходных данных вот в таком виде:

‘ Поправки Сербия Панчево Страница 39 из 77

Public Function ТЭХ_ПТ80_Рис3(ByRef Go As Single) As Variant

Dim Xt As Variant

Dim Yt As Variant

Xt = Array(13.0042194092827, 13.4767932489451, 14.0675105485232)

Yt = Array(-6.38888888888889E-02, -6.38888888888889E-02, -0.06875)

ТЭХ_ПТ80_Рис3 = kus_interp(Xt, Yt, Go, 4, 2)

End Function

Учтите что kus_interp при использовании данных с листа и из макроса отличаются.

Но об этом в следующий раз.

Планы на будущее

1. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции.

2. Построим поиск решения.

3. Строим график функции.

Базы данных в Excel. Или ВПР по неограниченному количеству условий⁠ ⁠

Довольно часто я встречаю решения выполнения поиска в таблице по двум/трём условиям с применением ВПР/ГПР. Подчас решения очень интересные. Однако что ВПР, что ГПР выдают только одно значение, и решения не масштабируемы. А что делать если необходимо из массива выбрать всю строку, которая будет подчиняться набору критериев?

Для этого служит встроенный в Excel механизм работы с таблицами как с базами данных. К сожалению им мало кто пользуется, но он очень прост в освоении. В этой теме попробую про него рассказать.

Итак, есть некая таблица и необходимо из неё выбрать все строки подчиняющиеся неким условиям.

Ход поиска однотипен:

1. Столбцы исходной таблицы имеют уникальные заголовки.

2. Для выборки из данной таблицы создаём таблицу условий. При этом заголовок таблицы условий должен совпадать с названием столбца из исходной таблицы, по которому будет проходить выборка. Если выборка по одному столбцу, но по нескольким условиям, то можно написать всё в один столбец (см.скрин ниже). Если условия по нескольким столбцам — по одному условию на столбец, наименования столбцов могут повторяться.

3. Затем переходим в вкладку «Данные» — «Сортировка и фильтр» — «Дополнительно». В открывшемся окне

3.1. отмечаем «скопировать результат в новое место». В этом случае исходная таблица не изменится.

3.2. Исходный диапазон — таблица исходная, вместе с шапкой

3.3. Диапазон условий — таблица условий, вместе с шапкой

3.4. Адрес ячейки с которой будет заполняться итоговая таблица согласно выборки.

Итоговая таблица при этом никак не связана с исходной. И с ней можно проводить любые операции.

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

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

Sub Выборка()

Range(«B3:B37»).AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range(«I3:I6»), _

CopyToRange:=Range(«K3»), _

Unique:=False

Range(«B3:B37») — исходная таблица

Range(«I3:I6») — таблица критериев

Range(«K3») — начало вывода результирующей таблицы

Соответственно можно повесить выполнением макроса на кнопку, менять условия и получать новые выборки.

Вызов функции выборки:

AdvancedFilter (Действие, CriteriaRange, CopyToRange, Уникальный)

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Причём работа с таблицами как с базами на этом не ограничивается, в частности она позволяет выполнять быстрые расчёты с учётом критериев. Например находить минимальные, максимальные и средние значения только для строк удовлетворяющих критериям. Заметьте — проблемы с числом критериев нет.

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Например, если я хочу просуммировать все значения из столбца «Продажи» с учётом того, что они проводились в марте, и контактов было от 35. 36, то я создаю таблицу критериев (см.скрин ниже) и в любой ячейке листа формулу =БДСУММ(B6:G21;F6;B2:В3)

B6:G21 — Диапазон исходной таблицы, включая заголовок;

F6 — по какому столбцу буду суммировать;

B2:D3 — при каких условиях суммировать.

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Да, сейчас это можно заменить на СУММЕСЛИ, но если будет больше условий? А если суммируется при сложных условиях содержания ячеек?

Насколько способ с использованием баз нагляднее, не так ли?

Варианты задания условий при этом поражают разнообразием. Не всегда можно сходу подобрать условия для СУММЕСЛИ, а тут это просто содержание одной ячейки.

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Ну и естественно умные таблицы используются без проблем

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

Рассмотрим ещё один метод поиска значения по 2-м, 3-м и более критериям без применение ВПР.

Для сокращения пишу для умной таблицы. К тому же она масштабируема, так что добавление участника не приведёт к необходимости корректировки формулы.

Допустим есть таблица исходных данных (Таблица1), в корой представлены список сотрудников (столбец Name) с датой их приёма на работу (Start) увольнения с должности (Stop) и названием должности (Role). При этом если человек принят, но ещё не уволился, то его ячейка Stop пустая. Нужно найти должность человека (F3) на какую то дату (F4). Попробуйте это через ВПР прописать ради интереса. А вот так делается без ВПР:

Базы данных в Excel. Или ВПР по неограниченному количеству условий Microsoft Excel, Vba, Плюшка, Длиннопост

В ячейке F6 собственно формула поиска.

=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(Таблица1[Name]=F3)/(Таблица1[Start]<=F4)/((Таблица1[Stop]=»»)+(Таблица1[Stop]>=F4));Таблица1[Role]);»нет данных»)

Разберём структуру поиска и задания условий:

Таблица1[Name]=F3 — совпадение имени

Таблица1[Start]<=F4 — дата зачисления на работу меньше даты поиска

(Таблица1[Stop]=»»)+(Таблица1[Stop]>=F4) — дата поиска или меньше даты увольнения или дата увольнения пустая.

Таблица1[Role] — вывод ячейки для которой все три условия истина.

Как видно формула легко масштабируется под любое количество условий.

Ну вот как то так.

Этим постом я хотел показать, что применение ВПР(ГПР) не всегда оправдано (хотя несомненно знание этих функций обязательно), и есть более простые и лёгкие способы.

Excel. Долгая дорога оцифровки. Часть 4. Макрос по созданию макросов апроксимации простых графиков полиномом⁠ ⁠

«Позабыты хлопоты, остановлен бег, Вкалывают роботы, счастлив человек!»(с)ПЭ

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

Всё базируется на трёх китах:

2. Результатом действия макроса может являться текст;

3. В текстовых переменных можно использовать спец символы:

3.1. Знак возврата каретки. vbCr она же символ Chr(13);

3.2. Знак перевода строки. vbLf она же символ Chr(10);

3.3. Символ объёдинения &.

Excel. Долгая дорога оцифровки. Часть 4. Макрос по созданию макросов апроксимации простых графиков полиномом Microsoft Excel, Vba, Прост, Длиннопост

Ну а теперь пройдём все шаги вместе.

В прошлом посте я говорил про макрос расчёта на основании построения тренда.

‘ Апроксимация полиномом для всего массива исходных данных

‘ В подпрограмму передаются все заданные точки и апроксимация ведётся по всем точкам!

‘ Данные из листа Excel

Public Function polinomEx_all(xVal As Range, yVal As Range, x As Single, Optional stepen As Long = 2) As Variant

Dim i As Integer

‘ Проверка требования «число элементов массива на 1 больше чем степень полинома»

If xVal.Count < stepen + 1 Then

stepen = xVal.Count — 1

polinomEx_all = 0#

Select Case stepen

Case 1 ‘ Уравнение а·х+b

For i = 1 To stepen + 1

polinomEx_all = polinomEx_all + (x ^ (stepen + 1 — i)) * Application.Index(WorksheetFunction.LinEst(yVal, xVal, True, True), 1, i)

Case 2 ‘ Уравнение а·х^2+b·x+c

For i = 1 To stepen + 1

polinomEx_all = polinomEx_all + _

(x ^ (stepen + 1 — i)) * Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2)), True, True), 1, i)

Case 3 ‘ Уравнение а·х^3+b·x^2+c·x+d

For i = 1 To stepen + 1

polinomEx_all = polinomEx_all + _

(x ^ (stepen + 1 — i)) * Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3)), True, True), 1, i)

For i = 1 To stepen + 1

polinomEx_all = polinomEx_all + _

(x ^ (stepen + 1 — i)) * Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4)), True, True), 1, i)

For i = 1 To stepen + 1

polinomEx_all = polinomEx_all + _

(x ^ (stepen + 1 — i)) * Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4, 5)), True, True), 1, i)

For i = 1 To stepen + 1

polinomEx_all = polinomEx_all + _

(x ^ (stepen + 1 — i)) * Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4, 5, 6)), True, True), 1, i)

For i = 1 To stepen + 1

polinomEx_all = polinomEx_all + _

(x ^ (stepen + 1 — i)) * Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4, 5, 6, 7)), True, True), 1, i)

End Function

Как видно — ничего сложного в этом макросе нет. В соответствии с заявленной степенью полинома происходит перемножение заданного Х в соответствующей степени на соответствующий коэффициент полинома. Коэф-ты вычисляются точно так же как вычислялись на листе экселя.

WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4)), True, True)

полностью совпадает с

ЛИНЕЙН(Y; X<1;2;3;4>; True; True)

Ну а теперь просто заменим расчёт на составление текстовой переменной

‘ Апроксимация полиномом для всего массива исходных данных

‘ В подпрограмму передаются все заданные точки и апроксимация ведётся по всем точкам!

‘ Данные из листа Excel

‘ Результат работы программы — текст (уравнение полинома)

Public Function polinomExStr(ByVal xVal As Range, ByVal yVal As Range, Optional stepen As Long = 2) As Variant

‘ Проверка требования «число элементов массива на 1 больше чем степень полинома»

Dim i As Integer

If xVal.Count < stepen + 1 Then

stepen = xVal.Count — 1

polinomExStr = «»

Select Case stepen

Case 1 ‘ Уравнение а·x+c

For i = 1 To 2

polinomExStr = polinomExStr & » + X ^ » & (2 — i) & » * » _

& Format(Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1)), True, True), 1, i), «0.###E+»)

Case 2 ‘ Уравнение а·х^2+b·x+c

For i = 1 To 3

polinomExStr = polinomExStr & » + X ^ » & (3 — i) & » * » _

& Format(Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2)), True, True), 1, i), «0.###E+»)

Case 3 ‘ Уравнение а·х^3+b·x^2+c·x+d

For i = 1 To 4

polinomExStr = polinomExStr & » + X ^ » & (4 — i) & » * » _

& Format(Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3)), True, True), 1, i), «0.###E+»)

For i = 1 To 5

polinomExStr = polinomExStr & » + X ^ » & (5 — i) & » * » _

& Format(Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4)), True, True), 1, i), «0.###E+»)

For i = 1 To 6

polinomExStr = polinomExStr & » + X ^ » & (6 — i) & » * » _

& Format(Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4, 5)), True, True), 1, i), «0.###E+»)

For i = 1 To 7

polinomExStr = polinomExStr & » + X ^ » & (7 — i) & » * » _

& Format(Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4, 5, 6)), True, True), 1, i), «0.###E+»)

For i = 1 To 8

polinomExStr = polinomExStr & » + X ^ » & (8 — i) & » * » _

& Format(Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Array(1, 2, 3, 4, 5, 6, 7)), True, True), 1, i), «0.###E+»)

End Function

Ну или немного в другом виде с учётом ряда особенностей и модификаций

‘ Программа формирования текста макроса для функции одного уравнения

Public Function fun_macros_Y(xVal As Range, yVal As Range, PolyStep As Long, _

Optional Name_f As String = «Nomogramma», _

Optional Opisanie As String = » Уравнение «, _

Optional NameX As String = «Xisk») As Variant

Dim j As Long

Dim N As Long

Dim k As Long

Dim stepen As Long

Dim xn() As Double ‘ заявляем массив X

Dim yn() As Double ‘ заявляем массив Y

Dim c() As Double ‘ заявляем массив c — коэффециенты уравнения полинома

fun_macros_Y = «» & Chr(10) & «‘ » & Opisanie & Chr(10)

fun_macros_Y = fun_macros_Y & «Public Function » & Name_f & «(ByRef » & NameX & » As Single) As Variant» & Chr(10)

Dim Nna4 As Long ‘Номер начала диапазона.

Dim Nkon As Long ‘Номер конца диапазона.

Nkon = xVal.Count

‘ Проверяем на соответствие число элементов участка степени полинома

If (Nkon — Nna4) < PolyStep Then

stepen = (Nkon — Nna4)

stepen = PolyStep

‘ Заполняем матрицы участка

ReDim xn(1 To (Nkon — Nna4 + 1), 1 To stepen)

ReDim yn(1 To (Nkon — Nna4 + 1), 1 To 1)

ReDim c(1 To stepen + 1) As Double

For j = 1 To (Nkon — Nna4 + 1)

xn(j, 1) = xVal.Rows(j + Nna4 — 1)

For N = 2 To stepen

xn(j, N) = xn(j, 1) ^ N

yn(j, 1) = yVal.Rows(j + Nna4 — 1)

‘ Делаем расчёт и вывод.

fun_macros_Y = fun_macros_Y & Name_f & » rv-related»>For k = 1 To stepen + 1 Step 1

c(k) = Format(Application.Index(WorksheetFunction.LinEst(yn, xn, True, True), 1, k), «0.####E+»)

If c(k) >= 0 And k > 1 Then

fun_macros_Y = fun_macros_Y & » + » & c(k)

fun_macros_Y = fun_macros_Y & c(k)

If (stepen + 1 — k) > 0 Then

fun_macros_Y = fun_macros_Y & » * » & NameX & » ^ » & (stepen + 1 — k) & » «

fun_macros_Y = fun_macros_Y & Chr(10) & «End Function» & Chr(10)

End Function

Макрос ждёт в качестве вводных данных:

xVal — столбец известных Х

yVal — столбец известных Y

PolyStep — желаемую степень уравнения. Если точек будет меньше чем требуется для степени — на уменьшится

Name_f — название получаемого макроса. Опционально. Если не задать будет Nomogramma

Opisanie — описание получаемого макроса. Опционально. Если не задать будет Уравнение

NameX — название/имя аргумента. Опционально. Если не задать будет Xisk

=ПОДСТАВИТЬ(fun_macros_Y(X; Y; 3; «fun_пример»; «Пример создания макроса»; «Go»);»,»;».»)

=ПОДСТАВИТЬ( ;»,»;».») требуется для замены запятых на точки. Иначе будет казус — VBA в качестве разделителя целой и дробной части использует точку, а в текстом виде (по крайней мере в рус.экселе) разделитель запятая.

Обратите внимание, что

«fun_пример»; «Пример создания макроса»; «Go» — текстовые, т.е. заключаются в кавычки

«fun_пример»; «Go» — должны соответствовать требованиям к переменным. Т.е. не должны содержать пробелов, не должны совпадать с имеющимися переменными или названиями ячеек/диапазонов.

Результатом выполнения макроса будет (поставил 3-ю степень чтобы результат влез в окно поста):

‘ Пример создания простого макроса

Public Function fun_Wтф(ByRef Go As Single) As Variant

fun_Wтф = 0.00000056401 * Go ^ 3 -0.001952 * Go ^ 2 + 1.3842 * Go ^ 1 + 25.341

End Function

Останется скопировать данный текст в модуль VBA и удалить двойные кавычки в начале и конце текстовки.

Если есть желание повысить количество знаков коэффициентов — правим формат «0.####E+»

Для ускорения работы у меня собраны листы/шаблоны позволяющие не лезть в заполнение вызова макросов.

Вызов макроса для данного случая у меня выглядит так (в Е9):

=ПОДСТАВИТЬ(fun_macros_Y(B3:ДВССЫЛ(«B»&E4);C3:ДВССЫЛ(«C»&E4);M7;E7;G5;G7);»,»;».»)

Как заполнены дополнительные столбцы А, D и ячейки Е4 и т.д. видно на скрине.

Столбец А — контроль верности снятия данных (по возрастанию Х).

Столбец D — подсчёт снятых точек.

В итоге выполнение/изготовление макроса для меня сводится в вставке исходных данных начиная с ячейки В3, и затем жёлтых полей ввода описания, ввода названия новой функции и аргумента, выбора степени. Т.е. занимает не более минуты.

Для наблюдательных — присутствующие в макросе Dim Nna4 As Long ‘Номер начала диапазона.

Dim Nkon As Long ‘Номер конца диапазона.

намекают на то, что после небольшой модификации данный макрос можно использовать для более сложных диаграмм. Но об этом позднее. Думаю что через неделю в лучшем случае.

Для продвинутых — да, можно обойтись без доп.столбцов А и D, да и E4 лишнее. И то и другое можно реализовать в макросе, но. но данный лист был так сформирован на основании удобства для меня — могу оперативно проверить правильность и полноту вставки исходных данных, отсутствие сбоев «снятия» точек с картинки при массовой оцифровке. И вообще — «работает? Стабильно? Без сбоев? Не трожь!» (с)Анекдот. Вам ничто не мешает сделать иначе.

Теория вкратце [ Часть 1. ]

Забираем данные с листа. [ Часть 2. ]

Апроксимация простых графиков полиномом средствами Excel [ Часть 3.]

Макрос по созданию макросов апроксимации простых графиков полиномом [ Часть 4.] Этот пост

Апроксимация графиков двух аргументов полиномом [ Часть 5.]

Кусочная интерполяция простых графиков [ Часть 6.]

Excel. Долгая дорога оцифровки. Часть 2. Забираем данные с листа⁠ ⁠

Под оцифровкой графиков будем понимать получение интерполяционной/аппроксимирующей функции (макроса), при вводе данных в которую будет получено значение, соответствующее значению, полученному с использованием графического метода (линейка и карандаш) на бумажном варианте графика-рисунка.

Первым этапом оцифровки будет получение набора данных XY соответствующих имеющейся кривой.

Некоторые РД (документация заводов-изготовителей, НТД, ТЭХ, ТНХ и т.д) предоставляют функции/уравнения, соответствующие графикам/номограммам. Однако стоит знать, что иногда предоставленная функция может не соответствовать предоставленному графику и требует проверки.

До проведения «снятия» точек требуется обработать отсканированный (если он таковой) график. А именно:

— максимально уменьшить искажения типа «горб» или «рыбий глаз. Т.е. сделать все вертикальные и горизонтальные линии параллельными между собой соответственно.

— максимально сделать перпендикулярными вертикальную и горизонтальную оси.

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

В далёком прошлом помню варианты «снятия» точек путём вставки картинки в автокад, простановки точек, их переписывания и пересчёта. Сейчас же при отсутствии функций-зависимостей следует воспользоваться программой Graph2Digit, GetData Graph Digitizer или аналогичной. GetData Graph Digitizer продаётся за денежку, но т.к. я начинал работать с ней, она установлена у меня на работе (а у нас нельзя самому ставить софт), то показывать буду на ней. Но тут скорее дело привычки, и пары удобств, которых не помню в Graph2Digit — типа возможности снятия точек с нескольких линий.

При этом автоматизированность снятия точек хоть и заявлена для обеих программ, но на деле ни одна программа с этим не справляется на реальных диаграммах — требуется идеальный вариант с чётким выделением линий графиков по цветам, отсутствием сеток и наличия бубна. Фактически для реальных графиков «снятие» точек производится в ручном режиме.

Для желающих — выбираем язык программы.

1. Установить оси. Потребуется установить точки минимального Х, максимального Х, минимального У, максимального У. При этом, как видно на скриншоте выше, достаточно установить точки там, где заведомо известны значения. Например максимальный Х задан как 400, хотя график продолжается и далее — значение по осям будут пересчитаны соответственно.

2. Ручная простановка точек. В ручном режиме наводится на место снятия точки, при этом в увеличенном виде это место отображается справа в нижнем углу, и ЛКМ фиксируем точку. Все снятые точки отображаются в таблице справа.

3. Копирование снятых точек в буфер обмена. В дальнейшем «снятые» точки копируются в лист Excel, и с ними производятся различные манипуляции.

Это был описаны основные действия при ручном снятии.

Насколько часто требуется производить «снятие» точек? Вопрос сложный. Но из опыта — линия, что связывает две проставленные точки, в идеале должна находиться внутри линии графика.

Что делать, если был не верно указан диапазон (например вместо Хмакс=1000 записали Хмакс=100)? Ничего страшного. Точки заново расставлять вдоль линии не надо, достаточно зайти в «Текущее состояние» — «Параметры» и задать правильное параметров осей.

— сохранять поле снятых точек. т.е. файл *.gdw. В дальнейшем, если обнаружится не верное отображение точек, легко проверить правильность указания осей. Дело в том, что если график один, то точки снимаются внимательно, и аккуратно. А когда ставится на поток, по 50 графиков в час, то бывают и плюхи.

— выставлять следующие настройки (в дальнейшем облегчат автоматическое получение макросов):

— немного забегая вперёд (постов эдак на 4). снятие точек производить от меньшего Х к большему. При наличии диаграммы зависимости от двух аргументов типаY(X1, X2) начиная с графика меньшего Х2.Т.е. см.диаграмму внизу — имеется зависимость qт(Nт,Qт). Снятие точек начинаем с Qт=0, Nт = 40. 120, затем с Qт=20, Nт = 40. 120 и т.д. С обязательным условием — каждая следующая линия должна начинаться с Х меньшего, чем закончилась предыдущая. Может немного сумбурно, но если понятней по видео, то вот — https://youtu.be/AXnTdHlmn34

— Нужно помнить, что погрешность оцифровки составляет толщину линии, и для вертикальных участков кривых может достигать 2%. Дополнительная погрешность вызывается искривлением графика при печати/сканировании. Поэтому точки, полученные в результате такого снятия можно, и подчас нужно, редактировать. Например, если Вы точно знаете что при Х=10 Y должен ровняться 20, а по снятым данным выходит 19.95, то или производится правка данной точки (заменяется на 20), или все точки поднимаются на 0,05 (+0,05).

При этом точность определения значений с помощью линейки и карандаша заведомо ниже, чем при использовании интерполированных кривых.

Забираем данные с листа. [ Часть 2. ] Этот пост

Апроксимация простых графиков полиномом средствами Excel [ Часть 3.]

Макрос по созданию макросов апроксимации простых графиков полиномом [ Часть 4.]

Похожие публикации:

Как перезапустить агент сервера 1с

Как скопировать файл python

Как установить flask python на windows

Bluetooth suite что это за программа

Как построить полином в excel