Новини освіти і науки:

G+

Тлумачний словник
Авто
Автоматизація
Архітектура
Астрономія
Аудит
Біологія
Будівництво
Бухгалтерія
Винахідництво
Виробництво
Військова справа
Генетика
Географія
Геологія
Господарство
Держава
Дім
Екологія
Економетрика
Економіка
Електроніка
Журналістика та ЗМІ
Зв'язок
Іноземні мови
Інформатика
Історія
Комп'ютери
Креслення
Кулінарія
Культура
Лексикологія
Література
Логіка
Маркетинг
Математика
Машинобудування
Медицина
Менеджмент
Метали і Зварювання
Механіка
Мистецтво
Музика
Населення
Освіта
Охорона безпеки життя
Охорона Праці
Педагогіка
Політика
Право
Програмування
Промисловість
Психологія
Радіо
Регилия
Соціологія
Спорт
Стандартизація
Технології
Торгівля
Туризм
Фізика
Фізіологія
Філософія
Фінанси
Хімія
Юриспунденкция

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

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

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

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

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

<== попередня сторінка	\|	наступна сторінка ==>
Використання функцій	\|	Робота зі списками

Не знайшли потрібну інформацію? Скористайтесь пошуком google:

Генерація сторінки за: 0.011 сек.