Сообщение об ошибке в Excel: «при сохранении были обнаружены ошибки. «
Рассмотрим следующий сценарий. вы работаете над проектом по автоматизации Microsoft Office. По этой причине вы открыли редактор Microsoft Visual Basic для приложений. Также открыт лист Excel, в котором содержится ссылка на данные Excel (см. Ссылки на данные Excel).
Вы удаляете лист Excel со ссылкой на данные Excel в think-cell. В Excel может появится сообщение об ошибке: «Требуется объект».
При попытке сохранить файл Excel, будет выдано сообщение об ошибке:
«Обнаружены ошибки при сохранении [имя файла]. Microsoft Excel может сохранить файл, удалив или исправив некоторые функции. Чтобы провести восстановление в новом файле, нажмите на кнопку «Далее». Для отмены сохранения файла нажмите на кнопку «Отмена»..»
При нажатии кнопки Продолжить , процесс восстановления завершается ошибкой.
Excel 2007 или 2010: если вы пытаетесь вставить новый лист Excel вместо сохранения файла, программа аварийно завершается.
Причина
Проблема обусловлена программной ошибкой в Excel. Мы отправили отчет о данной ошибке компании Microsoft. Если у вашей компании есть договор на поддержку Microsoft Office и вы хотите обратиться в Microsoft за исправлением, вы можете указать номер обращения Microsoft 114072911657591. Ошибку можно воспроизвести и без надстройки think-cell.
Воспроизведение ошибки без think-cell
Выполните следующие действия.
- Запустите Excel и создайте новый пустой файл.
- Введите 100 в ячейках A1 и A2 первого листа, Лист1 .
- Выберите ячейки A1:A2 в Лист1 и нажмите Ctrl + C , чтобы скопировать значения.
- (Только при попытке воспроизведения ошибки в Excel 2013 или более поздней версии: вставьте второй лист. В более предыдущих версиях Excel три листа по умолчанию, поэтому это не требуется.)
- Нажмите Alt + F11 , чтобы открыть окно редактора Microsoft Visual Basic для приложений и оставьте его открытым.
- Запустите Word и создайте новый пустой файл.
- Выберите в меню Word пункт Главная → Вставить → Специальная вставка → Вставить ссылку: объект листа Microsoft Excel и нажмите кнопку OK .
- В Excel нажмите правой кнопкой мыши на вкладку Лист1 и выберите команду Удалить .
- Попробуйте сохранить файл Excel. В Excel появляется сообщение об ошибке:
«Обнаружены ошибки при сохранении [имя файла]. Microsoft Excel может сохранить файл, удалив или исправив некоторые функции. Чтобы провести восстановление в новом файле, нажмите на кнопку «Далее». Для отмены сохранения файла нажмите на кнопку «Отмена»..» При нажатии кнопки Продолжить , процесс восстановления завершается ошибкой. Excel 2007 или 2010: если вы пытаетесь вставить новый лист Excel вместо сохранения файла, программа аварийно завершается.
Решение
При возникновении такой ошибки вы можете сохранить данные в новый документ Excel или сбросить изменения и восстановить исходный документ:
Сохранение данных в новом документе Excel
Для сохранения изменений, внесенных в документ, скопируйте измененные листы в новый документ Excel и сохраните последний.
Восстановление документа Excel, чтобы избежать повторения этой ошибки в будущем
Вы можете восстановить документ Excel, удалив все проекты Visual Basic для приложений (VBA) из своего документа. Однако, лишь удаления проектов в редакторе Visual Basic будет недостаточно. Выполните следующие действия:
- Создайте резервную копию документа.
- Загрузите и запустите бесплатный инструмент Structured Storage Viewer.
- Откройте документ в Structured Storage Viewer.
- В представлении документа в виде дерева выберите _VBA_PROJECT_CUR и нажмите кнопку Del , чтобы удалить этот элемент.
- Подтвердите удаление элемента и закройте Structured Storage Viewer.
- Откройте документ в Excel.
- Откроется диалоговое окно Microsoft Visual Basic → Файл не найден . Нажмите кнопку OK .
- Будет показано другое диалоговое окно Восстановление , где будет указан Потерянный проект Visual Basic . Нажмите кнопку Закрыть .
- Сохраните документ.
Теперь вы можете удалить лист с источниками данных для think-cell и затем сохранить документ.
Поиск ошибок в формулах
Кроме неожиданных результатов, формулы иногда возвращают значения ошибок. Ниже представлены некоторые инструменты, с помощью которых вы можете искать и исследовать причины этих ошибок и определять решения.
Примечание: В статье также приводятся методы, которые помогут вам исправлять ошибки в формулах. Это не исчерпывающий список методов для исправления каждой возможной ошибки формулы. Для получения справки по конкретным ошибкам поищите ответ на свой вопрос или задайте его на форуме сообщества Microsoft Excel.
Ввод простой формулы
Формулы — это выражения, с помощью которых выполняются вычисления со значениями на листе. Формула начинается со знака равенства (=). Например, следующая формула складывает числа 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.
Следите за соответствием открывающих и закрывающих скобок
Для указания диапазона используйте двоеточие
Указывая диапазон ячеек, разделяйте с помощью двоеточия (:) ссылку на первую ячейку в диапазоне и ссылку на последнюю ячейку в диапазоне. Например, =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
-
Ячейки, содержащие формулы, которые приводят к ошибке. Формула не использует ожидаемый синтаксис, аргументы или типы данных. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!и #VALUE!. Каждое из этих значений ошибок имеет разные причины и разрешается по-разному.
Примечание: Если ввести значение ошибки непосредственно в ячейку, оно сохраняется как это значение ошибки, но не помечается как ошибка. Но если на эту ячейку ссылается формула из другой ячейки, эта формула возвращает значение ошибки из ячейки.
- Ввод данных, не являющихся формулой, в ячейку вычисляемого столбца.
- Введите формулу в ячейку вычисляемого столбца, а затем нажмите клавиши CTRL+Z или выберите Отменить
на панели быстрого доступа.
Если ссылки, используемые в формуле, не соответствуют ссылкам в смежных формулах, Excel отображает ошибку.
Формулы, опускающие ячейки в области. Формула не может автоматически включать ссылки на данные, которые вы вставляете между исходным диапазоном данных и ячейкой, содержащей формулу. Это правило позволяет сравнить ссылку в формуле с фактическим диапазоном ячеек, смежных с ячейкой, содержащей формулу. Если смежные ячейки содержат дополнительные значения и не являются пустыми, Excel отображает рядом с формулой ошибку. Например, excel вставляет ошибку рядом с формулой =СУММ(D2:D4) при применении этого правила, так как ячейки D5, D6 и D7 находятся рядом с ячейками, на которые ссылается формула, и ячейкой, содержащей формулу (D8), а эти ячейки содержат данные, на которые следует ссылаться в формуле.
Последовательное исправление распространенных ошибок в формулах
- Выберите лист, на котором требуется проверить наличие ошибок.
- Если расчет листа выполнен вручную, нажмите клавишу F9, чтобы выполнить расчет повторно. Если диалоговое окно Проверка ошибок не отображается, выберите Формулы >аудит формул >проверка ошибок.
- Если вы ранее игнорировали какие-либо ошибки, вы можете снова проверка их, выполнив следующие действия: перейдите в раздел Параметры >файлов >Формулы. Для Excel на Mac выберите меню Excel > Параметры > проверки ошибок. В разделе Проверка ошибок выберите Сброс пропущенных ошибок >ОК.
Примечание: Сброс пропущенных ошибок применяется ко всем ошибкам, которые были пропущены на всех листах активной книги.
Совет: Советуем расположить диалоговое окно Поиск ошибок непосредственно под строкой формул.
Примечание: Если выбран параметр Игнорировать ошибку, ошибка помечается как игнорируемая для каждой последовательной проверка.
Исправление распространенных ошибок по одной
- Рядом с ячейкой выберите Проверка ошибок
, а затем выберите нужный параметр. Доступные команды различаются для каждого типа ошибки, и первая запись описывает ошибку. Если выбран параметр Игнорировать ошибку, ошибка помечается как игнорируемая для каждой последовательной проверка.
Исправление ошибки с #
Если формула не может правильно вычислить результат, в Excel отображается значение ошибки, например #####, #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА!, #ЗНАЧ!. Ошибки разного типа имеют разные причины и разные способы решения.
Приведенная ниже таблица содержит ссылки на статьи, в которых подробно описаны эти ошибки, и краткое описание.
Эта ошибка отображается в Excel, если столбец недостаточно широк, чтобы показать все символы в ячейке, или ячейка содержит отрицательное значение даты или времени.
Например, результатом формулы, вычитающей дату в будущем из даты в прошлом (=15.06.2008-01.07.2008), является отрицательное значение даты.
Совет: Попробуйте автоматически изменить ширину ячейки, дважды щелкнув между заголовками столбцов. Если ### отображается, так как Excel не может отобразить все символы, это исправит его.
Эта ошибка отображается в 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 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.
Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(D2:D5) возвращает результат 40.
Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент лог_выражение) имеет значение ЛОЖЬ.
Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только тогда, когда выражение имеет значение ИСТИНА.
- Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
- Перейдите к разделу >аудит формул >вычисление формулы.
- Нажмите Вычислить, чтобы проверить значение подчеркнутой ссылки. Результат вычисления отображается курсивом. Если подчеркнутая часть формулы является ссылкой на другую формулу, выберите Шаг В , чтобы отобразить другую формулу в поле Оценка . Нажмите Шаг с выходом, чтобы вернуться к предыдущей ячейке и формуле. Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.
- Продолжайте выбирать Вычислять , пока не будет выполнена оценка каждой части формулы.
- Чтобы снова просмотреть оценку, выберите Перезапустить.
- Чтобы завершить оценку, нажмите кнопку Закрыть.
- Некоторые части формул, использующие функции IF и CHOOSE , не вычисляются. В этих случаях #N/A отображается в поле Оценка .
- Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
- Некоторые функции вычисляются заново при каждом изменении листа, так что результаты в диалоговом окне Вычисление формулы могут отличаться от тех, которые отображаются в ячейке. Это функции СЛЧИС, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, ЧСТРОК, ЧИСЛСТОЛБ, ТДАТА, СЕГОДНЯ, СЛУЧМЕЖДУ.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Исправление ошибки #ЗНАЧ! ошибка
Ошибка #ЗНАЧ! в Excel означает: «Формула вводится неправильно. Или что-то не так с ячейками, на которые указывают ссылки». Эта ошибка возникает в самых разных случаях, и найти ее точную причину может быть сложно. Сведения на этой странице включают распространенные проблемы и решения ошибки.
Используйте раскрывающийся список ниже или перейдите к одной из других областей:
- Проблемы с вычитанием
- Проблемы с пробелами и текстом
- Другие решения
Исправление ошибок определенных функций
Какую функцию вы используете?
Ваша функция не отображается в этом списке? Попробуйте другие решения, перечисленные ниже.
Проблемы с вычитанием
Выполнение базового вычитания
Если вы раньше не работали в Excel, вероятно, вы неправильно вводите формулу вычитания. Это можно сделать двумя способами:
Вычтите одну ссылку на ячейку из другой
Введите два значения в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D2 содержит плановую сумму, а ячейка E2 — фактическую. F2 содержит формулу =D2-E2.
Или используйте функцию СУММ с положительными и отрицательными числами
Введите положительное значение в одной ячейке и отрицательное — в другой. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки. В этом примере ячейка D6 содержит плановую сумму, а ячейка E6 — фактическую как негативное число. F6 содержит формулу =СУММ(D6;E6).
Ошибка #ЗНАЧ! при базовом вычитании
Если используется Windows, ошибка #ЗНАЧ! может возникнуть даже при вводе самой обычной формулы вычитания. Проблему можно решить следующим образом.
- Для начала выполните быструю проверку. В новой книге введите 2 в ячейке A1. Введите 4 в ячейке B1. Затем введите формулу =B1-A1 в ячейке C1. Если возникнет ошибка #ЗНАЧ! перейдите к следующему шагу. Если сообщение об ошибке не появилось, попробуйте другие решения на этой странице.
- В Windows откройте панель управления «Региональные стандарты».
- Windows 10: нажмите кнопку Пуск, введите Регион, а затем выберите панель управления Регион.
- Windows 8. На начальном экране введите Регион, выберите Параметры, а затем — Регион.
- Windows 7: выберите Пуск, введите Регион, а затем выберите Регион и язык.
- На вкладке Форматы выберите Дополнительные параметры.
- Найдите пункт Разделитель элементов списка. Если в поле разделителя элементов списка указан знак «минус», замените его на что-то другое. Например, разделителем нередко выступает запятая. Также часто используется точка с запятой. Однако для вашего конкретного региона может подходить другой разделитель элементов списка.
- Нажмите кнопку ОК.
- Откройте книгу. Если ячейка содержит ошибку #VALUE!, дважды щелкните ее для редактирования.
- Если там, где для вычитания должны быть знаки «минус», стоят запятые, замените их на знаки «минус».
- Нажмите клавишу ВВОД.
- Повторите эти действия для других ячеек, в которых возникает ошибка.
Вычитание дат
Вычтите одну ссылку на ячейку из другой
Введите две даты в двух отдельных ячейках. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере ячейка D10 содержит дату начала, а ячейка E10 — дату окончания. F10 содержит формулу =E10-D10.
Или используйте функцию РАЗНДАТ
Введите две даты в двух отдельных ячейках. В третьей ячейке используйте функцию РАЗНДАТ, чтобы найти разницу дат. Дополнительные сведения о функции РАЗНДАТ см. в статье Вычисление разницы двух дат.
Ошибка #ЗНАЧ! при вычитании дат в текстовом формате
Растяните столбец по ширине. Если значение выравнивается по правому краю — это дата. Но если оно выравнивается по левому краю, это значит, что в ячейке на самом деле не дата. Это текст. И Excel не распознает текст как дату. Ниже приведены некоторые решения, которые помогут решить эту проблему.
Проверка наличия начальных пробелов
- Дважды щелкните дату, которая используется в формуле вычитания.
- Разместите курсор в начале и посмотрите, можно ли выбрать один или несколько пробелов. Вот как выглядит выбранный пробел в начале ячейки:
Проверка параметров даты на компьютере
Excel полагается на систему дат вашего компьютера. Если дата ячейки не введена в той же системе дат, Excel не распознает ее как дату true.
Например, предположим, что компьютер отображает даты в формате мм.дд.гггг. Если ввести такую дату в ячейке, Excel распознает ее как дату, а вы сможете использовать ее в формуле вычитания. Однако если вы ввели такую дату, как дд/мм/гг, Excel не распознает ее как дату. Вместо этого дата будет рассматриваться как текст.
Существует два решения этой проблемы: Вы можете изменить систему дат, которая используется на компьютере, чтобы она соответствовала системе дат, которая нужна в Excel. Или в Excel можно создать новый столбец и использовать функцию ДАТА, чтобы создать настоящую дату на основе даты в текстовом формате. Вот как это сделать, если система дат вашего компьютера — дд.мм.гггг, а в ячейке A1 записан текст 12/31/2017.
- Создайте такую формулу: =ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))
- Результат будет 31.12.2017.
- Чтобы использовать формат дд.мм.гг, нажмите клавиши CTRL+1 (или
Примечание: Формула выше написана с использованием функций ДАТА, ПРАВСИМВ, ПСТР и ЛЕВСИМВ. Обратите внимание, что она написана с предположением, что текстовая дата содержит два символа для дней, два символа для месяцев и четыре символа года. Возможно, вам потребуется настроить формулу в соответствии с датой.
Как найти ошибку в таблице Excel по формуле
Чтобы сэкономить время на визуальный анализ больших таблиц с целью выявления ошибок, рационально применить формулы для определения их местонахождения. Например, будет весьма полезной информация о локализации первой возникшей ошибки относительно строк и столбцов листа.
Поиск ошибок в Excel формулой
Чтобы определить местонахождение ошибки в таблице с большим количеством строк и столбцов рекомендуем воспользоваться специальной формулой. Для примера покажем формулу, которая умеет легко работать с большими диапазонами ячеек, в пределах A1:Z100.
Для определения локализации первой ошибки на листе относительно строк следует использовать следующую формулу:
Данная формула должна выполняться в массиве, поэтом после ее ввода для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки, как на рисунке.
Таблица с большим объемом данных содержит ошибки, первая из которых находится в диапазоне третей строки листа 3:3.
Как получить адрес ячейки с ошибкой
Опираясь на результат вычисления этой формулы можно составить другую формулу, которая уже не просто определить строку или столбец, а укажет непосредственный адрес ошибки на листе Excel. Для решения данной задачи ниже (в ячейку AB3) введите другую формулу:
Данная формула так же должна выполняться в массиве, поэтом после ее ввода снова для подтверждения жмем комбинацию клавиш CTRL+SHIFT+Enter.
Результат вычисления локального адреса ячейки, которая содержит первую ошибку в таблице:
Принцип действия формулы для поиска ошибок:
В первом аргументе функции АДРЕС указываем номер строки, который должен быть возвращен в адресе ячейки содержащей результат действия целой формулы. Номер строки определен предыдущей формулой и является числом 3. Поэтому мы только ссылаемся на ячейку AB2 с первой формулой. Далее с помощью функции ДВССЫЛ определяется ссылка на диапазон, который должен быть найден в соответствии с местом нахождения ошибок. Нет необходимости выполнять поиск по целой таблице нагружая таким образом процессор компьютера излишне отнимая вычислительные ресурсы программы Excel. Нас интересует только третья строка.
С помощью функции ЕОШИБКА проверяется каждая ячейка в диапазоне A3:Z3 на наличие ошибок. На основании полученных результатов в памяти программы создается массив логических значений ИСТИНА и ЛОЖЬ. Следующая функция СТОЛБЕЦ возвращает в память программы второй массив из номеров столбцов с количеством элементов соответствующему количеству столбцов в диапазоне A3:Z3.
Благодаря функции ЕСЛИ в первом массиве логическое значение ИСТИНА заменяется на соответственное числовое значение из второго массива. После чего функция МИН выбирает наименьшее числовое значение первого массива, которое соответствует номеру столбца содержащего первую ошибку. Так как били вычислены номер строки и столбца завершается вычисление формулы функцией АДРЕС. Она уже возвращает текстовым значением готовый адрес ячейки на основе номера столбца и строки указанных в ее аргументах.
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры