Абсолютна і відносна адресація в Excel.

Роздивимося таку задачу. Таблиця нарахування заробітної плати містить 4 графи: прізвище робітника, тарифну ставку кожного робітника, кількість відпрацьованих часів і нарахованої плати за працю.

 

	A	B	C	D
	Прізвище	Тариф. ставка	Відпрацьовані часи	Начисления
Значение	Формула
	Иванов	5,3			=В2*С2
	Петров	7,2			=В3*С3
	Сидоров	4,5			=В4*С4

 

Для того, щоб змусити електронну таблицю обчислити плату кожного робітника, необхідно в стовпчик D записати формули. Для другого рядка формула має вид =В2*С2. Для обчислення значення D2, як твори В2 иС2, ми використовували адреси комірок, що входять у формулу.

Але якщо нам захочеться підрахувати значення в такій комірці D3, те знову прийдеться записувати формулу D3=B3*C3. Це незручно для великих таблиць, і процес можна автоматизувати.

Набагато зручніше було б записати формулу для всіх комірок стовпчика D, щоб у них автоматично записувався твір відповідних комірок стовпчиків В и С. Формула тоді виглядала б так: Умножити значення, що знаходиться на дві комірки лівіше даної, на значення, розташоване в комірці зліва від даної. Адресация по методі «лівіше», «правіше», «нижче» і т.п. не потребує абсолютної вказівки адрес комірок, що входять у формулу, і називається відносною адресациєю.

Надається, по умовчанню програма Excel розглядає адреси комірок як відносні, тобто саме в такий спосіб. Це дозволяє копіювати формули методом заповнення. При копіюванні уздовж стовпчика в адресах, що входять у формулу, зміняться другі частини адрес - номера рядків. А при копіюванні уздовж рядка - перші частини адрес, тобто номера стовпчиків.

Проте іноді виникають ситуації, коли при заповненні комірок формулою необхідно зберегти абсолютну адресу комірки, якщо, наприклад, вона містить значення, використовуване при наступних обчисленнях в інших рядках і стовпчиках. Для того щоб задати посилання на комірку як абсолютну, треба задати перед позначенням номера стовпчика або номера рядка символ «$».

Таким чином, посилання на комірку, наприклад А1, може бути записана у формулі чотирма засобами: А1, $А1, А$1 і $А$1. При заповненні комірок формулою як відносна розглядається тільки та частина адреси, перед котрої немає символу «$».

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

 

Організація обчислень в Excel.

 

Уведення формул==================================================

Основне призначення Microsoft Excel — це обчислення даних числових та інших типів. У

Попередніх параграфах ви навчилися лише вводити дані в таблиці та оформляти їх належним

Чином. Інструментом опрацювання даних в Excel є формули. За допомогою формул можна

Робити арифметичні обчислення, працювати з текстовими даними тощо.

Подібно до інших даних, формули можуть бути введені в будь-які клітинки. Ознакою

формули є знак = на початку введених даних. Якщо першим символом клітинки вводиться =, то

Відображатися її вміст не буде. Замість цього Excel обчислить за певними правилами значення

виразу, який стоїть після знака =, і відобразить це значення в клітинці.

Наприклад, якщо в клітинку буде введено вираз =55+30, то замість нього ви побачите

Число 85. Первісний вираз, який ви ввели, можна побачити й відредагувати в рядку формул.

Якщо Excel не зможе через невідповідний синтаксис або некоректний запис обчислити

Значення введеного виразу, то в клітинці з’явиться повідомлення про помилку. Усі

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

====40.2. Обчислення арифметичних виразів====================================

Найпростіша формула, яку можна задати в Excel, — це формула обчислення значення

Арифметичного виразу. Арифметичний вираз — це сукупність чисел, знаків арифметичних дій

Та дужок.

В Excel визначені такі арифметичні операції:

+ — додавання;

– — віднімання;

* — множення;

/ — ділення;

^ — піднесення до степеня.

Пріоритет операцій визначається так само, як і в математиці: вищий пріоритет має

Операція піднесення до степеня, потім — множення і ділення, найнижчий — додавання і

Віднімання. Змінювати порядок обчислення можна за допомогою круглих дужок.

Приклади арифметичних виразів:

Вираз Результат

=(3+3)*4 24

=50/2^3 6,25

=45/2/4,5 5

Користуючись описаними вище можливостями, що надає Excel, його можна застосовувати

Навіть як калькулятор для обчислення складних арифметичних виразів.

Окрім чисел, у записах арифметичних виразів можна використовувати адреси клітинок

Таблиць. При виконанні обчислень значень формул замість адрес цих клітинок

Використовуються значення, що знаходяться в цих клітинках. 2

Наприклад:

Рядок Стовпець А Стовпець В Формула стовпець С Результат стовпець С

1 3 4 =А1+В1 7

2 2 5 =(3+А2)*В3 30

3 2 6 =А1+В1-А3+В3 11

Адреси клітинок можна вводити великими або маленькими буквами, вони будуть

Автоматично перетворюватися на великі. Часто початківці припускаються помилки, вводячи

Адреси клітинок літерами українського алфавіту. У таких випадках замість результату

обчислення в клітинці з’являється повідомлення про помилку: #ІМ’Я!.

Під час уведення адреси клітинки замість використання клавіатури можна клацнути лівою

Кнопкою миші по клітинці, і її адреса з’явиться у формулі, яку ви вводите. Особливо зручно

Таким чином уводити адреси клітинок, які знаходяться на інших аркушах.

Дуже важливою особливістю електронних таблиць є те, що вміст клітинок, які отримані за

Результатами обчислень через формули, автоматично поновлюється при будь-яких змінах у

Клітинках таблиці. Так, наприклад, у таблиці, що наведена вище, при зміні числа в клітинці А1 з

На 5 автоматично зміняться значення в клітинках С1 (на 9) та С3 (на 13).

Наведемо приклад оформлення таблиці, створеної для обчислення рахунку, що видається

для здійснення покупки в магазині (рис. 40.1).

Рис. 40.1. Таблиця «Рахунок на придбання товарів»

Ця таблиця містить такі формули в стовпці Е:

E5: =C5*D5

E6: =C6*D6

E7: =C7*D7

E8: =C8*D8

E9: =E5+E6+E7+E8

====40.3. Використання автозаповнення при введенні формул======================

Як добре видно з попереднього прикладу, у таблицях часто виникає необхідність уведення

Великої кількості схожих формул. Так, у стовпці Е при обчисленні вартості товарів користувач

Має ввести майже одну й ту саму формулу чотири рази. А що робити, якщо перелік товарів

Налічує десятки або навіть сотні найменувань? У такому випадку дуже корисним виявляється

Автозаповнення.

При протягуванні вниз (або вгору) маркера заповнення, починаючи з клітинки з

Формулою, ця формула автоматично скопіюється в усі наступні клітинки, при цьому будуть

Відповідно змінені всі номери рядків. Наприклад, при протягуванні вниз клітинки з формулою3

=A1+B1 у наступних клітинках з’являться формули =A2+B2, =A3+B3 і так далі.

Для заповнення клітинок стовпця Е таблиці, яка наведена на рис. 40.1, можна було

Набрати формулу тільки в клітинці Е5, а далі скопіювати її вниз на клітинки Е6, Е7 та Е8 і в

Такий спосіб значно скоротити собі роботу.

Аналогічно, при протягуванні вправо (або вліво) маркера заповнення, починаючи з

Клітинки з формулою, ця формула автоматично скопіюється в усі наступні клітинки, при цьому

Будуть відповідно змінені всі літери стовпців. Наприклад, при протягуванні вправо клітинки з

формулою =A1+B1 у наступних клітинках з’являться формули =B1+C1, =C1+D1 і так далі.

====40.4. Фіксовані адреси===================================================

При виконанні автозаповнення, як ми побачили, формула автоматично змінюється

Шляхом заміни в ній номерів рядків або літер стовпців. Інколи це є дуже зручним, але не

Завжди.

Рис. 40.2. Таблиця обчислення

Відстанні

Наведемо приклад таблиці, у якій обчислюється

відстань, що проїхав автомобіль за 1, 2,..., 10 с зі сталою

швидкістю 5 м/с (рис. 40.2).

Формули для обчислення відстані у клітинках

стовпця В мають вигляд:

B3: =A3*B1

B4: =A4*B1

...

B13: =A13*B1

При автозаповненні цих клітинок, починаючи з B3,

ми отримаємо такі формули:

B3: =A3*B1

B4: =A4*B2

...

B13: =A13*B10

Як бачите, автозаповнення змінило адреси клітинок не так, як ми хотіли: у формул і

Клітинки B4 замість А3 ми отримаємо А4, що нам і потрібно, але В1 перетвориться на В2, що

Неправильно.