Загальні відомості про електронні таблиці

Оператор порівняння Дія

= Дорівнює

> Більше

< Менше

>= Більше або дорівнює

<= Менше або дорівнює

<> Не дорівнює

Ці оператори використовуються для присвоєння твердженням значень ІСТИНА або НЕПРАВДА. Оператори порівняння можуть застосовуватися для порівняння тексту, записаного в різні комірки. При цьому потрібно враховувати, що оператор «=» застосовується в змісті збігу чи розбіжності текстів, а оператори «<» і «>» – у змісті  розташування «до» чи «після» в упорядкованому списку текстових фрагментів.

У Excel є один текстовий оператор:

&. (амперсанд) - Об'єднання послідовностей  символів в одну послідовність символів.

Цей оператор застосовується для з'єднання  текстів з різних комірок.

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

5.1.2. Синтаксис формул

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

• Символу =, яким завжди починається запис формули (цей символ вводиться в комірку із клавіатури або викликається клацанням по кнопці «=» у рядку формул);
• Операторів +, –, *, <, > і інших операторів;
• Функцій з набору вбудованих функцій Excel;
• Адрес комірок, посилань на діапазони комірок і імена – ці компоненти присутні, якщо у формулу потрібно підставити значення, що містяться в інших комірках.

Синтаксис формули задає визначену  послідовність обчислень. Після  знака рівності «=» випливає набір  величин, що обчислюються. Якщо у формулу  входить кілька операторів, то Excel визначає пріоритет цих операторів і призначає відповідну послідовність їхнього виконання. Оператори в порядку зменшення пріоритету розташовуються в такий спосіб:

1. Адресні оператори (:, <пропуск>);
2. Унарный мінус (використовується для вказівки негативних чисел, наприклад, –1 чи –0,23);
3. Відсоток ( %);
4. Піднесення до степеня (^);
5. Множення і ділення (* і /);
6. Додавання і віднімання (+ і – );
7. Об'єднання послідовностей символів (&);
8. Оператори порівняння (= < > <= >=  <>).

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

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

5.1.3. Введення і копіювання формул

Введення формули у суміжні  комірки

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

• Активізувати комірку, у якій записана вихідна формула.
• Навести курсор миші на маркер заповнення і протягнути маркер через комірки, у які потрібно скопіювати формулу.
• Відпустити кнопку миші, після чого в комірки будуть занесені копії формул (із зміненими адресами комірок), а вміст комірок буде полічено по цих формулах.

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

Переміщення і копіювання формули  перетаскуванням

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

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

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

5.1.4. Відображення формул на робочому  листі

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

Перейти до режиму відображення усіх формул на екрані можна в такий  спосіб:

• ввійти у меню Сервис;
• вибрати команду Параметры і відкрити вкладку Вид;
• у розділі Параметры окна встановити прапорець Формулы і натиснути ОК.

У цьому режимі стовпці автоматично  розширюються до розміру формул так, щоб формули були краще видні. Якщо потрібно повернутися до колишнього режиму відображення таблиці, то знову виконати команду Сервис → Параметры → вкладка Вид і зняти прапорець Формулы.

5.2. Система адресації в Excel

5.2.1. Адреси Робочої книги, листа,  осередку

Робоча книга в Excel містить безліч аркушів, а кожний лист – безліч комірок. Коли клацнути мишею в якому-небудь місці таблиці, – виділиться конкретна комірка, що належить визначеному листу, розташованому у визначеній Робочій книзі.

Найбільш великим об'єктом у Excel, призначеним для збереження й обробки даних, є Робоча книга. Книга в Excel являє собою файл, а адреса файлу, як відомо, указується за допомогою імені файлу і шляху до нього, наприклад:

С:\Мої документи\Накладна.xls

– це «адреса» файлу Накладна.xls у просторі імен Windows. У системі Excel, щоб звернутися до якої-небудь Робочої книги, потрібно записати посилання на цю книгу-файл. Посилання на Робочу книгу буде нагадувати адресу файлу, однак буде трохи відрізнятися від нього:

С:\Мої документи\[Накладна.xls]

Якщо Робоча книга під ім'ям Накладна уже відкрита в Excel, то посилання на неї записується просто як ім'я файлу в прямих дужках:

[Накладна.xls]

Робочий лист вказується в посиланні  слідом за ім'ям Робочої книги, а  комірка – слідом за ім'ям листа через знак оклику. Наприклад, настпниий запис

[Накладна.xls]Склад!В3

відповідає посиланню (чи адресі) комірці В3, розташованої на листі Склад Робочої книги Накладна.

Описана система адрес зручна, коли в розрахунках беруть участь не одна, а декілька взаємозалежних Робочих  книг.

5.2.2. Діапазони осередків і адресних  операторів

Записи A3:F7 чи JJ12:JN17 відповідають зв'язаним діапазонам комірок, що представляють собою прямокутні області таблиць з однією загальною межею. Коли в посиланні потрібно об'єднати два діапазони комірок, то діапазони записуються через кому, наприклад, A3:F7,В6:К13.

 Двокрапка і кома, використовувані при записі посилань на комірки, називаються адресними операторами. Ще одним адресним оператором у Excel є пропуск. За допомогою пропуску можна посилатися на область перетинання двох діапазонів. Наприклад, запис F1:F8_F5:F13 буде вказуват на комірки від F5 до F8 (тут символ _ позначає порожній пропуск, а не підкреслення).

Приведемо список адресних операторів:

Адресний оператор Дія

: (двокрапка) Об'єднання комірок у прямокутний діапазон

; (крапка з комою) Об'єднання діапазонів

_ (пропуск) Перетинання діапазонів

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

Посилання на комірки інших книг називаються зовнішніми, а на дані інших додатків - вилученими посиланнями. Вживання зовнішнього посилання на незв'язний діапазон комірок може виглядати як:

=СУМ(С:\Річні звіти\[1999.xls]Реалізація!А6:З9,G7:Q7)

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

=СУМ(Лист3:Лист9!С5:Н8)

підсумовує всі значення, що містяться  в діапазоні С5:H8 на аркушах від Лист3 до Лист9 включно.

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

Усі види посилань, що розглядалися, були відносними посиланнями. Цей вид посилань встановлюється в Excel за замовчуванням, і відносність їх виявляється, приміром, у наступному. Якщо записати в комірку A3 формулу =А1+А2 і скопіювали цю формулу в комірку В5, то в підсумку отримається формула =В3+В4. При відносних посиланнях дана вихідна формула представляється як деяка дія, відповідно до якого потрібно обчислити суму двох вищестоящих комірок.

Очевидно, що можливі випадки, коли зміна адрес у посиланнях на деякі  комірки небажана, наприклад, якщо в комірки записані оригінальні дані. Тоді встановлюють абсолютні посилання, що не змінюються при копіюванні формули. Абсолютні посилання відрізняються від відносних наявністю знака долара $. Наприклад, розглянута попередня формула, записана в абсолютних посиланнях, буде виглядати як =$А$1+$А$2, і ця формула не буде змінюватися при копіюванні або переміщенні в будь-який осередок.

У розрахунках можна також використовувати  абсолютні посилання тільки на рядок  або тільки на стовпець. Тоді посилання  буде виглядати, приміром, як А$1 чи як $А1 відповідно. Це так званні змішані посилання, що дозволяють "зафіксувати" рядок або стовпець.

Щоб змінити відносне посилання  на абсолютне, не обов'язково вручну розставляти  знаки згаданої грошової одиниці. Існує  простий спосіб зміни посилань за допомогою клавіші F4, що діє як чотирикроковий перемикач. Коли вводиться абсолютне посилання у формулу, необхідно активізувати потрібну комірку, а потім за допомогою послідовних натискань F4 вибрати форму запису посилання з ряду типу А1 - $А$1 - А$1 - $А1.

5.3. Функції

5.3.1. Синтаксис функцій

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

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

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

МАКС(число!,число2,...)

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

Дужки, що обмежують список аргументів, завжди повинні бути парними; пропуски перед дужками або після них не допускаються. Список аргументів може складатися з чисел, посилань, тексту, масивів, логічних величин (ІСТИНА чи НЕПРАВДА), значеннь помилок (наприклад, #ДЕЛ/0). Окремі аргументи в списку повинні бути розділені комами. Деякі функції можуть мати до 30 аргументів.

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

СУМ(А1:С5,МОПРЕД(О1:Е3))

використано вкладену функцію МОПРЕД, що повертає значення визначника матриці осередків О1:E3. Рівень вкладеності функцій у формулах Excel не повинний перевищувати семи.

5.3.2. Введення функцій

Функції вставляються у формули, а  останні починаються зі знаку =. Якщо формулу почати з назви функції, то знак рівності буде підставлений автоматично. Функція включається у формулу за допомогою Майстра функцій, що спрощує процес вставки функції в рядок формул. Викликається Майстер через меню Вставка → Функция.

Діалог Мастер функций містить два списки: Категория і Функция.

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

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