Как исправить #REF! ошибка
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 Еще…Меньше
Ошибка #REF! отображается, когда формула ссылается на недопустимую ячейку. Чаще всего это происходит потому, что формула ссылается на ячейки, которые были удалены или заменены другими данными.
В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).
Удаление столбцов B, C или D приведет к #REF! ошибку «#ЗНАЧ!». В этом случае мы удалим столбец C (Продажи 2007), а формула теперь будет читать =СУММ(B2,#REF!,C2). Если вы используете явные ссылки на ячейки, как это (когда вы ссылаетесь на каждую ячейку по отдельности, разделенные запятой) и удаляете строку или столбец, на которые ссылается ссылка, Excel не может разрешить их, поэтому он возвращает #REF! ошибку «#ЗНАЧ!». Это основная причина, по которой не рекомендуется использовать явные ссылки на ячейки в функциях.
Решение
-
Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку «Отменить» на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
-
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).
В следующем примере =VLOOKUP(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 или получить поддержку в сообществах.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Some Excel users have reported experiencing the Data source reference is not valid error while creating a pivot table in Excel. We will learn why this error occurs and how you can resolve it in this post.
Why is my data source reference not valid?
The common reason behind the Data source reference is not valid error in Excel is the fact that the filename of the Excel workbook contains square brackets which are invalid characters. Another primary reason for the same error is that the range from which you are trying to insert a pivot table doesn’t exist or is undefined. Or, the reference for the named range you are using for your pivot table is invalid. Besides that, it can also be the case that you are trying to open the file from an URL or attachment in an email.
Now, in any scenario, this post will help you resolve the error. Here, we will discuss working fixes to get rid of the “Data source reference is not valid” error in Microsoft Excel.
If you are experiencing the Data source reference is not valid error when creating a pivot table in Microsoft Excel, you can follow the below fixes to resolve it:
- Delete brackets from the filename.
- Save the workbook on your local disk.
- Make sure that the range exists and is defined.
- Ensure that the reference for the defined range is valid.
- Repair the Excel file.
1] Delete brackets from the filename
This error is likely to occur if the filename of the Excel workbook consists of an invalid character i.e., square brackets. Hence, if the scenario applies, you can change the filename of the problematic Excel file and delete brackets from it.
To do that, first, you must close Excel and make sure the problematic workbook is not open in any other program. Now, open File Explorer using Win+E and navigate to the folder where you have saved the Excel file. Next, right-click on the file, choose the Rename option, remove the brackets from the filename, and press the Enter button.
After renaming the file, launch Excel and open your file. Check if you are able to create a pivot table without the “Data source reference is not valid” error. If not, you can move on to the next fix.
2] Save the workbook on your local disk
It might be the case that you are opening the Excel file directly from an online source or email attachment. If so, you are likely to get the “Data source reference is not valid” error. So, if the scenario applies to you, save the workbook on your local disk first and then open it in Excel to create a pivot table. Open the file, click on the File > Save as option, and then save it on your local drive. Once done, check if the error is now resolved.
Read: How to fix the #REF error in Excel?
3] Make sure that the range exists and is defined
You will face this error if you are trying to create a pivot table in a range that is not defined or non-existing. For example, you are inserting a pivot table by clicking on Insert > PivotTable > From Table/Range. Now, you have entered the name of a table or range inside the Table/Range as “TWC” under the Select a table or range option. But, the TWC range doesn’t exist. So, you are likely to encounter the “Data source reference is not valid” error.
Hence, to avoid this error, you must ensure that the range from which you are trying to insert a pivot table exists and is defined. To define the range, you can follow the below steps:
- Firstly, open your Excel file and tap on the Formulas tab from the ribbon bar.
- Now, press the Name Manager option from the Formulas tab.
- Next, click on the New button and enter the name of the range you are creating.
- After that, inside the Refers to box, enter the cells you are going to use in the created range. To do that, you can either use the built-in selector or manually enter the range.
- Finally, press the OK button to complete the process.
Once done, you can try inserting a pivot table from the defined range and check if the error is resolved or not.
4] Ensure that the reference for the defined range is valid
In addition to the above fix, you must ensure that referenced cells (Refer to) for the created range contain valid values. Else, you will experience the Data source reference is not valid error. So, make sure the reference for the defined range is valid. Here’s how:
- First, click on the Formulas tab > Name Manager option.
- Next, select the range you are using for your pivot table and check the entry for the Refer to value.
- If the reference is incorrect, double-click on the range and then make the modifications to the entry accordingly.
- When done, you can try inserting a pivot table and hopefully, the error will be resolved now.
Read: Excel ran out of resources while attempting to calculate formulas.
5] Repair the Excel file
If the above solutions didn’t work for you, try repairing the problematic Excel file. It might be the case that your file is corrupted which is why you keep getting this error. So, you can repair the Excel file and recover data to fix the error.
To do that, you can use the native feature in Microsoft Excel. First, close the problematic file and click on the File > Open option. Now, choose the corrupted file, press the arrow present next to the Open button, and select the Open and Repair option. See if this helps. If this method doesn’t work, you can also try using a third-party Excel repair tool and check if it works.
Hope, this post helps you resolve the Data source reference is not valid error in Microsoft Excel.
Now read: Error, Reference source not found – Microsoft Office issue.
How do I fix the Reference is not valid in Excel?
You can fix the “Data source reference is not valid” error in Excel by removing square brackets from the filename of your Excel file. Apart from that, save the Excel file on your local drive, define the range you are using in your pivot table, and make sure the referenced cell values for the defined range are valid. We have discussed these and more solutions in detail below. So, let us check out.
[Excel2003-RUS]
1) Простой алгоритм выглядит так: Меню «Сервис»—«Проверка наличия ошибок…» (дублируется кнопкой на панели «Зависимости»).
Далее исправлять ошибки по очереди.
В данном случае Excel будет вести себя механистически — просто будет искать ошибочные ячейки сверху вниз. Поэтому до «первоисточника» путь может оказаться неблизким.
2) Списка ВСЕХ ошибок с рекомендациями — нет, не видел (справедливости ради, и не искал, т.к. с «неприятностями» справляюсь по мере их возникновения, а не коплю знания впрок).
3) Общие рекомендации по устранению конкретной ошибки даются в сообщении об ошибке: «Проверьте правильность указания пути, книги, названия диапазона и ссылки на ячейки.»
(примеч.: сама формулировка сообщения «…содержит ОДНУ ИЛИ НЕСКОЛЬКО …» говорит о том, что списка сразу всех «ошибочных ячеек» Excel Вам представить, скорее всего, не может. Переводя на человеческий — «есть как минимум одна, а сколько всего — не знаю»).
4) Некоторый общий перечень ошибок (не уверен, что всех) содержится в разделе справки «Поиск и исправление ошибок в формулах» (приведены причины/рекомендации по устранению по ошибкам «#####», «#ЗНАЧ!», «#ДЕЛ/0!», «#ИМЯ?», «#Н/Д», «#ССЫЛКА!», «#ЧИСЛО!», «#ПУСТО!». Описано «Наблюдение за формулой и результатом ячейки» при помощи «Окна контрольного значения». Даны инструкции по «пошаговому вычислению сложных формул». Описан метод «Прослеживание связей между формулами и ячейками» (трассировка зависимостей). Есть и подраздел «Исправление общих ошибок в формулах», включающий, например, работу со списком проверяемых/игнорируемых ошибок).
Здравствуйте, обращаюсь, прежде всего, к завсегдатаям форума, к тем, кто плотно работает с Excel, а также ко всем, кто мог видеть такое сообщение и знает в каких случаях оно может появляться.
Полный текст сообщения:
«Недопустимая ссылка. Ссылка для названий, значений или размеров должна указывать на одну ячейку, строку или столбец».
Это сообщение появляется после того, как дописываешь новую строку в «умную таблицу». При этом при последующем нажатии на «Ок» новое значение (новая строка) в умную таблицу добавляется, но, не нормально то что постоянно появляется это сообщение.
Файл, к сожалению, приложить не смогу, там данные, которые не могу афишировать. А упрощение файла и сведение его данных к небольшому объему, возможно, не даст проявления этого эффекта.
То есть, интересует ответ по теме вопроса в каких случаях по Вашим наблюдением можем появляться это сообщение и как сделать так (в каком направлении двигаться), чтобы избавиться от этого сообщения и избежать его в будущем (то есть что лучше не делать).
Поиск в сети по этому запросу ничего не дал. На сайте майкрософта тоже не нашел разъяснений.
Изменено: Konstantin Zhi — 18.05.2016 15:19:18
Office Products Excel 2010 Excel 2016 Excel 2013 Excel 2019 Excel for Office 365 Еще…Меньше
Проблемы
При открытии книги Excel, содержащей квадратные скобки, в имени (например, «foo [1]») при попытке создания сводных таблиц с использованием данных из книги пользователь получит следующее сообщение об ошибке:ссылка на источник данных недействительна
Причина
Причина из-за того, что квадратные скобки являются недопустимыми символами в имени файла книги Excel.
Решение
Это поведение ожидается, если выполняются эти условия. Для решения этой проблемы пользователю потребуется удалить все недопустимые символы Excel из имени файла.
Дополнительная информация
Эта проблема может возблюдаться при попытке открыть книгу Excel из Internet Explorer. Internet Explorer создает копию файла во временной папке, переименование файла с помощью квадратных скобок. Это можно избежать, если нажать кнопку «Сохранить», а не «Открыть», когда Internet Explorer выведет файл пользователю.
Нужна дополнительная помощь?
4 Solutions Data Source Reference Is Not Valid
Резюме :
В чем ошибка ссылки на источник данных недействительна? Что вызывает ошибку ссылки на источник сводных данных недействительно? Как исправить ошибку ссылки на источник данных недопустимая сводка? Это сообщение от MiniTool покажет вам решения.
Что такое ошибка ссылки на источник данных не является действительной?
При попытке создать сводную таблицу вы можете столкнуться с ошибкой: ссылка на источник данных недействительна. Тогда что может вызвать ошибку, ссылка на источник данных сводной информации недействительна?
Как правило, это может быть вызвано несколькими причинами, например следующими:
- Файлы Excel не сохраняются на локальном диске.
- Имя файла Excel содержит квадратные скобки.
- Данные сводной таблицы относятся к несуществующему диапазону.
- Источник данных относится к именованному диапазону, который содержит недопустимые ссылки.
Итак, знаете ли вы, как исправить ошибку ссылки на источник данных недействительно? Если нет, продолжайте читать и найдите решения в следующем содержании.
Исправьте Excel, который не отвечает, и спасите ваши данные (несколькими способами)
Вы хотите избавиться от Microsoft Excel, который не отвечает? В этом посте мы покажем вам несколько методов, которые могут эффективно решить эту проблему.
Читать больше
4 способа недействительной ссылки на источник данных
В этой части мы покажем вам, как исправить ошибку неверной ссылки на источник данных.
Способ 1. Снять скобки с имени файла
Чтобы исправить ошибку ссылки на источник данных недействительно, вы можете попробовать убрать скобки из имени файла.
А вот и руководство.
- Закройте окно Excel, в котором в данный момент используется файл.
- Затем перейдите к расположению файла Excel.
- Затем щелкните его правой кнопкой мыши и выберите Переименовать .
- Затем удалите квадратные скобки из имени файла, поскольку сводная таблица не настроена для их поддержки.
После этого заново создайте сводную таблицу и проверьте, исправлена ли ошибка ссылки на источник данных.
Способ 2. Сохраните файл на локальном диске
Ссылка на источник данных о проблеме недействительна, может возникнуть, если вы открываете файл непосредственно с веб-сайта или непосредственно из вложений электронной почты. В этом случае файл будет открыт из временного файла, что вызовет эту проблему.
В этом случае вам необходимо сохранить этот файл Excel на локальный диск.
А вот и руководство.
- Откройте файл Excel.
- Затем нажмите файл > Сохранить как .
- Затем сохраните файл Excel на физическом диске.
Когда все шаги выполнены, проверьте, является ли ошибка ссылки на источник данных недействительной.
Способ 3. Убедитесь, что диапазон существует и определен
Если вы пытаетесь вставить сводную таблицу в несуществующий или не определенный диапазон, вы можете обнаружить, что ссылка на источник данных ошибки недействительна.
Итак, чтобы решить эту проблему, вам нужно убедиться, что диапазон существует и определен.
А вот и руководство.
- Щелкните значок Формулы вкладку на панели ленты, а затем щелкните Имя Менеджер продолжать.
- В окне ‘Диспетчер имен’ нажмите Новый и назовите диапазон, который вы собираетесь создать. Затем используйте Относится к поле, чтобы установить ячейки, которые вы хотите использовать для диапазона. Вы можете набрать его самостоятельно или воспользоваться встроенным селектором.
После этого определяется диапазон. Вы можете успешно создать сводную таблицу, не обнаружив, что ссылка на источник данных ошибки недопустима.
Способ 4. Убедитесь, что ссылка на именованный диапазон действительна
Чтобы исправить ошибку ссылки на источник сводных данных недействительно, необходимо убедиться, что ссылка на именованный диапазон действительна.
А вот и руководство.
- Нажмите Формулы > Имя Менеджер .
- Посмотрите, относится ли диапазон к ячейкам, которые вы хотите проанализировать с помощью сводной таблицы.
- Если вы видите какие-либо несоответствия, используйте ссылку.
- Затем переключитесь на правильное значение.
После завершения всех шагов вы можете проверить, является ли ошибка ссылки на источник данных недействительной.
Исправить Excel не может открыть файл | Восстановить поврежденный файл Excel
Excel не может открыть файл из-за недопустимого расширения Excel 2019/2016/2013/2010/2007 или файл Excel поврежден? 5 решений для устранения проблемы.
Читать больше
Заключительные слова
Подводя итог, в этом посте показано, как исправить ошибку недействительной ссылки на источник данных 4 способами. Если вы столкнулись с той же проблемой, вы можете попробовать эти решения. Если у вас есть лучшее решение, чтобы исправить это, вы можете поделиться им в зоне комментариев.
Содержание
- 1 Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel?
- 1.1 Способ 1: удаление скобок из имени файла
- 1.2 Способ 2: сохранение файла на локальном диске
- 1.3 Метод 3: Обеспечение того, что диапазон существует и он определен
- 1.4 Метод 4: Обеспечение того, чтобы ссылка для именованного диапазона была действительной
Некоторые пользователи получают «Ссылка на источник данных недействительна”При попытке создать сводную таблицу из диапазона в Excel. Сообщается, что эта конкретная ошибка возникает в нескольких версиях Excel и Windows, поэтому проблема не связана с конкретной версией ОС или Excel.
Ссылка на источник не верна
Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel?
Мы исследовали это конкретное сообщение об ошибке, просмотрев различные пользовательские отчеты и шаги по их устранению, чтобы решить проблему. Как выясняется, есть несколько распространенных виновников, которые в конечном итоге вызовут это конкретное сообщение об ошибке:
- Имя файла Excel содержит квадратные скобки — Есть набор запрещенных символов, которые не поддерживаются в сводных таблицах. Одна из распространенных причин, по которой вы можете увидеть эту конкретную ошибку, заключается в том, что имя файла Excel содержит ‘[‘ или ‘]’. В этом случае проблему можно решить, отредактировав имя файла и убрав квадратные скобки.
- Файл Excel не сохраняется на локальном диске — Возможно, вы видите это конкретное сообщение об ошибке, если пытаетесь создать сводную таблицу из файла Excel, открытого непосредственно с веб-сайта или из вложения электронной почты. В этом случае файл будет открыт из временной папки, которая не имеет тех же разрешений, что и обычная папка. Если этот сценарий применим, вы можете решить эту проблему, предварительно сохранив файл .xlsx на локальном диске.
- Данные сводной таблицы относятся к несуществующему диапазону — Диапазон, который не определен должным образом, также может быть причиной этой конкретной ошибки. Если вы пытаетесь создать сводную таблицу с диапазоном, который не был определен, вы получите то же сообщение об ошибке. В этом случае вы можете решить проблему, определив ярость, прежде чем пытаться создать сводную таблицу.
- Источник данных ссылается на именованный диапазон, который содержит недопустимые ссылки. — Другая возможная причина, по которой возникает эта ошибка, — это когда диапазон определен, но он ссылается на недопустимые значения. В этом случае вы можете использовать Диспетчер имен для определения указанных значений и внесения необходимых изменений для решения проблемы.
Если вы в настоящее время пытаются решить Ссылка на источник данных недействительна Ошибка, эта статья предоставит вам несколько шагов по устранению неполадок, которые помогут вам определить проблему, которая вызывает сообщение об ошибке. Ниже вы найдете набор методов, которые другие пользователи в аналогичной ситуации использовали для решения этой проблемы.
Для достижения наилучших результатов следуйте методам в том порядке, в котором они представлены. В конечном итоге вы должны наткнуться на исправление, которое будет эффективно в вашем конкретном сценарии.
Давай начнем!
Способ 1: удаление скобок из имени файла
Одна из самых популярных причин, которая в конечном итоге вызывает Ссылка на источник данных недействительна ошибка — неправильное имя файла Excel. Если отчеты создаются системой автоматизации отчетов и имя содержит запрещенные символы, такие как квадратные скобки ‘[]‘, Вы получите это сообщение об ошибке всякий раз, когда вы пытаетесь создать сводную таблицу.
Если этот сценарий применим к вашей текущей ситуации, вы сможете решить проблему, изменив имя файла .xlsx, удалив запрещенные символы. Вот краткое руководство по этому:
- Закройте окно Excel, которое в данный момент использует файл. Если файл используется, вы не сможете его переименовать.
- Используйте проводник, чтобы перейти к местоположению файла Excel. Как только вы попадете туда, щелкните по нему правой кнопкой мыши и выберите Переименовать.
- Далее, удалите скобки из имени файла, так как таблица Pivots не настроена для их поддержки.
Снятие скобок с имени файла - Попытайтесь заново создать сводную таблицу и посмотрите, не возникла ли еще ошибка.
Снятие скобок с имени файлаЕсли вы все еще сталкиваетесь с Ссылка на источник данных недействительна Ошибка или этот метод не применим к вашему конкретному сценарию, перейдите к следующему способу ниже.
Способ 2: сохранение файла на локальном диске
Эта проблема также может возникнуть, если вы открываете файл непосредственно с веб-сайта или непосредственно из вложения электронной почты. В этом случае файл будет открыт из временного файла, который в конечном итоге вызовет Ссылка на источник данных недействительна ошибка.
Если этот сценарий применим к вашему текущему сценарию, вы сможете решить эту проблему, предварительно сохранив файл Excel на локальном диске. Итак, прежде чем пытаться создать сводную таблицу, перейдите к Файл> Сохранить как и сохраните файл в физическом месте (на локальном диске).
Сохранение файла Excel на локальном диске
Однажды превосходить файл был сохранен локально, воссоздайте шаги, которые ранее вызывали Ссылка на источник данных недействительна ошибки и посмотрите, можете ли вы создать таблицу Pivots, не обнаружив ошибки.
Если этот метод не подходит для вашей текущей ситуации, перейдите к следующему способу ниже.
Метод 3: Обеспечение того, что диапазон существует и он определен
Другая причина, по которой вы можете столкнуться с ошибкой «Ссылка на источник данных недействительна». При попытке вставить сводную таблицу — это несуществующий / не определенный диапазон.
Для наглядности предположим, что вы пытаетесь создать сводную таблицу. Естественно, вы идете в Вставить сводную таблицу, вы выбираете переключатель, связанный с Выберите стол или диапазон и установить Таблица / Диапазон в ‘тестовое задание’. Теперь, если вы основали значения на «тестовом» диапазоне, а он не существует, вы увидите ошибку «Ссылка на источник данных недействительна». Как только вы нажмете Хорошо.
Видя ошибку из-за несуществующего диапазона
Если этот сценарий применим, вы сможете обойти проблему, определив диапазон, прежде чем пытаться создать сводную таблицу. Вот краткое руководство о том, как это сделать.
- Нажмите на Формулы вкладка из панели ленты, затем нажмите на Имя менеджера из списка.
- Внутри Имя менеджера окно, нажмите на новый и назовите диапазон, который вы собираетесь создать. Затем используйте Относится к поле, чтобы установить ячейки, которые вы хотите использовать для диапазона. Вы можете либо напечатать его самостоятельно, либо использовать встроенный селектор.
- Теперь, когда диапазон определен, вы можете успешно создать сводную таблицу, не встречая того же сообщения об ошибке.
Если этот сценарий не применим или вы все еще сталкиваетесь с Недопустимая ссылка на источник данных даже после выполнения действий, описанных выше, перейдите к следующему способу ниже.
Метод 4: Обеспечение того, чтобы ссылка для именованного диапазона была действительной
Даже если вы успешно определили диапазон, но вы все еще видите Ссылка на источник данных недействительна ошибка, скорее всего, вы видите сообщение, потому что именованный диапазон относится к некоторым ячейкам, значения которых недопустимы.
Чтобы исправить это, перейдите на Формулы> Имя Менеджер и посмотрите, относится ли диапазон к ячейкам, которые вы хотите проанализировать через сводную таблицу. Если вы видите какие-либо несоответствия, используйте Ссылаться на: поле, чтобы сделать переключение на правильное значение.
Обеспечение того, что ссылки действительны
После внесения изменений попробуйте снова создать сводную таблицу и посмотрите, не появляется ли по-прежнему такое же сообщение об ошибке.
Многие пользователи Excel сообщали об ошибке при создании сводных таблиц в книге Excel. Полное сообщение об ошибке выглядит следующим образом:
"Data source reference is not valid."
При подробном исследовании ошибка видна, когда:
- Имя файла содержит недопустимые символы.
- Файл открывается из Internet Explorer.
Если вы видите эту проблему в своей системе, не паникуйте. В этой статье рассматриваются исправления, позволяющие избежать ошибки «Ссылка на источник данных не является допустимой».
Исправление 1. Удалите недопустимые символы из имени файла.
Наиболее частая причина появления этой ошибки — когда имя файла содержит недопустимые символы. Следующие символы, если они присутствуют в имени файла, недопустимы
< > ? [ ] : | *.
Если вы видите, что ваше имя файла содержит один из этих символов, подумайте об их удалении.
Например, если имя файла — Excel[1], рассмотрите возможность переименования его в Excel_1
Чтобы переименовать файл, сделайте следующее:
1. Щелкните правой кнопкой мыши проблемный файл.
2. Выберите «Переименовать».
3. Дайте допустимое имя и нажмите Enter.
Теперь проверьте, можете ли вы создавать сводные таблицы.
Исправление 2: Сохраните файлы, открытые в Internet Explorer.
Когда вы открываете файл в Internet Explorer, его копия создается во временном каталоге. Эта копия будет переименована в квадратные скобки. Вы видите ошибку, поскольку имя файла содержит недопустимые символы (квадратные скобки).
Чтобы избежать ошибки, нажмите «Сохранить». Сначала сохраните файл, а затем попробуйте внести изменения.
Это все.
Надеемся, эта статья была информативной. Спасибо за чтение.
Пожалуйста, прокомментируйте и сообщите нам исправление, которое вам помогло. Другие читатели также получат от этого пользу.
Я пытаюсь создать сводную точку из диапазона, но получаю сообщение об ошибке. Data source reference is not valid
Я прогуглил это и попробовал все следующее.
- Выберите из таблицы вместо диапазона.
- Откройте электронную таблицу Excel, нажмите на вкладку просмотра и выберите новое окно. Сохраните электронную таблицу с другим именем для нового окна.
- Нажмите ДОМОЙ> ПОДГОТОВИТЬ> ПРОВЕРЬТЕ ДОКУМЕНТ> Удалены все ненужные ссылки и формы XML.
У меня есть метки на всех столбцах, и все ячейки заполнены.
Я использую Office 2010 на Win7, и файл в формате .xlsx
задан Pieter van Niekerk428
Я нашел причину, по которой сводные таблицы не работали, из-за имени файла. Отчеты создаются системой автоматизации отчетов, а имя содержит квадратные скобки [] которые не нравятся Excel.
Я совершенно случайно снял квадратные скобки и обнаружил, что опорные точки работают.
Я также пытался сохранить файл в опции « Save As квадратных скобках, но, как я уже сказал, Excel стонет об этом и не позволяет вам.
ответ дан Pieter van Niekerk428
У меня была такая же ошибка, но в имени файла не было скобок [] . Я обнаружил, что файл Excel из моего веб-инструмента отчетности на самом деле не был в формате XLSX. Когда я сделал «Сохранить как» в Excel, тип файла был « Веб-страница ». Я изменил это на « Книга Excel (* .xlsx) » и сохранил с тем же именем, и сводная таблица сработала.
Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel-2010 pivot-table.
Появление электронно-вычислительной техники значительно облегчило и ускорило работу человека во многих отраслях. Совершенствование техники и программного обеспечения в настоящее время позволяет выполнять как сложные вычисления, так и рутинную работу с минимальными временными затратами. Microsoft Excel можно рассматривать как раз как такой инструмент, который позволяет выполнять различные операции с цифровыми данными.
- Сводные таблицы в Excel
- Создание сводной таблицы Excel — алгоритм для новичков
- Обновление данных в сводной таблице в Excel
- Как сделать сводную таблицу в Excel из нескольких листов
- Изменение структуры отчёта
- Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel
Сводные таблицы в Excel
Excel предлагает ряд стандартных шаблонов, которые позволяют сэкономить время на построении таблиц. Независимо от того какие задачи стоят перед пользователем: подсчет семейного бюджета, учет средств самозанятого или продажи юридического лица или подготовка диаграмм и таблиц для научной работы студента.
Помимо стандартных шаблонов в Excel можно реализовать функцию обработки данных при помощи сводных таблиц. Сводные таблицы являются инструментом, аккумулирующим данные из нескольких источников (таблиц).
Процесс создания сводной таблицы будет зависеть от того для каких целей она создается. Для её создания нужно выбрать те данные, которые необходимо будет перенести в сводную таблицу и воспользоваться функцией создания. Первоначально Excel предлагает шаблон. Кроме тех параметров, которые уже будут заданы, в состав таблицы можно включить иные нужные данные.
Помощь в конструировании окажут имеющиеся функции:
- фильтр отчётов;
- суммирование значений.
А также необходимое название строк и столбцов.
С помощью фильтра можно отсортировать необходимые данные, том числе и по цветовому решению таблиц. Например, данные в таблице можно выделить несколькими цветами и применив значение «Цвет элемента» отфильтровать эти данные. Перед сводной таблицей появится фильтр из значений которого можно будет выбрать необходимый цвет.
Наименования столбцов и названия строк служат таким же инструментом для создания фильтров. Задать можно как одно, так и несколько значений, по которым необходимо осуществить поиск информации. При работе с числовыми значениями пригодится функция «Суммирования значений». С её помощью можно сводить итоги выбранных позиций.
Создание сводной таблицы Excel — алгоритм для новичков
Excel как программа работы с таблицами выпускалась и функционирует в нескольких версиях. Пользуется популярностью версия 2007 г. и 2016 г., хотя при переходе от предыдущих версий для некоторых пользователей функционал видится несколько непривычным. Но вне зависимости от того, в какой версии программы работает пользователь, создать сводную таблицу не трудно даже новичкам. Рассмотрим процесс создания сводной таблицы.
Сводная таблица строится на основе данных, которые уже имеются в распоряжении пользователя. Такие таблицы могут быть очень объёмными, особенно если содержат сведения за большой промежуток времени, например несколько месяцев или лет.
В качестве примера рассмотрим алгоритм создания сводной таблицы из продаж кафе за неделю. Исходные данные — таблица с данными продаж за семь дней. При открытии программы выбиваем функцию «Вставка», где из выпадающего меню указываем «Сводная таблица». При её создании необходимо указать ту таблицу, где содержатся нужные данные или диапазон ячеек.
Если искомая таблица содержится в другом файле Excel, то этот файл будет внешним источником данных. Создаваемая таблица может располагаться как в отдельном файле, так и в том же где находится первоначальная таблица. Это пользователь также может выбрать в процессе создания.
Основное условие для исходной таблицы — все ячейки, в которых содержатся необходимые данные должны быть озаглавлены. А попадающие для анализа данные ячеек и столбцов не должны быть пустыми.
Выполнив перечисленные действия, пользователь получает шаблон сводной таблицы, где может настраивать нужные ему строки и столбцы, фильтры. Сведения в текстовом формате будут служить объединяющим элементом, на основе которого можно построить выборку.
Пластичность созданной сводной таблицы позволяет пользователю свободно перемещать строки и столбцы, менять их местами, раздвигать границы ячеек. Функции, которые доступны в созданной таблицы позволяют не только суммировать имеющиеся значения, но и выбирать максимальное и минимальное число, либо отдельные значения. Все зависит от той задачи, которая стоит перед пользователем.
Обновление данных в сводной таблице в Excel
Как инструмент — сводная таблица хороша тем, что данные, используемые в ней можно обновлять. Обновление можно проводить в ручном режиме, если это одно или несколько значений и в автоматическом. Автоматическая настройка актуальна в том случае, если первоначальным источником сводной таблицы служат базы данных, в том числе из внешних программ или они обновляются в постоянно режиме.
Ручное обновление данных в версии 2010 года и более новых версиях имеет ряд незначительных нюансов. При обновлении в более новых версиях достаточно щелкнуть кнопкой мыши на любое свободное место в таблице, где откроется меню «Работа со сводными таблицами». Необходимо выбрать пункт «Анализ» и в нём команду «Обновить». Процесс обновления можно проследить в процентах или отменить.
В версии программы 2010 г. процесс обновления данных может проводиться в отдельном выбранном стебле или во всех стеблях книги. При изменении данных, содержащихся в первоисточнике, сведения в сводной таблице можно обновить и повторно провести необходимые расчеты.
Кроме обновления функционал программы позволяет в случае необходимости добавить новый столбец в сводной таблице. Для чего в исходных данных нужно добавить требуемую информацию. В сводной таблице во вкладке «Анализ» выбрать «Источник». И уже в «Источнике» определить диапазон.
Как сделать сводную таблицу в Excel из нескольких листов
Если в распоряжении пользователя не одна таблица с данными, по которым необходимо провести анализ, а несколько то в этой ситуации можно создать сводную таблицу, состоящую из нескольких листов.
Для этого достаточно воспользоваться функцией «Мастер сводных таблиц». При выборе таблицы определяем первую используемую, вызываем команду «Мастер сводных таблиц», отмечаем необходимость использования нескольких диапазонов, указываем все нужные диапазоны. Сведения, содержащиеся в каждой таблице для удобства работы с ними желательно переименовать.
Сводная таблица из нескольких листов достаточно объемная и её создание актуально в том случае если предстоит провести аналитическую работу в отношении однородных данных за определенный временной промежуток. Располагать получившуюся сводную таблицу лучше на отдельном листе.
Изменение структуры отчёта
Кроме перечисленных функций программа предлагает возможность изменения структуры отчёта. При рассмотрении примера создания сводной таблицы за основу брался предлагаемый программой шаблон. При решении не стандартных задач можно использовать макет отчёта, который позволит задать необходимую конфигурацию данных.
Во вкладке «Конструктор» доступна функция «Макет отчёта», которая делится на три варианта:
- сжатая;
- структурированная;
- табличная.
Сжатый вариант — основной, используемый программой при построении сводных таблиц. Он является таковым по умолчанию.
Структурированный предполагает, что у каждого значения будет своё поле. Табличный вариант в целом аналогичен структурированному, однако промежуточные итоги располагаются внизу таблицы.
В «Макете отчета» есть возможность отметить необходимость повторения подписей всех элементов либо отменить их наличие.
Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel
При работе с книгами Excel и построении сводных таблиц может возникнуть ошибка, которая не позволяет завершить операцию. Основных причин может быть две и обе они зависят стороннего файла, с которыми работает пользователь.
Первая — если в процессе создания сводной таблицы используются данные из другого файла Excel, в наименовании которого содержатся квадратные скобки. Для того чтобы устранить эту причину, достаточно из наименования файла убрать скобки.
Вторая — используемый для работы файл, взят пользователем из Internet Explorer, что также порождает возникновение ошибки «Недопустимая ссылка на источник данных». Решением проблемы, как и первом случае, будет небольшая корректировка используемого файла, а точнее места его нахождения. Достаточно сохранить используемый файл и работу с ним можно продолжить.