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

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

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

Лист, показанный на рис. 167.1, отображает тот тип преобразования, о котором я говорю. Диапазон А1:Е4 содержит исходную сводную таблицу: 48 точек данных. Столбцы G:I показывают часть 48-строковой таблицы, полученную из сводной таблицы. Другими словами, каждое значение в исходной сводной таблице преобразуется в строку, которая также содержит соответствующие значению название продукта и месяц. Этот тип списка полезен, поскольку его можно отсортировать и манипулировать им другими способами.

Рис. 167.1. Преобразование сводной таблицы в список

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

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

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

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

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

Так, сводная таблица остается только с полем данных Сумма по полю Значение. На данный момент небольшая сводная таблица показывает только сумму всех значений (рис. 167.2).

Рис. 167.2. Эта небольшая сводная таблица может быть расширена

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

Сводная таблица в Excel. Как создать сводную таблицу?

Для создания первой сводной таблицы возьмем за основу книгу, созданную на уроке «Создание списка выбора в Excel 2003», ссылка здесь.

Откроется окно мастера создания сводных таблиц. Ставим все как на снимке:

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

На этом шаге указываем, где создать таблицу. Оставляем «новый лист». Так же можно сразу построить макет (на мой взгляд это удобнее делать описанным далее способом, он более наглядный) или задать некоторые параметры таблице. Но все это можно в дальнейшем поправить.

Построение таблицы осуществляется путем перетаскивания полей из «Список полей сводной таблицы» в нужные зоны. Перетянем поля в следующие зоны:

Поставщик – так же в левую, но немного правее Типа операции;

Кол-во и Сумму тащим в самую большую зону поочередно;

Для разбивки по датам перетянем поле Дата в зону чуть выше области данных;

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

Для того чтоб изменить вариант расчета, необходимо навести на строку «Количество по полю Кол-во» и «Количество по полю Сумма» указатель мыши таким образом чтобы он принял вид черной стрелки:

Щелкнув один раз левой кнопкой мыши все строки группы «Количество по полю Кол-во» должны выделиться как на снимке выше.

Теперь жмем правой кнопкой мыши и в контексте выбираем пункт «Параметры поля»

В открывшемся окне параметров вычесления выберем «Сумма»

То же самое проделайте и для строк группы «Количество по полю Сумма»/

Теперь скроем излишние строки итогов. Для этого также выделяем группы:

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

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

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

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

Сводная таблица в Excel. Как создать сводную таблицу?

1. Открываем вкладку «Файл», ее раздел «Параметры». Находим пункт «Панель быстрого доступа», в появившемся списке выбираем пункт «Мастер сводных таблиц и диаграмм». Кнопка «Добавить» станет активной, по ней нужно кликнуть, а после – нажать «ОК».

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

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

Как построить сводную таблицу по нескольким массивам (листам)?

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

Преобразование Универсального отчета в сводную таблицу Excel.

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

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

• В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
• Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
• В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.

Здравствуйте. При создании сводной из запроса выскакивает такая ошибка: Драйвер ODBC Excel Недопустимое использование скобок с именем ‘Эл.

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

Метод 1. Преобразование сводной таблицы Excel 2010 в статические значения

После создания сводной таблицы и настройки ее структуры выделите всю таблицу и скопируйте ее в буфер обмена. Далее перейдите на вкладку Главная (Ноте) и щелкните на кнопке Вставка (Insert), а затем выберите в раскрывающемся меню команду Вставить значения (Insert Values), как показано на рис. 6.19.

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

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

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

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

Когда мы получаем данные из выгрузки или от коллег, часто возникает проблема со структурой таблицы. Встаёт вопрос: «Как привести данные к нужной структуре, чтоб построить удобный и простой отчёт?».

Рассмотрим как должна выглядеть правильная структура таблицы. Правила будут следующими:

1. У каждого столбца должен быть заголовок.
2. В каждом столбце данные должны быть однородные, т.е. одного типа. Например, если столбец несет под собой значения даты, то в каждой строке в столбце «Дата» должен быть единый тип.

Рассмотрим преобразования трёх самых популярных типов сводной таблицы.

1. В заголовках имеем диапазон по дате или другим категориям

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

1. через CTRL выделить все столбцы с диапазоном, в данном случае кварталы;
2. перейти во вкладку «Преобразование»;
3. Найти кнопку «Отменить свертывания столбцов».

Действия отображены на рисунке ниже:

Получаем таблицу, с которой можем дальше проводить анализ в Power BI:

2. В одном столбце неоднородные данные

Бывает, что столбец с названием показателя вынесен отдельно:

Выделим нужный столбец и нажмем на кнопку «Столбец сведения», после чего откроется меню настройки:

Во вкладке «Столбец значений» выбираем значения, которые попадут в новые столбцы. Во вкладке «Функция агрегированного значения» выбираем пункт «Не агрегировать»:

По окончании проделанных шагов получаем таблицу на рисунке ниже:

3. Сложная комбинация пунктов 1 и 2.

При комбинации случаев 1 и 2 первым делом необходимо избавиться от пустых значений null. Для этого выберем первый столбец и нажмем «Заполнить значения вниз».

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

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

Следующим шагом избавимся от нескольких заголовков. Для этого необходимо объединить столбцы и нажать на кнопку «Объединить столбцы»:

Столбцы склеиваются в один:

Транспонируем таблицу обратно и используем первую строку в качестве заголовка:

Далее действуем как в предыдущих примерах. Выделяем нужный диапазон и нажимаем «Отменить свертывание столбцов»:

Разделим ранее склеенный столбец, чтобы отделить год:

Выделим столбец, где указаны кол-во и сумма, далее нажмем столбец сведения:

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