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

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

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

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

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

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

Что такое сводная таблица (Pivot Table – англ.)? Pivot Table дословно переводится как «таблица, которую можно крутить, показывать в разных разворотах».

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

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

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

Исходные данные для сводной таблицы

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

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

Как быстро выделить таблицу:

Выбрать ее любую ячейку и нажать Crtl + * или Ctrl + A, или
Выбрать самую первую ячейку в таблице, зажать кнопки Ctrl и Shift, а затем нажать на кнопки: вправо и вниз (→↓).

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

Добавьте сводную таблицу: перейдите на вкладку Вставка и выберите «Сводная таблица».

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

Когда сводная таблица добавлена, на листе появляется область сводной таблицы. Если эта область не активна (вы не выделили ее мышкой), на ней будет подсказка: «Чтобы начать работу с отчетом сводной таблицей, щелкните в этой области». Щелкаем по ней мышкой и происходят две вещи:

Справа появится список полей сводной таблицы.
В меню — две дополнительные вкладки, связанные с управлением сводной таблицей (Анализ и Конструктор).

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

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

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

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

Если изменился сам источник данных (добавлены новые строки или столбцы), выберите любую ячейку сводной таблицы и перейдите в меню Анализ -> Источник данных.

В появившемся окне выберите источник данных.

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

То, что написано о сводных таблицах в статье — это еще не все.

Для «продвинутой» работы и бизнес-аналитики в Excel есть надстройки и инструменты, которые расширяют функционал сводных таблиц до действительно серьезного уровня и переносят его в область Business Intelligence. Сейчас мы говорим о надстройках Power Query и Power Pivot, которые также задействованы в BI-продукте Microsoft — Power BI.

Источник: https://finalytics.pro/inform/statya-pivot-table/

Как использовать 5 продвинутых приёмов для сводных таблиц Excel

Проблема, с которой мы все сталкиваемся — это не отсутствие данных; наоборот — это огромные объемы данных! Именно поэтому я выступаю за использование замечательной функции Excel — Cводные таблицы, чтобы обобщить и проанализировать ваши данные.

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

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

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

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

Как применять продвинутые приёмы сводных таблицы в Excel

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

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

1. Срезы

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

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

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

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

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

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

2. Временная шкала

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

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

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

Чтобы добавить временную шкалу, убедитесь, что вы выбрали сводную таблицу (щелкните внутри неё), а затем нажмите кнопку Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (или несколько столбцов) и нажмите OK, чтобы создать временную шкалу.

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

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

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

3. Табличный вид

Стандартный вид сводных таблиц в Excel выглядит как водопад; при перетаскивании большего количества полей в строки, Excel создает больше «слоев» в данных.

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

Выберите Конструктор > Макет отчета > Показать в табличной форме, чтобы работать с вашей сводной таблицей в виде обычной таблицы.

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

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

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

4. Вычисляемые поля

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

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

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

Используйте Анализ > Поля, элементы и наборы > Вычисляемое поле, чтобы вставить вычисленное поле в вашу сводную таблицу.

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

Чтобы вычислить общую стоимость заказа, я умножил существующее поле цены за единицу (Unit Price) на поле количества (Quantity).

После добавления названия этого поля, я добавлю знак умножения *, а затем дважды кликаю по общему количеству (quantity). Давайте продолжим и нажмите ОК.

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

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

Я умножил общую стоимость заказа на 1.05 для расчета стоимости, включая налог с продаж; вы можете использовать числовые значения наряду с существующими полями.

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

5. Рекомендуемые сводные таблицы

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

Перейдите в Вставка > Рекомендуемые сводные таблицы, чтобы попробовать эту функцию.

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

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

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

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

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

Повторяйте и продолжайте обучение (с ещё бо́льшими уроками по Excel)

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

Эти уроки вам продвинуть ваши навыки с Excel и сводным таблицам на следующий уровень. Проверь их:

Что вы всё ещё хотите узнать о сводных таблицах? Сообщите мне ваши идеи или вопросы в х ниже этого урока.

Источник: https://business.tutsplus.com/ru/tutorials/advanced-excel-pivot-table-techniques—cms-28572

Как я использую сводные таблицы (pivot tables)

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

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

И я решила, что надо это сделать хорошо и правильно один раз, и потом весь год просто обновлять (3 минуты раз в 2 недели) и пользоваться плодами этой работы.
Для начала я создала из своих проектов что-то вроде базы, постаравшись предусмотреть все нужные мне параметры:
Чтобы было легче работать, нажимаем ctrl+T, и ячейки превращаются в таблицу.

Заполняется довольно легко, повторяющиеся поля просто копирую. После этого ставлю курсор на одну из ячеек таблицы, иду во вкладку Вставка и вставляю Сводную таблицу (она же Pivot table — пивот или пайвот).

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

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

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

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

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

Итак, я создаю Сводную таблицу-заготовку, и ставлю курсор на какую-нибудь из её ячеек. Появляется вкладка с настройками таблицы.

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

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

Источник: https://blogs.technet.microsoft.com/tasush/2011/08/09/pivot/

Общие сведения о сводных таблицах

Несмотря на то, что первая возможность создания сводных таблица появилась еще в Excel 5.0(аж в 1993 году), даже сейчас лишь немногие из пользователей Excel используют сводные таблицы для решения задач.

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

А вот польза при анализе информации просто неоценима.

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

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

А теперь представим как это будет выглядеть формулами:
-сначала надо получить список месяцев;

-затем список уникальных наименований филиалов;

-далее создать листы с рыбой таблиц, в которые надо будет собрать данные из исходной таблицы при помощи СУММЕСЛИ, СУММПРОИЗВ и им подобным.
При должном опыте можно уложиться минут в 15-20. С использованием сводных это можно сделать за две минуты.

Сводная таблица (Pivot Table) – инструмент Excel, используемый для создания уникального представления данных и последующего анализа. Сводная таблица может быть построена на основе правильно сформированной исходной таблицы данных:

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

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

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

Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить(Refresh) или вкладка Данные(Data) →Обновить все(Refresh all) →Обновить(Refresh).

СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

Выделить любую ячейку исходной таблицы

Вкладка Вставка(Insert)→группа Таблица(Table)→Сводная таблица(PivotTable)

В диалоговом окне Создание сводной таблицы(Create PivotTable) проверить правильность выделения диапазона данных (или установить новый источник данных), определить место размещения Сводной таблицы:

На новый лист (New Worksheet)
На существующий лист (Existing Worksheet)

нажать OK

СВОДНАЯ ТАБЛИЦА СОСТОИТ ИЗ ЧЕТЫРЕХ ОБЛАСТЕЙ:
Область данных – основная область сводной таблицы, в которой производятся расчеты. Содержит основные итоговые данные по числовым полям. В область данных можно поместить одно и тоже поле, но с разными вычислениями (например одно Сумма по полю, другое Количество по полю).
Основные вычислительные функции области данных:

Сумма (Sum)
Количество (Count)
Среднее (Average)
Максимум (Max)
Минимум (Min)
Произведение (Product)

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

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

Для выполнения обновления необходимо выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить(Refresh) или вкладка Данные(Data)→Обновить(Refresh).

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

Источник: https://www.excel-vba.ru/chto-umeet-excel/obshhie-svedeniya-o-svodnyx-tablicax/

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

Вопросы, рассмотренные в материале:

Что такое сводная таблица Excel?
Как создать сводную таблицу Excel?
Как пользоваться сводной таблицей Excel?
Какие плюсы и минусы сводной таблицы Excel?
Как сделать сводную таблицу Excel из нескольких таблиц?

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

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

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

Для чего нужна сводная таблица

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задумываясь над тем, как создать сводную таблицу в Excel, ознакомьтесь с пошаговой инструкцией:

1. Выделите ячейки, содержащие исходные данные, которые лягут в основу сводной таблицы.

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

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

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

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

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

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

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

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

Процесс объединения нескольких таблиц в одну не вызовет затруднений, если делать все поэтапно, следуя инструкции:

Для начала находим «Мастер сводных таблиц и диаграмм»: Панель быстрого доступа – затем «Другие команды» → далее «Настройки» → и наконец «Мастер сводных таблиц и диаграмм».
В первой таблице нажимаем «Мастер сводных таблиц и диаграмм», далее отмечаем, что хотим создать таблицу в «нескольких диапазонах консолидации», что означает, что мы будем объединять информацию, находящуюся в разных местах. Выбираем вид «Сводная таблица», жмем «Далее».
На следующем этапе используем команду «Создать поля» и идем «Далее».
Указываем диапазон данных, на основе которых будет строиться таблица, нажимаем «Добавить», проделываем то же самое со вторым диапазоном.
Далее в списке выбираем первый диапазон данных и ставим галочку у единицы. Так мы обозначаем первое поле сводной таблицы и озаглавливаем его «Магазин 1». То же самое проделываем со вторым диапазоном данных, только ставим галочку напротив цифры «2» и даем название полю «Магазин 2». Жмем «Далее».
Выбираем, где будет располагаться сводная таблица, на новом листе или существующем.

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

Изменение сводной таблицы

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

Использование фильтров в сводной таблице.

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

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

Со временем возникает необходимость вносить изменения в исходные данные. Нажмите правую кнопку мышки и выберете «Обновить».

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

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

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

Кеш сводной таблицы Excel 2016: достоинства и недостатки

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

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

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

Метод борьбы с этой проблемой довольно прост – нужно самостоятельно копировать и вставлять данные.

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

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

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

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

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

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

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

Источник: https://rdv-it.ru/news/sozdat-svodnuyu-tablitsu-excel/

Сводные таблицы в MS Excel

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

Сводные таблицы () могут пригодиться, если одновременно выполняются следующие условия:

имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра (CTRL+SHIFT+L), формул, Расширенного фильтра;
исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

каждый столбец должен иметь заголовок;
в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» — названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
в таблице должны отсутствовать полностью незаполненные строки и столбцы;
в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

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

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц.

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

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

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

В таблице имеются столбцы:

Товар – наименование партии товара, например, «Апельсины»;
Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
Дата поставки – Дата поставки Товара Поставщиком;
Регион продажи – Регион, в котором была реализована партия Товара;
Продажи – Стоимость, по которой удалось реализовать партию Товара;
Сбыт – срок фактической реализации Товара в Регионе (в днях);
Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен () откорректируем Имя таблицы на «Исходная_таблица».

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

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

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

В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.

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

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

Заполним сначала раздел Названия строк. Т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец — синонимы).

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

Теперь поставим галочку в Списке полей у поля Продажи.

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

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

Детализация данных Сводной таблицы

Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

Обновление Сводной таблицы

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

Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню .

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

Удаление Сводной таблицы

Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы).

Второй способ — удалить только саму Сводную таблицу: выделите любую ячейку Сводной таблицы, нажмите CTRL+A (будет выделена вся Сводная таблица), нажмите клавишу Delete.

Изменение функции итогов

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

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

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

Изменение порядка сортировки

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

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

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

Изменение формата числовых значений

Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат…
В появившемся окне выберите числовой формат и поставьте галочку флажка Разделитель групп разрядов.

Добавление новых полей

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

Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар).

Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

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

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

Сводная таблица примет следующий вид.
Меняем столбцы местами
Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место.

Удаление полей

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

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

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню ;
Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;

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

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

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

Несколько итогов для одного поля

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

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

Отключаем строки итогов

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

Группируем числа и Даты

Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:

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

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

Выделите одно значение Сводной таблицы в столбце Названия строк;
В меню выберите пункт Группировка по полю;
Появившееся окно заполните, как показано на рисунке ниже;

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт Разгруппировать в меню .
Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:
Теперь Сводная таблица показывает, сколько партий Товара поставлялось каждый месяц.

Условное форматирование ячеек Сводной таблицы

К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

Выделите все ячейки содержащие значения продаж;
Выберите пункт меню ;
Нажмите ОК.

Источник: https://excel2.ru/articles/svodnye-tablicy-v-ms-excel