Today, the system home Xiaobian introduces how to use the Excel design competition scoring system. Its main function is to input various basic scores, and all other required results are automatically displayed immediately. . Organs, schools, enterprises and institutions will hold various competition activities. If the scores of the competitions are handled by pure manual methods, not only the speed is slow, but also errors may occur, which greatly affects the actual effect of the competition activities. The author uses Excel to design a scoring system. As long as the host announces the scores of the judges, the staff will record the scores synchronously, and the system will automatically prompt the error scores; the highest scores and the lowest scores will be automatically used in different colors and fonts. Perform differentiated display; automatically remove the highest score and the lowest score; automatically calculate the final score of each player; automatically generate the score of the contestant's score; automatically display the top three scores with more red bold characters. If the system is displayed by multimedia projection equipment, the whole competition scoring process can be quick, compact, open and transparent, which can highlight the competition atmosphere. This system can be widely used for the scoring and statistics of various competitions as long as it is slightly modified according to the actual number of judges and the number of players. The design steps are as follows: 1. New competition scoring system new “ competition scoring system.xls” workbook, C2: J2 cells for each judge, B3: B12 cells for each player, K3: K12 cells for the final score , L3: L12 cells are ranked for the score. C3: K12 is the record partition, and all score entries are in this area (Figure 1). Second, set the data validity If the scoring standard is the percentage system, when the input data is not in this range, you can use "data validity" to automatically prompt data errors. (1) Use the mouse to select the cell in which the C3:K12 data is entered. (2) In the “Data” menu, click “Validity”. (3) Open the "Settings" tab and set the valid range of data (Figure 2). (4) Open the “Error Warning” tab (Figure 3, set the title and content of the prompt window when the input data exceeds the setting range. 3. Display the highest and lowest scores after the score is scored, use “ conditions Format & rdquo;, through the font and color, can distinguish the highest score, the lowest score and other scores. (1) Use the mouse to select the C3: J3 cell area, select the menu & ldquo; format & rarr; condition format & rdquo; command, open <;Conditional Format" dialog box (Figure 4). (2) Click the drop-down arrow below <;Condition 1”, select “Formula" in the drop-down list that pops up. Set the highest conditional format formula to :“=MAX($C3:$J3)=C3” Click the "format” button to set the font to “Bold” and “red”. Tip: If the conditional format formula is “ =MAX(C3:J3)=C3”, the reference is a relative reference, it will automatically change according to the actual offset of the cell, and the result will be wrong. (3) Click the “Add” button, use the same Method, will The formula for the low score is set to “=MIN($C3:$J3)=C3”, and the “font” format is set to "bold" and "blue" (Figure 5). Click “; OK & rdquo; button, confirm & ldquo; conditional format & rdquo; settings. (4) Apply the third line of "conditional format" to the following lines. Use the mouse to select the C3: J3 cell area, point the mouse to the J3 unit Fill the handle in the lower right corner of the grid, then the mouse turns into a black "10" shape, hold down the left mouse button, drag down to the cell J12, release the mouse, you can complete the fill. After setting, click “ determine the & rdquo; button, complete the "conditional format" settings. Fourth, calculate the player's final score (1) set the final score accuracy to two decimal places. Select cell K3: K12, right click, Select “Set Cell Format" in the “Numbers"Number" > Number" tab" "Number" "Number" "value", "number" ” set to 2 (Figure 6). (2) Calculate the last Enter the following formula in the last score cell K3: =IF(COUNT(C3:J3)=0,"",(SUM(C3:J3)-MAX(C3:J3)-MIN(C3:J3) ) /(COUNT (C3: J3)-2)) IF (COUNT (C3: J3) = 0, " " in the formula: when the line does not enter any score, no calculation, no display content. The function of SUM(C3:J3)-MAX(C3:J3)-MIN(C3:J3) in the formula is to sum the scores of the line and subtract the highest and lowest scores. The role of COUNT(C3:J3) in the formula is: to count the number of judges. The purpose of not using absolute numbers is mainly to solve the absence and uncertainty of the judges. In the absence of individual judges, the competition was conducted as usual and the statistical values were not affected. Click the K3 cell and point the mouse to the fill handle in the lower right corner of the cell. At this time, the mouse turns into a black "10" shape, hold down the left mouse button, drag down to the cell K12, and release the mouse. , you can complete the fill. Note: When the score is entered to the second, the denominator is zero, causing an error in the entire form, which can be ignored. 5. The statistic player scores the selected cell L3 and enters the following formula: =IF(COUNT(C3:J3)=0,"",RANK(K3,$K$3:$K$12)) RANK( The role of K3, $K$3: $K$12) is to count the ranking of K3 cells in K3:K12. In the formula, IF(COUNT(C3:J3)=0,"" is: when the line does not input any score, this cell does not display any content. When K3 has no value, RANK (K3, $K $3:$K$12) will display an error. Click on the L3 cell and point the mouse to the fill handle in the lower right corner of the cell. Then the mouse turns black and “10”, hold down the left mouse button and down Drag to cell L12 and release the mouse to fill the formula of cell L3 into the following cells. 6. Highlight the winner using the mouse to select the L3:L12 cell area, select the menu <;format → condition Format ” command, open the "conditional format" dialog box. Click the drop-down arrow below "condition 1", select "ld formula" in the pop-up drop-down list. The conditional format formula is set to: “=L3< ;=3” Click the "format” button to set the font to “bold"and"red" (Figure 7). After completing the template creation through the above steps, the moderator gradually announces the judges' After scoring, you can then announce the removal of one High, remove a minimum score, the player's final score; when the last player's final score is announced, then the top three players' names and scores will be announced. There will be no pauses or cold spots in the middle. .