Інформатика. Її види та властивості (стр. 3 )

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

Формула — це запис дій, які треба провести з вхідними дани­ми для отримання потрібного результату. Для внесення формули треба активізувати комірку, де має розміщуватися формула, на­тиснути = (Дорівнює) або на клавіатурі, або мишкою ліворуч у рядку формул і заносити текст. Текст формули складається з ад­рес комірок із вхідними значеннями, поєднаних математичними, логічними або пунктуаційними знаками.

Якщо, наприклад необхідно скласти значення клітин А1 та В1 і вивести їх суму у клітині С1 встановіть рамку виділлення на клітину С1 і введіть формулу =В1+В2.

Зведення у ступінь ^.

Арифметичні формули можуть також містити оператори порівняння: дорівнює (=), не рівно (< >), більше (>), менше (<), не більше (<=), не менше (>=). Результатом обчислення арифметичної формули є число.

Логічні формули можуть містити вказані оператори порівняння, а також спеціальні логічні оператори:

#NOT# - логічне заперечування “НІ”

#AND# - логічне “І”

#OR# - “АБО”

Логічні формули визначають, вираз “істина” чи “помилка”. Виразу “істина” привласнюється числова величина 1, виразу “помилка” - 0.

Форматування даних – вибір форми представлення числових або символьних даних в комірці.

Змінювати формат можна у одній комірці або у цілому діапазоні. Виділяється комірка або діапазон комірок,

• вибирається пункт меню Формат/Ячейки або

• права кнопка на виділеній комірці або діапазоні і пункт з динамічного меню Формат ячеек...

Відкриється вікно форматів з декількома вкладниками: Вкладника Число використовується для надання даним фор­матів, що перелічені у списку Числовые форматы. Тут можна вибрати основний формат і його додаткові параметри.

Вкладника Выравнивание використовується для вирівнювання даних по горизонталі та вертикалі (зона ліворуч угорі), для вибо­ру кута розташування даних у комірці (зона праворуч), а покаж­чики, розташовані ліворуч внизу, дозволяють поєднувати комір­ки у суміжному діапазоні, розташовувати текст у комірках в де­кілька рядків та автоматично змінювати ширину стовпчиків, як­що дані не зміщуються у ньому.

Вкладника Шрифт дозволяє вибирати параметри шрифта для виділеного діапазону.

Вкладинка Граница пропонує оформлення виділеного діапа­зону рамками різного типу.

Вкладинка Вид опрацьовує оформлення тла діапазону різним

кольором.

Вкладинка Защита використовується для захисту змісту ко­мірок у діапазоні (спрацьовує тільки після захисту аркуша).

34. Поняття функції у табличному процесорі MS EXCEL. Особливості використання Майстра функцій. Навести приклад математичних функцій.

Під функцією розуміють залежність однієї змінної (у) від іншої (х) або кілька змінних (х1, х2, ..., хn). Причому кожному набору значень змінних х1, х2, ..., хn буде відповідати єдине значення певного типу залежностей змінної у. Функції вводять у таблицю у склад формул або окремо.

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

тощо.

Загальний вигляд функції:

=ім'я функції(параметр/и)

У електронних таблицях можуть бути представлені такі види функцій:

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

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

- текстові функції виконують операції над текстовими рядками або послідовністю символів, обчислюючи довжину рядка, перетворюючи прописні літери у рядкові і т. п.

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

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

- функції дати і часу та ін.

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

Будь-яка функція виконується або внесенням її тексту з клаві­атури у активну комірку, або її викликом з активної комірки за допомогою Майстра функцій.

Майстер функцій — це спеціальна програма, за допомогою якої можна вибрати потрібну функцію і виконати її, вказавши всі потрібні параметри.

Майстер функцій можна завантажити через пункт меню

Вставка/Функція..., або за допомогою fx кнопки на панелі ін­струментів.

Приклади функцій:

SUM (список) – статистична функція визначення суми всіх числових значень у списку, який може складатися з адрес комірок і блоків, а також числових значень.

AVERAGE (список) – статистична функція визначення середнього арифметичного значення всіх перерахованих у списку величин.

МАХ (список) – статистична функція, результатом якої є максимальне значення у вказаному списку.

IF (умова, істина, помилка) – логічна функція, що перевіряє на істинність задану логічну умову.

35. Особливості роботи з логічними функціями у табличному процесорі MS EXCEL. Навести приклади.

Під функцією розуміють залежність однієї змінної (у) від іншої (х) або кілька змінних (х1, х2, ..., хn). Причому кожному набору значень змінних х1, х2, ..., хn буде відповідати єдине значення певного типу залежностей змінної у. Функції вводять у таблицю у склад формул або окремо.

Загальний вигляд функції:

=ім'я функції(параметр/и)

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

=ЕСЛИ(умова;дія 1 ;дія2)

Виконання діїї, коли умова істинна, або ді'й, коли умова хибна

=ИЛИ(умова1 ;умова2;... ;умова30)

ИСТИНА, якщо хоча б одна з умов виконується, або ЛОЖЬ, якщо жодна з умов не виконується

=И(умова1 ;умова2;... ;умова30)

ИСТИНА, якщо усі умови ви­конуються, або

ЛОЖЬ, якщо хоча б одна з умов не виконується

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

Списком називають набір рядків таблиці, що містить пов'я­зані між собою дані. Розрізняють списки користувача і спис­ки — бази даних.

Списки користувача створюються користувачем для подаль­шого автозаповнення таблиці даними. Елементами списку ко­ристувача можуть бути або дані, що вже набрані у таблиці, або дані, які вводяться користувачем безпосередньо під час ство­рення списку.

Створення списку користувача на основі існуючих даних

• Виділяється діапазон комірок, який містить елементи майбутнього списку користувача;

• вибирається пункт меню Сервис/Параметры...,

• на вкладниці Списки натискається кнопка Импорт і Ok.

Створення нового списку користувача

• Вибирається пункт меню Сервис/Параметры...;

• на вкладинці Списки вибирається Новый список;

• в зоні Элементы списка вводяться елементи списку, по­чинаючи з першого. Після введення чергового елемента натис­кається клавіша Enter;

• по закінченню введення всіх елементів списку натискає­ться кнопка Добавить і Ok.

Списком (базою даних) називають набір даних, що містить інформацію про певні об'єкти. У Microsoft Excel такою базою даних є таблиця, рядки в якій, починаючи з другого, називають записами, стовпчики — полями. Перший рядок списку містить назви полів (стовпчиків).

Під час створення списку — бази даних у середовищі Microsoft Excel слід дотримуватися певних правил:

• на одному робочому аркуші не можна розміщувати біль­ше одного списку;

• список має бути відокремлений від інших даних робочого аркуша щонайменше одним порожнім стовпчиком і одним по­рожнім рядком;

• список не може містити порожніх рядків (стовпчиків);

• заголовки полів мають бути унікальними, тобто не може бути повторів назв заголовків полів;

• бажано, щоб формати заголовків полів відрізнялися від форматів записів;

• не може бути порожнього рядка або навіть порожньої ко­мірки між заголовками полів і записами;

• в усіх рядках списку в однакових стовпчиках мають роз­ташовуватися однотипні дані.

Список (база даних) Microsoft Excel подано такими параме­трами:

• діапазоном бази даних або іменем діапазону та

• адресами назв полів або їх порядковими номерами у базі даних.

Наприклад, діапазоном бази даних таблиці замовлень є ко­мірки A11:L24 (або ім'я діапазону — Базаі, яке було надане вказаному діапазону), а адресами назв полів є All — поле № п/п (порядковий номер 1), ВИ — поле Прізвище клієнта (порядковий номер 2) і так далі до адреси L11 — поле Спла­чено (порядковий номер 12).

37. Аналіз даних за допомогою проміжних підсумків (Итоги) та зведених таблиць у табличному процесорі MS EXCEL.

Проміжні підсумки використовуються для розрах. Значень, які повторюються. Такими підсумками можуть бути ф-ції: Сума, К-сть зн-нь, Мін, Макс. Перед викон. Такої ф-ції БД має бути відсортована по значенням поля, які повторюються. Знайти б-я ф-цію для певного поля. Курсор у БД; Ком. Данные → Сортировка. Ком. Данные → Итоги; В полі “При каждом изменении в” вказують те поле, в якому відбувається сортування, яке має повторююче зн-ня. У полі “Операція” актив. відповідну ф-цію. У полі “Добавить итоги по” перемикачі (зліва) тих полів, зн-ня яких потрібно розрахувати. Перемикач “Заменить текущие итоги” активний, коли треба розрахувати лише 1 ф-цію. Коли декілька підсумків - він неактивний. Після ОК з’являється БД, в якій після кожної групи однакових значень виводиться підсумок. У лівій частині екрану з’являється структура вкладеності рівнів зн-нь. Кн. 1, 2, 3 … дозволяють згортати (розгортати) перегляд зн-нь. Для вилучення Ф-ції (не значень) – курсор у БД → Данные → Итоги → кн. Убрать все.

38. Використання автофільтру для аналізу списку в табличному процесорі MS EXCEL.

Для допомогою фільтрів можна вибрати записи, що містять потрібні для пошуку дані. Фільтри бувають двох типів: Авто-фільтр і Розширений фільтр.

Автофільтр може працювати з простими критеріями (одна умова), складними критеріями (максимум дві умови за одним полем) і складеними критеріями, які можуть містити стільки умов, скільки полів у списку, але при цьому використовують максимум дві умови за одним полем. Критерії заносяться під час роботи Автофільтра, а результати запиту для подальшого збереження користувач може самостійно скопіювати в інше місце поточного робочого аркуша або на новий аркуш.

Данные → Фильтр → Автофильтр.

39. Використання розширеного фільтру для аналізу списку в табличному процесорі MS EXCEL. Навести приклади з використанням простого та складеного критеріїв.

На відміну від Автофільтра, де критерії заносяться під час роботи фільтра, Розширений фільтр може працювати тільки тоді, коли критерії для пошуку даних попередньо створені користувачем і занесені у визначений діапазон комірок таб­лиці. Цей діапазон бажано має міститися над списком і має бути відокремленим від списку щонайменше одним порожнім рядком.

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

1. Виорати зі списку всі записи про клієнта Васильєва.

За правилами створення критеріїв діапазон для них оуде розміщуватися у робочому аркуші по рядках з першого по дев'ятий.

Даний запит включає одну умову (прізвище клієнта — Ва­сильєв), тому створюється простий критерій для пошуку в полі Прізвище клієнта прізвища Васильєв.

У комірку В1 копіюється заголовок поля Прізвище клієн­та, а в комірку В2 — умова для пошуку — Васильєв:

Таким чином, діапазон створеного простого критерію роз­міщується у комірках В1 :В2.

Складений критерій — це сукупність декількох складних критеріїв — містить по декілька умов на декілька полів.

4. Вибрати зі списку всі записи про клієнта Васильєва, що замовляв телефони, клієнта Петрова, що замовляв принтери, та всі замовлення клієнта Іванова.

У даному випадку створюється складений критерій, що міс­тить по три умови на два поля. Під час виконання запиту в полі Прізвище клієнта має міститися або прізвище Васильєв і одно­часно в полі Назва виробу — Телефон, або прізвище Петров і одночасно Принтер, або прізвище Іванов і будь-яка назва виробу.

У комірку СЗ копіюється назва виробу Принтер, а в комірку С4 заноситься знак * (який означає будь-яке текстове значення):

Діапазон створеного критерію — комірки В1 :С4.

40. Використання розширеного фільтру для аналізу списку в табличному процесорі MS EXCEL. Навести приклади з використанням складного та обчислювального критеріїв.

На відміну від Автофільтра, де критерії заносяться під час роботи фільтра, Розширений фільтр може працювати тільки тоді, коли критерії для пошуку даних попередньо створені користувачем і занесені у визначений діапазон комірок таб­лиці. Цей діапазон бажано має міститися над списком і має бути відокремленим від списку щонайменше одним порожнім рядком.

Складний критерій містить у собі декілька простих крите­ріїв, сполучених або по вертикалі — декілька умов на одне по­ле, або по горизонталі — по одній умові на декілька полів. ты в диапазон” зазначають адресу вільної комірки. ОК. З’являється нова БД.

2. Вибрати зі списку всі записи, що стосуються або клієнтів Васильєва, або Петрова, або Іванова.

Даний запит включає три умови, що накладаються на поле Прізвище клієнта (прізвище — або Васильєв, або Петров, або Іванов), тому створюється складний критерій із трьох простих, які поєднані зв'язкою ИЛИ.

У комірку ВЗ копіюється умова для пошуку — Петров, а в комірку В4 — Іванов:

Діапазоном створеного складного критерію будуть комірки В1:В4.

Обчислювальний критерій може бути різновидом просто­го або складного критерію, де як умова виступає функція або формула. Записи за таким критерієм вибираються зі списку за значенням, отриманим у результаті обчислення цієї формули або функції.

8. Вибрати зі списку всі записи про максимальну і міні­мальну суму замовлення.

У комірку Е6 копіюється заголовок поля Сума замовлення, у комірку Е7 заноситься функція

= МАКС(абсолютний діапазон числових значень цього поля), а у комірку Е8 — функція

=МИН(абсолютний діапазон числових значень цього поля).

Замість тексту функцій у критерію будуть показані обчис­лені за цими функціями значення.

41. Технологія використання функцій ВПР та ГПР у табличному процесорі MS EXCEL. Навести приклади.

=ВПР(пошукове значення; масив комірок; номер поля;[діапа-зон перегляду])

Функція ВПР провадить пошук вказаного значення у першо­му стовпчику масиву комірок і, якщо таке значення є у масиві, видає з поля значення, відповідне знайденому в тому ж рядку. Діапазон перегляду — необов'язковий логічний параметр, який може набувати значення ИСТИНА або ЛОЖЬ. Якщо він не вка­зується (ИСТИНА), то провадиться точний пошук, якщо ж вказу­ється (ЛОЖЬ), то провадиться приблизний пошук. Таблиця (ма­сив комірок) обов'язково має бути відсортована за зростанням по першому

Функція ГПР аналогічна ВПР, але замість полів(стовпчиків), вона аналізує рядки.

Функція ГПР має такий вигляд

=ГПР(пошукове значення; масив комірок; номер рядка;[діапа-зон перегляду])

Приклад:

Треба за введеним кодом виробу визначити:

• Назву виробу (вибирається з поля 2 — Назва виробу), Використовується функція ВПР з такими параметрами: Заданий код виробу — посилання на адресу комірки, де місти­ться значення коду або сам код,

Масив комірок — A3:F7,

Номер поля — 2 (стовпчик Назва виробу).

• замовлену кількість (вибирається з поля 3 — Замовлена к-сть),

Використовується функція ВПР з такими параметрами: Заданий код виробу — посилання на адресу комірки, де місти­ться значення коду, або сам код, Масив комірок — A3:F7, Номер поля — 3 (стовпчик Замовлена к-сть).

42. Технологія використання функцій баз даних для аналізу списку в табличному процесорі MS EXCEL. Навести приклади використання функцій БСЧЕТ ТА БИЗВЛЕЧЬ.

За допомогою функцій баз даних можна виконувати обчис­лення у списках — базах даних Microsoft Excel.

Загальний вигляд функції баз даних:

=ім'я функції(діапазон бази даних або його ім'я; поле;кри-терій)

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

=БСЧЕТ(діапазон бази даних або його ім'я; поле;критерш) — визначає кількість значень у вказаному числовому полі, якщо за­довольняється критерій,

Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)

5. Скільки разів сплачений податок перевищував середнє зна-чення. Використовується функція БСЧЕТ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — II1, або 9 (назва числового стовчика Кількість місти­ться у комірці 111, порядковий номер стовпчика= 9), Критерій — міститься у комірках F6:F7.

=БИЗВЛЕЧЬ(діапазон бази даних або його ім'я; поле;крите-рій) — визначає потрібне значення в указаному полі, якщо задовольняється критерій.

Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)

7. Прізвище клієнта з максимальною сумою замовлення. Використовується функція БИЗВЛЕЧЬ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — ВИ, або 2 (назва стовпчика Прізвище клієнта місти­ться у комірці В11, порядковий номер стовпчика= 2), Критерій — міститься у комірках Е6:Е7.

43. Технологія використання функцій баз даних для аналізу списку в табличному процесорі MS EXCEL. Навести приклади використання функцій БДСУММ та ДСРЗНАЧ.

За допомогою функцій баз даних можна виконувати обчис­лення у списках — базах даних Microsoft Excel.

Загальний вигляд функції баз даних:

=ім'я функції(діапазон бази даних або його ім'я; поле;кри-терій)

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

=БДСУММ(діапазон бази даних або його ім'я; поле;крите-рій) — визначає суму значень у вказаному полі, якщо задоволь­няється критерій,

Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)

Наприклад, за даними таблиці замовлень визначити: 1. Загальну суму податку за вироби, замовлені клієнтом Васи­льєвим.

Використовується функція БДСУММ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — II1, або 9 (назва стовпчика Податок міститься у комір­ці II1, порядковий номер стовчика= 9), Критерій — міститься у комірках В1 :В2.

=ДСРЗНАЧ(діапазон бази даних або його ім'я; поле;крите-рій) — визначає середнє значення у вказаному полі, якщо задо­вольняється критерій,

Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)

2. Середню кількість телефонів, замовлених клієнтом Васи­льєвим.

Використовується функція ДСРЗНАЧ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — F11, або 6 (назва стовчика Кількість міститься у ко­мірці F11, порядковий номер стовпчика= 6), Критерій — міститься у комірках В1 :С2.

44. Технологія використання функцій баз даних для аналізу списку в табличному процесорі MS EXCEL. Навести приклади використання функцій ДМИН та БСЧЕТА.

За допомогою функцій баз даних можна виконувати обчис­лення у списках — базах даних Microsoft Excel.

Загальний вигляд функції баз даних:

=ім'я функції(діапазон бази даних або його ім'я; поле;кри-терій)

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

=ДМИН(діапазон бази даних або його ім'я; поле;критерій) — визначає мінімальне значення в указаному полі, якщо задоволь­няється критерій,

Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)

4. Мінімальну кількість замовлених телефонів. Використовується функція ДМИН, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — F11, або 6 (назва стовчика Кількість міститься у ко-мірці F11, порядковий номер стовпчика= 6), Критерій — міститься у комірках Н6:Н7.

=БСЧЕТА(діапазон бази даних або його ім'я; поле;критерій) — визначає кількість значень у вказаному текстовому полі, якщо задо­вольняється критерій,

Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)

6. Скільки разів клієнт Васильєв замовляв телефони за готівку. Використовується функція БСЧЕТА, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — В11, або 2 (назва текстового стовпчика Прізвище клі­єнта міститься у комірці ВИ, порядковий номер стовпчика= 2), Критерій — міститься у комірках В1 :D2.

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

За допомогою функцій баз даних можна виконувати обчис­лення у списках — базах даних Microsoft Excel.

Загальний вигляд функції баз даних:

=ім'я функції(діапазон бази даних або його ім'я; поле;кри-терій)

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

=БИЗВЛЕЧЬ(діапазон бази даних або його ім'я; поле;крите-рій) — визначає потрібне значення в указаному полі, якщо задо­вольняється критерій.

Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)

7. Прізвище клієнта з максимальною сумою замовлення. Використовується функція БИЗВЛЕЧЬ, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — В11, або 2 (назва стовпчика Прізвище клієнта місти­ться у комірці В11, порядковий номер стовпчика= 2), Критерій — міститься у комірках Е6:Е7.

=ДМАКС(діапазон бази даних або його ім'я; поле;критерій) — визначає максимальне значення у вказаному полі, якщо задоволь­няється критерій,

Наприклад, діапазоном бази даних таблиці замовлень є комірки А11:L24 (або ім'я діапазону — База1)

3. Максимальну загальну вартість замовлення за готівку у 1997 році.

Використовується функція ДМАКС, параметрами якої є: Діапазон А11 :L24 (або ім'я діапазону — База1), Поле — НІ 1, або 8 (назва стовпчика Загальна вартість міс­титься у комірці НІ 1, порядковий номер стовчика= 8), Критерій — міститься у комірках D1:F2.

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 — строк експлуатації будівлі.