This is not the first time Renat has been a guest author on Lifehacker. We have previously published excellent material from him on how to make a training plan: and online resources, as well as creating a training plan.

This article contains simple tricks that allow. They will be especially useful for those who are engaged in management reporting, prepare various analytical reports based on downloads from 1C and other reports, and form presentations and diagrams for management from them. I do not pretend to be an absolute novelty - in one form or another, these techniques were probably discussed on the forums or.

Simple alternatives to VLOOKUP and GLOOKUP if the values ​​you are looking for are not in the first column of the table: LOOKUP, INDEX+MATCH

Result:

AT this case, in addition to the CHAR function (to display quotes), the IF (IF) function is used, which allows you to change the text depending on whether there is a positive sales trend, and the TEXT function, which allows you to display a number in any format. Its syntax is described below:

TEXT ( meaning; format)

The format is specified in quotation marks in the same way as if you entered a custom format in the Format Cells window.

More complex texts can also be automated. In my practice, there was an automation of long, but routine comments on management reporting in the format “INDICATOR fell / increased by XX relative to the plan, mainly due to the increase / decrease in FACTOR1 by XX, the increase / decrease in FACTOR2 by YY ..." with a changing list of factors. If you write such comments often and the process of writing them can be algorithmized, it is worth once to be puzzled by creating a formula or a macro that will save you at least part of the work.

How to keep data in each cell after merging

When merging cells, only one value is retained. Excel warns about this when trying to merge cells:

Accordingly, if you had a formula that depends on each cell, it will stop working after they are combined (#N/A error in lines 3-4 of the example):

To merge cells and still keep the data in each of them (maybe you have a formula, like in this abstract example; maybe you want to merge cells but keep all the data for the future or hide it intentionally), merge any cells on the worksheet , select them, and then using the "Format Painter" command, transfer the formatting to the cells that you need to merge:

How to build a summary from multiple data sources

If you need to build a summary from several data sources at once, you will have to add to the ribbon or panel quick access PivotTable and PivotChart Wizard, which has this option.

You can do this as follows: "File" → "Options" → "Quick Access Toolbar" → "All Commands" → "PivotTable and PivotChart Wizard" → "Add":

After that, the corresponding icon will appear on the ribbon, clicking on which calls the same wizard:

When you click on it, a dialog box appears:

In it, you need to select the item “In several consolidation ranges” and click “Next”. In the next paragraph, you can select "Create one page field" or "Create page fields". If you want to come up with a name for each of the data sources yourself, select the second item:

In the next window, add all the ranges on the basis of which the summary will be built, and give them names:

After that, in the last dialog box, specify where the pivot table report will be placed - on an existing or new sheet:

The PivotTable report is ready. In the "Page 1" filter, you can select only one of the data sources, if necessary:

How to calculate the number of occurrences of text A in text B ("MTS tariff SuperMTS" - two occurrences of the abbreviation MTS)

In this example, there are several text lines in column A, and our task is to find out how many times each of them contains the desired text located in cell E1:

To solve this problem, you can use a complex formula consisting of the following functions:

1. DLSTR (LEN) - calculates the length of the text, the only argument is the text. Example: DLSTR (“machine”) = 6.
2. SUBSTITUTE - replaces certain text in a text string with another. Syntax: SUBSTITUTE ( text; old_text; new_text). Example: SUBSTITUTE (“car”;“car”;“”)= “mobile”.
3. UPPER - Changes all characters in a string to uppercase. The only argument is text. Example: UPPER("machine") = "MACHINE". We need this function to do a case-insensitive search. After all, UPPER("machine") = UPPER("Machine")

To find the occurrence of a certain text string in another, you need to remove all its occurrences in the original one and compare the length of the resulting string with the original one:

DLSTR(“MTS Tariff Super MTS”) – DLSTR(“Super Tariff”) = 6

And then divide this difference by the length of the string we were looking for:

6 / DLSTR (“MTS”) = 2

It is two times that the string "MTS" is included in the original.

It remains to write this algorithm in the language of formulas (let us denote by “text” the text in which we are looking for occurrences, and by “desired” - the one whose number of occurrences we are interested in):

=(DLSTR( text)-DLSTR(SUBSTITUTE(UPPER( text);UPPER( desired);“”)))/DLSTR( desired)

In our example, the formula looks like this:

=(LEN(A2)-LONG(SUBSTITUTE(UPPER(A2),UPPER($E$1),“”)))/LEN($E$1)

Pivot tables in Excel are a powerful reporting tool. It is especially useful in cases where the user does not work well with formulas and it is difficult for him to do data analysis on his own. In this article, we will look at how to create such tables correctly and what opportunities exist for this in the Excel editor. You don't need to download any files for this. Training is available online.

The first step is to create a table. It is desirable that there are several columns. In this case, some information must be repeated, since only in this case it will be possible to make some analysis of the entered information.

For example, consider the same financial expenses in different months.

Creating pivot tables

In order to build such a table, you need to do the following steps.

1. To begin with, it must be completely selected.

1. Then go to the "Insert" tab. Click on the "Table" icon. Select "Pivot Table" from the menu that appears.

1. As a result of this, a window will appear in which you need to specify several basic parameters for building a pivot table. The first step is to select the data area on the basis of which the analysis will be carried out. If you have previously selected a table, then a link to it will be inserted automatically. Otherwise, it will need to be selected.

1. You will then be asked to specify exactly where the build will take place. It is better to select the item “To an existing sheet”, since it will be inconvenient to analyze information when everything is scattered over several sheets. Then you need to specify the range. To do this, click on the icon next to the input field.

1. Immediately after that, the PivotTable Wizard will collapse to a small size. In addition, it will also change appearance cursor. You will need to make a left mouse click in any place convenient for you.

1. As a result, the reference to the specified cell will be substituted automatically. Then you need to click on the icon on the right side of the window to restore it to its original size.

1. To complete the settings, click on the "OK" button.

1. As a result of this, you will see an empty template for working with pivot tables.

1. At this stage, you need to specify which field will be:
   1. column;
   2. string;
   3. value for analysis.

You can choose anything. It all depends on what kind of information you want to receive.

1. In order to add any field, you need to left-click on it and, without releasing your finger, drag it to the desired area. The cursor will change its appearance.

1. You can release your finger only when the crossed out circle disappears. Similarly, you need to drag all the fields that are in your table.

1. To see the full result, you can close sidebar settings. To do this, just click on the cross.

1. As a result of this, you will see the following. With this tool, you will be able to summarize the amount of expenses in each month for each item. In addition, information about the total result is available.

1. If you don't like the table, you can try building it a little differently. To do this, you need to change the fields in the construction areas.

1. Close the build assistant again.

1. This time, we can see that the PivotTable has become much larger, since now the columns are not months, but categories of expenses.

If you are unable to build a table yourself, you can always count on the help of an editor. In Excel, it is possible to create similar objects in automatic mode.

To do this, you must do the following, but first select all the information in its entirety.

1. Go to the "Insert" tab. Then click on the "Table" icon. In the menu that appears, select the second item.

1. Immediately after that, a window will appear in which there will be various examples for building. Similar options are suggested based on multiple columns. The number of templates directly depends on their number.

1. When hovering over each item, a preview of the result will be available. This makes it much more convenient to work.

1. You can choose what you like the most.

1. To insert the selected option, just click on the "OK" button.

1. As a result, you will get the following result.

Note that the table has been created on a new sheet. This will happen every time the constructor is used.

Once you add (it doesn't matter how) pivot table, you will see on the toolbar new tab"Analysis". It contains a huge number of different tools and functions.

Let's consider each of them in more detail.

By clicking on the button marked in the screenshot, you can do the following:

• change name;

• bring up the settings window.

In the options window you will see a lot of interesting things.

active field

With this tool, you can do the following:

1. First you need to select a cell. Then click on the "Active field" button. In the menu that appears, click on the "Field Options" item.

1. Right after that, you will see the following window. Here you can specify the type of operation that should be used to summarize the data in the selected field.

1. In addition, you can customize the number format. To do this, click on the appropriate button.

1. This will bring up the Format Cells window.

Here you can specify in what form you want to display the result of the analysis of information.

Thanks to this tool, you can customize grouping by selected values.

Paste Slice

Editor Microsoft Excel allows you to create interactive pivot tables. In this case, you do not need to do anything complicated.

1. Select a column. Then click on the "Insert Slice" button.
2. In the window that appears, as an example, select one of the proposed fields (in the future, you can select them in an unlimited number). After something is selected, the "OK" button is immediately activated. Click on it.

1. As a result, a small window will appear that can be moved anywhere. It will offer all possible unique values that are in this field. Thanks to this tool, you will be able to withdraw the amount only for certain months (in this case). By default, information is displayed for all time.

1. You can click on any of the items. Immediately after that, all values ​​in the sum field will change.

1. Thus, it will be possible to choose any period of time.

1. At any time, everything can be returned to its original form. To do this, click on the icon on the right upper corner this window.

In this case, we were able to sort the report by month because we had the corresponding field. But there is a more powerful tool for working with dates.

If you click on the corresponding button on the toolbar, you will most likely see this error. The fact is that in our table there are no cells that will have the "Date" data format explicitly.

As an example, let's create a small table with various dates.

Then you will need to build a pivot table.

Go back to the "Insert" tab. Click on the "Table" icon. In the submenu that appears, select the option we need.

1. We are then asked to select a range of values.

1. To do this, it is enough to select the entire table.

1. Immediately after that, the address will be substituted automatically. Everything is very simple here, because it is designed for dummies. Click the OK button to complete the build.

1. The Excel editor will offer us only one option, since the table is very simple (no more is needed for the example).

1. Try clicking the "Insert Timeline" icon again (located on the "Analysis" tab).

1. This time there will be no errors. You will be prompted to select a field for sorting. Check the box and click the OK button.

1. Thanks to this, a window will appear in which you can select the desired date using the slider.

1. We choose another month and there is no data, since all expenses in the table are indicated only for March.

If you have made any changes to the original data and for some reason this is not displayed in the pivot table, you can always update it manually. To do this, just click on the corresponding button on the toolbar.

If you decide to change the fields on which the building should be based, then it is much easier to do this in the settings, rather than deleting the table and creating it again taking into account new preferences.

To do this, click on the "Data Source" icon. Then select the menu item of the same name.

As a result of this, a window will appear in which you can re-select the required amount of information.

Actions

With this tool you will be able to:

• clear the table;
• highlight;
• move it.

Computing

If the calculations in the table are not enough or they do not meet your needs, you can always make your own changes. By clicking on the icon of this tool, you will see the following options.

These include:

• calculated field;

• computed object;

• calculation order (added formulas are displayed in the list);

• display formulas (no information, as there are no added formulas).

Here you can create a PivotChart or change the recommended table type.

With this tool, you can customize the appearance of the editor workspace.

Thanks to this, you will be able to:

• customize the display of the sidebar with a list of fields;

• enable or disable the plus/minus buttons;

• customize the display of field headers.

When working with pivot tables, in addition to the "Analysis" tab, another one will also appear - "Designer". Here you can change the appearance of your object until it is unrecognizable compared to the default.

Can be customized:

• subtotals:
  • do not show;
  • show all totals at the bottom;
  • show all totals in header.

• overall results:
  • disable for rows and columns;
  • enable for rows and columns;
  • enable for rows only;
  • enable for columns only.

• report layout:
  • show in a condensed form;
  • show in structure form;
  • show in tabular form;
  • repeat all element labels;
  • do not repeat element labels.

• empty lines:
  • insert a blank line after each element;
  • remove empty line after each element.

• PivotTable style options (here you can enable/disable each item):
  • line headers;
  • column headings;
  • alternating lines;
  • alternating columns.

• customize the style of the elements.

In order to see more different options, you need to click on the triangle in the lower right corner of this tool.

Immediately after that, a huge list will appear. You can choose anything. When you hover over each of the templates, your table will change (this is done for preview). Changes will not take effect until you click on any of the options.

In addition, if you wish, you can create your own design style.

You can also change the order in which rows are displayed here. Sometimes this is necessary for the convenience of cost analysis. Especially if the list is very large, since the required position is easier to find alphabetically than scrolling through the list several times.

To do this, do the following.

1. Click on the triangle next to the required field.
2. As a result, you will see the following menu. Here you can select the desired sorting option ("A to Z" or "Z to A").

If the standard option is not enough, you can click on the item " Extra options sorting".

As a result, you will see the following window. For more detailed settings, click on the "Advanced" button.

Everything here is set to automatic. If you clear this checkbox, you can specify the key you need.

Pivot Tables in Excel 2003

The steps above are suitable for modern editors (2007, 2010, 2013 and 2016). AT old version everything looks different. Opportunities, of course, there are much less.

In order to create a pivot table in Excel 2003, you need to do the following.

1. Go to the "Data" menu section and select the appropriate item.

1. As a result, a wizard for creating such objects will appear.

1. After clicking on the "Next" button, a window will open in which you need to specify a range of cells. Then click on "Next" again.

1. To complete the settings, click on "Finish".

1. As a result of this, you will see the following. Here you need to drag the fields to the appropriate areas.

1. For example, you might get the following result.

It becomes obvious that it is much better to create such reports in modern editors.

Conclusion

This article covered all the subtleties of working with pivot tables in the Excel editor. If something doesn't work out for you, you may select the wrong fields or there are very few of them - to create such an object, you need several columns with duplicate values.

If this tutorial is not enough for you, Additional information can be found in Microsoft's online help.

Video instruction

For those who still have unanswered questions, a video with comments on the instructions described above is attached below.

Watch the video for the article:

Standard reports included in our business intelligence (BI) turnkey modules allow you to answer 99% of the most popular user queries.

But if desired, analysts or any other users can make their reports in Excel on OLAP cubes.

Thanks to at the heart of each of our Business Intelligence (BI) modules is an OLAP cube, connecting to which users can easily build any report in Excel using Pivot Tables.

Also, reports built in Excel on OLAP cubes can be saved, and when re-opened, they will be automatically updated with the latest data from corporate business intelligence (BI) system .

Examples of reports built on OLAP cubes:

Description of reports:

(See the video for this article for the process of building reports)

1. Sales report in rubles and units:

• period: for the current year
• indicators: sales fact
• detailing: columns/quarter, month
• detailing: sales lines/channels, customers, brands, products

2. Report on the structure of sales in rubles and units (filter):

• period: for the current year
• indicators: actual sales, actual sales as a percentage
• detailing: columns/quarter, month
• detailing: sales lines/channels, brands

3. Report on the implementation of the sales plan in rubles and units (filter):

• period: for the current year
• indicators: sales plan, sales fact, plan execution
• detailing: columns/quarter, month
• detailing: sales lines/channels, customers, categories, products

4. Sales growth report in rubles and units (filter):

• period
• indicators
• detailing: lines/federal districts, regions, categories, products

5. Report on sales growth by time in rubles and units (filter):

• period: for the current year compared to the previous one
• indicators: sales of the previous year, sales plan, actual sales, implementation of the plan, sales growth in absolute and relative terms compared to the previous year
• detailing: rows/quarter, month, categories, products

6. Accounts receivable report:

• period: for the current date
• indicators: document amount, total receivables, current receivables, overdue receivables by periods - up to 15 days, up to 30 days, up to 60 days, up to 90 days, up to 180 days, up to 365 days or more, receivables structure
• detailing: sales lines/channels, counterparty, document payment date, document date, document

7. Report on the dynamics of receivables:

• period: for the current year
• indicators: total receivables, current receivables, overdue receivables, receivables structure
• detailing: rows/year, week, account

8. Payment receipt report by document date:

• period: for the current year
• indicators: document amount, document payment amount, payment deviations in absolute and relative terms
• detailing: lines/year, month, counterparty, document type, document date, document number

9. Payment receipt report by payment date:

• period: for the current year
• indicators: payment amount, payment distribution amount, distribution deviations in absolute and relative terms
• detailing: lines/year, month, counterparty, payment type, payment date, payment number

10. Inventory report:

• period: for the current date
• indicators: product balance, remaining shelf life in percent (according to DSO), product balance by expiration date - Term 0%, Term 10-30%, Term 40-60%, Term 70-90%, Term 99%
• detailing: lines/category, product, status, warehouse, receipt document date

Hi all! Today's material is for those who continue to master working with application programs and do not know how to make a pivot table in excel.

Having created a common table, in any of text documents, you can analyze it by making pivot tables in Excel.

Creating a pivot Excel table requires certain conditions to be met:

1. The data fits into the table, where there are columns and lists with names.
2. No blank forms.
3. No hidden objects.

How to make a pivot table in excel: step by step instructions

To create a pivot table you need:

An empty sheet has been created, where you can see the lists of areas and fields. The headings have become fields in our new table. The pivot table will be formed by dragging the fields.

They will be marked with a checkmark, and for the convenience of analysis, you will swap them in the table areas.


I decided that I would do data analysis through a filter by sellers, so that it would be clear by whom and for what amount every month it was sold, and which product.

We select a specific seller. We hold down the mouse and move the field "Seller" to the "Report filter". The new field is marked with a checkmark and the table view changes slightly.


We will put the category "Products" in the form of strings. In "Line Names" we transfer the field we need.


To display a drop-down list, it matters in what order we specify the name. If initially in the lines we make a choice in favor of the product, and then we indicate the price, then the products will just be drop-down lists, and vice versa.

The "Units" column, being in the main table, displayed the quantity of goods sold by a certain seller at a specific price.


To display sales, for example, for each month, you need to put the "Date" field in place of "Column Names". Select the "Group" command by clicking on the date.


Specify date periods and step. We confirm the choice.

We see such a table.


Let's move the "Amount" field to the "Values" area.


The display of numbers became visible, and we need exactly the number format


To fix, select the cells by calling the window with the mouse, select "Number Format".

We select the numeric format for the next window and check the "Digit separator". Confirm with the "OK" button.

Pivot Table Design

If we check the box that confirms the selection of several objects at once, we will be able to process data for several sellers at once.


Applying a filter is possible for columns and rows. By ticking one of the product varieties, you can find out how much of it is sold by one or more sellers.


Field parameters are also configured separately. In the example, we see that a certain Roma seller sold shirts for a specific amount in a specific month. By clicking the mouse, we in the line “Amount by field ...” call up the menu and select “Parameters of value fields”.


Next, to summarize the data in the field, select "Quantity". We confirm the choice.

Look at the table. It clearly shows that in one of the months the seller sold shirts in the amount of 2 pieces.


Now we change the table and make the filter work by months. We transfer the “Date” field to the “Report Filter”, and where “Column Names” is, there will be “Seller”. The table displays the entire sales period or for a specific month.

Selecting cells in a PivotTable will bring up a tab called PivotTable Tools with two more tabs, Options and Design.


In fact, you can talk about the settings of pivot tables for a very long time. Make changes to your taste, achieving a convenient use for you. Don't be afraid to push and experiment. You can always change any action by pressing the Ctrl + Z key combination.

I hope you have learned all the material, and now you know how to make a pivot table in excel.

To analyze large and complex tables, they usually use . Formulas can also be used to group and analyze existing data. Let's create simple reports using formulas.

As a starting point, we will use ( Insert/ Tables/ Table) containing information about sales of batches of products. The rows of the table contain data on the supply of a batch of a product and its sale. A similar table was used in the article .

The table has columns:

• Product- the name of the batch of goods, for example, " oranges»;
• Group- product group, for example, " oranges"are included in the group" Fruits»;
• date of delivery– Date of delivery of the Goods by the Supplier;
• Sales region- The region in which the consignment of the Goods was sold;
• Sales- The cost at which it was possible to sell the consignment of the Goods;
• Sales- the period of actual sale of the Goods in the Region (in days);
• Profit- a note on whether a profit was received from the sold lot of the Goods.

Through Name Manager adjust the tables to " source_table» (see example file).

Using formulas, we will create 5 simple reports that we will place on separate sheets.

Report No. 1 Total sales of Goods

Find the total sales of each Product.
The task is solved quite simply using the SUMIF() function, however, the construction of the report itself requires certain skills in working with some EXCEL tools.

So let's get started. To begin with, we need to create a list of names of the Goods. Because in the Product column of the source table, the names are repeated, then we need to select only values ​​from it. This can be done in several ways: formulas (see article), through the menu Data/ Working with data/ Remove duplicates or with . If you use the first method, then when adding new Products to the source table, new names will be included in the list automatically. But, here, for simplicity, we use the second method. For this:

• Go to the sheet with the original table;
• call ( Data/ Sort & Filter/ Advanced);
• Fill in the fields as shown in the figure below: set the switch to the position Copy the result to another location; in the Source Range field, enter $A$4:$A$530; Check the box Only unique records.

• Copy the resulting list to the sheet in which the report will be placed;
• Sort the list of products ( Data / Sort & Filter / Sort A to Z).

You should get the following list.

In a cell B6 enter the following formula, then copy it down to the end of the list:

SUMIF(Source_Table[Item],A6,Source_Table[Sales])

COUNTIF(Source_Table[Item],A6)

Report #2 Product Sales by Regions

Find the total sales of each Product in the Regions.
Let's use the list of Goods created for Report No. 1. Similarly, we will get a list of the names of the Regions (in the Initial range field, enter $D$4:$D$530).
Copy the resulting vertical range to Clipboard and it to horizontal. Place the resulting range containing the names of the Regions in the report header.

In a cell B 8 we introduce the following formula:

SUMIFS(Source_Table[Sales];
Source_Table[Item];$A8;
Source_Table[Region of sale];B$7)

The formula will return the total sales of the Product whose name is placed in the cell A8 , in Region from cell AT 7 . Pay attention to the usage (references $A8 and B$7), you will need it when copying the formula for the rest of the blank cells in the table.

Copying the above formula into the cells on the right with the help will not work (this was done for Report No. 1), because in this case in the cell C8 the formula will look like this:

SUMIFS(Original_Table[Sales, days];
Source_Table[Group];$A8;
Source_Table[Sales];C$7)

Report #3 Filtering Products by profitability

Let's go back to the original table. Each batch of Goods either made a profit or did not (see the Profit column in the source table). Let's calculate sales by Product Groups depending on profitability. To do this, we will filter records of the source table by the Profit field using formulas.

FREQUENCY(Source_Table[Sales, days];A7:A12)

Select a range to enter a formula C6:C12 , then in Formula bar enter the above formula and click CTRL+ SHIFT+ ENTER.

The same result can be obtained using the usual SUMPRODUCT() function:
=SUMPRODUCT((Source_Table[Sales, days]>A6)*
(Source_Table[Sales, days]<=A7))

Report No. 5 Goods supply statistics

Now we will prepare a report on the supply of Goods for the month.
First, let's create a list of months by year. In the original table, the earliest delivery date is 07/11/2009. You can calculate it using the formula:
=MIN(Source_Table[Delivery Date])

Let's create a list of dates - starting from the earliest delivery date. To do this, we use the formula:
=OMONTH($C$5,-1)+1

As a result, we get a list of dates - the first days of the months:

Applying the appropriate cell format, change the display of dates:

The formula for calculating the number of delivered consignments of Goods per month:

SUMPRODUCT((Source_Table[Delivery Date]>=B9)*
(Source_Table[Delivery Date]

Now let's add rows to count the total number of games for each year. To do this, we will slightly change the table by highlighting the year in which the delivery was made in a separate column using the YEAR() function.

Now, for the output by years, we will create a structure through the menu item:

• Select any cell of the modified table;
• Call window via menu item Data/ Structure/ Subtotals;
• Fill in the fields as shown in the figure:

After clicking OK, the table will be modified as follows:

Yearly subtotals will be created.
By pressing the small buttons in the upper left corner of the sheet, you can control the display of data in the table.

Summary :

Reports similar to those created can, of course, be made using or using filters to the original table or with the help of other functions BDSUMM() , BIzVARCH() , COUNT() , etc. The choice of approach depends on the specific situation.