Posobie_1_chast2_Excel

Примітка приєднається до комірки і буде з'являтися при наведенні на нього покажчика миші. Для зміни тексту примітки слід виділити відповідну комірку і в меню Вставка вибрати пункт Изменить примечание. Також для цього зручно використовувати контекстне меню.

Щоб побачити одночасно всі примітки і працювати з ними, можна перейти в режим Примечания через відповідний пункт у меню Вид. При цьому з'являється панель Рецензирование (див. рис. 10), що містить кнопки для роботи з примітками.

Рис. 10. Панель Рецензирование

Сортування дозволяє переупорядкувати рядки в таблиці за будь-яким полем. Для сортування даних необхідно виділити одну комірку таблиці і викликати команду Сортировка меню Данные.

У поле списку Сортировать по (див. рис.11) обирається поле, за яким будуть відсортовані дані, і тип сортування:

•по возрастанию – цифри сортуються за зростанням, текст – за абеткою, логічні вираження – ЛОЖЬ передує ИСТИНА.

•по убыванию – сортування в зворотному порядку.

Рис. 11. Діалогове вікно Сортировка диапазона У поле списку Затем по вказується поле, за яким будуть відсортовані

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

140

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

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

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

Форми даних. При виконанні характерних для баз даних операцій, таких як пошук, сортування, підведення підсумків, Microsoft Excel автоматично розглядає таблицю як базу даних.

При перегляді, зміні, додаванні і видаленні запису в базі даних, а також при пошуку записів за визначеним критерієм зручно використовувати форми даних. При звертанні до команди Форма меню Данные Microsoft Excel зчитує дані і створює діалогове вікно форми даних (див. рис.12). У формі даних на екран виводиться один запис. При введенні чи зміні даних у полях цього вікна змінюється вміст відповідних комірок бази даних.

Рис. 12. Діалогове вікно форми даних

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

141

вивести на екран відразу всі поля в даній таблиці, до 32 полів за один раз. За допомогою смуги прокручування можна прокручувати записи бази даних. Позиція виведеного запису вказується у верхньому правому куті. Пересуватися полями форми можна за допомогою миші і клавіш Tab (униз), Shift+Tab (нагору).

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

Критерії бувають двох типів.

•Критерії обчислення – це критерії, що є результатом обчислення формули. Наприклад, діапазон критеріїв =F7>СРЗНАЧ($F$7:$F$21) виводить на екран рядки, що мають у стовпці F значення більші, ніж середнє значення величин

вкомірках F7:F21. Формула повинна повертати логічне значення ЛОЖЬ або ИСТИНА. При фільтрації будуть доступні тільки ті рядки, значення яких будуть додавати формулі значення ИСТИНА.

•Критерії порівняння – це набір умов для пошуку, який використовується для

витягу даних при запитах за прикладом. Критерій порівняння може бути послідовністю символів (константою) чи вираженням (наприклад, Ціна > 700).

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

•виділити комірку у таблиці;

•у меню Данные вибрати команду Форма;

•натиснути кнопку Критерии;

•у полях редагування ввести критерії для пошуку даних;

•для виведення на екран першого запису, що відповідає критерію, натиснути кнопку Далее;

•для виведення на екран попереднього запису, що відповідає критерію, натиснути кнопку Назад;

•для пошуку записів у списку за іншими критеріями натиснути кнопку Критерии і ввести нові критерії;

•по закінченню натиснути кнопку Закрыть.

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

Фильтр меню Данные.

142

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

Якщо в полі списку вибрати пункт Условие …, то з'явиться вікно Пользовательский автофильтр (див. рис.13). У верхньому правому списку слід вибрати один з операторів (дорівнює, більше, менше й ін.), у полі праворуч – вибрати одне зі значень. У нижньому правому списку можна вибрати інший оператор, і в полі ліворуч – значення. Коли ввімкнений перемикач И, то будуть виводитися тільки записи, що задовольняють обом умовам. При ввімкненому ИЛИ перемикачі будуть виводитися записи, що задовольняють одній з умов. Для виведення декількох записів з найбільшим чи найменшим значенням за будь-яким полем слід в схованому списку поля вибрати пункт Первые 10. У діалоговому вікні Наложение условия по списку у першому полі з лічильником необхідно вибрати кількість записів, а в полі праворуч вибрати – найбільших чи найменших.

Щоб вивести всі дані таблиці, необхідно викликати команду Отобразить все чи скасувати команду Автофильтр меню Данные, підменю Фильтр.

Рис. 13. Діалогове вікно Пользовательский автофильтр

Розширений фільтр

Команда Расширенный фильтр дозволяє фільтрувати дані з використанням діапазону критеріїв для виведення тільки записів, що задовольняють визначеним критеріям (див. рис.14). При повторній фільтрації будуть аналізуватися всі рядки, і сховані, і відкриті. Значення перемикачів і полів вікна Расширенный фильтр наступні:

143

Рис. 14. Діалогове вікно Расширенный фильтр

•фильтровать список на месте – перемикач, що ховає рядки, що не задовольняють зазначеному критерію;

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

•Исходный диапазон – поле, що визначає діапазон, що містить список, що підлягає фільтрації;

•Диапазон условий – поле, що визначає діапазон комірок на робочому листі, що містить необхідні умови;

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

•Только уникальные записи – перемикач, що виводить тільки рядки, що задовольняють критерію і не містять неповторюваних елементів. Якщо діапазон критеріїв невизначений, то в цьому випадку всі рядки списку, що містять дублікати, будуть сховані.

Для встановлення складних критеріїв необхідно:

•вставити кілька рядків у верхній частині робочого листа;

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

•при використанні критеріїв порівняння, імена критеріїв повинні бути ідентичні іменам стовпчиків, що перевіряються;

•у рядках, розташованих під рядком з іменами стовпчиків, що перевіряються, увести критерії, яким повинні відповідати комірки стовпчиків, що перевіряються;

144

• вибрати в меню Данные пункт Фильтр, потім – Расширенный фильтр, і в діалоговому вікні ввести умови фільтрації.

Після використання команд Автофильтр чи Расширенный фильтр,

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

Щоб знову вивести всі записи слід в меню Данные вибрати пункт

Фильтр, потім пункт Отобразить все.

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

Для створення діаграми необхідно:

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

•вибрати команду Диаграмма меню Вставка або натиснути кнопку ;

•у діалогових вікнах Мастера диаграмм потрібно вибрати тип, формат і інші параметри діаграми;

•для переходу до наступного кроку використовується кнопка Далее >;

•для побудови діаграми на будь-якому кроці можна натиснути кнопку Готово, тоді Мастера диаграмм самостійно закінчить побудову діаграми;

•в останньому (4-м) вікні натиснути кнопку Готово.

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

Економіко-математичні додатки Microsoft Excel

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

Серед типових економіко-математичних додатків Microsoft Excel можуть

145

бути названі:

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

•статистична обробка даних, аналіз і прогнозування;

•проведення фінансово-економічних розрахунків;

•рішення рівнянь і оптимізаційних задач.

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

Урамках Excel за допомогою команд, доступних з вікна Анализ данных

(Сервис – Анализ данных), можна провести:

•описовий статистичний аналіз (Описова статистика);

•ранжирування даних (Ранг і персентіль);

•графічний аналіз даних (Гістограма);

•прогнозування даних (Ковзна середня, Експонентне згладжування);

•регресійний аналіз (Регресія) і ін.

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

		Таблиця 1
Функція	Призначення функції і її аргументи	Тип
ЛИНЕЙН	Визначає параметри лінійного тренда для	Вбудована
	заданого масиву
	ЛИНЕЙН(знач.Y; знач. X; констанста; стат.)
ТЕНДЕНЦИЯ	Визначає майбутні значення відповідно до	Вбудована
	лінійного тренда для заданого масиву (метод
	найменших квадратів)
	ТЕНДЕНЦИЯ(знач.У; знач. X; новые знач.Х;
	константа)
ПРЕДСКАЗ	Визначає майбутнє значення функції в заданій	Вбудована
	точці на основі лінійної регресії
	ПРЕДСКАЗ(Х; знач.Y; знач. X)
НАКЛОН	Визначає коефіцієнт для незалежної перемінної	Вбудована
	в рівнянні парної регресії
	НАКЛОН(знач. Y; знач. X)
ОТРЕЗОК	Визначає відрізок, що відтинається на осі	Вбудована
	ординат лінією лінійної регресії
	ОТРЕЗОК(знач.Y; знач. X)
КВПИРСОН	Визначає квадрат коефіцієнта кореляції	Вбудована

146

	Пірсона
	КВПИРСОН(знач.Y; знач. X)
ПИРСОН	Визначає коефіцієнт кореляції Пірсона (ступінь	Вбудована
	лінійної залежності між двома множинами
	даних)
	ПИРСОН(массив 1; массив 2)
СТОШУХ	Визначає стандартну помилку майбутніх	Вбудована
	значень Y для будь-якого Х
	СТОШУХ(знач.Y; знач. X)
РОСТ	Апроксимує дані експонентної кривої	Вбудована
	РОСТ(знач.Y; знач. X; новые знач. Х;
	константа;)

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

На даний момент стандартний курс фінансових обчислень містить у собі наступні основні аспекти:

•логіка фінансових операцій (тимчасова цінність грошей, операції нарощення і дисконтування і т.д.);

•прості відсотки (операції нарощення і дисконтування, податки, інфляція, заміна платежів); складні відсотки (те ж і еквівалентність ставок, операції з валютою і т.п.);

•грошові потоки;

•аналіз ефективності інвестиційних проектів;

•оцінка фінансових активів.

ВExcel реалізовано 15 вбудованих і 37 додаткових фінансових функцій. У разі потреби застосування додаткових фінансових функцій необхідно встановити надбудову Пакет анализа.

За типом розв'язуваних задач усі фінансові функції Excel можна поділити на наступні умовні групи:

•функції для аналізу аннуїтетів і інвестиційних проектів;

•функції для аналізу цінних паперів;

•функції для розрахунку амортизаційних платежів;

•допоміжні функції.

147

Функції кожної групи мають практично однаковий набір обов'язкових і додаткових (необов'язкових) аргументів.

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

•поточної величини потоку платежів;

•майбутньої величини потоку платежів;

•величини окремого платежу;

•норми прибутковості у виді процентної ставки;

•числа періодів проведення операції (число років, місяців).

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

		Таблиця 2
Функція	Призначення функції і її аргументи		Тип
БС	Дозволяє визначити майбутню величину потоку		Вбудована
	платежів при заданих вихідних даних
	БС(процентная ставка; общеечисло периодов;
	выплата; пс; тип)
ПС	Дозволяє визначити поточну (на момент початку		Вбудована
	операції) величину аннуїтету
	ПС(процентная ставка за период; общеечисло
	периодов; выплата; бс; тип)
КПЕР	Визначає загальну кількість виплат (або строк,		Вбудована
	через який початкова сума позики досягне заданого
	значення)
	КПЕР(процентная ставка за период; выплата; пс; бс;
	тип)
БЗРАСПИС	Дозволяє визначити майбутню цінність інвестицій		Додаткова
	(чи єдиної суми), якщо процентна ставка
	змінюється в часі (за правилом складного відсотка)
	БЗРАСПИС(первичное; план)
СТАВКА	Обчислює процентну ставку (рентабельність		Вбудована
	операції)
	СТАВКА (общеечисло периодов; регулярный
	платеж (один раз в период); приведенная к текущему
	моменту стоимость; пс; бс; тип)

148

ПЛТ	Обчислює величину періодичного платежу					Вбудована
	ППЛАТ(ставка; число периодов; пс; бс; тип)
ОСНПЛТ	Обчислює ту частину платежу, що складає його					Вбудована
	основну частину
	ОСНПЛТ(процентная ставка за период; период;
	число периодов; пс; бс; тип)
ОБЩПЛАТ	Обчислює накопичені відсотки (для розрахунків					Вбудована
	плану погашення кредиту) ОБЩПЛАТ(ставка; число
	периодов; нз; нач.период; кон. период)
ОБЩДОХОД	Обчислює накопичену суму погашеного боргу (для					Вбудована
	розрахунків плану погашення кредиту).
	ОБЩДОХОД(ставка;		число	периодов;	нз;
	нач.период; кон. период)
ЧПС	Визначає поточну (сучасну), приведену до дійсного					Вбудована
	моменту часу величину потоку платежів
	ЧПС (ставка; Значение1, значение2,...)
ВСД	Обчислює внутрішню норму рентабельності					Вбудована
	ВСД (значения; предположение)
МВСД	Обчислює	модифіковану		внутрішню	норму	Вбудована
	рентабельності (з урахуванням припущення щодо
	реінвестування)
	МВСД(значения; ставка_финанс;ставка_реинвест)
ЧИСТНЗ	Визначає поточну (сучасну), приведену до дійсного					Додаткова
	моменту часу величину довільного потоку платежів,
	здійснюваних за будь-які проміжки часу, крім цього
	ця функція вже враховує обсяг первісних інвестицій
	ЧИСТНЗ(ставка; значения; даты)
	Рішення рівнянь і оптимізаційних задач

Широке застосування на практиці знаходять функції і режими Excel, призначені для пошуку рішення рівнянь і оптимізаційних задач.

Зручним і простим для розуміння інструментом рішення рівнянь є режим

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

Процес рішення за допомогою даного методу розпадається на два етапи:

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

2.Введення адрес, що впливають і цільових комірок у діалогове вікно Подбор параметра й одержання відповіді (чи повідомлення про її відсутність/неможливість знайти).

149