Практична робота 1 Створення та заповнення таблиці постійними даними та формулами

Практична робота №1

 

Створення та заповнення таблиці постійними даними та формулами

 

Мета роботи: сформувати навички виконання основних операцій зі створення та заповнення електронної таблиці постійними даними та формулами (робота з листами, введення та форматування даних різних типів, використання автозаповнення, «Майстра функцій» тощо).

 

Теоретичні відомості

 

Електронні таблиці складаються з певної кількості аркушів. Кожен аркуш має ім'я, яке відображається на ярлику аркуша. За замовчуванням аркуші мають імена «Аркуш 1», «Аркуш 2» і т.д., які при необхідності можна змінити.

Робочий аркуш розділений сіткою на рядки та стовпці. Стовпці позначаються великими латинськими літерами (A, B, C, …, Z), а далі дволітерними поєднаннями (AA, AB, …, BA, BB, …). Рядки нумеруються послідовно, починаючи з 1.

Перетин конкретних стовпців і рядків утворює клітинку. Розташування клітинки задається адресою, утвореною з імені стовпця та номера рядка, на перетині яких вона знаходиться. Таким чином, найперша клітинка має адресу A1. Далі рядком – B1, C1 тощо. У наступному (другому) рядку – A2, B2, C2 тощо

Якщо треба задати адресу клітинки, яка знаходиться на іншому робочому аркуші або в іншій робочій книзі, перед ім'ям задають ім'я робочого аркуша, а в другому випадку ім'я робочої книги. Наприклад: Аркуш 1!А1 або Робота

1.xls'#'Завдання 2'.C13.

Поряд із поняттям адреси клітинки в електронній таблиці використовується концепція посилання. Посилання – це елемент формули, який використовується тоді, коли треба послатися на якусь клітинку таблиці. У цьому випадку адреса буде використовуватися як посилання.

Крім поняття клітинки використовується поняття діапазону - прямокутної області, що складається з декількох (або одного) стовпців і декількох (або одного) рядків. В якості адреси діапазону вказуються адреси лівої верхньої та правої нижньої клітинки діапазону, розділені знаком двокрапка (:). Наприклад, діапазон A1:C4 містить 12 осередків (по 3 осередки в рядках і 4 осередки в стовпцях). 

У клітинки робочого листа вводяться два види даних: постійні значення (константи) та формули.

Постійні значення – це цифри, символи, текст. У комірку константа записується так: курсор встановлюється в комірку і з клавіатури вводиться значення. Редагування введеного значення проводиться після встановлення курсору в потрібну комірку, а далі клацнути кнопкою миші в рядку введення у потрібному місці.

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

Формула завжди починається з символу (=).

Будьте уважні! Посилання на комірки, які використовуються у формулах, слід вводити англійською мовою.

Редагуються формули аналогічно постійним даним.

Хід роботи

 

Введення данних. Автозаповнення.

 

Завантажте електронні таблиці. Перейменуйте Аркуш 1. Для цього клацніть правою кнопкою на ярлику аркуша, виберіть з контекстного меню команду Перейменувати і введіть нове ім'я «Завдання 1».

 

Починаючи з комірки А1, введіть заголовки стовпців майбутньої таблиці відповідно до рисунку

 

 

 

Вирівняйте ширину стовпчиків.

Это можно сделать автоматически командой Формат / Ширина стовпця /  або зробити Автопідбір ширини стовпця. Також ширину можна змінити вручну, встановши курсор на межі між стовпцями (курсор при цьому стане двусторонньою стрілочкою) розтягнути стовпчик до потрібної ширини. 

 

В клітинку А2 введіть перший тип комп’ютера: Тип 1.

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

.

 

Щоб заповнити значеннями типів комп'ютерів кілька клітинок 

-             клацніть по клітинці А2;

-             підведіть вказівник миші до маркеру Автозаповнення так, щоб вказівник перетворився на чорний хрестик;

-             натисніть ліву кнопку миші та протягніть маркер Автозаповнення до потрібної комірки (до комірки А11). 

Зверніть увагу, що при опусканні вниз на кожну клітинку біля неї спливає майбутнє значення: Тип 2, Тип 3 і т.д.;відпустіть кнопку миші, клітинки заповняться значеннями автоматично.

(Таким чином зручно заповнювати прогресію, дні тижня, місяці тощо)

 

           

Створення формул.

 

Внесіть до таблиці кількість комп'ютерів та ціни в доларах ($) відповідно до рисунку, а також додайте додаткові рядки у вказаних на рисунку клітинках.

 

 

Розрахуйте загальну вартість закупівлі ($), використовуючи метод вводу формул вручну.

Для того щоб розрахувати вартість закупівлі комп'ютерів Типу 1 ($) методом введення формул вручну:

-             переконайтеся, що обрано англійську мову;

-             встановіть курсор у комірці D2;

-             введіть знак рівності (=), а потім вручну надрукуйте формулу: =В2 * С2;

 

Зверніть увагу, що всі дії повторюються вище в рядку формул;

 

Щоб завершити введення формули, натисніть клавішу <Enter> або кнопку на панелі формул. Переконайтеся, що в D2 з'явилося числове значення 6500.

Розглянемо раціональніший спосіб введення формул, яким краще користуватися надалі – метод введення формул шляхом вказівки клітинок.

Розрахуйте загальну вартість закупівлі ($). Для цього:

-             встановіть курсор у комірці D3;

-             клацніть у рядку формул і введіть знак рівності (=);

-             клацніть по комірці В3. Переконайтеся, що навколо клітинки В3 з'явилася активна рамка, а рядок формул відобразилася адреса комірки В3  продовжіть введення формули, надрукувавши з клавіатури знак множення (*); клацніть по клітинці С3. Переконайтеся, що її адреса також відобразилась у

рядку формул;

Щоб завершити введення формули, натисніть клавішу <Enter> або кнопку на панелі формул. Переконайтеся, що в D3 з'явилося числове значення 8000.

 

Адресація клітинок.

 

Для автоматизації однотипних обчислень в електронних таблицях використовується механізм копіювання та переміщення формул, при якому відбувається автоматичне налаштування посилань на комірки з вихідними даними. Бувають випадки, коли посилання, що використовуються у формулі, не потрібно змінювати або потрібно змінювати посилання лише ім'я стовпця або номер рядка. 

Для вирішення всіх цих завдань є посилання трьох типів: відносні, абсолютні, змішані (частково абсолютні).Розглянемо на прикладах їх особливості

 

Відносні посилання

 

Прорахуйте загальну вартість закупівлі ($) для решти типів комп'ютерів, використовуючи маркер автозаповнення. Для цього:

-             клацніть по клітинці D3;

-             встановіть курсор на маркер автозаповнення;

-             натисніть ліву кнопку миші і, не віджимаючи, протягніть формулу до кінця списку;

-             відпустіть ліву кнопку;

переконайтеся, що в кожному рядку програма змінила посилання на клітинки відповідно до нового положення формули (у вибраній клітинці D11 формула виглядає =В11*С11) і що всі клітинки заповнилися відповідними числовими значеннями.

 

 

Це стало можливим, оскільки за замовчуванням табличний редактор створює відносні посилання, які змінюються при копіюванні в залежності від нового положення формули. Зміна адреси відбувається за правилом відносної орієнтації клітинки з вихідною формулою і клітинок з аргументами. Відносні посилання мають вигляд А1, В3.

 

Абсолютні посилання

 

Прорахуйте ціну комп'ютерів у гривнях, використовуючи вказаний у таблиці курс долара по відношенню до гривні:

-            встановіть курсор у клітинці Е2;

-            введіть формулу = С2 * В21;

-            переконайтеся, що вийшло числове значення 52000;

спробуйте розповсюдити формулу вниз на весь список за допомогою маркера

автозаповнення. Переконайтеся, що скрізь вийшли нулі! 

Це сталося тому, що при копіюванні формули відносне посилання на курс долара в осередку В27 автоматично змінилося на В28, В29 тощо. А оскільки ці клітинки порожні, то при множенні на них виходить 0.

Таким чином, вихідну формулу перетворення ціни з доларів у гривні слід змінити так, щоб посилання на комірку В27 при копіюванні не змінювалося.

Для цього існує абсолютне посилання на клітинку, яка при копіюванні та перенесенні не мінюється. 

Для того, щоб посилання не змінювалось, перед іменем стовпця або номером рядка ставиться символ $.

Перерахуйте стовпець Е: видаліть весь вміст діапазону клітинок Е2:Е11,  введіть у Е2 формулу = С2*$В$21.

За допомогою маркера автозаповнення поширіть формулу вниз на весь список. Перегляньте формули і переконайтеся, що відносні посилання змінилися, але абсолютне посилання на клітинку В21 залишилося. Впевніться, що ціна розраховується правильно.

 

Існує також поняття змішаного (частково абсолютного) посилання: якщо при копіюванні та переміщенні не змінюється ім'я стовпця або номер рядка. У цьому символ $ у першому випадку ставиться перед номером рядка, тоді як у другому – перед найменуванням стовпця. 

Наприклад, $5, D$12 – часткове абсолютне посилання по рядку; $В5, $D12 - часткове абсолютне посилання по стовпцю.

 

Знаючи ціну типу комп'ютера в гривнях та кількість комп'ютерів кожного типу, розрахуйте останній стовпець: загальну суму закупівлі у гривнях

.

Використання функцій.

 

У табличному редакторі передбачені функції, які можна використовувати для створення формул і виконання складних розрахунків.

Всі функції мають однаковий формат запису і включають дві частини – ім'я функції та її аргументи. Наприклад: = SUM (А1; C1: D5). 

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

Аргументи записуються в круглих дужках після імені функції і поділяються крапкою з комою ( ; ). Якщо аргументів немає, то дужки залишаються пустими. Деякі функції можуть мати кілька аргументів.

Функції вводяться звичайним набором з клавіатури або більш кращим способом - за допомогою Майстра функцій. Розглянемо обидва ці методи на прикладах.

Розрахуйте результат за стовпцем «Кількість», використовуючи функцію SUM, методом введення функцій вручну.

Метод введення функцій вручну полягає в тому, що потрібно ввести вручну з клавіатури ім'я функції та список її аргументів. Іноді цей метод виявляється найефективнішим. При введенні функцій зверніть увагу, що функції пойменовані англійською мовою. Рекомендується вводити функції малими літерами. Якщо програма не перетворює введений текст до верхнього регістру, то вона не розпізнала запис як функцію, тобто. ви ввели її неправильно.

 

Для розрахунку підсумку по стовпцю «Кількість»:

-             встановіть курсор у клітинку В13;

-             надрукуйте з клавіатури формулу = SUM (B2: B11);

-             натисніть клавішу <Enter> і переконайтеся, що в клітинці В13 з'явилося числове значення 75.

 

 

Розрахуйте підсумок по стовпцю «Загальна вартість, $», використовуючи засіб Майстер функцій.

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

Для його відкриття використовують команду Вставлення функції

 

 

Для розрахунку підсумку по стовпцю «Загальна вартість, $»: встановіть курсор у комірці D13; викличте діалогове вікно Майстер функцій; у полі Категорія виберіть Усі; у полі Виберіть функцію знайдіть SUM. 

Зауважте, що при виборі функції в лівій частині діалогового вікна з'являється список її аргументів разом з коротким описом;

Введіть аргументи суми. У полі Число 1 можна ввести відразу весь діапазон підсумовування D2:D11 (діапазон можна ввести з клавіатури, а можна виділити на аркуші лівою кнопкою миші, і тоді він відобразиться у формулі автоматично) 

 

 

Pверніть увагу на кнопку згортання діалогового вікна, розташовану в правій частині поля Число 1 . Це призведе до тимчасового згортання вікна, в результаті чого буде видно весь робочий аркуш;

Клацніть по кнопці ОК, переконайтеся, що в клітинці D13 з'явилося числове значення 78718. Аналогічним чином розрахуйте підсумок по стовпчику F.

Розрахуйте додаткові параметри, зазначені у таблиці (середні ціни, мінімальні та максимальні). Для цього у зазначених осередках використовуйте відповідні функції:

Адреси осередків та відповідні їм розрахункові функції

 

С15	Е15	С17	Е17	С19	Е19
=AVERA GE(C2:C11)	=AVER AGE(E2:E11)	=MI N(C2:C11)	=MI N(E2:E11)	=MA X(C2:C11)	=MA X(E2:E11)

 

Форматування даних.

 

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

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

Деякі операції форматування виконуються автоматично. Наприклад, якщо ввести в клітинку значення 10%, то програма буде знати, що ви хочете використовувати процентний формат, і застосуватиме його автоматично. Аналогічно якщо ви використовуєте пробіл для відділення в числах тисяч від сотень (наприклад, 123456), табличний редактор застосує форматування з цим роздільником автоматично. Якщо ви ставите після числового значення знак грошової одиниці, встановлений за замовчуванням, наприклад «$», то до цієї комірки буде застосовано грошовий формат.

Для встановлення форматів клытинок призначено діалогове вікно Формат клітинок.

 

Існує кілька способів виклику цього вікна. Перш за все необхідно виділити клітинки, які повинні бути відформатовані, а потім вибрати команду Число

 або клацнути правою кнопкою миші по виділених клітинках і з

контекстного меню вибрати команду Формат клітинок.

Далі на вкладці Число можна обрати потрібний формат. При виборі відповідної категорії зі списку права сторона панелі змінюється так, щоб відобразити відповідні опції. Крім цього, діалогове вікно Формат клітинок містить кілька вкладок, що надають користувачеві різні можливості для форматування: Вирівнювання, Шрифт, Межі, Заливка, Захист

 

Змініть формат діапазону клітинок  С2:С11 на Грошовий:

-            виділіть діапазон С2:С11;

-            клацніть всередині діапазону правою кнопкою миші;

-            виберіть Формат клітинок;

-            на вкладці Число виберіть категорію Грошовий;

-            у переліку Формат виберіть $ англійська (Сполучені штати);

-            параметр Кількість десяткових розрядів вкажіть рівним 0;

-            натисніть кнопку ОК

 

 

 

Аналогічно змініть формат для стовпців «Загальна вартість, $», «Ціна, грн.», «Загальна вартість, грн.». Також зміните формат для клітинок підсумку із середньою, мінімальною і максимальною цінами. 

Зверніть увагу, що якщо в клітинці після зміни формату замість числа відображається ряд символів (решітка ##########), то це означає, що стовпець недостатньо широкий для відображення числа у вибраному форматі. Збільшіть його ширину.

 

Оформлення таблиць.

 

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

 

Додайте заголовок до таблиці:

-             клацніть правою кнопкою миші за цифрою 1 у першому рядку;

-             виберіть команду Додати клітинки;

-             виділіть      діапазон      А1:    F1      і         виконайте   команду

 

-             введіть в об'єднані клітинки  назву «Звіт із закупівлі технічного обладнання»

Встановіть такі параметри для заголовку: Шрифт – Times New Roman, накреслення – напівжирний, кегль – 14; Ефекти шрифту – колір синій; Вирівнювання – по центру; Межі – зовнішні, Заливка- жовта. Натисніть кнопку ОК.Відформатуйте вміст таблиці

 

Застосуйте напівжирне накреслення до даних в діапазонах А2: F2, А3: А28;

Встановіть Заливку та Межі для діапазонів комірок: А14: F14;

B16:E16; B18: Е18; B20: E20; А22: B22

 

Виділіть курс долара напівжирним накресленням і червоним кольором; Діапазон А2: F12 оформіть межами: зовнішня ліні та лінії всередині.  Відрегулюйте ширину стовпчиків, якщо в процесі форматування дані збільшилися і не вміщуються у межі клітинки.

 

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

.

Контрольне завдання.

 

Перейдіть на Аркуш 2. Перейменуйте його у «Завдання 2».

Використовуючи отримані навички, створіть таблицю розрахунку щомісячної плати за електроенергію. Початковий вид таблиці для самостійного розрахунку:

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

Також введіть довільні значення показників лічильника до кінця року (враховуйте, що показання можуть лише збільшуватись!).

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

Для комірок стовпчикая Е використовуйте формат Грошовий.

Зауважте, що оскільки тариф залишається постійним, то при посиланні на комірку В1 слід використовувати абсолютне посилання.

 

 

 

 

 

Контрольні питання

 

1.                  З чого складається адреса клітинки? Що таке посилання?

2.                  Що розуміють під діапазоном клітинок?

3.                  Які типи даних можна ввести в клітинки робочого аркуша?

4.                  Що розуміють під формулою в електронних таблицях?

5.                  Як перейменувати, скопіювати, перемістити робочий аркуш?

6.                  Які ви знаєте способи зміни ширини стовпців (рядків)?

7.                  Як працює маркер автозаповнення?

8.                  Яких правил слід дотримуватись при створенні формули ?

9.                  У чому особливості відносних, абсолютних та частково абсолютних посилань?

10.             Що таке функція в електронних таблицях?

11.             Які кроки слід виконати, щоб ввести функцію за допомогою «Майстер функцій»?

12.             Як змінити формат клітинок?