Adaptive performance Query system in table

Menu-Type score Query
Every school will use the result query. The general query query method is to input the relevant information of the object to be queried and let the system extract the performance data.

There are two drawbacks to this approach:

1. need to enter text, for not typing or character entry speed slow people inconvenient;

2. If you entered a typo, a homonym, you can not query the correct results.

Today I teach you a new way of querying, you do not need to enter any character to query any information. At the same time because do not need to enter the character, also avoids the error generation.

Suppose that figure A is the average score table for all classes in the school, in which the length of each semester is different, with 2 years, 3 years, and 4 years, so there is a gap in the middle.

Figure A Average score table

In this case, the results table holds all the data and needs to be displayed in the query table. The design query function steps are as follows:

One: Define name

1. Enter the sheet "Query table", click A1 Cell, select the Insert name definition in the Menu toolbar, and open the Definition Name dialog box;

2. type "Fasten" at the name and type at the reference location:

"=offset" ( score sheet!) A1,1,,counta (score Table!) A:A)-1) "

Then click the Add button to complete the first name definition process.

3. Continue typing "grade" In the Name box, and type the following formula at the reference location:

"=offset !" $A $1,,1,,counta (INDIRECT) ("Score table!") & MATCH (query table! $A $, score sheet! $A: a,0) & ":" &match (query Table! $A $, score sheet! $A: a,0)))-1) "

Then click the Add button and close the window.

Tip: The current active cell position is important when you define a name. In this case, you need to select A1 and redefine the name.

The two formulas in this example have the following meanings:

1."=offset" ( score sheet!) A1,1,,counta (score Table!) A:A)-1) "

This formula refers to the A1 cell in the score table as the reference point, offset 1 rows, 0 columns (the default value is 1 when the second and third parameters of offset are ignored), and the height of the offset is minus 1 for the result returned by the CountA function, that is, the number of nonblank cells in column A is reduced by 1. This formula is used to increase or decrease the adaptive system. Formulas can be automatically extracted when a new department is added to the worksheet, such as "performing department"

2."=offset" ( score sheet!) A1,,1,,counta (INDIRECT) ("Score table!") & MATCH (query table!) A1, the score sheet! a:a,0) & ":" &match (Inquiry form!) A1, the score sheet! a:a,0)))-1) "

The function of this formula is to return its corresponding grade according to the A1 's system. The result is an array that contains multiple cell references. If A1 is a "literary department", then this formula produces an array of 1-4 grade. If A1 is a "law system", this formula will produce an array of 1-2 grade.

The operation of this formula is more complex. Can be divided into understanding. where the match function calculates the rank of the A1 cell in the query table in column A in the "score table" and returns its result to the indirect function as a row reference. The CountA function calculates the number of nonblank cells in the reference line, which controls the number of grades and the ability to use formulas adaptively. The entire formula will be referenced by the A1 cell in the score table, offset by 0 rows, 1 columns, and a range reference that has a height of 1 (the default value of offset's fourth parameter and fifth parameter is 1), and the width is the CountA function return value.

Two: Generate Drop-down menu

In order to achieve the Drop-down menu selection criteria to query results, we need to apply the name defined in the previous step to the data validation, resulting in a drop-down menu sequence for the user to select.

1. Select A1 in the query table, click Data Validation, and open the Data Validation dialog box;

2. in the Allow Drop-down list, select Sequence, and in the Source box, enter the formula "= Department", as shown in Figure B. Then click the OK button to return to the worksheet.

Figure B Setting data validation

3. Select B1 cells, repeat steps 1 and 2, and add data validation to B1, whose source formula is "= Grade".

Note: The formula in the source box must have a half-width equal sign, otherwise the correct result cannot be obtained.

Three: Design score formula The Drop-down list, you need to use a cell to display the query results.


1. Select the C1 cell and enter the following formula:


=index (score table!) A1:i100,match (A1, score sheet!) a:a,0), MATCH (B1, score sheet!1:1,0)) & ""

in this formula, the match function is used to calculate the rank of A1 in column A of "score table", and to calculate the rank of the B1 grade in the 1 rows of the "score table", and then return to the area through the two coordinates . a1:i100 The value of the corresponding cell in the

The "&" in the formula can convert 0 values to white space. If the region referenced by index is blank, the result is 0, and to convert the 0 value to white space, add "&" after the formula.

Four: Query

1. in cell A1 click, select "Law department" from the Drop-down list, as shown in Figure C;

2. in the cell B1 unit, the Drop-down list produces the corresponding two grades in the legal system. Select "Second Grade", in C1 cell will automatically produce query results: Foreign Language Department of the third grade 84. See figure D below;

3. Click A1 Select "Literature Department", then B1 drop-down List will produce four grades;

4. If you add a new line to the "score sheet" or add a grade number, the A1 and B2 Drop-down lists are automatically updated.

Figure C Select a system

Figure D Select grade to produce query results

Conclusion: When using the name matching number to generate Drop-down list, we use the OFFSET function's fourth parameter or the fifth parameter to specify a region reference to achieve. In order to adapt the formula to the increase or decrease of the data, the CountA function is usually used to get the number of nonblank cells in the row or column as an offset parameter. This example shows a combination of formula, name, data validation of the multifunctional, adaptive query system.

automatically generate queries. rar