Как скопировать сводную таблицу в Excel на новый лист без ссылок

Обновлено: 21.11.2024

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

Без форматирования

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

Использовать буфер обмена

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

Результат выглядит как исходная сводная таблица без ссылки на исходные данные.

Дополнительную информацию о стилях сводных таблиц и форматировании сводных таблиц см. на странице Формат сводных таблиц Contextures.

Видео о копировании формата сводной таблицы и значений

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

25 мыслей о «Копировать формат и значения сводной таблицы»

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

Андреа, вы используете Excel 2003? Форматирование исчезает при вставке как значения в более поздних версиях.

Действительно, я делаю Дебру. Спасибо, что указали на это.

Андреа, тебе повезло! Они испортили эту функцию в Excel 2007, так что сейчас это сделать намного сложнее.

Я всегда поступал так…

1)Скопируйте сводную таблицу
2)Вставьте значения (с помощью специальной вставки)
3)Затем вернитесь к специальной вставке и выберите формат вставки

Результат тот же

Надеюсь, это поможет

Спасибо, Джон, но это не работает в Excel 2007 или более поздних версиях для копирования стилей сводной таблицы.

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

Очень жаль. Можно легко исправить, но зачем это делать, если можно просто все испортить?

Это отличный совет, и я использовал его несколько раз.

Однако сделать это с помощью VBA, похоже, не получится. Если вы запишете, как это делаете, вы получите:

но когда вы пытаетесь запустить этот код снова, он перестает работать со строкой .Paste (я думаю, потому что он видит буфер обмена Excel пустым после первой строки??).

Это остается королевским PITB при автоматизации публикации отчетов из сводных таблиц.

У кого-нибудь есть идеи?

Ярд, я опубликовал код, который копирует значения и форматирование сводной таблицы на новый лист:

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

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

У меня есть вопрос — как насчет вставки сводной таблицы в Word с тем же форматированием — знак с подкатегориями — например, у меня есть список отделов, которые привлекли 7 поставщиков — и есть 18 отделов, в каждом из которых задействованы разные поставщики, как найдено Pivot стол . как я могу вставить исходное форматирование в целевой (таблица документов Word с
-Department
vendor1
vendor2
vendor3, что мой босс точно знает, что в каждом отделе есть несколько поставщиков впереди об этом в слове. 🙂

Здравствуйте! Просто добавьте еще одно поле в конец сводной таблицы и выберите всю сводную таблицу, кроме последнего столбца (только что добавленного), нажмите «Копировать» и просто нажмите «Вставить» в другую таблицу Excel. Он копирует числа и формат без каких-либо ссылок на старую таблицу.
По сути, вы не копируете всю таблицу. Просто случайно наткнулся на это решение.
Надеюсь, это сработает для вас..

Хм, только что попробовал вставить из буфера обмена (Excel 2010), но не получил форматирования… есть идеи?

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

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

Привет,
Кто-нибудь знает, как вернуть сворачиваемые кнопки в ячейки после вставки сводной таблицы? Я работаю в версии 2010, и моя сводная таблица хорошо заклеена, но я хотел включить кнопки в ячейки, чтобы человек, которому я отправляю ее, мог минимизировать данные по своему усмотрению.
Спасибо!

Круто..
облегчил мне жизнь!

В Excel 2010 я нашел способ просто вставить данные и отформатировать их без ссылки на источник сводных данных.
Если вы попытаетесь скопировать всю сводную таблицу, это не сработает, и ссылка на источник данных будет скопирована вместе с форматированием.
Чтобы вставить только значения и форматирование, поместите курсор в первую ячейку строки «Ярлыки строк» ​​сводной таблицы. Нажмите на эту ячейку, а затем прокрутите вниз до общей цифры и, удерживая клавишу Shift, щелкните эту ячейку, чтобы определить данные для копирования. Теперь область, которую вы хотите скопировать, должна быть выделена. Вызовите функцию копирования или используйте Ctrl+C. Перейдите к новому рабочему листу или области, куда вы хотите вставить данные, и щелкните ячейку, куда вы хотите их вставить. «Вставить» или Ctrl+V (обычный значок вставки) на новый рабочий лист или в новую область. Табличные данные и форматирование вставляются без ссылки на источник данных.

Когда я копирую и вставляю существующую таблицу Excel в документ Word, таблица искажается, и последний столбец отсутствует.
Есть ли функция для вставки существующей таблицы Excel в текстовый документ?

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

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

Создание сводной таблицы

Чтобы вставить сводную таблицу, нам нужно перейти на вкладку «Вставка». Там мы найдем раздел «Таблицы», из которого мы можем выбрать нашу сводную таблицу.

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

Теперь мы создадим нашу сводную таблицу, выбрав все наши данные (наш диапазон), а затем выбрав Insert-Pivot Table. Появится всплывающее окно, похожее на изображение ниже:

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

В этом примере мы создали лист с названием "Сводная таблица" и скопировали данные на этот лист.

Чтобы окончательно создать нашу таблицу, мы отфильтруем некоторые данные.

Стандартная сводная таблица состоит из четырех областей:

  • Столбцы. Поле, используемое для измерения и сравнения данных.
  • Строки: поле для данных, которые вы хотите проанализировать.
  • Значения. Поле, содержащее значения, используемые таблицей для сравнения.
  • Фильтр (необязательно): поле, используемое для сортировки данных таблицы.

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

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

Наша таблица выглядит так:

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

Затем вы выбираете «Анализ», нажимаете «Изменить источник данных» и изменяете диапазон своей таблицы. Наконец, вы нажимаете «Обновить», чтобы применить изменения. Вы также можете использовать Обновить, если вы изменили данные в исходной таблице. В этом случае вам не нужно менять источник данных.

Копировать сводную таблицу

Первый и самый простой способ скопировать сводную таблицу — выбрать все данные и просто вставить таблицу. Это можно сделать с помощью известных сочетаний клавиш: CTRL+C для копирования и CTRL+P для вставки.

Таким образом, вы получите полностью такую ​​же таблицу, как исходная:

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

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

Вы создадите другую таблицу с теми же исходными данными, если воспользуетесь следующими параметрами:

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

Все ваши параметры можно увидеть, скопировав диапазон и щелкнув правой кнопкой мыши ячейку, в которую вы хотите скопировать данные. Затем вам нужно щелкнуть значок «Специальная вставка» в параметрах вставки (рисунок ниже):

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

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

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

  • Вставить как значения
  • Вставить, но сохранить форматирование значений и чисел.
  • Вставить, но сохранить значения и исходное форматирование.

Как и в случае с параметром Transpose, с этим параметром мы копируем только числа, а не исходную таблицу и наш сводной кэш.

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

Если мы нажмем кнопку «Специальная вставка» (рисунок ниже)

Появится всплывающее окно, как на картинке ниже:

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

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

К счастью, есть решение этой проблемы. Для этого мы используем буфер обмена.

Буфер обмена позволяет копировать до 24 элементов и вставлять их в другой документ Office.

В Excel значок буфера обмена находится в нижней части вкладки "Главная", как показано на рисунке ниже.

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

В левой части экрана появится окно:

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

Наши данные будут скопированы со всеми форматами и стилями, но без копирования нашего сводного кэша и без занимаемого нами драгоценного пространства.

сообщить об этом объявлении

Мы можем скопировать и вставить сводную таблицу в Excel с помощью сочетания клавиш, например CRTL+ALT+V . С помощью этого ярлыка мы можем предотвратить разглашение конфиденциальной информации нашими исходными данными. Шаги, описанные ниже, помогут пройти весь процесс.

Рисунок 1. Копирование сводной таблицы в Excel

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

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

Рисунок 2. Настройка данных

Копирование и вставка данных без CRTL+ALT+V

  • Мы скопируем данные сводной таблицы, нажав на таблицу, нажав CTRL+A, а затем CRTL+C.

Рисунок 3. Копирование данных сводной таблицы

  • Мы нажмем CTRL+N, чтобы открыть новую книгу.
  • Мы щелкнем любую ячейку в новой книге и нажмем CTRL+V, чтобы вставить данные.

Рисунок 4. Сводная таблица с исходными данными

  • Если в этой таблице содержится какая-либо конфиденциальная информация, она была разглашена. Это связано с тем, что сводная таблица была скопирована вместе с исходными данными. Например, если дважды щелкнуть ЮТА, сведения о нем отобразятся, как показано на рис. 5.

Рисунок 5. Сводная таблица с исходными данными

Копирование и вставка значений, включая форматирование сводной таблицы без исходных данных

  • Мы нажмем CTRL+N, чтобы открыть новую книгу.
  • Нажмите на любую ячейку сводной таблицы, нажмите CTRL+A, чтобы выделить ячейки, нажмите CTRL+C, чтобы скопировать данные.
  • Откроем новую книгу и нажмем CTRL+ALT+V.

Рисунок 6. Диалоговое окно "Специальная вставка"

  • Сначала мы установим флажок "Значения". Это удалит все форматирование.
  • Нажимаем OK

Рисунок 7. Вставка только значений

  • Поскольку таблицы все еще находятся в буфере обмена, мы снова нажмем CTRL+ALT+V
  • Мы установим флажок формата, чтобы восстановить исходный формат сводной таблицы, и нажмем "ОК".

Рисунок 9. Вставленная сводная таблица без исходных данных

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

Для этой иллюстрации я использую куб AdventureWorks, а это моя существующая сводная таблица, указывающая на тестовый сервер (рис. 1)

Рис. 1. Сводная таблица куба служб Analysis Services

Чтобы клонировать эту сводную таблицу, перейдите в раздел «Параметры инструментов сводной таблицы» → «Выбрать» → «Вся сводная таблица» (рис. 2).

Рисунок 2. Выбор всей сводной таблицы

Нажмите ячейку на том же рабочем листе или создайте новый рабочий лист и нажмите клавишу ВВОД. Теперь сводная таблица будет скопирована в новое место. Обратите внимание, что это единственный способ скопировать сводную таблицу. При выборе строк CTRL+C и CTRL+V данные копируются как текст, а не как сводная таблица.

После клонирования сводной таблицы перейдите к изменению источника данных клона. Щелкните любую область во вновь скопированной сводной таблице и измените источник данных, перейдя к «Инструменты сводной таблицы» → «Параметры» → «Изменить источник данных» → «Изменить источник данных» (рис. 3). Выберите источник данных (в данном случае AdventureWorks Cube), указывающий на другую среду (в данном случае — производственную). Если источник данных не существует, создайте новый.

Рисунок 3. Изменение источника данных

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