[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
Grade Rank:
= 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 instead of a string
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
1,451,451 characters into Chinese Traditional
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