Many office users often face a number of problems when trying to create and edit any office documents. This is often due to the fact that companies use several office programs, the principles of which can vary greatly. Especially a lot of difficulties can create an Excel pivot table.
Fortunately, MS Office 2010-2013 appeared relatively recently, which not only includes a number of updated programs for processing text files, tables, databases, and presentations, but also allows several employees to work with them at the same time, which is simply invaluable in a corporate environment.Why exactly the new versions?
To understand this issue, you need to imagine all the most significant changes that have occurred with this office suite.
, Excel , , . , «», , . .
Excel, , - .
, — , , . , Excel.
What is it and what is it for?
The most significant change in the new versions of the "Office" is their completely redrawn interface, which was named the creators of Ribbon (tape). In the feeds, all 1,500 teams are conveniently grouped into categories, so you don’t have to look for them for a long time. To fully comply with these parameters, the developers added improved pivot tables to Excel.
Today we will consider Excel "for dummies". Pivot tables are a special tool in which the results of a process are visually grouped. Simply put, with their help you can see how many products each seller sold for his shift. In addition, they are used in cases where it is necessary:
- Prepare analytical calculations for writing final reports.
- Calculate each indicator individually.
- .
- .
. , Excel , .
Excel? , . . :
, : , 9 , n-.
Having prepared all the initial information, place the cursor in the first cell of the first column, open the "Insert" tab, and then click on the "Pivot table" button. A dialog box will immediately appear in which you can do the following operations:
- If you immediately click on the “OK” button, the Excel pivot table will immediately be displayed on a separate sheet.
- Fully customize the output.
In the latter case, the developers give us the opportunity to determine the range of cells into which the desired information will be displayed. After that, the user must determine where the new table will be created specifically: on the existing one or on the newly created sheet. By clicking “OK”, you will immediately see the finished table in front of you. This completes the creation of pivot tables in Excel.
, . , . , .
«», « », « » «» ( ). .
To analyze sales by specific time periods, you must insert the relevant items in the table itself. To do this, go to the "Data" sheet and then insert three new columns immediately after the date. Select the column with the name of the product, and then click on the "Insert" button.
It is very important that all newly created columns are inside an existing table with the source data. In this case, creating pivot tables in Excel is not required again. You will simply add new fields with the data you require.
Formulas Used
For example, the newly created columns can be called "Year", "Month", "Months-Years". To get the data that interests us, in each of them we will have to write a separate formula for the calculations:- In the "Annual" we insert the formula of the form: "= YEAR" (referring to the date).
- : «=» ( ).
- : «=» ( ).
, . «», , «». ! Excel, .
?
«», « », . ? «», .
, «» , « ». , :
, , . « », « – ».
, « ». , «».
, , . , , .
. , .
To do this, put the cursor in the "Total" field, and then click on the "Delete total" button. After that, we automatically update the Excel pivot table in the manner described by us above (click on the right mouse button, select "Update").
As a result, this data disappears from our pivot table. To build a visual display of sales forecasts, put the cursor on the year from which we plan to build on, and then left-click on the button “Graph Forecast Model”.
After that, a beautiful and informative chart appears, on which the forecasted results of buying activity according to the forecast you are interested in are presented in a visual form. This will save your energy and save money that can be spent on the purchase of really necessary goods.Excel. , .
«». , , .
«» Office 2003. Microsoft Query. Excel, :
- , , , Excel, . , (Mail Merge Helper), .
- , , . , .
, , . .
Just click on the field you need with the left mouse button, and you can see the source data instead of the field names that appear in the main document. Fields can be inserted anywhere in the document.
Categories Used
Also here you can use the ASK category, which means “request”, and FILLIN, which means “fill in”, NEXT and NEXTIF, ELSE. You can also configure the parameters of those forecast points that will be displayed in the finished table. You can find the necessary record using a special filter or by simply sorting the data, as we mentioned above., Merge . , «». , ( ).
- , , . .
, Excel ( ) , .