Excel Skill Finishing Explanation

The common Office software Excel skill collation, the explanation, here gives the reader to look, gives everybody some hints, hoped in you in peacetime can use.

1, two columns of data to find the corresponding position of the same value

=match (b1,a:a,0)

2. The result of a known formula

Define Name =evaluate (sheet1! C1)

Formula for known results

Define name =get. CELL (6,sheet1! C1)

3, forced line wrapping

With Alt+enter

4, more than 15 digits input

This question asks too many people, also put up. First, the cell is set to text, and two, enter the number before entering '

5. If you hide column B, if you let it show up?

Select a to C column, right-click and unhide

Select a to C column, double-click to select any column wide line or change any column width

Move the mouse over the AC column, and drag when the mouse changes to a double vertical bar.

6excel in line and column interchange

Copy, paste selectively, select transfer, OK

How the 7excel is encrypted.

(1), save can be saved as >> the upper right corner of the "Tools" >> general >> settings

(2), Tools >> options >> Security

8. About COUNTIF

The COUNTIF function can have only one condition, such as greater than 90, =countif (a1:a10, ">=90")

A reduction is required between 80 and 90 for =countif (A1:A10, ">80")-countif (a1:a10, ">90")

9, according to the ID number of the date of birth

(1), =if (Len (A1) =18,date (mid a1,7,4), Mid (a1,11,2), Mid (a1,13,2), IF (Len (A1) =15, DATE (mid a1,7,2), Mid (a1,9,2), Mid ( a1,11,2)), "Wrong ID number")

(2), =text (MID a2,7,6+ (LEN (A2) =18) *2), "#-00-00") *1

10, want to SHEET2 in the full reference SHEET1 input data

Workgroup, hold down the SHIFT or CTRL key while selecting Sheet1, Sheet2.

11, a column does not enter a duplicate number

[Data]--[validation]--[custom]--[formula]

Input =countif (A:A,A1) =1

If you want to find the number that you entered repeatedly

Conditional Format Formula "=countif (A:A,A5) >1" format selected red

12, open a spreadsheet file directly when not open

Folder Options-file types found. XLS file, and in "advanced" to confirm whether there are parameters 1%, if not, please manually add

13, the implementation of Excel Pull-down menu

[Data]-[effectivity]-[sequence]

14, 10 column data aggregated into one column

=sum (OFFSET ($A, (ROW ()-2) *10+1,,10,1)

15, find the Data Formula Two (Basic lookup function for Vlookup,match)

(1), according to the ranks of the two conditions to find the corresponding results

=vlookup (H1,a1:e7,match (i1,a1:e1,0), FALSE)

(2), according to the matching of two columns of data to find the corresponding results (array formula)

=index (C1:c7,match (h1&i1,a1:a7&b1:b7,0))

16. How to hide 0 in a cell

Cell format custom 0;-0;; @ or Options View 0 value to tick. Oh, if you use the formula will see the situation.

17, multiple worksheet cell consolidation

=sheet1! d4+sheet2! d4+sheet3! D4, Better =sum (sheet1:sheet3! D4)

18. Get the name of the sheet

(1), definition name: Name

=get. DOCUMENT (88)

(2), definition name: Path

=get. DOCUMENT (2)

(3), in the A1 input =cell ("filename") to get the path-level file name

Enter the cell where you want to get the file name

=mid (A1,find ("*", Substitute (A1, "", "*", LEN (A1)-len (Substitute (A1, "", ""))) +1,len (A1))

(4), Custom function

Public Function name ()

Dim filename as String

filename = Activeworkbook.name

name = filename

End Function

19, how to obtain the maximum number of days one months

: "=day (DATE (2002,3,1)-1)" or "=day (b1-1)", B1 "2001-03-01

The sum of the items in the data area that contain a character, and what formula to use

=SUMIF (A:A, "*" & "a character" & "*", data area)

Last act text:

=offset ($b, MATCH (CHAR (65535), b:b)-1,)

Last act Number:

=offset ($b, MATCH (9.9999e+307,b:b)-1,)

Or: =lookup (2,1/(b1:b1000<> ""), b1:b1000)

Judges scoring, how to remove more than two of the highest points, two above the bottom of the list, the remaining staff to the average points? also shows the score removed.

Look at the Trimmean () function Help.

The score that was removed:

Maximum two: =large (data,)

Minimum two: =small (data,)

How easy is it to judge that the last one is the letter

Right (A1) *1

The wrong letter

=if (Isnumber (--right (a1,1)), "number", "Letter"

=if (Iserr (Right (A1) *1), "letter", "number"

How to set a cell so that it does not accept characters that contain spaces

Select Column A

Data--validity--customization--formula

=iserror (Find ("", A1))

Data--validity--customization--formula

=len (A1) =len (Trim (A1))

The original function is =a1+a4+a7+a10+a13+a16+a19+a22 ...

Now it's =sum (n (offset (A1, (Row (1:10)-1) *3))

Referencing data from other worksheets on one worksheet, but the referenced worksheet is not fixed and automatically selects the data in the corresponding worksheet based on the worksheet name I entered, how do I refer to the formula?

=indirect ("A1" & "!") & "E1") A1 for sheet name

Odd line Sum =sumproduct ((a1:a1000) *mod (Row (a1:a1000), 2)

Even line summation =sumproduct (a1:a1000) *not (MOD (Row (a1:a1000), 2))

View string words

=len (A1)

To find the number of non-empty cells

The data that the formula calculates, CountA cannot use (otherwise the null character also calculates in)

=countif ($E: $E 536, "? *")

The dynamic summation formula, which sums the cells from column a A1 to the row preceding the current row.

=sum (INDIRECT ("A1:a" &row ()-1))

20, the more useful Excel document Repair tool

Excelrecovery

21, Excel operation

8 Open 3 times, you can use this formula, enter =8^ in the cell (1/3)

22, the data distributed in the cell alignment

Text Format full-angle input

23, find the link in the worksheet

ctrl+~ or edit "link

24, how to make empty cells automatically fill 0

Select the area you want to change find empty replace "0

25. Convert the numbers in Word to excel

There are a variety of methods, select Copy to set the input cell as text "Paste Paste" value

Select table Convert to text paste by column to set the column options to text

Save As text file Open text file in Excel to set up the Import Text dialog box