Étiquette : data_processing

We present a simple example of creating a PivotTable for a data list with the following fields: Client, Country, Date, Amount. Place a button on the worksheet that calls the CreateStylePivotTable() procedure when clicked.

Put the code  into a standard module. Check what PivotTable you get on the new sheet).

Creating a formatted PivotTable (Standard Module)

Sub CreateStylePivotTable()
    Dim DCache As PivotCache
    Dim Sales As PivotTable
    ' Create the cache
    Set DCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
    ' Add a new worksheet to the workbook
    Worksheets.Add
    ' Create the PivotTable
    Set Sales = ActiveSheet.PivotTables.Add( _
        PivotCache:=DCache, TableDestination:=Range("A3"))
    ' Define the PivotTable layout
    With Sales
        .PivotFields("Страна").Orientation = xlPageField      ' Country → Page (Filter)
        .PivotFields("Дата").Orientation = xlColumnField      ' Date → Columns
        .PivotFields("Клиент").Orientation = xlRowField       ' Client → Rows
        .PivotFields("Стоимость").Orientation = xlDataField    ' Amount → Values
        ' Hide field captions
        .DisplayFieldCaptions = False
        ' Apply a PivotTable style
        .TableStyle2 = "PivotStyleDark5"
    End With
End Sub

A PivotTable is associated with a number of objects, listed in Table.
All of these objects are members of their respective collections: PivotTables, PivotFields, PivotFormulas, PivotItems, and PivotItemList.

Table. Objects Associated with a PivotTable

Object	Description
PivotTable	A PivotTable.
PivotCache	The memory cache allocated for the PivotTable. This object is returned by the PivotCache method of the PivotTable object.
PivotCell	A cell in the PivotTable. This object can be obtained via the PivotCell property of the Range object.
PivotField	A field in the PivotTable. This object is returned by the PivotFields method of the PivotTable object.
PivotFormula	A formula used for calculations in the PivotTable. This object is returned by the PivotFormulas method of the PivotTable object.
PivotItem	An item of a PivotTable field. This object is returned by the PivotItems method of the PivotTable object.
PivotLayout	Data about the placement of fields and axes of a PivotTable or PivotChart. This object can be obtained via the PivotLayout property of the Chart object.

The PivotTable Object

The PivotTable object encapsulates data about the PivotTable.
It is a member of the PivotTables collection.

Table. Methods of the PivotTable Object

Method	Description
AddDataField	Adds data fields.
AddFields	Adds rows, columns, and pages to the PivotTable.
CalculatedFields	Returns the CalculatedFields collection of all calculated fields in the PivotTable.
Format	Sets the report format of the PivotTable.
GetData	Returns the data from a specified PivotTable cell.
GetPivotData	Returns a range with information about the PivotTable.
ListFormulas	Creates a list of formulas on a separate sheet.
PivotCache	Returns the PivotCache object.
PivotFields	Returns the PivotFields collection.
PivotSelect	Selects a part of the PivotTable.
PivotTableWizard	Builds a PivotTable based on a given table.
RefreshTable	Refreshes the PivotTable. To recalculate manually, select the PivotTable and use Data → Refresh Data.
ShowPages	Sets the content of the Page area.
Update	Updates the links in the PivotTable.

Table. Properties of the PivotTable Object

Property	Description
ColumnFields, RowFields, DataFields, PageFields	Return an object (either a single field or a collection of fields) representing the columns, rows, data, or pages of the PivotTable.
VisibleFields, HiddenFields	Return an object (either a single field or a collection of fields) that is currently displayed (or hidden) in the PivotTable.

The PivotCache Object

The PivotCache object represents the memory cache allocated to a specific PivotTable.
It is a member of the PivotCaches collection and is returned by the PivotCache method of the PivotTable object.

The primary method of the PivotCaches collection is Add, with the following syntax:

Add(SourceType, SourceData)

• SourceType — required. Defines the type of data used to build the PivotTable. Acceptable constants (XlPivotTableSourceType):
  • xlConsolidation (consolidation of several worksheet ranges),
  • xlDatabase (Excel list or database),
  • xlExternal (external database),
  • xlPivotTable (another PivotTable).
• SourceData — optional. Specifies the source of data depending on the SourceType. Required if SourceType ≠ xlExternal.

Table. Methods of the PivotCache Object

Method	Description
CreatePivotTable	Creates a PivotTable object.CreatePivotTable(TableDestination, TableName, ReadData)• TableDestination — optional. Reference to the cell for the upper-left corner of the PivotTable. If creating on a new sheet, pass an empty string.• TableName — optional. The name of the PivotTable.• ReadData — optional Boolean. If True, the PivotTable is built on the entire external database.
MakeConnection	Establishes connections to the cache.
Refresh	Refreshes the cache.
ResetTimer	Resets the cache refresh timer.
SaveAsODC	Saves the cache to an ODC (Office Data Connection) file.

The PivotField Object

The PivotField object represents a PivotTable field.
It is a member of the PivotFields collection and is returned by the PivotField method of the PivotTable object.

Table. Methods of the PivotField Object

Method	Description
AddPageItem	Adds an item to a field.
AutoShow	Sets a rule for automatically displaying field items.
AutoSort	Sets a rule for automatic field sorting.
CalculatedItems	Returns the CalculatedItems collection.
Delete	Deletes the field.
PivotItems	Returns the PivotItems collection of all field items.

Table. Main Properties of the PivotField Object

Property	Description
Orientation	Returns the field’s location in the PivotTable. Possible values: xlColumnField, xlDataField, xlHidden, xlPageField, xlRowField.
Name	The name of the field.
Function	The summary function used in the field. Possible values: xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar, xlVarP.
Position	Returns the field’s position (first, second, etc.) among fields of the same location.

 

Before listing the various features and operations available for PivotTables, let us look at an example of how to prepare one using the built-in tools of Microsoft Office Excel 2010.

Suppose the worksheet Data contains a list of cars. The fields of this list are:

• Plate numbers (digits)
• Plate numbers (letters)
• Car make
• Year of manufacture
• Year of acquisition
• Color
• Mileage
• Price (in units)
• Inspection
• Owner

Steps to Create a PivotTable

• Go to a new worksheet in the workbook and run the PivotTable command, which is selected from the PivotTable list located in the Tables group on the Insert tab.
• In the Create PivotTable dialog box, in the Table or Range field, enter the reference to the data list of cars

and click OK.

NOTE
As data for a PivotTable, you can use an Excel list, an external data source (for example, database tables), consolidation ranges, or another PivotTable.

• A space will be reserved on the worksheet for creating the table, and the necessary tools will be displayed, including the contextual tabs Options and Design under the PivotTable Tools mod.

• Define the required elements of the PivotTable by dragging the corresponding fields to the areas for Filters, Rows, Columns, and Values in the PivotTable Field List window (Fig. 8.30).

NOTE
The Values area always contains calculated data. By default, the basic operation added is Sum.
To change the aggregate function or define a calculated field for the Values area, click the drop-down arrow next to the field in the Values area and choose Value Field Settings.
In the Value Field Settings dialog box,

you can:

• select the required aggregate function (on the Summarize Values By tab),
• define additional calculations (on the Show Values As tab),
• and set the data display format (using the Number Format button).
• Create a calculated field Operation Period, defined by the formula:

=’Year of acquisition’ – ‘Year of manufacture’

To do this, go to the Options contextual tab under PivotTable Tools, in the Calculations group, choose Fields, Items, & Sets → Calculated Field.
In the Insert Calculated Field dialog box

create the required field.
After definition, it will appear in the upper part of the PivotTable Field List window.
Add this field to the Values area and choose Sum as the aggregate function.

TIP
To make the PivotTable more readable, move the Values field (which appears after adding fields to the Values area) from the Column Labels area to the Row Labels area.

• Add another field, Inspection, as a filter for the PivotTable.
• Group the data by the field Year of manufacture: place the pointer in the row of column headings and choose the Group Field command in the Group group on the Options contextual tab under PivotTable Tools.

In the Grouping dialog box specify the start and end dates and the grouping interval.

NOTE
You can also group rows of the PivotTable manually: select the required rows and use the Group Selection command from the Group group on the Options contextual tab. The Ungroup button removes the grouping.

• Format the PivotTable using one of the available styles in the PivotTable Styles collection on the Design contextual tab under PivotTable Tools.
• The completed PivotTable is shown:

Additional Notes on PivotTables

• A PivotTable is only a display tool for data. Data cannot be edited directly in a PivotTable.
• To change PivotTable data, you must edit the source data and then refresh the PivotTable.
• To refresh, use Refresh or Refresh All commands in the Data group on the Options contextual tab under PivotTable Tools.

Common Actions with PivotTables

• Rename fields (this does not affect the source data).
• Rearrange fields for filters, rows, and columns by dragging them.
• Group field items at different hierarchy levels (and rename groups as desired).
• Show or hide details in groups; top-level items are placed at the top or left edge of the PivotTable.
• Create PivotCharts.
• Sort PivotTable items.
• Display PivotTable pages on separate worksheets (Show Report Filter Pages command in the Options tab).
• Manage grand totals and subtotals.
• Use different aggregate functions for data analysis.
• Insert calculated fields.
• Apply styles for formatting.

TIP
To delete a PivotTable:

• Place the cursor inside it.
• Use Select → Entire PivotTable from the Actions group on the Options tab.
• Then go to the Home tab, in the Editing group, choose Clear → Clear All.

PivotTables are a tool for grouping, summarizing, and analyzing data contained in Excel lists or in tables created in other applications.

PivotTables can be used for:

• summarizing large amounts of similar data;
• reorganizing data;
• filtering and grouping data;
• creating charts.

Visually, PivotTables are a structure that allows data to be displayed in a three-dimensional form.

Before you start creating a PivotTable, it is advisable to carefully think through its logic and its structure.

You need to define the following fields that will be used in the PivotTable layout:

• Row fields and Column fields of the table;
• Data fields for totals (with a selected operation, such as SUM, AVERAGE, COUNT, etc.) — placed at the intersection of rows and columns;
• Filter field (the PivotTable “Page” field) — for performing the necessary slices (filters), which makes it possible to present information in a three-dimensional view.
• Filter (Page field) of the PivotTable
• Column fields of the PivotTable
• Values (Data fields) of the PivotTable
• Row fields of the PivotTable

Creating a PivotTable

PivotTables are created using the PivotTable command, which is selected from the list of the same name located in the Tables group on the Insert tab.

TIP
Whenever possible, place the PivotTable on a separate worksheet. When refreshing or grouping data in the PivotTable, information on the worksheet next to the PivotTable may be hidden.

Let us demonstrate how the Scenario object works with a simple example.

We will create an expense table for LLC “Megatop” for January and forecast the expenses for the next month.

Our forecast will be based on the assumption that the relative structure of expenses in February will remain the same, while the absolute value will increase due to inflation.

We assume the inflation rate in February will be:

• best case: 1%
• worst case: 7%
• most likely: 3%

Setting up the analysis as follows Work with Scenarios

We will organize the scenario:

• Place a list box on the worksheet and, in the Properties window, set its Name property to lstScenarios.

When the workbook is opened, the Workbook_Open event procedure will automatically fill the list box with the names of possible inflation scenarios for the following month.

Clicking on a list item will:

1. • enter the chosen inflation rate into cell E2;
   • recalculate the projected expenses for the next month.

• In cell B7, enter a formula to calculate the total expenses:

=SUM(B2:B6)

• Select the range C2:C7 and enter the following array formula:

=B2:B7*(1+E2)

To confirm, press Ctrl+Shift+Enter (since this is an array formula).
This formula allows you to calculate the expected expenses for the entire range of values at once.

VBA Code Example

In code, such scenarios are implemented:.

Expense Scenarios Based on the Scenario Object. ThisWorkbook Module

Private Sub Workbook_Open()
    Dim sc As Scenario
    Dim i As Integer
    Dim V As Variant
    ' Delete existing scenarios
    For Each sc In Worksheets("January").Scenarios
        sc.Delete
    Next
    With Worksheets("January")
        ' Add new scenarios from values in column D (rows 10–12)
        For i = 10 To 12
            V = .Cells(i, 4).Value
            .Scenarios.Add Name:=.Cells(i, 3).Value, _
                ChangingCells:=.Range("E2"), Values:=V
        Next
        ' Configure the list box
        With .lstScenarios
            .ColumnCount = 2
            .ListFillRange = "C10:D12"
            .BoundColumn = 2
            .ListIndex = 0
        End With
    End With
End Sub

Expense Scenarios Based on the Scenario Object. Worksheet January Module

Private Sub lstScenarios_Click()
    Worksheets("January").Scenarios(lstScenarios.Text).Show
End Sub

This example demonstrates how to:

• Define different inflation scenarios;
• Use the Scenario Manager programmatically with VBA;
• Automatically update expenses when a scenario is selected from a list box.

The Scenario object allows you to store multiple values in a single cell and represents a scenario.
The Scenarios collection consists of Scenario objects and contains all the scenarios of a worksheet.

Table. Methods of the Scenarios Collection

Method	Description
Add	Adds a new scenario. Add(Name, ChangingCells, Values, Comment, Locked, Hidden) • Name — the name of the scenario; • ChangingCells — the range allocated for the scenario’s changing cells; • Values — an array of values entered into the changing cells; • Comment — a text string of comments; • Locked — a logical (Boolean) property. If set to True, modification of the scenario is blocked; • Hidden — a logical (Boolean) property. If set to True, the scenario is hidden.
CreateSummary	Adds a new worksheet to the workbook and creates a summary report. CreateSummary(ReportType, ResultCells) • ReportType — the type of report. Permissible values: → xlStandardSummary (a standard outline report); → xlSummaryPivotTable (a PivotTable report). • ResultCells — a reference to the cell or range of cells with formulas dependent on the values of the cells specified in the ChangingCells parameter of the Add method.The report is created on a separate worksheet and is not linked to the source data. It is very useful to assign names to the cells specified in the ResultCells parameter before generating the report. Otherwise, instead of meaningful names, the report will contain less comprehensible cell references.

Table Methods of the Scenario Object

Method	Description
Show	Displays the scenario by entering the scenario’s values into the changing cells.
Delete	Deletes the scenario.
ChangeScenario	Changes the group of changing cells in a scenario. ChangeScenario(ChangingCells, Values) • ChangingCells — the group of cells that will act as the new set of changing cells; • Values — an array with new values for the changing cells.

Table. Properties of the Scenario Object

Property	Description
ChangingCells	Returns the range of the changing cells. Example: ActiveSheet.Scenarios(1).ChangingCells.Select
Values	Returns an array of the current values of the changing cells. Example: ActiveSheet.Scenarios(1).Values = Range(« B1:B3 ») or ActiveSheet.Scenarios(1).Values = Array(1, 3, 5)

 

Let us consider an example. Suppose the project costs amount to 700 million rubles. The expected revenues over the next 5 years are, respectively, 70 million rubles, 90 million rubles, 300 million rubles, 250 million rubles, and 300 million rubles.

We need to assess the economic feasibility of the project by its internal rate of return, given that the market rate of return is 12%.

Also consider the following alternatives (project costs indicated with a minus sign):

• (–600; 50; 100; 200; 200; 300)
• (–650; 90; 120; 200; 250; 250)
• (–500; 100; 100; 200; 250; 250)

To calculate the internal rate of return (IRR), the following function is used:

=IRR(Values; Guess)

In this case, the function for solving the problem uses only the argument Values, one of which must be negative (project costs). If the internal rate of return exceeds the market rate of return, the project is considered economically feasible. Otherwise, the project should be rejected.

The solution for this example is shown:

Formulas for calculation:

• In cell B84:

=IRR(B75:B80)

• In cell C84:

=IF(B84>B82,"The project is economically feasible","The project must be rejected")

Creating Scenarios

Let us consider this example for all combinations of initial data.

To create (or modify) a scenario, use the Scenario Manager command from the What-If Analysis list in the Data Tools group on the Data tab.

In the Scenario Manager dialog box, click Add to add a new scenario. In the Add Scenario window 

enter a new name for the scenario and set the necessary parameters. After clicking OK, you can enter new values for the changing cells.

To save the results for the first scenario, it is not necessary to edit the cell values — simply click OK to confirm the default values and return to the Scenario Manager window.

Adding More Scenarios

To add new scenarios for this task, simply click Add again in the Scenario Manager window and repeat the above steps, changing the values of the initial data.

In Fig. :

• Scenario Turnover_Speed_1 corresponds to data (–700; 70; 90; 300; 250; 300)
• Scenario Turnover_Speed_2 corresponds to data (–600; 50; 100; 200; 200; 300)
• Scenario Turnover_Speed_3 corresponds to data (–650; 90; 120; 200; 250; 250)
• Scenario Turnover_Speed_4 corresponds to data (–500; 100; 100; 200; 250; 250)

By clicking Show, you can view the calculation results on the worksheet for the corresponding set of initial values.

Generating the Scenario Report

To obtain a summary report for all added scenarios, click Summary in the Scenario Manager window.

In the Scenario Summary dialog box

, select the desired type of report and specify the cells that contain the resulting functions.

When you click OK, a report for the scenarios is generated on the corresponding worksheet.

Each unique value in a cell, or each unique group of values for a group of cells, is called a scenario. Scenarios make it possible to perform so-called “what-if” data analysis. You can enter different values into key cells and observe what happens as a result. Quite often, it is necessary to have various solution options at hand, and scenarios provide exactly this possibility for the user.

The Scenario Manager in MS Excel allows you to automatically perform “what-if” analysis for different models. You can create several sets of input data (changing cells) for any number of variables and assign a name to each set. By the name of the selected data set, MS Excel generates analysis results on the worksheet. In addition, the Scenario Manager allows you to create a scenario summary report that displays the results of substituting different combinations of input parameters.

The Scenario Manager is opened with the Scenario Manager command, which is selected from the What-If Analysis list located in the Data Tools group on the Data tab of the ribbon.

In the window that appears, using the corresponding buttons, you can add a new scenario, edit, delete, or display an existing one, as well as merge several different scenarios and obtain a summary report for the existing scenarios.

We will use the Subtotal method and the Outline object to solve a simple task. We will work with a data list having the following fields: OrderID, ShippingCost, RecipientName, RecipientCity, RecipientCountry, which reflects the necessary shipping expenses for delivering orders to specific customers. We need to obtain summary data on the number of orders placed by each customer and the total postal (shipping) expenses for delivering these orders for each country.

Create a form and place on it a toggle switch, a button, a spin control, a text box, and a label.
When the toggle switch is on, it displays the caption Subtotals applied, and the worksheet will create subtotals that count the number of orders placed by each customer and the total postal (shipping) expenses for delivering these orders.

Clicking the Sort by RecipientCountry button performs sorting by the RecipientCountry field of the list, which must be done before creating the subtotals.

The spin control will allow you to manage the display of different outline levels of the subtotals. When the toggle switch is off, it displays the caption Subtotals removed, and the subtotals are removed from the worksheet. They are also removed when the dialog box is closed.

To complete the application, enter the corresponding code in the form module.

The Outline object encapsulates data about the worksheet outline.
The Outline property of a worksheet returns an Outline object.
Table lists the main properties of the Outline object.

Table. Main Properties of the Outline Object

Property	Description
AutomaticStyles	Accepts logical values. If this property is set to True, the outline is built based on automatic styles.
SummaryColumn	Returns the location of the summary columns. The allowable values are the following XlSummaryColumn constants: xlLeft (summary columns are located to the left of the columns being summarized), xlRight (summary columns are located to the right).
SummaryRow	Returns the location of the summary rows. The allowable values are the following XlSummaryRow constants: xlAbove (summary rows are located above the rows being summarized), xlBelow (summary rows are located below).

Displaying a Specified Number of Outline Levels

The Outline object has a single method, ShowLevels, which displays the specified number of outline levels for rows and columns.

ShowLevels(RowLevels, ColumnLevels)

• RowLevels — optional parameter that sets the number of displayed outline levels for rows.
• ColumnLevels — optional parameter that sets the number of displayed outline levels for columns.

Removing an Outline

The ClearOutline method of the Range object removes an outline.
For example, the following instruction removes the outline associated with the range A1:I40:

Range("A1:I40").ClearOutline

Displaying Outline Symbols

The DisplayOutline method of the Window object accepts logical values and controls the display of outline symbols.
For example, the following instruction hides outline symbols:

ActiveWindow.DisplayOutline = False

Automatic Outline Creation

The AutoOutline method of the Range object automatically creates an outline that replaces the existing one.
If the Range object is a single cell, the outline is created for the entire worksheet.
For example, the following instruction creates an outline for the range A1:I40:

Range("A1:I40").AutoOutline