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

Working with an OLAP cube in MS Excel
1. Get permission to access the SQL Server Analysis Services (SSAS) OLAP cube
2. MS Excel 2016 / 2013 / 2010 must be installed on your computer (MS Excel 2007 is also possible, but it is not convenient to work in it, and MS Excel 2003 has very poor functionality)
3. Open MS Excel, launch the wizard for setting up a connection to the analytical service:

3.1 Specify the name or IP address of the current OLAP server (sometimes you need to specify an open port number, for example, 192.25.25.102:80); domain authentication is used:

3.2 Select a multidimensional database and an analytical cube (if you have access rights to the cube):

3.3 The settings for connecting to the analytical service will be saved in an odc file on your computer:

3.4 Select the type of report (pivot table/graph) and specify the location for its placement:

If a connection has already been created in the Excel workbook, then it can be used again: the main menu "Data" -> "Existing connections" -> select the connection in this workbook -> insert the pivot table into the specified cell.
4. Successfully connected to the cube, you can start interactive data analysis:

When starting interactive data analysis, it is necessary to determine which of the fields will be involved in the formation of rows, columns and filters (pages) of the pivot table. In general, the PivotTable is three-dimensional, and we can think of the third dimension as being perpendicular to the screen, and we're seeing sections that are parallel to the plane of the screen and are determined by which "page" is selected for display. Filtering can be done by dragging the appropriate dimension attributes to the report's filters area. Filtering limits the space of the cube, reducing the load on the OLAP server, so it is preferable to install the necessary filters first. You then place dimension attributes in the row and column areas, and measures in the PivotTable data area.

Every time the PivotTable changes, an MDX statement is automatically sent to the OLAP server and the data returned when executed. The larger and more complex the volume of processed data, calculated indicators, the longer the query execution time. You can cancel the execution of the request by pressing the key Escape. The last performed operations can be undone (Ctrl+Z) or redone (Ctrl+Y).

As a rule, for the most frequently used combinations of dimension attributes, the cube stores pre-calculated aggregated data, so the response time of such queries is several seconds. However, it is impossible to calculate all possible combinations of aggregations, since this can require a lot of time and storage space. Massive requests for data at the level of detail can require significant server computing resources, so their execution time can be long. After reading data from disk drives, the server places it in the RAM cache, which allows subsequent such requests to be executed instantly, since the data will be retrieved from the cache.

If you think that your request will be used frequently and its execution time is unsatisfactory, you can contact the analytical development support service to optimize the execution of the request.

After placing the hierarchy in the rows/columns area, it is possible to hide the individual levels:

For key attributes(less often - for attributes higher in the hierarchy) dimensions can have properties - descriptive characteristics that can be displayed both in tooltips and as fields:

If you want to display several field properties at once, you can use the corresponding dialog list:

User Defined Sets

Excel 2010 introduces the ability to interactively create custom (user-defined) sets from dimension members:

Unlike sets created and stored centrally on the side of the cube, custom sets are stored locally in an Excel workbook and can be used later:

Advanced users can create sets using MDX constructs:

Setting PivotTable Properties

The "Pivot Table Options..." item of the context menu (right-click within the pivot table) provides the ability to customize the pivot table, for example:
- "Output" tab, "Classic pivot table layout" option - the pivot table becomes interactive, fields can be dragged (Drag&Drop);
- "Output" tab, "Show elements without data in rows" parameter - the pivot table will display empty rows that do not contain any indicator values for the corresponding dimension elements;
- "Layout and format" tab, "Keep cell formatting when updating" parameter - in the pivot table, you can override and save the cell format when updating data;

Creating PivotCharts

For an existing OLAP pivot table, you can create a pivot chart - pie, bar, histogram, graph, scatter and other types of charts:

In this case, the pivot chart will be synchronized with the pivot table - when you change the composition of indicators, filters, dimensions in the pivot table, the pivot table is also updated.

Creating Dashboards

Select the original pivot table, copy it to the clipboard (Ctrl + C) and paste its copy (Ctrl + V), in which we will change the composition of the indicators:

To manage multiple pivot tables at the same time, let's insert a slicer (new functionality available starting from MS Excel 2010). Let's connect our Slicer to pivot tables - right-click within the slicer, select "Connections to Pivot Table ..." from the context menu. It should be noted that there can be multiple slicer panels that can serve PivotTables on different sheets at the same time, allowing you to create coordinated Dashboards.

Slicer panels can be configured: select the panel, then see "Size and properties...", "Slice settings", "Assign macro" in the right-click context menu or in the "Options" item of the main menu. So, it is possible to set the number of columns for the elements (buttons) of the slicer, the sizes of the slicer buttons and the panel, define the color scheme and design style for the slicer from the existing set (or create your own style), define your own panel title, assign a program macro through which you can expand panel functionality.

Executing an MDX query from Excel

First of all, you need to perform the DRILLTHROUGH operation on some indicator, i.e. go down to the detailed data (the detailed data is displayed on a separate sheet), and open the list of connections;
Open the connection properties, go to the "Definition" tab;
Select the default command type, and in the command text field place a pre-prepared MDX inquiry;
When the button is pressed, after checking the correct syntax of the request and the availability of appropriate access rights, the request will be executed on the server, and the result will be presented in the current sheet in the form of a regular flat table.
You can view the text of the MDX query generated by Excel by installing a free add-on that also provides other additional functionality.

Translation into other languages

The analytical cube supports localization into Russian and English (if necessary, localization into other languages is possible). Translations apply to the names of dimensions, hierarchies, attributes, folders, measures, as well as elements of individual hierarchies if there are translations for them on the side of accounting systems / data storage. To change the language, open the connection properties and add the following option in the connection string:
Extended Properties="Locale=1033"
where 1033 - English localization
1049 - Russian localization

Additional Excel Extensions for Microsoft OLAP

The ability to work with Microsoft OLAP cubes will increase if you use additional extensions, for example, OLAP PivotTable Extensions, thanks to which you can use fast search by dimension:

website 2011-01-11 16:57:00Z Last modified: 2017-10-15 16:33:59Z Age audience: 14-70

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:

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:

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 grumble that everything is too simplified for me, that my processing will be bent 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 retailers, 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.

The problems of analytics, OLAP, data warehouses are of increasing interest to Russian IT specialists. To date, our computer press and the Internet have published a lot of good, academic in the way of presenting materials on this topic, including introductory ones. We bring to your attention an article in which we deliberately try to explain OLAP “on the fingers”, using a specific example. Practice shows that such an explanation is necessary for some IT professionals and especially for end users.

So, OLAP *1 as a first approximation, “on the fingers”, can be defined as a special way of analyzing data and generating reports. Its essence is to provide the user with a multidimensional table that automatically summarizes data in various sections and allows you to interactively manage calculations and the form of the report. This article will talk about the technology and basic operations of OLAP using the example of analyzing invoices for an enterprise engaged in the wholesale trade in food products.

*one. OLAP - On-Line Analytical Processing, operational data analysis.

As a tool, an OLAP system of the simplest and most inexpensive class, an OLAP client *1, will be considered. For example, the simplest product from the number of OLAP clients is chosen - “Contour Standard” from Intersoft Lab. (For clarity, later in the article, generally accepted OLAP terms will be indicated in bold and accompanied by English counterparts.)

*one. For more information about the classification of OLAP systems, see the article "OLAP Made in Russia" in PC Week/RE, No. 3/2001.

So let's get started with the system. First you need to describe the data source (data source) - the path to the table and its fields. This is the task of the user who knows the physical implementation of the database. For end users, it translates the name of the table and its fields into domain terms. Behind the “data source” is a local table, table or SQL server view or stored procedure.

Most likely, in a particular database, invoices are stored not in one, but in several tables. In addition, some fields or records may not be used for analysis. Therefore, a Sample (result set or query) is then created, in which the following are configured: the algorithm for combining tables by key fields, filtering conditions and the set of returned fields. Let's call our selection “Invoices” and put all the fields of the data source “Invoices” into it. Thus, an IT specialist, by creating a semantic layer, hides the physical implementation of the database from the end user.

Then the OLAP report is configured. This can be done by a subject matter expert. First, the fields of a flat data sample are divided into two groups - facts (facts or measures) and measurements (dimensions). Facts are numbers, and measurements are "cuts" in which the facts will be summarized. In our example, the dimensions will be: “Region”, “City”, “Customer”, “Product”, “Date”, and the fact will be one - the “Amount” field of the invoice. For a fact, one or more aggregation algorithms must be selected. OLAP is able not only to sum up the results, but also to perform more complex calculations, up to statistical analysis. Selecting multiple aggregation algorithms will create virtual, calculated facts. In the example, one aggregation algorithm is selected - “Sum”.

A special property of OLAP systems is the generation of dimensions and data for older time periods from a date and the automatic calculation of totals for these periods. Let's select the periods "Year", "Quarter" and "Month", while there will be no data for each day in the report, but the generated dimensions "Year", "Quarter" and "Month" will appear. Let's name the report "Sales Analysis" and save it. The work on creating the interface of the analytical application is completed.

Now, when running this interface on a daily or monthly basis, the user will see a table and graph in which invoices are summarized by item, customer, and period.

In order to make manipulations with data intuitive, the tools for managing a dynamic table are the elements of the table itself - its columns and rows. The user can move, delete, filter, and perform other OLAP operations. The table automatically calculates new subtotals and final totals.

For example, by dragging (“move” operation) the “Product” column to the first place, we will get a comparison report - “Comparison of sales volumes of products for the year”. To aggregate data for a year, just drag the “Quarter” and “Month” columns to the top of the table - “inactive measurements area”. The “Quarter” and “Month” dimensions transferred to this area will be closed (“close dimension” operation), i.e. excluded from the report; the facts will be summed up for the year. Despite the fact that the dimensions are closed, you can set specific years, quarters and months for data filtering (“filter” operation).

For greater clarity, let's change the type of graph illustrating the OLAP table and its location on the screen.

Drilling down into the data (“drill down” operation) allows you to get more detailed information about the sales of the product of interest to us. By clicking on the “+” sign opposite the “Coffee” product, we will see its sales volumes by region. Having opened the Ural region, we will get sales volumes in the context of the cities of the Ural region, delving into the data on Yekaterinburg, we will be able to view data on wholesale buyers of this city.

You can also use open measurements to set filters. To compare the dynamics of candy sales in Moscow and Yekaterinburg, let's set filters for the "Product" and "City" dimensions.

Close unnecessary measurements and select the type of graph "Line". On the resulting graph, you can trace the dynamics of sales, evaluate seasonal fluctuations and the relationship between drops and growth in sales of goods in different cities.

Thus, we were convinced that OLAP technology allows the user to release dozens of types of various reports from one interface, managing a dynamic OLAP table with the mouse. The task of a programmer who owns such a tool is not routine coding of reporting forms, but setting up an OLAP client for databases. At the same time, the methods of managing the report are intuitive for the end user.

Indeed, OLAP is a natural extension and development of the idea of spreadsheets. In essence, the OLAP visual interface is also a spreadsheet, but equipped with a powerful calculation engine and a special standard for presenting and managing data. Moreover, some OLAP clients are implemented as add-ins to MS Excel. Therefore, the millionth army of “white collars”, who are confident in spreadsheets, are also quickly mastering OLAP tools. For them, this is a “velvet revolution” that provides new opportunities, but does not involve the need to retrain.

If the reader, after reading this article, has not lost interest in OLAP, he can refer to the materials mentioned at the beginning. Collections of such materials are posted on a number of sites on the Internet, including the site Intersoft lab - www.iso.ru. From it you can also download a demo version of the Kontur Standard system with the example described in the article.

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.

Launch the Microsoft Excel program. 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

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 easiest way to get 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()-S3)-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

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 quantity . 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 the many useful features, 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. This 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 means 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, having a predefined date location in this example- 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 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.