Calc:使用函数
出自OOo 中文指南手册翻译
目录 |
Calc有超过350个函数帮助你分析引用数据。许多函数是以数字方式使用的，但另一些则是以日期和时间甚至文本的方式使用的。一个函数可以简单到将两个数字加在一起或查找一列数字的平均值。或者，复杂的如计算样本的标准偏差或数字的双曲正切。所有函数的列表见函数分类列表。 Calc includes over 350 functions to help you analyze and reference data. Many of these functions are for use with numbers, but many others are used with dates and times, or even text. A function may be as simple as adding two numbers together, or finding the average of a list of numbers. Alternatively, it may be as complex as calculating the standard deviation of a sample, or a hyperbolic tangent of a number. See a list of all functions in functions listed by category.
一般来说，函数名是对这个函数做什么的一个简短描述。例如，函数FV给出投资的将来值，BIN2HEX将二进制数字转换为十六进制数字。依传统，输入函数时字母全部大写，尽管Calc也能正确读取小写或大小写混合的函数。 Typically, the name of a function is an abbreviated description of what the function does. For instance, the FV function gives the future value of an investment, while BIN2HEX converts a binary number to a hexadecimal number. By tradition, functions are entered entirely in upper case letters, although Calc will read them correctly if they are in lower or mixed case, too.
一些基本函数与运算符近似。例： A few basic functions are somewhat similar to operators. Examples:
- +，该运算符将两个数字相加得到结果。SUM()则能将连续范围的数字相加。 :"+", this operator will add two numbers together for a result. SUM() on the other hand will add a contiguous range of numbers together.
- *，该运算符将数字相乘得到结果。PRODUCT()以乘法的方式做同样的事，SUM()则以加法的方式完成。 :"*", this operator will multiply to numbers together for a result. PRODUCT() does the same for multiplying, that SUM() does for adding.
每一个函数有若干参数用于计算。这些参数可能有或没有自己的名称。你的任务是输入执行函数所需的参数。在某些情况下，参数有预定义选择，你也许需要参考在线帮助或本书的附录B（函数说明）来理解他们。然而，往往参数是你手工输入的一个值，或者是电子表格的一个单元格或单元格范围里已经输入的值。在Calc中，你可以通过输入其他单元格的名字或范围来输入其中的值，或者通过鼠标选择单元格。随着单元格中的值改变，函数结果将自动更新。 Each function has a number of arguments used in the calculations. These arguments may or may not have their own name. Your task is to enter the arguments needed to run the function. In some cases, the arguments have pre-defined choices, and you may need to refer to the online help or Appendix B (Description of Functions) in this book to understand them. More often, however, an argument is a value that you enter manually, or one already entered in a cell or range of cells on the spreadsheet. In Calc, you can enter values from other cells by typing in their name or range, or by selecting cells with the mouse. Should the values in the cells change, the result of the function will be automatically updated.
为了兼容，Calc中的函数和参数与Microsoft Excel中对应的有相同的名字。然而，Excel和Calc都有对方所不具有的函数。偶尔地，Calc和Excel里同名的函数也有不同的参数，或具有相同参数的名字差异很小——但他们都不能互相导入。然而，可能9/10的函数可以在Calc和Excel之间互相导入不会有任何问题。 For compatibility, functions and their arguments in Calc have almost identical names to their counterparts in Microsoft Excel. However, both Excel and Calc have functions that the other lacks. Occasionally, too, functions with the same names in Calc and Excel have different arguments, or slightly different names for the same argument—neither of which can be imported to the other. However, perhaps nine-tenths of functions can be imported between Calc and Excel without any problems.
[编辑] 理解函数结构 Understanding the structure of functions
所有函数都有类似的结构。如果你使用正确的工具输入一个函数，可以避开学习这个结构，但为了解决问题也是值得获知的。 All functions have a similar structure. If you use the right tool for entering a function, you can escape learning this structure, but it is still worth knowing for troubleshooting.
给出一个典型的例子，查找符合输入的搜索条件的单元格，它的函数结构是： To give a typical example, the structure of a function to find cells that match entered search criteria is:
= DCOUNT (Database;Database field;Search_criteria)
因为一个函数不能单独存在，它必须是公式的一部分。因此，即使函数代表整个公式，也必须在公式的开头有=号。不管函数在公式的哪个位置，函数都以函数名开始，上例中的DCOUNT。在函数名后紧跟它的参数。所有参数都需要有输入值，除非特别列出是可选参数。 Since a function cannot exist on its' own, it must always be part of a formula. Consequently, even if the function represents the entire formula, there must be an "=" sign at the start of the formula. Regardless of where in the formula a function is, the Function will start with the function's name, DCOUNT in the example above. After the name of the function comes its arguments. All arguments are required, unless specifically listed as optional.
参数添加在括号（圆括号）中并以分号分隔。许多参数都将是数字。Calc函数能占用30个数字作为一个参数。刚听上去这可能不是很多。然而，如果你意识到数值不仅是一个数字或单元格，也可能是一个数组或包含几个甚至成百个单元格的范围时，那么明显的限制就消失了。 Arguments are added within the brackets (parentheses) and separated by semicolons. Many arguments will be a number. A Calc function can take up to thirty numbers as an argument. That may not sound like much at first. However, when you realize that the number can be not only a number or a single cell, but also an array or range of cells that contain several or even hundreds of cells, then the apparent limitation vanishes.
依赖于其性质，函数参数可以输入如下： Depending on the nature of the function arguments may be entered as follow:
"文本数据" 引号确定输入的是文本或字符串数据 The quotes establish that text or string data has been entered 9 本例中数字9作为数值输入 In this case the number nine has been entered as a number "9" 本例中数字9作为文本输入 In this case the number nine is being entered as text A1 输入的是单元格A1的地址，无论A1里面有什么 The address for whatever is in Cell A1 is being entered
[编辑] 嵌套函数 Nested functions
函数也可以作为其他函数的参数来使用。 Functions can also be used as arguments within other functions.
=SUM(2;PRODUCT(5;7))
要想知道嵌套函数能做什么，设想正在设计一个自主学习模块。模块中，学生做了三个小测验，并分别在单元格A1、A2、A3中输入结果。在A4中，你可以创建一个嵌套公式通过 =AVERAGE(A1:A3)计算测验结果的平均值。公式用IF函数根据测验的平均成绩给学生反馈。整个公式如下： To get an idea of what nested functions can do, imagine that you are designing a self-directed learning module. During the module, students do three quizzes, and enter the results in cells A1, A2, and A3. In A4, you can create a nested formula that begins by averaging the results of the quizzes with the formula =AVERAGE(A1:A3). The formula then uses the IF function to give the student feedback that depends upon the average grade on the quizzes. The entire formula would read:
=IF(AVERAGE(A1:A3) >85; "恭喜！你准备前进到下一模块"; "失败了。请再次复习材料。如果需要，联系你的导师寻求帮助")
=IF(AVERAGE(A1:A3) >85; "Congratulations! You are ready to advance to the next module"; "Failed. Please review the material again. If necessary, contact your instructor for help")
根据平均值，学生将收到祝贺或失败的消息。 Depending on the average, the student would receive the message for either congratulations or failure.
如果你对电子表格没有经验，最好的办法是将函数作为脚本语言来思考。我们使用简单的例子来解释得更清楚些，但是通过函数嵌套，Calc公式可以变得很复杂。 If you are new to spreadsheets, the best way to think of functions is as a scripting language. We've used simple examples to explain more clearly, but, through nesting of functions, a Calc formula can quickly become complex.
3.1的更新 New in 3.1
当你输入公式时Calc会将公式的语法显示在单元格旁的提示文本里，这是很方便的辅助记忆工具。 Calc now keeps the syntax of a formula displayed in a tooltip next to the cell as a handy memory aid as you type.
一个更可靠的方法是使用函数列单。从插入菜单里，函数列表作为面板自动停靠在Calc编辑窗口的右侧。如果你愿意，可以按住Ctrl键在面板顶部空白处双击释放面板，使其成为浮动窗口。 A more reliable method is to use the Function List. Available from the Insert menu, the Function List automatically docks as a pane on the right side of the Calc editing window. If you wish, you can Control+double-click on a blank space at the top of the pane to undock this pane and make it a floating window.
函数列表包括每一个函数和参数的简要说明；高亮该函数在面板的底部就会看到描述。如果需要，将光标悬停在列表和描述之间的分割线上；当光标变成双箭头时，向上拖动增加描述的空间。双击函数名将其和每一个参数的占位符一起添加到当前单元格里。 The Function List includes a brief description of each function and its arguments; highlight the function and look at the bottom of the pane to see the description. If necessary, hover the cursor over the division between the list and the description; when the cursor becomes a two-headed arrow, drag it upwards to increase the space for the description. Double-click on a function’s name to add it to the current cell, together with placeholders for each of the function’s arguments.
使用函数列表和手工输入一样快，且优点是不需要你记住要用的公式。理论上，也更不容易出错。实际上，有些用户用数值替换占位符来摸索。另一方便的特性是可以显示最后使用的公式。 Using the Function List is almost as fast as manual entry, and has the advantage of not requiring that you memorize a formula that you want to use. In theory, it should also be less error-prone. In practice, though, some users may fumble when replacing the placeholders with values. Another handy feature is the ability to display the last formulas used.
最常用的输入方式是函数向导。要打开函数向导，选择插入>函数，或点击函数工具栏上它的按钮，或者按Ctrl+F2。一旦打开，函数向导提供和函数列表一样的帮助特性，但是添加了字段可以看见完整函数的结果，即使它仅是任意长公式的一部分也一样。 The most commonly used input method is the Function Wizard. To open the Function Wizard, select Insert > Function, or click its button on the Function tool bar, or press Ctrl+F2. Once open, the Function Wizard provides the same help features as the Function List, but adds fields in which you can see the result of a completed function, as well as the result of any larger formula of which it is part.
你也可以选择结构标签查看公式某部分的树状视图。超越函数列表的主要优点是每一个参数都在自己的字段里输入，这样易于管理。可靠的代价是输入较慢，但是付出的代价往往很小，在创建电子表格时毕竟精确是比速度更重要的。 You can also select the Structure tab to see a tree view of the parts of the formula. The main advantage over the Function List is that each argument is entered in its own field, making it easier to manage. The price of this reliability is slower input, but this is often a small price to pay, since precision is generally more important than speed when creating a spreadsheet.
函数向导的另一个优点是当添加每一个参数时可以减少窗口。在每一个参数的输入字段后是收缩按钮
。收缩按钮临时移开向导除当前输入字段外的其他部分，这样你可以看到下面的电子表格。当你输入完参数的值后，再次点按钮返回整个函数向导。 Another advantage of the Function Wizard is that you can reduce the window as you are adding each argument. At the end of each input field for an argument is the Shrink button . The Shrink button temporarily removes all parts of the wizard except the current input field so that you can see the spreadsheet beneath. When you are finished entering the argument’s value, click the button again to return to the entire Function Wizard.
最后，正如OpenOffice.org里的许多其他特性一样，你可以为任何喜爱的函数公式创建宏，并分配到工具栏、菜单或组合按键里。 Finally, as with many other features in OpenOffice.org, you can create a macro for any favorite function formula, and assign it to a tool bar, menu, or keystroke combination.
不管你怎样输入一个公式，在使用它以前最好花一点时间来检查它的结构。如果它看起来正确了，按Enter键或从函数工具栏里选择采用按钮将其添加到单元格并获得结果（采用按钮在输入栏旁是个绿色对勾）。 No matter how you enter a formula, take a moment to check its structure before using it. If it looks right, press the Enter key or select the Accept button from the Function toolbar to add it to the cell and get its result (the Accept button is the green check mark beside the Input line).
如果你看到的是单元格里的公式而不是结果，那么是在工具>选项>OpenOffice.org Calc>视图>显示里选择了显示公式。去掉勾选，将显示结果。然而，你仍能在公式栏字段里看到公式。 If you see the formula in the cell instead of the result, then Formulas are selected for display in Tools > Options > OpenOffice.org Calc > View > Display. Unselect the checkbox beside it, and the result will display. However, you can still see the formula in the formula bar field.
[编辑] 进度表
| Calc:使用函数 中文化进度 | 翻译 | 0 | 校对 | 0 | 插图 | / | 上次编辑 | 20100719083416 | 编者 | Yookee |
