asyan.org
добавить свой файл
1
Інструкційна карта № 16
Тема 4: Технологія комп’ютерної обробки інформації
Тема уроку: Створення формул. введення формул. робота з майстром функцій.
Мета уроку: Формування умінь та навиків обчислення за допомогою формул у середовищі табличного процесора; навчитися використовувати стандартні функції.
Обладнання, програмне забезпечення та інші матеріали: Комп’ютер, інструкції з ТБ в комп’ютерній лабораторії, Windows XP, Microsoft Excel 2003,
Завдання:

1. Скласти таблицю множення від 1 до 9.

2. Створити та заповнити таблицю. В комірки з знаком «?» ввести необхідні формули. Врахувати, що Вартість= Ціна*Кількість. Відформатувати за зразком.



3. Розрахуйте математичний вираз , дані комірки В1 – аргумент х, х=4,

комірки В2-y, y=3.




4. Відредагуйте таблицю «Список працівників фірми «Арекс» за зразком:


5. Для таблиці «Список працівників фірми «Арекс», виконати обчислення посадового окладу;

6. Підрахувати розмір надбавки за знання іно­земної мови.

^ Розмір Надбавки за володіння іноземною мовою (грн) у розмірі 25 % *посадовий оклад (за умови володіння іноземною мовою)

7. Виконати обчислення загальної суми виплат. (сума посадового окладу + надбавка за володіння іноземною)

^ 8. Обчислити стаж роботи працівників в організації (в роках);

9. Розрахувати середнє, максимальне та мінімальне значення для стовпців Посадовий оклад, Стаж, До сплати.

10. Підрахувати Загальну суму до сплати.

Результати обчислень вписати в таблицю.

^ 11. Зберегти зміни в робочій книзі pr3_прізвище.xls
Інструкції до завдання:
1. Для створення таблиці множення завантажуємо табличний процесор MS Excel.

  • вводимо послідовність чисел від 1 до 9 у комірки В2:J2 і А3:А11 за допомогою автозаповнення.

  • у клітинку В3 вводимо за допомого клавіатури формулу =$A3*B$2 і копіюємо її в комірки C3:J3



Примітка:Формулу можна вводити, як в комірку, так і в рядок формул



  • Виділяємо комірки В3: J3 і виконуємо автозаповнення формулами в комірки В4: J11



  • Форматуємо та отримуємо таблицю множення.

  • Зберігаємо документ з назвою zd1_прізвище.xls в своїй папці. Файл/Сохранить как

^ 2. Створюємо та заповнюємо таблицю «Рахунок» Для цього:

  • в робочій книзі zd1_прізвище.xls переходимо на Аркуш 2.

  • вводимо в комірку Е3 формулу =C3*D3;

  • копіюємо формулу в комірки Е4:Е7 (виділяємо комірку Е3, натискаємо на кнопку «копіювати» , виділяємо комірки Е4:Е7, натискаємо на кнопку «вставити» );

  • в комірку D8 вводимо формулу для розрахунку суми чисел в стовпчику (виділяємо комірку D8; натискаємо на кнопку «Автосума» на панелі інструментів Стандартная (діапазон сумування включається автоматично: він включає всі комірки які розташовані над (або ліворуч) поточної лунки та утворює безперервний діапазон.

^ Якщо в формулі буде вказано потрібний діапазон СУММ(D3:D6), то натискаємо «Enter»);

  • аналогічно в комірку Е8 вводимо формулу для розрахунку суми чисел в стовпчику Е.

^ 3. Для розрахунку математичного виразу у комірку В1 вводимо число 4, у комірку В2 – число 3, в комірку В3 вводимо формулу. Якщо В1 – аргумент х, а В2 – y, то формула буде мати вигляд: =(В1-2)/(5+(2*В1)/(В2^2+3))



^ 4. Відкриваємо робочу книгу pr2_прізвище.xls і зберігаємо її з ім’ям pr3_прізвище.xls редагуємо таблицю «Список працівників фірми «Арекс» за поданим вище зразком.

5. Вводимо відповідні дані (суми посадових окладів, (грн.), (okl)): начальник відділу (в комірки Е3, Е8)— 1500 грн; аудитор (в комірки Е7, Е10, Е11) — 1400 грн; консуль­тант (в комірки Е4:Е6, Е9)— 1200 грн.



^ 6. В комірку J3 вводимо формулу =(Е3*25)/100, натискаємо Enter. Далі через контекстне меню копіюємо формулу і вставляємо її в комірку J6, J8, методом автозаповнення копіюємо формулу до суміжної комірки J9.



7. У комірку К3 вводимо формулу =Е3+J3, методом автозаповнення копіюємо формулу в комірки J4: J11


8. Формула підрахунку стажу роботи повинна мати наступний вигляд: =СЕГОДНЯ()-F3.

Для цього:

  • виділяємо комірку Н3;

  • виконуємо команду Вставка/Функция або натискаємо кнопку Вставка функции на панелі інструментів Стандартная;

  • у відкритому вікні Мастер функций в полі Категория вибираємо категорію функції Дата и время

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


  • в полі категорії вибираємо функцію СЕГОДНЯ() і натискаємо OK, ім'я функції внесено до рядку формул, а палітра функцій змінює свій вигляд;



  • натисненням ОК закриваємо вікно функції;

  • розрахунок завершено;

  • методом автозаповнення розмножуємо формулу на весь діапазон комірок (Н4:Н7).

Примітка: для діапазону комірок задаємо формат Дата, рр.

^ 9. Для розрахунку середнього значення потрібно скористатися функцією СРЗНАЧ, яка належить до категорії Статистические, за допомогою Майстра функцій.

Формула повинна мати наступний вигляд =СРЗНАЧ(E3:E11)

Для цього:

  • встановлюємо курсор в комірку Е12,

  • викликаємо Майстра функцій





  • у відкритому вікні Мастер функций в полі Категория вибираємо категорію функції Статистические/ СРЗНАЧ або (AVERAGE)

















  • Згортаємо вікно клацанням на кнопці праворуч поля;

  • виділяємо діапазон клітинок Е3:Е11

  • розгортаємо вікно натисканням кнопки

  • натискаємо кнопку ОК – вікно закрито, розрахунок завершено.

Аналогічно розраховуємо середнє значення для стовпців Стаж, До сплати.

  • Для обчислення мінімального значення встановлюємо курсор в комірку ^ Н13, в діалоговому вікні Вставка функций вибираємо категорію Статистические, в списку функцій відшукуємо функцію MIN (МИН), натискаємо ОК. У вікні Аргумент функции вводимо діапазон комірок Н3:Н11. Аналогічно розраховуємо мінімальне значення для стовпців Посадовий оклад та стовпця До сплати.

- Для обчислення максимального значення встановлюємо курсор в комірку К14, в діалоговому вікні ^ Вставка функций вибираємо категорію Статистические, в списку функцій відшукуємо функцію MAX (МАКС), натискаємо ОК. У вікні Аргумент функции вводимо діапазон комірок К3:К11. Аналогічно розраховуємо мінімальне значення для стовпців Посадовий оклад та Стаж.

10. Підраховуємо Загальну суму (Усього) до сплати. Для цього:

  • виділяємо комірку К15;

  • викликаємо Майстра функцій за допомогою команди Вставка /функция

  • з категорії Математические або 10 недавно использовавшихся вибираємо функцію СУММ або (SUM)

  • у відкритому вікні функції в полі Число1 натискаємо на кнопку , вікно приймає згорнутий вигляд;

  • виділяємо діапазон комірок, які сумуються К3:К11 – діапазон внесено в поле вікна.

  • Натискаємо кнопку для розгорнення вікна, ОК – розрахунок завершено

  • Для перевірки формули знову виділяємо комірку К15 в рядку формул формула повинна мати вигляд = СУММ (К3:К11) або =SUM(К3:К11)

11. Зберігаємо зміни в робочій книзі pr3_прізвище.xls Файл/Сохранить або натискаємо на кнопку на панелі інструментів Стандартная

Перелік завдань для самостійної роботи:
Завдання І
Створити та заповнити таблицю. В комірки з знаком «?» ввести необхідні формули. Врахувати, що «Вартість, грн.»=«Вартість, $»*«Курс».



Завдання ІІ

Створити та заповнити таблицю. В комірки з знаком «?» ввести необхідні формули та функції, враховуючи, що:

  • вартість дорівнює добутку ціни на кількість;

  • знижка обраховується за правилом: якщо вартість більша 1000, то знижка становить 20% від вартості, інакше – 5% від вартості;

  • сума дорівнює різниці вартості і знижки.

  1. Створити таблицю та заповнити її відомими значеннями. Врахувати формати даних.




  1. Ввести формули в стовпчик Е («Вартість»).

  2. В комірку F3 ввести функцію ЕСЛИ (IF):

    1. запустити майстер функцій кнопкою «f(x)»;

    2. на першому кроці вибрати категорію «нещодавно використані» або «логічні»;

    3. вибрати функцію ЕСЛИ (IF) та натиснути «Ок»;

    4. в рядок «логічний вираз» ввести «Е3>1000», в рядок «значення_якщо_істина» ввести «Е3*0,2», в рядок «значення_ якщо_хибність» ввести «Е3*0,05».

  3. Скопіювати формулу з комірки F3 в діапазон F4:F8.

  4. Ввести формули в стовпчик G («Сума»).

  5. Ввести формули в рядок 9 («Разом»).

Завдання ІІІ
Скласти табличний документ для розвязку задачі.

Умова. Обчислити обсяг незавершеного виробництва за даними, приведеними нижче. Величина незавершеного виробництва у вартісному вираженні визначається з допомогою формули



де N - кількість виробів у натуральних одиницях, С - собівартість одного виробу, Тц - тривалість циклу виготовлення одного виробу, Кнз - коефіціент наростання затрат під час виготовлення виробу, Д - кількість робочих днів у році. Протабулювати величину незавершеного виробництва в залежності від собівартості одного виробу, якщо остання буде змінюватися на 10 грн. (з кроком в 2,5 грн.) відносно заданого в умові значе6ння. Побудувати графік і діаграму зміни величини незавершеного воробництва в залежності від зміни собівартрості одного виробу.
Питання для самоконтролю:


  1. Які елементи може містити формула?

  2. Які посилання можуть бути у формулах?

  3. Як вводиться формула в Microsoft Excel?

  4. Назвіть пріоритети виконання операцій у формулах.

  5. Які повідомлення можуть бути виведені у разі наявності помилки у формулі?

  6. Яким чином можна скопіювати формулу?

  7. Для чого використовуються у формулах абсолютна, відносна, змішана адреси?

  1. Для чого використовується кнопка Автосумма стандартної панелі інструментів?

  2. Як викликати Мастер функций?

Додаток до інструкційної карти 2

^ ПРИКЛАДИ ФУНКЦІЙ MS EXCEL:


Функція

Призначення

АВS (аргумент)

Модуль аргументу

AVERAGE (список) (СРЗНАЧ)

Середнє значення списку

COS (аргумент)

Косинус аргументу у радіанах

СОUNТ(список) (СЧЕТ)

Кількість непорожніх комірок списку

СОUNTIF (список,критерій) (СЧЕТЕСЛИ)

Кількість комірок із списку, котрі задовольняють умову критерію

DAY(чч.мм.pp) (ДЕНЬ)

День місяця із дати

ЕХР (аргумент)

Експонента аргументу

EXP (l)

Значення числа «е»

FACT (аргумент)

Факторіал аргументу (добуток всіх натуральних чисел від 1 до значення аргументу)

INT (аргумент)

Ціла частина аргументу

IF (умова;оператор1;оператор2) (ЕСЛИ)

оператор1, якщо умова виконується

оператор2, якщо умова не виконується

IF(AND(умова 1;умова 2); оператор1;

оператор2) ЕСЛИ(И(... ;...);...;...)

оператор1, якщо виконується

одночасно умова1 і умова2;

оператор2 врешті випадків

IF(OR(умова1;умова2); оператор1;

оператор2)

если(или(... ;...);...;...)

оператор, якщо виконується хоч одна

з умов 1 чи 2;

оператор2 в решті випадків

IF(AND(умова1;OR(умова2; умова3));оператор1; оператор2)

оператор1, якщо виконується і умова1 і хоч

одна з умов2 чи 3;

оператор2 врешті випадків

LN(аргумент)

Натуральний логарифм аргументу

LOG 10(аргумент)

Десятковий логарифм аргументу

LOG (аргумент1 ;аргумент2)

Логарифм аргументу 1 при основі аргумент2

МАХ (список) (МАКС)

Максимальне значення списку

МIN (список) (МИН)

Мінімальне значення списку

МОNТН (чч.мм.рр)

Номер місяця із дати

МОD (аргумент1;аргумент2) (ОСТАТ)

Залишок від ділення аргументу 1 на аргумент2

РI()

Значення числа пі

PRODUCT (список) (ПРОИЗВЕД)

Добуток комірок із списку

SIN(аргумент)

Синус аргументу у радіанах

SQRT(аргумент) (КОРЕНЬ)

Квадратний корінь аргументу

SUM (список) (СУММ)

Сумування комірок із списку

SUMIF (список;критерій) (СУММЕСЛИ)

Сумування комірок із списку, котрі задовольняють умову критерію

SUMPRODUCT(список1; список2)

Сума добутків відповідних елементів списку 1 та списку2

SUMSQ(список) (СУММКВ)

Сума квадратів елементів списку

TAN (аргумент)

Тангенс аргументу у радіанах

^ TODAY() (СЕГОДНЯ )

Сьогоднішня дата у форматі дати

YEAR(чч.мм.pp)

Значення року із дати


^ ТИПИ ОПЕРАТОРІВ

Тип

Оператори

Арифметичний

+ (додавання), — (віднімання);* (множення), / (ділення),

% (відсоток), ^ (піднесення до степеня)

Текстовий

& (амперсанд) — об'єднання декількох текстових рядків в один

Оператори відношень

> (більше), > = (не менше), < (менше), < = (не більше),

= (дорівнює), <> (не дорівнює)

Оператори посилань

: (двокрапка) ставиться між посиланнями на першу й останню клітинки діапазону,

; (крапка з комою) — об'єднує декілька посилань в одне посилання, (пропуск) — оператор перерізу множин, служить для посилання

на загальні клітинки двох діапазонів



^ ЗНАЧЕННЯ ПОМИЛОК

Код помилки

Причини виникнення помилки

#:# #:#-

ширина лунки не дозволяє відобразити число в заданому форматі;

#ИМЯ?

програма не змогла розпізнати ім'я, використане з формулі

#ДЕЛ/0!

у формулі робиться спроба поділу на нуль

#ЧИСЛО!

порушення правила задавання операторів, прийшлі в математиці

#Н/Д

таке повідомлення може з'явитися, якщо в якості аргументу задане посилання на порожню лунку;

#ПУСТО!

зазначене перетинання двох областей, що не мають загальних лунок

#ССЬІЛКА!

у формулі задане посилання на неіснуючу лунку

#ЗНАЧ!

використаний неприпустимий тип аргумент



^ ПОРЯДОК ВИКОНАННЯ ДІЙ

Якщо в одній формулі використовується декілька операторів, то Microsoft Excel виконує операції в такому порядку.





^ Формат оператора

Опис



: (двокрапка) , (кома)

(один пробіл)

Оператори посилань



- (унарний мінус)

Унарний мінус



%

Відсоток



^

Піднесення до сте­пеня




* i /

Множення і ділення




+ і -

Додавання і відні­мання




&

Об'єднання двох тек­стових рядків в один




>, > = , <, < = , = , <>

Порівняння

Примітка: Якщо формула містить оператори з однако­вим пріоритетом, наприклад оператори ділення і множення, вони виконуються зліва направо. Для того щоб змінити порядок виконання, час­тину формули, яка має виконуватися першою, беруть у дужки.