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

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

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

Шаг 1: введите данные

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

Шаг 2: Создайте сводную таблицу

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

В появившемся новом окне выберите A1: C16 в качестве диапазона и поместите сводную таблицу в ячейку E1 существующего рабочего листа:

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

Перетащите поле « Магазин » в поле « Строки », затем перетащите поле « Продукт » в поле « Столбцы », затем перетащите поле « Количество » в поле « Значения »:

Сводная таблица будет автоматически заполнена следующими значениями:

Шаг 3. Преобразование сводной таблицы в таблицу

Чтобы преобразовать эту сводную таблицу в обычную таблицу данных, просто выберите всю сводную таблицу (в данном случае мы выбираем диапазон E1:I6 ) и нажмите Ctrl+C , чтобы скопировать данные.

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

Значения из сводной таблицы будут автоматически вставлены как обычные значения данных, начиная с ячейки E8 :

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

У нас просто осталась таблица обычных значений данных.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

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

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

1. Щелкните правой кнопкой мыши на панели быстрого доступа и выберите в контекстном меню пункт Настройка панели быстрого доступа.
2. В разделе Панель быстрого доступа диалогового окна Параметры Excel выберите Команды на ленте из раскрывающегося списка слева.
3. Прокрутите список и выберите пункт Мастер сводных таблиц и диаграмм.
4. Нажмите кнопку Добавить.
5. Нажмите 0К, чтобы закрыть диалоговое окно Параметры Excel.

1. Активизируйте любую ячейку в сводной таблице.
2. Щелкните на значке Мастер сводных таблиц и диаграмм, который вы добавили на панель быстрого доступа.
3. В диалоговом окне Мастер сводных таблиц и диаграмм установите первый переключатель в положение в нескольких диапазонах консолидации и нажмите кнопку Далее.
4. В шаге 2а установите переключатель в положите Создать поля страницы и нажмите кнопку Далее.
5. В шаге 2b в поле Диапазон укажите диапазон сводной таблицы ( А1:Е4 для выборки из примера) и нажмите кнопку Добавить; затем нажмите кнопку Далее, чтобы перейти к шагу 3.
6. В шаге 3 выберите место для сводной таблицы и нажмите кнопку Готово. Excel создаст сводную таблицу с данными и покажет область Список полей сводной таблицы.
7. В области Список полей сводной таблицы снимите флажки Строка и Столбец.

Как обновить данные в сводной таблице Excel?

1. Данные вписываются в таблицу, где есть столбцы и списки с названиями.
2. Отсутствие незаполненных форм.
3. Отсутствие скрытых объектов.

Как сделать сводную таблицу в excel: пошаговая инструкция

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

Категорию «Товары» мы поставим в виде строк. В «Названия строк» мы переносим необходимое нам поле.

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

Для отображения продаж, например, по каждому месяцу, нужно поле «Дата» поставить на место «Названия столбцов». Выберите команду «Группировать», нажав на дату.

Указываем периоды даты и шаг. Подтверждаем выбор.

Сделаем перенос поля «Сумма» к области «Значения».

Стало видно отображение чисел, а нам необходим именно числовой формат

Для исправления, выделим ячейки, вызвав окно мышкой, выберем «Числовой формат».

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

Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений».

Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор.

Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук.

Теперь меняем таблицу и делаем так, чтобы фильтр срабатывал по месяцам. Поле «Дата» мы переносим в «Фильтр отчета», а там где «Названия столбцов», будет «Продавец». Таблица отображает весь период продаж или за конкретный месяц.

Выделение ячеек в сводной таблице приведет к появлению такой вкладки как «Работа со сводными таблицами», а в ней будут еще две вкладки «Параметры» и «Конструктор».

Преобразование отчета из какой-то левой БД в плоскую таблицу в Excel ⁠ ⁠

Приложить файл примера не знаю как. Если кто подскажет, добавлю файл.

Ой, как я «люблю» отчеты из 1С анализировать! (Нет, нет и еще раз нет!) Там такие неудобные шапки, сводки, да еще объединенные ячейки везде. Выполнить анализ в Excel примерно такого отчета без предварительных «танцев с бубном» раньше было сложно:

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

Итак, шаги по порядку.

1 шаг. Сформировать запрос к таблице

Лучше делать это из чистой книги.

1. На вкладке Данные выбираем Получить данные — Из файла — Из книги, находим файл, нажимаем Импорт:

2. Выбираем лист с таблицей, нажимаем Преобразовать (данные):

3. Пугаемся того, что открылось. Знакомимся с окном Power Query и открытой импортированной таблицей:

Для тех, что ранее не работал с окном Power Query:

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

Сверху в окне Power Query находятся командные вкладки для преобразования данных.

2 шаг. Преобразование данных

1. Удаление лишних строк

Так как данные листа импортированы полностью, а таблица начинается лишь с 4-й строки на листе, нужно удалить первые 3 строки: вкладка Главная — Удалить строки — Удаление верхних строк. В появившемся окне ввести «3», нажать ОК:

2. Повышение заголовков

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

3. Переименуем столбец Column3 в «Наименование товара», т.к. здесь не было понятного заголовка: двойной клик по наименованию Column3:

4. Удаляем лишние столбцы

Все столбцы, что идут с названием ColumnХХ — пустые. Они получились в результате разъединения объединенных ячеек. Чтобы их удалить, на Главной вкладке нажимаем Выбор столбцов — Выбор столбцов, а затем снимаем флажки со всех столбцов с заголовками ColumnХХ, а также со столбца Итого:

Результат, который получается на данный момент:

5. Заполняем пустые строки в столбце Категория

Выделив этот столбец, на вкладке Преобразование выбираем Заполнить — Заполнить вниз. По всему столбцу будут заполнены категории товаров:

6. Удалим пустые строки

Оставшиеся пустые строки (null) содержат либо ненужные заголовки, либо суммы по категориям товаров. Эти сведения для плоской таблицы не нужны. Убрать их можно фильтрацией по столбцу Наименование товара: нажав кнопку фильтра столбца, убрать флаг NULL:

Результат после 6-го действия:

7. «Развернем» данные

Нужно выделить первые 2 столбца (Категория и Наименование товара), затем на вкладке Преобразование в команде Отменить свертывание столбцов выбрать Отменить свертывание других столбцов:

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

9. Для корректного анализа данных Периоду нужно присвоить формат данных Дата, а Сумме — формат Валюта: кнопка «АВС123» в заголовках столбцов:

Результат после 9 действия (4 столбца и 126 строк):

10. Последнее действие — выгрузить данные на лист Excel: команда Закрыть и Загрузить на вкладке Главная:

На листе Excel появится таблица и сведения о запросе:

Область с именем запроса не нужна для отображения, ее можно закрыть.

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

Например, можно сделать такой отчет:

Видео со всеми шагами:

661 пост 14.8K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

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

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

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

Очень круто! Но зачем?

Любой 1С отчет выполненный в СКД (а на скрине похоже как раз такой) можно и без танцев с бубном вывести в виде плоской таблицы за полминуты примерно, прямо в самой 1С.

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

Для того чтобы любой отчет получить в виде плоской таблицы нужно:

1. В меню «Еще» выбрать пункт «Настройки».

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

3. Включаем вид настроек «Расширенный» (слева внизу переключатель). Удаляем текущую структуру.

4. Идем в меню «Еще», нажимаем кнопку «Добавить таблицу».

5. В таблице настраиваем состав строк и колонок так как нам надо. В вашем случае в строках будет Категория, Наименование товара и Период, а колонки добавлять не надо, т.к. сумма это ресурс и она сама встанет куда надо автоматически.

6. Нажимаем «Закрыть и сформировать» и вуаля — перед нами данные в виде плоской таблицы.

7. Сохраняем полученный отчет в файл и работаем с ним в Excel.

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

На рисунке я привел пример настройки.

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

если отчет на СКД, то можно его в прямо в 1с, настройками, в плоскую таблицу вывести.

Вместо двух кликов мышкой в настройках отчёта в 1с такая мутня. Жесть.

Чет если честно — то проще разгруппировать строки, удалить лишние столбцы и все.

Читать ещё на Пикабу

Социал-демократы Австрии из-за ошибки Excel выбрали в председатели проигравшего кандидата⁠ ⁠

Находящаяся в оппозиции Социал-демократическая партии Австрии (СДПА) из-за ошибки в программе Excel выбрала в председатели кандидата, набравшего меньшее количество голосов. Об этом сообщила в понедельник избирательная комиссия партии.

За пост председателя на выборах, прошедших на выходных, боролись глава федеральной земли Бургенланд Ханс Петер Доскоциль и бургомистр города Трайскирхен Андреас Баблер. Изначально победителем был объявлен Доскоциль, однако в понедельник стало известно о технической ошибке, из-за которой результаты пришлось пересмотреть. Сбой произошел при переносе данных в таблицу Excel.

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

Вскоре после пересмотра результатов голосования австрийское сатирическое издание Die Tagespresse объявило о закрытии, заявив, что "его работа закончена".

СДПА имеет 40 мест в нижней палате парламента страны, что делает ее второй по величине после правящей консервативной Австрийской народной партии (71 место).

Создание сводных таблиц в Microsoft Excel

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

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

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

Вариант 1: Обычная сводная таблица

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

1. За основу возьмем таблицу выплат заработной платы работникам предприятия. В ней указаны имена работников, пол, категория, дата и сумма выплаты. То есть каждому эпизоду выплаты отдельному работнику соответствует отдельная строчка. Нам предстоит сгруппировать хаотично расположенные данные в этой таблице в одну сводную таблицу, при этом сведения будут браться только за третий квартал 2016 года. Посмотрим, как это сделать на конкретном примере.
2. Прежде всего преобразуем исходную таблицу в динамическую. Это нужно для того, чтобы при добавлении строк и других данных они автоматически подтягивались в сводную таблицу. Наводим курсор на любую ячейку, затем в расположенном на ленте блоке «Стили» кликаем по кнопке «Форматировать как таблицу» и выбираем любой понравившийся стиль таблицы.

Вариант 2: Мастер сводных таблиц

Создать сводную таблицу можно, применив инструмент «Мастер сводных таблиц», но для этого сразу нужно вывести его на «Панель быстрого доступа».

Переходим в пункт меню «Файл» и кликаем на «Параметры».

Настройка сводной таблицы

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

1. Для приведения таблицы к нужному виду кликаем на кнопку около фильтра «Дата». В нем устанавливаем галочку напротив надписи «Выделить несколько элементов». Далее снимаем галочки со всех дат, которые не вписываются в период третьего квартала. В нашем случае это всего лишь одна дата. Подтверждаем действие.
2. Таким же образом мы можем воспользоваться фильтром по полу и выбрать для отчета, например, только одних мужчин.
3. Сводная таблица приобрела такой вид.
4. Для демонстрации того, что управлять информацией в таблице можно как угодно, снова открываем форму списка полей. Переходим на вкладку «Параметры», и щелкаем на «Список полей». Перемещаем поле «Дата» из области «Фильтр отчета» в «Название строк», а между полями «Категория персонала» и «Пол» производим обмен областями. Все операции выполняем с помощью простого перетягивания элементов.
5. Теперь таблица выглядит совсем по-другому. Столбцы делятся по полам, в строках появилась разбивка по месяцам, а фильтрацию теперь можно осуществлять по категории персонала.
6. Если же в списке полей название строк переместить и поставить выше дату, чем имя, тогда именно даты выплат будут подразделяться на имена сотрудников.
7. Можно также отобразить числовые значения таблицы в виде гистограммы. Для этого выделяем ячейку с числовым значением, заходим на вкладку «Главная», жмем «Условное форматирование», выбираем пункт «Гистограммы» и указываем понравившийся вид.
8. Гистограмма появляется только в одной ячейке. Чтобы применить правило гистограммы для всех ячеек таблицы, кликаем на кнопку, которая появилась рядом с гистограммой, и в открывшемся окне переводим переключатель в позицию «Ко всем ячейкам».
9. В итоге наша сводная таблица стала выглядеть более презентабельно.

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