Скопируйте исходные данные на новый лист в ячейки A1:D14

2. Выделите диапазон ячеек B17:E21 (рис.2) для сохранения результатов вычислений функции ЛИНЕЙН – массива регрессионной статистики.

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов.

3. Вызовите статистическую функцию ЛИНЕЙН и установите параметры, как на рис.1. Параметр Изв_знач_y содержит диапазон D2:D14, т.е. известные значения y. Параметр Изв_знач_х содержит диапазон A2:C14, т.е. известные значения х. Параметр Стат=1 – для получения дополнительной статистики.

Рис. 1

4. После нажатия ОК встаньте на строку формул (или нажмите F2) и нажмите Ctrl+Shift+Enter. В результате должен получиться массив значений, показанный на рис.2.

На рисунке искомые коэффициенты a₀, a₁, a₂, a₃ выделены (подробнее см. справку F1). Коэффициент детерминации R²=0.9725 вполне удовлетворителен (близок к единице).

Таким образом, аппроксимирующая формула y=a₀+a₁x₁+a₂x₂+a₃x₃ имеет вид:

Y = –19,27 + 1,36*х1 + 0,1*х2 – 0,21*х3

Рис. 2

5. С использованием полученной формулы вычислите теоретические значения y_теор и прогнозное значение функции y_прогн при х1 = 37, х2 = 11, х3 = 3, записав самостоятельно в любую ячейку формулу для расчета. Результат расчета: y_прогн= 31,63.

Для этой же задачи рассмотрите пример использования функции ТЕНДЕНЦИЯ для расчета значений y при других различных вариантах данных X.

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

Порядок решения задачи:

1. Новые значения Х, для которых надо рассчитать y, введите в ячейки F2:H14.

2. Выделите диапазон I2:I14 для записи в него рассчитываемых значений y.

3. Вызовите функцию ТЕНДЕНЦИЯ. Параметры функции заполните как на рис.3. Параметр Нов_знач_х содержит диапазон F2:H14, т.е. новые значения x.

Рис. 3

После нажатия ОК встаньте на строку формул (или нажмите клавишу F2) и нажмите Ctrl+Shift+Enter. В результате диапазон I2:I14 будет заполнен рассчитанными значениями y (рис.4).

Рис. 4

6. Оценка эффективности рекламы. Прогнозирование с использованием функций ЛГРФПРИБЛ и РОСТ

Экспериментально известны проценты увеличения оборота при затратах в 5, 10, 15, 20 тыс.$ в 3-х масс-медиа – на телевидении, радио и в прессе:

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

Кроме этого, надо вычислить процент увеличения оборота при затратах на рекламу в прессе 2 тыс.$ и на телевидении 22 тыс.$. Дополнительно вычислите проценты при затратах во всех масс-медиа 2, 17 и 25 тыс.$.

Решение задачи. Для решения задачи в первую очередь правильно разместите данные (рис.1).

Рис. 1

Вычислите массив с регрессионной статистикой функцией ЛИНЕЙН: выделите диапазон ячеек F2:H6 и проделайте известные из предыдущего примера (п.5) действия. В итоге должен получиться массив:

Видно, что коэффициент детерминации R²=0.8757 недостаточно удовлетворителен.

Поэтому выполните подбор формулы с помощью функции для нелинейных зависимостей ЛГРФПРИБЛ.

Справка: функции ЛГРФПРИБЛ и РОСТ применяют для аппроксимации экспериментальных данных нелинейные (показательные) зависимости вида

Функция ЛГРФПРИБЛ (как и функция ЛИНЕЙН) возвращает массив с вычисленными значениями параметров (a₀, a₁, a₂, …, a_n), коэффициентом детерминации R² и другими характеристиками функции.