当前位置:首页 > 办公软件 > Excel教程 > Excel2010导学(公式基础)

excel2010公式教程 Excel2010导学(公式基础)

2016-10-17 | 来源:网络 | 关键字:
Excel can be used to calculate numerical information.

Excel的掌握要从基本的概念出发

Excel2010导学(公式基础)

Excel can be used to calculate numerical information. In this lesson, you will learn how tocreate simple formulasin Excel to add, subtract, multiply, and divide values in a workbook. You'll also learn the various ways you can usecell referencesto make working with formulas easier and more efficient.Excel通常用来计算一些数值型信息。下面,我们介绍在Excel中创建简单的公式(加,减,乘,除),及单元格在公式里的引用,使得工作高效,便捷。

Aformulais an equation that performs a calculation. Like a calculator, Excel can execute formulas that add, subtract, multiply, and divide.公式是一个方程,能够进行计算,就像计算器,Excel可以执行简单的加,减,乘,除运算。

One of Excel's most useful features is its ability to calculate using a cell address to represent the value in a cell. This is called using a cell reference.单元格的引用,使得用户可以用引用的方式,表征单元格的值。

To maximize the capabilities of Excel, it is important to understand how tocreate simple formulasanduse cell references.为了挖掘Excel的功能,下面两块内容很重要:创建简单的公式使用单元格

Creating simple formulas

Excel uses standard operators for equations, such as aplus signfor addition (+),minus signfor subtraction (-),asteriskfor multiplication (*),forward slashfor division (/), andcaret(^) for exponents.Excel在方程中使用标准的操作符如+,-,*,/(分别代表加减乘除), 及^(乘方)

The key thing to remember when writing formulas for Excel is that all formulas must begin with anequals sign(=). This is because the cell contains—or is equal to—the formula and its value.写公式有一点很重要,就是打头的符号为=(别忘记输入这个符号呀!) ,因为单元格是存放公式的运算结果或者值。下面的运算结果,大家可以口算一下,上机测试一下。

Excel2010导学(公式基础)

To create a simple formula in Excel创建简单公式:

    Select the cell where the answer will appear(B4,for example).选择要输入公式的单元格,如B4。

    Excel2010导学(公式基础)

    Type theequals sign (=).首先输入等于号

    Type in the formula you want Excel to calculate (75/250, for example)./然后输入要运算的公式,如75/250

    Excel2010导学(公式基础)

    PressEnter. The formula will be calculated, and the value will be displayed in the cell.按回车键,公式将运算出来,结果会出现在单元格中。

    Excel2010导学(公式基础)

If the result of a formula is too large to be displayed in a cell, it may appear aspound signs(#######) instead of a value. This means the column is not wide enough to display the cell content. Simplyincrease the column widthto show the cell content.如果出现很多的井字号(#######),表示列宽不够,显示不了所有信息,你要做的事情就是增加列宽即可。

Creating formulas with cell references公式中引用单元格

When a formula contains a cell address, it is called acell reference. Creating a formula with cell references is useful because you can update data in your worksheet without having to rewrite the values in the formula.当公式中包含单元格地址时,叫做单元格引用。那么引用单元格,与直接输入计算的值有什么区别呢?如何单元格引用,单元格中的值在修改时,公式本身不需要修改,这样可以大大节省编辑的时间。

To create a formula using cell references举例:

    Select the cell where the answer will appear (B3,for example).选择要进行计算的单元格。

    Excel2010导学(公式基础)

    Type theequals sign (=).输入等于号(=)

    Type the cell address that contains the first number in the equation (B1,for example).输入单元格地址,如B1。

    Excel2010导学(公式基础)

    Type the operator you need for your formula. For example, type theaddition sign (+).输入一些公式的中运算符,如+。

    Type the cell address that contains the second number in the equation (B2,for example).输入其它的单元格地址,如B2。

    Excel2010导学(公式基础)

    PressEnter. The formula will be calculated, and the value will be displayed in the cell.公式结束时,输入回车键,公式会计算出结果并显示在单元格中。

    Excel2010导学(公式基础)

If you change a value in either B1 or B2, the total will automatically recalculate.如果你修改某个单元格的值(如B1,B2)时,公式会被重新计算,并显示出来。

Excel2010导学(公式基础)

Excelwill not always tell youif your formula contains an error, so it's up to you to check all of your formulas. To learn how to do this, you can read theDouble-Check Your Formulaslesson from ourExcel Formulastutorial.Excel不会告诉你公式中的错误,所以你要对公式中的内容进行检查。

To create a formula using the point-and-click method使用点击方式创建公式:

    Select the cell where the answer will appear (B4,for example).选择目标单元格。

    Excel2010导学(公式基础)

    Type theequals sign (=).输入等号

    Click thefirst cellto be included in the formula (A3,for example).点击第一个单元格,省去输入的环节。

    Excel2010导学(公式基础)

    Type the operator you need for the formula. For example, type themultiplication sign (*).输入公式中的运算符,如*。

    Click thenext cellin the formula (B3,for example).点击第二个单元格如B3。

    Excel2010导学(公式基础)

    PressEnter. The formula will be calculated, and the value will be displayed in the cell.按回车键,公式计算结果并显示在目标单元格。

    Excel2010导学(公式基础)

To edit a formula公式的编辑:

    Click the cell you want to edit.点击要编辑的单元格。

    Insert the cursor in theformula bar, and edit the formula as desired. You can alsodouble-click the cell to view and edit the formula directlyfrom the cell.在公式栏中放置光标,编辑公式,也可以直接双击单元格,在其中直接修改内容。

    When you're done, pressEnteror select theEntercommandExcel2010导学(公式基础).

    Excel2010导学(公式基础)

    The new value will be displayed in the cell.单元格立即显示修改后运算的结果。

    Excel2010导学(公式基础)

If you change your mind, use theCancelcommandExcel2010导学(公式基础)in the formula bar to avoid accidentally making changes to your formula.如果不小心误修改,可以通过取消按钮。

Simple formulas have one mathematical operation, such as5+5.Complex formulashave more than one mathematical operation, such as5+5-2. When there is more than one operation in a formula, theorder of operationstells us which operation to calculate first. To use Excel to calculate complex formulas, you'll need to understand the order of operations.简单的公式,如5+5。复杂的公式有二个以上的运算符号,如5+5-2。如果运算符,就涉及到运算符的优先级。

The order of operations运算符的优先级

Excel calculates formulas based on the followingorder of operations:Excel优先级的情况。

    Operations enclosed inparentheses 括号(优先1)

    Exponentialcalculations (to the power of) 指数符(优先2)

    Multiplicationanddivision, whichever comes first 乘、除符(优先3)

    Additionandsubtraction, whichever comes first 加、减符(优先4)

A mnemonic that can help you remember the order isPleaseExcuseMyDearAuntSally.你可以通过助记符:

请原谅我亲爱的阿姨Sally(最好别翻译噢!)

Example 1例子1

The following example demonstrates how to use the order of operations to calculate a formula:下面的例子是运算优先级的示例。

Excel2010导学(公式基础)

Example 2例子2

In this example, we'll review how Excel will calculate a complex formula using the order of operations. The selected cell will display the percent of total Pete Lily seeds sold that were white.来看一个更加复杂的运算符优先级的例子。Pete Lily卖出的白色的种子的百分比。

Excel2010导学(公式基础)

    First, Excel will calculate the amount sold in parentheses:(19*1.99)=37.81White Pete Lily seeds and(33*1.99)=65.67Total Pete Lily seeds.先计算白色种子的总金额,所有种子的金额

    Second, it will divide the White Pete Lily seeds amount by the Total Pete Lily seeds amount:37.81/65.67=.5758. ,然后两者相除即可。

    Last, it will multiply the result by 100 to obtain the value as a percent:.5758*100=57.58.然后这个百分数乘以,得到结果。

Based on this complex formula, the result will show that57.58%of the total Pete Lily seeds sold were white. You can see from this example that it is important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately.从上面的例子中,大家可以看到,运算顺序的重要性。如果运算顺序不准确,会导致结果的偏差。

To create a complex formula using the order of operations:

In this example, we'll usecell referencesin addition to actual values to create a complex formula that will add tax to the nursery order.下面我们会在例子中引入单元格的引用。

    Click the cell where you want the formula result to appear (F11, for example).单击所有编辑公式的单元格,如F11。

    Type theequals sign (=).输入等于号

    Type anopen parenthesis, then click the cell that contains the firstvalueyou want in the formula (F4, for example).在公式中输入左小括号,点击要引用的单元格。

    Type the firstmathematical operator(the addition sign, for example).输入一些数学运算符号

    Click the cell that contains the secondvalueyou want in the formula (F5,for example), then type aclosed parenthesis.引用第二个单元格,输入右小括号。

    Type the nextmathematical operator(the multiplication sign, for example).输入第二个数学运算符号。

    Type the nextvaluein the formula (0.055for5.5% tax, for example).输入参加运算的数字。

    Excel2010导学(公式基础)

    ClickEnterto calculate your formula. The results show that $2.12 is the tax for the nursery order.按回车键。得到运算结果$2.12 。

    Excel2010导学(公式基础)

Excelwill not always tell youif your formula contains an error, so it's up to you to check all of your formulas. To learn how to do this, you can read theDouble-Check Your Formulaslesson from ourExcel Formulastutorial.

Working with cell references单元格引用

In order to maintain accurate formulas, it is necessary to understand how cell references respond when you copy or fill them to new cells in the worksheet.确保公式的正确性,很有必要掌握单元格的知识,特别在复制与填充单元格时。

Excel will interpret cell references as eitherrelativeorabsolute. By default, cell references arerelative references. When copied or filled, they change based on the relative position of rows and columns. If you copy a formula (=A1+B1) into row 2, the formula will change to become (=A2+B2).单元格引用有相对引用和绝对引用两种。 相对引用:当复制或者填充单元格时,单元格引用会根据行与列的位置,发生变化。比如,第一行中的A1+B1,复制到第二行时,公式会变化成A2+B2。Excel2010导学(公式基础)

Absolute references, on the other hand, do not change when they are copied or filled and are used when you want the values to stay the same.绝对引用:当单元格复制或者填充时,单元格的地址不用发生变化,保持原来的值。

Relative references相对引用

Relative references can save you time when you're repeating the same type of calculation across multiple rows or columns.在多行或者多列进行运算时,并且人运算的公式相似时,相对引用的使用可以节省你大量的时间。

In the following example, we're creating a formula with cell references in row 4 to calculate the total cost of the electric bill and water bill for each month (B4=B2+B3). For the upcoming months, we want to use the same formula with relative references (C2+C3, D2+D3, E2+E3, etc.). For convenience, we can copy the formula in B4 into the rest of row 4, and Excel will calculate the value of the bills for these months using relative references.下面的例子主要说费用计算时,只要做一个样本的公式即B4=B2+B3,其它的月份,只要使用粘贴或者填充即可。

To create and copy a formula using relative references:使用相对引用,创建与复制公式

    Select the first cell where you want to enter the formula (B4, for example).先对B4单元格进行操作。

    Excel2010导学(公式基础)

    Enter the formula to calculate the value you want (B2+B3, for example).输入公式B2+B3

    Excel2010导学(公式基础)

    PressEnter. The formula will be calculated.按回车键,公式运算并得到结果。

    Excel2010导学(公式基础)

    Select the cell you want to copy (B4, for example), then click theCopycommand from theHometab.复制B4单元格内容。

    Select the cells where you want to paste the formula, then click thePastecommand from theHometab. You can also drag the fill handle to fill cells.把B4单元格的内容复制或者填充到其它单元格,任务完成。

    Excel2010导学(公式基础)

    Your formula is copied to the selected cells as a relative reference (C4=C2+C3, D4=D2+D3, E4=E2+E3, etc.), and the values are calculated.相对引用让工作更方便与轻松。

Absolute references绝对引用

There may be times when you do not want a cell reference to change when copying or filling cells. You can use an absolute referenceto keep a row and/or column constant in the formula.

相对引用使用的场合比较多,但有时候也会用到绝对引用,这时候复制或者填充单元格时,单元格中的行与列的位置不会发生改变。

An absolute reference is designated in the formula by the addition of adollar sign ($). It can precede the column reference, the row reference, or both.绝对引用设计时,要在公式中单元格的列号或者行号前面添加$符号,可以添加一个,也可以添加两个。

Excel2010导学(公式基础)

In the below example, we want to calculate the sales tax for a list of products with varying prices. We'll use an absolute reference for the sales tax ($B$1) because we do not want it to change as we are copying the formula down the column of varying prices.下面的例子说明绝对引用的使用,请仔细阅读。

To create and copy a formula using an absolute reference使用绝对引用创建或者复制公式:

    Select the first cell where you want to enter the formula (C4, for example).选择单元格

    Excel2010导学(公式基础)

    Type an equals sign, and then click the cell that contains the firstvalueyou want in the formula (B4, for example).输入等于符号,并输入第一个单元格。

    Type the firstmathematical operator(the multiplication sign, for example).输入运算符,如*

    Type thedollar sign ($), then enter thecolumn letterof the cell you are making an absolute reference to (B, for example),输入$符号,然后输入列号,说明此列内容保持不变。

    Excel2010导学(公式基础)

    Type thedollar sign ($), then enter therow numberof the same cell you are making an absolute reference to (1, for example).再输入$符号,然后输入行号,说明行号不变。

    Excel2010导学(公式基础)

    PressEnterto calculate the formula.按回车键,计算出结果。

    Excel2010导学(公式基础)

    Select the cell you want to copy (C4, for example), then click theCopycommand from theHometab.复制内容。

    Select the cells where you want to paste the formula, then click thePastecommand from theHometab. You can also drag the fill handle to fill cells.粘贴复制的内容,或者进行填充操作。

    Excel2010导学(公式基础)

    Your formula is copied to the selected cells using the absolute reference (C5=B5*$B$1, C6=B6*$B$1, etc.), and your values are calculated.得到的公式全为绝对引用,所有的结果立即出现在目的单元格中。

When writing a formula, you can press theF4key on your keyboard to switch between relative and absolute cell references. This is an easy way to quickly insert an absolute reference.

小技巧,你在输入公式时,可以按F4键,可以帮助你输入$键,多次按键还可以在相对引用与绝对引用之间进行切换(共4种状态)。


评论

评论数10

表情
发表评论
网友评论仅供其表达个人看法,并不表明网易立场。
《Excel2010导学(公式基础)》更多评论

头条推荐

阅读下一篇

Excel格式刷高阶使用技巧三则

第一,excel2013格式刷快捷键的添加方法  1:右键点击“格式刷”按钮,点击添加到快速访问工具栏。此时 ... 查看全文

返回Excel教程 返回网站首页