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

=БЗ(13,5%/2 ; 33*2 ; ; -27000)

Відповідь: 2 012 074,64 грн.

Обчислення поточної вартості

Для розрахунку поточної вартості єдиної суми вкладення (позики) і фіксованих періодичних платежів застосовується функція

=ПЗ(ставка; к-сть платежів; виплата; майбутня вартість; тип)

Функція ПЗ є оберненою щодо функції БЗ.

Приклад:

Фірмі будуть потрібні 5 000 грн через 12 років. Зараз фірма має гроші і готова покласти їх на рахунок єдиним вкладом, щоб через 12 років він досяг величини 5 000 грн. Обчислити необхідну суму для вкладення, якщо % ставка становить 12 % на рік.

Застосовується функція

=ПЗ(ставка; к-сть платежів; майбутня вартість)

За умовою задачі

ставка = 12 %

к-сть платежів =12

майбутня вартість = 5 000

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

=ПЗ(12%; 12; 5000)

Відповідь: -1 283,38 грн.

Отриманий результат від'ємний, бо ці гроші треба вкласти.

Вирахування чистої поточної вартості

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

Функція =НПЗ(ставка; сума1; сума 2; …; сума N)

сума 1,2,3… N -- значення виплат та надходжень

Приклад:

Інвестиції у проект на кінець першого року його реалізації становитимуть 10 000 грн. На наступні 3 роки очікуються річ-

ні доходи по проекту 3 000 грн, 4 200 грн, 6 800 грн. Витрати залучення капіталу 10 %. Розрахувати чисту поточну вартість проекту.

За умовою задачі інвестиція розміром у 10 000 грн відноситься не до початкового моменту, на який провадиться розрахунок, тому це значення включається у список аргументів. Цей параметр -- вкладення, тому має знак мінус.

Усі інші грошові потоки -- це доходи, тому мають знак плюс.

ставка = 10 %

сума 1 = -10 000

сума 2 = 3 000

сума 3 = 4 200

сума 4 = 6 800

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

=НПЗ(10%; -10 000; 3000; 4 200; 6 800)

Відповідь: 1 188,44 грн.

Обчислене значення -- це прибуток від вкладення 10 000 грн. з урахуванням витрат на залучення капіталу.

Визначення строку платежів

Для розрахунку строку платежів використовується функція =КПЕР(ставка; виплата; поточне значення; майбутнє значення; тип)

Приклад:

Розрахувати, через скільки років вклад розміром у 1 млн грн досягне значення 1 млрд грн, якщо річна ставка % = 16,79 %, а % нараховуються щоквартально.

Застосовується функція

=КПЕР(ставка; поточна вартість; майбутня вартість),

де за умовою задачі

ставка = 16,79%/4

поточна вартість = -1 000 000

майбутня вартість = 1 000 000 000

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

=КПЕР(16,79%/4; -1000000; 1000000000)

Відповідь: 167,99853

Отримане число -- це кількість кварталів, тому правильний результат отримується діленням числа на 4.

Правильна відповідь: 41,9996325

Визначення відсоткової ставки

Для розрахунку відсоткової ставки застосовується функція =НОРМА(к-сть платежів;виплата;поточна вартість;майбутня вартість;тип)

Приклад:

Фірмі будуть потрібні 100 млн грн через 2 роки. Фірма готова вкласти 5 млн грн одразу і по 2,5 млн кожного наступного місяця. Розрахувати % ставку, щоб отримати необхідну суму в кінці 2 року.

Використовується функція

=НОРМА(к-сть платежів; початкова вартість; майбутня вартість)

За умовою задачі

к-сть платежів = 2*12

виплата = -2 500 000

початкова вартість = -5 000 000

майбутня вартість = 100 000 000

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

=НОРМА(24; -2500000; -5000000; 100000000)

Відповідь: 3,28 %

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

Правильна відповідь: 46,70 %

Підбиття підсумків у базі даних

Один зі способів обробки й аналізу бази даних полягає в підбитті різних підсумків. За допомогою команди Данные/ Итоги можна вставити рядок підсумків у список, здійснивши підсумовування даних у потрібний спосіб. У разі вставляння рядків підсумків Excel автоматично поміщає в кінець списку даних рядок загальних підсумків.

Після виконання команди Данные/Итоги ви можете виконати такі операції:

· вибрати одну чи кілька груп для автоматичного підбиття підсумків щодо цих груп;

· вибрати функцію для підбиття підсумків;

· вибрати дані, за якими потрібно підбити підсумки.

Крім підбиття підсумків по одному стовпцю, автоматичне підбиття підсумків дає можливість:

· виводити один рядок підсумків щодо кількох стовпців;

· виводити багаторівневі, вкладені рядки підсумків щодо кількох стовпців;

· виводити багаторівневі рядки підсумків з різними способами обчислення для кожного рядка;

· ховати чи показувати детальні дані в цьому списку

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

Синтаксис: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функції; посилання)

Номер_функції -- це число від 1 до 11, що вказує, яку функцію використовувати під час обчислення підсумків усередині списку.

Номер функціїФункція

1РЗНАЧ

2СЧЕТ

3СЧЁТЗ

4МАКС

5МИН

6ПРОИЗВЕД

7СТАНДОТКЛОН

8СТАНДОТКЛОНП

9СУММ

10ДИСП

11ДИСПР

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

Якщо список із проміжними підсумками вже створений, його можна модифікувати, редагуючи формулу з функцією ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Вставка проміжних підсумків

Для кожного товару можна ввести в таблицю проміжні підсумки -- суми по полях: Кількість, Сума закупівлі, Сума реалізації, Прибуток і середнє значення поля Закупівельна ціна (грн).



Спочатку заповнюються поля, що містять вихідні дані: «Найменування», «Фірма-постачальник», «Ціна (грн)» і «Кількість». Поля, що обчислюються, таблиці заповнюються формулами. Для створення формули в полі «Ціна ($)» використовується ім'я, присвоєне комірці, що містить числове значення поточного курсу валюти. Для створення формул у полі «Сума реалізації» використовується часткова заборона модифікації адреси комірок (за допомогою значка $ перед номером рядка), що містять роздрібну ціну для кожного з товарів.

Проміжні підсумки заповнюються через меню Данные/Итоги.../Промежуточные итоги:



Рис. . Виведення проміжних підсумків

Попередньо необхідно виділити ділянку таблиці, в якій ви збираєтеся підбивати підсумки, включивши в неї рядок з назвами полів. У наведеному вище прикладі -- це заголовок таблиці («№ п/п»; «Найменування»; ... «Валовий дохід») і всі записи щодо товарів (запис -- це один рядок у таблиці, що починається з порядкового номера запису і закінчується цифрою валового доходу).

1. У списку «При каждом изменении в»: установити значення «Найменування».

2.У списку «Операція» встановити значення Сума.

3.У списку «Добавить итоги по»: відзначити елементи: «Сума реалізації», «Сума закупівлі», «Кількість», «Валовий дохід».

4. Звести прапорці: «Заменить текущие итоги» і «Итоги под данными».

5. Такі формули мають бути записані в комірки таблиці (табл.).

Такий вигляд матиме результат обчислення формул (табл.):

Основи мови макросів. Запис макросів. Виконання макросу

Макрос - це спосіб виконання набору команд в Excel, а також спосіб прискорення вводу команд, що повторюються.

Якщо ви надаєте перевагу форматуванню чисел, без допомоги панелі інструментів «Форматирование», то вам потрібно виконати, принаймні, біля шести клацань кнопкою миші або одинадцять натиснень клавіш клавіатури («Alt+м», «я», натискання однієї або декількох клавіш управління курсором для вибору вкладки, «Alt+ч» та натискання клавіш управління курсором для вибору числового формату, «Alt+т» та натискання клавіш управління курсору для вибору типа формату, натискання клавіші «Enter»), або ще більше клацань миші або натиснень клавіш для створення формату користувача. Це, звичайно, не потребує великих зусиль, але при повторені цих дій по декілька разів у день, дратує.

Якби ви могли замінити ці дії простим натисненням комбінації клавіш, наприклад, «Ctrl+е», або вибором одного пункту в меню сервіс, або, ще краще клацанням на кнопці панелі інструментів. Це значно спростило б ваше завдання.

В Excel існує можливість записати будь-які дії при роботі з таблицею та зберегти їх на листку макросів робочої книги. Один раз записавши макрос, ви можете повторити його в любий час. Ці дії зберігаються на листку макросів у вигляді послідовності команд, виконання яких називається виконання макросу.

Особиста книга макросів не чим не відрізняється від звичайної робочої книги, за винятком того, що стовпчики листка особистої книги трохи ширші. Сам макрос розташований у верхньому лівому куті листка, в чарунках А1:А3. Чарунка А1 містить ім'я макросу та букву швидкої клавіші, яка назначена макросу. Чарунки А2 і А3 містять макрофункцію форматування чарунки і команду, яка передає керування користувачу.

Макрофункції - це формули, вони завжди починаються зі знаку рівності «=». На листку макросу завжди відображаються формули, а не їх результати. Існує команда відображення формул листка робочої книги, але звичайно на листку робочої книги відображені тільки значення формул. Ви можете використовувати вкладку «Вид» діалогового вікна «Параметри», яке відкривається з меню «Сервіс», для того щоб не відображати формули на листку макросу, проте значення, що повертаються формулами не інформативні. У даному випадку макрофункції в другому і третьому рядках будуть замінені значенням ЛОЖЬ.

Коли ви знову відобразите формули замість їх значень вони будуть вирівняні по лівому краю чарунок, і таке вирівняння не може бути змінене параметрами вирівняння з меню «Формат». Усі інші параметри форматування на листку макросу застосовуються та як і на звичайному листку робочої книги.

Макрос можна виконати одним з трьох способів. Перший і самий легший спосіб - нажати комбінацію клавіш, яку ви назначили (якщо назначили). Другий - використати діалогове вікно макрос, яке відкривається після вибору пункту «Макрос» з меню «Сервис». В ньому знаходиться список всіх макросів персональної книги макросів, ви можете вибрати потрібний макрос з діалогового вікна макрос і клацнути по кнопці «ОК». Третій спосіб - вибрати параметр формат чарунки, який тепер знаходиться в меню «Сервис».

Якщо ви вийдете з Excel, не закриваючи персональної книги макросів і не клацнувши по кнопці «Да» при відповіді на питання о збережені змін в цій книзі, вікно персональної книги макросів буде з'являтися кожен раз при запуску Excel, доки ви не виберете пункт «Скрыть» з меню «Окно» та не збережете зміни при виході з Excel.

Діагностика помилок у формулах

Реакцією на неправильне уведення формул є повідомлення Excel про помилки, тобто значення в комірках, що починаються зі знаку #. Щоб легше було знаходити й усувати помилки у формулах, у Excel передбачена найпростіша діагностика помилок, а саме: помилки розділяються по категоріях, і кожній категорії відповідає своє повідомлення. Список можливих значень помилок:

† ##### - найбільш часте значення помилки в роботі в початківців, що з'являється, коли ширина комірки недостатня для розміщення в ній числа, чи дати часу. Щоб усунути помилку, потрібно розширити комірку або змінити формат числа.

† #ИМЯ? - неможливість розпізнати використовуване ім'я. Це значення помилки виникає, коли неправильно зазначене ім'я об'єкта або мається посилання на ім'я, що було вилучено, коли невірно записана функція, коли при записі адрес замість латині використані кирилиця і т.д.

† #ЗНАЧ! - спроба некоректного використання функції. Звичайною помилкою є невідповідність даних установленому формату, наприклад, замість числа або дати в аргументі записано текст. Це ж значення помилки буде з'являтися, коли для функції або оператора, що вимагають одного значення аргументу, записують декілька.

† #ЧИСЛО! - значення помилки, що означає проблему, зв'язану з представленням або з використанням чисел. Не виключено, що у функції з числовим аргументом використовується аргумент нечислового формату. Можливо також, що в комірку уведена формула, що повертає занадто велике значення по модулю (понад 1037), або результат обчислень по формулі не сходиться до кінцевого значення.

† #ССЫЛКА! - означає наявність проблеми з інтерпретацією посилань, що маються у формулі. Можливо, що формула містить посилання на комірку, що уже вилучена, чи посилання на комірку, у яку скопійований вміст інших комірок.

† #ДЕЛ/0! - спроба ділення на нуль. Така ситуація частіше виникає не через те, що в комірку записаний явне ділення на нуль (оператор /0), а як наслідок використання як дільник посилання на порожню комірку або комірку, що містить нульове значення.

† #ПУСТО! - значення помилки, що з'являється у випадку завдання в посиланні порожньої безлічі комірок. Можливо, що помилка у визначенні перетинання двох діапазонів.

† #Н/Д - скорочення від терміна «невизначені дані». Це значення помилки звичайно спеціально вводиться в комірки, щоб запобігти обчислення, що не можуть бути зроблені через відсутність даних.

Смарт-тег перевірки помилок

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

Панель аудиту формул

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

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

Виявлення і виправлення помилок у виконаних розрахунках

Кнопка Перевірка наявності помилок (Error Checking tool) дозволяє знайти на робочому аркуші невірні, з точки зору Excel формули. Після знаходження помилки відображується діалогове вікно з можливими варіантами виправлень. Якщо на вашу думку помилки немає, то натискуйте кнопку Пропустити помилку (Ignore).

Excel 2003 стежить за тим, чи не має яке-небудь вічко, що містить число, текстовий формат; контролює, чи не бере яка-небудь формула дані з порожнього вічка (унаслідок неправильної вказівки її параметрів). Параметри відстежування помилок можна задати після вибору команди Параметри (Options) у меню Сервіс (Tools) на вкладці Перевірка помилок (Error Checking). Якщо у вічку виявлена помилка, це вічко позначається зеленим трикутником в лівому верхньому кутку.

Відстеження вічок, що беруть участь в обчисленнях

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

Прослідити дорогу від вихідних даних до результатів дозволяють команди Залежності формул (Formula Auditing) Впливаючі вічка (Trace Precedents) і Залежні вічка (Trace dependents) у меню Сервіс (Tools) або однойменні кнопки на панелі інструментів Залежності (Formula Auditing).

Як приклад прослідимо, які вічка впливають на результат обчислень за формулою, приведеною у вічку F5 (мал.). Виділимо вічко F5 і натискуватимемо кнопку Впливаючі вічка панелі інструментів Залежності. У таблиці з'являться стрілки, що показують вічка, що беруть участь у формулі, Аби прибрати стрілки, натискуйте кнопку Прибрати стрілки до впливаючих вічок (Remove Precedent Arrows).

Перед тим, як відображувати панель інструментів Залежності слід переконатися, що на вкладці Вигляд (View) (мал.) у вікні команди Параметри (Options) з меню Сервіс (Tools) перемикач в групі Об'єкти (Objects) не стоїть в положенні не відображувати (Hide all).

Мал. Відображення стрілок, що показують, які вічка беруть участь в здобутті результату

Після виділення вічка, що містить помилку, можна натискувати кнопку Джерело Помилки (Trace Error) -- з'являться червоні стрілки, що показують, які вічка можуть бути джерелом помилки, наприклад, ділення на нуль.

Кнопка Прибрати всі стрілки (Remove All Arrows) видаляє всі стрілки на вя аркуші .

Знаходження вічок, що впливають на задане вічко

Мал. Діалогове вікно Виділення групи вічок

Excel дозволяє знайти вічка, що впливають на вічко, що цікавить нас. Для визначення цих вічок виконаєте наступні дії:

виділите задане вічко;

виберіть команду Перейти (Go To) у меню Правка (Edit) і натискуйте кнопку Виділити (Special) у діалоговому вікні Перехід (Go To);

на екрані відображуватиметься діалогове вікно Виділення групи вічок (Go To Special). Аби знайти вічка, що впливають на задане вічко, встановите перемикач впливаючі вічка (Precedents) і один з пов'язаних з ним перемикачів на всіх рівнях (All levels) або лише безпосередньо (Direct only); після того, як ви натискуватимете кнопку ОК на аркуші будуть відмічені всі впливаючі вічка.

Вікно контрольного значення

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

Для спостереження за формулами і їх результатами виконаєте наступні дії:

До відображення вікна виділите вічка для перегляду. Аби виділити на аркуші всі вічка, що містять формули, в меню Правка (Edit) виберіть команду Перейти (Go To)натискуйте кнопку Виділити (Special) а потім встановите кнопку перемикача в положення формули (formulas).

Мал. Вкладка Обчислення діалогового вікна Параметри, що дозволяє обмежити час, що витрачається на обчислення

У меню Сервіс (Tool) вкажіть на пункт Залежності формул (Formula Auditing) а потім виберіть команду Показати вікно контрольного значення (Show Watch Window).

Натискуйте кнопку Додати контрольне значення. (Add Watch).

Натискуйте кнопку Додати (Add).

Перемістите панель інструментів Вікно контрольного значення у верхню, нижню, ліву або праву частину вікна.

Аби змінити ширину стовпця, перетягнете правий кордон заголовка стовпця.

Аби відображувати вічко, на яке посилається вічко на панелі інструментів Вікно контрольного значення, двічі клацніть вічко.

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

Вказати вічко, поточне значення якого відображуватимуться у вікні після обчислення за формулою, дозволяє кнопка Показати вікно контрольного значення (Add watch) панелі інструментів Залежності.

Висновок

Електронні таблиці Microsoft Excel мають розвинуті засоби роботи з числовою інформацією. Дана програма дуже проста й ефективна в роботі. Використовуючи її, і натискаючи клавіші або працюючи з “мишею” можна дуже швидко й ефективно виконувати не тільки саму трудомістку роботу, але й автоматизувати легку щоденну. До найбільш корисних і ефективних функцій Excel варто віднести: автозаповнення, автозаміну, формули та вбудовані функції, сортування, фільтрацію, створення діаграм та запитів, прогнозування рішень та ін. У сукупності ці функції здатні не тільки полегшити роботу з електронними таблицями, але і відчутно скоротити час трудомістких обрахунків та аналізу даних.

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

Перелік використаної літератури

1. Гаевский А.Ю.

- Самоучитель работы в Microsoft Office: Word 97/2000, Excel 97/2000, Электронная почта. К.: А.С.К., 2001. - 480 с.: ил.

- Леонтьев Ю. Microsoft Office 2000: краткий курс. СПб.: Издательство “Питер”, 2000. - 228 с.: ил.

2. Руденко В.Д., Макарчук О.М., Патланжоглу М.О.

3. Практичний курс інформатики.

4. За ред. Мадзігона В.М. - К.:Фенікс, 1997.-304с.

5. Спірін О.М.

6. Практична інформатика: 2-ге видання, перероблене і доповнене.

7. Методичний посібник.- Житомир: Поліграфічний центр ЖДПУ,

8. 2001.-176с., іл.

9. В. Пасько, А. Колесников

10. Самоучитель работы на персональном компьютере. К.: Издательская группа BHV, 2001. - 656 с., ил.

11. Мартин С. Метьюз. «Exel для Windows 95. Проще простого». 5 видання. Київ. 1996 рік.

12. Довідкова система Microsoft Excel 2003.

Размещено на Allbest.ru