Формули та розрахунки

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

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

До формули можна також включати імена стандартних функцій, вибираючи їх зі спеціальної вкладки Формули - Бібліотека функцій (Рис. 3.6).

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

Введення функції завершується натисканням клавіші Enter.

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

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

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

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

У формулах для посилання на відповідні значення використовують адреси клітинок. В Excel використовують два типи адрес (посилань) клітинок: відносні та абсолютні.

Відносні адреси - це адреси, які в процесі копіювання змінюють своє значення (посилання на іншу клітинку) відповідно до нової позиції формули при її копіюванні. Їх адреси позначаються звичним чином та використовувати їх не завжди зручно.

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

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

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

3.5. Опрацювання графічних матеріалів засобами Excel

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

Діаграма - графічне відображення числових даних.

Табличний процесор Excel дозволяє побудувати 12 стандартних типів діаграм, кожен із яких має ще кілька різновидів. Для цього використовують послугу Вставлення - Діаграми.

Створення будь-якої діаграми розпочинається з виділення діапазону даних (Рис. 3.7.), що підлягають відображенню на ній. Початковий діапазон даних можна виділяти пізніше. Його попереднє виділення пояснюється тільки прагненням мати зразок діаграми вже після вибору її типу та вигляду.

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

Вибір типу та вигляду діаграми, а також перегляд її зразка.

1. Зміна або вибір діапазону даних, на основі яких буде побудовано діаграму, та визначення способу формування її рядів.

4. Вибір варіанту розташування діаграми (на поточному або на окремому аркуші).

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

Процес створення графіка або діаграми розпочинається з активізації послуги Діаграми та обрання її типу. Якщо тип обраної діаграми незадовільняє користувача, його можна змінити, скориставшись послугою Знаряддя для діаграм - Конструктор - Тип - Змінити тип діаграми. (Рис. 3.9).

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

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

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

Скориставшись піктограмою Перехід рядок/стовпець - зміна способу формування рядів діаграми здійснюється автоматично і одразу відображається на аркуші.

При виборі піктограми Вибір даних, відкривається вікно Вибір джерела даних (Рис. 3.10), яке дозволяє обрати діапазон даних діаграми, здійснити перехід рядок/стовпець, редагувати записи легенди та підписи осей.

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

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

Для зміни візуально стилю діаграми доцільно скористатися послугою Знаряддя для діаграм - Конструктор - Стилі діаграм.

Цей крок здійснюються за допомогою вкладки Конструктор - Розташування - Перемістити діаграму. При цьому на екрані відкривається вікно (Рис. 3.11) Переміщення діаграми, в якому можна обрати варіант розміщення діаграми.

Скориставшись одним з двох перемикачів зазначеного вікна - окремому чи наявному, діаграму можна розмістити на окремому робочому аркуші книги, або, як вбудований графічний об'єкт, на поточному аркуші.

Щоб розмістити діаграму на окремому робочому аркуші, досить увімкнути відповідний перемикач, а потім (за бажанням) замінити системне ім'я Діаграма 1 на ім'я, задане користувачем.

Для розміщення діаграми як вбудованого графічного об'єкта на одному з аркушів робочої книги потрібно ввімкнути перемикач наявному, а потім вибрати ім'я цього аркуша зі списку, що активується.

Редагування діаграм Після вставлення діаграми до основних вкладок додається контекстне меню Знаряддя для діаграм, до якого відносяться Конструктор (Рис. 3.8), Макет (Рис. 3.12), Формат (Рис. 3.13). За допомогою інструментальних засобів, що містяться в цих вкладках, можна поліпшити зовнішній вигляд діаграми, зробити її більш наочною та привабливішою.

Розміри будь-якої діаграми можна змінювати також "буксуванням" її кадрових маркерів у відповідних напрямках, а розташування на екрані - "буксуванням" її кадру. Форматування ділянок діаграм У кожній діаграмі розрізняють дві ділянки: ділянку діаграми й ділянку побудови діаграми. Перша - це простір, обмежений зовнішньою рамкою діаграми, друга - простір між осями координат Х та У. Форматування об'єктів в межах зазначених ділянок виконується окремо. Для цього будь-яку з ділянок активізують натисканням правої кнопки миші у довільній точці зазначеної ділянки. На екрані з'явиться відповідне вікно форматування об'єкта, в якому можна вибрати і здійснити необхідні за бажанням користувача дії з певним об'єктом діаграми. Якщо активізувати ділянку, обмежену зовнішньою рамкою діаграми, з'являється можливість відкрити вікно Формат області діаграми (Рис. 3.14) з п'ятьма вкладками - Зиливка, Колір межі, Стиль межі, Тінь, Формат об'ємної фігури. Вкладка Заливка дає змогу встановлювати спосіб і колір заливки області діаграми: без заливки, суцільна заливка з можливістю обрання коліру та прозорості, градієнтна (назва заготовки, тип, напрямок, кут, точки градієнта і їх розташування, колір, прозорість), рисунок або текстура (вибір текстури, рисунка, графіки, перетворення рисунка на текстуру, параметри перекривання, вирівнювання, тип віддзеркалення, прозорість) та автоматично.

Вкладка Колір межі забезпечує вибір коліру для елементів ділянки діаграми: без ліній, суцільна лінія, градієнтна лінія, автоматично. Вкладка Стиль межі дозволяє задати ширину межі, тип штриха, обрати складений тип, тип точки та з'єднання, налаштування стрілки та округлення кутів. Вкладка Тінь дає змогу обрати заготовку (без тіні, зовні, у середині, у перспективі), задати колір, прозорість, розмір, розмиття, кут та відстань. Вкладка Формат об'ємної фігури (Рис. 3.14) дозволяє задати рельєф (згори, знизу за потрібною висотою та шириною), глибину, контури та поверхню з вибором матеріалу (стандартні, особливий ефект, напівпрозорі) й освітлення (нейтральні, теплі, холодні, особливі). Якщо активізувати ділянку побудови діаграми (простір між осями координат Х та У), з'явиться вікно Формат області побудови, яке містить такі ж вкладки як Формат області діаграми. Контекстне меню редагування діаграм

Редагування об'єктів, що належать до ділянки діаграми або ділянки побудови діаграми редагують інструментальними засобами відповідних контекстних меню (Рис. 3.15). Ці меню викликають натисканням правої кнопки миші на ділянці діаграми або на ділянці її побудови, коли вони виділені. Так, до контекстного меню ділянки діаграми входять: - три добре відомих послуги - Вирізати, Копіювати, Вставити; - послуги Відновити відповідність стилю, Шрифт; - послуга Формат області діаграми і послуги, які практично реалізують функції вкладки Конструктор (Змінити тип діаграми, Вибір даних, Перемістити діаграму). Ці послуги дозволяють редагувати діаграму на будь-якому етапі роботи з нею; - параметри - На передній план, На задній план забезпечують висування ділянки щодо таблиці даних тощо. Контекстне меню ділянки побудови діаграми має невелику кількість команд, однак його можливості щодо редагування приблизно такі самі, як і меню ділянки діаграми. Вибір і форматування об'єктів діаграми Будь-який об'єкт діаграми, що підлягає форматуванню, потрібно заздалегідь виділити. Це потребує охайності і точності, тому що багато об'єктів мають порівняно невеликі розміри і встановити точно покажчик миші на їх маркери не завжди вдається. Тоді доводиться виділення об'єкта повторювати. Розрізняють об'єкти, що мають вигляд окремих (точкових) елементів даних, та об'єкти, яким відповідають послідовності (низки) даних. Форматування перших можна здійснювати окремо, других - одночасно. Для виділення об'єкта точкового характеру, наприклад, назви, легенди або написів, потрібно: - натиском лівої кнопки миші активізувати його маркери, що мають вигляд невеликих чорних квадратиків; - натиснути правою кнопкою миші на будь-якому з маркерів; - активізувати команду Формат... в контекстному меню, що з'явиться; - вибрати в робочому вікні потрібну вкладку, а в ній - послугу; - натиснути кнопку ОК. Для виділення об'єкта типу послідовності (низки) даних потрібно: - натиснути ліву кнопку миші на будь-якому місці об'єкта активізуючи його маркери; - натиснути правою кнопкою миші на будь-якому з маркерів; - активізувати команду Формат ряду даних в контекстному меню, що з'явиться; - вибрати в робочому вікні потрібну вкладку, а в ній - певну послугу; - натиснути кнопку ОК. Форматування осей діаграми В Excel масштаб значень по осях Х та Y, а також координати точки перетину осей (0,0) встановлюються автоматично. У випадках, коли потрібно скорегувати параметри осей, належить: - натиснути праву клавішу миші на потрібній осі; - обрати з контекстного меню, що відкриється, команду Формат осі; - у вікні Формат осі (Рис. 3.16) вибрати потрібну вкладку, а в ній - певну послугу або перемикач; - зробивши всі налаштування закрити вікно.

3.6. Редагування в Excel Табличний процесор Excel орієнтовано на роботу з даними, що весь час змінюються. Для цього його оснащено могутніми засобами редагування, локалізації та виправлення помилок у робочих аркушах. Програма дає змогу без особливих зусиль вилучати, копіювати, вирізувати, вставляти і переміщати клітинки та діапазони клітинок. Редагування даних клітинок Безпосередньо в клітинках редагують дані, довжина яких сумісна з їхніми розмірами. Якщо дані в поточній клітинці неправильні, то їх можна легко вилучати натисканням на клавішу Delete, або за допомогою команд Основне - Редагування - Очистити, або введенням нових даних. Однак, якщо дані клітинок довгі, то для їх редагування краще скористатися рядком формул. Скасування і повторення останньої операції В Excel передбачена можливість скасувати останню операцію та відновити початковий стан клітинок робочого аркуша. Оперативно скасовують останню операцію за допомогою кнопки Скасувати ввід в панелі швидкого доступу або клавішами Ctrl+Z. Повторити останню виконану операцію, тобто відновити скасовану дію, можна кнопкою Повернути в панелі інструментів або клавішами Ctrl+Y. Скасування останньої операції та її відновлення виконуються командами Скасувати ввід і Повернути ввід із панелі швидкого доступу. Використовуючи вказані кнопки та послуги, можна переглянути робочий аркуш (клітинку) до і після виконання певної дії. Вставлення і видалення стовпців, рядків і клітинок Для вставлення порожнього стовпця треба виділити стовпець таблиці, перед яким є намір його розмістити, і активізувати команду Вставлення - Клітинки - Вставити. Вставлення стовпців (рядків) супроводжується автоматичним налаштуванням формул таблиці за місцем їх нового розміщення. Вилучення об'єктів робочого аркуша здійснюється через послугу Вставлення - Клітинки - Видалити, яка активізує вікно різних варіантів видалення (Рис. 3.17). За структурою воно аналогічне вікну Вставити клітинки.

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

До поля цього вікна водять фрагмент пошуку. Це може бути частина формули, заголовка або тексту, а також характерне слово або окремий символ. Власне пошук здійснюється через команду Знайти далі, яка дає змогу виявляти заданий об'єкт у межах робочого аркуша. У вкладці Знайти задають також режими й ділянки пошуку. Після виявлення потрібного фрагмента послугою Знайти вікна Пошук і заміна активізують вкладку Замінити, до якого і вводять нову редакцію фрагмента. Послуговуючись командою Замінити, вибраний фрагмент замінюють новим. Пошук наступного фрагмента поновлюється командою Знайти далі, після чого процес заміни повторюється до кінця робочого аркуша. Перейти до наступного циклу пошуку можна також за допомогою клавіш Shift+F4, а повернутися до попереднього - клавіш Ctrl+Shift+F4. Процедура пошуку і заміни прискорюється командою Замінити все, яка виправляє вибраний фрагмент по всьому робочому аркушу. Теми Office і стилі Excel 2007 У Office Excel 2007 можна швидко відформатувати дані на аркуші за допомогою теми й певного стилю. Теми можна використовувати також у інших програмах Випуск Office 2007, зокрема, Microsoft Office Word і Microsoft Office PowerPoint, а стилі призначені для змінення формату тільки елементів Excel, тобто таблиць, діаграм, зведених таблиць, фігур, схем. Тема - це попередньо визначений набір кольорів, шрифтів, ліній та різновидів заливки, який можна застосувати до всієї робочої книги або до окремих елементів, наприклад, діаграм або таблиць. За допомогою тем можна створювати документи, які матимуть чудовий вигляд. Можна скористатись однією із тем, визначених у Excel, або ж створити власну тему, що надасть єдиний професійний вигляд усім робочим книгам Excel та іншим документам MS Office 2007. Під час створення теми користувач має змогу змінювати колір, шрифт і різновиди заливки та налаштовувати кожний із цих параметрів окремо. Стиль - це попередньо визначений формат, створений на основі теми, який можна застосувати для змінення вигляду таблиць Excel, діаграм, зведених таблиць, форм або схем. Якщо вбудовані попередньо визначені стилі не відповідають вимогам користувача, він має можливість налаштувати власний стиль. Для діаграм можна вибрати один із багатьох попередньо визначених стилів, але створювати власні стилі діаграм не можна. Як і в Excel 2003, для форматування виділених клітинок використовуються стилі клітинок, але тепер можна швидко застосувати попередньо визначений стиль клітинок. Більшість стилів клітинок не основані на темі, застосованій до робочої книги, і можна легко створити власний стиль. У MS Office 2007 можна використовувати умовне форматування для створення візуальних приміток до даних для аналізування. Щоб легко знайти винятки та виявити важливі зміни в даних, можна запровадити й використовувати різні правила умовного форматування, які застосовують розширене візуальне форматування у формі градієнтних кольорів, лінійок даних і наборів піктограм до даних, що відповідають заданим правилам. Умовними форматами дуже легко користуватися. Їх можна копіювати, застосовувати по декілька правил до одного діапазону, задаючи пріорітети форматування. За необхідно правила умовного форматування можна видалити. Нові формати файлів у Microsoft Office Excel 2007 У Microsoft Office 2007 Microsoft вводить нові формати файлів для Word, Excel і PowerPoint, які мають назву формати Office Open XML. Ці нові формати файлів сприяють інтеграції з зовнішніми джерелами даних, а також зменшують розмір файлів і покращують відновлення даних. У Office Excel 2007 формат робочої книги Excel за промовчанням є форматом Office Excel 2007 із підтримкою XML (XLSX). Окрім цього, є ще такі формати з підтримкою XML: формат Office Excel 2007 із підтримкою XML із увімкнутими макросами (XLSM), формат Office Excel 2007 для шаблонів Excel (XLTX) і формат Office Excel 2007 для шаблонів Excel із увімкнутими макросами (XLTM). Окрім нових форматів файлів на основі XML, Office Excel 2007 також вводить двійкову версію сегментованого стисненого формату файлів для великих або складних робочих книг. Цей формат файлів, бінарний (або BIFF12) формат Office Excel 2007 (XLS), можна використовувати для оптимізації продуктивності й зворотної сумісності.