Не удается открыть исходный файл сводной таблицы Excel

Обновлено: 21.11.2024

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

Вы можете открыть файл Excel со сводными таблицами в Numbers на iPhone, iPad и Mac:

Чтобы открыть файл Excel с помощью Numbers на iPhone или iPad, коснитесь файла в диспетчере электронных таблиц в Numbers. Если вы не видите диспетчер электронных таблиц, нажмите кнопку «Назад» (на iPhone) или «Таблицы» (на iPad), а затем коснитесь файла Excel, который хотите открыть.
Чтобы открыть файл Excel с помощью Numbers на Mac, перетащите файл Excel на значок Numbers в Dock или в папку «Программы». Или выберите «Файл» > «Открыть» в строке меню Numbers, выберите файл и нажмите «Открыть».

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

Если сводная таблица выглядит иначе

При открытии файла Excel в Numbers открывается сводная таблица, но эти элементы могут выглядеть иначе:

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

Если некоторые элементы не включены

При открытии файла Excel в Numbers открывается сводная таблица, но в нее не включаются следующие элементы:

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

Если сводная таблица преобразована в стандартную таблицу

Numbers автоматически преобразует сводную таблицу в стандартную, если сводная таблица Excel содержит следующие элементы:

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

Подробнее

Информация о продуктах, не производимых Apple, или независимых веб-сайтах, не контролируемых и не тестируемых Apple, предоставляется без рекомендации или одобрения. Apple не несет ответственности за выбор, работу или использование сторонних веб-сайтов или продуктов. Apple не делает никаких заявлений относительно точности или надежности сторонних веб-сайтов. Свяжитесь с поставщиком для получения дополнительной информации.

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

ПРИМЕЧАНИЕ. Макросы, помогающие управлять исходными данными, можно найти на странице макросов исходных данных сводной таблицы. Чтобы макрос развернул исходные данные, перейдите на страницу "Развернуть макрос данных Excel".

Найти и исправить исходные данные

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

ПРИМЕЧАНИЕ. В качестве долгосрочного решения используйте именованную таблицу Excel в качестве источника данных. Он будет корректироваться автоматически при добавлении новых строк.

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

Найдите исходные данные

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

Выполните следующие действия, чтобы найти исходные данные для сводной таблицы:

Выберите любую ячейку в сводной таблице.
На ленте на вкладке "Инструменты для сводных таблиц" нажмите вкладку "Анализ" (в Excel 2010 нажмите вкладку "Параметры").
В группе "Данные" щелкните верхнюю часть команды "Изменить источник данных".

Откроется диалоговое окно «Изменить источник данных сводной таблицы», и вы увидите исходную таблицу или диапазон в поле «Таблица/диапазон». Это может быть ссылка на лист и диапазон ячеек, например

или имя таблицы, например

За диалоговым окном вы можете видеть исходный диапазон на листе, окруженный движущейся рамкой.

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

Настройка диапазона исходных данных

Если исходные данные сводной таблицы являются статической ссылкой на конкретный лист и диапазон, они не корректируются автоматически при добавлении новых данных. На приведенном выше снимке экрана диапазон источников данных относится к Orders!$A$1:$H$9, и его можно настроить вручную, включив в него строку 10.

Чтобы вручную настроить диапазон статического источника:

В окне «Изменить источник данных сводной таблицы» введите новую конечную строку в ссылке на диапазон — в этом примере измените значение с «Заказы!$A$1:$H$ 9» на «Заказы!$A$1:$H$ 10<». /li>
Нажмите "ОК", чтобы закрыть окно.

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

Изменить исходные данные

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

ПРИМЕЧАНИЕ. После перехода к другой исходной таблице может потребоваться переименовать некоторые поля или добавить другие поля в сводную таблицу.

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

Выберите любую ячейку в сводной таблице.
На ленте на вкладке "Инструменты для сводных таблиц" нажмите вкладку "Анализ" (в Excel 2010 нажмите вкладку "Параметры").
В группе "Данные" щелкните верхнюю часть команды "Изменить источник данных".
Откроется диалоговое окно «Изменить источник данных сводной таблицы», и вы увидите исходную таблицу или диапазон в поле «Таблица/диапазон». На этом снимке экрана ниже текущим источником данных является таблица Sales_Data, которая содержит данные из обоих регионов.

Динамический источник для сводной таблицы

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

Динамический источник – таблица Excel

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

Запишите название таблицы, чтобы использовать ее в качестве источника сводной таблицы

Динамический источник — именованный диапазон

Если вы не можете использовать таблицу Excel в качестве исходных данных, вы создаете динамический именованный диапазон на основе формулы ИНДЕКС или СМЕЩ. Здесь есть письменные инструкции, или посмотрите видео ниже.

В видеоролике показано, как создать динамический диапазон с помощью функции СМЕЩ в Excel 2007, и шаги аналогичны в более поздних версиях.

ПРИМЕЧАНИЕ. Запомните название диапазона, чтобы использовать его в качестве источника сводной таблицы

Динамический источник — динамический массив

Если у вас есть версия Excel, поддерживающая новые функции, такие как СОРТИРОВКА или УНИКАЛЬНОСТЬ, вы можете использовать эти функции для создания динамических массивов. В этом методе используются только формулы, без макросов.

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

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

Затем вы можете создать сводную таблицу только на основе этих отфильтрованных данных.

Использовать динамический источник для сводной таблицы

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

Выберите любую ячейку в сводной таблице.
На ленте на вкладке "Инструменты для сводных таблиц" нажмите вкладку "Анализ" (в Excel 2010 нажмите вкладку "Параметры").
В группе «Данные» щелкните верхнюю часть команды «Изменить источник данных».
В диалоговом окне "Изменить источник данных сводной таблицы" снимите флажок "Таблица/диапазон".
В поле Таблица/диапазон введите имя созданной таблицы или именованного диапазона. В этом примере динамическим источником является именованная таблица — tblOrders.
Нажмите "ОК".

Сохранить исходные данные с помощью сводной таблицы

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

У обоих вариантов есть свои преимущества и недостатки:

Если вы сохраните исходные данные:

Файл будет больше
Файл может открываться быстрее

Если вы не сохраните исходные данные:

Заливка будет меньше
Файл может открываться медленно во время перестройки сводного кеша
Сводная таблица должна обновляться после открытия файла

Как изменить настройку

Чтобы включить или отключить параметр "Сохранить исходные данные":

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

Обновлять данные при открытии

Если вы решите отключить параметр «Сохранить исходные данные в файле», вам следует включить параметр «Обновлять данные при открытии файла». Этот параметр находится чуть ниже флажка "Сохранить исходные данные".

В противном случае вы увидите сообщение при попытке отфильтровать данные или внести любые другие изменения макета.

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

Если вы видите это сообщение, нажмите "ОК", а затем вручную обновите сводную таблицу.

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

Восстановить исходные данные сводной таблицы

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

ПРИМЕЧАНИЕ. Это не будет работать для всех сводных таблиц, но стоит попробовать, чтобы восстановить исходные данные.

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

Исправить извлеченные данные

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

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

Подключение к извлеченным данным

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

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

Загрузить образец файла

Пример сводного источника. Чтобы следовать инструкциям, вы можете загрузить файл DateAmt.zip. Заархивированный файл имеет формат xlsx и не содержит макросов.

Пример отфильтрованных исходных данных. Этот пример относится к версиям Excel с новыми функциями, такими как СОРТИРОВКА и УНИКАЛЬНОСТЬ. Загрузите этот образец файла, чтобы увидеть, как сводная таблица создается только из видимых строк в отфильтрованной таблице Excel.Заархивированный файл имеет формат xlsx и не содержит макросов.

Как большой поклонник Excel, я перешел на Excel 2016, чтобы попробовать новую версию.

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

Не удается открыть исходный файл сводной таблицы

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

Перед перемещением сводных таблиц и диаграмм:

Изменить источник данных

После перемещения сводной таблицы или диаграммы ссылка содержит имя файла:

Изменить абсолютную ссылку на источник данных

И это только начало… 🙂

Файл непригоден для использования, каждый раз, когда пользователь меняет имя, сводная таблица не обновляется.

Я попробовал следующие решения, но безрезультатно:

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

После 2 дней работы я, наконец, сузил источник ошибки…

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

файл excel был получен от другого человека, и он был создан в предыдущей версии excel, это один из случаев, когда параметр в меню «Параметры Excel» — «Центр управления безопасностью» — «Параметры конфиденциальности»: «Удалить личную информацию из свойств файла на сохранить” установлен флажок.
файл содержит сводную таблицу со сводной диаграммой или без нее.
сводная диаграмма или сводная таблица (или и то, и другое) перемещается на другой лист (диаграмма с вырезанием-вставкой, сводная с параметром – Переместить сводную таблицу)

Ошибка вызвана Инспектором документов…

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

Скопируйте рабочий лист со старой сводной таблицей и сводной диаграммой в другой рабочей книге.
Переместите скопированный лист обратно в исходную книгу;
Измените исходные данные новой диаграммы на новую сводную таблицу;
Измените источник данных сводной таблицы на новый диапазон;
Обновите сводную таблицу.

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

Мое решение на самом деле является хаком, потому что нет простого способа исправить книгу…

В предыдущих версиях Microsoft Office файлы, созданные в Microsoft Office Excel, Microsoft Office PowerPoint и Microsoft Office Word, сохранялись в собственном формате одного файла; они были известны как бинарные файлы.

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

Чтобы просмотреть структуру файла Excel, вы можете изменить расширение файла с .xlsx на .zip (игнорируйте предупреждающее сообщение) и просто дважды щелкните файл, чтобы открыть его; не забудьте вернуть расширение на .xlsx после того, как закончите редактирование файла.

Или вы можете щелкнуть файл Excel правой кнопкой мыши и открыть с помощью WinRaR, WinZip, 7Zip или любого другого архиватора.

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

xl\pivotCache\_rels , проблема связана с отношениями сводного кэша…

В этой папке должен быть как минимум 1 файл с именем: pivotCacheDefinition1.xml.rels

Если кешей несколько, остальные файлы будут следующими: pivotCacheDefinition2.xml.rels, pivotCacheDefinition3.xml.rels и т. д.

Нормальное содержимое этого файла должно выглядеть так:

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

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

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

Загадка, почему Excel считает, что у нас есть внешнее содержимое, и добавляет связь xlExternalLinkPath/xlPathMissing, а какая связь с Инспектором документов.

Если посмотреть в архиве, то в папке xl\pivotCache есть 2 файла:pivotCacheDefinition1.xml и pivotCacheRecords1.xml.

PivotCacheDefinition1.xml будет содержать следующее выражение (обратите внимание на идентификатор отношения, выделенный красным):

Красная часть никогда не будет найдена в обычном файле, нормальное значение должно быть:

А теперь решение:

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

Шаг 1:

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

Шаг 2:

Откройте архив Excel с помощью архиватора и перейдите в папку xl\pivotCache\_rels.

Из файла pivotCacheDefinition1.xml.rels (и из всех других файлов определения pivotCache) просто удалите красную часть, но ТОЛЬКО красную часть, иначе Excel не сможет ее открыть, она будет повреждена:

Сохраните изменения и закройте файл.

Шаг 3:

Откройте книгу Excel.

Все источники данных сводных таблиц будут иметь эту странную ссылку: ! Таблица данных . Обратите внимание, что ссылка на имя файла отсутствует, но восклицательный знак все еще есть.

Все, что вам нужно сделать, это нажать кнопку СОХРАНИТЬ и закрыть файл! Теперь, когда вы открываете файл, ошибка исчезает навсегда…

Готово!

Идентификатор связи из pivotCacheDefinition1.xml не будет содержать неправильный идентификатор связи r:id="rId2″, Excel позаботился об этом сам и переписал pivotCacheDefinition1.xml.rels.

Примечание:

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

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

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

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

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

Альтернатива:

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

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

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

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

Не удается открыть исходный файл сводной таблицы «[имя_файла[1].xls]Исходные данные’

Это вызвано тем, что IE кэширует потоковый файл с временным именем, содержащим квадратные скобки (например, имя файла[1].xlsx). Поскольку Excel не позволяет источникам данных сводной таблицы содержать скобки, при открытии кэшированного файла сводные таблицы пытаются обновиться, используя имя временного файла.

Есть несколько способов обойти это:

Используйте браузер, отличный от IE, например Firefox или Chrome.
Используйте IE 9 или более позднюю версию – похоже, эта проблема была решена в более новых версиях IE.
Сохраните файл на диск перед его открытием, чтобы не открывать файл с временным именем
Потоковая передача встроенного файла вместо вложения. Это откроет файл в плагине Excel для IE, а не загрузит его. Если вы используете IE 7 или более позднюю версию, вам потребуется настроить реестр на клиенте, чтобы использовать этот параметр.
Добавьте макросы Excel в отчет, чтобы динамически переименовать сводную таблицу, чтобы исключить скобки, а затем обновить сводную таблицу. Пример этих макросов доступен в этой записи блога.

Примечание. Это не проблема продукта OfficeWriter. Это происходит каждый раз, когда кто-то пытается загрузить и открыть книгу Excel со сводной таблицей непосредственно из определенных версий IE.

Мы сделали эти решения доступными в Интернете, потому что наши клиенты сочли эту информацию полезной. Однако на эти решения не распространяется поддержка продуктов OfficeWriter. Если у вас возникли проблемы с реализацией любого из этих решений, мы рекомендуем вам обратиться к соответствующему поставщику (например, Mozilla для Firefox, Google для Chrome и Microsoft для IE 9 (или более поздней версии) или настроить встроенный подключаемый модуль).< /p>

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

Поделиться записью "Ошибка: не удается открыть исходный файл сводной таблицы ‘[filename[x].xls]SourceData’"

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