# excel function formula and techniques used to collect 2

2011-09-27  来源：本站原创  分类：Industry  人气：175

[Calculated age and length of service]

Calculated according to age, date of birth

= DATEDIF (A1, TODAY (), "y")

= DATEDIF (A1, TODAY (), "y") & "years old"

= DATEDIF (A1, NOW (), "y")

According to projections Zodiac Birth

12 Lunar New Year Chinese people, is what can be derived. That with the birth of the year divided by 12, then in addition to countless number of control over the following: 0 → Monkey, 1 → chicken, 2 → dog, 3 → Pig, 4 → rat, 5 → cattle, 6 → Hu, 7 → rabbits, 8 → Long, 9 → snakes, 10 → Ma, 11 → sheep, for example: XXX was born in 1921, that is 1921 divided by 12, business yielding 160, a remainder of 1, were above that I was number 1 corresponds to the Lunar New Year chicken, XXX in the case of the chicken.

= MID ("Monkey Pig Rat Ox Tiger Rabbit Jigou snakes horses sheep", MOD (YEAR (A2), 12) +1,1) (2007)

How to find a person to a specified date in years?

= DATEDIF (start date, end date, "Y")

Formula for calculating the retirement age from

= IF (E2 ="","", IF (E2> = V2, "retired", "retired from there" & DATEDIF (TODAY (), DATE (YEAR (U2) + (V2), MONTH (U2) , DAY (U2)), "Y") & "years" & DATEDIF (TODAY (), DATE (YEAR (U2) + (V2), MONTH (U2), DAY (U2)), "YM") & "a On "& DATEDIF (TODAY (), DATE (YEAR (U2) + (V2), MONTH (U2), DAY (U2))," Md ") &" days "))

E2 which is the age (the formula used to generate ID number);

V2 is the statutory retirement age (male 60, female 50) formula: = IF (D2 ="","", IF (D2 = "M", 60,50))

D2 is male or female (ID number of the formula used to generate); U2 as date of birth (the formula used to generate ID number).

Length of service requirements

= DATEDIF (B2, TODAY (), "y")

= DATEDIF (B2, TODAY (), "ym")

= DATEDIF (B2, TODAY (), "md")

= DATEDIF (B2, TODAY (), "y") & "years" & DATEDIF (B2, TODAY (), "ym") & "months" & DATEDIF (B2, TODAY (), "md") & "days"

Calculating length of service

= DATEDIF (C6, C8, "y") seeking the number of years between two dates

= DATEDIF (C6, C8, "ym") seeking to remove two full years between the number of months remaining

= DATEDIF (C6, C8, "m") seeking two total number of months between the date of

If you only need to calculate the anniversary, you can use = datedif ("1978-8", "2006-5", "Y")

Age and length of service calculation

A date of birth to the age requirements?

Length of service time to find a job? (The results obtained many years another few months, such as: 0303 form, that is 3 years and 3 months).

a1 is the date of birth or work hours:

= Datedif (a1, today (), "y")

= Text (datedif (a1, today (), "y"), "00") & text (datedif (a1, today (), "m"), "00")

If [B2] = 1964-9-1 then:

= TEXT (DATEDIF (B2, TODAY (), "y"), "00") & TEXT (MOD (DATEDIF (B2, TODAY (), "m"), 12), "00") 'Show 4009

= TEXT (DATEDIF (B2, TODAY (), "y"), "00 Year ") & TEXT (MOD (DATEDIF (B2, TODAY (), "m"), 12), "00 months") 'display 40 years 09

If you can not find DATEDIF function, it can not DATEDIF function,

If [B2] = 1964-9-1 then:

= TEXT (RIGHT (YEAR (NOW ()-B2), 2), "00") & TEXT (MOD (MONTH (NOW ()-B2) -1,12), "00") 'Show 4009

= TEXT (RIGHT (YEAR (NOW ()-B2), 2) & "years" & MOD (MONTH (NOW ()-B2) -1,12) & "months ","") 'display 40 years 09 month

Automatically calculate the length of service date format (yyyy.mm.dd)

Can use: (yyyy.mm.dd) this format to calculate how long it worked for ~?

Previously used such a (= TEXT (RIGHT (YEAR (NOW ()-A1), 2) & "years" & MOD (MONTH (NOW ()-A1) -1,12) & "months ","")) .

However, this method can only be used: (yyyy-mm-dd) date format in order to achieve this!

You may wish to "." Replaced "-" not on line yet, to say the latter is a standard date format,

= TEXT (RIGHT (YEAR (NOW ()-SUBSTITUTE (A1 ,".","-")), 2) & "years" & MOD (MONTH (NOW ()-SUBSTITUTE (A1 ,".","-" )) -1,12) & "months ","")

Mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm

[Time and date of application]

Automatically displays the current date formula

= YEAR (NOW ()) Current year

= MONTH (NOW ()) Current month

= DAY ((NOW ())) Current Day

How to automatically fill in the cell the current date

Ctrl +;

How to determine whether a particular day Sunday

= WEEKDAY (A2, 2)

= TEXT (A1, "aaaa")

= MOD (A1, 7) <2

A day of the week

For example February 9, 2007, in a cell show of the week.

= TEXT (A1, "aaa") (e)

= TEXT (A1, "aaaa") (Friday)

= TEXT (A1, "ddd") (Fri)

= TEXT (A1, "dddd") (Friday)

What function can display the current week

Such as: Tuesday 10:41:56

= TEXT (NOW (), "aaaa hh: mm: ss")

Find the number of days this month

A1 set number of days this month, seeking to 2006-8-4

A1 = DAY (DATE (YEAR (A1), MONTH (A1) +1,0))

There are more simple formula: = DAY (EOMONTH (NOW (), 0)) need to load the Analysis ToolPak.

Number of days in current month: = DATE (YEAR (TODAY ()), MONTH (TODAY ()) +1,1)-DATE (YEAR (TODAY ()), MONTH (TODAY ()), 1)

Calculated with a formula to remove the month on Saturday, the number of days other than Sunday

= SUMPRODUCT (- (MOD (ROW (INDIRECT (DATE (YEAR (NOW ()), MONTH (NOW ()), 1 )&":"& DATE (YEAR (NOW ()), MONTH (NOW ()) + 1,0))), 7)> 1))

Show yesterday's date

Cell shows a day yesterday's date, but excluding weekends.

For example, today is July 3, then it shows July 2, July 9, if it is on display July 6.

= IF (TEXT (TODAY (), "AAA") = "one", TODAY () -3, IF (TEXT (TODAY (), "AAA") = "Day", TODAY () -2, TODAY () -1))

= IF (TEXT (TODAY (), "AAA") = "one", TODAY () -3, TODAY () -1)

Date of taking

How to set up a formula to A1 on (date) pushed back five years, become 2011-7-15

= DATE (YEAR (A1) +5, MONTH (A1), DAY (A1))

= EDATE (A1, 12 * 5)

How to date, the middle and late distinguished

= LOOKUP (DAY (A1), {0,11,21,31}, {"early", "mid", "late", "late"})

How to get the maximum number of days a month

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

Date format conversion formula

Will be "01/12/2005" into "20050112" format

= RIGHT (A1, 4) & MID (A1, 4,2) & LEFT (A1, 2)

= YEAR (\$ A2) & TEXT (MONTH (\$ A2), "00") & TEXT (DAY (\$ A2), "00") that do not set data validation formula, but to set the cell formatting.

You can also use the following two methods:

1, first converted into text, then use the character handling functions.

2, [data] - [Columns] [Date] - [MDY]

The "September 2005" into "200 509" format

First with the formula: = text (a1, "yyyymm") +0 then a regular cell formatting.

Will "2005-8-6" format convert "20,050,806" format

Using the formula: = TEXT (A1, "YYYYMMDD")

On the contrary, the 20,050,806 to date 2005-8-6 format, available formulas:

= DATE (LEFT (A1, 4), MID (A1, 5,2), RIGHT (A1, 2))

The other four kinds of formulas:

= Text (a1, "0000-00-00") shows :2005 -08-06

= - TEXT (A1, "# -00-00"), set the cell format to date :2005 -8-6

= TEXT (20050806, "0000-00-00") * 1, set the date-type cells display :2005 -8-6

= VALUE (LEFT (A1, 4 )&"-"& MID (A1, 5,2 )&"-"& RIGHT (A1, 2)) shows :2005 -8-6

The "20060501" to "2006-05-01" format

= DATE (LEFT (A2, 4), MID (A2, 5,2), RIGHT (A2, 2))

The "199 306" to "1993-6"

Formula 1: = LEFT (A3, 4 )&"-"& RIGHT (A3, 2) * 1

Formula 2: =-- TEXT (A3 * 100 +1, "# -00-00") formula 2 to set the Format Cells, Custom: em

Formula 3: = TEXT (TEXT (A3 & "01", "0000-00-00"), "em")

Convert the 198,405 1984.05

First, find the -1984, -1984 replaces.

Second, if all the years, I personally recommend,

1, to take supplementary = mid (xxxxxx, 1,4) & "." & Right (xxxxxx, 2)

2, check it out, with disaggregated data. Then ... ... ... ... ...

Third, the Format Cells / Number / Custom, type the following input: ####"."##

The text "2004.01.02" converted to date format: 2004-1-2

= DATE (MID (A1, 1,4), MID (A1, 6,2), MID (A1, 9,2))

Will 2005-8-6 converted to August 6, 2005 Format

= TEXT (A1, "yyyy" "Year" "m" "May" "d" "Day "";@")

How to convert like 22 22? Turn into a month day year

The formula is: = date (year (now ()), month (now ()), 22)

The "May 2006" into "May 2006"

The formula is: = TEXT (A8, "yyyy" "Year" "mm" "May "";@")

Can also be handled this way: select the cell, set the cell formula - Digital - Custom, will yyyy "in" m "month" to: yyyy "in" mm "month", you can. But this method to print out display: 2006 / 5 /

The "June 12, 1968" to "1968/6/12" format

= YEAR (A1 )&"/"& MONTH (A1 )&"/"& DAY (A1) shows: 1968/6/12

= TEXT (A1, "yyyy / mm / dd") shows: 1968/06/12

The "June 12, 1968" to "1968-6-12" format

= YEAR (A1 )&"-"& MONTH (A1 )&"-"& DAY (A1) shows :1968 -6-12

= TEXT (A1, "yyyy-mm-dd") Show :1968 -06-12

1993-12-28 The date format will be converted to December 1993

= CONCATENATE (YEAR (A1), "years", MONTH (A1), "month")

= YEAR (A1) & "years" & MONTH (A1) & "months"

You can also customize the format of [\$ -404] e "in" m "month"

The "1978-5-2" contains a date into month, day, "197,805" years only format

= Year (A1) & text (month (A1), "00")

To "99.08.15" format convert "1999.08.15" how do

Selected columns, select the Data menu breakdown, breakdown of the process "format" choose "Date YMD", the end.

Format to maintain 2005/8/6

When you enter the system automatically become 2005-8-6 2005/8/6 to maintain 2005/8/6 format, you can use to force the text (preceded by '), or use the formula = TEXT (A1, "YYYY / MM / DD "). You can also use another formula: = IF (ISERROR (TEXT (A1, "yyyy / mm / dd")), TEXT (A1, "0000! / 00! / 00"), TEXT (A1, "yyyy / mm / dd "))

The "Second 0 0 " to "2003-12-25" format,

1, you can use an array formula to Chinese dates into the date line number {= 14610 + MATCH (SUBSTITUTE (A3, "Yuan" and "one"), TEXT (ROW (\$ 14611: \$ 55153), "[DBNum1] yyyy years m months d day "), 0)}

The formula is slow.

2, improved formula, the speed is much faster and more:

{= DATE (1899 + MATCH (LEFT (A7, 4), TEXT (ROW (\$ 1900: \$ 2100), "[DBNum1] 0000"), 0), MONTH (MATCH (SUBSTITUTE (MID (A7, 6,7), "Yuan" and "one"), TEXT (ROW (\$ 1: \$ 366), "[DBNum1] m on d day"), 0)), DAY (MATCH (SUBSTITUTE (MID (A7, 6,7), "Yuan "and" one "), TEXT (ROW (\$ 1: \$ 366)," [DBNum1] m on d day "), 0)))}

To set the date format in 1900.

Date format conversion

If column A is the month number 8, B column is the date the number is 18, how in the C column shows the "August 18"

= A1 & "months" & B1 & "Day"

On the contrary, to column C of the "August 18", respectively, directly to the D, E out, month and date display,

Months were = LEFT (C5, FIND ("month", C5) -1)

Date number = MID (C5, FIND ("month", C5) +1, FIND ("Day", C5)-FIND ("month", C5) -1)

Also were used to the formula:

= Month (- c5)

= Day (- c5)

Date format conversion

Enter the date :04 -07-26 with another cell of the "001" merged out is: 040,726,001.

= TEXT (A1, "YYMMDD") & "001"

To automatically obtain the "Date of preparation: XXXX years X months X days"

In the cell, type = "Date of preparation:" & TEXT (TODAY (), "yyyy d day in month m")

Mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm

[Filter] ranking and sorting

A summary way with 11 SUBTOTAL function

= SUBTOTAL (9, \$ B \$ 2: B2)

In sum there is unexpected data filtering function, 11 function as follows: 1, averaging, 2, find the count of three, find the count (automatic screening sequence) 4, the maximum, 5 minimum, 6 , product demand, 7, find the overall standard deviation, 8, find the standard deviation, 9, Sum, 10, seeking the variance, 11, seeking the overall variance.

Automatic sorting

= SUBTOTAL (3, \$ B \$ 2: B2) * 1

= IF (A2 <> A1, 1, N (C1) +1)

Sort by parity

I would like to ask how to order and then even at odd sort order

= IF (MOD (A1, 2), 0,1)

= IF (ROW ()> 50, (ROW () * 2) -100, (ROW () * 2) -1)

= ROW () * 2-1 - (ROW ()> 50) * 99

Automatically generated number

For example, in the second column, enter the contents of the carriage return after the first column of the next line automatically generated serial number.

= IF (B2 <>"", A2 +1, "")

How to automatically mark the size of the figures in column A sort?

= RANK (A1, \$ A \$ 1: \$ A \$ 5)

= RANK (A1, A: A)

How to set up automatic sorting

A column is automatically arranged into small to large

B = SMALL (A \$ 2: A \$ 28, ROW (1:1))

A column in descending order automatically becomes

B = LARGE (A \$ 2: A \$ 28, ROW (1:1))

Repeat the sequence data is the only qualifying

The emergence of the total number of data you want ({1,2,2,3,4,4,5} the emergence of a total of 5 data)?

Answer: do not plug out, do not need a lot of functions on the line. = RANK (B3, B \$ 3: B \$ 12) + COUNTIF (B \$ 3: B3, B3) -1

Sort by number of characters

Production of the song list, used by the number of characters arranged in categories, but not directly by the word EXCEL and sorting. Need to calculate the number of words per song, and then sort.

Such as A, B column are "singers" and "song" in the C1 type "words", enter the formula in C2:

= LEN (B2) under the drag, click C2, click on the toolbar of the "ascending order" to delete the C column.

Sort of mixed content of letters and numbers

Daily use, there is often a form of letters and numbers mixed data, such data is sorted, it is usually more letters the size of the first, and then compare the size of figures, but EXCEL is based on the characters to sort of compare-by-bit the following table: A7 ranked No. 5, but not the first one. Sort results unsatisfactory.

 A 1 A122 2 A29 3 A317 4 A43 5 A7 6 B20 7 B3 8 C144 9 C5 10 C33
 A B 1 A7 A007 2 A29 A029 3 A43 A043 4 A122 A122 5 A317 A317 6 B3 B003 7 B20 B020 8 C5 C005 9 C33 C033 10 C144 C144

If you want to change the sorting rules of EXCEL, the data needs to make some changes.

In B1 enter the formula: LEFT (A1, 1) & RIGHT ("000" & RIGHT (A1, LEN (A1) -1), 3) under the drag

Click the B2, click on the toolbar of the "ascending order" button.

Random sort

Such as A, B column are "singers" and "song" in the C1 type "order", in C2 enter the formula:

= RAND (), lower drag, click C2, click on the toolbar of the "descending" to the list of songs in random order.

Sort of problem

I want this sort: 2001-2003

2004-2006

2007-2009

2010-2012;

In fact, not a sort of data should be populated with data.

Enter the formula = LEFT (E3, 4) +3 &"-"& RIGHT (E3, 4) +3 to.

How can I make a few columns automatically addend

What can be done to make the series automatically addend

A A0001

B B0001

A A0002

C C0001

A A0003

B B0002

C C0002

Formula = A1 & "000" & COUNTIF (A \$ 1: A1, A1) dragged down

= TEXT (COUNTIF (A \$ 1: A1, A1 ),"!"& A1 & "0000") or number more than 900 on error.

A scheduling problem

A spreadsheet format is 101, 102 ... 9,990,101 ... 999,10101,10102 ... 99901,1010101,1020201, how is it arranged 101,10101,1010101,102,10201,1020101, .. . 999,99901,9990101 form.

I added a letter before the number, such as "d" & "number", then you can sort them according to your needs ranked. And finally the letter "d" to remove.

Number of automatic sorting, inserting after the change?

1 Zhao General Manager

2 Chao Assistant Manager

3 Cho three deputy

4 Zhao Si Technician

5 Zhao five

6 Zhao six employees

As a table, how to achieve this when I put the whole Zhao six lines (line 6) into the above table, A-list of the serial number of the same? Final results are as follows:

1 Zhao General Manager

2 Chao Assistant Manager

3 Zhao six employees

4 Zhao three deputy

5 Zhao Si Technician

6 Zhao five

Cell A1 enter the formula = row (), pull down, and then insert.

= SUBTOTAL (3, \$ B \$ 2: \$ B2)

In A1 enter the formula: "= if (b1 ="","", counta (\$ b \$ 1: b1)" after the drop-down copy to each row in column A can be ("" do not have to enter)

According to the law of the repeated column names automatically generated serial number

No. Name

Joe Smith 1

Joe Smith 1

John Doe 2

John Doe 2

Zhao F 3

Zhao F 3

Zhao F 3

King 6:4

King 6:4

= (A1 <> A2) + N (B1)

= IF (A3 = A2, B2, B2 +1)

Name sorted:

B2 = SUMPRODUCT (1/COUNTIF (A \$ 2: A2, A \$ 2: A2))

Name unsorted:

B2 = IF (COUNTIF (A \$ 2: A2, A2)> 1, VLOOKUP (A2, A: B, 2,0), SUMPRODUCT (1/COUNTIF (A \$ 2: A2, A \$ 2: A2)))

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Ranking function

With ranking function to rank results, it is very easy for use.

= IF (ISERR (RANK (M3, M: M )),"", RANK (M3, M: M))

A column is the result, B is the ranking list

= SUMPRODUCT ((A \$ 1: A \$ 9> A1) / COUNTIF (A \$ 1: A \$ 9, A \$ 1: A \$ 9)) +1

Automatic ranking formula

= RANK (C3, \$ C \$ 3: \$ C \$ 12)

= RANK (A2, \$ A \$ 2: \$ A \$ 11,0)

= RANK (C2, \$ C \$ 2: \$ C \$ 65) + COUNTIF (\$ C \$ 2: C2, C2) -1

Percentage ranking formula written as:

= PERCENTRANK (\$ C \$ 3: \$ C \$ 12, C3)

And the average score ranking

= AVERAGE (B2: E2)

= RANK (F2, \$ F \$ 2: \$ F \$ 65536)

Seeking top ranking

Statistical results were encountered in seeking a class ranking and grade overall ranking problem, I do not know what the formula should be used to achieve.

Class rank:

= SUMPRODUCT ((BJ = A2) * (ZF> E2)) +1

= RANK (E2, ZF) under the drag formula.

Ranking

According to the size of the total score, only the names sorted, the results in descending order

= INDEX (A \$ 2: A \$ 6, RANK (D2, D \$ 2: D \$ 6))

According to the size of the total score, only the names sorted, ascending order

= INDEX (A \$ 2: A \$ 6, RANK (D2, D \$ 2: D \$ 6,1))

Ranked according to score for general

= RANK (A2, \$ A \$ 2: \$ A \$ 12)

= RANK (A2, A \$ 2: A \$ 12) + COUNTIF (A \$ 2: A2, A2) -1

= SUMPRODUCT (1 * (\$ E \$ 3: \$ E \$ 12> = E3))

= RANK (K3, \$ K \$ 3: \$ K \$ 26)

= RANK (A2, A \$ 2: A \$ 12)

= SUM ((A \$ 2: A \$ 12> = A2) / COUNTIF (A \$ 2: A \$ 12, A \$ 2: A \$ 12))

= COUNTIF (\$ K \$ 3: \$ K \$ 26 ,">"& K3) +1

= INDEX (\$ A \$ 2: \$ A \$ 7, MATCH (LARGE (\$ C \$ 2: \$ C \$ 7, ROW (A1)), \$ C \$ 2: \$ C \$ 7,0), 1)

= SUMPRODUCT ((\$ A \$ 2: \$ A \$ 12> A2) / COUNTIF (\$ A \$ 2: \$ A \$ 12, \$ A \$ 2: \$ A \$ 12 &""))+ 1

= RANK (D2, OFFSET (\$ A \$ 1, MATCH (\$ A2, \$ A: \$ A, 0) -1,3, COUNTIF (\$ A: \$ A, \$ A2), 1))

For the same general ranking scores, ranked according to the order does not repeat

= RANK (K32, \$ K \$ 32: \$ K \$ 55) + COUNTIF (\$ K \$ 32: \$ K32, K32) -1

= COUNTIF (\$ K \$ 32: K32, K32) -1 + COUNTIF (\$ K \$ 3: \$ K \$ 26 ,">"& K32) +1

= SUMPRODUCT (1 * ((\$ E \$ 3: \$ E \$ 12 + ROW (\$ E \$ 3: \$ E \$ 12) / 100> = (\$ E3 + ROW (E3) / 100))))

= RANK (E3, \$ E \$ 3: \$ E \$ 12) + COUNTIF (\$ E \$ 3: E3, E3) -1

= SUMPRODUCT (1 * ((\$ E \$ 3: \$ E \$ 12 + \$ B \$ 3: \$ B \$ 12/100)> = (E3 + B3/100)))

Scores assessed by ranking according to performance ranking

= RANK (\$ E3, \$ E \$ 3: \$ E \$ 22) built-in way of ranking

= SUMPRODUCT (1 * (\$ E \$ 3: \$ E \$ 12> = E3)) ranked the usual way

{= RANK (E3, \$ E \$ 3: \$ E \$ 22) + SUM (IF (\$ E \$ 3: \$ E \$ 22> E3, 1/COUNTIF (\$ E \$ 3: \$ E \$ 22, \$ E \$ 3: \$ E \$ 22), 0))-COUNTIF (\$ E \$ 3: \$ E \$ 22 ,">"& E3)} General way of ranking

= RANK (E3, \$ E \$ 3: \$ E \$ 12) + COUNTIF (\$ E \$ 3: E3, E3) -1 not duplicate ranking

= SUMPRODUCT (1 * ((\$ E \$ 3: \$ E \$ 12 + ROW (\$ E \$ 3: \$ E \$ 12) / 100> = (\$ E3 + ROW (E3) / 100))))

= SUMPRODUCT (1 * ((\$ E \$ 3: \$ E \$ 12 + \$ B \$ 3: \$ B \$ 12/100)> = (E3 + B3/100))) does not repeat the ranking

= SUMPRODUCT (1 * ((\$ E \$ 3: \$ E \$ 12 + \$ B \$ 3: \$ B \$ 12/100 + \$ C \$ 3: \$ C \$ 12/10000)> = (E3 + B3/100 + C3/10000))) do not repeat the position

= RANK (\$ E3, \$ E \$ 3: \$ E \$ 22,1) sort down

American-style rankings

= RANK (K247, \$ K \$ 247: \$ K \$ 270)

= RANK (B1, \$ B1: \$ H1)

Chinese-style ranking

= RANK (B2, \$ B \$ 2: \$ B \$ 21,0)

= RANK (B1, \$ B1: \$ H1) + COUNTIF (\$ B \$ 1: B1, B1) -1

= SUM (IF (\$ A \$ 1: \$ E \$ 1> = A1, 1/COUNTIF (\$ A \$ 1: \$ E \$ 1, \$ A \$ 1: \$ E \$ 1 ),""))

= SUMPRODUCT ((\$ B \$ 2: \$ B \$ 21> = B2) / COUNTIF (\$ B \$ 2: B \$ 21, B \$ 2: B \$ 21))

= SUMPRODUCT ((B \$ 3: B \$ 21> B3) * (1/COUNTIF (\$ B \$ 3: \$ B \$ 21, \$ B \$ 3: \$ B \$ 21))) +1 (Ascending)

= SUMPRODUCT ((B \$ 3: B \$ 21 <B3) * (1/COUNTIF (\$ B \$ 3: \$ B \$ 21, \$ B \$ 3: \$ B \$ 21))) +1 (descending)

{= SUM (- (IF (FREQUENCY (B \$ 2: B \$ 21, B \$ 2: B \$ 21), B \$ 2: B \$ 21> B2))) +1}

{= SUM (IF (\$ B \$ 3: \$ B \$ 21 <= B3, "", 1 / (COUNTIF (\$ B \$ 3: B \$ 21, B \$ 3: B \$ 21 ))))+ 1} (ascending)

{= SUM (IF (\$ B \$ 3: \$ B \$ 21 <= B3, 1 / (COUNTIF (\$ B \$ 3: B \$ 21, B \$ 3: B \$ 21 )),""))} (descending)

{= SUM (IF (\$ B \$ 2: \$ B \$ 21> B2, 1/COUNTIF (\$ B \$ 2: B \$ 21, B \$ 2: B \$ 21))) +1}

{= SUM (IF (\$ A \$ 1: \$ E \$ 1> = A1, 1/COUNTIF (\$ A \$ 1: \$ E \$ 1, \$ A \$ 1: \$ E \$ 1 ),""))}

{= SUM ((\$ B \$ 2: \$ B \$ 21> B2) * (MATCH (\$ B \$ 2: B \$ 21, B \$ 2: B \$ 21,) = ROW (\$ 1: \$ 20))) +1}

{= SUM (IF (\$ B \$ 1: \$ H \$ 1 <= B1, "", 1 / (COUNTIF (\$ B \$ 1: \$ H \$ 1, \$ B \$ 1: \$ H \$ 1 ))))+ 1}

For the most streamlined automated ranking formula

= RANK (E2, \$ E \$ 2: \$ E \$ 21)

= RANK (A2, \$ A \$ 2: \$ A \$ 9,0)

= RANK (A2, \$ A \$ 2: \$ A \$ 10) + COUNTIF (\$ A \$ 2: \$ A2, \$ A2) -1 (if the data column values are the same)

= RANK (F10, \$ F10: \$ Q10) + COUNTIF (\$ F10: F10, F10) -1

= INDEX (A: A, 1/MOD (LARGE (E \$ 2: E \$ 21 +1 / ROW (\$ 2: \$ 21), ROW (1:1)), 1))

= LOOKUP (1,0 / ((\$ F \$ 2: \$ F \$ 21 = A27) * (COUNTIF (D \$ 26: D26, \$ A \$ 2: \$ A \$ 21) = 0)), \$ A \$ 2: \$ A \$ 21) = INDIRECT ("A" & RIGHT (LARGE ((\$ E \$ 2: \$ E \$ 21 * 100 + ROW (\$ A \$ 2: \$ A \$ 21)), ROW (A1)), 2))

= RANK (C2, OFFSET (\$ C \$ 1, MATCH (E2, \$ E \$ 2: \$ E \$ 768,),, COUNTIF (\$ E \$ 2: \$ E \$ 768, E2)))

Array formula

{= INDEX (A: A, MOD (LARGE (E \$ 2: E \$ 21 * 100 + ROW (\$ 2: \$ 21), ROW (1:1)), 100))}

{= OFFSET (\$ A \$ 1, RIGHT (LARGE (\$ E \$ 2: \$ E \$ 21 * 1000 + ROW (\$ E \$ 2: \$ E \$ 21), ROW () -25), 3) -1,,)}

= OFFSET (\$ A \$ 1, RIGHT (LARGE ((\$ E \$ 2: \$ E \$ 21 * 100 + ROW (\$ A \$ 1: \$ A \$ 20)), ROW (A3)), 2),)

= TEXT (SUMPRODUCT ((\$ E \$ 2: \$ E \$ 21> = E2) / COUNTIF (\$ E \$ 2: \$ E \$ 21, \$ E \$ 2: \$ E \$ 21)), "the first [DBNUM1] G / common format name")

Sorted ranked

{= SUM (IF (\$ B \$ 2: \$ B \$ 15> = B2, 1/COUNTIF (\$ B \$ 2: \$ B \$ 15, \$ B \$ 2: \$ B \$ 15)))}

= SUMPRODUCT ((B \$ 2: B \$ 15> = B2) / COUNTIF (B \$ 2: B \$ 15, B \$ 2: B \$ 15))

Ranking ranking

{= IF (\$ B2: \$ O2> = 0, RANK (\$ B2: \$ O2, \$ B2: \$ O2, 0),)}

According to two-column joint performance ranking

= RANK (C345, (\$ C \$ 345: \$ C \$ 356, \$ H \$ 345: \$ H \$ 356))

Ranked among the double row

= RANK (B2, (\$ B \$ 2: \$ B \$ 26, \$ E \$ 2: \$ E \$ 16))

Other ranking

Descending ranking

= RANK (B3, \$ B \$ 3: \$ B \$ 12)

= SUMPRODUCT ((\$ A \$ 16: \$ A \$ 25 = A16) * (\$ B \$ 16: \$ B \$ 25> B16)) +1

Ranked from small to big

= RANK (B3, \$ B \$ 3: \$ B \$ 12,1)

= SUMPRODUCT ((\$ A \$ 16: \$ A \$ 25 = A16) * (\$ B \$ 16: \$ B \$ 25 <B16)) +1

Range ranking (the first row of small lines)

Descending

= RANK (B3, \$ B \$ 3: \$ B \$ 12) + COUNTIF (\$ B \$ 3: B3, B3) -1

= SUMPRODUCT ((\$ A \$ 16: \$ A \$ 25 = A16) * (\$ B \$ 16: \$ B \$ 25-ROW (\$ B \$ 16: \$ B \$ 25) / 10000> B16-ROW (B16) / 10000)) +1

Small to large

= RANK (B3, \$ B \$ 3: \$ B \$ 12,1) + COUNTIF (\$ B \$ 3: B3, B3) -1

= SUMPRODUCT ((\$ A \$ 16: \$ A \$ 25 = A16) * (\$ B \$ 16: \$ B \$ 25 + ROW (\$ B \$ 16: \$ B \$ 25) / 10000 <B16 + ROW (B16) / 10000)) +1

Range ranking (the first large-row)

Descending

= COUNT (\$ B \$ 3: \$ B \$ 12)-RANK (B3, \$ B \$ 3: \$ B \$ 12,1)-COUNTIF (\$ B \$ 3: B3, B3) +2

= SUMPRODUCT ((\$ A \$ 16: \$ A \$ 25 = A16) * (\$ B \$ 16: \$ B \$ 25 + ROW (\$ B \$ 16: \$ B \$ 25) / 10000> B16 + ROW (B16) / 10000)) +1

Small to large

= COUNT (\$ B \$ 3: \$ B \$ 12)-RANK (B3, \$ B \$ 3: \$ B \$ 12) -COUNTIF (\$ B \$ 3: B3, B3) +2

= SUMPRODUCT ((\$ A \$ 16: \$ A \$ 25 = A16) * (\$ B \$ 16: \$ B \$ 25-ROW (\$ B \$ 16: \$ B \$ 25) / 10000 <B16-ROW (B16) / 10000)) +1

By ranking

Descending

= SUMPRODUCT ((B \$ 3: B \$ 12> B3) * (1/COUNTIF (B \$ 3: B \$ 12, B \$ 3: B \$ 12))) +1

= SUMPRODUCT ((\$ A \$ 16: \$ A \$ 25 = A16) * (\$ B \$ 16: \$ B \$ 25> B16) / COUNTIF (\$ K \$ 16: \$ K \$ 25, \$ K \$ 16: \$ K \$ 25)) +1

Small to large

= SUMPRODUCT ((B \$ 3: B \$ 12 <B3) * (1/COUNTIF (B \$ 3: B \$ 12, B \$ 3: B \$ 12))) +1

= SUMPRODUCT ((\$ A \$ 16: \$ A \$ 25 = A16) * (\$ B \$ 16: \$ B \$ 25 <B16) / COUNTIF (\$ K \$ 16: \$ K \$ 25, \$ K \$ 16: \$ K \$ 25)) +1

There are tied for

= RANK (B2, \$ B \$ 2: \$ B \$ 20)

= SUMPRODUCT (1 * (\$ B \$ 3: \$ B \$ 21> B3)) +1

= COUNTIF (\$ B \$ 3: \$ B \$ 21 ,">"& B3) +1

{= SUM (IF (\$ B \$ 3: \$ B \$ 21> B3, 1,0)) +1}

= 19-FREQUENCY (\$ B \$ 3: \$ B \$ 21, B3) +1

= SUMPRODUCT ((\$ B \$ 2: \$ B \$ 20> = B2) / COUNTIF (\$ B \$ 2: \$ B \$ 20, \$ B \$ 2: \$ B \$ 20))

No tied for

= RANK (B3, \$ B \$ 3: \$ B \$ 21) + COUNTIF (\$ B \$ 3: \$ B3, B3) -1

= SUMPRODUCT ((B3-ROW () / 1000 <\$ B \$ 3: \$ B \$ 21-ROW (\$ B \$ 3: \$ B \$ 21) / 1000) * 1) +1

= 19-FREQUENCY (\$ B \$ 3: \$ B \$ 21-ROW (\$ B \$ 3: \$ B \$ 21) / 1000, B3-ROW () / 1000) +1

{= SUM (IF (\$ B \$ 3: \$ B \$ 21-ROW (\$ B \$ 3: \$ B \$ 21) / 1000> B3-ROW () / 1000,1,0)) +1}

There are parallel sub-rankings

= SUMPRODUCT ((\$ A \$ 3: \$ A \$ 21 = A3) * (\$ C \$ 3: \$ C \$ 21> C3)) +1

= 19-FREQUENCY ((\$ A \$ 3: \$ A \$ 21 = A3) * (\$ C \$ 3: \$ C \$ 21), C3) +1

{= MATCH (C3, LARGE (OFFSET (\$ C \$ 2, IF (\$ A \$ 3: \$ A \$ 21 = A3, ROW (\$ A \$ 3: \$ A \$ 21) -2),), ROW (INDIRECT ("1:" & COUNTIF (\$ A \$ 3: \$ A \$ 21, A3 )))), 0)}

{= MATCH (C3, LARGE (IF (\$ A \$ 3: \$ A \$ 21 = A3, \$ C \$ 3: \$ C \$ 21), ROW (INDIRECT ("1:" & COUNTIF (\$ A \$ 3: \$ A \$ 21, A3)) )), 0)}

{= SUMPRODUCT ((\$ A \$ 3: \$ A \$ 21 = A3) * (\$ C \$ 3: \$ C \$ 21> C3) / COUNTIF (\$ N \$ 3: \$ N \$ 21, \$ N \$ 3: \$ N \$ 21)) +1} (with auxiliary column)

No sub-ranked side by side

{= SUMPRODUCT ((\$ A \$ 3: \$ A \$ 21 = A3) * (\$ C \$ 3: \$ C \$ 21-ROW (\$ C \$ 3: \$ C \$ 21) / 10000> C3-ROW (C3) / 10000)) +1 }

= 19-FREQUENCY ((\$ A \$ 3: \$ A \$ 21 = A3) * (\$ C \$ 3: \$ C \$ 21-ROW (\$ C \$ 3: \$ C \$ 21) / 1000), C3-ROW () / 1000) +1

Performance ranking

 No. Name Language Mathematics English 1 Yang Zenghai 135 136 146 2 Guo Ailing 138 137 141 3 Hua Zhifeng 134 138 141 4 Yuan Wenfei 134 143 135

Can I use a formula used to directly identify the first candidates in the language performance of the score is 100 how much?

= LARGE (C2: C417, 100)

= PERCENTILE (C2: C417, (416-100) / 416)

= PERCENTILE (\$ C \$ 2: \$ C \$ 417, (COUNTA (\$ C \$ 2: \$ C \$ 417) -100) / COUNTA (\$ C \$ 2: \$ C \$ 417))

Directly to find out whether a formula in the language used by candidates and test scores in number by 35% in the first 35% of the tangent of the results is how much?

Ascending

= SMALL (C2: C417, 416 * 0.35)

= PERCENTILE (\$ C \$ 2: \$ C \$ 417,0.35)

Descending

= LARGE (C2: C417, 416 * 0.35)

= PERCENTILE (\$ C \$ 2: \$ C \$ 417,1-0.35)

How rankings

1, the rank of English, absent are not counted.
2, the rank of English, absent counted.

 English English Ranking 42 9 62 3 72 1 48 5 48 5 72 1 54 4 42 9 Absent Absent 45 8 46 7

Not be counted absent

b2 = IF (A2 = "absent ","", RANK (A2, \$ A \$ 2: \$ A \$ 13)) and then sort by column B

Counted absent

= IF (A2 = "absent", COUNTIF (\$ A \$ 2: \$ A \$ 13, "> = 0") +1, RANK (A2, \$ A \$ 2: \$ A \$ 13))

= IF (A2 = "absent", COUNT (\$ A \$ 2: \$ A \$ 13) +1, RANK (A2, \$ A \$ 2: \$ A \$ 13,0))

Data rankings (ranking every few lines)

= IF (A2 ="","", RANK (A2, \$ A \$ 2: \$ A \$ 11,0))

If the rankings every few lines, the following table, fifth row, ninth and twelfth row line does not participate in rankings.

 Unit Data Rankings A 1 8 A 5 7 A 6 6 Subtotal 12 B 8 4 B 9 3 B 7 5 Subtotal 24 C 18 1 C 11 2 Subtotal 29

= IF (A2 = "Subtotal ","", RANK (B2, (B \$ 2: B \$ 4, B \$ 6: B \$ 8, B \$ 10: B \$ 11))) Down

Ranked according to scores down

= RANK (\$ E3, \$ E \$ 3: \$ E \$ 22,1)

= RANK (K60, \$ K \$ 60: \$ K \$ 83,1)

= COUNTIF (\$ K \$ 60: \$ K \$ 83 ,"<"& K60) +1

What is the last ranking function

1 is a positive sort, 0 for the reverse order.

Countdown Ranking = RANK (A2, \$ A \$ 2: \$ A \$ 5,0)

Positive rank = RANK (A2, \$ A \$ 2: \$ A \$ 5,1)

How to achieve the ranking of the daily production of the workshop

= RANK (C2, OFFSET (\$ C \$ 2: \$ C \$ 5, MATCH (A2, \$ A \$ 2: \$ A \$ 1000,0) -1 ,,,))

= RANK (C2, OFFSET (\$ C \$ 2: \$ C \$ 5, MATCH (A2, \$ A \$ 2: \$ A \$ 33,0) -1 ,,,))

Score the same score by a subject to rank

{= MATCH (K308 * 100 + D308, LARGE (\$ K \$ 308: \$ K \$ 331 * 100 + \$ D \$ 308: \$ D \$ 331, ROW (\$ K \$ 308: \$ K \$ 331) -307),)}

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

After screening the serial number and automatically generated summary

Automatically generated sequence number: Enter the following formula in A1, drag down.

= SUBTOTAL (3, \$ B \$ 2: B2) * 1

Automatically aggregated, using the following formula:

= SUBTOTAL (9, \$ B \$ 2: B2)

Note: the summary, not in the "Select All" under the state, the first "screening" out of a particular unit, automatic summation Σ . And then return to "Select All" or select any unit, will automatically summarizes (in the "screening" of a unit sum, the general form will automatically generate summary formula above).

Other: If while in the other cells show the number in the "Select" mode, the selected cells, point "fx" (with "sum" function) and then click the last ending sequence number, you can.

How odd line screening

Formula = MOD (A1, 2) = 1

Name of function screening

How the two as long as A and A + contains the filter out of personnel

= IF (ISNUMBER (FIND ("A", C2)) + ISNUMBER (FIND ("A", B2))> 0, "OK ","")

Rank filter

Rank = RANK (K5, K \$ 2: K \$ 435)

Class rank = RANK (K6, OFFSET (K \$ 2, MATCH (A6, A: A,) -2,, COUNTIF (A \$ 1: A \$ 500, A6)))

How to achieve fast positioning (filtering out duplicate values)

= IF (COUNTIF (\$ A \$ 2: A2, A2) = 1, A2, "")

= IF ((COUNTIF (\$ A \$ 2: A2, A2) = 1) = TRUE, A2, "")

= INDEX (A: A, SMALL (IF (MATCH (A \$ 1: A \$ 20, A \$ 1: A \$ 20,) = ROW (\$ 1: \$ 20), ROW (A \$ 1: A \$ 20), 65536), ROW ()) ) & "" (array formula)

How in the N column lists A1: L9 in the value of each column there

{= IF (ROW ()> SUM (- x ),"", INDEX (A: A, SMALL (IF (x, ROW (\$ A \$ 1: \$ A \$ 9)), ROW ())))}

Number of gender issues automatically

There is a code, 5, No. 1, 1 male, 2 female, followed by four, the number on his behalf, from 0001-9999, how to achieve the following table:

Gender coding

M 10001

M 10002

Female 20001

M 10003

Female 20002

Men is from 0001-9999

Woman is from 0001-9999

If you have other information entered, just code words for fast input. Screening can be achieved with it.
First to "male" as the sort key, and then a man in the first encoded input 10001, down to the last one to copy. Similarly then, "female" for sorting. Objectives.

Using the formula: = IF (A2 = "", TEXT (COUNTIF (A \$ 2: A2, A2), "10000"), TEXT (COUNTIF (A \$ 2: A2, A2), "20000")) down to drag

Mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm mm

[Text] with the Page Setup

How to remove * No. EXCEL

Entry account is entered in the * number, how to remove.

Can function SUBSTITUTE (a1 ,"*","")

Find ~ * Replace empty.

The string of asterisks "*" with other characters

In the Find field, enter ~ *

Replaced by "-" button.

To function spaces

How to delete spaces in the cell, such as name before, during and after the space, the cell is a word that names in the middle of a space, like deleting the ways. Such as: China, read: China.

1, using the formula: = SUBSTITUTE (A2, "","") Note: The first pair of double quotes in a space. The second "" is no spaces.

2, the use of search - replace, once and for all solution.

"Edit" - "replace" (or Ctrl + H), in the "Find" field enter a space, "with" do-nothing type (blank). Then "Replace All" button.

3, there is a special function to delete spaces: TRIM ()

Edit column in EXCEL, whether Chinese or English can only lose one byte of input space, but if the word from the middle two bytes of space, then the TRIM () will not work, it is not that is a space, but a character, how to "TRIM" useless. Example: cell A1 has "center", if TRIM becomes "center", like all the spaces removed, only with SUBSTITUDE () function, how much space can be removed.

How to remove the characters and the space into cells

8900079501 8,900,079,501 -

1900078801 1900078802 -

= SUBSTITUTE (B2 ,"~","")

How quickly remove table rows and columns in different spaces

Editing - Positioning - Positioning conditions - null values, select all blank cells, then remove.

How to stop the input space

In Excel, how to edit "valid data" to prohibit the entry space? Grateful heroes who bother to answer.

Answer: the effectiveness of the formula. = COUNTIF (A1, "* *") = 0

(Note: COUNTIF (A1, "* *") spaces in the cell when the result is 1, the result is 0 when there is no space

If you want the first one can not enter a space: countif (a1, "*") = 0

Can not be the last one if they wish to enter a space: countif (a1, "*") = 0)

Cell number, start bit, counting the first few digits from the beginning the number to be used in place of the string.

windows2000 into windows2K

= REPLACE (B2, 8,3, "K")

Cell number, to replace the out of character, to be used instead of the characters, the first of several.

Instead of the whole cell B391 in the TT, to UU.

EETTCCTTFF into EEUUCCUUFF

= SUBSTITUTE (B394, "TT", "UU")

Only instead of the cell B391 in the first occurrence of TT, to UU.

EETTCCTTFF into EEUUCCTTFF

= SUBSTITUTE (B397, "TT", "UU", 1)

The number of cells into a specific character format

The second parameter function double quotes must not be Chinese format (can not use any Chinese input method input double quotes.)

Example: 20000 Purpose: To become a character with a dollar sign

10,000 yuan to become the symbol of character with

151 581 into the character with the euro symbol

15,748,415 characters into Simplified Chinese

Steps: = TEXT (B72, "\$ 0.00") Results: \$ 20,000.00

= TEXT (B73, " ¥ 0.00") ¥ 10000.00

= TEXT (B74, " € 0.00") € 151581.00

= TEXT (B75, "[DBNum2] G / GM format"), rented furnishings Si Shi Wu Wan THOUSAND Sibai Fifty One

= TEXT (B76, "[DBNum1] G / GM format"), 15,748,415

There are over six hundred to a cell into a multi-column

There is a table, a total of 14, but only one per page, how to put them together into one (according to the order page), if you use cut and paste way, as too much trouble.

= INDIRECT ("r" & (COLUMN () -3) * 48 + ROW () & "C1", 0) copied to other cells

N columns will change the formula summarized in column M

= OFFSET (\$ A \$ 1, INT (((ROW (A1) -12) * m + COLUMN (A1) -1) / n), MOD ((ROW (A1) -1) * m + COLUMN (A1) - 1, n))

= OFFSET (\$ A \$ 1, INT (((ROW (A1) -1) * 7 + COLUMN (A1) -1) / 4), MOD ((ROW (A1) -1) * 7 + COLUMN (A1) - 1,4)) four variable seven

= OFFSET (\$ A \$ 1, INT (((ROW () -20) * 10 + COLUMN () -1) / 7), MOD ((ROW () -20) * 10 + COLUMN () -1,7) ) seven changes ten

A change four

= OFFSET (\$ A \$ 1, ROW (\$ A1) * 4-COLUMNS (C: \$ F),)

= OFFSET (\$ A \$ 1, (ROW () -3) * 4 + MOD (COLUMN () -8,4),)

= OFFSET (\$ A \$ 1, ROW (A1) * 4-4 + MOD (COLUMN () -13,4),)

Four become one

= OFFSET (\$ F \$ 1, INT (ROW (1:1) / 4 +3 / 4) -1, MOD (ROW () -1,4))

= OFFSET (\$ F \$ 1, INT ((ROW (1:1) -1) / 4), MOD (ROW () -1,4))

= OFFSET (\$ F \$ 1, ROUNDUP ((ROW (1:1) / 4), 0) -1, MOD (ROW () -1,4))

= OFFSET (\$ F \$ 1, (ROW () -1) / 4, MOD (ROW () -1,4))

Repeated four times filled

= TEXT (INT (ROW () / 4 +3 / 4), "00")

= IF (TRUNC ((ROW () -1) / 4,0) <9, "0" & TRUNC (ROW () / 4-0.01,0) +1, TRUNC (ROW () / 4-0.01,0) +1)

= TEXT (ROUNDUP (ROW () / 4,), "00")

= TEXT (ROW (2:2) / 4, "00")

Multiple rows of data in a row

 a1 b1 c1 d1 e1 f1 g1 h1 i1 a2 b2 c2 d2 e2 g2 h2 i2 a3 c3 d3 g3 h3 i3 a4 c4 g4 h4 i4 A5 c5 g5 h5 g6
 a1 a2 a3 a4 A5 b1

{= IF (ROW ()> COUNTA (\$ A \$ 1: \$ I \$ 10 ),"", INDEX (\$ A \$ 1: \$ I \$ 10, MOD (SMALL (IF (\$ A \$ 1: \$ I \$ 10 <>"", ROW (\$ A \$ 1: \$ I \$ 10) + COLUMN (\$ A \$ 1: \$ I \$ 10) * 100000), ROW ()), 100000), INT (SMALL (IF (\$ A \$ 1: \$ I \$ 10 <> "" , ROW (\$ A \$ 1: \$ I \$ 10) + COLUMN (\$ A \$ 1: \$ I \$ 10) * 100000), ROW ()) / 100000)))}

Into the cell a multi-column

If you have an information need to be divided into multiple columns, select this column as long as the first, and then select "Data" → "breakdown", a dialog box appears, select "fixed width" or "separator." In the former case, the next step with a mouse click as long as the information that can be segmented according to any width, such as for the latter as long as there is a clear separator can, the next step after just minutes you can customize the format of the column , will be good even after the completion.

Steps:

1, make sure one of the most appropriate column width, then divided the number of columns multiplied by its width, that is

Out the top of the column width = most appropriate column width × number of columns to be divided.

2, Edit - Fill - Content rearrangement.

3, data - points out.

First letter capitalized

Cell numbers in the first letter of the word into upper case letters, and the remaining letters to lowercase.

Such as china - China

= PROPER (B160)

The cell number in lowercase letters into uppercase

lafayette148 LAFAYETTE148

= UPPER (B1)

= LOWER (B1) (uppercase letters to lowercase letters formula)

To name Justified

Name with the words, plenty of three characters, some two characters, print it out very beautiful, to make the names of the characters is about the word aligned with the three words there are two ways:

Method One: formatting method. After we have selected to delete the name of a space cell, click the "Format → Cells" open the "Format Cells" dialog box, select the horizontal alignment of the "Distributed" option to determine the withdrawal to enable students to name words about alignment.

Method Two: function formula method. Using Excel's "IF", "LEN", "MID" combination of three functions can name about using the word alignment. Specific examples are: In cell C3 enter the formula: "= IF (LEN (B2)> = 3, B2, (MID (B2, 1,1) & &" "& & MID (B2, 2,1)))", determined using the fill handle to copy the formula.

Center and then align the decimal number

Either side of the decimal point may be valid for the zero-added space, so that when the format is set to a fixed width font, you can align the decimal point.

Format - Cells - Digital - Custom - ???.???- determine

I ask you: after the decimal point "0" there are ways to display it? For example:

2.0

12.001

Format - Cells - Digital - Custom -??? .0? - OK

Evaluates the specified cell number group the number of empty cells Africa

Calculation B252 to B262 between the number of non-empty cells.

= COUNTA (B252: B262)

Compare the two cell contents are the same

74P125148 74P125148

Compare cell B53 and C53 contents are the same.

If consistent, then the return value is TRUE, inconsistent, then the return value is FALSE.

= EXACT (B53, C53)

Result: TRUE

How to set the order for this column each cell can only enter 12

How to set the order for a column or a row of each cell can only enter 12, (Arabic numerals and letters, including 26 English, not Chinese.)

Select column A, set data validation: Custom> formula: "= LEN (A1) = 12"

How to make worksheet odd even row background is red line is the blue background

With the condition format

= ROW () / 2 = INT (ROW () / 2) set the color

Conditional formatting: The formula is = MOD (ROW (), 2) = 0

Calculate a specific group of cells, to meet the conditions of the number of cells

Still more questions, for example, calculate the three people in the B307 to B313 in the respective share of the number of cells.

Lee Six: = COUNTIF (B307: B313, B323)

Wang Wu: = COUNTIF (B307: B313, C323)

Chen Feng: = COUNTIF (B307: B313, D323)

Name: Lee 6 Wang Wu Chenfeng

Results: 322

• excel function formula and techniques used to collect 2 2011-09-27

[Calculated age and length of service] Calculated according to age, date of birth = DATEDIF (A1, TODAY (), "y") = DATEDIF (A1, TODAY (), "y") & "years old" = DATEDIF (A1, NOW (), "y") According to projections Zo

• excel function formula and techniques used to collect 4 2011-09-27

[Character] changes in the interception and After the interception of a character cell to the character = RIGHT (A1, LEN (A1)-SEARCH (",", A1, 1)) Intercept character formula There is a set of data 101 103 * 20 * 11 ~ ~ ... I want to * intercept

• excel function formula and techniques used to collect 3 2011-09-27

The text format of numbers into real numbers = VALUE (B1) Set Page How to set "the first page × total × page" page. Set in the footer: Page & [Page] page, a total of & [Total Page] Pages to How to insert page numbers in an Excel spreadsh

• excel function formula and techniques used to collect 5 2011-09-27

With data on how the unit sum In the data unit must be added to the final statistics but also the sum, and ask how AutoSum? (Example: A1: 2KG, A2: 6KG ....., in the last line KG automatically calculate the total number). = SUMPRODUCT (- LEFT (A1: A5,

• excel function formula and techniques used to collect 1 2011-09-27

[ID information? Extract] Extracted from the ID number Date of Birth = TEXT (MID (A1, 7,6 + (LEN (A1) = 18) * 2 ),"#- 00-00 ") +0 = TEXT (MID (A1, 7,6 + (LEN (A1) = 18) * 2 ),"#- 00-00 ") * 1 = IF (A2 <>"", TEXT ((LEN (

• Java Excel function of the effective interest rate demand RATE 2010-10-22

There is a function in excel RATE is to get the real interest rate, have identified a half-line, the formula'd find a lot of, you can not have a realization of the algorithm, depressed, their own research and write one out to be a memo Formula: The a

• EXCEL VBA formula issue summary 2011-07-27

The first recommended a very good EXCEL program learning site: http://club.excelhome.net/ Problem Background: Recently, my colleagues encountered two problems, described below. Scene 1: Excel tool, a cell complex formula, this formula is derived from

• EXCEL function _ multi-unit operation 2011-08-08

excel

• vlookup function in excel 2012-08-17

Lesson 19: VLOOKUP function in Excel The VLOOKUP function in Excel searches for a value in the leftmost column of a table and returns the value on the same row of another column to the right. So VLOOKUP looks up for " John" in column A and his a

• JS Export Excel, Word 2009-05-28

/ / Export Excel function AllAreaExcel () ( var oXL = new ActiveXObject ( "Excel.Application"); var oWB = oXL.Workbooks.Add (); var oSheet = oWB.ActiveSheet; var sel = document.body.createTextRange (); sel.moveToElementText (export1); sel.select

• web are several ways to export excel file 2010-03-29

- Http: / / www.javaeye.com/topic/461899 Is often asked how to export data to excel file. Overall, the two methods: the server-side generation and generation browser. Generated server-side is this: according to user request, to obtain the appropriate

• 2010.03.01 - html export to word excel documents 2008-05-25

2010.03.01 - html export to word excel documents html <body> <BR> <table id = "PrintA" width="100%" border="1" cellspacing="0" cellpadding="0" bgcolor = "#61FF13"> <TR> &l

• GridView Export Excel 2010-02-23

1, first set the aspx page EnableEventValidation = "false" as follows: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FieldObjSearch.aspx.cs" Inherits="Unitop.JGXT2010.DAS.FieldObjSearch" Ena

• Js to achieve with print and export to Excel 2010-04-05

Printing can be achieved with the control, can also be saved as excel using POI to achieve, but if the current page only designated areas for print or export Excel can use js to achieve, or very simple. Save as Excel: function saveAsExcel(HeadName, D

• php Export Export Excel 2010-04-24

Class OutOrInService { // Export excel function saveExcel(\$filename,\$sheetname,\$data) { // \$black = new BlackService(); // \$data = (array)\$black->select("","","","",1); // \$title = Array("id"," Sou

• php import and export Excel 2010-04-24

1. This method is simple, there is no export problem , But the essence or XML, it is not possible to import the exported file .(Excel_XML This is an external method, you need Excel_XML External class ) Class OutOrInService { // Export excel function

• String Aggregation Techniques 2010-05-07

In some cases, it is necessary to aggregate data from number of rows into a single row, giving a list of data associated with a specific value. There are some methods to achieve this. LISTAGG Analystic Function in 11g Release 2 The LISTAGG analytic f

• PHP export excel method, only the array can be passed 2010-05-31

PHPExcel is a powerful class library, but for general users, there is no need Gaode less complex, as long as export into Excel on it. Therefore, their conclusion at this point to write something. The following is a php-excel in the library resource p

• PHP export excel method can only pass arrays 2010-05-31

PHPExcel is a powerful class library, but for the average user, there is no need Gaode so complex, so long as export into Excel on it. Therefore, their conclusion at this point to write something. The following is a php-excel in the library resource

• javascript to export data to excel in the table (to support export form lines) 2010-06-16

<HTML> <HEAD> <META http-equiv=Content-Type content="text/html; charset=utf-8"> <title>WEB Page of the export to EXCEL document </title> </HEAD> <body> <BR> <table id = "PrintA" width=

1
2
3