Программа ms excel формулы компоненты формул диагностика ошибок - Решение и исправление самых разных ошибок на TopOshibok.ru

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).

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

Рекомендация	Дополнительные сведения
Начинайте каждую формулу со знака равенства (=)	Если опустить знак равенства, введенные данные могут отображаться в виде текста или даты. Например, если ввести SUM(A1:A10), Excel отображает текстовую строку SUM(A1:A10) и не выполняет вычисление. Если ввести 11/2, вместо деления 11 на 2 Excel отображается дата 2–ноябрь (при условии, что ячейка имеет формат «Общий«) вместо деления 11 на 2.
Следите за соответствием открывающих и закрывающих скобок	Все скобки должны быть парными (открывающая и закрывающая). При использовании функции в формуле важно, чтобы каждая скобка была в правильном положении, чтобы функция работала правильно. Например, формула *=ЕСЛИ(B5<0);»Недопустимо»;B51,05) не будет работать, поскольку в ней две закрывающие скобки и только одна открывающая (требуется одна открывающая и одна закрывающая). Правильный вариант этой формулы выглядит так: =ЕСЛИ(B5<0;»Недопустимо»;B51,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 добавит числа 3 и 100, а затем добавит их итог к значению из A3, а не 3100 к A3, что будет =СУММ(3100,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).
  
  Если ссылки, используемые в формуле, не соответствуют ссылкам в смежных формулах, Excel отображает ошибку.
- Формулы, опускающие ячейки в области. Формула не может автоматически включать ссылки на данные, которые вы вставляете между исходным диапазоном данных и ячейкой, содержащей формулу. Это правило позволяет сравнить ссылку в формуле с фактическим диапазоном ячеек, смежных с ячейкой, содержащей формулу. Если смежные ячейки содержат дополнительные значения и не являются пустыми, Excel отображает рядом с формулой ошибку.
  
  Например, excel вставляет ошибку рядом с формулой =СУММ(D2:D4) при применении этого правила, так как ячейки D5, D6 и D7 находятся рядом с ячейками, на которые ссылается формула, и ячейкой, содержащей формулу (D8), а эти ячейки содержат данные, на которые следует ссылаться в формуле.
- Незаблокированные ячейки, содержащие формулы. Формула не заблокирована для защиты. По умолчанию все ячейки на листе блокируются, поэтому их нельзя изменить при защите листа. Это поможет избежать случайных ошибок, таких как случайное удаление или изменение формул. Эта ошибка указывает, что ячейка была разблокирована, но лист не был защищен. Убедитесь, что ячейка не заблокирована.
- Формулы, ссылающиеся на пустые ячейки. Формула содержит ссылку на пустую ячейку. Это может привести к неверным результатам, как показано в приведенном далее примере.
  
  Предположим, требуется найти среднее значение чисел в приведенном ниже столбце ячеек. Если третья ячейка пуста, она не включается в вычисление и результат равен 22,75. Если эта ячейка содержит значение 0, результат будет равен 18,2.
- Данные, введенные в таблицу, недопустимы. В таблице возникает ошибка проверки. Проверьте параметр проверки ячейки, перейдя на вкладку Данные > группу Data Tools > Проверка данных.

Выберите лист, на котором требуется проверить наличие ошибок.
Если расчет листа выполнен вручную, нажмите клавишу 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 не распознает текст в формуле. Например, имя диапазона или функция могут быть написаны неправильно. Примечание: Если вы используете функцию, убедитесь, что ее имя написано неправильно. В данном случае слово СУММ введено с ошибкой. Удалите «e», и 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) поможет устранить проблему.

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

Эту панель инструментов можно перемещать и закреплять, как и любую другую. Например, можно закрепить ее в нижней части окна. На панели инструментов выводятся следующие свойства ячейки: 1) книга, 2) лист, 3) имя (если ячейка входит в именованный диапазон), 4) адрес ячейки 5) значение и 6) формула.

Примечание: Для каждой ячейки может быть только одно контрольное значение.

Добавление ячеек в окно контрольного значения

Выделите ячейки, которые хотите просмотреть.

Чтобы выделить все ячейки на листе с формулами, перейдите на страницу Главная > Редактирование > выберите Найти & Выбрать (или можно использовать клавиши CTRL+G или CONTROL+G на компьютере Mac)> Перейти к специальным > формулам.
Перейдите в раздел «Формулы » > аудит формул > выберите Контрольное окно.
Выберите Добавить контрольные значения.
Убедитесь, что выбраны все ячейки, которые нужно watch, и нажмите кнопку Добавить.
Чтобы изменить ширину столбца, перетащите правую границу его заголовка.
Чтобы открыть ячейку, ссылка на которую содержится в записи панели инструментов «Окно контрольного значения», дважды щелкните запись.

Примечание: Ячейки, содержащие внешние ссылки на другие книги, отображаются на панели инструментов «Окно контрольного значения» только в случае, если эти книги открыты.

Удаление ячеек из окна контрольного значения

Если панель инструментов Контрольного окна не отображается, перейдите в раздел Формулы > аудит формул > выберите Контрольное окно.
Выделите ячейки, которые нужно удалить.

Чтобы выделить несколько ячеек, нажмите клавишу CTRL, а затем выделите ячейки.
Выберите Удалить контрольные значения.

Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =IF(AVERAGE(D2:D5)>50,SUM(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, выражение в первом аргументе функции ЕСЛИ (аргумент лог_выражение) имеет значение ЛОЖЬ. Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только тогда, когда выражение имеет значение ИСТИНА.

Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
Перейдите к разделу >аудит формул > вычисление формулы.
Нажмите Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычисления отображается курсивом.

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

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

Примечания:

Некоторые части формул, использующие функции IF и CHOOSE , не вычисляются. В этих случаях #N/A отображается в поле Оценка .
Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
Некоторые функции вычисляются заново при каждом изменении листа, так что результаты в диалоговом окне Вычисление формулы могут отличаться от тех, которые отображаются в ячейке. Это функции СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ, СЛУЧМЕЖДУ.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

См. также

Отображение связей между формулами и ячейками

Рекомендации, позволяющие избежать появления неработающих формул

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Источник

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

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

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

Десять последних функций, применяемых пользователем, Excel автоматически группирует в категорию «10 недавно использовавшихся».

Если Excel не может выполнить обработку формулы в ячейке и вывести результат, то он генерирует сообщение об ошибке и выводит его в данной ячейке (вместо самой формулы или ее результата). Сообщение об ошибке всегда начинается со знака «#».

Сообщения об ошибках в Excel могут принадлежать к одному из 8 типов:

•######

•#ЗНАЧ!

•#ДЕЛ/0!

•#ИМЯ?

•#Н/Д

•#ССЫЛКА!

•#ЧИСЛО!

•#ПУСТО!

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

Ошибка #ЗНАЧ! возникает, когда используется недопустимый тип аргумента, например, пользователь пытается сложить текстовое и числовое значение.

Ошибка #ДЕЛ/0 появляется, когда в формуле делается попытка деления на ноль.

Сообщение об ошибке типа #ИМЯ? появляется, когда Excel не может найти имя, используемое в формуле. Например, такая ситуация возникнет, если:

o при наборе имени произошла опечатка;

o текст ошибочно не был заключен в двойные кавычки;

o в ссылке на диапазон ячеек пропущен знак двоеточия (:). Ошибка #Н/Д является сокращением термина «Неопределенные Данные».

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

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

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

Ввод и обработка данных в Excel

Значительная часть работы в Excel приходится на ввод данных, их редактирование и обработку.

Рабочий лист в Excel 2007 состоит из 13384 столбцов и 1048576 строк.

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

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

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

Форматирование и защита рабочих листов

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

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

Форматирование в Excel включает в себя решение следующих вопросов:

• изменение шрифта, размеров, начертания и цвета символов;

•выравнивание и изменение ориентации текста и чисел в ячейках;

•форматирование чисел, дат и времени;

•форматирование строк и столбцов;

•создание и использование пользовательских форматов;

•условное форматирование;

•защита ячеек, листов и рабочих книг;

•использование стилей при форматировании;

•применение автоформатов.

Работа с электронными таблицами

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

Рабочий лист электронной книги состоит из ячеек, каждая из которых имеет свой адрес: сочетание имени столбца и строки. Столбцы идентифицируются буквами латинского алфавита (А, B, C, D…), а строки

– арабскими цифрами (1,2,3…). Ячейка, в которой находится курсор, считается активной, то есть предназначенной для ввода данных. Например, адрес F10 говорит о том, что вводимая информация при активной ячейке F10 попадет именно в эту ячейку, а содержимое этой ячейки отразится в строке формул.

Многие команды Excel позволяют работать с блоками ячеек. Блок ячеек — это прямоугольник, задаваемый координатами противоположных углов, обычно, верхней левой и нижней правой ячеек. Имена ячеек в блоках разделяются двоеточием (:). Например, блок А1:В4 включает в себя ячейки А1, А2, А3, А4, В1, В2, В3 и В4. Выделение блока ячеек осуществляется протаскиванием курсора мыши на нужный диапазон. При этом ячейка, начиная с которой выделяется блок, остается белого цвета, а остальные ячейки затемняются. Но блок ячеек не обязательно может включать смежные ячейки. Для выделения таких ячеек в блоке следует использовать клавишу <Ctrl>.

Ячейкам и блокам для удобства работы можно давать имена.

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

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

Источник

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

В результате вычисления в Excel возможно получить следующие ошибки:

Вид ошибки	Описание ошибки
#####	Ширина колонки слишком маленькая, чтобы отобразить число в ячейке;
#ИМЯ?	Формула содержит текст, который Excel не распознал;
#ЗНАЧ!	Формула содержит неверно заданный аргумент (например, суммируются числовая и текстовая ячейки);
#ССЫЛКА!	Формула отсылает к несуществующей ячейке (например, если столбец был удален);
#ДЕЛ/0!	В формулу заложено деление на ноль.

Влияющие и зависимые ячейки

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

При этом информация о том, какие ячейки участвуют в формировании формулы, будет отображена стрелками.

Влияющие ячейки в Excel

Расположенная рядом кнопка Зависимые ячейки покажет, на какие ячейки влияет выделенная ячейка.

Зависимые ячейки в Excel

Проверка ошибок

Кнопка Проверка ошибок открывает диалоговое окно, содержащее подробную информацию об ошибках.

Проверка ошибок в Excel

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

Функция ЕСЛИОШИБКА

Функция ЕСЛИОШИБКА позволяет при получении в результате вычисления ошибки вывести вместо нее произвольное значение. Если ошибка не возникает, выводится результат вычисления.

Формула: =еслиошибка(значение;значение_если_ошибка)

Расписание ближайших групп:

Загружаю…

Источник

В случае неверного написания Excel поможет исправить формулы при помощи проверки наличия ошибок.

В окне открытого листа перейдите к вкладке «Формулы» и в группе «Зависимости формул» раскройте меню кнопки «Проверка наличия ошибок».
В списке команд выберите пункт «Проверка наличия ошибок».
окне «Контроль ошибок» отобразится первая формула листа, содержащая ошибку (рис. 4.24).

Рис. 4.24. Окно «Контроль ошибок»

Чтобы просмотреть данные по найденной ошибке, щелкните по кнопке «Справка по этой ошибке» и в окне «Справка Excel» просмотрите нужную информацию.

[stextbox id=»info»]Справка по Excel 2007 доступна только при подключении к интернету.[/stextbox]

Чтобы вручную исправить формулу, в окне «Контроль ошибок» нажмите кнопку «Изменить в строке формул», а затем в окне ввода на строке формул внесите нужные исправления в формулу.

[stextbox id=»info»]После необходимых исправлений нажмите кнопку «Продолжить» в окне «Контроль ошибок».[/stextbox]

Чтобы игнорировать ошибочную формулу, щелкните по кнопке «Пропустить ошибку». При этом она будет в дальнейшем пропускаться при каждой проверке.
Чтобы просмотреть вычисление ошибочной формулы подробнее, нажмите кнопку «Показать этапы вычисления» и в окне «Вычисление формулы» (рис. 4.25) поэтапно проверьте ошибки в формуле.

Рис. 4.25. Окно «Вычисление формулы»

[stextbox id=»info»]Для возвращения в окно «Контроль ошибок» щелкните по кнопке «Закрыть».[/stextbox]

Чтобы продолжить просмотр ошибочных формул листа, щелкните по кнопке «Далее в окне “Контроль ошибок”».
После проверки последней формулы откроется окошко с сообщением «Проверка ошибок завершена для всего листа» (рис. 4.26), в котором нажмите кнопку «ОК».

Рис. 4.26. Информационное окошко после проверки формул на ошибки

Источник

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

К счастью, в наших руках несколько отличных инструментов для поиска «хитрых» ошибок в формулах MS Excel.

Влияющие и зависимые ячейки в MS Excel

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

Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые. Различить и запомнить их просто:

Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б — это данные влияющие на результат вычисления формулы).
Зависимые — содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).

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

Для иллюстрации я подготовил простейшую табличку с данными. В ней есть два условных показателя и коэффициент, а итоговый расчет осуществляется простой плюсовкой обоих показателей с последующим умножение на результат: (Показатель 1 + Показатель 2) х Коэффициент.

Дополнительно я создал ещё одну простую формулу: она умножает наш «Итог» на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6.

Давайте перейдем на вкладку «Формулы» и в группе «Зависимости формул» посмотрим на два крайне полезных в работе инструмента: «Влияющие ячейки» и «Зависимые ячейки».

Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке

Выделяю результирующую ячейку «Итог» и нажимаю кнопку «Влияющие ячейки». Оп, и на листе MS Excel появляются синие стрелки ведущие от трех используемых в вычислениях ячеек к итоговой формуле. Согласитесь, нагляднее представить себе понятие «влияющая ячейка» невозможно.

А теперь зависимые ячейки. Весь лист теперь как на ладони

Теперь нажимаю (не убирая курсор с ячейки «итоги») кнопку «Зависимые ячейки» и на экране появляется ещё одна стрелка. Она ведет к ячейке «результат с поправкой», то есть той, результат вычислений в которой зависит от текущей.

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

Ошибка возникшая из-за замены цифры на букву. Excel подсветил «ошибочное» вычисление красной стрелкой

Отключить графику можно в любой момент нажав на кнопку «Убрать стрелки».

Чтобы убрать стрелки с листа MS Excel воспользуйтесь соответствующей кнопкой

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

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

Ищем ошибку в формуле Excel

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

А вот и ошибка — как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей

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

Вот и всё. Пользуйтесь этими несложными методами, и без труда «расщелкаете» любую возникшую при вычисления в MS Excel ошибку.

Источник