Формулы в Excel
При ошибочных вычислениях, формулы отображают несколько типов ошибок вместо значений. Рассмотрим их на практических примерах в процессе работы формул, которые дали ошибочные результаты вычислений.
ОШИБКИ В ФОРМУЛЕ EXCEL ОТОБРАЖАЕМЫЕ В ЯЧЕЙКАХ
В данном уроке будут описаны значения ошибок формул, которые могут содержать ячейки. Зная значение каждого кода (например: #ЗНАЧ!, #ДЕЛ/0!, #ЧИСЛО!, #Н/Д!, #ИМЯ!, #ПУСТО!, #ССЫЛКА!) можно легко разобраться, как найти ошибку в формуле и устранить ее.
Как убрать #ДЕЛ/0 в Excel alt=»img» /> alt=»img» />
Как видно при делении на ячейку с пустым значением программа воспринимает как деление на 0. В результате выдает значение: #ДЕЛ/0! В этом можно убедиться и с помощью подсказки.
Читайте также: Как убрать ошибку деления на ноль формулой Excel.
В других арифметических вычислениях (умножение, суммирование, вычитание)пустая ячейка также является нулевым значением.
Результат ошибочного вычисления – #ЧИСЛО!
Неправильное число: #ЧИСЛО! – это ошибка невозможности выполнить вычисление в формуле.
Несколько практических примеров: 
Ошибка: #ЧИСЛО! возникает, когда числовое значение слишком велико или же слишком маленькое. Так же данная ошибка может возникнуть при попытке получить корень с отрицательного числа. Например, =КОРЕНЬ(-25).
В ячейке А1 – слишком большое число (10 1000). Excel не может работать с такими большими числами.
В ячейке А2 – та же проблема с большими числами. Казалось бы, 10000 небольшое число, но при возвращении его факториала получается слишком большое числовое значение, с которым 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, вероятно, вы неправильно вводите формулу вычитания. Это можно сделать двумя способами:
Вычтите одну ссылку на ячейку из другой
Введите два значения в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую. F2 содержит формулу =D2-E2.
Или используйте функцию СУММ с положительными и отрицательными числами
Введите положительное значение в одной ячейке и отрицательное — в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 — фактическую как негативное число. F6 содержит формулу =СУММ(D6;E6).
Если используется Windows, ошибка #ЗНАЧ! может возникнуть даже при вводе самой обычной формулы вычитания. Проблему можно решить следующим образом.
Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.
В Windows откройте панель управления «Региональные стандарты».
Windows 10: нажмите кнопку Пуск, введите Регион, а затем выберите панель управления Регион .
Windows 8. На начальном экране введите Регион, выберите Параметры, а затем — Регион.
Windows 7: выберите Пуск, введите Регион, а затем выберите Регион и язык.
На вкладке Форматы выберите Дополнительные параметры.
Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак «минус», замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.
Нажмите кнопку ОК.
Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.
Если там, где для вычитания должны быть знаки «минус», стоят запятые, замените их на знаки «минус».
Нажмите клавишу ВВОД.
Повторите эти действия для других ячеек, в которых возникает ошибка.
Вычтите одну ссылку на ячейку из другой
Введите две даты в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.
Или используйте функцию РАЗНДАТ
Введите две даты в двух отдельных ячейках. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.
Растяните столбец по ширине. Если значение выравнивается по правому краю — это дата. Но если оно выравнивается по левому краю, это значит, что в ячейке на самом деле не дата. Это текст. И Excel не распознает текст как дату. Ниже приведены некоторые решения, которые помогут решить эту проблему.
Проверка наличия начальных пробелов
Дважды щелкните дату, которая используется в формуле вычитания.
Разместите курсор в начале и посмотрите, можно ли выбрать один или несколько пробелов. Вот как выглядит выбранный пробел в начале ячейки: 
Если в ячейке обнаружена эта проблема, перейдите к следующему шагу. Если вы не видите один или несколько пробелов, перейдите к следующему разделу и проверьте параметры даты на компьютере.
Выберите столбец, содержащий дату, выбрав его заголовок столбца.
Выберите Data > Text to Columns (Текст > столбцы).
Дважды нажмите кнопку Далее .
На шаге 3 из 3 мастера в разделе Формат данных столбца выберите Дата.
Выберите формат даты и нажмите кнопку Готово.
Повторите эти действия для других столбцов, чтобы убедиться, что они не содержат пробелы перед датами.
Проверка параметров даты на компьютере
Excel полагается на систему дат вашего компьютера. Если дата ячейки не введена в той же системе дат, Excel не распознает ее как дату true.
Например, предположим, что компьютер отображает даты в формате мм.дд.гггг. Если ввести такую дату в ячейке, Excel распознает ее как дату, а вы сможете использовать ее в формуле вычитания. Однако если вы ввели такую дату, как дд/мм/гг, Excel не распознает ее как дату. Вместо этого дата будет рассматриваться как текст.
Существует два решения этой проблемы: Вы можете изменить систему дат, которая используется на компьютере, чтобы она соответствовала системе дат, которая нужна в Excel. Или в Excel можно создать новый столбец и использовать функцию ДАТА, чтобы создать настоящую дату на основе даты в текстовом формате. Вот как это сделать, если система дат вашего компьютера — дд.мм.гггг, а в ячейке A1 записан текст 12/31/2017.
Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))
Результат будет 31.12.2017.
Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или 
+ 1 на Mac).
Выберите другой языковой стандарт, в котором используется формат дд.мм.гг, например Немецкий (Германия). Когда вы закончите применять формат, результатом будет 31/12/2017 , и это настоящая дата, а не текстовая дата.
Примечание: Формула выше написана с использованием функций ДАТА, ПРАВСИМВ, ПСТР и ЛЕВСИМВ. Обратите внимание, что она написана с предположением, что текстовая дата содержит два символа для дней, два символа для месяцев и четыре символа года. Возможно, вам потребуется настроить формулу в соответствии с датой.
Проблемы с пробелами и текстом
Часто ошибка #ЗНАЧ! возникает, потому что формула ссылается на другие ячейки, содержащие пробелы или (что еще сложнее) скрытые пробелы. Из-за этих пробелов ячейка может выглядеть пустой, хотя на самом деле таковой не является.
1. Выберите ячейки, на которые указывают ссылки
Найдите ячейки, на которые ссылается формула, и выделите их. Во многих случаях рекомендуется удалить пробелы для всего столбца, так как можно заменить несколько пробелов одновременно. В этом примере при выборе E выделяется весь столбец.
2. Найдите и замените
На вкладке Главная выберите Найти & Выберите > Заменить.
3. Удалите пробелы
В поле Найти введите один пробел. Затем в поле Заменить удалите все, что там может быть.
4. Замените одно или все вхождения
Если вы уверены, что все пробелы в столбце должны быть удалены, выберите Заменить все. Если вы хотите выполнить пошаговое выполнение и заменить пробелы ни на что не по отдельности, сначала можно нажать кнопку Найти далее , а затем нажать кнопку Заменить , если вы уверены, что пространство не требуется. После этого ошибка #ЗНАЧ! ошибка может быть устранена. Если нет — перейдите к следующему шагу.
5. Включите фильтр
Иногда скрытые символы, отличные от пробелов, могут сделать ячейку пустой, если она на самом деле не пуста. Например, это может происходить из-за одинарных кавычек в ячейке. Чтобы убрать эти символы из столбца, включите фильтр, последовательно выбрав Главная > Сортировка и фильтр > Фильтр.
6. Установите фильтр
Щелкните стрелку фильтра 
и снимите флажок Выделить все. Затем установите флажок Пустые.
7. Установите все флажки без названия
Установите все флажки, напротив которых ничего не указано, как на этом рисунке.
8. Выделите пустые ячейки и удалите их
Если Excel вернет пустые ячейки, выделите их. Затем нажмите клавишу DELETE. Это очищает все скрытые символы в ячейках.
9. Очистите фильтр
Щелкните стрелку фильтра , а затем выберите Очистить фильтр из. , чтобы все ячейки были видны.
10. Результат
Если причиной появления ошибки #ЗНАЧ! были пробелы, вместо ошибки отобразится результат формулы, как показано в нашем примере. Если нет — повторите эти действия для других ячеек, на которые ссылается формула. Или попробуйте другие решения на этой странице.
Примечание: В этом примере обратите внимание, что ячейка E4 содержит зеленый треугольник, а число выравнивается по левому краю. Это значит, что число имеет текстовый формат. Позже это может привести к дополнительным проблемам. Если вы заметили эту проблему, рекомендуем преобразовать числа из текстового формата в числовой.
Ошибку #ЗНАЧ! могут вызвать текст и специальные знаки в ячейке. Но иногда сложно понять, в каких именно ячейках они присутствуют. Решение: Используйте функцию ISTEXT для проверки ячеек. Обратите внимание, что ISTEXT не устраняет ошибку, а просто находит ячейки, которые могут вызвать ошибку.
Пример с ошибкой #ЗНАЧ!
Вот пример формулы с ошибкой #ЗНАЧ! . Ошибка, скорее всего, возникает из-за ячейки E2. Специальный символ отображается в виде небольшого прямоугольника после «00». Или, как показано на следующем рисунке, можно использовать функцию ЕТЕКСТ в отдельном столбце для поиска текста.
Этот же пример с функцией ЕТЕКСТ
Здесь в столбец F добавлена функция ЕТЕКСТ. Все ячейки в порядке, кроме одной со значением ИСТИНА. Это значит, что ячейка E2 содержит текст. Чтобы решить эту проблему, можно просто удалить содержимое ячейки и еще раз ввести число 1865,00. Вы также можете использовать функцию ПЕЧСИМВ, чтобы убрать символы, или функцию ЗАМЕНИТЬ, чтобы заменить специальные знаки на другие значения.
Использовав функцию ПЕЧСИМВ или ЗАМЕНИТЬ, вы можете скопировать результат в буфер обмена, а затем выбрать Главная > Вставить > Специальная вставка > Значения. Кроме того, может потребоваться преобразовать числа из текстового формата в числовой.
Формулы с математическими операциями, такими как + и *, могут не вычислить ячейки, содержащие текст или пробелы. В таком случае попробуйте использовать вместо них функцию. Функции часто игнорируют текстовые значения и вычисляют все в виде чисел, устраняя #VALUE! . Например, вместо =A2+B2+C2 введите =СУММ(A2:C2). Или вместо =A2*B2 введите =ПРОИЗВЕД(A2,B2).
Другие решения
Выберите ошибку
Сначала выделите ячейку с ошибкой #ЗНАЧ! .
Щелкните «Формулы» > «Вычислить формулу»
Выберите Формулы > Оценка формулы > Оценка. Excel по отдельности проходит по частям формулы. В данном случае формула =E2+E3+E4+E5 выдает ошибку из-за скрытого пробела в ячейке E2. Пробела не видно, если смотреть на ячейку E2. Но его можно увидеть здесь. Он отображается как » «.
Иногда вам может быть нужно вместо ошибки #ЗНАЧ! выводить что-то свое, например собственный текст, ноль или пустую ячейку. В этом случае можно добавить в формулу функцию ЕСЛИОШИБКА. IFERROR проверяет наличие ошибки и, если да, заменяет ее другим значением по вашему выбору. Если ошибки нет, исходная формула вычисляется. IFERROR работает только в Excel 2007 и более поздних версиях. В предыдущих версиях можно использовать функцию ЕСЛИ(ЕОШИБКА()).
Предупреждение: IFERROR скрывает все ошибки, а не только #VALUE! . Ошибки не рекомендуется скрывать, так как они часто указывают на то, что какое-то значение нужно исправить, а не просто скрыть. Мы не рекомендуем использовать эту функцию, если вы не уверены, что формула работает так, как вам нужно.
Ячейка с ошибкой #ЗНАЧ!
Вот пример формулы, в которой ошибка #ЗНАЧ! вызвана скрытым пробелом в ячейке E2.
Ошибка, скрытая функцией ЕСЛИОШИБКА
А вот эта же формула с добавленной функцией ЕСЛИОШИБКА. Ее можно прочитать как «Вычислить формулу, но если возникнет какая-либо ошибка, заменить ее двумя дефисами.» Помните, что также можно использовать «», чтобы ничего не отображать вместо двух дефисов. Или вы можете подставить свой текст, например: «Ошибка суммирования».
К сожалению, как вы видите, функция ЕСЛИОШИБКА не устраняет ошибку, а только скрывает ее. Так что используйте ее, если точно уверены, что ошибку лучше скрыть, чем исправить.
В какой-то момент подключение к данным может стать недоступным. Чтобы исправить ошибку, восстановите подключение или, если это возможно, импортируйте данные. Если у вас нет доступа к подключению, попросите автора книги создать для вас новый файл. В идеале новый файл будет содержать только значения и не будет подключений. Это можно сделать, скопировав все ячейки и вставив только в качестве значений. Чтобы вставить только значения, можно выбрать Главная > Вставить > Вставить специальные значения >. При этом будут удалены все формулы и подключения, а ошибки #ЗНАЧ! исчезнут.
Если вам не помогли эти рекомендации, поищите похожие вопросы на форуме сообщества, посвященном Excel, или опубликуйте там свой собственный.
Почему в Excel вместо текста появляются решетки (###)? Как исправить?
При копировании текста в ячейку появляется куча решеток, а не сам текст. Как исправить?
Обычно такие решетки появляются от того, что текст не вмешается в ячейку, поэтому визуально программа ставит такие символы, которые говорят о том, что ячейку нужно растянуть под размер текста и он начнет отображаться. Бывает такая ситуация.
Кто работает в программе Excel, тот, я думаю, обязательно сталкивался с такой неприятностью, как символ решетка (###) в ячейке вместо текста или цифр. Конечно, эти решетки означают, что текстовая часть, предназначенная для этой ячейки, в нее не входит.
Я помню, когда еще только начинала работать в этой программе, при таком явлении чисто интуитивно растянула эту ячейку (а с нею и весь столбец, конечно) — и нужная мне информация появилась.
Но растянуть ячейку удается не всегда, например, если работаешь в какой-то специальной программе.
Тогда при копировании текста можно уменьшить его размер (задать другой масштаб) и попытаться вставить новый текст. Часто это помогает — текст хорошо встает, а решетка не появляется.
Это самые простые варианты.
Более сложный — зайти в меню и поменять настройки, как описывает уважаемая Автор вопроса.
Почему в экселе выходит знач
Microsoft Excel — одна из самых популярных и мощных программ для обработки данных. Иногда возникают ситуации, когда при вводе данных в ячейки таблицы, вместо числа или текста, в ячейке появляется знак #. Это один из наиболее часто встречающихся неприятных моментов при работе с программой, который может вызвать затруднения в работе с таблицами.
Знак # в Excel появляется не случайно, и чаще всего связан с ошибками, которые допускают пользователи при вводе данных. Но существует несколько причин, по которым может появиться данный символ, например, ошибки формул, превышение максимального значения ячейки, несоответствие формата ячейки и другие.
Чтобы решить проблему со знаком # в Excel, необходимо знать источник проблемы и принять соответствующие меры. В данной статье будет дано несколько вариантов, как исправить эту проблему и продолжить работу с таблицей в Excel без проблем.
Почему Excel выводит знак «#»?
Когда в Excel встречается ошибка при чтении, вместо значения ячейки отображается символ «#». У знака «#» могут быть различные причины: столбец не может содержать больше данных, формула вызывает ошибку, ссылка на другой лист неверна и многое другое. Важно понимать, что знак «#» не является ошибкой, а лишь напоминанием, что что-то пошло не так.
Для исправления проблемы со знаком «#» нужно открыть ячейку, содержащую этот знак, и устранить проблему, вызвавшую его появление. Например, проверить исправность формулы, увеличить ширину или высоту нужного столбца или строки, проверить правильность ссылок на другие листы.
Если ошибка не является критической, отображение знака «#» можно отключить в опциях Excel. Это сделать достаточно просто: сначала необходимо зайти в «Файл», затем в «Параметры», далее выбрать «Расширенные» и найти там опцию «Отображать знак решётки (#) в ячейках, где есть ошибка». Снимите галочку напротив этой настройки и сохраните изменения.
В целом, знак «#» в Excel может возникать по разным причинам, но справиться с этой проблемой несложно, если проявить терпение и установить причину возникновения этого знака.
Причины появления символа «#» в Excel
Знак «#» в Excel может появиться по разным причинам.
- Ячейка слишком маленькая для отображения значения. Если значение в ячейке слишком длинное или содержит много символов, а размер ячейки слишком маленький, то Excel не сможет отобразить его полностью и заменит его знаком «#».
- Изменение формата ячейки. Если формат ячейки не соответствует типу значения в ней, то знак «#» может появиться. Например, если в ячейке находится дата, а формат ячейки не установлен на «дата», то Excel заменит значение знаком «#».
- Ошибка в ссылке на ячейку. Если в формуле используется ссылка на несуществующую ячейку, то вместо значения Excel выведет знак «#».
- Неправильный формат числа. Если значение в ячейке содержит неправильный формат числа, например, если вместо десятичной точки используется запятая, то Excel заменит значение знаком «#».
Во всех случаях, знак «#» указывает на ошибку или несоответствие, которое нужно исправить, чтобы получить корректные данные в ячейке.
Как исправить появление символа «#» в Excel
Если в Excel при открытии файла появляется символ «#» вместо данных, это значит, что формат ячеек не соответствует типу хранимых данных. Например, если в ячейке числовое значение, а формат ячейки установлен в текстовый, то вместо числа будет появляться символ «#».
Чтобы исправить эту проблему, необходимо установить правильный формат ячеек для данных, хранимых в данной ячейке. Для этого нужно выделить нужные ячейки, затем нажать правой кнопкой мыши и выбрать пункт «Формат ячеек».
В открывшемся окне «Формат ячеек» нужно выбрать нужный формат для хранимых данных. Например, если это число, то нужно выбрать формат «Число». Если это дата, то нужно выбрать формат «Дата». Если это текст, то нужно выбрать формат «Текст».
Если после изменения формата ячеек символ «#» всё ещё не исчез, то возможно данные в ячейке некорректны. В этом случае нужно проверить данные на наличие ошибок и исправить их. Также можно попробовать скопировать данные в другую ячейку с правильным форматом и проверить, исправится ли проблема.
В целом, чтобы избежать появления символа «#» в Excel, нужно следить за тем, чтобы формат ячеек соответствовал типу данных, хранимых в ячейке, и проверять данные на корректность перед вводом их в ячейку.