【 Weak current College 】 controlled common 25 operating skills Excel applies (2)

14, 10 column data from a column

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

15, finding data, formula two (basic find functions for VLOOKUP, MATCH)
(1), pursuant to the agreement between the ranks of the two criteria to find the corresponding results

=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)

(2), based on the combination of two columns of data to find the corresponding results (for array formulas)

=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0))

16, how hidden cell 0

Cell formatting custom 0-0;; @ Or options "view of zero values to check. Oh, let's say you use a formula depends on the circumstances.

17, multiple task table calculation of cell merger

= Sheet1! D4 + Sheet2! D4 + D4 Sheet3!, better = SUM (Sheet1: Sheet3! D4) 18, obtained the title of the task tables

(1), define the title: Name

=GET.DOCUMENT(88)

(2), defines the title: Path

=GET.DOCUMENT(2)

(3), in CELL A1, enter = ("filename") by way of class file name

The need to get the file name of the cell entry

=MID(A1,FIND("*",SUBSTITUTE(A1,"","*",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))))+1,LEN(A1))

(4), the custom function

PublicFunctionname()

DimfilenameAsString

filename=ActiveWorkbook.name

name=filename

EndFunction

19. How can I get a maximum number of days of the month

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

Data region contains a character in the aggregate, the what formula

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

At the beginning of a text:

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

Number: at the beginning of a behavior

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

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

The judges scoring, how to get rid of the two above the highest scores, the two above the base score, and the remaining staff of the average score was also showing buildings out of score.

See trimmean () function.

Be rid of the score:

Maximum two: = large (data, {1; 2})
Minimum two: = small (data, {1; 2})

How to easily distinguish one letter at the beginning of the

right(a1)*1

Error-letters

= IF (ISNUMBER (--RIGHT (A1, 1)), "digital", "letter")

= IF (ISERR (RIGHT (A1) * 1), "letter", "number")

How to configure the cell, so that it does not accept spaces in character

Select A column

Data — validity — custom-formula

=iserror(find("",a1))

Data--validity--custom--formula

=len(a1)=len(trim(a1))

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

Today is = sum (n (offset (a1, (row (1: 10)-1) * 3,)))

Network Institute (http://www.hackhome.com) prompted the masses in a task table invoke another task data in the table, however, be invoked in a task table is not fixed, according to the tasks I enter the table name automatically pick the appropriate task data in the table, I ask how the formula used in?

=INDIRECT("A1"&"!" &Amp; "E1") for the task table A1

Odd row sum = SUMPRODUCT ((A1: A1000) * MOD (ROW (A1: A1000), 2))

Even row sum = SUMPRODUCT ((A1: A1000) * NOT (MOD (ROW (A1: A1000), 2)))

Check the string of words

=LEN(A1)

For non-empty cell number

The data calculated by the formula, you cannot use the COUNTA (otherwise null characters are calculated in)

=COUNTIF($E$3:$E$65536,"?*")

Static summation formula, since A column cell a1 to now line first surface row cells sum.

= SUM (INDIRECT ("A1: A" & ROW ()-1)) 20, reflecting a good use of Excel document correction tool

ExcelRecovery

21, EXCEL squqre

Will open the 3rd party 8, you can use this formula in the cell input = 8 ^ (1/3)

22, cell data distributed alignment

Text format, full-width input

23, find the link in the task table

Ctrl + ~ or edit "link

24. How can I get a blank cell automatically fill to 0

Select the desired correction of regional "find" empty "replace", 0

25, put the Word in the digital conversion to Excel
There are several ways to select the "copy" to configure the input cell for the text "select Paste on the value of

Select the "convert a table to text" Paste "disaggregated" disaggregated options configured as text

Save as a text file, Excel opened text file to import text dialog box to perform the corresponding configuration