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

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

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

Якщо при копіюванні формули необхідно зберегти всі або деякі адреси клітинок без змін, то використовується абсолютна адресація. Для створення абсолютної адреси клітинки перед іменем стовпчика і/або перед номером рядка ставиться знак $ (знак долара). Наприклад, адреса $A$8 є абсолютною клітинки А8.

Іноді при копіюванні в формулах використовують змішанні адреси.

Наприклад, змішаними адресами є:

$A2 – адреса стовпчика A – абсолютна (стовпчик A не буде змінюватися при копіюванні), а адреса рядка 2 – відносна (номери рядків будуть змінюватися).

B$4 – адреса стовпчика B – відносна (адреси стовпчиків будуть змінюватися), а адреса рядка 4 – абсолютна (рядок 4 не змінюється при копіюванні).

Під час введення формул, в яких використовуються значення клітинок, їх адреси вводяться в формулі за допомогою клавіатури (вводиться назва колонки і номер рядка) або виділенням виказувачем миші потрібної клітинки (в формулу автоматично буде введена адреса виділеної клітинки). Для введення в формулу абсолютної адреси клітинки можна послідовно з клавіатури ввести символ $ потім назву колонки, символ $ і номер рядка або будь-як ввести адресу клітинки і натиснути на клавішу F4, після чого відносна адреса буде перезаписана комп’ютером абсолютною. Наприклад, потрібно обрахувати вартість кожного товару в умовних одиницях, курс умовної одиниці вказаний у клітині F2 (рис. 14).

 

 

Рис. 14

Таблиця у формульному вигляді (рис. 15).

Рис. 15

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

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

Для виклику майстра функцій потрібно вибрати із Головного меню Вставка – Функция… або натиснути на кнопку в рядку формул або на панелі інструментів «Стандартная». В Мастере функций (рис. 16) всі функції розбиті на категорії: математичні, статистичні, логічні тощо. При виборі потрібної категорії у вікні Выберите функцию: з’являються всі функції, що належать до цієї категорії.

Виклик майстра функцій можна здійснити також за допомогою випадаючого списку, що з’являється у лівому кутку рядка формул після натиснення у правій частині рядка формул знака дорівнює «=» (рис. 17).

 

Рис. 17

 

Рис. 16

 

Наприклад, для обчислення суми в клітині B6 (рис. 18) потрібно:

1. Виділити клітину B6, натиснути

2. В діалоговому вікні вибрати Категория: Математические, Функция: СУММ, ОК.

Рис. 18

3. В наступному діалоговому вікні (рис. 19) вказуємо діапазон клітин B2:B5, тобто діапазон клітин, значення яких потрібно просумувати.

Рис. 19

У клітині B6 з’являється результат обчислення, а у рядку формул наступна формула = СУММ(B2: B5)

При обчисленні середнього, мінімального та максимального значень вибирають Категория: Статистические, Функция: СРЗНАЧ, МИН, МАХ відповідно, а в якості діапазону вказують B2:B5

Результат обчислень в режимі формул представлений на рис. 20.

 

Рис. 20

Функції ЕСЛИ, И та ИЛИ

Принцип дії більшості логічних функцій Excel полягає в перевірці деякої умови і виконання в залежності від неї тих або інших дій. Функція ЕСЛИ виконує перевірку умови, що задається першим аргументом логічного виразу:

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

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

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

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

Зауваження: до 7 функцій ЕСЛИ можуть бути вкладені одна в одну як значення значение_если_истина і значение_если_ложь.

 

Наприклад, якщо ціна за одиницю перевищує середнє значення ціни, то товар належить до 1-ої групи, інакше – до 2-ої (див. табл.1).

 

Порядок виконання:

1. Активізуємо клітину G2, , в діалоговому вікні вибираємо Категория: Логические, Функция: ЕСЛИ, ОК.

2. В наступному діалоговому вікні (рис. 21) вказуємо умову та дії, якщо умова виконується або не виконується.

 

Рис. 21

Результат обчислень представлений на рис. 22.

 

Рис. 22

Додаткові логічні функції:

Функція И використовується тоді, коли потрібно перевірити виконання декількох умов одночасно. Одне з найбільш частіших використовувань функції И - є перевірка чи попадає число х до діапазону від х1 до х2.

=И(логическое_значение1; логическое_значение2;...)

Функція И приймає від 1 до 30 аргументів (в Office 2007 -- до 256), повертає значення ИСТИНА, якщо всі аргументи дійсні, та ЛОЖЬ, якщо хоч один аргумент недійсний.

 

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

 

Порядок виконання:

1. Активізуємо клітину Н2, , в діалоговому вікні вибираємо Категория: Логические, Функция: ЕСЛИ, ОК.

2. В наступному діалоговому вікні заповнюємо поля Значение_если_истина та Значение_если_ложь (рис. 23), ставимо курсор у поле Лог_выражение та натискаємо на у лівій частині рядка формул, у розкриваючому списку вибираємо Другие функции…, вибираємо Категория: Логические, Функция: И, ОК.

3. В діалоговому вікні заповнюємо (рис. 24) відповідні поля і натискаємо ОК.

Рис. 24

Рис. 23

Результат представлений на рис. 25

 

Рис. 25

Функція ИЛИ

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

=ИЛИ(логическое_значение1; логическое_значение2;...)

Функція ИЛИ приймає від 1 до 30 аргументів (в Office 2007 -- до 256), повертає значення ИСТИНА, якщо хоч би один із аргументів є ИСТИНА.

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

Вставка вкладених функцій

В одну функцію можна вставити іншу функцію. Дозволяється до 7-ми рівнів вкладення функцій (в Office 2007 - до 64). Існує дуже зручний спосіб вкладення функції – спеціальна кнопка у лівій частині рядка формул, що відкриває розкриваючий список, в якому відображуються функції, що недавно використовувалися і пункт Другие функции…

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

Рис. 26

Виконання:

1. Активізуємо клітину C2, , в діалоговому вікні вибираємо Категория: Логические, Функция: ЕСЛИ, ОК.

2. Заповнюємо перші два вікна відповідними значеннями (рис.27), ставимо курсор у третє вікно і натискаємо кнопку , у розкриваючому списку (рис. 28), вибираємо функцію ЕСЛИ, ОК.

3. З’являється діалогове вікно функції ЕСЛИ, яка буде вкладеною функцією і заповнюємо його відповідно умові (рис. 29).

Рис. 27

Рис. 28

Рис. 29

 

Результат виконання представлений на рис 30.

Рис. 30

Умовне форматування

При умовному форматуванні використовується КМ Формат - Условное форматирование… За допомогою цього інструмента Excel сам форматує клітини в залежності від умови.

Наприклад, потрібно щоб вартість товару що перевищує 30 грн. мала червоний колір, тип шрифту полужирный курсив (див. табл. 1).

 

Виконання:

1. Виділяємо клітини D2: D5

2. КМ Формат - Условное форматирование… У діалоговому вікні Условное форматирование вибираємо умову больше (рис. 31).

Рис. 31

Праворуч вказуємо значення 30 (рис. 32).

Рис. 32

Натискаємо кнопку Формат… та вибираємо потрібний червоний колір та тип накреслення шрифту (рис. 33), кнопка ОК. У діалоговому вікні Условное форматирование знову натискаємо кнопку ОК.

Рис. 33

Натиснувши на кнопці А также >> можна задати ще одну умову для умовного форматування.

Результат умовного форматування представлено на рис. 34.

 

 

Рис. 34

 

Зауваження: в MS Excel версії до 2003 включно можна використати не більше трьох умов.

Робота з функціями списків

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

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

Функція СЧЕТЕСЛИ (діапазон;критерій) підраховує кількість елементів у діапазоні, які задовольняють критерію.

Діапазон записується у вигляді блока, критерій записується у виді текстового рядка, що містить умови.

Наприклад, щоб підрахувати кількість співробітників, у яких кількість дітей більше одного (див. табл. 2).

Виконання:

1. Активізуємо клітину B8, , в діалоговому вікні вибираємо Категория: Статистические, Функция: СЧЕТЕСЛИ, ОК.

2. Вводимо діапазон клітин, що перевіряються та критерій (рис. 35)

Рис. 35

3. Результат представлений на рис. 36

Функція СУММЕСЛИ (діапазон;критерій;діапазон_підсумовування) підсумовує значення в зазначеному діапазоні, з огляду тільки на ті записи, що задовольняють критерію.

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

 

Виконання:

1. Активізуємо клітину B8, , в діалоговому вікні вибираємо Категория: Математические, Функция: СУММЕСЛИ, ОК.

2. Вводимо діапазон клітин, що перевіряються та критерій та діапазон підсумовування (рис. 37).

Рис. 37

3. Результат представлений на рис. 36.

Таблиця 2

Рис. 36

Результат у формульному вигляді має вигляд (рис. 38):

Рис. 38