Лабораторна робота № 5.2 (4 години)

працювати з неструктурованою інформацією, з реальними джерелами економічних даних; оцінювати повноту та релевантність даних, необхідних для розв’язування економічних завдань; добирати засоби ІКТ для ефективного розв’язування поставлених задач.

Самостійна робота студентів для підготовки до лабораторної роботи.

1. Студенти повинні ознайомитися з особливостями використання кейс-методу на заняттях з інформатики та прикладами розв’язаних кейсів [4, C. 5—82].

2. Виконання тестових завдань, що представлені у збірнику кейсів [4, C. 211—264], з метою самоперевірки готовності до розв’язування кейсів та за потреби — корекції знань.

Робота студентів на лабораторній роботі.

Працюючи в малій творчій групі, розв’язати запропонований або заздалегідь обраний кейс [4, C. 83—210] та представити для обговорення отримані результати. Прийняти участь у обговоренні отриманих результатів та підбитті підсумків заняття.

Інформаційне забезпечення підготовки до проведення лабораторної роботи.

Під час підготовки до лабораторної роботи студенти повинні ознайомитися з літературними джерелами: [4].

5. ПРИКЛАДИ ТИПОВИХ ЗАВДАНЬ,
ЩО ВИНОСЯТЬСЯ НА ІСПИТ

1. Розв’язання задач економічного аналізу в MS Excel.

Завдання:

Дана БД:

1. Написати послідовність дій для розрахунку значень поля Вартість матеріалів (Вартість матеріалів = Кількість, т * Ціна за 1т, грн.).

2. Записати діапазони звичайного та обчислювального критерію для пошуку записів за критеріями:

a) записи 4-го місяцю;

b) записи цегли;

c) записи 4-го місяцю, вартість матеріалу яких не перевищує 1 000 000;

d) записи цегли та каменю;

e) записи з кількістю від 2 000 до 5 000;

f) записи за 4 місяць з кількістю від 2 000 до 5 000;

g) записи за 4 та 5 місяці з кількістю не меншою 2 000;

h) записи 5 місяця, у яких вартість матеріалу була або менша за 300 000 грн., або більша за 700 000 грн.;

i) записи з максимальною вартістю матеріалу;

j) записи, у яких вартість більше середньої або ціна є максимальною;

k) записи, у яких вартість більше кількості, збільшеної у 300 разів.

3. Використовуючи відповідні функції MS Excel, визначити:

a) кількість записів 4-го місяця;

b) загальну вартість цегли;

c) середню вартість цегли;

d) максимальну вартість цегли;

e) кількість записів, у яких ціна не перевищує 400 грн.;

f) кількість записів, у яких вартість матеріалу більше середньої;

g) кількість записів цегли, у яких вартість матеріалу більше середньої.

Розв’язання:

1. Для розрахунку значень поля Вартість матеріалів: у комірку Е2 заноситься формула =C2*D2 і копіюється у комірки Е3:Е10 за допомогою маркера автозаповнення:

2. Діапазони звичайного та обчислювального критерію створюються під БД. Для завдань а)—h) створити можна обидва діапазони критерію, для завдань і)—j) створення та використання діапазону звичайного критерію не є коректним (оскільки в ньому не можна використовувати формули), для завдання k) взагалі не можна створити діапазон звичайного критерію:

a) діапазон звичайного критерію — А12:А13, обчислювального – Е12:Е13

b) діапазон звичайного критерію — А15:А16, обчислювального – Е15:Е16

c) діапазон звичайного критерію — А18:В19, обчислювального – Е18:Е19

d) діапазон звичайного критерію — А21:А23, обчислювального – Е21:Е22

e) діапазон звичайного критерію — А25:В26, обчислювального – Е25:Е26

f) діапазон звичайного критерію — А28:С29, обчислювального – Е28:Е29

g) діапазон звичайного критерію — А31:В33, обчислювального – Е31:Е32

h) діапазон звичайного критерію — А35:В37, обчислювального – Е35:Е36

i) діапазон обчислювального критерію — Е39:Е40

j) діапазон обчислювального критерію — Е42:Е43

k) діапазон обчислювального критерію — Е45:Е46

3. Для проведення умовних розрахунків за БД використовуються або функції СЧЕТЕСЛИ та СУММЕСЛИ, або функції баз даних. Загальний синтаксис функцій такий:

СЧЕТЕСЛИ (діапазон перевірки умови; умова)

СУММЕСЛИ (діапазон перевірки умови; умова; діапазон підсумовування)

ім’я функції БД (діапазон бази даних; поле БД; діапазон критерію)

Якщо необхідно знайти кількість або суму та умова є простою можна використати одну з вказаних функцій, якщо проводиться інша операція або умова складна – необхідно створити попередньо діапазон критерію (звичайний або обчислювальний) та використати відповідну функцію БД.

	формула	результат
a)	=СЧЁТЕСЛИ(A2:A10;4)	=БСЧЁТ(A1:E10;;A12:A13)
b)	=СУММЕСЛИ(B2:B10;"Цегла";E2:E10)	=БДСУММ(A1:E10;E1;A15:A16)
c)	=СУММЕСЛИ(B2:B10;"Цегла";E2:E10) /СЧЁТЕСЛИ(B2:B10;"Цегла")	=ДСРЗНАЧ(A1:E10;E1;A15:A16)
d)	–	=ДМАКС(A1:E10;E1;A15:A16)
e)	=СЧЁТЕСЛИ(D2:D10;"<400")	=БСЧЁТ(A1:E10;;I1:I2)
f)	=СЧЁТЕСЛИ(E2:E10; ">"&СРЗНАЧ(E2:E10))	=БСЧЁТ(A1:E10;;I4:I5)
g)	–	=БСЧЁТ(A1:E10;;I7:I8)

2. Розв’язання задач фінансового аналізу в MS Excel.

Завдання:

У Вас є вибір: отримати від свого дядечка 1 000 дол. через рік або 900 дол. сьогодні від своєї тітоньки. Річна ставка в улюбленому банку становить 12%, відсотки нараховуються щомісяця.

a) Яка майбутня вартість подарунка від дядечка? Від тітоньки? Який подарунок Ви оберете?

b) Чи змінилася б ваша відповідь, якщо ставку знизили до 10%?

c) За якої процентної ставки не має різниці який подарунок обрати?

Розв’язання:

Для вирішення цієї дилеми необхідно порівняти майбутню вартість обох подарунків. Подарунок від дядечка через рік так і буде коштувати 1 000 дол., а вартість подарунка тітоньки дорівнює сумі, що буде через рік на рахунку у банку, початковий розмір якого 900 дол. Тому майбутня вартість подарунка тітоньки розраховується за формулою, у якій використовується функція БС. Ця функція має синтаксис:

БС (ставка; кількість періодів;розмір періодичних виплат; початкове значення)

За умовою задачі період нарахування відсотків – місяць, тому кількість періодів дорівнює 1*12, а ставка за період – 12%/12. Розмір періодичних виплат дорівнює 0, оскільки виплати не здійснюються. Початковий внесок на рахунок у банку становить -900 (враховується напрям руху грошей). Отже, майбутня вартість подарунка тітоньки розраховується за формулою:

=БС(12%/12;1*12;0;-900)

Це становить 1 014,14 дол., тому треба обрати подарунок тітоньки. Якщо ставку знизили до 10%, подарунок тітоньки буде коштувати лише 994,24 дол. (=БС(10%/12;1*12;0;–900)), а тому в цій ситуації краще обрати подарунок дядечка. Ставка, за якої обидва подарунки є рівноцінними, — це ставка, за якої майбутня вартість 900 дол. становить 1 000 дол., і її значення обчислюється за формулою:

=СТАВКА(1*12;0;–900;1000)*12

Отримаємо, що, за ставки 10,6 % не має різниці який подарунок обрати.

3. Розв’язання задач статистичного аналізу в MS Excel.

Завдання:

На основі статистичних даних спрогнозувати курс гривні щодо долара США:

1. у вказані місяці, вважаючи що має місце лінійна парна регресія;

2. за вказаних значень курсів євро та російського рубля, вважаючи що має місце лінійна множинна регресія.

Розв’язання:

1. Для прогнозування курсу USD у вказані місяці можна використати один із п’яти способів (шостий спосіб — використання автозаповнення — не можна застосувати, оскільки дати непослідовні):

1) аналітичне прогнозування курсу у першу дату з використанням функції ТЕНДЕНЦИЯ з подальшим копіюванням формули для інших дат (рис. 1);

2) аналітичне прогнозування курсу одночасно для всіх дат з використанням функції ТЕНДЕНЦИЯ та формули масиву (рис. 2);

3) аналітичне прогнозування курсу у першу дату з використанням функції ПРЕДСКАЗ з подальшим копіюванням формули для інших дат (рис. 3);

4) аналітичне прогнозування курсу одночасно для всіх дат з використанням функції ПРЕДСКАЗ та формули масиву (рис. 4);

5) графічне прогнозування за допомогою лінії тренду (рис. 5).

Отримані результати тотожні (рис. 6).

Рис. 1. Рис. 2

Рис. 3. Рис. 4.

Рис. 5. Рис. 6.

2. Для прогнозування курсу USD за вказаних значень курсів іноземних валют використовувати функцію ПРЕДСКАЗ не можна, оскільки вона використовується тільки для парної регресії, тому використовують функцію ТЕНДЕНЦИЯ та один з перших двох вказаних способів (Рис. 7, 8).

Отримані результати тотожні (Рис. 9).

Рис. 7. Рис. 8.

Рис. 9.

4. Розв'язання задач на знаходження екстремумів та задач оптимізації.

Завдання:

Знайти умовний максимум функції у = ax ₁+ bx ₂, де a =2, b =5, за обмежень: х ₁²+ х ₂²<=100 та x ₁ i x ₂ не від’ємні.

Розв’язання:

Задача розв’язується засобами MS Excel. У комірку А2 заноситься початкове значення х ₁ (0), у комірку В2 — початкове значення х ₂ (0), у комірку С2 — формула цільової функції =2*А5+5*А6, у комірку А4 — формула першого обмеження =А5^2+А6^2.

Використовується надбудова Поиск решения, у вікно якої заносяться дані (рис. 10). Надбудова викликається з меню Сервис → Поиск решения.

Рис. 10.

Після занесення даних і натиснення кнопки Выполнить значення у комірках А2:В2 будуть змінюватись так, щоб виконувались обмеження і значення у комірці С2 набуло найбільшого значення. В результаті у комірках А2 і В2 значення аргументів, які раніше дорівнювали 0, стануть рівними 3,713902961 і 9,28476843 відповідно (не від’ємними, умова виконується). У комірці А4 (це сума х ₁²+ х ₂²) отримаємо 100 (<=100, умова виконується). Отже, обидва обмеження виконуються, а умовний максимум функції становить 53,8516481 (комірка С2).

5. Використання функцій для обчислення значень виразів у додатках MS Office.

Завдання:

Обчислити значення функцій у згідно заданого формули для вказаних значень аргументів:

Ошибка! Объект не может быть создан из кодов полей редактирования. де а = –52,5; b =8,3; х = 4, 3, –6, –2, 1, 0.

Ÿ Розв’язання у MS Excel за допомогою вбудованих функцій:

Використовується логічна функція:

ЕСЛИ (умова; дія, якщо умова виконується;дія, якщо умова не виконується)

Результати оформлюються таким чином: заносяться вхідні значення: значення констант а та b — у комірки А2 та В2, значення аргументу х — у комірки С2:С7. У комірці D2 розраховується значення функції за допомогою формули:

=ЕСЛИ(C2<0;$A$2*C2^2;ЕСЛИ(C2>0;$В$2*LN(C2);”не визначена”))

яка копіюється у комірки D3: D7.

Ÿ Розв’язання у MS Excel за допомогою функції користувача, створеної мовою VBA:

Для розв’язування задачі створюється функція користувача MyY, значення якої залежить від вхідних параметрів: констант а і b та значення аргументу х.

Функція користувача створюється на окремому листі модуля (Сервис → Макрос → Редактор Visual Basic → у вікні проектів активізувати проект поточної книги → Вставка → Модуль).

Після створення функції, вона використовуються у формулі в комірці Е2 та копіюється у комірки Е3:Е7:

Отриманий результат (діапазон Е2:Е7) такий самий, як і при використанні вбудованих функцій(діапазон D2:D7).

Ÿ Розв’язання у MS Access за допомогою вбудованих функцій:

Усі вхідні значення повинні зберігатися у таблиці з такими полями:

А – числове, В — числове, Х — числове.

Створюється запит на вибірку даних у режимі Конструктора, у який додається нове розрахункове поле У:

ц

Це поле створюється у вікні Построитель выражений (використовується вбудована функція IIf):

Треба звернути увагу, що за умови х =0 не можна у вираз записати "не визначена", тому що створений вираз міститься у числовому полі.

Результат запиту:

Очевидно, що результат збігається з отриманим у MS Excel.

6. Аналіз БД в MS Access.

Завдання:

До БД MS Access входять такі 2 таблиці:

1. Створити запит на вибірку, до якого включити поля
Номер картки, Район, Телефон, Площа (загальна /житлова /кухні), Ціна
де поле Ціна розраховується за формулою
Ціна = Загальна площа * Середня ціна за 1 м²
запит відбирає записи Дарницького району та записи Солом’янського району з вказаною кількістю кімнат.

2. Створити підсумковий запит, в якому визначити
за районами за полем Номер картки — кількість значень; за полем Середня ціна — середнє та максимальне значення.

Розв’язання:

Однією з передумов створення запиту наявність зв’язку поміж таблицями. В обох таблиць прості ключі (Табл-1 — поле Номер картки, Табл-2 — поле Район). Поле зв’язку — Район, оскільки воно містить співпадаючі дані. Тип зв’язку — один до багатьох, оскільки кожному значенню поля зв’язку Табл-2 (головної) відповідає скільки завгодно записів Табл-1 (пов’язаної). Для того, щоб в результат запиту потрапили вісі записи Табл-1, необхідно встановити зовнішнє поєднання записів таблиць (опція Объединение всех записей Табл-1 и только тех записей из Табл-2, в которых связанные поля совпадают у вікні Параметры объединения). Під час зв’язування таблиць у вікні схеми даних поле зв’язку Район тягнуть від головної Табл-2 до пов’язаної Табл-1.

Для створення запиту: кн. Запросы на панелі об’єктів головного вікна БД ® двічі клацнути на піктограмі створення нових об’єктів Создание запроса в режиме конструктора ® додати Табл-1 та Табл-2 Þ з’явиться вікно конструктора запитів, в області об’єктів відображаються списки полів таблиць та лінії зв’язку.

Для створення першого запиту необхідно заповнити бланк, як вказано на рис. 11. Умови, що накладаються на поле Район, поєднані логічним оператором ИЛИ, тому їх заносять у різні рядки (Условие отбора та или). Ці умови — текстові константи, тому пишуться в лапках (MS Access встановлює їх автоматично). Для того, щоб мати можливість вказувати кількість кімнат квартир Солом’янського району, для поля Кіл-ть кімнат в умову необхідно ввести параметр, який поєднати з умовою „ Район — Солом’янський” логічним оператором И. Для цього назва параметру пишеться в квадратних дужках в рядку Условие отбора. Формулу розрахункового поля створюють у вікні Построитель выражений (для відкриття вікна -- кн. Построить, заповнюється як вказано на рис. 12). Для перегляду результату запиту: кн. Запуск Þ з’явиться діалогове вікно введите значение параметра (рис. 13) ® ввести потрібну кількість кімнат, наприклад, 1 ® кн. ok Þ відобразиться результат запиту (рис. 14)

Рис. 11.

Рис. 12.

Рис. 13.

Рис. 14.

Для створення другого запиту до бланку додають тільки ті поля, про які йдеться (рис. 15). Для додавання рядку, в якому вказується підсумкова операція натискають кн. Групповые операции і зі списків цього рядку обирають необхідні операції.

Рис. 15.

Усі результати надані для самостійної перевірки на комп'ютері.

6. КАРТА САМОСТІЙНОЇ РОБОТИ СТУДЕНТІВ

6.1. Карта самостійної роботи студентів денної форми навчання

КАРТА САМОСТІЙНОЇ РОБОТИ СТУДЕНТА

з науки (дисципліни) інформатики

для студентів напряму підготовки Економіка підприємництва

галузі знань (спеціальності) всіх економічних спеціальностей

Денна форма навчання

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

6.2. Карта самостійної роботи студентів заочної форми навчання

КАРТА САМОСТІЙНОЇ РОБОТИ СТУДЕНТА

з науки (дисципліни) інформатики

для студентів напряму підготовки Економіка підприємництва

галузі знань (спеціальності) всіх економічних спеціальностей

Заочна форма навчання

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

6.3. Завдання для самостійного опрацювання

Завдання для самостійного опрацювання № 1.

¾ Носії інформації. Способи подання і кодування інформації у комп’ютерах.

¾ Поняття системи числення та роботи у різних системах числення.

¾ Архітектура персонального комп’ютера.

Завдання для самостійного опрацювання № 2.

¾ Компоненти глобальної комп’ютерної мережі Інтернет.

¾ Протокол TCP/IP.

¾ Веб-сервіс Інтернет. Типи пошукових систем. Організація ефективного пошуку даних за допомогою пошукових систем.

Завдання для самостійного опрацювання № 3.

¾ Створення власного Веб-сайту за індивідуальним завданням.

Завдання для самостійного опрацювання № 4.

¾ Використання функцій роботи з текстом у MS Excel.

¾ Використання функцій роботи з датою та часом у MS Excel.

Завдання для самостійного опрацювання № 5.

¾ Творче завдання з метою розвитку умінь та навичок використання логічних
функцій табличного процесору MS Excel у процесі розв’язування задач фахового спрямування.

Завдання для самостійного опрацювання № 6.

¾ Творче завдання з метою розвитку умінь та навичок роботи із БД у табличному процесорі MS Excel під час розв’язування задач фахового спрямування.

Завдання для самостійного опрацювання № 7.

¾ Творче завдання з метою розвитку умінь та навичок використання статистичних функцій табличного процесору MS Excel у процесі розв’язування задач фахового спрямування.

Завдання для самостійного опрацювання № 8.

¾ Творче завдання з метою розвитку умінь та навичок проектування БД під час розв’язування задач фахового спрямування.

Завдання для самостійного опрацювання № 9.

¾ Творче завдання з метою розвитку умінь та навичок роботи з запитами та формами у СУБД MS Access під час розв’язування задач фахового спрямування.

7. ПОРЯДОК ПОТОЧНОГО І ПІДСУМКОВОГО
ОЦІНЮВАННЯ ЗНАНЬ З ІНФОРМАТИКИ

Порядок здійснення поточного та підсумкового контролю знань студентів з інформатики та організація самостійної та індивідуально-констультативної роботи студентів та викладачів відповідає вимогам відповідних нормативно-правових та організаційно-методичних документів, що ухвалені Вченою Радою університету.

7.1 Календарний план поточного контролю знань студентів
денної форми навчання

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

7.2. Приклади типових завдань, що виносяться на іспит

1. Робота з матрицями в MS Excel. Розв’язання систем рівнянь.

Завдання:

4. Записати послідовність дій, що дозволяють отримати матриці F = С * D, К = В + С, S = A ^Т та
обчислити визначник матриці B (якщо це можливо зробити, відповідь обґрунтуйте).

5. Розв’язати систему рівнянь Вх = D матричним способом.

Розв’язання:

1. Отримати матрицю F=С*D. Операції над масивами проводяться за допомогою формул масивів. Для множення матриць в MS Excel застосовується функція МУМНОЖ. Для того, щоб матриці можна було перемножити, число стовпців першої з них повинно дорівнювати числу рядків другої. Розмірність матриці С (5´ 4), тобто 5 рядків і 4 стовпця, а вектора D (4 ´1).

Щоб отримати матрицю F, необхідно ввести формулу масиву наступним способом:

а) виділити діапазон комірок під результат множення матриць: цей діапазон комірок повинен містити, згідно правилам множення матриць, стільки рядків, скільки їх в першій матриці (в нашому прикладі 5), та стільки стовпців, скільки їх у другій (в нашому прикладі 1), отже мати розмірність 5´1, наприклад, діапазон B6:B10;

б)ввести формулу = МУМНОЖ(O1:R5;U1:U4)

в)клацнути у рядку формул;

г) одночасно натиснути CTRL+SHIFT+ENTER.

Отримати матрицю К=В+С. Для того, щоб матриці мможна було додати, матриці повинні бути однакової розмірності. Розмірність матриці B (4´4), а розмірність матриці С (5´4). Оскільки матриці мають різну розмірність, додавати їх не можна.

Отримати матрицю S = A ^Т.Під час транспонування матриці її рядки і стовпці міняються місцями. Розмірність матриці A (3´5), розмірність транспонованої матриці буде 5´3. Для транспонування матриці в MS Excel застосовується функція ТРАНСП.

Щоб отримати матрицю S, необхідно ввести формулу масиву наступним способом:

а) виділити діапазон комірок розміром 5´3 під результат транспонування матриці, наприклад, діапазон I6:K10;

б) ввести формулу = ТРАНСП(B1:F3)

в) клацнути у рядку формул;

г) одночасно натиснути CTRL+SHIFT+ENTER.

Обчислити визначник матриці B. Визначник матриці обчислюється тільки для квадратної матриці. Розмірність матриці B (4´4), тобто матриця квадратна. Для обчислення визначника матриці в MS Excel застосовується функція МОПРЕД.

а) виділити комірку під результат, наприклад, U6;

б) ввести формулу = МОПРЕД(I1:L4)

в) натиснути ENTER.

2. Розв’язок рівняння знаходиться за формулою: х = В ^–1 D. Розмірність вектора х (4´1). Щоб отримати вектор х, необхідно ввести формулу масиву наступним способом:

а) виділити діапазон комірок розміром 4´1 під результат транспонування матриці, наприклад, діапазон W1:W4;

б) ввести формулу = МУМНОЖ(I1:L4;U1:U4)

в) клацнути у рядку формул;

г) одночасно натиснути CTRL+SHIFT+ENTER.

1. Розв’язання задач економічного аналізу в MS Excel.

Завдання:

Дана БД:

1. Написати послідовність дій для розрахунку значень поля Вартість матеріалів (Вартість матеріалів = Кількість, т * Ціна за 1т, грн).

2. Записати діапазони звичайного та обчислювального критерію для пошуку записів за критеріями:

a) записи 4-го місяцю;

b) записи цегли;

c) записи 4-го місяцю, вартість матеріалу яких не перевищує 1 000 000;

d) записи цегли та каменю;

e) записи з кількістю від 2 000 до 5 000;

f) записи за 4 місяць з кількістю від 2 000 до 5 000;

g) записи за 4 та 5 місяці з кількістю не меншою 2 000;

h) записи 5 місяця, у яких вартість матеріалу була або менша за 300 000 грн, або більша за 700 000 грн;

i) записи з максимальною вартістю матеріалу;

j) записи, у яких вартість більше середньої або ціна є максимальною;

k) записи, у яких вартість більше кількості, збільшеної у 300 разів.

3. Використовуючи відповідні функції MS Excel, визначити:

a) кількість записів 4-го місяця;

b) загальну вартість цегли;

c) середню вартість цегли;

d) максимальну вартість цегли;

e) кількість записів, у яких ціна не перевищує 400 грн;

f) кількість записів, у яких вартість матеріалу більше середньої;

g) кількість записів цегли, у яких вартість матеріалу більше середньої.

Розв’язання:

4. Для розрахунку значень поля Вартість матеріалів: у комірку Е2 заноситься формула =C2*D2 і копіюється у комірки Е3:Е10 за допомогою маркера автозаповнення:

5. Діапазони звичайного та обчислювального критерію створюються під БД. Для завдань а)—h) створити можна обидва діапазони критерію, для завдань і)—j) створення та використання діапазону звичайного критерію не є коректним (оскільки в ньому не можна використовувати формули), для завдання k) взагалі не можна створити діапазон звичайного критерію:

l) діапазон звичайного критерію — А12:А13, обчислювального – Е12:Е13

m) діапазон звичайного критерію — А15:А16, обчислювального – Е15:Е16

n) діапазон звичайного критерію — А18:В19, обчислювального – Е18:Е19

o) діапазон звичайного критерію — А21:А23, обчислювального – Е21:Е22

p) діапазон звичайного критерію — А25:В26, обчислювального – Е25:Е26

q) діапазон звичайного критерію — А28:С29, обчислювального – Е28:Е29

r) діапазон звичайного критерію — А31:В33, обчислювального – Е31:Е32

s) діапазон звичайного критерію — А35:В37, обчислювального – Е35:Е36

t) діапазон обчислювального критерію — Е39:Е40

u) діапазон обчислювального критерію — Е42:Е43

v) діапазон обчислювального критерію — Е45:Е46

6. Для проведення умовних розрахунків за БД використовуються або функції СЧЕТЕСЛИ та СУММЕСЛИ, або функції баз даних. Якщо необхідно знайти кількість або суму та умова є простою, можна використати функцію СЧЕТЕСЛИ та СУММЕСЛИ відповідно, якщо проводиться інша операція або умова складна — необхідно попередньо створити діапазон критерію (звичайний або обчислювальний) та використати відповідну функцію БД.

	формула	результат
h)	=СЧЁТЕСЛИ(A2:A10;4)	=БСЧЁТ(A1:E10;;A12:A13)
i)	=СУММЕСЛИ(B2:B10;"Цегла";E2:E10)	=БДСУММ(A1:E10;E1;A15:A16)
j)	=СУММЕСЛИ(B2:B10;"Цегла";E2:E10) /СЧЁТЕСЛИ(B2:B10;"Цегла")	=ДСРЗНАЧ(A1:E10;E1;A15:A16)
k)	–	=ДМАКС(A1:E10;E1;A15:A16)
l)	=СЧЁТЕСЛИ(D2:D10;"<400")	=БСЧЁТ(A1:E10;;I1:I2)
m)	=СЧЁТЕСЛИ(E2:E10; ">"&СРЗНАЧ(E2:E10))	=БСЧЁТ(A1:E10;;I4:I5)
n)	–	=БСЧЁТ(A1:E10;;I7:I8)

1. Розв’язання задач фінансового аналізу в MS Excel.

Завдання:

У Вас є вибір: отримати від свого дядечка 1000 дол. через рік або 900 дол. сьогодні від своєї тітоньки. Річна ставка в улюбленому банку становить 12 %, відсотки нараховуються щомісяця.

d) Яка майбутня вартість подарунка від дядечка? Від тітоньки? Який подарунок Ви оберете?

e) Чи змінилася б ваша відповідь, якщо ставку зменшать до 10 %?

f) За якої процентної ставки не має різниці, який подарунок обрати?

Розв’язання:

Для вирішення цієї дилеми необхідно порівняти майбутню вартість обох подарунків. Подарунок від дядечка через рік так і буде коштувати 1000 дол., а вартість подарунка тітоньки дорівнює сумі, що буде через рік на рахунку у банку, початковий розмір якого становить 900 дол. Тому майбутня вартість подарунка тітоньки розраховується за формулою, у якій використовується функція БС. Ця функція має синтаксис:

БС (ставка; кількість періодів;розмір періодичних виплат; початкове значення)

За умовою задачі період нарахування відсотків — місяць, тому кількість періодів дорівнює 1*12, а ставка за період — 12 %/12. Розмір періодичних виплат дорівнює 0, оскільки виплати не здійснюються. Початковий внесок на рахунок у банку становить –900 (враховується напрям руху грошей). Отже, майбутня вартість подарунка тітоньки розраховується за формулою:

=БС(12%/12;1*12;0;-900)

Це становить 1 014,14 дол., тому треба обрати подарунок тітоньки. Якщо ставку зменшили до 10 %, подарунок тітоньки буде коштувати лише 994,24 дол. (=БС(10%/12;1*12;0;–900)), а тому в цій ситуації краще обрати подарунок дядечка. Ставка, за якої обидва подарунки є рівноцінними, — це ставка, за якої майбутня вартість 900 дол. становить 1 000 дол., і її значення обчислюється за формулою:

=СТАВКА(1*12;0;–900;1000)*12

Отримаємо, що, за ставки 10,6 % не має різниці який подарунок обрати.

1. Розв’язання задач статистичного аналізу в MS Excel.

Завдання:

На основі статистичних даних спрогнозувати курс гривні щодо долара США:

3. в зазначені місяці, вважаючи що має місце лінійна парна регресія;

4. за вказаних значень курсів євро та російського рубля, вважаючи що має місце лінійна множинна регресія.

Розв’язання:

1. Для прогнозування курсу USD в зазначені місяці можна використати один із п’яти способів (шостий спосіб — використання автозаповнення — не можна застосувати, оскільки місяці непослідовні):

1) аналітичне прогнозування курсу у перший місяць з використанням функції ТЕНДЕНЦИЯ з подальшим копіюванням формули для інших місяців (рис. 1);

2) аналітичне прогнозування курсу одночасно для всіх місяців з використанням функції ТЕНДЕНЦИЯ та формули масиву (рис. 2);

3) аналітичне прогнозування курсу у перший місяць з використанням функції ПРЕДСКАЗ з подальшим копіюванням формули для інших місяців (рис. 3);

4) аналітичне прогнозування курсу одночасно для всіх місяців з використанням функції ПРЕДСКАЗ та формули масиву (рис. 4);

5) графічне прогнозування за допомогою лінії тренду (рис. 5).

Отримані результати тотожні (рис. 6).

Рис. 1. Рис. 2

Рис. 3. Рис. 4.

Рис. 5. Рис. 6.

2. Для прогнозування курсу USD за вказаних значень курсів іноземних валют використовувати функцію ПРЕДСКАЗ не можна, оскільки вона використовується тільки для парної регресії, тому використовують функцію ТЕНДЕНЦИЯ та один з перших двох вказаних способів (Рис. 7, 8). Отримані результати тотожні (Рис. 9).

Рис. 7. Рис. 8.

Рис. 9.

1. Розв'язання задач оптимізації.

Завдання 2:

Менеджер відділу логістики торговельної фірми складає план перевезення холодильників із двох складських приміщень фірми до трьох її магазинів. Вартість перевезення одного холодильника з урахуванням відстані перевезення та інших обставин наведено в таблиці (10):

Рис. 10.

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

Розв’язання:

	Етап розв’язання	Розв’язання
	Визначення збалансованості задачі	У комірку E5 занести формулу =СУММ(E2:E3) для обчислення обсягів запасів холодильників на двох складах. Дістаємо результат — 100 холодильників. У комірку F4 занести формулу =СУММ(B4:D4) для обчислення обсягів замовлення холодильників трьома магазинами. Дістаємо результат — 145 холодильників. Отже, попит на холодильники перевищує пропонування (кількість холодильників на двох складах)
	Формування таблиці розв’язку задачі	У діапазоні B8:D9 буде визначено кількість перевезених холодильників з кожного складу до відповідного магазину. Так, у комірці В8буде обчислена кількість холодильників, які доцільно доставляти зі Складу 1 до Магазину 1. Значення діапазону B8:D9 мають бути цілими та невід’ємними числами.
	Обчислення кількості товару, що буде вивезений зі Складу 1 та Складу 2	У комірку E8 занести формулу =СУММ(B8:D8). У комірку E9 занести формулу =СУММ(B9:D9). Оскільки комірки діапазону B8:D9 на данний момент порожні, то фор­мули будуть повертати нуль
	Обчислення кількості товару, що буде привезений до Магазину 1, Магазину 2 та Магазину 3	У комірку В10 занести формулу =СУММ(B8:В9) У комірку С10 занести формулу =СУММ(С8:С9) У комірку D10 занести формулу =СУММ(D8:D9) Оскільки комірки діапазону B8:D9 на даний момент порожні, то формули будуть повертати нуль
	Обчислення суми транспортних витрат на перевезення холодильників	У комірку Е10занести формулу =СУММПРОИЗВ(B2:D3;B8:D9) Оскільки комірки діапазону B8:D9 на даний момент порожні, то формула буде повертати нуль. Отже, після ввімкнення на робочому аркуші режиму відображення формул одержимо:
	Використання надбудови Поиск решения	Вкл. Данные ® гр. Анализ ® кн. Поиск решения. У вікні Поиск решения зазначаємо необхідні параметри: ü цільова комірка — $E$10 ü тип оптимізації — мінімізація ü комірки, що змінюються, — $B$8:$D$9 ü обмеження: $B$8:$D$9—цілі числа; $B$8:$D$9 ≥ 0; $B$10:$D$10 £ $B$4:$D$4; $Е$2:$Е$3 = $Е$8:$Е$9 Натискаємо кн. Выполнить.
Результат

Завдання 2:

Дана БД:

1. Заповніть вікно надбудови Поиск решения для розв’язання задачі оптимізації для заданої БД:

знайти такі значення поля Кількість, т за яких загальна вартість щебеню дорівнює 5 000 000, за умови, що значення поля
Кількість, т є додатними і не перевищують 7 000.

Розв’язання:

1. Для розрахунку значень поля Вартість матеріалів: у комірку Е2 заноситься формула =C2*D2 і копіюється у комірки Е3:Е21 за допомогою маркера автозаповнення;

2. Розрахувати в комірці Е22 цільову функцію: =СУММЕСЛИ(B2:B21;"Щебінь";E2:E21);

3. Активізувати комірку з формулою цільової функціїї (комірку Е22) та завантажити надбудову Поиск решения з меню Сервис → Поиск решения;

4. Заповнити вікно надбудови Поиск решения

Рис. 10.

Після занесення даних і натиснення кнопки Выполнить значення у комірках C2:С21 будуть змінюватись так, щоб виконувались обмеження ($C$2:$C$21<=7000 та $C$2:$C$21>=0) і значення у комірці E22 набуло значення 5 000 000.

1. Використання функцій для обчислення значень виразів у додатках MS Office.

Завдання 1:

Обчислити значення функцій у згідно заданого формули для вказаних значень аргументів:

,

Ÿ Розв’язання у MS Excel за допомогою вбудованих функцій:

Використовується логічна функція:

ЕСЛИ (умова; дія, якщо умова виконується;дія, якщо умова не виконується)

Область допустимих значень функції y задається такою системою:

Тому значення функції можна буде обчислити, тільки якщо одночасно виконуються такі умови: x + 7 > 0 та b + 81 ≠ 0.

Результати оформлюються таким чином: заносяться вхідні значення: значення константи а — у комірку А2, значення змінних х та b — у комірки В2:B4 та C2:C4 відповідно. У комірці D2 розраховується значення функції за допомогою формули:

=ЕСЛИ(И(B2+7>0;C2+81<>0);$A$2^2/(B2+7)^(1/2)-ABS($A$2*C2+B2^3)+
COS(($A$2*B2*C2)^5)/(C2+81);"функція не визначена")

яка копіюється у комірки D3: D4.

Завдання 2:

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

, де а = –52,5; b =8,3; х = 4, 3, –6, –2, 1, 0.

Ÿ Розв’язання у MS Excel за допомогою вбудованих функцій:

Використовується логічна функція: