Поиск и исправление ошибок в формулах MS Excel
Иногда мы все ошибаемся, в том числе и при использовании формул в MS Excel. Однако не всегда наши ошибки являются именно ошибками, то есть приводят к выводу MS Excel соответствующего предупреждения и рекомендаций о том, как справится с возникшей проблемой. Гораздо хуже, когда никакого предупреждения об ошибках не выводится, а мы явно видим, что результат совершенно не соответствует реальности.
К счастью, в наших руках несколько отличных инструментов для поиска “хитрых” ошибок в формулах MS Excel.
Влияющие и зависимые ячейки в MS Excel
Обычно в итоговых строках таблицы выводится некая сводка данных расположенных в других местах таблицы, а то и результат каких-то промежуточных вычислений. Если таких вычислений довольно много, то ошибиться в одном из них становится проще простого. Впрочем, исправить такую ошибку тоже очень просто… но только при условии, что вы знаете где искать.
Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые. Различить и запомнить их просто:
- Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б – это данные влияющие на результат вычисления формулы).
- Зависимые – содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).
Зная какие ячейки влияют на результат и от чего он зависит, мы без труда найдем причину проблемы.
Для иллюстрации я подготовил простейшую табличку с данными. В ней есть два условных показателя и коэффициент, а итоговый расчет осуществляется простой плюсовкой обоих показателей с последующим умножение на результат: (Показатель 1 + Показатель 2) х Коэффициент.
Дополнительно я создал ещё одну простую формулу: она умножает наш “Итог” на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6.
Давайте перейдем на вкладку “Формулы” и в группе “Зависимости формул” посмотрим на два крайне полезных в работе инструмента: “Влияющие ячейки” и “Зависимые ячейки”.
Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке
Выделяю результирующую ячейку “Итог” и нажимаю кнопку “Влияющие ячейки”. Оп, и на листе MS Excel появляются синие стрелки ведущие от трех используемых в вычислениях ячеек к итоговой формуле. Согласитесь, нагляднее представить себе понятие “влияющая ячейка” невозможно.
А теперь зависимые ячейки. Весь лист теперь как на ладони
Теперь нажимаю (не убирая курсор с ячейки “итоги”) кнопку “Зависимые ячейки” и на экране появляется ещё одна стрелка. Она ведет к ячейке “результат с поправкой”, то есть той, результат вычислений в которой зависит от текущей.
Теперь я намеренно “порчу” таблицу, внося в исходные данные ошибку – подставляя букву вместо цифры. Мгновение, и я уже точно знаю откуда эта ошибка взялась. Мне даже искать ничего не пришлось – все вполне наглядно и графически красиво.
Ошибка возникшая из-за замены цифры на букву. Excel подсветил “ошибочное” вычисление красной стрелкой
Отключить графику можно в любой момент нажав на кнопку “Убрать стрелки”.
Чтобы убрать стрелки с листа MS Excel воспользуйтесь соответствующей кнопкой
Исправление ошибок возникающих в MS Excel
Впрочем, не всегда ошибка представляется нам так явно, иногда нужно ещё понять какое из промежуточных вычислений приводит к сбою. Есть в MS Excel отличный помощник и для этого.
А вот и ошибка – как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей
Одно нажатие на кнопку – один шаг в вычислениях. Уже на третьем шаге мы наглядно увидим в какой именно части формулы происходят вычисления порождающие ошибку, и теперь без труда сможем их исправить.
Вот и всё. Пользуйтесь этими несложными методами, и без труда “расщелкаете” любую возникшую при вычисления в MS Excel ошибку.
Получить первое, последнее или определенное значение читать подробную статью
- Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б – это данные влияющие на результат вычисления формулы).
- Зависимые – содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).
Если книга, на которую требуется сослаться, не открыта в Excel, ссылку на нее все же можно включить в формулу. Нужно указать полный путь к файлу, как в следующем примере: =ЧСТРОК(‘C:\Мои документы\[Кв2 Операции.xlsx]Продажи’!A1:A8). Эта формула возвращает число строк в диапазоне, включающем ячейки с A1 по A8 в другой книге (а именно, 8).
7. Общие сведения о значениях ошибок.
Для проверки формул на наличие ошибок можно применять определенные правила. Эти правила действуют как средство проверки орфографии, которое проверяет данные, введенные в ячейки, на наличие ошибок. Эти правила не гарантируют полного отсутствия ошибок на листе, но помогают избежать распространенных ошибок. Каждое из этих правил можно включать и выключать независимо от других.
Ошибки можно отмечать и исправлять двумя способами: по одной ошибке (как в средстве проверки орфографии) или немедленно по мере их возникновения на листе во время ввода данных. В обоих случаях при обнаружении ошибки в ячейке в ее левом верхнем углу появляется треугольник.
Ячейка с ошибкой в формуле
Ошибку можно устранить, используя предлагаемые MS Excel варианты, или пропустить, нажав кнопку Пропустить ошибку. Если пропустить ошибку в определенной ячейке, то при последующих проверках наличие ошибки в этой ячейке определяться не будет. Однако состояние всех пропущенных ошибок можно сбросить, чтобы они отображались снова.
На вкладке Файл выберите команду Параметры, а затем — категорию Формулы.
В области Правила контроля ошибок установите или снимите флажок для любого из перечисленных далее правил.
Ячейки, которые содержат формулы, приводящие к ошибкам. В данной формуле используется неправильный синтаксис, аргументы или типы данных. Значения таких ошибок: #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!. Каждое из этих значений ошибки вызывается различными причинами, и такие ошибки устраняются разными способами.
Примечание Если значение ошибки ввести непосредственно в ячейку, оно будет сохранено как значение ошибки, но отмечаться как ошибка не будет. Однако если на эту ячейку будет ссылаться формула из другой ячейки, для этой ячейки будет возвращено значение ошибки.
Несогласованная формула в вычисляемом столбце таблицы. Вычисляемый столбец может содержать формулы, отличающиеся от формулы столбца, что приводит к возникновению исключения. Исключения вычисляемого столбца возникают при указанных ниже действиях.
Ввод в ячейку вычисляемого столбца данных, отличных от формулы.
Ввод в ячейку вычисляемого столбца формулы с последующим нажатием кнопкиОтменитьна панели быстрого доступа.
Ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений.
Копирование в вычисляемый столбец данных, которые не соответствуют формуле вычисляемого столбца.
Примечание Если копируемые данные содержат формулу, эта формула перезапишет данные в вычисляемом столбце.
Перемещение или удаление ячейки в другой области листа, на которую ссылается одна из строк вычисляемого столбца.
Числа, отформатированные как текст или с предшествующим апострофом. Ячейка содержит числа, хранящиеся в виде текста. Обычно это является следствием импорта данных из других источников. Числа, хранящиеся в виде текста, могут стать причиной неправильной сортировки, поэтому лучше преобразовать их в числовой формат.
Формулы, не охватывающие смежные ячейки. Формула может не включать ссылки на данные, вставленные между исходным диапазоном и ячейкой с формулой. Это правило позволяет сравнить ссылку в формуле с фактическим диапазоном ячеек, смежных с ячейкой формулы. Если смежные ячейки содержат дополнительные значения и не являются пустыми, MicrosoftExcelвыведет рядом с формулой ошибку.
Например, в случае применения этого правила приложение MicrosoftExcel выведет ошибку рядом с формулой =СУММ(A2:A4), поскольку между указанным в формуле диапазоном ячеек и ячейкой с формулой (A8) находятся заполненные ячейки A5, A6 и A7, на которые также должна быть ссылка в формуле.
Формулы, которые ссылаются на пустые ячейки. Формула содержит ссылку на пустую ячейку. Это может привести к неверным результатам, как показано в приведенном далее примере.
Предположим, нужно вычислить среднее значение для чисел из указанного ниже столбца ячеек. Если третья ячейка будет пустой, она не будет учтена при выполнении вычисления, и результатом будет 22,75. Если третья ячейка будет содержать значение 0, результатом будет 18,2.
В таблицу введены недопустимые данные. В таблице произошла ошибка проверки. Чтобы просмотреть параметры проверки для ячейки, на вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.
Последовательное исправление распространенных ошибок в формулах
Внимание! Если на листе уже выполнялась проверка ошибок, то ошибки, которые были пропущены, не будут отображаться, пока их состояние не будет сброшено.
Выберите лист, на котором требуется проверить наличие ошибок.
Если расчет листа выполнен вручную, нажмите клавишу F9, чтобы выполнить расчет повторно.
На вкладке Формулы в группе Зависимости формул нажмите кнопку группы Проверка наличия ошибок.
В случае обнаружения ошибок открывается диалоговое окно Контроль ошибок.
Чтобы повторно проверить пропущенные ранее ошибки, выполните указанные ниже действия.
В разделе Контроль ошибок нажмите кнопку Сброс пропущенных ошибок.
Примечание Сброс пропущенных ошибок применяется ко всем ошибкам, которые были пропущены на всех листах активной книги.
Расположите диалоговое окно Контроль ошибок непосредственно под строкой формул .
Нажмите одну из управляющих кнопок в правой части диалогового окна. Доступные действия зависят от типа ошибки.
Примечание Если нажать кнопку Пропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.
Выполняйте эти действия, пока проверка ошибок не будет завершена.
К началу страницы
Пометка и исправление распространенных ошибок формул на листе
Нажмите кнопку Параметры и выберите категорию Формулы.
Убедитесь, что в области Контроль ошибок установлен флажокВключить фоновый поиск ошибок.
Чтобы изменить цвет треугольника, которым помечаются ошибки, выберите нужный цвет в поле Цвет индикаторов ошибок. Нажмите кнопку «ОК», чтобы закрыть диалоговое окно Параметры Excel.
Чтобы исправить ошибку на листе, выберите ячейку с треугольником в левом верхнем углу.
Нажмите появившуюся рядом с ячейкой кнопку Контроль ошибоки выберите нужный пункт. Доступные команды зависят от типа ошибки. Первый пункт содержит описание ошибки.
Если нажать кнопкуПропустить ошибку, помеченная ошибка при последующих проверках будет пропускаться.
Если формула содержит ошибку, которая не позволяет правильно выполнить вычисления, будет показано значение ошибки, например #####, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!. Каждый тип ошибки вызывается разными причинами, и такие ошибки устраняются разными способами.
В таблице ниже приведены ссылки на статьи с подробным описанием ошибок и краткий вступительный обзор каждой ошибки.
Эта ошибка появляется в том случае, если ширины столбца недостаточно, чтобы показать все знаки в ячейке, или если ячейка содержит отрицательные значения даты или времени.
Так, при вычислении формулы, которая вычитает более позднюю дату из более ранней, например =15.06.2008-01.07.2008 , получится отрицательное значение даты.
Эта ошибка появляется в том случае, если число делится на ноль (0) или на ячейку, в которой нет значения.
Эта ошибка появляется в том случае, если формула содержит ячейки с разными типами данных. Если включена проверка ошибок в формулах, при наведении указателя мыши на смарт-тег появится всплывающая подсказка «Значение, используемое в формуле, имеет неправильный тип данных». Обычно для исправления этой ошибки достаточно внести в формулу небольшие изменения.
Эта ошибка появляется в том случае, если текст в формуле не удается распознать. Например, имя диапазона или функции может иметь неверное написание.
Эта ошибка появляется в том случае, если значение для функции или формулы недоступно.
Эта ошибка появляется в том случае, если указано пересечение двух областей, которые на самом деле не пересекаются. Оператором пересечения служит пробел, который разделяет ссылки в формуле.
Например, области A1:A2 и C3:C5 не пересекаются, и если ввести формулу =СУММ(A1:A2 C3:C5) , будет показана ошибка #ПУСТО!.
Эта ошибка появляется в том случае, если ссылка на ячейку является неверной. Например, вы могли удалить ячейки, на которые ссылались другие формулы, или вставить перемещенные ячейки на место тех ячеек, на которые ссылались другие формулы.
Эта ошибка появляется в том случае, если формула или функция содержит недопустимые числовые значения.
Формулы представляют собой выражения, по которым выполняются вычисления значений на листе. Формула начинается со знака равенства (=).Формула может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.
Функции. Функция ПИ() возвращает значение числа Пи: 3,142.
Ссылки. A2 возвращает значение ячейки A2.
Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
Операторы. Оператор ^ («крышка») возводит число в степень, а оператор * («звездочка») перемножает два или более числа.
Исправление распространенных ошибок во время ввода формул
В приведенной ниже таблице перечислены некоторые наиболее распространенные ошибки, совершаемые пользователями при вводе формул, и способы их исправления.
Если знак равенства опустить, введенные значения могут быть отображены как текст или дата. Например, если ввести СУММ(A1:A10), MicrosoftExcel отобразит текстовую строку СУММ(A1:A10) и не станет вычислять значение формулы. Если ввести 11/2, Excel отобразит дату (например, «2 ноября» или «02.11.2009») вместо того, чтобы разделить 11 на 2.
Все открывающие и закрывающие скобки согласованы
Убедитесь, что у каждой скобки имеется соответствующая ей пара. Чтобы функция в формуле работала правильно, важно, чтобы каждая скобка стояла на своем месте. Например, формула =ЕСЛИ(B5 <0);"Неверно";B5*1,05)работать не будет, потому что в ней две закрывающие и только одна открывающая скобка. Правильная формула будет выглядеть так: =ЕСЛИ(B5<0;"Неверно";B5*1,05).
При указании ссылки на диапазон ячеек используйте двоеточие (:) в качестве разделителя между первой и последней ячейками диапазона. Например: A1:A5.
Для некоторый функций листа, например СУММ, требуются числовые аргументы. Другие функции, напримерЗАМЕНИТЬ, требуют текстового значения по меньшей мере для одного из аргументов. Прииспользования в качестве аргумента данных неверного типа можно получить неправильные результаты или ошибку.
Имена других листов заключены в одинарные кавычки
Если в формуле имеются ссылки на значения или ячейки других листов или книг, имена которых содержат небуквенные знаки, такие имена необходимо заключить в одинарные кавычки ( ‘ ).
Ссылаясь на лист в формуле, следует поместить после его имени восклицательный знак (!).
Например, чтобы вернуть значение ячейки D3 листа с именем «Данные за квартал» в той же книге, воспользуйтесь формулой =’Данные за квартал’!D3.
Убедитесь, что каждая внешняя ссылка (Внешняя ссылка. Ссылка на ячейку или диапазон ячеек в другой книге Microsoft Excel или ссылка на имя, определенное в другой книге.) содержит имя книги и путь к ней.
Ссылка на книгу включает в себя имя этой книги и должна быть заключена в квадратные скобки ([]). Кроме того, в ссылке должно быть указано имя листа в книге.
Например, формула со ссылкой на ячейки с A1 по A8 на листе с именем «Продажи» в книге (в настоящий момент открытой в Excel) с именем Кв2 Операции.xlsx, будет выглядеть примерно так: =[Кв2 Операции.xlsx]Продажи!A1:A8.
Если книга, на которую требуется сослаться, не открыта в Excel, ссылку на нее все же можно включить в формулу. Нужно указать полный путь к файлу, как в следующем примере: =ЧСТРОК(‘C:\Мои документы\[Кв2 Операции.xlsx]Продажи’!A1:A8). Эта формула возвращает число строк в диапазоне, включающем ячейки с A1 по A8 в другой книге (а именно, 8).
Примечание Если полный путь содержит пробелы, как в предыдущем примере, его необходимо заключить в одинарные кавычки (в начале пути и после имени листа, перед восклицательным знаком).
Не форматируйте числа, которые вводите в формулах. Например, если вам нужно ввести значение $1,000, введите в формулу просто 1000. Если ввести запятую как часть числа, Excel будет трактовать ее как разделительный знак. Если требуется, чтобы числа отображались с разделителями тысяч и миллионов или с символами валюты, отформатируйте ячейки после ввода чисел.
Например, если нужно добавить 3100 к значению в ячейке A3, и вы ввели формулу =СУММ(3,100;A3), приложение Excel сложит числа 3 и 100, а затем добавит результат к значению ячейки A3, вместо того чтобы добавить 3100 к значению A3. А если введена формула =ABS(-2,134), будет показана ошибка, поскольку функция ABS принимает только один аргумент.
При попытке разделить значение в ячейке на значение в другой ячейке, содержащей ноль или не содержащей никакого значения, будет показана ошибка #ДЕЛ/0!.
Вычисления в Excel выполняются с помощью Формул. Формула начинается со знака равно (=) и состоит из элементов (операндов: константы, ссылки на ячейки или диапазоны ячеек, функции), соединенных операторами (знаки операций).
Оператор. Знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок
Функция ЕСЛИ в Excel с примерами нескольких условий. Функция ЕСЛИ (IF) в Excel
Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И».
