Застосування методу найменших квадратів в Excel
Метод найменших квадратів є математичною процедуру побудови лінійного рівняння, яке б найбільш точно відповідало набору двох рядів чисел. Метою застосування даного способу є мінімізація загальної квадратичної помилки. У програмі Excel є інструменти, за допомогою яких можна застосовувати даний метод при обчисленнях. Давайте розберемося, як це робиться.
зміст
Використання методу в Ексель
Метод найменших квадратів (МНК) є математичним описом залежності однієї змінної від другої. Його можна використовувати при прогнозуванні.
Включення надбудови «Пошук рішення»
Для того, щоб використовувати МНК в Ексель, потрібно включити надбудову «Пошук рішення», яка за замовчуванням відключена.
- Переходимо у вкладку «Файл».
- Кількома по найменуванню розділу «Параметри».
- У вікні, зупиняємо вибір на підрозділі «Надбудови».
- У блоці «Управління», який розташований в нижній частині вікна, встановлюємо перемикач в позицію «Надбудови Excel» (якщо в ньому виставлено інше значення) і тиснемо на кнопку «Перейти ...».
- Відкривається невелике віконце. Ставимо в ньому галочку біля параметра «Пошук рішення». Тиснемо на кнопку «OK».
Тепер функція Пошук рішення в Excel активована, а її інструменти з'явилися на стрічці.
умови завдання
Опишемо застосування МНК на конкретному прикладі. Маємо два ряди чисел x і y, послідовність яких представлена на зображенні нижче.
Найбільш точно дану залежність може описати функція:
y=a+nx
При цьому, відомо що при x = 0 y теж дорівнює 0. Тому дане рівняння можна описати залежністю y = nx.
Нам належить знайти мінімальну суму квадратів різниці.
Рішення
Перейдемо до опису безпосереднього застосування методу.
- Зліва від першого значення x ставимо цифру 1. Це буде наближена величина першого значення коефіцієнта n.
- Праворуч від стовпчика y додаємо ще одну колонку - nx. У перший осередок даного стовпця записуємо формулу множення коефіцієнта n на осередок першої змінної x. При цьому, посилання на поле з коефіцієнтом робимо абсолютної , Так як це значення мінятися не буде. Кількома по кнопці Enter.
- використовуючи маркер заповнення , Копіюємо цю формулу на весь діапазон таблиці в стовпці нижче.
- В окремій клітинці вираховуємо суму різниць квадратів значень y і nx. Для цього натискаємо на кнопку «Вставити функцію».
- У відкритому «Майстрі функцій» шукаємо запис «СУММКВРАЗН». Вибираємо її і тиснемо на кнопку «OK».
- Відкривається вікно аргументів. В поле «Массів_x» вводимо діапазон комірок стовпчика y. В поле «Массів_y» вводимо діапазон комірок стовпчика nx. Для того, щоб ввести значення, просто встановлюємо курсор в поле і виділяємо відповідний діапазон на аркуші. Після введення тиснемо на кнопку «OK».
- Переходимо у вкладку «Дані». На стрічці в блоці інструментів «Аналіз» тиснемо на кнопку «Пошук рішення».
- Відкривається вікно параметрів даного інструменту. В поле «Оптимізувати цільову функцію» вказуємо адресу осередку з формулою «СУММКВРАЗН». У параметрі «До» обов'язково виставляємо перемикач в позицію «Мінімум». В поле «Змінюючи осередки» вказуємо адресу зі значенням коефіцієнта n. Тиснемо на кнопку «Знайти рішення».
- Рішення буде відображатися в осередку коефіцієнта n. Саме це значення буде найменшим квадратом функції. Якщо результат задовольняє користувача, то слід натиснути на кнопку «OK» в додатковому вікні.
Як бачимо, застосування методу найменших квадратів досить складна математична процедура. Ми показали її в дії на простому прикладі, а існують набагато більш складні випадки. Втім, інструментарій Microsoft Excel покликаний максимально спростити вироблені обчислення.