10 возможных ошибок в функциях Excel и их решения

Вот как автоматически обнаруживать эти ошибки в Excel

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

Вот 10 распространенных ошибок в функциях Excel

Ниже приведены десять наиболее распространенных кодов ошибок для возможных проблем Excel и их решений.

1. # ИМЯ?

Возникают ошибки ввода - если это так в формуле, вместо результата отображается сообщение об ошибке # ИМЯ? Нет смысла скрывать ошибку такой функцией, как ЕСЛИОШИБКА. Вы должны это исправить. Все, что вам нужно сделать, это внимательно посмотреть на название, которое вы дали формуле. Есть скрученное письмо? Вы забыли букву или ввели ее дважды? Например, исправьте = СУММ (A3: A16) на = СУММ (A3: A16), и вы получите правильный результат.

Использовать мастер формул

Вы можете избежать подобных опечаток с помощью Помощника по формулам. Вот как это работает:

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

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

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

2. #NULL!

Это сообщение об ошибке может указывать на две вещи:

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

В первом случае вы имеете в виду непрерывный диапазон ячеек в формуле - например, ячейки от B4 до B12. Вы используете двоеточие как оператор диапазона. Например, если вы хотите подсчитать сумму, правильная формула: = СУММ (B4: B12). Если вы имеете в виду две области без перекрытия, точка с запятой является подходящим оператором. Если вы хотите вычислить сумму из областей от B4 до B12 и от C9 до C23, эта формула верна: = СУММ (B4: B12; C9: C23).

Во втором случае вы хотите работать с перекрывающимися диапазонами ячеек, но два указанных диапазона не перекрываются. Например, вы получаете ошибку для формулы = ЯЧЕЙКА («Адрес» (B4: B12 D4: D6)). Эти две области не пересекаются. Если вы измените области так, чтобы они перекрывались - например, как = ЯЧЕЙКА («Адрес» (B4: B12 B5: D5)), в результате отобразится точка пересечения обеих областей. В данном случае это ячейка B5.

3. # ССЫЛКА!

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

Например, у вас есть электронная таблица Excel со строками 1, 2 и 3 и столбцами A, B и C. Формула: = СУММ (A2; B2; C2). Теперь удалите строку 2. Вместо результата вы увидите значение ошибки #REFER, потому что одно из значений для расчета не существует.

Если вы случайно удалили деталь, отсутствующую для расчета, вы можете исправить ошибку напрямую с помощью команды «Отменить». Если подобласть была удалена правильно, переформулируйте формулу. Если вы введете = СУММ (A2: C2), вам будет показан правильный результат, потому что Excel просто удаляет удаленный второй столбец.

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

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

Поскольку существует множество потенциальных источников ошибок для #VALUE! есть много способов исправить это. Следующие решения могут помочь:

• Вы можете использовать функции вместо математических операторов. Поэтому вместо = B4 + B5 + B6 введите функцию = СУММ (B4: B6).
• Проверьте соответствующие ячейки на наличие специальных символов. При необходимости используйте для этого функцию ЕСТТЕКСТ в отдельном столбце. Это показывает, в какой ячейке находится ошибка.
• Возможно, значение ошибки связано с пробелами в ячейке, которая появляется в функции. Чтобы узнать, отметьте соответствующие ячейки. Затем перейдите к «Поиск и выбор» в разделе «Пуск» и нажмите «Заменить». Введите пробел для «Искать» и ничего для «Заменить на». Теперь вы можете заменить ненужные пробелы.
• Скрытые символы также могут привести к этой распространенной ошибке в функциях Excel. Чтобы их устранить, перейдите в «Пуск» и «Сортировка и фильтр» на «Фильтр». Под стрелкой фильтра отключите «Выбрать все» и отметьте «Пустые ячейки» и все позиции, где ничего нет. Если вы нажмете ОК, Excel покажет вам все якобы пустые ячейки, содержащие скрытые символы. Выделите его и нажмите «Удалить». Удалите фильтр еще раз.

5. #####

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

6. # DIV / 0!

Например, предположим, что вы вводите функцию, которую хотите разделить несколько значений на другие значения. Тогда может случиться так, что в одной из ячеек, значения которой вы хотите разделить, есть ноль - или вообще ничего. В этом случае появляется значение ошибки # DIV / 0!, Указывающее, что деление на ноль запрещено.

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

7. #NV!

Этим значением ошибки Excel указывает, что то, что вы искали, найти не удалось. Например, если вы присвоили значения определенным терминам и хотите их отобразить, но один из терминов отсутствует, это сообщение об ошибке появляется вместо результата. Это часто выполняется в сочетании с формулами ПРОСМОТР, ГПР, ПОИСКПОЗ или ВПР. Например, вы можете отображать ошибки с помощью ВПР, потому что это помогает вам сравнивать таблицы друг с другом или суммировать их.

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

8. # НОМЕР

Если функция содержит недопустимое числовое значение, вы получите ошибку # ЧИСЛО. Это может произойти, например, когда вы пишете числа вроде 1000 с точкой после единицы. Введите числа без форматирования. Возможно, вы также извлекаете квадратный корень из отрицательного числа или выполняете другие вычисления, которые не работают математически?

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

Как это сделать:

1. В разделе «Файл» и «Параметры» выберите «Формулы». Здесь в разделе «Параметры расчета» установите флажок «Активировать итерационный расчет».

2. В разделе «Максимальное количество итераций» вы можете указать количество вычислений, которые должен выполнить Excel. Чем выше это число, тем больше времени потребуется для расчета.

3. В поле «Максимальное изменение» вы можете указать, насколько может быть сумма изменения между двумя результатами счета.

В редких случаях также может случиться, что результат формулы окажется слишком большим или слишком маленьким для вычисления в Excel. Однако для этого результат должен быть ниже -1x10 (high) 307 или выше 1x10 (high) 307. В этом случае вам необходимо отредактировать формулы, чтобы результат находился в диапазоне, который Excel может вычислить.

9. Циркулярная ссылка

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

Как устранить ошибку:

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

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

10. E +

E + не является типичной ошибкой в функциях Excel, но время от времени он все же создает путаницу для пользователей. Если вы введете очень большое число в одну из ячеек, Excel может сократить его. Затем в игру вступает E +. Например, 265000000000000 в ячейке Excel становится 2.65E + 14. Это экспоненциальный формат. Excel использует его, чтобы отображать даже большие числа, чтобы они были полностью видны в ячейках.

Вот как вы можете отобразить числа полностью

Если вы хотите, чтобы числа отображались полностью, необходимо выполнить несколько простых шагов:

1. Отметьте соответствующие позиции в вашем списке.

2. Нажмите Ctrl и 1, чтобы отформатировать ячейки.

3. В открывшемся диалоговом окне выберите вкладку «Числа» и категорию «Число».

4. Введите большое число и выберите, сколько десятичных знаков вы хотите отобразить и хотите ли вы отображать разделитель тысяч.

5. После того, как вы нажмете ОК, Excel полностью отобразит номер в таблице.

Автоматическое обнаружение типичных ошибок в функциях Excel

У вас есть возможность активировать автоматическую проверку Excel. Если он у вас еще не включен, достаточно нескольких кликов:

В разделе «Файл» перейдите в «Параметры».

Выберите «Формулы».

Установите флажок «Включить фоновую проверку ошибок».

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

Эти функции выявляют ошибки в функциях Excel.

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

ЕСЛИ ОШИБКА

Некоторые формулы могут быть неполными в нынешнем виде. Вы хотите, чтобы ошибки не отображались вам? Для этого подходит функция ЕСЛИОШИБКА. Он оценивает типы ошибок #NV, #VALUE!, #REFERENCE!, # DIV / 0!, #NUMBER!, #NAME? или #NULL! конец. Он имеет простую структуру, поскольку содержит только значение и value_if_error. Первое значение проверяет правильность функции. Программа должна отображать последнее значение в результате, если есть ошибка. Функция очень полезна при длительном редактировании таблицы.

Пример:

Вы вводите = ЕСЛИОШИБКА (B3 * C3; «Введите значения») или = ЕСЛИОШИБКА (B3 * C3; ""). В первом случае вам напомнят, что вам еще нужно сделать (в случае ошибки), с текстом «Добавить значения». Во втором случае ячейка, которая обычно содержала бы результат, просто остается пустой.

Внимание: При вводе текста в формулу вы всегда должны заключать его в кавычки. Иначе не получится. Поскольку без кавычек Excel понимает слова как часть формулы.

ОШИБКА

В связи с функцией ЕСЛИОШИБКА функция ЕСТЬ ОШИБКА также может указывать на существующие ошибки. Таким образом, они не мешают дальнейшим вычислениям в списке. Для этого введите, например: = IF (ISERROR (B3); «проверьте»; B3 / 6). Если расчет можно провести, вы получите результат. Если Excel обнаруживает ошибку при проверке B3, в соответствующей ячейке появляются слова «пожалуйста, проверьте».

Вывод: исправляйте ошибки в функциях Excel быстро и легко

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