Табличний процесор MS Excel: формати даних, робота з формулами

Лекція 7

Тема. Табличний процесор MS Excel: формати даних, робота з формулами.

План

Типи даних в Excel.
Основні правила, яких слід дотримуватись при створенні електронних таблиць.
Поняття формули в Excel. Пріоритет операторів у формулі.
Типи посилань у Excel.

Ключові слова: формати даних, формула, операнд формули, оператор, відносне, абсолютне та змішане посилання, зовнішнє посилання, вилучене посилання, пріоритет операцій у формулі, масив даних

1. Типи даних

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

Якщо перший символ – літера або знак «'», то Excel вважає, що вводиться текст. Якщо перший символ – цифра або знак «=», то Excel вважає, що вводиться число або формула.

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

Введення тексту

Будь-яка введена послідовність знаків, що містить нечислові символи і не може бути інтерпретована Excel як число, формула, дата, час дня, логічне чи значення помилки, інтерпретується як текст. Текстові дані являють собою послідовність літер і цифр. За замовчуванням текст, що вводиться в комірку, вирівнюється по її лівому краю.

Для того, щоб ввести число як текст, потрібно взяти його в лапки. Також можливо вибрати формат подання інформації як тексту. Для цього потрібно виділити комірку чи діапазон комірок та виконати команду Формат – Ячейки – Число й у списку форматів, що з’явився, вибрати – Текстовый. У цьому випадку табличний процесор для виділеної комірки (або діапазону комірок) введену інформацію буде сприймати як текст.

Введення чисел

Числові дані подаються у вигляді послідовності цифр 0-9 з використанням спеціальних символів (+, -, /, *), дефісів і круглих дужок. Введене число автоматично вирівнюється по правому краю комірки. Проте спосіб вирівнювання можна змінити. Для подання чисел у певному форматі використовують команду Формат – Ячейки… і в опції Число задають формат зображення даних у активній або виділених комірках таблиці. В Excel числа відображаються в категоріях таких форматів, як Общий, Числовой, Денежный, Финансовый, Дата, Время, Процентный, Дробный, Экспоненциальный, Текстовый.

Формат Общий. Якщо Ви хочете вводити числа, не зв’язуючи себе якими-небудь форматами, то Excel буде їх вводити у форматі Общий. За замовчуванням всі комірки нової робочої книги мають формат Общий. Цей формат має один дуже важливий недолік: їм не можна керувати. Формат Общий використовується для відображення як текстових, так і числових даних довільного типу.

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

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

Формати Дата і Время використовуються для подання дат і часу в різних варіантах: 16.04.99, травень 99, 13:30, 9:22:37, 3.04.99 12:45 і т. д.

У форматі Процентный значення комірок множаться на 100 і виводяться на екран із символом %.

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

Формат Экспоненциальный використовується для подання чисел із рухомою комою (стандартний вид числа). Число з експонентою складається з мантиси і порядку, розділених латинською літерою е або Е. Мантиса – ціле або дійсне число від 1 до 10, порядок – ціле. Число з експонентою трактується як мантиса, помножена на 10 у степені, що дорівнює порядку. Найчастіше, числа з експонентою використовують для запису дуже великих або маленьких величин (див. табл. 1).

Таблиця 1

Число в звичайному вигляді	Стандартний вид числа	Мантиса	Порядок числа	Запис в Excel
0,00000000002	2*10-11	2	-11	2E-11
650000000000000000	6,5*1017	6,5	17	6,5E+17

Рядки, які мають Текстовый формат, обробляються як текстові рядки, навіть якщо в них були введені числові дані.

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

2. Основні правила, яких слід дотримуватись при створенні електронних таблиць

Створення електронної таблиці, якщо не вказано інше, розпочинають з першого рядка і першого стовпця.

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

Якщо текст у комірці записаний у кілька рядків, слід скористатись командою Формат – Ячейки – Выравнивание – Переносить по словам (а не вносити у різні комірки!).

Заголовок таблиці зазвичай розміщують безпосередньо над таблицею. Вдалим є розташування заголовку По центру выделения.

Формули для обчислень не мають вноситись в об’єднані комірки!

Десяткові дроби за замовчуванням записуються з використанням символу "," (2,35). А дата – з використанням символу "." (10.02.2009).

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

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

3. Поняття формули в Excel

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

Операндом формули може бути константа (число, текст, логічне значення), адреса комірки (посилання на комірку), функція.

Операторами позначаються операції, які слід виконати над операндами формули. У Microsoft Excel є 4 види операторів: арифметичні, текстові, оператори порівняння (відношень) та оператори посилань.

Арифметичні оператори використовуються для виконання арифметичних операцій, таких як: (+) додавання; (-) віднімання або протилежне число; (*) множення; (/) ділення; (^) піднесення до степеня, (%) відсотки.

Оператори порівняння (відношень) використовуються для порівняння двох значень, а саме: > більше; < менше; = дорівнює; <= менше або дорівнює; >= більше або дорівнює; < > не дорівнює. Результатом є логічне значення ІСТИНА або ХИБА.

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

Текстовий оператор конкатенації (амперсанд &) використовується для об’єднання декількох текстових рядків в один рядок. Текстові константи у формулі обмежуються подвійними лапками. Наприклад, "Північний"&"вітер".

Оператори посилань використовуються для опису посилань на діапазони комірок: : (двокрапка) – ставиться між посиланнями на першу та останню комірку діапазону (B5:B15), ; (крапка з комою) – оператор об’єднання, поєднує кілька посилань в одне (СУММ(B5:B15;D5:D15)), (пропуск) – оператор перетину множин, використовується для посилання на спільні комірки діапазонів (B7:D7 C6:C8).

Пріоритет операторів у формулі

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

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

знаходження протилежних значень (знак "-" перед операндом);
1. знаходження відсотків від числа ("%");
2. піднесення до степеня ("^");
3. множення і ділення ("*", "/");
4. додавання та віднімання ("+", " - ").

Як правило, формули не відображаються в комірках, де виводяться результати проведених обчислень. Щоб відобразити формулу, виділіть необхідну комірку. Формула з’явиться в рядку формул. Коли необхідно відобразити всі формули, розміщені в комірках листа, потрібно виконати команду Сервис – Параметры, перейти на вкладку Вид, в області Параметры окна встановити прапорець Формулы та натиснути ОК.

4. Типи посилань у Excel

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

Посилання, яке при копіюванні в інші комірки буде модифікуватись (змінюватись) відповідно новому розташуванню, є відносним. Наприклад, А1, В4, Р38 тощо.

Абсолютним називається посилання, що не змінюється при виконанні операцій з комірками і листами. В абсолютних посиланнях перед назвою стовпця і номером рядка стоїть знак $. Наприклад, $А$1, $B$4, $Р$38 тощо.

У змішаних посиланнях абсолютною є назва стовпця і відносною – назва рядка або навпаки (наприклад, $А1 та А$1). При виконанні операцій з комірками і листами модифікується тільки відносна частина посилань.

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

Можна здійснювати посилання на комірки, які знаходяться на іншому листі робочої книги. Зокрема, посилання Квартал1!В19 буде означати відносне посилання на комірку В19, яка розташована на листі Квартал1. Якщо в імені листа є пропуски, то воно береться в одинарні лапки. Наприклад, 'Лист 2'!А14:В14 (посилання на діапазон комірок А14:В14 з листа 2).

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

Табличний процесор MS Excel: формати даних, робота з формулами