Yulia Perminova
Coach of Softline Training Center since 2008.
A basic tool for working with a huge amount of unstructured data from which you can quickly draw conclusions without fiddling with filtering and sorting manually. PivotTables can be created with a few steps and can be quickly customized depending on how you want to display the results.
Useful addition. You can also create PivotCharts based on PivotTables that will automatically update when they change. This is useful if, for example, you need to regularly generate reports on the same parameters.
The initial data can be anything: data on sales, shipments, deliveries, and so on.
Before us is a table with unstructured data. We can organize them and customize the display of the data that we have in the table. We send the “Amount of orders” to “Values”, and “Sales”, “Sale date” - to “Lines”. According to different sellers for different years, the amounts were immediately calculated. If necessary, you can expand every year, quarter or month - we will get more detailed information for a specific period.
The set of options will depend on the number of columns. For example, we have five columns. They just need to be positioned correctly and choose what we want to show. Let's say the amount.
You can detail it, for example, by country. We transfer "Countries".
You can see the results by sellers. Change "Country" to "Sellers". For sellers, the results will be as follows.
This geo-referenced data visualization method allows you to analyze data, find patterns that have a regional origin.
Useful addition. Coordinates do not need to be written anywhere - it is enough just to correctly indicate the geographical name in the table.
The points on the map are our cities. But we are simply not very interested in cities - it is interesting to see information tied to these cities. For example, amounts that can be displayed through the height of the column. Hovering the cursor over the column shows the amount.
Also quite informative is the pie chart by year. The size of the circle is given by the sum.
Often, seasonal patterns are observed in business processes, which must be taken into account when planning. Forecast sheet - most precision tool for forecasting in Excel than all the functions that were before and are now. It can be used to plan the activities of commercial, financial, marketing and other services.
Useful addition. To calculate the forecast, you will need data for earlier periods. Forecasting accuracy depends on the amount of data by period - better than no less than a year. You require equal intervals between data points (for example, a month or an equal number of days).
In the example below, we have data for 2011, 2012 and 2013. It is important to indicate not numbers, but time periods (that is, not March 5, 2013, but March 2013).
For the forecast for 2014, you need two sets of data: dates and their corresponding indicator values. Select both rows of data.
On the Data tab, in the Forecast group, click on Forecast Sheet. In the Create Forecast Sheet window that appears, select the forecast presentation format - a graph or a histogram. In the "End forecast" field, select the end date, and then click the "Create" button. The orange line is the forecast.
This functionality is perhaps the first step towards what can be called business analysis. It's nice that this functionality is implemented in the most user-friendly way: the desired result is achieved in just a few clicks. You don't have to count anything, you don't have to write down any formulas. It is enough to select the desired range and choose what result you want to get.
Useful addition. You can instantly create Various types charts or sparklines (micrographs right in the cell).
IN fast analysis there are also several formatting options. You can see which values are larger and which are smaller in the cells of the histogram.
You can also put multi-colored icons in the cells: green - the largest values, red - the smallest.
We hope that these techniques will help speed up the work with data analysis in Microsoft Excel and quickly conquer the heights of this complex, but so useful application in terms of working with numbers.
If you need to develop complex statistical or engineering analyses, you can save steps and time with an analysis package. You provide data and parameters for each analysis, and the tool uses the appropriate statistical or engineering functions to calculate and display the results in an output table. Some tools create charts in addition to output tables.
Data analysis functions can only be used on one sheet. If data analysis is carried out in a group consisting of several sheets, then the results will be displayed on the first sheet, on the remaining sheets empty ranges containing only formats will be displayed. To analyze data on all sheets, repeat the procedure for each sheet individually.
Note: To enable the feature Visual Basic for Applications (VBA) for Analysis Pack, you can download the add-in " Analysis Pack - VBA" in the same way as when loading the analysis package. In the dialog box Available add-ins check box Analysis Pack - VBA .
To download the analysis pack to Excel for Mac, follow these steps:
On the menu Service select add-ons excel.
In the window Available add-ons check the box Analysis package, and then click the button OK.
If the add-on Analysis package missing from field list Available add-ons, press the button Review to find her.
If you receive a message that the analysis pack is not installed on your computer, click Yes to install it.
Exit the Excel application and restart it.
Now on the tab Data command available Data analysis.
There are several third-party add-ins that provide Analysis ToolPak functionality for Excel 2011.
Option 1. Download statistical software XLSTAT Mac Add-in and use it in Excel 2011. XLSTAT contains more than 200 basic and advanced statistics tools that include all the features of the Analysis ToolPack.
Select the version of XLSTAT that matches your operating system. Mac system OS and download it.
Open excel file, which contains the data, and click the xlstat icon to open the xlstat toolbar.
Within 30 days you will have access to all the features of XLSTAT. After 30 days, you can use free version, which includes the functions of the analysis package, or order one of the more complete XLSTAT solutions.
Option 2. Download Statplus: Mac LE for free from Analystsoft and then use Statplus: Mac LE with Excel 2011.
You can use Statplus: Mac LE to perform many of the functions that were previously available in analysis packages such as regression, histograms, analysis of variation (Two-way ANOVA), and t-tests.
Go to the AnalystSoft website and follow the instructions on the download page.
After downloading and installing Statplus: Mac LE, open the workbook containing the data you want to analyze.
This article will discuss how to analyze data using a pivot table. For training, you can use the table available at this link (simple table.xlsx).
The first thing you might want to analyze using a pivot table is to summarize subtotals. In our example, this may be the need to calculate the sales volumes for all stores for each date.
To do this, click on any row header of the pivot table (in our example, these are the fields Date, point of sale And Phone brand), and in the opened tabs Working with pivot tables go to tab Options. You have to press the button on it. Field Options in Group active field.
In the window that opens, the first tab will be the tab.
The absence of such a bookmark means that you have not selected a row header, that is, the cursor is placed on a cell with a numeric value.
Bookmark Subtotals and filters You can select a condition for displaying subtotals. The following conditions are offered:
By setting automatic subtotaling, we get the following table, which contains subtotals for each condition:
If setting subtotals with the command Field Options, does not give visible results, check the settings for displaying subtotals using the command Subtotals groups Layout tabs Constructor.
Let's say we want to display subtotals for dates only, hiding subtotals for points of sale. To do this, click on any field of the table with the name of the store and call context menu. In it, you need to uncheck the condition Subtotal: point of sale. As we can see, subtotals remain only for dates:
It is often necessary to sort the data in a PivotTable to better understand it. To do this, simply select the field by which you want to sort, go to the tab Are common, in Group Editing click on the button Sort and filter and set the sorting conditions you need.
Very useful feature to analyze information in a pivot table is the ability to group data. For example, we need to group our sales by week of the month. To do this, select the dates that are included in the first week (15.05-21.05):
Please note that for ease of selection, we have collapsed the data for individual stores by using the + button on the left side of the cell with the name of the store.
Next, you need to run the command Group by selection groups Group tabs Options. A new column will appear in the table, in which the field Group1 will merge the fields we have selected.
It remains only to rename the name of the group by simple editing cells:
To ungroup, just use the command Ungroup from the same group, after selecting the field to be ungrouped. Please note that you cannot ungroup a field that we included in the pivot table build condition - for example, the field Point of sale or date.
Let's consider another way to display data that will help us analyze the information from our table. For example, we need to know the volume of revenue not in monetary terms, but as a percentage of the total revenue for the entire sales period.
To do this, select any cell in the Revenue column of our pivot table. After that you need to run the command Field Options in Group active field tabs Options.
In the dialog box that opens, go to the tab Additional Calculations and select from the dropdown menu Percentage of total by column. After clicking the OK button, our table will look like this:
If the data in the table will not be displayed as percentages, check the settings for the number format of the cells (this can be done immediately in the dialog box Field Options by clicking on the button Numeric format, or by calling the corresponding window from the context menu).
When performing complex analytical tasks on statistics (for example, correlation and variance analysis, calculations using the Fourier algorithm, creating a predictive model), users often wonder how to add data analysis to Excel. The designated package of functions provides a versatile analytical toolkit, useful in a number of professional areas. But it does not apply to the tools included in Excel by default and displayed on the ribbon. Let's find out how to enable data analysis in Excel 2007, 2010, 2013.
Consider data analysis in Excel: how to enable and how the activation procedure will differ for other versions. In most versions of the program, the procedure is the same. Therefore, the steps in this section work for most versions, including 2013 and 2016 releases.
The package under consideration belongs to the category of add-ons, that is, complex analytical additions. Accordingly, to enable the package, go to the add-ons menu. This procedure is performed as follows:
Let's see how to activate the analytical functions provided by the package add-on:
The analytical package operates with a large set of tools that optimize the solution of statistical problems. Some of the number:
To apply one or another option, follow the algorithm below:
The functions included in the package are designed to use numbers from only one Excel sheet. If the desired statistical values are placed on several sheets, you will need to first create a pivot table by copying the required parameters there.
The algorithm for enabling data analysis in Excel 2007 differs from the rest in that at the very beginning (to access the Excel options), instead of the "File" button, the user presses a four-color Microsoft symbol office. Otherwise, the sequence of operations is identical to that given for other versions.
Data analysis in Excel involves the design itself spreadsheet processor. A lot of program tools are suitable for this task.
Excel positions itself as the best all-rounder software in the world in the processing of analytical information. From small business to large corporations, executives spend a significant part of their working time analyzing the vital activity of their business. Consider the main analytical tools in Excel and examples of their application in practice.
One of the most compelling data analyzes is what-if. It is located: "Data" - "Working with data" - "What-if".
What-If Analysis Tools:
A practical example of using "What-if" to find the best discounts on a data table.
Other tools for data analysis:
You can analyze data in Excel using built-in functions (mathematical, financial, logical, statistical, etc.).
To make it easier to view, process, and summarize data, Excel uses PivotTables.
The program will treat the entered / entered information as a table, and not a simple data set, if the lists with values are formatted accordingly:
The default formatting style is applied to the specified range. The Table Tools tool (Design tab) becomes active.
You can create a report using the PivotTable.
Creating a pivot table is already a way to analyze data. Moreover, the user selects the information he needs at a particular moment to display. He can use other tools in the future.
Powerful data analysis tool. Let's consider the organization of information using the "What-if" tool - "Data Table".
Important conditions:
Procedure for creating a "Data Table":
To analyze the activities of the enterprise, data are taken from the balance sheet, income statement. Each user creates his own form, which reflects the characteristics of the company, important information for decision-making.
