При ошибочных вычислениях, формулы отображают несколько типов ошибок вместо значений. Рассмотрим их на практических примерах в процессе работы формул, которые дали ошибочные результаты вычислений.
Ошибки в формуле Excel отображаемые в ячейках
В данном уроке будут описаны значения ошибок формул, которые могут содержать ячейки. Зная значение каждого кода (например: #ЗНАЧ!, #ДЕЛ/0!, #ЧИСЛО!, #Н/Д!, #ИМЯ!, #ПУСТО!, #ССЫЛКА!) можно легко разобраться, как найти ошибку в формуле и устранить ее.
Как убрать #ДЕЛ/0 в Excel
Как видно при делении на ячейку с пустым значением программа воспринимает как деление на 0. В результате выдает значение: #ДЕЛ/0! В этом можно убедиться и с помощью подсказки.
Читайте также: Как убрать ошибку деления на ноль формулой Excel.
В других арифметических вычислениях (умножение, суммирование, вычитание) пустая ячейка также является нулевым значением.
Результат ошибочного вычисления – #ЧИСЛО!
Неправильное число: #ЧИСЛО! – это ошибка невозможности выполнить вычисление в формуле.
Несколько практических примеров:
Ошибка: #ЧИСЛО! возникает, когда числовое значение слишком велико или же слишком маленькое. Так же данная ошибка может возникнуть при попытке получить корень с отрицательного числа. Например, =КОРЕНЬ(-25).
В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.
В ячейке А2 – та же проблема с большими числами. Казалось бы, 1000 небольшое число, но при возвращении его факториала получается слишком большое числовое значение, с которым 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 для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
Кроме неожиданных результатов, формулы иногда возвращают значения ошибок. Ниже представлены некоторые инструменты, с помощью которых вы можете искать и исследовать причины этих ошибок и определять решения.
Примечание: В статье также приводятся методы, которые помогут вам исправлять ошибки в формулах. Этот список не исчерпывающий — он не охватывает все возможные ошибки формул. Для получения справки по конкретным ошибкам поищите ответ на свой вопрос или задайте его на форуме сообщества Microsoft Excel.
Ввод простой формулы
Формулы — это выражения, с помощью которых выполняются вычисления со значениями на листе. Формула начинается со знака равенства (=). Например, следующая формула складывает числа 3 и 1:
=3+1
Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.
Части формулы
-
Функции: это специальные формулы Excel, которые выполняют определенные вычисления. Например, функция ПИ() возвращает значение числа Пи: 3,142…
-
Ссылки: это ссылки на отдельные ячейки или диапазоны. Например, A2 возвращает значение ячейки A2.
-
Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
-
Операторы: оператор * (звездочка) служит для умножения чисел, а оператор ^ (крышка) — для возведения числа в степень. С помощью + и – можно складывать и вычитать значения, а с помощью / — делить их.
Примечание: Для некоторых функций требуются так называемые аргументы. Аргументы — это значения, которые некоторые функции используют при вычислениях. Аргументы функции указываются в ее скобках (). Функция ПИ не требует аргументов, поэтому у нее пустые скобки. У некоторых функций несколько аргументов, в том числе необязательные. Аргументы разделяются точкой с запятой (;).
Например, функция СУММ требует только один аргумент, но у нее может быть до 255 аргументов (включительно).
Пример одного аргумента: =СУММ(A1:A10).
Пример нескольких аргументов: =СУММ(A1:A10;C1:C10).
В приведенной ниже таблице собраны некоторые наиболее частые ошибки, которые допускают пользователи при вводе формулы, и описаны способы их исправления.
|
Рекомендация |
Дополнительные сведения |
|
Начинайте каждую формулу со знака равенства (=) |
Если опустить знак равенства, введенные данные могут отображаться в виде текста или даты. Например, при вводе СУММ(A1:A10)Excel отображает текстовую строку SUM(A1:A10) и не выполняет вычисление. Если ввести значение 11/2, вместо деления 11 на 2 Excel отображается дата 2–ноя (при условии, что ячейка имеет формат “Общий“) вместо деления 11 на 2. |
|
Следите за соответствием открывающих и закрывающих скобок |
Все скобки должны быть парными (открывающая и закрывающая). Если в формуле используется функция, для ее правильной работы важно, чтобы все скобки стояли в правильных местах. Например, формула =ЕСЛИ(B5<0);”Недопустимо”;B5*1,05) не будет работать, поскольку в ней две закрывающие скобки и только одна открывающая (требуется одна открывающая и одна закрывающая). Правильный вариант этой формулы выглядит так: =ЕСЛИ(B5<0;”Недопустимо”;B5*1,05). |
|
Для указания диапазона используйте двоеточие |
Указывая диапазон ячеек, разделяйте с помощью двоеточия (:) ссылку на первую ячейку в диапазоне и ссылку на последнюю ячейку в диапазоне. Например, =SUM(A1:A5), а не =SUM(A1 A5), которые возвращают #NULL! Ошибка. |
|
Вводите все обязательные аргументы |
У некоторых функций есть обязательные аргументы. Старайтесь также не вводить слишком много аргументов. |
|
Вводите аргументы правильного типа |
В некоторых функциях, например СУММ, необходимо использовать числовые аргументы. В других функциях, например ЗАМЕНИТЬ, требуется, чтобы хотя бы один аргумент имел текстовое значение. Если использовать в качестве аргумента данные неправильного типа, Excel может возвращать непредвиденные результаты или ошибку. |
|
Число уровней вложения функций не должно превышать 64 |
В функцию можно вводить (или вкладывать) не более 64 уровней вложенных функций. |
|
Имена других листов должны быть заключены в одинарные кавычки |
Если формула содержит ссылки на значения или ячейки на других листах или в других книгах, а имя другой книги или листа содержит пробелы или другие небуквенные символы, его необходимо заключить в одиночные кавычки (‘), например: =’Данные за квартал’!D3 или =‘123’!A1. |
|
Указывайте после имени листа восклицательный знак (!), когда ссылаетесь на него в формуле |
Например, чтобы возвратить значение ячейки D3 листа “Данные за квартал” в той же книге, воспользуйтесь формулой =’Данные за квартал’!D3. |
|
Указывайте путь к внешним книгам |
Убедитесь, что каждая внешняя ссылка содержит имя книги и путь к ней. Ссылка на книгу содержит имя книги и должна быть заключена в квадратные скобки ([Имякниги.xlsx]). В ссылке также должно быть указано имя листа в книге. В формулу также можно включить ссылку на книгу, не открытую в Excel. Для этого необходимо указать полный путь к соответствующему файлу, например: =ЧСТРОК(‘C:My Documents[Показатели за 2-й квартал.xlsx]Продажи’!A1:A8). Эта формула возвращает количество строк в диапазоне ячеек с A1 по A8 в другой книге (8). Примечание: Если полный путь содержит пробелы, как в приведенном выше примере, необходимо заключить его в одиночные кавычки (в начале пути и после имени книги перед восклицательным знаком). |
|
Числа нужно вводить без форматирования |
Не форматируйте числа, которые вводите в формулу. Например, если нужно ввести в формулу значение 1 000 рублей, введите 1000. Если вы введете какой-нибудь символ в числе, Excel будет считать его разделителем. Если вам нужно, чтобы числа отображались с разделителями тысяч или символами валюты, отформатируйте ячейки после ввода чисел. Например, если для прибавления 3100 к значению в ячейке A3 используется формула =СУММ(3 100;A3), Excel не складывает 3100 и значение в ячейке A3 (как было бы при использовании формулы =СУММ(3100;A3)), а суммирует числа 3 и 100, после чего прибавляет полученный результат к значению в ячейке A3. Другой пример: если ввести =ABS(-2 134), Excel выведет ошибку, так как функция ABS принимает только один аргумент: =ABS(-2134). |
Вы можете использовать определенные правила для поиска ошибок в формулах. Они не гарантируют исправление всех ошибок на листе, но могут помочь избежать распространенных проблем. Эти правила можно включать и отключать независимо друг от друга.
Существуют два способа пометки и исправления ошибок: последовательно (как при проверке орфографии) или сразу при появлении ошибки во время ввода данных на листе.
Ошибку можно исправить с помощью параметров, отображаемых приложением Excel, или игнорировать, щелкнув команду Пропустить ошибку. Ошибка, пропущенная в конкретной ячейке, не будет больше появляться в этой ячейке при последующих проверках. Однако все пропущенные ранее ошибки можно сбросить, чтобы они снова появились.
-
Для Excel в Windows щелкните Параметры > файла > формулы.
Для Excel на Mac щелкните меню Excel > Параметры > проверки ошибок.В Excel 2007 нажмите кнопку Microsoft Office
и выберите Параметры Excel > Формулы. -
В разделе Поиск ошибок установите флажок Включить фоновый поиск ошибок. Все найденные ошибки помечаются треугольником в левом верхнем углу ячейки.
-
Чтобы изменить цвет треугольника, которым помечаются ошибки, выберите нужный цвет в поле Цвет индикаторов ошибок.
-
В разделе Правила поиска ошибок установите или снимите флажок для любого из следующих правил:
-
Ячейки, содержащие формулы, которые приводят к ошибке. Формула не использует ожидаемый синтаксис, аргументы или типы данных. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!и #VALUE!. Каждое из этих значений ошибок имеет разные причины и разрешается по-разному.
Примечание: Если ввести значение ошибки прямо в ячейку, оно сохраняется как значение ошибки, но не помечается как ошибка. Но если на эту ячейку ссылается формула из другой ячейки, эта формула возвращает значение ошибки из ячейки.
-
Несогласованная формула вычисляемого столбца в таблицах. Вычисляемый столбец может содержать отдельные формулы, отличающиеся от формулы столбца master, что создает исключение. Исключения вычисляемого столбца возникают при указанных ниже действиях.
-
Ввод данных, не являющихся формулой, в ячейку вычисляемого столбца.
-
Введите формулу в ячейку вычисляемого столбца, а затем нажмите клавиши CTRL+Z или нажмите кнопку Отменить
на панели быстрого доступа. -
Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.
-
Копирование в вычисляемый столбец данных, не соответствующих формуле столбца. Если копируемые данные содержат формулу, эта формула перезапишет данные в вычисляемом столбце.
-
Перемещение или удаление ячейки из другой области листа, если на эту ячейку ссылалась одна из строк в вычисляемом столбце.
-
-
Ячейки, содержащие годы, представленные в виде 2 цифр: ячейка содержит текстовую дату, которая может быть неправильно интерпретирована как неправильный век, если она используется в формулах. Например, дата в формуле =ГОД(“1.1.31”) может относиться как к 1931, так и к 2031 году. Используйте это правило для выявления дат в текстовом формате, допускающих двоякое толкование.
-
Числа в формате текста или предшествуют апострофу. Ячейка содержит числа, хранящиеся в виде текста. Обычно это является следствием импорта данных из других источников. Числа, хранящиеся как текст, могут стать причиной неправильной сортировки, поэтому лучше преобразовать их в числовой формат. ‘=SUM(A1:A10) рассматривается как текст.
-
Формулы, несовместимые с другими формулами в регионе. Формула не соответствует шаблону других формул, расположенных рядом с ней. Во многих случаях формулы, соседствующие с другими формулами, отличаются только используемыми ссылками. В следующем примере из четырех смежных формул Excel отображает ошибку рядом с формулой =СУММ(A10:C10) в ячейке D4, так как смежные формулы увеличиваются на одну строку, а одна — на 8 строк. Excel ожидает формулу =СУММ(A4:C4).
Если используемые в формуле ссылки не соответствуют ссылкам в смежных формулах, приложение Microsoft Excel сообщит об ошибке.
-
Формулы, опускающие ячейки в области. Формула не может автоматически включать ссылки на данные, которые вы вставляете между исходным диапазоном данных и ячейкой, содержащей формулу. Это правило позволяет сравнить ссылку в формуле с фактическим диапазоном ячеек, смежных с ячейкой, содержащей формулу. Если смежные ячейки содержат дополнительные значения и не являются пустыми, Excel отображает рядом с формулой ошибку.
Например, при использовании этого правила Excel отображает ошибку для формулы =СУММ(D2:D4), поскольку ячейки D5, D6 и D7, смежные с ячейками, на которые ссылается формула, и ячейкой с формулой (D8), содержат данные, на которые должна ссылаться формула.
-
Незаблокированные ячейки, содержащие формулы. Формула не заблокирована для защиты. По умолчанию все ячейки на листе блокируются, поэтому их нельзя изменить при защите листа. Это поможет избежать случайных ошибок, таких как случайное удаление или изменение формул. Эта ошибка указывает, что ячейка была разблокирована, но лист не был защищен. Убедитесь, что ячейка не заблокирована.
-
Формулы, ссылающиеся на пустые ячейки. Формула содержит ссылку на пустую ячейку. Это может привести к неверным результатам, как показано в приведенном далее примере.
Предположим, требуется найти среднее значение чисел в приведенном ниже столбце ячеек. Если третья ячейка пуста, она не используется в расчете, поэтому результатом будет значение 22,75. Если эта ячейка содержит значение 0, результат будет равен 18,2.
-
Данные, введенные в таблицу, недопустимы. В таблице возникает ошибка проверки. Проверьте параметр проверки ячейки, перейдя на вкладку Данные > группу Data Tools > Проверка данных.
-
и выберите Параметры Excel > Формулы.
на панели быстрого доступа.
-
Выберите лист, на котором требуется проверить наличие ошибок.
-
Если расчет листа выполнен вручную, нажмите клавишу F9, чтобы выполнить расчет повторно.
Если диалоговое окно Поиск ошибок не отображается, щелкните вкладку Формулы, выберите Зависимости формул и нажмите кнопку Поиск ошибок.
-
Чтобы повторно проверить пропущенные ранее ошибки, щелкните Файл > Параметры > Формулы. Для Excel на Mac щелкните меню Excel > Параметры > проверки ошибок.
В разделе Поиск ошибок выберите Сброс пропущенных ошибок и нажмите кнопку ОК.
Примечание: Сброс пропущенных ошибок применяется ко всем ошибкам, которые были пропущены на всех листах активной книги.
Совет: Советуем расположить диалоговое окно Поиск ошибок непосредственно под строкой формул.
-
Нажмите одну из управляющих кнопок в правой части диалогового окна. Доступные действия зависят от типа ошибки.
-
Нажмите кнопку Далее.
Примечание: Если нажать кнопку Пропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.
-
Рядом с ячейкой нажмите кнопку “Проверка ошибок ”
, а затем выберите нужный параметр. Доступные команды различаются для каждого типа ошибки, и первая запись описывает ошибку.Если нажать кнопку Пропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.
, а затем выберите нужный параметр. Доступные команды различаются для каждого типа ошибки, и первая запись описывает ошибку.Если формула не может правильно вычислить результат, в Excel отображается значение ошибки, например #####, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА!, #ЗНАЧ!. Ошибки разного типа имеют разные причины и разные способы решения.
Приведенная ниже таблица содержит ссылки на статьи, в которых подробно описаны эти ошибки, и краткое описание.
|
Статья |
Описание |
|
Исправление ошибки #### |
Эта ошибка отображается в Excel, если столбец недостаточно широк, чтобы показать все символы в ячейке, или ячейка содержит отрицательное значение даты или времени. Например, результатом формулы, вычитающей дату в будущем из даты в прошлом (=15.06.2008-01.07.2008), является отрицательное значение даты. Совет: Попробуйте автоматически изменить ширину ячейки, дважды щелкнув между заголовками столбцов. Если ### отображается потому, что Excel не может отобразить все знаки, эта проблема будет исправлена. |
|
Исправление ошибки #ДЕЛ/0! ошибка |
Эта ошибка отображается в Excel, если число делится на ноль (0) или на ячейку без значения. Совет: Добавьте обработчик ошибок, как в примере ниже: =ЕСЛИ(C2;B2/C2;0). |
|
Исправление ошибки #Н/Д |
Эта ошибка отображается в Excel, если функции или формуле недоступно значение. Если вы используете такую функцию, как ВПР, есть ли для искомого значения соответствие в диапазоне поиска? Скорее всего, нет. Используйте функцию ЕСЛИОШИБКА для подавления ошибки #Н/Д. В этом случае можно ввести следующее: =ЕСЛИОШИБКА(ВПР(D2;$D$6:$E$8;2;ИСТИНА);0) |
|
Исправление ошибки #ИМЯ? ошибка |
Эта ошибка отображается, если Excel не распознает текст в формуле. Например имя диапазона или имя функции написано неправильно. Примечание: Если вы используете функцию, убедитесь, что ее имя написано неправильно. В данном случае слово СУММ введено с ошибкой. Удалите “а”, и Excel исправит формулу. |
|
Исправление ошибки #ПУСТО! |
Эта ошибка отображается в Excel, когда вы указываете пересечение двух областей, которые не пересекаются. Оператором пересечения является пробел, разделяющий ссылки в формуле. Примечание: Убедитесь, что диапазоны разделены правильно: области C2:C3 и E4:E6 не пересекаются, поэтому ввод формулы =СУММ(C2:C3 E4:E6) возвращает #NULL! могут вызвать текст и специальные знаки в ячейке. Если поставить запятую между диапазонами C и E, она будет исправлена =СУММ(C2:C3;E4:E6) |
|
Исправление ошибки #ЧИСЛО! ошибка |
Эта ошибка отображается в Excel, если формула или функция содержит недопустимые числовые значения. Используете ли вы функцию, которая выполняет итерацию, например IRR или RATE? Если да, то #NUM! ошибка, вероятно, из-за того, что функция не может найти результат. Инструкции по устранению неполадок см. в разделе справки. |
|
Исправление ошибки #ССЫЛКА! ошибка |
Эта ошибка отображается в Excel при наличии недопустимой ссылки на ячейку. Например, вы удалили ячейки, на которые ссылались другие формулы, или вставили поверх них другие ячейки. Вы случайно удалили строку или столбец? Смотрите, что произошло после удаления столбца B в формуле =СУММ(A2;B2;C2). Нажмите кнопку Отменить (или клавиши CTRL+Z), чтобы отменить удаление, измените формулу или используйте ссылку на непрерывный диапазон (=СУММ(A2:C2)), которая автоматически обновится при удалении столбца B. |
|
Исправление ошибки #ЗНАЧ! ошибка |
Эта ошибка отображается в Excel, если в формуле используются ячейки, содержащие данные не того типа. Вы используйте математические операторы (+, -, *, / ^) с разными типами данных? В таком случае попробуйте использовать вместо них функцию. В этом случае =СУММ(F2:F5) поможет устранить проблему. |
Если ячейки не видны на листе, для просмотра их и содержащихся в них формул можно использовать панель инструментов “Окно контрольного значения”. С помощью окна контрольного значения удобно изучать, проверять зависимости или подтверждать вычисления и результаты формул на больших листах. При этом вам не требуется многократно прокручивать экран или переходить к разным частям листа.
Эту панель инструментов можно перемещать и закреплять, как и любую другую. Например, можно закрепить ее в нижней части окна. На панели инструментов выводятся следующие свойства ячейки: 1) книга, 2) лист, 3) имя (если ячейка входит в именованный диапазон), 4) адрес ячейки 5) значение и 6) формула.
Примечание: Для каждой ячейки может быть только одно контрольное значение.
Добавление ячеек в окно контрольного значения
-
Выделите ячейки, которые хотите просмотреть.
Чтобы выделить все ячейки с формулами, на вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить (вы также можете нажать клавиши CTRL+G или CONTROL+G на компьютере Mac). Затем выберите Выделить группу ячеек и Формулы.
-
На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.
-
Нажмите кнопку Добавить контрольное значение.
-
Убедитесь, что вы выделили все ячейки, которые хотите отследить, и нажмите кнопку Добавить.
-
Чтобы изменить ширину столбца, перетащите правую границу его заголовка.
-
Чтобы открыть ячейку, ссылка на которую содержится в записи панели инструментов “Окно контрольного значения”, дважды щелкните запись.
Примечание: Ячейки, содержащие внешние ссылки на другие книги, отображаются на панели инструментов “Окно контрольного значения” только в случае, если эти книги открыты.
Удаление ячеек из окна контрольного значения
-
Если окно контрольного значения не отображается, на вкладке Формула в группе Зависимости формул нажмите кнопку Окно контрольного значения.
-
Выделите ячейки, которые нужно удалить.
Чтобы выделить несколько ячеек, щелкните их, удерживая нажатой клавишу CTRL.
-
Нажмите кнопку Удалить контрольное значение.
Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0) будет легче понять, если вы увидите промежуточные результаты:
|
В диалоговом окне “Вычисление формулы” |
Описание |
|
=ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0) |
Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ. Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40. |
|
=ЕСЛИ(40>50;СУММ(E2:E5);0) |
Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40. |
|
=ЕСЛИ(ЛОЖЬ;СУММ(E2:E5);0) |
Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент лог_выражение) имеет значение ЛОЖЬ. Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только тогда, когда выражение имеет значение ИСТИНА. |
-
Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
-
Откройте вкладку Формулы и выберите Зависимости формул > Вычислить формулу.
-
Нажмите кнопку Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычисления отображается курсивом.
Если подчеркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку Шаг с заходом, чтобы отобразить другую формулу в поле Вычисление. Нажмите кнопку Шаг с выходом, чтобы вернуться к предыдущей ячейке и формуле.
Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.
-
Продолжайте нажимать кнопку Вычислить, пока не будут вычислены все части формулы.
-
Чтобы посмотреть вычисление еще раз, нажмите кнопку Начать сначала.
-
Чтобы закончить вычисление, нажмите кнопку Закрыть.
Примечания:
-
Некоторые части формул, в которых используются функции ЕСЛИ и ВЫБОР, не вычисляются. В таких случаях в поле Вычисление отображается значение #Н/Д.
-
Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
-
Некоторые функции вычисляются заново при каждом изменении листа, так что результаты в диалоговом окне Вычисление формулы могут отличаться от тех, которые отображаются в ячейке. Это функции СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ, СЛУЧМЕЖДУ.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Отображение связей между формулами и ячейками
Рекомендации, позволяющие избежать появления неработающих формул
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Внесение даже небольшого изменения на рабочем листе Excel может привести к образованию ошибок в других ячейках. К примеру, вы можете случайно ввести значение в ячейку, которая раньше содержала формулу. Эта простая ошибка может оказать значительное влияние на другие формулы, и вы не сможете обнаружить ее, пока не сделаете какие-нибудь изменения на листе.
Ошибки в формулах делятся на несколько категорий:
Синтаксические ошибки: Возникают при неправильном синтаксисе формулы. Например, формула имеет несоответствующие скобки, или функция имеет не корректное количество аргументов.
Логические ошибки: В этом случает формула не возвращает ошибку, но имеет логический изъян, что является причиной неправильного результата расчета.
Ошибки неправильных ссылок: Логика формул верна, но формула использует некорректную ссылку на ячейку. Простой пример, диапазон данных для суммирования в формуле СУММ может содержать не все элементы, которые вы хотите суммировать.
Семантические ошибки: Например, название функции написано неправильно, в этом случае Excel вернет ошибку #ИМЯ?
Циклические ошибки: Циклические ссылки возникают, когда формула ссылается на саму себя, прямо или косвенно. Циклические ссылки иногда могут быть полезны, но зачастую они указываю на наличие проблемы.
Ошибки в формулах массивов: Когда вы вводите формулу массива, по окончании ввода необходимо нажать Ctrl + Sift + Enter. Если вы не сделали этого, Excel не поймет, что это формула массива, и вернет ошибку или некорректный результат.
Ошибки неполных расчётов: В этом случае формулы рассчитываются не полностью. Чтобы удостовериться, что се формулы пересчитаны, наберите Ctrl + Alt + Shift + F9.
Проще всего найти и скорректировать синтаксические ошибки. Чаще всего, вы знаете, когда формула содержит синтаксическую ошибку. К примеру, Excel не даст ввести формулу с несогласованными скобками. Иные ситуации синтаксических ошибок приводят к выводу следующих ошибок в ячейке листа.
Ошибка #ДЕЛ/0!
Если вы создали формулу, в которой производится деление на ноль, Excel вернет ошибку #ДЕЛ/0!
Так как Excel воспринимает пустую ячейку как ноль, то при делении на пустую ячейку тоже будет возвращена ошибка. Эта проблема часто встречается при создании формулы для данных, которые еще не были введены. Формула ячейки D4 была протянута на весь диапазон (=C4/B4).
Эта формула возвращает отношение значений колонок C к B. Так как не все данные по дням были занесены, формула вернула ошибку #ДЕЛ/0!
Чтобы избежать ошибки, вы можете воспользоваться формулой ЕСЛИ, для проверки, являются ли ячейки колонки B пустыми или нет:
Эта формула вернет пустое значение, если ячейка B4 будет пустой или содержать 0, в противном случае вы увидите посчитанное значение.
Другим подходом является использование функции ЕСЛИОШИБКА, которая проверяет на наличие ошибки. Следующая формула вернет пустую строку, если выражение C4/B4 будет возвращать ошибку:
Ошибка #Н/Д
Ошибка #Н/Д возникает в случаях, когда ячейка, на которую ссылается формула, содержит #Н/Д.
Обычно, ошибка #Н/Д возвращается в результате работы формул подстановки (ВПР, ГПР, ПОИСКПОЗ и ИНДЕКС). В случае, когда совпадение не было найдено.
Чтобы перехватить ошибку и отобразить пустую ячейку, воспользуйтесь функцией =ЕСНД().
Обратите внимание, что функция ЕСНД является новой функцией в Excel 2013. Для совместимости с предыдущими версиями воспользуйтесь аналогом этой функции:
Ошибка #ИМЯ?
Excel может вернуть ошибку #ИМЯ? в следующих случаях:
- Формула содержит неопределенный именованный диапазон
- Формула содержит текст, который Excel интерпретирует как неопределенный именованный диапазон. Например, неправильно написанное имя функции вернет ошибку #ИМЯ?
- Формула содержит текст не заключенный в кавычки
- Формула содержит ссылку на диапазон, у которого отсутствует двоеточие между адресами ячеек
- Формула использует функцию рабочего листа, которая была определена надстройкой, но надстройка не была установлена
Ошибка #ПУСТО!
Ошибка #ПУСТО! возникает в случае, когда формула пытается использовать пересечение двух диапазонов, которые фактически не пресекаются. Оператором пересечения в Excel является пробел. Следующая формула вернет #ПУСТО!, так как диапазоны не пересекаются.
Ошибка #ЧИСЛО!
Ошибка #ЧИСЛО! будет возвращена в следующих случаях:
- В числовом аргументе формулы введено нечисловое значение (например, $1,000 вместо 1000)
- В формуле введен недопустимый аргумент (например, =КОРЕНЬ(-12))
- Функция, использующая итерацию, не может рассчитать результат. Примеры функций, использующих итерацию: ВСД(), СТАВКА()
- Формула возвращает значение, которое слишком большое или слишком маленькое. Excel поддерживает значения между -1E-307 и 1E-307.
Ошибка #ССЫЛКА!
Ошибка #ССЫЛКА! возникает в случаях, когда формула использует недействительную ссылку на ячейку. Ошибка возникает в следующих ситуациях:
- Вы удалили колонку или строку, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если первая строка или столбцы A или B были удалены:
- Вы удалили рабочий лист, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если Лист1 был удален:
- Вы скопировали формулу в расположение, где относительная ссылка становится недействительной. Например, при копировании формулы из ячейки A2 в ячейку A1, формула вернет ошибку #ССЫЛКА!, так как она пытается обратиться к несуществующей ячейке.
- Вы вырезаете ячейку и затем вставляете ее в ячейку, на которую ссылается формула. В этом случае будет возвращена ошибка #ССЫЛКА!
Ошибка #ЗНАЧ!
Ошибка #ЗНАЧ! является самой распространенной ошибкой и возникает в следующих ситуациях:
- Аргумент функции имеет неверный тип данных или формула пытается выполнить операцию, используя неверные данные. Например, при попытке сложения числового значения с текстовым, формула вернет ошибку
- Аргумент функции является диапазоном, когда он должен быть одним значением
- Пользовательские функции листа не рассчитываются. Для принудительного пересчета нажмите Ctrl + Alt + F9
- Пользовательская функция листа пытается выполнить операцию, которая не является допустимой. Например, пользовательская функция не может изменить среду Excel или сделать изменения в других ячейках
- Вы забыли нажать Ctrl + Shift + Enter при вводе формулы массива
Вам также могут быть интересны следующие статьи
5 комментариев
Ренат, прошу у вас помощи,как у спеца… Создала книгу с кучей страниц и ссылок на разные страницы. Потом необходимо построить диаграммы, а у меня мастер диаграмм почему-то стал неактивен, и никак не могу сделать ни через «Вставку», никак. Может я что-то в настройках сбила? помогите, пожалуйста, что можно проверить.
Людмила, ни разу не сталкивался с проблемой неактивности мастера диаграмм по причине большого количества связей. В любом случае необходимо взглянуть на книгу. Мой ящик — admin@exceltip.ru
Людмила, есть вариант, что мастер диаграмм перестает быть доступен, если книга находится в общем доступе. Если это ваш случай, сделайте монопольный доступ в книге, вставляйте диаграммы и снова делайте книгу общей.
Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)
Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:
Причем заметьте, что итоги в нашей таблице тоже уже не считаются — одна ошибка начинает порождать другие, передаваясь по цепочке от одной зависимой формулы к другой. Так что из-за одной ошибочной ячейки, в конце концов, может перестать работать весь расчет.
Для лечения подобных ситуаций в Microsoft Excel есть мегаполезная функция ЕСЛИОШИБКА (IFERROR), которая умеет проверять заданную формулу или ячейку и, в случае возникновения любой ошибки, выдавать вместо нее заданное значение: ноль, пустую текстовую строку «» или что-то еще.
Синтаксис функции следующий:
=ЕСЛИОШИБКА( Что_проверяем ; Что_выводить_вместо_ошибки )
Так, в нашем примере можно было бы все исправить так:
Все красиво и ошибок больше нет.
Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции ЕОШ (ISERROR) и ЕНД (ISNA) . Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF) , создавая вложенные конструкции типа:
Такой вариант ощутимо медленне работает и сложнее для понимания, так что лучше использовать новую функцию ЕСЛИОШИБКА, если это возможно.
Какие существуют обозначения ошибок и способы их исправления?
Если формула содержит ошибку, то «Excel 2007» отобразит специальное сообщение об ошибке. При этом каждый тип ошибки обозначается своим сообщением, вызывается разными причинами и соответственно требует различных способов разрешения.
##### — Что обозначает и как исправить?
Эти символы сообщают, что столбец, содержащий числа, недостаточно широк для них, или же дата и время, введенные в ячейки данного столбца, содержат отрицательные числа.
В первом случае достаточно просто увеличить ширину столбца или изменить числовой формат данных (например, уменьшить число знаков после запятой).
Во втором же случае надо:
- проверить формулу, если вычисляется число дней между двумя датами;
- если формула не содержит ошибок, то необходимо изменить формат ячейки и перейти, например, с формата «Дата и время» на «Общий» или «Числовой» формат.
#ЗНАЧ! — Что обозначает и как исправить?
Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет отображаться #ЗНАЧ! .
#ДЕЛ/0! — Что обозначает и как исправить?
Эти сообщение, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.
- В окне открытого листа выделите ячейку с данной ошибкой и нажмите клавишу F2.
- Когда в ячейке отобразится сама формула или функция, а также станут выделенными все ячейки, связанные ссылками с данной формулой или ячейкой, внимательно проверьте значения в выделенных ячейках и при необходимости внесите коррективы в формулу или измените ссылки на пустые ячейки.
- Нажмите Enter или кнопку «Ввод» на строке формул.
#ИМЯ? — Что обозначает и как исправить?
Эти символы сообщают, что в формуле используется несуществующее имя или неправильный оператор.
1 вариант
Если используется имя, которое не было определено, то необходимо сделать следующее:
- В окне открытого листа перейдите к вкладке «Формулы» и в группе «Определенные имена» щелкните по кнопке «Диспетчер имен».
- В окне «Диспетчер имен» просмотрите, присутствует ли данное имя в списке.
Если данное имя отсутствует, то необходимо добавить его согласно инструкции «Как присвоить имя ячейке или диапазону ячеек?».
2 вариант
Если существует ошибка в написании имени, то необходимо проверить его орфографию.
- В окне открытого листа нажмите клавишу F3.
- В окошке «Вставка имени» выберите в списке имя нужное имя и нажмите кнопку «ОК».
- Внесите исправления (при необходимости) в формуле, которая отобразится в соответствующей ячейке.
- Для закрепления нажмите клавишу Enter.
3 вариант
Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10) .
- В окне открытого листа выделите ячейку с ошибкой в написании функции.
- Раскройте меню кнопки «Источник ошибки» рядом с данной ячейкой.
- В списке команд выберите пункт «Изменить в строке формул».
- На строке формул в окошке имени отобразится правильно написание нужной формул, согласно которому и измените ошибочное написание.
- Закрепите результат щелчком по клавише Enter.
4 вариант
Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в формуле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .
5 вариант
Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках проверить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10) .
6 вариант
Если в формулу включена ссылка на значения ячеек других листов или книг, при этом имя не заключено в ординарные кавычки, то необходимо это имя заключить в апострофы ( “ ).
#Н/Д — Что обозначает и как исправить?
Эти символы сообщают, что нужное значение не доступно для функции или формулы.
1 вариант
Если в формулу были введены недостающие данные, а также #Н/Д или НД() , то #Н/Д необходимо заменить новыми данными.
2 вариант
Если в функциях ГПР , ПРОСМОТР , ПОИСКПОЗ или ВПР указывается неверное значение аргумента «искомое_значение» (например, ссылка на диапазон ячеек, что недопустимо), то необходимо соответственно указать ссылку только на нужную ячейку.
3 вариант
Если не заданы необходимые аргументы стандартной функции листа, то нужно ввести все необходимые соответствующие аргументы функции.
4 вариант
Если в формуле используется недоступная в данный момент функция, то необходимо проверить, что книга, использующая функцию листа, открыта, а также то, что функция правильно работает.
5 вариант
Если для просмотра значений в несортированной таблице используются функции ВПР , ГПР или ПОИСКПОЗ , для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интервальный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определенное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ .
В функции ПОИСКПОЗ содержится аргумент «тип_сопоставления», позволяющий сортировать данные для поиска. Если же соответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.
6 вариант
Если в формуле массива используется аргумент, не соответствующий диапазону, указанному в формуле массива, то необходимо проверить диапазон ссылок формулы на соответствие числу строк и столбцов или ввести формулу массива в меньшее число ячеек.
7 вариант
Если не заданы один или несколько необходимых аргументов стандартной или созданной функции листа, необходимо проверить и задать все необходимые аргументы функций.
#ССЫЛКА! — Что обозначает и как исправить?
Эти символы сообщают о неверной ссылке на ячейку.
1 вариант
Если ячейка, на которую ссылается формула, была удалена или же в данную ячейку помещено значение скопированных ячеек, то необходимо изменить формулу с учетом новых ссылок.
2 вариант
Если используется функция OLE, связанная с незапущенной программой, то необходимо запустить требуемую программу.
3 вариант
Если используется ссылка на недоступный объект DDE (Dynamic Data Exchange – динамический обмен данными), например «system», то необходимо проверить, что используется правильный раздел DDE.
4 вариант
Если используется макрос, вызывающий макрофункцию, которая при определенных вариантах выдает значение #ССЫЛКА! . Необходимо проверить аргумент функции и удостовериться, что он ссылается на допустимые ячейки или диапазоны ячеек.
#ЧИСЛО! — Что обозначает и как исправить?
Это сообщение об использовании неправильных числовых значений в формуле или функции.
1 вариант
Если в функцию, использующую числовой аргумент, было вставлено неприемлемое значение, то необходимо проверить все аргументы функции и при необходимости исправить написание всех чисел и формат соответствующих ячеек.
2 вариант
Если в функции с итерацией (подбором параметров), например «ВСД» или «СТАВКА», невозможно найти результат, то необходимо попробовать иное начальное приближение или изменить число итераций.
3 вариант
Если в результате вычисления формулы получается число, которое слишком велико или, наоборот, слишком мало, чтобы оно могло быть отображено в Excel, то необходимо изменить формулу и добиться, чтобы результат находился в диапазоне от 1*10307 до 1*10307.
#ПУСТО! — Что обозначает и как исправить?
Эти сообщение об отсутствии общих ячеек, когда задано пере
сечение двух областей.
1 вариант
Если используется ошибочный оператор диапазона, то необходимо внести исправления, а именно:
- для обозначения ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между начальной и конечной ячейкой диапазона. Например, СУММ(С1:С20) .
- для обозначения ссылки на два непересекающихся диапазона используется оператор объединения – точкой с запятой (;). Например, СУММ(С1:С20;D1:D20) .
2 вариант
Если указанные диапазоны не имеют общих ячеек, то необходимо изменить ссылки, добиваясь нужного пересечения.
Наглядный пример всего этого можно найти, например, в графики и таблицы которые мы включаем, чтобы придать дополнительную ценность нашим проектам здесь. Точно так же, как правило, мы используем всевозможные тексты, дополняющие те числовые данные, которые составляют основную часть проекта. Все это, не говоря уже о различных формулах и функциях, которые Microsoft Программа позволяет нам выполнять все виды операций.
Более того, мы можем сказать, что мы находим характеристики, связанные с разные типы данных , как мы собираемся показать вам ниже. Не все из них сосредоточены на том, чтобы предоставить нам то, что нам нужно для выполнения более или менее сложных вычислений. Мы также находим некоторые полезные функции, которые помогут нам создавать и улучшать электронные таблицы в других разделах. Мы даже собираемся найти некоторые из этих элементов, которые мы можем определить для более продуктивной работы с вышеупомянутыми тексты .
Именно об этом случае мы и поговорим далее. Может возникнуть ситуация, когда мы окажемся с большая электронная таблица с которыми нам приходится иметь дело. Внезапно мы обнаруживаем, что некий текст, составленный из слово или их цепочка , неправильно и неоднократно. Обычным делом в этом случае является попытка исправить это вручную, но в большинстве случаев это будет не лучшим решением.
При этом мы хотим сказать вам, что мы можем пройти всю электронную таблицу с помощью курсоров, исправляя этот текст один за другим. Это можно рекомендовать, если указанная ошибка повторяется четыре или пять раз. Однако, если говорить о нескольких десятках или даже сотнях, все уже существенно меняется. Исправление этого ошибка в строке символов при большом количестве записей может стать довольно утомительной задачей.
Но для экономии времени и сил в этом плане Microsoft Excel предлагает нам полезный встроенный функционал, который поможет нам во всем этом. В частности, мы имеем в виду функцию под названием «Найти и заменить», которая позволяет нам решать вышеупомянутые проблемы с помощью тексты электронной таблицы . Поэтому это может стать отличной альтернативой, когда речь идет о массовом изменении и замене определенных текстовых строк.
Здесь мы найдем новое окно, в котором мы уже можем определить исходный текст и целевой текст, которые мы хотим заменить массово . Более того, у нас даже будет возможность установить определенный формат ввода для этого текста и заменить его другим форматом вывода. Кроме того, в этом же окне мы должны указать места, в которых Excel будет искать эту строку символов и произвести соответствующую замену.
У нас даже будет возможность того, что приложение различает или нет между прописные и строчные буквы введенной строки, в дополнение к возможности поиска этих символов в самих формулах.
Ошибки пользователей при вводе данных – одна из самых распространенных проблем в Excel. В результате ошибочных данных ячейки с формулами на разных листах друг за другом могут заполниться ошибками #ЗНАЧ, #H/Д и т.п. Поиск ошибок может занимать длительное время, потому что непонятен первоначальный источник ошибки. С чего начинать? Но появление ячеек с ошибками это даже хорошо.
Гораздо хуже, когда внешне все выглядит нормально, но при этом в данных есть ошибки. В таких случаях пользователь даже не догадываться о наличии ошибок и формирует недостоверные отчеты. А в результате принимаются ошибочные управленческие решения. Что же делать?
НЕДОСТАТКИ СТАНДАРТНЫХ ИНСТРУМЕНТОВ EXCEL
Как вы наверняка знаете, в Excel существует стандартный функционал для контроля вводимых в ячейки данных. Он устанавливается через меню Данные -> Проверка данных. В результате появляется диалоговое окно Проверка вводимых значений c тремя закладками.
На закладке Параметры в поле Тип данных можно выбрать тип данных. В зависимости от введенного типа становятся доступными для заполнения другие поля, например:
- для типа Число, Дата, Время можно задать границы – от и до и проверять на соблюдение условий между, меньше, больше и т.д.;
- для типа Список можно задать список доступных значений;
- для типа Другой можно задать свою собственную формулу, которая будет проверять вводимое значение на соблюдение каких либо условий, в том числе в зависимости от значений в других ячейках.
На закладках Сообщение для ввода и Сообщение об ошибке можно задать текст соответствующего сообщения, чтобы подсказать пользователю в чем он ошибся.
Внешне сервис проверки данных кажется очень эффективным. Особенно удобно выбирать значение из выпадающего списка, а не вводить его вручную. Тем не менее стандартный функционал Excel позволяет решить проблему контроля вводимых данных лишь частично и у него есть существенные недостатки:
1. Стандартные средства контроля введенных данных могут создать у пользователя вредную иллюзию защищенности от ошибок. Особенно если данные вводили не вы, а кто-то другой, и вы рассчитываете что там все нормально, система отловила ошибки. Ведь проверка работает только при ручном вводе данных, но не работает при внесении данных другими способами!
Например, если данные вводятся в ячейку с использованием команд Копировать->Вставить, Копировать->Специальная вставка-Значения или путем «растаскивания» от соседних ячеек, то проверка данных не осуществляется. Также значения в ячейках не контролируются, если они введены в ячейку с помощью макроса (если только контроль не осуществляется в самом макросе).
Таким образом, неверные данные могут попасть в ячейки, многократно тиражированы и Excel это не отследит!
Часто это бывает, когда вы выгружаете данные в таблицы из других программ, например, 1С. Но особенно неприятно когда ошибки незаметны или малозаметны. Например:
- Значение в ячейке выглядит как число и дата, а на самом деле оно является текстом. Например потому, что перед или после цифр есть пробел или другие непечатные (невидимые) символы.
- Ячейка выглядит пустой, а на самом деле там есть непечатные символы.
- В качестве разделителя целой и дробной части использована точка вместо запятой.
- Введено значение, которое отсутствует в “привязанном” к ячейке выпадающем списке.
Последняя ситуация достаточно распространена. Например, вы ведете реестр платежей от контрагентов, и вводите наименование контрагентов с помощью выпадающего списка, связанного со справочником контрагентов. Спустя некоторое время вы обнаруживаете в наименовании контрагента ошибку и исправляете ее в справочнике. При этом Excel никак не отреагирует на то, что в реестре есть записи со старым (ошибочным) наименованием контрагента. То есть Excel при изменении элементов списка не отлавливает уже введенные данные типа «Список».
2. Стандартные способы проверки могут “тормозить”
Достаточно распространенной проверкой является проверка для типа данных Другой с использованием формул. Это может быть проверка на непревышение планового бюджета по статьям расходов, проверка на уникальность элемента списка и т.п.
В этом случае часто используются «тяжелые» функции СЧЕТЕСЛИ, СЧЕТЕСЛИМН, которые подсчитывают количество или сумму по уже введенным ранее данным и сравнивают, не превысит ли вводимое значение какой-то предел.
Всякий раз, когда вы вводите или изменяете значение в ячейке, отрабатывает эта “тяжелая” формула. Если при этом с ячейкой связано много других ячеек, будут пересчитываться и они. Когда вы это делаете и тестируете в таблицах мало данных и задержек не ощущается.
Но по мере роста таблиц Excel может начать «залипать» при вводе данных, иначе говоря не успевать за руками пользователя. И это очень раздражает.
Поэтому, использовать проверку вводимых данных с использованием типа Другой и «тяжелыми» формулами нужно очень обдуманно. Я советую вообще исключить использование подобных приемов.
3. Бесполезный инструмент “Обвести неверные данные”
Для проверки правильности введенных значений в Excel есть встроенный инструмент «Обвести неверные данные», который вызывается через пункты меню – Данные -> Проверка данных -> Обвести неверные данные.
Команда доступна только на незащищенном листе, а результатом ее работы будет обведение неверных по мнению Excel данных красным овалом как на рисунке выше.
На мой взгляд, это функция практически бесполезна по трем причинам:
- Будут признаны ошибочными и обведены пустые ячейки с типом «Список допустимых значение», если для них при настройке была снята галочка в поле «игнорировать пустые ячейки» (она снята в большинстве случаев). Но согласитесь – неверные значения и пустые значения это разные вещи.
- Помечаются только первые 255 недопустимых значения.
Если в вашей таблице десятки колонок и тысячи строк, то придется много раз повторять одни и те же действия – исправлять ошибки и снова запускать обводку неверных данных.
- Она не всегда работает корректно. На вышеприведенном рисунке не были обведены значения в колонке ЦУ – Объект «Сколково» – хотя эти значения ошибочные, т.к. их нет в списке.
4. “Подсвечивание” ячеек с ошибочными данными
Еще одним распространенным способом проверки наличия неверных данных является выделение ячеек, в которых обнаружены некорректные данные, ярким цветом фона или шрифта с помощью условного форматирования.
Я считаю это способ неэффективным. Ну “подсветили” вы некорректные данные в таблице, а что дальше?
Если таблице тысячи строк, то либо придется прокручивать всю таблицу в поисках подсвеченных ячеек, либо применять автофильтр по цвету. Причем поочередно делать для каждого столбца, так как непонятно – есть там неверные данные или нет.
Это очень трудоемко. Поэтому, пользователи начинают добавляют к своей таблице макросы, которые позволяют подсчитывать количество ячеек по цвету фона или шрифта. Такие макросы можно без проблем найти в интернете, но дело не в этом.
Мало того, что из-за правил условного форматирования Excel будет “тормозить”, так пользователи еще и усложняют систему совсем необязательными макросами.
КАК ПРАВИЛЬНО ОРГАНИЗОВАТЬ ПРОВЕРКУ ВВЕДЕННЫХ ДАННЫХ
В своих решениях я перепробовал несколько вариантов и остановился на мой взгляд может быть не самый эстетичный, зато самый надежный и эффективный.
Этот способ может быть дополнением к стандартному функционалу проверки вводимых данных, или применяться самостоятельно. Суть способа в следующем.
Любую проверку вводимых значений можно делать и обычными функциями Excel, такими как ЕСЛИ, ЕТЕКСТ, ЕНЕТЕКСТ, ЕЧИСЛО, ПОИСКПОЗ. Поэтому в таблице можно создать служебную колонку в ячейках которой задать формулу, которая будет проверять корректность введенных данных по заданным условиям во всех ячейках строки, предназначенных для ввода и сообщать об этом пользователю. Расчет формулы сделать включаемым/ выключаемым в зависимости от положения “тумблера”. Что такое тумблеры и зачем они, написано здесь.
Это не предотвратит некорректный ввод данных, но предупредит об этом пользователя.
Зато формула будет отрабатывать не при ручном вводе данных, а по вашему требованию. Таким образом, пользователь может свободно вводить данные и Excel не будет «тормозить». Перед формированием управленческих отчетов пользователь запускает проверку, выявляет все ошибки и исправляет их.
Как это устроено проще всего понять это на примере (см. рисунок ниже)
1. В шапке таблицы создается служебная строка, каждая ячейка которой содержит формулу =Столбец (). Теперь у пользователя всегда перед глазами есть порядковые номера столбцов. Причем, что бы вы ни сделали со столбцами (вставка, удаление, перемещение) нумерация всегда будет сохраняться.
2. В таблице создается служебная колонка (удобней если она будет крайней слева), в ячейках которой вводится формула, проверяющие корректность введенных в строке таблицы значений.
Формула состоит из блоков функций, соединенных между собой амперсандом для сцепки текста в единую строку. Каждый блок проверяет одну ячейку в строке и если значение ошибочное возвращает номер колонки, а если верное не возвращает ничего.
Блоки могут быть такие:
- Ячейки, в которых должна вводиться дата или число проверяется такой конструкцией
ЕСЛИ(ЕТЕКСТ(проверяемое_значение);номер_столбца&”.”;””)
- Ячейки, в которых значение вводится из выпадающего списка проверяется такой конструкцией:
EСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(искомое_значение; просматриваемый_массив;0);-1)>0;””;номер_столбца)
- Если вам нужно проверять даты на вхождение в определенный период времени используйте обычные функции ЕСЛИ, >=,<= и т.д. Аналогично для чисел.
Не забудьте всю эту конструкцию поместить внутрь функции ЕСЛИ: =ЕСЛИ(НаТ1;ваша_цепочка_блоков;””), где НаТ1 это имя ячейки-тумблера, которая включает/выключает расчет формулы. Это позволит не вычислять формулу при всяком изменении в ячейках, а делать это по необходимости.
В результате в служебной колонке, в тех строках, где будут обнаружены ошибки, отобразятся номера колонок с некорректными данными, разделенные точками. В шапке таблицы можно подсчитать количество строк с ошибками следующей формулой:
=ЧСТРОК(A$5:A$677)-СЧЁТЕСЛИ(A$5:A$677;””) , где A$5:A$677 – диапазон служебного столбца в котором выводятся признаки ошибок.
В результате вы всегда видите сколько, в каких строчках и в каких столбцах у вас ошибки и можете быстро их исправить, используя стандартные возможности Excel – фильтр, растаскивание и т.д.
Ваша программа будет еще удобнее если вы соберете данные о количестве ошибок на одном листе (в моих решениях это лист Настройки как на рисунке ниже) и там же разместите кнопку для запуска формул проверки ошибок. В этом случае вам не нужно метаться между листами рабочей книги. Достаточно нажать одну кнопку – макрос включит «тумблер» – все листы будут проверены на наличие ошибок, данные о количестве ошибок будут выведены в таблице. Если ошибок не обнаружено ячейка останется пустой. Если ошибки обнаружены вам нужно перейти на соответствующий лист и исправить их.
Все просто и удобно, никаких «тормозов» и экономия времени!
Cookie-файлы
Настройка cookie-файлов
Детальная информация о целях обработки данных и поставщиках, которые мы используем на наших сайтах
Аналитические Cookie-файлы
Отключить все
Мы используем файлы Cookie для улучшения работы, персонализации и повышения удобства пользования нашим сайтом. Продолжая посещать сайт, вы соглашаетесь на использование нами файлов Cookie.
Подробнее о нашей политике в отношении Cookie.
Подробнее
Понятно
