Щоб ефективно використовувати формули, потрібно розуміти три важливі моменти.
Обчислення – це процес обчислення формул і відображення результатів як значень у клітинках, які містять ці формули. Щоб уникнути зайвих обчислень, які потребують часу і сповільнюють комп’ютер, програма Microsoft Excel автоматично переобчислює формулу тільки після змінення клітинок, від яких ця формула залежить. Це поведінка за промовчанням під час першого відкриття робочої книги та її редагування. Проте можна керувати тим, коли та як програма Excel переобчислює формули.
Ітерація – це повторюване переобчислення формул на робочому аркуші, доки не буде дотримано конкретну числову умову. Програма Excel не може автоматично обчислити формулу, яка посилається на клітинку – прямо або непрямо – що містить формулу. Це називається циклічним посиланням. Якщо формула посилається на одну із власних клітинок, необхідно визначити, скільки разів формула має переобчислюватися. Циклічні посилання можуть переобчислюватися нескінченно. Проте можна регулювати максимальне число ітерацій і кількість прийнятних змін.
Точність — це міра, наскільки точним є обчислення. Excel зберігає й обчислює з точністю до 15 значущих цифр. Проте, можна змінити точність обчислень, щоб під час проведення переобчислень формул Excel використовував значення, які відображаються, замість значень, які зберігаються.
Поки виконується обчислення, можна вибирати команди й виконувати дії, наприклад вводити числа або формули. Програма Excel тимчасово перериває обчислення, щоб виконати інші команди або дії, а потім поновлює його. Процес обчислення може тривати довше, якщо в книзі багато формул, або аркуші містять таблиці даних чи функції, які автоматично переобчислюються разом із книгою. Також процес переобчислення може тривати довше, якщо на аркушах є посилання на інші аркуші або книги. Щоб керувати тим, коли відбувається обчислення, можна вибрати обчислення вручну.
Увага!: Змінення будь-якого з цих параметрів впливає на всі відкриті робочі книги.
-
На вкладці Файл виберіть пункт Параметри, а потім виберіть категорію Формули.
В Excel 2007 натисніть кнопку Microsoft Office, виберіть параметри Excel, а потім виберіть категорію Формули .
-
Виконайте одну з таких дій:
-
Щоб переобчислити усі залежні формули після кожного змінення значення, формули або імені, у розділі Параметри обчислення виберіть пункт Обчислення робочої книги, відтак виберіть пункт автоматично. Це настройка обчислення за промовчанням.
-
Щоб переобчислити всі залежні формули, крім таблиць даних , щоразу, коли змінюється значення, формула або ім'я, у розділі Параметри обчислення в розділі Обчислення книги виберіть пункт Автоматично, крім таблиць даних.
-
Щоб вимкнути автоматичне переобчислення та переобчислювати відкриті робочі книги лише явно (натискаючи клавішу F9), у розділі Параметри обчислення в групі Обчислення робочої книги виберіть параметр Вручну.
Примітка.: Якщо вибрати пункт вручну, програма Excel автоматично встановить прапорець Обчислити робочу книгу повторно перед збереженням. Якщо збереження робочої книги займає багато часу, зняття прапорця Обчислити робочу книгу повторно перед збереженням може прискорити збереження.
-
Щоби переобчислити вручну всі відкриті робочі аркуші, включно з таблицями даних, і оновити всі відкриті аркуші діаграм, на вкладці Формули в групі Обчислення натисніть кнопку Обчислити зараз.
-
Щоб переобчислити вручну поточний аркуш і будь-які діаграми й аркуші діаграм, пов’язані з цим аркушем, на вкладці Формули в групі Обчислення натисніть кнопку Виконати обчислення.
-
Порада.: Більшість цих параметрів можна також змінити, не використовуючи діалогове вікно Параметри Excel. На вкладці Формули в групі Обчислення натисніть кнопку Параметри обчислення та виберіть пункт Автоматично.
Примітка.: Якщо аркуш містить формулу, що посилається на аркуш, який не переобчислювався, і ви оновлюєте таке посилання, то Excel відображає повідомлення про те, що вихідний аркуш не повністю переобчислений. Для оновлення посилання відповідно до поточного значення, яке зберігається на вихідному аркуші, навіть якщо це значення неправильне, натисніть кнопку OK. Для скасування оновлення посилання і використання попереднього значення, отриманого з вихідного аркуша, натисніть кнопку Скасувати.
|
Дія |
Клавіші |
|---|---|
|
Переобчислити формули, змінені після останнього обчислення, і залежні від них формули в усіх відкритих книгах. Якщо в книзі ввімкнуто автоматичне переобчислення, натискати клавішу F9 для переобчислення не потрібно. |
F9 |
|
Переобчислити формули, які змінилися після останнього обчислення, і формули, які залежать від них, на поточному аркуші. |
Shift+F9 |
|
Переобчислити всі формули в усіх відкритих книгах, незалежно від того, чи змінювалися вони після останнього переобчислення. |
Ctrl+Alt+F9 |
|
Перевірити залежні формули й переобчислити всі формули в усіх відкритих книгах, незалежно від того, чи змінювалися вони після останнього переобчислення. |
Ctrl+Shift+Alt+F9 |
-
На вкладці Файл виберіть пункт Параметри, а потім виберіть категорію Формули.
В Excel 2007 натисніть кнопку Microsoft Office, виберіть параметри Excel, а потім виберіть категорію Формули .
-
У розділі Параметри обчислення встановіть прапорець Увімкнути повторне обчислення.
-
Щоб задати максимальну кількість переобчислень для програми Excel, введіть кількість ітерацій у поле Максимальна кількість повторів. Що більша кількість ітерацій, то більше часу триватиме переобчислення аркуша у програмі Excel.
-
Щоб задати максимальну кількість змін, які буде прийнято між результатами переобчислень, введіть кількість у полі Максимальне змінення. Що менше число, то точніші результати і довше обчислюватиметься аркуш.
Примітка.: ''Пошук розв’язання'' та ''Підбір параметра'' – це частина набору команд, які іноді називають знаряддям аналіз "what-if". Обидві команди використовують ітерацію в контрольований спосіб, щоб отримати потрібні результати. Команду ''Пошук розв’язання'' можна використовувати, якщо потрібно знайти оптимальне значення для певної клітинки, змінивши значення кількох клітинок, або якщо потрібно застосувати певні обмеження до одного або кількох значень в обчисленні. Команду ''Підбір параметра'' можна використовувати, якщо відомий потрібний результат для однієї формули, але невідоме вхідне значення, яке потрібне для визначення результату за допомогою цієї формули.
Перш ніж змінювати точність обчислень, візьміть до уваги такі важливі моменти:
За замовчуванням в Excel обчислюються ті значення, які зберігаються, а не ті, які відображаються.
Значення, що відображається та друкується, залежить від формату та способу відображення значення, яке зберігається. Наприклад, клітинка, у якій відображається дата "22.06.2008", також містить порядковий номер, що зберігається як значення дати в клітинці. Ви можете змінити формат, у якому відображається дата на аркуші (наприклад, на "22 червня 2008 р."), але значення, що зберігається, при цьому не зміниться.
Обережно змінюйте точність обчислень.
Коли обчислюється формула, в Excel зазвичай використовуються значення, що зберігаються в клітинках, на які посилається формула. Наприклад, якщо кожна з двох клітинок містить значення 10,005 і їх відформатовано так, щоб відображати значення в грошовому форматі, у кожній клітинці відображатиметься значення 10,01 ₴. Якщо додати ці дві клітинки, отримаємо результат 20,01 ₴, оскільки в Excel додаються значення, які зберігаються (10,005 і 10,005), а не ті, що відображаються.
Якщо точність обчислень у книзі змінюється за допомогою відображуваних (форматованих) значень, Excel остаточно змінює значення, які зберігаються у клітинках, з формату повної точності (15 цифр) на будь-який інший формат, що відображається, включно з десятковими розрядами. Якщо пізніше ви вирішите виконати обчислення з повною точністю, вихідні основні значення буде неможливо відновити.
-
На вкладці Файл виберіть пункт Параметри, а потім виберіть категорію Додатково.
В Excel 2007 натисніть кнопку Microsoft Office, виберіть параметри Excel, а потім виберіть категорію Додатково .
-
У розділі Під час обчислення цієї книги виберіть потрібну робочу книгу та встановіть прапорець Настроїти точність, як на екрані.
Хоча в Excel точність обмежується до 15 цифр, це не означає, що в Excel можна зберегти число щонайбільше з 15 цифр. Граничне значення – 9,99999999999999E+307 для додатних чисел і –9,99999999999999E+307 для від’ємних. Це приблизно те саме, що 1 або –1 із 308 нулями.
Точність в Excel означає, що будь-яке число, яке складається більш ніж із 15 цифр, зберігається та відображається лише з 15-розрядною точністю. Це можуть бути цифри в будь-якій комбінації до або після десяткового роздільника. Праворуч від 15-ої цифри будуть лише нулі. Наприклад, число +1234567,890123456 має 16 цифр (сім цифр до і дев’ять після десяткового роздільника). В Excel воно зберігається і відображається (у рядку формул і клітинці) як 1234567,89012345. Якщо встановити для клітинки числовий формат, щоб відображалися всі цифри (замість експоненційного формату, для якого відображатиметься 1,23457E+06), число відобразиться як +1234567,890123450. Цифра 6 у кінці (16-а) відкидається та заміняється на 0. Точність обмежується 15-ою цифрою, а всі наступні дорівнюють нулю.
У комп’ютері може бути більше одного процесора (він містить кілька фізичних процесорів) або в ньому може використовуватися гіперпотокова технологія (тобто комп’ютер містить кілька логічних процесорів). На таких комп’ютерах можна зменшити або контролювати час, потрібний для переобчислення робочих книг, що містять багато формул, якщо задати кількість процесорів для переобчислення. У багатьох випадках частини операцій з переобчислення можна виконувати одночасно. Розподіл операцій між кількома процесорами може зменшити загальний час для виконання переобчислення.
-
На вкладці Файл виберіть пункт Параметри, а потім виберіть категорію Додатково.
В Excel 2007 натисніть кнопку Microsoft Office, виберіть параметри Excel, а потім виберіть категорію Додатково .
-
Щоб увімкнути або вимкнути використання кількох процесорів під час обчислення, у розділі Формули встановіть або зніміть прапорець Увімкнути багатопотокове обчислення.
Примітка. Цей прапорець установлено за промовчанням, і під час обчислення використовуються всі процесори. Кількість процесорів на комп’ютері визначається автоматично та відображається поруч із параметром Використовувати всі процесори комп’ютера.
-
Також, якщо встановлений прапорець Увімкнути багатопотокове обчислення, то можна контролювати кількість процесорів, які використовуються для обчислень. Наприклад, може знадобитися обмежити кількість процесорів, використовуваних для обчислень, якщо на комп’ютері виконуються інші програми, для оброблення яких потрібен додатковий час.
-
Щоб керувати кількістю процесорів, у розділі Кількість потоків обчислення виберіть пункт Вручну. Введіть кількість процесорів, що використовуватимуться (максимальна кількість: 1024).
Щоб забезпечити правильне обчислення старіших книг програма Excel під час першого відкриття робочої книги, збереженої в попередній версії програми, працює інакше, ніж під час відкриття книги, створеної в поточній версії.
-
Коли відкривається робоча книга, створена в поточній версії, Excel переобчислює лише формули, що залежать від клітинок, які змінилися.
-
Коли відкривається книга, створена в попередній версії програми Excel, переобчислюються всі формули у книзі: ті, що залежать від клітинок, які змінились, і ті, що не залежать. Це забезпечує повну оптимізацію книги для поточної версії програми Excel. За виключенням випадку, коли книга знаходиться в іншому режимі обчислення, наприклад ''Вручну''.
-
Оскільки повне переобчислення може тривати довше, ніж часткове, відкриття книги, яку не було попередньо збережено в поточній версії програми Excel, може зайняти більше часу, ніж звичайно. Після збереження робочої книги в поточній версії програми Excel вона відкриватиметься швидше.
У Інтернет-версія Excel результат формули автоматично переобчислюється під час змінення даних у клітинках, які використовуються в цій формулі. Ви можете вимкнути це автоматичне переобчислення та обчислити результати формул вручну. Ось як це зробити.
Примітка.: Змінення параметра обчислення в книзі вплине лише на поточну книгу, а не будь-які інші відкриті книги в браузері.
-
В електронній таблиці Інтернет-версія Excel перейдіть на вкладку Формули .
-
У розкривному списку поруч із пунктом Параметри обчислення виберіть один із таких параметрів:
-
Щоб переобчислювати всі залежні формули щоразу, коли ви змінюєте значення, формулу або ім'я, натисніть кнопку Автоматично. Це параметр за замовчуванням.
-
Щоб переобчислити всі залежні формули, крім таблиць даних , щоразу, коли ви змінюєте значення, формулу або ім'я, виберіть пункт Автоматично за винятком таблиць даних.
-
Щоб вимкнути автоматичне переобчислення та переобчислити відкриті книги лише тоді, коли ви явно зробите це, натисніть кнопку Вручну.
-
Щоб переобчислити книгу вручну (включно з таблицями даних), натисніть кнопку Обчислити книгу.
-
Примітка.: У Інтернет-версія Excel не можна змінити кількість переобчислень формули, доки не буде виконано певну числову умову, а також не можна змінити точність обчислень, використовуючи значення, що відображається, а не збережене значення під час переобчислення формул. Однак це можна зробити в класичній програмі Excel. Натисніть кнопку Відкрити у програмі Excel , щоб відкрити книгу, щоб указати параметри обчислення та змінити переобчислення, ітерацію або точність формули.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
