Фінансові функції для розрахунків по цінним паперам у середовищі електронної таблиці M.Excel
Фінансові функції для розрахунків по цінним паперам у середовищі електронної таблиці M.Excel
При запуску програми M.Excel користувачеві звичайно пропонується для заповнення даними новий порожній документ в оперативній пам'яті комп'ютера зі стандартним ім'ям Книга1(Book1), що складається з 16 робочих аркушів, розграфлених у вигляді таблиці на 16384 рядки й 256 стовпців, стандартна ширина кожної клітки дорівнює 9 символам. Екран комп'ютера на початку стандартного сеансу роботи програми виглядає як на мал. 1.
Мал. 1. Вид вікна (програми (обробки) електронних таблиць Excel при запуску.
Вихідне положення покажчика поточної клітки усередині видимої на екрані частини таблиці – перетинання першого рядка й першого стовпця, це клітка з адресою A1 в однойменному стилі посилань.
Альтернативний стиль посилань на чарунки робочого аркуша, коли стовпці також нумеруються, а номер рядка вказується в першу чергу, можна активізувати командою Сервис Параметры, вибравши в її діалоговому вікні на вкладці Общие в групіСтиль R1C1. Тоді перша клітка (чарунка) робочого аркушапосилань позицію перемикача так і буде йменуватися R1C1, від англійського Row1Column1 (ряд перший, колонка перша).
Рух покажчикапо табличному полю робочого аркуша необхідно для вибору заповнюваних, що редагують, форматуючих або чарунки, що переглядаються користувачем. Обрана покажчиком чарунка є тривіальним виділеним діапазоном. Виділення діапазону чарунок, з якими необхідно зробити ті або інші дії, передує виконанню більшості команд і завдань.
Для виділення прямокутного діапазону чарунок, зробіть поточною кліткою один з його майбутніх кутів, помістите покажчик миші усередину рамки виділення клітки (він повинен мати при цьому форму товстого білого плюса), і втримуючи натиснутої ліву кнопку миші, переміщайте її, при цьому виділюваний блок кліток буде зафарбовуватися кольорами, контрастним до основного тла таблиці. При звільненні натиснутої кнопки миші виділення блоку закінчується. Для скасування поточного виділення досить змінити положення покажчика в таблиці.
Безперервне переміщення покажчика в будь-якому напрямку ініціюється мишею (вибір позиції фіксується щигликом), а дискретне - стандартними керуючими клавішами.
Основні клавіші керування положенням табличного покажчика поточної клітки
Напрямок Крок руху
на одну клітку на один екран до кінця блоку даних або границі робочого аркуша
униз ¯ PageDown End,потім ¯
нагору Ý PageUp End, потім Ý
праворуч ® утримуючи Ctrl, нажати ® End, потім ®
ліворуч ¬ утримуючи Ctrl, нажати ¬ End, потім ¬
Крім того, для прямого стрибка в клітку з явно заданою адресою використається клавіша F5, а клавіша Home активізує перший чарунок того рядка таблиці, де був покажчик до її натискання.
Завдання
Скільки кліток міститься в таблиці на одному робочому аркуші стандартної книги Excel?
1) Переведіть покажчик до кінця робочого аркуша вниз і праворуч;
2) Для визначення номера останнього стовпця активізуйте стиль посилань R1C1;
3) Перемножте (розрахункову формулу можна розмістити в будь-якій вільній клітці) номер останнього рядка на номер останнього стовпця.
Перехід на інші аркуші табличної книги досягається щигликом миші по ярличках, або парними комбінаціями службових клавіш: Ctrl+PageUp й/або Ctrl+PageDown. Перейменування робочого аркуша ініціюється подвійним щигликом лівої кнопки миші по ярличку, після чого можна вводити нове ім'я.
Завдання
Викличте інтерактивну довідкову систему (пункт меню ? або клавіша F1), уведіть для пошуку в Предметному покажчику ключову фразу "выделение ячеек", і, прочитавши отримані інструкції, попрактикуйтесь у виділенні невеликих прямокутних областей.
Для запису нового документа у вигляді файлу електронної таблиці на диск використається команда Файл Сохранить как ., у діалозі з якої користувач вибирає потрібну папку на диску, а також ім'я й тип створюваного файлу. Для таблиць Microsoft Excel за умовчуванням передбачене розширення *.XLS
Приклад. Припустимо, що ми хочемо організувати облік витрат домашнього господарства на придбання фруктів й овочів.
Мал. 2. Заповнення першої клітки таблиці даними приклада 1 у режимі уведення із клавіатури.
Наприклад, було куплено 850 м апельсинів за ціною 12 руб. 56 коп. за 1 кг . Запишемо в перший рядок таблиці робочого аркуша цю інформацію. У стовпець A будемо вносити назви, у стовпець B - вага покупки, а в стовпець C - ціну .
Дані вводяться на робочий аркуш електронної таблиці порціями, звичайно послідовно вводять інформацію в кілька сусідніх осередків, по черзі заповнюючи кожну з них. При натисканні користувачем алфавітно-цифрової клавіші поточна клітка таблиці автоматично переходить у режим ввода, готуючись прийняти дані, розпізнати їхній тип, зберігати отримане значення й виводити його в заданому форматі.
Для уведення даних необхідно:
•зробити заповнювану клітку поточної (перевести туди рамку покажчика);•набрати послідовність символів на клавіатурі, при цьому вводить строка, що, в Excel відображається й у заповнюваній клітці, і над полем таблиці в рядку формул;
•закінчити уведення натисканням клавіші уведення ¿ Enter, або щигликом миші по її екранній кнопці, що заміняє, із зображенням зеленої галочки (символ a) , розташованої в режимі уведення над полем робочого аркуша в лівій частині рядка уведення.
У стані уведення даних, відтвореному на мал. 2, потрібно додати до набираного слова, ще одну букву, щоб назва фруктів стоялася в множині, і можна закінчувати уведення.
Для виправлення допущених при наборі помилок після виходу з режиму уведення можна
• повторити уведення даних у ту ж клітку;
• відредагувати поточну клітку, двічі клацнувши по ній мишею, або нажавши клавішу F2.
Скасувати незакінчене уведення можна клавішеюEsc, або екранною кнопкою із червоним хрестиком (символr) у рядку уведення.
Якщо дані набрані правильно, але уведені помилково не в ту клітку, їх можна перенести, наприклад, методом перетаскування: підвівши знизу покажчик миші до рамки виділеного осередку з даними (він повинен прийняти форму товстої білої стрілки), нажати ліву кнопку й, утримуючи її, переміщати маніпулятор, орієнтуючись на пунктирну рамку положення клітки, що приймає перенос.
Для повного очищення поточної клітки від раніше уведеної інформації натискайте клавішу Delete.
Після успішного заповнення боковика таблиці першим написом, що пояснює, можна переходити до уведення вихідних числових даних про вагу й ціну покупок. Подивимося, як це вийшло в починаючого користувача, екран якого відтворюється на мал. 3.
У Росії прийнято відокремлювати цілу частину дробового числа знаком ком, а в США – десятковою крапкою. Залежно від стану параметрів настроювання версії Windows на національний стандарт країни використання комп'ютера, в Excel коректним роздільником у складі числа може виявитися або крапка, або кома, але вони не можуть вільно заміняти один одного й бути припустимими символами-роздільниками одночасно.
Мал. 3. Перетворення числової інформації в календарну в результаті помилки кодування.
Ви точно не помилитеся в тім, який же символ – крапку або кому можна використати в десяткових дробах, якщо будете набирати числа не верхньому ряді основний частина клавіатури, а на додаткової, у режимі NumLock. Крім / * - + 9 8 7 6 5 4 3 2 1 0 і клавіші уведення, там є клавіша із зображенням десяткової крапки (сполучена з Del). При роботі в Excel вона завжди вводить саме той символ, що і варто використати як роздільник при записі десяткового числа.
У нашому прикладі при уведенні ваги в кілограмах користувачем правильно була поставлена кома, а уведення в клітку C1 як значення ціни в рублях числа 12.56 (через крапку) приводить до того, що, завдяки дії настроювання комп'ютера на крапку як роздільник компонентів дати, ці п'ять символів інтерпретуються програмою як початок 12-го місяця 56-го року поточного століття (спочатку вживає спроба відшукати в календарі 56-й місяць). У рядку формул це значення й показане - 01.12.1956, що відповідає 1 грудня, причому дата тут виводиться скорочено, без вказівки номера року, як 1.12 у клітці C1.
В залежності від поточної установки короткого формату дати, користувач може побачити й інші варіанти, наприклад запис без номера дня - груд.56. Якщо спробувати поліпшити положення, перетворивши дату, що з'явилася в результаті неправильного уведення десяткового числа, до грошового формату, наприклад, нажавши відповідну кнопку на панелі інструментів (див. мал. 6), то результатом таких зусиль буде 20 790р., що як ціна кілограма апельсинів замість вихідного значення 12,56 р. виглядає трохи зненацька.
Звідки ж з'явилося це загадкове п'ятизначне число? Справа в тому, що в Excel дати кодуються шляхом перерахування днів з початку століття, у цій системі день номер 1 – це 1 січня 1900 року. Сериальное число 20790 позначає точну кількість днів, що пройшли між початком XX сторіччя й 1 грудня 1956 р. Рецептом виправлення показаної на мал. 6 помилки є тільки повторне уведення числа 12,56 у ту ж саму клітку C1, але тепер уже правильно – через кому. Оскільки в процесі експерименту цій клітці був призначений грошовий формат з округленням до цілих, то на екрані з'явиться ціна 13р. без копійок.
Мал. 4. Збільшення точності округлення при виводі значення десяткового дробу на екран.
Зверніть увагу, що в рядку формул (див. мал. 4) виводиться щире значення уведеної в поточну клітку числової константи 12,56. Для його виводу в грошовому форматі з точністю до копійок потрібно сполучити білу стрілку покажчика миші з екранною кнопкою Увеличить разрядность панелі інструментів Форматування й пару раз "нажати на неї", клацаючи мишею. Тоді вийде 12,56р.Залежно від состава вводить інформації, що, і особливо від її першого символу (префікса) даних електронні таблиці автоматично відносять їх після уведення до одному із двох типів: константа або формула. Табличні формули починаються із префікса й можуть складатися з:
• числових констант;
• знаків дій і дужок;
• адрес й/або імен табличних діапазонів й окремих кліток;
• імен убудованих функцій.
Префіксами формули, з яких обов'язково починається її уведення, можуть бути символи =, + й -
При уведенні послідовності символів, що ні з якого префікса формули не починається, дані інтерпретується програмою як константа – число, дата або текст.
• Числове вираження може складатися тільки із цифр, знаків "плюс", "мінус"круглих і фігурних дужокі деяких інших знаків, передбачених дробовим, процентним, експонентним, грошовим і фінансовим форматами.
• Дати зберігаються як цілі числа, хоча формат їхнього запису більше схожий на текст.
• Текстом є будь-які дані, які програмі не вдається розпізнати як число або формулу, у тому числі й дані, які мається на увазі як числа й формули, при уведенні яких були допущені помилки.
Текстпри уведенні вирівнюється по лівому краї чаруноку, а дати, числа й формули - по правому. Якщо формат виводу значення числового вираження (константи або результату формули) не міститься на екрані завширшки клітки, то замість нього для залучення уваги користувача виводиться "заборчик" знаків нумерації #########. Якщо ж завширшки стовпця не укладається текст, а чарунок праворуч по рядку вже зайнятий, то закінчення довгого тексту усікається.
Дії оператора по упорядкуванню виду таблиці після уведення даних:
1. Величина ширина першого стовпця.
1. Вставлено новий перший рядок.
2. Текст у рядку 1 вирівняний по центрі.
3. Текст в A10 вирівняний по правому краї.
4. Збільшена до тисячних) розрядність запису ваги апельсинів у клітці B2.
5. Формат кліток B2 й C2 визнаний зразковим і скопійований униз.
Мал. 5. Заповнення таблиці вихідними даними і їхнє елементарне форматування.
Після заповнення блоку таблиці інформацією настав час переходити до розрахунків. Знайдемо, наприклад загальну суму витрат. Організувати її обчислення користувач Excel може декількома способами.
Самий традиційний підхід - знайти витрати на окремі продукти (шляхом перемножування ваги кожного продукту на ціну), а потім скласти їх разом.
Щоб довідатися, скільки потрібно заплатити за 850 м апельсинів при ціні 12,56 р. /кг, можна просто помістити в осередок D2 формулу добутку числових констант Цей спосіб відповідає використанню табличної клітки як аналог калькулятора арифметичних виражень, тільки клавіша = в Excel при наборі розрахункового вираження натискається першої, а не останньої.
Сучасні калькулятори, а тим більше персональні комп'ютери, зберігають у своїй пам'яті не тільки дані, але й алгоритми рішення завдань, їх можна програмувати. Клітка робочого аркуша електронної таблиці відповідає чарунку машинної пам'яті, призначену для зберігання змінюваної в процесі роботи інформації, а імена кліток схожі на ідентифікатори змінних у мові програмування й мають те ж призначення. Щоб запрограмувати деяке просте обчислення, потрібно закодувати його розрахункову формулу адресами кліток з вихідними даними, з'єднаними знаками дій у коректне математичне вираження й увести його в потрібне місце таблиці, наприклад формулу =B2*C2 помістити в чарунок D2. Щоб не промахнутися з координатами кліток і не наробити при буквено-цифровому наборі помилок, рекомендується вставляти у формули табличні адреси, просто клацаючи мишею по клітках з операндами (див. мал. 9). При цьому клавішу = , дужки й знаки дій зручно натискати вільної від миші рукою.
Мал. 6. Уведення в таблицю формули із вказівкою посилань щигликомпо клітках без натискання алфавітних і цифрових клавіш для набору адрес співмножників.
При використанні в записі формул табличних адрес співмножників замість їх фіксованих числових значень результат, звичайно, не зміниться, але при необхідності повторити розрахунок витрат, наприклад, на покупку тієї ж ваги апельсинів при зміні ціни на них, не буде потрібно ще раз повністю набирати обидва співмножники й знак дії, а досить тільки переправити одну ціну в чарунок C2, і таблиця автоматично відреагує новими значеннями всіх залежних чарунок.
Обчисливши витрати на апельсини - 10,68р., можна переходити до програмування аналогічних дій у наступному рядку й далі вниз до кінця списку продуктів. Тільки що уведена формула відбиває суть розрахункової моделі, у кожному рядку однакової - число з колонки B помножити на число з колонки C, а значення їхнього добутку вивести на екран у колонку D.
а) виділення блоку кліток, що приймають копію
б) звільнення лівої кнопки миші
Мал. 7. Процес копіювання формули на блок кліток униз рухом миші.Для кодування повторюваних обчислень в електронних таблицях застосовується технологія копіювання кліток з формулами. Джерелом формули в нашому прикладі буде клітка D2, а копіювати її потрібно вниз по стовпці на блок осередків D3:D9. Після того як вихідна формула правильно записана й уведена в клітку-джерело, що вже є поточної, необхідно сполучити покажчик миші з кутовим маніпулятором рамки виділення. Покажчик, що був до того товстим білим плюсом, стане більше тонким чорним хрестом.
Утримуючи натиснутої ліву кнопку при цьому положенні покажчика, переміщайте мишу вниз, і границі блоку приймаючу копію будуть поступово виділятися на екрані пунктиром (див. мал. 7а). Коли Ви звільните ліву кнопку миші, формула перемножування ціни даного товару на кількість пошириться із блока-джерела на весь виділений діапазон чарунок з адаптацією використовуваних табличних посилань у стилі "паралельного переносу" (див. мал. 7б).
Не квапитеся скасовувати виділення блоку кліток, що збереглося після закінчення копіювання, а натисніть у цьому стані екранну кнопку Автосуммирование панелі інструментів Стандартна (на кнопці зображений математичний символ операції підсумовування – більша грецька буква S , читається "сигма"), і під виділеним блоком у результаті автоматичної підстановки формули =СУМ(D2:D9) в чарунку D10 виникне значення самої популярної убудованої табличної функції, відзначеної персональної кнопки на стандартній панелі інструментів. При копіюванні адреси кліток у складі формули адаптуються до напрямку копіювання – у нашому випадку номера рядків будуть рости із кроком 1 від рядка до рядка.
Копіювання формули на блок кліток – не єдиний спосіб кодування повторюваних обчислень засобами сучасних електронних таблиць. Обробку набору однотипних елементів загальною операцією в мовах програмування оформляють оператором циклу. Лічильник циклу при цьому перебирає підряд всі номери елементівмасиву. Така структура даних є в Excel, але клітки збираються разом у масив не явно по номерах, а виділенням потрібного блоку мишею. Так, множення ваги на ціну можна виразити формулою масиву.
Мал. 8. Уведення формули масиву у виділений діапазон.
Для її уведення потрібно виділити в таблиці заповнюваний блок кліток D2:D9, набрати розрахункову формулу =B2:B9*C2:C9, і закінчити її уведення (не в одну клітку, а у весь виділений блок) уже не звичним натисканням клавіші уведення, а комбінацією трьох сусідніх клавіш Ctrl-Shift-Enter.
Така формула заповнює відразу весь виділений блок, і необхідність у копіюванні відпадає. Зверніть увагу, що в рядку уведення формула масиву, який підлегла поточна клітка, відображається у фігурних дужках {=B2:B9*C2:C9}.
Використання в таблиці формул масиву виправдує трудомісткість їхнього створення, якщо необхідно цілком підкорити блок кліток впливу єдиної формули й виключити можливість ізольованого виправлення вмісту окремих кліток усередині масиву (ступінь захисту).
Є ще один спосіб розрахунку витрат, що не вимагає до явного обчислення в таблиці окремих часток доданків. З погляду економіст-математика, витрати на придбання набору товарів обчислюються як скалярний добуток векторів кількості товарів і цін.
Нехай, – кількість продуктів (у нашому прикладі їх сім);
– номер продукту в упорядкованому списку, міняється від 1 до ;
– придбана кількість -го продукту;
– ціна, по якій здобувається -ый продукт. Тоді витрати на придбання вмісту даного фруктово-овочевого кошика є сума покоординатных добутків елементів векторів й .
де, – вектор, в елементах якого записана вага продуктів, що купують;
– набір відповідних цін.
Настільки широко розповсюджена в побуті облікова операція вже закодована в Excel убудованої математичної функцій. В оригінальній (англо-американської) версії пакета вона називається =SUMPROD, а в русифікованій має ідентифікатор =СУММПРОИЗВ, що з точки зору введиної довжини рядка та ймовірності помилки при посимвольному наборі менш вдало.
Для запобігання помилок при уведенні довгих формул рекомендується вибирати назва потрібної функції з готового списку імен, що розкривається при звертанні до процедури Майстер функцій. Вона активізується кнопкою зі значком (див. мал. 12): на панелі інструментів Стандартна.
Спробуємо звернутися до убудованої функції =СУММПРОИЗВ для приміщення підсумкової величини витрат у клітку D10. Для цього потрібно виділити цю клітку D10 перекладом у неї (рамки) табличного покажчика, а потім клацнути по кнопці Майстер функцій, або використати команду меню Вставка Функція .
На кроці 1 у лівому вікні вибирається Категория функций – Математические v, а потім у правому вікні прокручується алфавітний список імен всіх убудованих функцій обраної категорії.Накроці 2 визначаються аргументи обраної функції. Аргументами функцій можуть бути константи, або табличні посилання на їхні клітки, що зберігають. У нашому прикладі пошлемося на інтервали чарунок таблиці, куди була уведена вага й ціна кожного продукту, як окремі елементи скалярно перемножених векторів.
Діалогове вікно Майстра функцій звичайно спливає на екрані саме в такому положенні, що закриває собою потрібні чарунки робочого аркуша, але його легко підсунути убік мишею, утримуючи натисканням лівої кнопки миші стрілку екранного покажчика на рядку заголовка. Коли дані про вагу стануть цілком видні на екрані, досить їх виділити (клітки будуть обводитися пунктиром), щоб посилання на відповідний діапазон B2:B9 синхронно вписалася в поле уведення табличних координат першого аргументу функції. Потім крапка уведення даних | щигликом миші переводиться вниз у поле уведення другого аргументу, і аналогічно зафарбовуються дані про ціни – C2:C9.
Тепер можна закінчити виклик функції, нажавши на клавішу уведення або екранну кнопку Готово діалогового вікна Майстра функцій (крайня праворуч унизу). У випадку успіху в поточну клітку D10 нами уведена послідовність символів =СУММПРОИЗВ(B2:B9;C2:C9), сприймана програмою Excel як розрахункова формула, що складається з тільки звертання до стандартної функції.
У клітці таблиці після уведення в неї формули з'являється відповідь– у прикладі 1 це числове значення 205,6795, а виробляюча формула видна над полем таблиці в рядку уведення, якщо клітка виділена поточним положенням рамки табличного покажчика. Пропонуємо читачеві самостійно впоратися з форматуванням отриманого значення, наприклад вивести його в грошовому стилі з точністю до копійок.
Мал. 9. Стан уведення в поточний осередок звертання до функції за допомогою Майстра функцій
Припустимо тепер, що вага куплених в умовах приклада 6 бананів збільшився до 1,5 кг.
Якщо впоратися із заміною значення в клітці B5 прямим уведенням "зверху" нового числа, то після прийому значення даних підсумок витрат миттєво стане дорівнює 216,27р., тому що електронна таблиця автоматично обновляє результати формул при зміні вихідних даних, значення яких задані не константами, а табличними посиланнями на інші клітки (осередку).
•Клітки, на які посилається формула поточної, називаються залежними.
•Клітки, формули яких посилаються на поточну, називаються що впливають.
При організації складних обчислень у таблиці важливо стежити за структурою, що утвориться, формул. Неоціненну допомогу в цьому здатна зробити панель інструментів Залежності (див. мал. 13).
Припустимо тепер, що ми направилися за покупками, маючи в кишені рівно 200 руб., і, як уже з'ясувалося, на придбання всього вмісту відібраного кошика їх не вистачить. Не маючи можливості змінити ціни, подумаємо, як можна зменшити вага картоплі, щоб укластися в бюджетне обмеження.
Мал. 10. Пошук на робочому аркуші кліток таблиці, у яких є формули, що залежать від поточної.
Спробуємо просто вводити в чарунок B8 нові числа – 3,5 (можна побільше), 4 (треба ледве поменше), і так далі . Цей процес підбора в Excel автоматизований. Для виклику процедури підбора значення одного параметра, що приводить ланцюжок формул до потрібної відповіді, дамо команду Сервіс Підбор параметра .
Мал.11. Діалогове вікно процедури чисельного рішення неявних рівнянь.
Процедура Підбор параметра дозволяє (див. мал. 11) установити в залежномучарунку (утримуючої розрахункову формулу) шукане числове значення, змінюючи значення що впливає (на значення залежної від її формули) чарунку. З її допомогою користувач Excel одержує можливість знаходити вирішальне значення (корінь) неявного рівняння, не прибігаючи до виводу явної аналітичної залежності
При цьому ліва частина рівняння може бути досить складної й кодуватися навіть не однієї, а декількома формулами, зв'язаними між собою табличними посиланнями (адресами кліток) у загальну залежність.
Помітимо, що якщо рішень трохи, те перебуває тільки одне з них - найближче до початкового значення осередку, що впливає, що при підгоні до потрібної відповіді змінюється. Якщо потрібно знайти інший корінь - повторите підбор з іншим початковим наближенням. Давайте докладніше вивчимо вплив приватних витрат на придбання кожного продукту на показник "Витрати РАЗОМ".
Нагляднішою формою порівняння чисел є побудова по таблиці даних графіків стандартного типу за допомогою процедури Майстер діаграм, що активізується кнопкою панелі інструментів Стандартна, або через меню вибором командної послідовності Вставка Діаграма .
При вставці діаграми на той же робочий аркуш, де перебувають вихідні дані, необхідно вказати щигликом по табличному полю майбутнє положення її лівого верхнього кута, а потім обмежити розмір вікна діаграми діагональним рухом маніпулятора правіше вниз, утримуючи натиснутої ліву кнопку до завершення виділення пунктиром необхідних границь вікна.
Рис. 12. Виділення діапазону, що містить дані для побудови діаграми – крок 1.На кроці 2 виберемо тип діаграми – Кругова, а на кроці 3 – вид діаграми (7).
Цікаво, що процедурою Майстер діаграм автоматично обчислюються при цьому питомі частки витрат у загальній сумі, адже в таблиці цих даних у явному виді просто немає. Давайте перевіримо розрахунок часток окремих доданків у сумі витрат, повторивши його з точністю більшої, ніж до цілих відсотків.
Питома вага кожного доданка - дріб, у чисельнику якої коштує поточний доданок, а знаменник усіх - повна сума чисельників. Питома вага витрат на апельсини розраховується по формулі =D2/D10. Якщо ми скопіюємо її вниз, то одержимо =D3/D11, =D4/D12, .
Як тільки посилання знаменника вкаже на порожню клітинку D11 (за замовчуванням числове її значення вважають нульовим), відбудиться вивід повідомлення про помилку: вживається спроба виконати математично некоректну операцію - розділи позитивний чисельник на нуль (повідомлення #ДЕЛ/0!).
Мал. 13. Кругова діаграма за числовим значенням формул у діапазоні D2:D9
Мал. 14. Таблична модель обчислення часток загальних витрат.
Щоб закріпити у формулі знаменника номер 10 рядка, захистити його від зміни при копіюванні вираження вниз на інші рядки, необхідно вставити перед 10 знак долара $, після чого адреса клітки стане абсолютним і запис формули прийме вид =D2/D$10
Це виправлення в режимі редагування вмісту клітки можна зробити, перемкнувшись на латинський алфавіт, і нажавши знак $ у верхньому ряді клавіатури (цей символ сполучений із цифрою 4), причому одночасно прийде втримувати Shift - клавішу перемикання регістра.
Зручніше при редагуванніперетворювати адреса клітки в абсолютний, натискаючи клавішу F4. Тоді адреса D10, на який указує крапка уведення | , буде сам послідовно перетворювати в усі можливі варіанти: абсолютний $D$10, змішані D$10 й $D10, відносний D10.
При копіюванні формули =D2/D$10 униз вийде послідовність виражень =D3/D$10, D4/D$10 ., при цьому в нових дробах змінюється номер рядка в чисельнику, де адреса відносний. Зверніть увагу на те, що на побудованій раніше круговій діаграмі картоплі відповідає сектор, що займає 26% площі, а явний розрахунок дає менший результат - частка витрат на картоплю дорівнює 25,4% загальної суми (див. вище мал. 14).
Для продовження обчислювальної практики розглянемо нове припущення про те, що замовлення на фрукти й овочі був зроблений у цінах минулого тижня, а тепер всі ціни небагато змінилися, і необхідно переоцінити витрати на придбання колишньої кількості продукції.
Щоб новий розрахунковий фрагмент таблиці мав на екрані компактний і доступний для огляду вид, краще не заповнювати праворуч новими формулами велика кількість кліток у тих же перших рядках, а розмістити ще один екземпляр набору вихідних констант у вільних клітках нижче. Наприклад, можна розмістити копію блоку A1:A9 у таблиці, починаючи з A13 (виділити A1:A9, команда Виправлення Копіювати, клацнути по A13, нажати клавішу уведення), або ввести в A14 формулу =A2 і методом автозаполнения (мишею за кут) скопіювати її на діапазон A14:C21.
Так будуть отримані копії значень кліток-джерел, а зовнішній вигляд їх також можна заново не регулювати, а ще раз виділити блок-джерело й скористатися його форматом як зразком, нажавши кнопку Копіювати формат панелі інструментів Стандартна. Потім потрібно тільки обвести пунктиром діапазон, що приймає копію вихідного формату, і звільнити кнопку миші. Тепер можна вписати в таблицю ціни нового тижня й знайти по них суму витрат.
Для оцінки зміни купівельної спроможності грошей по заданому наборі продуктів за минулий період обчислюється зведений показник – агрегатний індекс цін:
Мал.15. Вимір росту цін за допомогою індексу фіксованого состава.
де p – вектор цін продуктів;
q – вектор обсягів споживання продуктів (вага покупки).
[http://www.cfin.ru/finanalysis/smirnova/excel_bl2.shtml]