Для подбора формулы с помощью функции ЛГРФПРИБЛ выделите диапазон ячеек F9:H13 и проделайте известные из предыдущего примера (п.5) действия

В итоге должен получиться массив:

В этом случае коэффициент детерминации R²=0.989 вполне удовлетворителен, и тогда искомая аппроксимирующая формула показательного типа (т.к. использована функция ЛГРФПРИБЛ) будет иметь вид:

Y = 0,44 * 0,46^х1 * 1,08^х2

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

Вычислите проценты для всех масс-медиа при затратах 2, 17 и 25 тыс.$. Подготовьте новые данные X в колонках K и L:

Поскольку стало известно, что зависимость нелинейная показательная, то для вычисления значений Y при новых значениях X можно использовать функцию РОСТ.

Справка: функция РОСТ лишь вычисляет значения аппроксимирующей функции в диапазоне наблюдения без предоставления ее характеристик.

Выделите диапазон ячеек M2:M10 и введите функцию РОСТ; установите параметры функции как на рисунке.

Результаты расчетов:

7. Решение задач регрессионного анализа с помощью пакета анализа

Пакет анализа – это надстройка для выполнения статистического анализа.

Если вы не найдете средство Пакет анализа в меню в стандартной конфигурации программы EXCEL, установите его: кнопка Office – Параметры Excel / Надстройки / Перейти… . В диалоговом окне Надстройки установите флажок Пакет анализа / ОК.

После этого в меню Данные появится новая команда Анализ данных. Эта команда предоставляет доступ к средствам анализа EXCEL.

Условия задачи и исходные данные остаются теми же, что и в п.6.

Для проведения регрессионного анализа выполните следующие действия:

1) выберите команду Данные / Анализ данных;

2) в диалоговом окне Анализ данных выберите инструмент Регрессия, а затем щелкните на кнопке ОК;

Рис. Диалоговое окно регрессия

3) в диалоговом окне Регрессия в поле “Входной интервал Y” введите адрес одного диапазона ячеек, который представляет зависимую переменную. В поле “Входной интервал X” введите адреса одного или нескольких диапазонов, которые содержат значения независимых переменных; 4) если выделены и заголовки столбцов, то установить флажок Метки в первой строке; 5) выберите параметры вывода: в данном примере – ячейка $F$3; 6) ОК.

Результаты регрессионного анализа

Во втором столбце таблицы содержатся коэффициенты уравнения регрессии а₀, а₁, а_2.

Уравнение регрессии зависимости величины Y от переменных X1 и индекса потребительских расходов, полученное с помощью EXCEL, имеет вид: .