Practical work on olap technology in excel. Understanding real-time analytical processing (OLAP). Some information about OLAP cubes and Power Pivot data models

OLAP client tools are applications that calculate aggregate data (sums, averages, maximum or minimum values) and display them, while the aggregate data itself is contained in a cache within the address space of such an OLAP tool.

If the source data is contained in a desktop DBMS, the calculation of aggregate data is performed by the OLAP tool itself. If the source of the initial data is a server DBMS, many of the client OLAP tools send SQL queries containing the GROUP BY statement to the server, and as a result receive aggregate data calculated on the server.

As a rule, OLAP functionality is implemented in statistical data processing tools (from products of this class to Russian market products from StatSoft and SPSS are widely used) and in some spreadsheets. In particular, it has the means of multivariate analysis Microsoft Excel. With this product, you can create and save as a file a small local multidimensional OLAP cube and display two- or three-dimensional cross-sections of it.

Application package add-ons Microsoft Office data mining are a set of functions that provide access to data mining and processing capabilities from Microsoft Office applications, thereby enabling predictive analysis on local computer. Due to the fact that platform services built into Microsoft SQL Server data extraction and processing algorithms are available from within the Microsoft Office application environment, business users can easily extract valuable information from complex data sets with just a few clicks. Office data extraction and manipulation add-ins enable end users to perform analysis directly in Microsoft Excel and Microsoft Visio.

Microsoft Office 2007 includes three separate OLAP components:

Data Mining Client for Excel lets you create and manage SSAS-based data mining projects from within Excel 2007;
Table Analysis Tools for Excel let you use SSAS's built-in information extraction and processing capabilities to analyze data stored in Excel tables;
Visio Data Mining Templates let you visualize decision trees, regression trees, cluster diagrams, and dependency networks in Visio diagrams.

Table 1.1. Oracle Products for OLAP and Business Intelligence

Type of funds	Product

The first interface for pivot tables, also called pivot reports, was included in Excel back in 1993 (Excel version 5.0). Despite the many useful functionality, it is practically not used in work by 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, provides practical recommendations for their use, and also provides an example of accessing data based on multiple tables.

Multivariate data analysis terms

Most economists have heard the terms “multidimensional data”, “virtual cube”, “OLAP technologies”, etc. But a detailed conversation usually turns out that almost everyone has no idea what we are talking about. That is, people mean something complex and usually not related to their daily activities. Actually this is not true.

Multidimensional data, measurements

It is safe to say that economists almost constantly encounter multidimensional data, but they try to present it in a predefined form using spreadsheets. Multidimensionality here means the ability to enter, view or analyze the same information with changes appearance, using various groupings and sorting of data. For example, a sales plan can be analyzed using the following criteria:

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

Each of the above criteria is called a “dimension” in terms of multidimensional data analysis. We can say that a measurement characterizes information according to a specific set of values. A special type of measurement of multidimensional information is “data”. In our example, the sales plan data could be:

volume of sales;
Selling price;
individual discount
and so on.

In theory, data can also be a standard dimension of multidimensional information (for example, you could group data by sales price), but data is usually still 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 that have many characteristics) and reference books (characteristics or data measurements).

OLAP

The abbreviation OLAP (online analytical processing) literally means “real-time analytical processing”. The definition is not very specific; almost any report of any software product can be summed up under it. In its meaning, 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 special term proposed by some specialized vendors software. OLAP systems typically prepare and store data in their own structures, and special analysis interfaces (such as Excel summary reports) access the data in these virtual cubes. Moreover, 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 either through specialized software or through simple access to database tables or any other source, such as an Excel spreadsheet.

Pivot table

Pivot Table is a user interface for displaying multidimensional data. Using this interface, you can group, sort, filter, and rearrange data to obtain different analytical samples. The report is being updated by simple means user interface, data is automatically aggregated according to specified rules, without requiring additional or repeated entry of any information. Summary interface Excel tables 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.

Excel's PivotTable interface allows you to arrange dimensions of multidimensional data in a 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 areas to display information: filter, columns, rows, and data. Data dimensions are called 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 ability to edit the information. A closer meaning would be the widespread use of the term “pivot report”, and this is exactly what this interface was called until 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 ability to change source data when working with reports. However, a whole class has formed on the market software systems, implementing 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 problems. An example of editing implementation for Excel pivot tables based on worksheet data can be found on our website.

Preparing multidimensional data

Let's come to the practical use of pivot tables. Let's try to analyze sales data in various directions. File pivottableexample.xls consists of several sheets. Sheet Example contains basic information about sales for a certain period. To simplify the example, we will analyze a single numerical indicator – sales volume in kg. The key data dimensions are: product, buyer and carrier (shipping company). In addition, there are several additional data dimensions that are characteristics of the product: type, brand, category, supplier, and customer: type. This data is collected on the Directories sheet. In practice, there can be much more such measurements.

Sheet Example contains standard remedy data analysis – autofilter. Looking at the example of filling out the table, it is obvious that sales data by date (they are arranged in columns) lends itself to normal analysis. In addition, using an autofilter, you can try to summarize data based on combinations of one or more key criteria. There is absolutely no information about brands, categories and types. There is no way to group data with automatic summation 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, using automatic means.

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

Firstly, we need to get rid of this shortcoming - i.e. remove the predefined location of one of the source data dimensions. Example of a correct table - sheet Sales.

The table has the form of a log of information entry. Here, date is an equal dimension of data. It should also be noted that for subsequent analysis in pivot tables, the relative position of the rows relative to each other (in other words, sorting) is completely indifferent. Records in relational databases have these properties. The interface of pivot tables is primarily aimed at analyzing large volumes of databases. 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 prohibits the use of Excel interface tools in their work - pivot tables analyze only data, and formatting, filters, groupings and sorting of source cells can be arbitrary.

From autofilter to summary report

Theoretically, it is already possible to carry out analysis in three dimensions using the data from the Sales sheet: goods, customers and carriers. There is no data on the properties of products and customers 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 the source Excel data does not allow you to link data from multiple tables using certain fields. You can get around this limitation software– see the example supplement to this article on our website. In order not to resort to software methods for processing information (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, using AutoFilter, you can analyze the data in different dimensions. But the problem of groupings remains unresolved. For example, tracking the amount only by brand for certain dates is quite problematic. If you limit yourself Excel formulas, then you need to build additional samples using the SUMIF function.

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

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

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

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

Properties and Formatting

In addition to directly displaying data, there is a wide range of options for displaying the appearance of pivot tables. You can hide unnecessary data 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 advisable to set the display of other elements of the pivot table not through cell formatting, but through setting a field or element of the pivot table. To do this, you need to move the mouse pointer to the desired element, wait for a special cursor shape (in the form of an arrow) to appear, then select the selected element with a single click. Once selected, you can change the view via the ribbon, context menu or call the standard cell format dialog:

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

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

Access to external data

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

Excel supports many types of external data sources:

The greatest effect from using external sources of information can be achieved by using automation tools (VBA programs) both to obtain data and to pre-process it in pivot tables.

Amazing - close...

In the course of my work, I often needed to make complex reports, I was always trying to find something common in them in order to compile them more simply and universally, I even wrote and published an article on this subject, “Osipov’s Tree.” However, my article was criticized and they said that all the problems that I raised had long been solved in MOLAP.RU v.2.4 (www.molap.rgtu.ru) and they recommended looking at the pivot tables in EXCEL.
It turned out to be so simple that, having applied my ingenious little hands to it, I got a very simple circuit for downloading 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 for work.
2. The user can design reports himself 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. Sample processing is available for downloading other data.
5. You can design report forms in advance and then apply them to different data without re-designing them.
6. Quite 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 pretty quickly any report can be built on the basis of this cube. For example, data on product sales for a store for 3 months with an assortment of 6000 products is loaded into EXCEL in 8 minutes on Cel600-128M, the rating by product and group (OLAP report) is recalculated in 1 minute.
7. Data is downloaded from 1C7 in full for the specified period (all movements, across all warehouses, companies, 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)

Let's say you have a retail chain. Let the data on trading operations be uploaded to a text file or table like this:

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

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

Data for analysis is usually uploaded into an OLAP system for a certain period of time, from which, in principle, another period can be selected using loading filters.

This table is the source for OLAP analysis.

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

The user himself determines which of the table fields 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 it yourself :

Unpack the data from the distribution exactly into the c:\fixin directory (for trading system perhaps in c:\reports) . Read the readme.txt and follow all the instructions in it.

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

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

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

To analyze the data, the tables "Movement Analysis.xls" ("Accounting Analysis.xls") are used. When opening them, do not disable macros, otherwise you will not be able to update the reports (they are run by VBA macros). These files take their source data from the files C:\fixin\motions.txt (C:\fixin\buh.txt), otherwise they are the same. Therefore, you may have to copy your data to one of these files.
To load your data into EXCEL, select or write your filter and click the “Generate” button on the “Conditions” sheet.
Report sheets begin with the prefix "Report". Go to the report sheet, click "Refresh" and the report data will change according to the last loaded data.
If you are not satisfied with standard reports, there is a ReportTemplate sheet. Copy it to new leaf and customize the report type by working with a pivot table on this sheet (about working with pivot tables - in any book on EXCEL 2000). I recommend setting up reports on a small set of data, and then running them on a large array, because... There is no way to disable redrawing of tables 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 there will be multiple files being uploaded (leftovers and movements) in the near future. Then, by clicking the “Submit” button in Explorer --> “To OLAP analysis in EXCEL 2000,” 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 by naming the files motions.txt or buh.txt.

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

Microsoft Query is used to import text files into Excel ( component EXCEL), for it to work, you must 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 are 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 themselves determine 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, numbers are uploaded with a comma delimiter, and dates are in the format "DD.MM.YYYY".

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

I understand that fans of MS SQL Server and powerful databases will begin to grumble that everything is too simplified, that my processing will be exhausted by a year-long sample, but first of all I want to give the benefits of OLAP analysis to medium-sized organizations. I would position this product as an annual analysis tool for wholesale companies, quarterly analysis for retailers, and operational analysis for any organization.

I had to tinker with VBA so that the data could be taken from a file with any list of fields and I could 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 downloaded data from 1C and select the “Send” command, then “To OLAP analysis in EXCEL 2000”.
2. Open the file "Motion Analysis.xls"
3. Select 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 Report.
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 example:

Processing motionsbuh2011.ert - the latest version of uploading transactions from Accounting 7.7 for analysis in Excel. It has a checkbox “Attach to file”, which allows you to upload data in parts by period, appending it to the same file, rather than uploading it to the same file again:

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

Examples of reports:

Wiring chess:

Operator workload by types of invoices:

P.S. :

It is clear that a similar scheme can be used to organize the downloading of data from 1C8.
In 2011, a user contacted me who needed to improve this processing in 1C7 so that it would upload large amounts of data, I found an outsourcer and did the work. So the development is quite relevant.

The processing of motionsbuh2011.ert has been improved to cope with unloading large amounts of data.

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

"Purpose and interface of MS Excel"

After completing the tasks in this topic, you:

1. Learn to run spreadsheets;

2. Reinforce 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.

Exercise: Get acquainted with the basic elements of the MS Excel window.

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

Documents that are created usingEXCEL , are calledworkbooks and have an extension. XLS. The new workbook has three worksheets called SHEET1, SHEET2 and SHEET3. These names are located on the sheet labels at the bottom of the screen. To move to another sheet, click on the name of that sheet.

Actions with worksheets:

Rename a worksheet. Place the mouse pointer on the spine of the worksheet and double-click the left key or call the context menu and select the Rename command.Set the name of the sheet to "TRAINING"

Inserting 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 give the name "Probe" .

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

Cells and cell ranges.

The work field consists of rows and columns. Rows are numbered from 1 to 65536. Columns are designated with Latin letters: A, B, C, ..., AA, AB, ..., IV, total - 256. At the intersection of the row and column there is a cell. Each cell has its own address: the name of the column and the row number at the intersection of which it is located. For example, A1, SV234, 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 lines 8 to 20.

A:A – all cells in column A.

H:P – all cells in columns H to R.

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

2. Selecting cells in Excel

What do we highlight?

Actions

One cell

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

String

Click on the line number.

Column

Click on the 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.

Entire table

Click the Select All button (the empty button to the left of the column names)

You can change the width of columns and height of rows by dragging the borders between them.

Use the scroll bars to determine how many rows the table has and what the last column name is.
Attention!!! To quickly reach the end of the table horizontally or vertically, you need to 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 there in the table? Enter the address of the last row in cell B3.

3. The following types of data can be entered into EXCEL:

Numbers.

Text (for example, headings and explanatory material).

Functions (eg sum, sine, root).

Formulas.

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

Just click in the cell and type the required data.

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 make it bold.
Enter the current year in cell C5 using the formula bar.

4. Change of data.

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

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

To change formulas, you can only use 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, according to which calculations are made in the table. Formulas consist of cell references, operation symbols, and functions. Ms EXCEL has a very large set of built-in functions. With their help, you can calculate the sum or arithmetic average of values from a certain range of cells, calculate interest on deposits, etc.

Entering formulas always begins with an equal sign. After entering a formula, the calculation result appears in the corresponding cell, 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 operations.

Autocomplete.

A very convenient tool, which is used only in MS EXCEL, is autofill 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 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 there is a small square - a fill marker.

Move the mouse pointer to the fill marker (it will look like 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 out some number series, then you need to enter the first two numbers into the adjacent two cells (for example, enter 1 in A4, and 2 in B4), select these two cells and drag the selection area using the marker to the desired size.

Document selected for viewing Excel pr.r. 2.docx

Library
materials

Practical work 2

“Entering data and formulas into MS Excel spreadsheet cells”

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

Exercise: Enter the necessary data and simple calculations in the table.

Task execution technology:

1. Run the programMicrosoft Excel.

2. To cellA1 Sheet 2 enter the text: "Year of school foundation." Record the data in the cell using any method known to you.

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

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

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

5. Select a cellD1 , enter the formula from the keyboard to calculate the school age:= C1- B1

Attention! Entering formulas always begins 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.

6. Delete the contents of a cellD1 and repeat entering the formula using the mouse. In a cellD1 set a sign«=» , then click on the cellC1, Please note the address of this cell appeared inD1, put up a sign«–» and click on the cellB1 , press(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 to calculate your age in the current year(=C2-B2).

11. Select a cellC2. Enter next year's number. Please note, recalculation in the cellD2 happened automatically.

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

Independent work

Exercise: Calculate, using ET, is 130 rubles enough for you to buy all the products that your mother ordered for you, and is it 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 language!
o In formulas, cell names are written instead of variables.
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 your teacher!!!

Document selected for viewing Excel pr.r. 3.docx

Library
materials

Practical work 3

"MS Excel. Creating and editing a spreadsheet document"

By completing the tasks in this topic, you will learn:

 Create and fill a table with data;

 Format and edit data in a cell;

 Use simple formulas in the table;

 Copy formulas.

Exercise:

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 , replace the word "Golden" with "Great" and press the keyEnter .

3. Select cellA6 , left-click on it twice and replace “Ugryumovo” with “Veselkovo”

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

5. Complete the “Schedule” table with calculations of train stop times in each locality. (insert columns) Calculate the total stop time, the total travel time, the time spent by the train moving from one settlement 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 teamCut .
Place the cursor in cell D1;
Run the commandInsert ;
Align 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 the parking time in a populated area.

4. You need to copy the formula into 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, grab it and extend the formula down to cell C7.

5. Enter the text “Traveling Time” in cell E1. 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 town 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 the keyOK .

8. Calculate the total parking time.
Select cell C9;
Click the buttonAutosum on the toolbar;
Confirm the selection of the 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!!!

Run 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. Decorate the table with color and highlight the borders of the table.

Independent work

Calculate using a spreadsheetExcelexpenses of schoolchildren planning to go on an excursion to another city.

Document selected for viewing Excel pr.r. 4.docx

Library
materials

Practical work 4

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

By completing the tasks in this topic, you will learn:

Perform copy, move, and autofill operations on individual cells and ranges.

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

Use Excel's built-in mathematical and statistical functions in calculations.

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

Table. Built-in Excel functions

* Written without arguments.

Table . Types of links

Exercise.

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

Working technology:

1. Align text in cells. Select cells A3:E3. Home - Format - Cell Format - Alignment: horizontally - in the center, vertically - in the center, display - move by words.

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

5. Fill in cells B4:C10 as shown.

6. In cell D4, enter the formula to find the electricity consumption. And fill out the lines below using the autocomplete marker.

7. In cell E4, enter the formula to find the cost of electricity=D4*$B$1. And fill out the lines below using the autocomplete marker.

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

8. In cell A11, enter the text “Statistics,” 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 image.

10. Click cell B12 and enter the math functionSUM , to do this you need to click in the formula barby signfx and select the function, as well as confirm the cell range.

11. Functions are set similarly in cells B13:B15.

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

Independent work

Exercise 1:

Calculate your age from this year to 2030 using the autocomplete marker. The year you were born 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 there should be formulas: AVERAGE, COUNTIF, MAX, MIN. CellsB3: H12 are filled in with information by you.

Document selected for viewing Excel pr.r. 5.docx

Library
materials

Practical work 5

By completing the tasks in this topic, you will learn:

 Technologies for creating a spreadsheet document;

 Assign a type to the data used;

 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.

Working technology:

1. Launch the Excel application.

2. In cell A1, enter your date of birth (day, month, year – 12/20/97). Record your 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 presented as a date, in which case it should be converted to a numeric type.

Task 2. Age of students. By given list students and their dates of birth. Determine who was born earlier (later), determine who is the oldest (youngest).

Working technology:

1. Get the Age file. By local network: Open the Network Neighborhood folder -Boss–General documents– 9th grade, find the file Age. Copy it in any way you know or download from this page at the bottom of the application.

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

3. Let's determine the earliest birthday. Write the formula in cell C22=MIN(C3:C21) ;

4. Let's determine the youngest student. Write the formula in cell D22=MIN(D3:D21) ;

5. Let's determine the latest birthday. Write the formula in cell C23=MAX(C3:C21) ;

6. Let's determine the oldest student. Write the formula in cell D23=MAX(D3:D21) .

Independent work:
Task. Produce necessary calculations student growth in different units measurements.

Document selected for viewing Excel pr.r. 6.docx

Library
materials

Practical work 6

"MS Excel. Statistical functions" Part II.

Task 3. Using a spreadsheet, process data using statistical functions. Information about students in 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:
Let's fill the table with the initial data and carry out the necessary calculations.Pay attention to the format of the values in the "GPA" (numeric) and "Date of Birth" (date) cells.

The table uses additional columns that are necessary to answer the questions posed in the problem -student age and is the studentan excellent student and a girl simultaneously.
To calculate age, the following formula was used (using cell G4 as an example):

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

Let's comment on it. The student's date of birth is subtracted from today's date. Thus, we obtain the total number of days that have passed since the birth of the student. Dividing this number by 365.25 (the real number of days in a year, 0.25 days for a normal year is compensated 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 cell H4 as an example):

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

Let's proceed to the basic calculations.
First of all, you need to determine the girls' average score. 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 table 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 a given criterion (in our case, the child is a boy). The COUNTIF function counts the number of values that meet a specified criterion. Thus we get what we need.
To calculate the share of excellent students among all girls, we will take the number of excellent girls to the total number of girls (here we will use a set of values from one of the auxiliary columns):

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

Finally, we will 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 solution results for a given data set.

Document selected for viewing Excel pr.r. 7.docx

Library
materials

Practical work 7

“Creating charts using MS Excel”

By completing the tasks in this topic, you will learn:

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

 Edit chart data, its type and design.

What is a diagram? A chart is designed to represent data graphically. Lines, bars, columns, sectors, and other visual elements are used to display numeric data entered into table cells. The appearance of the diagram depends on its type. All charts, with the exception of the pie chart, have two axes: a horizontal one – the category axis and a vertical one – the value axis. When creating 3-D charts, a third axis is added – the series axis. Often a chart will contain elements such as a grid, titles, and a legend. Gridlines are an extension 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, and the legend helps to identify the data series presented in the chart. There are two ways to add charts: embed them in the current worksheet or add a separate chart sheet. If the diagram itself is of interest, it is placed on a separate sheet. If you need to simultaneously view the diagram and the data on which it was built, then an embedded diagram is created.

The diagram is saved and printed along with the workbook.

Once the diagram is generated, changes can be made to it. Before performing any actions on the diagram elements, select them by left-clicking on them. After this, call the context menu using right button mouse or use the appropriate buttonsChart toolbar .

Task: Use a spreadsheet to graph the function Y=3.5x–5. Where X takes values from –6 to 6 in increments of 1.

Working technology:

1. Launch Excel spreadsheet processor.

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

3. Select the range of cells A1:B1 and center the text in the cells.

4. In cell A2, enter the number -6, and in cell A3, enter -5. Use the AutoFill marker to fill in the cells below up to option 6.

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

6. Select the entire table you created and give it external and internal borders.

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

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

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

10. Move the chart below the table.

Independent work:

Graph 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 a call without a discount (column D) and the cost of a call taking into account the discount (column F).

For a clearer representation, construct two pie charts. (1-diagram of the cost of a call without a discount; 2-diagram of the cost of a call with a discount).

Document selected for viewing Excel pr.r. 8.docx

Library
materials

Practical work 8

CONSTRUCTION OF GRAPHICS AND DRAWINGS BY MEANS MS EXCEL

1. Construction of the drawing"UMBRELLA"

The functions whose graphs are included in this image are given:

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, xО

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

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

- Launch MS EXCEL

· - In the cellA1 enter variable designationX

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

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

Procedure:

Place the cursor in a cellIN 1 and entery1

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

Click Enter on keyboard

The function value is calculated automatically.

Stretch the formula to cell A26

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

The result should be the following ET

After all function values have been 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 the desired view → Click Ok .

The result should be the following figure:

Assignment for individual work:

Construct graphs of functions in one coordinate system.x from -9 to 9 in increments of 1 . Get the drawing.

1. "Glasses"

2. "Cat" Filtering (sampling) of data in a table allows you to display only those rows whose cell contents meet a specified condition or several conditions. Unlike sorting, filtering does not reorder data, but only hides those records that do not meet the specified selection criteria.

Data filtering can be done in two ways:using AutoFilter or Advanced Filter.

To use the autofilter you need:

o place the cursor inside the table;

o select 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 criterion in the dialog boxCustom auto filter.

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

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

The advanced filter allows you to create multiple selection criteria and perform more complex filtering of spreadsheet data by specifying a set of selection conditions across several columns. Filtering records using an advanced filter is done using the menu commandData - Filter - Advanced filter.

Exercise.

Create a table in accordance with the example shown in the figure. Save it as Sort.xls.

Task execution technology:

1. Open the Sort.xls document

3. Execute menu commandData - Sorting.

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

Let us remember that every day we need to print a list of goods remaining in the store (having a non-zero balance), but for this we first need to obtain such a list, i.e. filter the data.

5. Place the frame cursor inside the data table.

6. Execute menu commandData - Filter

7. Deselect tables.

8. Each table header cell now has a "Down Arrow" button; it is not printed; it allows you to set filter criteria. We want to leave all records with a non-zero remainder.

9. Click the arrow button that appears in the columnRemaining quantity . A list will open from which the selection will be made. Select lineCondition. Set the condition: > 0. ClickOK . The data in the table will be filtered.

10. Instead of full list products, we will receive a list of products sold to date.

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

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

13. To avoid confusion in your reports, insert a date that will automatically change according to your 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 data using statistical functions.
1. Information is given about the students of the class (10 people), 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 ship. Nikolai sailed 150 km by boat, traveled 140 km by train and flew 1100 km by plane. Vasily sailed 200 km by boat, traveled 220 km by train and flew 1160 km by plane. Anatoly flew 1200 km by plane, traveled 110 km by train and sailed 125 km by boat. 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 that each of the guys traveled.

Calculate the total number of kilometers that the children traveled by train, flew by plane and sailed by boat (on each type of transport separately).

Calculate the total number of kilometers of all friends.

Determine the maximum and minimum number of kilometers traveled by friends using all types of transport.

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

2.2 task (2 points).

Create a table “Lakes of Europe” using the following data on area (sq. km) and greatest depth (m): Ladoga 17,700 and 225; Onega 9510 and 110; Caspian Sea 371,000 and 995; Wenern 5550 and 100; Chudskoye with Pskovsky 3560 and 14; Balaton 591 and 11; Geneva 581 and 310; Wettern 1900 and 119; Constance 538 and 252; Mälaren 1140 and 64. Determine the largest and smallest lake in 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): Volga 3688 and 1350; Danube 2850 and 817; Rhine 1330 and 224; Elbe 1150 and 148; Vistula 1090 and 198; Loire 1020 and 120; Ural 2530 and 220; Don 1870 and 422; Sena 780 and 79; Thames 340 and 15. Determine the longest and shortest river, calculate the total area of river basins, the average length of rivers in the European part of Russia.

Task 3 (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 established for debtors: if the company has repaid the loan by more than 70 percent, the fine will be 10 percent of the debt amount, 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,

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

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

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

The OLAP system of the simplest and most inexpensive class will be considered as a tool - OLAP client *1. For example, we selected the simplest product among OLAP clients - “Contour Standard” from Intersoft Lab. (For clarity, later in the article, generally accepted OLAP terms will be indicated in bold and accompanied by their English equivalents.)

*1. More details about the classification of OLAP systems are described in 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 - 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, SQL server table or 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 Selection (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 place all the fields of the “Invoices” data source into it. Thus, the 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 dimensions (dimensions). Facts are numbers, and measurements are “sections” in which the facts will be summarized. In our example, the dimensions will be: “Region”, “City”, “Customer”, “Product”, “Date”, and there will be one fact - the “Amount” field of the invoice. For a fact, you need to select one or more aggregation algorithms. OLAP is capable of not only summarizing results, but also performing more complex calculations, including 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 measurements 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 data for each day will not be 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 for the analytical application is completed.

Now, when running this interface daily or monthly, the user will see a table and graph that summarizes invoices by item, customer, and period.

In order for data manipulation to be intuitive, the tools for managing a dynamic table are the elements of the table itself - its columns and rows. The user can move them, delete them, filter them, and perform other OLAP operations. In this case, the table automatically calculates new intermediate and final totals.

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

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

Digging deeper into the data (“drill down” operation) allows us to obtain more detailed information about the sales of the product we are interested in. By clicking on the “+” sign next to the “Coffee” product, we will see its sales volumes by region. Having expanded the “Ural” region, we will get sales volumes by cities of the Ural region, delving into the data for “Ekaterinburg”, we will be able to view data on wholesale buyers of this city.

You can also use open dimensions to set filters. To compare the dynamics of candy sales in Moscow and Yekaterinburg, we will install filters on the “Product” and “City” dimensions.

Let’s close unnecessary measurements and select the “Line” graph type. Using the resulting graph, you can track sales dynamics, evaluate seasonal fluctuations and the relationship between drops and increases in product sales in different cities.

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

Indeed, OLAP is a natural continuation 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 an add-in to MS Excel. Therefore, a million-strong army of “white collar” workers, confidently owning spreadsheets, very quickly masters OLAP tools. For them, this is a “velvet revolution” that provides new opportunities, but does not involve the need to relearn.

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 Internet sites, including the Intersoft lab site - www.iso.ru. From it you can also download a demo version of the “Contour Standard” system with the example described in the article.