Читачі Лайфхакера вже знайомі з Денисом Батьяновимякий ділився з нами. Сьогодні Денис розповість про те, як уникнути найпоширеніших проблем із Excel, які ми найчастіше створюємо собі самостійно.
Відразу зазначу, що матеріал статті призначається для користувачів-початківців Excel. Досвідчені користувачі вже запально станцювали на цих граблях не раз, тому моє завдання вберегти від цього молодих та недосвідчених «танцюристів».
Ви не даєте заголовки стовпцям таблиць
Багато інструментів Excel, наприклад: сортування, фільтрація, розумні таблиці, зведені таблиці - мають на увазі, що ваші дані містять заголовки стовпців. В іншому випадку ви або взагалі не зможете ними скористатися, або вони відпрацюють не зовсім коректно. Завжди дбайте, щоб ваші таблиці містили заголовки стовпців.
Порожні стовпці та рядки всередині ваших таблиць
Це збиває з пантелику Excel. Зустрівши порожній рядок або стовпець усередині вашої таблиці, він починає думати, що у вас дві таблиці, а не одна. Вам доведеться постійно його виправляти. Також не варто приховувати непотрібні вам рядки/стовпці усередині таблиці, краще видаліть їх.
На одному аркуші розміщується кілька таблиць
Якщо це не крихітні таблиці, що містять довідники значень, то так не варто робити.
Вам буде незручно повноцінно працювати більше, ніж з однією таблицею на аркуші. Наприклад, якщо одна таблиця розташовується зліва, а друга справа, то фільтрація однієї таблиці впливатиме і на іншу. Якщо таблиці розташовані одна під одною, то неможливо скористатися закріпленням областей, а також одну з таблиць доведеться постійно шукати та проводити зайві маніпуляції, щоб стати на неї табличним курсором. Воно вам треба?
Дані одного типу штучно розташовуються у різних стовпцях
Дуже часто користувачі, які знають Excel досить поверхово, віддають перевагу такому формату таблиці:
Здавалося б, перед нами нешкідливий формат для накопичення інформації з продажу агентів та їх штрафів. Подібне компонування таблиці добре сприймається людиною візуально, оскільки вона компактна. Однак, повірте, що це справжній жах - намагатися витягувати з таких таблиць дані та отримувати проміжні підсумки (агрегувати інформацію).
Справа в тому, що даний формат містить 2 виміри: щоб , Ви повинні визначитися з рядком, перебираючи філію, групу та агента. Коли ви знайдете потрібну стоку, потім доведеться шукати вже потрібний стовпець, оскільки їх тут багато. І ця «двовимірність» сильно ускладнює роботу з такою таблицею і для стандартних інструментів Excel – формул та зведених таблиць.
Якщо ви побудуєте зведену таблицю, то виявите, що немає можливості легко отримати дані за роком або кварталом, оскільки показники рознесені на різні поля. Ви не маєте одного поля за обсягом продажів, яким можна зручно маніпулювати, а є 12 окремих полів. Доведеться створювати руками окремі поля для кварталів і року, хоча, якби все це було в одному стовпці, зведена таблиця зробила б це за вас.
Якщо ви захочете застосувати стандартні формули підсумовування типу СУМІСЛІ (SUMIF), СУМІСЛІМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то також виявите, що вони не зможуть ефективно працювати з таким компонуванням таблиці.
Рознесення інформації з різних аркушів книги «для зручності»
Ще одна поширена помилка — це, маючи якийсь стандартний формат таблиці та потребуючи аналітики на основі цих даних, розносити її за окремими аркушами книги Excel. Наприклад, часто створюють окремі листи щомісяця чи рік. В результаті обсяг роботи з аналізу даних фактично множиться на кількість створених аркушів. Не потрібно так робити. Накопичуйте інформацію на одному листі.
Інформація у коментарях
Часто користувачі додають важливу інформацію, яка може знадобитися їм, в коментар до комірки. Майте на увазі те, що знаходиться в коментарях, ви можете тільки подивитися (якщо знайдете). Витягти це в комірку важко. Рекомендую краще виділити окремий стовпець для коментарів.
Бардак із форматуванням
Безперечно не додасть вашій таблиці нічого хорошого. Це виглядає відразливо для людей, які користуються вашими таблицями. У кращому разі цьому не нададуть значення, у гіршому — подумають, що ви не організовані та неохайні у справах. Прагніть наступного:
Об'єднання осередків
Використовуйте об'єднання осередків лише тоді, коли без нього ніяк. Об'єднані осередки сильно ускладнюють маніпулювання діапазонами, до яких вони входять. Виникають проблеми при переміщенні осередків, при вставці осередків тощо.
Об'єднання тексту та чисел в одному осередку
Тяжке враження справляє осередок, що містить число, доповнене ззаду текстовою константою « РУБ.» або "USD", введеної вручну. Особливо якщо це не друкована форма, а звичайна таблиця. Арифметичні операції з такими осередками природно неможливі.
Числа у вигляді тексту в комірці
Уникайте збереження цифрових даних у комірці у форматі тексту. Згодом частина осередків у такому стовпці у вас матимуть текстовий формат, а частина – у звичайному. Через це будуть проблеми із формулами.
Якщо ваша таблиця презентуватиметься через LCD проектор
Вибирайте максимально контрастні комбінації кольору та фону. Добре виглядає на проектор темний фон і світлі літери. Найстрашніше враження справляє червоний на чорному і навпаки. Це поєднання дуже неконтрастно виглядає на проекторі - уникайте його.
Сторінковий режим листа в Excel
Це той самий режим, коли Excel показує, як лист буде розбитий на сторінки під час друку. Кордони сторінок виділяються блакитним кольором. Не рекомендую постійно працювати в цьому режимі, що багато хто робить, тому що в процесі виведення даних на екран бере участь драйвер принтера, а це в залежності від багатьох причин (наприклад, мережний принтер і в даний момент недоступний) загрожує підвисаннями процесу візуалізації і перерахунку формул. Працюйте у звичайному режимі.
Ще більше корисної інформації про Excel можна дізнатися на
При створенні складних формул (та й просто неуважності) у MS Excel помилку зробити досить легко. Зазвичай MS Excel у таких випадках виводить повідомлення про помилки або навіть пропонує «правильний» на його погляд варіант написання формули, проте навіть за наявності довідкової системи спочатку досить складно зрозуміти, чого хоче від нас «дурна програма». У цій статті ми розглянемо всі типи помилок, що виникають у формулах MS Excel, і навчимося їх виправляти та розуміти.
Помилка #ЗНАЧ! (помилка у значенні)
Якби був «топ помилок MS Excel», перше місце належало б помилці #ЗНАЧ!. Як можна здогадатися з назви, виникає вона у разі, коли у формулу чи функцію підставлено неправильне значення. Якщо ви намагаєтеся провести арифметичні операції з текстом, або підставляєте в функцію діапазон осередків, коли потрібно вказати лише одну комірку, результатом обчислень буде помилка #ЗНАЧ!.
Як і говорилося - спроба скласти число і текст ставить MS Excel у глухий кут
Помилка #ПОСИЛКА! (Неправильне посилання на комірку)
Одна з найчастіших помилок під час обчислень. Позначає найпростішу річ — у формулі використовується посилання на комірку, яку ви або не створювали або ненароком видалили. Найчастіше #ПОСИЛАННЯ!виникає коли ви видаляєте «непотрібний» стовпець, деякі осередки якого, як виявляється, брали участь у обчисленнях.
Помилка #СПРАВ/0! (ділення на нуль)
Зі шкільної лави ми пам'ятаємо просте правило: на нуль ділити не можна! Помилка #СПРАВ/0!- Це попередження від MS Excel про те, що це базове правило порушено і ви все-таки намагаєтеся розділити деяке число на нуль. При цьому сам «нуль» не є обов'язковим — будь-яка спроба розділити існуюче число на «порожній» осередок також викличе цю помилку.
Ділити на нуль не можна - порожній осередок сприймається MS Excel як той же нуль
Помилка #Н/Д (значення недоступне)
Помилка #Н/Двиникає в тому випадку, якщо в функції пропущено якийсь аргумент, або одне з значень, що використовуються у формулі, стає недоступно. Побачив #Н/Д — насамперед шукай чогось у твоїх обчисленнях не вистачає.
Застосовую функцію ВПР, знак поділу поставив, а ось вказати до якого осередку він належить - забув
Помилка #ІМ'Я? (Неприпустиме ім'я)
Помилка #ІМ'Я— ознака того, що ви та Excel один одного не зрозуміли. Точніше MS Excel не зрозумів, що ви мали на увазі — ви явно вказуєте на якийсь елемент, а програма його не може знайти. У яких випадках це зазвичай відбувається?
- У функції вказано осередок або діапазон осередків з неіснуючим (найчастіше з неправильно введеним) ім'ям.
Спроба підсумувати неіснуючий діапазон під назвою Стовпець
- Текст усередині функції полягає у лапки. Якщо цього не відбувається (тобто замість = "Вася" ми вводимо = Вася), MS Excel приходить в повне здивування.
Ще одна найпростіша помилка - текст у функціях та формулах вказується в лапках
- У назві функції випадково допущено друкарську помилку.
Помилка #ПУСТО! (порожня безліч)
Помилка #ПУСТОнайчастіше виникає коли у формулі пропущено один із операторів, але може виникати і в тому випадку, коли нам потрібно знайти перетин двох діапазонів осередків, а цього перетину просто не існує.
Все б добре, але забув про другий знак «+»
Помилка #ЧИСЛО! (Неправильне число)
Помилка #КІЛЬКІСТЬ! ms Excel видає у випадках, коли результат математичних обчислень у формулі породжує якийсь зовсім нереальний результат. Результат у вигляді гранично великого чи малого числа, спроба обчислити корінь із негативного числа — це призведе до виникнення помилки #ЧИСЛО!
Обчислити корінь із негативного числа? Вас би не зрозумів не лише Excel
Знаки «решітки» в осередку Excel (#######)
У минулому дуже поширена «помилка» MS Excel пов'язана з раптовим заповненням комірки знаками решітки (#) могла бути викликана тим, що в комірку введено число яке не міститься в ній цілком (але якщо комірка має формат «числовий» або «дата») .
З появою MS Office 2013 помилка практично зійшла нанівець, тому що «розумнілий» Excel став у більшості випадків автоматично збільшувати ширину комірки під число. Якщо ж ви бачите «решітки», найпростіше позбутися їх збільшивши ширину комірки вручну.
Достатньо збільшити ширину стовпця і проблема зникне
Якщо ви знаєте в яких випадках в екселі виникає та чи інша помилка, ви швидше за все відразу зрозумієте, чим вона викликана. Однак програма ще більше спрощує вам роботу і виводить поруч із помилковим значенням спецсимвол у вигляді значка знака оклику в жовтому ромбі. При натисканні на нього вам буде запропоновано список можливих дій з виправлення помилки.
Натисніть значок, щоб отримати допомогу у виправленні помилки
У першому рядку контекстного меню, що з'явилося, ви побачите повну назву помилки, у другому зможете викликати докладну довідку по ній, але найцікавіше приховує в собі третій пункт: « Показати етапи обчислення.«.
«Показати етапи обчислення…» — програму не обдуриш, точно виводить фрагмент формули, де допущена помилка
Натисніть на нього і в вікні, що з'явилося, побачите той самий фрагмент формули де допущена помилка - це особливо зручно, коли «розплутувати» доводиться цілий клубок з громади вкладених один в одного дій.
При помилкових обчисленнях формули відображають кілька типів помилок замість значень. Розглянемо їх у практичних прикладах у процесі роботи формул, які дали помилкові результати обчислень.
Помилки у формулі Excel відображаються в комірках
У цьому уроці буде описано значення помилок формул, які можуть містити комірки. Знаючи значення кожного коду (наприклад: #ЗНАЧ!, #СПРАВА/0!, #ЧИСЛО!, #Н/Д!, #ІМ'Я!, #ПУСТО!, #ПОСИЛКА!) можна легко розібратися, як знайти помилку у формулі та усунути її.
Як прибрати #ДІЛ/0 в Excel
Як видно при розподілі на комірку з порожнім значенням програма сприймає як розподіл на 0. У результаті видає значення: # СПРАВ/0! У цьому вся можна переконатися і з допомогою підказки.
В інших арифметичних обчисленнях (множення, підсумовування, віднімання) порожній осередок також є нульовим значенням.
Результат помилкового обчислення - #КІЛЬКІСТЬ!
Неправильне число: #ЧИСЛО! - Це помилка неможливості виконати обчислення у формулі.
Декілька практичних прикладів:
Помилка: #КІЛЬКІСТЬ! виникає, коли числове значення занадто велике або занадто маленьке. Так само дана помилка може виникнути при спробі отримати корінь із негативного числа. Наприклад, = КОРІНЬ (-25).
У осередку А1 – дуже велика кількість (10^1000). Excel не може працювати з такими великими числами.
У осередку А2 – та сама проблема з великими числами. Здавалося б, 1000 невелике число, але при поверненні його факторіалу виходить занадто велике числове значення, з яким Excel не впорається.
У осередку А3 – квадратний корінь може бути з негативного числа, а програма відобразила цей результат цієї ж помилкою.
Як прибрати НД в Excel
Значення недоступне: #Н/Д! означає, що значення є недоступним для формули:
Записана формула B1: =ПОШУКПОЗ("Максим"; A1:A4) шукає текстовий вміст "Максим" в діапазоні осередків A1:A4. Вміст знайдено у другому осередку A2. Отже, функція повертає результат 2. Друга формула шукає текстовий вміст «Андрій», діапазон A1:A4 не містить таких значень. Тому функція повертає помилку #Н/Д (немає даних).
Помилка #ІМ'Я! в Excel
Відноситися до категорії помилки у написанні функцій. Неприпустиме ім'я: #ІМ'Я! - означає, що Excel не розпізнав тексту написаного у формулі (назва функції = СУМ() йому невідомо, воно написано з помилкою). Це результат помилки синтаксису під час написання імені функції. Наприклад:
Помилка #ПУСТО! в Excel
Порожня множина: #ПОРОЖНЯ! - Це помилки оператора перетину множин. В Excel існує таке поняття як перетин множин. Воно застосовується для швидкого отримання даних великих таблиць за запитом точки перетину вертикального і горизонтального діапазону осередків. Якщо діапазони не перетинаються, програма відображає хибне значення – #ПУСТО! Оператором перетину множин є одиночний пробіл. Їм поділяються вертикальні та горизонтальні діапазони, задані в аргументах функції.
У цьому випадку перетином діапазонів є комірка C3 і функція відображає її значення.
Задані аргументи функції: =СУМ(B4:D4 B2:B3) – не утворюють перетин. Отже, функція дає значення з помилкою - #ПУСТО!
#ПОСИЛАННЯ! – помилка посилань на комірки Excel
У цьому прикладі помилка виникала при неправильному копіюванні формули. У нас є 3 діапазони осередків: A1: A3, B1: B4, C1: C2.
Під першим діапазоном в комірку A4 вводимо формулу, що підсумовує: =СУММ(A1:A3). А далі копіюємо цю формулу під другий діапазон, в комірку B5. Формула, як і раніше, підсумовує лише 3 осередки B2: B4, минаючи значення першої B1.
Коли та сама формула була скопійована під третій діапазон, в комірку C3 функція повернула помилку #ПОСИЛКА! Так як над коміркою C3 може бути тільки 2 комірки, а не 3 (як того вимагала вихідна формула).
Примітка. У цьому випадку найзручніше під кожним діапазоном перед початком введення натиснути комбінацію гарячих клавіш ALT+=. Тоді вставиться функція підсумовування і автоматично визначить кількість комірок, що підсумовують.
Як виправити ЗНАЧ в Excel
#ЗНАЧ! - Помилка в значенні. Якщо ми намагаємося скласти число і слово Excel в результаті ми отримаємо помилку #ЗНАЧ! Цікавий той факт, що якби ми спробували скласти два осередки, в яких значення першої число, а другий - текст за допомогою функції = СУМ (), то помилки не виникне, а текст прийме значення 0 при обчисленні. Наприклад:
Грати в осередку Excel
Ряд ґрат замість значення осередку ###### – це значення не є помилкою. Просто це інформація про те, що ширина стовпця занадто вузька для того, щоб вмістити вміст комірки, що коректно відображається. Потрібно просто розширити стовпець. Наприклад, зробіть подвійне клацання лівою кнопкою мишки на межі заголовків стовпців цієї комірки.
Так решітки (######) замість значення осередків можна побачити за негативної дати. Наприклад, ми намагаємося відібрати від старої дати нову дату. А в результаті обчислення встановлено формат осередків "Дата" (а не "Загальний").
Неправильний формат комірки також може відображати замість значень ряд символів решітки (######).
Якщо Excel не може правильно оцінити формулу чи функцію робочого листа; він відобразить значення помилки - наприклад, #ІМ'Я?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ПОСИЛКА! - у комірці, де знаходиться формула. Розберемо типи помилок у Excelїх можливі причини, і як їх усунути.
Помилка #ІМ'Я?
Помилка #ІМ'Яз'являється, коли ім'я, яке використовується у формулі, було видалено або не було визначено раніше.
Причини виникнення помилки #ІМ'Я?:
- Якщо у формулі використовується ім'я, яке видалено або не визначено.
Помилки в Excel - Використання імені у формулі
Усунення помилки: визначте ім'я Як це зробити описано в цій статті.
- Помилка написання імені функції:
Помилки в Excel - Помилка в написанні функції ПОШУКПОЗ
Усунення помилки: перевірте правильність написання функції.
- У посиланні на діапазон осередків пропущено знак двокрапки (:).
Помилки в Excel - Помилка в написанні діапазону осередків
Усунення помилки: виправте формулу У наведеному прикладі це =СУМ(A1:A3).
- У формулі використовується текст, не укладений у подвійні лапки. Excel видає помилку, тому що сприймає такий текст як ім'я.
Помилки в Excel - Помилка в поєднанні тексту з числом
Усунення помилки: укласти текст формули в подвійні лапки.
Помилки в Excel - Правильне поєднання тексту
Помилка #ЧИСЛО!
Помилка #ЧИСЛО! в Excel виводиться, якщо формула містить некоректне число. Наприклад:
- Використовуйте негативне число, коли потрібне позитивне значення.
Помилки в Excel - Помилка у формулі, негативне значення аргументу у функції КОРІНЬ
Усунення помилки: перевірте правильність введених аргументів у функції.
- Формула повертає число, яке занадто велике чи занадто мало, щоб його можна було у Excel.
Помилки в Excel - Помилка у формулі через занадто велике значення
Усунення помилки: скоригуйте формулу так, щоб в результаті виходило число в доступному діапазоні Excel.
Помилка #ЗНАЧ!
Дана помилка Excelвиникає у тому випадку, коли у формулі введено аргумент неприпустимого значення.
Причини помилки #ЗНАЧ!:
- Формула містить пробіли, символи або текст, але має бути число. Наприклад:
Помилки в Excel - Підсумовування числових та текстових значень
Усунення помилки: перевірте, чи правильно задані типи аргументів у формулі.
- У аргументі функції введено діапазон, а функція передбачається введення одного значення.
Помилки в Excel - У функції ВПР як аргумент використовується діапазон замість одного значення
Усунення помилки: вкажіть у функції правильні аргументи.
- З використанням формули масиву натискається клавіша Enter і Excel виводить помилку, оскільки сприймає її як нормальну формулу.
Усунення помилкиДля завершення введення формули використовуйте комбінацію клавіш Ctrl+Shift+Enter .
Помилки в Excel - Використання формули масиву
Помилка #ПОСИЛКА
Помилки в Excel - Помилка у формулі, через віддалений стовпчик А
Усунення помилки: змініть формулу
Помилка #СПРАВ/0!
Дана помилкаExcelвиникає при розподілі на нуль, тобто коли як дільник використовується посилання на комірку, яка містить нульове значення, або посилання на порожню комірку.
Помилки в Excel - Помилка #ДІЛ/0!
Усунення помилки: виправте формулу
Помилка #Н/Д
Помилка #Н/Д в Excelозначає, що у формулі використовується недоступне значення.
Причини помилки #Н/Д:
- При використанні функції ВПР, ДПР, ПЕРЕГЛЯД, ПОШУКПОЗ використовується невірний аргумент шукане_значення:
Помилки в Excel - Шуканого значення немає в масиві, що переглядається.
Усунення помилки: задайте правильний аргумент шукане значення.
- Помилки використання функцій ВПР чи ГПР.
Усунення помилки: див. розділ присвячений
- Помилки в роботі з масивами: використання розмірів діапазонів, що не відповідають. Наприклад, аргументи масиву мають менший розмір, ніж результуючий масив:
Помилки у Excel - Помилки у формулі масиву
Усунення помилки: відкоригуйте діапазон посилань формули з відповідністю рядків і стовпців або введіть формулу масиву в комірки, що відсутні.
- Функції не задані один або кілька обов'язкових аргументів.
Помилки у Excel - Помилки у формулі, немає обов'язкового аргументу
Усунення помилки: введіть усі необхідні аргументи функції.
Помилка #ПУСТО!
Помилка #ПУСТО! в Excelвиникає коли, у формулі використовуються діапазони, що не перетинаються.
Помилки в Excel - Використання у формулі СУМ непересічні діапазони
Усунення помилки: перевірте правильність написання формули
Помилка ####
Причини виникнення помилки
- Ширини стовпця недостатньо, щоб відобразити вміст комірки.
Помилки в Excel - Збільшення ширини стовпця для відображення значення в осередку
Усунення помилки: збільшення ширини стовпця/стовпців.
- Осередок містить формулу, яка повертає негативне значення при розрахунку дати або часу. Дата і час у Excel мають бути позитивними значеннями.
Помилки в Excel - Різниця дат і годинника не повинна бути негативною
Усунення помилки: перевірте правильність написання формули, число днів чи годин було позитивним числом
Списки та діапазони (5)
Макроси(VBA процедури) (63)
Різне (39)
Баги та глюки Excel (3)
Як у комірці з формулою замість помилки показати 0
Трапляються ситуації, коли в робочій книзі на аркушах створено багато формул, які виконують різні завдання. При цьому формули створені колись давно, можливо, навіть на вами. І формули повертають помилки. Наприклад #СПРАВ/0! (#DIV/0!) . Ця помилка виникає, якщо всередині формули відбувається розподіл на нуль: = A1 / B1 де в B1 нуль або порожньо. Але можуть бути інші помилки (#Н/Д, #ЗНАЧ! і т.д.). Можна змінити формулу, додавши перевірку на помилку:
=IF(ISERR(A1/B1),0, A1/B1)
аргументи:
=ЯКЩО(ЕОШ(1 аргумент);2 аргумент; 1 аргумент)
Ці формули працюватимуть у будь-якій версії Excel. Щоправда, функція ЕОШ не обробить помилку #Н/Д (#N/A). Щоб так само обробити і #Н/Д необхідно використовувати функцію ПОМИЛКА:
=ЯКЩО(ПОМИЛКА(A1 / B1);0; A1 / B1)
=IF(ISERROR(A1/B1),0, A1/B1)
Однак далі за текстом я застосовуватиму ЕОШ(т.к. вона коротша) і до того ж не завжди треба "не бачити" помилки #Н/Д.
Але для версій Excel 2007 і вище можна застосувати більш оптимізовану функцію ЯСЛИПОМИЛКА (IFERROR):
=ЯКЛИПОМИЛКА(A1 / B1 ;0)
=IFERROR(A1 / B1 ,0)
аргументи:
=ЯКЛИПОМИЛКА(1 аргумент; 2 аргумент)
1 аргумент: вираз для обчислення
2 аргумент: значення або вираз, який необхідно повернути в комірку у разі помилки у першому аргументі.
Чому ЯСЛИПОМИЛКА краща і я називаю її більш оптимізованою? Розберемо першу формулу докладніше:
=ЯКЩО(ЕОШ(A1 / B1);0; A1 / B1)
Якщо обчислити покроково, то побачимо, що спочатку відбувається обчислення виразу A1/B1 (тобто поділ). І якщо його результат помилка - то ЄОШ поверне ІСТИНА (TRUE), яке буде передано в ЯКЩО (IF). І тоді функцією ЯКЩО(IF) буде повернено значення другого аргументу 0.
Але якщо результат не є помилковим і ЕОШ (ISERR) повертає Брехня (FALSE) – то функція заново обчислюватиме вже обчислений раніше вираз: A1 / B1
З наведеною формулою це особливої ролі не грає. Але якщо застосовується формула на кшталт ВПР (VLOOKUP) з переглядом кілька тисяч рядків – то обчислення двічі може значно збільшити час перерахунку формул.
Функція ж ЕСЛИПОМИЛКА (IFERROR) один раз обчислює вираз, запам'ятовує його результат і якщо він помилковий повертає записаний другим аргументом. Якщо ж помилки немає, то повертає запам'ятований результат обчислення виразу з першого аргументу. Тобто. обчислення за фактом відбувається один раз, що практично не впливатиме на швидкість загального перерахунку формул.
Тому якщо у вас Excel 2007 і вище і файл не буде використовуватися в більш ранніх версіях - то має сенс використовувати саме ПОЛИШНЯ (IFERROR).
Навіщо формули з помилками взагалі виправляти? Зазвичай робиться для більш естетичного відображення даних у звітах, особливо якщо звіти потім керівництву надсилають.
Отже, є на аркуші такі формули, помилки яких треба опрацювати. Якщо подібних формул для виправлення одна-дві (і навіть 10-15) – то проблем майже немає замінити вручну. Але якщо таких формул кілька десятків, а то й сотень – проблема набуває майже світових масштабів:-). Проте процес можна спростити через написання щодо простого Visual Basic for Application.
Для всіх версій Excel:
| Sub IfIsErrNull() Const sToReturnVal As String = "0", vbInformation, "www.сайт" =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" If Left(s, 9)<>"IF(ISERR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc. .Number Then MsgBox "Формули оброблені" |
Sub IfIsErrNull() Const sToReturnVal As String = "0" "якщо необхідно замість нуля повертати порожньо "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Виділений діапазон не містить даних", vbInformation, "www..HasFormula Then s = rc. =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" If Left(s, 9)<>"IF(ISERR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc. .Number Then MsgBox "Неможливо перетворити формулу в комірці: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..сайт" End If End Sub
Для версій 2007 та вище
| Sub IfErrorNull() Const sToReturnVal As String = "0" "якщо необхідно замість нуля повертати порожньо "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String , ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Виділений діапазон не містить даних", vbІнформація, "www.сайт" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc. " & sToReturnVal & ")" If Left(s, 8)<>"IFERROR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc. Then MsgBox "Неможливо перетворити формулу в осередку: "& ss & vbNewLine & _ Err.Description, vbInformation, "www.сайт" Else MsgBox "Формули оброблені", vbInformation, "www.сайт" End If End Sub |
Sub IfErrorNull() Const sToReturnVal As String = "0" "якщо необхідно замість нуля повертати пусто "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Виділений діапазон не містить даних", vbInformation, "www..HasFormula Then s = rc. =" & "IFERROR(" & s & "," & sToReturnVal & ")" If Left(s, 8)<>"IFERROR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc. Then MsgBox "Неможливо перетворити формулу в комірці: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..сайт" End If End Sub
Як це працює
Якщо не знайомі з макросами, то спочатку краще прочитати як їх створювати і викликати: Що таке макрос і де його шукати? , т.к. може статися так, що все зробите правильно, але забудете макроси дозволити і нічого не заробить.
Копіюєте наведений код, переходьте до редактора VBA( Alt+F11), створюєте стандартний модуль ( Insert -Module) і просто вставляєте в нього код. Переходьте в потрібну книгу Excel і виділяєте всі осередки, формули в яких необхідно перетворити таким чином, щоб у разі помилки вони повертали нуль. Тиснете Alt+F8, вибираєте код IfIsErrNull(або IfErrorNull, в залежності від того, який саме скопіювали) і тиснете Виконати.
До всіх формул у виділених осередках буде додано функцію обробки помилки. Наведені коди враховують так само:
-якщо у формулі вже застосована функція ЕСЛИПОМИЛКА або ЕСЛИ(ЕОШ, то така формула не обробляється;
-код коректно обробить так само функції масиву;
-Виділяти можна несуміжні осередки(через Ctrl).
У чому недолік:складні та довгі формули масиву можуть викликати помилку коду, у зв'язку з особливістю даних формул та їх обробкою з VBA. У такому разі код напише про неможливість продовжити роботу та виділить проблемний осередок. Тому рекомендую робити заміни на копіях файлів.
Якщо значення помилки треба замінити порожньо, а чи не нуль, треба рядок
"Const sToReturnVal As String = """"""
Видалити апостроф ( " )
Також можна цей код викликати натисканням кнопки (Як створити кнопку для виклику макросу на аркуші) або помістити в надбудову (Як створити свою надбудову?), щоб можна було викликати з будь-якого файлу.
І невеликий додаток: намагайтеся застосовувати код вдумливо. Не завжди повернення помилки заважає. Наприклад, при використанні ВПР іноді корисно бачити, які значення не були знайдені.
Також хочу зазначити, що застосовувати треба до реально працюючих формул. Оскільки формула повертає #ИМЯ!(#NAME!), це означає, що у формулі невірно записаний якийсь аргумент і це помилка запису формули, а чи не помилка результату обчислення. Такі формули краще проаналізувати та знайти помилку, щоб уникнути логічних помилок розрахунків на аркуші.
("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left"," :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","text ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"text textcss":"display:block;padding:12px;"textbgcss":"display:bsol; ; background-color:#333333; opacity:0.6; position:relative; font:bold 14px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssress "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
Поломки
