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

Добавил пользователь Дмитрий К.
Обновлено: 05.10.2024

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

Исходные данные

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

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

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

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

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

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

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

Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

Выберите команду Сводная таблица [PivotTable].

В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].

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

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

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

После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 [PivotTable 1]. Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.

Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ, КОЛОННЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

отметить флажок напротив поля Группа;
щелкнуть по полу Группа правой кнопкой мыши и выбрать Добавить в названия строк [Add to Row Labels].

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

Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.

Мы все перегружены данными. Больше чем что-нибудь, нам нужен простой способ для обработки больших объемов данных и поиска в них смысла. Если вы работаете с таблицами в Microsoft Excel, то Сводные таблицы это идеальное решение для больших объёмов данных данных. Сводные таблицы так же просты в использовании и мощны, как и другие инструменты.

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

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

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

Что такое сводная таблица?

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

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

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

Может быть сложно понять данные из таблицы. Что, если я захотел узнать общую сумму, на которую я выставил счёт каждому клиенту? Конечно, я мог бы отфильтровать по каждому клиенту и подытожить данные, но есть сделать это можно куда проще.

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

Сводная таблица в Excel, показан пример счетов клиентов.

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

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

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

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

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

1 . Подготовьте сводную таблицу Excel

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

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

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

2. Создайте сводную таблицу в Excel

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

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

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

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

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

3. Перетащите поля

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

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

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

Конструктор отчётов сводных таблиц Excel.

Начнем с перетаскивания Client в область Стро́ки. Нажмите и перетащите Client из списка полей в пустую область под надписью Строки.

Когда мы перетаскиваем Client в область Стро́ки (Rows), сводная таблица Excel помещает каждого клиента в отдельную строку.

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

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

На скриншоте ниже ещё один пример. Я перетащил Project Type в область Столбцы. Теперь наша сводная таблица Excel показывает каждый тип проекта из наших исходных данных в качестве отдельного столбца.

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

Я отменю эти изменения и верну обратно поле Client в область Стро́ки.

Что, если мы хотим узнать, на сколько мы выставили счёт каждому клиенту? Помните что у нас в исходных данных в столбце Amount Billed (Сумма выставленных счетов). Нам также нужно добавить эти суммы в таблицу.

Поскольку мы хотим видеть эти суммы в виде числовых значений, перетащите Amount Billed в область Значения (н правый ижний угол).

Количество клиентов Client в сводной таблице Excel.

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

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

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

После того, как вы перебросили Amount Billed в область Значения, кликните правой кнопкой мыши по данным и выберите Итоги по > Сумма, чтобы подсчитать общую сумму на клиента, вместо подсчета количества раз.

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

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

Для этого давайте возьмем поле Project Type и перетащим его в область Строки, поместите его над Client.

Project Type добавлен в сводную таблицу Excel.

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

Повторение — мать учения!

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

У Боба Флиссера есть расширенное руководство по сводным таблицам, которое научит вас такому навыку, как Объединение данных из нескольких листов.
Если вам нужно очистить таблицу перед созданием сводной таблицы, вот как найти и удалить дубликаты — How to Find and Remove Duplicates.
Если вы хотите более подробно ознакомиться с функциями Excel, ознакомьтесь с нашей серией Как создавать и использовать формулы в Excel.

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

Здравствуй уважаемый, читатель!

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

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

Возникает закономерный вопрос, где же применение сводной таблицы даст наибольший эффект:

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

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

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

Каждый без исключения столбец обязан иметь собственный заголовок шапки;
Все строки и столбики вы обязаны заполнить, пробелы должны отсутствовать.
Для всех столбцов данных, должены быть определенные форматы ячеек, для тех данных, которые должны в них хранятся (пример, для поля “Дата” нужен формат календарной даты, а для поля “Контрагент” — формат текста и т.п.)
Значения в этих ячейках должны быть “единоличным”, это значит такими которые не делятся (к примеру, “Договор №23 от 03.09.2016 года” должен быть записан в 3 разных столбцах “Документ”, “Номер” и “Дата”, это позволит создавать гибкую и удобную систему). Также это возможно при помощи функции СЦЕПИТЬ.
Если вы ведете расходно-доходную табличку в которой кроме суммирования еще есть надобность отнять, прибавить и прочее, то и в базу первоначальных данных, в случаях с отрицательными значениями, вводите данные которые уже изначально со знаком “-” и тогда в свёрнутом виде вы получите нужный вам результат;
Сама конструкция вашей сводной таблицы обязана иметь оптимальный вид.

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

Как создается сводная таблица в Excel

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

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

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

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

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

2. Сводная таблица (мастер сводных таблиц)

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

Еще стоить поговорить о том, что при манипуляциях со сводными таблицами, Excel дополнительно формирует новое меню в панеле управления для работы с данными таблиц:

Но не стоит забывать что данные в нашей таблице самостоятельно не умеют обновляться, а это значит то что при изменении в исходных данных (для примера я просто убрал данные 2 строк) у нас изменился итог, а значится надо сообщить об этом нашей сводной таблице:

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

Пример можно взять здесь.

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

Не забудьте поблагодарить автора!

Золото убило больше душ, чем железо – тел.
В. Скотт

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

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

Рис. 3.20. Настройте стили 8 диалоговом окне Изменение экспресс-стиля сводной таблицы

Рис. 3.21. Новый стиль добавляется в верхнюю часть палитры

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

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

Читайте также: