Пошук залежності між даними за допомогою графіків функцій. Лінія тренда

Є декілька базових функцій (лінійна, логарифм, експонента, поліноми,...). Якщо у нас є невідомо яка залежність, Excel може нам сказати, на яку базову функцію вона схожа більше всього.

Для цього поступимо наступним чином. Побудуємо графік цієї залежності (можна График, можна Точечная), скористаємося пунктом Меню => Диаграмма => Добавить линию тренда. Виберемо тип тренда. Excel побудує на діаграмі ще один графік, що показує, до якої залежності щонайближче досліджувані дані.

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

Для того, щоб зрозуміти, що за тренд побудований, треба вивести його рівняння (“Формат линии тренда”, вкладка “Параметры”, відповідна галочка). І отримаємо приблизне рівняння залежності між даними.

Для того, щоб зрозуміти, який тип тренда треба використовувати, на тій же вкладці “Параметры” вікна “Формат линии тренда”, включаємо галочку “Поместить на диаграмму величину достоверности аппроксимации (R^2)”. Після цього в підписі до тренда з'явиться ця сама величина достовірності. Чим вона більша, тим краще побудований тренд. (Див. приклад 5).

Лінія тренда - один з найпростіших, але досить ефективний спосіб дослідження різних залежностей в Excel.

Наприклад, дані статистичні дані про середню зарплату і про витрати на продукти харчування за декілька років. Можна дослідити, чи залежать витрати на харчування від середньої зарплати і як. По-перше, треба відзначити точки, відповідні даним (побудувати точкову діаграму). Якщо точки вийшли не хаотично розкидані по всій діаграмі, а більш-менш по якійсь лінії, можна спробувати побудувати тренд цієї залежності (додати лінію тренда: всіх типів по черзі і подивитися, при якому типі найбільший R²). При великому коефіцієнті R² (наприклад, R²> 0, 9) можна вважати, що ми знайшли відповідну залежність. Практично так поступають на практиці.

Те, що практичні дані відхиляються від теоретичної кривої, пояснюється таким чином. У реальному житті присутні багато випадкових факторів. Можливо, у населення була масова депресія напередодні виборів - ось і витрачали менше, ніж треба теоретично. А може бути, піднесення настрою з приводу виходу улюбленої команди у фінал - витрачали більше. Але в середньому залежність така, як вийшла теоретична крива (тренд).