БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.

MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ

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

Іменування комірок. Цю ж задачу можна розв’язати іншим способом. Замістьабсолютноїадресиможнавикористативформуліім’якомірки. Іменувати комірку доцільно, якщо вона містить дані, які впливають на значну кількість комірок ЕТ і до того ж ці дані часто змінюються (наприклад, курс грошової одиниці). Щоб задати ім’я комірки, слід її активізувати, клацнути в вікні адреси (воно знаходиться ліворуч в рядку формул), замінити адресу на текст, який стане ім’ям комірки, натиснути Enter. Ім’я комірки можна вживати в формулах замість її адреси.

Так, якщо в останньому прикладі, надати комірці В11 ім’я «Разом», то формула для комірки С3 матиме вигляд:

= В3 / Разом * 100.

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

2.9.Функції

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

50

Частина II ЕЛЕКТРОННА ТАБЛИЦЯ MICROSOFT WORD

функції. Якщо вираз, який утворює аргумент функції, містить адресу комірки, доцільно вводити адресу клацанням мишею, коли її курсор наведенонакомірку, аніжнабиративручнузклавіатури. Аргументифункції в свою чергу також можуть мати в своєму складі ту чи іншу функцію, вибрати яку можна також за допомогою «Мастера функций». Якщо функція має кілька аргументів, вони відокремлюються крапкою з комою.

Функції, які використовуються найчастіше, знаходяться в категорії «10 недавно использовавшихся».

Математичні функції. До математичних функцій належать:

•ABS — повертає модуль (абсолютну величину числа);

•SIN, COS, TAN — повертає значення тригонометричних функцій — синус, косинус, тангенс; аргумент задається в радіанах;

•ГРАДУСЫ, РАДИАНЫ — відповідно перетворюють радіани на градуси та градуси на радіани;

•ПИ — повертає число π (функція без аргументів);

•ASIN, ACOS, ATAN — повертає значення обернених тригонометричних функцій — арксинус, арккосинус, арктангенс;

•EXP — повертає експоненту заданого числа;

•LN, LOG — відповідно повертає натуральний логарифм числа та логарифм числа за довільною основою;

•КОРЕНЬ — повертає квадратний корінь числа;

•СТЕПЕНЬ — повертає результат піднесення числа до степеня;

•ФАКТР — повертає факторіал числа;

•ЧИСЛКОМБ — повертає кількість сполучень; функція має два аргументи — загальну кількість об’єктів і кількість вибраних об’єктів.

Приклади:

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

Довжина вектора обчислюється за формулою:

d = √(x1 – x2)2 – (y1 – y2)2,

де (x1, y1), (x2, y2) — відповідно координати початку і кінця вектора.

51

БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.

MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ

Така задача в Excel розв’язується за допомогою формули:

= КОРЕНЬ((А1 – В1) ^ 2 + (А2 – В2) ^ 2)

2.Контролер перевіряє партію з 50 виробів шляхом відбору навмання 10 виробів і визначення їх якості. Якщо серед вибраних контролером виробів немає жодного бракованого, то вся партія виробів приймається. Яка ймовірність того, що партія виробів, яка містить 5 бракованих виробів, буде прийнята контролером?

Така задача розв’язується за формулою:

C10

p = C4510 , 50

де Cnm — кількість можливих варіантів вибору m елементів з n елементів, які є в наявності.

Така задача в Excel розв’язується за допомогою формули: = ЧИСЛКОМБ(45;10) / ЧИСЛКОМБ(50;10)

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

•СРЗНАЧ — повертає середнє арифметичне значення чисел, що складають діапазон даних; належить до категорії статистичних функцій;

•СУММКВ — повертає суму квадратів аргументів;

•СУММПРОИЗВ — має два або більше аргументів, які є діапазонами даних; повертає суму добутків відповідних елементів діапазонів, що складають аргументи функції;

•СЧЕТ — повертає кількість чисел в діапазоні даних та інші.

Длятакихфункційаргументоммаютьстатиадресидвохкрайніхко-

мірок діапазону, розділені двокрапкою. Діапазон даних, який має стати аргументом функції, зазначається увікні «Число 1» «Мастера функций». Якщо діапазон даних не утворює нерозривний прямокутник в ЕТ, то на кожну з частин діапазону дається посилання у вікнах «Число 1», «Число 2» тощо «Мастера функций».

52

Частина II ЕЛЕКТРОННА ТАБЛИЦЯ MICROSOFT WORD

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

Приклад

ВкоміркахА1:А5 містятьсяданіпроврожайністьрайонуза5 останніх років. Визначити середню врожайність району за даний період.

Задача розв’язується за формулою: = СРЗНАЧ (А1:А5).

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

Приклад

Значення змінної х міститься в комірці А1. Визначити

Задача розв’язується за формулою: sin√e2x+1

= SIN(КОРЕНЬ(ЕХР(2*А1 + 1)))

Функціяокруглення. Звернемосьдотаблиці«Дохідвідреалізації». Неважковпевнитись, щовпершомужрядкуобчисленнянекоректні: якщо вартість реалізації одиниці товару складає 3,19 грн., а продано 155 одиниць цього товару, то загальна вартість має складати 494,45 грн., а не 493,68 грн., як це зазначено в таблиці. Це сталося внаслідок того, що вартістьреалізаціїбулоутвореношляхомобчислень— закупівельнавартість збільшуваласьнавеличинуторговельноїнацінки. Томуфактичновартість реалізаціїстановить3,185 грн., азагальнавартість — 493,675 грн. Цівартостінеокруглювались, донихлишебулозастосованочисельнийформат даних з двома цифрами після десяткового роздільника. Але, як було відзначено раніше, форматування даних не змінює самих даних.

Для того, щоб обчислення за формулами проводились коректно, слід використати функцію округлення — ОКРУГЛ. Функцію ОКРУГЛ

слід обов’язково використовувати у всіх фінансових розрахунках,

53

Частина II ЕЛЕКТРОННА ТАБЛИЦЯ MICROSOFT WORD

Щодо таблиці «Дохід від реалізації» її слід корегувати, замінивши формули для рядка G (наприклад, для комірки G3):

= ОКРУГЛ(C3 * (1 + F3 / 100); 2)

Таблиця «Дохід від реалізації» набуває вигляду (табл. 2.4)

Функції роботи з масивами даних. Дані можуть об’єднуватись в масиви. За суттю масив даних являє собою діапазон комірок ЕТ, але у функції до нього звертаються як до самостійного об’єкта. Такими функціями, наприклад, є:

•МОБР — повертає обернену матрицю;

•МОПРЕД — обчислює визначник матриці;

•МУМНОЖ — перемножує матриці;

•ТРАНСП — повертає транспоновану матрицю.

Єдві особливості при використанні таких функцій. По-перше, перед використанням функції треба виділити в ЕТ діапазон саме такого розміру, який відповідає результату обчислень. По-друге, після того, як будуть визначені всі аргументи функції, вихід з «Мастера функций» здійснюється шляхом натискання комбінації клавіш Ctrl + Shift + Enter, а не кнопки «ОК».

Приклад

В таблиці наведено дані про виконання міжгалузевого балансу за звітний період:

Таблиця 2.5

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

55

БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.

MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ

Згідно з моделлю Леонтьєва багатогалузевої економіки, розв’язок можна одержати за формулою:

X = (E – A)–1Y,

де Х — вектор валового випуску; Е — одинична матриця; А — матриця прямих витрат; Y — вектор кінцевого продукту.

Вважаємо, що діапазон А1:В2 містить матрицю споживання; діапазон D1:D2 — вектор кінцевого продукту з урахуванням того, що, відповідно до умов, кінцеве споживання енергетичної галузі збільшиться вдвічі; діапазон Н1:І1 — вектор валового випуску.

Розв’язання задачі можна подати у вигляді ЕТ (табл. 2.6). Коефіцієнти матриці прямих витрат А (діапазон D4:E5) визнача-

ються як відношення xij / хj , де xij — обсяг продукції і-ої галузі, який споживає j-а галузь в процесі виробництва; хj — валовий обсяг продукції і-ої галузі (i, j = 1, 2). Матриця А формується в комірках D4:E5. Для комірки D4 формулу для обчислень доцільно подати у вигляді:

= А1 / H$1

Тоді всі інші елементи матриці А можна отримати шляхом копіювання цієї формули методом автозаповнення.

Таблиця 2.6

56

Частина II ЕЛЕКТРОННА ТАБЛИЦЯ MICROSOFT WORD

Одинична матриця Е зберігається в діапазоні Н4:Е5.

МатрицяЕ— АзберігаєтьсявдіапазоніD7:E8. ДлякоміркиD7 формула для обчислень має вигляд:

= H4 – D4

Всі інші елементи матриці Е — А можна отримати шляхом копіювання цієї формули методом автозаповнення.

Длявизначенняматриціповнихвитрат(E — A)-1 використовується функція МОБР обчислення оберненої матриці. Результат розміщено в ді-

апазоні D10:E11:

= МОБР (D7:E8)

Для визначення вектора валового випуску Х використовується функція МУМНОЖ, аргументами якої ємасив D10:E11 (матриця повних витрат) і масив D2:D3 (вектор кінцевого продукту):

= МУМНОЖ (D10:E11; D2:D3)

Результати — нові обсяги валового випуску, що містяться в діапазоні Н10:Н11.

Логічні функції. Якщо спосіб обчислення деякої величини залежить відвиконання абоневиконання певноїумови, доцільнозастосувати вформулі функцію ЕСЛИ, яка входить до категорії логічних функцій. Функція ЕСЛИ має три аргументи: логічний вираз, який може набувати двох значень — «Істинність» та «Хибність», а також два вирази, за якими проводяться обчислення у випадку, коли логічний вираз є відповідно істинним і хибним. Не слід звертати увагу на істинність умови під час створення функції ЕСЛИ, тому що за інших значень комірок ЕТ істинна умова може стати хибною (або навпаки) і буде вибрано інший варіант обчислень. Логічний вираз найчастіше складається з двох арифметичних виразів, поєднаниходнієюзопераційпорівняння: = (дорівнює), > (більшеза), < (менше за), >= (більше або дорівнює), <= (менше або дорівнює), <> (нерівність).

Приклад

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

57

БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.

MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ

Таблиця 2.7

Задача розв’язується за допомогою табл. 2.7.

Дані про нараховані суми (стовпець С) відомі заздалегідь. Дані, наведені в стовпці D, обчислюються за допомогою функції ЕСЛИ. Так, для комірки D3 формула для обчислень набуває вигляду:

=ОКРУГЛ(ЕСЛИ(С3 <= 150; С3*0,01; С3*0,02); 2)

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

Приклад

Припустимо, що податок з доходів працівників підприємства обчислюється наступним чином. Він складає 13 % від нарахованої суми, але якщо нарахована сума не перевищує 530 грн., встановлюєтьсяподатковапільгаврозмірі61,50 грн. Дотогож, податокздоходів не поширюється на суми відрахувань до пенсійного фонду, фонду страхування від безробіття і фонду соціального страхування, які складають 2 % від нарахованої суми, якщо остання не перевищує 150 грн. і 3 % всупереч.

Таблиця «Відрахування з заробітної плати» набуватиме такого вигляду:

58

Частина II ЕЛЕКТРОННА ТАБЛИЦЯ MICROSOFT WORD

Таблиця 2.8

Наведемо формулу для обчислення податку з доходів, наприклад, для комірки Е3:

=ОКРУГЛ(ЕСЛИ(С3 <= 150; (С3*0,98–61,50) * 0,13; ЕСЛИ(С3 <= 530; (С3*0,97–61,50) * 0,13; С3*0,97*0,13)); 2)

Якщо логічний вираз у функції ЕСЛИ є складним, то для об’єднання простих логічних виразів у складний можна застосовувати й інші логічні функції: И, ИЛИ, НЕ.

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

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

•величина постійної періодичної виплати R (плт) — член ренти;

•загальна кількість періодів сплат n (кпер);

•номер окремого періоду сплат N (период);

•момент сплати t (тип) — набуває значення 0, якщо сплату здійснено в кінці періоду і значення 1, якщо сплату здійснено на початку

59

БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.

MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ

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

•ставка складних відсотків за період і (ставка).

До того ж існують узагальнені показники фінансових рент:

•нарощена сума або майбутня вартість S (бс) — сума всіх членів ренти з нарахованими на кінець терміну ренти відсотками;

•приведена або сучасна вартість А (пс) — сума всіх членів ренти, зменшена (дисконтована) на величину відсоткової ставки, на поча-

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

Зазначені параметри пов’язані між собою рівнянням фінансової рівноваги:

Для визначення параметрів фінансових рент в Excel існують такі функції:

•БС(ставка; кпер; плт; пс; тип) — повертає значення нарощеної суми;

•ПС(ставка; кпер; плт; бс; тип) — повертає значення сучасної вартості ренти;

•ПЛТ(ставка; кпер; пс; бс; тип) — обчислює розмір постійної періодичної сплати ренти за фіксованою відсотковою ставкою;

•КПЕР(ставка; плт; пс; бс; тип) — повертає кількість періодів, необхідних для накопичення заданої суми за фіксованою відсотковою ставкою;

•СТАВКА(кпер; плт; пс; бс; тип; предположение) — обчислює відсоткову ставку за один період ренти, необхідну для отримання певної суми протягом заданого терміну шляхом внесення постій-

них внесків.

Важливим є те, що для всіх параметрів фінансових функцій сплачені або вкладені суми подаються від’ємними числами, а отримані суми (доходи, дивіденди тощо) — додатними числами.

60

Частина II ЕЛЕКТРОННА ТАБЛИЦЯ MICROSOFT WORD

Приклади:

1. На початку кожного року протягом п’яти років на банківський рахунок перераховують 20 000 грн. при постійній ставці 12 % річних. Яка сума буде на банківському рахунку наприкінці терміну ренти?

Розв’язок:

=БС(0,12; 5; –20 000;; 1)

Результат дорівнює 142 303,78 грн.

2.Пропонується інвестувати в проект 50 млн грн., щоб при постійній ставці 8 % річних: а) протягом семи років одержувати річний дохід розміром 10 млн грн.; б) протягом семи років одержувати річний дохід розміром 4 млн грн., але наприкінці терміну отримати назад весь початковий внесок. Який проект є більш прибутковий?

Розв’язки для задач (а) і (б) становлять відповідно:

=ПС(0,08; 7; 10;; 0)

=ПС(0,08; 7; 4; 50; 0)

Результати відповідно дорівнюють –52,06 млн грн. та –50,00 млн грн. Отже, в першому випадку прибуток складає 2,06 млн грн. В другому випадку прибутку немає. Таким чином, перший проект є вигіднішим.

3. Підприємство отримало позику в розмірі 150 000 грн. і має намір розраховуватись рівними щомісячними виплатами протягом трьох років. Яким має бути розмір щомісячної виплати при ставці 10 % річних?

Розв’язок:

= ПЛТ(0,1/12; 36; 150000;; 0)

Результат дорівнює -4840,08 грн. Тут слід зважити на те, що період виплат становить один місяць, тому річна ставка рівномірно ділиться на 12 місяців.

4. Фізична особа отримала кредит розміром 80 000 грн. для придбання квартири під 13 % річних з постійною щомісячною виплатою 1000 грн. Скільки років потрібно для повернення кредиту?

Розв’язок:

= КПЕР(0,13/12; –1000; 80000;; 0) / 12

Результат дорівнює 15,6 років.

61

БИКОВ І. Ю., ЖИРНОВ М. В., ХУДЯКОВА І. М.

MICROSOFT OFFICE В ЗАДАЧАХ ЕКОНОМІКИ ТА УПРАВЛІННЯ

5. Підприємству пропонують інвестувати 6 млн. грн. для того, щоб щорічно протягом 8 років отримувати дохід розміром 1 млн. грн. В той же час банк пропонує підприємству 8 % річних. Чи вигідна така інвестиція?

Розв’язок:

= СТАВКА(8; 1; –6;; 0)

Результат дорівнює 6,88 %. Оскільки банк пропонує 8 % річних, то пропозиція щодо інвестування є менш привабливою.

Розглянемо функції визначення амортизаційних відрахувань. Параметрами таких функцій є:

•початкова вартість (нач_стоимость);

•залишкова або ліквідна вартість (ост_стоимость);

•період експлуатації (время_эксплуатации);

•номер періоду для обчислення амортизації (период).

Основними функціями визначення амортизаційних відрахувань в

Excel є:

•АПЛ (нач_стоимость; ост_стоимость; время_эксплуатации);

•АСЧ (нач_стоимость; ост_стоимость; время_эксплуатации; период);

•ДДОБ (нач_стоимость; ост_стоимость; время_эксплуатации; период; коэффициент).

Приклад

Верстат вартістю 8000 грн. має термін експлуатації 6 років, після чого його ліквідна вартість становить 1000 грн. Визначити щорічну амортизацію: а) заметодомрівномірноїамортизації; б) залінійнимметодом; в) за методом k-кратного обліку амортизації.

Розв’язок:

а) За методом рівномірної амортизації остання обчислюється за формулою:

= АПЛ(8000; 1000; 6)

Результат дорівнює 1166,67 грн.

62

Частина II ЕЛЕКТРОННА ТАБЛИЦЯ MICROSOFT WORD

б) За лінійним методом амортизація обчислюється за формулами:

•для першого року: = АСЧ(8000; 1000; 6; 1)

•для другого року: = АСЧ(8000; 1000; 6; 2)

тощо.

Результати відповідно дорівнюють 2000,00 грн., 1666,67 грн., 1333,33 грн., 1000,00 грн., 666,67 грн.

в) В методі k-кратного обліку амортизації вважаємо, що k = 2. Тоді за цим методом амортизація обчислюється за формулами:

•для першого року:

=ДДОБ(8000; 1000; 6; 1; 2)

•для другого року:

=ДДОБ(8000; 1000; 6; 2; 2)

тощо.

Результати відповідно дорівнюють 2666,67 грн., 1777,78 грн., 1185,19 грн., 790,12 грн., 526,75 грн.

Функції дати. Інколи буває корисним вживати функції, які стосуються роботи з датами. Такими функціями, наприклад, є:

•ГОД (дата) — добуває рік з заданої дати;

•МЕСЯЦ (дата) — добуває номер місяця з заданої дати; результат — число від 1 до 12;

•ДЕНЬ (дата) — добуває номер дня з заданої дати; результат — число від 1 до 31;

•ДЕНЬНЕД (дата; тип) — добуває номер дня тижня з заданої дати; результат — число від 1 (неділя) до 7 (субота) для типу 1, число від 1 (понеділок) до 7 (неділя) для типу 2, число від 0 (понеділок) до 6 (неділя) для типу 3;

•ДАТА (год; месяц; день) — утворює дату з трьох чисел;

•СЕГОДНЯ() — повертає сьогоднішню дату.

63