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 summation function of the use of methods and techniques

excel function has a total of 11 categories, respectively, database functions, date and time functions, engineering functions, financial functions, information functions, logic functions, query and citation functions, mathematical and trigonometric functions, calculation functions, text functions, and user-defined functions. The following is the fall network Qiutian.ZqNF.Com editor for us to pack the use of excel summation function skills, I hope we can learn from it!

Tips for using the excel summation function:

Syntax: sum(number1, number2, …) .

Parameters: number1, number2 … For 1 to 30 numeric values (including logical values and text expressions), regions, or citations, the parameters must be separated by commas.

Note: Numbers, logical values, and text expressions of numbers in parameters can participate in the calculation, where logical values are converted to 1 and text is converted to numbers. If the parameter is an array or a quote, only the numbers in it participate in the calculation; blank cells, logical values, text or error values in the array or quote are ignored.

Sum function application examples: cross-table sum

The use of sum function in the same worksheet sum is relatively simple, if the demand for different worksheets of multiple regions for the sum, you can choose the following method: check excelxp “Insert Function” dialog box function, “OK” after the function, “Insert Function” dialog box. “OK” to open the “function parameters” dialog box. Switch to the first worksheet, mouse click on the “number1” box to select the need to sum up the region. If the other regions of the same worksheet need to participate in the calculation, you can click on the “number2” box, select the worksheet again to calculate the other regions. After the completion of the above operations, switch to the second worksheet, repeat the above operations to complete the input. “OK” after the formula where the cell will display the results of the calculation.

Sum function application example 2: sum function in the mixed operation of addition and subtraction

Financial calculations require a mixture of addition and subtraction, such as deducting a number of expenditure items in the cash flow statement. In accordance with the provisions of these items in the worksheet did not enter a negative sign. It is possible to construct a formula such as “=sum(b2:b6, c2:c9, -d2, -e2)”. Which b2:b6, c2:c9 cited as income, and d2, e2 for the expenditure. As excel does not allow the cell citation in front of the negative sign, so should be expressed in the expenditure of the cell before the negative sign, so that you can calculate the correct results. Even if the expenditure data where the cells are continuous, must also use commas to separate them one by one, written as “= sum(b2:b6, c2:c9, -d2, -d3, d4)” such a form.

Sum function application example three: the number of passing

If b1:b50 region to store the gender of students, c1:c50 cells to store a class of students’ test scores, to calculate the number of girls passing the exam. You can use the formula “= sum (if (b1:b50 = ′ female ′, if (c1:c50 & gt; = 60, 1, 0)))”, because it is an array formula, after the end of the input must be held down ctrl + shift key enter. The formula will be actively added on both sides of the curly brackets, in the edit bar shows “{ = sum (if (b1:b50 = ′ female ′, if (c1:c50 & gt; = 60, 1, 0)))}”, this is the use of array formulas are essential steps.

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.



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.

I hope the above explanation will help you.