# excel function application examples lesson plan

### excel function multiplication how to use two methods to teach you to use

Excel tables support simple calculations and statistics. There are two ways to achieve this effect, respectively, the PRODUCT function method and multiplication function method. Here let me take WPS11.11.10 version as an example to show you how to operate.

Method 1: PRODUCT function method

Step 1: Select any cell, in the upper edit box, enter [= PRODUCT (cell one, cell two, cell three, ……)] function. In this example, we enter [= PRODUCT (A1, B1)] function, and then click Enter.

Step 2: The visible product of the two cells has been displayed in the blank cell.

Method 2: multiply the number of function method

Step 1: Select any cell, in the upper edit box, enter [= cell one * cell two * cell three * ……] function. In this example, we enter [= A1 * B1] function, and then click Enter.

Step 2: The visible product of the two cells has been displayed in the blank cell.

Tip: The PRODUCT function used in Method 1 can only support the calculation of the product of up to 30 data values, but Method 2 does not limit the number of data.

### EXCEL formula lesson plan

Formulas in Excel (lesson plan)

Subject: formulas in Excel

Teaching time: teaching class:

Teaching objectives: to master the formula input format and calculations, the representation of computer mathematical operators, to be able to use formulas for routine Excel data calculations, understanding of the Excel in several commonly used functions

Teaching focus: formula input format and calculation, computer mathematical operators representation

Teaching difficulties: formula input format, Excel function

Teaching Methods: Lecture, Demonstration

Teaching Process:

Organization of Teaching and Learning

Two, Review Introduction

Previous session we learned the beautification of the worksheet in Excel, this session we began to learn the method of calculating data in Excel in the formula calculation.

Three, teaching a new lesson

1. Enter the formula

Excel is a statistical data and analysis of applications, statistics and analysis of the way to achieve the main means of calculating, we will now begin to learn the formula in Excel input.

Steps: (to sum, for example, calculate the ‘Wang Xiaohong’ of the total score)

1) selected to store the total score of the cell (demonstration of the specified cell)

2) enter the formula

3) Enter to determine

2. Input formula format

2. p>

Format 1: <=>[< cell application > < operator >, < cell application > < operator > ……]

Example: Calculate the total grades of Wang Xiaohong, in cell G3 enter “=C3+D3+E3+F3”

Note that “=” in the format; cell references should be correct (mathematical operators in computers)

Format 2: <=>[<Function Expression& gt;]

Example: Calculate the total score of Wang Xiaohong, in cell G3 enter “= SUM (C3:F2)”

(Excel function use and function parameter format)

Of course, you can also be in the cell directly using the formula will be the result of the accumulation of data, but If the results obtained using such a method has certain defects? (The concept of dependent cells)

3. Mathematical operators in computers

Mathematical operators in computers differ from our normal mathematical operators:

Operators Mathematical operators in computers Ordinary mathematical operators

Plus + +

Decrease – –

Multiply by * ╳

Divided by / ÷

Computers do not have the concept of curly brackets, parentheses and brackets, only brackets ()

4. Functions in Excel

The use of formulas to calculate the advantages of simplicity, but for the cell is too much in the handling of the case of more cumbersome, Excel provides a function to enter the calculation method. The main functions are: SUM, AVERAGE, COUNT, MAX, MIN and so on. (Function content for the next lesson focus)

5. Slave cells

Cells containing the formula is called slave cells.

Subordinate cells is an important concept in Excel cells, its main feature is that as long as the formula is referenced in the number of cells modified from the number of cells will automatically follow the change. (Example)

In the previous example, the sum of the data, we can quote both the cell can also use the direct data, when the source data change how the results will be? (Demo)

Conclusion: For ease of use, try to avoid referencing direct data.

IV. Exercise

Use the formula method to calculate the total and average grades of all students.

V. Summary.

### Excel COUNTIFS function and examples of the use of

excel is a data processing tool, the next step is to explain a function that can directly screen the number of cells that meet the conditions, that is, the use of COUNTIFS.

tools/materials

excel

COUNTIFS function of the basic structure is:

=COUNTIFS (condition matching query region 1, condition 1, condition matching query region 2, condition 2, ……)

Example, count the number of all absentees in a table. It is known that the absentees have a note “Absent” in column F and the numbers in columns c, d, and e are all less than zero.

First of all, we choose the first parameter is f, the second parameter selection is also written f, that we filter the scope of the data is the f column, and then write the conditions of missing exams, at this time we have the meaning of this command is to count the number of missing exams in the f column.

Input the formula in the cell = countif (f: f, “missing”) and enter, you can count the number of missing cells in the f column.

First of all, we choose the first parameter c, the second parameter also choose c, that is, the statistical range of c columns, at this time we write the conditions to pay attention to the keyboard adjusted to the state of the English language in capital letters, the entire condition should be written in double quotes such as “<0”, and not written as “

Enter the formula = countif (c:c, “<0”) Enter, you can count all the c column less than zero in the number of cells.

We are still with the above steps to write the first screening range, choose to screen the data in column e, the subsequent operation with the above steps, special attention should be paid to if we want to use less than or equal to such conditions, to be written as “<=”, and do not write “≤ “.

Enter the formula for = countif (d:d, “<0”) Enter, you can count all the d column less than zero in the number of cells.

Or enter the formula = countif (e:e, “<0”) Enter, you can count the number of cells less than zero in all e column.

We can also add the text wildcard * (asterisk symbol) to the condition.

The * symbol can represent any text, which helps us match the condition we want from multiple characters (or statements).

After following the above steps, you can display the number of absentees.