Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически

Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически

Вопрос от пользователя

Здравствуйте!

У меня есть одна задачка, и уже третий день ломаю голову — не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .

Заранее благодарю. Андрей, Москва.

Доброго дня всем!

То, что вы описали — относится к довольно популярным задачам, которые относительно просто и быстро решать с помощью Excel. Достаточно загнать в программу две ваши таблицы, и воспользоваться функцией ВПР . О ее работе ниже.

Пример работы с функцией ВПР

В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.

Две таблицы в Excel — сравниваем первые столбцы

Как это сделать.

Ставим указатель мышки в ячейку B2 — то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:

=ВПР( A2 ; $E$1:$F$7 ; 2 ; ЛОЖЬ )

где:

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

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

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

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

Растягиваем формулу (копируем формулу в другие ячейки)

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

Значения из одной таблицы подставлены в другую

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

Линейная регрессия в Excel

Линейная регрессия — это статистический метод / метод, используемый для изучения взаимосвязи между двумя непрерывными количественными переменными. В этом методе независимые переменные используются для прогнозирования значения зависимой переменной. Если существует только одна независимая переменная, то это простая линейная регрессия, а если число независимых переменных больше, чем одна, то это множественная линейная регрессия. Модели линейной регрессии имеют связь между зависимыми и независимыми переменными путем подгонки линейного уравнения к наблюдаемым данным. Линейный относится к тому факту, что мы используем линию, чтобы соответствовать нашим данным. Зависимые переменные, используемые в регрессионном анализе, также называют ответными или прогнозными переменными, а независимые переменные также называют объясняющими переменными или предикторами.

Линия линейной регрессии имеет уравнение вида: Y = a + bX;

• X — объясняющая переменная,
• Y является зависимой переменной,
• б — наклон линии,
• a является y-перехватом (то есть значением y, когда x = 0).

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

Методы использования линейной регрессии в Excel

В этом примере показано, как выполнить анализ линейной регрессии в Excel. Давайте посмотрим на несколько методов.

Вы можете скачать этот шаблон Excel с линейной регрессией здесь — Шаблон Excel с линейной регрессией

Метод № 1 — Точечная диаграмма с линией тренда

Допустим, у нас есть набор данных о некоторых людях с их возрастом, индексом биомассы (ИМТ) и суммой, потраченной ими на медицинские расходы за месяц. Теперь, имея представление о характеристиках людей, таких как возраст и ИМТ, мы хотим выяснить, как эти переменные влияют на медицинские расходы, и, следовательно, использовать их для проведения регрессии и оценки / прогнозирования средних медицинских расходов для некоторых конкретных людей. Давайте сначала посмотрим, как только возраст влияет на медицинские расходы. Давайте посмотрим на набор данных:

Сумма на медицинские расходы = б * возраст + а

• Выберите два столбца набора данных (x и y), включая заголовки.

• Нажмите «Вставить» и разверните раскрывающийся список «Диаграмма разброса» и выберите эскиз «Разброс» (первый)

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

• Теперь на панели «Format Trendline» справа выберите «Linear Trendline» и «Показать уравнение на графике».

• Выберите «Показать уравнение на графике».

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

После Импровизации диаграммы мы получаем вывод.

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

Метод № 2 — Анализ надстройки ToolPak Метод

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

• Нажмите на меню «Файл».

После этого нажмите «Опции».

• Выберите «Надстройки Excel» в поле «Управление» и нажмите «Перейти»

• Выберите «Пакет инструментов анализа» -> «ОК»

Это добавит инструменты «Анализ данных» на вкладку «Данные». Теперь запустим регрессионный анализ:

• Нажмите «Анализ данных» на вкладке «Данные»

• Выберите «Регрессия» -> «ОК».

• Откроется диалоговое окно регрессии. Выберите диапазон ввода Y и диапазон ввода X (медицинские расходы и возраст соответственно). В случае множественной линейной регрессии мы можем выбрать больше столбцов независимых переменных (например, если мы хотим увидеть влияние ИМТ также на медицинские расходы).
• Установите флажок «Метки», чтобы включить заголовки.
• Выберите желаемый вариант вывода.
• Установите флажок «Остатки» и нажмите «ОК».

Теперь результаты нашего регрессионного анализа будут созданы в новом рабочем листе с указанием статистики регрессии, ANOVA, остатков и коэффициентов.

Выходная интерпретация:

• Статистика регрессии показывает, насколько хорошо уравнение регрессии соответствует данным:

• Множество R — это коэффициент корреляции, который измеряет силу линейных отношений между двумя переменными. Он лежит в диапазоне от -1 до 1, и его абсолютное значение показывает силу отношения с большим значением, указывающим на более сильное отношение, низким значением, указывающим на отрицательное значение, и нулевым значением, указывающим на отсутствие отношения.
• Квадрат R — это коэффициент определения, используемый в качестве показателя качества соответствия. Он находится в диапазоне от 0 до 1, а значение, близкое к 1, указывает на то, что модель хорошо подходит. В этом случае 0, 57 = 57% значений y объясняются значениями x.
• Скорректированный квадрат R — это квадрат R, скорректированный на количество предикторов в случае множественной линейной регрессии.
• Стандартная ошибка отображает точность регрессионного анализа.
• Наблюдения отображают количество модельных наблюдений.
• Anova рассказывает об уровне изменчивости в рамках регрессионной модели.

Обычно это не используется для простой линейной регрессии. Однако «Значения F значимости» указывают на то, насколько надежны наши результаты, при этом значение больше 0, 05 предлагает выбрать другого предиктора.

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

Итак, наше уравнение регрессии будет: у = 16, 891 х — 355, 32. Это то же самое, что сделано методом 1 (точечная диаграмма с линией тренда).

Теперь, если мы хотим предсказать средние медицинские расходы в возрасте 72 лет:

Итак, у = 16, 891 * 72 -355, 32 = 860, 832

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

• Остатки указывают на разницу между фактическими и прогнозируемыми значениями.

Последний метод регрессии используется не так часто и требует статистических функций, таких как slope (), intercept (), correl () и т. Д. Для проведения регрессионного анализа.

Что нужно помнить о линейной регрессии в Excel

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

Рекомендуемые статьи

Это руководство по линейной регрессии в Excel. Здесь мы обсудим, как сделать линейную регрессию в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Как построить график в Excel

Предыдущей статье мы уже рассмотрели, как сделать диаграмму в Excel. Теперь настало время узнать, как построить график в Excel.

В первую очередь перед тем как построить график по таблице, нам нужно определиться какой тип графика мы будем использовать – график или точечную диаграмму. В случае если необходимо построить график по данным таблицы, используя значения как х, так и у, т.е. построить график x y в Excel, то в таком случае используется точечная диаграмма. Диаграмма График равномерно распределяет значения по оси х, т.е. при построении графика используются только значения у.

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

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

Разберем примеры данных типов.

Построить график в Excel по данным таблицы

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

Как построить график в Excel – Данные для построения графика

Выделяем всю таблицу. Заголовки столбцов будут использоваться для подписи данных. Переходим во вкладка « Вставка », в группе «Диаграммы» выбираем пункт «График».

Как построить график в Excel – График

В результате получаем график по данным таблицы:

Как построить график в Excel – График курса доллара за 2016 год

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

Построить график по точкам в Excel

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

Начнем с примера, в котором рассмотрим, как построить график квадратичной функции.

Создадим следующую таблицу:

Как построить график в Excel – Построение графика функции, исходные данные

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

Исходные данные готовы. Переходим к построению графика функции. Выделяем всю таблицу, переходим во вкладку « Вставка », в группе «Диаграммы» выбираем пункт « Точечная с гладкими кривыми и маркерами ».

Как построить график в Excel – Точечная диаграмма

Получаем следующий график функции:

Как построить график в Excel – Точечный график квадратичной функции

Вы также можете поработать с оформлением получившегося графика, применив к нему понравившийся стиль.

Как построить два графика в Excel

Перейдем к вопросу, как построить два графика в Excel. Добавим еще один график кубической параболы. Создадим в таблице еще один столбец (столбец С), в котором введем формулу кубической параболы. Теперь наша таблица выглядит следующим образом:

Как построить график в Excel – Расширение таблицы исходных данных

Теперь во вкладке « Конструктор » выбираем пункт « Выбрать данные ».

Как построить график в Excel – Выбрать данные

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

Как построить график в Excel – Выбор источника данных

Мы можем либо изменить « Диапазон данных для диаграммы », выбрав нашу обновленную таблицу; либо добавить еще один ряд с помощью кнопки « Добавить ». Мы воспользуемся вторым способом, и просто добавим еще один ряд.

В открывшемся окне в поле « Имя ряда » выбираем ячейку с заголовком столбца, в поле « Значения Х » – выбираем первый столбец таблицы, « Значения У » – третий. Нажимаем кнопку « ОК ».

Как построить график в Excel – Изменение ряда

Теперь в окне « Выбор источника данных » содержится два ряда:

Как построить график в Excel – Построить два графика

Нажимаем « ОК ». И на нашей диаграмме уже отображаются два графика.

Как построить график в Excel – Два графика на одной точечной диаграмме

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

Как построить график в Excel – Графики функций

Теперь вы знаете, как построить график функции в Excel. Давайте рассмотрим, как быстро оформить график, и как переместить график.

Редактирование графика

Если вы хотите изменить размещение графика, то дважды кликаем по графику, и в « КОНСТРУКТОРЕ » выбираем « Переместить диаграмму ».

Как построить график в Excel – Переместить диаграмму

В открывшемся диалоговом окне выбираем, где хотим разместить наш график.

Как построить график в Excel – Перемещение диаграммы

Мы можем разместить наш график на отдельном листе с указанным в поле названием, для этого выбираем пункт « на отдельном листе ».

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

Разместим график по данным таблицы на отдельном листе с названием « Курс доллара, 2016 год ».

Как построить график в Excel – Перемещение графика на отдельный лист

Теперь книга Excel содержит лист с графиком, который выглядит следующим образом:

Как построить график в Excel – График курса доллара на отдельном листе

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

Во вкладке « Конструктор » в группе « Стили диаграмм » находится коллекция стилей, которые можно применить к текущему графику.

Как построить график в Excel – Стили диаграмм

Для того чтобы применить понравившийся вам стиль достаточно просто щелкнуть по нему мышкой.

Как построить график в Excel – Коллекция стилей диаграмм

Теперь наш график полностью видоизменился.

Как построить график в Excel – График с оформлением

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

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

Создание модели линейной регрессии в Excel

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

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

Ключевые вынос

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

Важные соображения

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

1. Переменные должны быть действительно независимыми (используя критерий хи-квадрат).
2. Данные не должны иметь различные отклонения ошибок (это называется гетероскедастичностью (также пишется гетероскедастичность)).
3. Условия ошибки каждой переменной должны быть некоррелированными. Если нет, это означает, что переменные последовательно коррелируют.

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

Вывод регрессии в Excel

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

Используя Data Analysis ToolPak, для создания регрессионного вывода достаточно нескольких щелчков мышью.

Независимая переменная входит в диапазон X.

С учетом доходности S & P 500, скажем, мы хотим знать, можем ли мы оценить силу и соотношение доходности акций Visa (V). Запас Visa (V) возвращает данные, заполняет столбец 1 как зависимую переменную. S & P 500 возвращает данные, заполняющие столбец 2 как независимую переменную.

1. Выберите «Данные» на панели инструментов. Появится меню «Данные».
2. Выберите «Анализ данных». Откроется диалоговое окно «Анализ данных — Инструменты анализа».
3. В меню выберите «Регрессия» и нажмите «ОК».
4. В диалоговом окне «Регрессия» щелкните поле «Диапазон ввода Y» и выберите данные зависимой переменной (доходность Visa (V)).
5. Щелкните поле «Input X Range» и выберите данные независимых переменных (S & P 500 возвращает).
6. Нажмите «ОК» для запуска результатов.

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

Интерпретировать результаты

Используя эти данные (то же самое из нашей статьи R-квадрат), мы получаем следующую таблицу:

R 2 Значение, также известное как коэффициент детерминации, измеряет долю вариации в зависимой переменной, объясняемой независимой переменной, или насколько регрессионная модель соответствует данным. R 2 значение варьируется от 0 до 1, а более высокое значение указывает на лучшее соответствие. Значение p или значение вероятности также колеблется от 0 до 1 и указывает, является ли тест значимым. В отличие от R 2 значение, меньшее значение p является благоприятным, поскольку оно указывает на корреляцию между зависимой и независимой переменными.

Диаграмма регрессии в Excel

Мы можем построить регрессию в Excel, выделив данные и представив их в виде точечной диаграммы. Чтобы добавить линию регрессии, выберите «Макет» в меню «Инструменты диаграммы». В диалоговом окне выберите «Линия тренда», а затем «Линейная линия тренда». Добавить R 2 выберите «Дополнительные параметры трендовой линии» в меню «Трендовая линия». Наконец, выберите «Показать значение R в квадрате на графике». Визуальный результат суммирует силу взаимосвязи, хотя и за счет того, что не дает столько деталей, сколько таблица выше.

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

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

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

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

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

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

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

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

У нас имеется таблица продаж автомобилей за промежуток 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% находится в пределах допустимых значений ошибки <8…10%. Значит вычисления достаточно точны.

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