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

Способ 1: Копирование через специальную вставку

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

1. Выделите всю сводную таблицу, включая заголовки и итоговые строки. Для быстрого выделения кликните по любой ячейке внутри таблицы и нажмите Ctrl + A.

2. Скопируйте выделенные данные в буфер обмена, нажав комбинацию клавиш Ctrl + C. По периметру таблицы появится мигающая пунктирная рамка, указывающая на то, что данные скопированы.

3. Выберите место для вставки обычной таблицы — это может быть новая ячейка на том же листе, другой лист в текущей книге или совершенно новый файл Excel.

4. Откройте меню специальной вставки одним из способов: нажмите комбинацию Ctrl + Alt + V, либо кликните правой кнопкой мыши по выбранной ячейке и выберите пункт «Специальная вставка». Форматирование чисел может вести себя по-разному в зависимости от выбранного способа вставки.

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

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

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

Способ 2: Получение детальных данных двойным кликом

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

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

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

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

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

Способ 3: Использование Power Query для регулярного преобразования

Power Query представляет собой инструмент ETL (извлечение, преобразование, загрузка) данных, встроенный в Excel начиная с версии 2016. Для задач регулярного преобразования сводных таблиц в обычные, когда требуется периодически обновлять данные и повторять процесс преобразования, Power Query обеспечивает автоматизированное решение с возможностью обновления одним нажатием кнопки.

1. Подготовьте вашу сводную таблицу, сняв все активные фильтры через вкладку «Анализ сводной таблицы» — «Очистить» — «Очистить фильтры», чтобы отобразились все данные без исключений.

2. Выделите всю сводную таблицу и скопируйте ее в новое место на листе или на отдельный лист, где она будет служить источником для Power Query.

3. Выделите диапазон скопированной таблицы, затем перейдите на вкладку «Данные» и в группе «Получить и преобразовать данные» нажмите кнопку «Из таблицы/диапазона».

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

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

6. После завершения всех преобразований нажмите «Закрыть и загрузить» на вкладке «Главная». Power Query создаст новую таблицу с преобразованными данными на отдельном листе.

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

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

Способ 4: Быстрое копирование с сохранением форматирования

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

1. Выделите всю сводную таблицу целиком, включая все заголовки, подписи и итоговые строки, нажав Ctrl + A после клика по любой ячейке таблицы.

2. Скопируйте выделенную область стандартным способом через Ctrl + C или правой кнопкой мыши — «Копировать».

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

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

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

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

Если требуется сохранить возможность управления детализацией, лучше оставить данные в формате сводной таблицы. Итоговые строки и столбцы после преобразования становятся обычными ячейками с числами, которые не пересчитываются автоматически при изменении данных. Если вы планируете редактировать значения в преобразованной таблице, потребуется заменить статические итоги на формулы суммирования, чтобы они обновлялись при изменении исходных данных. Для этого выделите ячейку с итогом и вручную введите формулу =СУММ() с указанием нужного диапазона.