Застосування методу найменших квадратів у Excel

Зміст

• Використання методу в Екселі
  • Включення надбудови & 171; пошук рішення & 187;
  • Умови завдання
  • Рішення
• Питання та відповіді

Метод найменших квадратів являє собою математичну процедуру побудови лінійного рівняння, яке б найбільш точно відповідало набору двох рядів чисел. Метою застосування даного способу є мінімізація загальної квадратичної помилки. У програмі Excel є інструменти, за допомогою яких можна застосовувати даний метод при обчисленнях. Давайте розберемося, як це робиться.

Використання методу в Екселі

Метод найменших квадратів (МНК) є математичним описом залежності однієї змінної від другої. Його можна використовувати при прогнозуванні.

Включення надбудови & 171; пошук рішення & 187;

Для того, щоб використовувати МНК в Ексель, потрібно включити надбудову &171; пошук рішення & 187; , яка за замовчуванням відключена.

1. Переходимо у вкладку " Файл» .

2. Натискаємо по найменуванню розділу «Параметри» .

3. У вікні зупиняємо вибір на підрозділі " надбудови» .

4. В блоці «Управління» , який розташований в нижній частині вікна, встановлюємо перемикач в позицію «Надбудови Excel» (якщо в ньому виставлено інше значення) і тиснемо на кнопку " Перейти...» .

5. Відкривається невелике віконце. Ставимо в ньому галочку близько параметра " пошук рішення» . Тиснемо на кнопку «OK» .

Тепер функція пошук рішення в Excel активована, а її інструменти з'явилися на стрічці.

Урок: пошук рішення в Екселі

Умови завдання

Опишемо застосування МНК на конкретному прикладі. Маємо два ряди чисел x і y , послідовність яких представлена на зображенні нижче.

Найбільш точно дану залежність може описати функція:

Y=a+nx

При цьому, відомо що при x=0 y також дорівнює 0 . Тому дане рівняння можна описати залежністю y=nx .

Нам належить знайти мінімальну суму квадратів різниці.

Рішення

Перейдемо до опису безпосереднього застосування методу.

1. Зліва від першого значення x ставимо цифру 1 . Це буде наближена величина першого значення коефіцієнта n .

2. Праворуч від стовпця y додаємо ще одну колонку – nx . В першу клітинку даного стовпця записуємо формулу множення коефіцієнта n на клітинку першої змінної x . При цьому, посилання на поле з коефіцієнтом робимо абсолютної , так як це значення змінюватися не буде. Кількома по кнопці Enter .

3. Використання маркер заповнення , копіюємо цю формулу на весь діапазон таблиці в стовпці нижче.

4. В окремій комірці вираховуємо суму різниць квадратів значень y і nx . Для цього натискаємо на кнопку " Вставити функцію» .

5. У відкритому & 171; Майстер функцій&187; шукаємо запис " СУМКВРАЗН» . Вибираємо її і тиснемо на кнопку «OK» .

6. Відкривається вікно аргументів. У полі " Масив_х» вводимо діапазон осередків стовпця y . У полі " Масив_у» вводимо діапазон комірок стовпця nx . Для того, щоб ввести значення, просто встановлюємо курсор в поле і виділяємо відповідний діапазон на аркуші. Після введення тиснемо на кнопку «OK» .

7. Переходимо у вкладку»дані" . На стрічці в блоці інструментів " аналіз» тиснемо на кнопку " пошук рішення» .

8. Відкривається вікно параметрів даного інструменту. У полі " оптимізувати цільову функцію» вказуємо адресу комірки з формулою " СУМКВРАЗН» . В параметрі «До» обов'язково виставляємо перемикач в позицію «мінімум» . У полі " зміна клітин» вказуємо адресу зі значенням коефіцієнта n . Тиснемо на кнопку " знайти рішення» .

9. Рішення буде відображатися в комірці коефіцієнта n . Саме це значення буде найменшим квадратом функції. Якщо результат задовольняє користувача, то слід натиснути на кнопку «OK» у додатковому вікні.

Як бачимо, застосування методу найменших квадратів досить складна математична процедура. Ми показали її в дії на найпростішому прикладі, а існують набагато складніші випадки. Втім, інструментарій Microsoft Excel покликаний максимально спростити вироблені обчислення.