Application of functions in Excel 2007 query operation

In Excel, we often need to copy data from some worksheets to another worksheet. For example, we need to summarize the students ' scores from different worksheets to a new worksheet, and the number of references and the order of the worksheets are not identical, and they cannot be copied and pasted directly. At this point, if you use Excel's VLOOKUP, index, or offset functions, you can make the problem very simple. Let's take Excel 2007 for example.

Figure 1

Assume that the performance worksheet is shown in Figure 1. B is listed as the name and the total score and rank of the items to be summarized are in columns H and I (That is, columns 7th and 8th, starting with column B). and the summary table as shown in Figure 2, a column of names, C, d two columns are to be summed up the first test scores and ranking. The other grades are arranged backwards in turn.

Figure 2

First, VLOOKUP function

We can enter the formula in the C3 cell of the "Consolidated" Worksheet "=vlookup ($B 3, 1th time! $B $ $I $92,7,false)", enter the first students will be the first test of the total score of the total.

The C3 cell formula is copied to the D3 cell, and the third parameter "7" in the formula is changed to "8", and after the return, you can get the student's first Test position.

Select C3:d3 These two cells, drag down the fill handle to the end you can get all the students total score and position. Is it simple? As shown in Figure 3.

The use of the VLOOKUP function is this: VLOOKUP (parameter 1, parameter 2, parameter 3, parameter 4). "Parameter 1" is "who are you looking for?" "In this case, B3 the cell, which is to find the person name displayed in the cell B3. "Where is the argument 2" where to find it? "In this case, the 1th time! $B $ $I $92" is to tell Excel to look in the b1:i92 range of cells in the 1th time worksheet. "Parameter 3" is "find the data in the first few columns?" "7" In this case is the data from column 7th of the 1th Time worksheet, that is, column H. In this example, "Parameter 4" or "FALSE" means that the query is query-only for exact matching values.

The formula first b!:i on the 1th time worksheet The first column of the 92 range of cells (that is, the B1:b92 cell range) looks for the B3 cell data and, when found, returns data from column 7th (h column) in the row where the data resides. So, after you change the parameter 3 to "8", you can return the data in column I.

As you can see, when you use the VLOOKUP function, the data for parameter 1 must be in the first column of the parameter 2 range. Otherwise, it is not available to find.