Формули і функції в Excel

3. Не можна використовувати  імена, які можуть трактуватися  як посилання на осередки (А1, С4).

4. Як імена можуть  використовуватися одиночні букви  за винятком букв R, C.

5. Пропуски необхідно  замінювати символом підкреслення.

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

У формулах розрізняють  відносні та абсолютні посилання  на певні комірки. Запис A1 - це відносна адресація комірки, натомість $A$1  - абсолютна адресація цієї ж комірки. Навіщо таке було придумано ? Справа в тім, що електронні таблиці набули такої великої популярності через можливість швидкої обробки великої кількості подібної інформації . 

Наприклад, якщо в комірці A1 введена назву товару, що поступив на склад, в комірці В1  введена кількість цього товару, а в комірці С - введена ціна одиниці товару   , то ввівши в комірку D1 формулу  =B1*C1 , то  отримаємо там вартість всієї партії цього товару. Якщо ж  на склад поступило 300 найменувань товару, то нема необхідності вводити подібну формулу для   кожної з комірок D2:D300, бо це займе багато часу і може призвести до виникнення помилок.  Значно кращим рішенням буде скопіювати цю формулу  на вказаний діапазон . Ось саме для процесу копіювання і придумана можливість  абсолютної чи відносної адресації комірок. При абсолютній адресації ( = $B$1*$C$1) у всіх комірках скопійованого діапазону був би отриманій той самий результат, що і в комірці  D1.  При відносній же адресації комірок  формула  у комірці D2  вже виглядала б як =B2*C2, в наступній  =B3*C3 і так далі, бо при відносній адресації комірок цю формулу можна трактувати як =вміст комірки на дві лівішої помножити на вміст комірки сусідньої зліва.  

Змішане посилання складає комбінацію відносного і абсолютного посилання - $A1, A$1.

Для швидкої зміни  типу посилання використовується клавіша F4. Введіть в комірку А1 будь-яке число. В комірку А2 введіть формулу =А1. Потім натискайте клавішу F4.

Після кожного натискання клавіші тип посилання буде змінюватися (Малюнок 1.3)

Малюнок 1.3

1.16 Поняття функції в Excel

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

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

Excel має велику кількість стандартних функцій робочих таблиць, які користувачі можуть застосовувати в своїх формулах. До них належать як досить поширені функції, такі як СУММ (SUM), СРЗНАЧ (AVERAGE) і КОРЕНЬ (SQRT), так і функції, призначені для виконання спеціальних обчислень, наприклад, статистичні функції або прикладні функції. Функції можуть спростити формули і зробити їх зрозумілішими, крім того, вони здебільшого дають змогу робити обчислення, які без них виконати дуже важко.

Всі функції мають  однаковий формат запису і включають ім'я функції(одне слово) і список розділених крапкою з комою (;) аргументів, що знаходяться у круглих дужках. Різні функції мають  різну кількість аргументів різних типів даних. За кількістю аргументів функції можна поділити на 

• з фіксованою кількістю (0, 1, 2, 3 ...) ;
• з невизначеною кількістю ;
• з не обов’язковим аргументом.

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

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

Деякі дії з даними у таблиці виконуються за допомогою  функцій, наприклад, обчислення середнього, мінімального, максимального значення, суми значень із заданого діапазону значень тощо. Загальний вигляд функції: =ім'я функції(параметр/и). У електронних таблицях можуть бути представлені такі види функцій:

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

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

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

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

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

- функції дати і часу та ін.

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

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

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

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

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

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

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

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

Excel допускає вкладення  функцій одна в одну, тобто  використання функції як аргумент для іншої функції. Функція, що є аргументом іншої функції, називається вкладеною. Наприклад, у записі  СУМ(А1:С5,МОПРЕД(О1:Е3))  використано вкладену функцію МОПРЕД, що повертає значення визначника матриці осередків О1:E3. Рівень вкладеності функцій у формулах Excel не повинний перевищувати семи.

1.18 Введення функцій за допомогою майстра

Як я уже знаю, крім вводу функцій за допомогою клавіатури існує й можливість введення їх ще й за допомогою майстра функцій. Виклик майстра забезпечує команда /Insert/Function...(Вставка/Функція) з меню програми  чи команда Paste function(Вставка функції) з панелі інструментів Стандартна. 

Майстер вводу функцій  має всього два вікна для набору параметрів  
(Малюнок 1.4).

Малюнок 1.4

У першому вікні потрібну функцію , а у другому - аргументи  для неї. У першому вікні є  два розгорнутих списки. У лівому знаходяться назви категорій функцій , а у правому - назви самих функцій. Вибравши категорію у лівому списку , можна побачити усі функції вибраної категорії у правому списку. Якщо користувачу не відома категорія потрібної функції, то можна спробувати вести пошук потрібної функції серед усіх функцій , вибравши відповідний рядок у лівому списку. Або можна спробувати найти потрібну функцію у так званій категорії "Найбільш часто вживані функції". Після вибору потрібної функції  з правого списку, у нижній частині вікна  дається коротке визначення вибраної функції та її аргументів.  У Excel 2003 інтерфейс цього вікна дещо змінений , проте не суттєво (добавилося поле редагування з кнопкою для пошуку. Наприклад, аби знайти функції, пов'язані з логарифмами чисел, можна ввести  log. )

Після подвійного клацання по вибраній функції чи після  клавіші Ok відкривається вікно Аргументів функції(Function Arguments). У верхній частині вікна розміщуються поля, призначені для введення аргументів, в нижній частині — довідкова інформація: ім'я вибраної функції, всі її аргументи, призначення функції і кожного аргументу, поточний результат функції і всієї формули. У тих випадках, коли аргумент приведений напівжирним шрифтом, він є обов'язковим, якщо звичайним шрифтом, то його можна пропустити. Аби панель формул не закривала діапазон комірок з даними, її можна перемістити і. Аби ввести як аргумент посилання на вічко, потрібно натиснути на значок вибору біля правої межі поля.

Якщо діалогове вікно Аргументи функції  дозволяє ввести декілька аргументів, то перехід від одного поля аргументу до іншого можна виконувати клавішею Tab. Після введення аргументів буде виведений поточний результат. Клавіша Ok  закриває вікно Аргументів функції (Приклад такого вікна зображений на малюнку 1.5) і заносить функцію у вибране вічко. 

 
Малюнок 1.5 

Меню Вставка/Функция. З’являється діалогове вікно майстра функцій.

(Малюнок 1.6) Вверху знаходиться категорія функції. Внизу можна вибрати потрібну функцію. Нижче визначення даної функції. В залежності від вибраної функції з’явиться наступне діалогове вікно, в якому потрібно ввести аргументи функції i клацнути мишкою на ОК. Функція буде введена в активну комірку таблиці.

Малюнок 1.6

Найбільш вживані функції:

Математичні функції:

ABS(X) - обчислюється абсолютне значення (модуль) X.

COS(X) - обчислюється COSX;

SIN(X) - обчислюється SINX;

КОРЕНЬ(число) – повертає значення квадратного кореня

СУММ(число1;число2; ...) – знаходить суму чисел в інтервалі комірок

СТЕПЕНЬ(число;степень) – повертає результат піднесення числа 
до степеня.

Статистичні функції

МАКС(число1;число2; ...) - повертає найбільше значення із списку аргументів, логічні і текстові значення ігноруються.

МИН(число1;число2; ...) - повертає найменше значення із списку аргументів, логічні і текстові значення ігноруються.

СРЗНАЧ(число1; число2; ...) - повертає середнє арифметичне своїх аргументів, які можуть бути числами, іменами, масивами або посиланнями на комірки з числами.

Логічні функції

ЕСЛИ(лог. вираз;значення істини;значення хиби) - якщо умова виконується повертає значення істини, не виконується – хиби.

И(логічне значення1;лог.значення2;...) -  повертає значення істини, якщо усі аргументи мають значення істина; повертає значення хиби, якщо хоча б один з аргументів має значення хиба.

ИЛИ(логічне значення1;логічне значення2; ...) – повертає значення істина, якщо хоча б один з аргументів має значення істина; повертає хибу, якщо всі аргументи мають значення хиба.

1.19 Автосума підсумкових функцій

В Excel автоматизований виклик функцій, обчислює підсумкові значення в діапазонах осередків з числовими даними, такі функції вибирають із списку кнопки Автосумма в панелі інструментів (Таблиця 1.6)

Найменування функції  в списку	Ім´я функції	Операції
Сума	=СУММ()	Обчислення суми чисел.
Середнє	=СРЗНАЧ()	Обчислення середнього арифметичного значення.
Число	=СЧЁТ()	Підрахунок кількості чисел.
Максимум	=МАКС()	Вибір максимального значення.
Мінімум	=МИН()	Вибір мінімального значення.