Excel one-step make student achievement notice

It is not easy for a teacher to tidy up a student's grades after every exam. Usually collected students ' papers are not in the order of the existing score table, so every time you enter the results in Excel before the test papers according to the order of the record table sorted, and then input in sequence, which is naturally very troublesome. In fact, the most rapid input method should be in the order of the test paper in Excel input the number and score, by the computer by the school number to fill in the score table corresponding students in the record line. It is not difficult to implement this requirement in Excel.

First we need to have an Excel record sheet, then add four columns (J:N) to the record table and enter the column headings.

1. Table settings

Select J1 for the Excel table, click Data/effectivity on the menu, click the "Allow" Drop-down list on the Settings tab, select "Sequence," and then enter = $C $: $H $. Select the K column right click Select Format cells, in the Format Cells Window Number tab, in category, select text, and make sure it is formatted as text.

2. Input formula

Select the J2 input Formula =if (ISERROR (VLOOKUP (A2,l:m,2,false)), "", VLOOKUP (A2,l:m,2,false)), press A2 's school number to find and display the corresponding score in l:m, or null if an error is not found.

In the Excel table's L2 input formula =value ("2007" &left (k2,3)), extract the K2 data from the left three-digit number and precede it with 2007, then convert to a numeric value. Since the previous part of the classmate is generally the same, in order to speed up the input speed we do not need all input, as long as the input number of the last three digits can be, and then the L2 formula will automatically be extracted from the number of numbers before the same part of the "2007" show the full number. Then, in M2 input formula =value (MID k2,4,3), the K2 data from the Excel table is extracted from the 4th digit 3 digits (i.e. fractions) and converted to a numeric value. Finally select the J2:l2 range and drag its fill handle down to copy the same number of rows as the student record.

Note: The value function of Excel is used to convert the extracted text to a numeric value. If there is a number of characters other than Arabic numerals, such as 2007-001 or LS2007001, then the number is no longer the number format and become text format, at this time the formula L2 no longer need to be converted into a numerical value, should be changed to = "2007" &left (k2,3), otherwise there will be errors.

3. Prevention of duplication

Check the Excel table's k column click Format/conditional formatting, select formula in the Drop-down list for condition 1 in the Conditional Formatting window, and enter a formula =l1=2007 without formatting. Then click the Add button, add Condition 2, set the formula to =countif (L:L,L1) >1, click the Format button later, and set the shading to red in the Pattern tab of the Format window to determine the completion setting.

In this way, when two identical numbers appear in the L column of an Excel table, they become red. Set by the previous formula, the L column will appear as "2007" when K is empty, so the preceding condition 1 is not formatted when l1=2007, just to avoid the repetition.