НМЦ

ІНФОРМАЦІЙНІ ТЕХНОЛОГІЇ

Електронний посібник

 

ВФПО

МОДУЛЬ ІІ. ОРГАНІЗАЦІЯ ТАБЛИЧНИХ ДАНИХ ЗАСОБАМИ ОФІСНОГО

ПРОГРАМНОГО ЗАБЕЗПЕЧЕННЯ

 

8. Обчислення у документах MICROSOFT EXCEL обчислення за допомогою формул

8.1. Основні типи і формати даних

8.2. Обчислення за допомогою формул

8.3. Абсолютні та відносні посилання на клітинки

8.4. Стандартні функції. Використання майстра функцій

8.5. Підсумкові обчислення. Аналіз даних

 

Перегляд презентації:

Обчислення у документах Microsoft Excel.

Обчислення за допомогою формул

 

8. Обчислення у документах MICROSOFT EXCEL обчислення за допомогою формул

8.1. Основні типи і формати даних

 

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

Числа. Для представлення чисел можуть використовуватися формати декількох різних типів: числовий, експоненціальний, дріб і процентний. Існують спеціальні формати для зберігання дат (наприклад, 25.09.2003) і часу (наприклад, 13:30:55), а також фінансовий і грошовий формати (наприклад, 1500,00 грн), які використовуються у проведенні розрахунків.

За замовчуванням для представлення чисел електронні таблиці використовують числовий формат, який відображає два десяткові знаки числа після коми (наприклад, 115,20).

Експоненціальний формат застосовується, якщо число, що містить велику кількість розрядів, не вміщається в комірці. В цьому випадку розряди числа представляються за допомогою додатних або від’ємних ступенів числа 10. Наприклад, числа 2000000 і 0,000002 подаються в експоненціальному форматі як 2×106 і 2H10-6, будуть записані в електронних таблицях у вигляді 2,00Е+06 і 2,00Е-06.

За умовчанням числа вирівнюються в комірках по правому краю. Це пояснюється тим, що за розміщення чисел один під одним (у стовпці таблиці) зручно мати вирівнювання по розрядах (одиниці під одиницями, десятки під десятками і т. д.).

Текст. Текстом в електронних таблицях є послідовність символів, що складається з букв, цифр і пропусків, наприклад, текстом може бути послідовність цифр 2008.

За замовчуванням текст вирівнюється в комірці по лівому краю. Це пояснюється традиційним способом читання листа (зліва направо).

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

Наприклад, формула=А1+В1 забезпечує додавання чисел, що зберігаються в комірках А1 і В1, а формула =А1*5 – множення числа, що зберігається в комірці А1, на 5. За зміни початкових значень, що входять у формулу, результат перераховується автоматично.

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

 

Рис. 8.1. Зображення Формули

 

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

Для редагування формули необхідно клацнути на рядку формул і провести редагування. Аналогічна дія виконується при натисненні на клавішу F2.

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

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

Введення у формули імен комірок можна здійснювати виділенням потрібної комірки за допомогою миші.

Дані можна копіювати або переміщати з одних комірок або діапазонів в інші. В процесі копіювання можна вставляти в комірки не тільки самі дані, але і формат даних, і параметри оформлення комірок (тип межі і колір заливки).

Для швидкого копіювання даних з однієї комірки відразу у всі комірки певного діапазону використовується спеціальний метод: спочатку виділяється комірка і необхідний діапазон, а потім вводиться команда Заповнити => вниз [управо, вгору, вліво].

У формулах можуть використовуватися посилання на адреси комірок. Існують два основних типи посилань: відносні і абсолютні. Відмінності між відносними і абсолютними посиланнями виявляються при копіюванні формули з активної комірки в інші.

 

 

Обчислення в Excel виконують за допомогою формул. Формула може містити числові константи, посилання на клітинки і функції Excel, що сполучені знаками математичних операцій. Дужки дозволяють змінювати звичайний порядок виконання дій. Якщо клітинка містить формулу, то зазвичай в клітинці на робочому аркуші відображається тільки поточний результат обчислення цієї формули. Щоб побачити саму формулу, а не результат її роботи, потрібно виділити клітинку і подивитися на запис, який відображається в рядку формул, або двічі клацнути по клітинці. Основне правило виконання обчислень в Excel полягає в тому, що якщо значення комірки дійсно залежить від інших комірок таблиці, завжди слід використовувати формулу, навіть якщо операцію легко виконати «подумки»! Це гарантує, що подальше редагування таблиці не порушить її цілісності та правильності, виконаних в ній обчислень.

 

 

У формулі на рис. 8.2 записана операція над вмістом двох клітинок. Замість чисел у формулі використані адреси клітинок – посилання на клітинки, вміст яких використовується в обчисленнях.

 

Рис. 8.2. Операція над вмістом клітинок

 

Це означає, що результат обчислення залежить від того, які числа знаходяться в клітинках.

Клітинка, яка містить формулу, називається залежною.

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

 

Посилання на клітинки можна задати:

*     адреси клітинок ввести вручну;

*     клацнути на потрібній клітинці або обраному діапазону, адресу якого потрібно ввести. Клітинка та діапазон виділяються пунктирною рамкою.

 

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

Відносна адресація. Від методу адресації посилань залежить, що буде з ними відбуватися при копіюванні формули з однієї клітинки в іншу. За замовчуванням посилання на клітинки у формулах розглядаються як відносні. Це означає, що адреси посилань при копіюванні формули з однієї клітинки в іншу автоматично змінюються. Вони приводяться у відповідність з відносним розташуванням початкової клітинки і створюваної копії. Наприклад, в клітинці В2 є посилання на клітинку A3, яка розташовується на один стовпець лівіше і на один рядок нижче (рис. 8.3). Якщо формула буде скопійована в іншу клітинку, то таке відносне посилання збережеться. Наприклад, при копіюванні формули в клітинку С5 відносне посилання буде продовжувати вказувати на клітинку, яка розташована на один стовпець лівіше і на один рядок нижче, тобто на клітинку В6.

 

Рис. 8.3. Вигляд відносного посилання

 

Абсолютна адресація

За абсолютніої адресації адреси посилань при копіюванні формули не змінюються, так що адреса клітинки, на яку вказує посилання, розглядається як постійна. Для зміни способу адресації при редагуванні формули треба виділити посилання на клітинку і натиснути клавішу F4. Елементи адреси клітинки з абсолютною адресацію позначаються символом $. Наприклад, при послідовних натисненнях клавіші F4 адреса клітинки А1 буде записуватися як $А$1, А$1 і $А1. У двох останніх випадках один з компонентів адреси клітинки розглядається як абсолютний, а інший – як відносний.

Введення формули у рядок формул завжди починається зі знаку =. Для простих обчислень використовуються числа і знаки математичних операцій. Порядок виконання дій регулюється дужками. Однак для складних обчислень виникає необхідність у використанні Стандартних функцій Excel.

 

 

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

 

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

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

 

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

 

Найпростіший спосіб вставки функції – почати введення клацанням на кнопці fx в рядку формул. Відкриється діалог Вставлення функції (рис. 8.4.), який полегшує вибір і вставку в формулу потрібної функції. У списку Категорія вибирається категорія, до якої належить функція (Математичні, Статистичні тощо). У списку Виберіть функцію вказується функція.

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

 

Рис. 8.4. Вставлення функції

Рис. 8.5. Синтаксис Excel

 

Тут же можна отримати довідку щодо обраної функції, яка відкривається у вікні Довідка.

Потрібну функцію можна також знайти і вставити в клітинку, використовуючи Бібліотеку функцій вкладки Формули, де функції представлені за категоріями.

 

Рис. 8.6. Вміст вкладки Формули

 

Клацання на кнопці ОК відкриває вікно Аргументи функції, одночасно ім'я функції заноситься в рядок формул разом з дужками, які обмежують список аргументів.

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

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

 

Рис. 8.7. Вікно Аргументи функції

 

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

 

 

Підсумкові обчислення

 

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

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

 

Наприклад, можливе обчислення:

*     суми значень елементів, які входять в набір;

*     середнього значення та інших статистичних характеристик;

*     кількості елементів набору.

 

Підсумкові обчислення в Excel виконуються за допомогою вбудованих функцій.

 

Рис. 8.8. Вікно значення підсумкових функцій

Рис. 8.9. Настроювання рядка стану

 

Особливість підсумкових функцій полягає в тому, що вони завжди обчислюються для діапазону клітинок. Наприклад, на рис. 8.8 виділений діапазон клітинок В3:В15, автоматично для цього діапазону в рядку стану виводяться значення деяких підсумкових функцій.

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

Щоб підсумкові значення відображалися в клітинках, в ці клітинки необхідно ввести формули з використанням стандартних функцій, які можна обрати зі списку після команди Основне → Редагування → Автосума або натиснути кнопку Швидкий аналіз і на панелі Підсумки вибрати мініатюру потрібної функції.

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

 

Рис. 8.10. Вікна швидкого аналізу даних

 

Відкривається мініпанель з набором вкладок (рис. 8.10).

Наприклад, вкладка Форматування утримує інструменти для виділення цікавих даних. Вкладка Діаграми – рекомендує діаграми для візуалізації даних. Вкладка Підсумки – вбудовані підсумкові функції.

Значно більші можливості для форматування даних в клітинках можна отримати, виконавши команди Основне → Стилі → Умовне форматування (рис. 8.11).

 

Рис. 8.11. Вікно Умовне форматування

 

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

Для прикладу дані стовпця Витрати форматовано у вигляді гістогра-ми з градієнтною заливкою.

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

 

Питання для самоконтролю

1. Розкрийте поняття Формули у Microsoft Excel.

2. Які типи даних можна виділити при роботі з таблицями Microsoft Excel?

3. Розкрийте поняття Абсолютне посилання на клітинки.

4. Розкрийте поняття Відносне посилання на клітинки.

5. Як відбувається обчислення за допомогою формул?

Попередня тема

На початок

Наступна тема