Практична робота 2:"Табличний процесор Excel"

ПРАКТИЧНА РОБОТА № 2.

 

 Тема:   Робота з формулами в MS Excel 2000/XP. Використання умовного форматування. Робота з майстром функцій.

 

 Ціль:  Ознайомитися з формулами в MS Excel і здобути основні навички роботи з майстром функцій та умовним форматуванням.

 

МЕТОДИЧНІ ВКАЗІВКИ.

 

2.1. Формули робочого листа

 

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

У рядку редагування формули є три кнопки: щиглик по [v] підтверджує внесені у формулу зміни, по [x] – скасовує, а кнопка [fx] дозволяє вставити функцію MS Excel. Вставка у формулу пробілів і розривів рядка не впливає на результат обчислень, однак не можна відокремлювати ім'я книги від імені листа й комірки. Розрив рядка вводиться комбінацією <Alt+Enter>. Для того, щоб видалити з комірки формулу, залишивши тільки повернуте їй значення, потрібно вибрати комірку, а потім послідовно натиснути <F2>, <F9> та <Enter>.

 

У формулах припустимі наступні операції:

 

+ Додавання

- Вирахування або знак мінус

* Множення

/ Ділення

^ Зведення в ступінь

& Конкатенація (тобто зчеплення рядків)

=  Логічне порівняння: дорівнює

< Логічне порівняння: більше ніж

> Логічне порівняння: менше ніж

<> Логічне порівняння: не дорівнює

>= Логічне порівняння: не менше ніж

<= Логічне порівняння: не більше ніж

Наведемо приклади формул робочого листа й виконувані формулами дії:

 

=3*2    Множить три на два

=А1+А2   Складає вміст комірок A1 й А2

= А2^2/3   Зводить вміст комірки А2 у ступінь 2/3

=СУММ(А1:А4)  Повертає суму значень із діапазону комірок А1:А4

(використовується вбудована функція СУММ).

=А1>А2 Повертає значення ИСТИНА, якщо вміст комірки А1 більше вмісту комірки А2, і повертає ЛОЖЬ в противному випадку

=Al&B1 Конкатенація (з'єднання) двох текстових рядків з комірок А1 й В1 (якщо в комірці A1 введене – Иван, а в B1 – Сидоров, то формула поверне значення ИванСидоров).

 

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

 

2.2. Помилки, що повертаються формулами

 

Іноді формула повертає не число або рядок тексту, а повідомлення, що починається із символу #, після якого йде спеціальний текст. Так MS Excel інформує користувача про те, що формула повертає помилку. У цьому випадку треба скорегувати або саму формулу, або значення в тих комірках, на які формула посилається. Перелічимо список помилок, які можуть бути повернуті формулами робочого листа:

 

#ПУСТО! Використано помилкового  оператора  перетинання діапазонів або помилкове посилання на комірку.

#ДЕЛ/0! Як дільник використовується посилання на комірку, у якій міститься нульове або порожнє значення (якщо посиланням є порожня комірка, той її вміст інтерпретується як нуль).

#ЗНАЧ!             1) Замість   числового   або   логічного   значення   введений   текст, і MS Excel не може перетворити його до потрібного типу даних; 2) Введення формули масиву завершено натисканням клавіші <Enter>, а не комбінацією клавіш <Ctrl>+<Shift>+<Enter>; 3) Використана неправильна матриця в одній з матричних функцій листа.

#ССЫЛКА! Посилання на неіснуючу комірку.

#ИМЯ? Помилка в написанні імені або використовується неіснуюче ім'я.

#ЧИСЛО! 1) Функція із числовим аргументом використовує неприйнятний аргумент; 2) Формула повертає числове значення, що занадто велике або занадто мале, щоб його можна було представити в MS Excel.

#Н/Д Значення помилки #Н/Д є скороченням терміна «невизначені дані» або «немає даних». Це значення допомагає запобігти використанню посилання на порожню комірку.

 

 

2.3. Абсолютна і відносна адресація, зовнішні та тривимірні посилання

 

У формулах використовуються відносні, абсолютні й змішані посилання. Відносне посилання - це посилання, що автоматично змінюється при зміні адреси комірки й позначається простим зазначенням відповідних рядків і стовпців, наприклад А10, С11 і т.д. Вона використовується за замовчуванням. Наприклад, якщо обчислюється вираз СУММ(С1:С10), то при переміщенні комірок у діапазон D1:D10, формула автоматично зміниться на СУММ(D1:D10).

Абсолютне посилання - це посилання, що не змінюється при змінах адреси комірки. Абсолютне посилання позначається знаком $, що встановлюється перед ім'ям стовпця і номером рядка, наприклад - $A$1.

Змішане посилання - це посилання, що є сполученням абсолютної і відносної, вона закріплює адресу комірки по одному параметру (стовпцю або рядку), і змінює його по іншому. Частина адреси, що закріплюється, позначається знаком $. Наприклад, якщо обчислюється вираз =СУММ($C1:$C10), те при переміщенні комірок у діапазон D10:D20 формула автоматично зміниться на =СУММ($C10:$C20).

Посилання на  комірки, що знаходяться в інших книгах, називаються зовнішніми. Зовнішнє посилання організується завданням імені книги, листа та комірки: [Книга1]Лист1!A1.

MS Excel дозволяє у формулах використовувати тривимірні посилання на комірки та діапазони, тобто об'єднати в одному посиланні кілька комірок або діапазонів комірок, розташованих на різних робочих листах.

Як приклад використання тривимірного посилання наведемо наступну формулу, що повертає суму значень із діапазонів, розташованих у робочих листах Лист1, Лист2 та Лист3:

 

=СУММ(Лист1!:Лист3!А1:В2)

 

 

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

 

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

 

 

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

 

=COS((ПИ)*A1)^2 .

 

1.    Виберіть комірку В1.

2. Натисніть кнопку Вставка функции на панелі інструментів Стандартная або виберіть команду Вставка -> Функция. На екрані відобразиться діалогове вікно Мастер функций. Для MS Excel 2000 воно складається із двох частин (мал. 2.1.): ліва - Категория зі списком дванадцятьох категорій функцій; права - Функция зі списком імен функцій, що входять в обрану категорію.

Для MS Excel XP вікно буде трохи відрізнятися наявністю кнопки Найти й вікна для введення опису шуканої функції. Категорія Полный алфавитный перечень містить всі вбудовані функції та їхні імена впорядковані за алфавітом. Категорія 10 недавно использовавшихся містить імена десяти останніх застосованих функцій.

3.  Функція  COS  відноситься   до  категорії   Математические.   Виберіть категорію й функцію, а потім натисніть кнопку OK. Після цього на екрані відобразиться панель формул і поле введення.

 

 

 

Мал. 2.1. Робота з майстром функцій в MS Excel

 

 

Мал. 2.2. Вікно введення аргументів функції в MS Excel

 

В поле Число вводиться аргумент функції - у розглянутому прикладі це ПИ()*А1. За допомогою клавіатури в це поле введіть тільки ПИ()*, а посилання на комірку A1 у формулу додайте, клацнувши по комірці A1 на робочому листі. Після натискання кнопки ОК в комірку B1 буде введена формула: =COS(ПИ()*A1).

4. За допомогою клавіатури додайте у формулу =COS(ПИ()*A1) операцію зведення функції у квадрат. Після всіх описаних дій у комірці В1 повинна з'явитися формула:  =COS(ПІ()*A1)^2.

 

2.5. Умовне форматування

 

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

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

Для виконання умовного форматування спочатку виберіть комірки, які необхідно виділити, а потім виберіть меню Формат -> Условное форматирование (мал. 2.3)

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

В якості критерію форматування для оцінки даних або умов можна використати формули. Ліворуч із поля виберіть параметр Формула, а потім у поле праворуч введіть формулу. Формула повинна приймати логічне значення ИСТИНА або ЛОЖЬ.

Можна задати до трьох умов. Завдання декількох умов провадиться натисканням кнопки А также. Якщо жодна із заданих умов не набуває правдивого значення, то формат комірок залишається попереднім. Потім натисніть кнопку Формат, виберіть тип шрифту, його кольор, підкреслення, рамку та затінення комірок (мал. 2.4).

Вибрані формати будуть застосовані, тільки якщо значення комірки відповідає поставленій умові, або якщо формула приймає значення ИСТИНА. Видалення умовного форматування або окремих його умов для виділених комірок робиться натисканням кнопки Удалить.

 

 

 

Мал. 2.3. Застосування умовного форматування

 

 

Мал. 2.4. Завдання декількох умов форматування

 

 

ПРАКТИЧНЕ ЗАВДАННЯ.

 

 2.1. Створити новий лист MS Excel, за допомогою майстра функцій в комірку B4 ввести формули відповідно до варіанта:

 

 Варіант 1 : sin ;

 Варіант 2 : cos (2x+1);

 Варіант 3 : arctg 3x;

 Варіант 4 : cos 2x+1;

 Варіант 5 : 3ctg x;

 Варіант 6 : lg (-x);

 Варіант 7 : ;

 Варіант 8 : ; 

 Варіант 9 : |6x+3|;

 Варіант 10 :  sin(cos x).

 

В комірку B3 ввести значення x = - (номер за журналом).

 

 

2.2. У рядок 1 ввести ряд натуральних чисел 1...15 і зробити виділення комірок зі значенням відповідно до варіанта:

 

 Варіант 1 : більше 6 і менше 9;

 Варіант 2 :  менше 2 або більше чи дорівнює 14;

 Варіант 3 :  більше чи дорівнює 4 й  менше 10;

 Варіант 4 :  менше 13 і не дорівнює 5;

 Варіант 5 :  менше 7 або більше 12, а також рівне 10;

 Варіант 6 :  більше чи дорівнює 13 і не дорівнює 15;

 Варіант 7 :  дорівнює 1, чи 3, чи 5;

 Варіант 8 :  більше чи дорівнює 5 або дорівнює 8;

 Варіант 9 :  не дорівнює 7 ,8, 9, 10;

 Варіант 10 :  дорівнює 1, 2, 3 або 5, 6, 7.

 

 

КОНТРОЛЬНІ ПИТАННЯ.

 

1. Що називається формулою в MS Excel?
2. Чи впливає вставка у формулу пробілів та розривів рядка на результат обчислень?
3. Як видалити з комірки формулу, залишивши повернуте їй значення?
4. Які види помилкових значень, повертаємих формулами, ви знаєте? Коли вони виникають?
5. Вкажіть різницю між абсолютною, відносною та змішаною адресацією.
6. Що таке тривімірне та зовнішнє посилання? Наведіть прикдади.
7. Для чого використовується майстер функцій?
8. Що таке умовне форматування? Коли його слід викоритовувати?
9. Скільки умов можна задати одночасно при умовному форматуванні комірки?