Використання формул і функцій у MS Excel
Лабораторна робота № 10
Тема: Використання формул і функцій у MS Excel
Мета: сформувати вміння та навички щодо роботи з формулами; абсолютними, відносними ти мішаними посиланнями; закріпити на практиці правила запису формул і перетворення абсолютних, відносних та мішаних посилань під час копіювання формул; розвивати інформаційну компетентність.
Обладнання: ПЕОМ IBM PC; ОС Windows; Microsoft Office 2013; Microsoft Excel.
Теоретичні відомості
Структура формули
Формули – це вирази, по яких виконуються обчислення. Формула завжди починається із знаку дорівнює (=). Формула може включати функції, посилання на комірки або імена, оператори і константи.
Наприклад, у формулі =СУММ(В2:В8)*30
СУММ() – функція;
В2 і В8 – посилання на комірки;
: (двокрапка) і * (зірочка) – оператори; 30 – константа.
Функції – заздалегідь визначені формули, які виконують обчислення по заданих величинах, що називаються аргументами, і у вказаному порядку. Структура функції: ім'я функції, відкриваюча дужка, список аргументів, розділених крапками з комою, закриваюча дужка. Аргументом функції може бути число, текст, логічне значення, масив, значення помилки, посилання на комірку. Як аргументи використовуються також константи, формули, або функції.
Посилання вказує на комірку або діапазон комірок аркуша, які потрібно використовувати у формулі. Можна задавати посилання на комірки інших листів поточної книги і на інші книги. Посилання на комірки інших книг називаються зв'язками.
Оператором називають знак або символ, що задає тип обчислення у формулі. Існують математичні, логічні оператори, оператори порівняння та посилання.
Константою називають постійне (не обчислюване) значення. Формула і результат обчислення формули не є константами.
Оператори
Арифметичні оператори.
Арифметичні оператори служать для виконання арифметичних операцій, таких як додавання, віднімання, множення. Операції виконуються над числами.
Використовуються наступні арифметичні оператори:
|
Оператор |
Значення |
Приклад |
|
+ (знак плюс) |
Додавання |
A1+A2 |
|
– (знак мінус) |
Віднімання |
A1‐A2 |
|
Заперечення |
‐A1 |
|
|
* (зірочка) |
Множення |
A1*A2 |
|
/ (коса риска) |
Ділення |
A1/A2 |
|
% (знак відсотка) |
Відсоток |
50% |
|
^ (кришка) |
Піднесення до ступеня |
A1^2 |
Оператори порівняння
Оператори порівняння використовуються для порівняння двох значень. Результатом порівняння є логічне значення: або ІСТИННЕ, або ХИБНЕ.
|
Оператор |
Значення |
Приклад |
|
= (знак рівності) |
Рівно |
(A1=B1) |
|
> (знак більший) |
Більше |
(A1>B1) |
|
< (знак менший) |
Менше |
(A1<B1) |
|
>= (знак більше і рівно) |
Більше або рівно |
(A1>=B1) |
|
<= (знак менше і рівно) |
Менше або рівно |
(A1<=B1) |
|
<> (знак менший і більший) |
Не рівно |
(A1<>B1) |
Текстовий оператор конкатенації
Текстовий оператор амперсанд (&) використовується для об'єднання декількох текстових значень.
|
Оператор |
Значення |
Приклад |
|
& (амперсанд) |
Об'єднання послідовностей знаків в одну послідовність |
"Прізвище"&"Ім’я"&"По- батькові" |
Оператори посилань
Оператори посилань використовують для опису посилань на діапазони комірок.
|
Оператор |
Значення |
Приклад |
|
: (двокрапка) |
Ставиться між посиланнями на першу і останню комірку діапазону |
B5:B15 |
|
;(крапка з комою) |
Оператор об'єднання |
B5:B15;D5:D15 |
|
(пропуск) |
Оператор перетину множин, служить для посилання на загальні комірки двох діапазонів |
B7:D7 C6:C8 |
Створення і редагування формул
Введення формул з клавіатури
Формули можна вводити з використанням клавіатури і миші при роботі в будь‐якій вкладці Excel. З використанням клавіатури вводять оператори (знаки дій), константи, дужки та, інколи, функції. З використанням миші виділяють комірку і діапазони комірок, що включаються у формулу:
-
Виділіть комірку, в яку потрібно ввести формулу.
- Введіть = (знак рівності).
- Виділіть мишею комірку, що є аргументом формули.
- Введіть знак оператора.
- Виділіть мишею комірку, що є другим аргументом формули.
- При необхідності продовжуйте введення знаків операторів і виділення комірок.
- Підтвердіть введення формули у комірку: натисніть клавішу Enter або Tab або кнопку
Введення (галочка) в рядку формул.
Наприклад, необхідно створити формулу для розрахунку вартості декількох книг у комірці D2
таблиці на малюнку:
- виділіть комірку D2
- введіть знак =,
- клацніть мишею по комірці В2
- введіть знак *,
- клацніть мишею по комірці С2
- натисніть клавішу Enter.
При введенні з клавіатури формула відображується як в рядку формул, так і безпосередньо у комірці (див. мал.). Комірки, які використовуються у формулі, виділені кольоровою рамкою, а посилання на ці комірки у формулі шрифтом того ж кольору.
Примітка: Для обчислення вартості інших книг в стовбці D слід скористатися автозаповненням.
Створення формул з використанням майстра функцій
Функція – стандартна формула, яка забезпечує виконання певних дій над значеннями, що виступають аргументами. Функції дозволяють спростити формули, особливо якщо вони довгі або складні. Функції використовують не лише для безпосередніх обчислень, але також і для перетворення чисел, наприклад для округлення, для пошуку значень, порівняння і так далі.
Для створення формул з функціями зазвичай використовують групу Бібліотека функцій вкладки Формули.
- Виділіть комірку, в яку потрібно ввести формулу.
- Клацніть по кнопці потрібної категорії функцій в групі Бібліотека функцій, виберіть потрібну функцію.
-
У вікні Аргументи функції у відповідному полі (полях) введіть аргументи функції. Посилання на комірки можна вводити з клавіатури, але зручніше використовувати виділенням комірок мишею. Для цього поставте курсор у відповідне поле і на аркуші виділіть необхідну комірку або діапазон комірок. Для зручності виділення комірок вікно Аргументи функції можна пересунути або згорнути. Текст, числа і логічні вирази як аргументи зазвичай вводять з клавіатури. Як підказка у вікні відображується призначення функції, а в нижній частині
вікна відображується опис аргументу, в полі якого в даний момент знаходиться курсор. Слід мати на увазі, що деякі функції не мають аргументів.
- У вікні Аргументи функції натискуйте кнопку ОК.
Наприклад, в таблиці на малюнку у комірці А6 необхідно створити формулу для округлення до двох знаків після коми значення у комірці А4:
- виділіть комірку А6;
- клацніть по кнопці категорії Математичні в групі Бібліотека функцій і виберіть функцію ОКРУГЛ;
- у вікні Аргументи функції (мал. нижче) встановіть курсор в полі Число і на аркуші виділіть комірку А4, потім встановіть курсор в полі Число розрядів і з клавіатури введіть число 2;
-
натисніть кнопку ОК.
Для вставки функції не обов'язково використовувати кнопки категорій функцій в групі Бібліотека функцій. Для вибору потрібної функції можна використовувати майстер функцій. Причому, це можна зробити при роботі в будь‐якій вкладці:
- Натисніть кнопку Вставити функцію в рядку формул.
- У вікні Майстер функцій: крок 1 з 2 в списку Категорія виберіть категорію функції, потім в списку виберіть функцію.
- Натискуйте кнопку ОК або двічі клацніть мишею по назві вибраної функції.
- У вікні, що з'явилося, Аргументи функції так само, як і у попередньому випадку введіть аргументи функції. Натисніть кнопку ОК.
Деякі функції Excel
Excel має наступні стандартні функції:
- фінансові;
- дата і часу;
- математичні;
- статистичні;
- масиви;
- робота з базою даних;
- текстові;
- логічні;
- перевірка властивостей і значень;
- інженерні;
- аналітичні.
Наведемо список найуживаніших функцій:
Математичні функції:
ABS(число) повертає абсолютне значення числа. Наприклад, вираз ABS(‐2) дає число 2.
КОРЕНЬ(число) повертає додатне число квадратного кореня числа. Якщо число від‘ємне, то на екран виведеться повідомлення про помилку.
СТЕПІНЬ(число; показник степеня) підносить число до вказаного степеня.
ОКРУГЛ(число; кількість рядків) округлює число до вказаного значення кількості десяткових розрядів.
ОКРУГЛВВЕРХ(х; точність) повертає результат округлення з надлишком до найближчого числа, що кратне точності. Наприклад, вираз ОКРУГЛВВЕРХ (2.2552; 1) = 3.
ОКРУГЛВНИЗ(х; точність) – це округлення з недостачею. Ця функція подібна до ОКРУГЛВВЕРХ. Наприклад, ОКРУГЛВНИЗ (2,2556; 1)=2.
ПИ() виводить на екран число Пі з точністю до 15 чисел. РАДИАНЫ(кут в градусах) обчислює значення кута в радіанах. SIN(кут в радіанах чи в градусах) знаходить значення синуса кута. COS(кут в радіанах чи в градусах) знаходить значення синуса кута. EXP(число). Повертає число e, піднесене до вказаного степеня.
ПРОИЗВЕД(число1;число2;...).Перемножує від 1 до 255 чисел, логічних значень чи чисел представлених в текстовому вигляді..
СУММ(число1;число2; ...). Знаходить суму— від 1 до 255 аргументів.
СУММЕСЛИ(діапазон; критерій; діапазон сумування). Слугує для підсумовування значень у діапазоні, які відповідають вказаній умові. Припустімо, наприклад, що стовпець містить числа, і потрібно знайти суму чисел, більших за 5. Для цього можна скористатися такою формулою:
=SUMIF(B2:B25;">5"). Проте також можна застосувати умову до одного діапазону, а підсумувати відповідні значення в іншому діапазоні.
Статистичні функції:
СРЗНАЧ(число1;число2; ...). Повертає середнє арифметичне аргументів (від 1 до 255
аргументів).
СРЗНАЧА(число1;число2; ...). Повертає середнє арифметичне аргументів, враховуючи числа, текст і логічні значення (від 1 до 255 аргументів).
МАКС(число1;число2; ...). Повертає найбільше значення із списку аргументів. Логічні і текстові значення ігноруються.
МАКСА(число1;число2; ...). Повертає найбільше значення із списку аргументів. Логічні і текстові значення враховуються (аргументи, які містять значення ІСТИНА, обчислюються як 1; аргументи, які містять текст або значення ХИБНІСТЬ, обчислюються як 0 (нуль)).
МИН(число1;число2; ...). Повертає найменше значення із списку аргументів. Логічні і текстові значення ігноруються.
МИНА(число1;число2; ...). Повертає найменше значення із списку аргументів. Логічні і текстові значення враховуються (аргументи, які містять значення ІСТИНА, обчислюються як 1; аргументи, які містять текст або значення ХИБНІСТЬ, обчислюються як 0 (нуль)).
Логічні функції:
ИСТИНА(). Повертає логічне значення Истина. ЛОЖЬ(). Повертає логічне значення Ложь.
НЕ(логічне значення). Змінює значення Ложь на Истина, а Истина на Ложь.
И(логічне значення1, логічне значення2,…). Повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення Ложь.
ИЛИ(логічне значення1, логічне значення2,…). Повертає значення ИСТИНА, якщо принаймні один аргумент має значення ИСТИНА, або ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ.
ЕСЛИ(лог_вираз;значення_якщо_істина;значення_якщо_хибність). Повертає одне значення, якщо обчислене значення заданої умови — ИСТИНА, та інше значення, якщо обчислене значення заданої умови — ЛОЖЬ.
Переміщення і копіювання формул
Переміщати і копіювати комірки з формулами можна точно так, як і комірки з текстовими або числовими значеннями.
Крім того, при копіюванні комірок з формулами можна користуватися можливостями спеціальної вставки. Це дозволяє копіювати лише формулу без копіювання формату комірки.
При переміщенні комірки з формулою посилання, що містяться у формулі, не змінюються. При копіюванні формули посилання на комірки можуть змінюватися залежно від їх типа (відносні або абсолютні).
Помилки у функціях і аргументах
Основні помилки і деякі можливі причини їх появи приведені в таблиці.
|
Відображення у комірці |
Причина |
Приклад |
|
#ЗНАЧ! |
Використання недопустимого типа аргументу або операнда |
У формулу замість числа або логічного значення (ІСТИННЕ або ХИБНЕ) введений текст. Для оператора або функції, що вимагає одного значення, вказується діапазон. |
|
#ДЕЛ/0! |
Ділення числа на 0 (нуль). |
У формулі міститься явне ділення на нуль (наприклад |
|
#ИМЯ? |
Excel не може розпізнати ім'я, використовуване у формулі |
=А1/0). Використовується посилання на порожню комірку або комірку, що містить 0 як дільник
Не задано один або декілька необхідних аргументів стандартної або призначеної для користувача функції аркуша Комірки, на які посилаються формули, були видалені або в ці комірки було поміщено вміст інших скопійованих комірок.
Використовується помилковий оператор діапазону |
|
#Н/Д |
Значення недоступне функції або формулі |
|
|
#ССЫЛКА! |
Посилання на комірку вказане невірно |
|
|
#ЧИСЛО! |
Неправильні числові значення у формулі або функції. |
|
|
#ПУСТО! |
Задано пересічення двох областей, які насправді не мають загальних комірок |
Окрім критичних помилок, при яких неможливо обчислити результат, Excel відображає повідомлення і про можливі помилки у формулах. В цьому випадку також з'являється індикатор помилки, але у комірці відображається результат.
При виділенні комірки з помилкою поряд з нею з'являється кнопка Источник ошибки. Якщо клацнути по кнопці, з'явиться меню, в якому вказаний тип помилки, а також команди дій для виправлення помилки.
Якщо у формулі дійсно є помилка, слід виправити її за допомогою команди меню Источник ошибки або самостійно. Якщо ж створена правильна формула, можна вибрати команду меню Пропустить ошибку. При цьому індикатор помилки зникне.
Опрацювання масивів числових даних
Масив – це послідовність даних, розташованих у рядку або стовпчику. Для обробки масивів в табличному процесорі Ехсеl використовуються формули для роботи з масивами.
Формула масиву – це формула, яка може виконувати кілька обчислень над одним або кількома елементами масиву та повертати один чи кілька результатів. Наприклад, формулу масиву можна помістити в діапазон клітинок і за її допомогою обчислити стовпець або рядок проміжних підсумків. Формулу масиву також можна помістити в одну клітинку й обчислити один результат. Формула масиву в кількох клітинках називається формулою для кількох клітинок, а формула масиву в одній клітинці – формулою для однієї клітинки.
За допомогою цих формул можна виконувати складні завдання, зокрема:
- підрахувати кількість символів у діапазоні клітинок;
- підсумувати лише ті числа, які відповідають певним умовам (наприклад, найменші значення діапазону або значення, які перебувають між верхньою та нижньою межами);
- підсумувати кожне n-е значення в діапазоні.
Масиви формул зручно використовувати для ведення однотипних формул і обробки даних у вигляді таблиць. Наприклад, для обчислення модуля від чисел, розміщених в комірках B1, C1, D1, E1, замість введення формул в кожну комірку можна вводити одну формулу – масив для всіх комірок. Microsoft Excel додає навколо масиву формул фігурні дужки, по яким його можна відрізнити.
Для створення масиву формул необхідно:
1. Виділити комірки, в яких має знаходитися масив формул.
2. Ввести формулу звичайним чином, вказавши в якості аргументів групу комірок-аргументів.
3. В останньому вікні замість кнопки ОК натиснути комбінацію клавіш Ctrl+Shift+Enter.
Для редагування масиву формул необхідно:
1. Виділити комірки, в яких знаходиться масив.
2. Клацнути комірки всередині рядка редагування і відредагувати формулу.
3. Натиснути комбінацію клавіш Ctrl+Shift+Enter.
Загалом у формулах масиву використовується стандартний синтаксис формул. Усі вони починаються зі знака рівності (=) і підтримують усі вбудовані функції програми Excel. Основна відмінність полягає в тому, що для введення формули масиву потрібно натиснути сполучення клавіш Ctrl+Shift+Enter. Після цього програма Excel бере формулу масиву у фігурні дужки. Якщо такі дужки ввести самостійно, формула перетвориться на текстовий рядок і не працюватиме.
Одним з основних достоїнств електронної таблиці Excel є наявність потужного апарату формул і функцій. Будь-яка обробка даних у Excel здійснюється за допомогою цього апарату. Можна складати, множити, ділити числа, добувати квадратні корені, обчислювати синуси і косинуси, логарифми та експоненти.
Обчислення в таблицях виконуються за допомогою формул. Результатом виконання обчислень є деяке нове значення, що міститься в тій комірці, куди була введена формула. Введення формули в комірку завжди починається зі знаку рівності "=". У формулі можуть використовуватися:
- математичні оператори (+, -, *, /, ^), порядок обчислень яких обумовлюється звичайними математичними законами;
- константи − текстові або числові значення, які вводяться у формулу й не можуть змінюватися під час копіювання формул;
- посилання на комірку, що можуть бути двох видів – абсолютні та відносні. Абсолютне посилання на комірку – це вказівка на комірку, положення якої щодо інших комірок не змінюється. Відносне посилання на комірку – це вказівка місця розташування комірки відносно іншої.
- Для позначення абсолютного посилання використовується знак $. Абсолютною може бути як все посилання, так і його частина. Наприклад, якщо записати D$7, то абсолютним буде тільки номер рядка 7; запис $D7 означає незмінність символу стовпчика D; а запис $D$7 – що все посилання є абсолютним. При переміщенні формули не змінюються посилання обох видів. При копіюванні формули не змінюються тільки абсолютні посилання (частини посилань), а відносні посилання змінюються на величину перенесення формули.
- посилання на діапазон комірок. Для звертання до групи комірок використовуються спеціальні символи: двокрапка (:) − формує звертання до діапазону комірок (С4:D6 − звертання до комірок С4, С5, С6, D4, D5, D6); крапка з комою (;) − позначає об'єднання комірок (D2:D4; D6:D8; Е5 − звертання до комірок D2, D3, D4 й D6, D7, D8 та Е5).
- стандартні функції.
Функції в Excel використовуються для виконання стандартних обчислень в робочих книгах. Excel містить більше 300 вбудованих функцій. Для роботи з ними в Excel є спеціальний засіб - Майстер функцій, робота з яким складається з двох кроків: пропонується спочатку вибрати ім'я потрібної функції зі списку категорій (перший крок); потім у діалоговому вікні ввести значення аргументів (другий крок). Майстер функцій викликається командою Вставить функцию (вкладка Формулы група Библиотека функций) або натисканням кнопки Вставка функции на рядку формул (також можна скористатися комбінацією клавіш Shift+F3).
Для зручності й наочності роботи з формулами при виправленні помилок застосовують команди з групи Зависимости формул вкладка Формулы.
Розглянемо деякі команди цієї групи.
Впливаючі комірки – стрілками вказуються комірки, які впливають на результат розрахунків в активній комірці. Залежні комірки – стрілками вказуються комірки, які залежать від даних активної комірки. Проверка наличия ошибок – якщо в поточній комірці міститься повідомлення про помилку, то ця команда виявляє помилку. Показать формулы — в таблиці встановлюється режим перегляду формул, а для активної комірки виділяються кольорами рамки комірок, що впливають на результат розрахунків в активній комірці.
Хід роботи
ЧАСТИНА 1
1) Завантажте Excel та назвіть документ Лаб_роб_10. У всіх завданнях застосувати границю до комірок та вирівнювання по центру комірок.
2) Розв’язати задачу нарахування заробітної плати в аркуші 1, який перейменуйте на «Заробітна плата». Для стовпчиків Тариф, Нараховано, Податок, Пофспіл.внесок, Пенс.фонд, До видачі застосувати числовий формат з 2-ма знаками після коми у грн.
[Нараховано] = [Днів]*[Тариф]; [Податок] = [Нараховано]*13%;
[Профспіл.внесок] = [Нараховано]* 1%; [Пенс.фонд] = [Нараховано]* 14%;
[До видачі] = [Нараховано] – [Податок] – [Профспіл.внесок] – [Пенс.фонд].
|
Прізвище |
Днів |
Тариф |
Нараховано |
Податок |
Профспіл. внесок |
Пенс. фонд |
До видачі |
|
Білоус |
25 |
60,00 |
? |
? |
? |
? |
? |
|
Білявська |
22 |
58,50 |
? |
? |
? |
? |
? |
|
Миронов |
21 |
72,00 |
? |
? |
? |
? |
? |
|
Карнаух |
22 |
80,80 |
? |
? |
? |
? |
? |
|
Качура |
22 |
75,00 |
? |
? |
? |
? |
? |
3) Побудувати таблицю за зразком у аркуші 2 «Розмірність валют». Враховуючи коефіцієнт обміну долара і гривні (1$=28,25 грн.), а також коефіцієнт обміну євро та гривні (1 EUR = 33,32 грн.), обчислити ціну у відповідній валюті. Округлити до 2 знаків після коми. Виставити розмірність валюти.
|
Назва товару |
Кількість штук |
Ціна за 1 шт ($) |
Вартість, $ |
Ціна за 1 шт (EUR) |
Вартість, EUR |
Ціна за 1 шт (грн) |
Вартість, грн. |
|
М’яч |
100 |
5,00 |
|
4,27 |
|
141,49 |
|
|
Зошит |
1000 |
0,10 |
|
0,09 |
|
2,83 |
|
|
Олівець |
200 |
0,05 |
|
0,04 |
|
1,41 |
|
|
Ручка |
150 |
0,50 |
|
0,43 |
|
14,15 |
|
4) В аркуші 3 скласти звітну відомість за результатами діяльності торгівельної фірми у весняно-літній період, наведеними у табл. 1.
У звітній відомості потрібно визначити:
- сумарну та середню виручку кожної з філій за звітний період;
- сумарну виручку всіх філій за кожен місяць звітного періоду;
- місце, яке займає кожна з філій в сумарному обсязі виручки;
- частку кожної з філій в сумарному обсязі виручки;
- кількість філій, що мають сумарну виручку до 5000 тис. грн, від 5000 тис. грн до 7000, від 7000 тис. грн до 10 000 і понад 10 000 тис. грн;
- найменшу місячну виручку за звітний період;
- найбільшу місячну виручку за звітний період.
Таблиця 1
Алгоритм виконання:
1. Перейменуйте робочий аркуш Лист 3 на «Звітна відомість».
2. Створіть таблицю в комірках цього робочого аркуша з даними табл. 1 у тих самих комірках.
3. Створіть заголовок Сумарна виручка, тис. грн у комірці І3.
4. Щоб визначити сумарну виручку першої філії введіть у комірку І4 формулу = СУММ (B4:G4).
5. Виділіть комірку І4, розташуйте покажчик миші на маркері заповнення і перемістіть його вниз на діапазон I5:I13. Це сумарна виручку кожної філії.
6. Для обчислення сумарного обсягу виручки всіх філій за березень уведіть у комірку А14 заголовок Всього, а в комірку В14 – формулу = СУММ (В4:В13).
7. Виділіть комірку В14, розташуйте покажчик миші на маркері заповнення і перемістіть його вправо на діапазон C14:G14, що дасть можливість знайти сумарну виручку філій за кожен місяць окремо.
8. У комірці І14 обчисліть сумарну виручку в цілому, ввівши в неї формулу = СУММ (I4:I13).
9. Щоб визначити середню виручку першої філії, введіть у комірку J3 заголовок Середня виручка, тис. грн, а в комірку J4 – формулу = CPЗHAЧ(B4:G4).
10.Виділіть комірку J4, розташуйте покажчик миші на маркері автозаповнення і перемістіть його вниз на діапазон J5:J13, таким чином – середня виручка кожної філії.
11.Для визначення частки обсягу виручки першої філії стосовно сумарної виручки всієї мережі філій введіть в комірку К3 заголовок Частка, а в комірку К4 – формулу =І4/$І$14.
12. Виділіть комірку К4, розташуйте покажчик миші на маркері автозаповнення і перемістіть його вниз на діапазон K5:K13. Це дасть змогу обчислити частку обсягу виручки кожної філії щодо сумарної виручки всієї мережі філій.
13.Виділіть комірку I14 і назвіть її Разом за допомогою натискання на кнопку команди Имя/Присвоить.
Надання комірці або діапазону комірок імені дає інший спосіб абсолютної адресації (абсолютного посилання на комірки).
14. Виділіть комірку К4 і змініть формулу на = I4 / Разом.
15. Скопіюйте формули на діапазон комірок К5:К13 за допомогою маркера автозаповнення. По черзі виділіть кожну з комірок діапазону К4:К13 і за допомогою рядка формул проаналізуйте формули в цих комірках. Зробіть висновки.
16. Виділіть діапазон комірок I4:I13 і назвіть його СумаПоФіліям шляхом клацання на кнопку Имя/Присвоить та ввестиім’я.
17. Виділіть комірку К4 і змініть формулу на – СумаПоФіліям / Разом.
18. Виконайте копіювання формули на діапазон комірок К5:К13 за допомогою маркера автозаповнення. По черзі виділіть кожну з комірок діапазону К4:К13 і за допомогою рядка формул проаналізуйте формули у цих комірках. Зробіть висновки.
19. Виділіть діапазон комірок К4:К13 і натисніть на кнопку Процентный формат на вкладці Главная у групі Число. Це дасть змогу встановити відсотковий формат у комірках вибраного діапазону.
20. Щоб визначити місце першої філії в сумарній виручці всієї мережі філій, введіть у комірку L3 заголовок Рейтинг, а в комірку L4 – формулу
=PAHГ(J4; $J$4: $J$13) або =PAHГ(J4; СумаПоФіліям).
21. Виділіть комірку L4, розташуйте покажчик миші на маркері автозаповнення і перемістіть його вниз на діапазон L5:L13. Таким чином визначите рейтинг кожної філії в сумарній виручці всієї мережі філій.
22. Для обчислення кількості філій, що мають сумарну виручку до 5000 тис. грн, від 5000 тис. грн до 7000, від 7000 тис. грн до 10 000 і понад 10 000 тис. грн спочатку потрібно в комірках МЗ та N3 створити заголовки Межі виручки, тис. грн і Кількість філій, а потім у комірку М4 ввести число 5000, у комірку М5 – 7000, М6 – 10 000.
23. Виділіть діапазон комірок N4:N13 і введіть у нього формулу масиву, використовуючи майстер функцій: { = ЧАСТОТА (СумаПоФіліям; М4:М6)}.
Не забудьте завершити її введення шляхом натискання на клавіші комбінації Ctrl + Shift + Enter.
24. Для обчислення найменшої місячної виручки за звітний період потрібно виділити певну комірку, наприклад, І15 і ввести формулу = МИН (B4:G13).
Бажано створити текстовий заголовок у комірці зліва Найменша місячна виручка, грн.
25. Щоб визначити найбільшу місячну виручку за звітний період, слід виділити певну комірку, наприклад І16 і ввести формулу = MAKC(B4:G13). Рис. 1. Зразок звітної відомості
Рекомендується створити текстовий заголовок у комірці зліва Найбільша місячна виручка, грн.
Отже, звітна відомість створена (рис. 1). У ній міститься багато цінних даних, необхідних для прийняття певних управлінських рішень. Проаналізуйте їх. За потреби їх можна роздрукувати.
Створіть відповідну таблицю в Excel та назвіть аркуш 4 під назвою «Логічні». Виконайте форматування згідно таблиці 2.
Таблиця 2
|
А |
В |
С |
D |
Е |
F |
G |
H |
|
|
Розрахунок економічних показників |
||||||||
|
№ |
Найменування товару |
Кількість, т |
Ціна за 1 т, грн |
Ціна партії, грн |
Знижка, % |
Вартість з урахуванням знижки, грн |
Прибуток, грн |
|
|
1 |
Батон |
7 |
866,67 |
|
|
|
|
|
|
2 |
Шарлотка |
4 |
868,00 |
|
|
|
|
|
|
3 |
Плетенка |
10 |
1065,00 |
|
|
|
|
|
Алгоритм виконання:
1. Стовпець Е визначається як добуток «Кількість, т» на «Ціна за 1 т. грн».
2. У стовпець F запишіть логічну функцію: якщо «Ціна партії, грн» більша за 10 000, то знижка становить 2 %, інакше знижки немає.
3. У стовпці G використайте формулу добутку: «Ціна партії» на (100 % - «Знижка»).
4. Визначте прибуток (стовпець Н), як різницю «Ціна партії, грн.» від «Вартість з урахуванням знижки». Застосуйте числовий формат з 2 знаками після коми у грн.
ЧАСТИНА 2
Завдання 1. Розрахувати значення функції y(x)= A sin2 (3x), якщо А=4, а значення х змінюються на інтервалі від -3 до 3 з кроком 0,2. Результат оформити за наведеним ескізом та розмістити на аркуші з ім’ям Функція.
|
|
A |
B |
C |
|
1 |
Функція y(x)=A*sin2(3x) |
||
|
2 |
A= |
4 |
|
|
3 |
Х |
Y(x) |
|
Порядок виконання завдання 1
- Створити на робочому аркуші таблицю згідно ескізу.
- У комірку В2 ввести значення константи А – число 4.
- У діапазоні комірок А4:А34 створити арифметичну прогресію від -3 до 3 з кроком 0,2.
- Зробити активною комірку В4 та ввести формулу для розрахунку – =$В$2*sin(3*А4)^2.
- Так як значення константи А знаходиться тільки у комірці В2, то при копіюванні формули посилання на цю комірку не повинно змінюватися. Тобто, повинно бути абсолютним.
- За допомогою маркера заповнення виконати копіювання формули у відповідні комірки таблиці. Для цього виділити комірку B4, потім маркер заповнення відбуксувати до комірки В34. Відпустити ліву клавішу миші.
- Встановити режим перегляду формул, виконавши команду Показать формулы (вкладка Формулы група Зависимости формул) та переконатися, що формули автоматично модифікувалися щодо нового місця розташування. Відключити режим перегляду формул.
|
|
A |
B |
C |
D |
Е |
|
1 |
Модуль числа |
||||
|
2 |
Число |
-25,12 |
12,34 |
-45,6 |
87,7 |
|
3 |
Модуль |
|
|
|
|
- Виконати необхідне форматування та налаштувати ширину стовбців та висоту рядків таблиці для коректного відображення даних.
- Зберегти аркуш з обчисленнями.
Завдання 2. Створити новий аркуш з ім’ям Масиви.
Створити масив формул обчислення модуля для чисел -25,12; 12,34; -45,6; 87,7 за наведеним ескізом.
Порядок виконання завдання 2
- Перейти на будь-який вільній аркуш робочої книги. Змінити ім’я поточного робочого аркуша на ім’я Масиви.
- Створити на робочому аркуші таблицю згідно ескізу. У діапазон комірок В2:Е2 ввести необхідні для обчислення числа (-25,12; 12,34; -45,6; - 87,7). Виконати необхідне форматування та налаштувати ширину стовбців та висоту рядків таблиці для коректного відображення даних.
- Виділити діапазон комірок В3:Е3, у яких буде знаходитися масив формул.
- Клацнути на кнопці Вставка функции, яка розташована на рядку формул (також можна використати комбінацію клавіш Shift+F3)
- У діалоговому вікні Мастер функций – шаг 1, у поле Категория вибрати тип функції Математические, потім у списку Выберите функцию – ABS (обчислення модуля). Клацнути кнопку ОК.
- На кроці 2 – Аргументы функции в поле Число ввести В2:Е2.
- Замість кнопки ОК натиснути комбінацію клавіш Ctrl+Shift+Enter. У діапазоні комірок В3:Е3 з'являться модулі чисел.
- Зберегти аркуш з обчисленнями
Завдання 3. Створити новий робочий аркуш – Таблиця. Створити таблицю множення 10 на 10 за допомогою масив формул за наведеним ескізом.
|
|
A |
B |
C |
D |
Е |
F |
G |
H |
I |
J |
K |
|
|
1 |
Таблиця множення |
|||||||||||
|
2 |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
|
3 |
1 |
|
|
|
|
|
|
|
|
|
|
|
|
4 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
5 |
3 |
|
|
|
|
|
|
|
|
|
|
|
Порядок виконання завдання 3
- Перейти на будь-який вільній аркуш робочої книги. Змінити ім’я робочого аркуша на ім’я Таблиця.
-
Створити на робочому аркуші таблицю згідно ескізу. У діапазон комірок В2:K2 та A3:A12 ввести натуральні числа від 1 до 10.
- Виділити діапазон комірок В3:K12, у яких буде знаходитися масив формул.
- Встановити курсор у рядок формул та ввести формулу для розрахунку =B2:K2*A3:A12.
- Натиснути комбінацію клавіш Ctrl+Shift+Enter. У діапазоні комірок В3:K12 з'явиться таблиця множення.
- Виконати необхідне форматування та налаштувати ширину стовбців та висоту рядків таблиці для коректного відображення даних.
Оформити звіт і здати на перевірку викладачу.
Контрольні запитання
- Яка послідовність уведення формули в комірку та її виконання?
- Що таке «відносне посилання» у Excel?
- Що таке «абсолютне посилання» у Excel?
- Що таке «змішане посилання» у Excel?
- Що таке «функція» у Excel?
- Що таке аргумент?
- Яка структура функції у Excel?
- Як можна додати функцію у Excel?
- Які основні функції Excel ви знаєте?
- Які функції належать до категорії математичних функцій?
- Які ви знаєте основні статистичні функції Excel? Як можна їх задати?
- Які логічні функції Excel ви знаєте? Як можна їх задати?
- Як присвоїти комірці або діапазонові комірок ім’я?
- У чому полягає відмінність між відносним посиланням на комірки і посиланням з використанням імен комірок?
- В яких випадках потрібно використовувати абсолютне посилання на комірки?
- Яку синтаксичну формулу мають функції МИН, МАКС і СРЗНАЧ?
- Для чого призначена функція РАНГ? Яка її синтаксична формула?
- Яке призначення має функція ЧАСТОТА? Яка її синтаксична формула?
- З якою метою призначена функція СУММПРОИЗВ? До якої категорії вона належить?
- Як записати синтаксичну формулу функції ЕСЛИ?
- Як записати синтаксичну формулу функції И?
- Як записати синтаксичну формулу функції ИЛИ?
- Яка максимальна кількість вкладень для функції ЕСЛИ?
- Які обчислювальні процеси дає змогу реалізувати функція ЕСЛИ?
- Як можна відобразити формулу, а не результати розрахунків у комірці?
- Як можна копіювати формули в Excel?
- Назвіть способи вставки функцій у комірку?
про публікацію авторської розробки
Додати розробку