In the application of Excel, functions or formulas are inseparable. If you can master some common functions or formulas proficiently, it is definitely not a little bit for improving work efficiency!
1. Excel function: Sumif
function: sums the cells that meet the conditions.
grammatical structure: =Sumif (condition range, condition, [sum range]).
When the parameters "condition range" and "summing range" are the same, the "summing range" can be omitted.
Purpose: calculates "monthly salary" by "gender".
Method:
enter the formula in the target cell: =SUMIF(D3:D12,I3,G3:G12).
2. Excel function: Vlookup
function: searches for elements that meet the conditions in the first column of the table area, determines the row number of the cell to be retrieved in the area, and further returns the value of the selected cell.
syntax structure: =Vlookup (query value, data range, return column number, [matching mode]).
There are two matching modes: "0" and "1". "0" means exact matching and "1" means fuzzy matching. When this parameter is omitted, the default is fuzzy matching.
Purpose: query "monthly salary" based on "employee name".
Method:
enter the formula in the target cell: =VLOOKUP(I3,B3:G12,6,0). Interpretation of
:
1, the column where the query value is located must be the first column of the query data range.
2, "Number of returned columns" is relative to the "data range", not based on the data source.
3. Excel function: Text
function: converts the value into text according to the specified number format.
syntax structure: =Text (value or cell reference, format code).
Purpose: calculates the working hours of employees.
Method:
enter the formula in the target cell: =TEXT(F3-E3,"[m]minutes").
4. Excel function: Networkdays.intl
Function: uses a custom weekend parameter to return the number of complete working days between two dates.
grammatical structure: =Networkdays.intl (start date, end date, [statistics], [holiday]).
When the parameters "Statistical Method" and "Holidays" are omitted, the calculation is based on normal working days by default.
Purpose: calculates the "working days" of employees.
Method:
enter the formula in the target cell: =NETWORKDAYS.INTL(E3,F3,1,). Interpretation of
:
if there are other holidays, just add a cell range in the fourth parameter.
5. Excel function: Countifs
function: returns the number of cells specified by a set of given conditions.
grammatical structure: =Countifs (condition 1 range, condition 1, condition 2 range, condition 2...condition N range, condition N).
Purpose: counts the number of people with "monthly salary"> 2000 by "gender".
Method:
enter the formula in the target cell: =COUNTIFS(D3:D12,I3,G3:G12,">"&J3). Interpretation of
:
condition range and condition must appear in pairs.
6. Excel function: Datedif
function: calculates the difference between two dates according to the specified statistical method.
grammatical structure: =Datedif (start date, end date, statistical method). The common statistical methods of
are "Y", "M", and "D", which are "year", "month" and "day" respectively.
Purpose: calculates age based on date of birth.
Method:
enter the formula in the target cell: =DATEDIF(C3,TODAY(),"y"). Interpretation of
:
1, Datedif functions are hidden functions of the system, which do not exist in the documentation. You can use them directly after understanding the grammatical structure.
2, the function of Today() is to get the current date of the system, so the age of the above formula is automatically updated, and it is the latest age, not indefinite.
Seven, Excel function: Rank
function: returns a number in a group of numbers relative to other values of the size ranking, if there are multiple values the same, it returns the average ranking.
grammatical structure: =Rank (value or cell reference, data range, [sort method]). The
sorting method is divided into "0" and "1", namely "descending" and "ascending". If omitted, the default is "descending".
Purpose: sorts "monthly salary".
Method:
enter the formula in the target cell: =RANK(G3,G$3:G$12). Interpretation of
: When
omits the third parameter, the default sorting is descending.
8. Excel function: Sumproduct
function: returns the sum of the product of the corresponding array area.
syntax structure: =Sumproduct (array 1 or cell range 1...array N or cell range N).
When there is only one array or cell range, sum the element values.
Purpose: calculates the total sales of goods.
Method:
enter the formula in the target cell: =SUMPRODUCT(C3:C12,D3:D12). Interpretation of
: The calculation process of the
formula is: C3*D3+C12*D12, which is the sum of the product of the corresponding elements.
9. Excel function: Numberstring
function: capitalizes the value according to the specified format.
grammatical structure: =Numberstring (value or cell reference, format code). The format codes are divided into 1, 2, and 3.
Purpose: capitalizes "monthly salary".
Method:
enter the formula in the target cell: =NUMBERSTRING(C3,1), =NUMBERSTRING(C3,2), =NUMBERSTRING(C3,3).
10. Excel function: RMB
Function: converts the value into text characters in currency format.
grammatical structure: =RMB (value or cell reference, [number of decimal places]).
When "decimal places" is omitted, 2 decimal places are reserved by default.
Purpose: converts "monthly salary" into currency format.
Method:
enter the formula in the target cell: =RMB(C3), =RMB(C3,1).
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-