посилатися на діапазон комірок;
виконувати складні обчислення;
виправляти помилки в формулах.
Вам, напевно, не раз доводилося підраховувати суму або середнє значення стовпців або рядків таблиць. Excel може виконувати з даними комірок аркуша ті ж обчислення, які виконуються за допомогою калькулятора. Формули і функції Ехсе1 здатні обробляти величини як конкретних елементів таблиці, так і цілих блоків (наприклад, рядків або стовпців). Формули Excel можуть бути дуже складні, а результат їх обчислень, в свою чергу, можна використовувати в інших розрахунках. Незаперечною перевагою електронної таблиці є те, що при зміні даних листа результати обчислень моментально оновлюються.
Обчислення в Excel
Всі обчислення в Excel виконуються за допомогою формул, які можна вводити в будь-осередки листа. Якщо вміст комірки починається зі знака рівності (=), Excel вважає, що слідом за цим знаком йде формула, і намагається виконати зазначену операцію. Коли це вдається, в клітинку з такою формулою виводиться результат розрахунку. Якщо щось не так, з'являється повідомлення про помилку. Сама формула відображається в осередку тільки в тому випадку, коли осередок знаходиться в режимі редагування тексту (тобто після подвійного клацання на ній). Якщо осередок просто виділена, то її формула виводиться в рядку формули у верхній частині вікна Excel.
Вправа 1. Введення формул
Найпростішим способом завдання формули є її безпосереднє введення в рядок формул. У цього варіанту є лише один недолік - ви повинні пам'ятати правила побудови формул Excel, назви вбудованих функцій і методи посилань на осередки листа.
Відкрийте фaйл Електронна табліца.xls, створений на попередньому занятті. У цій робочій книзі є лист Формули, змінений в контрольному вправі попереднього заняття. Розкрийте його. Перші сім рядків листа повинні виглядати так, як показано па рис. 10.1, інші осередки будуть порожні. Якщо це не відповідає дійсності, відредагуйте лист.
Клацніть на осередку В10 і введіть формулу = (СЗ-ВЗ) / ВЗ * 100.
У вправі 2 попереднього заняття ви вивчили прийом автозаповнення осередків послідовними назвами місяців і числами. Давайте скористаємося тим же прийомом для додавання однотипних формул.
Мал. 10.1. введення формули
Клацніть на осередку В10 і перетягніть кутовий маркер виділення вправо, щоб виявилися охопленими осередки з В10 по F10.
Щоб результат виводився в процентах, не обов'язково множити його на 100, досить змінити формат даних, виділивши клітинку і клацнувши на кнопці Процентний формат панелі інструментів Форматування.
На перший погляд здається, що в осередку C10: F10 повинна дублюватися формула осередку В 10, але це не так. Excel підтримує механізм відносних посилань на осередки. Коли формула переноситься на одну клітку вправо, точно так же зміщуються всі посилання цієї формули (це вірно і для зміщення формули в будь-яких інших напрямках на будь-яке число клітин). Тобто в осередку С11 з'явиться формула = (D3-C3) / C3 * 100, в осередку D10 - формула = (E3-D3) / D3 * 100 і т. Д. Щоб перевірити це, клацніть на кожному осередку і вивчіть вміст рядка фор • мул. В результаті осередки рядка 10 будуть містити відносний приріст продажів компанії Фантом по місяцях.
Виділіть рядки з третьою по сьому.
Клацанням на кнопці Вирізати (Cut) панелі інструментів Стандартна (Standard) виріжте їх вміст.
Клацніть на осередку А2 правою кнопкою миші і виберіть в контекстному меню команду Вставити. Дані таблиці перемістяться вгору на один рядок. Тепер знову вивчіть формули осередків рядка 10.
При зміщенні осередків з даними за допомогою операцій вирізання та вставки посилання на ці осередки в наявних формулах модифікуються так, що в оновленій таблиці в розрахунках беруть участь ті осередки, в які були переміщені вихідні дані.
Якщо необхідно, щоб знак рівності на початку тексту осередку не сприймався як індикатор формули, а інтерпретувався як символ, поставте перед ним одинарні лапки ( ').
Вправа 2. функції
Excel підтримує безліч стандартних математичних функцій, які можна вставляти в формули. З трьома з них - СУМ (SUM), СРЗНАЧ (AVERAGE) і РАХУНОК (COUNT) - ви познайомилися на попередньому занятті. Щоб навчитися працювати з іншими функціями, виконайте наступні кроки.
Клацніть на осередку Н8 і введіть текст Максимум. Натисніть Tab.
- Фінансові (Financial) - функції для розрахунку амортизації майна, вартості основних фондів, норми прибутку, величини виплат на основний капітал і інших фінансових показників;
- Дата і час (Date Time) - операції прямого і зворотного перетворення дати і часу в текстові рядки. Функції цієї групи перераховані в табл. 10.1;
Мал. 10.2. вставка функції
ТАБЛИЦЯ 10.1. Функції дати і часу
Виберіть в списку Функція (Function Name) пункт МАКС.
Клацніть на кнопці ОК. Відкриється вікно введення діапазону комірок, показане на рис. 10.3.
Мал. 10.3. Вікно введення аргументів
Деякі функції, наприклад SIN, мають тільки один аргумент, оскільки не можна обчислити синус відразу двох чисел. Однак багато функцій, подібні МАКС, здатні обробляти практично необмежені масиви даних. Такі функції можуть сприймати до 30 аргументів, кожен з яких є числом або посиланням на одну або кілька осередків.
В якості аргументів можуть виступати також арифметичні вирази і інші функції, які повертають значення потрібного типу.
За допомогою функції МАКС ви зараз знайдете максимальний рівень продажів за один місяць для компаній Фантом, РИФ і Вікінг.
Мал. 10.4. Вибір осередків для першого аргументу
Клацніть на кнопці в правій частині поля Число 1 (Number 1). Вікно діалогу згорнеться в рядок, відкриваючи доступ до осередків листа.
Виділіть всі числові комірки рядка Фантом. Позначення відповідного діапазону комірок з'явиться в рядку згорнутого вікна діалогу введення аргументів (рис. 10.4).
Клацніть на кнопці рядка аргументу. На екрані знову розгорнеться вікно введення аргументів.
Клацніть на кнопці в правій частині поля Число 2 (Number 2).
Виділіть комірки B4: G4 і знову клацніть на кнопці рядка введення аргументу. Оскільки ви ввели вже два аргументи, Excel автоматично додасть поле введення третього.
Введіть в поле Ч і їв про 3 (Number 3), як показано на рис. 10.5, текст B6: G6. Це діапазон потрібних осередків рядка Вікінг. У вікні діалогу правіше за поля з аргументами демонструються їх реальні значення. Нижче списку чисел відображається результат обчислень.
Мал. 10.5. Три діапазону комірок в якості аргументів функції МАХ
Клацніть на кнопці ОК. В осередку 18 з'явиться максимальне число з діапазону, записаного в рядках 2,4 і 6. Сама формула з'явиться в рядку формул у верхній частині вікна Excel. Клацніть в цьому рядку. Три аргументи функції МАКС, що задають три діапазону комірок, будуть виділені різними кольорами, а відповідні групи осередків листа Excel виявляться обведеними рамками відповідних кольорів, як показано на рис. 10.6.
Мал. 10.6. Групи осередків, які виступають в якості аргументів формули
Якщо ви добре знайомі з синтаксисом функцій і формул Excel, то можете просто вводити формули в осередки, як це робилося на попередньому занятті, не користуючись вікнами діалогу вибору функції і введення аргументів.
Вправа 3. Діапазон осередків
Для посилання на дані осередків листа в Excel використовуються імена клітин, що складаються з букви стовпця (або двох букв, якщо стовпців більше 26) і номера рядка. Так як багато функцій можуть сприймати в якості аргументів цілі масиви даних, потрібно вивчити правила посилань на такі масиви.
Клацніть на осередку Н9 і введіть текст Сума. Потім натисніть клавішу Tab.
Введіть символи = СУММ () (= SUM ()). Ця формула обчислює суму всіх величин, зазначених в дужках як аргумент.
Клацніть в рядку формул і помістіть курсор між двома дужками.
Введіть в дужки текст В: В.
Клацніть на кнопці Введення зліва від рядка формул. В осередку 19 з'явиться сума всіх комірок стовпчика В, так як символи В: В позначають всі комірки стовпчика В.
Знову клацніть в рядку формул. У таблиці виявиться виділеної та група осередків, які описуються аргументом В: В, як показано на рис. 10.7.
Повторіть кроки з 3-го по 6-й, по черзі вводячи в дужки формул значення з першого стовпця табл. 10.6. Вивчіть, які діапазони осередків відповідають зазначеним умовним позначенням. Трохи потренувавшись, ви зможете вказувати в якості аргументу 4> ункции будь-які групи осередків листа. Якщо в різних формулах часто доводиться посилатися на одну і ту ж групу осередків, особливо якщо в групу входять розрізнені осередки з різних областей листа, їй зручно привласнити спеціальне ім'я.
Протягуванням миші виділіть клітинки B3: G3.
Натисніть Ctrl і протягнете покажчик миші по осередках B5: G5.
Мал. 10.7. Стовпець В як аргумент функції
ТАБЛИЦЯ 10.6 .Способи посилання на групи осередків
Змініть вміст комірки так, щоб вийшла формула СРЗНАЧ (В 11: F11) (AVERAGE (B11: F11)).
Вправа 6. Застосування функцій
До сих пір ви користувалися тільки функціями СУМ, СРЗНАЧ, РАХУНОК і МАКС. Давайте розглянемо на прикладі деякі функції з розряду текстових і логічних, а також функції роботи з датою і часом. Результат всіх обчислень, які будуть виконані в цій вправі.
У листі Формули виділіть і скопіюйте комірки Bl: Gl.
Розгорніть лист ЛістЗ (Sheet3).
Клацніть правою кнопкою миші на осередку А1 і виберіть в контекстному меню команду Вставити.
Введіть в клітинку A3 формулу = ЛЕВСИМВ (А1; 3) (= LEFT (A1; 3)). Ця формула повертає три лівих символу комірки А1.
Перетягніть кутовий маркер виділення вправо, щоб рамка охопила осередки A3: F3. Тепер в третій рядок виводяться скорочені варіанти назв місяців з осередків першого рядка.
Клацніть наячейкеНЗ і введіть формулу = СЦЕПИТЬ (ВЗ; D3) (CONCATENATE (ВЗ; D3)). В осередку НЗ з'явиться об'єднання рядків лютого і квіт.
У осередок А8 введіть формулу = ТДАТУ () (= NOW ()) і натисніть клавішу Enter. У ній тут же з'являться поточні дата і час.
Введіть в осередку з В8 по В13 формули = ГОД (А8) (= YEAR (A8)), = МІСЯЦЬ (А8) (= MONTH (А8)), = ДЕНЬ (А8) (= DAY (А8)), = ЧАС ( А8) (= HOUR (A8)), = ХВИЛИНИ (А8) (= MINUTE (A8)) і = СЕКУНДИ (А8) (= SECOND (A8)). У цих осередках з'являться окремо всі шість компонентів поточних дати і часу.
Двічі клацніть на осередку А8, щоб перевести її в режим редагування.
Клацніть на кнопці Введення, розташованої зліва від рядка формул. Ця операція призведе до оновлення значення в осередку А8, що вплине і на числа осередків В8: В13.
Тепер давайте виконаємо логічну операцію.
ВведітевячейкуА4формулу = ЕСЛИ (АЗ = "лютого"; "Так"; "Ні") (= IF (АЗ = "лютого"; "Так"; "Н е т")). Ця операція порівнює значення осередку A3 з текстовим рядком лют. У разі рівності виводиться текст другого аргументу - Так. У разі нерівності виводиться текст третього аргументу - Ні. Так як в осередку A3 присутній текст Янв, то результатом цієї операції буде значення Немає.
Розтягніть маркер осередку А4 вправо, щоб рамка охопила діапазон A4: F4. Тепер формули рядка 4 перевіряють осередки рядка 3 на збіг їх вмісту з рядком лют. Як бачите, слово Так з'явилося тільки в стовпці В.
Знаки, які використовуються в Excel для порівняння величин, перераховані в табл. 10.8. Зверніть увагу, що текстові рядки, які виступають в якості аргументів функцій, повинні полягати в подвійні лапки.
Текстові рядки рівні, якщо попарно збігаються всі їхні символи, в іншому випадку рядки не рівні. Одна текстовий рядок більше другий, якщо в ній код першого символу, неспівпадаючого в обох рядках, більше коду того ж символу в другому рядку.
ТАБЛИЦЯ 10.8. знаки порівняння