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

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

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

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

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

1. В сводной таблице выберите необходимый вам диапазон и нажмите скопируйте его (воспользуйтесь сочетанием клавиш Ctrl+C).
2. На новом листе или другом свободном месте текущего листа выделите ячейку отобразите диалоговое окно Специальная вставка.
3. В диалоговом окне Специальная вставка, выберите Значения параметров и нажмите OK.

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

Чтобы при копировании диапазона сохранялось исходное форматирование выполните дополнительные действия:

1. Отобразите Буфер обмена Office. В Excel 2007-2013 эта функция находится на вкладке Главная → Буфер обмена
2. В открывшемся списке выберите нужный элемент (скорее всего это будет последний элемент) и нажмите вставить с исходным форматированием.

Теперь ваша скопированная сводная таблица не имеет связи с источником данных и сохранила исходное форматирование.

Связывание ячеек: консолидация и обеспечение согласованности данных

Дополнительные сведения о планах и их возможностях см. на странице «Расценки».

Связывание ячеек: консолидация и обеспечение согласованности данных

PLANS

• Smartsheet

For more information about plan types and included capabilities, see the Smartsheet Plans page.

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

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

Типы связей: входящая и исходящая

Доступно два типа связей ячеек.

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

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

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

Чтобы удалить входящую или исходящую связь, наведите курсор мыши на открывшееся окошко с информацией и щёлкните ссылку Удалить.

Создание входящей связи ячеек

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

1. Откройте конечную таблицу.
2. Выберите ячейку и на панели инструментов щёлкните Связывание ячеек, чтобы открыть форму связывания ячеек.

Рекомендации по эффективному использованию связей ячеек

При создании входящей связи в исходной таблице автоматически создаётся исходящая связь.

Можно выбрать несколько ячеек, чтобы создать связь в каждой из них.

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

Из одной исходной таблицы можно создать ссылки на 500 ячеек, а в конечной таблице может быть до 20 000 входящих связей.

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

Изменение и удаление связей

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

Входящие связи

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

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

1. Щёлкните ячейку, содержащую входящую связь (или зажмите кнопку мыши и потяните рамку, чтобы выделить группу ячеек).
2. Щёлкните ячейку правой кнопкой мыши и выберите пункт Удалить ссылку.

Исходящие связи

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

1. Выберите исходную ячейку в таблице с исходящей связью.
2. Наведите курсор мыши на связанную ячейку, чтобы появилась ссылка «Удалить».
3. Щёлкните ссылку Удалить.

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

Создание связей с помощью специальной вставки (из исходной таблицы)

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

Чтобы создать связь с помощью функции «Специальная вставка», выполните следующие действия.

1. Откройте исходную таблицу и скопируйте ячейку или диапазон ячеек (с помощью контекстного меню или сочетаний клавиш).
2. В той же вкладке браузера откройте конечную таблицу, выберите ячейку, в которой нужно создать связи, а затем щёлкните её правой кнопкой мыши (пользователи Mac могут щёлкнуть её, удерживая нажатой клавишу Ctrl) и выберите пункт Специальная вставка, чтобы открыть соответствующую форму.
3. Выберите параметр Ссылки на скопированные ячейки, а затем нажмите кнопку ОК. Ссылки со скопированными ячейками создаются, начиная с выделенной ячейки.

Типы ячеек, не допускающие связывание

Связи ячеек нельзя создать в столбцах «Вложения» и «Обсуждения».

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

• ячейки с формулами в столбцах;
• даты окончания;
• Предшественники
• сводные ячейки в родительских строках («Дата начала», «Дата окончания», «Процентов выполнено»);
• даты начала с зависимостью.

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

Ячейки с входящими связями также нельзя изменить в следующих ситуациях:

• из опубликованной таблицы;
• из запроса изменения;
• с помощью мобильного приложения Smartsheet;
• с помощью приложения Smartsheet для планшетов;
• из отчёта;
• в форме «Изменить».

Связанный контент

Справочная статья

Smartsheet Gov: Report on data from multiple sheets

With a report, you can compile information from multiple sheets and show only items that meet the criteria you specify.

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

Доброго времени суток, Уважаемые!
Подскажите пожалуйста, можно ли скопировать-вставить сводную таблицу в другую часть листа или на другой лист? Именно чтобы получилась вторая сводная с такими же рабочими полями. Если да,то на что нажимать? Спасибо.

Пользователь
Сообщений: 11907 Регистрация: 22.12.2012
Excel 2016, 365
12.03.2016 20:36:51
Доброе время суток
На другой лист — без проблем. Просто сделайте копию листа.
Пользователь
Сообщений: 201 Регистрация: 23.07.2015
12.03.2016 20:38:23
А на тот же? Ситуация такая, что надо только 2 поля поменять местами, а заново формировать долго.
Пользователь
Сообщений: 11907 Регистрация: 22.12.2012
Excel 2016, 365
12.03.2016 20:46:52

А дальше в скопированном листе на сводной, на вкладке «Работа со сводными», «Анализ», группа «Действия», кнопка «Переместить» и задаёте новое положение
Успехов.

Пользователь
Сообщений: 2308 Регистрация: 23.10.2014
12.03.2016 20:47:54

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

Изменено: Karataev — 12.03.2016 20:48:30
Пользователь
Сообщений: 201 Регистрация: 23.07.2015
12.03.2016 20:50:55
Пользователь
Сообщений: 2308 Регистрация: 23.10.2014
12.03.2016 20:56:57

Перед копирование сводную можно так выделить (может быть это будет удобнее в каких-то случаях): кликните внутри сводной по любой ячейке — вкладка Анализ — Действия — Выделить — Всю сводную таблицу.

Пользователь
Сообщений: 11907 Регистрация: 22.12.2012
Excel 2016, 365
12.03.2016 22:15:58
Karataev, спасибо. Что-то я перезамудрил с лишними телодвижениями
Страницы: 1
Читают тему

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Excel: как делать ссылки на сводную таблицу

«Уважаемые сотрудники «Б & К»! Я часто пользуюсь сводными таблицами, и в связи с этим у меня вопрос: как правильно делать ссылки на ячейки сводного отчета? Дело в том, что при обычном способе создания ссылок Excel вместо адреса вставляет специальную функцию, а это иногда очень неудобно. Подскажите, есть ли простой способ решения этой проблемы? Я работаю с программой MS Excel 2010. Среди параметров программы подходящих настроек я не нашел. Надеюсь на вашу помощь. Спасибо.

Владимир Ярославцев, главный бухгалтер, г. Днепропетровск».

Отвечает Николай КАРПЕНКО , канд. техн. наук, доцент каф. Прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

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

Я поясню ее на примере отчета, фрагмент которого показан на рис. 1. Это сводная таблица, которая сформирована по некоторой базе данных. В таблице показаны объемы продаж по шести контрагентам. Предположим, что эти данные мы решили вставить в другую таблицу в виде ссылок на ячейки сводного отчета, и уже там сделать окончательный расчет. Посмотрим, что из этого получится. Чтобы не усложнять задачу, я создам ссылки на том же рабочем листе, где расположена сводная таблица. Дальше делаем так:

1. Становимся на свободную ячейку, пусть это будет «D3».

2. Набираем символ «=» (начало формулы).

3. Щелкаем левой кнопкой мыши на ячейке «B3» (я хочу сделать ссылку на сумму реализации по контрагенту «ТОВ «Топаз»»). В ячейке «D3» вместо ссылки мы увидим такой результат: «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма»; $A$1;»Покупатель»;»ТОВ «»Топаз»»»)». При этом значение в ячейке «D3» будет равно «119,80», что соответствует объемам продаж по «ТОВ «Топаз»».

4. Копируем эту формулу вниз до ячейки «D8» (на всю высоту сводной таблицы). Результат во всех ячейках будет одинаковым — «119,80». То есть функция получения данных из сводного отчета сослалась на одну и ту же ячейку сводной таблицы.

Причина такого поведения лежит в параметрах функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()». Таких параметров у нее четыре. Первым идет название поля, по которому нужно выбрать итог. В нашем случае это поле «Сумма». Так это поле называлось в исходной базе, с этим именем оно и попало в сводный отчет. Вторым параметром стоит ссылка на ячейку с заголовком поля. В формуле эта ссылка выглядит как «$A$1». Кстати, абсолютная адресация в данном случае обязательна! Третий параметр — название поля, по которому Excel будет выбирать данные из сводного отчета. В формуле указано, что поиск конкретного числа в сводной таблице нужно делать по полю «Покупатель». Последний параметр — это строка для поиска конкретного значения среди покупателей. В нашей функции указано значение «ТОВ «Топаз»». Поэтому Excel выберет итог именно по этому контрагенту. Сразу бросается в глаза, что большинство параметров в функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» указаны в виде текстовых строк. Именно поэтому не сработала корректировка адресов при копировании формулы в ячейки «D3:D8», и все функции вернули один и тот же результат.

Кстати, исправить такую ситуацию несложно: нужно вместо фиксированного элемента «»ТОВ «»Топаз»»»» поставить ссылку на ячейку «A3». То есть формула в ячейке «D3» должна выглядеть так: «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма»;$A$1;»Покупатель» ;A3)» (изменения выделены полужирным начертанием). В этом варианте после копирования формулы вниз до ячейки «D8» мы получим правильные объемы реализации по каждому контрагенту.

Однако речь сейчас о другом. Использование функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» имеет свои преимущества и недостатки. Среди преимуществ я бы указал, что независимо от порядка сортировки записей в сводной таблице ссылка через функцию обеспечит правильный результат. И это понятно — извлечение данных из сводного отчета функция делает по ключевому полю, а не по адресу рабочего листа! Если посмотреть на формулу в ячейке «D3», то ключевым полем для обращения к сводной таблице является название фирмы «ТОВ «Топаз»». И при этом не имеет никакого значения, где конкретно находится запись по этой фирме — на первой позиции отчета или в самом конце. Данные Excel подставит правильно.

Недостаток работы с функцией «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» состоит в том, что нужно корректировать значение ключевого поля или заменять его ссылкой. Поэтому в некоторых случаях удобнее вместо встроенной функции использовать ссылки на ячейки сводной таблицы. Чтобы вставить такие ссылки автоматически (отказаться от использования функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()»), нужно знать одну тонкость.

Секрет Встроенную функцию «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» Excel использует только при ссылках на поля в области данных сводного отчета. При организации ссылок на заголовки строк или колонок он вставляет обычные ссылки на ячейки рабочего листа.

Зная это правило, мы легко получим «нормальные» ссылки на ячейки сводной таблицы. Для этого делаем так:

1. Открываем документ, как на рис. 1.

2. Становимся на ячейку «D3».

3. Вводим символ «=» (начинаем запись формулы).

4. Щелкаем левой кнопкой мыши на ячейке «A3». Excel добавит в текущую ячейку ссылку «=A3», где записано название фирмы. В данном конкретном случае — это «ТОВ «Топаз»».

5. Нажимаем «Enter» (завершаем ввод формулы).

6. Копируем формулу в ячейку «E3».

Смотрим на содержимое ячеек «D3» и «E3». Как и следовало ожидать, там находятся обычные ссылки: «=A3» и «=B3». Одна указывает на ячейку с названием фирмы, вторая — на объем реализации. Теперь с этими ссылками можно делать все что угодно — переносить на другой лист, использовать в расчетах и т. д.

И последнее. Работа с обычными ссылками незаменима, когда нужно построить график по данным сводного отчета (!) в программе Excel 2003. При создании такого графика Excel 2003 формирует его на отдельном листе, а это не всегда удобно. Чтобы отказаться от такой возможности и построить диаграмму на текущем листе, нужно создать рабочую область со ссылками на данные сводной таблицы. А затем по этим ссылкам сформировать диаграмму. Для таблицы на рис. 1 процедура выглядит так:

1. Открываем документ, переходим на ячейку «D3».

2. Вводим в нее формулу «=A3».

3. Копируем формулу в ячейки «D3:E8». В результате мы получим копию данных из сводной таблицы в виде формул.

4. Строим график по данным «D3:E8».

5. Чтобы скрыть «рабочую область», форматируем значения в блоке «D3:D8» белым цветом или ставим график поверх ячеек «D3:D8», чтобы закрыть им вспомогательную информацию (рис. 2).