What are the common commands in excel?

1, where two columns of data find the same value =MATCH(B 1, A:A, 0).

2. The result of known formula

Definition name =EVALUATE(Sheet 1! Known results of C 1) formula

Definition name =GET. Cell (6, Sheet 1! C 1)

3. use Alt+Enter to force a line break.

4, 15 or more digital input.

There are too many people asking this question. Put it away. First, set the cell as text; Secondly, enter numbers before entering them.

5. If column B is hidden, what if it is displayed? Select columns A to C, and then right-click to unhide.

Select column A to column C, double-click to select any column width line or change any column width to move the mouse between AC columns, and drag when the mouse becomes a double vertical line.

6. Row-column interchange in 6.EXCEL

Copy, paste selectively, transpose selectively and confirm.

7. How is 7.Excel encrypted?

(1). You can save it as >; & gt "Tools" > > General > > Create in the upper right corner.

(2) Tools > > Options > > Security

8. About COUNTIF

The COUNTIF function can only have one condition, such as = countif (a 1: a 10, "> =90").

Between 80 and 90, you need to subtract, that is,

=COUNTIF(A 1:A 10，" & gt80 ")-COUNTIF(a 1:a 10，" & gt90")

9. Extract the date of birth according to the ID number (1)

=IF(LEN(A 1)= 18，DATE(MID(A 1，7，4)，MID(A 1， 1 1，2)，MID(A 1， 13，2))，IF(LEN(A 1)= 15，Date (mid (a 1，7，2)，

(2)、

=TEXT(MID(A2，7，6+(LEN(A2)= 18)*2)，" #-00-00")* 1

10. If you want to fully refer to the data workgroup entered by SHEET 1 in SHEET2, please hold down the Shift or Ctrl key and select Sheet 1, Sheet 21,and do not enter duplicate numbers in one column.

1 1, do not enter duplicate numbers in one column.

[Data]-[Validity]-[User-defined]-[Formula] Input =COUNTIF(A:A, A 1)= 1 If you want to find the repeatedly entered numbers,

The format of conditional format formula =COUNTIF(A:A, A5)> 1 is red 12. When you open the spreadsheet file directly, you cannot open it. Folder option-XLS file in file type, and confirm whether there is a parameter of 1% in advanced. If not, please add it manually.

13, excel drop-down menu to achieve [data]-[validity]-[sequence] 14, 10 column data add up to one column.

=SUM(OFFSET($A$ 1，(ROW()-2)* 10+ 1，， 10， 1))

15, find two data formulas (the basic search function is VLOOKUP, MATCH) (1), and find the corresponding results according to the two conditions of row and column.

=VLOOKUP(H 1, A 1: E7, MATCH(I 1, A 1: E 1, 0), FALSE) (2), find the corresponding result according to the data matching two columns (as an array formula. I 1，a 1:A7 & amp； B 1:B7，0)) 16。 How to hide 0 in a cell?

Cell format customization 0; -0; ; @ or option view "Zero to Scale. Hehe, it depends on the formula.

17, cell consolidation calculation of multiple worksheets =Sheet 1! D4+Sheet2！ D4+ sheetlet 3! D4，better =SUM(Sheet 1:Sheet3！ D4)

18. Get the worksheet name (1) and define the name: Name =GET. Document (88) (2) and definition name: Path =GET. Document (2).

(3) Enter =CELL("filename ") in A 1 to get the path-level file.

name

Enter in the cell where you want to get the file name.

=MID(A 1，FIND("* "，SUBSTITUTE(A 1)，" * "，LEN(A 1)-LEN(SUBSTITUTE(a 1，"，" ")))+ 1，LEN(a 1))

(4) User-defined function public function name () dim file name is a string.

filename = active workbook . name name = filename End Function

19. How to get the maximum number of days in a month?

: "= day (date (2002, 1)- 1)" or "= day (b 1- 1)", and b 1 is "200/kloc-0.

When the data area contains the sum of items of a certain character = sumif (a: a, "*"&; "Someone"&; "*", data area) the last line of text:

= offset ($ b $ 1, match (char (65535), b: b)- 1,) Last line number:

= offset ($ b $ 1, match (9.9999e+307, b: b)- 1,) or = lookup (2,1/(b1:b1). ""), b 1:b 1000) In judgment, how to get rid of more than two highest scores and more than two lowest scores and find the average score of the remaining personnel? At the same time, the removed score is displayed.

Check the trimmean () function help. Scores removed:

At most two: =large(data, {1; 2}) Least two: = small (data, {1; 2}) How to simply judge that the last digit is the letter right (a 1)* 1?

= if (is number (-right (a 1, 1)), numbers and letters) = if (iserr (right (a1) *1), letters and numbers.

data-validity-customization-formula = iserror(find("，a 1))data-validity-customization-formula = len(a 1)= len(trim(a 1))

The original function was = a1+a4+a7+a10+a13+a16+a19+a22 ... Now it is = sum (n (offset (a/a) )) In one worksheet, data from other worksheets are referenced, but the referenced worksheet is not fixed. According to the worksheet name I entered, the data in the corresponding worksheet is automatically selected. How to quote in the formula?

filename = active workbook . name name = filename End Function

19. How to get the maximum number of days in a month?

: "= day (date (2002, 1)- 1)" or "= day (b 1- 1)", and b 1 is "200/kloc-0.

When the data area contains the sum of items of a certain character = sumif (a: a, "*"&; "Someone"&; "*", data area) the last line of text:

= offset ($ b $ 1, match (char (65535), b: b)- 1,) Last line number:

= offset ($ b $ 1, match (9.9999e+307, b: b)- 1,) or = lookup (2,1/(b1:b1). ""), b 1:b 1000) In judgment, how to get rid of more than two highest scores and more than two lowest scores and find the average score of the remaining personnel? At the same time, the removed score is displayed.

Check the trimmean () function help. Scores removed:

At most two: =large(data, {1; 2}) Least two: = small (data, {1; 2}) How to simply judge that the last digit is the letter right (a 1)* 1?

= if (is number (-right (a 1, 1)), numbers and letters) = if (iserr (right (a1) *1), letters and numbers.

data-validity-customization-formula = iserror(find("，a 1))data-validity-customization-formula = len(a 1)= len(trim(a 1))

The original function was = a1+a4+a7+a10+a13+a16+a19+a22 ... Now it is = sum (n (offset (a/a) )) In one worksheet, data from other worksheets are referenced, but the referenced worksheet is not fixed. According to the worksheet name I entered, the data in the corresponding worksheet is automatically selected. How to quote in the formula?

filename = active workbook . name name = filename End Function

19. How to get the maximum number of days in a month?

: "= day (date (2002, 1)- 1)" or "= day (b 1- 1)", and b 1 is "200/kloc-0.

When the data area contains the sum of items of a certain character = sumif (a: a, "*"&; "Someone"&; "*", data area) the last line of text:

= offset ($ b $ 1, match (char (65535), b: b)- 1,) Last line number:

= offset ($ b $ 1, match (9.9999e+307, b: b)- 1,) or = lookup (2,1/(b1:b1). ""), b 1:b 1000) In judgment, how to get rid of more than two highest scores and more than two lowest scores and find the average score of the remaining personnel? At the same time, the removed score is displayed.

Check the trimmean () function help. Scores removed:

At most two: =large(data, {1; 2}) Least two: = small (data, {1; 2}) How to simply judge that the last digit is the letter right (a 1)* 1?

= if (is number (-right (a 1, 1)), numbers and letters) = if (iserr (right (a1) *1), letters and numbers.

data-validity-customization-formula = iserror(find("，a 1))data-validity-customization-formula = len(a 1)= len(trim(a 1))

The original function was = a1+a4+a7+a10+a13+a16+a19+a22 ... Now it is = sum (n (offset (a/a) )) In one worksheet, data from other worksheets are referenced, but the referenced worksheet is not fixed. According to the worksheet name I entered, the data in the corresponding worksheet is automatically selected. How to quote in the formula?