Ошибка при создании сводной таблицы недопустимая ссылка на источник данных

Проблемы

При открытии книги Excel, содержащей квадратные скобки, в имени (например, «foo [1]») при попытке создания сводных таблиц с использованием данных из книги пользователь получит следующее сообщение об ошибке:ссылка на источник данных недействительна

Причина

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

Решение

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

Дополнительная информация

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

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:

1. Delete brackets from the filename.
2. Save the workbook on your local disk.
3. Make sure that the range exists and is defined.
4. Ensure that the reference for the defined range is valid.
5. 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.

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

«Ссылка на источник данных недействительна».

При подробном исследовании ошибка видна, когда-

Имя файла содержит недопустимые символы. Файл открывается из Internet Explorer.

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

Исправление 1. Удалите недопустимые символы из имени файла.

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

<>? []: | *.

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

Например, если имя файла- Excel [1] , рассмотрите возможность переименования его в Excel_1

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

1. Щелкните правой кнопкой мыши проблемный файл.

2. Выберите Переименовать .

3. Дайте допустимое имя и нажмите Enter .

Теперь проверьте, можете ли вы создавать сводные таблицы.

Исправление 2: Сохраните файлы, открытые в Internet Explorer.

Когда вы открываете файл в Internet Explorer, его копия создается во временном каталоге. Эта копия будет переименована в квадратные скобки. Вы видите ошибку, поскольку имя файла содержит недопустимые символы (квадратные скобки).

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

Вот и все.

Мы надеемся, что эта статья была информативной. Спасибо за чтение.

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

Ануша Пай (Anusha Pai)-инженер-программист с большим опытом работы в ИТ-индустрии и страстным желанием писать.

Pieter van Niekerk

2011-09-01 в 12:16

Я пытаюсь создать пивот из диапазона, но получаю ошибку Data source reference is not valid

Я прогуглил это и попробовал все следующее.

• Выберите из таблицы вместо диапазона.
• Откройте электронную таблицу Excel, нажмите на вкладку просмотра и выберите новое окно. Сохраните электронную таблицу с другим именем для нового окна.
• Нажмите ДОМОЙ> ПОДГОТОВИТЬ> ПРОВЕРЬТЕ ДОКУМЕНТ> Удалены все ненужные ссылки и формы XML.

У меня есть метки на всех столбцах, и все ячейки заполнены.

Я использую Office 2010 на Win7, и файл в формате .xlsx

---

2 ответа на вопрос

Pieter van Niekerk

2011-09-05 в 08:29

Я нашел причину, по которой сводные таблицы не работали, из-за имени файла. Отчеты создаются системой автоматизации отчетов, а имя содержит квадратные скобки, []которые не нравятся Excel.

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

Я также пытался сохранить файл в Save Asопции в квадратных скобках, но, как я уже сказал, Excel стонет об этом и не позволяет вам.

Rich Ellis

2012-12-20 в 22:46

У меня была такая же ошибка, но []в имени файла не было скобок . Я обнаружил, что файл Excel из моего веб-инструмента отчетности на самом деле не был в формате XLSX. Когда я сделал «Сохранить как» в Excel, тип файла был « Веб-страница ». Я изменил это на « Книга Excel (* .xlsx) » и сохранил с тем же именем, и сводная таблица сработала.

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

Сводные таблицы в Excel

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

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

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

Помощь в конструировании окажут имеющиеся функции:

• фильтр отчётов;
• суммирование значений.

А также необходимое название строк и столбцов.

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

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

Создание сводной таблицы Excel — алгоритм для новичков

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

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

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

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

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

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

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

Обновление данных в сводной таблице в Excel

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

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

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

Кроме обновления функционал программы позволяет в случае необходимости добавить новый столбец в сводной таблице. Для чего в исходных данных нужно добавить требуемую информацию. В сводной таблице во вкладке «Анализ» выбрать «Источник». И уже в «Источнике» определить диапазон.

Как сделать сводную таблицу в Excel из нескольких листов

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

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

Сводная таблица из нескольких листов достаточно объемная и её создание актуально в том случае если предстоит провести аналитическую работу в отношении однородных данных за определенный временной промежуток. Располагать получившуюся сводную таблицу лучше на отдельном листе.

Изменение структуры отчёта

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

Во вкладке «Конструктор» доступна функция «Макет отчёта», которая делится на три варианта:

• сжатая;
• структурированная;
• табличная.

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

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

В «Макете отчета» есть возможность отметить необходимость повторения подписей всех элементов либо отменить их наличие.

Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel

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

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

Вторая — используемый для работы файл, взят пользователем из Internet Explorer, что также порождает возникновение ошибки «Недопустимая ссылка на источник данных». Решением проблемы, как и первом случае, будет небольшая корректировка используемого файла, а  точнее места его нахождения. Достаточно сохранить используемый файл и работу с ним можно продолжить.