Ефективна реалізація розрахунків в Microsoft Excel

В Microsoft Excel прийнята лінійна форма запису формул у відповідному рядку 4 рис. 1.1. Водити функції можна за допомогою майстра функцій або клавіатури. У якості аргументів функції можуть виступати числа, посилання на комірки, текстова інформація. Комірка в якій вводиться формула називається залежною, а комірка в якій записаний аргумент цієї формули – впливаючою. Очевидно, що одна і таж сама комірка може розглядатися і як залежна і як впливаюча, в залежності від того, яку формулу ми розглядаємо.

Наприклад розглянемо завдання 1 лабораторно-практичної роботи. Необхідно кількість товару помножити на його ціну із подальшим знаходженням сумарної вартості покупки. Для цього помножимо комірку із кількістю товару на комірку із ціною. Розповсюдимо отриману формулу на весь необхідний діапазон. Отримаємо автосуму добутків за допомогою функції СУММ (рис. 1.3). Для формули представленої на рис. 1.3 залежною коміркою є D7, а впливаючими В7 та С7. В той же час комірка D7 є спливаючою відносно формули в залежній комірці D17.

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

Відносні посилання – посилання на впливаючі комірки, адреса яких змінюється, як по рядку так і по стовпчику, відносно зсуву впливаючої комірки при копіюванні. Приклад запису А1, D17, Е20 и т.д.

При реалізації розрахунків представлених на рис. 1.3 використовувались виключно відносні посилання, однак це не завжди зручно.

Абсолютні посилання – посилання на впливаючі комірки, адреса яких залишається зафіксованою при копіюванні залежної комірки. Приклад запису $А$1, $D$17, $Е$20 и т.д.

Використання абсолютних посилань на комірки розглянемо на наступному прикладі. Маємо тарифні коефіцієнти для 25-ти розрядів єдиної тарифної сітки та посадовий оклад для першого розряду. Шляхом множення окладу для першого розряду на відповідні тарифні коефіцієнти необхідно знайти посадові оклади для кожного розряду єдиної тарифної сітки. Для цього в рядку введення формули зробимо посилання на комірку із посадовим окладом першого розряду. Шляхом натискання кнопки F4 перетворимо відносне посилання в абсолютне. Отриману комірку помножимо на тарифний коефіцієнт другого розряду. Це посилання залишимо відносним (рис. 1.4).

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

Окрім відносних та абсолютних посилань на комірки залишився третій змішаний тип посилань.

 

Рис. 1.3 – Реалізація розрахунків із використанням відносних посилань на комірки

Рис. 1.4 – Реалізація розрахунків із використанням абсолютних та відносних посилань на комірки

 

Змішані посилання – посилання на впливаючі комірки, адреса яких змінюється, тільки по рядку або тільки по стовпчику, відносно зсуву впливаючої комірки при копіюванні, причому інша частина посилання (стовпчик або рядок) залишається зафіксованою. Приклад запису змішаного посилання із зафіксованим стовпчиком $А1, $D17, $Е20. Приклад запису змішаного посилання із зафіксованим рядком А$1, D$17, Е$20. Як бачимо, зафіксованою залишається та частина посилання перед якою стоїть знак «$».

Використання змішаних посилань розглянемо в рамках третього завдання лабораторної роботи. Маємо 20 видів товару, ціна кожного із яких відома. Необхідно розрахувати таблицю вартостей товару для різної їх кількості від 2 до 10 шт. Очевидно, що в рядку введення формули необхідно ввести добуток ціни товару на кількість. Причому при посиланні на комірку із ціною товару фіксуємо тільки стовпчик, шляхом натискання кнопки F4 три рази. При посиланні на комірку із кількістю товару фіксуємо тільки рядок, шляхом натискання кнопки F4 два рази (рис. 1.5).

 

 

Рис. 1.5 – Реалізація розрахунків із використанням змішаних

посилань на комірки

 

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

Перехід від відносних до абсолютних а потім змішаних посилань може здійснюватись шляхом натискання кнопки F4 по циклу показаному на рис. 1.6. Очевидно, що знак «$» також можна поставити із клавіатури.

 

 

Рис. 1.6 – Перехід від до різних типів посилань

 

Результат виконання усіх трьох завдань та оформлення лабораторно-практичної роботи наведений у додатку 1. Дана лабораторно-практична робота має на меті здобуття студентами практичних навичок роботи в Microsoft Excel, тому може бути виконана за одним варіантом, що розглядається вище.