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.
6, Excel in the row and column interchange
Copy, paste selectively, select transfer, OK
7, how Excel 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 ($, (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 $1,match (CHAR (65535), b:b)-1,)
Last act Number:
=offset ($b $1,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,{1;2})
Minimum two: =small (data,{1;2})
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 $65536, "? *")
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
Note : More wonderful tutorials Please pay attention to the triple computer tutorial section, triple Computer office group: 189034526 welcome you to join