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

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

Функція СУММЕСЛИ. Використовується для роз­рахунку суми значень, якізадовольняють поставлену Умову. Наприклад, необхідно розрахувати суму кредитів банку «Аваль» (див. рис. 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).

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

Поиск по сайту: