Створення формул та аналіз даних – ЧАСТИНА 1

Формули – душа і серце робочого листа, і Microsoft Excel пропонує цілий арсенал засобів для їх створення Озброївшись всього лише кількома математичними операторами і навчившись правильно вводити формули в комірки, ви легко перетворите звичайний робочий лист на потужний засіб для проведення різних математичних обчислень

Основні відомості про формули

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

Щоб побачити, як працюють формули, давайте створимо кілька найпростіших прикладів Почнемо з того, що виділимо осередок, наприклад, А10 Тепер наберемо в ній:

= 10 +5 і натиснемо клавішу Enter У комірці зявиться значення 15 Якщо виділити вічко А10, в рядку формул можна побачити саму формулу, введену в цей осередок Отримане значення є результатом обчислень, а в рядку формул показується сама формула

Пріоритет операторів

Операторами (Operators) називаються символи математичних операцій, включаючи знаки додавання (+), віднімання (-), множення (*) і ділення (/) Операторам з великим пріоритетомпри виконанні обчислень віддається першість При цьому Excel керується такими правилами:

► в першу чергу проводяться обчислення значень в дужках

► множення і ділення виконуються перш додавання і віднімання

► оператори з рівним пріоритетом обробляються в порядку зліва направо

Виділіть порожню комірку і введіть в неї = 4 +12 / 6 Натисніть Enter, і в комірці зявиться обчислене значення 6 Тут Excel спочатку робить розподіл двох операндів -12 на 6, а потім до отриманого значення додає 4 Якби порядок обчислень був інший, то і результат був би зовсім відмінний від отриманого Виділіть ще одну порожню комірку і введіть в неї формулу = (4 +12) / б Натисніть Enter в комірці відобразиться значення 2,666667 Цей приклад показує, як дужки впливають на пріоритет обчислень у формулах Ще кілька випадків продемонстровано в табл 121, де формули складаються з одних і тих же операндів і операторів, але підсумок обчислень залежить від розташування дужок

Таблиця 121 Вплив дужок на результат обчислень Формула Результат

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

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

РАДА

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

Використання у формулах посилань

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

Наприклад, виділіть клітинку А1 і введіть в неї формулу = 10 * 2 Тепер виділіть комірку А2 і наберіть в ній = А1 В обох осередках ви побачите число 20 Якщо ви зміните значення в комірці А1, то і значення в комірці А2 також зміниться Тепер виділимо осередок A3 і введемо в неї формулу = А1 + А2 Excel поверне значення 40 Надалі ви переконаєтеся, наскільки корисні посилання при створенні та використанні складних формул

Введення посилань за допомогою миші

Ви значно заощадите час і уникнете безлічі помилок, якщо будете вводити у формули посилання на комірки за допомогою миші, а не набирати їх текст на клавіатурі Наприклад, щоб ввести в комірку В10 формулу з посиланнями на комірки А9 і А10, виконайте такі дії:

1 Виділіть клітинку В10 і введіть знак рівності

2 Клацніть на осередку А9 і введіть знак плюса

3 Клацніть на осередку А10 і натисніть клавішу Enter

Коли ви клацаєте на осередку, навколо неї зявляється «біжучий» рамка, а в рядку формул відображається посилання на цей осередок Закінчивши введення формули, натисніть Enter Якщо ви не натиснете Enter і виділіть-яку іншу клітинку, Excel сприйме це як продовження введення в формулу ще одного посилання на клітинку

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

РАДА

Якщо після прокрутки активна комірка пропала з екрану, для того щоб вона знову ока-

залась в межах видимості, натисніть клавіші Ctrl + Backspace

Відносні, абсолютні і змішані посилання

Відноснапосилання (relative reference) вказує на місце розташування осередку щодо активної комірки, наприклад: «на два рядки вище» У попередніх прикладах ми користувалися саме такими посиланнямиАбсолютнапосилання (absolute references) вказує фіксоване положення клітинки на аркуші, наприклад: «осередок, розташована на перетині стовпця А і рядки 2» Нарешті, змішана посилання (mixed reference) є поєднанням відносної й абсолютної посилань, наприклад: «осередок знаходиться в стовпці А і на два рядки вище» Абсолютні s і змішані посилання особливо корисні при копіюванні формул з одного місця листа в інше При виконанні копіювання і вставки відносні посилання підлаштовуються автоматично, на відміну від абсолютних До цього ми ще повернемося більш докладно в розділі «Поведінка посилань при копіюванні формул»

Відносне посилання на клітинку А1 записується таким чином: = А1 Абсолютне посилання на цю ж комірку має інший вигляд: = $ А $ 1 Комбінуючи абсолютні і відносні посилання, можна створити такі два варіанти змішаних посилань:

= $ А1 і = А $ 1

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

За допомогою клавіші F4 можна швидко змінити тип посилання:

1 Виділіть клітинку А1 і введіть в неї формулу = В1 + В2 (не натискайте поки клавішу

Enter)

2 Натисніть клавішу F4, щоб відносну посилання, що знаходиться поруч з мер-

цающім курсором, змінити на абсолютну Формула прийме вид = В1 + $ В $ 2

3 Знову натисніть клавішу F4, і посилання з абсолютної перетворюється в змішану наступним чином: = В1 + В $ 2 (відносна координата стовпця і абсолютна координата рядка)

4 Нарешті, ще раз натиснувши F4, ви звернете цю змішану посилання, зробивши координату стовпця абсолютної, а рядки – відносною Тепер формула виглядатиме так: = В1 + $ В2

5 Якщо ще раз натиснути F4, формула набере початкового вигляду: = В1 + В2

ПРИМІТКА –

Разом з книгою поширюється демонстраційна версія надбудови Spreadsheet Assistant, яка включає в себе ряд корисних інструментів У тому числі меню Assistants (Допомога), а в ньому підміню Formulas & Functions (Формули і функції), команди якого працюють з числами, наприклад виконують перетворення «текстових чисел» в «числові числа» і округлення значень в виділених осередках Команда Hide Formula Errors (Приховати помилкові значення) цього меню відкриває діалогове вікно, що дозволяє вказати діапазон комірок, в якому всі «помилкові» значення не будуть відображатися

Посилання на інші аркуші тієї ж книги

При роботі з формулами ви вправі використовувати посилання не тільки на осередки поточного листа, але і на будь-які інші осередки поточної книги Наприклад, щоб ввести в комірку

В10 аркуша Лист1 посилання на комірку А9 листа Лист2, виконайте такі дії:

1 Виділіть клітинку В10 на аркуші Лист1 і введіть знак рівності

2 Активізуйте Лист2, клацнувши на ярличку цього листа в нижній частині вікна

3 Клацніть на осередку А9 і натисніть клавішу Enter

Після цього активним знову стане Лист1 Виділивши комірку В10, ви побачите в рядку формул вміст цього осередку: = Лист2 А9

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

Посилання на листи інших книг

Точно так само, як ви посилаєтеся на осередки листів поточної книги, ви можете посилатися і на клітинки інших книг Такі посилання називаються зовнішніми (External) Наприклад, щоб ввести посилання з книги Книга1 на клітинку книги Книга2, необхідно виконати наступні дії:

1 Створіть нову книгу – Книга2, натиснувши кнопку Створити (New) панелі інструментів

Стандартна (Standard)

2 Виберіть у меню Вікно (Window) команду Розташувати (Arrange) і потім встановіть перемикач в положення Зліва направо (Vertical)

3 Виділіть клітинку А1 на аркуші Лист1 книги Книга1 і введіть знак рівності

4 Активізуйте Кнігу2

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*