Как исправить #REF! #ПУСТО!
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for 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 Еще…Меньше
Ошибка #REF! отображается, когда формула ссылается на недопустимую ячейку. Чаще всего это происходит потому, что формула ссылается на ячейки, которые были удалены или заменены другими данными.
В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).
Удаление столбцов B, C или D приведет к #REF! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!. В этом случае мы удалим столбец C (Продажи 2007), а формула теперь будет читать =СУММ(B2,#REF!,C2). Если вы используете явные ссылки на ячейки, как это (когда вы ссылаетесь на каждую ячейку по отдельности, разделенные запятой) и удаляете строку или столбец, на которые ссылается ссылка, Excel не может разрешить их, поэтому он возвращает #REF! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!. Это основная причина, по которой не рекомендуется использовать явные ссылки на ячейки в функциях.
Решение
-
Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку «Отменить» на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
-
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).
В следующем примере =ВПР(A8;A2:D5;5;FALSE) вернет #REF! ошибка, так как она ищет значение, возвращаемое из столбца 5, но диапазон ссылок — A:D, то есть только 4 столбца.
Решение
Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).
В этом примере формула =INDEX(B2:E5,5;5) возвращает #REF! Ошибка, так как диапазон INDEX состоит из 4 строк на 4 столбца, но формула запрашивает возврат того, что находится в 5-й и 5-й строках.
Решение
Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.
В следующем примере функция INDIRECT пытается ссылаться на закрытую книгу, вызывая #REF! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.
Решение
Откройте книгу, на который ссылается ссылка. Эта же ошибка возникает при ссылке на закрытую книгу с динамической функцией массива.
Структурированные ссылки на имена таблиц и столбцов в связанных книгах не поддерживаются.
Вычисляемые ссылки на связанные книги не поддерживаются.
Перемещение или удаление ячеек вызвало недопустимую ссылку на ячейку, или функция возвращает ошибку ссылки.
Если вы использовали ссылку OLE, которая возвращает #REF! ошибка, а затем запустите программу, которую вызывает ссылка.
Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.
Проблемы с макросами
Если макрос вводит на листе функцию, которая ссылается на ячейку над функцией, а ячейка, содержащая функцию, находится в строке 1, функция вернет #REF! поскольку нет ячеек над строкой 1. Проверьте функцию, чтобы узнать, ссылается ли аргумент на ячейку или диапазон ячеек, которые недопустимы. Для этого может потребоваться изменить макрос в редакторе Visual Basic (VBE), чтобы учесть эту ситуацию.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
|
В ячейках стоят формулы, тянущие данные из других книг Excel, в некоторых случаях чтобы оставить формулы , но занулить значения , ссылка делается на пустую ячейку. Проблема в том , что после обновления файлов появляется ошибка #ссылка! , если перейти по ссылке то , после окртыия файлов ошибка исчезает. |
|
|
LVL Пользователь Сообщений: 903 |
вероянее всего после открытия книги Вы пытаетесь пересчитать формулы вручную, а книги на которые есть ссылки закрыты, соответственно ошибка. Поменяйте режим пересчёта на авто, тогда ссылки будут обновляться корректно, но только по запросу |
|
бобочек  Пользователь Сообщений: 14 |
Ошибка «ссылка!» появляется при копировании ячейки из одного листа в другой на одной книге (Excel2010). Активных надстроек нет, Chrome отключен. |
|
Bema  Пользователь Сообщений: 6671 |
Файл бы увидеть. Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл |
|
БМВ  Модератор Сообщений: 20938 Excel 2013, 2016 |
Не посомтрел что ТС иной Изменено: БМВ — 09.03.2018 14:38:16 По вопросам из тем форума, личку не читаю. |
|
бобочек Пользователь Сообщений: 14 |
Ячейка С15 =(C3*КОРЕНЬ(2)-C4/2)*C8/C29/K2 превращается в =(#ССЫЛКА!*КОРЕНЬ(2)-#ССЫЛКА!/2)*#ССЫЛКА!/C18/#ССЫЛКА! Ну и так со всеми остальными |
|
БМВ Модератор Сообщений: 20938 Excel 2013, 2016 |
бобочек, изучайте относительные и абсолютные ссылки и изменении адресов при копировании содержимого формул. P.S. Z. да согласен, сбила с толку 8 ка в числе. и 2013 со слепу за 2018 принял Изменено: БМВ — 09.03.2018 14:49:01 По вопросам из тем форума, личку не читаю. |
|
Z  Пользователь Сообщений: 6111 Win 10, MSO 2013 SP1 |
#8 09.03.2018 14:45:36
off Да и «возраст» темы почтительный — стоило ли тревожить?!. «Ctrl+S» — достойное завершение ваших гениальных мыслей!.. |
||
|
бобочек Пользователь Сообщений: 14 |
Как-то раньше не было таких проблем, сейчас нашел только то, что если ячейку С15 копирую на другой лист на тот же С15, то все нормально, после этого даже копия по другому адресу проходит, если же сразу копировать не на С15, то идут неустранимые ошибки ССЫЛКА! Если Вы о адресации с $, то я не знаю как это поможет, неужели перед копированием надо в исходной формуле вставлять $ а потом это чистить ? Изменено: бобочек — 09.03.2018 15:18:11 |
|
vikttur Пользователь Сообщений: 47199 |
Показать пример с проблемой — намного эффективнее, чем кричать «Караул!» |
|
бобочек Пользователь Сообщений: 14 |
Еще примеры : Исходная С16 =C2*КОРЕНЬ(2)*C8/C29/K2 перенос на другой лист на С16 =C2*КОРЕНЬ(2)*C8/C29/K2 |
|
БМВ Модератор Сообщений: 20938 Excel 2013, 2016 |
бобочек, 22-18=4, 4-4=0 адрес со строкой 0 не существует. Еще нужны пояснения? По вопросам из тем форума, личку не читаю. |
|
бобочек Пользователь Сообщений: 14 |
#13 09.03.2018 15:51:21 Кажется дошло, спасибо ! В первом примере G4 должен был стать D0, а это низя. Изменено: бобочек — 14.03.2018 16:10:47 |
Excel Found a Problem with One Or More Formula References!
«One of my least favorite messages that Excel provides is: Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct. But I can never find bad references. It’s usually buried somewhere very deep, in a chart series formula, or a name definition, or who knows where else».
If you are experiencing the error — Excel found a problem with one or more formula references in this worksheet in the Excel workbook, it indicates that the Excel file is corrupt or partially damaged. Resolving the error is critical as it doesn’t let you save the file and may cause Excel data loss.
Here are some methods that you can follow to fix the error «Excel found a problem with one or more formula references in this worksheet».
| Workable Solutions | Step-by-step Troubleshooting |
|---|---|
| Isolate the Spreadsheet Tab | Copy your tabs from the old workbook to a new one. Keep checking which tab…Full steps |
| Run Excel Repair Software | Download, install and launch EaseUS Data Recovery Wizard. Go to the software’s home screen and…Full steps |
| Check Formulas in Excel | Go to Formulas and click on the «Error Checking» button. This runs a scan…Full steps |
| Check the External Links | Go to the «Data» Tab. In the Connections group, click on «Edit Links». It opens…Full steps |
| Other Workable Solutions |
Check Your Charts…Full steps Check Pivot Tables…Full steps |
Solution 1. Isolate the Specific Spreadsheet Tab
The first thing you need to do is isolate the specific Excel sheet. If you have a file with lots of tabs, you may want to start by isolating the specific tab to solve the case. It’s easy to isolate the tab.
Step 1. Copy your tabs from the old workbook to a new one.
Step 2. Keep checking which tab in the new workbook throws the error message.
Step 3. Click «Save» after copying each of the tabs. The error message usually appears at the time of saving the event. Then, you can find the problematic tab.
This will help you find the faulty sheet from the workbook that you can review. In case you can’t fix the error, back up the specific sheet and remove it after the backup.
Solution 2. Run Excel Repair Software to Repair Excel Files
If Method 1 can’t solve the error — Excel found a problem with one or more formula references in this worksheet, you can turn to advanced Excel repair software. EaseUS Data Recovery Wizard is a powerful tool that can repair corrupt Word documents, Excel files, PPT files, and PDFs. Follow the steps below to recover and repair Excel files.
Step 1. Launch EaseUS Data Recovery Wizard, and then scan disk with corrupted documents. This software enables you to fix damaged Word, Excel, PPT, and PDF files in same steps.
Step 2. EaseUS data recovery and repair tool will scan for all lost and corrupted files. You can find the target files by file type or type the file name in the search box.
Step 3. EaseUS Data Recovery Wizard can repair your damaged documents automatically. After file preview, you can click «Recover» to save the repaired Word, Excel, and PDF document files to a safe location.
Solution 3. Check Formulas in Excel to Fix Excel Errors
Small and silly formula errors in excel can also lead to big consequences. To avoid such a case, it’s advised to check your formula and make it error-free.
Step 1. Go to Formulas and click on the «Error Checking» button
Step 2. This runs a scan on the sheet and displays the issues.
If no issue is found, it displays the following message:
«The error check is completed for the entire sheet.»
Then, you can try to save the Excel file again. If the error message persists, proceed to the next method.
Solution 4. Check the External Links
The next option to solve this issue is to find the external links that have errors. Follow the steps below to check out the external links containing errors:
Step 1. Go to the «Data» Tab.
Step 2. In the Connections group, click on «Edit Links». It opens the Edit Links dialog box will list all the workbooks that are being referenced.
Step 3. Check the links. If you find any faulty link, remove it and then save the sheet.
Solution 5. Check Your Charts
If you are still experiencing the fault, you can try to review the charts to solve the problem. As you know, if the error is within a chart it will pop up after you have selected the chart or when you are entering the Select Data Source dialog box.
Here is a list of area locations where you need to check for errors.
- In the source data of the chart.
- In each of the chart series formulas present within the Select Data Source dialog box.
- Check for the errors in the horizontal axis formula which is present inside the Select Data Source dialog box.
- Also, check in the chart’s Secondary Axis.
- Also check in the linked Axis Labels, Data Labels, or the Chart Title.
Solution 6. Check Pivot Tables
There could be an error in your Pivot Table’s Data Source formula. To check Pivot Tables, follow these steps:
Step 1. Go to «PivotTable Tools > Analyze > Change Data Source > Change Data Source…»
Step 2. Check out whether any of your formulas are having the error or not.
Conclusion
This article has provided you with almost all the fixes to troubleshoot this error «Excel found a problem with one or more formula references in this worksheet» error. But the solutions may lead to a partial loss of information. Therefore, you’d better make a backup to external devices in advance.
Excel Found a Problem with One Or More Formula References!
«One of my least favorite messages that Excel provides is: Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct. But I can never find bad references. It’s usually buried somewhere very deep, in a chart series formula, or a name definition, or who knows where else».
If you are experiencing the error — Excel found a problem with one or more formula references in this worksheet in the Excel workbook, it indicates that the Excel file is corrupt or partially damaged. Resolving the error is critical as it doesn’t let you save the file and may cause Excel data loss.
Here are some methods that you can follow to fix the error «Excel found a problem with one or more formula references in this worksheet».
| Workable Solutions | Step-by-step Troubleshooting |
|---|---|
| Isolate the Spreadsheet Tab | Copy your tabs from the old workbook to a new one. Keep checking which tab…Full steps |
| Run Excel Repair Software | Download, install and launch EaseUS Data Recovery Wizard. Go to the software’s home screen and…Full steps |
| Check Formulas in Excel | Go to Formulas and click on the «Error Checking» button. This runs a scan…Full steps |
| Check the External Links | Go to the «Data» Tab. In the Connections group, click on «Edit Links». It opens…Full steps |
| Other Workable Solutions |
Check Your Charts…Full steps Check Pivot Tables…Full steps |
Solution 1. Isolate the Specific Spreadsheet Tab
The first thing you need to do is isolate the specific Excel sheet. If you have a file with lots of tabs, you may want to start by isolating the specific tab to solve the case. It’s easy to isolate the tab.
Step 1. Copy your tabs from the old workbook to a new one.
Step 2. Keep checking which tab in the new workbook throws the error message.
Step 3. Click «Save» after copying each of the tabs. The error message usually appears at the time of saving the event. Then, you can find the problematic tab.
This will help you find the faulty sheet from the workbook that you can review. In case you can’t fix the error, back up the specific sheet and remove it after the backup.
Solution 2. Run Excel Repair Software to Repair Excel Files
If Method 1 can’t solve the error — Excel found a problem with one or more formula references in this worksheet, you can turn to advanced Excel repair software. EaseUS Data Recovery Wizard is a powerful tool that can repair corrupt Word documents, Excel files, PPT files, and PDFs. Follow the steps below to recover and repair Excel files.
Step 1. Launch EaseUS Data Recovery Wizard, and then scan disk with corrupted documents. This software enables you to fix damaged Word, Excel, PPT, and PDF files in same steps.
Step 2. EaseUS data recovery and repair tool will scan for all lost and corrupted files. You can find the target files by file type or type the file name in the search box.
Step 3. EaseUS Data Recovery Wizard can repair your damaged documents automatically. After file preview, you can click «Recover» to save the repaired Word, Excel, and PDF document files to a safe location.
Solution 3. Check Formulas in Excel to Fix Excel Errors
Small and silly formula errors in excel can also lead to big consequences. To avoid such a case, it’s advised to check your formula and make it error-free.
Step 1. Go to Formulas and click on the «Error Checking» button
Step 2. This runs a scan on the sheet and displays the issues.
If no issue is found, it displays the following message:
«The error check is completed for the entire sheet.»
Then, you can try to save the Excel file again. If the error message persists, proceed to the next method.
Solution 4. Check the External Links
The next option to solve this issue is to find the external links that have errors. Follow the steps below to check out the external links containing errors:
Step 1. Go to the «Data» Tab.
Step 2. In the Connections group, click on «Edit Links». It opens the Edit Links dialog box will list all the workbooks that are being referenced.
Step 3. Check the links. If you find any faulty link, remove it and then save the sheet.
Solution 5. Check Your Charts
If you are still experiencing the fault, you can try to review the charts to solve the problem. As you know, if the error is within a chart it will pop up after you have selected the chart or when you are entering the Select Data Source dialog box.
Here is a list of area locations where you need to check for errors.
- In the source data of the chart.
- In each of the chart series formulas present within the Select Data Source dialog box.
- Check for the errors in the horizontal axis formula which is present inside the Select Data Source dialog box.
- Also, check in the chart’s Secondary Axis.
- Also check in the linked Axis Labels, Data Labels, or the Chart Title.
Solution 6. Check Pivot Tables
There could be an error in your Pivot Table’s Data Source formula. To check Pivot Tables, follow these steps:
Step 1. Go to «PivotTable Tools > Analyze > Change Data Source > Change Data Source…»
Step 2. Check out whether any of your formulas are having the error or not.
Conclusion
This article has provided you with almost all the fixes to troubleshoot this error «Excel found a problem with one or more formula references in this worksheet» error. But the solutions may lead to a partial loss of information. Therefore, you’d better make a backup to external devices in advance.