Using ET table to create a more intuitive correlation function of students ' score analysis

A few days ago in the forum sent a post: "Use et form to create more intuitive student performance analysis" (hereinafter referred to as "score analysis"), http://bbs.wps.cn/thread-21963426-1-1.html. Many altar friends are interested in the functions involved in the text. I'll send you another one today. Explain some of the related functions as a supplement to the previous text.

The functions involved in the previous article are mainly: SUMIF, COUNTIF, Sumproduct, VLOOKUP. These functions are often used in statistical analysis of scores, which can be useful for teachers. We have one by one ways to come.

First, SUMIF function

The role of the SUMIF function is to sum several cells, ranges, or references based on a specified condition. Its syntax is SUMIF (a range of cells used for conditional judgment, a decision condition consisting of numbers, logical expressions, etc.), a cell, range, or reference that needs to be summed. Take the table shown in Figure 1 as an example.

Figure 1

We want to display the scores of the 2 students in the table in the D13 cell. The analysis can see the student's class in the B2:b11 cell area, and the language score is distributed in the D2:d11 cell area. So, according to the syntax of the SUMIF function, we only need to enter the formula in D13 cell =SUMIF ($B $: $B $11, "Class 2, D2:d11)" on it. The parameter "Class 2" is the judgment condition, $B $: $B $11 is the range of cells that provide logical judgment, and D2:D11 is the range of cells that are actually summed. Therefore, the formula "=SUMIF" ($B $: $B $11, "Class 2", D2:d11) "can be translated to: in the B2:b11 cell value is" Class 2 ", the corresponding D column cell data are summed.

In the analysis of results, the formula "=SUMIF ($B: $B, $Q $3,d:d)" is easy to understand: The value in column B is equal to the Q3 cell, and the corresponding D column cell is summed.

Second, countif function

The role of the Counit function is to calculate the number of cells in a range that meet a given condition. Syntax is similar to the SUMIF function: COUNTIF (a statistical condition for the range of cells in which you want to calculate the number of cells that meet the criteria). Where statistical conditions can be numeric, tabular, or text. The simple understanding is countif (where to count, according to what counts).

The table shown in Figure 1 is still taken as an example. If we enter the formula "=countif" ($B $: $B $11, "Class 2"), then naturally you can get the number of cells in the B2:B11 range with the value "Class 2". Therefore, D14 cell to Statistics 2 class language average score is much simpler, only need to input formula "=d13/countif ($B $: $B $11," 2 classes ")" OK.

Three, sumproduct function

This function can be used for multiple condition counts, that is, to calculate the number of cells that meet 2 or more criteria. Its syntax is sumproduct (condition 1) * (Condition 2) * (condition ...)). The table shown in Figure 2.

Figure 2

If we want to count the number of male teachers with the title "Medium High" in the table, just enter the formula "=sumproduct" (q2:q11= "Male") * (r2:r11= "medium High") in the cell. I believe that the meaning of the formula is clear in comparison with tables and formulas.

The table in Figure 1 shows "class" in column B and the language score in column D. If you want to calculate the pass rate for the 2-class language subjects, you need to first count the number of cells that meet the two criteria. Conditions 1:B listed as "Class 2", the Condition 2:d column is greater than or equal to 60 points. The formula "=sumproduct ($B $: $B $11=" 2) * (d2:d11>=60)) "can meet the requirements, and then divide by the number (COUNTIF ($B $: $B $11," Class 2 ")) is not the pass rate?

Performance analysis of the "excellent rate" is such statistics, but the score from "60" to set the results on the line.

Four, VLOOKUP function

The role of the VLOOKUP function is to find a specified number in the first column of a table or array of values, and thus return the numeric value at the specified column in the current row of the table. Its syntax is VLOOKUP (lookup values, data tables, column ordinals, matching criteria). A "lookup value" refers to a value that needs to be looked up in the first column of a datasheet, which can be a numeric value, a reference, or a string of text. Datasheet is a reference to a range or range name for a datasheet in which you want to find data. Column ordinal is the column ordinal in the datasheet where the matching data is to be returned. The match criterion is FALSE to return an exact match, and if TRUE or omitted, an approximate match is returned, that is, if an exact match is not found, the maximum value less than the lookup value is returned.

Or, for example, more clearly. Suppose we want to know what the title of "Teacher 04" is in the table shown in Figure 2. Then we can enter the formula in the cell "=vlookup" ("Teacher", P2:r11,3,false), and the result is the carriage return, as shown in Figure 3.

Figure 3

The meaning of a formula is to find a cell in the first column of the P2:r11 range that has the value "teacher 04" and return the third column of data in its row. A comparison of the table is clear.

In the VLOOKUP function in "score analysis", another function column () is used, which returns the number of columns in which the cell is located. For example, the formula "=column (D3)" Result is "4". Column (), without any arguments, returns the number of columns in which the current cell is located.

All right, the functions involved in the analysis of grades are basically finished. How to have the opportunity to practice, must be able to appreciate the fun of using these functions.