Табличний процесор Excel. Майстер функцій
План
1. Табличний процесор Excel: загальні відомості
2. Функції Excel. Майстер функцій
2.1 Фінансові функції
2.2 Математичні функції
2.3 Логічні функції
3 Копіювання формул і адрес аргументів
4 Копіювання формул без зміни адрес аргументів
1. Табличний процесор Excel: загальні відомості
Табличний процесор Excel представляє собою програму (частину пакету програм Office), призначену здійснювати різноманітні математичні операції, починаючи від операцій елементарної математики і закінчуючи громіздкими обчисленнями здебільшого економічного характеру, але придатних і для інших сфер господарювання. Табличний процесор Excel також містить убудоване прикладне середовище програмування Visual Basic For Applications (VBA), що дозволяє створювати нові програмні модулі для полегшення роботи користувача програмою (наприклад, створити модуль, що за вхідними даними обчислює рентабельність інвестиційного проекту або ж ефективність виробництва – Excel не містить окремих формул і функцій для здійснення цих обчислень, але на основі тих функцій, що маються, можна створити окрему програму, яка б містила універсальні формули, потрібні для поточних розрахунків).
Основним об’єктом роботи в табличному процесорі Excel є Лист. Лист – це вся робоча площа екрану Excel. Кожний лист містить комірки, в яких відбуваються обчислення. За замовчанням файлу Excel надається ім’я «Книга1». Книга – це файл Excel, що вміщує один або декілька листів.
Основний прийом роботи з Excel – це створення формул. Вони створюються на основі введених чисел та їхньої обробки за допомогою стандартних математичних операцій чи за допомогою вбудованих в Excel функцій, про які буде більш докладно розказано далі в даному рефераті.
2. Функції Excel. Майстер функцій
Функції— заздалегідь визначені формули, які виконують обчислення по заданих величинах, що називаються аргументами, і в зазначеному порядку. Ці функції дозволяють виконувати як прості, так і складні обчислення. Наприклад, функція ОКРУГЛ округляє число в даній комірці.
Структура функції починається зі знака рівності (=), за ним іде ім'я функції, що відкриває дужка, список аргументів, розділених комами, що закриває дужка.
Для появи списку доступних функцій клацніть комірку і натисніть клавіші SHIFT+F3.
Існують різні типи аргументів: число, текст, логічне значення (ИСТИНА й ЛОЖЬ), масиви(масив – це об'єкт, що використовується для одержання декількох значень у результаті обчислення однієї формули або для роботи з набором аргументів, розташованих у різних комірках і згрупованих по рядках або стовпцях. Діапазон масиву використовує загальну формулу; константа масиву являє собою групу констант, що використовуються як аргументи.), значення помилки (наприклад #Н/Д), або посилання на комірку. Як аргументи використаються константи(константа – це постійне (не обчислюване) значення. Вираження й результат обчислення вираження константами не є.), формули, або функції. У кожному конкретному випадку необхідно використати відповідний тип аргументу.
Спливаюча підказка із синтаксисом й аргументами з'являється після уведення функції. Наприклад, що спливає підказка з'явиться після уведення =ОКРУГЛ(. Спливаючі підказки з'являються тільки для убудованих функцій.
Діалогове вікно Майстер функцій полегшує уведення функцій при створенні формул, що містять функції. При уведенні функції у формулу діалогове вікно Майстер функцій відображає ім'я функції, всі її аргументи, опис функції й кожного аргументу.
У деяких випадках може знадобитися використання функції як одного з аргументів(аргумент – це значення, використовувані функцією для виконання операцій або обчислень. Тип аргументу, що використовується функцією, залежить від конкретної функції. Звичайно аргументи, що використовуються функціями, є числами, текстом, посиланнями на комірки й іменами.) іншої функції. Наприклад, у формулі функція СРЗНАЧ вкладена у функцію ЕСЛИ для порівняння середнього значення декількох значень із числом 50.
Вкладена функція, що використовується як аргумент, повинна обчислювати відповідному цьому аргументу тип даних. Наприклад, якщо аргумент повинен бути логічним, тобто мати значення або ИСТИНА, або ЛОЖЬ, те вкладена функція в результаті обчислень теж повинна давати логічне значення або ИСТИНА, або ЛОЖЬ. Інакше з'явиться повідомлення про помилку «#ЗНАЧ!».
У формулах можна використати до семи рівнів вкладення функцій. Коли функція Б є аргументом функції А, функція Б перебуває на другому рівні вкладеності. Наприклад, припустимо, що функції СРЗНАЧ і СУМ уважаються функціями другого рівня, тому що обидві вони є аргументами функції ЕСЛИ. Функція, вкладена як аргумент у функцію СРЗНАЧ, буде функцією третього рівня й так далі.
Майстер функцій – це модуль (підпрограма), вбудована в пакет EXCEL, що здійснює користування вбудованими функціями та полегшує користування ними. Можна вводити функції вручну, якщо знати їхній синтаксис, але бувають випадки, коли функція дуже складна і ручний ввід не є зручним. Тоді легше використовувати Майстер функцій (рис.1). Його можна відкрити за допомогою виконання команди Функція меню Вставка.
Вікно майстра функцій складається з декількох частин. У верхній частині є поле для заповнення назви потрібної функції та кнопка швидкого пошуку. Трохи нижче розташовано поле функцій, що розкривається. За його допомогою можна відкрити всі функції, ті 10, що востаннє використовувались, функції певної групи тощо. У полі, що розташовано іще нижче, одразу ж виводяться функції, які «замовив» користувач. За замовчанням відкривається 10 функцій, що востаннє використовувались. Нижче цього поля можна знайти синтаксис написання обраної (виділеної) функції та її коротке описання. Якщо користувач вважає інформацію по функціях, надану у віконці, недостатньою, можна викликати натисканням гіперпосилання довідку з приводу даної функції.
Рис.1. Екранна форма Майстра функцій
Після вибору відповідної функції і натискання клавіші «ОК», з’являється вікно, в якому власне і відбувається здійснення обчислень. Якщо аргументів функції багато, у вікні автоматично збільшується кількість потрібних для обчислення полів, але не більш як 30.
Рис. 2. Аргументи функції. Екранна форма.
2.1 Фінансові функції
Фінансові функції використовуються здебільшого для фінансових обчислень. Як правило, кожна з наведених нижче функцій використовується у якості вкладеної, тому що фінансові розрахунки здебільшого містять складені показники, які базуються на основі декількох функцій. Також фінансові обчислення здебільшого виконують за допомогою стандартних функцій Excel та можливостей VBA. Розглянемо коротко, які дії виконують фінансові функції EXCEL.
НАКОПДОХОДПовертає накопичений доход по цінних паперах з періодичною виплатою відсотків.
НАКОПДОХОДПОГАШПовертає накопичений доход по цінних паперах, відсоток по яких виплачується в строк вступу в силу.
АМОРУМПовертає величину амортизації для кожного періоду, використовуючи коефіцієнт амортизації.
АМОРУВ Повертає величину амортизації для кожного звітного періоду.
ДНЕЙКУПОНДОПовертає кількість днів між початком періоду купона й датою угоди.
ДНЕЙКУПОНПовертає число днів у періоді купона, що містить дату угоди.
ДНЕЙКУПОНПОСЛЕПовертає число днів від дати угоди до строку наступного купона.
ДАТАКУПОНПОСЛЕПовертає наступну дату купона після дати угоди.
ЧИСЛКУПОНПовертає кількість купонів, які можуть бути оплачені між датою угоди й строком вступу в силу.
ДАТАКУПОНДОПовертає попередню дату купона перед датою угоди.
ОБЩПЛАТПовертає загальну виплату, зроблену між двома періодичними виплатами.
ОБЩДОХОДПовертає загальну виплату по позиці між двома періодами.
ФУОПовертає амортизацію майна на заданий період, використовуючи метод постійного обліку амортизації.
ДДОБПовертає величину амортизації майна для зазначеного періоду при використанні методу дворазового обліку амортизації або іншого явно зазначеного методу.
СКИДКАПовертає норму знижки для цінних паперів.
РУБЛЬ.ДЕСПеретворить ціну в рублях, виражену у вигляді дробу, у ціну в рублях, виражену десятковим числом.
РУБЛЬ.ДРОБЬПеретворить ціну в рублях, виражену десятковим числом, у ціну в рублях, виражену у вигляді дробу.
ДЛИТПовертає щорічну тривалість дії цінних паперів з періодичними виплатами по відсотках.
ЭФФЕКТПовертає діючі щорічні процентні ставки.
БСПовертає майбутнє значення внеску.
БЗРАСПИСПовертає майбутнє значення початкової пропозиції після обліку ряду складних процентних ставок.
ИНОРМАПовертає ставку прибутковості повністю забезпеченого цінного папера.
ПРПЛТПовертає величину виплати прибутку на вкладення за даний період.
ВСДПоновлює внутрішню ставку прибутковості (віддачі) для серії потоків коштів.
ПРОЦПЛАТОбчислює виплати за зазначений період інвестиції.
МДЛИТПовертає модифіковану тривалість Маколея для цінних паперів з передбачуваною номінальною вартістю 100 рублів.
МВСД Повертає внутрішню ставку прибутковості, при якій позитивні й негативні грошові потоки мають різну ставку.
НОМИНАЛПовертає номінальну річну процентну ставку.
КПЕРПовертає загальна кількість періодів виплати для даного внеску.
ЧПСПовертає чисту наведену вартість інвестиції, заснованої на серії періодичних грошових потоків і ставці дисконтування.
ЦЕНАПЕРВНЕРЕГПовертає ціну за 100 рублів номінальної вартості цінних паперів з нерегулярним першим періодом.
ДОХОДПЕРВНЕРЕГПовертає доход по цінних паперах з нерегулярним першим періодом.
ЦЕНАПОСЛНЕРЕГПовертає ціну за 100 рублів номінальної вартості цінних паперів з нерегулярним останнім періодом.
ДОХОДПОСЛНЕРЕГПовертає доход по цінних паперах з нерегулярним останнім періодом.
ПЛТПовертає величину виплати за один період річної ренти.
ОСПЛТПовертає величину виплат на основний капітал для внеску в заданий період.
ЦЕНАПовертає ціну за 100 рублів номінальної вартості цінних паперів, по яких виробляється періодична виплата відсотків.
ЦЕНАСКИДКАПовертає ціну за 100 рублів номінальної вартості цінних паперів, на які зроблена знижка.
ЦЕНАПОГАШПовертає ціну за 100 рублів номінальної вартості цінних паперів, по яких виплачується прибуток у момент вступу в силу.
ПСПовертає наведену (на цей момент) вартість інвестиції.
СТАВКАПовертає процентну ставку по ануїтету за один період.
ПОЛУЧЕНОПовертає суму, отриману в строк вступу в силу повністю забезпечених цінних паперів.
АПЛПовертає величину безпосередньої амортизації майна за один період.
АСЧПовертає величину амортизації активу за даний період, розраховану методом «суми (річних) чисел».
РАВНОКЧЕКПовертає еквівалентний облігації доход по казначейському чеку.
ЦЕНАКЧЕКПовертає ціну за 100 рублів номінальної вартості для казначейського чека.
ДОХОДКЧЕКПовертає доход по казначейському чеку.
ПУОПовертає величину амортизації майна для явно зазначеного або відповідного періоду при використанні методу разового обліку амортизації.
ЧИСТВНДОХПовертає внутрішню ставку прибутковості запланованих неперіодичних грошових потоків.
ЧИСТНЗПовертає чисту поточну вартість інвестиції, що обчислює на основі ряду надходжень наявних, які не обов’язково є періодичними.
ДОХОДПовертає доход від цінних паперів, по яких виробляються періодичні виплати відсотків.
ДОХОДСКИДКАПовертає річний доход по цінних паперах, на які зроблена знижка. Приклад— казначейські чеки.
ДОХОДПОГАШПовертає річний доход від цінних паперів, відсоток по яких виплачується в строк погашення.
2.2 Математичні функції
Математичні функції в Excel – найчисленніша група функцій. За їхньою допомогою дуже легко проводити різноманітні розрахунки, особливо економічні.
ABSПовертає модуль (абсолютну величину) числа.
ACOSПовертає арккосинус числа.
ACOSHПовертає гіперболічний арккосинус числа.
ASINПовертає арксинус числа.
ASINHПовертає гіперболічний арксинус числа.
ATANПовертає арктангенс числа.
ATAN2Повертає арктангенс для заданих координат x й y.
ATANHПовертає гіперболічний арктангенс числа.
ОКРВВЕРХОкругляє число до найближчого цілого або до найближчого кратного зазначеному значенню.
ЧИСЛКОМБПовертає кількість комбінацій для заданого числа об'єктів.
COSПовертає косинус числа.
COSHПовертає гіперболічний косинус числа.
ГРАДУСЫПеретворить радіани в градуси.
ЧЁТНОкругляє число до найближчого парного цілого.
EXP Повертає число e, зведене в зазначений ступінь.
ФАКТРПовертає факторіал числа.
ДВФАКТРПовертає подвійний факторіал числа.
ОКРВНИЗОкругляє число до найближчого меншого по модулі цілого.
НОДПовертає найбільший загальний дільник.
ЦЕЛОЕОкругляє число до найближчого меншого цілого.
НОКПовертає найменше загальне кратне.
LNПовертає натуральний логарифм числа.
LOGПовертає логарифм числа по заданій підставі.
LOG10Повертає десятковий логарифм числа.
МОПРЕДПовертає визначник матриці, що зберігається в масиві.
МОБРПовертає зворотну матрицю (матриця зберігається в масиві).
МУМНОЖПовертає добуток матриць, що зберігаються в масивах.
ОСТАТПовертає остача від ділення.
ОКРУГЛТПовертає число, округлене з необхідною точністю.
МУЛЬТИНОМПовертає мультиномінальний коефіцієнт множини чисел.
НЕЧЁТОкругляє число до найближчого непарного цілого.
ПИПовертає число «пі».
СТЕПЕНЬПовертає результат піднесення числа в ступінь.
ПРОИЗВЕДПовертає добуток аргументів.
ЧАСТНОЕПовертає цілу частина частки при діленні.
РАДИАНЫПеретворить градуси в радіани.
СЛЧИСПовертає випадкове число в інтервалі від 0 до 1.
СЛУЧМЕЖДУПовертає випадкове число в заданому інтервалі.
РИМСКОЕПеретворить число в арабському записі до числа в римської як текст.
ОКРУГЛОкруглює число до зазначеної кількості десяткових розрядів.
ОКРУГЛВНИЗОкруглює число до найближчого меншого по модулі цілого.
ОКРУГЛВВЕРХОкруглює число до найближчого по модулі більшого цілого.
РЯД.СУММПовертає суму статечного ряду, обчислену по заданій формулі.
ЗНАКПовертає знак числа.
SINПовертає синус заданого кута.
SINHПовертає гіперболічний синус числа.
КОРЕНЬПовертає позитивне значення квадратного кореня.
КОРЕНЬПИПовертає значення квадратного кореня з (число * «пі»).
ПРОМЕЖУТОЧНЫЕ.ИТОГИПовертає проміжний підсумок у списку або базі даних.
СУММСумує аргументи.
СУММЕСЛИСумує осередку, що задовольняють заданій умові.
СУММПРОИЗВПовертає суму добутків відповідних елементів масивів.
СУММКВПовертає суму квадратів аргументів.
СУММРАЗНКВПовертає суму різниць квадратів відповідних значень у двох масивах.
СУММСУММКВПовертає суму сум квадратів відповідних елементів двох масивів.
СУММКВРАЗНПовертає суму квадратів різниць відповідних значень у двох масивах.
TANПовертає тангенс числа.
TANHПовертає гіперболічний тангенс числа.
ОТБРВідкидає дробову частину числа.
2.3 Логічні функції
Логічні функції нечисленні за своєю кількістю, але мають велике значення для обчислень, причому як в економіці, так і в інших галузях господарства. Якщо аргументи функції знаходяться в певному діапазоні, дуже зручно користуватись логічними функціями для обмеження границь обчислення.
ИПовертає значення ІСТИНА, якщо всі аргументи мають значення ІСТИНА.
ЛОЖЬПовертає логічне значення НЕПРАВДА.
ЕСЛИВиконує перевірку умови.
НЕМіняє на протилежне логічне значення свого аргументу.
ИЛИПовертає ІСТИНА, якщо хоча б один аргумент має значення ІСТИНА.
ИСТИНАПовертає логічне значення ІСТИНА.
2.4 Функції дати, часу
Функції дати і часу особливо використовуються в бухгалтерських та економічних розрахунках, коли зручніше встановити у формулу значення певного діапазону дат або ж функцію дати чи часу.
ДАТАПовертає задану дату в числовому форматі Microsoft Excel.
ДАТАЗНАЧПеретворить дату з текстового формату в числовий.
ДЕНЬПеретворить дату в числовому форматі в день місяця.
ДНЕЙ360Обчислює кількість днів між двома датами на основі 360-денного року.
ДАТАМЕСПовертає дату в числовому форматі, що відстоїть на задане число місяців уперед або назад від початкової дати.
КОНМЕСЯЦАПовертає дату в числовому форматі для останнього дня місяця, що відстоїть уперед або назад на задане число місяців.
ЧАСПеретворить дату в числовому форматі в годинники.
МИНУТЫПеретворить дату в числовому форматі у хвилини.
МЕСЯЦПеретворить дату в числовому форматі в місяці.
ЧИСТРАБДНИПовертає кількість робочих днів між двома датами.
ТДАТАПовертає поточну дату й час у числовому форматі.
СЕКУНДЫПеретворить дату в числовому форматі в секунди.
ВРЕМЯПовертає заданий час у числовому форматі.
ВРЕМЗНАЧПеретворить час із текстового формату в числовий формат.
СЕГОДНЯПовертає числовий формат поточної дати.
ДЕНЬНЕДПеретворить дату в числовому форматі в день тижня.
НОМНЕДЕЛИ Перетворить числове подання в число, що вказує, на який тиждень року доводиться зазначена дата.
РАБДЕНЬПовертає дату в числовому форматі, що відстоїть уперед або назад на задану кількість робочих днів.
ГОДПеретворює дату в числовому форматі в рік.
ДОЛЯГОДАПовертає частку року, що становить кількість днів між початковою й кінцевою датами.
3. Копіювання формул і адрес аргументів
Копіювати формули можна за допомогою стандартних команд «Копіювати» - «Вставити» (або ж за допомогою комбінацій клавіш Ctrl+C – Ctrl+V).
При переміщенні комірки з формулою посилання, що вміщуються у формулі, не змінюються. При копіюванні формули посилання на комірки можуть змінюватися залежно від їхнього типу.
1. Виділіть комірку, що містить формулу.
2. Переконайтеся, що посилання на комірки, що використовуються у формулі, дають необхідний результат. Виберіть необхідний тип посилання. Щоб перемістити формулу, використайте абсолютне посилання (абсолютне посилання – це закріплення комірки в формулі знаками $ перед літерою та числом адреси комірки).
3. Копіювання формул без зміни адрес аргументів
Якщо потрібно копіювати формулу без зміни адрес аргументів, необхідно навести клавішу миші на правий нижній кут комірки, де міститься значення, обчислене за даною формулою. Замість знаку миші з’явиться знак «плюс». Необхідно, «зачепившись» за цей знак, «протягнути» комірку до необхідної комірки. При цьому адреси комірок будуть змінюватись відповідно до про стягнення. Якщо цього не повинно відбуватись, необхідно закріпити знаком $ адреси комірок у вихідній формулі. Це можна просто зробити за допомогою клавіші F4.