Étiquette : data_processing

The purpose of structuring is to split the data contained in a worksheet into specific levels of detail. By using structure, it becomes easier to analyze and compare data.

If there is a strict dependency between the data, MS Excel allows you to automatically create a structure: in this case, MS Excel searches for cells that contain formulas summarizing the information in rows and that are located on the left. The data must be consistent in one direction. To perform automatic structuring, all detailed columns must be on one side of the total columns, and all detailed rows must be positioned relative to the totals either only below or only above them. If this condition is not met, the structure must be created manually.

A worksheet can contain only one structure, although it can be divided into several parts.

Showing and hiding structured data can affect parts of the worksheet that are not included in the hierarchy, since rows collapse and expand across the entire width of the worksheet, and columns — across the entire height of the worksheet.

When a structure is displayed, special symbols appear along the left and top edges of the worksheet. These symbols are used to show and hide levels of detail.

Table. Outline Symbols

To automatically create a structure, you should:

• Check that the total formulas contain references to detailed data located in one direction relative to the totals;
• For structuring part of a worksheet, select the desired cell range; for structuring the entire worksheet, select a single cell;
• Use the Create Outline command, choosing it from the Group list located in the Outline group on the Data tab of the ribbon.

To manually structure a worksheet, you need to:

• Select the necessary cells of rows and columns to be grouped into a structure, excluding the cell with the total formula;
• Use the Group command from the Group list located in the Outline group on the Data tab of the ribbon;
• In case of errors or to ungroup data, select the Ungroup command from the Ungroup list located in the Outline group on the Data tab of the ribbon;
• To show or hide structured data, use the Show Detail and Hide Detail commands, also located in the Outline group on the Data tab of the ribbon;
• To return the worksheet to its original state, use the Clear Outline command from the Ungroup list located in the Outline group on the Data tab of the ribbon.

For structured data, it is also possible to create charts based on specified outline levels.

Let us demonstrate, using a business case of constructing a summary table of the expenses of the company LLC “Alliance” for the reporting period, how to create and delete consolidating tables in code. For this, create a workbook containing several sheets, for example, January, February, March, with tables In addition, the workbook must contain an empty sheet named Summary. After that, add the corresponding code to a standard module and to the ThisWorkbook module.

NOTE
The program consolidates an unspecified number of tables. Therefore, you cannot use the Array function (with an unknown size) as the value of the Sources parameter of the Consolidate method. This issue is easily solved in the program — by introducing an additional variable of type Variant, assigning it the values of a dynamic array containing the addresses of the consolidated tables. Afterwards, this auxiliary variable is used as the value of the Sources parameter.

The standard module contains two procedures that implement the business logic of the project:

• ConsolidationBuilder: builds the consolidating table from any number of data sheets whose names differ from the sheet name of the consolidating table (i.e., from the sheet Summary). Before creating the required constructions, this procedure checks whether the Summary sheet already contains a table (more precisely, whether there is any data in its first column). If such data is present, no new construction is performed.
• ConsolidationKiller: deletes the consolidating table. More precisely, it removes the structure created by this table using the ClearOutline method and clears the cell contents using the Clear method. Before deletion, this procedure checks for the presence of such a structure on the worksheet, and if it does not exist, deletion is canceled as unnecessary.

In the ThisWorkbook module, there are two procedures that place the necessary buttons on the Add-ins tab of the ribbon when the workbook is opened, and remove them when the workbook is closed :

• The Open event procedure of the Workbook object constructs a toolbar Consolidation (classic style), on which two buttons are created: Consolidate and Delete Consolidation. These buttons appear on the Add-ins tab in the Custom Toolbars group and execute the procedures ConsolidationBuilder and ConsolidationKiller.
• The BeforeClose event procedure of the Workbook object removes the created Consolidation toolbar with its buttons when the workbook is closed (and, accordingly, the Add-ins tab will not appear when other workbooks are opened).

For programmatically constructing a consolidation table, the Consolidate method of the Range object is used. This method allows you to summarize and consolidate homogeneous data placed in several ranges. On a worksheet, the actions programmed by the Consolidate method correspond to the Consolidate command located in the Data Tools group on the Data tab of the ribbon.

expression.Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks)

• expression — a reference to the range or cell in its upper-left corner where the consolidation table will be created.
• Sources — optional parameter, specifies an array of references in R1C1 format to the ranges from which the consolidation table is built. The references must contain full range names including worksheet names. Example:

Array("'January'!R1C1:R5C3", "'February'!R1C1:R5C3")

• Function — optional parameter, specifies the function on which the consolidation table is based. Acceptable values are the following XlConsolidationFunction constants:
  • xlAverage (average),
  • xlCount (number of values),
  • xlCountNums (number of numbers),
  • xlMax (maximum),
  • xlMin (minimum),
  • xlProduct (product),
  • xlStDev (unbiased variance),
  • xlStDevP (biased variance),
  • xlSum (sum),
  • xlVar (unbiased deviation),
  • xlVarP (biased deviation).
• TopRow — optional Boolean parameter. Indicates whether consolidation is based on the column headers of the consolidated ranges.
• LeftColumn — optional Boolean parameter. Indicates whether consolidation is based on the row headers of the consolidated ranges.
• CreateLinks — optional Boolean parameter. Indicates whether the consolidated table is linked to the source tables. If set to True, the consolidated table is displayed as an outline.

Properties of the Worksheet Object Important for Data Consolidation

In data consolidation, three properties of the Worksheet object play an important role.

Table. Properties of the Worksheet object used in data consolidation

Consolidation by position is performed when it is planned to combine data located in the same cells of different ranges. Consolidation by category is performed when there are several ranges and the goal is to combine these data by rows or columns with identical labels.

Along with consolidation, it is also useful to apply outlining, which can be created automatically. Importantly, the worksheets intended for consolidation do not necessarily have to share the same structure.

Let us describe the process of data consolidation using the example of creating a consolidated table of expenses for the company Alliance LLC for the reporting period from January to March.

• Make sure that all ranges of the data to be consolidated are presented in list format.
  • If consolidation is performed by position, ensure that the layouts of all ranges match.
  • If consolidation is performed by category, ensure that the column or row labels to be combined are identical (case-sensitive).

So, check that your workbook contains three sheets — January, February, March — with the data in the format.

In addition, the workbook must also contain a Totals sheet, where the resulting table after consolidation will be placed.

• Select the top-left cell of the range where the consolidated data should be placed. In our case, select cell A2 of the Totals worksheet.

Select the Consolidate command located in the Data Tools group on the Data tab of the ribbon. The Consolidate dialog box will appear.

• From the Function drop-down list, choose the so-called summary function. This function defines the type of calculation performed when combining the data in the consolidation table. The following functions are available: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev (biased), StdDev (unbiased), Var (biased), Var (unbiased).
  In this case, choose Sum.
• Click in the Reference field, open the sheet containing the first data range for consolidation, enter the reference to this range (in this case January!$A$2:$E$8), and click Add. As a result, the reference to the range will be added to the All references list. Repeat this step for all ranges to be consolidated (in this case, February!$A$2:$E$8 and March!$A$2:$E$8).
  • If the consolidation table should be updated automatically whenever the source data changes, and later there will be no need to change or add data ranges, select the checkbox Create links to source data (this is what we should do in this case).
  • If consolidation is performed by position, leave all fields in the Use labels in group empty. In MS Excel, the labels of source rows and columns are not copied into the consolidated data. If you need labels in the consolidated data, copy them manually. In our case, this checkbox is not selected.
  • If consolidation is performed by category, in the Use labels in group select the checkboxes corresponding to the location of labels in the source ranges: top row, left column, or both. Any labels not matching those in other source areas will appear in separate rows or columns in the consolidated data. In our case, this checkbox is selected.
• Click OK.

As a result, a consolidated table will be created, shown :

As an example of consolidation using 3D formulas, let’s consider a business case of building a consolidated table of expenses for the company Alliance LLC for the reporting period from January to March. These expenses are collected in tables located on the worksheets January, February, and March. The company’s expenses are detailed quarterly.

So:

• Create a worksheet called Totals, where you will place the template of the report table.
• Enter in cell B3 the formula that calculates the total expenses for telephone in the first quarter from January through March:

=SUM(January:March!B3)

or the equivalent formula:

=SUM(January!B3,February!B3,March!B3)

• Place the mouse pointer on the fill handle and drag it down and to the right over the range B3:E8. This will allow you to calculate the total expenses for each category of expenses from June through August.

Consolidation Using 3D Formulas in Code

The procedure for creating a consolidated table based on 3D formulas, described in the previous section, can be automated with the following code.

The code includes a check for the existence of a worksheet named Totals. If such a sheet does not exist, it is created; if it already exists, a message is displayed and the process of building the consolidated table is interrupted.

Consolidation Using 3D Formulas

Sub DemoConsolidate3D()
    Dim rgn As Range
    Dim ws As Worksheet
    Dim str As String
    Dim nm As String   
    nm = "Totals"   
    For Each ws In Worksheets
        str = str & ws.Name & "!B3" & ";"
        If ws.Name = nm Then
            MsgBox "The Totals sheet already exists"
            Exit Sub
        End If
    Next   
    str = Left(str, Len(str) - 1)   
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = nm
    Worksheets("January").Range("A1:E8").Copy Worksheets(nm).Range("A1:E8")  
    Range("B3:E8").Clear
    Range("B3").FormulaLocal = "=SUM(" & str & ")"
    Range("B3").AutoFill Destination:=Range("B3:B8"), Type:=xlFillDefault
    Range("B3:B8").AutoFill Destination:=Range("B3:E8"), Type:=xlFillDefault
End Sub

Nested subtotals involve creating several levels of nesting for a single data list.

Nested subtotals are calculated as follows:

• Prepare the data list and place the cell pointer within it. Decide which totals you need — by levels of nesting.
• Sort by the required fields (use the Sort dialog box, which opens when executing the Sort command located in the Sort & Filter group on the Data tab of the ribbon).
• Calculate the totals using the Subtotal dialog box (which opens when executing the Subtotal command located in the Outline group on the Data tab of the ribbon).
  When creating nested subtotals, you should clearly understand the levels of totals and create them in the order of increasing detail: first — by the primary sort field, then, disabling the Replace current subtotals option (in the Subtotal dialog box) — by the second field, and so on.

Now let’s look at an example of nested subtotals, using the same sales list. Suppose we need to obtain the total number of products sold by a specific seller, taking into account the particular sale date.

• Select the list (or place the cell pointer in the list) and sort it (execute the Sort command located in the Sort & Filter group on the Data tab of the ribbon) by the fields Seller and Sale Date. To add each subsequent field in the Sort dialog box, click the Add Level button.

• Use the Subtotal command located in the Outline group on the Data tab of the ribbon.
• In the Subtotal dialog box that opens, set the parameters according to obtain the upper (first) level of totals — the total number of products sold by a specific seller.
• To obtain the second level of totals in the same list with the calculated subtotals, place the cell pointer back into the list and then use the Subtotal command again.
• In the Subtotal dialog box that opens, set the parameters according.

• The resulting subtotals are shown in Fig.

NOTE
When subtotals are added to a list, the list layout changes so that its structure becomes visible. By pressing the outline buttons , and , you can create a summary report by hiding details and showing only totals.

The Subtotal Method

The Subtotal method of the Range object adds subtotals to a data list based on changes in specified data fields. Subtotals allow summarizing data. The Subtotal method automatically inserts rows containing formulas that calculate the subtotals. It is necessary to sort the data correctly before activating this method; otherwise, the method may produce incorrect results. The Subtotal method is equivalent to executing the Subtotal command located in the Outline group on the Data tab of the ribbon.

expression.Subtotal(GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)

• expression — a reference to the cell or the entire range for which the subtotals are calculated.
• GroupBy — required parameter, specifies the field number by which the subtotals are calculated.
• Function — required parameter, specifies the function used for calculating the subtotals. The acceptable values are the following XlConsolidationFunction constants:
  • xlAverage (arithmetic mean),
  • xlCount (number of values),
  • xlCountNums (number of numbers),
  • xlMax (maximum),
  • xlMin (minimum),
  • xlProduct (product),
  • xlStDev (unbiased deviation),
  • xlStDevP (biased deviation),
  • xlSum (sum),
  • xlVar (unbiased variance),
  • xlVarP (biased variance).
• TotalList — required parameter, specifies an array of integers with the numbers of the fields for which subtotals are calculated.
• Replace — optional Boolean parameter. If True, existing subtotals will be replaced.
• PageBreaks — optional Boolean parameter. If True, a page break will be inserted after each group.
• SummaryBelowData — optional parameter that sets the location of the subtotals. Acceptable values are the following XlSummaryRow constants:
  • xlSummaryAbove (subtotals will be displayed above the data),
  • xlSummaryBelow (subtotals will be displayed below the data).

Removing Subtotals

The RemoveSubtotal method of the Range object removes subtotals from a worksheet.
For example, the following instruction removes subtotals associated with the range A1:I40:

Range("A1:I40").RemoveSubtotal

Simple subtotals are calculated as follows (subtotal method):

• Prepare the data list and place the cell pointer within it. Decide which totals you need.
• Sort by the required field (for example, use the Sort dialog box, which opens when executing the Sort command located in the Sort & Filter group on the Data tab of the ribbon).
• Calculate the totals using the Subtotal dialog box (it opens when executing the Subtotal command located in the Outline group on the Data tab of the ribbon).

TIP
To remove subtotals, place the pointer in the list with totals and use the Remove All button in the Subtotal dialog box.

Now, let’s look at an example of calculating simple subtotals. Suppose we have a data list with the following fields:

No., Seller, Product, Batch Number, Price, Quantity, Total, Sale Date, Customer. The task is to determine the number of products sold by a specific seller.

To calculate simple subtotals, perform the following steps:

• Select the list (or simply place the cell pointer in the list) and sort it (execute the Sort command located in the Sort & Filter group on the Data tab of the ribbon) by the Seller field 

• Use the Subtotal command located in the Outline group on the Data tab of the ribbon.
• In the Subtotal dialog box that opens, set the parameters according

and click OK.

• The resulting subtotals are shown :