Формулы виды ошибок при вычислении

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

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

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

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

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

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

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

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

      …или изменить числовой формат ячейки.

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. В имени функции допущена опечатка:

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

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

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

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

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

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

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

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

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

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

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

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

   

2. Еще пример. Формула в ячейке B2 ссылается на ячейку B1, т.е. на ячейку, расположенную выше на 1 строку.

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

   

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

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

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

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

Формула представляет собой синтаксическую
конструкцию со знаком “=” в первой
позиции, набирается с клавиатуры или
кнопкой Изменить
формулу. По этому признакуExcelзапускает Палитру формул, которая
проверяет синтаксис, автоматически
исправляет распространенные ошибки и
обеспечивает справочными сведениями.

Формула может содержать следующие
элементы:

Выражение – это операнды, соединенные
знаками операций. Используемые в формулах
Excelоперации приведены в
таблице 1. Порядок вычисления выражений
слева направо с учетом приоритетов
операций и может быть изменен скобками
( ).

Таблица 1

Операции в формулах Excel

Операнд – это константа, ссылка на
ячейку или диапазон ячеек, заголовок,
имя или функция.

Константа – это число или текст, введенные
с клавиатуры, в отличие от формулы или
возвращаемого ею значения.

Ссылка на ячейку – это адрес ячейки,
однозначно определяющий ее месторасположение,
в Excelпо умолчанию
используется стиль адресации А1 по букве
столбца и номеру строки, на пересечении
которых расположена ячейка. При помощи
ссылок можно использовать в одной
формуле данные из разных частей листа,
а также использовать в нескольких
формулах значение одной ячейки.

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

Абсолютная ссылка – фиксированный
адрес ячейки на листе, например, $А$3.
Абсолютные адреса предваряются знаком
$.

Относительная ссылка – относительно
ячейки с формулой, содержащей эту ссылку,
например, A3.

Продолжение прил. 2

Смешанная ссылка. Например, А$1 –
абсолютная ссылка на первую строку, $A1
– абсолютная ссылка на первый столбец.

Ссылки на ячейки других листов той же
книги, Лист2!А1.

Внешние ссылки на другие книги,
[Книга2]Лист1!A1 на открытую
книгу, иначе с полным путем к книге на
локальном диске, ‘С:Мои
документы[Книга2]Лист1’!$A$1.

Удаленные ссылки на данные других
приложений; сетевые адреса и URL (Uniform
Resource Locator, унифицированный указатель
ресурса,
адреса интернета).

Трехмерные ссылки на несколько листов,
Лист2:Лист13!A1.

Циклические ссылки – это последовательность
ссылок, при которой формула ссылается
на себя напрямую или через другие ссылки.
Вычисление следующих значений по
предыдущим в таких замкнутых
последовательностях называется
итерацией. Excel прекращает итерационный
процесс по заранее установленным
ограничениям на количество итераций,
по умолчанию 100, или на невязку (разность
значений с двух соседних итераций), по
умолчанию 0,001.

Ячейки, на которые есть ссылки в указанной
ячейке, называются влияющими. Ячейки,
в которых есть ссылки на указанную
ячейку, называютсязависимыми.

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

При копировании
формулы абсолютные ссылки не обновляются.
Относительные ссылки при копировании
формулы в другую ячейку
обновляются
автоматически так,
что взаимное
расположение влияющих ячеек и формулы
сохраняется. Например, формула в ячейке
A3 =$A$1+A2 после копирования в ячейку В3
=$A$1+B2.
Копирование формулы в примыкающий
диапазон можно быстро осуществить
протаскиванием маркера заполнения.

Диапазон – это несколько ячеек листа.
Ссылка на непрерывный прямоугольный
диапазон задается как адрес левой
верхней ячейки :адрес правой
нижней ячейки. В ссылке на несмежный
диапазон поддиапазоны перечисляются
через;и выделяются на листе с
нажатым Ctrl.

Формула может содержать одну или
несколько функций, связанных между
собой арифметическими операторами или
вложенных друг в друга. Функции – это
встроенные в программу Excel формулы,
которые выполняют вычисления по своим
аргументам. Обращение к функции идет
по имени функции и в скобках списку
аргументов через запятую. Вставку
функции можно выполнить, используя меню
Вставка –Функция… либо одноименную кнопку
на панели инструментов Стандартная. В
результате будет запущен двухшаговый
Мастер функций, содержащий всю коллекцию
функцийExcelв более чем
10 различных категорий.

Продолжение прил. 2

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

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

Ошибка #####

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

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

Меры
по устранению ошибки– увеличьте
ширину столбца путем перемещения
границы, расположенной между заголовками
столбцов. Кроме того, можно изменить
формат числа ячейки (меню Формат –Ячейки…, вкладка Число).

1. При работе с датами получается
   отрицательное значение.

Меры
по устранению ошибки– введите
правильную формулу.

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

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

1. Вместо числового или логического
   значения введен текст.

Меры
по устранению ошибки– проверьте в
формуле правильность задания типов
аргументов.

1. После ввода или редактирования
   формулы массива нажимается клавиша
   Enter.

Меры
по устранению ошибки– для редактирования
формулы укажите ячейку или диапазон
ячеек, содержащих формулу массива,
нажмите клавишуF2, а затем
– комбинацию клавишCtrl+Shift+Enter.

1. Использована неправильная размерность
   матрицы данных в одной из матричных
   функций.

Меры
по устранению ошибки– укажите
правильную размерность при работе с
матрицами данных.

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

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

1. В качестве делителя используется ссылка
   на ячейку, содержащую нулевое или пустое
   значение (если аргумент является пустой
   ячейкой, то ее содержимое интерпретируется
   как нуль). Такая ситуация чаще всего
   возникает случайно.

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

Продолжение прил. 2

Ошибка #ИМЯ?

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

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

Меры
по устранению ошибки– определите
имя. Для этого выполните команду Имя
меню Вставка

1. Имеется ошибка в написании имени
   функции.

Меры
по устранению ошибки– исправьте
написание имени функции вручную или
вставьте функцию с помощью мастера
функций.

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

Меры
по устранению ошибки– заключите
текст формулы в двойные кавычки. Например,
ессли в ячейке А1 содержится значение
200, я в ячейке В1 – формула = «Итого:»&А1,
то в ячейке В1 будет выведен результат
Итого:200.

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

Меры
по устранению ошибки– исправьте
формулу так, чтобы во всех ссылках на
диапазон ячеек использовался знак
двоеточия, например =СУММА(А1:С10).

Ошибка #Н/Д

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

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

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

1. Не заданы один или несколько аргументов
   стандартной или пользовательской
   функции листа.

Меры
по устранению ошибки– задайте все
необходимые аргументы функции.

1. Используется пользовательская функция,
   обращение к которой приводит к ошибке.

Меры
по устранению ошибки– проверьте,
что книга, использующая функцию листа,
открыта, и убедитесь в правильности
работы функции (проведите отладку в
программной средеVBA).

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

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

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

Окончание прил. 2

Меры
по устранению ошибки– измените
формулы или сразу же после удаления или
вставки скопированного восстановите
прежнее содержимое ячеек с помощью
кнопки Отменить.

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

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

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

Меры
по устранению ошибки– проверьте
правильность использования в функции
аргументов.

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

Меры
по устранению ошибки– используйте
другое начальное приближение для этой
функции.

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

Меры
по устранению ошибки– измените
формулу так, чтобы в результате ее
вычисления получалось число, попадающее
в диапазон от -110³⁰⁷до 110³⁰⁷.

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

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

1. Использован оператор, задающий
   пересечение диапазонов, не имеющих
   общих ячеек.

Меры
по устранению ошибки– задайте
правильно размерность пересекающихся
диапазонов или не используйте оператор
пересечения, если диапазоны не являются
таковыми. ВMicrosoftExcelоператором пересечения диапазонов
является пробел ( ).

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Если 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 выдает ошибку #ССЫЛКА.

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

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

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

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

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

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

Ошибка #Н/Д

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка ####

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

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

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

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

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

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

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

Просмотров: 129

Ошибки в формулах Excel.Причины и решения.

Почему Excel выдает ошибку?

Во время работы с Excel часто приходится сталкиваться с вычислениями. При этом время от времени вместо определенного значения введенная функция или формула Excel выдает ошибку. Сегодня мы попробуем разобраться с значениями ошибок и их источниками. Что делать, если Excel выдает ошибку и как это исправить?

Ошибки, связанные с неверным вводом формулы.

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

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

Пример 1. Перепутан порядок параметров для функции СУММЕСЛИ. В результате общий размер депонированной зарплаты по выбранному отделу стал равен нулю, что конечно не соответствует данным.

Рисунок 1

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

Рисунок 2

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

Рисунок 3

Частые ошибки, связанные с результатом.

В ходе работы случаются ситуации, когда вроде бы все сделано правильно, но в результате выходи не конкретное значение, пусть и неверное, а непосредственно название ошибки. В таких случаях в начале названия ошибки ставится знак решетки (#). Как распознать причину возникновения таких ошибок: на самом деле, все не так и сложно. Причина ошибки зачастую кроется в ее названии.

Наиболее распространены следующие варианты таких ошибок.

#### – такая ошибка говорит о том, что введено число, не умещающееся в границах ячейки. Исправить ее, наверное, проще всего – достаточно поменять ширину столбца. Но не всегда все так радужно. Например, такая же ошибка получится при получении в ходе расчета отрицательной даты или отрицательного времени.

#ДЕЛ/0! –эта ошибка возникает в результате деления на пустое значение. Так как ноль тоже является пустым значением – и не положительным, и не отрицательным, то эта же ошибка возникает и при попытке деления на ноль. Чтобы избежать такой ошибки, надо убедиться ячейки, на которые ссылается формула, на наличие данных. Учтите, что это может быть не настолько очевидно, как кажется! Например, функции ЧАСТНОЕ, ОСТАТ и другие в качестве второго аргумента используют указанный делитель.

#Н/Д! – Ошибка, связанная с недоступными данными. В примере ниже данная ошибка появилась, так как указан код, отсутствующий в таблице с данными.

Рисунок 4

#ИМЯ!  – а вот эту ошибку я называю ошибка зеваки. Ее появление говорит о том, что введено неверное название. Обычно она возникает при попытке ввода в формуле адреса ячеек русскими буквами – как вариант, А10 – буква здесь русская. К этой же ошибке приведет неверный ввод имени диапазона, таблицы, названия формулы и так далее. Проще говоря, она чаще всего возникает при элементарной опечатке во время ввода.

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

А) двоеточие. Используется для указания границ диапазона. Например, запись А1:А12 говорит о использовании ячеек в диапазоне от А1 до А12 включительно.

Б) точка с запятой. Указывает на перечисление диапазонов или отдельных ячеек.  В качестве примера можно привести запись А1:А12;С1:С12

В) пробел определяет пересечение диапазонов. Результатом будет новый диапазон, состоящий из общих ячеек исходных диапазонов. Например, запись =СУММ(B1:C5 A2:D3) идентична записи =СУММ(B2:C3) так как при пересечении диапазонов B1:C5 A2:D3 образуется диапазон B2:C3. Это наглядно видно на изображении ниже. Обратите внимание, что результат вычисления функции СУММ в обеих случаях одинаков.

Рисунок 5

#ЧИСЛО! Распространенный вариант ошибки, связанной с типом данных. Ошибка говорит о недопустимом вводе числового значения. Обычно означает о вводе числа в недопустимом формате, либо о получаемом в результате значении, выходящем за пределы числовых данных, используемых Excel. На примере ниже происходит попытка возвести число 1000000000 в степень с показателем 1000.

Рисунок 6

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

#ССЫЛКА! Так же довольно распространенный вариант. Возникает при попытке обратиться к несуществующему диапазону или ячейке. В следующем примере выводится ошибка именно такого типа при вставке функции ВПР. Дело в том, что в обрабатываемом диапазоне $D$2:$H$715 шестого столбца нет и в помине. Такая же ошибка может появится непосредственно в формуле, если диапазон, на который ссылалась формула или функция, был удален.

Рисунок 7

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

Как определить ячейки с ошибками и как их исправить?

Вот в этом месте вынужден своих читателей разочаровать. Если речь идет о неверном вводе пользователем в формуле, то автоматически никак. Только если проверять каждую формулу по очереди. В частности, на вкладке ФОРМУЛЫ есть кнопка «ВЫЧИСЛИТЬ ФОРМУЛУ».  С ее помощью можно по шагам проверить ход выполнения формулы и при возникновении ошибки    определить место ее возникновения.

Рисунок 8

Однако этот вариант работает только для составных формул. Если же вы применили простой вариант, например, функцию СУММЕСЛИ или ВПР, не применяя вложенных формул, то она не помощник. Другими словами, если вы ошиблись в простой формуле вроде перечисленных, то вы получите сообщение о имеющейся ошибке, и на этом все. Дальше разбирайтесь сами.

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

Прежде всего с помощью команды «Найти и выделить» → «выделить группу ячеек», расположенной на вкладке «Главная». Этот же вариант можно вызвать, нажав сочетание F5 или Ctrl+G, а затем кнопку «выделить».

Рисунок 9

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

Так же можно и использовать условное форматирование или проверку данных, применив в них формулы для определения ячеек с ошибками. Можно использовать кнопки «влияющие ячейки» и «Зависимые ячейки» на вкладке «Формулы».

Рисунок 10

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

Рисунок 11

Рисунок 12

В таких случая можно попробовать сочетания клавиш Ctrl+[ или Ctrl+Shift+{ для перехода к влияющим ячейкам на другом листе. Более подробно о поиске неверных данных с помощью условного форматирования и проверки данных можно прочитать в наших материалах здесь.

Однако все эти инструменты смогут лишь определить данные, которые уже неправильны.

Чтобы избежать ошибок при вводе данных, необходимо соблюдать ряд простых правил.

1. При вводе информации не применять никаких форматирований. Формат внешнего вида применяется к ячейкам, но не к самим данным.
2. При вставке функций, как по отдельности, так и в составной формуле, соблюдайте ее синтаксис и назначение аргументов. Например, если в функции ИНДЕКС первым параметром вы зададите номер строки или столбца, то программа просто вас не поймет.
3. Внимательно следить за введенными данными. Именно из-за невнимательности появляются такие ошибки как #ЗНАЧ, #ЧИСЛО, #ССЫЛКА и другие.
4. Использовать вложенность формул только тогда, когда это действительно необходимо. Например, если необходимо проверить количество знаков в ячейке В1 и при необходимости довести их до 12, добавив недостающие нули в начале, можно написать так

=СЦЕПИТЬ(ПОВТОР(«0»;12-ДЛСТР(В1));В1)

А можно так

=ТЕКСТ(В1;«000000000000»).

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

1. Не изменять расположение файлов с исходными данными, листов и диапазонов с ними без крайней необходимости.
2. Если все же формула выдала ошибку, не надо ее удалять и писать заново. От того, что вы десятки раз напишите формулу с ошибкой, ошибка не исчезнет. Внимательно просмотрите все диапазоны и адреса, которые в формуле применяются, проверьте соответствие данных параметрам функций и так далее. На практике часто случаюсь, что люди первым параметром функции СУММЕСЛИ указывали диапазон для суммирования, при работе с ВПР для приблизительного поиска четвертым аргументом указывали ноль, а для точного поиска четвёртый параметр вообще оказывался не задан, случалось, что при расчете количества позиций пытались ввести в качестве исходного значения сразу несколько вариантов ну и так далее.

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