Копіювання формул. Абсолютні та відносні адреси

Якщо потрібно використати ідентичну формулу для кількох клітинок (тільки для різних значень), то в такому випадку формулу вводять 1 раз, а потім її копіюють.

Копіювання формули – це процес поширення дії формули, занесеної в одну клітинку, на інші клітинки. Копіювання формули виконують методом перетягування маркера табличного курсора вибраної клітинки, що містить формулу, у потрібному напрямку. Маркер знаходиться у нижньому правому куті курсора. Для копіювання клацають на маркері мишею (вказівник миші має набути хрестоподібного вигляду) і, не відпускаючи ЛКМ, виконують перетягування. (Пригадайте метод автозаповнення таблиці).

Під час копіювання формули відбуваються такі дії:

§ формула записується в іншу комірку;

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

Наприклад.

Якщо в комірці С1 записана формула =А1*В1, то при її розтягуванні на весь стовпчик С у комірці С2 буде формула =А2*В2, у С3 =А3*В3 і т.д. Аналогічно і при розтягуванні на рядок, тільки тут букви будуть наростати за алфавітом.

Це дуже зручний механізм підрахунку даних в таблицях, але інколи він заважає. Наприклад, коли всі формули посилаються на одну комірку, де зберігається незмінне значення. У такому разі застосовують „ абсолютну адресу ”. Наприклад, $A$1. Буква або число, що стоїть після знака $, при розтягуванні на рядки чи стовпчики не змінюється.

Адреси, які не містять позначки $, називаються відносними.

В адресі можна зафіксувати тільки номер стовпця або тільки номер рядка. У такому випадку маємо змішану адресу. Наприклад, $АА5, АА$5. Перша адреса зберігатиме під час копіювання назву стовпця АА, друга — номер рядка 5.

Важливою властивістю ЕТ є автоматичне переобчислення результатів – якщо змінити хоч би одне вхідне дане, то результати у всій таблиці переобчисляться автоматично.

Файл ЕТ Excel зберігається з розширенням.xls і називається книгою Excel. Одна книга може містити кілька таблиць, що всередині файлу називаються аркушами. Знаходячись на поточному аркуші, можна звертатися до комірок з іншого аркуша. Посилання на комірку з іншого аркуша треба виконувати за повною адресою. (Наприклад, Лист2!А1 – це посилання на клітинку А1 з другого аркуша).

Діагностика помилок у формулах

 

Реакцією на неправильне введення формул є повідомлення Excel про помилки, тобто значення в комірках, що починаються зі знака #. Наведемо список можливих значень помилок:

§ ##### — найчастіше з'являється у користувачів-початківців і означає, що ширина комірки недостатня для розміщення у ній числа, дати або часу. Розширте комірку або змініть формат числа.

§ #ИМЯ? — неможливість розпізнати ім'я, що використовується. Ця помилка виникає, коли неправильно зазначене ім'я об'єкта або є посилання на вилучене ім'я, коли неправильно записана функція, коли під час запису адрес замість латини використана кирилиця тощо.

§ #ЗНАЧ! — спроба некоректного використання функції. Поширеною помилкою є невідповідність даних установленому формату. Наприклад, замість числа або дати в аргументі використовується текст.

§ #ЧИСЛО! — з'являється під час неправильного запису або використання чисел. Можливо, у функції з числовим аргументом використовується аргумент нечислового формату або до комірки введена формула, що повертає занадто велике за модулем числове значення (понад 10³⁷).

§ #ССЫЛКА! — означає неправильне застосування посилань, які є у формулі. Можливо, формула містить посилання на комірку, що уже вилучена, або на комірку, до якої скопійовано зміст інших комірок.

§ #ДЕЛ/0! — спроба ділення на нуль. Така ситуація найчастіше виникає під час використання як дільника посилання на порожню комірку або комірку, яка містить нульове значення.

§ #ПУСТО! — значення помилки, що з'являється під час посилання на порожню множину комірок. Можливо, що ви помилилися у визначенні перетину двох діапазонів. Наприклад, поставили у вираз типу (А3:А6 С4:F7) замість коми пропуск.

§ #Н/Д — скорочення від терміна «невизначені дані». Це значення помилки звичайно спеціально вводять до комірок, щоб запобігти обчисленням у них (наприклад, за відсутністю даних). Коли необхідні дані з'являються, їх просто вводять у формулу.

 

3.8. Запитання, завдання, тести

Запитання для самоконтролю:

1. Що таке формула Excel?
2. Що таке функція Excel?
3. Яке призначення функцій Excel?
4. Які типи функцій ви знаєте?
5. Які типи логічних функцій вам відомі?
6. Для чого використовуються логічіні функції?
7. Які елементи використовують у формулах для проведення обчислень?
8. Який знак потрібно завжди записувати перед формулою? Які ще знаки можна використовувати?
9. Які правила запису формули?
10. Яке призначення копіювання формул?
11. Як можна копіювати формулу з однієї комірки у безперервну послідовність наступних комірок в одному стовпчику з автоматичною зміною номера рядка?
12. Як швидше обчислити суму вмісту комірок FЗ, F4, F5, F6, F7 та записати її у наступну комірку F8?
13. У яких випадках використовують абсолютні посилання на адреси комірок?
14. Які правила запису функції в ЕТ Ехсеl?
15. На які категорії поділяються вбудовані функції Ехсеl?
16. Що таке «значення помилки»?
17. Які знаки арифметичних дій використовують у формулах?
18. Яку формулу треба записати до комірки F6, щоб обчислити суму чисел, що знаходяться в комірках D6 і Е6?
19. Який загальний вигляд має функція електронної таблиці?
20. Які види операторів використовують в формулах електронних таблиць?
21. Як ввести функцію до формули за допомогою майстра функцій?
22. Назвіть найпростіший спосіб обчислення суми комірок.
23. Що таке «вкладена функція»?
24. Які бувають значення помилок?

Завдання для самостійного виконання

1) Відкрити документ Чек.xls

2) Обчислити значення клітинок стовпчиків Сума і Всього.

Для цього:

1) введіть у клітинку Е3 знак =;

2) клацніть лівою клавішею мишки по клітинці С3 (після цього у формулу автоматично запишеться адреса клітинки);

3) введіть знак * (помножити);

4) клацніть лівою клавішею мишки по клітинці D3;

5) натисніть Enter (щоб виконати обчислення за введеною формулою);

6) наведіть вказівник миші на маркер автозаповнення;

7) затисніть ліву клавішу мишки і утримуючи їїі розтягніть виділення на діапазон E4:E17 (відбудеться копіювання формули). Після цього відпустіть мишку.

8) Тепер обчислимо ВСЬОГО: поставте курсор на клітинці Е18.

9) Виконайте команду головного меню Вставка/Функция.

10) У вікні Майстра функцій у стовпчику Категорія виберіть Математичні.

11) У стовпчику Функція виберіть СУММ і натисніть ОК.

12) У вікні Аргументи функції поставте курсор в рядок Число1.

13) І поза вікном виділіть діапазон E3:E17 (щоб вписати адресу діапазону, суму якого необхідно знайти)..

14) Натисніть ОК (у вікні Аргументи функції).

15) Дооформлюйте таблицю і завдання виконане.

3) Вставте у книгу новий аркуш і назвіть його Рахунок-фактура.

4) Побудуйте таблицю за зразком:

Примітка: Величина в колонці " Сума " отримується від множення відповідних значень в колонках " Кількість " і " Ціна ". Значення " Сума з врахуванням знижки " знаходять в результаті віднімання від " Суми " добутку " Суми " на " Торговельну знижку ". Величина в рядку " Всього " дорівнює сумі значень колонки „ Сума з врахуванням знижки ”.

5) Додаткове завдання:

Довідка за тарифами:

Електроенергія	0,16грн.	За Квт/год.
Холодна вода	10,92	На одного мешканця
Гаряча вода	10,55	На одного мешканця
Опалення	0,99	На 1 м^2 площі квартири
Газ	1,86	На одного мешканця
Каналізація	2,97	На одного мешканця
Експлуатаційні витрати	0,19	На 1 м^2 площі квартири

Використайте абсолютну адресу у формулах при посиланні на тариф відповідної послуги.

Тести

Перевірте себе по Розділу 3. (файл test.pps, кнопка Розділ 3.)

 

Діаграми

Види діаграм

Діаграми призначені для наочного подання числових даних.

Діаграми поділяються на стандартні (найбільш поширені) та нестандартні. Стандартні діаграми бувають кругові, точкові, стовпчикові тощо. З нестандартних використовуються: блоки з областями, блакитна кругова, дерев’яна тощо.

Усі діаграми (окрім кругової і пелюсткової) мають дві осі: горизонтальну — вісь категорій, вертикальну — вісь значень. Об'ємні діаграми мають третю вісь — вісь рядів.

Діаграма складається з декількох елементів, назви яких можна прочитати на екрані, навівши на елемент діаграми покажчик миші.

Елементи діаграми є об'єктами, над якими визначені певні дії. Наприклад, за допомогою контекстного меню можна виконати команду Формат элемента і замалювати рамку, в якій знаходиться елемент, деяким кольором чи текстурою.

Побудова діаграм

 

Побудова діаграм здійснюється за допомогою спеціального засобу — Майстра діаграм. Майстер діаграм запускається за допомогою кнопки Мастер диаграмм на панелі інструментів або командою з меню Вставить → Диаграмму.

Рекомендовано перед запуском Майстра виділяти діапазони з даними, які потрібно графічно відобразити. Це, зазвичай, суміжні рядки чи стовпці (переважно з назвами). Нагадаємо, для виділення несуміжних діапазонів потрібно скористатися клавішою Ctrl.

Під керівництвом майстра користувач виконує чотири кроки:

Крок 1: вибирає тип і вигляд діаграми (Мал. 9).

Мал. 9

Крок 2: задає дані (якщо вони не були вибрані заздалегідь)(мал.10). На цьому кроці також визначаються ряди даних (вкладка Ряд, мал.11, – це рядки даних, розміщених по рядках таблиці чи по стовпцях (вибираємо). На вкладці Ряд можна додати і видалити непотрібні рядки, змінити назви рядків.

 

Мал.10 Мал.11

Крок 3: задає параметри (підписи) діаграми (мал.12).

Мал.12

Крок 4: зазначає, куди занести діаграму (на окрему чи на поточну сторінку)(мал.13).

Мал.13

Щоб перейти до наступного кроку, натискають на кнопку Далее, а щоб повернутися — на кнопку Назад.

Можна пропустити один чи два кроки, натискаючи відразу на кнопку Далее. Щоб завершити (достроково) роботу Майстра діаграм і отримати діаграму, натискають на кнопку Готово.

 

4.3. Запитання, завдання, тести

Запитання для самоконтролю:

1. Яке призначення діаграм?

2. Накресліть ескіз кругової діаграми.

3. Для чого використовують кругові діаграми?

4. Як отримати на діаграмі назву її елементів?

1. Як вилучити діаграму зі сторінки?
2. Як перемістити діаграму в потрібне місце?
3. Як збільшити розмір діаграми?
4. Як можна запустити Мастер диаграмм?
5. Для чого використовують стовпчикові діаграми?
6. Які є типи діаграм?
7. Які є види кругової діаграми?
8. З яких елементів складається діаграма?
9. Які дії визначені над елементами діаграми?

14. Накресліть ескіз стовпчикової діаграми.

1. Як скопіювати діаграму на іншу сторінку?
2. Як виділити елемент діаграми?
3. Яке призначення Мастера диаграмм?
4. Як виділити несуміжні діапазони даних?
5. Що таке діаграма?
6. Як користуватися Майстром діаграм?
7. Як здійснюється підбір типу діаграми?
8. Як проводити настроювання діаграми?
9. Що таке панель інструментів "Діаграми"? Як нею користуватися?

Завдання для самостійного виконання

1) Створіть таблицю за зразком. Побудуйте діаграму кількості населення за зразком.

Країни
№ з/п	Країна	Площа(кв.км)	Населення
1.	Австрія	83 800,00	7 909 000
2.	Болгарія	111 000,00	8 990 000
3.	Великобританія	244 100,00	57 960 000
4.	Греція	132 000,00	10 068 000
5.	Данія	44 500,00	5 181 000
6.	Естонія	45 200,00	1 582 000
7.	Іспанія	504 800,00	39 115 000
8.	Італія	3 012 000,00	57 836 000
9.	Латвія	64 500,00	2 681 000
10.	Литва	65 200,00	3 728 000
11.	Мальта	300,00	363 000
12.	Молдова	33 700,00	4 467 000
13.	Німеччина	357 000,00	7 970 000
14.	Польща	312 700,00	38 459 000
15.	Росія	17 075 400,00	148 545 000
16.	Угорщина	93 000,00	10 289 000
17.	Україна	603 700,00	49 700 000
18.	Франція	544 000,00	57 529 000
19.	Швеція	450 000,00	8 627 000
20.	Азербайджан	86 600,00	7 137 000
21.	Афганістан	647 000,00	18 052 000
22.	В'єтнам	331 700,00	71 187 000
23.	Грузія	697 000,00	5 464 000
24.	Ізраїль	20 800,00	5 191 000
25.	Ірак	438 000,00	20 286 000
26.	Іран	1 648 000,00	56 933 000
27.	Узбекистан	447 400,00	20 708 000
28.	Туреччина	781 000,00	62 965 000
29.	Японія	372 000,00	124 620

2) Побудуйте на проміжку [-2; 2] з кроком h=0,5 таблицю значень функції у=х². Побудувати графік функції за знайденими значеннями.

Примітка. Гафік повинен мати схожий вигляд:

Щоб побудувати графік, треба побудувати таблицю значень Х та У. Таблиця міститиме два стовпчики (Х та У). Стовпчик Х заповніть значеннями: перше значення -2, наступні – обчислюються в порядку зростання на 0,5 (арифметична прогресія з кроком 0,5, останнє значення 2) Значення стовпчика У обчислюються за формулою у=х² (відповідне значення стовпчика Х піднести до квадрату).

3) Побудувати таблицю і діаграму за зразком.

ГРАФІК ЗМІНЮВАННЯ ТЕМПЕРАТУРИ

Число
tº С

 

Тести

Перевірте себе по Розділу 4. (файл test.pps, кнопка Розділ 4.)

 

 

5. Підтримка баз даних в електронних таблицях