Publications. Olap cubes in excel Additional Excel extensions for Microsoft OLAP

Analytical Analytical Processing (OLAP) is a technology that is used to organize large business databases and support business intelligence. OLAP databases are divided into one or more cubes, and each cube is organized by the cube administrator to suit how data is retrieved and analyzed to make it easier to create and use the PivotTable and PivotChart reports that you'll need.

In this article

What is business intelligence?

A business analyst often wants to get a bigger picture of the business, to view broader trends based on aggregated data, and to view trends broken down into any number of variables. Business intelligence is the process of extracting data from an OLAP database and analyzing that data to provide insights that can be used to make informed business decisions and take action. For example, with OLAP and business intelligence, you can answer the following questions about business data.

How does the total sales of all products in 2007 compare to sales since 2006?

How does this compare to the date and time for the benefit period over the past five years?

How much money did customers spend on 35 last year, and how has that behavior changed over time?

How many products were sold in two specific countries/regions this month as opposed to the same month last year?

For each customer age group What is the breakdown of profitability (both margin percentage and total) by product category?

Search for top and bottom sellers, distributors, suppliers, customers, partners and customers.

What is Online Analytical Processing (OLAP)?

OLAP (Online Analytical Processing) databases simplify business intelligence queries. OLAP is a database technology that is optimized for query and reporting, not transaction processing. The data source for OLAP is online transaction processing (OLTP) databases, which are typically stored in data warehouses. OLAP data is extracted from this historical data and combined into structures that allow complex analysis. OLAP data is also organized hierarchically and stored in cubes rather than tables. It is a complex technology that uses multidimensional structures to provide fast access to data for analysis. In this organization, for a PivotTable report or a PivotChart report, you can easily display high-level summary data, such as sales totals for an entire country or region, and also display details about sites where sales are particularly strong or weak.

OLAP databases are designed to speed up the loading of data. Since the OLAP server, not Microsoft office Excel calculates aggregated values, less data needs to be sent to Excel when you create or edit a report. This approach allows you to work with a larger amount of source data than if the data was organized in a traditional database where Excel retrieves all the individual records and calculates aggregated values.

OLAP databases contain two main types of data: measures, which are numeric data, quantities, and averages, which are used to make informed business decisions, and dimensions, which are categories used to organize these measures. OLAP databases help you organize your data with many levels of detail using the same categories you know for data analysis.

The following sections describe each component in detail below.

Cubic A data structure that combines measures across levels and hierarchies of each dimension that you want to analyze. Cubes combine multiple dimensions such as time, geography, and product lines with summary data such as sales and inventory. Cubes are not "Cubes" in the strict mathematical sense, as they don't necessarily have the same sides. However, they represent an apt metaphor for a complex concept.

measurements A set of values ​​in a cube that are based on a column in the cube's fact table, and which is usually a numeric value. Measures are central values ​​in a Cube that are pre-processed, processed, and analyzed. The most common examples are sales, income, income, and expenses.

Member An element in a hierarchy that represents one or more occurrences of data. An element can be either unique or non-unique. For example, 2007 and 2008 represent unique members at the year level of the time dimension, while January represents non-unique members at the month level because there is more than one January in the time dimension because it contains data for more than one year.

Calculated member A member of a dimension whose value is evaluated at run time using an expression. The values ​​of calculated members can be derived from the values ​​of other members. For example, the calculated item "profit" can be determined by subtracting the value of the item, as well as costs, from the value of the item, sales.

dimension A set of one or more ordered cube level hierarchies that the user understands and uses as the basis for data analysis. For example, a geographic dimension might include country/region, state/region, and city levels. In addition, a time dimension can include a hierarchy with year, quarter, month, and day levels. In a PivotTable report or PivotChart report, each hierarchy becomes a set of fields that can be expanded and collapsed to show lower or higher levels.

Hierarchy A logical tree structure that arranges the members of a dimension such that each member has one parent and zero or more children. A child element is a member of an earlier group in the hierarchy that is directly related to the current member. For example, in a time hierarchy containing quarter, month, and day levels, January is a child of Qtr1. The parent element is the lower level member in the hierarchy that is directly related to the current member. The parent value is usually a consolidation of the values ​​of all child elements. For example, in a time hierarchy containing quarter, month, and day levels, Qtr1 is the parent of January.

Level In a hierarchy, data can be organized into lower and higher levels of detail, such as years, quarters, months, and day levels in a time hierarchy.

OLAP Functions in Excel

Getting OLAP data You can connect to OLAP data sources in the same way as you can connect to other external data sources. You can work with databases created with Microsoft services SQL Server OLAP version 7.0, Microsoft SQL Server Analysis Services version 2000 and Microsoft SQL Server Analysis Services version 2005, Microsoft OLAP server products. Excel can also work with third party OLAP products that are compatible with OLE-DB for OLAP.

OLAP data can only be displayed as a PivotTable or PivotChart report, or in a worksheet function converted from a PivotTable report, not as an external data range. You can save OLAP PivotTable and PivotChart reports in report templates, and you can create Office Data Connection (ODC) files to connect to OLAP databases for OLAP queries. When you open an .odc file in Excel, an empty PivotTable report is displayed and is ready to be placed.

Create cube files for offline use You can create a standalone cube (.cub) file with a subset of data from an OLAP server database. Offline cube files are used to work with OLAP data when you are not connected to a network. With a cube, you can work with more data in a PivotTable report or PivotChart report than you otherwise would, and get the data faster. You can only create cube files if you are using an OLAP provider such as Microsoft SQL Analysis Services version 2005 that supports this feature.

Server actions A server action is an optional function that an OLAP cube administrator can define on a server that uses a cube element or measure as a parameter in a query to retrieve information in a cube, or to launch another application such as a browser. Excel supports URLs, Reports, Rowsets, drill-down, and drill-down detailed server actions, but does not support its own native operator and dataset.

KPI A KPI is a special calculated measure defined on the server that allows you to track "Key Performance Indicators", including status (the current value corresponds to a specific number). and trend (values ​​over time). When they are displayed, the server can send the appropriate icons, similar to the new Excel icon, to line up above or below the status levels (for example, for a stop icon), as well as scroll the value up or down (for example, a directional arrow icon).

Formatting on the server Cube administrators can create measures and calculated members using color formatting, font formatting, and conditional formatting rules that can be assigned as an enterprise standard business rule. For example, a server-side format for income could be a number currency format, the cell color is green if the value is greater than or equal to 30,000 and red if the value is less than 30,000, and the font style is bold if the value is less than 30,000, and if the value is positive - usual. greater than or equal to 30,000. For more information, see .

Office interface language A cube administrator can define translations for data and errors on the server for users who need to view PivotTable information in another language. This feature is defined as a file connection property, and the user's computer's locale and country must match the interface language.

Software components required to access OLAP data sources

OLAP provider To set up OLAP data sources for Excel, you need one of the following OLAP providers.

Microsoft OLAP provider Excel includes a data source driver and client software for accessing databases created with Microsoft SQL Server olap version 7.0, Microsoft SQL Server olap version 2000 (8.0), and Microsoft SQL Server Analysis services version 2005 (9 ,0).

Third Party OLAP Providers Other OLAP products require additional drivers and client software to be installed. To use Excel's OLAP data manipulation capabilities, a third-party product must comply with the OLE-DB for OLAP standard and be compatible with Microsoft Office. For information about installing and using a third-party OLAP provider, contact your system administrator or OLAP product provider.

Server Databases and Cube Files The Excel OLAP client software supports connections to two types of OLAP databases. If the database on the OLAP server is available on the network, you can get source data from it directly from it. If you have a standalone cube file that contains OLAP data or a cube definition file, you can connect to that file and get source data from it.

Data sources A data source provides access to all data in an OLAP database or offline cube file. Once you create an OLAP data source, you can base reports on it and return OLAP data to Excel as a PivotTable or PivotChart report, or as a sheet function converted from a PivotTable report.

Microsoft Query With Query, you can retrieve data from an external database such as Microsoft SQL or Microsoft Access. You do not need to use a query to retrieve data from an OLAP PivotTable associated with a cube file. Additional information .

Differences in OLAP Functions and Non-OLAP Source Data

If you are working with PivotTable reports and PivotCharts from OLAP source data and other types of source data, you will see some feature differences.

Data Extraction The OLAP server returns new data to Excel whenever the report layout changes. With other types of external data sources, you query all of the source data at once, or you can set parameters for the query only when displaying different report filter field items. In addition, you have several more options for updating the report.

In reports based on OLAP source data, report filter field options are not available, background query is not available, and the memory optimization option is not available.

Note: The memory optimization option is also not available for OLEDB data sources and PivotTable reports based on a range of cells.

Field types OLAP source data. dimension fields can only be used as rows (rows), columns (category), or page fields. Measure fields can only be used as value fields. For other types of source data, all fields can be used in any part of the report.

Access to detailed data For source OLAP data, the server determines the available levels of detail and calculates summary values, so the detail records that make up the summary values ​​may not be available. However, the server may provide property fields that you can display. Other source data types do not have property fields, but you can display basic information for data field and element values, and display elements without data.

OLAP report filter fields may not have all elements and command Show report filter pages not available.

Initial sort order For OLAP source data, the items are first displayed in the order they are returned by the OLAP server. You can sort or manually reorder the elements. For other source data types, the elements of the new report are first sorted by element name in ascending order.

Nimi OLAP servers provide summary values ​​directly to the report, so it is not possible to change the summary functions for value fields. For other source data types, you can change the aggregate function for the value field and use multiple summary functions for the same value field. You cannot create calculated fields and calculated members in reports with OLAP source data.

Subtotals In reports with OLAP source data, you cannot change the summary function for subtotals. With other types of source data, you can change the summary functions for subtotals and show or hide subtotals for all row and column fields.

For OLAP source data, you can include or exclude hidden items when calculating subtotals and grand totals. For other types of source data, you can include hidden items in report filter fields in subtotals, but hidden items in other fields will be excluded by default.

In a standard PivotTable, the source data is stored on the local hard drive. This way, you can always manage and reorganize them, even if you don't have access to the network. But this in no way applies to OLAP PivotTables. In OLAP PivotTables, the cache is never stored on the local hard drive. Therefore, immediately after disconnection from local network your pivot table will fail. You will not be able to move any of the fields in it.

If you still need to analyze OLAP data after going offline, create an offline data cube. An offline data cube is a separate file that is a PivotTable cache and stores OLAP data that is viewed after being disconnected from the local network. OLAP data copied into a pivot table can be printed, the site http://everest.ua describes this in detail.

To create a standalone data cube, first create an OLAP PivotTable. Place the cursor within the pivot table and click the button OLAP Tools(OLAP Tools) contextual tab Options (Tools), included in the group of contextual tabs Working with PivotTables (PivotTable Tools). Select the Offline OLAP command (Fig. 9.8).

Rice. 9.8. Create an offline data cube

The Offline OLAP Data Cube Settings dialog box appears. Click the Create Offline Data File button. You have started the Create Data Cube File Wizard. Click the Next button to continue the procedure.

First you need to specify the dimensions and levels that will be included in the data cube. In the dialog box, you must select the data that will be imported from the OLAP database. The idea is to specify only those dimensions that will be needed after the computer is disconnected from the local network. The more dimensions you specify, the larger the offline data cube will be.

Click the Next button to move to the next dialog box masters. It gives you the ability to specify members or data elements that will not be included in the cube. In particular, you won't need the Internet Sales-Extended Amount measure, so it will be unchecked in the list. A cleared checkbox indicates that the specified item will not be imported and take up extra space on the local hard drive.

In the last step, specify the location and name of the data cube. In our case, the cube file will be named MyOfflineCube.cub and will be located in the Work folder.

Data cube files have the extension .cub

After a while, Excel saves the offline data cube in the specified folder. To test it, double click on the file, which will automatically generate an Excel workbook that contains a PivotTable associated with the selected data cube. Once created, you can distribute the offline data cube to all interested users who are working in offline LAN mode.

Once connected to the local network, you can open the offline data cube file and update it, as well as the corresponding data table. The main principle is that the offline data cube is used only for work when the local network is disconnected, but it is mandatory to update it after the connection is restored. Attempting to update an offline data cube after the connection is broken will fail.

Working with Offline Cube Files

An offline cube file (.cub) stores data in the form of an OLAP (Online Analytical Processing) cube. This data may be part of the OLAP database on the OLAP server, or it may be generated independently of the OLAP database. Use an offline cube file to continue working with PivotTable and PivotChart reports when the server is unavailable or when you are offline.

Safety note: Use caution when using or distributing an offline cube file that contains sensitive or personal data. Instead of a cube file, it is recommended that you store the data in a workbook so that you can control access to the data using rights management. For more information, see Information Rights Management in Office.

When working with a PivotTable or PivotChart report that is based on OLAP Server source data, you can use the Offline Cube Wizard to copy the source data to a separate offline cube file on your computer. Creating these offline files requires an OLAP data provider that supports this capability, such as MSOLAP from Microsoft SQL Server Analysis Services, installed on the computer.

Note: The creation and use of offline cube files from Microsoft SQL Server Analysis Services is governed by the term and licensing of the Microsoft SQL Server installation. Review the relevant SQL Server edition licensing information.

Working with the Offline Cube Wizard

To create an offline cube file, you can select a subset of data in an OLAP database using the offline cube wizard and then save that subset. The report does not need to include all the fields included in the file, nor does it need to select any of them and the data fields available in the OLAP database. To keep the file at a minimum, you can include only the data you want to appear in the report. You can omit all dimensions, and for most dimension types, you can also exclude lower-level information and top-level items that you don't want to display. For all elements that you include, the property fields available in the database for those elements are also stored in the offline file.

Taking data offline and reconnecting it

To do this, you first need to create a PivotTable or PivotChart report based on the server database, and then create a standalone cube file from the report. After that, you can switch the report between the server database and the offline file at any time. For example, if you are using a laptop computer for home work and video travel, then reconnect the computer to the network.

The following are the basic steps you need to take to work offline with your data and then bring your data back online.

Create or open a PivotTable or PivotChart report based on the OLAP data that you want to access offline.

Create an offline cube file on your computer. In section Create an offline cube file from an OLAP server database(below in this article).

Disconnecting from the network and working with an offline cube file.

Go online and reattach the cube file offline. Check out the section Reconnecting the Offline Cube File to the OLAP Server Database(below in this article).

Update the offline cube file with new data and regenerate the offline cube file. Check out the section update and re-create the offline cube file(below in this article).

BLOG

Only quality posts

What are Excel PivotTables and OLAP Cubes

Watch the video for the article:

OLAP- this is English. online analytical processing, real-time analytical data processing technology. In simple terms - a storage with multidimensional data (Cube), even simpler - just a database from which you can get data in Excel and analyze it using the Excel tool - PivotTables.

Pivot tables is a user interface for displaying multidimensional data. In other words - a special kind of tables, with which you can make almost any report.

To make it clear, let's compare the "Regular Table" with the "Pivot Table"

Regular table:

Pivot table:

Main difference Pivot tables is the presence of a window Pivot Table Field List”, from which you can select the desired fields and get any table automatically!

How to use

open excel file, which is connected to the OLAP cube, for example "BIWEB":

Now what does this mean and how to use it?

Drag and drop the required fields to get, for example, the following table:

« Pluses» allow you to drill down into the report. In this example, 'Brand' is drilled down to 'Abbreviated Names' and 'Quarter' to 'Month', i.e. So:

Analytic functions in Excel (cube functions)

Microsoft is constantly adding new features to Excel in terms of data analysis and visualization. Working with information in Excel can be represented as three relatively independent layers:

• "correctly" organized source data
• mathematics (logic) of data processing
• data presentation

Rice. 1. Data analysis in Excel: a) raw data, b) measure in Power Pivot, c) dashboard; To enlarge an image, right-click on it and select Open image in new tab

Download note in Word or pdf format, examples in Excel format

Cube functions and pivot tables

The simplest and at the same time very powerful means of presenting data are pivot tables. They can be built from data contained in: a) an Excel worksheet, b) an OLAP cube, or c) a Power Pivot data model. In the last two cases, in addition to the pivot table, you can use analytical functions (cube functions) to generate a report on an Excel sheet. Pivot tables are easier. Cube functions are more complex, but provide more flexibility, especially in reporting, so they are widely used in dashboards.

The following discussion applies to cube formulas and pivot tables based on the Power Pivot model and, in a few cases, based on OLAP cubes.

An easy way to get the functions of cubes

When (if) you started learning VBA code, you learned that the easiest way to get the code is by recording a macro. Further, the code can be edited, loops, checks, etc. can be added. Similarly, the easiest way to get a set of cube functions is by converting the pivot table (Fig. 2). Stand on any cell of the pivot table, go to the tab Analysis, click on the button Facilities OLAP, and press Convert to formulas.

Rice. 2. Converting a PivotTable to a Cube Function Set

The numbers will be saved, and these will not be values, but formulas that extract data from the Power Pivot data model (Fig. 3). You can format the resulting table. In particular, you can delete and insert rows and columns inside the table. The slice remains, and it affects the data in the table. When the original data is updated, the numbers in the table will also be updated.

Rice. 3. Table based on cube formulas

CUBEVALUE() function

This is perhaps the main function of cubes. It is equivalent to the area Values pivot table. CUBEVALUE retrieves data from a Power Pivot cube or model and displays it outside of a PivotTable. This means that you are not limited by the boundaries of the pivot table and can create reports with countless possibilities.

Writing a formula from scratch

You don't have to convert the finished PivotTable. You can write any cube formula from scratch. For example, the following formula is entered in cell C10 (Fig. 4):

Rice. 4. The CUBEVALUE() function in cell C10 returns bicycle sales for all years, as in the pivot table

Little trick. To make it easier to read cube formulas, it is desirable that only one argument is placed on each line. You can shrink the Excel window. To do this, click on the icon Roll into the window located in the upper right corner of the screen. And then adjust the window size horizontally. An alternative option is to force the text of the formula to wrap on a new line. To do this, in the formula bar, put the cursor at the place where you want to make a transfer and press Alt + Enter.

Rice. 5. Minimize window

Syntax of the CUBEVALUE() function

The Excel help is completely accurate and completely useless for beginners:

CUBEVALUE(connection, [element_expression1], [element_expression2], ...)

Connection is a required argument; a text string representing the name of the connection to the cube.

element_expression– optional argument; a text string representing an MDX that returns an element or tuple in the cube. In addition, "element_expression" can be a set defined using the CUBESET function. Use "member_expression" as a slice to determine the part of the cube for which you want to return an aggregated value. If no measure is specified in element_expression, the default measure for that cube will be used.

Before proceeding to explain the syntax of the CUBEVALUE function, a few words about cubes, data models, and the cryptic tuple.

Some background on OLAP cubes and Power Pivot data models

OLAP data cubes ( O n l ine A nalytical P rocessing - operational data analysis) were developed specifically for analytical processing and fast data extraction from them. Imagine a three-dimensional space, where the axes are periods of time, cities and goods (Fig. 5a). The nodes of such a coordinate grid contain the values ​​of various measures: sales volume, profit, costs, number of units sold, etc. Now imagine that there are dozens of measurements, or even hundreds ... and there are also a lot of measures. This will be the multidimensional OLAP cube. Creating, configuring, and keeping up to date with OLAP cubes is the business of IT professionals.

Rice. 5a. 3D OLAP cube

Excel analytic formulas (cube formulas) extract axis titles (for example, Time), names of elements on these axes (August, September), values ​​of measures at the intersection of coordinates. It is this structure that allows cube-based pivot tables and cube formulas to be so flexible and adapt to the needs of users. PivotTables based on Excel worksheets do not use measures, so they are not as flexible for data analysis purposes.

Power Pivot is a relatively new Microsoft feature. This is a built-in Excel and somewhat independent environment with a familiar interface. Power Pivot is far superior to standard PivotTables. At the same time, developing cubes in Power Pivot is relatively simple, and most importantly, it does not require the participation of an IT specialist. Microsoft realizes its slogan: "Business analytics - to the masses!". Although Power Pivot models are not 100% cubes, they can also be referred to as cubes (see Mark Moore's introductory Power Pivot and Rob Colley's more extensive Power Pivot DAX Formulas for more details).

The main components of a cube are dimensions, hierarchies, levels, elements (or members; in English members) and measures (measures). Measurement - the main characteristic of the analyzed data. For example, product category, time period, sales geography. A dimension is something that we can put on one of the pivot table's axes. Each dimension, in addition to unique values, includes an element that aggregates all elements of this dimension.

Measurements are based on hierarchy. For example, a product category can be divided into subcategories, then into models, and finally into product names (Fig. 5b). Hierarchy allows you to create summary data and analyze it at various levels of structure. In our example, the hierarchy Category includes 4 level.

Elements(individual members) are present at all levels. For example, the Category level has four elements: Accessories, Bikes, Clothing, Components. Other levels have their own elements.

measures are calculated values, such as sales volume. Measures in cubes are stored in their own dimension, called (see Figure 9 below). Measures do not have hierarchies. Each measure calculates and stores a value for all dimensions and all members, and slices based on which dimension members we place on the axis. They also say which coordinates we will set, or which filter context we will set. For example, in fig. 5a in each small cube, the same measure is calculated - Profit. And the value returned by the measure depends on the coordinates. On the right in Figure 5a it is shown that Profit (in three coordinates) for Moscow in October on apples = 63,000 rubles. The measure can be interpreted, and as one of the measurements. For example, in fig. 5a instead of an axle Goods, place axis Measures with elements Volume of sales, Profit, Units sold. Then each cell will be some value, for example, Moscow, September, sales volume.

Tuple- several elements of different dimensions, specifying the coordinates along the axes of the cube, in which we calculate the measure. For example, in fig. 5a Tuple= Moscow, October, apples. Also a valid tuple is Perm, apples. Another one is apples, August. Dimensions not included in the tuple are implicitly present in the tuple, and are represented by the default member . Thus, a cell in a multidimensional space is always defined by a complete set of coordinates, even if some of them are omitted from the tuple. You cannot include two elements of the same dimension in a tuple, the syntax won't allow it. For example, the invalid tuple Moscow and Perm, apples. To implement such a multidimensional expression, you need a set of two tuples: Moscow and apples + Perm and apples.

Set of elements– several elements of the same dimension. For example, apples and pears. Set of tuples- several tuples, each of which consists of the same dimensions in the same sequence. For example, a set of two tuples: Moscow, apples and Perm, bananas.

Autocomplete to the rescue

Let's return to the syntax of the CUBEVALUE function. Let's use autocomplete. Start entering a formula in a cell:

Excel will suggest all connections available in the Excel workbook:

Rice. 6. Connection to Power Pivot data model is always called ThisWorkbookDataModel

Rice. 7. Connections to cubes

Let's continue entering the formula (in our case, for the data model):

Autocomplete will suggest all available tables and data model measures:

Rice. 8. Available elements of the first level - table names and a set of measures (highlighted)

Choose an icon Measures. Point to:

CUBEVALUE(» ThisWorkbookDataModel » ; » .

Autocomplete will suggest all available measures:

Rice. 9. Available elements of the second level in the set of measures

Choose a measure. Add quotes, closing bracket, press Enter.

CUBEVALUE(" ThisWorkbookDataModel " ; " . ")

Rice. 10. Formula CUBEVALUE in an Excel cell

Similarly, you can add a third argument to the formula:

VBA in Excel Excel.PivotTable Object and Working with PivotTables and OLAP Cubes in Excel

10.8 Working with pivot tables (PivotTable object)

Excel.PivotTable object, programmatically work with PivotTables and OLAP cubes in Excel using VBA, PivotCache object, create a pivot table layout

During the operation of most enterprises, the so-called raw data on activities is accumulated. For example, for a trade enterprise, data on sales of goods can be accumulated - for each purchase separately, for enterprises cellular communication- statistics of load on base stations, etc. Very often, the management of an enterprise needs analytical information that is generated on the basis of raw information - for example, to calculate the contribution of each type of product to the enterprise's income or the quality of service in the area of ​​a given station. It is very difficult to extract such information from raw information: you need to execute very complex SQL queries that take a long time and often interfere with ongoing work. Therefore, it is increasingly common for raw data to be summarized first in the Data Warehouse and then in OLAP cubes, which are very convenient for interactive analysis. It's easiest to think of OLAP cubes as multidimensional tables, in which, instead of the standard two dimensions (columns and rows, as in regular tables), there can be a lot of dimensions. The term "sectional" is commonly used to describe dimensions in a cube. For example, a marketing department may need information by time, by region, by product type, by sales channel, and so on. Using cubes (as opposed to standard SQL queries), it is very easy to get answers to questions like “how many products of this type were sold in the fourth quarter of last year in the Northwest region through regional distributors.

Of course, you cannot create such cubes in regular databases. OLAP cubes require specialized software products. SQL Server comes with an OLAP database from Microsoft called Analysis Services. There are OLAP solutions from Oracle, IBM, Sybase, etc.

To work with such cubes, a special client is built into Excel. In Russian it is called pivot table(on the graphical screen, it is available through the menu Data -> pivot table), and in English - pivot table. Accordingly, the object that this client represents is called PivotTable. It should be noted that it can work not only with OLAP cubes, but also with regular data in Excel tables or databases, but many features are lost.

The PivotTable and the PivotTable object are software products from Panorama Software that have been acquired by Microsoft and integrated into Excel. Therefore, working with the PivotTable object is somewhat different from working with other Excel objects. Figuring out what to do is often difficult. Therefore, it is recommended to actively use the macro recorder to receive hints. At the same time, when working with pivot tables, users often have to perform the same repetitive operations, so automation is necessary in many situations.

What does it look like to programmatically work with a pivot table?

The first thing we need to do is create a PivotCache object that will represent the set of records retrieved from the OLAP source. Very conditionally, this PivotCache object can be compared with QueryTable. Only one PivotCache object can be used per PivotTable object. The PivotCache object is created using the Add() method of the PivotCaches collection:

Dim PC1 As PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches is a standard collection, and of the methods that deserve detailed consideration, only the Add() method can be named in it. This method takes two parameters:

• SourceType- required, defines the type of data source for the pivot table. You can choose to create a PivotTable based on a range in Excel, data from a database, an external data source, another PivotTable, and so on. In practice, it usually makes sense to use OLAP only when there is a lot of data - accordingly, specialized external storage is needed (for example, Microsoft Analysis Services). In this situation, xlExternal is selected.
• SourceData- required in all cases except when the value of the first parameter is xlExternal. Strictly speaking, it defines the data range on the basis of which the PivotTable will be created. Usually takes a Range object.

The next task is to configure the parameters of the PivotCache object. As already mentioned, this object is very similar to QueryTable, and its set of properties and methods is very similar. Some of the most important properties and methods are:

• ADOConnection- the ability to return an ADO Connection object that is automatically created to connect to an external data source. Used to further configure connection properties.
• connection- works exactly the same as the QueryTable object property of the same name. It can accept a connection string, a prepared Recordset object, a text file, a Web request. Microsoft Query file. Most often, when working with OLAP, a connection string is written directly (since it makes little sense to receive a Recordset object, for example, to change data - OLAP data sources are almost always read-only). For example, setting this property to connect to the Foodmart database (Analysis Services sample database) on the LONDON server might look like this:

PC1.Connection = "OLEDB;Provider=MSOLAP.2;Data Source=LONDON1;Initial Catalog = FoodMart 2000"

• properties Command Type And command text describe the type of command that is sent to the database server and the text of the command itself in the same way. For example, to access the Sales cube and get it entirely cached on the client, you can use code like

• property LocalConnection allows you to connect to a local cube (*.cub file) created by Excel. Of course, it is highly discouraged to use such files for working with "production" data volumes - only for the purposes of creating layouts, etc.
• property MemoryUsed returns the number random access memory, used by PivotCache. If the PivotTable based on this PivotCache has not yet been created and opened, returns 0. Can be used for checks if your application will work on weak clients.
• property OLAP returns True if the PivotCache is connected to the OLAP server.
• OptimizeCache- the ability to optimize the structure of the cache. The initial loading of data will take longer, but then the speed of work may increase. For OLE DB sources does not work.

The remaining properties of the PivotCache object are the same as those of the QueryTable object, and therefore will not be discussed here.

The main method of the PivotCache object is the CreatePivotTable() method. With the help of this method, the next stage is performed - the creation of a pivot table (PivotTable object). This method takes four parameters:

• TableDestination is the only required parameter. Accepts a Range object, in the upper left corner of which the pivot table will be placed.
• table name- Pivot table name. If not specified, then the name of the form "PivotTable1" will be automatically generated.
• read data- if set to True, then all the contents of the cube will be automatically cached. You need to be very careful with this parameter, because its incorrect use can dramatically increase the load on the client.
• DefaultVersion- This property is usually not specified. Allows you to specify the version of the PivotTable that is being created. By default, the most recent version is used.

Creating a pivot table in the first cell of the first sheet of the workbook might look like this:

PC1.CreatePivotTable Range("A1")

The pivot table has been created, but immediately after creation it is empty. It provides four areas in which you can place fields from the source (on the graphic screen, all this can be configured either using the window Pivot Table Field List- it opens automatically or with a button Layout on the last screen of the PivotTable Wizard):

• column area- it contains those dimensions (“section” in which the data will be analyzed), the members of which are less;
• line area- those dimensions, the members of which are more;
• page area- those measurements by which it is only necessary to filter (for example, to show data only for such and such a region or only for such and such a year);
• data area- in fact, the central part of the table. Those numerical data (for example, the amount of sales) that we analyze.

Relying on the user to correctly place elements in all four areas is difficult. Also, it may take some time. Therefore, it is often necessary to arrange data in a PivotTable programmatically. This operation is performed using the CubeField object. The main property of this object is Orientation, it determines where this or that field will be located. For example, let's put the dimension Customers in the column area:

PT1.CubeFields("").Orientation = xlColumnField

Then - the Time dimension to the area of ​​strings:

PT1.CubeFields(""). Orientation = xlRowField

Then - the Product dimension to the page area:

PT1.CubeFields(""). Orientation = xlPageField

And finally, the indicator (numerical data for analysis) Unit Sales:

PT1.CubeFields(".").Orientation = xlDataField

Amazing close...

In the course of work, I often needed to make complex reports, I always tried to find something in common in them in order to compose them more simply and universally, I even wrote and published an article on this subject “The Osipov Tree”. However, they criticized my article and said that all the problems that I raised have long been resolved in MOLAP.RU v.2.4 (www.molap.rgtu.ru) and recommended to look at the pivot tables in EXCEL.
It turned out to be so simple that, having applied my ingenious hands to this, I got a very simple scheme for uploading data from 1C7 or any other database (hereinafter, 1C means any database) and analysis in OLAP.
I think many OLAP upload schemes are too complicated, I choose simplicity.

Characteristics :

1. Only EXCEL 2000 is required to work.
2. The user himself can design reports without programming.
3. Uploading from 1C7 in a simple text file format.
4. For accounting entries, there is already a universal processing for unloading that works in any configuration. For unloading other data, there are sample processing.
5. You can pre-design report forms and then apply them to different data without re-designing them.
6. Pretty good performance. At the first long stage, the data is first imported into EXCEL from a text file and an OLAP cube is built, and then any report can be built quite quickly based on this cube. For example, data on sales of goods in a store for 3 months with an assortment of 6000 goods are loaded into EXCEL in 8 minutes on Cel600-128M, the rating by goods and groups (OLAP report) is recalculated in 1 minute.
7. Data is downloaded from 1C7 in full for the specified period (all movements, for all warehouses, firms, accounts). When importing into EXCEL, it is possible to use filters that load only the necessary data for analysis (for example, from all movements, only sales).
8. Currently, methods have been developed for analyzing movements or residues, but not movements and residues together, although this is possible in principle.

What is OLAP : (www.molap.rgtu.ru)

Suppose you have a trading network. Let the data on trading operations be uploaded to a text file or a table of the form:

Date - transaction date
Month - month of operation
Week - week of operation
Type - purchase, sale, return, write-off
Counterparty - an external organization participating in the operation
Author - the person who issued the invoice

In 1C, for example, one line of this table will correspond to one line of the invoice, some fields (Contractor, Date) are taken from the invoice header.

Data for analysis is usually uploaded to the OLAP system for a certain period of time, from which, in principle, another period can be distinguished by using load filters.

This table is the source for OLAP analysis.

Report	measurements	Data	Filter
How many goods and for what amount are sold per day?	Date, Product	Quantity, Amount	View="sale"
Which counterparties supplied which goods for which amount per month?	Month, Contractor, Product	Sum	View="purchase"
For what amount did the operators of invoices of what type write out for the entire period of the report?		Sum

The user himself determines which of the fields of the table will be Dimensions, which Data and which Filters to apply. The system itself builds a report in a visual tabular form. Dimensions can be placed in the row or column headings of a report table.
As you can see, from one simple table, you can get a lot of data in the form of various reports.

How to use on your own :

Unpack the data from the distribution package exactly to the c:\fixin directory (for a trading system it is possible to c:\reports) . Read readme.txt and follow all instructions in it.

First you must write a processing that uploads data from 1C to a text file (table). You need to define the composition of the fields that will be uploaded.
For example, ready-made universal processing that works in any configuration and unloads postings for a period for OLAP analysis unloads the following fields for analysis:

Date|Day of the Week|Week|Year|Quarter|Month|Document|Company|Debit|DtNomenclature
|DtGroupNomenclature|DtSectionNomenclature|Credit|Amount|ValAmount|Quantity
|Currency|DtContractors|DtGroupContractors|KtContractors|KtGroupContractors|
CTMiscellaneousObjects

Where under the prefixes Dt (Kt) there are subcontos of Debit (Credit), Group is a group of this subconto (if any), Section is a group of a group, Class is a section group.

For a trading system, the fields can be as follows:

Direction|Type of Movement|For Cash|Product|Quantity|Price|Amount|Date|Company
|Warehouse|Currency|Document|WeekDay|Week|Year|Quarter|Month|Author
|ProductCategory|MovementCategory|CounterpartyCategory|ProductGroup
|ValAmount|Cost price|Contractor

For data analysis, tables "Analysis of movements.xls" ("Analysis of accounting.xls") are used. When opening them, do not disable macros, otherwise you will not be able to update reports (they are triggered by macros in the VBA language). These files take their initial data from the files C:\fixin\motions.txt (C:\fixin\buh.txt), otherwise they are the same. Therefore, you may need to copy your data to one of these files.
In order for your data to be loaded into EXCEL, select or write your own filter and click the "Generate" button on the "Conditions" sheet.
Report sheets begin with the prefix "From". Go to the report sheet, click "Refresh" and the report data will change according to the latest data loaded.
If you are not satisfied with the standard reports, there is a sheet OtchTemplate. Copy it to a new sheet and customize the report view by working with a pivot table on this sheet (more about working with pivot tables - in any book on EXCEL 2000). I recommend setting up reports on a small data set, and then run them on a large array, because there is no way to disable table redrawing every time the report layout changes.

Technical Notes :

When uploading data from 1C, the user selects the folder where to upload the file. I did this because it is likely that several files (leftovers and movements) will be uploaded in the near future. Then, by clicking the "Send" --> "To OLAP analysis in EXCEL 2000" button in the Explorer, the data is copied from the selected folder to the C:\fixin folder. (for this command to appear in the list of the "Send" command, you need to copy the file "For OLAP analysis in EXCEL 2000.bat" to the C:\Windows\SendTo directory) Therefore, upload the data immediately giving names to the motions.txt or buh.txt files.

Text file format:
The first line of the text file contains the column headings separated by "|", the remaining lines contain the values ​​of these columns separated by "|".

To import text files into Excel, Microsoft Query (part of EXCEL) is used; for its operation, it is necessary to have a shema.ini file in the import directory (C:\fixin) containing the following information:

ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI

Explanation: motions.txt and buh.txt is the name of the section, corresponds to the name of the imported file, describes how to import a text file into Excel. The remaining parameters mean that the first line contains the names of the columns, the column separator is "|", the character set is Windows ANSI (for DOS - OEM).
The field type is determined automatically based on the data contained in the column (date, number, string).
The list of fields does not need to be described anywhere - EXCEL and OLAP will determine for themselves which fields are contained in the file by the headings in the first line.

Attention, check your regional settings "Control Panel" --> "Regional Settings". In my processing, the numbers are uploaded with a comma separator, and the dates are in the "DD.MM.YYYY" format.

When you click the "Generate" button, the data is loaded into the pivot table on the "Base" sheet, and all reports on the "Return" sheets take data from this pivot table.

I understand that lovers of MS SQL Server and powerful databases will start grumbling that everything is too simplified for me, that my processing will die on a yearly sample, but first of all I want to give the benefits of OLAP analysis to medium-sized organizations. I would position this product as a yearly analysis tool for wholesalers, quarterly analysis for retail and operational analysis for any organization.

I had to tinker with VBA so that the data was taken from a file with any list of fields and it was possible to prepare report forms in advance.

Description of work in EXCEL (for users):

Instructions for using reports:
1. Send the downloaded data for analysis (check with the administrator). To do this, right-click on the folder into which you have uploaded data from 1C and select the "Submit" command, then "To OLAP analysis in EXCEL 2000".
2. Open the "Motion Analysis.xls" file
3. Select the Filter value, the filters you need can be added on the "Values" tab.
4. Click the "Generate" button, and the downloaded data will be loaded into EXCEL.
5. After loading the data into EXCEL, you can view various reports. To do this, just click the "Refresh" button in the selected report. Report sheets begin with Rep.
Attention! After you change the filter value, you need to click the "Generate" button again so that the data in EXCEL is reloaded from the upload file in accordance with the filters.

Processing from the demo:

Processing motionsbuh2011.ert is the latest version of unloading transactions from Accounting 7.7 for analysis in Excel. It has a checkbox "Append to file", which allows you to upload data in parts by periods, attaching them to the same file, and not uploading to the same file again:

Processing motionswork.ert uploads sales data for analysis in Excel.

Report examples:

Chess by posting:

Workload of operators by types of invoices:

P.S. :

It is clear that according to a similar scheme, you can organize the unloading of data from 1C8.
In 2011, I was contacted by a user who needed to refine this processing in 1C7 so that it could upload large amounts of data, I found an outsourcer and did this job. So the development is quite relevant.

Motionsbuh2011.ert processing has been improved to handle large data uploads.

Select a document from the archive to view:

18.5 KB cars.xls

14 KB countries.xls

Excel pr.r. 1.docx

Library
materials

Practical work 1

"The purpose and interface of MS Excel"

By completing this topic, you will:

1. Learn how to run spreadsheets;

2. Fix the basic concepts: cell, row, column, cell address;

3. Learn how to enter data into a cell and edit the formula bar;

5. How to select entire rows, a column, several cells located next to each other and the entire table.

The task: Get familiar with the basic elements of the MS Excel window.

Run Microsoft program Excel. Take a close look at the program window.

Documents created withEXCEL , are calledworkbooks and have the extension. XLS. The new workbook has three worksheets called SHEET1, SHEET2 and SHEET3. These names are listed on the sheet tabs at the bottom of the screen. To switch to another sheet, click on the name of this sheet.

Worksheet Actions:

Renaming a worksheet. Place the mouse pointer on the spine of the worksheet and double-click the left button or call the context menu and select the Rename command.Name the sheet "WORKOUT"

Insert a worksheet . Select the sheet tab "Sheet 2" before which you want to insert a new sheet, and using the context menuinsert a new sheet and name it "Sample" .

Deleting a worksheet. Select the sheet tab "Sheet 2", and using the context menudelete .

Cells and cell ranges.

The workspace consists of rows and columns. Rows are numbered from 1 to 65536. Columns are denoted by Latin letters: A, B, C, ..., AA, AB, ..., IV, in total - 256. There is a cell at the intersection of a row and a column. Each cell has its own address: the name of the column and the number of the row at the intersection of which it is located. For example, A1, CB234, P55.

To work with several cells, it is convenient to combine them into "ranges".

A range is cells arranged in a rectangle. For example, A3, A4, A5, B3, B4, B5. To write a range, use ": »: A3:B5

8:20 - all cells in rows 8 to 20.

A:A - all cells in column A.

N:R - all cells in columns from H to R.

The cell address can include the worksheet name: Sheet8!A3:B6.

2. Select cells in Excel

What we highlight

Actions

one cell

Click on it or move the selection with the arrow keys.

string

Clicking on a line number.

Column

Clicking on a column name.

Cell range

Drag the mouse pointer from the upper left corner of the range to the lower right.

Multiple Ranges

Select the first one, press SCHIFT + F 8, select the next one.

Whole table

Clicking on the "Select All" button (empty button to the left of the column names)

You can change the column width and row height by dragging the borders between them.

Use the scrollbars to determine how many rows the table has and what is the name of the last column.
Attention!!! To quickly reach the end of the table horizontally or vertically, you must press the key combinations: Ctrl+→ - end of columns or Ctrl+↓ - end of rows. Quick return to the beginning of the table - Ctrl+Home.

In cell A3, enter the address of the last column of the table.

How many rows are in the table? Enter the address of the last line in cell B3.

3. In EXCEL, you can enter the following types of data:

Numbers.

Text (such as headings and explanatory material).

Functions (for example, sum, sine, root).

Formulas.

Data is entered into cells. To enter data, the desired cell must be selected. There are two ways to enter data:

Just click in a cell and type in the data you want.

Click in the cell and in the formula bar and enter data in the formula bar.

Press ENTER.

Enter your name in cell N35, center it in the cell, and apply bold.
Enter the current year in cell C5 using the formula bar.

4. Change data.

Select a cell and press F 2 and change the data.

Select the cell e click in the formula bar and change the data there.

To change the formulas, you can use only the second method.

Change the data in a cell N35, add your last name. using any of the methods.

5. Entering formulas.

A formula is an arithmetic or logical expression by which calculations are made in a table. Formulas consist of cell references, operation signs, and functions. Ms EXCEL has a very large set of built-in functions. With their help, you can calculate the sum or arithmetic mean of values ​​from a certain range of cells, calculate interest on deposits, etc.

Formulas always start with an equal sign. After entering the formula in the corresponding cell, the result of the calculation appears, and the formula itself can be seen in the formula bar.

Action

Examples

+

Addition

A1+B1

-

Subtraction

A1 - B2

*

Multiplication

B3*C12

/

Division

A1 / B5

Exponentiation

A4 ^3

=, <,>,<=,>=,<>

relationship signs

A2

You can use parentheses in formulas to change the order of actions.

Autocomplete.

A very convenient tool that is used only in MS EXCEL is autocompletion of adjacent cells. For example, you need to enter the names of the months of the year in a column or row. This can be done manually. But there is a much more convenient way:

Enter the desired month in the first cell, for example January.

Select this cell. In the lower right corner of the selection frame is a small square - the fill handle.

Move the mouse pointer to the fill marker (it will take the form of a cross), while holding down the left mouse button, drag the marker in the desired direction. In this case, the current value of the cell will be visible next to the frame.

If you need to fill in some number series, then you need to enter the first two numbers in the adjacent two cells (for example, enter 1 in A4, and 2 in B4), select these two cells and drag the selection area by the marker to the desired size.

Selected document to view Excel pr.r. 2.docx

Library
materials

Practical work 2

"Entering data and formulas into MS Excel spreadsheet cells"

· Enter data of different types into cells: text, numbers, formulas.

The task: Perform the required data entry and simple calculations in the table.

Task execution technology:

1. Run the programMicrosoft Excel.

2. To cellA1 Sheet 2 enter the text: "Year of foundation of the school". Fix the data in the cell in any way you know.

3. To cellIN 1 enter the number - the year the school was founded (1971).

4. To cellC1 enter a number - the current year (2016).

Attention! Please note that in MS Excel, text data is left-aligned, while numbers and dates are right-aligned.

5. Highlight a cellD1 , use the keyboard to enter the formula for calculating the age of the school:=C1-B1

Attention! Formulas always start with an equal sign«=». Cell addresses must be entered in Latin letters without spaces. Cell addresses can be entered into formulas without using the keyboard, but simply by clicking on the corresponding cells with the mouse.

6. Delete the contents of a cellD1 and re-enter the formula using the mouse. In a cellD1 install sign«=» , then click on the cellC1, note the address of this cell appeared inD1, put up a sign«–» and click on the cellB1 , click(Enter).

7. To cellA2 enter text"My age".

8. To cellB2 enter your year of birth.

9. To cellC2 enter the current year.

10. Type in cellD2 formula for calculating your age in the current year(=C2-B2).

11. Highlight a cellC2. Enter next year number. Notice the recalculation in the cellD2 happened automatically.

12. Determine your age in 2025. To do this, replace the year in the cellC2 on the2025.

Independent work

The exercise: Calculate, using ET, will 130 rubles be enough for you to buy all the products that your mother ordered for you, and will it be enough to buy chips for 25 rubles?

Exercise technology:
o In cell A1, enter “No.”
o In cells A2, A3, enter “1”, “2”, select cells A2, A3, point to the lower right corner (a black cross should appear), stretch to cell A6
o In cell B1, enter “Name”
o In cell C1, enter “Price in rubles”
o In cell D1 enter “Quantity”
o In cell E1, enter “Cost”, etc.
o In the “Cost” column, all formulas are written in English!
o In formulas, instead of variables, cell names are written.
o After pressing Enter instead of the formula, a number immediately appears - the result of the calculation

o Calculate the total yourself.

Show the result to the teacher!

Selected document to view Excel pr.r. 3.docx

Library
materials

Practical work 3

MS Excel. Creating and editing a spreadsheet document»

By completing this topic, you will learn:

 Create and populate a table with data;

 Format and edit data in a cell;

 Use simple formulas in the table;

 Copy formulas.

The task:

1. Create a table containing the train schedule from Saratov station to Samara station. The general view of the "Schedule" table is shown in the figure.

2. Select cellA3 , change the word "Golden" to "Great" and press the keyEnter .

3. Select cellA6 , left-click on it twice and replace "Gloomy" with "Veselkovo"

4. Select cellA5 go to the formula bar and replace "Sennaya" with "Sennaya 1".

5. Supplement the table "Schedule" with calculations of the time of train stops in each settlement. (insert columns) Calculate the total stop time, the total travel time, the time it takes the train to travel from one location to another.

Task execution technology:

1. Move the Departure Time column from column C to column D. To do this, follow these steps:

Select block C1:C7; select a teamCut .
Place the cursor in cell D1;
Execute the commandInsert ;
Justify the column width to match the header size.;

2. Enter the text "Parking" in cell C1. Align the column width to match the header size.

3. Create a formula that calculates parking time in a locality.

4. You need to copy the formula to block C4:C7 using the fill handle. To do this, follow these steps:
There is a frame around the active cell, in the corner of which there is a small rectangle, grabbing it, extend the formula down to cell C7.

5. Enter in cell E1 the text "Travel time". Align the column width to match the header size.

6. Create a formula that calculates the time it takes a train to travel from one location to another.

7. Change the number format for blocks C2:C9 and E2:E9. To do this, follow these steps:

Select the block of cells C2:C9;
Home - Format - Other number formats - Time and set parameters (hours:minutes) .

Press keyOK .

8. Calculate the total parking time.
Select cell C9;
Click the buttonAutoSum on the toolbar;
Confirm the selection of cell block C3:C8 and press the keyEnter .

9. Enter text in cell B9. To do this, follow these steps:

Select cell B9;
Enter the text "Total parking time". Align the column width to match the header size.

10. Delete the contents of cell C3.

Select cell C3;
Execute the main menu command Edit - Clear or clickDelete on keyboard;
Attention! The computer automatically recalculates the amount in cell C9!!!

Execute the command Cancel or click the corresponding button on the toolbar.

11. Enter the text "Total Travel Time" in cell D9.

12. Calculate the total travel time.

13. Color the table and highlight the borders of the table.

Independent work

Calculate with a spreadsheetExcelexpenses of schoolchildren who are going to go on an excursion to another city.

Selected document to view Excel pr.r. 4.docx

Library
materials

Practical work 4

"Links. Built-in functions of MS Excel".

By completing this topic, you will learn:

Perform operations on copying, moving and autofilling individual cells and ranges.

Distinguish between types of links (absolute, relative, mixed)

Use the built-in mathematical and statistical functions of Excel in calculations.

MS Excel contains 320 built-in functions. The simplest way receiving complete information about any of them is to use the menureference . For convenience, functions in Excel are divided into categories (mathematical, financial, statistical, etc.).
The call to each function consists of two parts: the name of the function and the arguments in parentheses.

Table. Built-in functions in Excel

* Written without arguments.

table . Types of links

The task.

1. The cost of 1 kWh is set. electricity and meter readings for the previous and current months. It is necessary to calculate the electricity consumption for the past period and the cost of electricity consumed.

Work technology:

1. Align text in cells. Select cells A3:E3. Home - Format - Cell Format - Alignment: horizontally - centered, vertically - centered, display - word wrap.

2. In cell A4, enter: Sq. 1, in cell A5 enter: Sq. 2. Select cells A4:A5 and use the autocomplete marker to fill in the numbering of apartments up to 7 inclusive.

5. Fill in cells B4:C10 according to the picture.

6. In cell D4, enter the formula for finding the consumption of electricity / energy. And fill in the lines below with the autocomplete token.

7. In cell E4, enter the formula for finding the cost of electricity=D4*$B$1. And fill in the lines below with the autocomplete token.

Note!
When autofilling, the address of cell B1 does not change,
because an absolute reference is set.

8. In cell A11, enter the text "Statistical Data", select cells A11:B11 and click the "Merge and Center" button on the toolbar.

9. In cells A12:A15, enter the text shown in the figure.

10. Click on cell B12 and enter a mathematical functionSUM , to do this, click in the formula barby signfx and select a function, and confirm the range of cells.

11. Similarly, functions are set in cells B13:B15.

12. You performed the calculations on Sheet 1, rename it to Electricity.

Independent work

Exercise 1:

Calculate your age from the current year to 2030 using the autocomplete token. The year of your birth is an absolute reference. Perform calculations on Sheet 2. Rename Sheet 2 to Age.

Exercise 2: Create a table according to the example.In cellsI5: L12 andD13: L14 should be formulas: AVERAGE, COUNTIF, MAX, MIN. cellsB3: H12 are filled with information by you.

Selected document to view Excel pr.r. 5.docx

Library
materials

Practical work 5

By completing this topic, you will learn:

 Technologies for creating a spreadsheet document;

 Assign a type to the used data;

 Creating formulas and rules for changing links in them;

 Use Excel's built-in statistical functions for calculations.

Exercise 1. Calculate the number of days lived.

Work technology:

1. Launch the Excel application.

2. In cell A1, enter your date of birth (day, month, year - 12/20/97). Fix data entry.

3. View different date formats(Home - Cell Format - Other Number Formats - Date) . Convert date to typeHH.MM.YYYY. Example, 03/14/2001

4. Consider several types of date formats in cell A1.

5. Enter today's date in cell A2.

6. In cell A3, calculate the number of days lived using the formula. The result may be represented as a date, in which case it should be converted to a numeric type.

Task 2. The age of the students. According to a given list of students and their date of birth. Determine who was born earlier (later), determine who is the oldest (youngest).

Work technology:

1. Get the file Age. By LAN: Open the My Network Places folder -Boss-General Documents - Grade 9, find the Age file. Copy it in any way you know or download from this page at the bottom of the application.

2. Calculate the age of students. To calculate the age, you need to use the functionTODAY select today's current date, the student's date of birth is subtracted from it, then from the resulting date using the YEAR function, only the year is extracted from the date. From the resulting number, subtract 1900 - century and get the age of the student. In cell D3, write the formula=YEAR(TODAY()-C3)-1900 . The result may be presented as a date, in which case it should be converted tonumeric type.

3. Determine the earliest birthday. In cell C22, write the formula=MIN(C3:C21) ;

4. Determine the youngest student. In cell D22, write the formula=MIN(D3:D21) ;

5. Determine the latest birthday. In cell C23, write the formula=MAX(C3:C21) ;

6. Determine the oldest student. In cell D23, write the formula=MAX(D3:D21) .

Independent work:
A task. Make the necessary calculations of the growth of students in different units of measurement.

Selected document to view Excel pr.r. 6.docx

Library
materials

Practical work 6

MS Excel. Statistical functions” Part II.

Task 3. Using a spreadsheet, process the data using statistical functions. Information about the students of the class is given, including the average score for the quarter, age (year of birth) and gender. Determine the average score of boys, the proportion of excellent students among girls and the difference in the average score of students of different ages.

Solution:
Fill in the table with the initial data and carry out the necessary calculations.Pay attention to the format of the values ​​in the cells "GPA" (numeric) and "Date of birth" (date)

The table uses additional columns that are necessary to answer the questions posed in the task -student's age and is the studentexcellent student and girl simultaneously.
To calculate the age, the following formula was used (using the example of cell G4):

=INTEGER((TODAY()-E4)/365.25)

Let's comment on it. The student's date of birth is subtracted from today's date. Thus, we get the total number of days that have passed since the birth of the student. Dividing this number by 365.25 (the actual number of days in a year, 0.25 days for a normal year is offset by a leap year), we get the total number of years of the student; finally, highlighting the whole part, - the age of the student.

Whether a girl is an excellent student is determined by the formula (using the example of cell H4):

=IF(AND(D4=5,F4="w");1,0)

Let's start with the basic calculations.
First of all, it is required to determine the average score of girls. According to the definition, it is necessary to divide the total score of girls by their number. For these purposes, you can use the corresponding functions of the spreadsheet processor.

=SUMIF(F4:F15,"W",D4:D15)/COUNTIF(F4:F15,"W")

The SUMIF function allows you to sum the values ​​only in those cells of the range that meet the specified criterion (in our case, the child is a boy). The COUNTIF function counts the number of values ​​that meet the specified criteria. Thus, we get what we need.
To calculate the share of excellent students among all girls, we will attribute the number of excellent girls to the total number of girls (here we will use the set of values ​​from one of the auxiliary columns):

=SUM(H4:H15)/COUNTIF(F4:F15,"W")

Finally, we determine the difference in the average scores of children of different ages (we will use the auxiliary column in the calculationsAge ):

=ABS(SUMIF(G4:G15,15,D4:D15)/COUNTIF(G4:G15,15)-
SUMIF(G4:G15,16,D4:D15)/COUNTIF(G4:G15,16))

Please note that the data format in cells G18:G20 is numeric, two decimal places. Thus, the problem is completely solved. The figure shows the results of the solution for a given data set.

Selected document to view Excel pr.r. 7.docx

Library
materials

Practical work 7

"Creating charts using MS Excel"

By completing this topic, you will learn:

 Perform operations to create charts based on the data entered in the table;

 Edit chart data, its type and appearance.

What is a chart. The chart is intended for graphic representation of the data. Lines, bars, columns, sectors, and other visual elements are used to display numerical data entered in table cells. The appearance of a chart depends on its type. All charts, with the exception of the pie chart, have two axes: the horizontal one is the category axis and the vertical one is the value axis. When creating 3D charts, a third axis is added - the series axis. Often a chart contains elements such as a grid, titles, and a legend. Gridlines are extensions of the divisions found on the axes, titles are used to explain the individual elements of the chart and the nature of the data presented on it, the legend helps to identify the data series presented on the chart. There are two ways to add charts: embed them in the current worksheet and add a separate chart sheet. In the event that the diagram itself is of interest, then it is placed on a separate sheet. If you need to simultaneously view the chart and the data on the basis of which it was built, then an embedded chart is created.

The chart is saved and printed with the workbook.

After the diagram is generated, it will be possible to make changes to it. Before performing any actions with chart elements, select them by clicking on them with the left mouse button. After that, call the context menu with the right mouse button or use the corresponding buttonsChart toolbar .

A task: Use the spreadsheet to plot the function Y=3.5x–5. Where X takes values ​​from -6 to 6 in increments of 1.

Work technology:

1. Start the Excel spreadsheet.

2. In cell A1 enter "X", in cell B1 enter "Y".

3. Select the range of cells A1:B1, align the text in the cells to the center.

4. In cell A2, enter -6, and in cell A3, enter -5. Fill in the cells below with the autofill marker up to parameter 6.

5. In cell B2, enter the formula: =3.5*A2–5. Use the autocomplete token to extend this formula to the end of the data parameters.

6. Select the entire table you created and set its outer and inner boundaries.

7. Select the table header and fill the inner area.

8. Select the rest of the table cells and fill the inner area with a different color.

9. Select the entire table. Select from the menu bar Insert -Diagram , Type: Spot, View: Spot with smooth curves.

10. Move the chart below the table.

Independent work:

Plot the function y=sin(x)/ xon the segment [-10;10] with a step of 0.5.

Display the graph of the function: a) y=x; b) y=x 3 ; c) y=-x on the segment [-15;15] with step 1.

Open the "Cities" file (go to the network folder - 9th grade-Cities).

Calculate the cost of the conversation without the discount (column D) and the cost of the conversation with the discount (column F).

For a visual representation, build two pie charts. (1- diagram of the cost of a conversation without a discount; 2- diagram of the cost of a conversation with a discount).

Selected document to view Excel pr.r. 8.docx

Library
materials

Practical work 8

BUILDING GRAPHS AND DRAWINGS WITH MEANS MS EXCEL

1. Building a drawing"UMBRELLA"

Here are the functions whose graphs are involved in this image:

y1= -1/18x 2 + 12, xО[-12;12]

y2= ​​-1/8x 2 +6, xО[-4;4]

y3= -1/8(x+8) 2 + 6, xО[-12; -4]

y4= -1/8(x-8) 2 + 6, хО

y5= 2(x+3) 2 – 9, хн[-4;0]

y6=1.5(x+3) 2 – 10, xО[-4;0]

- Run MS EXCEL

- In a cellA1 enter variable designationX

· - Fill in the range of cells A2:A26 with numbers from -12 to 12.

For each graph of the function, we will introduce formulas sequentially. For y1= -1/8x 2 + 12, xО[-12;12], for
y2= ​​-1/8x 2 +6, xО[-4;4], etc.

The order of actions:

Set the cursor to a cellIN 1 and entery1

To cellIN 2 enter the formula=(-1/18)*A2^2 +12

Click Enter on keyboard

The value of the function is automatically calculated.

Expand the formula to cell A26

Likewise in a cellC10 (since we find the value of the function only on the interval x from [-4; 4]) we enter the formula for the graph of the functiony2= ​​-1/8x 2 +6. ETC.

The result should be the following ET

After all the values ​​of the functions are calculated, you canbuild graphs thesefunctions

Select the range of cells A1:G26

On the toolbar, selectInsert menu → Diagram

In the Chart Wizard window, selectSpot → Select desired view→ Click Ok .

The result should be the following image:

Task for individual work:

Plot the graphs of the functions in one coordinate system.x from -9 to 9 in steps of 1 . Get a drawing.

1. "Points"

2. "Cat" Filtering (selecting) data in the table allows you to display only those rows, the contents of the cells of which meet the specified condition or several conditions. Unlike sorting, data is not reordered during filtering, but only those records that do not meet the specified selection criteria are hidden.

Data filtering can be done in two ways:using autofilter or advanced filter.

To use the autofilter you need:

o set the cursor inside the table;

o choose a teamData - Filter - Autofilter;

o expand the list of the column by which the selection will be made;

o select a value or condition and set the selection criteria in the dialog boxCustom autofilter.

To restore all rows of the source table, select the row all in the drop-down list of the filter or select the commandData - Filter - Show all.

To cancel the filtering mode, place the cursor inside the table and select the menu command againData - Filter - Autofilter (uncheck).

An advanced filter allows you to create multiple selection criteria and perform more complex filtering of spreadsheet data by specifying a set of criteria for selection on several columns. Filtering records using the advanced filter is performed using the menu commandData - Filter - Advanced filter.

The task.

Create a table according to the example shown in the figure. Save it as Sort.xls.

Task execution technology:

1. Open Sort.xls document

2.

3. Execute a menu commandData - Sorting.

4. Select the first sort key "Ascending" (All departments in the table will be sorted alphabetically).

Recall that we need to print out a list of items left in the store (having a non-zero balance) every day, but to do this, we first need to get such a list, i.e. filter the data.

5. Set the frame cursor inside the data table.

6. Execute a menu commandData - Filter

7. Deselect the tables.

8. Each cell of the table header now has a "Down Arrow" button, it is not printed, allowing you to set the filter criteria. We want to leave all entries with a non-zero remainder.

9. Click on the arrow button that appears in the columnRemaining amount . A list will open to select from. Select lineCondition. Set the condition: > 0. ClickOK . The data in the table will be filtered.

10. Instead of a complete list of products, we will get a list of products sold to date.

11. The filter can be enhanced. If you additionally select a department, you can get a list of undelivered goods by department.

12. In order to see again the list of all unsold goods for all departments, you need to select the criterion "All" in the "Department" list.

13. In order not to get confused in your reports, insert a date that will automatically change according to the computer's system timeFormulas - Insert Function - Date and Time - Today .

Independent work

MS Excel. Statistical functions»

1 task (general) (2 points).

Using a spreadsheet, process the data using statistical functions.
1. Information about students in a class (10 people) is given, including grades for one month in mathematics. Count the number of fives, fours, twos and threes, find the average score of each student and the average score of the entire group. Create a chart illustrating the percentage of grades in a group.

2.1 task (2 points).

Four friends travel by three modes of transport: train, plane and boat. Nikolai swam 150 km on a steamboat, traveled 140 km on a train and flew 1,100 km on an airplane. Vasily sailed 200 km on a steamer, traveled 220 km on a train and flew 1,160 km on an airplane. Anatoly flew 1200 km on an airplane, traveled 110 km on a train and sailed 125 km on a steamboat. Maria traveled 130 km by train, flew 1500 km by plane and sailed 160 km by boat.
Build a spreadsheet based on the above data.

Add a column to the table that will display the total number of kilometers traveled by each of the guys.

Calculate the total number of kilometers that the guys traveled by train, flew by plane and sailed by steamboat (each mode of transport separately).

Calculate the total number of kilometers of all friends.

Determine the maximum and minimum number of kilometers traveled by friends for all modes of transport.

Determine the average number of kilometers for all modes of transport.

2.2 task (2 points).

Create a table “Lakes of Europe” using the following data for area (sq km) and deepest depth (m): Ladoga 17,700 and 225; Onega 9510 and 110; Caspian Sea 371,000 and 995; Venern 5550 and 100; Chudskoye with Pskov 3560 and 14; Balaton 591 and 11; Geneva 581 and 310; Vättern 1900 and 119; Constance 538 and 252; Mälaren 1140 and 64. Determine the largest and smallest lake in terms of area, the deepest and shallowest lake.

2.3 task (2 points).

Create a table “Rivers of Europe” using the following length (km) and basin area (thousand sq. km) data: Volga 3688 and 1350; Danube 2850 and 817; Rhine 1330 and 224; Elba 1150 and 148; Vistula 1090 and 198; Loire 1020 and 120; Ural 2530 and 220; Don 1870 and 422; Seine 780 and 79; Thames 340 and 15. Determine the longest and shortest rivers, calculate the total area of ​​river basins, the average length of rivers in the European part of Russia.

3 task (2 points).

The bank records the timeliness of payments of loans issued to several organizations. The loan amount and the amount already paid by the organization are known. Penalties are set for debtors: if the company repaid the loan by more than 70 percent, then the fine will be 10 percent of the debt, otherwise the fine will be 15 percent. Calculate the fine for each organization, the average fine, the total amount of money that the bank is going to receive additionally. Determine the average fine of budgetary organizations.

Find material for any lesson,

The first pivot table interface, also called pivot reports, was included with Excel back in 1993 (Excel version 5.0). Despite many useful functionality, it is practically not used in the work of most Excel users. Even experienced users often mean by the term "summary report" something built using complex formulas. Let's try to popularize the use of pivot tables in the daily work of economists. The article discusses the theoretical basis for creating summary reports, gives practical recommendations for their use, and also provides an example of accessing data based on several tables.

Terms of multivariate data analysis

Most economists have heard the terms "multidimensional data", "virtual cube", "OLAP technologies", etc. But with a detailed conversation, it usually turns out that almost everyone does not really understand what is at stake. That is, people mean something complex and usually not relevant to their daily activities. Actually it is not.

Multidimensional data, dimensions

It's safe to say that economists deal with multidimensional data almost all the time, but they try to present it in a predefined way using spreadsheets. Multidimensionality here means the ability to enter, view or analyze the same information with a change appearance, applying various groupings and sortings of data. For example, a sales plan can be analyzed according to the following criteria:

• types or groups of goods;
• brands or product categories;
• periods (month, quarter, year);
• buyers or groups of buyers;
• sales regions
• etc.

Each of the above criteria in terms of multivariate data analysis is called a "dimension". It can be said that a dimension characterizes information on a certain set of values. A special type of measurement of multidimensional information is "data". In our example, the sales plan data can be:

• volume of sales;
• Selling price;
• individual discount
• etc.

Theoretically, data can also be a standard dimension of multidimensional information (for example, you can group data by sales price), but data is usually a special type of value.

Thus, we can say that in practical work, economists use two types of information: multidimensional data ( actual and planned numbers with many features) and directories (characteristics or measurements of data).

OLAP

The abbreviation OLAP (online analytical processing) in literal translation sounds like “real-time analytical processing”. The definition is not very specific; almost any report of any software product can be summed up under it. By definition, OLAP implies a technology for working with special reports, including software, for obtaining and analyzing multidimensional structured data. One of the popular software products that implement OLAP technologies is SQL Server Analysis Server. Some even mistakenly consider him the only representative of the software implementation of this concept.

Virtual Data Cube

"Virtual Cube" (multidimensional cube, OLAP cube) is a technical term coined by some vendors of specialized software. OLAP systems usually prepare and store data in their own structures, and special analysis interfaces (such as Excel summary reports) access the data from these virtual cubes. At the same time, the use of such a dedicated storage is not at all necessary for processing multidimensional information. In general, virtual cube- this is an array of specially optimized multidimensional data that is used to create summary reports. It can be obtained both through specialized software tools and through simple access to database tables or any other source, such as an Excel spreadsheet.

pivot table

"Pivot Report" (pivot table, Pivot Table) is a user interface for displaying multidimensional data. Using this interface, you can group, sort, filter and change the location of data in order to obtain various analytical samples. Updating the report is done by simple means user interface, the data is automatically aggregated according to the specified rules, and no additional or re-entry of any information is required. The Excel pivot table interface is perhaps the most popular software product for working with multidimensional data. It supports both external data sources (OLAP cubes and relational databases) and internal spreadsheet ranges as a data source. Starting with version 2000 (9.0), Excel also supports a graphical form of displaying multidimensional data - a Pivot Chart.

The PivotTable interface implemented in Excel allows you to arrange dimensions of multidimensional data in the worksheet area. For simplicity, you can think of a pivot table as a report lying on top of a range of cells (in fact, there is a certain binding of cell formats to the fields of the pivot table). An Excel PivotTable has four display areas: filter, columns, rows, and data. Data dimensions are named pivot table fields. These fields have their own properties and display format.

Once again, I would like to draw your attention to the fact that the Excel pivot table is intended solely for data analysis without the possibility of editing information. Closer in meaning would be the widespread use of the term "pivot report" (Pivot Report), and that's how this interface was called until the year 2000. But for some reason, in subsequent versions, the developers abandoned it.

Editing pivot tables

By its definition, OLAP technology, in principle, does not imply the possibility of changing the source data when working with reports. Nevertheless, a whole class of software systems has emerged on the market that implements the capabilities of both analysis and direct editing of data in multidimensional tables. Basically, such systems are focused on solving budgeting problems.

Using Excel's built-in automation tools, you can solve many non-standard tasks. An example implementation of editing for Excel pivot tables based on worksheet data can be found on our website.

Preparing multidimensional data

Let's approach the practical application of pivot tables. Let's try to analyze sales data in different directions. File pivottableexample.xls consists of several sheets. Sheet Example contains basic information about sales for a certain period. For simplicity of the example, we will analyze the only numerical indicator - the volume of sales in kg. The following key data dimensions are available: product, buyer, and carrier (transport company). In addition, there are several additional dimensions of data that are attributes of the product: type, brand, category, supplier, as well as buyer: type. This data is collected on the Directories sheet. In practice, there may be many more such measurements.

Sheet Example contains standard remedy data analysis - autofilter. Looking at the example of filling in the table, it is obvious that sales data by dates (they are arranged in columns) lend themselves to normal analysis. In addition, using the autofilter, you can try to sum up the data by combinations of one or more key criteria. There is absolutely no information about brands, categories and types. It is not possible to group data with automatic summarization by a specific key (for example, by customers). In addition, the set of dates is fixed, and it will not be possible to view summary information for a certain period, for example, 3 days, by automatic means.

In general, the presence of a predefined date location in this example is the main drawback of the table. By arranging the dates by columns, we kind of predetermined the dimension of this table, thus depriving ourselves of the opportunity to use analysis using pivot tables.

First, we need to get rid of this shortcoming - i.e. remove the predefined location of one of the dimensions of the source data. An example of a valid table is a sheet Sales.

The table is in the form of an information entry log. Here, the date is an equal data dimension. It should also be noted that for subsequent analysis in pivot tables, the relative position of rows relative to each other (in other words, sorting) is completely indifferent. Records in relational databases have these properties. It is the analysis of large volumes of databases that is primarily focused on the interface of pivot tables. Therefore, you must adhere to these rules when working with a data source in the form of cell ranges. At the same time, no one forbids using Excel interface tools in your work - pivot tables analyze only data, and formatting, filters, grouping and sorting of source cells can be arbitrary.

From autofilter to summary report

Theoretically, on the Sales sheet data, it is already possible to analyze in three dimensions: goods, buyers and carriers. There is no data on the properties of products and buyers on this sheet, which, accordingly, will not allow them to be shown in the summary table. In the normal mode of creating a pivot table for source data, Excel does not allow you to link data from multiple tables by certain fields. You can get around this limitation software tools- see an example-addition to this article on our website. In order not to resort to program methods of information processing (especially since they are not universal), you should add additional characteristics directly to the journal entry form - see the SalesAnalysis sheet.

The use of VLOOKUP functions makes it easy to supplement the original data with missing characteristics. Now, by applying the autofilter, you can analyze the data in different dimensions. But the problem of groupings remains unresolved. For example, tracking the amount only for brands on certain dates is quite problematic. If you are limited to Excel formulas, then you need to build additional samples using the SUMIF function.

Now let's see what features the pivot table interface provides. On the sheet CodeAnalysis built several reports based on a range of cells with sheet data SalesAnalysis.

The first analysis table is built through the Excel 2007 interface Ribbon \ Insert \ PivotTable(in Excel 2000-2003 menu Data\PivotTable).

The second and third tables are created through copying and subsequent customization. The data source for all tables is the same. You can check this by changing the original data, then you need to update the summary reports data.

From our point of view, the advantages in the visibility of information are obvious. You can swap filters, columns and rows, hide certain groups of values ​​from any dimension, use manual drag and drop and automatic sorting.

Properties and Formatting

In addition to directly displaying data, there is a large set of options for displaying the appearance of pivot tables. Extra data can be hidden using filters. For a single element or field, it is easier to use the context menu item Delete(in version 2000-2003 Hide).

It is also desirable to set the display of other elements of the pivot table not through cell formatting, but through setting the field or element of the pivot table. To do this, move the mouse pointer to the desired element, wait for the appearance of a special cursor shape (in the form of an arrow), then select the selected element with a single click. After selection, you can change the view through the ribbon, context menu, or call the standard cell format dialog:

In addition, Excel 2007 introduced many predefined PivotTable display styles:

Notice that the control filters and drag areas are active in the chart.

Access to external data

As already noted, perhaps the greatest effect from the use of pivot tables can be obtained when accessing data from external sources - OLAP cubes and database queries. Such sources usually store large amounts of information, and also have a predefined relational structure, which makes it easy to define dimensions of multidimensional data (pivot table fields).

Excel supports many types of external data sources:

The greatest effect from the use of external sources of information can be achieved by using automation tools (VBA programs) both for obtaining data and for their preliminary processing in pivot tables.