What is the relationship between formulas and functions in analyzing excel?
Functions and formulas go hand in hand.
Functions can make complex arithmetic formulas concise.
For example, the numbers 1, 2, 3, 4, 5 are in the cells A1, B1, C1, D1, E1. If you use the formula to sum, you can enter =A1+B1+C1+D1+E1 in F1, but if you use the function, you can enter it as
=sum(A1:E1).
Also, in the case of functions, again, you have to follow the formula algorithm.
excel function formulas detailed
excel function formulas detailed
excel function formulas detailed, in Excel forms, we often use Excel formulas to count some reports or data, etc., it is inevitable that we have to use the addition, subtraction, multiplication, division, the following look at excel function formulas detailed.
excel function formula 1
First, digital processing
1, take the absolute value = ABS (digital)
2, take the whole = INT (digital)
3, rounding = ROUND (digital, decimal digits)
Second, the judgment formula
1, the formula produces an error value is displayed as empty!
Formula: = IFERROR (A2/B2, “”)
Description: If the value of the error is displayed as empty, otherwise normal display.
2, IF multi-conditional judgment return value
Formula: = IF (AND (A2 & lt; 500, B2 = “not due”), “make up the money”, “”)
Description: the two conditions at the same time with the establishment of AND, any of which was established with the OR function.
Third, the statistics formula
1, statistics on the duplication of two tables
Formula: = COUNTIF (Sheet15!A:A,A2)
Description: If the return value is greater than 0 indicates that there is another table, 0 does not exist.
2, statistics do not repeat the total number of people
Formula: = SUMPRODUCT (1/COUNTIF (A2: A8, A2: A8))
Description: COUNTIF with the number of occurrences of each person counted with 1 in addition to the number of occurrences into the denominator, and then add up.
Fourth, the sum formula
1, summing across the columns
Formula: = SUMIF ($A $ 2: $G $ 2, H $ 2, A3: G3) or = SUMPRODUCT ((MOD (COLUMN (B3: G3), 2) = 0) * B3: G3)
Description: If the header line does not have the rules with the 2nd formula.
2, single-conditional summation
Formula: = SUMIF (A: A, E2, C: C)
Description: the basic use of SUMIF function
3, multi-conditional fuzzy summation
Formula: = SUMIFS (C2: C7, A2: A7, A11 & “*”, B2: B7, B11, B2: B7, B11)
Description: If there is no rule in the header line with the 2. B2:B7,B11)
Description: In sumifs you can use the wildcard *
5, multiple tables in the same location and summing
Formula: = SUM (Sheet1:Sheet19!B2)
Description: After the table is deleted or added in the middle of the table, the results of the formula will be automatically updated.
6. Summing by date and product
Formula:=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
Description: SUMPRODUCT can be accomplished by multi-conditional summation
V, Find and reference formula
1, single-condition find formula
Formula: = VLOOKUP (B11,B3:F7,4,FALSE)
Description: Find is the best of VLOOKUP, the basic use of
2, two-way find formula
Formula: = INDEX (C3:H7, MATCH(B10,B10,B10,B10,MATCH(B10,B10,B10,B10,MATCH(C3:H7),4) MATCH (B10,B3:B7,0),MATCH (C10,C2:H2,0))
Description: Use the MATCH function to find the location, take the value with the INDEX function
3, to find the last record that meets the conditions.
excel function formula detailed 2
EXCEL function formula references that thing
Speaking of relative references and absolute references in Excel, is to make a lot of friends who have just contacted Excel is a very headache, but to learn Excel knowledge in depth, this issue must also be fully understood to be able to.
Because relative references and absolute references in many operations will be used, for example: conditional formatting, data validity, function formulas, advanced charts and even macros and VBA code.
If you can’t figure out how to reference them, you can’t talk about all the great applications.
When the formula reference cell or region, the type of reference can be divided into the following three:
Relative reference:
When the formula is copied to other cells, rows or columns of reference will change. The so-called row or column references will change, that is, the numbers representing the rows and the letters representing the columns will change accordingly to the actual offset.
An example: D5 cell enter the formula = A1
(here we do not post a picture of Ha, you can open Excel to operate) when the formula to fill down to D6, D7 cells, the formula will become = A2, = A3.
If you fill the formula to the right to the E5, F5 cells, when it will become = B1, =C1……
This relative reference is in fact the wind down, willing to do a wall of grass ah.
Absolute reference:
When the formula is copied to other cells, rows and columns of the reference will not change.
An example: cell D5 enter the formula = $ A $ 1.
When filling down the formula.
When filling down the formula, no matter which cell is copied and filled, the formula is still = $A$1.
When filling to the right of the formula, no matter which cell is copied and filled, the formula is still = $A$1.
This is an absolute reference to the sea of the heart is unchanging, a lifetime of forever.
Mixed references:
One of the rows or columns is a relative reference, the other is an absolute reference.
An example: D5 cell enter the formula = A $ 1.
Note that here and in the above example there is less of a $ sign, when the formula is filled down to the D6, D7 cells, the formula is still = A $ 1.
If you fill the formula to the right to the E5, F5 cells, it will become = B $ 1, = C $ 1. ……
You see here that because a $ sign is added in front of the line number, the line number is fixed, while the column label is not preceded by a $ sign, the column label will change. Does this mean that it’s better to do things where there’s money?
There is another use of mixed references: = $ A1.
Here and in the above example is almost the same pattern, only the $ sign to the front of the column header to go, when the formula is filled down to D6, D7 cells, the formula will become = $ A2, = $ A3 ……
If you fill the formula to the right to E5, F5 cells, the formula is still = $ A1.
Excel Practical Tips
Double-click on a side of the cell to move the selected cell
In the worksheet to move the selected cell there is a shortcut: the mouse pointer is placed on one side of a selected cell, pay attention to the direction of the move to choose and move! related to the side, that is, to move down, the mouse pointer will be placed at the bottom of the cell; if you want to move to the right, the mouse pointer will be placed on the right side of the cell;
and so on. This time the mouse pointer into the shape of a white arrow, double-click to select a side of the cell, the mouse pointer will move along the selected direction to a specific cell. If this direction is adjacent to a blank cell, it will move to the farthest consecutive blank cells; if this direction is adjacent to a non-blank cell, it will move to the farthest consecutive non-blank cells.
Double-click on a cell edge to select the cell area
Similar to the previous tip, if you double-click on a cell edge while pressing the Shift key, depending on whether the adjacent cells in this direction are blank or non-blank, you can select the area from this cell to the farthest blank or non-blank cell.
Quickly select discontinuous cells
Press the key combination to activate the “Add Selected” mode, at this time, the status bar below the worksheet will show the word “Add to Selected”, and then click the discontinuous cells or cell areas can be selected. Or cell region can be selected, without having to hold down the Ctrl key.
Change text to digital
In the work, found that some text files or other financial software data imported into Excel is actually in the form of text (digital default is right-aligned, while the text is left-aligned), even if it is to reset the cell formatting for the number is also not helpful. There is a way to quickly turn these documents back to the digital: fill in the blank cells in the number 1, and then select the cell, copy, and then select the range to be converted, select the “selective paste” in the “multiply”, you will find that They are changed to numbers.
Select cells according to the conditions
Press F5, in the open “Positioning” dialog box, click the “Positioning Conditions” button, according to the type of region you want to select, “Positioning Conditions” dialog box. “dialog box, select the need to select the cell type (), such as” constants “,” formula “and so on, at this time you can also check” digital “, “Text” and other items, click “OK” button after all cells will be selected to meet the conditions.
excel function formula 3
1, A1 * B1 = C1 Excel multiplication formula
① First of all, open the form, in cell C1, enter “= A1 * B1” multiplication formula.
② After the input is complete, you will find that in cell C1 will show “0”, of course, because now there is no data to be multiplied by it, naturally, it will show 0.
③ Now in the “A1” and “B1” cell to enter the data to be multiplied by the product, the following chart, respectively, in cells A1 and B1 enter 10 and 50 for multiplication, the results will be displayed in C1, equal to “500”.
2, Excel multiply multiple cells multiplication formula
① In cell E1, enter the multiplication formula “= A1 * B1 * C1 * D1”.
② Then enter the data to be multiplied in A1, B1, C1, D1, and the result will be displayed in “E1”!
Look at the results in the figure is correct ah! In fact, this method is almost the same as the above, just a few more numbers. So what is the formula for excel multiplication function?
Because in the work of more than multiplication is so simple, occasionally there will be some need to “add, subtract, multiply and divide” together with the arithmetic time, so when encountered this mixed arithmetic should be how to achieve it? Here we have to look at the elementary school math have not learned well. Here to do an elementary school math problems!
3, Excel mixed operations multiplication formula, 5 plus 10 minus 3 times 2 divided by 3 equals how much?
Hint: plus = +, minus = -, multiply = *, divide = /.
① First of all, understand how to write this formula, “5 + 10-3 * 2/3” This is the wrong way to write, the correct way to write should be “(5 + 10-3) * 2/3”.
② Well, know the formula, should not immediately come to Excel in the “F1” in the input “= (A1 + B1 – C1) * D1 / E1”.
③ Then in turn in A1, B1, C1, D1, E1, enter the data to be operated.
Well, some of the above basic multiplication formula has been said to play, the following teach you a little skill, in the case of multiple lines need to calculate how to do it?
4, the formula will be copied to each line or column
① First of all, use the mouse to select the “F1” cell until the mouse becomes a black cross, the left button hold down to drag down.
② At this point, from F1 to the following F2, F3, F4 and so on, have copied the “F1” in the formula, the next time you need to operate, directly in front of the input data in F2, F3, F4 and other cells will automatically display the results of the operation.
EXCEL formulas and functions of the difference between what
To make the difference between the two very clear may be difficult. The two are both linked to the difference between the two, in general, the function is Excel provides a solution to a calculation, find and replace, extract the function of the module, Excel function has a total of 11 categories, respectively, the database function, date and time functions, engineering functions, financial functions, information functions, logical functions, query and reference functions, mathematical and trigonometric functions, statistical functions, text functions, as well as user-defined functions. customized functions. The formula is generally to solve a practical problem for the calculation of the formula, generally a combination of multiple functions, but can also not contain a function, and direct calculation. The formula is generally combined with the actual data in the worksheet, the reference worksheet cells, is a method of solving specific problems with the function. In this sense, the function is the basis of the formula. Formula by calling the function so as to achieve complex data processing functions. It is these marvelous combinations, so that Excel has more of an endless charm! The same problem, you have a different degree of mastery of Excel, as well as your mathematical modeling ability, different logical thinking, it is likely to call different functions to combine into a formula. A good formula, like a good poem written by a poet, breathtaking!