При расчете источника возникает ошибка в excel dwlink

Обновлено: 21.11.2024

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 Еще. Меньше

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

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

Узнайте, как вводить простую формулу

Формулы – это уравнения, которые выполняют вычисления со значениями на листе. Формула начинается со знака равенства (=). Например, следующая формула добавляет 3 к 1.

Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.

Части формулы

Функции: функции, включенные в Excel, представляют собой разработанные формулы, которые выполняют определенные вычисления. Например, функция PI() возвращает значение числа пи: 3,142.

Ссылки: ссылки на отдельные ячейки или диапазоны ячеек. A2 возвращает значение в ячейке A2.

Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2.

Операторы. Оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает. Используйте + и – для сложения и вычитания значений и / для деления.

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

Например, для функции SUM требуется только один аргумент, но общее количество аргументов может достигать 255.

=SUM(A1:A10) – это пример одного аргумента.

=СУММ(A1:A10, C1:C10) — это пример нескольких аргументов.

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

Убедитесь, что вы

Начинайте каждую функцию со знака равенства (=)

Если вы опустите знак равенства, введенное вами может отображаться как текст или как дата. Например, если ввести СУММ(A1:A10), Excel отобразит текстовую строку СУММ(A1:A10) и не выполнит расчет. Если вы введете 11/2, Excel отобразит дату со 2 по ноябрь (при условии, что формат ячейки Общий) вместо деления 11 на 2.

Соответствует всем открывающим и закрывающим скобкам

Убедитесь, что все круглые скобки являются частью совпадающей пары (открывающей и закрывающей). Когда вы используете функцию в формуле, важно, чтобы каждая скобка находилась в правильном положении, чтобы функция работала правильно. Например, формула =ЕСЛИ(B5

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

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

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

Для Excel в Windows нажмите "Файл" > "Параметры" > "Формулы" или
для Excel на Mac выберите меню Excel > "Настройки" > "Проверка ошибок".

В Excel 2007 нажмите кнопку Microsoft Office > Параметры Excel > Формулы.

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

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

В разделе "Правила проверки Excel" установите или снимите флажки любого из следующих правил:

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

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

Введите данные, отличные от формулы, в ячейку вычисляемого столбца.

Введите формулу в ячейку вычисляемого столбца, а затем нажмите Ctrl + Z или нажмите "Отменить" на панели быстрого доступа.

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

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

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

Ячейки, содержащие годы, представленные в виде двух цифр. Ячейка содержит текстовую дату, которая может быть неправильно истолкована как неверное столетие при использовании в формулах. Например, дата в формуле =ГОД("1/1/31") может быть 1931 или 2031. Используйте это правило, чтобы проверить наличие неоднозначных текстовых дат.

Числа в текстовом формате или с предшествующим апострофом: ячейка содержит числа, сохраненные в виде текста. Обычно это происходит, когда данные импортируются из других источников. Числа, хранящиеся в виде текста, могут привести к неожиданным результатам сортировки, поэтому лучше преобразовать их в числа. ‘=SUM(A1:A10) рассматривается как текст.

Формулы несовместимы с другими формулами в регионе. Формула не соответствует шаблону других формул рядом с ней. Во многих случаях формулы, расположенные рядом с другими формулами, отличаются только используемыми ссылками. В следующем примере с четырьмя соседними формулами Excel отображает ошибку рядом с формулой =СУММ(A10:C10) в ячейке D4, потому что соседние формулы увеличиваются на одну строку, а эта увеличивается на 8 строк — Excel ожидает формулу =СУММ (А4:С4).

Если ссылки, используемые в формуле, не совпадают со ссылками в соседних формулах, Excel отображает ошибку.

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

Например, Excel вставляет ошибку рядом с формулой =СУММ(D2:D4), когда применяется это правило, поскольку ячейки D5, D6 и D7 находятся рядом с ячейками, на которые ссылается формула, и ячейкой, на которую ссылается формула. содержит формулу (D8), а эти ячейки содержат данные, на которые должна была быть ссылка в формуле.

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

Формулы, ссылающиеся на пустые ячейки: формула содержит ссылку на пустую ячейку. Это может привести к непредвиденным результатам, как показано в следующем примере.

Предположим, вы хотите вычислить среднее значение чисел в следующем столбце ячеек. Если третья ячейка пуста, она не включается в расчет и результат равен 22,75. Если третья ячейка содержит 0, результат равен 18,2.

Данные, введенные в таблицу, недействительны: в таблице произошла ошибка проверки. Проверьте настройку проверки для ячейки, перейдя на вкладку "Данные" > группу "Инструменты данных" > "Проверка данных".

Выберите лист, который вы хотите проверить на наличие ошибок.

Если рабочий лист рассчитан вручную, нажмите F9, чтобы пересчитать.

Если диалоговое окно "Проверка ошибок" не отображается, нажмите вкладку "Формулы" > "Проверка формул" > кнопка "Проверка ошибок".

Если вы ранее игнорировали какие-либо ошибки, вы можете снова проверить их, выполнив следующие действия: нажмите "Файл" > "Параметры" > "Формулы". Для Excel на Mac выберите меню Excel > «Настройки» > «Проверка ошибок».

В разделе "Проверка ошибок" нажмите "Сбросить пропущенные ошибки" > "ОК".

Примечание. При сбросе пропущенных ошибок сбрасываются все ошибки на всех листах активной книги.

Совет. Переместите диалоговое окно "Проверка ошибок" чуть ниже строки формул.

Нажмите одну из кнопок действий в правой части диалогового окна.Доступные действия различаются для каждого типа ошибки.

Нажмите "Далее".

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

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

Если вы нажмете «Игнорировать ошибку», ошибка помечается как игнорируемая при каждой последующей проверке.

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

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

Excel отображает эту ошибку, когда число делится либо на ноль (0), либо на ячейку, которая не содержит значения.

Совет. Добавьте обработчик ошибок, как в следующем примере: =IF(C2,B2/C2,0)

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

Если вы используете функцию ВПР, имеет ли то, что вы пытаетесь найти, совпадение в диапазоне поиска? Чаще всего это не так.

=ЕСЛИОШИБКА(ВПР(D2,$D$6:$E$8,2,ИСТИНА),0)

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

Примечание. Если вы используете функцию, убедитесь, что имя функции написано правильно. В этом случае СУММА написана неправильно. Удалите букву «е», и Excel исправит ее.

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

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

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

Вы случайно удалили строку или столбец? Мы удалили столбец B в этой формуле =СУММ(A2,B2,C2) и посмотрите, что получилось.

Либо используйте команду "Отменить" (Ctrl+Z), чтобы отменить удаление, либо перестройте формулу, либо используйте ссылку на непрерывный диапазон, например: =СУММ(A2:C2), которая автоматически обновлялась бы при удалении столбца B.

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

Используете ли вы математические операторы (+, -, *, /, ^) с разными типами данных? Если это так, попробуйте вместо этого использовать функцию. В этом случае =СУММ(F2:F5) устранит проблему.

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

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

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

Добавить ячейки в окно просмотра

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

Чтобы выделить все ячейки на листе с формулами, на вкладке «Главная» в группе «Редактирование» нажмите «Найти и выделить» (или можно использовать Ctrl+G или Control+G на Mac) > «Перейти к специальным» > «Формулы». .

На вкладке "Формулы" в группе "Аудит формул" нажмите "Окно наблюдения".

Нажмите "Добавить наблюдение".

Подтвердите, что вы выбрали все ячейки, которые хотите отслеживать, и нажмите "Добавить".

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

Чтобы отобразить ячейку, на которую ссылается запись на панели инструментов Watch Window, дважды щелкните запись.

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

Удалить ячейки из окна просмотра

Если панель инструментов "Окно наблюдения" не отображается, на вкладке "Формулы" в группе "Аудит формул" нажмите "Окно наблюдения".

Выберите ячейки, которые хотите удалить.

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

Нажмите «Удалить отслеживание».

Иногда сложно понять, как вложенная формула вычисляет окончательный результат, потому что есть несколько промежуточных вычислений и логических тестов. Однако с помощью диалогового окна «Вычислить формулу» можно увидеть, как различные части вложенной формулы оцениваются в том порядке, в котором вычисляется формула. Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5),0) будет легче понять, если вы увидите следующие промежуточные результаты:

В диалоговом окне "Вычислить формулу"

Описание

Сначала отображается вложенная формула. Функция СРЗНАЧ и функция СУММ вложены в функцию ЕСЛИ.

Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому результат функции СРЗНАЧ(D2:D5) равен 40.

Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент логическая_проверка) равно False.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только в том случае, если выражение истинно.

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

Выберите вкладку "Формулы" > "Аудит формул" > "Оценить формулу".

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

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

Кнопка "Вход" недоступна для ссылки во второй раз, когда ссылка появляется в формуле, или если формула ссылается на ячейку в отдельной книге.

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

Чтобы снова просмотреть оценку, нажмите "Перезапустить".

Чтобы завершить оценку, нажмите "Закрыть".

Если ссылка пуста, в поле "Оценка" отображается нулевое значение (0).

Следующие функции пересчитываются каждый раз при изменении рабочего листа и могут привести к тому, что в диалоговом окне "Вычислить формулу" будут отображаться результаты, отличные от показанных в ячейке: СЛУЧАЙ, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, СТРОКИ, СТОЛБЦЫ, СЕЙЧАС , СЕГОДНЯ, СЛУЧАЙНО МЕЖДУ.

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

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

При открытии книги, содержащей внешние ссылки или пользовательские функции в Microsoft Excel, может возникнуть одна или несколько из следующих проблем:

Ссылки в рабочей книге рассчитываются по таблице внешних ссылок, хранящейся в рабочей книге, и могут отличаться от рассчитанных ранее.

Формулы, которые включают или ссылаются на внешние ссылки на другие книги, значения которых не хранятся в таблице внешних ссылок, могут отображать одну из следующих ошибок:

Формулы, включающие пользовательские функции из надстроек или других книг, которые недоступны, могут отображать следующую ошибку:

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

Причина

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

Временное решение

Чтобы обойти эту проблему, воспользуйтесь одним из следующих способов.

Перед открытием книги убедитесь, что источники внешних ссылок доступны

Если у вас есть ошибки в формулах внешних ссылок при открытии книги, но вы еще не сохранили книгу, выполните следующие действия:

Не сохранять книгу. Вместо этого закройте книгу, не сохраняя ее. Это отменит все изменения, внесенные в книгу.

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

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

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

Обновить или удалить ссылки

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

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

Закрыть все рабочие книги.

Создайте новую книгу.

Нажмите кнопку Microsoft Office, а затем щелкните Параметры Excel.

На вкладке "Формулы" нажмите "Вручную" в разделе "Параметры расчета", а затем нажмите "ОК".

Откройте сохраненную книгу.

На вкладке "Данные" нажмите "Изменить ссылки" в группе "Подключения".

Каждая ссылка будет содержать имя файла исходной книги, с которой связана эта книга. Если щелкнуть ссылку, исходный путь к файлу исходной книги появится под списком на метке «Расположение».

Нажмите Проверить статус, чтобы обновить статус всех ссылок в списке. Дождитесь обновления статуса всех ссылок.

Проверьте статус в столбце Статус, нажмите на ссылку и выполните одно из следующих действий:

Если статус "ОК", никаких действий не требуется. Ссылка рабочая и актуальная.

Если статус «Неизвестно», нажмите «Проверить статус», чтобы обновить статус для всех ссылок в списке.

Если статус неприменим, ссылка использует OLE или динамический обмен данными (DDE). Excel не может проверить статус этих типов ссылок.

Если статус "Ошибка: источник не найден", нажмите "Изменить источник", а затем выберите соответствующую книгу для ссылки.

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

Если статус «Предупреждение: значения не обновлены», нажмите «Обновить значения». Ссылка не обновлялась при открытии книги.

Если статус "Предупреждение: исходный код не пересчитан", нажмите "Открыть исходный код", а затем нажмите клавишу F9, чтобы рассчитать рабочую книгу. Рабочая книга может быть настроена на ручной расчет в исходном файле. Чтобы настроить книгу на автоматический расчет, нажмите кнопку Microsoft Office, а затем щелкните Параметры Excel. На вкладке "Формулы" нажмите "Автоматически" в разделе "Параметры расчета".

Если статус "Ошибка: неопределенное или непрямоугольное имя", некоторые имена невозможно разрешить, пока вы не откроете исходную книгу. Щелкните Открытый исходный код, вернитесь к целевой книге и щелкните Проверить состояние. Если это не решит проблему, убедитесь, что имя не пропущено и не написано с ошибками. Переключитесь на исходную книгу, перейдите на вкладку "Формулы", нажмите "Определить имя" и найдите имя.

Если статус «Предупреждение: откройте исходный код для обновления значений», нажмите «Открытый исходный код». Ссылка не может быть обновлена, пока вы не откроете исходный код.

Если статус Источник открыт, источник открыт. Никаких действий не требуется, если только вы не получаете сообщения об ошибках рабочего листа.

Если статус «Значения обновлены из имени файла», никаких действий не требуется. Значения обновлены.

Если статус "Ошибка: статус не определен", Excel не может определить статус ссылки. Источник может не содержать рабочих листов. В качестве альтернативы источник может быть сохранен в неподдерживаемом формате файла. Нажмите Обновить значения.

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

Нажмите кнопку Microsoft Office, а затем щелкните Параметры Excel.

На вкладке "Формулы" выберите "Автоматически" в разделе "Параметры расчета", а затем нажмите "ОК".

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

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

214395 Описание того, как Excel определяет текущий режим расчета

Навсегда удалить формулу ссылки, а затем заменить ее значением

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

Примечание. Когда вы заменяете формулу ее значением, Excel навсегда удаляет формулу.

Чтобы скопировать формулы и вставить значения, выполните следующие действия:

Откройте книгу в той версии Excel, в которой книга была сохранена в последний раз. Когда вам будет предложено обновить ссылки, нажмите «Нет». Поскольку последний раз файл был сохранен в этой версии Excel, принудительное обновление ссылок не требуется. Расчет может происходить с последним известным значением ссылки.

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

Щелкните правой кнопкой мыши ту же ячейку или диапазон ячеек и выберите "Специальная вставка".

В диалоговом окне "Специальная вставка" нажмите "Значения" в разделе "Вставить" и нажмите "ОК".

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше

В следующем примере используется формула =СУММ(B2,C2,D2) в столбце E.

Если вы случайно удалили строки или столбцы, вы можете сразу же нажать кнопку "Отменить" на панели быстрого доступа (или нажать CTRL+Z), чтобы восстановить их.

Настройте формулу так, чтобы в ней использовалась ссылка на диапазон вместо отдельных ячеек, например =СУММ(B2:D2). Теперь вы можете удалить любой столбец в диапазоне сумм, и Excel автоматически скорректирует формулу. Вы также можете использовать =SUM(B2:B5) для суммы строк.

Пример. Функция ВПР с неправильными ссылками на диапазон

Увеличьте диапазон или уменьшите значение поиска в столбце, чтобы оно соответствовало эталонному диапазону. =VLOOKUP(A8,A2:E5,5,FALSE) будет допустимым эталонным диапазоном, как и =VLOOKUP(A8,A2:D5,4,FALSE).

Пример: ИНДЕКС с неправильной ссылкой на строку или столбец

Настройте ссылки на строки или столбцы так, чтобы они находились в пределах диапазона поиска INDEX. =INDEX(B2:E5,4,4) вернет правильный результат.

Пример. ДВССЫЛНАЯ ссылка на закрытую книгу

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

Проблемы OLE

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

Проблемы DDE

Примечание. Динамический обмен данными (DDE) — это установленный протокол для обмена данными между программами на базе Microsoft Windows.

Проблемы с макросами

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

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Симптомы

Вы получаете следующее сообщение об ошибке:

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

Ошибка возникает, когда вы:

Открыть или сохранить книгу Excel
Открыть книгу Excel, которая ссылается на относительное имя из другой книги
Использование определенного имени в формуле в книге Excel
Определение или удаление имени в книге Excel

Разрешение

Примечание. Прежде чем переходить к методам разрешения, убедитесь, что у вас установлены последние обновления Office: обновляйте Windows с помощью последних обновлений.

После установки доступных обновлений проверьте, сохраняется ли проблема. Перейдите к методам решения, если у вас все еще есть проблемы.

Мы рекомендуем вам следовать приведенным в этой статье методам по порядку. Однако, если вы ранее пробовали один из способов исправить эту ошибку, и он не помог, вы можете быстро перейти к другому методу из этого списка:

Способ 1. Проверьте, не превышаете ли вы лимиты.
Способ 2. Убедитесь, что активен только один экземпляр Excel
Способ 3. Закройте все приложения.
Способ 4. Протестируйте Excel в безопасном режиме.
Способ 5. Отключите панель предварительного просмотра в проводнике Windows (только для Windows 7)
Способ 6. Сохраните файл книги Excel, если вы используете относительные имена.
Способ 7. Измените определенные имена на ссылки непосредственно на ячейки

Способ 1. Проверьте, не превышаете ли вы лимиты

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

Максимальный размер листа – 1 048 576 строк на 16 384 столбца.
Общее количество символов, которое может содержать ячейка, составляет 32 767 символов.
Максимальный диапазон, выбранный для расчета, – 2048.
Максимальный уровень вложенности функций в вычисление – 64.

Полный список спецификаций и ограничений Excel 2010 см. в этой статье на веб-сайте Office:

Если вы проверили и рабочий лист или рабочая книга не превышают ограничения Excel, перейдите к следующему способу.

Способ 2. Убедитесь, что активен только один экземпляр Excel

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

Откройте диспетчер задач. Для этого выполните одно из следующих действий:

Нажмите CTRL + ALT + Delete, а затем нажмите "Запустить диспетчер задач".
Нажмите CTRL + Shift + Esc.
Щелкните правой кнопкой мыши пустую область панели задач и выберите "Запустить диспетчер задач".

Открыв диспетчер задач, перейдите на вкладку "Приложения".

Нажмите на панель задач, чтобы отсортировать приложения в алфавитном порядке.

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

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

Способ 3. Закройте все приложения

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

Вы можете закрыть приложения вручную или выполнить шаги "чистой загрузки", описанные в одной из следующих статей:

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

Способ 4. Протестируйте Excel в безопасном режиме

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

Нажмите "Пуск" .

В Windows 7 введите excel /s в поле Поиск программ и файлов и нажмите Enter. В Windows Vista введите excel /s в поле «Начать поиск» и нажмите клавишу ВВОД.

Проверьте заголовок. Должно быть написано «Книга 1 — Microsoft Excel (безопасный режим)».

Нажмите "Файл" и выберите "Открыть".

Найдите рабочую книгу Excel для тестирования и откройте ее.
Откройте книгу Excel и проверьте. Если ошибка больше не возникает, возможно, у вас слишком много надстроек или конкретная надстройка может вызвать эту ошибку. Мы рекомендуем выполнить действия, описанные в этой онлайн-статье Microsoft, чтобы выгрузить надстройки:

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

Способ 5. Отключите панель предварительного просмотра в проводнике Windows (только для Windows 7)

Панель предварительного просмотра используется для просмотра содержимого большинства файлов в проводнике Windows. Например, если щелкнуть изображение, видео или текстовый файл, можно просмотреть его содержимое, не открывая файл. По умолчанию панель предварительного просмотра отключена в Windows 7. Однако, если она включена, это может вызвать конфликт с книгой Excel, которую вы пытаетесь открыть, что приведет к этой ошибке. Мы рекомендуем отключить панель предварительного просмотра и протестировать открытие книги Excel. Для этого:

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

Способ 6: сохранить как файл книги Excel, если вы используете относительные имена

Ошибка может возникнуть, когда вы создаете книгу, содержащую относительное имя, а затем заполняете диапазон ячеек, ссылающихся на это относительное имя, в новой книге. Например, вы создаете книгу, содержащую относительное имя, а затем в другой книге нажимаете Ctrl + Enter, чтобы заполнить диапазон ячеек ссылкой на относительное имя. Вы сохраняете вторую книгу как файл «Книга Excel 97-2003 (*.xls)», а затем закрываете обе книги.

Чтобы обойти эту проблему, воспользуйтесь одним из следующих вариантов:

Вариант 1

Сначала откройте книгу Excel, содержащую относительное имя.
Затем откройте книгу Excel, содержащую ссылку на относительное имя.

Вариант 2

Сохраните обе книги как файлы книг Excel (.xlsx). Для этого:

Нажмите "Файл", а затем "Сохранить как".
Выберите Книга Excel (*.xlsx) в поле Тип файла и сохраните файл.

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

Способ 7. Измените определенные имена на прямые ссылки на ячейки

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

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

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

Если ошибка продолжает возникать, перейдите к разделу "Ссылки" этой статьи.

Ссылки

Если информация в этой статье базы знаний не помогла устранить ошибку в Excel 2010, выберите один из следующих вариантов:

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Исправить ошибку для определенной функции

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

Проблемы с вычитанием

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

Вычесть ссылку на ячейку из другой

Введите два значения в две отдельные ячейки. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере в ячейке D2 указана запланированная сумма, а в ячейке E2 — фактическая сумма. F2 имеет формулу =D2-E2.

Или используйте СУММ с положительными и отрицательными числами

Введите положительное значение в одну ячейку и отрицательное значение в другую. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки вместе. В этом примере ячейка D6 содержит бюджетную сумму, а ячейка E6 имеет фактическую сумму в виде отрицательного числа. F6 имеет формулу =СУММ(D6,E6).

В Windows откройте панель управления регионом.

Windows 10: нажмите "Пуск", введите "Регион" и нажмите панель управления "Регион".

Windows 8: на начальном экране введите «Регион», нажмите «Настройки», а затем нажмите «Регион».

Windows 7: нажмите "Пуск", введите "Регион", а затем нажмите "Регион и язык".

На вкладке "Форматы" нажмите "Дополнительные настройки".

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

Если есть запятые там, где должны быть знаки минус для вычитания, измените их на знаки минус.

Повторите этот процесс для других ячеек, в которых есть ошибка.

Вычесть ссылку на ячейку из другой

Введите две даты в две отдельные ячейки. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере в ячейке D10 указана дата начала, а в ячейке E10 — дата окончания. F10 имеет формулу =E10-D10.

Или используйте функцию РАЗНДАТ

Введите две даты в две отдельные ячейки. В третьей ячейке используйте функцию DATEDIF, чтобы найти разницу в датах. Дополнительные сведения о функции РАЗНДАТ см. в разделе Расчет разницы между двумя датами.

Расширьте столбец даты. Если ваша дата выровнена по правому краю, то это дата. Но если он выровнен по левому краю, это означает, что дата на самом деле не является датой. Это текст. И Excel не распознает текст как дату. Вот несколько решений, которые могут решить эту проблему.

Проверить начальные пробелы

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

Поместите курсор в начало и посмотрите, сможете ли вы выбрать один или несколько пробелов. Вот как выглядит выделенный пробел в начале ячейки:

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

Выберите столбец, содержащий дату, щелкнув его заголовок.

Нажмите Данные > Текст в столбцы.

Дважды нажмите "Далее".

На шаге 3 из 3 мастера в разделе Формат данных столбца нажмите Дата.

Выберите формат даты и нажмите "Готово".

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

Проверьте настройки даты вашего компьютера

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

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

Есть два решения этой проблемы. Вы можете изменить систему дат, которую использует ваш компьютер, чтобы она соответствовала системе дат, которую вы хотите ввести в Excel. Или в Excel вы можете создать новый столбец и использовать функцию ДАТА для создания истинной даты на основе даты, сохраненной в виде текста. Вот как это сделать, если система дат вашего компьютера — мм/дд/гггг, а ваша текстовая дата — 31/12/2017 в ячейке A1:

Создайте следующую формулу: =ДАТА(ПРАВО(A1,4),СРЕДИНА(A1,4,2),ЛЕВО(A1,2))

Результатом будет 31 декабря 2017 г.

Если вы хотите, чтобы формат отображался как дд/мм/гг, нажмите CTRL+1 (или + 1 на Mac).

Выберите другой язык, в котором используется формат дд/мм/гг, например английский (Великобритания). Когда вы закончите применять формат, результатом будет 31/12/2017, и это будет настоящая дата, а не текстовая дата.

Примечание. Приведенная выше формула написана с использованием функций ДАТА, ПРАВО, СРЕДНЯЯ и ЛЕВАЯ. Обратите внимание, что это написано с предположением, что текстовая дата состоит из двух символов для дней, двух символов для месяцев и четырех символов для года. Возможно, вам придется изменить формулу в соответствии с вашей датой.

Проблемы с пробелами и текстом

1. Выберите ячейки, на которые ссылаются

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

2. Найти и заменить

На вкладке "Главная" нажмите "Найти и выбрать" > "Заменить".

3. Замените пробелы ничем

В поле Найти введите один пробел. Затем в поле Заменить на удалите все, что там может быть.

4. Заменить или Заменить все

5. Включите фильтр

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

6. Установите фильтр

Нажмите стрелку фильтра и снимите флажок "Выбрать все". Затем установите флажок «Пробелы».

7. Установите любые неназванные флажки

Установите флажки, рядом с которыми ничего нет, например этот.

8. Выберите пустые ячейки и удалите

Когда Excel вернет пустые ячейки, выделите их.Затем нажмите клавишу Удалить. Это удалит все скрытые символы в ячейках.

9. Очистить фильтр

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

10. Результат

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

Тот же пример с ISTEXT

Здесь функция ISTEXT была добавлена в столбец F. Все ячейки в порядке, кроме одной со значением TRUE. Это означает, что в ячейке E2 есть текст. Чтобы решить эту проблему, вы можете удалить содержимое ячейки и повторно ввести значение 1865,00. Или вы также можете использовать функцию CLEAN для очистки символов или использовать функцию REPLACE для замены специальных символов другими значениями.

После использования CLEAN или REPLACE вы захотите скопировать результат и использовать Главная > Вставить > Специальная вставка > Значения. Возможно, вам также придется преобразовать числа, сохраненные в виде текста, в числа.

Читайте также: