Сводная таблица: что это?

Вычисляемое поле в Сводных таблицах в MS Excel

Научимся добавлять и редактировать Вычисляемое поле в Сводной таблице MS EXCEL 2010.

Простые Сводные таблицы мы научились строить в статье Сводные таблицы в MS Excel . Теперь научимся создавать и изменять Вычисляемое поле в Сводной таблице.

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

Нашей задачей будет:

  • вычислить % выполнения плана
  • представить полученные данные по годам для каждого месяца (каждый год — отдельный столбец)

В итоге у нас должна получиться вот такая сводная таблица.

Исходная таблица

Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).

На основе даты продажи в столбце А, в таблице рассчитываются 2 столбца: Номер месяца =МЕСЯЦ() и Год =ГОД() . Для форматирования ячеек столбца А в виде окт11 использован пользовательский формат Даты МММГГ;@.

Столбец План представляет собой линейный тренд (это не важно для целей данной статьи), столбец Продано — фактический объем продаж.

Сводная таблица

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

Нажав ОК, сводная таблица автоматически создастся на новом листе.

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

Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.

Создаем вычисляемое поле

Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’

Это можно сделать непосредственно в Сводной таблице , создав Вычисляемое поле ПроцентВыполнения.

Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :

Появится диалоговое окно:

Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:

  • Вместо Поле1 введите название Вычисляемого поля, например, ПроцентВыполнения
  • В списке полей выделите поле Продано, руб. и нажмите кнопку Добавить поле или дважды кликните на него. Название поля будет введено в поле Формула
  • Введите символ деления / в поле Формула
  • В списке полей выделите поле План, руб. и нажмите кнопку Добавить поле
  • Нажмите ОК

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

После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).

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

Теперь разберемся, что Вычисляемое поле нам насчитало.

Вычисляемое поле. Алгоритм расчета

Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года — это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).

Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?

Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).

В этом состоит одно из ограничений Вычисляемого поля — итоговые значения вычисляются только на основании суммирования.

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

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

Чтобы обойти данное ограничение и вычислить, например, средний % выполнения плана для всех январских месяцев, придется отказаться от Вычисляемого поля. Создайте в исходной таблице новый столбец — отношение продажи к плану для каждого месяца (см. лист Исходная таблица2). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.

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

Изменяем и удаляем Вычисляемое поле

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

Там же можно удалить это поле.

Еще одно ограничение

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

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

Создать само Вычисляемое поле для номера месяца — не проблема:

Однако, перенести его в качестве строк сводной таблицы не получается.

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

Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.

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

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

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

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

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

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

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

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

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

В результате мы получаем настоящую сводную таблицу.

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

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

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

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

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

2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.

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

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

В целом требований немного, но их следует знать.

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

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

или
через команду во вкладке Данные – Обновить все.

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

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

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

Скачать файл с примером.

Поделиться в социальных сетях:

Управление сводными таблицами в Excel

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

Фильтр в сводной таблице Excel

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

Для примера возьмем следующую таблицу:

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

Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.

Напомним, как выглядит диалоговое окно сводного отчета:

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

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

Например, среднее количество заказов по каждому поставщику:

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

Установим фильтр в сводном отчете:

  1. В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
  2. Перетащим это поле в область «Фильтр отчета».
  3. Таблица стала трехмерной – признак «Склад» оказался вверху.

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

Например, «1»:

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

Отфильтровать отчет можно также по значениям в первом столбце.



Сортировка в сводной таблице Excel

Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».

Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:

После нажатия ОК сводная таблица приобретает следующий вид:

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

Значения в сводном отчете поменяются в соответствии с отсортированными данными:

Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:

Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».

Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):

Формулы в сводных таблицах Excel

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

  1. Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» — в сводную таблицу добавятся три одинаковых столбца.
  2. Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
  3. Поменяем местами значения столбцов и значения строк. «Поставщик» — в названия столбцов. «Σ значения» — в названия строк.

Сводный отчет стал более удобным для восприятия:

Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».

Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.

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

Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.

Сводные таблицы в 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 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

About the author

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *