Office Products Excel 2010 Excel 2016 Excel 2013 Excel 2019 Excel for Microsoft 365 Еще…Меньше
Проблемы
При открытии книги Excel, содержащей квадратные скобки, в имени (например, «foo [1]») при попытке создания сводных таблиц с использованием данных из книги пользователь получит следующее сообщение об ошибке:ссылка на источник данных недействительна
Причина
Причина из-за того, что квадратные скобки являются недопустимыми символами в имени файла книги Excel.
Решение
Это поведение ожидается, если выполняются эти условия. Для решения этой проблемы пользователю потребуется удалить все недопустимые символы Excel из имени файла.
Дополнительная информация
Эта проблема может возблюдаться при попытке открыть книгу Excel из Internet Explorer. Internet Explorer создает копию файла во временной папке, переименование файла с помощью квадратных скобок. Это можно избежать, если нажать кнопку «Сохранить», а не «Открыть», когда Internet Explorer выведет файл пользователю.
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Многие пользователи Excel сообщали об ошибке при создании сводных таблиц в книге Excel. Полное сообщение об ошибке выглядит следующим образом:
"Data source reference is not valid."
При подробном исследовании ошибка видна, когда:
- Имя файла содержит недопустимые символы.
- Файл открывается из Internet Explorer.
Если вы видите эту проблему в своей системе, не паникуйте. В этой статье рассматриваются исправления, позволяющие избежать ошибки «Ссылка на источник данных не является допустимой».
Исправление 1. Удалите недопустимые символы из имени файла.
Наиболее частая причина появления этой ошибки — когда имя файла содержит недопустимые символы. Следующие символы, если они присутствуют в имени файла, недопустимы
< > ? [ ] : | *.
Если вы видите, что ваше имя файла содержит один из этих символов, подумайте об их удалении.
Например, если имя файла — Excel[1], рассмотрите возможность переименования его в Excel_1
Чтобы переименовать файл, сделайте следующее:
1. Щелкните правой кнопкой мыши проблемный файл.
2. Выберите «Переименовать».
3. Дайте допустимое имя и нажмите Enter.
Теперь проверьте, можете ли вы создавать сводные таблицы.
Исправление 2: Сохраните файлы, открытые в 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:
- 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.
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) » и сохранил с тем же именем, и сводная таблица сработала.
Похожие вопросы
-
4
MSFT Excel сводная таблица ссылки на внешние данные
-
4
Excel 2007 | Удалить пустые поля из сводных таблиц
-
3
Как изменить формат группы по данным в сводных таблицах Excel 2003
-
-
1
Внешние данные для сводной таблицы
-
4
Как добавить вычисляемое поле в сводную таблицу, которая показывает разницу между двумя сериями данн…
-
3
Инструмент для автоматического объединения многих больших сводных таблиц во многие большие таблицы E…
-
1
Является ли этот способ использования сводной таблицы Excel 2007 для BI масштабируемым?
-
1
Пользователь Sql Server Compact 3.5 в качестве источника данных в Excel 2007/2010?
-
11
Как «отключить» или «обратный поворот» в Excel?
-
1
Как изменить программу по умолчанию для открытия файлов Excel из Excel 2010 в Excel 2007?
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает #ССЫЛКА!
Автор Silver9, 26.01.2012, 13:19
« назад — далее »
Файл1, в нем сводная таблица. Файл2 (лежит рядом, сначала был в сети..), в нем ссылки сабжевой формулой на сводную таблицу из Файл1. Если открыты оба файла — данные отображаются, если же Файл1 закрыт — получаем результат #ССЫЛКА! 
Второй день яндекс и здешний форум не говорят мне ничего дельного.. Есть подозрение, что эта формула не работает с закрытой книгой, подтверждения этому я не нашел Помогите пожалуйста.
Цитата: Serge 007 от 26.01.2012, 13:48
Цитата: Silver9 от 26.01.2012, 13:19
Есть подозрение, что эта формула не работает с закрытой книгой, подтверждения этому я не нашел
Ваши подозрения необоснованы. Эта формула работает с закрытыми книгами.
Тогда, может быть, подскажете в чем может быть проблема? Офис2007, для СТ Лента-Параметры-Обновить сделал, файлы пересохранил из формата 97-2003 в .xlsx — не помогло. Куда копать еще?
У меня работает. Если подождёте до вечера — выложу видео с экрана.
ЗЫ Единственное что, нельзя эти формулы редактировать при закрытом исходнике.
У меня не работает на нескольких компутерах. Я Вам верю, но спасет ли меня выложенное видео? 
… значит, дело в каких-то настройках Excel’а ? Что скажете?
Цитата: Silver9 от 26.01.2012, 15:02
Что скажете?
Скажу что работало уже около десяти лет в разных версиях Excel, на разных компах и в разных локалях…
ЗЫ Попробуйте сделать следующее (оба файла обязательно в одной папке и не перемещать!):
Откройте оба файла (формула должна показывать значение, а не ошибку), отредактируйте формулу (прибавьте 0 например), сохраните оба файла, закройте. Потом откройте файл с формулой. Что будет ошибка или значение?
Цитата: Serge 007 от 26.01.2012, 15:55
ЗЫ Попробуйте сделать следующее (оба файла обязательно в одной папке и не перемещать!):
…… Потом откройте файл с формулой. Что будет ошибка или значение?
Как лежали, так и лежат — корень диска. (изначально, еще до моих экспериментов, файлы все время лежали на сетевом диске в разных папках. В формулах конечно же был полный путь к файлу с данными)
Все инструкции выполнил. Ошибка по-прежнему
Советую бросить это дело и сделать сводную в файле с формулами (источник можно оставить прежний).
Дольше выяснять будем почему не работает.
… что ж, неплохой вариант. Обсужу с постановщиками задачи.
Спасибо за помощь!
- Профессиональные приемы работы в Microsoft Excel
-
►
Обмен опытом -
►
Microsoft Excel -
►
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает #ССЫЛКА!
|
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
. 
Хотя в данном случае исходные данные роли не играют (исходник вообще можно было не показывать), надо только связать сводную с итогом 
Как отключить ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel
В этой статье я расскажу как отключить функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA в англ. версии) при создании ссылки на ячейку сводной таблицы в Excel.
Сталкивались ли вы с тем, что при создании ссылки на ячейку сводной таблицы Excel создает ссылку типа:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(“Сумма по полю Сумма товара”;$A$3;”Продавец”;”Лабытин”)
Система, вместо того, чтобы сделать прямую ссылку на ячейку типа “ =С4 ” создает ссылку с функцией ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ . Ситуацию усложняет тот факт, что с этой формулой невозможно протянуть формулу на другие ячейки таблицы.
Как включить/выключить функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в сводной таблице
Для отключения функции выполните следующие действия:
- Нажмите левой клавишей мыши на любой ячейке внутри сводной таблицы;
- Перейдите на вкладку “Анализ” на панели инструментов;
- Нажмите на иконку выпадающего списка рядом с кнопкой “Сводная таблица” => пункт “Параметры”;
- В выпадающем списке уберите галочку напротив надписи “Создать GETPIVOTDATA”.
Теперь, при создании формулы, ссылающейся на ячейку сводной таблицы, система будет создавать прямую ссылку типа “ =B7 “. Полученную формулу вы сможете протянуть на другие ячейки.
Отключив автоматическое создание функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ при ссылке на ячейку сводной таблицы, она отключается во всех файлах Excel с которыми вы работаете на данном компьютере. То есть, если вы отправите файл с отключенной функцией на другой компьютер, там также будут работать и создаваться ссылки типа ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Аналогом функции ВПР для сводных таблиц является функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которая возвращает данные, хранящиеся в отчете сводной таблицы.
Чтобы получить быстрый доступ к функции, необходимо ввести знак равенства в ячейку (=) и выделить необходимую ячейку в сводной таблице. Excel сгенерирует функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ автоматически.
Отключение создания GetPivotData
Чтобы отключить автоматическую генерацию функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, выберите любую ячейку в сводной таблице, перейдите по вкладке Работа со сводными таблицами -> Параметры в группу Сводная таблица. Щелкните по стрелке вниз, находящейся рядом с вкладкой Параметры. В выпавшем меню, уберите галку с пункта Создать GetPivotData.
Теперь, при ссылке на ячейку, находящуюся в сводной таблице, Excel будет генерировать адрес ячейки.
Использование ссылок на ячейки в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Вместо указания названия пунктов или полей в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, вы можете ссылаться на ячейки, находящиеся на листе. В примере ниже ячейка E3 содержит название продукта, а формула в ячейке E4 ссылается на нее. В результате будет возвращен суммарный объем по тортам.
Использование ссылок на поле сводной таблицы
Вопросов по работе ссылок на пункты сводной таблицы нет, проблемы возникают, если мы захотим сослаться на поле данных.
В примере ячейка E3 содержит название поля данных «Количество», и было бы неплохо ссылаться на эту ячейку в функции, вместо того, чтобы иметь название поля в формуле ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
Однако, если мы поменяем первый аргумент поле_данных на ссылку на ячейку E3, Excel вернет нам ошибку #ССЫЛКА!
Проблему решит простое добавление пустой строки (“”) в начало или конец ссылки на ячейку.
Простая коррекция формулы приведет к возврату правильного значения.
Использование дат в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Если вы используете даты в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, у вас могут возникнуть проблемы, даже если дата отображается в сводной таблице. К примеру, аргументом формулы ниже является дата “21/04/2013”, и сводная таблица содержит поле с датами продаж. Однако формула в ячейке E4 возвращает ошибку.
Для предотвращения ошибок, связанных с датами, вы можете воспользоваться одним из следующих способов:
- Сравнять форматы дат в формуле и сводной таблице
- Использовать функцию ДАТАЗНАЧ
- Использовать функцию ДАТА
- Сослаться на ячейку с корректной датой
Сравнять форматы дат в формуле и сводной таблице.
Для получения корректного результата, во время использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, убедитесь, что форматы дат аргумента формулы и сводной таблицы одинаковые.
В ячейке E4, в формуле использована дата формата “ДД.ММ.ГГГГ”, и в результате возвращена правильтая информация.
Использование функции ДАТАЗНАЧ
Вместо ручного ввода даты в формуле, можно добавить функцию ДАТАЗНАЧ для возврата даты.
В ячейке E4, дата введена с помощью функции ДАТАЗНАЧ, и Excel возвращает необходимую информацию.
Использование функции ДАТА
Вместо ручного ввода даты в формуле, можно воспользоваться функцией ДАТА, которая позволит корректно вернуть необходимую информацию.
Ссылка на ячейку с датой
Вместо ручного ввода даты в формуле, можно сослаться на ячейку, содержащую дату (в любом формате, в котором Excel воспринимает данные, как даты). В примере в ячейке E4, формула ссылается на ячейку E3 и Excel возвращает корректные данные.
Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Предположим, что из вот такой базы данных по продажам:
. вы создали небольшую, но симпатичную сводную таблицу:
Но ваш руководитель хочет не совсем ее, а что-то похожее на:
То есть имеем несколько ощутимых трудностей:
- Исходный внешний вид сводной таблицы не подходит — дизайн отчета должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). «Дорабатывать напильником» дизайн сводной — долгий и мучительный процесс. И не факт, что красота не слетит после пересчета и обновления.
- Из всей сводной для отчета вам нужны не все данные, а только конкретные модели Ford по Питеру — придется руками фильтровать.
- Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зеленых ячейках, но среднее по месяцу в итогах — сводная так не умеет.
- Полученные в сводной результаты — еще не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе (особенно для сводных на основе OLAP-кубов) или делается, но «через одно место» с помощью вычисляемых полей и объектов.
- Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).
Изящным решением всех этих проблем может стать функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) , которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах.
Сделаем на отдельном от сводной листе заготовку отчета:
Выделите первую ячейку зеленого диапазона, введите знак «равно» и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля «морской бой» Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:
Давайте разберем ее подробно:
- Первый ее аргумент («Выручка») — это имя извлекаемого поля.
- Второй (Лист1!$A$4) — это адрес первой ячейки сводной таблицы, откуда мы берем данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать из какой именно нужно вытащить число.
- Все остальные аргументы начиная с третьего — это попарно название поля и его значение, т.е., в нашем случае, это имя модели (Наименование=»Fiesta») и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто «январь».
А теперь самое интересное.
Аккуратно замените в формуле «Fiesta» на $С7, а единичку на D$5 и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зеленые ячейки.
Теперь с данными в зеленых ячейках можно работать как с обычными формулами, а не как со сводной таблицей с ее жесткими ограничениями, т.е. дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д.
Если исходные данные поменяются, то нужно будет лишь обновить сводную (правой кнопкой мыши — Обновить). Даже если завтра изменится количество моделей или, скажем, Fiesta будет уже не третьей по счету машиной в сводной, наша функция все равно ее найдет и правильно извлечет соответствующий ей результат для нашего отчета.
Примеры работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel предназначена для получения доступа к полям данных сводных таблиц и возвращает данные в соответствии с запросом (формируется на основе переданных в данную функцию аргументов).
Пример как использовать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel
Пример 1. В таблице Excel содержатся данные о поступлениях бытовой техники различного типа и от разных производителей на склад интернет-магазина по номеру дня. Создать сводную таблицу на основе существующей, получить данные о количестве полученных ноутбуках фирмы Samsung и их общей стоимости с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
Создадим сводную таблицу на новом листе, которая категорирует данные по типу техники и фирме-производителе, и содержит данные о количестве поступивших товаров и их общей сумме:
Для получения искомых данных можно вручную ввести следующую функцию:
- «Количество» – поле сводной таблицы, данные из которого требуется получить;
- A4:C15 – диапазон ячеек, в которых находится сводная таблица;
- «Наименование»;»Ноутбук»;»Фирма»;»Samsung» – характеристика получаемых данных, на основании которой производится поиск требуемых данных.
Как видно, результат совпадает со значением, хранящимся в сводной таблице. Для получения значения поля «Сумма» воспользуемся более удобным способом получения данных, когда рассматриваемая функция генерируется автоматически. Для этого выделим ячейку G8, вставим символ «=» и выделим ячейку B7:
Данная формула была сгенерирована автоматически. Полученный результат:
С помощью данной формулы мы имеем возможность получить доступ к любым значениям полей сводной таблицы Excel.
Выборка данных из сводной таблицы с помощью формулы Excel
Пример 2. На основании данных из первого примера необходимо определить, насколько количество привезенных на склад телевизоров Samsung превышает количество телевизоров LG, а также разницу их общей стоимости.
При работе с исходной таблицей нужно было бы отыскать данные для каждого поступления требуемой техники, что потребовало бы огромные временные затраты, если таблица содержала, например, данные за последний год. Благодаря использованию сводной таблицы и рассматриваемой функции расчет сводится к простым формулам:
Формула 1 для разницы количества:
Формула 2 для разницы общей суммы:
К числу достоинств данной функции относится не только возможность получения результатов в любом удобном представлении. Все полученные значения находятся в динамической зависимости с исходной таблицей, то есть будут обновляться при внесении изменений в исходную таблицу.
Формула для анализа данных полученных из сводной таблицы Excel
Пример 3. В таблицу Excel выгружены статистические данные сайта в виде таблицы с полями «День», «Логин», «Страна» и «Время активности». Определить среднее время активности на сайте за исследуемый период для пользователей из каждой страны, вычислить наибольшее среди полученных значений.
Создадим сводную таблицу на новом листе и добавим поля для отображения среднего значения времени активности на сайте:
Произведем расчет для пользователя из GB (Великобритания):
Формула выглядит очень громоздкой, однако ее аргументы генерируются автоматически при выборе соответствующих ячеек. Итоговые результаты расчета:
Определим наибольшее значение с помощью соответствующей формулы:
Как видно, на сайте в среднем больше времени проводили пользователи из UA (Украины).
Особенности использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel
Рассматриваемая функция имеет следующий синтаксис:
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( поле_данных; сводная_таблица ; [поле1; элем1; поле2; элем2]; …)
Описание аргументов (первые два являются обязательными для заполнения):
- поле_данных – аргумент, характеризующий имя поля данных, в котором содержатся данные для извлечения. Должен быть представлен текстовой строкой, например «Покупки».
- сводная_таблица – аргумент, принимающий ссылку на всю сводную таблицу, либо на некоторый диапазон ячеек, содержащийся в ней. Необходим для определения сводной таблицы, данные из которой требуется получить.
- [поле1; элем1; поле2; элем2]; … — необязательные аргументы, характеризующие данные, которые необходимо получить. Функция принимает до 126 пар имен полей и элементов. Имена элементов необходимо заключать в кавычки (исключением являются данные числового типа и даты).
- В качестве аргумента сводная_таблица может быть передан диапазон ячеек, который включает сразу несколько сводных таблиц. В этом случае функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будет извлекать данные из созданной последней таблицы.
- Если аргумент сводная_таблица принимает диапазон пустых ячеек, рассматриваемая функция вернет код ошибки #REF!.
- Пара поле1; элем1, ссылающаяся на одну и ту же ячейку, вернут данные из данной ячейки, которые могут быть любого типа, включая код ошибки.
- Код ошибки #ССЫЛКА! Будет возвращен в случае, если аргументы функции указывают на невидимое поле или содержат фильтр, не отображающий данные согласно установленным им условиям.
- Для корректного отображения данных в формате Время и Дата необходимо выполнять прямое преобразование (использовать функции ДАТА, ДАТАЗНАЧ и ВРЕМЯ).
- Сводные таблицы используются для создания удобочитаемого отчета на основе данных из имеющейся громоздкой таблицы с большим количеством полей данных.
- В Excel реализован визуальный интерфейс создания сводных таблиц, который делает данный процесс простым и наглядным. Однако алгоритм форматирования таких таблиц не является достаточно гибким, поэтому зачастую не удается достичь ожидаемого результата.
- Один из специалистов Microsoft предложил новый метод, согласно которому созданная сводная таблица является не окончательным действием, а лишь промежуточным этапом при создании отчетов. Требуется самостоятельно создать оболочку итогового отчета, которая затем будет заполнена данными из сводной таблицы с использованием рассматриваемой функции. При этом сводная таблица может иметь примитивный вид, не требует форматирования и может находиться на скрытом листе в качестве невидимой базы данных.
- При необходимости можно отключить функционал автоматического генерирования рассматриваемой функции. Для этого в параметрах сводных таблиц необходимо снять флажок перед «Создать GetPivotData».
- Синтаксис функции достаточно сложный, поэтому чтобы упростить работу с ней, можно выполнить следующие действия:
- выделить пустую ячейку и ввести символ «=»;
- выделить поле данных с требуемой информацией в сводной таблице;
- функция с требуемыми аргументами сгенерируется автоматически.
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ)
Функция GetPivotData Возвращает видимые данные из сводной таблицы.
В этом примере = GETPIVOTDATA («продажи»; a3) возвращает общую сумму продаж из сводной таблицы.
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элем1; поле2; элем2]; …)
Аргументы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ описаны ниже.
Имя поля сводной таблицы, содержащего данные, которые требуется извлечь. Это должно быть в кавычках.
Ссылка на ячейку, диапазон ячеек или именованный диапазон ячеек в сводной таблице. Эти сведения используются для определения сводной таблицы, содержащей данные, которые необходимо извлечь.
field1, Item1, поле2, элем2.
От 1 до 126 пар имен полей и элементов, описывающих данные, которые необходимо извлечь. Они могут следовать друг за другом в произвольном порядке. Имена полей и имена для элементов, отличных от даты и чисел, должны быть заключены в кавычки.
Для сводных таблиц OLAPэлементы могут содержать исходное имя измерения, а также исходное имя элемента. Пара «поле-элемент» для сводной таблицы OLAP может выглядеть следующим образом:
Можно быстро ввести простую формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, введя = (знак равенства) в ячейке, в которой должно быть возвращено значение, и затем щелкнув ячейку в сводной таблице, содержащей необходимые данные.
Вы можете отключить эту функцию, выбрав любую ячейку в существующей сводной таблице, а затем перейдите на вкладку анализ сводной таблицы > Параметры > сводной таблицы > снимите флажок Создать GetPivotData .
Вычисляемые поля, элементы и пользовательские вычисления можно включить в расчеты в GETPIVOTDATA.
Если аргумент pivot_table является диапазоном, содержащим две или несколько сводных таблиц, данные будут извлекаться из какой-либо сводной таблицы, созданной последними.
Если аргументы поля и элемента описывают одну ячейку, возвращается значение этой ячейки независимо от того, является ли она строкой, числом, ошибкой или пустой ячейкой.
Если аргумент «элемент» содержит дату, необходимо представить это значение как порядковый номер или воспользоваться функцией ДАТА, чтобы это значение не изменилось при открытии листа в системе с другими языковыми настройками. Например, элемент, ссылающийся на дату 5 марта 1999 г., можно ввести двумя способами: 36 224 или ДАТА(1999;3;5). Время можно задать в виде десятичных значений или с помощью функции ВРЕМЯ.
Если аргумент pivot_table не является диапазоном, в котором найдена Сводная таблица, функция GETPIVOTDATA возвращает #REF!.
Если аргументы не описывают видимое поле или содержат фильтр отчета, в котором не отображаются отфильтрованные данные, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает значение ошибки #ССЫЛКА!. значение ошибки.
В формулах в приведенном ниже примере показаны различные методы для считывания данных из сводной таблицы.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).