Pivot table excel 2013 как сделать

Excel 2013: How to Use Pivot Tables

A pivot table is a tool that you can use to summarize data when you have a lot of it in a worksheet. A pivot table can count totals, give an average of the data, or sort data – in addition to other things. In this article, we are going to go in-depth as we learn to create and work with pivot tables.

We will discuss how to:

• Use Recommended PivotTables

• Create a pivot table from scratch

• Create multiple pivot tables

• Sort data in a pivot table

• Refresh data in a pivot table

• Verify data in a pivot table

Recommended Pivot Tables is a feature that is new to Excel 2013. With Recommended Pivot Tables, Excel analyzes the data that you have in your spreadsheet, then suggests possible pivot tables for you to use.

Take a look at our worksheet below:

This worksheet is simply a list of items, the price for each item, the quantity sold, the total price, and then the date sold.

To see the recommended pivot tables, click anywhere in the worksheet.                                                                                                                   

Go to the Insert tab, then click Recommended Pivot Tables in the Tables group.

You will then see the Recommended PivotTables dialogue box.

• In the dialogue box, you will see Excel's recommended PivotTables.
• As you can see, in our recommended pivot tables, Excel summarizes the data by the price of each item, the total price, and the number of items.
• If you want to use one of these suggested pivot tables, simply click on the pivot tables in the column on the left. 
• We have chosen the total price for each item.

1. That pivot table is now visible in the right column.
2. Click the OK button.
3. The pivot table is then placed into a new sheet, as shown below.

Creating a PivotTable from Scratch

We are going to use the same worksheet that we used when working with Recommended PivotTables. It is pictured below.

In starting to create your own pivot table, you do not need to select or highlight data. The only way that you would be required to select data is if you had a blank column or row within your data. Of course, you wouldn't want that included in your pivot table, so you would select the data you did want included.

To start creating your pivot table, click within the data, then go to the Insert tab. Click the PivotTable button.

In the Select a Table or Range field, Excel fills in the range of cells that contains your data.

You can compare the range of cells listed above with our worksheet, and you can see that all the data was included. Unless you need to change the range of cells, you do not need to enter anything into this field.

Next, decide if you want the new pivot table to appear in the existing worksheet (with your data) or in a new worksheet.   It's recommended that you place it into a different worksheet, but the choice is ultimately yours.   If you place it in the existing worksheet, you'll need to specify the location where you want to place it.

Click OK.

This is what you will see in a new worksheet:

This is where your pivot table will start.

If you look to the far right side of the Excel window, you will see PivotTable Fields, as shown below.

You will use this to create your pivot table. 

In the top section, you will see your columns listed.

The bottom section contains the sections of your pivot table.

• Filters is the filter above the pivot table.

• Columns are the column headings.

• Values are the crossover of the rows and columns.

The only thing in the bottom section that you need to make a pivot table work is Values. You'll find that Rows, Columns, and Filters help to organize the data and information in the pivot table.

To see what we mean, let's choose a column from the top half.

We are going to choose Item. We want each item to appear in a row, so we drag it to the Rows section in the bottom half. 

• Next, we are going to click on Items in the top half again, and drag it to the Values section.
• All you have to do is drag and drop.

Now, if we look at our pivot table, we see that Excel has summarized the number of each item in our worksheet.   Remember, this is the number of times the item appears in our data. It's not an inventory count.

1. Now let's say we want to add another column to our pivot table.  
2. We are going to drag Date Sold from the top section to the Columns section in the bottom half, as shown in the snapshot below.
3. Take a look at our pivot table:
4. Now we can see which item sold in which months. 

With pivot tables, there's something you need to keep in mind. If you drop a text field (such as Item) into values, Excel will assume you want to count the values. We did this, and it counted the number of occurrences of the item in our data.   If you drop a numerical field into Values, Excel will assume you want a sum of the items.

We've redone our pivot table by dragging Total Price into the Values section (and removing Item).

If you make a mistake and drag something into Values, Columns, Filters, or Rows that you don't want there, you can simply click and drag it back to the top section. This will remove it. 

You can also drag and drop to and from Values, Columns, Rows, and Filters. 

You can do these things to create the pivot table that you want.

Changing the Formatting and Formulas of PivotTables

It's easy to create a pivot table in Excel 2013, but that's just where the fun begins.   Now that you created a pivot table, it's time to learn how to format it.

Below is our pivot table.

If you wanted to format the data in the pivot table, you could do so by selecting a column or row, then going to the Home tab and applying formatting, such as changing the font type, font size, or font color. Those are very basic Excel skills and easy enough for you to do.

Instead, go to the panel on the right side of the Excel window where we created the pivot table. In the bottom section of the pivot table, click the downward arrow to the right of the field you want to format.

• In the snapshot below, we are going to click the downward arrow to the right of the Item field.
• Click on Field Settings, as circled in red above to format the field.
• However, in order to show you a better example, we are going to click on the field Sum of Total Price in the Values section, then choose Field Settings from the menu.
• We then see the Value Field Settings dialogue box. 
• In this dialogue box, we can change the name of the field in the pivot table by going to the Custom Name field.

Right now, we have the cells in this field formatted so that all numbers display as currency. If we wanted to change that, we would click the Number Format button.

1. Choose the formatting for our cells, then click OK.
2. This will return you to the Value Field Settings dialogue box. 

Also in the Value Field Settings dialogue box, we can change the function.   Right now, we have it at the default, which is Sum.   It can be changed to Count, Average, Max, Min, etc. All of these functions relate to the total price.   For example, average of the total price, and so on.

Click OK when you are finished.

You can also add the same field more than once.  

This means that, if you wanted, you could change the function for the Total Price field to Count.   Then, you could drag the Total Price field to the Values section again, which would display the Sum of Total Price.

Just make sure you change the number format that matches the function.   We wouldn't want Currency for the Count function, for example.

As you can see in our pivot table pictured below, you now have columns for the Count and Sum functions. 

Creating Different PivotTables Using the Same Data

You can create as many pivot tables as you need to using the same data from the same worksheet.  You can choose to place the pivot tables together, or you can place them in different worksheets.

We are going to create another pivot table by clicking in the data, then going to Insert>PivotTable.

As you can see in the dialogue box pictured above, we have chosen to place the new pivot table in the same worksheet as the pivot table that we created earlier in this article.

   Next, we clicked on the cell where we want the upper left hand corner of the pivot table to be placed.

• Click OK.
• You can now see our new pivot table below our existing pivot table.

Moving PivotTables

1. You can move a pivot table to a new location within a worksheet or to a new worksheet entirely.

2. To move a pivot table, click within the data of the pivot table, then click the Analyze tab under PivotTable Tools in the Ribbon, as pictured below.
3. Next, click Move PivotTable in the Actions group.

NOTE: Some Excel 2013 users may see an Action button instead. In this case, click Action>Move PivotTable.

• You will then see this dialogue box:
• You can choose to move the pivot table to a new worksheet, or you can click on a cell in the existing worksheet where you want to place the pivot table.
• Click the OK button when you are finished.
• The pivot table is moved for you.

Deleting PivotTables

1. To delete a pivot table, click within the data, then go to the Analyze tab under PivotTable Tools in the Ribbon.
2. Click the Select button, then choose Entire PivotTable. 
3. This selects the PivotTable.
4. You can then press Delete on your keyboard.

The Report Filter Option

• In the snapshot below, we have a simple pivot table.
• You can see that we have dragged and dropped the Item field into the Rows section, and the Total Price field into the Values section in order to create the pivot table.
• Now we are going to learn what happens when you drag a field to the Filters section.
• For this example, we are going to drag the Date Sold field to the Filters section.
• When we do this, we can see the filter is added above our pivot table.
• If we click the downward arrow to the right of Date Sold, we see our filtering options:
• We can now filter the data in our pivot table by the date the items were sold.
• Of course, we can also add another filter to our pivot table to further refine the data that's summarized in the table.
• Let's drag the Quantity Field to the Filters section.
• If we go to the pivot table and click the dropdown arrow beside Quantity, we can choose a quantity to filter the data by.
• Click OK when you've chosen how you want to filter the data.
• You will then see the changes to your pivot table.

Sorting Data in a PivotTable

Data in a pivot table can be sorted by row or column labels, as well as values.

Whenever you create a pivot table, Excel does the sorting for you.   Excel puts row and column labels in the order that they appeared in the original data worksheet. 

1. If you want to sort row or column labels, simply click the dropdown arrow that appears to the right of either Row Labels or Column labels.
2. You can see Row Labels circled in red below.
3. We are going to click the downward arrow. 
4. As you can see in the next snapshot, we are now given the ability to sort the labels alphabetically from A to Z, or Z to A.
5. We are going to click on Sort A to Z.
6. Now, if you want to sort values in a pivot table, click the downward arrow again. 
7. This time, click on More Sort Options.
8. You will then see the following dialogue box.
9. Choose if you want to sort the values from A to Z or Z to A by putting a check beside your choice.
10. Next, click the downward arrow in the field below your choice, as shown below.
11. In the snapshot above, you see that we can also sort by Sum of Total Price – or our values.
12. Select the option, then click OK.
13. Our data is then sorted by values.

Refreshing the Data in a PivotTable

Источник: https://www.universalclass.com/articles/computers/excel/excel-2013-how-to-use-pivot-tables.htm

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

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

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

См. Обновление данных.

• Построить сводную таблицу очень просто:
• (пример приведен на базе MS Excel 2003)
• Выделите массив данных (если желаете в будущем обновлять построенную таблицу по мере поступления данных в ее массив, выделяйте данные по колонкам). Пример:

Из меню программы выберите Data, PivotTable and PivotChartReport:

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

Для построениz сводной таблицы от листа MS Excel, выбирайте первый вариант – Microsoft Office Excel list or database:

Следующее окно выбора массива данных (мы его указали заранее, поэтому сейчас просто жмем Next):

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

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

В главном меню мастера построения сводных таблиц выбираем:

•Куда хотим разместить результат таблицы (Where do you want to put the Pivot Table report): New worksheet – для размещения таблицы (или будем называть ее отчетом) на новом листе текущей книги; Existing worksheet – для размещения отчета в текущем листе, текущей книги (при построении сводной таблицы от данных листа MS Excel, из возможных – только вариант New worksheet).

•Дальше переходим к построению таблицы, которое можем произвести через Layout…или, нажав на Finish, с помощью панели инструментов Pivot Table. Выберем Layout:

1. В окне диаграммы Layout «перетаскиваем» «кубики» – поля таблиц – из правой части диаграммы мастера в ее левую часть.
2. Как бы «бросаем» эти «кубики» в PAGE, ROW и COLUMN – для текстовых полей — измерений и в DATA – для числовых полей – фактов сводной таблицы.
3. Было:

Стало:

• Итак, расположили:
• ➢В область PAGE, фильтр данных таблицы.
• Значения каждого поля, расположенного в PAGE, будут служить параметром фильтра.

➢В область ROW – строчки сводной таблицы (в нашем примере номера недель). Как-нибудь расположите в ROW больше одного поля в качестве наглядного примера для понимания конечной «организации» нескольких полей «в одну строку».

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

1. ➢В область DATA числовые поля – для подсчета сводок таблицы, с указанием метода сводки: суммирование, количество, минимальное / максимальное значение и пр.
2. Метод сводки задается с помощью двойного щелчка мыши на поле.
3. После нажатий ОК и Finish, результат сводной таблицы выглядит примерно следующим образом:

В область DATA можно положить также одно и то же поле, но с разной формой подсчета фактов. Например, разместим поле Sales Pack два раза, один раз для подсчета суммы продаж (Sum), другой для подсчета количества записей по продажам (Count):

Результат и будет выглядеть согласно задуманному (см. также: как переименовать поля, если их авто-именование не устраивает):

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

• Например, если в такой таблице:
• поле Data подхватить и бросить в область Total, то ее вид преобразуется в такой:
• См. также:
• ➢Сводная таблица от внешнего источника данных
• ➢Сводная таблица от 10 млн. записей и больше

Источник: http://pashelp.ru/excelpivottable.html

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

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

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

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

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

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

1. 

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

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

3. Выберите команду Рекомендуемые сводные таблицы [Recommended PivotTables].

4. Выберите подходящую таблицу в списке слева. После выборы в окне справа вы увидите макет будущей таблицы.

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

1. 

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

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

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

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

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

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

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

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

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

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

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

Данную операцию можно сделать еще 2 способами:

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

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

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

Скачать

Источник: https://micro-solution.ru/excel/pivot-tables/first-pivot-table

Новые возможности сводных таблиц в Excel 2013

16388 30.12.2012 Скачать пример

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

На вкладке Вставка (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).

Источник: https://www.planetaexcel.ru/techniques/8/158/

How to use pivot tables in Excel — tutorial for beginners

In this tutorial you will learn what a PivotTable is, find a number of examples showing how to create and use pivot tables in Excel 2016, 2013, 2010 and 2007.

If you are working with large data sets in Excel, pivot table comes in really handy as a quick way to make an interactive summary from many records. Among other things, it can automatically sort and filter different subsets of data, count totals, calculate average as well as create cross tabulations.

Another benefit of using pivot tables is that you can set up and change the structure of your summary table simply by dragging and dropping the source table's columns. This rotation or pivoting gave the feature its name.

What is a pivot table in Excel?

An Excel PivotTable is a tool to explore and summarize large amounts of data, analyze related totals and present summary reports designed to:

• Present large amounts of data in a user-friendly way.
• Summarize data by categories and subcategories.
• Filter, group, sort and conditionally format different subsets of data so that you can focus on the most relevant information.
• Rotate rows to columns or columns to rows (which is called «pivoting») to view different summaries of the source data.
• Subtotal and aggregate numeric data in the spreadsheet.
• Expand or collapse the levels of data and drill down to see the details behind any total.
• Present concise and attractive online of your Excel data or printed reports.

One possible way to sum this long list of numbers by one or several conditions is to use Excel formulas as demonstrated in SUMIF and SUMIFS tutorials. However, if you want to compare several facts about each figure, using a pivot table is a far more efficient way.

In just a few mouse clicks, you can get a resilient and easily customizable summary table that totals the numbers by any field you want.

The screenshots above demonstrate just a few of many possible layouts. And the steps below show how you can quickly create your own pivot table in Excel 2016, 2013, 2010 and 2007.

How to make a pivot table in Excel: quick start

Many people think that creating an Excel pivot table is burdensome and time-consuming. But this is not true! Microsoft has been refining the technology for many years, and in the modern versions of Excel, the summary reports are user-friendly are incredibly fast. In fact, you can build your own summary table in just a couple of minutes. And here's how:

1. Organize your source data

Before creating a summary report, organize your data into rows and columns, and then convert your data range in to an Excel Table. To do this, select all of the data, go to the Insert tab and click Table.

Using an Excel Table for the source data gives you a very nice benefit — your data range becomes «dynamic». In this context, a dynamic range means that your table will automatically expand and shrink as you add or remove entries, so won't have to worry that your pivot table is missing the latest data.

Useful tips:

• Add unique, meaningful headings to your columns, they will turn into the field names later.
• Make sure your source table contains no blank rows or columns, and no subtotals.
• To make it easier to maintain your table, you can name your source table by switching to the Design tab and typing the name in the Table Name box the upper right corner of your worksheet.

2. Create a pivot table

Select any cell in the source data table, and then go to the Insert tab > Tables group > PivotTable.

This will open the Create PivotTable window. Make sure the correct table or range of cells is highlighted in the Table/Range field. Then choose the target location for your Excel pivot table:

• Selecting New Worksheet will place a table in a new worksheet starting at cell A1.
• Selecting Existing Worksheet will place your table at the specified location in an existing worksheet. In the Location box, click the Collapse Dialog button to choose the first cell where you want to position your table.

Clicking OK creates a blank pivot table in the target location, which will look similar to this:

Useful tips:

• In most cases, it makse sense to place a pivot table in a separate worksheet, this is especially recommended for beginners.
• If you are creating a pivot table from the data in another worksheet or workbook, include the workbook and worksheet names using the following syntax [workbook_name]sheet_name!range, for example, [Book1.xlsx]Sheet1!$A$1:$E$20. Alternatively, you can click the Collapse Dialog button and select a table or range of cells in another workbook using the mouse.
• It might be useful to create a pivot table and pivot chart at the same time. To do this, in Excel 2016 and Excel 2013, go to the Insert tab > Charts group, click the arrow below the PivotChart button, and then click PivotChart & PivotTable. In Excel 2010 and 2007, click the arrow below PivotTable, and then click PivotChart.

Источник: https://www.ablebits.com/office-addins-blog/2014/11/19/excel-pivot-table-tutorial/

Как сделать сводную таблицу в Excel c помощью надстройки PowerPivot

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

С помощью надстройки PowerPivot
(доступна для версий Excel 2010 и выше) можно сформировать сводные таблицы
в Excel, которые «умеют» намного больше, чем ваши обычные отчеты. Рассмотрим
подробнее, как создавать сводные таблицы в Excel используя PowerPivot.

Сначала я знакомлю
вас с базовыми возможностями этой надстройки для Excel, а также
встроенного в нее языка DAX (Data Analysis eXpression).

Затем
 рассмотрю некоторые улучшения в функциональности сводных таблиц в
Excel в случае их перевода на платформу PowerPivot.

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

Создание моделей
данных

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

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

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

» сводный отчет,
исходную таблицу фактов придется сначала дополнить новым столбцом «Менеджер»,
а затем рассчитать на его базе вспомогательный столбец «Дирекция».
Тогда таблица фактов вместо трех исходных столбцов измерений («Дата», «Сервис»
и «Компания») будет содержать целых пять («Дата», «Сервис», «Компания»,
«Сотрудник», «Дирекция»).

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

При кажущейся простоте такой способ
представления данных неэффективен.

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

В среде
PowerPivot хранение информации основано на альтернативном подходе. Вместо
того чтобы сводить все данные в одну большую сводную таблицу в Excel,
предлагается логически разбить их на отдельные структурные
блоки — таблицы фактов и 
таблицы-справочники (содержат вторичные аналитические признаки).

 2), а таблица
«Сотрудник_Изм» —
опосредованно через промежуточное отношение.

Рисунок 1. Структура формирования отчета в PowerPivot Рисунок 2. Формирование связей в PowerPivot

Переход на PowerPivot

Работа в среде
PowerPivot во многом похожа на разработку обычных баз данных. Для
пользователей, имеющих минимальный опыт создания БД в одной
из стандартных СУБД, например MS Access, переход на новую платформу будет делом простым и интуитивно
понятным. Нужно лишь помнить об основных отличительных особенностях баз
данных табличного формата (Tabular Model). Перечислю их.

1. Установление
связей возможно только между атрибутами разных таблиц. То есть запрещено
«замыкание» одних атрибутов таблицы на другие атрибуты этой же
таблицы (self-joins).

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

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

Последнее
обстоятельство является, пожалуй, самой ценной особенностью баз данных нового
формата. Поэтому раскроем эту тему более подробно. В среде MS Access установление связей между таблицами фактов
и измерения «Компании» (из рис.

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

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

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

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

• объединяют
  в одном месте информацию из разнородных источников, например Excel,
  текстового файла и базы данных;
• позволяют задать
  семантику атрибутов, то есть заменить технические названия столбцов
  на имена, понятные пользователю. При необходимости в модели можно
  изменить порядок сортировки атрибутов. Типичный пример — показывать
  названия месяцев в естественном,
  а не алфавитном порядке: «Янв», «Фев», «Мар» и т. д.

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

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

Выражения в PowerPivot, написанные на языке DAX, работают аналогичным
образом.

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

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

• Выражение 1:
• =CALCULATE(‘Таблица_Фактов'[Доход];PREVIOUSMONTH(‘Дата_Изм'[Дата]))
• Учитывая, что
  различные типы оператора CALCULATE применяются в разных расчетах, для
  выражения 1 есть более удобный вариант записи (выражение 2), позволяющий
  существенно упростить формулы DAX.
• Выражение 2:
• =[Доход](PREVIOUSMONTH(‘Дата_Изм'[Дата]))

Новые меры
добавляются в сводный отчет двумя способами. Первый — в среде PowerPivot как вычисляемое поле таблицы фактов.
Второй — непосредственно в Excel с помощью
вызова команды «Добавить новую меру» в диспетчере
метаданных.

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

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

Далее
в диспетчере метаданных выбираем таблицу фактов, в которую нужно
добавить новую меру, а затем команду «Добавить новую меру»
в контекстном меню. Она выведет на экран диалоговую форму,
позволяющую задать конкретные параметры показателя (см. рис. 3).

Рисунок 3. Диалоговое окно «Параметры меры» в PowerPivot

В поле «Формула»
введем выражение 2, а в поле «Имя меры» — строку «Доход_Пред».
После подтверждения кнопкой «ОК» в многомерное
пространство будет добавлена новая аналитика. Работать с ней можно так же,
как и с обычным полем сводной таблицы.

Источник: https://palikhov.wordpress.com/2019/04/23/pivot-table-by-power-pivot/

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

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

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

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

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

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

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

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

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

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

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

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

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

• Уже несколько месяцев, как я наслаждаюсь результатами этого подхода.
• Мне нравится. )

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