Створення формул. Майстер функцій. Категорії функцій. Виконання функцій з даними з різних аркушів

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

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

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

   Якщо значення в комірках, на які є посилання у формулах, міняються, то результат зміниться автоматично.

     Як приклад введемо формули, що обчислюють корені квадратного тричлена: ax² + bx + c = 0. Вони ведені в комірки A2 і A3 та мають такий вигляд:

= (–B1 + КОРЕНЬ(B1*B1 – 4*A1*C1))/2/A1

= (–B1 – КОРЕНЬ(B1*B1 – 4*A1*C1))/2/A1

У комірках A1, B1 і C1 перебувають значення коефіцієнтів a, b

 і с, відповідно. Якщо ви ввели значення коефіцієнтів a = 1, b = –5 і с = 6 (це означає, що в комірках A1, B1 і C1 записані числа 1, 5 і –6), то в комірках A2 і A3, де записані формули, ви одержите числа 2 і –3. Якщо ви зміните число в комірці A1 на –1, то в комірках з формулами ви одержите числа –6 і Використання стандартних функцій.

Excel містить більше ніж 400 вбудованих функцій. Функція має ім'я і список аргументів у круглих дужках

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

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

Якщо функція з’являється на самому початку формули,  їй повинен передувати знак рівності, як і в усякій іншій формулі.

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

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

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

Типи функцій. Для зручності роботи функції в Excel розбиті по категоріях: функції керування базами даних і списками, функції дати і часу, DDE/Зовнішні функції, інженерні функції, фінансові, інформаційні, логічні, функції перегляду і посилань. Крім того, присутні такі категорії функцій: статистичні, текстові та ма­тематичні.

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

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

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

У Excel широко представлені математичні функції. Наприклад, можна виконувати різні операції з матрицями: множити, знаходити зворотну, транспонувати.

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

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

Функції у ЕТ  — це скорочений ваpiaнт формул, які часто застосовують, тобто це програми, що за готовими формулами реалізують обчислення над аргументами, які задаються користувачем згідно з правилами запису (синтаксису) функції. Кожна функція має ім'я (латинські або кирилицею у Excel, тільки латинські у Calc) та аргументи, які записують у круглих дужках після iмeнi функції через крапку з комою. Аргументами функцій можуть бути числа, посилання на клітинки та діапазони, імена, текст, логічні значення, значення помилок, вкладені функції тощо. Доступ до функції здійснюється за її іменем. 

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

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

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

Рис 8. Вікно Майстра функцій

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

Викликати Майстра функцій можно декількома способами, спочатку виділити клітинку

-     Вставка - Функція;

-     (не має у Calc) Панель  інструментів Стандартная -кнопку   стрілка униз

-     Інші функції… ;

-     Рядок Формул - кнопку   (Вставка функції);

-     ввести  знак «=» - у Поле ім’я  - Інші функції…);

-     (тільки у Calc)  Вставка - Список функцій.

        

Рис 9. Способи виклику Майстра функцій

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

Арифметичні функції

СУММ(число1; число2;...) - повертає суму аргументів  (Calc- SUM).

ПРОИЗВЕД(число1; число2;...) - повертає добуток своїх аргументів (Calc- PRODUCT).

АВS(число)  - повертає абсолютне значення числа.

КОРЕНЬ(число) - повертає додатне значення квадратного кореня числа (Calc- SQRT). Якщо число від'ємне, з'являється повідомлення про помилку #ЧИСЛО!.

СТЕПЕНЬ(число; показник степеня) - повертає результат піднесення до степеня (Calc- POWER). Цей вираз є еквівалентним до виразу число^показник степеня.

ОКРУГЛ(число; кількість розрядів) - повертає результат округлення до зазначеної кількості десяткових розрядів (Calc- ROUND).

ОКРВВЕРХ(х; точність) та ОКРВНИЗ(х; точність) - повертає результат округлення з надлишком (з нестачею) до найближчого числа, що кратне точності (Calc- ROUNDDOWN та ROUNDUP).

ОТБР(число; кількість_розрядів) - залишає зазначену кількість розрядів числа після коми, відкидаючи всі наступні (не виконую­чи округлення) (Calc – TRUNC).

СЧЁТЕСЛИ(Диапазон;Критерий) - повертає кількість тільки тих ар­гументів, які відповідають певним умовам (Calc – COUNTIF).

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

Абсолютні, відносні і мішані посилання. Посилання на клітинки інших аркушів та інших книг.

В ЕТ у формулах та функціях для посилання на клітинки і блоки клітинок використовують імена клітинок як адреси. Існує три види адресів: відносні, абсолютні та змішані. Відносні адреси змінюються при копіюванні формул і мають вигляд просто адреси - А1, абсолютні адреси використовують якщо посилання на клітинку не повинно змінюватися при копіюванні, , наприклад, $А$1, тобто додається символу "$" перед позначенням стовпця і номером рядка. Іноді використовують змішані адреси, коли постійною є одна частина адреси, наприклад А$1 або $А1. При редагуванні адреси у формулі циклічну зміну вигляду посилання здійсню­ють за допомогою клавіші F4.

Адреса прямокутного блоку (діапазону) клітинок, сумісного, складається з адреси лівої верхньої клітинки блоку, двокрапки (оператора діапазону) і адреси правої нижньої клітинки, наприклад А1:Е5. Якщо треба  описати не сумісний діапазон клітинок, то використовують «;», наприклад,  А1;В3;Е5.

Адреси використовують також повні імена (адреси) клітинок. Для зазначення повного імені клітинки до її адреси дописують ім'я листа, де вона розташована, і назву книги (файлу):

[Книга 1]Лист1!А1.    

             Копіювання формул.

Копіювання формул і автоматичне переобчислення у таблиці — це два головні засоби автоматизації обчислень в ЕТ.

Під час копіювання формули відбуваються такі дії:

-формула вводиться в інші клітинки автоматично;

-формула автоматично модифікується — змінюються відносні адреси, на які є посилання у формулі.

Наприклад, під час копіювання формули = В3*С3 з третього рядка у четвертий формула в четвертому рядку набуде вигляду  = В4*С4.

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

Другий спосіб, заповнити першу клітинку початковим значенням        -Правка - Заповнити - Прогресія… (Calc   Ряди)

Рис 10. Діалогове вікно для заповнення клітинок даними (Ms Excel)

Можна створити свою власну послідовність будь яких значень (наприклад список назв днів тижня, назв місяців тощо) список. Для цього слід виконати послідовність дій: Сервіс -Параметри…- вкладка Списки  та ввести свою послідовність.

 Консолідація даних і зведені таблиці.

Консолідація даних це збирання та об'єднання даних з різних вихідних областей. Вихідні області можуть розташовуватися на будь-якому аркуші або книзі, на інших відкритих аркушах або книгах.  Існує кілька способів консолідації.

-       із застосуванням тривимірних формул;

-       за допомогою команди Консолідація.

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

Нехай ми маємо дані діяльності магазинів за шість місяців, що мають вигляд:

Відсортуємо кожну таблицю за двома першими стовпцями.

Додамо до таблиці аркуш «підсумок1» та скопіюємо вміст перших двох стовпців з довільного аркушу. Додамо до таблиці стовпчик «Виторг» та до комірок внесемо формули.

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

Розглянемо другий спосіб консолідування даних. Для цього створимо  у нашій електронній книзі аркуш «підсумок 2» і проведемо підготовчу роботу, аналогічну попередньому. Виділимо пусту комірку (згідно демонстрації та викличемо команду Консолідація закладини Дані:

Виконаємо відповідні налаштування:

Крім сумування можна при консолідації використовувати і інші функції:

Після застосування таблиці отримаємо консолідовану таблицю виду: