• Гідрологія і Гідрометрія
• Господарське право
• Економіка будівництва
• Економіка природокористування
• Економічна теорія
• Земельне право
• Історія України
• Кримінально виконавче право
• Медична радіологія
• Методи аналізу
• Міжнародне приватне право
• Міжнародний маркетинг
• Основи екології
• Предмет Політологія
• Соціальне страхування
• Технічні засоби організації дорожнього руху
• Товарознавство продовольчих товарів

Функції з використанням умов

До функцій із використанням умов належать СУМ­МЕСЛИ, СЧЕТЕСЛИ.

Функція СУММЕСЛИ. Використовується для роз­рахунку суми значень, якізадовольняють поставлену Умову. Наприклад, необхідно розрахувати суму кредитів банку «Аваль» (див. рис. 5.1). Для цього спочатку активізують вільну комірку таблиці та кнопку Вставка Функции,у переліку знаходять функцію СУММЕСЛИ.Її аргумент Диапазонмістить значення діапазону комірок з назвами банків (АЗ:А9), серед яких віднаходять ті, що задовольняють поставлену умову.

В аргумент Критерий вносять значення умови:

• вводять значення умови з клавіатури (у прикладі - «Аваль»);

• або активізують будь-яку комірку, що містить потрібну назву (наприклад, А4).

Цей аргумент також може містити число, умовний вираз, текстове значення, адресу комірки.

Аргумент Диапазон_суммирования містить діапа­зон тих комірок, у якому відбувається підсумовування-при цьому обробляють лише ті записи, значення яких задовольняють поставлену умову. Для поданого прик­ладу це діапазон зі значеннями сум кредитів (ВЗ:В9). Якщо Диапазон_суммирования пропущений, то оброб­ляються комірки, адреси яких задано в аргументі Диа­пазон.

Друге вікно інструмента Мастер функций зображене на рис. 5.14.

Функція СЧЕТЕСЛИ. З її допомогою розрахову­ють кількість комірок діапазону, які задовольняють поставлену умову. Якщо, наприклад, слід розрахува­ти кількість значень поля «Сума комісії за видачу кредиту», що перевищують 200, то ця функція мати­ме вигляд = C4ETECUIH(F3:F9;«>200»), а її резуль­татом буде 3.

Логічні функції ЕСЛИ, И, ИЛИ, НЕ

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

До логічних функцій відносять ЕСЛИ, И, ИЛИ, НЕ.

Функція ЕСЛИ. Її використовують для розрахунку одного з кількох виразів залежно від виконання постав­леної умови. Функція має такий вигляд:

ЕСЛИ (Лог_выражение; Значение_если_истина; Значение_если_ложь).

Якщо задана умова після розрахунку має значення ИСТИНА, то розраховують значення аргументу Значение_если_истина, якщо значення умови після розраху­нку буде ЛОЖЬ — значення аргументу Значение_если ложь. При цьому аргументи можуть мати вигляд вбудованої функції ЕСЛИ. У разі складних перевірок їх буває до семи.

Наприклад, слід розрахувати таку функцію:

дех = -1;4; 9; - 5; 16; - 2,5.

Спочатку на новому робочому листку створюють таблицю, яка містить значення Xу діапазоні комірок А2:А7. Далі для розрахунку першого значення Yуста­новлюють курсор у комірку В2 й активізують кнопку Вставка функции, знаходять функцію ЕСЛИ й натис­кають кнопку ОК. З'являється вікно, зображене на Рис. 5.15.

У вікні послідовно вносять потрібні значення:

• в аргументі Лог_выражение (2) створюють умову Для першого рівняння (X < 0), причому Xв Excel — це адреса комірки, що містить значення X. Умова матиме вигляд А2 < 0;

• аргумент Значение_если_истина містить розраху­нок першого рівняння, якщо умова аргументу Лог_выражение має значення ІСТИНА. У першому рівнянні є вбудована функція модуля числа. Для її внесення необхідно активізувати список функцій, який розташований у лівій частині панелі формул (3), і вибрати команду Другие функции....

Знайшовши функцію розрахунку модуля (ABS), заносять значення в аргумент (А2) й активізують кнопку ОК. Після цього Мастер функцийзакінчує свою роботу.

Щоб продовжити створення складної функції й забезпечити надалі її коригування, слід активізувати кно­пку панелі формул (1). Після внесення значень цей аргумент матиме такий вигляд: ABS(A2)+1.

Аргумент Значение_если_ложьмістить розрахунок другого рівняння, якщо умова аргументу Логическое выражениемає значення ЛОЖЬ. У цьому разі необхідно активізувати вбудовану функцію ЕСЛИзі списку функцій на панелі (3), внести потрібні значення, після чого аргумент буде такий:

ЕСЛИ(А2>1;А2~(1/2)/2;0).

Функцію створено, далі її копіюють для всіх значень Y(за допомогою знака «+» у нижньому правому кутку комірки), внаслідок чого функція ЕСЛИ набуває вигляду, показаного на рис. 5.16.

Аналогічно можна розв'язати задачу відповідно до поставленої умови і для інших випадків. Наприклад, банк «Аваль» зменшує комісію за кредит у 1,2 раза (див. рис. 5.1). Для розрахунку використовують функ­цію ЕСЛИз такими аргументами:

=ЕСЛИ(АЗ="Аваль";Е2/1,2;ЕЗ).

Її копіюють для всього діапазону рядків і одержують розраховані значення:

1,75% , 1,04% , 1,46% , 1% , 1,5% , 1,46% , 2% .

Функція И.її використовують для об'єднання двох і більше умов. Наприклад, для перевірки, чи належить X (комірка А2 на рис. 5.16) до діапазону від 2 до 5, викорис­товують функцію И з такими аргументами: И(А2>2; А2<5). Результатом розрахунку є ЛОЖЬ (див. рис. 5.17).

ФункціяИЛИ. Перевіряє введені значення і повер­тає значення ИСТИНА, якщо хоча б один з аргументів має значення ИСТИНА. Наприклад, щоб перевірити, чи належить X (комірка А2 на рис. 5.16) до діапазону мен­ше 0 або дорівнює 10, функція має вигляд =ИЛИ(А2>1; А2<0) і повертає значення ИСТИНА.

Функція НЕ.Змінює значення свого аргументу на Протилежне. її використовують для точного визначення того значення, яке не може бути отримане. Наприклад, ^всі значення X, крім значення «-1», необхідно збільшити вдвічі. Для цього, користуючись значеннями, пода­ний на рис. 5.16, в комірці С2 створюють функцію та­кого виду: =ЕСЛИ(НЕ($А$2= -1);а2*2) і копіюють її вДіапазоні С2:С7.

Для роботи зі значеннями типу Дата і Время в таб­личному процесорі Excel існують спеціальні функції, зокрема ДАТАЗНАЧ (перетворення дати з текстового формату в числовий), ЧИСТРАБДНИ(розраховує кіль­кість робочих днів між двома датами), ГОД(повертає дату числового формату у вигляді року) та ін. Всі вони вміщені в категорії Дата и время інструмента Мастер функций. Якщо функції немає в переліку, необхідно вибрати команди Сервис, Надстройки, активізувати Analysis ToolPak.

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

Функція ДАТАповертає значення дати. Загальний вигляд функції: ДАТА(рік;місяць;день).

Наприклад, функція ДАТА(2000;2;1) залежно від установленого формату дати повертає значення 01.02.00.

Так, існує таблиця даних, де в стовпці В містяться значення років, у стовпці С — значення місяців, у стов­пці D — днів. Функція для розрахунку дати має вигляд: =ДАТА(В2;С2;Б2). Під час перетворення значення з формату дати в загальний або числовий функція повер­тає числове значення дати. Наприклад, значення функ­ції ДАТА(2001;1;2) дорівнює 36 893.

Функція ДЕНЬ повертає день дати в числовому форму. Наприклад, у комірці F2 вміщена дата 28.10.2003, тодізначення функції ДEHЬ(F2) дорівнює 28.

Функція ДЕНЬНЕД має загальний вигляд ДЕНЬНЕД(Дата, тип) і повертає порядковий номер дня тижня поданої дати. При цьому аргумент Тип визначає порядок розрахунку і може мати значення:

1— (за замовчуванням) — число від 1 (неділя) до 7;

2 — число від 1 (понеділок) до 7;

3 — число від 0 (неділя) до 6.

Наприклад, функція =ДЕНЬНЕД(Е2) (в F2 — дата 28 10.2003) повертає значення 3, а функція =ДЕНЬ-НЕД(«23.Ю.2003»;2) — значення 2.

Функція СЕГОДНЯ має загальний вигляд СЕГОД-НЯ() і повертає значення поточної дати.

Створення функцій користувача і робота з ними

У випадку, коли певні розрахунки проводять часто і результат необхідно вміщувати у різні комірки таблиці, доцільно створити власну функцію — функцію користу­вача. Для цього активізують команди Сервис, Макрос, Редактор Visual Basic. Після цього відкривається вікно Microsoft Visual Basic, у якому вибирають команди In­sert, Module. Активізується вікно Code (рис. 5.18), в якому з клавіатури вводять такі команди:

Function Рентабельність (Прибуток, Активи) Рентабельність = Прибуток / Активи * 100

End Function

Функцію створено, для роботи з нею в таблиці активізують потрібну комірку і за допомогою інструмента Мастер функційвибирають категорію Определенные пользователемі функцію Рентабельність.Далі вносят, відповідні значення в аргументи функції (рис. 5.19).

Робота з масивами значень

Масив — сукупність елементів одного типу, згрупо­ваних за рядками та (або) стовпцями.

У таблицях Excel дії з елементами масиву слід вико­нувати у певній послідовності:

• виділяють діапазон вільних комірок, який має стільки ж рядків і стовпців, як і початковий;

• у рядку формул вводять знак «=»;

• натиснувши на клавішу миші, проводять по діапа­зону початкового масиву значень;

• у рядку формул вводять потрібну формулу й акти­візують комбінацію клавіш Ctrl + Shift + Enter;

• розраховують значення кожного елемента нового масиву. При цьому створена формула автоматично бе­реться у фігурні дужки, наприклад {=А2:А6 + 10},які засвідчують, що це формула масиву.

Формули в комірках розрахованого масиву зміню­вати не можна, спочатку необхідно виділити весь ма­сив. Для цього активізують будь-яку комірку масиву і вибирають команди Правка, Перейти, Выделить, теку­щий массив.

Значенням масиву можна також надати ім'я, яке й вкористовують надалі у формулах. Для цього діапазон потрібних значень виділяють, активізують команди Вставка, Имя, Присвоить,вводять потрібне ім'я (наприклад, «Масив» і натискають на кнопку ОК. Тоді введена вище формула матиме такий вигляд: {=Масив + 10}.

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

Матриця — прямокутна таблиця чисел, яка містить m рядків і п стовпців.

Матриці можна перемножувати між собою, множи­ти на вектор, транспонувати, створювати обернені мат­риці тощо.

Спочатку створюють матрицю А у діапазоні комірок А2:С4 (див. рис. 5.21), наприклад

Після цього з елементами матриці можна виконува­ти такі дії.

Множення матриці на число. Якщо необхідно по­множити дану матрицю на число 3, виділяють область вільних комірок за розміром, що відповідає заданій ма­триці (наприклад, D2:F4). Далі у рядку формул вводять формулу =А2:С4*3 й активізують клавіші Ctrl + Shift + + Enter.У виділеному діапазоні комірок з'являються значення початкових даних, помножені на 3.

Множення матриці на вектор. Для цього викорис­товують функцію МУМНОЖ (Массиві; Массив2),де Массиві, Массив2— це масиви, які перемножуються. Кількість стовпців аргументу Массивімає дорівнювати кількості рядків аргументу Массив2.Внаслідок мно­ження матриць утворюється масив з такою самою кіль­кістю рядків, що й масив 1, і з такою самою кількістю стовпців, що й масив 2. Множення матриць здійснюють ³& формулою:

Де b_ij — елемент нового масиву; і — номер рядка; j — Номер стовпця; a_ik — елемент масиву 1; c_kj — елемент Масиву 2; k — номер рядка масиву 1 або номер стовпця масиву 2; п — розмір матриці. При множенні матриці на вектор розрахована матриця має розмірність вектора.

Спочатку створюють вектор у діапазоні G2:G4, який повинен мати стільки рядків, скільки стовпців у матра­ці. Далі виділяють діапазон комірок, де буде створюва­тися нова матриця (А7:А9), активізують кнопку Вставка Функции, вибирають функцію МУМНОЖ і вносять відповідні значення діапазонів матриці А та вектора С (рис. 5.20).

Після цього встановлюють курсор на рядок формул, активізують клавіші Ctrl + Shift + Enter й одержують:

МУМНОЖ(А2 :С4 ;G2 :G4).

Створення обернених матриць.Такі матриці за­стосовують для розв'язання систем рівнянь з кількома невідомими. Для цього активізують функцію МОБР, що виконує обчислення з точністю до 16-значних чисел (для округлення цих значень слід користуватися функ­цією ОКРУГЛ).

Наприклад, треба створити обернену матрицю з еле­ментів матриці А. Для цього виділяють діапазон комі­рок (D7:F9), де буде створюватися нова матриця, акти­візують кнопку Вставка функции, вибирають функцію МОБР і заносять відповідні значення елементів матри­ці А:

МОБР(А2:С4).

Після цього встановлюють курсор на рядок формул і натискають на клавіші Ctrl + Shift + Enter.

Транспонування матриць. Для цього використовують функцію ТРАНСП, яка переводить вертикальний діапазон комірок у горизонтальний, і навпаки. Наприклад необхідно створити транспоновану матрицю з елементів матриці А. З цією метою виділяють діапазон комірок, де буде створюватися нова матриця (А12:С14), активізують кнопку Вставка функции, вибирають функцію ТРАНСПвносять відповідні значення елементів мат­риці А:

ТРАНСП(А2:С4).

Потім встановлюють курсор на рядок формул й ак­тивізують клавіші Ctrl + Shift + Enter.

Щоб перевірити, чи правильний розрахунок, пере­множують матрицю А на обернену матрицю, використо­вуючи функцію МУМНОЖ(А2:С4;Б7:Е9). Внаслідок цього отримують матрицю з елементами 1 по діагоналі.

Розраховані значення та функції, що застосовують для роботи з матрицями, подані в екранних вікнах на рис. 5.21 і 5.22.

Побудова діаграм

Графічні зображення, або діаграми, більш наочно ілюструють табличні дані, дають змогу аналізувати от­римані результати.

Нехай, наприклад, треба знайти залежність суми комісії від терміну кредиту. Для побудови діаграми слід виділити (провести покажчиком миші) ті місця табли­ці, дані яких увійдуть до діаграми (у даному прикладі це діапазони А2:А9; D2:D9; F2:F9), після чого активізу­вати кнопку (Мастер диаграмм).

Створення діаграми відбувається у чотири кроки.

Крок 1. У першому вікні, що зображене на рис. 5.23, необхідно вибрати тип діаграми (наприклад, Гисто­грамма — графічне зображення статистичного розподі­лу величини, що має вигляд ряду різновисоких прямо­кутників) й активізувати кнопку Далее.

Крок 2. У наступному вікні (рис. 5.24) перевіряють попередньо визначений діапазон даних таблиці, а та­кож місцерозташування значень — у рядках чи стовп­цях. При цьому можна побачити діаграму, що будуєть­ся. Потім активізують кнопку Далее.

Крок 3. У наступному вікні зазначають назву діаграми та назви вісей X, Y.Відкривши відповідну вкладку цього вікна, можна створити написи на діаграмі, визна­чити лінії сітки, наявність і розташування легенди -текстового пояснення до кожного об'єкта діаграми.

Крок 4. Активізують кнопку Далее в наступному вікні й визначають місцерозташування діаграми: на окремому робочому листку чи на поточному (там, де вміщено таблицю з даними).

Після активізації кнопки Готовона екрані дисплей з'являються таблиця і побудована гістограма (рис. 5.25).

Для редагування наявної діаграми її слід виділити, клацнувши на ній мишею, й активізувати потрібну команду меню Диаграмма, що з'являється в рядку го­ловного меню.

Читайте також:

1. А. Розрахунки з використанням дистанційного банкінгу.
2. Адвокатура в Україні: основні завдання і функції
3. Алгоритм знаходження ДДНФ (ДКНФ) для даної булевої функції
4. Але відмінні від значення функції в точці або значення не існує, то точка називається точкою усувного розриву функції .
5. Аналіз коефіцієнтів цільової функції
6. АРХІВНІ ДОВІДНИКИ В СИСТЕМІ НДА: ФУНКЦІЇ ТА СТРУКТУРА
7. АРХІВНІ ДОВІДНИКИ В СИСТЕМІ НДА: ФУНКЦІЇ ТА СТРУКТУРА
8. Асимптоти графіка функції
9. Асимптоти графіка функції
10. Аудиторські ризики, пов’язані з використанням комп’ютерних інформаційних систем
11. Аутентифікація з використанням односторонніх функцій
12. Базальні ядра, їх функції, симптоми ураження

Переглядів: 2017