Обчислення на робочому аркуші. Функції робочого аркуша

ОБЧИСЛЕННЯ — це процес розрахунку формул з наступним висновком результатів у вигляді значень в осередках, що містить формули. При зміні значень в осередках, на якому посилаються формули, значення формул обновляються (тобто відбувається повторне обчислення). Цей процес називається перерахунком. Він торкає тільки тих осередків, які містять посилання на осередки, що змінилися.

За замовчуванням Excel виконує перерахування завжди, коли зміни впливають на значення осередків. Якщо перераховується досить багато осередків, у лівій частині рядка стани з'являються слова «Розрахунок осередків» і деяке число. Число показує відсоток виконаного перерахунку осередків. Процес перерахування можна перервати. При уведенні якої-небудь команди й значення в осередок під час виконання перерахування Excel призупинить відновлення обчислень і продовжить його, коли користувач закінчить операцію.

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

Циклічне посилання — це формула, що залежить від свого власного значення. При виявленні циклічного посилання Excel видає повідомлення про помилку. Багато циклічних посилань можуть бути дозволені. Для установки цього режиму варто встановити прапорець Ітерації на вкладці Обчислення команди Параметри меню Сервіс. У цьому випадку Excel перераховує задане число раз всі осередки у всіх відкритих аркушах, які містять циклічне посилання. Якщо встановлено прапорець Ітерації, можна задати граничне число ітерацій (за замовчуванням 100) і відносну погрішність (за замовчуванням 0,001). Excel виконує перерахування зазначене граничне число раз або доти, поки зміна значень між ітераціями не стане менше заданої відносної погрішності. При використанні циклічних посилань доцільно встановити ручний режим обчислень. У противному випадку програма буде перераховувати циклічні посилання при кожній зміні значень в осередках.

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

У загальному виді будь-яка функція може бути записана у вигляді:

=<ім'я_функції>(аргументи)

Існують наступні правила уведення функцій:

1. Ім'я функції завжди вводиться після знака «=».

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

3. Між ім'ям функції й знаком «(» пробіл не ставиться.

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

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

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

1. Арифметичні й тригонометричні.

2. Інженерні, призначені для виконання інженерного аналізу (функції для роботи з комплексними змінними; перетворення чисел з однієї системи числення в іншу; перетворення величин з однієї системи мір в іншу).

3. Інформаційні, призначені для визначення типу даних, збережених в осередках.

4. Логічні, призначені для перевірки виконання умови або декількох умов (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).

5. Статистичні, призначені для виконання статистичного аналізу даних.

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

7. Функції баз даних, призначені для аналізу даних зі списків або баз даних.

8. Текстові функції, призначені для обробки тексту (перетворення, порівняння, зчеплення рядків тексту й т.д.).

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

10. Нестандартні функції. Це функції, створені користувачем для власних потреб. Створення функцій здійснюється за допомогою мови Visual Basic.

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

1. виділити осередок, у якій повинен розташовуватися підсумок;

2. клацнути по кнопці Автосумма;

3. буде запропонований діапазон для підсумовування (він оточений рухливою рамкою). Якщо діапазон невірний, варто виділити потрібний діапазон (осередок, суміжні осередки, несуміжні осередки - у будь-якій комбінації);

4. клацнути по кнопці Автосумма.

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

Якщо при наборі формули були допущені помилки, то в осередок буде виведене значення помилки. В Excel визначено сім помилкових значень:

1. #ДЕЛ/0! - спроба розподілу на 0. Ця помилка звичайно виникає, якщо у формулі дільник посилається на порожній осередок;

2. #ИМЯ? - у формулі використається ім'я, відсутнє в списку імен діалогу Присвоєння імені. Excel також уводить це помилкове значення в тому випадку, коли рядок символів не укладений у подвійні лапки;

3. #ЗНАЧ! - видається при вказівці аргументу або операнду неприпустимого типу, наприклад, уведена математична формула, що посилається на текстове значення, а також у тому випадку, коли Excel не може виправити формулу засобами автовиправлення;

4. #ССЫЛКА! - відсутній діапазон осередків, на який посилається формула (можливо, він вилучений);

5. #Н/Д - немає даних для обчислень. Аргумент функції або операнду формули є посиланням на осередок, що не містить даних. Будь-яка формула, що посилається на осередки, що містять #Н/Д, повертає значення #Н/Д;

6. #ЧИСЛО! - заданий неправильний аргумент функції, наприклад, v(-5). #ЧИСЛО!,може також указувати на те, що значення формули занадто велике або занадто мало й не може бути представлене на аркуші;

7. #ПУСТО! - у формулі зазначене перетинання діапазонів, але ці діапазони не мають загальних осередків.

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

· переглянути всі осередки, що впливають; будуть зазначені весь осередки, на які є посилання у формулі активного осередку;

· переглянути всі залежні осередки; будуть зазначені весь осередки, у яких є посилання на активний осередок;

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

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

Існують також три спеціальні логічні функції ЕОШ, ЕОШИБКА й ЕНД, що дозволяють перехоплювати помилки й значення #Н/Д и запобігати їхнє поширення по робочому аркуші. Функції мають наступний формат:

· =ЕОШ(значення);

· =ЕОШИБКА(значення);

· =ЕНД(значення).

Ці функції перевіряють значення аргументу або осередку й визначають, чи містять вони помилкове значення. Функція ЕОШ перевіряє значення на всі помилки, за винятком #Н/Д, ЕОШИБКА відслідковує всі помилкові значення, а ЕНД перевіряє тільки появу значення #Н/Д.

У принципі аргумент «значення» може бути числом, формулою або рядком символів, але звичайно це посилання на осередок або діапазон. У противному випадку перевіряється тільки один осередок діапазону, а саме осередок, що перебуває в тім же стовпці або рядку, що й формула. Звичайно функції ЕОШ, ЕОШИБКА й ЕНД використаються як логічні вираження функції ЯКЩО.

Дата добавления: 2014-10-31