Читачі Інфіяа вже знайомі з Денисом Батьяновим, Який ділився з нами. Сьогодні Денис розповість про те, як уникнути найпоширеніших проблем з Excel, які ми часто створюємо собі самостійно.

Відразу обмовлюся, що матеріал статті призначається для початківців користувачів Excel. Досвідчені користувачі вже запально танцювали на цих граблях не раз, тому моє завдання уберегти від цього молодих і недосвідчених «танцюристів».

Ви не даєте заголовки стовпцями таблиць

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

Порожні стовпчики і рядки всередині ваших таблиць

Це збиває з пантелику Excel. Зустрівши порожній рядок або стовпець всередині вашої таблиці, він починає думати, що у вас 2 таблиці, а не одна. Вам доведеться постійно його поправляти. Також не варто приховувати непотрібні вам рядки / стовпці всередині таблиці, краще видаліть їх.

На одному аркуші розташовується кілька таблиць

Якщо це не крихітні таблиці, що містять довідники значень, то так робити не варто.

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

Дані одного типу штучно розташовуються в різних стовпчиках

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

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

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

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

Якщо ви захочете застосувати стандартні формули підсумовування типу СУММЕСЛИ (SUMIF), СУММЕСЛІМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то також виявите, що вони не зможуть ефективно працювати з такою компоновкою таблиці.

Рознесення інформації з різних листам книги «для зручності»

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

Інформація в коментарях

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

Бардак з форматуванням

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

об'єднання осередків

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

Об'єднання тексту і чисел в одній комірці

Тяжке враження справляє осередок, що містить число, доповнене ззаду текстової константою «РУБ.» або »USD», введеної вручну. Особливо, якщо це не печатна форма, а звичайна таблиця. Арифметичні операції з такими осередками природно неможливі.

Числа у вигляді тексту в осередку

Уникайте зберігати числові дані в осередку в форматі тексту. Згодом частина осередків в такому стовпці у вас будуть мати текстовий формат, а частина в звичайному. Через це будуть проблеми з формулами.

Якщо ваша таблиця буде презентуватися через LCD проектор

Вибирайте максимально контрастні комбінації кольору і фону. Добре виглядає на проекторі темний фон і світлі букви. Найжахливіше враження справляє червоний на чорному і навпаки. Це поєднання вкрай неконтрастний виглядає на проекторі - уникайте його.

Сторінковий режим листа в Excel

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

Ще більше корисної інформаціїпро Excel можна дізнатися на

Дата: 24 грудень 2015 Категорія:

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

Якщо осередок повністю заповнена знаками решітки (#), це зовсім не помилка. В осередку недостатньо місця для відображення результату. Збільште розміри осередку або зменшіть шрифт, щоб результат міг відобразитися.

типи помилок

Якщо помилка все ж таки відбулася, допомогти в її виправленні допоможе розшифровка:

Помилка	опис
# СПРАВ / 0!	Помилка виникає при спробі розподіл на нуль
# ІМ'Я?	Програма не може розпізнати введене ім'я. Наприклад, ви помилилися при написанні імені функції, або не уклали текстовий рядок в лапки
# Н / Д	Дані недоступні. Наприклад, не знайшла жодного значення
# ПУСТО!	Ви запросили перетин діапазонів, які не перетинаються
# ЧИСЛО!	Проблема в одному з числових значень, які використовуються у формулі. Наприклад, Ви намагаєтеся витягти квадратний корінь з негативного числа. У класичній математиці ця операція позбавлена ​​сенсу
#ПОСИЛАННЯ!	Формула містить посилання, якої немає. Наприклад, ви видалили осередок, на яку вона посилається
# Значить!	Формула містить неприпустимі компоненти. Часто така помилка виникає при порушенні синтаксису формул

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

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

Відстеження помилки через етапи обчислення

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

Ще один тип помилки - це циклічна посилання. Вона виникає, коли ви посилаєтеся на осередок, значення якої залежить від тієї, в якій ви пишете формулу. Наприклад, в клітці А1записана формула = А2 + 1, а в А2написати = A1, виникне циклічна посилання, яка буде перераховуватися нескінченно. В цьому випадку, програма попереджає про появу циклічного посилання, зупиняє розрахунок «зациклених формул». У лівій частині осередків з'являється двунаправленная стрілка. Доведеться виправити виниклу помилку і повторити розрахунок.

Іноді трапляється складне «зациклення», коли циклічна посилання утворюється з декількома проміжними формулами.

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

Відстеження циклічних посилань

В 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 такий плейбой і не може перетворити даний текств осередку в правильний тип даних.
Необхідно переконатися, що формула або функція посилається на ті осередки, які містять дійсні значення.
Наприклад, якщо в осередку A2 міститься число, а в осередку A3 міститься текст, то в осередку А1 з формулою = A2 + A3 буде відображатися # значить! .

# СПРАВ / 0! - Що означає і як виправити?

Ці повідомлення, що в осередку відбувається розподіл числа на 0 (нуль) або ж використовуються посилання на порожню комірку.

• У вікні відкритого листа виділіть клітинку з даної помилкою і натисніть клавішу F2.
• Коли в осередку відобразиться сама формула або функція, а також стануть виділеними все осередки, пов'язані посиланнями з цією формулою або осередком, уважно перевірте значення в виділених осередках і при необхідності внесіть корективи в формулу або змініть посилання на порожні клітинки.
• Натисніть Enter або кнопку «Введення» на рядку формул.

Якщо в якості операнда використовується порожня клітинка, то він автоматично вважається рівним нулю.

# ІМ'Я? - Що означає і як виправити?

Ці символи повідомляють, що у формулі використовується неіснуюче ім'я або неправильний оператор.

1 варіант

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

• У вікні відкритого листа, перейдіть до вкладки «Формули» і в групі «Певні імена» натисніть на кнопку «Диспетчер імен».
• У вікні «Диспетчер імен» перегляньте, чи присутній дане ім'яв списку.

Якщо дане ім'я відсутнє, то необхідно додати його згідно з інструкцією «».

2 варіант

Якщо існує помилка в написанні імені, то необхідно перевірити його орфографію.

• У вікні відкритого листа натисніть клавішу F3.
• У віконці «Вставка імені» і виберіть ім'я потрібне ім'я і натисніть кнопку «ОК».
• Внесіть виправлення (при необхідності) у формулі, яка відобразиться у відповідній клітинці.
• Для закріплення натисніть клавішу Enter.

3 варіант

Якщо у формулі використовується функція з помилкою в написанні.
Наприклад, СУМ (A1: А10) замість СУММ (A1: А10).

• У вікні відкритого листа виділіть клітинку з помилкою в написанні функції.
• Розкрийте меню кнопки «Джерело помилки» поруч з даної осередком.
• У списку команд виберіть пункт «Змінити в рядку формул».
• На рядку формул у віконці імені відобразиться правильно написання потрібної формул, згідно з яким і змініть помилкове написання.
• Закріпіть результат клацанням по клавіші Enter.

4 варіант

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

5 варіант

Якщо на засланні на діапазон комірок пропущено двокрапку, то для виправлення необхідно у формулі у всіх подібних посиланнях перевірити знак двокрапки і виправити в міру необхідності.
Наприклад, СУММ (A1 А10) замість СУММ (A1: А10).

6 варіант

# Н / Д - Що означає і як виправити?

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

1 варіант

Якщо в формулу були введені відсутні дані, а також # Н / Д або НД (), то # Н / Д необхідно замінити новими даними.

Позначення # Н / Д вводиться в комірки, дані з яких ще не доступні.

2 варіант

Якщо у функціях ГПР, ПЕРЕГЛЯД, ПОИСКПОЗ або ВПР вказується невірне значення аргументу «шукане_значення» (наприклад, посилання на діапазон комірок, що неприпустимо), то необхідно відповідно вказати посилання тільки на потрібну комірку.

3 варіант

Якщо не задані необхідні аргументи стандартної функціїлиста, то потрібно ввести всі необхідні відповідні аргументи функції.

4 варіант

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

5 варіант

Якщо для перегляду значень в несортованої таблиці використовуються функції ВПР, ГПР або ПОИСКПОЗ, для яких за замовчуванням відомості для перегляду таблиць повинні розташовуватися в порядку зростання.
У функціях ВПР і ГПР міститься аргумент «інтервальний _просмотр», що дозволяє шукати певне значенняі в несортованої таблиці. Але при цьому, щоб відшукати певне значення, аргумент «інтервальний_просмотр» повинен мати значення БРЕХНЯ.
У функції ПОИСКПОЗ міститься аргумент «тіп_сопоставленія», що дозволяє сортувати дані для пошуку. Якщо ж відповідне значення відшукати неможливо, то рекомендується поставити аргумент «тіп_сопоставленія» рівний 0.

6 варіант

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

7 варіант

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

#ПОСИЛАННЯ! - Що означає і як виправити?

1 варіант

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

2 варіант

У разі використання опцій OLE, пов'язана з незапущеній програмою, то необхідно запустити необхідну програму.

Інтерфейс OLE (Object Linking and Embedding - зв'язування і впровадження об'єктів) підтримується безліччю різних програм і використовується для приміщення документа, створеного в одній програмі, в іншу програму. Наприклад, можна вставити документ Wordв книгу Excelі навпаки.

3 варіант

4 варіант

Якщо використовується макрос, викликає макрофункції, яка при певних варіантах видає значення # ПОСИЛАННЯ! . Необхідно перевірити аргумент функції і упевнитися, що він посилається на допустимі осередку або діапазони клітинок.

# ЧИСЛО! - Що означає і як виправити?

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

1 варіант

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

2 варіант

Якщо в функції з итерацией (підбором параметрів), наприклад «ВСД» або «СТАВКА», неможливо знайти результат, то необхідно спробувати інше початкове наближення або змінити число ітерацій.

3 варіант

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

# ПУСТО! - Що означає і як виправити?

Ці повідомлення про відсутність загальних осередків, коли задано пере
перетин двох областей.

1 варіант

Якщо використовується помилковий оператор діапазону, то необхідно внести виправлення, а саме:

• для позначення посилання на безперервний діапазон комірок використовується двокрапка (:) як роздільник між початковою і кінцевою осередком діапазону. Наприклад, СУММ (С1: С20).
• для позначення посилання на два непересічних діапазону використовується оператор об'єднання - крапкою з комою (;). Наприклад, СУММ (С1: С20; D1: D20).

2 варіант

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

Списки і діапазони (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.сайт" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid (s, 2) ss = " = "&" 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.Select Exit For End If End If End If Next rc If Err .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 On Error Resume Next Set rr = Intersect (Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Виділений діапазон не містить даних", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid (s, 2) ss =" = "&" 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.Select Exit For End If End If End If Next rc If Err .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 "Виділений діапазон не містить даних", VbInformation, "www.сайт" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid (s, 2) ss = "=" & "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.Select Exit For End If End If End If Next rc If Err.Number 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 Dim s As String, ss As String On Error Resume Next Set rr = Intersect (Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "Виділений діапазон не містить даних", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid (s, 2) ss =" = "&" 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.Select Exit For End If End If End If Next rc If Err.Number 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 "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; 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; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))