Ошибки в формулах MS Excel

При разработке сложных формул (ну и просто невнимательности) в MS Excel ошибку совершить достаточно просто. Обычно MS Excel в таковых вариантах выводит сообщения о ошибках либо даже дает “верный” на его взор вариант написания формулы, но даже при наличии справочной системы, сначала достаточно трудно осознать, чего же же желает от нас “глуповатая программка”. В данной статье мы разглядим все типы ошибок возникающих в формулах MS Excel, и научимся их исправлять и осознавать.

Ошибка #ЗНАЧ! (ошибка в значении)

Если б был “топ ошибок MS Excel”, 1-ое пространство в нем принадлежало бы ошибке #ЗНАЧ!. Как можно додуматься из наименования, возникает она в том случае, когда в формулу либо функцию подставлено неверное значение. Если вы пытаетесь провести арифметические операции с текстом, либо подставляете в функцию спектр ячеек, когда требуется указать всего одну ячейку, результатом вычислений будет ошибка #ЗНАЧ!.

Как и говорилось – попытка сложить число и текст ставит MS Excel в тупик

Ошибка #ССЫЛКА! (некорректная ссылка на ячейку)

Одна из самых нередких ошибок при вычислениях. Обозначает самую простейшую вещь – в формуле употребляется ссылка на ячейку которую вы либо не делали либо ненароком удалили. Почаще всего #ССЫЛКА! возникает когда вы удаляете “ненадобный” столбец, некие ячейки которого, как оказалось, участвовали в вычислениях.

Ошибка #ДЕЛ/0! (деление на ноль)

Со школьной скамьи мы помним обычное правило: на ноль разделять недозволено! Ошибка #ДЕЛ/0! – это предупреждение от MS Excel о том, что это базисное правило нарушено и вы все-же пытаетесь поделить некоторое число на ноль. При всем этом сам “ноль” не обязателен – неважно какая попытка поделить имеющееся число на “пустую” ячейку также вызовет эту ошибку.

Разделять на ноль недозволено – пустая ячейка воспринимается MS Excel как этот же ноль

Ошибка #Н/Д (значение труднодоступно)

Ошибка #Н/Д возникает в том случае, если в функции пропущен некий аргумент, либо одно из применяемых в формуле значений становится труднодоступно. Узрел #Н/Д – сперва отыскивай чего же в твоих вычислениях не хватает.

Применяю функцию ВПР, символ разделения поставил, а вот указать к какой ячейке он относится – запамятовал

Ошибка #ИМЯ? (недопустимое имя)

Ошибка #ИМЯ – признак того, что вы и Excel друг дружку не сообразили. Точнее MS Excel не сообразил что вы имели ввиду – вы очевидно указываете на некий элемент, а программка его не может отыскать. В которых вариантах это обычно происходит?

• В функции указана ячейка либо спектр ячеек с несуществующим (почаще всего с некорректно введенным) именованием.

Попытка суммировать несуществующий спектр с заглавием Столбец

• Текст снутри функции заключается в кавычки. Если этого не происходит (другими словами заместо =”Вася” мы вводим =Вася), MS Excel приходит в полное недоумение.

Ещё одна простая ошибка – текст в функциях и формулах указывается в кавычках

• В заглавии функции случаем допущена опечатка.

Ошибка #ПУСТО! (пустое огромное количество)

Ошибка #ПУСТО почаще всего возникает когда в формуле пропущен один из операторов, но может возникать и в том случае, когда нам требуется отыскать пересечение 2-ух диапазонов ячеек, а этого пересечения просто не существует.

Все бы отлично, но запамятовал про 2-ой символ “+”

Ошибка #ЧИСЛО! (неверное число)

Ошибку #ЧИСЛО! ms Excel выдает в тех вариантах, когда итог математических вычислений в формуле порождает некий совсем мистический итог. Итог в виде максимально огромного либо малого числа, попытка вычислить корень из отрицательного числа – все это приведет к появлению ошибки #ЧИСЛО!

Вычислить корень из отрицательного числа? Вас бы не сообразил не только лишь Excel

Знаки “сетки” в ячейке Excel (#######)

В прошедшем очень всераспространенная “ошибка” MS Excel сплетенная с неожиданным наполнением ячейки знаками сетки (#) могла быть вызвана тем, что в ячейку введено число которое не помещается в ней полностью (но лишь если ячейка имеет формат “числовой” либо “дата”).

С возникновением MS Office 2013 ошибка фактически сошла на нет, потому что “поумневший” Excel стал в большинстве случаев автоматом наращивать ширину ячейки под число. Если же вы видите “сетки”, проще всего избавиться от их увеличив ширину ячейки вручную.

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

Исправление ошибок в MS Excel

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

Нажмите на значок, чтоб получить помощь в исправлении ошибки

В первой строке показавшегося контекстного меню вы увидите полное заглавие ошибки, во 2-ой можете вызвать подробную справку по ней, но самое увлекательное прячет в для себя 3-ий пункт: “Показать этапы вычисления…“.

“Показать этапы вычисления…” – программку не обманешь, буквально выводит фрагмент формулы где допущена ошибка

Нажмите на него и в показавшемся окне увидите тот фрагмент формулы где допущена ошибка – это в особенности комфортно, когда “распутывать” приходится целый клубок из громадины вложенных друг в друга действий.

Обзор ошибок, возникающих в формулах Excel

Ошибки в Excel появляются достаточно нередко. Вы, наверное, замечали странноватые значения в ячейках, заместо ожидаемого результата, которые начинались со знака #. Это гласит о том, что формула возвращает ошибку. Чтоб избавиться от ошибки, Вы должны поправить ее причину, а они могут быть самыми различными.

Несоответствие открывающих и закрывающих скобок

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

К примеру, на рисунке выше мы преднамеренно пропустили закрывающую скобку при вводе формулы. Если надавить кнопку Enter, Excel выдаст последующее предупреждение:

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

Ячейка заполнена знаками сетки

Бывают случаи, когда ячейка в Excel стопроцентно заполнена знаками сетки. Это значит один из 2-ух вариантов:

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



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

В этом случае повышение ширины столбца уже не поможет.

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

Ошибка #ДЕЛ/0! возникает, когда в Excel происходит деление на ноль. Это быть может, как очевидное деление на ноль, так и деление на ячейку, которая содержит ноль либо пуста.

Ошибка #Н/Д

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

1. Функция поиска не находит соответствия. Например, функция ВПР при четком поиске возвратит ошибку #Н/Д, если соответствий не найдено.
2. Формула прямо либо косвенно обращается к ячейке, в которой отображается значение #Н/Д.
3. При работе с массивами в Excel, когда аргументы массива имеют наименьший размер, чем результирующий массив. В этом случае в незадействованных ячейках итогового массива отобразятся значения #Н/Д.К примеру, на рисунке ниже видно, что результирующий массив C4:C11 больше, чем аргументы массива A4:A8 и B4:B8.



Нажав комбинацию кнопок Ctrl+Shift+Enter, получим последующий итог:

Ошибка #ИМЯ?

Ошибка #ИМЯ? возникает, когда в формуле находится имя, которое Excel не соображает.

1. К примеру, употребляется текст не заключенный в двойные кавычки:
2. Функция ссылается на имя спектра, которое не существует либо написано с опечаткой:

В данном примере имя спектр не определено.

1. Адресок указан без разделяющего двоеточия:
2. В имени функции допущена опечатка:

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

Ошибка #ПУСТО! возникает, когда задано пересечение 2-ух диапазонов, не имеющих общих точек.

1. К примеру, =А1:А10 C5:E5 – это формула, использующая оператор пересечения, которая обязана возвратить значение ячейки, находящейся на пересечении 2-ух диапазонов. Так как спектры не имеют точек пересечения, формула возвратит #ПУСТО!.
2. Также данная ошибка возникнет, если случаем опустить один из операторов в формуле. Например, формулу =А1*А2*А3 записать как =А1*А2 A3.

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

Ошибка #ЧИСЛО! возникает, когда неувязка в формуле связана со значением.

1. К примеру, задано отрицательное значение там, где обязано быть положительное. Броский пример – квадратный корень из отрицательного числа.
2. К тому же, ошибка #ЧИСЛО! возникает, когда ворачивается очень огромное либо очень маленькое значение. К примеру, формула =1000^1000 возвратит как раз эту ошибку.

Не запамятовывайте, что Excel поддерживает числовые величины от -1Е-307 до 1Е+307.

1. Еще одним случаем появления ошибки #ЧИСЛО! является употребление функции, которая при вычислении употребляет способ итераций и не может вычислить итог. Броским примером таковых функций в Excel являются СТАВКА и ВСД.

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

Ошибка #ССЫЛКА! возникает в Excel, когда формула ссылается на ячейку, которая не существует либо удалена.

К примеру, на рисунке ниже представлена формула, которая суммирует значения 2-ух ячеек.

Если удалить столбец B, формула возвратит ошибку #ССЫЛКА!.

Если мы скопируем данную формулу в всякую ячейку 1-й строчки (к примеру, ячейку D1), формула возвратит ошибку #ССЫЛКА!, т.к. в ней будет находиться ссылка на несуществующую ячейку.

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

Ошибка #ЗНАЧ! одна из самых всераспространенных ошибок, встречающихся в Excel. Она возникает, когда значение 1-го из аргументов формулы либо функции содержит недопустимые значения. Самые всераспространенные случаи появления ошибки #ЗНАЧ!:

1. Формула пробует применить обычные математические операторы к тексту.
2. В качестве аргументов функции употребляются данные несоответствующего типа. Например, номер столбца в функции ВПР задан числом меньше 1.
3. Аргумент функции обязан иметь единственное значение, а заместо этого ему присваивают целый спектр. На рисунке ниже в качестве искомого значения функции ВПР употребляется спектр A6:A8.

Вот и все! Мы разобрали обычные ситуации появления ошибок в Excel. Зная причину ошибки, еще проще поправить ее. Фурроров Для вас в исследовании Excel!

Ошибка в формуле в excel

Вы сможете нередко повстречать некие ошибки формул в книжках, к примеру # DIV / 0, #Value !, #REF, # N / A, #NUM !, #NAME ?, #NULL. А тут мы покажем для вас несколько нужных способов поиска и подмены этих # ошибок формул на количество нулевых либо пустых ячеек в Microsoft Excel. Пример быть может легким для осознания. Представим, мы будем находить и подменять # ошибок формулы в последующей таблице:

Найдите и поменяйте # ошибки формул определенными числами

Обычно мы можем употреблять Microsoft Excel ERROR.TYPE функция для преобразования всех # ошибок формул в определенные числа.
В нашем примере выше просто введите = ТИП ОШИБКИ (A3) в пустой ячейке, и вы получите число 2. И различные ошибки # формулы будут преобразованы в различные числа:

# Ошибки

Формулы

Древная к

= ERROR.TYPE (# DIV / 0!)

= ТИП ОШИБКИ (# ЗНАЧ!)

= ТИП ОШИБКИ (# ЧИСЛО!)

Естественно, вы сможете перетащить маркер заливки чтоб конвертировать остальные ошибки формулы в числа по спектрам. Но этих цифр очень много, чтоб их можно было уяснить и применить.

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

Kutools for Excel‘s Мастер критерий ошибки Утилита поможет для вас просто поменять все значения ошибок либо определенный тип значения ошибки, к примеру # N / A, пустым, нулем либо определенным текстом, как для вас необходимо в Excel. Загрузите полнофункциональную 30-дневную бесплатную версию Kutools for Excel прямо на данный момент!



Найдите и поменяйте # ошибки формулы на 0 либо пустые ячейки при помощи команды Перейти

Таковым образом можно просто конвертировать все ошибки формул # в выделенном фрагменте с 0, пустым либо хоть какими иными значениями при помощи Microsoft Excel. Перейти к команда.

Шаг 1: Изберите спектр, с которым вы будете работать.

Шаг 2: Нажмите F5 , чтоб открыть Перейти к диалоговое окно.

Шаг 3: Нажмите Особенный клавишу, и он открывает Перейти к специальному диалоговое окно.

Шаг 4: В Перейти к специальному диалоговое окно, лишь отметьте Формула вариант и ошибки вариант, см. скриншот:



Шаг 5: И потом щелкните OK, были выбраны все # ошибки формулы, см. скриншот:



Шаг 6: Сейчас просто введите 0 либо хоть какое другое значение, нужное для подмены ошибок, и нажмите Ctrl + Enter ключи. Тогда вы получите, что все избранные ячейки ошибок заполнены 0.



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

Найдите и поменяйте # ошибок формулы на 0 либо пустые ячейки при помощи Kutools for Excel

Если у вас есть Kutools for Excel установлен, его Мастер критерий ошибки Инструмент упростит вашу работу, заменив все виды ошибок формул на 0, пустые ячейки либо любые пользовательские сообщения.

Перед применением Kutools for Excel, Пожалуйста, поначалу скачайте и установите.

1: Изберите спектр со значениями ошибок, которые вы желаете поменять на ноль, пробел либо текст, как для вас необходимо, потом cоблизывание Kutools > Еще > Мастер критерий ошибки. Смотрите снимок экрана:



2. В Мастер критерий ошибки диалоговое окно, сделайте последующее:



(1) В типы ошибок изберите подходящий тип ошибки, к примеру Хоть какое значение ошибки, Лишь значение ошибки # Н / Д or Хоть какое значение ошибки, не считая # N / A. Тут я выбираю Хоть какое значение ошибки опцию.

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

Чтоб поменять все значения ошибок на ноль либо определенный текст, изберите Сообщение (текст) вариант, введите номер 0 либо подходящий текст в пустое поле.

(3) Щелкните значок OK клавишу.

Тогда и вы сможете узреть, что все значения ошибок в избранном спектре заменяются пробелами, нулями либо определенным текстом, как вы сходу указали выше. Смотрите снимки экрана:

Поменяйте все значения ошибок пустыми



Поменяйте все значения ошибок на ноль



Поменяйте все значения ошибок определенным текстом



Если вы желаете получить 30-дневную бесплатную пробную версию данной утилиты, пожалуйста, нажмите, чтоб загрузить это, а потом перейдите к применению операции в согласовании с обозначенными выше шагами.

Ошибка в формуле в excel

Формулы Excel в Excel могут посодействовать для вас выполнить некие достаточно сложные задачки. Но вы наверное столкнетесь с ошибками формул в Excel. В данной статье мы покажем для вас 8 ошибок формул в Excel.

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

1. # ЗНАЧЕНИЕ!



Эта ошибка возникает в основном из-за того, что формула содержит много различных типов значений. На изображении ниже показан таковой пример. Ячейка A2 содержит текстовое значение, а ячейка B2 — число. Таковым образом, вы увидите «# ЗНАЧЕНИЕ!» ошибка в ячейке.



Не считая того, не считая данной предпосылки, эта ошибка будет возникать и в остальных критериях. В данной ячейке находится формула функции СМЕЩЕНИЕ. Но эта формула будет содержать две ячейки. В итоге вы также увидите эту ошибку на листе.

2. # ИМЯ?



Эта ошибка значит, что вы сможете употреблять неверное имя функции в формуле. Представим, сейчас для вас необходимо вычислить среднее значение спектра. И вы обязательно воспользуетесь функцией СРЕДНИЙ. Но вы вводите «среднее» в ячейку. И это вызовет эту ошибку.

Excel будет обновляться, и существует много различных версий этого приложения. В наиболее новейшей версии будут новейшие функции. Если вы используете новейшие функции в старенькых версиях Excel, вы также увидите эту ошибку. К примеру, если в Excel 2003 вы используете СРЕДНЕЕСЛИМН, вы увидите «# ИМЯ?» ошибка.

3. # DIV / 0！



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

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

4. # N / A



По наименованию вы узнаете, что это значит труднодоступно. Если ячейка недосягаема в формуле, вы увидите этот итог. На этом изображении ниже вы сможете узреть функцию ВПР в спектре. Но тут мы используем «ИСТИНА» в качестве крайнего аргумента.

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



Эта ошибка возникает, если ширина ячейки недостаточна для отображения всех знаков. Число в ячейке A1 — 1231068, тогда как ширина данной ячейки весьма мала. Потому в ячейке Excel будет отображаться «###».



Не считая того, если в ячейке указаны отрицательные даты, вы также увидите эту ошибку. Тут формат ячейки — «Дата». А в камере минусовый вздох. Таковым образом, он покажет эту ошибку.

6. #NULL!



Эта ошибка значит, что в формуле указан неверный региональный оператор. Тут мы также покажем для вас пример. Тут мы используем функцию СУММ, чтоб суммировать два спектра на листе. Но мы забываем запятую меж различными аргументами, вы увидите эту ошибку.

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

7. # ЧИСЛО!



Эта ошибка значит, что там содержатся недопустимые значения. На этом изображении ниже мы употребляли пример. Это формула «= 2 ^ 12345». Итог — бесконечность. Таковым образом, Excel возвратит «# ЧИСЛО!» в камере.

Видите ли, данной ошибки просто избежать.

8. # ССЫЛКА!



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



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

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

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

Ошибки Excel могут быть достаточно сложными

Кроме ошибок формулы, сам Excel также будет выдавать ошибки. Наиболее того, эти ошибки могут быть достаточно сложными. Потому для вас нужно употреблять спец инструмент восстановления Excel, чтоб вернуть покоробленный файл Excel XLSX. Этот инструмент может обрабатывать most заморочек в Excel. Не считая того, он может просто возвратить ваши драгоценные данные и информацию из покоробленных файлов.