Шпаргалки! - Інформатика та інформаційні мережі

46. Створення зведених таблиць.

Зведеною таблицею називають таблицю, яка створюється на основі даних зі списку з великою кількістю полів і використовується для зручного перегляду потрібних записів та швидкого виконання запитів, у тому числі і запитів із проміжними підсумками.
Зведену таблицю можна створити, використовуючи не тільки списки даних у середовищі Microsoft Excel, а й зовнішні джерела інформації (наприклад, записи із баз даних, створених у середовищі Microsoft Access).
Зведені таблиці використовуються для групування значень таблиці і виведення підсумкових значень по групах. Для створення необхідно: 1.встановити курсор в будь-яку комірку таблиці і активізувати Данные \ Свободная таблица. 1 вікно: визначають який діапазон значень активізує перемикач. “В списке или БД Microsoft excel”. Кнопка Далее. 2 вікно: вводять або перевіряють діапазон комірок для створення зведеної таблиці. Далее. 3 вікно: визначають стовпці, які буде мати зведена таблиця. Для цього перетягують із переліку кнопок в потрібну частину зведеної таблиці. В частину « Данные» перетягують назву стовпця, який розраховується. В полі « Операция» вміщено перелік всіх функцій , де вибираємо потрібну. Далее. Наступне вікно : активізуємо перемикач Новый лист або Существующий лист . при виборі Существующий лист визначають адресу комірки, з якої починається зведена таблиця.

47. Робота з функціями фінансового аналізу НОРМА, КПЕР, ППЛАТ (ОСНПЛАТ та ПЛПРОЦ), ПЗ, БЗ, НПЗ.

За допомогою фінансових функцій здійснюються такі типові фінансові розрахунки, як обчислення суми платежу по позиці, обсяг періодичних платежів по вкладенню або позиці, вартість вкладення або позики по завершенні всіх вкладених платежів.
Найчастіше аргументами фінансових функцій є такі величини:
• майбутня вартість — вартість вкладення або позики по завершенні усіх платежів;
• кількість виплат — загальна кількість платежів або періодів виплат;
• виплата — обсяг періодичних платежів по вкладенню або позиці;
• поточна вартість — початкова вартість вкладення або позики. Наприклад, початкова вартість позики дорівнює сумі позики;
• ставка — відсоткова ставка або знижка по вкладенню або позиці;
• тип — режим, що показує, як здійснюються виплати (наприкінці періоду чи на його початку).
- Функція НОРМА
Для розрахунку відсоткової ставки застосовується функція =НОРМА(к-сть платежів;виплата;поточна вартість;майбутня вартість;тип)
- Функція КПЕР
Для розрахунку строку платежів використовується
функція =КПЕР(ставка; виплата; поточне значення; майбутнє значення; тип)
- Функція БЗ
Вираховує майбутнє значення вкладу на основі постійної відсоткової ставки і має такий вигляд:
=БЗ (ставка; к-сть виплат; виплата; поточна вартість; тип)
- Функція ПЗ
Для розрахунку поточної вартості єдиної суми вкладення (позики) і фіксованих періодичних платежів застосовується функція
=ПЗ(ставка; к-сть платежів; виплата; майбутня вартість; тип)
Функція ПЗ є оберненою щодо функції БЗ.
- Функція НПЗ
Застосовується при оцінці ефективності інвестицій і дозволяє визначити нижню межу прибутковості і використати її як критерій при оцінці найефективнішого проекту.
Функція =НПЗ(ставка; сумаї; сума 2; ...; сума N)
сума 1,2,3... N — значення виплат та надходжень
ПЛТ(ставка ;кпер;пс;бс;тип)
Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
ОСПЛТ(ставка ;период;кпер;пс;бс;тип)
Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.
ПРОЦПЛАТ(ставка;период;кпер;пс)
Вычисляет проценты, выплачиваемые за определенный инвестиционный период

48. Використання функцій СРЗНАЧ, СЧЕТЕСЛИ, НАИМЕНЬШИЙ, МАКС, СЧЕТ табличного процесору MS EXCEL для проведення статистичних розрахунків. Навести приклади.

=СРЗНАЧ(параметр) - Середнє значення чисел

=СЧЕТЕСЛИ(діапазон для перевірки умови; «умова») - Кількість разів виконання умови
У процесі роботи цієї функції проглядається діапазон для
перевірки умови і підраховується кількість виконань умови. Наприклад,
• визначити, скільки разів робив замовлення клієнт Іванов: Діапазон для перевірки — В12:В24, умова — «Іванов». Функція має такий вигляд: =СЧЕТЕСЛИ(В12:В24;«Іванов»)
• визначити, скільки разів робились замовлення до 1998 року: Діапазон для перевірки — С12:С24; умова — «<01.01.98». Функція має такий вигляд:
=СЧЕТЕСЛИ(С 12:С24;«<01.01.98»)

=НАИМЕНЬШИЙ(параметр;N) - N-e мінімальне з чисел

=МАКС(параметр) - Максимальне з чисел

=СЧЕТ(параметр 1 ;параметр2;... ;параметр30) - Кількість чисел серед параметрів

49. Використання функцій СУММЕСЛИ, НАИБОЛЬШИЙ, МИН табличного процесору MS EXCEL для проведення статистичних розрахунків. Навести приклади.
Для підрахунку суми числових значень у певному діапазоні використовується функція СУММ (діапазон комірок), проте вона підраховує суму із усіх значень, розташованих у вказаному діапазоні. Коли ж потрібно підрахувати суму числових значень, які відповідають певній умові у таблиці, використовується функція СУММЕСЛИ, яка має такий вигляд: =СУММЕСЛИ(діапазон для перевірки умови; «умова»; діапазон для підрахунку суми).- Сума чисел за умовою
Наприклад, за даними попередньої таблиці треба підрахувати суму податку, що сплатив клієнт Іванов.
Діапазоном для перевірки умови є стовпчик з прізвищами клієнтів — В12:В24, серед яких зустрічається (або ні) потрібне прізвище.
Умовою для пошуку є прізвище «Іванов», а діапазоном для підрахунку суми значень є стовпчик з нарахованими податками —112:124. Тоді функція набуває такого вигляду: =СУММЕСЛИ(В12:В24;«Іванов»;І12:І24)

=НАИБОЛЬШИЙ(параметр;N) - N-e максимальне з чисел

=МИН(параметр) - Мінімальне з чисел

50. Використання функцій ПРЕДСКАЗ, ТЕНДЕНЦИЯ, РОСТ табличного процесору MS EXCEL для аналізу складної сукупності даних та прогнозування. Навести приклади.

Для проведення прогнозування за допомогою статистичних \ функцій Microsoft Excel застосовують регресивний аналіз. Це вид І статистичного аналізу, що дозволяє оцінити міру залежності між І змінними, пропонуючи механізм обчислення передбачуваного значення змінної з декількох уже обчислених значень. Іншими словами, на основі статистичної вибірки відомих значень функції F(x) та аргументів х, можна спрогнозувати поведінку функції шляхом підстановки нових значень аргументів.

• 3 використанням одиничної лінійної регресії — =ПРЕДСКАЗ(новийХ;відоміУ;відоміХ) — обчислює (прогнозує) одне нове значення функції на основі нового заданого X і статистичної виборки відомих значень Х та Y.
=ТЕНДЕНЦИЯ(відоміУ;відоміХ;новіХ) — на відміну від функції ПРЕДСКАЗ прогнозує не одне, а декілька нових значень функції на основі декількох нових заданих Х'\ статистичної виборки.
• 3 використанням одиничної нелінійної регресії — =РОСТ(відоміУ;відоміХ;новіХ) — так само, як і ТЕНДЕНЦИЯ,
прогнозує декілька нових значень функції на основі декількох нових заданих X і статистичної виборки.
Приклад.
Задана статистична вибірка курсу американського долара та німецької марки за 8 місяців. Спрогнозувати зміну курсу німецької марки, якщо курс американського долара на наступні 4 місяці прогнозується згідно з таблицею:

Статистична вибірка характеризується функцією DM = F($US), де відомими значеннями х є значення курса американського долару (комірки В15:В22), відомими значеннями У є значення курсу німецької марки (комірки С15:С22).
За допомогою функції ПРЕДСКАЗ можна спрогнозувати значення курсу німецької марки тільки для одного місяця (вересня) на основі одного нового значення курсу американського долара (комірка В24 =5,42).
Функція ПРЕДСКАЗ заноситься у відповідну комірку С24:
=ПРЕДСКАЗ(В23;В15:В22;С15:С22 Enter і отримується результат.
Для прогнозування курсу німецької марки можна використати функції
=ТЕНДЕНЦИЯ(С15:С22;В15:В22;В24:В27),або
=РОСТ(С15:С22;В15:В22;В24:В27) де В24:В27 — це значення нових х — передбачуваних значень курсу американського долара протягом наступних чотирьох місяців.
Функція ТЕНДЕНЦИЯ має бути занесена у комірки D24:D27, а функція РОСТ — у комірки Е24:Е27 як формула масиву. Для уведення функції як формули масиву треба виділити діапазон клітин, де має міститися така функція, занести її і натиснути спочатку на клавішу F2, а потім клавіші CTRL+SHIFT+Enter.

51. Використання функцій ЛИНЕЙН, ЛГРФПРИБЛ табличного процесору MS EXCEL для аналізу складної сукупності даних та прогнозування. Навести приклади.

Для проведення прогнозування за допомогою статистичних \ функцій Microsoft Excel застосовують регресивний аналіз. Це вид І статистичного аналізу, що дозволяє оцінити міру залежності між І змінними, пропонуючи механізм обчислення передбачуваного значення змінної з декількох уже обчислених значень. Іншими словами, на основі статистичної вибірки відомих значень функції F(x) та аргументів х, можна спрогнозувати поведінку функції шляхом підстановки нових значень аргументів.

• 3 використанням множинної лінійної регресії — =ЛИНЕИН(відоміY;відомiX) — прогнозує значення коефіцієнтів а, (і = п, п-\, ..., 2,1) та Ь. Отримані при прогнозуванні значення коефіцієнтів підставляються у рівняння множинної лінійної регресії і отримується значення Y.
• 3 використанням множинної нелінійної регресії — =ЛГФПРИБЛ(відоміY; відоміX) — прогнозує значення коефіцієнтів а, (і = п, п- 1, ..., 2,1) та b. Отримані при прогнозуванні значення коефіцієнтів підставляються у рівняння множинної нелінійної регресії і отримується значення Y.

Приклад:
Фірма бажає придбати будівлю під офіс і має оцінити вартість нерухомості, запропонованої на ринку.
Розв 'язання:
Можна використати множинний регресійний аналіз для прогнозування вартості будівлі. Передбачається, що використовується функція У = F(xi), де Y— вартість будівлі, хі — площа, х2 — кількість поверхів, хЗ — кількість входів, х4 — строк експлуатації будівлі.
Для прогнозування вартості триповерхової будівлі, що експлуатується ЗО років, з площею у 2000 м" та 2 входами, використовують Прайс-лист для нерухомості подібного типу у вигляді таблиці:

Для оцінки вартості використовується функція =ЛИНЕЙН (ві-доміУ;відомь¥), або функція
=ЛГФПРИБЛ(в ідом і Y; відомії).
Кожна з функцій не визначає Y у відповідному рівнянні множинної регресії, а визначає значення коефіцієнтів а, та Ь, починаючи з старшого коефіцієнта ап. Обчислені коефіцієнти використовуються у формулі, яка реалізує рівняння множинної регресії (лінійної, якщо використовувалась функція ЛИНЕЙ, і нелінійної, якщо використовувалась функція ЛГФПРИБЛ).
Відомими Ye значення, розташовані у комірках ЕЗ:Е13, відомими Хе значення, розташовані у комірках A2:D13.
Для визначення коефіцієнтів треба створити заголовки у комірках А15:Е15 і занести вибрану статистичну функцію у комірки А16:Е16 як формулу масиву:

у комірку Е20 заноситься формула яка реалізує рівняння множинної лінійної регресії =D16*A20+C16*B20+B16*C20+A16*D20+E16, і отримується результат.

52. Поняття макросів. Різні способи створення макросів у табличному процесорі MS EXCEL. Різні способи завантаження макросів.

Макрос — це серія команд і функцій, що зоерігаються в мо-дулі Visual Basic for Applications (VBA) — мови програмування Четвертого покоління. Один раз створивши макрос, його можна виконувати стільки разів, скільки необхідно виконати дану задачу.
Перед тим як записати або написати макрос, необхідно спланувати кроки і команди, які він буде виконувати. Якщо під час запису макроса була допущена помилка, дія, що виправляє її, буде також записана. Щоразу при запису макроса всі дії, виконані Користувачем, інтерпретуються у вигляді інструкцій мови VBA, які зберігаються в спеціальному модулі, приєднаному до робочої книги. Кожному макросу надається ім'я, за яким його можна викликати для виконання, відредагувати та знищити, якщо макрос містить помилки або якщо він уже непотрібний.
Макрос може бути виконаний за доп. Ком. Меню, за доп. Комбінації клавіш, і Кнопки. Для створення – Сервіс → Макрос → Начать запись; у вікні вказуємо ім’я макроса, під яким він буде зберігатися (перший символ – літера, без пробелів); з’являється панель, що називається “Остановка макроса” (містить дві кнопки: Остановить запись, Относительная ссылка, яка за умовчуванням активна); ком., які хочемо записати у макрос → Сервіс → Запись → Остановить запись. Макрос створено, для активізації потрібно: комб. Клавіш; Сервис → Макрос → Макросы, де у переліку вибираємо потрібний макрос → Выполнить; створюється кн. Панель інструментів → Формы → кн. ■ → змінюємо вказівник мишки і на роб. Полі створюємо кнопку. Цій кнопці можна надати ім’я. Для вик-я макросу – клацнути на цій кнопці. Знач-я стовпця повинно бути виділеним → вик-я макросу. Для перевірки дії макросу або повернутися, або виконати з іншим стовпцем.

53. Створення та використання функцій користувача у табличному процесорі MS EXCEL. Навести приклад.

Поряд із стандартними функціями, які виконують дії з даними, користувач може створити свою власну функцію, яка буде виконувати дії над даними, які потрібні користувачеві.
За виглядом функція користувача не відрізняється від стандартних:
=ім' яфункції_користувача(аргументи) і вводиться у комірку, де треба виконати потрібну процедуру над даними — аргументами.
Але для того, щоб подані аргументи оброблялись належним чином, мовою VBA створюється процедура — функція.
Процедура Function є послідовністю інструкцій мови Visual Basic, обмежених інструкціями Function і End Function. Процедура Function схожа на процедуру Sub, однак на відміну від останньої вона повертає обчислене значення у робочій аркуш, звідки вона була викликана у супроводі формальних аргументів. Отримані аргументи (константи, змінні, або вирази) процедура Function підставляє в тому ж порядку замість власних умовних аргументів, які містяться у дужках після імені функції.
У поданій нижче таблиці клієнтів є формули, за якими визначаються вихідні результати — Податок і Сума замовлення.

Сума замовлення = Податок + Загальна вартість
- Викликається редактор VBA, активізується модуль з уже створеними макросами або створюється новий модуль, де записуються інструкції для виконання функції визначення податку:
Function Tax(Cost)
If Cost > 50000 Then
Tax = 0.1 *Cost
Elself Cost > 25000 Then
Tax = 0.12* Cost
Elself Cost > 10000 Then
Tax = 0.15* Cost
Else
Tax = 0.18* Cost
End If
End Function
Назва функції користувача Tax, Cost — умовний аргумент, від якого залежить значення функції Tax.
Для обчислення податку у робочому аркуші в комірці обчислення податку 12 вводиться створена функція = Тах(Н2) .

54.1. Використання функцій пошуку рішень

Цей засіб застосовується в випадках, коли необхідно знайти значення, яке залежить від цілого ряду значень. При цьому на змінення деяких значень можна накласти певні обмеження. Для реалізації треба ств. Цільову комірку, визначити діапазон комірок, які змінюються і діапазон обмежень. Цільова комірка – така, в якій містяться формули з посиланням на всі комірки діапазону, які включені до розрахунку. В цільову комірку встановлюємо курсор, ком. Сервіс – пошук рішення. У полі изменение ячейки вводимо діапазон комірок, що змінюються. Створюємо обмеження в групі ограничение, акт. Кн. Добавить. В вікні, що відкрилось, у полі Установление цели вводимо адресу комірки, на яку ми накладаємо обмеження. В інших полях встан. Умову. При ОК активізується попереднє вікно, при натисканні Добавить ств-ся інші обмеження: ком. Виполнить. з’являється вікно з повідомленням, що рішення знайдено або може бути. У переліку Тип отчета можна ств. Звіт за результатами: перемикач Результат, в якому виведені попередні і нові значення комірок, які змінююься.

Комірка з кінцевим значенням називається цільовою коміркою, а комірки з вхідними даними, що підлягають зміні, називаються змінними комірками. Цільова комірка обов'язково повинна містити формулу або функцію, параметрами якої є значення змінних комірок.