MicrosoftExcel is one of the components of MicrosoftOffice, which can process all kinds of data, analyze statistical data and assist decision-making. Widely used in management, statistical finance, finance and other fields. At present, excel is widely used, powerful and easy to use! In Excel, formulas and functions have played a role in improving the efficiency of making Excel tables! Many friends can't use it! Below, I will share with you some skills for Excel to learn and master formulas and functions quickly. Let's study together! Excel quickly learns the skills of mastering formulas and functions: 1. Form a good habit of function input. For beginners, when entering a function, you can open the function parameter dialog box and enter parameters in the parameter text box. You can enter parameters directly, or you can position the input cursor in the parameter box, and then drag the mouse pointer in the table to select parameters. For users with a certain foundation, you can directly enter "=" in the edit field, and then enter the function name. When the related function options appear below, you can directly click this option to complete the function input, or you can enter parameters according to the following prompts. 2. Be good at learning functions with the help of help functions. When we encounter a function that we can't understand, we can use the help function of Excel. Press F 1 to open the help window, search for the function you want to view in the search box, and click the searched link to view the help about the function. 3. Easily debug the functions entered by Excel functions. If there is an error, you can debug the function by selecting parameters, pressing F9 to evaluate and evaluating formulas. This is a commonly used function when parsing functions, and it is called the "Three Musketeers" for parsing function formulas. (1) parameter selection: put the input cursor on the function name in the edit bar, and the syntax of the function will be displayed below. Click the corresponding function parameter and select the expression part corresponding to this parameter in the formula, so as to analyze the particularly long formula. (2) Press F9 to evaluate: select a part of the formula, press F9 to display the calculation result of the selected formula, and press Esc to cancel the result display and restore to the original state. (3) Formula evaluation: If the formula calculation is wrong, it can be calculated step by step by formula evaluation to find out the location of the error. Click Formula → Formula Approval Group → Formula Evaluation to open the formula evaluation dialog box. Click the evaluate button one by one and calculate step by step until all formulas are calculated. 4. Accurately understand the complex formulas written by others. It is difficult to directly understand the long and nested formulas written by others. You can understand them accurately by following the steps below. (1) Sort out the structure and grammar of each function contained in the formula one by one from the table to the inside. (2) Find the parameters according to the outermost function, define the structure of the formula, and record each parameter separately. (3) Analyze the function of each parameter one by one. (4) If the parameter contains multiple layers of nesting, you can understand its meaning one by one in the same way. 5. When using formulas and functions, you must learn absolute reference and relative references. It is a common operation to refer to cells. Reference is to get the address name of a cell or data range. Let's look at the following example first. Open the file "Relative References and Absolute References". Xlsx ",and in the" demand "worksheet, you need to calculate the salary ratio of each department according to the total salary of each department. It can be seen that the formula used in the cell C 1 is "=B2/SUM(B2:B6)", and the salary ratio of "Personnel Department" is calculated as "14.89%". "B2" and "B2: B6" here are references to cells and cell ranges. Filling the result down to C5 cell, we can see that the total salary of "Logistics Department" is the least, but the proportion is "100%". What is the cause of the mistake? Select cell C6, and the formula in the edit bar is "= B6/ sum (B6:B 10)". We can see that the reference will change with the change of the filling area, and the change of the numerator "B6" is correct, but the change of the parameter reference of the SUM function for calculating the total salary is wrong, so this part of the parameter reference does not need to be changed at this time. Here comes the question of absolute quotation and relative references. What is an absolute quotation? What is relative references? What's the difference between them? Here is the difference between relative references and absolute quotation. (1) relative references. Select the worksheet Absolute References and relative references in the relative references and Absolute References file. Xlsx ",select cell B9, Enter the formula" =B 1 ",and then press Enter. This refers to cell B 1 in cell B9, and the result presented by cell B9 is the content "name" in cell B 1. Select cell B9 and fill it horizontally to the right. You can see that the references in the cells become C 1, D 1 and E 1, and the column numbers also change with the cells. Select cell B9 and fill it vertically downward. You can see that the references in the cells are changed to B2, B3 and B4, and the line numbers change with the cells. This way of quoting is relative references. Tip: If you select B9 cell and fill it vertically, you can see "#REF!" In cell B8. A that indicates a reference error. (2) Absolute reference of fixed columns. Select cell B 15, enter the formula "=$B 1", press enter, and then fill it horizontally to the right. You will see that the references in the cells and the display results will not change. Here, the symbol "$" is added before "B" to indicate the locked column number. This reference method is absolute reference. Select cell B 15 and fill it vertically downward. You can see that the references in the cell will be changed to $B2, $B3 and $ b4. The "$" symbol only locks the column number. When filling down, the line number will still change. (3) Absolute reference of fixed line. Select cell B20, enter the formula "=B$ 1", press enter, and then fill it horizontally to the right. You will see that the references in the cell become C$ 1, D$ 1 and E$ 1. At this point, the line number is locked. Select cell B20 and fill it vertically downward. You can see that the line number referenced in the cell will not appear. (4) Fixed absolute references of rows and columns. Select cell B25, Enter the formula "=$B$ 1", and then press Enter. At this time, add the "$" symbol before the line number and column number, and then fill it horizontally to the right or vertically downward, and the reference and display results in the cell will not change. Use F4 to switch between relative reference and absolute reference. Double-click the cell containing the reference content or click the edit bar, and press F4 after entering the editing state, and you will see that the reference switches cyclically between "b 1", "b $ 1" and "b $ 1". Next, back to the case of 1, select C2 cell of the "Requirements" worksheet, where the parameter "B2:B6" of the SUM function needs to remain unchanged, and absolute reference can be used. Select "B2:B6" in the edit bar, and then press F4. You can see that the cell range reference becomes "$B$2:$B$6". Tip: Select a complete cell range reference in the formula, and then press F4 to change the whole formula. Press Enter and fill it in C6 cell, then you can calculate the salary proportion of each department. Let's consolidate the knowledge of relative references and absolute quotation through a small case. Select the "Apply" worksheet and find the cumulative sum of cells in column A. Select cell B2, Enter the formula "=SUM($A$2:A2)" and press Enter. Fill the cell B 10 with the fill function, and you can calculate the cumulative sum of the cells in column A in sequence in column B. 6. In this way, any complicated formula can not be wrong. For complex formulas, you can write the framework first, leave out the parts that you can't write for the time being, then solve them module by module, and finally assemble all the modules. For example, the following formula. The specific writing is as follows. Write out all kinds of situations first. First, analyze the case of 1. If A2 is greater than or equal to 90, 1 is returned; otherwise, re-nesting is performed. = IF(A2 & gt； =90, 1, a double nesting) and then analyze the second case. According to the above conditions, A2 is less than 90 at this time. If A2 is greater than or equal to 80, 0.7 will be returned; otherwise, double nesting will be performed. IF(A2 & gt； =80, 0.7, double nesting) Analyze the remaining situations in turn and write the formulas in turn until the final completion. IF(A2 & gt； = 70,0.5, triple nesting) if(a2 >；; = 60,0.3, quadruple nesting) if(a2 >；; =50, -0. 1, -0.2) is finally replaced step by step from back to front, with "if(a2 >；; =50, -0. 1, -0.2) "to replace" quadruple nesting "and get" if(a2 >；; =60, 0.3, if (A2 & gt=50, -0. 1, -0.2)), then replace "triple nesting" with it until it is finished, and finally write the formula: "= IF(A2 & gt；; =90， 1，IF(A2 & gt； = 80,0.7, if (A2> = 70,0.5, if (A2> = 60,0.3, if (A2 & gt=50,-0. 1,-0.2))). Share here about "How does Excel learn and master formulas and functions quickly? "That's all the content. I hope everyone can learn quickly! For students who want to study Excel functions professionally, we still have many specialized courses to learn. Why not experience it?