Как сделать pivot table в excel

Создание сводных таблиц (PivotTables)

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

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

Пример задачи

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

В ней каждая отдельная строка содержит полную информацию об одном кредитном соглашении:

• название заемщика;
• номер сделки;
• дата выдачи и дата погашения по кредиту;
• тип кредита;
• сумма кредита

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

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

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

Подготовка данных

Примите во внимание следующие советы при подготовке данных перед созданием Сводных таблиц:

• используйте чистые табличные данные для достижения наилучших результатов;
• размещайте данные в столбцах, а не в строках;
• убедитесь, что все столбцы имеют заголовки с одной строчкой уникальных непустых подписей для каждого столбца. Избегайте двойных строк заголовков или объединенных ячеек;
• отформатируйте данные как «умную таблицу Excel» (щелкните где-либо на ваших данных, а затем выберите «Вставить» – «Таблица» ( Insert — Table ) в меню-ленте (ribbon);
• если у вас есть сложные или вложенные данные (например, кросс-таблица), используйте Power Query для их преобразования (например, можно воспользоваться макросом, чтобы отменить сведение данных ), чтобы они были организованы в столбце с одной строкой заголовка.

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

Выделяем всю нашу таблицу с данными и жмем в меню Вставка — Сводная таблица ( Insert — PivotTable ).

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

По умолчанию Эксель выбирает поместить сводную таблицу на новый лист (New Worksheet), однако мы можем в случае необходимости вручную выбрать текущий или любой другой лист книги (Existing Worksheet). Лучше для этого выбирать новый лист – тогда нет риска что сводная таблица «перекроется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку OK и переходим к самому интересному — этапу конструирования нашего отчета.

Работа с макетом

То, что Вы увидите, нажав кнопку OK называется макет (layout) сводной таблицы:

Работать с ним несложно – надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (PivotTable Field List) в области:

• строк (Row Labels);
• столбцов (Column Labels);
• фильтров (Report Filter);
• данных макета (Values).

Единственный нюанс – делайте это точнее, не промахнитесь! Уехали.

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

Форматирование Сводной таблицы

Выбираем форматирование сводной таблицы в меню PivotTables Tools – Design:

И получаем следующий вид:

Не так ли это все и сложно, не правда ли?

Недостатки

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

Статьи по теме:

• Изменение регистра букв в тексте
• Сумма прописью на украинском языке
• Поиск латиницы в кириллице и наоборот
• Транслитерация с украинского на английский

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

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

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

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

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

1. Убедитесь, что данные имеют заголовки столбцов или заголовки таблицы и что пустых строк нет.
2. Щелкните любую ячейку в диапазоне ячеек или таблице.
3. Нажмите кнопку >рекомендуемых срезов.
4. В диалоговом окне «Рекомендуемые складки» щелкните любой макет, чтобы получить предварительный просмотр, а затем выберите тот, который отображает данные нужным образом, и нажмите кнопку «ОК».

Создание сводной диаграммы

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

1. Выберите любую ячейку данных.
2. На вкладке «Вставка» в группе «Диаграммы» выберите «Рекомендуемые диаграммы».
3. На вкладке «Рекомендуемые диаграммы» выберите любую диаграмму со значком сводная диаграмма в верхнем углу. Предварительный просмотр сводная диаграмма в области предварительного просмотра.
4. Выбрав подходящую сводную диаграмму, нажмите ОК. Если вам не нравятся сводная диаграмма, нажмите кнопку сводная диаграмма на вкладке «Вставка», а не «Рекомендуемые диаграммы».
5. В сводная диаграмма щелкните любой интерактивный инструмент управления и выберите нужные параметры сортировки или фильтрации.

Создание сводной диаграммы

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

Создание сводной диаграммы

1. Выделите ячейку в таблице.
2. Нажмите кнопку Вставка и выберите

Данные о расходах семьи

Соответствующая сводная диаграмма

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

1. Выделите ячейку в таблице.
2. Нажмите кнопку Вставка и выберите Сводная диаграмма.
3. Выберите диаграмму.
4. Нажмите кнопку ОК.

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

1. Создайте сводную таблицу.
2. Выберите любую ячейку в сводной таблице.
3. На вкладке Вставка нажмите кнопку для вставки гистограммы, графика, круговой или лепестковой диаграммы. Обратите внимание на то, что другие типы диаграмм в настоящее время не поддерживают сводные таблицы. Например, диаграммы «дерево», а также статистические и комбинированные диаграммы использовать нельзя.
4. После вставки гистограммы, графика, круговой или лепестковой диаграммы вы сможете сводить данные в ней путем изменения или перемещения полей в списке полей сводной таблицы.
5. Вы также можете фильтровать данные в сводной таблице и использовать срезы. При этом также фильтруются данные в диаграмме.

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

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

См. также

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

См. также

Нужна дополнительная помощь?

Нужны дополнительные параметры?

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

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

Создание сводной таблицы с внешним источником данных

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

Вот как создать сводную таблицу с помощью существующего подключения к внешним данным:

1. Щелкните любую ячейку на листе.
2. Выберите Вставка >Сводная таблица.

В диалоговом окне Создание сводной таблицы щелкните Из внешнего источника данных.

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

1. В списке подключений выберите нужное подключение и нажмите кнопку » Открыть».
2. В разделе «Выбор места размещения отчета сводной таблицы» выберите расположение.
   • Чтобы поместить сводную таблицу на новый лист, начиная с ячейки A1, выберите » Создать лист».
   • Чтобы поместить сводную таблицу на активный лист, выберите «Существующий лист«, а затем в поле «Расположение» введите ячейку, в которой будет запущена сводная таблица.
3. Нажмите кнопку ОК. Excel добавляет пустую сводную таблицу и отображает список полей, чтобы можно было отобразить нужные поля и изменить их порядок для создания собственного макета.

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

Подключение к новому внешнему источнику данных

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

Щелкните «>из других источников».

• Щелкните «SQL Server«, чтобы создать подключение к SQL Server таблице.
• Щелкните «Из служб Analysis Services«, чтобы создать подключение к SQL Server analysis.

• На странице 1 введите сервер базы данных и укажите способ входа на сервер.
• На странице 2 введите базу данных, таблицу или запрос, содержащий нужные данные.
• На странице 3 введите файл подключения, который вы хотите создать.

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

Если таблиц несколько, установите флажок «Включить выбор нескольких таблиц», чтобы установить флажки нужных таблиц, а затем нажмите кнопку «ОК».

В диалоговом окне «Импорт данных» выберите способ просмотра данных в книге и место их размещения, а затем нажмите кнопку «ОК «.

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

Дополнительные сведения о сводных таблицах

• Создание сводной таблицы для анализа данных на листе
• Создание сводной таблицы для анализа данных в нескольких таблицах
• Упорядочение полей сводной таблицы с помощью списка полей
• Изменение диапазона исходных данных для сводной таблицы
• Обновление данных в сводной таблице
• Удаление сводной таблицы