Створення таблиць підстановки 2 страница

Для об’єднання комірок (наприклад, оформлення заголовку), потрібно виділити комірки, натиснути кнопку “Об’єднати і помістити в центрі” на панелі інструментів.

Хід роботи

1. Запустити програму Microsoft Ехсеl.

2. Відкрити документ, створений при виконанні попередньої лабораторної роботи.

3. Встановити масштаб перегляду 100%.

4. Встановити для блоку комірок A1:G1 режим Перенесення слів.

5. Встановити для таблиці автоформат Список2.

6. Встановити для значення комірок C2:N11 вирівнювання по центру.

7. Скопіювати таблицю (A1:G11) з аркуша Зведена_відомість на початок аркуша Нараховано_вересень (всі подальші вказівки стосуються цього аркуша).

8. Змінити колір таблиці на сірий.

9. Встановити для комірок Е2:Е10 грошовий формат із позначенням грошової одиниці “грн” та скопіювати його до комірок F2:G10.

10. Встановити для комірок Е3:N11 аркуша Зведена_відомість стиль Денежный, задавши параметри: грошова одиниця – грн., кількість десяткових знаків – 2, шрифт - Times New Roman, накреслення – курсив, границя – зовнішня.

11. Зберегти документ.

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

1) Що таке форматування? Для чого використовують форматування таблиці?

2) Що таке “умовне форматування”?

3) Який шрифт встановлюється у новій робочій книзі за замовчуванням?

4) Що таке “відсотковий формат”?

5) Як можна вводити відсотки автоматично?

6) Як об’єднати комірки для створення заголовку таблиці?

7) Що таке “стилістичне форматування”?

8) Як задати обрамлення комірок?

9) Що таке “форматування за зразком”?

Лабораторна робота 3

Тема. Обчислення в таблицях. Формули та правила їх обчислення. Абсолютні та відносні адреси.

Мета. Навчитись використовувати найпростіші формули для обчислень в електронних таблицях. Сформулювати поняття про відносні та абсолютні адреси.

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

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

Програмне забезпечення: табличний процесор Microsoft Ехсеl.

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

Без формул програма Ехсеl майже нічим не відрізнялась би від звичайного текстового редактора. Адже саме можливість вводити прості та складні формули і миттєво отримувати результати їх обчислення робить Ехсеl зручним і незамінним інструментом для виконання фінансових, інженерних, статистичних та інших розрахунків.

Числа, введенні в комірки, за допомогою формул можна складати, віднімати, множити і ділити. Ці операції здійснюються з використанням арифметичних операторів, що вводяться у формули, зокрема операторів додавання (+), віднімання (-), множення (*) та ділення (/). Формули можуть містити наступні елементи: числа, текст, арифметичні і логічні оператори, посилання на комірки, а також вбудовані функції Ехсеl. Вихідні дані для розрахунків можна поміщати в одні комірки, а формули – в інші, що дозволяє побачити, як зміна даних впливає на результати їх обчислення. Результат обчислення формули відображається безпосередньо в клітинці, куди введена ця формула. Саму формулу можна побачити в рядку формул, якщо встановити табличний курсор в клітинці з формулою.

Щоб показати програмі, що в комірку вводиться не числове значення або текст, а формула, потрібно перед першим символом формули поставити знак (=). Однак запис формули можна почати і зі знаків “+” , або “-”, при цьому наступний запис також буде сприйнятий як формула.

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

Оператори, які розпізнає програма Ехсеl:

Оператор	Назва	Приклад	Пояснення
+	Додавання	=А1+В1	Додає значення з комірки А1 в комірку В1
-	Віднімання	=А1–В1	Віднімає значення з комірки В1 із значення комірки А1
*	Множення	=А1*10	Виконує множення значення, що міститься в клітинці А1, на 10
/	Ділення	=А1/В1	Виконує ділення значення, що міститься в клітинці А1, на значення з комірки В2
^	Піднесення до степеня	=А1^2	Підносить значення, що міститься в клітинці А1, до степеня (у даному випадку до степеня 2)
&	Конкатенація	=А1&В1	Виконує об’єднання вмісту комірок А1 та В1. Як правило, використовується для текстових значень.
=	Логічне порівняння “дорівнює”	=А1=В1	Повертає значення TRUE (ІСТИНА), якщо значення в клітинці А1 дорівнює значенню в клітинці В1. Інакше повертає значення FALSE (ХИБА)
>	Логічне порівняння “більше ніж”	=А1>В1	Повертає значення TRUE, якщо значення в клітинці А1 більше значення в клітинці В1. Інакше повертає значення FALSE
<	Логічне порівняння “менше ніж”	=А1<В1	Повертає значення TRUE, якщо значення в клітинці А1 менше значення в клітинці В1. Інакше повертає значення FALSE
>=	Логічне порівняння “більше або дорівнює”	=А1>В1	Повертає значення TRUE, якщо значення в клітинці А1 більше або дорівнює значенню в клітинці В1. Інакше повертає значення FALSE
<=	Логічне порівняння “менше або дорівнює”	=А1<В1	Повертає значення TRUE, якщо значення в клітинці А1 менше або дорівнює значенню в клітинці В1. Інакше повертає значення FALSE
<>	Логічне порівняння “більше ніж”	=А1<>В1	Повертає значення TRUE, якщо значення в клітинці А1 не дорівнює значенню в клітинці В1. Інакше повертає значення FALSE

Формули можуть містити довільну кількість операторів – як однотипних, так і різних. Послідовність виконання операторів залежить від пріоритету оператора. Найбільший пріоритет має оператор піднесення до степеня (^), а найменший - оператори логічного порівняння (<, >). Виконавши операцію піднесення до степеня у формулі, Ехсеl обчислює вираз в дужках, після чого виконує множення і ділення, потім – додавання і віднімання, і тільки після цього інші операції. Якщо ж формула містить декілька операцій множення і ділення, вони виконуються послідовно, зліва направо. Застосовуючи дужки, можна змінити порядок виконання операцій.

У формулах допускається використовувати тільки круглі ( ) дужки. Якщо ввести фігурні {} чи квадратні [ ] дужки, програма відобразить повідомлення про помилку. У формулах можна також використовувати вкладені дужки. В цьому випадку важливо, щоб кількість відкривних дужок дорівнювала кількості закривних.

В Ехсеl є два способи введення формул в комірки робочого аркуша:

1) ввести оператори та адреси комірок вручну.

Наприклад, потрібно в комірку С3 потрібно ввести формулу =А3+В3.Для цього потрібно розмітити табличний курсор в клітинці С3, ввести символ “=”. Далі з допомогою клавіатури, ввести А3+В3. Під час введення формули посилання на комірки та межі навколо окремих комірок позначаються різними кольорами. Після завершення ведення формули натиснути на Enter або клацнути на кнопці в рядку формул.

2) ввести тільки оператори, а адреси комірок вказати за допомогою миші або клавіш керування курсором.

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

Послідовність дій:

1. Активізувати комірку, до якої треба ввести формулу.

2. Ввести в комірку символ “=”.

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

4. Ввести арифметичний оператор.

5. Клацнути мишею на наступній клітинці, значення якої бере участь у обчисленні.

6. Натиснути на Enter або клацнути на кнопці в рядку формул.

Формула, що набирається в електронній таблиці, відображається у рядку формул. Після команди введення формули (натискання клавіші “=” або клацання по кнопці “ ”) у рядку формул з'являються нові кнопки: і . Клацання по кнопці з червоним хрестиком скасовує внесені до формули зміни, а клацання по кнопці з зеленою «галочкою» дає команду на виконання формули, записаної у комірці. Завершити введення формули можна натисканням на клавішу Enter, або переміщенням курсора до іншої комірки або клацанням по кнопці з зеленою галочкою в рядку формул.

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

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

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

Функції автоматичного обчислення

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

Клацнувши правою кнопкою миші на рядку стану, можна вибрати наступні команди:

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

Количество значений – визначає кількість виокремлених комірок.

Количество чисел - визначає кількість комірок, що містять числа у виокремленому діапазоні.

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

Минимум - визначає найменше значення, що містить комірки у виокремленому діапазоні.

Сумма - задає обчислення суми чисел у виокремленому діапазоні.

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

Абсолютні, відносні та мішані адреси

Зазвичай у формулах Ехсеl використовуються не фіксовані значення, а посилання на комірки, які містять ці значення.

Посилання поділяються на три типи.

• Відносні. Змінюються при копіюванні формул.

• Абсолютні. Не змінюються при копіюванні формул.

• Мішані. Є абсолютними тільки частково.

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

Абсолютні посилання вказують на фіксовану комірку. При копіюванні формул з абсолютними посиланнями адреси комірок в цих посиланнях гне змінюються. Про те, що посилання є абсолютним, вказує символ “$”, розташований біля кожної з координат посилання. Наприклад: $А$1, $В$2, $С$5.

Якщо символ “$” стоїть лише біля одної з координат посилання, тоді одна частина посилання є абсолютною, а інші – відносною. Наприклад, $В5, В$5. Такі посилання називають мішаними. Якщо абсолютною є тільки одна з координат посилання, це посилання завжди буде вказувати на той самий фіксований стовпець або рядок.

Символ “$” можна ввести вручну або переключатися клавішею F4. наприклад, якщо ввести посилання на комірку А1, а потім послідовно натискати F4, програма буде змінювати тип посилання наступним чином: $А$1, А$1, $А1, А1.

Типові помилки при введенні формул

Якщо при введенні формули була допущена помилка, Ехсеl видає у цій клітинці спеціальне повідомлення.

#DIV/0!	У формулі задане ділення на нуль або на порожню комірку.
#NAME?	Програма не може розпізнати у формулі введене ім’я комірки чи діапазону або інший текст.
#N/A	Значення недоступне для формули або функції (наприклад, не вказано один або кілька необхідних аргументів функції)
#NUM!	Введення невірного числового значення (наприклад, число занадто велике або замість додатного введено від’ємне)
#REF!	У формулі неправильно задане посилання на комірку (наприклад, посилання на вилучену комірку)
#VALUE!	У формулі використовується операнд чи аргумент неправильного типу або виконуються арифметичні дії з даними текстового типу
#NULL!	Задане перетинання двох областей, що не перетинаються

Кілька символів “#”, що заповнюють комірку, свідчать про недостатню ширину стовпця для відображення значення, яке міститься в ньому, або про те, що використовуються від’ємні дата і час. Щоб позбутися цієї помилки, потрібно розширити стовпець або змінити числовий формат комірки.

Якщо формула посилається сама на себе (наприклад, якщо в комірку В1 ввести формулу В2‑В1), виникає циклічне посилання, про яке видається повідомлення:

Іноді помилка і одній клітинці призводить до помилок в багатьох інших комірках з формулами, які мають на неї посилання. Для пошуку помилок у формулах можна використовувати команди з меню Сервис/Зависимости, які дозволяють відстежити зв’язки між комірками і формулами шляхом відображення стрілок.

Хід роботи

1. Відкрити документ, створений при виконанні лабораторної роботи 2.

2. Перейти на аркуш Нараховано_вересень.

3. В комірку G2 ввести формулу для обчислення заробітної плати працівника, припустивши, що в місяці 22 робочих дні і врахувавши кількість відпрацьованих днів.

4. Скопіювати формулу за допомогою автозаповнення в комірки G3-G10.

5. Перейти на аркуш Зведена_відомість. В комірку G3 ввести значення з комірки G2 аркуша Нараховано_вересень (відносне посилання) таким чином: активізувати комірку G3, ввести “=”, натиснути вкладку Нараховано_вересень, вказати комірку G2, натиснути Enter.

6. Скопіювати формулу за допомогою автозаповнення в комірки G4-G11.

7. В комірки Н3-Н11 ввести значення авансу: 100 грн.

8. В комірки J3-J11 ввести значення 1% від нарахованої суми: “=G3*1%”.

9. В комірки К3-К11ввести значення 2% від окладу працівника.

10. В комірки L3-L11ввести значення 0,5% від окладу працівника.

11. В комірки М3-М11 ввести значення 0,6 грн.

12. Перед стовпцем Сума_до_видачі, грн. вставити стовпець Всього_вирахувано, грн.

13. В першу комірку стовпця внести формулу для обчислення суми значень, що містяться в комірках J3-M3.

14. Скопіювати цю формулу в комірки N4-N11.

15. Відформатувати таблицю.

16. Зберегти документ.

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

1) Які є арифметичні оператори в програмі Ехсеl?

2) Де можна побачити формулу, введену в комірку?

3) Якими знаками можна починати введення формули в комірку?

4) Для чого використовується оператор “&”?

5) Від чого залежить послідовність виконання операторів в формулах?

6) Які є способи введення формул в комірки робочого аркуша?

7) Як можна ввести одну і ту саму формулу у суміжні комірки?

8) Що таке функції автоматичного обчислення і як вони застосовуються?

9) Що таке абсолютні, відносні та мішані адреси? Як їх розпізнати в рядку формул?

10)Що означає запис “#####” у комірці?

Лабораторна робота 4

Тема. Фіксація заголовків. Додавання приміток. Захист даних в комірках.

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

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

Учні повинні уміти: фіксацувати заголовки, додавати та видіяти примітки, встановлювати захист даних в комірках.

Програмне забезпечення: табличний процесор Microsoft Ехсеl.

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

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

Зафіксувати потрібні області можна так:

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

Виконати команду Окно/Закрепить область.

Закріплені рядки і стовпці позначаються більш темними лініями. Щоб скасувати закріплення областей, потрібно виконати команду Окно/Снять закрепление областей.

Захист даних в комірках

В Ехсеl передбачено кілька рівнів захисту, що дозволяють керувати доступом до даних та їх редагуванням: захист на рівні аркуша, захист на рівні книги і захист окремих елементів таблиці, зокрема комірок, графічних об’єктів і діаграм.

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

За замовчування Ехсеl захищає всі комірки робочого аркуша: Формат ячеек/Защита/Встановити прапорець Защищаемая ячейка.

Однак захист не набере сили, доки не задано захист усього аркуша. Щоб встановити захист аркуша, потрібно виконати такі дії:

Сервис/Защита/Защитить лист. З’явиться діалогове вікно Защита листа, в якому можна ввести пароль для зняття захисту. Довжина пароля не може перевищувати 255 символів, крім цього, розпізнається регістр літер.

Щоб зняти захист робочого аркуша, потрібно виконати команду Сервис/Защита/Снять защиту лист.

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

Виокремити діапазон комірок, які треба залишити доступними для зміни.

Виконати Сервис/Защита/Разрешить измение диапазона. З’явиться діалогове вікно, в якому натиснути кнопку Создать. В цьому діалоговому вікні можна надати ім’я діапазону, уточнити його адресу, а також ввести пароль для доступу до вмісту комірок.

Іменування комірок

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

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

• Іменами можуть бути будь-які комбінації літер і цифр, але імена не повинні починатися з цифри чи нагадувати адреси комірок.

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

• Хоча Excel дозволяє використовувати в іменах до 255 символів, імена повинні бути якомога стислими, але змістовними.

В Excel існує декілька способів іменування комірок та діапазонів.

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

Для призначення імен можна також використовувати команду Вставка/Ім'я/Надати. Після вибору цієї команди Excel розкриє діалогове вікно Надання імені, де потрібно призначити ім’я клітинці або діапазону.

Щоб перейти до якої-небудь комірки чи діапазону робочого аркуша, що мають ім’я, потрібно виконати одну з дій:

1. Розкрити список Имяі клацнути на потрібному імені.

2. Виконати команду Правка/Перейтиі в отриманому діалоговому вікні двічі клацнути на імені комірки або діапазону.

Додавання приміток

Примітки зазвичай створюють, щоб описати вміст комірок. Щоб додати примітку, треба виконати такі дії:

1. Виокремити комірку, до якої потрібно додати примітку.

2. Виконати команду Вставка/Примечание, ввести текст примітки в вікно, що з’явиться. При бажанні можна видалити ім’я користувача в примітці.

3. Клацнути мишею за межами вікна примітки, щоб приховати її.

Комірки з приміткою позначаються індикатором примітки – маленьким червоним трикутником. Щоб прочитати примітку до комірки, потрібно навести на неї вказівник миші. Якщо треба відобразити всі примітки, необхідно вибрати команду Вид/Примечание. Повторний вибір цієї команди приведе до приховання всіх приміток.

Хід роботи

1. Внести в таблицю наступну інформацію:

2. Відформатувати таблицю:

• для стовпця Місто встановити розмір шрифту 14 і використати заливку кольором;

• для стовпця Ціна встановити формат Грошовий, грошова одиниця – євро;

• для стовпця Сума використати заливку кольором, встановити формат Грошовий, грошова одиниця – євро, для стовпця Затрати_на_переїзд встановити формат Грошовий, грошова одиниця – грн.;

• останній рядок таблиці виділити кольором та рамкою.

3. Встановити фіксацію заголовка таблиці.

4. Додати до таблиці примітки:

• до комірки Місто: “Нові регіони продажу”;

• до комірки Дата: “Дата здачі звіту головного адміністратора”;

• до комірки Продано_автомобілей: “З 1 серпня 2002 року”;

• до комірки Затрати_на_переїзд: “Витрати на дорожні витрати менеджерів з продажу та агентів з реклами”.

5. Створити імена: для діапазону, який містить інформацію про кількість проданих автомобілей, присвоїти ім’я Продано; для діапазону, який містить інформацію про ціну автомобіля, присвоїти ім’я Ціна.

6. В стовпець Сума для розрахунку використати дані зі стовпців Продано_автомобілів та Ціна.

7. Використати Автосуму для обчислення кількості проданих автомобілів, загальної суми, та затрат на переїзд.

8. Встановити захист листа від змін даних.

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

1) Для чого використовується фіксація областей робочої таблиці?

2) Як відрізняються закріплені діапазони від незакріплених?

3) Як можна зафіксувати потрібні області ?

4) Які рівні захисту передбачає програма Ехсеl?

5) Як встановити захист аркуша?

6) Для чого коміркам і діапазонам комірок надають імена?

7) Яке призначення приміток?

8) Як додати примітку в потрібну комірку?

Лабораторна робота 5

Тема. Використання математичних функцій

Мета. Дати поняття про функції, структуру функції. Навчити використовувати математичні функції та вводити їх в комірки різними способами. Закріплювати навички роботи на комп’ютері.

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

Учні повинні уміти: використовувати математичні функцій, вводити функції в комірки.

Програмне забезпечення: табличний процесор Microsoft Ехсеl.

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

Функція – це створена заздалегідь формула, що виконує певні обчислення.

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

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

Структура функції

Введення функції починається зі знаків “=”, “+” або “”. Усі функції програми Ехсеl складаються з двох основних частин – імені функції і аргументів. Аргументи функцій задаються в круглих дужках, що йдуть безпосередньо за ім’ям функцій. Функція може мати один, декілька чи не мати жодного аргументу. Аргументом може бути діапазон комірок, наприклад функція =SUM(A1:A5) знаходить суму значень, які містяться в комірках діапазону A1:A5. Якщо функція має декілька аргументів, вони відокремлюються один від одного крапкою з комою, наприклад функція =SUM(A1:A5; В1:В5; С1:С5) підсумовує усі значення, що міститься в зазначених діапазонах. Наявність круглих дужок під час уведення функції є обов’язковою, навіть якщо функція не має аргументів.

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

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