Если Excel не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки - например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! - в ячейке, где находится формула. Разберем типы ошибок в Excel , их возможные причины, и как их устранить.

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ? :

1. Если в формуле используется имя, которое было удалено или не определено.

Ошибки в Excel - Использование имени в формуле

Устранение ошибки : определите имя. Как это сделать описано в этой .

1. Ошибка в написании имени функции:

Ошибки в Excel - Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки : проверьте правильность написания функции.

1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel - Ошибка в написании диапазона ячеек

Устранение ошибки : исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

1. В формуле используется текст, не заключенный в двойные кавычки. Excel выдает ошибку , так как воспринимает такой текст как имя.

Ошибки в Excel - Ошибка в объединении текста с числом

Устранение ошибки : заключите текст формулы в двойные кавычки.

Ошибки в Excel - Правильное объединение текста

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel - Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки : проверьте корректность введенных аргументов в функции.

1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel - Ошибка в формуле из-за слишком большого значения

Устранение ошибки : откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.

Ошибка #ЗНАЧ!

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Причины ошибки #ЗНАЧ!:

1. Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:

Ошибки в Excel - Суммирование числовых и текстовых значений

Устранение ошибки : проверьте правильно ли заданы типы аргументов в формуле.

1. В аргументе функции введен диапазон, а функция предполагается ввод одного значения.

Ошибки в Excel - В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки : укажите в функции правильные аргументы.

1. При использовании формулы массива нажимается клавиша Enter и Excel выводит ошибку, так как воспринимает ее как обычную формулу.

Устранение ошибки : для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter .

Ошибки в Excel - Использование формулы массива

Ошибка #ССЫЛКА

Ошибки в Excel - Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки : измените формулу.

Ошибка #ДЕЛ/0!

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

Ошибки в Excel - Ошибка #ДЕЛ/0!

Устранение ошибки : исправьте формулу.

Ошибка #Н/Д

Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.

Причины ошибки #Н/Д:

1. При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel - Искомого значения нет в просматриваемом массиве

Устранение ошибки : задайте правильный аргумент искомое значение.

1. Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки : см. раздел посвященный

1. Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:

Ошибки в Excel - Ошибки в формуле массива

Устранение ошибки : откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.

1. В функции не заданы один или несколько обязательных аргументов.

Ошибки в Excel - Ошибки в формуле, нет обязательного аргумента

Устранение ошибки : введите все необходимые аргументы функции.

Ошибка #ПУСТО!

Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.

Ошибки в Excel - Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки : проверьте правильность написания формулы.

Ошибка ####

Причины возникновения ошибки

1. Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel - Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки : увеличение ширины столбца/столбцов.

1. Ячейка содержит формулу, которая возвращает отрицательное значение при расчете даты или времени. Дата и время в Excel должны быть положительными значениями.

Ошибки в Excel - Разница дат и часов не должна быть отрицательной

Устранение ошибки : проверьте правильность написания формулы, число дней или часов было положительным числом.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скрытие и отображение всех нулевых значений на листе

Выберите Файл > Параметры > Дополнительно .

В группе

• Показывать нули в ячейках, которые содержат нулевые значения .

Скрытие нулевых значений в выделенных ячейках

Эти действия скрывают нулевые значения в выделенных ячейках с помощью числового формата. Скрытые значения отображаются только в строке формул и не выводятся на печать. Если значение в одной из этих ячеек изменится на ненулевое, оно будет отображаться в ячейке, а формат значения будет похож на общий числовой формат.

Отображение скрытых значений .

Выделите ячейки со скрытыми нулевыми значениями.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек .

Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК .

Скрытие нулевых значений, возвращенных формулой

На вкладке Главная Условное форматирование и выберите "Правила выделения ячеек" > "Равно".

В левом поле введите 0 .

В правом поле выберите Пользовательский формат .

В поле Формат ячейки откройте вкладку Шрифт .

В списке Цвет выберите белый цвет и нажмите кнопку ОК .

Отображение нулей в виде пробелов или тире

Для решения этой задачи воспользуйтесь функцией ЕСЛИ .

Если ячейка содержит нулевые значения, для возврата пустой ячейки используйте формулу, например такую:

ЕСЛИ(A2-A3=0;"";A2-A3)

Вот как читать формулу. Если результат вычисления (A2-A3) равен "0", ничего не отображается, в том числе и "0" (это указывается двойными кавычками ""). В противном случае отображается результат вычисления A2-A3. Если вам нужно не оставлять ячейки пустыми, но отображать не "0", а что-то другое, между двойными кавычками вставьте дефис "-" или другой символ.

Выберите отчет сводной таблицы.

На вкладке Анализ в группе Сводная таблица Параметры и выберите пункт Параметры .

Перейдите на вкладку Разметка и формат

• Изменение отображения ошибок В поле Формат установите флажок Для ошибок отображать

  Изменение отображения пустых ячеек Установите флажок Для пустых ячеек отображать

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

Выберите Файл > Параметры > Дополнительно .

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

• Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения .

  Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения .

Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек .

В поле Тип введите 0;-0;;@

Примечания:

Скрытые значения отображаются только в строке формул или в ячейке, если вы редактируете ее содержимое. Эти значения не печатаются.

Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек . Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий . Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число .

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование , наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно .

В левом поле введите 0 .

В правом поле выберите Пользовательский формат .

В диалоговом окне Формат ячеек откройте вкладку Шрифт .

В поле Цвет выберите белый цвет.

Для выполнения этой задачи используйте функцию ЕСЛИ .

Пример

Функция ЕСЛИ .

Скрытие нулевых значений в отчете сводной таблицы

На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .

Перейдите на вкладку Разметка и формат , а затем выполните следующие действия.

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

Установите флажок Для пустых ячеек отображать . Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

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

Отображение и скрытие всех нулевых значений на листе

Скрытие нулевых значений в выделенных ячейках с помощью числового формата

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

Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.

Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек .

В поле Тип введите 0;-0;;@

Примечания:

Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования

Выделите ячейку, содержащую нулевое (0) значение.

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите "Правила выделения ячеек" > "Равно".

В левом поле введите 0 .

В правом поле выберите Пользовательский формат .

В диалоговом окне Формат ячеек откройте вкладку Шрифт .

В поле Цвет выберите белый цвет.

Использование формулы для отображения нулей в виде пробелов или тире

Для выполнения этой задачи используйте функцию ЕСЛИ.

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

Выделите пример, приведенный в этой статье.

Важно: Не выделяйте заголовки строк или столбцов.

Выделение примера в справке

Нажмите клавиши CTRL+C.

В Excel создайте пустую книгу или лист.

Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе "Зависимости формул" нажмите кнопку Показать формулы .

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ .

Скрытие нулевых значений в отчете сводной таблицы

Щелкните отчет сводной таблицы.

На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .

Перейдите на вкладку Разметка и формат , а затем выполните следующие действия.

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

Изменение способа отображения пустых ячеек. Установите флажок Для пустых ячеек отображать . Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

Здравствуйте, друзья. Бывало ли у Вас, что, после ввода формулы, в ячейке отображается сама формула вместо результата вычисления? Это немного обескураживает, ведь Вы сделали все правильно, а получили непонятно что. Как заставить программу вычислить формулу в этом случае?

Для начала нужно разобраться с причинами такого поведения, после чего исправлять ситуацию. А причины могут быть такими.

Включено отображение формул

В Экселе есть режим проверки вычислений. Когда он включен, вы видите на листе формулы. Как и зачем применять отображение формул, я рассказывал в . Проверьте, возможно он активирован, тогда отключите. На ленте есть кнопка Формулы – Зависимости формул – Показать формулы . Если она включена – кликните, по ней, чтобы отключить.

Часто показ формул включают случайно, нажав комбинацию клавиш Ctrl+` .

Формула воспринимается программой, как текст

Если Excel считает, что в ячейке текстовая строка, он не будет вычислять значение, а просто выведет на экран содержимое. Причин тому может быть несколько:

• В ячейке установлен текстовый формат . Для программы это сигнал, что содержимое нужно вывести в том же виде, в котором оно хранится.
  
  
  Измените формат на Числовой, как сказано .
• Нет знака «=» вначале формулы . Как я рассказывал в , любые вычисления начинаются со знака «равно».
  
  
  Если его упустить – содержимое будет воспринято, как текст. Добавьте знак равенства и пересчитайте формулу.
• Пробел перед «=» . Если перед знаком равенства случайно указан пробел, это тоже будет воспринято, как текст.
  
  
  Эту ошибку сложно заметить, поэтому придется проверять вручную. Удалите лишний пробел и пересчитайте.
• Формула заключена в кавычки . Такая ошибка случается, когда работаем с текстовыми данными. Само собой, информация в кавычках трактуется, как текст.
  
  
  Удалите лишние кавычки, нажмите Enter , чтобы пересчитать.

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

Обещает быть очень интересной. Мы рассмотрим некоторые простые приемы быстрого написания формул. Если довести их до автоматизма, Ваша работа будет выполняться проще и быстрее. Подпишитесь на обновления, чтобы не пропустить!

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

1 Вариант

Вполне вероятно у Вас отключен автоматический пересчет листа, для этого просто нажмите клавишу F9

2 Вариант для всех версий Office

Проверьте что бы формат ячейки не было текстовым (из-за этого могут не работать вычисления и соответственно формулы)

PS что бы это исправить, выделите ячейки и выберите формат Общий (см. рисунок Формат Ячеек)

3 Вариант для Office 2010

Проверьте в настройках параметры вычисления формул.

Для этого нажмите на меню ФАЙЛ-> ПАРАМЕТРЫ-> ФОРМУЛЫ и в меню «Параметры вычисления» смотрите где стоит у Вас галочки (см картинку должно быть так)

4 Вариант для Office 2007/2010

Для Office 2007 путь такой Формула->Параметры вычисления-> и ставим галку на Автоматически, кроме таблиц данных. (см рисунок ниже)