Excel function Learning Series one

The following functions have been tested

Key F9 can query the value of a logical expression in a function

1.vlookup (a2,sheet2! $b $ $h $200,5,0)
Description: The item that matches the A2 must be in the first column of the selection area, and the return column must be included in the selected zone

The domain. For example, the area contains only 2 columns, but it is wrong to return the value of the 3rd column.
Result: Returns a value for the specified column.

2.countif ($a $ $a, ">200") counts the number of items that are greater than 200 in A2 to A200. Within the region

The content should match the conditional operation. The range in a function can be multiple columns. Result: Returns a numeric value.

3.countifs (), multiple conditions, "and" relationships.
=countifs (h2:h1111, ">2000", e2:e1111, "= 25"), Result: Returns a numeric value.

4.if (logical expression, true-time value, false-time value), result: Returns one of two values.

5.mid (Specify string or cell, start position, length), result: Returns a substring in a string
=mid (j2,1,2) J2 is a cell
=mid ("Beijing Municipal People's Congress", BIS) near-return value is "NPC Representative"
=mid ("Beijing People's Congress representative", 4,2) the value of the near-return is "NPC"

6. Modulo mod (m,n) function, the value m takes n modulo, namely: MoD (m,n) =m/n remainder, such as mod (5,3) =2

, mod (6,3) = 0, if the negative number is modulo equal to the remainder and modulus and value, mod ( -7,3) =2 (i.e. -1+3=2).

=if (MOD (MID (b2,15,3), 2), "Male", "female")
The B2 cell is a * * number, which also applies to 15-digit * * * numbers
If it's just a 18-digit * * number, you can =if (MOD (b2,17,1), 2), "Male", "female")

7.indirect returns and displays the contents of the specified reference. Use the indirect function to refer to the names of other workbooks

, sheet name, and cell reference.

Indirect ("A1") if the A1 is quoted, the value stored in the A1 cell is taken, and no quotation marks are stored in the A1.

Address at this point, an error occurs if the address is not in the A1.
If the table name is a number, enclose the single quotation mark. such as: = ' 1 '! A1
=sum (INDIRECT ("1! E2:e20 ") can also be =sum (INDIRECT (" ' 1 '! E2:e20 "))

Instance:
Referencing data from another workbook (must open) on a worksheet
=sum (INDIRECT ("[m.xls]sheet1! B2:b4 ") M.xls the area of the table Sheet1 in the worksheet b2:b4

Values are summed, and the workbook name is enclosed in square brackets.

8.lookup (value, area to be checked, return value area)
Note: The values of the area to be checked must be sorted in ascending order, and if there is no exact match, the nearest

VALUES (smaller than the value of the check) specify the value of the column.
Returns the #a/v error message if the value is less than all the values of the region being checked

=lookup (1000,a1:a200,b1:b200), find the value 1000 in a1:a200, and return to column B in the same row

The value. If there is no 1000 in column A, find the row that is smaller than 1000 and closest to 1000, returning the row of column B

Value.

9.SUMIF (Region of condition value, condition, area to be fit when conditions are met)

Example: SUMIF (m2:m1111, "education", h2:h1111)
Sum m2 to m1111 (H2 to h1111) H column in the row where the value equals "education"

11.index (area, number of rows, number of columns)
Returns the value of the specified number of rows and columns in the range

Index (Zone 1, Region 2, Region 3, ..., number of rows, number of columns, specified number of regions)
Cases:
=index (B2:c11, E2:f11), 2, 2, 2) returns the value of the second column in the second row of the second range (that is,

E2:F11)

12.match (value, Region, Query method)
Returns the position of the column where the specified value is located (the first row of the range)
Query method: 1 or omit: Find the maximum value less than or equal to the lookup value, the area must be sorted in ascending order
0: Find equals specified value, that is, exact lookup
-1: Find the maximum value greater than or equal to the lookup value, and the area must be sorted in descending order

13.index () in conjunction with match ()
Cases:
=index (A1:a10,match (value or cell reference, b1:b10,0)
Match finds exactly where the value corresponds in B1:B10 (the first few rows), and index returns the value of column A in that row

14.find ("checked character", the cell where the string or string is located, starting from the first few characters)
Cases:
=find ("Car", b48,1)
=find ("Car", "I Am the Orient Team", 2) returned is 5
This function is used in conjunction with other functions

15.left (string, number) right (string, number)
Cases:
=left ("Chinase", 2) return CH
=left ("I am Chinese", 2) back I was

16.len (string or cell reference) returns the number of characters
=len ("I am Chinese") return 5
=len ("China") returns 5

17.datedif (date 1, date 2, parameter) returns the difference of two dates
Parameters:
Y returns the number of years between two dates
M returns the number of months between two dates
D returns the number of days between two dates
YM ignores number of years and returns the month difference
YD ignores years, returns the number of days
MD ignores number of months, returns the number of days

=datedif (DATEVALUE ("2001-01-01"), DATEVALUE ("2002-01-01"), "Y") return 1
=datedif (DATEVALUE ("2001-01-01"), DATEVALUE ("2002-01-01"), "M") return 12
=datedif (DATEVALUE ("2001-01-01"), DATEVALUE ("2002-01-01"), "D") return 365
=datedif (DATEVALUE ("2001-01-01"), DATEVALUE ("2002-01-01"), "YM") return 0
=datedif (DATEVALUE ("2001-01-01"), DATEVALUE ("2002-03-01"), "YM") return 2
=datedif (DATEVALUE ("2001-01-01"), DATEVALUE ("2002-03-09"), "yd") return 67

(31+28+8=67)
=datedif (DATEVALUE ("2001-01-01"), DATEVALUE ("2002-02-05"), "MD") return 4

18.trim ("string") returns a string with no trailing spaces
Example: =trim ("GGG") returns "GGG"

19.substitute (string or reference, "old string", "new string", replace the first occurrence of the old string)
Replace "old string" in a string or reference with a "new string"
Cases
=substitute (D58, "22", "99", 2) Replace the second occurrence of the string "22" in cell d58 with "99"
=substitute (D58, "22", "88", 3) Replace the third occurrence of the string "22" in cell d58 with "88"
If the number of occurrences is omitted, replace all

=substitute ("I Am the People", "", "") returned "I am the people", putting the empty Ge Qing aside

20.count () returns the number of numeric values
Usage One
=count (A2:H10) returns the number of numeric values in the A2 to H10 area, ignoring text and text-type numbers
Usage Two
=count (123,23,11,4) returns 4, which is the number of digits
Usage Three
=count (a2:h10,123,23,11,4) returns the number of numeric values in the A2 to H10 area plus 4
=count (a2:h10,123,23) returns the number of numeric values in the A2 to H10 area plus 2

21.counta ()
Statistics are not empty cells, as long as the cell has content, it will be counted, including some invisible characters.
If the cell has not been entered, it is ignored. Input spaces are also counted.

22.round (rounded number, number of decimal digits)
Cases
=round (12.34,1) returns 12.3
=round (12.34,2) returns 12.34
=round (12.34,0) returns 12
=round (12.34,3) returns 12.34

23.sumproduct (data 1, Data 2, ....) Returns the sum of the product of the corresponding column data
Cases
=sumproduct (A1:A5) return value =A1+A2+A3+A4+A5
=sumproduct (A1:A3,B1:B3) return value =A1*B1+A2*B2+A3*B3
Multiply columns if more than one area
It's an array.
=sumproduct ({#)}) return value =1+2+3=6
=sumproduct ({1,2},{3,4}) return value =1*3+2*4=3+8=11
Case of error
Regional inconsistencies
=sumproduct (A1:A3,B1:B4)
Inconsistent array dimensions
=sumproduct ({1,2},{3,4,5})
Value is not a numeric type
=sumproduct ({1,2},{3,a})
The area contains non-numeric cells
=sumproduct (A1:A5)

Excel function Learning Series one