Преобразовать список в таблицу данных пример эксель. Вставка строк или столбцов. Использование нескольких источников данных

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

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

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

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

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

Открывается заготовка Сводного отчета со Списком полей , которые можно отобразить.

Покажем, к примеру, количество проданного товара.

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

Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

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

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

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

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

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

После изменения диапазона в сводке появилось поле «Продажи».

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

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

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

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

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

По такой же схеме можно группировать данные в сводной таблице по другим параметрам.

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

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

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

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

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

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

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

Открывается окно, в котором нам опять нужно выбрать диапазон, или название таблицы. Как видим, программа уже сама подтянула имя нашей таблицы, так что тут ничего больше делать не нужно. В нижней части диалогового окна можно выбрать место, где будет создавать сводная таблица: на новом листе (по умолчанию), или же на этом же. Конечно, в большинстве случаев, намного удобнее использовать сводную таблицу на отдельном листе. Но, это уже индивидуальное дело каждого пользователя, которое зависит от его предпочтений, и поставленных задач. Мы же просто жмем на кнопку «OK».

После этого, на новом листе открывается форма создания сводной таблицы.

Как видим, в правой части окна расположен список полей таблицы, а ниже четыре области:

Названия строк;
Названия столбцов;
Значения;
Фильтр отчёта.

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

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

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

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

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

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

После этого, сводная таблица приобрела такой вид.

Чтобы продемонстрировать, что управлять данными в таблице можно как угодно, снова открываем форму списка полей. Для этого переходим во вкладку «Параметры», и жмем на кнопку «Список полей». Затем, перемещаем поле «Дата» из области «Фильтр отчета» в область «Название строк», а между полями «Категория персонала» и «Пол», производим обмен областями. Все операции выполняем с помощью простого перетягивания элементов.

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

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

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

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

Теперь, наша сводная таблица приобрела презентабельный вид.

Создание сводной таблицы с помощью Мастера сводных таблиц

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

В открывшемся окне параметров, переходим в раздел «Панель быстрого доступа». Выбираем команды из команд на ленте. В списке элементов ищем «Мастер сводных таблиц и диаграмм». Выделяем его, жмем на кнопку «Добавить», а потом на кнопку «OK» в правом нижнем углу окна.

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

После этого, открывается мастер сводных таблиц. Как видим, мы имеем четыре варианта источника данных, откуда будет формироваться сводная таблица:

в списке или в базе данных Microsoft Excel;
во внешнем источнике данных (другой файл);
в нескольких диапазонах консолидации;
в другой сводной таблице или в сводной диаграмме.

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

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

Затем, Мастер сводных таблиц предлагает выбрать место, где будет размещаться новая таблица на этом же листе или на новом. Делаем выбор, и жмем на кнопку «Готово».

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

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

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

Инструкция

Откройте документ Excel с данными, из которых вы хотите сделать сводную таблицу . Щелкните указателем мыши по одной из ячеек таблицы, обязательное условие - чтобы в этой ячейке было записано какое-то значение. Самое удобное – присвоить столбцу соответствующее имя, например, «название», «количество» и т.п.

В панели меню в верхней части окна найдите кнопку «Данные» (для Excel 2000, ХР, 2003) или вкладку «Вставка» (для Excel 2007) и нажмите ее. Откроется список, в котором активируйте кнопку «Сводная таблица». Откроется «Мастер создания сводных таблиц», он поможет настроить все необходимые параметры.

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

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

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

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

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

Полезный совет

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

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

Excel – одна из основных программ Windows. Многие пользователи знают о ее существовании, но не подозревает о тех возможностях, которые она содержит. Одной из них можно считать таблицу. Те, кто в первый раз об этом узнали, могут спросить «Как создать таблицу в Excel?»

Инструкция

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

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

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

Немного теории

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

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

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

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

Запустив его, перед вами откроется обширное поле, разделенное на большое количество ячеек. Более детально о том, как делать сводные таблицы в Excel, вам подскажет видеоурок выше.

С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel.
На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».

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

Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

Заранее озаглавьте каждый столбик

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

Закрепив все настройки, получаем готовую основу. По левую сторону располагается область, где размещена основа будущей рамки. В правой части есть окно с настройками, которое помогает управлять реестром.

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

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

Обратите внимание, как расположились эти данные в нижней области панели настройки.

Отдел автоматически ушел в строки, а числовые данные в значения. Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец.

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

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

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

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

В параметрах полей значений вы найдете множество вариантов для анализа.

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

Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.

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

Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все».

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

Также можно выбрать и значения для отдела.

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

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

Обновление данных в сводной таблице в Excel

Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».

Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.

Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

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

Чтобы добавить дополнительный столбец, вам необходимо добавить его в исходные данные и расширить диапазон для нашего реестра.

Перейдите на вкладку «Анализ» и откройте источник данных.

Excel сам все предложит.

Обновите и вы получите новый перечень полей в области настройки.

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

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

Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».

Выберите все команды.

И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.

Значок появится сверху.

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

Нам нужно несколько полей, а не одно.

На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист (щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.

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

Щелкайте «Далее» и создавайте на новом листе.

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

Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.

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

Как видите, у нас одно значение в соответствующей области.

Изменение структуры отчёта

Мы поэтапно разобрали пример, как создать сводную таблицу Exce, а как получить данные другого вида расскажем далее. Для этого мы изменим макет отчета. Установив курсор на любой ячейке, переходим во вкладку «Конструктор», а следом «Макет отчета».

Вам откроются на выбор три типа для структуризации информации:

Сжатая форма

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

Структурированная форма

Все показатели подаются иерархично: от малого к большому.

Табличная форма

Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.

Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

Отличного Вам дня!

Сводные таблицы также называют двумерными (2D) таблицами или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк. Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д. Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.

Надстройка «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:

Редизайн сводной таблицы в список в секунды
Преобразование сложных таблиц с многоуровневыми заголовками
Корректный редизайн таблиц с объединёнными или пустыми ячейками
Сохранение заголовков столбцов
Сохранение форматирования ячеек

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Добавить «Редизайн таблицы» в Excel 2019, 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2019 - 2007, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

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

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
Совет
Укажите размер заголовков:
В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1
Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая).
Нажмите ОК >

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

Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заголовки. Их также можно сделать плоскими с помощью XLTools:

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
Выделите сводную таблицу, включая заголовки.
Совет : нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.
Укажите размер заголовков:
- Заглавных строк: число строк, которые составляют заголовок таблицы сверху.
- Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.
Укажите, следует ли поместить результат на новый или на существующий лист.
Нажмите ОК > Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

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

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

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

Выделите сводную таблицу, включая заголовки.
Укажите размер заголовков.
Отметьте флажком «Пропустить пустые значения».
Нажмите ОК > Готово.

Как выполнить редизайн таблицы с объединёнными ячейками

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
Выделите сводную таблицу, включая заголовки.
Укажите размер заголовков.
Отметьте флажком «Дублировать значение в объединённых ячейках»:
- Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответствующей строке плоского списка.
- Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
Укажите, куда поместить результат.
Нажмите ОК > Готово.

Как выполнить редизайн таблицы с сохранением заголовков

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
Выделите сводную таблицу, включая заголовки.
Укажите размер заголовков.
Отметьте флажком «Сохранить заголовки»:
- Где это возможно, надстройка продублирует заголовки из сводной таблицы.
- Категориям таблицы будет автоматически присвоен заголовок «Категория».
- Переменным значениям таблицы будет автоматически присвоен заголовок «Значение».
Укажите, куда поместить результат.
Нажмите ОК > Готово.

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

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
Выделите сводную таблицу, включая заголовки.
Укажите размер заголовков.
Отметьте флажком «Сохранить формат ячеек»:
Каждая ячейка сохранит своё форматирование в результирующем плоском списке, в т.ч. цвет заливки, границы, цвет шрифта, цвета условного форматирования, дата/общий/числовой/денежный/ формат, т.д.
Укажите, куда поместить результат.
Нажмите ОК > Готово.

Внимание : обработка больших таблиц с множеством форматов займёт больше времени.

Какие таблицы обрабатывает надстройка «Редизайн таблицы»

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

Термином «Таблица» в Excel часто обозначают разные понятия:

«Настоящая» таблица — именованный диапазон с применением стиля таблицы (операция «Форматировать как таблицу»). Может быть преобразована в простой диапазон.
Диапазон — простой диапазон внешне похожий на таблицу, с применением (или без) форматирования цвета фона, границ, т.д. Может быть преобразован в «настоящую» таблицу.
Сводная таблица (PivotTable) — динамическая таблица, сгенерированная с помощью операции Excel «Сводная таблица» (PivotTable). Ячейки не могут быть отредактированы.

Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.

Появились вопросы или предложения? Оставьте комментарий ниже.