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

На вкладке Вставка (Insert) появилась кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables), предлагающая несколько подходящих сводных таблиц разного типа на выбор в зависимости от вида исходных данных:

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

Фильтрация с помощью Временной Шкалы

К возможности фильтровать сводные таблицы срезами (slicers), появившейся в Excel 2010, добавили Временную шкалу (Timeline) — интерактивную графическую ось времени, которую можно вставить на вкладке Анализ (Analysis) . Выделив любой временной интервал на оси, мы получим в привязанной к ней сводной результаты именно за выбранный период:

 

Новый механизм расчета сводных таблиц (Data Object Model)

В Excel 2013 впервые реализована возможность строить сводные таблицы не на основе классического механизма кэша, как во всех прошлых версиях до этого. Теперь при построении сводной таблицы можно установить в нижней части диалога неприметную галочку Добавить эти данные в модель данных (Add to Data Object Model) и, тем самым, использовать для построения сводной «движок» Power Pivot, т.е. по-сути полноценную базу данных, встроенную в Excel:

 

Именно объектная модель позволяет сделать один из самых впечатляющих трюков этой версии — построить сводную сразу по нескольким диапазонам данных.

Связи и сводная по нескольким таблицам

На вкладке Данные (Data) появилась кнопка Отношения (Relationships), позволяющая связать нескольких таблиц по ключевым полям (столбцам) с помощью вот такого диалога:

 

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

 

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

 

Функция подсчета количества уникальных элементов

Если вы строите сводную с использованием Data Object Model, то в списке функций производимых над данными (правой кнопкой мыши по полю — Параметры поля) добавится еще одна новая — Число различных элементов (Distinct Count). Она вычислит не общее количество непустых элементов, как обычная функция Счет (Count), а количество неповторяющихся представителей.

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

Экспресс-просмотр

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

Логическим продолжением и развитием этой идеи стала новая функция Экспресс-просмотр (Quick Explore) в сводных таблицах Excel 2013.

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

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

Недостатки

Из замеченного в процессе работы:

• В сводных таблицах построенных на основе Объектной модели не работает группировка. Это хоть и не критично, но печально. Будем надеяться, что исправят.
• Иногда названия столбцов в списке полей не соответствуют реальности, т.е. берутся не из ячеек шапки, а непонятно откуда. Помогает предварительное форматирование исходных диапазонов как «умных» таблиц.
• Для выгрузки исходных данных из Объектной модели приходится использовать команду Данные — Подключения (Data — Connections).

This Excel tutorial explains how to create a pivot table in Excel 2013 (with screenshots and step-by-step instructions).

Question: How do I create a pivot table in Microsoft Excel 2013?

Answer: In this example, the data for the pivot table resides on Sheet1.

Highlight the cell where you’d like to see the pivot table. In this example, we’ve selected cell A1 on Sheet2.

Next, select the INSERT tab from the toolbar at the top of the screen. In the Tables group, click on the Tables button and select PivotTable from the popup menu.

A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we’ve chosen cells A1 to F16 in Sheet1.

Your pivot table should now appear as follows:

Next, choose the fields to add to the report. In this example, we’ve selected the checkboxes next to the Order ID and Quantity fields.

Next in the VALUES section, click on the «Sum of Order ID» and drag it to the ROWS section.

Finally, we want the title in cell A1 to show as «Order ID» instead of «Row Labels». To do this, select cell A1 and type Order ID.

Your pivot table should now display the total quantity for each Order ID as follows:

Last updated: January 25, 2017

Learning how to make a pivot table in Excel 2013 will give you a new tool in your Excel utility belt that can make sorting and analyzing data much simpler. A pivot table in Excel 2013 offers a variety of options for comparing data in columns, and grouping similar data in a way that would otherwise be difficult to do manually. When you create a pivot table in Excel 2013, you are taking data from your spreadsheet and inserting it into, by default, a new format in another sheet of your Excel workbook. Once the data has been reconfigured for the pivot table, you can adjust the way that it is organized and displayed to help you better understand the information that the spreadsheet contains. So read below to learn how to make a pivot table in Excel 2013.

If you are planning to install Office 2013 on a second computer, consider getting an Office 2013 subscription instead. This allows you to install your copy of Office on up to five computers, with the ability to remove and add those licenses if you get a new computer, or want to switch a license to a different computer.

How to Make a Pivot Table in Excel 2013

If you find yourself asking the question “what is a pivot table,” then know you are not alone. According to Wikipedia, a pivot table is “a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot table can automatically sort, count, total or average the data stored in one table or spreadsheet, displaying the results in a second table showing the summarized data”.

The way that I use pivot tables the most often it to quickly total amounts that are associated with different rows. For example, I have created a very simple spreadsheet below that lists sales amounts for three different members of a sales team. There is only a small amount of data in this table in an effort to keep this as simple as possible, but this same process can easily be expanded to handle much larger amounts of data, and prevent you from needing to write any formulas or macros to get the information that you want. We are going to create a simple pivot table that takes these 8 sales and combines them into a three-row report that shows the name of the salesperson and their total sales. So read below to learn how to create this type of pivot table in Excel 2013.

Step 1: Open the Excel 2013 spreadsheet that contains the data that you want to put into a pivot table.

Step 2: Use your mouse to highlight all of the data that you want to include in the pivot table.

Highlight the data you want to include in the pivot table

Step 3: Click the Insert tab at the top of the window, then click the PivotTable icon in the Tables section of the ribbon. This opens a Create PivotTable window.

Click Insert, then PivotTable

Step 4: I am going to leave these settings at their defaults, which will create a new worksheet for the pivot table. Click the OK button to continue.

Click the OK button

Step 5: Check the box to the left of each of your column names in the PivotTable Fields column at the right side of the window.

Check the box to the left of each column to include in the PivotTable

Step 6: View your pivot table in the spreadsheet. You will see that the initial data from my spreadsheet has been combined into easy to read totals that give me the totals that I wanted from my data.

Sample PivotTable

Now that your data is in the pivot table, you can manipulate it by double-clicking a column heading, then changing a setting on that screen. For example, if I wanted to view the number of sales instead of the total sales amount, I could change the option under Summarize value field by to Count instead of Sum.

Change your PivotTable options

Now that you have seen how a basic pivot table works, take some time to experiment with the feature to see what different types of data you can produce from your spreadsheets. Pivot tables are incredibly helpful for reducing the amount of manual addition that anyone should have to do with Excel data that can’t be easily totaled with a formula, so it can be a real time-saver. If you need more help with Excel 2013 pivot tables, visit Microsoft’s Excel 2013 help site, where they provide a very comprehensive view of the capabilities of pivot tables in Excel 2013.

Some helpful tips for working with pivot tables in Excel 2013

• The order that you click the boxes in step 5 can matter. If Excel 2013 isn’t displaying your data the way that you would want, try clicking the boxes next to each column in a different order.
• You can format your pivot table cells in a similar manner to how you would a regular cell. Simply right-click the cell, then choose the Format Cells option.
• There is an option on the Design tab under PivotTable Tools call Banded Rows. If you check that option Excel will automatically alternate the fill color for each row in your pivot table. This can make the data a little easier to read.
• Additionally, there are options on that Design tab for Subtotals, Grand Totals, Report Layout and Blank Rows. Once you have your data showing correctly in the pivot table, these options can help you create well-formatted reports that you can share with your colleagues.

For more information on Excel 2013, read our article about changing the default save location in Excel 2013. You can choose to save your spreadsheets to your computer by default, instead of the SkyDrive option that many Office 2013 installations will do by default.

Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.