Ошибка в экселе число. Скрытие значений и индикаторов ошибок в ячейках

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

Сразу оговорюсь, что материал статьи предназначается для начинающих пользователей Excel. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».

Вы не даёте заголовки столбцам таблиц

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

Пустые столбцы и строки внутри ваших таблиц

Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.

На одном листе располагается несколько таблиц

Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.

Вам будет неудобно полноценно работать больше чем с одной таблицей на листе. Например, если одна таблица располагается слева, а вторая справа, то фильтрация одной таблицы будет влиять и на другую. Если таблицы расположены одна под другой, то невозможно воспользоваться закреплением областей, а также одну из таблиц придётся постоянно искать и производить лишние манипуляции, чтобы встать на неё табличным курсором. Оно вам надо?

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтобы , вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel - формул и сводных таблиц.

Если вы построите сводную таблицу, то обнаружите, что нет возможности легко получить данные по году или кварталу, так как показатели разнесены по разным полям. У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей. Придётся создавать руками отдельные вычисляемые поля для кварталов и года, хотя, будь это всё в одном столбце, сводная таблица сделала бы это за вас.

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

Разнесение информации по разным листам книги «для удобства»

Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel. Например, часто создают отдельные листы на каждый месяц или год. В результате объём работы по анализу данных фактически умножается на число созданных листов. Не надо так делать. Накапливайте информацию на ОДНОМ листе.

Информация в комментариях

Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.

Бардак с форматированием

Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:

Объединение ячеек

Используйте объединение ячеек только тогда, когда без него никак. Объединенные ячейки сильно затрудняют манипулирование диапазонами, в которые они входят. Возникают проблемы при перемещении ячеек, при вставке ячеек и т.д.

Объединение текста и чисел в одной ячейке

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

Числа в виде текста в ячейке

Избегайте хранить числовые данные в ячейке в формате текста. Со временем часть ячеек в таком столбце у вас будут иметь текстовый формат, а часть в обычном. Из-за этого будут проблемы с формулами.

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

Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

Страничный режим листа в Excel

Это тот самый режим, при котором Excel показывает, как лист будет разбит на страницы при печати. Границы страниц выделяются голубым цветом. Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.

Ещё больше полезной информации про Excel можно узнать на

Ошибки в Экселе – непременный спутник всех, кто . Когда выражение в ячейке рассчитать невозможно, программа отображает в ячейке сообщение об ошибке. Оно начинается со знака «#», после которого записывается имя ошибки. Этого не нужно пугаться, если вы ориентируетесь в функциях Эксель и умеете следовать простейшей логике математических операций – легко найдёте и исправите ошибку.

Если ячейка полностью заполнена знаками решётки (#), это вовсе не ошибка. В ячейке недостаточно места для отображения результата. Увеличьте размеры ячейки или уменьшите шрифт, чтобы результат мог отобразиться.

Типы ошибок

Если ошибка всё же произошла, помочь в её исправлении поможет расшифровка:

Когда в ячейке формула с ошибкой, возле нее появляется маркер. Нажав на него, вы можете почитать справку по этой ошибке. А еще можно посмотреть этапы вычисления. Выберите этот пункт, и программа покажет окно, где место ошибки будет подчёркнуто. Это лучший способ определить место, где возникает ошибка.

Кроме того, можно обойтись без исправления таких ошибок, а просто их . Но это должно быть уместно. Ошибки нужно обходить, только если нельзя исправить. В противном случае, результаты расчетов могут быть искажены.

Циклические ссылки в Excel

Еще один тип ошибки – это циклическая ссылка. Она возникает, когда вы ссылаетесь на ячейку, значение которой зависит от той, в которой вы пишете формулу. Например, в клетке А1 записана формула =А2+1 , а в А2 написать =A1 , возникнет циклическая ссылка, которая будет пересчитываться бесконечно. В этом случае, программа предупреждает о появлении циклической ссылки, останавливает расчет «зацикленных формул». В левой части ячеек появляется двунаправленная стрелка. Придётся исправить возникшую ошибку и повторить расчет.

Иногда случается сложное «зацикливание», когда циклическая ссылка образуется с несколькими промежуточными формулами.

Чтобы отследить такие ошибки, выполните Формулы – Зависимости формул – Проверка наличия ошибок – Циклические ссылки . В выпадающем списке программа выводит адреса ячеек, создающих бесконечный цикл. Остаётся только исправить формулы в этих клетках.

В Excel можно попытаться рассчитать результат зацикленных формул. Для этого установите галочку Файл – Параметры – Формулы – Включить итеративные вычисления . В этом же блоке можно задать максимальное количество итераций (просчётов) для нахождения баланса и допустимую погрешность. В большинстве случаев этого делать не нужно, поэтому я рекомендую эту галку не устанавливать. Тем не менее, когда Вы знаете, что зацикленные формулы верны и их расчет приведет к устойчивому результату — почему бы это не сделать?

Вот и всё про типы ошибок в Эксель. В этой короткой статье вы получили достаточно информации, чтобы справиться с самыми распространенными ошибками в Excel путем анализа возвращаемого значения. А вот расширенный перечень ошибок читайте в ! Готов ответить на ваши вопросы – пишите в комментариях.

В следующей статье я расскажу . Стоит ли говорить, что функции Эксель — «наше всё»?
Думаю, нет. Потому, переходите и читайте, это будет первый шаг в мир сложных формул с правильными результатами!

Как в ячейке с формулой вместо ошибки показать 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" "если необходимо вместо нуля возвращать пусто "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..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:alpha(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}}

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

1. #ДЕЛ/О! — данная ошибка практически всегда означает, что формула в ячейке пытается разделить какое-то значение на ноль. Чаще всего это происходит из-за того, что в другой ячейке, ссылающейся на данную, находится нулевое значение или значение отсутствует. Вам необходимо проверить все связанные ячейки на предмет наличия таких значений. Также данная ошибка может возникать, когда вы вводите неправильные значения в некоторые функции, например в ОСТАТ() , когда второй аргумент равен 0. Также ошибка деления на ноль может возникать, если вы оставляете пустые ячейки для ввода данных, а какая-либо формула требует некоторые данные. При этом будет выведена ошибка #ДЕЛ/0! , что может смутить конечного пользователя. Для этих случаев вы можете использовать функцию ЕСЛИ() для проверки, например =ЕСЛИ(А1=0;0;В1/А1) . В этом примере функция вернет 0 вместо ошибки, если в ячейке А1 находится нулевое или пустое значение.
2. #Н/Д — данная ошибка расшифровывается как недоступно, и это означает, что значение недоступно функции или формуле. Вы можете увидеть такую ошибку, если введете неподходящее значение в функцию. Для исправления проверьте прежде всего входные ячейки на предмет ошибок, особенно если в них тоже появляется данная ошибка.
3. #ИМЯ? — данная ошибка возникает, когда вы неправильно указываете имя в формуле или ошибочно задаете имя самой формулы. Для исправления проверьте еще раз все имена и названия в формуле.
4. #ПУСТО! — данная ошибка связана с диапазонами в формуле. Чаще всего она возникает, когда в формуле указывается два непересекающихся диапазона, например =СУММ(С4:С6;А1:С1) .
5. #ЧИСЛО! — ошибка возникает, когда в формуле присутствуют некорректные числовые значения, выходящие за границы допустимого диапазона.
6. #ССЫЛКА! — ошибка возникает, когда были удалены ячейки, на которые ссылается данная формула.
7. #ЗНАЧ! — в данном случае речь идет об использовании неправильного типа аргумента для функции.

Если при вводе формулы вы случайно неправильно расставили скобки, Excel выведет на экран предупреждающее сообщение — см. рис. 1. В этом сообщении вы увидите предположение Excel о том, как их необходимо расставить. В случае если вы подтверждаете такую расстановку, нажмите Да . Но зачастую требуется собственное вмешательство. Для этот нажмите Нет и исправьте скобки самостоятельно.

Обработка ошибок с помощью функции ЕОШИБКА()

Перехватить любые ошибки и обработать их можно с помощью функции ЕОШИБКА() . Данная функция возвращает истину или ложь в зависимости от того, появляется ли ошибка при вычислении ее аргумента. Общая формула для перехвата выглядит так: =ЕСЛИ(ЕОШИБКА(выражение);ошибка; выражение) .

Функция если вернет ошибку (например, сообщение), если при расчете появляется ошибка. Например, рассмотрим следующую формулу: =ЕСЛИ(ЕОШИБКА(А1/А2);""; А1/А2) . При возникновении ошибки (деление на 0) формула возвращает пустую строку. Если же ошибки не возникает, возвращается само выражение А1/А2 .

Существует другая, более удобная функция ЕСЛИОШИБКА() , которая совмещает две предыдущие функции ЕСЛИ() и ЕОШИБКА() : ЕСЛИОШИБКА(значение;значение при ошибке) , где: значение — выражение для расчета, значение при ошибке — возвращаемый результат в случае ошибки. Для нашего примера это будет выглядеть так: =ЕСЛИОШИБКА(А1/А2;"") .