Полная версия

Главная arrow Информатика arrow MS Excel: виконання обчислень в таблицях

  • Увеличить шрифт
  • Уменьшить шрифт


<<   СОДЕРЖАНИЕ   >>

Виконання розрахунку з використанням стандартних функцій

Аби виконати розрахунок, використовуючи стандартну функцію, виконаєте наступні дії:

1. Виділите вічко, в яке треба вставити функцію, введіть «= »,а потім в списку Функції, що розкривається, в рядку формул виберіть потрібну із списку (див. мал. 16.1). На екрані відображуватиметься діалогове вікно Аргументи функції (Function Arguments) (мал.). Якщо в списку, що розкривається, вибрати Інші функції (More functions) то відкриється діалогове вікно Майстер функцій -- крок 1 з 2 (Insert Function) (мал.).

Інші способи відображення діалогового вікна Майстер функцій -- крок 1 з 2 (Insert Function):

натискуйте кнопку Вставку функції (Insert Function) у рядку формул;

виберіть команду Функція (Function) у меню Вставка (Insert);

натискуйте клавіші Shift+F3.

2. У діалоговому вікні Майстер функцій -- крок 1 з 2 (Insert Function) (мал.) у полі Пошук функції (Search for а function) введіть опис дії, яку ви хотіли б виконати. Наприклад, аби знайти функції, пов'язані з логарифмами чисел, введіть log. Якщо ви не знаєте, до якої категорії відноситься ваша функція, то в списку, що розкривається, або категорія (Or select а category) виберіть рядок Повний алфавітний перелік (АН) і проглянете список всіх функцій в алфавітному порядку. Смуга прокрутки дозволяє проглянути невидимі в даний

Мал. Діалогове вікно майстра функцій -- крок 1 з 2

момент елементи списку. У нижній частині вікна дається визначення виділеної функції і її аргументів. Аби отримати опис функції, виберіть функцію в списку і клацніть заслання Довідка по цій функції (Help on this function). Виділите потрібний рядок в списку Виберіть функцію (Select а function) і натискуйте кнопку (Жили клавішу Enter. 3. На екрані відображуватиметься діалогове вікно Аргументи функції (Function Arguments) (мал.). У верхній частині вікна розміщуються поля, призначені для введення аргументів, в нижній частині -- довідкова інформація: ім'я вибраної функції, всі її аргументи, призначення функції і кожного аргументу, поточний результат функції і всієї формули. У тих випадках, коли аргумент приведений напівжирним шрифтом, він є обов'язковим, якщо звичайним шрифтом, то його можна пропустити. Аби панель формул не закривала діапазон вічок з даними, її можна перемістити, утримуючи такою, що натискує кнопку миші. Аби ввести як аргумент заслання на вічко, клацніть значок розташований біля правої межі поля. ^™*

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

Мал. Використання панелі формул для оцінки дисперсії по вибірці

Формула масиву

Якщо формула масиву повертає одне значення, вкажіть вічко, в яке необхідно ввести формулу. Якщо формула масиву повертає декілька значень, то виділите діапазон вічок, в які необхідно ввести формулу. Наберіть формулу і натискуйте клавіші Ctrl+Shift+Enter. При введенні формули масиву Excel автоматично бере її у фігурних дужок {} після натиснення вказаних клавіш.

Масиви формул зручно використовувати для уведення однотипних формул і обробки даних у вигляді таблиць. Наприклад, для обчислення модуля від чисел, розміщених у вічках B1, C1, D1, E1 замість уведення формул у кожне вічко можна увести одну формулу - масив для усіх вічок. Microsoft Excel додає навколо масиву формул фігурні дужки { }, по яким його можна відрізнити.

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

  • · виділити вічка, в яких повинен знаходитися масив формул (мал.14);
  • · увести формулу звичайним способом, вказавши в якості аргументів групу вічок-аргументів ABS(B1:E1);
  • · у останньому вікні замість кнопки ОК натиснути комбінацію клавіш Ctrl+Shift+Enter.

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

  • · виділити вічка, в яких знаходиться масив;
  • · натиснути мишею усередині рядка редагування і відредагувати формулу;
  • · натиснути комбінацію клавіш Ctrl+Shift+Enter.

Автокорекція при введенні формул

Excel 2003 може розпізнати найбільш поширені помилки, що допускаються користувачами при введенні формул у вічко. Наприклад, автоматично виправляються помилки, пов'язані з неправильними засланнями, отриманими в результаті переміщення вічок. Введений помилково символ «х» автоматично перетворюється на знак множення і так далі При цьому на екрані з'являється запит про необхідність виробити виправлення.

Використання поля Введіть питання паю вирішення питань, зв'язаних з використанням функції

Для вирішення питань, зв'язаних з використанням функції, її синтаксисом, визначенням до якої категорії вона відноситься, можна ввести назву функції в поле Введіть питання (Type а question for help) і натискувати клавішу Enter. Так, якщо у вас виникли питання по використанню кореляційної функції, введіть в поле: кореляція (correlation). Будуть запропоновані можливі варіанти використання функції (мал. 16.7).

Мал. Інформація, що надається помічником про функцію

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

Вправи

1. Excel допомагає вам знайти обчислити різні довідкові дані, не користуючись довідниками. Знайдіть десятковий логарифм числа 250. Виконаєте вправу таким чином:

Виділите вічко, в яке треба вставити функцію, і натискуйте кнопку Вставка функції (Insert Function) у рядку формул. У діалоговому вікні Майстер функцій -- крок 1 з 2 (Insert Function) (мал.) у списку, що розкривається, Категорія виділите рядок Математичні (Math & Trig) а в нижньому списку Виберіть функцію (Select а function) -- LOG10 і натискуйте кнопку ОК. На екрані відображуватиметься діалогове вікно Аргументи функції (Function Arguments) (мал.). Введіть число 250 в рядок Число (Number) діалогового вікна і отримаєте результат 2,398.

2. Знайдіть значення тригонометричних функцій: синуса, косинуса, тангенса, котангенса для кута 10 градусів.

Мал. Обчислення десяткового логарифма.

Виділите вічко, в яке треба вставити функцію, і натискуйте кнопку Вставка функції (Insert Function) у рядку формул. У діалоговому вікні Майстер функцій -- крок 1 з 2 (Insert Function) (мал.) у списку, що розкривається, Категорія виділите рядок Математичні (Math & Trig) а в нижньому списку Виберіть функцію (Select а function) виберіть відповідну тригонометричну функцію і натискуйте кнопку ОК. Введіть число 10 в рядок Число (Number) діалогового вікна Аргументи функції (Function Arguments) і побачите результат. Для довідки приведемо значення тригонометричних функцій: sinl0°=0,17, coslO°=0,98, tglO°=0,18, ctglO°=5,67.

3. Передбачимо, що з інструментів, відштампованих однією і тією ж машиною, вибрано навдогад 10 зразків і випробувані на злам. Значення вибірки (1345, 1301, 1368, 1322, 1310, 1370, 1318, 1350, 1303, 1299) збережені у вічках А2:ез відповідно. Оціните дисперсію по вибірці. Для вирішення вправи використовуємо статистичну функцію. Зробимо поточним перше вільне вічко в стовпці А, наприклад #* А4, і натискуватимемо кнопку Вставка функцій (Paste Function) у рядку формул.. У діалоговому вікні Майстер функцій -- крок 1 з 2 (Insert Function) (див. мал. 16.6) у списку, що розкривається, або категорія (Or select а category) виділите рядок Статистичні (Statistical) а в нижньому списку Виберіть функцію (Select а function) -- рядок ДІСП (VAR). У даному прикладі функція ДІСП (VAR) оцінює дисперсію опору на злам для всіх інструментів.

Функція ДІСП передбачає, що аргументи є лише вибіркою з генеральної сукупності. Якщо дані представляють всю генеральну сукупність, обчислюйте дисперсію, використовуючи функцію ДІСПР. Логічні значення, такі як ІСТИНА або БРЕХНЯ, а також текст ігноруються. Якщо вони не повинні ігноруватися, користуйтеся функцією робочого аркуша ДІСПА (VARA). Синтаксис функції: ДІСП(число1;число2;...)

Число1, число2 ... -- це від 1 до 30 числових аргументів, відповідних вибірці з генеральної сукупності.

Натискуйте кнопку ОК або клавішу Enter. У вікні Excel нижче за рядок формул відображуватиметься панель формул. Виділите діапазон вічок А2:ез, в якому зберігаються дані. Внизу панелі формул побачите напис Значення (Formula Result): 754,3. Натискуйте кнопку ОК. Таким чином, дисперсія опору на злам для всіх інструментів дорівнюватиме 754,3.

Мал. Вікно, використовуване для обчислень функції часу

4. Знайдіть значення часу у вигляді десятинного дробу для 8 годин 53 хвилин 14 секунд. Виконаєте вправу таким чином: Натискуйте клавіші Shift+F3. У діалоговому вікні Майстер функцій -- крок 1 з 2 (Insert Function) у списку, що розкривається, Категорію виділите рядок Дата і час (Date& Time) а в нижньому списку Виберіть функцію (Select а function) -- ЧАС (Time) (мал. 16.5) і натискуйте кнопку ОК. На екрані відображуватиметься діалогове вікно Аргументи функції (Function Arguments) (мал. 16.9). У полі Година (Hour) введіть 8, в полі Хвилина (Minute) -- 53, в полі Секунда (Second) -- 14 і натискуйте кнопку ОК. У вічку набудемо значення 0,370301.

Використання фінансових функцій Microsoft Excel

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

Найчастіше аргументами фінансових функцій є такі величини:

  • · майбутня вартість -- вартість вкладення або позики по завершенні усіх платежів;
  • · кількість виплат -- загальна кількість платежів або періодів виплат;
  • · виплата -- обсяг періодичних платежів по вкладенню або позиці;
  • · поточна вартість -- початкова вартість вкладення або позики. Наприклад, початкова вартість позики дорівнює сумі позики;
  • · ставка -- відсоткова ставка або знижка по вкладенню або позиці;
  • · тип -- режим, що показує, як здійснюються виплати (наприкінці періоду чи на його початку).

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

  • ? визначення накопиченої суми (майбутньої вартості);
  • ? визначення початкового значення (поточної вартості);
  • ? визначення строку платежів і відсоткової ставки.

Значення аргументів ставка та кількість періодів залежать від режиму виплат (методу нарахування відсотків). У таблиці показані значення таких аргументів, де n -- кількість років, а k -- річна відсоткова ставка.

Метод нарахування %

Загальне число періодів для начислення відсотків

% ставка за період нарахування

щороку

n

k

щопівроку

n*2

k/2

щоквартально

n*4

k/4

щомісячно

n*12

k/12

щоденно

n*365

k/365

Обчислення майбутнього значення суми

Функція БЗ

Вираховує майбутнє значення вкладу на основі постійної відсоткової ставки і має такий вигляд:

=БЗ (ставка; к-сть виплат; виплата; поточна вартість; тип)

Де:

ставка -- це ставка процента за період,

число періодів -- кількість платежів,

поточна вартість -- початкове значення вкладу

тип -- число 0 або 1

(0 -- виплата наприкінці періоду, а 1 -- виплата на початку періоду)

Приклад:

Розрахувати, яка сума буде на рахунку, якщо 27 тис. грн покладені на 33 роки під 13,5 % річних. Відсотки нараховуються кожні півроку.

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

=БЗ(норма; к-сть виплат; поточна вартість)

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

ставка -13,5 %/2, тому що % нараховуються кожні півроку

к-сть виплат -33*2, також тому, що виплати провадяться кожні півроку

поточна вартість -- -27 000 -- від'ємне, і це означає не отримання, а вкладення грошей

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

=БЗ(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. Такі формули мають бути записані в комірки таблиці (табл.).

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

 
Перейти к загрузке файла
<<   СОДЕРЖАНИЕ   >>