Главная страница Случайная страница
Разделы сайта
АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника
Теоретичні відомості. ТЕМА: Застосування функцій в Excel.
|
|
ЛАБОРАТОРНА РОБОТА №21
ТЕМА: Застосування функцій в Excel.
МЕТА: Ознайомитися з категоріями функцій, навчитися застосовувати основні математичні, статистичні, логічні, текстові функції.
Теоретичні відомості
Excel має вбудовану бібліотеку функцій, до якої входять більше ніж 300 різноманітних функцій. Усі вони для зручності пошуку розподілені на групи (категорії): математичні, статистичні, логічні, фінансові, текстові та ін.
Функція в Excel має ім'я і результат, є функції з аргументами і без аргументів.
Аргументом функції може бути число, текст (його потрібно брати в подвійні лапки), вираз, посилання на клітинку або діапазон клітинок, результат іншої функції.
Під час використання функції у формулі спочатку вказується її ім'я, а потім, якщо функція має аргументи, у дужках вказується список аргументів через крапку з комою. Якщо функція не має аргументів, то в дужках після імені функції нічого не вказується.
Призначення кожної функції, наявність аргументів та їхня кількість, типи аргументів можна подивитися в Довідці або в коментарях під час уведення функції у формулу.
Вставити функцію у формулу можна кількома способами:
1) використати список функцій кнопки категорії функцій у групі Бібліотека функцій вкладки Формули на Стрічці;
2) виконати Формули => Бібліотека функцій => Вставити функцію;
3) вибрати кнопку Вставлення функції Рядка формул;
4) увести безпосередньо в клітинку або в Рядок формул.
Розглянемо детальніше кожний із цих способів.
Відкривши список однієї з кнопок категорій функцій, можна вибрати ім'я потрібної функції. За наведення вказівника на ім'я функції спливає коротка підказка про її призначення. Після вибору імені функції в поточну клітинку автоматично вставляється знак = (якщо в цій клітинці введення формули ще не розпочиналося), ім'я функції і пара круглих дужок, а також відкривається вікно Аргументи функції з полями для введення аргументів цієї функції.
Якщо функція має фіксовану кількість аргументів, то вікно Аргументи функції одразу містить відповідну кількість полів для їхнього введення. Якщо функція має нефіксовану кількість аргументів, то у вікні спочатку з'являється кілька полів, а потім, у процесі введення аргументів, з'являються наступні поля.
Якщо аргументом є число або текст, то його потрібно вводити в поле з клавіатури.
Якщо аргументом є посилання на клітинки, то його можна вводити або з клавіатури, або виділити відповідні клітинки з використанням миші.
Після введення в поля всіх потрібних аргументів функції необхідно вибрати кнопку ОК.
Вікно Аргументи функції містить коментарі про призначення даної функції та її аргументів. Крім того, під час уведення аргументів справа від поля з'являються їхні значення і в інформаційній частині вікна відображаються поточні результати обчислення.
| Рис.21.1. Діалогове вікно Вставлення функції |
Якщо виконати Формули => Бібліотека функцій => Вставити функцію або вибрати кнопку Вставлення функції Рядка формул, то відкриється вікно Вставлення функції (рис. 21.1). У цьому вікні в списку поля Категорія можна вибрати потрібну категорію, після чого в списку поля Виберіть функцію вибрати потрібну функцію. Після вибору кнопки ОК відкривається вікно Аргументи функції і далі введення функції в формулу відбувається аналогічно способу, розглянутому вище.
Можна також уводити функцію у формулу безпосередньо в клітинку або в поле Рядка формул. Уводити з клавіатури імена функцій і посилання можна як малими, так і великими англійськими літерами.
Після введення першої літери імені функції відкривається список імен функцій, що починається з цієї літери. Уведення наступної літери імені буде змінювати список. Для вибору функції з відкритого списку потрібно двічі клацнути на імені необхідної функції або встановити курсор на імені функції (при цьому спливає коментар про призначення цієї функції) і натиснути клавішу Tab.
Після того як у формулу вставилося ім'я функції, біля клітинки з формулою спливає підказка з кількістю аргументів функції та типом цих аргументів.
Аргумент, який потрібно ввести наступним, виділяється напівжирно. Аргумент, узятий у квадратні дужки, є необов'язковим.
Звертаємо вашу увагу:
1. За вибору функції зі списку кнопки Автосума, яка знаходиться як у групі Бібліотека функцій вкладки Формули, так і в групі Редагування вкладки Основне, вікно Аргументи функції не відкривається, а розпочинається введення функції безпосередньо в клітинку.
2. Список будь-якої кнопки групи Бібліотека функцій вкладки Формули містить команду Інші функції або Вставити функцію, вибір якої відкриває вікно Вставлення функції.
Після початку введення формули (знака =) поле Ім'я змінюється на поле Функції, і в ньому з'являється ім'я функції, яка використовувалася останньою. Вибравши кнопку цього поля, одержимо список імен 10 функцій, які використовувалися останніми. Це поле можна також використовувати для введення імен функцій у формулу, зокрема під час уведення функції як аргументу іншої функції.
Математичні функції в Excel – найчисленніша група функцій. За їхньою допомогою дуже легко проводити різноманітні розрахунки.
Електронні таблиці представляють собою природне середовище для роботи з матрицями, оскільки в комірках таблиць зручно розташовувати матричні елементи.
В Excel множення матриць А та В можна виконати за допомогою спеціальної функції для роботи з масивами. MУMHOЖ(мaccив1; мaccив2) (MMULT).
Спочатку в електронну таблицю вводимо елементи матриці A і B. Далі виділимо діапазон, де буде розташована матриця результату С.
Вводимо функцію масиву =MУMHOЖ(мaccив1; мaccив2), що здійснює множення матриць. Далі вказуємо діапазони комірок, в яких міститься перша матриця А і друга матриця В. Аргументи функції (вказані діапазони) відокремлюються крапкою з комою.
Щоб завершити введення формули множення матриць, треба натиснути комбінацію клавіш < Ctrl+Shift+Enter>. Дана комбінація використовується для введення функції =MУMHOЖ(B4: C6; B8: E9) у всі комірки діапазону результату. Натиснення однієї клавіші < Enter> приведе до введення формули лише в одну комірку, і в електронній таблиці буде відображено лише один елемент матриці-добутку С.
Знаходження оберненої матриці для даної називається оберненням цієї матриці. Операція обернення матриць може бути застосована лише до квадратних неособливих (несингулярних) матриць. Оберненою матрицею по відношенню до даної називається матриця, множення на яку даної матриці як справа так і зліва дає одиничну матрицю.
В Excel обернення матриці можна здійснити за допомогою функції масиву MOБP (MINVERSE). Введемо в електронну таблицю в діапазон комірок матрицю A розмірності 3× 3. Виділимо діапазон комірок, де буде розміщено результат.
Вводимо функцію масиву =MOБP(мaccив). Далі вказуємо діапазон комірок, в якому міститься матриця А, яку обертають. Щоб завершити введення формули обернення матриці, треба натиснути комбінацію клавіш < Ctrl+Shift+Enter>.
Розв’язання систем лінійних рівнянь за допомогою оберненої матриці.
Запишемо систему лінійних алгебраїчних рівнянь (СЛАР) у вигляді матричного рівняння AxХ=В,
де А – матриця коефіцієнтів системи, В – вектор-стовпець її вільних членів, Х – вектор-стовпець невідомих. Якщо матриця А – неособлива, тобто її визначник det A≠ 0, то існує обернена матриця A-1. Множачи обидві частини рівняння зліва на A-1, одержимо
A-1хAхХ= A-1хВ
Або Х= A-1хВ.
В результаті отримано вектор, компонентами якого є шукані невідомі.
Для розрахунку суми комірок діапазону, які задовольняють заданий критерій, використовують функцію СУММЕСЛИ (SUMIF). Функція СУММЕСЛИ має три параметри з таким синтаксисом:
СУММЕСЛИ (Диапазон; Критерий; Диапазон_Суммирования)
· Диапазон задає прямокутну множину комірок, які функція бере до розгляду. Розривні діапазони не підтримуються.
· Критерий – це вираз з умовою для значень у комірках діапазону. Задається аналогічно функції СЧЁТЕСЛИ
Диапазон _ Суммирования визначає діапазон комірок, в якому, власне, виконується додавання значень комірок, для яких відповідні комірки у параметрі Диапазон задовольняють Критерий. Параметр Диапазон _ Суммирования можна не зазначати. У цьому разі береться сума комірок у Диапазон (які, звичайно ж, задовольняють критерій).
Розглянемо деякі логічні функції. Функція ЕСЛИ (IF) має три параметри з таким синтаксисом:
ЕСЛИ(Условие; Значение для Истина; Значение для ЛОЖЬ)
· Условие – будь-який вираз, значення якого буде або ИСТИНА, або ЛОЖЬ.
· Значение для Истина – це значення чи вираз, який поверне функція ЕСЛИ як істинне значення параметра Условие. Якщо параметр не зазначити, то як результат ЕСЛИ повернеться ИСТИНА. Виразом у параметрі, зокрема, може бути будь-яка інша функція, зокрема ЕСЛИ
· Значение для ЛОЖЬ – це значення чи вираз, який поверне функція ЕСЛИ як хибне значення параметра Условие. Якщо параметр не зазначити, то як результат ЕСЛИ повернеться ЛОЖЬ. Виразом у параметрі, зокрема, може бути будь-яка інша функція, зокрема ЕСЛИ
· Зверніть увагу: якщо у функції ЕСЛИ пропускається другий параметр, то символ „; ” перед третім параметром має бути обов’язково: ЕСЛИ(Условие;; Значение для ЛОЖЬ).
· Допускається до семи вкладень функцій ЕСЛИ одна в одну.
· Для складніших умовних виразів, коли задається багато значень, потрібно використовувати функції И(Р1; Р2;...) (AND) та ИЛИ(Р1; Р2;...) (OR), де Рn – умовний вираз. Ці функції мають бути вкладені у функцію ЕСЛИ на місці Условие.
Розглянемо деякі статистичні функції.
Для розрахунку кількості комірок у діапазоні, які задовольняють певний критерій, використовують функцію СЧЁТЕСЛИ (COUNTIF). Функція має два параметри з таким синтаксисом:
СЧЁТЕСЛИ(Диапазон; Критерий)
· Диапазон задає прямокутну множину комірок, які функція бере до розгляду. Розривні діапазони не підтримуються. Значеннями в комірках можуть бути числові дані в усіх різновидах, логічні значення, слова і фрази (комірки можуть бути також порожні). Допустимі значення можна задавати як константою, так і формулою.
· Критерий – це вираз з умовою для значень у комірках діапазону. Вираз (умова) подається у вигляді:
· точного значення, якому і мають задовольняти комірки;
· виразу найтиповішого відношення рівності/нерівності, яке після обчислення давало б значення ИСТИНА чи ЛОЖЬ (наприклад, СЧЁТЕСЛИ(Е16: Е24; „> =200”) задає критерій у вигляді відношення нерівності). У разі визначення критерію у вигляді відношення його форма має бути задана за правилами об’єднання тексту у формулі.
Функція СРЗНАЧЕСЛИ (AVERAGEIF) повертає середнє значення всіх клітинок у діапазоні, які відповідають певній умові.
Синтаксис:
СРЗНАЧЕСЛИ(діапазон, умови, [діапазон_усереднення]), де
Синтаксис функції СРЗНАЧЕСЛИ має такі аргументи:
· діапазон – обов’язковий аргумент. Одна або кілька клітинок, які містять числа, імена, масиви або посилання на числа, для яких потрібно обчислити середнє значення.
· умови – обов’язковий аргумент. Умова у вигляді числа, виразу, посилання на клітинку або тексту, що визначає, для яких клітинок потрібно обчислити середнє значення.
· діапазон_середн – необов’язковий аргумент. Фактичний набір клітинок для обчислення середнього значення.
Розглянемо деякі текстові функції.
Функція LEFT повертає перший символ або символи в текстовому рядку, залежно від заданої кількості символів.
Синтаксис:
LEFT(текст, [кількість_символів]), де
Текст – обов’язковий аргумент. Текстовий рядок, що містить символи, які потрібно витягнути.
Кількість_символів – необов’язковий аргумент. Указує кількість символів, які має витягнути функція LEFT.
Якщо аргумент " кількість_символів" перевищує довжину тексту, функція LEFT повертає весь текст.
Функція CONCATENATE об’єднує до 255 текстових рядків в один. Об’єднані елементи можуть бути текстом, числами, посиланнями на клітинки або комбінацією цих елементів.
Синтаксис
CONCATENATE(текст1, [текст2],...), де
Текст1 – перший текстовий елемент, який потрібно об'єднати;
Текст2... – додаткові елементи тексту, до 255. Елементи потрібно відокремлювати комами. (Додатковий аргумент.).
ПРИМІТКА Щоб об'єднати елементи тексту, замість функції CONCATENATE можна скористатись оператором амперсанд (&). Наприклад: =A1 & B1 повертає те саме значення, що й =CONCATENATE(A1, B1)
Хід роботи (1 з 5 варіантів)
1. Розв’яжіть систему лінійних рівнянь:
Для цього:
ü запишіть на аркуші Excel матрицю А (її елементами є коефіцієнти при невідомих) та матрицю В (її елементами є вільні члени)Ж
ü за допомогою математичної функції MINVERSE (МОБР) знайдіть матрицю обернену до матриці А;
ü за допомогою математичної функції MMULT (МУМНОЖ) перемножте матрицю А-1 на матрицю вільних членів В.
2. Побудуйте на аркуші «Працівники фірми» таблицю:
3. За допомогою функцій дати і часу YEAR (ГОД) та TODAY (СЕГОДНЯ) обчисліть вік кожного працівника.
4. У стовпці Премія, використовуючи логічну функцію IF (ЕСЛИ), нарахуйте премію у відсотках окладу для ювілярів: 1) 20 років – 120 %; 2) З0 років – 150 %; 3) 40 років – 200 %.
5. Розрахуйте кількість продавців та рекламних агентів за допомогою статистичної функції COUNTIF (СЧЕТЕСЛИ).
6. Визначте загальну зарплату продавців та рекламних агентів, використовуючи математичну функцію SUMIF (СУММЕСЛИ).
7. Визначте середню зарплату продавців та рекламних агентів, використовуючи статистичну функцію AVERAGEIF (СРЗНАЧЕСЛИ).
8. Заповніть стовпець Прізвище та ініціали, використовуючи текстові функції CONCATENATE (СЦЕПИТЬ) та LEFT (ЛЕВСИМВ).
9. Оформіть і здайте звіт.
Хід роботи (Варіант 2)
1. Розв’яжіть систему лінійних рівнянь:
Для цього:
ü запишіть на аркуші Excel матрицю А (її елементами є коефіцієнти при невідомих) та матрицю В (її елементами є вільні члени)Ж
ü за допомогою математичної функції MINVERSE (МОБР) знайдіть матрицю обернену до матриці А;
ü за допомогою математичної функції MMULT (МУМНОЖ) перемножте матрицю А-1 на матрицю вільних членів В.
2. Побудуйте на аркуші «Відомість» таблицю:
3. За допомогою функцій дати і часу YEAR (ГОД) та TODAY (СЕГОДНЯ) обчисліть вік кожного студента.
4. У стовпці Пора року, використовуючи логічні функцію IF (ЕСЛИ) та OR (ИЛИ), визначте, в якій порі року народився кожен студент.
5. Розрахуйте кількість «5», «4», «3», «2» та «н/а» за допомогою статистичної функції COUNTIF (СЧЕТЕСЛИ).
6. Визначте кількісний показник за формулою: (Кількість «5» + Кількість «4» + Кількість «3»)/Кількість студентів.
7. Визначте якісний показник за формулою: (Кількість «5» + Кількість «4)/Кількість студентів.
8. Заповніть стовпець Прізвище та ініціали, використовуючи текстові функції CONCATENATE (СЦЕПИТЬ) та LEFT (ЛЕВСИМВ).
9. Оформіть і здайте звіт.
Хід роботи (Варіант 3)
1. Розв’яжіть систему лінійних рівнянь:
Для цього:
ü запишіть на аркуші Excel матрицю А (її елементами є коефіцієнти при невідомих) та матрицю В (її елементами є вільні члени)Ж
ü за допомогою математичної функції MINVERSE (МОБР) знайдіть матрицю обернену до матриці А;
ü за допомогою математичної функції MMULT (МУМНОЖ) перемножте матрицю А-1 на матрицю вільних членів В.
2. Побудуйте на аркуші «Працівники фірми» таблицю:
3. За допомогою функцій дати і часу YEAR (ГОД) та TODAY (СЕГОДНЯ) обчисліть вік кожного працівника.
4. У стовпці Премія, використовуючи логічну функцію IF (ЕСЛИ), нарахуйте премію у відсотках окладу для ювілярів: 1) 20 років – 120 %; 2) З0 років – 150 %; 3) 40 років – 200 %.
5. Розрахуйте кількість продавців, менеджерів та консультантів за допомогою статистичної функції COUNTIF (СЧЕТЕСЛИ).
6. Визначте загальну зарплату продавців, менеджерів та консультантів, використовуючи математичну функцію SUMIF (СУММЕСЛИ).
7. Визначте середню зарплату продавців, менеджерів та консультантів, використовуючи статистичну функцію AVERAGEIF (СРЗНАЧЕСЛИ).
8. Заповніть стовпець Прізвище та ініціали, використовуючи текстові функції CONCATENATE (СЦЕПИТЬ) та LEFT (ЛЕВСИМВ).
9. Оформіть і здайте звіт.
Хід роботи (Варіант 4)
1. Розв’яжіть систему лінійних рівнянь:
Для цього:
ü запишіть на аркуші Excel матрицю А (її елементами є коефіцієнти при невідомих) та матрицю В (її елементами є вільні члени)Ж
ü за допомогою математичної функції MINVERSE (МОБР) знайдіть матрицю обернену до матриці А;
ü за допомогою математичної функції MMULT (МУМНОЖ) перемножте матрицю А-1 на матрицю вільних членів В.
2. Побудуйте на аркуші «Відомість» таблицю:
3. 
За допомогою функцій дати і часу YEAR (ГОД) та TODAY (СЕГОДНЯ) обчисліть вік кожного студента.
4. У стовпці Успішність, використовуючи логічні функції IF (ЕСЛИ) та OR (ИЛИ), встановіть відповідні значення: 1) Сер. бал 5 – «відмінно»; 2)) Сер. бал в межах 4-4, 9 – «добре»; 3) Сер. бал в межах 3-3, 9 – «задовільно».
5. Розрахуйте кількість хлопців та дівчат за допомогою статистичної функції COUNTIF (СЧЕТЕСЛИ).
6. Визначте середній бал хлопців та дівчат, використовуючи статистичну функцію AVERAGEIF (СРЗНАЧЕСЛИ).
7. Визначте загальну стипендію хлопців та дівчат, використовуючи математичну функцію SUMIF (СУММЕСЛИ).
8. Визначте середню стипендію хлопців та дівчат, використовуючи статистичну функцію AVERAGEIF (СРЗНАЧЕСЛИ).
9. Заповніть стовпець Прізвище та ініціали, використовуючи текстові функції CONCATENATE (СЦЕПИТЬ) та LEFT (ЛЕВСИМВ).
10. Оформіть і здайте звіт.
Хід роботи (Варіант 5)
1. Розв’яжіть систему лінійних рівнянь:
Для цього:
ü запишіть на аркуші Excel матрицю А (її елементами є коефіцієнти при невідомих) та матрицю В (її елементами є вільні члени)Ж
ü за допомогою математичної функції MINVERSE (МОБР) знайдіть матрицю обернену до матриці А;
ü за допомогою математичної функції MMULT (МУМНОЖ) перемножте матрицю А-1 на матрицю вільних членів В.
2. 
Побудуйте на аркуші «Замовлення» таблицю:
3. У стовпці Виконання, використовуючи логічну функцію IF (ЕСЛИ), встановіть значення «1», якщо послугу виконано, і значення «0», – якщо не виконано.
4. Заповніть стовпець Прізвище та ініціали виконавця, використовуючи текстові функції CONCATENATE (СЦЕПИТЬ) та LEFT (ЛЕВСИМВ).
5. Розрахуйте кількість замовлених послуг клієнтами Гранд, Дербі та Вікна за допомогою статистичної функції COUNTIF (СЧЕТЕСЛИ).
6. Визначте вартість послуг для клієнтів Гранд, Дербі та Вікна, використовуючи математичну функцію SUMIF (СУММЕСЛИ).
7. Визначте середню вартість послуг для клієнтів Гранд, Дербі та Вікна, використовуючи статистичну функцію AVERAGEIF (СРЗНАЧЕСЛИ).
8. Розрахуйте кількість замовлених послуг 20.01.14, 22.01.14, 23.01.14, 24.01.14 та 25.01.14 за допомогою статистичної функції COUNTIF (СЧЕТЕСЛИ).
9. Визначте вартість послуг 20.01.14, 22.01.14, 23.01.14, 24.01.14 та 25.01.14, використовуючи математичну функцію SUMIF (СУММЕСЛИ).
10. Визначте середню вартість послуг 20.01.14, 22.01.14, 23.01.14, 24.01.14 та 25.01.14, використовуючи статистичну функцію AVERAGEIF (СРЗНАЧЕСЛИ).
11. Оформіть і здайте звіт.
Контрольні запитання
1. Як Excel розуміє поняття «функція»?
2. Опишіть методи введення функцій.
3. Скільки вкладених конструкцій ЕСЛИ() може бути у функції ЕСЛИ?
4. Які існують варіанти задавання критеріїв функцій СЧЁТЕСЛИ, ЕСЛИ?
5. Скільки параметрів у функції СУММЕСЛИ?
6. Як знайти добуток матриць?
7. Як знайти обернену матрицю?
8. Як розв’язати систему лінійних рівнянь?
|
|