Повідомлення про помилки

Значення, яке свідчить про помилку(Error value), – це результат обробки формули, яку Excel не в силах обчислити коректно Всі сім стандартних повідомлень про помилки в формулах представлені в табл 122

Таблиця 122 Помилки при обробці формул

Помилка Опис

# СПРАВ / 0 (# DIV / 0) Спроба ділення на нуль Помилка зазвичай повязана з тим, що ви створили формулу, в якій дільник посилається на порожню комірку

# ІМЯ (# NAME) У формулі використовується імя, відсутнє вхпіске імен діалогового вікна Присвоєння імені Можливо, ви помилилися при введенні або вказали імя, яке було видалено Також це повідомлення виводиться, якщо рядок символів не полягає у лапки

продовження œ

Таблиця 122 (Продовження)

Помилка Опис

# Значить (# VALUE) Введена математична формула, що посилається на текстове значення

# ПОСИЛАННЯ (# REF) Відсутня діапазон комірок, на який посилається формула

(Можливо, ви його видалили)

# Н / Д (# N / A) Ні даних для обчислень При побудові моделі таблиці можна

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

# NUM (# NUM) Заданий неправильний-аргумент функції Також повідомлення # NUM може свідчити про те, що значення формули занадто велике або занадто мало і не може бути виведено на аркуші

# ПУСТО (# NULL) У формулі вказано перетин діапазонів, але ці діапазони не мають спільних комірок

Обчислення на аркуші

Розробники Excel ходять в зношених футболках, на яких красується бойовий клич

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

За замовчуванням обчислення проводяться в той час, коли користувач не працює з таблицею Якщо пересчету піддається велика кількість клітинок аркуша, в лівій частині рядка стану зявляються слова Розрахунок осередків (Calculating Cells) і деяке число Воно вказує відсоткове співвідношення виконаної роботи до її загального обсягу Процес перерахунку можна перервати, виконавши будь-яку команду або ввівши будь-яке значення в комірку Excel призупинить обчислення і продовжить їх по завершенні виконання команди або введення

Управління обчисленнями

Часом, щоб заощадити час, особливо коли мова йде про перерахунок книги з великою кількістю формул, має сенс перемкнутися з автоматичного виконання на ручне оновлення Тоді Excel буде проводити перерахунок тільки по вашій команді Для цього відкрийте командою Сервіс ► Установки (Tools ► Options) діалогове вікно Параметри (Options) і перейдіть на вкладку Обчислення (Calculating), зображену на рис 1220

Елементи управління вкладки Обчислення використовуються таким чином:

► Щоб відключити автоматичне оновлення значень в книзі, встановіть перемикач Обчислення (Calculation) в положення Вручну (Manual)

Рис 1220 Вкладка Обчислення діалогового вікна Параметри

► Встановивши прапорець Перерахунок перед збереженням (Recalculate Before Save), ви отримаєте впевненість, що в збереженої книзі містяться актуальні дані

► Якщо обрано варіант Автоматично крім таблиць (Automatic Except Tables), програма без вашої участі перераховує всі дані, на які впливає зміна значень в осередках, крім таблиць підстановок Докладніше про це див розділ

«Таблиці підстановки» глави 18

► Щоб провести обробку всіх відкритих книг, натисніть клавішу F9 або клацніть на кнопці Обчислити (Calc Now)

► Якщо ви хочете перерахувати лише активний аркуш, клацніть на кнопці Перерахунок листа (Calc Sheet) або використовуйте комбінацію клавіш Shift + F9

Копіювання значень формул

Зазвичай при копіюванні комірки, яка містить формулу, разом з повертається значенням в буфер обміну поміщається і сама формула При необхідності скопіювати тільки результат виберіть у меню Правка команду Спеціальна вставка (Paste Special) На екрані зявиться однойменне діалогове вікно, як показано на рис 1221 Серед безлічі елементів управління цього вікна вам потрібен лише той, який відповідає за результати обчислення формул Щоб вставляти в осередки тільки повертаються значення, встановіть перемикач Вставити (Paste) в положення Значення (Values) Детальніше див розділ «Команда Спеціальна вставка» глави 7

Рис 1221 Для вилучення результуючої інформації з копійованих осередків встановіть перемикач в діалоговому вікні Спеціальна вставка в положення Значення

Проміжні значення у формулах

При бажанні ви можете бачити результат обчислень тільки певної частини складної формули, коли, наприклад, ви проводите трасування залежностей або шукаєте помилки в обчисленнях Щоб змінити частина формули на значення, що повертається, виділіть цю частину формули та натисніть клавішу F9 Точно таким же способом замінюються окремі посилання на комірки їх значеннями Сказане ілюструє рис 1222

Закінчивши перевірку, натисніть Esc, щоб повернути формулами їх первісний вигляд Натиснувши клавішу Enter, ви затвердили зміни у формулі, тобто замініть її частина повертається значенням

РАДА

У вашому розпорядженні також команда Сервіс ► Залежності формул ► Обчислити формулу (Tools ► Formula Auditing ► Evaluate Formula), що допомагає вирішити наявні питання Докладніше про це див розділ «Обчислення і перевірка формул» глави 9

Рис 1222 Виділіть фрагмент формули та натисніть клавішу F9 для перетворення його в значення

Циклічні посилання

Циклічне посилання(Circular reference) – це формула, яка залежить від свого власного значення Найбільш очевидний приклад – формула, що містить посилання на свою власну клітинку Так, якщо ввести в комірку А1 формулу = С1-А1, Excel видасть повідомлення про помилку, показане на рис 1223

Рис 1223 Подібне повідомлення про помилку зявляється при спробі введення формули,

містить циклічне посилання

Щоб знайти помилку в обчисленнях, натисніть ОК і погляньте на формулу Якщо причина не кидається в очі, перевірте осередку, на які ця формула посилається

Для цього виберіть у меню Вигляд команду Панелі інструментів, а потім – Циклічні посилання (Circular Reference) Натисніть кнопку Впливаючі комірки (Trace Precedents) або Зовсім комірки (Trace Dependents) зявилася панелі інструментів, щоб відстежити причину неполадок Програма у вигляді стрілок наочно покаже шлях до всіх комірок, залученим у процес обчислення формули з круговою посиланням, як продемонстровано на рис 1224

Рис 1224 Панель інструментів Циклічні посилання допомагає виправити помилки в формулах, повязані з циклічними посиланнями

Якщо натиснути кнопку Впливаючі комірки (Trace Precedents), будуть намальовані лінії від осередку, що зберігає формулу з циклічною посиланням, до всіх комірок, на які ця формула посилається Кожне клацання на кнопці Впливають осередки відображає новий рівень залежностей у випадку, коли які-небудь впливають осередки самі утримують інші формули

ДИВИСЬ ТАКОЖ

Детальніше про трасуванні осередків див розділ «Перевірка і документування листів» глави 9

Більшість циклічних посилань створюється навмисно, аналогічно прикладу на рис

1225 Вони тут циклічні, оскільки формула в комірці А1 залежить від вмісту комірки A3, яке, в свою чергу, визначається значенням осередки А1

Рис 1225 Цей лист містить циклічне посилання

Щоб дозволити обробку посилань подібного типу, встановіть прапорець Ітерації (Iteration) на вкладці Обчислення, показаної раніше на рис 1220 У цьому випадку Excel перераховує задану кількість раз всі комірки відкритих листів, на яких присутні циклічні посилання

Число ітерацій задається в полі з лічильником Граничне число ітерацій (Maximum Iterations) за замовчуванням воно дорівнює 100 При кожному перерахунку формул результат в осередках наближається до правильних значень Процес обчислення закінчується, коли задану кількість ітерацій пройдено, або коли різниця значень між итерациями стане менше величини, зазначеної в поле Відносна похибка (Maximum Change) за замовчуванням це 0,001

Якщо в рядку стану після завершення обчислень зявляється слово Обчислити (Calculate), процес може бути продовжений Для цього необхідно або прийняти поточний результат, або збільшити кількість ітерацій, або зменшити похибку Якщо програма не в змозі вирішити циклічне посилання, вдруге попереджувальне повідомлення на екран не виводиться Ви повинні самі визначити, коли перервати обчислення, тобто задати умови, при яких відповідь буде вважатися досить близьким до очікуваного результату Excel працює дуже швидко, але якщо на аркуші багато формул, не нехтуйте радою встановити ручний режим обчислень (положення перемикача Вручну (Manual)) В іншому випадку програма буде перераховувати циклічні посилання при кожній зміні значень в осередках

Сценарій, коли послідовно одержувані значення зближуються, називається конвергенцією (Convergence) Назва зворотного процесу звучить схоже – дивергенція (Divergence) і означає, що різниця між результатами з кожною наступною ітерацій зростає У цьому випадку програма продовжує обчислення, просто поки не виконає задану кількість повторень

Точність числових значень

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

У табл 123 наведено приклади того, як програма звертається з цілими числами і десятковими дробами довжиною більше 15 цифр, коли вони вводяться в клітинку з встановленою за замовчуванням шириною 8,43 символу

Таблиця 123 Точність числових значень

Введене значення відобразитися значення збереженої значення

123456789012345678

123457Е +17

123456789012345000

123456789012345678

1234568

123456789012345

123456789012345678

1234567890

123456789012345

123456789012345678                   

Діапазону позитивних значень, з якими справляється Excel, вистачає практично для будь-яких обчислень – від 100Е-307 до 999Е +307 Якщо формула повертає значення поза цього інтервалу, воно зберігається як текст, а в комірці з формулою відображається повідомлення про помилку # ЧІСЛ0 (# NUM)

ВИРІШЕННЯ ПРОБЛЕМ

При наявності на аркуші округлених значень ви можете зіткнутися з помилками при підсумовуванні таких чисел Наприклад, в комірці, відформатованої для виведення чисел у грошовому форматі, тобто з двома десятковими знаками після коми, значення 10,006 округлятиметься до $ 10,01 Якщо просто скласти 10,006 і 10,006, правильним результатом буде 20,012 Але коли для цих чисел використовується вказаний формат, ви побачите число $ 20,01, що не є помилкою, проте абсолютно неприйнятно в деяких випадках, наприклад для презентацій

Ця ситуація легко поправна Просто встановіть прапорець Точність як на екрані (Precision As Displayed) на вкладці Обчислення діалогового вікна Параметри Однак користуйтеся даним режимом з обережністю, так як збережені значення тут замінюються на які відображаються Іншими словами, якщо для комірки, що містить значення 10,006 і асоційованою з грошовим форматом, встановити прапорець Точність як на екрані (Precision As Displayed), це значення назавжди заміниться на 10,01 Детальніше про форматування чисел див розділ «Форматування осередків» глави 8

Масиви

Масиви (Arrays) як поняття добре знайомі програмістам Для решти найпростіше сказати, що масиви представляють собою набір елементів Excel – одна з небагатьох програм, які полегшують обробку масивів за допомогою індивідуальної і групової адресації їх елементів у простих термінах t Насамперед познайомимося з деякими специфічними поняттями Excel, повязаними з обробкою масивів

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

Діапазон масиву – це блок осередків, який поділяється загальною формулою масиву

(Приклад в наступному в розділі скаже вам більше)

Масив констант (Array constant) – спеціальним чином організований список постійних значень, використовуваних як аргументи у формулах масиву

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

Одномірнімасиви

Найпростіший спосіб зрозуміти, що таке масиви і як вони працюють, – це розглянути кілька прикладів Скажімо, визначивши простий масив-формулу, неважко обчислити середні значення на аркуші, показаному на рис 1226

Рис 1226 Для обчислення середніх значень в діапазоні F4: F8 ми використовували формулу масиву

Цей приклад, зокрема, демонструє спосіб захисту формул від підміни, так як окремі формули в осередках, оголошених частиною масиву, Excel змінювати не дозволяє Щоб ввести формулу масиву, виконайте такі дії:

1 Виділіть діапазон F4: F8

2 Введіть вираз в рядок формул, як показано на рис 1226, без фігурних дужок

3 Натисніть клавіші Ctrl + Shift + Enter

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

необхідно виділити всю формулу масиву, іншими словами, весь діапазон F4: F8

Формули масиву

Щоб ввести формулу масиву, перш за все потрібно виділити клітинку або діапазон, в якому будуть виводитися результати Якщо формула повертає кілька значень, діапазон повинен бути тих же розмірів і форми, що і діапазон або діапазони з вихідними даними

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

► Для вказівки на те, що формула є формулою масиву, використовуйте комбінацію клавіш Ctrl + Shift + Enter Після їх натискання Excel укладає формулу у фігурні дужки, які якраз і є розпізнавальним ознакою Не вводьте дужки самостійно – програма сприйме це як текстове значення

► Не можна редагувати, видаляти і переміщати вміст окремих осередків масиву точно так само, як не можна додавати і видаляти самі осередки Масив-діапазон потрібно розглядати як монолітний обєкт і відповідно редагувати як єдине ціле

► Щоб відредагувати масив, виділіть його цілком і клацніть у рядку формул (фігурні дужки навколо формули зникнуть) Закінчивши сеанс редагування, знову натисніть клавіші Ctrl + Shift + Enter

► Якщо ви хочете видалити масив, виділіть його діапазон і натисніть клавішу Delete

► Щоб швидко виділити весь масив, клацніть на будь-якій його комірці і натисніть клавіші Ctrl + /

► Щоб перемістити діапазон масиву, виділіть весь масив, а потім або виріжте і вставте його на нове місце, або перетягніть за допомогою миші

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

Двовимірні масиви

У попередньому прикладі формула масиву розміщувалася у вертикальному одновимірному масиві (стовпці або рядку) Більш складні масиви, розташовані на декількох рядках і стовпцях, називаються двовимірними (Рис 1227)

Щоб сформувати двовимірний масив, виконайте такі дії:

1 Виділіть діапазон такого ж розміру і форми, що і діапазон з вихідними даними

2 Введіть формулу в рядок формул і натисніть клавіші Ctrl + Shift + Enter

Рис 1227 При обчисленні рейтингу екзаменованих використовувалася формула для двовимірного масиву В10: Е14 Простий одновимірний масив застосований для осередків F10: F14

ПРИМІТКА

На жаль, не можна створювати тривимірні масиви, як би наскрізні для кількох аркушів книги

Застосування формул масиву

Формули масивів виконують обчислення відразу для великої кількості значень, але під результат відводиться єдиний осередок Звідси очевидно, що формула масиву повертає всього одне значення Наприклад, щоб підрахувати кількість «помилкових» значень у заданому діапазоні, найпростішим буде вираз із залученням функції ЕОШИБКА (ISERROR), як показано на рис 1228

Рис 1228Результат підрахунку помилкових значень в діапазоні B4: N38 ми помістили в клітинку A3

Формула в цьому прикладі може бути введена як звичайна формула (натисканням Enter) або як формула масиву (натисканням клавіш Ctrl + Shift + Enter) В останньому випадку її застосування дещо відрізняється від стандартного Справа в тому, що при звичайному введенні без різниці, скільки помилок знайдено на аркуші, – формула завжди поверне значення 1, оскільки функція СУММ (SUM) має тільки один аргумент – результат обчислення функції ЕОШИБКА Якщо ж ввести формулу масиву, вона поверне загальна кількість помилок на аркуші, оскільки тепер аргументом функції СУММ стане набір

окремих результатів обчислення функції ЕОШИБКА Докладніше про функції

ЕОШИБКА див розділ «Інформаційні функції» глави 14

Припустимо, перед вами стоїть завдання підрахувати кількість елементів таблиці, що задовольняють двом умовам Ви хочете зясувати, скільки транзакцій величиною більше $ 1000 відбулося після зазначеної дати Для цього ви можете додати до таблиці стовпець, що містить функції ЕСЛИ (IF) для виявлення кожної транзакції, що підпадає під зазначені критерії, і результат тоді буде показаний в тому ж стовпці Найпростішим способом вирішення задачі є формула зразок

= СУММ ((А1: А100> 37266) * (С1: СЮ0> 999))

Число 37266 є числовим поданням дати, відповідним 01102002 Введіть формулу та натисніть клавіші Ctrl + Shift + Enter При розрахунку кожна частина формули, укладена в першу круглі дужки, зведеться до логічному значенню 1 (TRUE) або 0 (FALSE), залежно від дати кожен елемент у другій парі вкладених дужок також виродиться в 0 або 1, виходячи з того, більше або немає він значення 999 При обробці формули будуть перемножував нулі й одиниці, і коли обидва співмножники виявляться рівні TRUE, результуюче значення також буде 1 Функція СУММ складає ці одиниці для підрахунку загальної кількості Ви можете збільшити кількість умов і додати, відповідно, більше увязнених в дужки фрагментів у формулу будь-який вираз з результатом FALSE (0) буде виключено з підсумку, оскільки проміжне множення дасть нуль

Є кілька способів розвитку даної формули Наприклад, замініть числове представлення дати на функцію ДАТАЗНАЧ (DATEVALUE), і тоді формула буде працювати з досить довільним видом дати (наприклад, 31 грудень 2003 та 1/10/2002) Ще краще використовувати посилання на комірки в якості аргументів для кожного елемента тоді різноманітні умови можна набирати прямо в осередках, а не вбудовувати їх у формулу За інформацією про функції ДАТАЗНАЧ звертайтеся до розділу

«Форматування дат і часу» глави 15

Масиви констант

Масив констант(Array constant) – це спеціально організований лист значень, які можна використовувати як аргументи формул масиву Масив констант може складатися з числових, текстових або логічних значень При цьому він повинен бути укладений у фігурні дужки, причому його елементи, розташовані в різних стовпцях, відокремлюються символами крапки з комою, а в різних рядках – двокрапкою Наприклад, для формули на рис 1229 задіяні значення всіх девяти виділених осередків

Рис 1229В як аргумент для цієї формули використовується масив констант

Щоб ввести у формулу масив констант, виконайте такі дії:

1 Виділіть діапазон комірок, в якому будуть знаходитися результати обчислення формули У нашому прикладі аргумент для функції ЦІЛЕ (INT) складається з трьох груп констант, відділених один від одного символами крапки з комою (між значеннями у стовпцях) і двоєточиямі (між значеннями в рядках) 1

2 Поставте фігурні дужки навколо аргументу (самостійно), щоб вказати, що ці значення являють собою масив констант

3 Натисніть клавіші Ctrl + Shift + Enter Таким чином, формула виявиться укладена в дві пари фігурних дужок: одна пара для констант, а інша – навколо всієї формули

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

Розширення масивів-аргументів

При використанні масивів як аргументи формул все масиви повинні бути однакової розмірності Якщо розмірності масивів не збігаються, Excel в деяких випадках розширює аргументи Так, щоб перемножити всі значення в діапазоні А1: В5 на 10, підходить кожна з наступних формул: {-А1: В5 * 10} або

{={12:34:56:78:910}*10}

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

{-А1: В5 * {10 10:10 10:10 10:10 10:10 10}}

а друга буде виглядати так:

{= {1 2:3 4:5 б: 7 8:9 10} * {10 10:10 10:10 10:10 10:10 10}}

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

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*