Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторна робота №6
Тема: Табличний процесор Excel. Розв’язання економічних задач за допомогою електронних таблиць. Використання логічних функцій.
Мета: Уміти формувати умовні вирази та використовувати їх у розрахунках.
Теоретичні відомості
Оператори відношення
Для перевірки наявності/відсутності якої-небудь ознаки або події, використовують логічні вирази. Логічні вирази формуються за допомогою операторів відношення:
= логічне дорівнює
> більше
< менше
>= більше або дорівнює
<= менше або дорівнює
<> не дорівнює
Наприклад, (D1=500), (A1<>A5), (F1>=10%)
Логічний вираз приймає значення істина ( або 1), якщо умова у виразі виконується, та лож ( або 0) – якщо умова не виконується. Логічні вирази можуть складатися з однієї або декількох умов. Якщо необхідно перевірити одночасне виконання декількох умов, то умови у логічному виразі об’єднаються за допомогою знаку “*”. Для перевірки виконання хоча б однієї з декількох умов, їх об’єднують знаком “+”.
Наприклад, для перевірки факту, що у комірці В1 знаходиться значення у межах від 50 до 100, можна використати умову (В1>=50)*(B1<=100). Для перевірки факту, що у комірці С1 знаходиться літера “А” або літера “В”, можна скористатися умовою (С1=“А”)+(С1=“В”).
Для перевірки у логічному виразі виконання одночасно декількох умов, також використовують логічні функції И() та ИЛИ().
И( умова; умова; …) – функція И() повертає логічне значення істина, якщо всі її аргументи (умови) приймають значення істина. Якщо хоча б одна з умов приймає хибне значення, то функція И() повертає значення лож.
ИЛИ( умова; умова; …) – функція ИЛИ() повертає логічне значення істина, якщо хоча б одна з її умов приймає значення істина. Якщо всі умови приймають хибне значення, то функція ИЛИ() повертає значення лож.
Функція ЕСЛИ
Для виконання розрахунків в залежності від виконання будь-якої умови призначена функція ЕСЛИ(). Ця функція дозволяє наділити формули здатністю прийняття рішення.
ЕСЛИ ( аргумент 1; аргумент 2; аргумент 3)
логічний вираз; значення якщо так; значення якщо ні
Функція використовує три аргументи. Спочатку обчислюється аргумент 1 – логічний вираз, який може приймати значення істина (Так) або лож (Ні). Якщо аргумент 1 має значення Так, то виконується аргумент 2, у випадку, якщо логічний вираз має значення Ні, виконується аргумент 3.
Наприклад, для перевірки значення (позитивне чи негативне), що знаходиться у комірці А1, можна скористатися формулою:
=ЕСЛИ(А1>0; „Позитивне”; „Негативне”)
Якщо у комірці А1 знаходиться значення більше 0, то функція повертає рядок „Позитивне”, у протилежному випадку – „Негативне”. Однак, ця формула має недолік – коли комірка А1 порожня або містить 0, функція надає значення „Негативне”. У цьому випадку можна використати вкладену функцію ЕСЛИ() (рис. 1). Тоді формула приймає вигляд:
=ЕСЛИ(А1>0; „Позитивне”; ЕСЛИ(А1<0; „Негативне”; „Нуль”))
Функція ЕСЛИ() може містити до семи таких вкладених функцій. Однак, слід пам’ятати, що коли рівень вкладення перевищує три-чотири, розібратися у формулі стає складно. Для використання функцій активізують Мастер функций командою меню Вставка/Функция або відповідною кнопкою на панелі інструментів ƒx . Покрокове використання майстра функцій описано у лабораторій роботі №5 (див. лаб. раб. 5, рис. 4).
Використання підсумкових функцій
Для отримання різного роду підсумків за даними, що містяться у таблиці, використовують підсумкові функції.
СУММ( діапазон1; діапазон2; ...) – функція обчислює суму чисел, що входять до списку діапазонів. Список може містити до 30 елементів. Якщо у вибраному діапазоні є нечислові дані, при розрахунках вони вважаються як нулі.
СРЗНАЧ( діапазон1; діапазон2; ...) – функція розраховує середнє арифметичне серед чисел вказаних діапазонів.
МИН( діапазон1; діапазон2; ...) – функція знаходить мінімальне значення серед чисел вказаних діапазонів.
МАКС( діапазон1; діапазон2; ...) – функція знаходить максимальне значення серед чисел вказаних діапазонів.
СУММЕСЛИ( діапазон перегляду; умова пошуку; діапазон підсумовування) – функція виконує пошук у діапазоні перегляду даних, що задовольняють умові пошуку та підсумовує значення відповідних комірок із діапазону. Якщо діапазон сумування не заданий, сумування виконується у діапазоні перегляду. Умова пошуку може складатися тільки з однієї умови.
СЧЁТ( діапазон перегляду) – функція підраховує у діапазоні перегляду кількість комірок, що містять числові значення.
СЧЁТЕСЛИ( діапазон перегляду; умова пошуку) – функція виконує підрахунок у діапазоні перегляду кількості комірок, що задовольняють умові пошуку.
НАИБОЛЬШИЙ ( діапазон перегляду; номер) – функція виконує пошук найбільшого по рахунку елементу з вказаним номером від максимального значення у заданому діапазоні перегляду.
НАИМЕНЬШИЙ ( діапазон перегляду; номер) – функція виконує пошук найменшого по рахунку елементу з вказаним номером від мінімального значення у заданому діапазоні перегляду.
Використання функцій СУММЕСЛИ() та СЧЁТЕСЛИ() обмежено можливістю використання у них тільки однієї умови, яку можна внести у якості умови пошуку. Тому, якщо необхідно зробити вибірку за складною (з декількома умовами) умовою, ці функції не допоможуть. У цьому випадку використовують функції роботи з масивами.
Масив – це організована множина комірок або констант будь-якого типу. Масив може містити не тільки множину комірок, але й множину констант. Наприклад, вираз {3; -4; 12} описує масив констант з трьох числових елементів.
При роботі з масивами можна використовувати функції СЧЁТ(), МАКС(), СУММ() та інші підсумкові функції, включивши до них функцію ЕСЛИ() з необхідними умовами відбору. Умови можуть складатися не тільки з констант, але й з посилань на комірки. В умовах не можна використовувати логічні функції И(), ИЛИ(), НЕ(), Але можна використовувати вкладені функції. Якщо необхідно обчислити кількість деяких елементів, у якості аргументу функції ЕСЛИ() використовують 1, для обчислення суми – самі ті елементи, що містять необхідні дані.
Для завершення введення формули, що обробляє масив, необхідно натиснути клавіші CTRL+SHIFT+ENTER. При такому введенні формула автоматично обрамляється фігурними дужками.
Автоматична перевірка правильності введених даних
У програмі Excel існують засоби автоматичної перевірки правильності введення даних. Правила, що будуть використовуватися для підтвердження правильності введення, повинні бути розроблені заздалегідь. Виконати додаткову операцію по налагодженню умов правильності введення необхідно при підготовці робочої книги до введення даних. Для цього слід виділити область робочого листа, призначену для введення даних визначеного типу, та виконати команду меню Данные/Проверка. Умови, які накладаються на значення, що вводяться, задаються на вкладниці Параметри діалогового вікна Проверка вводимых значений (рис. 2)
Якщо у обраній комірці дозволено введення тільки значень із визначеного переліку, необхідно створити де-небудь на робочому листі список припустимих варіантів, а потім обрати у списку Тип данных пункт Список та вказати діапазон комірок, які містять список, у полі Источник. Тоді при введенні значення в комірку, поряд з нею з’явиться кнопка, що дозволяє розгорнути список припустимих значень. Можливість некоректного введення даних у цьому випадку повністю відсутня.
Інші пункти списку Тип данных дозволяють обрати інші типи даних та задати для них відповідні обмеження. Способи обрання обмежень залежать від того, які саме дані повинні бути занесені у комірку.
Для пояснення, що саме повинне міститись у даній комірці, можна задати параметри діалогового вікна (заголовок та текст), що автоматично відкривається, коли дана комірка стає поточною. Для цього існує вкладника Сообщение для ввода.
Повідомлення про порушення правил введення задається на вкладниці Сообщение об ошибке. Тут виконується опис виду діалогового вікна, якщо введені дані не задовольняють заданим умовам. Повідомлення про помилку можуть бути 3 видів:
Умовне форматування
Засоби умовного форматування дають можливість керувати кольором тексту та заливки, видом рамки, шрифтом та підкресленням в залежності від заданих умов. Умова може бути складною та залежати від значення будь-якої комірки.
Для встановлення умов форматування необхідно виділити область робочого листа, призначену для форматування, та виконати команду меню Формат/Условное форматирование (рис. 3).
Умов може бути до трьох. Вікно кожної наступної умови відкривається за допомогою кнопки А также. Умови можуть бути двох типів:
- значение – дозволяє задати значення, відносно якого перевіряють комірки, що форматуються;
- формула – дозволяє вказати логічну формулу будь-якої складності.
Умови можуть містити не тільки константи, але й посилання як на окремі комірки так й на діапазони комірок.
Завдання для лабораторної роботи
Файл/Сохранить/ обрати потрібну папку/ у полі Имя файла ввести EXCEL_lab_6 /Сохранить.
Зробити активним Лист1/Формат/Лист/Переименовать/вводимо з клавіатури назву Зарплата/Enter.
Форматування виділених комірок: Формат/Ячейки.
Для формування списку: у комірку К1 внести значення “ч”, у комірку К2 – “ж”. Виділити діапазон В7:В13, обирати Данные/ Проверка/ вкладинка Параметры/ Тип данных вибирати Список/у полі Источник вибирати $K$1:$K$2. Перейти на вкладинку Сообщение об ошибке/у полі Вид обирати значення Останов/ у полі Заголовок ввести Увага!/ у полі Сообщение ввести Список значень обмежений. Допустимі значення “ч” та “ж”/ ОК.
|
A |
B |
C |
D |
E |
F |
G |
|
|
1 |
Місяць |
3 |
Премії |
||||
|
2 |
Мінімальний неоподатковуваний мінімум |
17 грн. |
До 23 лютого |
10% |
|||
|
3 |
Податок |
13% |
До 8 березня |
500 грн. |
|||
|
4 |
До Дня митника |
400 грн. |
|||||
|
5 |
Розрахунок премії, податків та заробітної плати митників |
||||||
|
6 |
Співробітник |
Стать |
Діти |
Оклад |
Премія |
Сума податків |
Сума на руки |
|
7 |
Петров В.А. |
ч |
1 |
2000 грн. |
грн. |
грн. |
грн. |
|
8 |
Кислова К.Д. |
ж |
2 |
1500 грн. |
грн. |
грн. |
грн. |
|
9 |
Синиціна А.Д. |
ж |
0 |
1700 грн. |
грн. |
грн. |
грн. |
|
10 |
Марков П.Р. |
ч |
1 |
1750 грн. |
грн. |
грн. |
грн. |
|
11 |
Косов К.Л |
ч |
1 |
1650 грн. |
грн. |
грн. |
грн. |
|
12 |
Волкова Т.П. |
ж |
2 |
1700 грн. |
грн. |
грн. |
грн. |
|
13 |
Федотов К.Н. |
ч |
0 |
1450 грн. |
грн. |
грн. |
грн. |
|
14 |
Всього |
грн. |
грн. |
грн. |
грн. |
||
|
15 |
Серед них жінок: |
Серед всіх співробітників: |
|||||
|
16 |
Загальна кількість |
Середні заробітна плата |
грн. |
||||
|
17 |
Кількість з дітьми |
Отримують зарплату більше середньої |
грн. |
||||
|
18 |
Загальна сума заробітку |
грн. |
Перша найбільша зарплата |
грн. |
|||
|
19 |
Середній заробіток |
грн. |
Друга найбільша зарплата |
грн. |
|||
|
20 |
Максимальна зарплата |
грн. |
Третя найбільша зарплата |
грн. |
Виділити діапазон С7:С13, обирати команду меню Данные/Проверка/ на вкладинці Параметры у полі Тип данных вибирати Целое число/ у полі Значение вибирати Между/ у полі Минимум вказати 0/ у полі Максимум вказати 5. Перейти на вкладинку Сообщение для ввода / у полі Заголовок ввести Попередження! у полі Сообщение ввести Припустимі цілі значення від 0 до 5! /на вкладинці Сообщение об ошибке / у полі Вид обрати значення Предупреждение/ у полі Заголовок ввести Увага!/ у полі Сообщение ввести Невірно введені дані!/ ОК.
Виділити діапазон D7:D13, обирати команду Данные/ Проверка / на вкладинці Параметры/ у полі Тип данных вибрати Действительное/ у полі Значение вибрати Больше либо равно/ у полі Минимум вазати 350/ на вкладниці Сообщение для ввода/ у полі Заголовок ввести Увага! / у полі Сообщение ввести Мінімальна заробітна плата становить 350 грн.! /на вкладинці Сообщение об ошибке / у полі Вид обрати значення Сообщение/ у полі Заголовок ввести Увага!/ у полі Сообщение ввести Мінімум –350 грн.!/ ОК.
Розрахунок премії: Премія = ЕСЛИ ( стать = жіноча, то 500 грн., інакше – 0 грн.).
У комірку Е7 занести формулу: = ЕСЛИ (В7=”ж”; $G$3; 0). Для цього поставити курсор на комірку Е7 та натиснути кнопку Вставка функции. У вікні майстра обрати функцію ЕСЛИ(). У вікні, що розкриється (рис. 1), у рядок Лог_выражение занести В7=”ж”, у рядок Значение_если_істина занести $G$3, у рядок Значение_если_лож занести 0/ ОК (або Enter).
За допомогою маркеру заповнення скопіювати формулу у діапазон комірок Е8:Е13.
У формулі необхідно врахувати значення місяця:
Премія = ЕСЛИ ( стать = жіноча ТА місяць = березень, то 500 грн., інакше – 0 грн.).
У формулу у комірці Е7 внести виправлення: =ЕСЛИ( И(В7=”ж”;$D$1=3); $G$3; 0). Для цього поставити курсор на комірку Е7 і натиснути кнопку у рядку формул. У функції ЕСЛИ() для виділеного рядку Лог_выражение зі списку функцій у рядку формул вибирати функцію И(). У рядок Логическое_значение1 заносимо В7=”ж”, у рядок Логическое_значение2 занести $D$1=3 /Ok.
За допомогою маркеру заповнення скопіювати формулу у діапазон комірок Е8:Е13.
Премія = ЕСЛИ ( стать = жіноча ТА місяць = березень, то 500 грн.
ЕСЛИ( стать = чоловіча ТА місяць = лютий,
то 10% від зарплати, інакше – 0 грн.)
Нарахування премії у вигляді формули:
E7=ЕСЛИ( И(В7=”ж”;$D$1=3); $G$3; ЕСЛИ( И(В7=”ч”;$D$1=2); $G$2*С7; 0))
Складну формулу можна спростити, поділивши на два додатки:
E7=ЕСЛИ( И(B7=”ж”;$D$1=3); $G$3;0) + ЕСЛИ( И(В7=”ч”;$D$1=2); $G$2*С7; 0))
За допомогою маркеру заповнення скопіювати формулу у діапазон комірок Е8:Е13.
Для спрощення формули представимо кожний вид премії окремим додатком:
Премія = ЕСЛИ ( стать = жіноча ТА місяць = березень, то 500 грн., інакше – 0) + ЕСЛИ( стать = чоловіча ТА місяць = лютий, то 10% від зарплати,
інакше – 0 грн.)+ ЕСЛИ(місяць = червень, то 400 грн., інакше – 0 грн.)
Тоді формула виглядає:
E7=ЕСЛИ( И(В7=“ж”;$D$1=3); $G$3;0) + ЕСЛИ( И(В7=“ч”;$D$1=2); $G$2*D7; 0) +
+ЕСЛИ($D$1=6; $G$4; 0)
За допомогою маркеру заповнення скопіювати формулу у діапазон комірок Е8:Е13.
Сума податку = (Оклад + Премія – ЕСЛИ( стать = жіноча, то кількість дітей * МНМ, інакше – 0))*Податок
У комірку F7 занести формулу: = (D7 + E7 – ЕСЛИ( В7 = “ж”; С7*$D$2; 0) )* $D$3. За допомогою маркеру заповнення скопіювати формулу у діапазон комірок F8:F13.
Сума на руки = Оклад + Премія – Сума податку
У комірку G7 занести формулу: = D7 + E7 – F7.
За допомогою маркеру заповнення скопіювати формулу у діапазон комірок G8:13.
У комірку С16 занести формулу =СЧЁТЕСЛИ (В7:В13; “ж”)
У комірку С17 занести формулу {=СУММ (ЕСЛИ(С7:С13>0; ЕСЛИ (В7:В13=“ж”; 1)))}
Введення формули завершуємо натисканням CTRL+SHIFT+ENTER, при цьому фігурні дужки додаються автоматично.
Розрахунок можна виконати також за формулою: {=СЧЁТ(ЕСЛИ(С7:С13>0; ЕСЛИ (В7:В13=“ж”; 1)))}. Тут всі комірки, що містять у діапазоні В7:В13 букву “ж” та у діапазоні С7:С13 значення більше 0, будуть приймати участь у підрахунках та сумуванні як одиниці.
У комірку С18 занести формулу =СУММЕСЛИ (В7:В13 =“ж”; G7:G13)
У комірку С19 занести формулу ={СРЗНАЧ( ЕСЛИ (В7:В13 =“ж”; G7:G13;0))}
У комірку С20 занести формулу ={МАКС( ЕСЛИ (В7:В13 =“ж”; G7:G13;0))}
У комірку G16 занести формулу =СРЗНАЧ( G7:G13)
У комірку G17 занести формулу {=СУММ( ЕСЛИ(G7:G13 >= G16; 1;0))}
У комірку G18 занести формулу =НАИБОЛЬШИЙ( G7:G13; 1)
У комірку G19 занести формулу =НАИБОЛЬШИЙ( G7:G13; 2)
У комірку G20 занести формулу =НАИБОЛЬШИЙ( G7:G13; 3)
Виділити комірки В7:В1, обрати команду Формат/Условное форматирование. У полі Условие1 обрати Значение равно “ж”, натиснути кнопку Формат/ вкладинка Шрифт обрати начертание – полужирный, цвет – красный/Ок/Ок.
Виділити комірки А7:А13, обрати команду Формат/Условное форматирование. У полі Условие1 обрати Формула і ввести =G7>=$G$16. Натиснути кнопку Формат, у вкладинці Вид обрати цвет – розовый /Ок/Ок.
Завдання для самостійної роботи
На окремому листі Авто_дизель обчислити суму платежів, що сплачується при митному оформленні транспортних засобів, що були придбані за межами України. Сума платежів складається з:
Основою для нарахування мита та акцизного збору для легкових автомобілів є робочий об’єм двигуна (см3). Ставки мита та акцизного збору для легкових автомобілів із дизельним двигуном надані у таблиці.
|
Об’єм двигуна, см3 |
До 1500 |
З 1501 по 2500 |
Більше 2500 |
|
Акцизний збір, євро |
0,2 |
0,3 |
0,8 |
|
Мито, євро |
0,1 |
0,15 |
0,8 |
Розрахунки платежів виконуються за формулами:
Мито = Об’єм двигуна * Ставка мита * Курс євро
Акцизний збір = Об’єм двигуна * Ставка акцизного збору * Курс євро
ПДВ становить 20% від суми митного збору, мита та акцизного збору.
У таблиці 3 наведена структура даних, де комірки, значення яких слід розрахувати за допомогою формул, містять знак „?”.
Для перевірки правильності введення даних для стовпця Дата оформлення задайте умову на можливість введення тільки дат поточного року; для стовпця Об’єм двигуна дозволити введення цілих значень не менше 1000 та не більше 8000; марки автомобілів обирати з раніше сформованого списку (значення у списку задати самостійно).
За допомогою умовного форматування виділити кольором марки автомобілів, об’єм двигуна яких перевищує 2500 см3.
За допомогою умовного форматування виділити дати, які приходились на вихідні (субота, неділя). Примітка: функція ДЕНЬНЕД(дата; 2) повертає номер дня тижня.
Таблиця 3
|
A |
B |
C |
D |
E |
F |
G |
|||
|
1 |
Курс євро |
6,27 грн. |
Об’єм двигуна, см3 |
До 1500 |
З 1501 по 2500 |
Більше 2500 |
|||
|
2 |
ПДВ |
20% |
Акцизний збір, євро |
0,2 |
0,3 |
0,8 |
|||
|
3 |
Митний збір |
15$ |
Мито, євро |
0,1 |
0,15 |
0,8 |
|||
|
4 |
|||||||||
|
5 |
Дата митного оформлення |
Марка авто |
Об’єм двигуна, см3 |
Мито |
Акцизний збір |
ПДВ |
Сума платежів |
||
|
6 |
1/10/10 |
Фольксваген |
1400 |
? грн. |
? грн. |
? грн. |
? грн. |
||
|
7 |
23/03/10 |
Фіат |
1814 |
? грн. |
? грн. |
? грн. |
? грн. |
||
|
8 |
20/04/10 |
Тайота |
2200 |
? грн. |
? грн. |
? грн. |
? грн. |
||
|
9 |
30/04/10 |
Мерседес |
6800 |
? грн. |
? грн. |
? грн. |
? грн. |
||
|
10 |
10/05/10 |
Ягуар |
3400 |
? грн. |
? грн. |
? грн. |
? грн. |
||
|
11 |
7/06/10 |
Тайота |
1600 |
? грн. |
? грн. |
? грн. |
? грн. |
||
|
12 |
Всього |
? грн. |
? грн. |
? грн. |
? грн. |
||||
|
13 |
В середньому |
? грн. |
? грн. |
? грн. |
? грн. |
? грн. |
|||
|
14 |
Кількість авто з об’ємом двигуна: |
||||||||
|
15 |
До 1500 |
? |
|||||||
|
16 |
З 1500 по 2500 |
? |
|||||||
|
17 |
Більше 2500 |
? |
Контрольні питання
PAGE 11
EMBED PBrush
Рис 2. Встановлення умов на дані, що вводяться
Параметри для
обраного обмеження
Вид обмеження
EMBED PBrush
EMBED PBrush
Рис. 3. Вікно Майстра умовного форматування
EMBED PBrush
Рис 1. Вікно Майстра функції ЕСЛИ()