Excel table Formulas

Excel table Formulas
1. Formula for searching duplicate content: = If (countif (A: A, A2)> 1, "DUPLICATE ","").
2. Calculate the age by Date of Birth: = trunc (days360 (h6, "2009/8/30", false)/360,0 ).

3. calculated from the input 18-digit ID card number's Birthdate: = concatenate (mid (E2,), "/", mid (E2 ),"/", mid (E2, 13, 2 )).

4. Ask the system to automatically extract gender from the entered ID card number. Enter the following formula:

= If (LEN (C2) = 15, if (mod (mid (C2, 15,1), 2) = 1, "male", "female "), if (mod (mid (C2, 17,1), 2) = 1, "male", "female") The "C2" in the formula indicates the cell that inputs the ID number.

1. Sum: = sum (K2: k56) -- sums K2 to k56;

2. Average: = average (K2: k56) -- calculate the average for the K2 k56 region;

3. ranking: = rank (K2, K $2: K $56) -- rank the scores of 55 students;

4. Level: = If (k2> = 85, "excellent", if (k2> = 74, "good", if (k2> = 60, "pass ", "fail ")))

5. General term evaluation: = k2 * 0.3 + m2 * 0.3 + N2 * 0.4 -- assume that column K, column M, and column N respectively store the students' "average rating", "mid-term", and "end-of-period ". score;

6. Highest score: = max (K2: k56) -- calculate the highest score from k2 to k56 (55 students;

7. Score: = min (K2: k56) -- score the score from k2 to k56 (55 students;

8. Number of people in the score segment:

(1) = countif (K2: k56, "100") -- calculates the number of people from k2 to k56, where the result is stored in cell k57;

(2) = countif (K2: k56, "> = 95")-k57 -- evaluate K2 to k56 region 95 ~ Number of people with 99.5 points. Assume that the result is stored in cell K58;

(3) = countif (K2: k56, "> = 90")-sum (k57: K58) -- evaluate the range of K2 from the k56 area to the 90 ~ Number of people with 94.5 points. Assume that the result is stored in cell k59;

(4) = countif (K2: k56, "> = 85")-sum (k57: k59) -- calculate K2 to k56 region 85 ~ The number of people with 89.5 points. Assume that the result is stored in cell k60;

(5) = countif (K2: k56, "> = 70")-sum (k57: k60) -- Obtain K2 to k56 region 70 ~ The number of people with 84.5 points. Assume that the result is stored in cell k61;

(6) = countif (K2: k56, "> = 60")-sum (k57: k61) -- calculate K2 to the k56 region 60 ~ The number of people with 69.5 points. Assume that the result is stored in cell K62;

(7) = countif (K2: k56, "<60") -- calculates the number of people between K2 and k56 with a score below 60. Assume that the result is stored in cell K63;

Note: The countif function can also calculate the number of male and female in a region.

For example: = countif (C2: c351, "male") -- count the number of male from C2 to c351 (350 in total;

9. Excellent rate: = sum (k57: k60)/55*100

10. pass rate: = sum (k57: K62)/55x100

11. Standard Deviation: = STDev (K2: k56) -- calculate the score fluctuation from k2 to k56 (55 people) (the smaller the value, it indicates that the scores of the students in this class are slightly different, and vice versa, it indicates that the class has polarization );

12. Condition summation: = sumif (B2: b56, "male", K2: k56) -- suppose Column B stores the gender of the student, and column K stores the score of the student, the result returned by this function is the sum of the scores of the boys in this class;

13. Multi-condition summation: {= sum (if (C3: c322 = "male", if (G3: g322 =, 0)} -- assume column C (C3: c322 area) stores the student's gender, G column (G3: g322 area) stores the student's class code (1, 2, 3, 4, 5 ), the result returned by this function indicates the number of boys in the first class. This is an array function. Press Ctrl + Shift + enter to generate "{…}". "{}" Cannot be entered manually. It can only be generated using a combination of keys.

14. automatically calculate the age based on the date of birth: = trunc (days360 (D3, now ()/360,0)

--- Assume that column D stores the student's birth date, and column E enters this function to generate the student's age.

15. Three Tips in word:

① Input three "~" consecutively You can get a wavy line.

② Input three "-" consecutively to get a straight line.

Input three "=" consecutively to obtain a dual-line.

1. How to display a specific color in another cell in Excel when a cell meets a specific condition, for example:
When A1> 1, C1 displays red
0 <A1 <1, C1 is green
When A1 <0, C1 displays yellow
The method is as follows:
1. Click cell C1, click "format"> "condition format", and set condition 1:
Formula = A1 = 1
2. Click "format"-> "font"-> "color", and click "OK" in red ".
Condition 2 is set:
Formula = and (A1> 0, A1 <1)
3. Click "format"> "font"> "color". Click "OK" after "green ".
Condition 3 is set:
Formula = A1 <0
Click "format"-> "font"-> "color", and click "OK" in yellow ".
4. After the three conditions are set, click "OK.

2. How to control the data length of each column in Excel and avoid repeated Input

1. Define the data length with data validity.
Select the data range you want to enter, click "data"-> "validity"-> "set ", set "validity condition" to "allow" "text length" "to" 5 "(the specific condition can be changed according to your needs ).
You can also define the prompt information, error warning information, and whether to enable the Chinese input method. Then click "OK ".
2. Avoid duplication in the condition format.
Select column A, click "format"-> "condition format", and set the condition to "formula = countif ($ A: $ A, $ A1)> 1 ", click "format"-> "font"-> "color", select red and click "OK" twice ".
After this setting, if the length of the input data is incorrect, a message will be displayed. If the data is duplicated, the font will change to red.

3. How to identify the differences between column B and column A in Excel?
(1) If the data in the same row of column A and column B is required to be compared:
For the first row header, click cell A2, and then click "format"> "condition format" to set the condition:
"Cell value" "not equal to" = b2
Click "format"> "font"> "color", select red, and click "OK" twice ".
Copy the conditional format of cell A2 downward with a format brush.
Column B can be set using this method.
(2) If Column A and column B are compared as a whole (that is, the same data is not in the same row ):
For the first row header, click cell A2, and then click "format"> "condition format" to set the condition:
"Formula" = countif ($ B: $ B, $ A2) = 0
Click "format"> "font"> "color", select red, and click "OK" twice ".
Copy the conditional format of cell A2 downward with a format brush.
Column B can be set using this method.
After the preceding settings, all data in column AB is not colored, and columns A have Column B or B has column A without data marked as red.

4. How to batch sort by row in Excel

If there is a large amount of data (numeric value), you need to sort each row in ascending order. How can this problem be solved?
Because both sort by row and sort by column can only have one primary key word, the primary key word can be sorted by the secondary key word at the same time. Therefore, this problem cannot be solved by sorting. The solution is as follows:
1. Assume that your data is in column A to column E. Enter the formula in cell F1:
= Large ($ A1: $ E1, column (A1 ))
Use the fill handle to copy the formula to the right to the corresponding range.
Your original data will appear in the column F to column J in ascending order of rows. If necessary, you can copy "selective paste/value" to other places.
Note: The formula in step 1 can be modified based on your actual situation (data range. To sort data in ascending order, change the formula to: = small ($ A1: $ E1, column (A1 ))

5. Use function combinations to calculate multiple conditions

For example, in the first row header, column A is "name", column B is "class", column C is "Chinese score", and column D is "admission result ", count the number of students whose "class" is "2", whose "Chinese score" is greater than or equal to 104, and whose "admission result" is "DUPLICATE. The statistical results are stored in other columns of this worksheet.
The formula is as follows:
= Sum (if (B2: b9999 = "2") * (C2: c9999> = 104) * (D2: d9999 = "DUPLICATE ))
After entering the formula, press Ctrl + Shift + enter to automatically add the array formula symbol "{}".

6. How can I determine whether a cell contains specified text?
If cell A1 is determined to have "specified text", any of the following formulas can be used:
= If (countif (A1, "*" & "specify text" & "*") = 1, "yes", "no ")
= If (iserror (find ("specified text", A1, 1), "NONE", "yes ")

Calculate the number of non-duplicated data in a region
For example, calculate the number of non-duplicated data in the range of A1: a100. If a number is repeated multiple times, only one data record is counted. There are two calculation methods:
First, use the array formula:
= Sum (1/countif (A1: a100, A1: a100 ))
After entering the formula, press Ctrl + Shift + enter to automatically add the array formula symbol "{}".
The second is to use the product summation function:
= Sumproduct (1/countif (A1: a100, A1: a100 ))

7. How to quickly organize a directory worksheet in a workbook

1. Use macro 3.0 to retrieve the name of each worksheet. Method:
CTRL + F3 the custom name dialog box is displayed, named X. In the reference position box, enter:
= Mid (get. Workbook (1), find ("]", get. Workbook (1) + 1,100)
OK
2. Use the hyperlink function to insert connections in batches. Method:
Enter the formula in the A2 cell of the Directory Worksheet (usually the first sheet:
= Hyperlink ("# '" & Index (x, row ())&"'! A1 ", index (x, row ()))
Fill down the formula until an error occurs, and the directory is generated.