Логарифмическое приближение

В логарифмической модели уравнение регрессии имеет следующий вид.

.

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

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

1. С Листа1 скопируйте данные вместе с диаграммой на Лист3.

2. Щелкните правой кнопкой мыши на линии тренда и в контекстном меню выберите команду Очистить.

3. На диаграмме рассеяния щелкните правой кнопкой мыши на любой точке данных (Ряд1) и в контекстном меню выберите команду Добавить линию тренда…

4. В диалоговом окне на вкладке Тип щелкните по пиктограмме Логарифмическая.

5. Откройте вкладку Параметры и в области Название аппроксимирующей (сглаженной) кривой выберите опцию автоматическое:. Включите опции показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Щелкните на кнопке ОК.

6. Текст с уравнением регрессии и значением R ² расположите под заголовком диаграммы.

Далее с помощью инструмента анализа Регрессия получим более полные оценки регрессии логарифмической модели.

7. Вставьте в книгу Лист4 и скопируйте в него данные с Листа1 (диапазон A1:B16) в такой же диапазон.

8. Выделите столбец B и из контекстного меню выберите команду Добавить ячейки.

9. В ячейку B1 введите метку Ln(Площадь). Увеличьте ширину столбца B, дважды щелкнув на правой границе его заголовка.

10. Выделите ячейку B2 и введите в нее формулу =LN(A2). В остальные ячейки столбца B скопируйте формулу, выделив ячейку B2 и дважды щелкнув по маркеру заполнения.

11. Выберите команду: Сервис®Анализ данных®Регрессия. В диалоговом окне Регрессия установите следующие параметры.

Ü Входной интервал Y: укажите диапазон значений зависимой переменной (C1:C16), включая метку в первой строке.

Ü Входной интервал X: укажите диапазон значений независимой переменной (B1:B16), включая метку в первой строке.

Ü Метки: включите эту опцию, так как во Входные интервалы X и Y были включены подписи.

Ü Параметры вывода: включите Выходной интервал, выберите текстовую строку и введите в нее ссылку на ячейку E1, указывающую левый верхний угол области вывода результатов. Щелкните на кнопке ОК.

12. Выделите диапазон столбцов E:M и увеличьте ширину столбцов, дважды щелкнув по правой границе в строке заголовков столбцов.

13. Удалите часть результатов, относящихся к дисперсионному анализу. Для этого выделите диапазон E10:M14 и выберите из контекстного меню команду Удалить… В диалоговом окне установите опцию ячейки, со сдвигом вверх. Щелкните на кнопке ОК.