Как преобразовать сводную таблицу в обычную excel
Предположим, что вам необходимо представить набор данных в таком виде, чтобы в нем определялись количество, объемы продаж и доход от продажи оборудования по регионам и рынкам сбыта. Ваша непосредственная задача — экспортировать указанные данные в виде, доступном для использования в другой системе.
Результат, показанный на рис. 3.13, весьма близок к желаемому, за некоторыми исключениями:
- промежуточные итоги в строках 20, 29 и т.д. нужно удалить из отчета;
- пустые ячейки А13:А19, А22:А28 и т.д. требуется заполнить реальными данными;
- общие итоги необходимо удалить;
- сводную таблицу необходимо преобразовать в статические значения.
Чтобы внести в сводную таблицу указанные изменения, выполните следующие действия.
- Выделите в сводной таблице любую ячейку.
- На контекстной вкладке Конструктор (Design) откройте меню Общие итоги (Grand Totals) и выберите в нем команду Отключить для строк и столбцов (Off for Rows and Columns).
- На той же контекстной вкладке Конструктор откройте меню Промежуточные итоги (Subtotals) и выберите команду Не показывать промежуточные суммы (Do Not Show Subtotals).
- На контекстной вкладке Конструктор щелкните на кнопке Макет отчета и в раскрывающемся меню выберите параметр Повторять все подписи элементов (Repeat All Item Labels). В результате из отчета исчезнут пустые поля, как показано на рис. 3.14.
Рис. 3.14. После удаления итогов и промежуточных итогов выделите исходные данные и строку заголовков
Рис. 3.15. Выберите параметр Вставить значения для вставки статической версии отчета
В результате вставки получим непрерывный блок ячеек с данными, подходящий для экспорта в другие системы.
Кросс-таблицы также называют сводными, двумерными (2D) или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк. Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д. Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.
Инструмент «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:
Перед началом работы добавьте «Редизайн таблицы» в Excel
«Редизайн таблицы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Как преобразовать сводную таблицу Excel в плоский список
Укажите размер заголовков:
В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1
Укажите, следует ли поместить результат на новый или на существующий лист.
Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая).
Как преобразовать сложную сводную таблицу с многоуровневыми заголовками
Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заголовки. Их также можно сделать плоскими с помощью XLTools:
Укажите размер заголовков:
Как выполнить редизайн таблицы с пустыми ячейками
Если в вашей сводной таблице имеются пустые ячейки, то и в соотвествующие ячейки плоского списка также будут пустыми. При этом пустые значения в плоском списке не несут значимой информации для анализа. Поэтому мы рекомендуем следующее:
Если пустые ячейки находятся в теле таблицы: вы можете пропустить соответствующие строки в плоском списке:
Как выполнить редизайн таблицы с объединёнными ячейками
Отметьте флажком Дублировать значение в объединённых ячейках :
Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответствующей строке плоского списка.
Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
Как выполнить редизайн таблицы с сохранением заголовков
Отметьте флажком Сохранить заголовки :
Как выполнить редизайн таблицы с сохранением формата ячеек
Отметьте флажком Сохранить формат ячеек :
Каждая ячейка сохранит своё форматирование в результирующем плоском списке, в т.ч. цвет заливки, границы, цвет шрифта, цвета условного форматирования, дата/общий/числовой/денежный/ формат, т.д.
Какие таблицы обрабатывает надстройка «Редизайн таблицы»
Редизайн таблицы по сути означает, что данные исходной таблицы копируются и преобразуются так, чтобы сформировать плоский список. Ваши исходные таблицы не изменяются. Чтобы избежать искажения данных, вместо ссылок на ячейки, функций или формул в исходной таблице, в результирующий плоский список надстройка XLTools вставляет их значения.
Термином «Таблица» в Excel часто обозначают разные понятия:
«Настоящая» таблица — именованный диапазон с применением стиля таблицы (операция «Форматировать как таблицу»). Может быть преобразована в простой диапазон.
Диапазон — простой диапазон внешне похожий на таблицу, с применением (или без) форматирования цвета фона, границ, т.д. Может быть преобразован в «настоящую» таблицу.
Сводная таблица (PivotTable) — динамическая таблица, сгенерированная с помощью операции Excel «Сводная таблица» (PivotTable). Ячейки не могут быть отредактированы.
Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.
Через некоторое время вы можете захотеть преобразовать разработанную сводную таблицу в список в Excel, теперь вы можете прочитать это руководство, чтобы завершить преобразование.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Преобразовать сводную таблицу в список
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы преобразовать сводную таблицу в список, вы должны убедиться, что сводная таблица настроена так, как вам нужно. Например, я конвертирую приведенную ниже сводную таблицу в список.
1. Щелкните любую ячейку в сводной таблице и перейдите к Дизайн вкладку и щелкните подытоги > Не показывать промежуточные итоги.
2. Нажмите Общие итоги > Отключено для строк и столбцов под Дизайн таб. Смотрите скриншот:
3. Нажмите Макет отчета > Повторить все метки предметов под Дизайн таб. См. Снимок экрана:
4. Нажмите Макет отчета снова и щелкните Показать в табличной форме. Смотрите скриншот:
Теперь сводная таблица выглядит следующим образом:
5. Нажмите Доступные опции вкладку (или Анализировать вкладка) и снимите флажок Кнопки и Заголовки полей в Показать группа.
Теперь сводная таблица, показанная ниже:
5. Теперь я покажу пустые ячейки как ноль. Щелкните любую ячейку сводной таблицы и щелкните правой кнопкой мыши, чтобы выбрать Параметры сводной таблицы, затем в появившемся диалоговом окне под Макет и формат вкладка, снимите флажок Для пустых ячеек показатьи нажмите OK закрыть Параметры сводной таблицы диалог. Смотрите скриншот:
Если вы хотите узнать больше о том, как отображать пустые ячейки как ноль, нажмите здесь.
6. Выберите сводную таблицу и нажмите Ctrl + C тем временем, чтобы скопировать его, затем поместите курсор в ячейку, в которую вы хотите вставить сводную таблицу в виде списка, и щелкните правой кнопкой мыши, чтобы выбрать Специальная вставка > Значение (V). Смотрите скриншот:
Внимание: В Excel 2007 вам нужно щелкнуть Главная > макаронные изделия > Вставить значения , чтобы вставить сводную таблицу как список.
Вы когда-нибудь хотели перевернуть или транспонировать сводную таблицу в Excel, как показано на скриншотах ниже. Теперь я расскажу вам, как быстро изменить сводную таблицу в Excel.
(11 шагов) Обратная сводная таблица с мастером сводной таблицы и сводной диаграммы
(7 шагов) Обратная сводная таблица с Kutools для Excel Размеры транспонированной таблицы
Обратная сводная таблица с помощью PivotTable и PivotChart Wizard
Чтобы перевернуть сводную таблицу, вам необходимо сначала открыть диалоговое окно мастера сводных таблиц и сводных диаграмм и создать новую сводную таблицу в Excel.
1. Нажмите Альт + Д + П сочетания клавиш для открытия диалогового окна мастера сводных таблиц и диаграмм, затем установите флажок Несколько диапазонов консолидации вариант под Где данные, которые вы хотите проанализировать и PivotTable вариант под Какой отчет вы хотите создать .
Внимание: Вы также можете добавить команду PivotTabe и PivoChart Wizard на панель быстрого доступа и щелкнуть, чтобы открыть диалоговое окно.
2. Нажмите Далее перейти к следующему диалогу, чтобы проверить Я создам поля страницы и нажмите Далее.
3. Выберите базовые данные, затем нажмите Добавить чтобы добавить диапазон данных в Все диапазоны список. Смотрите скриншот:
4. Нажмите Далее чтобы перейти к последнему шагу мастера, отметьте нужный вариант в Куда вы хотите поместить отчет сводной таблицы раздел. Затем нажмите Завершить.
5. Теперь создается новая сводная таблица и дважды щелкните последнюю ячейку в правом нижнем углу новой сводной таблицы, после чего новая таблица будет создана на новом листе. Смотрите скриншоты:
6. Затем создайте новую сводную таблицу на основе этой новой таблицы. Выделите всю новую таблицу и нажмите Вставить > PivotTable > PivotTable.
7. Затем во всплывающем диалоговом окне отметьте нужный вариант в Выберите, где вы хотите разместить отчет сводной таблицы .
8. Нажмите OK, Затем Список полей сводной таблицы появится панель и перетащите Строка и Колонка полей для заголовки строк раздел и поле значения для Ценности раздел. Смотрите скриншот:
9. Затем щелкните любую ячейку новой сводной таблицы и перейдите к Дизайн нажмите вкладку «Макет отчета»> «Показать в табличной форме».
10. Затем перейдите к Макет отчета снова щелкнуть Повторить все метки предметов из списка. Смотрите скриншот:
Внимание: Это не Повторить все метки предметов в раскрывающемся списке Макет отчета в Excel 2007, просто пропустите этот шаг.
11. Нажмите Дизайн > подытоги > Не показывать промежуточные итоги.
Теперь сводная таблица перевернута. Смотрите скриншот:
Обратная сводная таблица с Kutools для Excel Размеры транспонированной таблицы
С указанным выше способом есть так много шагов для решения задачи. Чтобы значительно повысить эффективность вашей работы и сократить рабочее время, я предлагаю вам перевернуть сводную таблицу с помощью Kutools for ExcelАвтора Перенести размеры таблицы функцию.
1. Выберите базовые данные и нажмите Кутулс > Диапазон > Перенести размеры таблицы.
2. в Перенести размеры таблицы диалог, проверьте Перекрестная таблица в список под Тип транспонирования раздел, затем выберите ячейку, в которую вы хотите поместить новую таблицу.
3. Нажмите Ok чтобы создать новую таблицу, а затем вставьте заголовки над новой таблицей. Смотрите скриншот:
4. Выберите новую таблицу, включая заголовки, и нажмите Вставить > PivotTable > PivotTable, то в Создать сводную таблицу в диалоговом окне выберите нужный вариант в Выберите, где вы хотите разместить отчет сводной таблицы .
5. Нажмите OK, И в Список полей сводной таблицы панель, перетащите Строка и Колонка полей для заголовки строк раздел и Значение поле к Ценности .
6. Щелкните любую ячейку новой сводной таблицы и щелкните Дизайн > Макет отчета > Показать в табличной форме, затем нажмите Макет отчета снова щелкнуть Повторить все метки предметов. Смотрите скриншоты:
Внимание: Это не Повторить все метки предметов в раскрывающемся списке кнопки «Макет отчета» в Excel 2007, просто пропустите ее.
7. Нажмите Дизайн > подытоги > Не показывать промежуточные итоги.
Теперь сводная таблица перевернута. Смотрите скриншот:
Работы С Нами Kutools for ExcelАвтора Перенести размеры таблицы функцию, вы также можете преобразовать таблицу списка в кросс-таблицу. Нажмите здесь, чтобы узнать больше.
Транспонировать сводную таблицу
Быстро перенести кросс-таблицу в список или наоборот
Относительные статьи:
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF . Pivot Table Grouping by week number, day of week and more. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name . -->
- Более 300 мощных функций . Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Подраздел Развернуть()
'
Dim Rowlabel As Range
Затемнить метку столбца как диапазон
Дим Пап как диапазон
Dim Table As Range
Dim i As Integer
Dim j как целое число
Dim a As Integer
Dim b как целое число
Затемнить данные как диапазон
Dim k как целое число
Затемнение метки как диапазона
Dim pvtCache как PivotCache
Dim pvt как сводная таблица
Dim SrcData как строка
'
ActiveSheet.Copy До: = Рабочие листы (1)
Установить Таблицу = Выбор
Для каждого Pap в таблице
Если Pap.MergeCells Тогда
С Pap.MergeArea
.UnMerge
.Value = Pap.Value
Конец с
End If
Далее
i = Application.InputBox("Ярлык So Dong Chua", "Hoang", i, Type:=2)
j = Application.InputBox("Ярлык So cot chua", "Hoang", j, Type:=2)
On Error Resume Next
Листы("Unpivot_Table").Удалить
Sheets.Add.Name = "Unpivot_Table"
Установить Pap = Range("Unpivot_Table!B2")
b = Таблица.Строек.Количество
a = Таблица.Столбцов.Количество
Установить данные = диапазон (Табл. Ячейки (i + 1, j + 1), Табл. Ячейки (б, а))
Установите Columnlabel = Range (Tabl.Cells (i + 1, 1), Tabl.Cells (b, j))
Установите Rowlabel = Range (Tabl.Cells (1, j + 1), Tabl.Cells (i, a))
Пап.Выбрать
Для каждого столбца в Data.Columns
Столбец.Копировать
Selection.PasteSpecial Вставить:=xlPasteValues
Метка столбца.Копировать
Selection.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
Столбец.Копировать
Выбор.Смещение(b - i, -1).Выбрать
Следующий столбец
Pap.Offset(0, j + 1).Выбрать
Для каждого столбца в Rowlabel.Columns
Столбец.Копировать
Диапазон (Выделение, Выбор. Смещение (b - i - 1, 0)). Специальная вставка: = xlPasteValues, транспонирование: = True
Selection.End(xlDown).Offset(1, 0).Select
Следующий столбец
Установить метку = диапазон (Pap.Offset (-1, 0), Pap.Offset (0, i + j + 1))
Для k = 1 To i + j + 1
Label.Cells(1, k).Value = Application.InputBox(Label.Cells(2, k).Value & "la", "Hoang", k, Type:=2)
Далее
Диапазон(Pap.End(xlUp), Pap.End(xlDown).End(xlToRight)).Выбрать
SrcData = ActiveSheet.Name & "!" & Выбор.Адрес
On Error Resume Next
Листы("Сводка").Удалить
Sheets.Add.Name = "Сводка"
Установите pvtCache = ActiveWorkbook.PivotCaches.Create(_
SourceType:=xlDatabase, _
Исходные данные: = Исходные данные)
Установите pvt = pvtCache.CreatePivotTable(_
TableDestination:="Сводка!" & Sheets("Pivot").Range("A3").Address(ReferenceStyle:=xlR1C1), _
ИмяТаблицы:="СводнаяТаблица1")
End Sub
Как преобразовать диапазон в таблицу или наоборот в Excel?
Таблица - это полезная функция для нас, позволяющая выполнять некоторые операции в Excel, поэтому иногда нам нужно создать таблицу на основе диапазона данных. Чтобы преобразовать диапазон в таблицу или наоборот, Excel предлагает несколько простых способов использования. Пожалуйста, узнайте больше в этой статье.
- Преобразовать диапазон в таблицу в Excel
- Преобразовать таблицу в диапазон в Excel
- Преобразование таблицы в обычный текст диапазона с помощью Kutools for Excel
Преобразовать диапазон в таблицу в Excel
Чтобы преобразовать диапазон данных в таблицу, сделайте следующее:
1. Выберите диапазон данных, который вы хотите преобразовать.
2. Нажмите Вставить > Настольные, В Создать таблицу диалоговое окно, отметьте В моей таблице есть заголовки если у ваших данных есть заголовки, смотрите скриншоты:
3. Затем нажмите OK, и ваш диапазон данных был преобразован в формат таблицы.
Функции: За исключением вышеуказанного способа, вы также можете отформатировать диапазон данных в виде таблицы с помощью метода ниже.
1. Выберите диапазон данных и нажмите Главная > Форматировать как таблицу, выберите понравившийся стиль стола из Форматировать как таблицу выпадающий список.
2. В выскочившем Форматировать как таблицу диалоговое окно, отметьте В моей таблице есть заголовки если у ваших данных есть заголовки, а затем нажмите OK чтобы закончить процедуру.
Один щелчок, чтобы преобразовать таблицу в обычный текст диапазона в Excel
При применении Главная > Очистить > Очистить форматы в таблицу, он удалит из таблицы цвет заливки, цвет шрифта или другие форматы. Однако выбранный диапазон по-прежнему представляет собой таблицу. Но вы хотите удалить все форматы из выбранной таблицы и преобразовать ее в нормальный диапазон с обычным текстом. В этом случае попробуйте Очистить все форматирование ячеек утилита Kutools для Excel. Полнофункциональная бесплатная 30-дневная пробная версия!
Преобразовать таблицу в диапазон в Excel
1. Выберите диапазон таблицы, щелкните правой кнопкой мыши и выберите в контекстном меню «Таблица»> «Преобразовать в диапазон». Смотрите скриншот:
Функции: Вы также можете выбрать диапазон таблицы и затем щелкнуть Дизайн > Преобразовать в диапазон.
2. После этого появится окно с подсказкой, нажмите Да кнопку, и выбранная таблица будет преобразована в нормальный диапазон.
Внимание: Этот метод удалит только стиль форматирования таблицы, но сохранит цвет заливки и цвет шрифта исходной таблицы. Если вам нужно удалить все форматирование таблицы, включая цвет заливки и цвет шрифта, используйте следующий метод.
Преобразование таблицы в обычный текст диапазона с помощью Kutools for Excel
Kutools for Excel's предоставляет Очистить форматирование всех ячеек Утилита, позволяющая пользователям Excel одним щелчком мыши очистить стиль форматирования таблицы без потери данных таблицы в Excel.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Выберите таблицу, стиль которой вы хотите удалить, и нажмите Кутулс > Формат > Очистить форматирование всех ячеек. Смотрите скриншот:
Теперь вы увидите, что стиль форматирования таблицы полностью удален / очищен с сохранением исходных данных таблицы, как показано ниже:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демо: преобразование таблицы в простой диапазон в Excel
Читайте также: