Original link：How to do data analysis with Excel?
know: Jackpop, with 100,000+ followers and daily updates of fine content!
act asdata analysisMany tools may be used, Python, R, Java, etc.
However, there is no way to bypass Excel, although projects involving production are developed in programming languages or SQL, but some of the daily probing and analysis will use Excel.
How to use Excel for data analysis, I think it can be split into 2 ways of looking at it, one from an Excel perspective and one from a data analysis perspective.
Therefore, I broke down the Excel analysis into two steps:
- Excel mastery
- data analysis
Below, from these two aspects to step-by-step introduction to how to use Excel for data analysis.
I. Mastering Excel
In my opinion, learning the same knowledge should not immediately dive headlong into the study, but to sort out the study plan, step by step, clear regulations.
For Excel learning, I've broken it down into 4 tiers:
- newbie
- middle level (in a hierarchy)
- high level
- master
Here's a grade-by-grade description of what to learn.
newbie
In fact, the vast majority of students who think they know Excel are at the novice stage.
For the novice stage, I think the focus should be to learn the basic operation of Excel, for example, the most basic new, input, save, insert, new sheet, and some simplefunction (math.)For example, SUM, IF, AVERAGE, COUNT, ROUND.
middle level (in a hierarchy)
For text, dates, mathematical operations can easily build formulas, in addition, need to know some in-depth features, such as thepivot tablevlookup knows what plug-ins are and how to install them, understands and can use the auto-filtering feature, can record macros and use them, and can successfully edit/modify simple recorded macros.
Intermediate formula:SUMIF、COUNTIF、VLOOKUP、CHOOSE、MID
high level
In the advanced stage, one needs to focus on the following areas:
- Know what an array formula is and how to use it
- Macros can be created and modified using or disabling the macro recorder
- Know how to build macros that can be triggered by events
- Familiarity with most functions in Excel
- Simple teaching using Excel, able to have a structured knowledge of Excel
rating scale: SUMPRODUCT, INDIRECT, INDEX, MATCH, OFFSET
master
For the Excel expert level, add-ons can be built for distribution and widespread use through Excel, and, at that level, it's not just theknow how to useExcel formulas, but rather to be able to clearly recognize the advantages and disadvantages of each formula, and to be able to choose just the right usage in use.
How can I improve my Excel?
I think a lot of students in the process of learning Excel is demand-driven learning, in short, is encountered a problem, and then go to a single point of learning a little.
Personally, I don't think it's a good way to learn if you wish to go deeper in a particular area, and the time spent is not bad, but what you learn is sporadic and fragmented.
I have divided learning Excel into 2 stages:
- Learning the basics
- Daily accumulation
Learning the basics
In the basic knowledge of the learning stage, I recommend a systematic, systematic study of Excel, Excel, knowledge of what functions it has, and what each function is used for.
That way, when we run into a problem, we at least know that Excel is capable of doing the job, and then we can go back and deepen our understanding and impression.
In the basics learning stage, you can choose to follow a book or some online tutorials.
However, I personally recommend choosing a good book more because a good book has more detailed and comprehensive knowledge.
Here are 5 books that I think are pretty good, so if you are interested, you can choose the one that suits you and read it:
- "You should've played excel like this a long time ago.
- The Complete Book of Excel Applications
- Don't Be Afraid, Excel Functions Are Actually Very Simple
- Excel Functions and Formulas Application Skills
- Excel VBA is actually very simple.
Daily accumulation
As mentioned earlier, the book knowledge allows us to have a framework understanding of the basic knowledge of Excel, such as over really want to grow into an expert in the field of Excel, or need to continue to deepen and consolidate on a daily basis.
In order to deepen and consolidate, on the one hand, you need to use more, to have mastered the knowledge from the use of changes in proficiency. The second is to continue to draw new knowledge, this can be done by paying more attention to some forums or learning platforms, "three people, there must be my teacher," from the exchange of people, to be able to see a lot of their own do not understand the use of Excel.
Here, 2 good forums for you, not too many to recommend, greedy and chewy.
[Excelhome](/question/Excel Charts and Graphs - ExcelHome Technology Forums)
Excelhome is a well-known old Excel forum, now has about 5 million users, nearly ten million posts, from Excel basics, to function formulas, VBA, icons everything, not only rich resources, and bulls gathered, there are very many high-quality original content.
Excel Elite Forum
Like Excelhome, Excel Elite Forum is also a professional Excel communication and sharing forum, tips, formulas, VBA everything. The forum has a large number of posts, listing the use of Excel in a variety of issues, there are many Excel bull answer.
From these questions and answers, we can contact and learn a lot in the daily work of the scene can not be used, can greatly broaden the knowledge of Excel.
II. Data analysis
As one of the most popular data analysis tools, Excel is configured with a lot of features related to data analysis, such as pivot tables, which are a very popular analysis tool.
Here's how to import, explore, clean, analyze, and visualize data through Excel, with an in-depth discussion of various data analysis methods in Excel.
1. Sorting
Sorting data is a very critical and important part of data analysis, you can sort Excel data by multiple columns or a single column.
Sorting actions are mainly categorized as sorting in ascending or descending order.
single-column sorting
Consider the following data:
Let's sort the data based on column C. To sort the data, you need to do the following:
- Click any cell in the column you want to sort.
- Select "Start-> Sorting and Filtering", to sort the relevant columns
The sorted results are as follows:
multicolumn sorting
Single-column sort is more commonly used and more basic, the following is a slightly more complex multi-column sort.
Box multiple columns of data, click "Start - & gt; Sorting and Filtering", select "Customize Sort", you can add more than one sorting sequence, for each column you can also choose to ascending or descending order, as well as the sorting of the main basis, secondary basis:
In this way, when the primary bases are the same, they will be sorted according to the secondary bases.
2. Screening
When we want to get data that matches a specific condition, we need to use the s filter function.
Click on any individual cell in the data.
switch toData tab > Sorting and Filtering > FilteringAt this point, you will find that a small drop-down arrow appears in front of each column, click on the arrow to filter:
3. COUNTIF
COUNTIF is a very common Excel function used to meet a single condition of the region to count the cells.
Syntax:
= COUNTIF (range, condition)
- 1
An example:
Let's countStockis less than 10% of the number:
Here you can use COUNTIF, 2 parameters are the range of statistics, and judgment conditions.
This is just a simple example, you can learn by example, the conditional statement in the programming language will be often used, in the statistics of the use of data is also very frequent.
4. SUMIF
Excel SUMIF function to return to meet the individual conditions of the sum of the cells, usage and COUNTIF similar, are often used in statistical analysis of the operation.
Syntax:
= SUMIF (range, criteria, [sum_range])
- 1
5. Pivot tables
Pivot tables are a powerful and very useful feature in Excel, we can use them to summarize the data stored in the table.
Pivot tables make it possible to organize and rearrange statistical data, which in turn makes it clearer to devote your efforts to critical and valuable information.
Suppose, we want to statistically analyze the following data according to different centuries (Cent).
We can click "Insert -> Pivot Table" to create a pivot table, and then select the dimensions of their own concern to analyze:
The specific effects are as follows:
Doesn't it look much clearer?
Of course, we are free to organize filters, rows, columns, and statistical values to meet our individual analysis needs.
6. Hypothesis analysis using solvers
Hypothesis analysis is the process of changing values to try different values (scenarios) for a formula, and you can use several different sets of values in one or more formulas to explore all the different results.
Solver is a Microsoft Excel add-in program that can help at many levels in what-if analysis.
We can use this feature to find the best (maximum or minimum) value for a formula in a cell (called the target cell). This is subject to some constraints or limitations of other formula cell values on the worksheet.
The solver uses a set of cells, called decision variables or simply variable cells, to compute formulas in the objective and constraint cells. The solver also adjusts the values of the decision variable cells to handle the constraint cell limitations.
Thus, this helps in producing the desired results for the target unit.
Activating the solver plug-in
On the File tab, click Options, go to Add-ins, select Solver Add-in, and click the Go button.
Check the solver plug-in and click OK:
In the Analysis group of the Data tab, you can see that the Solver option has been added.
How to use solvers in Excel?
In this example, we will try to find a solution to a simple optimization problem.
concern: Suppose you are the owner of a business and want your income to be $3,000.
goal: Calculate the units to be sold and the price per unit to achieve the target.
For example, we created the followingmould：
- In the Analysis group of the Data tab, click the Solver button
- In Set Goals, select the Income cell and set its value to $3000
- To change the variable cells, select cells C3, C4, and C8.
- Click to solve
It will be noticed that the model will change a bit:
It seems to be a very simple operation, but in fact it involves loads of mathematical calculations and optimization problems behind it.
This can be done in a few simple steps with the Excel solver, and in this respect, Excel is indeed powerful in data analysis.
7. Data analysis toolkit
- Click the File tab, click Options, and then click the Add-ons category
- Select Analysis ToolPak and click the Go button.
- Check the Analytics Tool Library and click OK
In the Analysis group of the Data tab, you can now click Data Analysis:
Add image annotations, no more than 140 characters (optional)
In the data analysis toolkit, Excel provides a very large number of solver-like features and designs that greatly address the difficulty of analyzing data.
8. Descriptive statistics
As the name suggests, just give it a simple description and it can analyze the data on demand, for example:
- Mean, Median, Plurality and Range
- Variance and standard deviation
Suppose, we have the runs scored by a batsman in his last 10 matches, to generate a descriptive analysis, follow these steps.
- Go to Data tab > Analysis group > Data analysis
- Select Descriptive Statistics and click OK
- Select the range of inputs
- Select the range of outputs to display
- Checking summary statistics
At this point, the results of the descriptive analysis are available:
9. Analysis of variance
ANOVA (Analysis of Variance) in Excel is a statistical method used to test the difference between two or more means, whether using Excel or Python or R. ANOVA is a common method used in data analysis.
Given a statistic, here are the runs scored by the three batsmen in the last eight matches:
To perform a one-way ANOVA, the following steps need to be followed:
- Go to Data tab > Analysis group > Data analysis
- Select Anova: Single Factor and click OK.
- Select Inputs and Outputs and click OK
This way the ANOVA results are good:
10. Regression analysis
In Excel, we use regression analysis to estimate the relationship between two or more variables.
Consider the following data, in which we sold several COVID cases and masks in a given month.
- Go to Data tab > Analysis group > Data analysis
- Select Regression and click OK
It UI opens the following parameter window:
Choose to enter a Y range for the number of masks sold and an X range for COVID cases, check the residuals and click OK and we will get the following information:
Multiple R is a correlation coefficient that measures the strength of the linear relationship between two variables.
R Square denotes the coefficient of determination, which is used as an indicator of the goodness of fit. With the help of R Square, it is possible to track how many points fall on the regression line.
Standard Error is another goodness-of-fit measure that shows the precision of the regression analysis.
Through this content, I first introduced how to learn and advanced Excel, and then around the Excel data analysis, from shallow to deep, which has a relatively simple sorting, screening, but also more complex solver, analysis of variance, regression analysis, which are often used in data analysis work. In the past, we need to implement complex algorithms through programming languages, or install and call third-party toolkits, in fact, through Excel can also be easily dealt with, and more time-saving. Therefore, as a powerful data analysis tool, Excel is not only able to deal with some routine data processing, but also can deal with many complex data analysis needs.
An article can not be all-encompassing introduction, I hope that through this content can be Excel data analysis related to the string of knowledge, to give interested students a basic blueprint, interested students can then study in depth.