Створення простих формул

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

В Excel формули починаються зі знака =. Дужки ( ) можуть використовуватися для визначення порядку математичної операції.

Excel підтримує наступні арифметичні операції:

• додавання (+);

• множення (*);

• знаходження відсотка (%);

• віднімання (–);

• ділення (/);

• піднесення до степеню (^).

Оператори порівняння:

• = дорівнює;

• [ менше;

• ] більше;

• [= менше або дорівнює;

• ]= більше або дорівнює;

• [] не дорівнює.

Оператори зв'язку:

• : діапазон;

• ; об'єднання;

• & оператор з'єднання текстів.

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

Автосума

Кнопка [Автосумма] – Σ може використовуватися для автоматичного створення формули, що підсумує область сусідніх комірок, що перебувають безпосередньо ліворуч у даному рядку й безпосередньо вище в даному стовпчику.

Функція „Автосумма” автоматично трансформується у випадку додавання й видалення комірок усередині області. Тиражування формул за допомогою маркера заповнення Область комірок (комірка) може бути розмножена за допомогою використання маркера заповнення. Маркер заповнення являє собою контрольну точку в правому нижньому куті виділеної комірки.

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

Відносні й абсолютні посилання

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

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

Абсолютне посилання на комірку або область комірок буде завжди посилатися на ту саму адресу рядка й стовпчика. При порівнянні з напрямками вулиць це буде приблизно наступне: „Йдіть на перетинання вулиці Миру та Бериславського шосе”. Поза залежністю від місця старту це буде приводити до одного й того ж місця. Якщо формула вимагає, щоб адреса комірки залишалась незмінною при копіюванні, то повинно використовуватися абсолютне посилання (формат запису $А$1).

Імена у формулах

Імена у формулах легше запам’ятати, чим адреси комірок, тому замість абсолютних посилань можна використовувати іменовані області (одна або кілька комірок). У меню Вставка, Имя існують дві різні команди створення іменованих областей: Создать і Присвоить.

Команда Создать дозволяє задати (ввести) необхідне ім’я (тільки одне), команда Присвоить використовує мітки, розміщені на робочому аркуші, як імена областей (дозволяється створювати відразу кілька імен).

Функції в Excel

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

УМОВНИЙ ОПЕРАТОР

У деяких завданнях залежно від отриманих результатів доводиться виводити на екран різні повідомлення. У більш загальному випадку в залежності від вихідних даних і проміжних результатів доводиться змінювати алгоритм розрахунків (використовувати різні формули). Подібний вибір в електронній таблиці Excel досягається застосуванням логічної функції ЕСЛИ, що повертає одне значення, якщо задана умова при обчисленні дає значення ИСТИНА, і інше значення, якщо та ж умова дає значення ЛОЖЬ. Функція ЕСЛИ використовується при перевірці умов для значень і формул.

Синтаксис

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение – це будь-яке значення або вираз, що приймає значення ИСТИНА або ЛОЖЬ. Наприклад, A10=100 – це логічне вираження; якщо значення в комірці A10 дорівнює 100, то вираз приймає значення ИСТИНА. У противному випадку – ЛОЖЬ. Цей аргумент може бути використаний у будь-якому операторі.

Значение_если_истина – це значення, що повертається, якщо лог_выражение дорівнює ИСТИНА. Наприклад, якщо цей аргумент – рядок „У межах бюджету” і лог_выражение дорівнює ИСТИНА, тоді функція ЕСЛИ відобразить текст „У межах бюджету”. Якщо лог_выражение дорівнює ИСТИНА, а значение_если_истина порожньо, то повертається значення 0. Щоб відобразити слово ИСТИНА, необхідно використовувати логічне значення ИСТИНА для цього аргументу. Значение_если_истина може бути формулою.

Значение_если_ложь – це значення, що повертається, якщо лог_выражение дорівнює ЛОЖЬ. Наприклад, якщо цей аргумент – рядок „Перевищення бюджету” і лог_выражение дорівнює ЛОЖЬ, то функція ЕСЛИ відобразить текст „Перевищення бюджету”. Якщо лог_выражение дорівнює ЛОЖЬ, а значение_если_ложь опущено (тобто після значение_если_истина немає крапки з комою), то вертається логічне значення ЛОЖЬ. Якщо лог_выражение дорівнює ЛОЖЬ, а значение_если_ложь порожньо (тобто після значение_если_истина стоїть крапка з комою з наступною закриваючою дужкою), то повертається значення 0. Значение_если_ложь може бути формулою.

Логічні функції

Будь-які складні логічні вирази можуть бути представлені у вигляді комбінації трьох логічних функцій И, ИЛИ й НЕ. При цьому И повертає „істину”, якщо істинні всі її аргументи (виконуються всі умови). ИЛИ повертає „істину”, якщо істинний хоч один її аргумент (виконується хоч одна умова). НЕ інвертує „істину” в „неправду”. (Константи „ИСТИНА” і „ЛОЖЬ” також визначені в Excel і можуть набиратися вручну або через „Мастер функций”).

СОРТУВАННЯ

Порядок сортування, що використовується за замовчуванням При сортуванні за зростанням Microsoft Excel використовує наступний порядок (при сортуванні за зменшенням цей порядок заміняється на зворотний за винятком порожніх комірок, які завжди поміщаються в кінець списку).

Числа. Числа сортуються від найменшого від’ємного до найбільшого додатного числа.

Алфавітно-цифрове сортування. При сортуванні алфавітно- цифрового тексту Microsoft Excel порівнює значення по знаках ліворуч праворуч. Наприклад, якщо комірка містить текст „A100”, Microsoft Excel помістить її після комірки, що містить запис „A1”, і перед коміркою, що містить запис „A11”.

Текст, у тому числі числа з текстом, сортується в наступному порядку:

0 1 2 3 4 5 6 7 8 9 (пробел) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | }

~ + [ = ] A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В

Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

Апострофи (’) і дефіси (-) ігноруються з єдиним винятком: якщо два рядки тексту однакові, не вважаючи дефіса, текст із дефісом ставиться в кінець.

Логічні значення.Логічне значення ЛОЖЬ ставиться перед значенням ИСТИНА.

Значення помилки.Всі значення помилки рівні.

Порожні значення.Порожні значення завжди ставляться в кінець.

Фільтрація. Автофільтр

Одним з базових понять електронних таблиць і баз даних є „фільтрація”. Фільтром називається логічне подання даних, коли логічна умова перевіряється стосовно до всього виділеного фрагмента таблиці, і всі рядки, що не задовольняють умові, не показуються на екрані. Це легко помітити, оскільки в заголовку стовпчика з’являється кнопка виклику списку фільтрів.

Розширений фільтр

Більше складні умови відбору даних Excel дозволяє здійснити з використанням „розширеного фільтру”.

РОБОТА З АРКУШАМИ

Ярлики аркушів

Ярлики аркушів розташовуються внизу ліворуч в області робочої книги. Ім'я кожного робочого аркуша з’являється на відповідному ярлику. Однак будь-якому ярлику може бути привласнено інше ім'я, довжиною не більше 31 символу. Це ім’я може бути використане при адресації аркуша у формулах.

Адресація аркуша

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

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

Кожна комірка в Excel має дві координати – номер рядка й номер стовпчика. Оскільки робоча книга складається з декількох робочих аркушів (за замовчуванням із трьох), то комірці можна привласнити третю координату – номер аркуша, що являє собою третій вимір, що дозволяє говорити про об'ємні таблиці.

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

• гнучкість в оформленні робочих аркушів;

• можливість консолідації (об’єднання) даних.

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

ПОБУДОВА ДІАГРАМ

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

Область даних діаграми – це виділена область комірок на аркуші, значення яких використовуються для побудови діаграми. Вона може містити дані разом із заголовками (мітками) рядків і стовпчиків. Мітки рядків і стовпчиків використовуються відповідно для визначення осі X і найменувань груп даних, так званої легенди.

Область числових даних – це комірки, які містять числові дані. Послідовність однотипних даних визначається як ряд. Ряди даних можуть бути задані по рядках або по стовпчиках. Залежно від обраного методу визначення рядів міняються мітки по осі X. Категорії осі X – це область, що містить імена, які являють собою мітки, що поміщаються уздовж осі X (горизонтальної осі) діаграми. У круговій діаграмі категорії осі X використовуються для позначення сегментів кола.

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

Іноді необхідно виключити деякі частини аркуша перед створенням діаграми, тому що вони порожні або містять дані, які повинні бути відсутніми на діаграмі. Найпростіший шлях виключити комірки, які не повинні відображатися на діаграмі, – це натиснути клавішу [Ctrl] при виділенні несуміжних областей комірок.

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

Практична робота №9

Створення файлів нескладних облікових докуме­нтів за допомогою табличного процесора. Редагування комірок електронної табли­ці.

ЗАВДАННЯ 1

1. Створити книгу на основі шаблону „Рассрочка”

2. Заповнити її даними.

ТЕХНОЛОГІЯ РОБОТИ

1. Виберіть команду Создать з меню Файл.

2. Виберіть закладку Решения (в нових версіях Excel Создать при помощи шаблона, Общие шаблоны) для визначення категорії шаблона створюваної робочої книги.

3. Потім виберіть шаблон Рассрочка, на якому буде ґрунтуватися нова робоча книга.

4. Заповніть шаблон наступними даними:

5. Збережіть книгу з ім’ям „Розстрочка за кредитом”, виконавши команду Файл, Сохранить как…

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

Змінити знак можна, виконавши команду головного меню системи: Пуск, Настройка, Панель управления, Язык и региональные стандарты, вкладка Региональные параметры, клацніть по кнопці [Настройка], вкладка Числа, параметр Разделитель целой и дробной части.

ЗАВДАННЯ 2

Створити таблицю за зразком, зберігаючи необхідне форматування:

ВІДОМІСТЬ НА ОДЕРЖАННЯ ЗАРПЛАТИ №

ТЕХНОЛОГІЯ РОБОТИ

1. Створіть нову книгу.

2. В комірку А1 введіть: „ВІДОМІСТЬ НА ОДЕРЖАННЯ ЗАРПЛАТИ”, натисніть ENTER.

3. Встановіть курсор назад в комірку А1. За допомогою команди верхнього меню Формат, Ячейки, Шрифт, установіть наступні параметри для тексту: Times New Roman, 14, напівжирний курсив. Натисніть „ОК”.

4. В комірку А2 введіть: „№”, натисніть ENTER.

5. Установіть курсор назад в комірку А2. За допомогою команди верхнього меню Формат, Ячейки, Выравнивание, встановіть наступні параметри для Ориентация: 90 градусів, Выравнивание по горизонтали: по центру; по вертикали: по центру, виберіть закладку Шрифт, встановіть наступні параметри для тексту: Times New Roman, 14, напівжирний курсив. Натисніть „ОК”.

6. Виділіть комірки з адресами B2-Е2. За допомогою команди верхнього меню Формат, Ячейки, Число, встановіть формат: Текстовий. Тепер у ці комірки можна ввести тільки текстову інформацію. Переключіться в закладку Выравнивание, встановіть наступні параметри: Выравнивание по горизонтали: по центру; по вертикали: по центру, виберіть закладку Шрифт, встановіть наступні параметри для тексту: Times New Roman, 14, напівжирний курсив. Натисніть „ОК”.

7. Внесіть наступну інформацію в комірки:

B2: Прізвище

C2: Нараховано

D2: Податок

Е2: До видачі

8. Виділіть комірки з адресами B3-B5. За допомогою команди верхнього меню Формат, Ячейки, Число, встановіть формат для даних: Текстовий, виберіть закладку Шрифт, встановіть наступні параметри: Times New Roman, 14, звичайний. Введіть прізвища в стовпчик, згідно зразка.

9. Виділіть комірки з адресами А3-А5. За допомогою команди верхнього меню Формат, Ячейки, Число, встановіть формат для даних: Числовой, число десятичных знаков: 0, виберіть закладку Шрифт, встановіть наступні параметри: Times New Roman, 14, обычный. Введіть номери в стовпчик, відповідно до зразка.

10. Виділіть комірки з адресами C3-C5. За допомогою команди верхнього меню Формат, Ячейки, Число, встановіть формат для даних: Числовой, число десятичных знаков: 0, виберіть закладку Шрифт, встановіть наступні параметри: Times New Roman, 14, обычный. Введіть дані в стовпчики, відповідно до зразка.

11. Виділіть комірки з адресами А2-Е5. За допомогою команди верхнього меню Формат, Ячейки, Границы, виберіть: внутренние, внешние. Натисніть „ОК”.

12. Виділіть комірки з адресами А1-E1. Натисніть клавішу

13. Збережіть книгу з ім’ям Відомість.xls

ЗАВДАННЯ 3

У файлі Відомість.xls „залити” шапку таблиці червоним кольором, весь текст, що залишився, зробити теж червоним, границі таблиці зробити пунктиром.

ТЕХНОЛОГІЯ РОБОТИ

1. Відкрийте файл Відомість.xls.

2. Виділіть комірки A2-E2.

3. За допомогою команди верхнього меню Формат, Ячейки, Вид, виберіть колір заливання красный.

4. Виділіть комірки А3-Е5.

5. За допомогою команди верхнього меню Формат, Ячейки, Шрифт, виберіть колір шрифту красный.

6. Виділіть комірки А2-Е5.

7. За допомогою команди верхнього меню Формат, Ячейки, Граница, виберіть колір границі синій, натисніть внутренние, внешние.

8. Виберіть тип лінії.

9. Натисніть ОК.

ЗАВДАННЯ 4

Встановлення захисту.

ТЕХНОЛОГІЯ РОБОТИ

1. Відкрийте файл Відомість.xls.

2. У меню Сервис виберіть команду Защита, а потім команду Защитить лист.

3. Щоб ніхто інший не зміг зняти захист із аркуша, введіть пароль.

4. Щоб зняти захист, у захищеному файлі виберіть команду Сервис, Защита, Снять защиту, введіть свій пароль

Практична робота №10

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

ЗАВДАННЯ 1

Створення таблиці й розрахунок по формулах.

ТЕХНОЛОГІЯ РОБОТИ

1. Створіть нову книгу, збережіть її з ім’ям Практична 3.

2. Створіть таблицю за зразком, зберігаючи необхідне форматування, як показано в табл. 8.

Таблиця 8

Вихідна таблиця даних

3. Виберіть комірку В7, у якій буде обчислена сума по вертикалі.

4. Клацніть кнопку [Автосумма] – Σ та ENTER.

5. Повторіть дії пунктів 3 і 4 для комірок С7 і D7.

6. Обчисліть кількість співробітників без вищої освіти в комірці В8 (по формулі В7-ВЗ):

а) Оберіть комірку В8 і наберіть знак (=).

б) Клацніть мишею в комірці В7, що є першим операндом у формулі.

в) Введіть із клавіатури знак (-) і клацніть мишею в комірці ВЗ, що є другим операндом у формулі (буде введена формула).

г) Натисніть Enter (в комірці В8 буде обчислений результат).

7. Повторіть пункти а)-г) для обчислень по відповідних формулах в комірках С8 і D8.

8. Збережіть аркуш із ім’ям Освіта_співробітників.

Таблиця 9