Office software is closely linked to the PDF,the PDF is also must have to control!
Set home Page Add to Favorites
|
record several times with the Excel 2007 exam in the examination results, examination results for each work separately in different tables, the table format shown in Figure 1. Because the worksheet is not the same sort of data, in order to better grasp the learning situation, can quickly access to every student in the previous exams and ranking of each subject changes, so each a work table, one by one to find a student's achievements, it is very cumbersome and not intuitive. So I want to create a new worksheet, each student will then use the results for each test a new worksheet, make clear.
one, quickly set up tables first select A4: A11 cells, merge them into a cell. Then B4: B11 cells are combined into one cell. Then select the two merged cells, drag the fill handle down to B379 cells (assuming This class has 47 students), and then enter the test number and name. $ Show_page $ in C4: C11 cells, respectively, enter the name of each subject, and then select the C4: C11 cell, drag the fill handle down to the C379 cells, can quickly fill a good discipline, and form design shown in Figure 2 below.
2, final exam results of the reference sections quoted subject grade students, of course, should use the VLOOKUP function. However, there are two problems to be solved: question one: If we enter the formula in cell D4 "= VLOOKUP (B4, the end! $ C $ 3: $ S $ 115,2, FALSE)", Enter the natural can be the first after the final exam students Language scores (the score in the "final" work table column D). However, if we drag the fill handle to copy the formula down, then there will be a problem: D4: D11 cells in the first parameter should be "B4", but this parameter is automatically populated when the formula change will occur, need fixed for the B4, but when the next student to fixed B12, and so on. The issue was assisted by setting out, again with the demand function of the balance was finally resolved. Achieved as follows: the D column as supporting columns. Click cell D4, enter the formula "=" $ B "& (INT ((ROW () +4) / 8) * 8-4)", Enter the results obtained "$ B4", then drag the fill period handle to the D379 cells, so that you can achieve the purpose of the fixed search parameters, shown in Figure 3.
Figure 3 $ show_page $ question 2: VLOOKUP function also need to configure the third parameter. The academic performance results in the table in the original location is not the same, according to the form shown in Figure 1, find the language performance parameters should be used when "2", mathematics, English, theory, practice, culture, profession, Total all subjects of this parameter should be under the "3", "4", "5", "6", "7", "9", "11." So, definitely not work directly drag the fill handle. 8 need to reference the results can only use eight different formulas to achieve. Solution to this problem is to use nested IF functions, coupled with the use of several functions as judge of more than demand conditions. Fortunately, Excel 2007 allows us to use 64 nested IF functions. In addition, each student's achievement 8 rows occupied by the cell, so the number of rows divided by 8, where results from the remainder is 0 to 7 that several different figures, as the right to judge the condition. order to make our formula becomes a little simpler, we can for the sake of the remainder part of naming the formula. Click Ribbon "formula" tab, "the name of the definition of" functional group "definition name" button in the dialog box that pops up the "Name" input box, enter the name of "yushu", the "reference position", enter the formula " = MOD (ROW (), 8) ", shown in Figure 4. Later, we use the formula "yushu", is equivalent to directly use the formula "= MOD (ROW (), 8)" the.
Third, other results and rankings of reference select cell E4, drag the fill handle to copy the formula right to F4 cells. Select cell F4, the formula in the edit field the VLOOKUP function to the third argument "2", "3", "4", "5", "6", "7", "9", "11 "were replaced by" 13 "," 14 "," 15 "," 16 "," 17 "," 8 "," 10 "," 12 "(these numbers are shown in Figure 1, all subjects in the specified table Look in the decision in the number of columns). Drag the fill handle to cell F4 copy the formula down to the F379. Stop here a reference to final exam has been completed. $ Show_page $ If you want to be "No. 1" sheet of achievements, you need to select the E4: F379 range of cells, copy it to the G4: H379 range of cells. Then select the G4: H379 range of cells, press "Ctrl + F" shortcut key, click the pop-up dialog "Replace" tab, in the "Find what" input box, enter "final!" In "with "input box, enter" No. 1! ", shown in Figure 6. Click "Replace All" button on the line.
|
