Ceci est une ancienne révision du document !
In past articles, I have discussed and used functions to illustrate other functionality in Calc, but today, I am going to show you three different ways to enter functions. I'll show you the structure of a function; we will create data for a spreadsheet; then I will apply each of the input methods: Function Wizard, Function List, and manual entry.
Structure of a Function
It helps to understand the structure of functions if you plan to use them. I will use the following function for my discussion of function structure:
=PRODUCT(B5, A1:A6, 0.25)
Functions are always a part of a formula. When you use any formula or function, it must begin with an equals sign (
. If you use multiple functions, the equals sign is required only at the beginning.
The start of a function is the function name. By tradition, function names are entered in all upper-case letters, but Calc will recognize them in lower or mixed case letters. Keeping with the tradition, I usually enter function names in all capitals. The name of our function in the example is PRODUCT. PRODUCT is to multiplication what SUM is to addition, it multiplies all its arguments into a final total.
After the function name is the argument list, separated by commas, and surrounded by parenthesis. This is the (B5, A1:A6, 0.25) part of our sample function. Arguments can come in several forms, and the function will usually expect a certain type in each position of the arguments. Arguments can take the form of numbers (9), “Quoted text”, cell reference (C3), cell range (C3:C10), comparisons (C3 > C1), or another function. Note that quotes around a number, “9”, defines the argument as text – and not a number.
Dans le passé, j'avais présenté et utilisé des focntions pour illustrer d'autres focntionnalités de Calc, mais aujourd'hui, je vais vous montrer 3 façons différentes de saisir des fonctions. Je vous montrerai la structure d'une fonction ; nous créerons des données pour une feuille de calcul ; puis j'appliquerai chacune des méthodes de saisie : L'assistant Fonctions, la liste de fonctions et l'entrée manuelle.
Setting Up the Sheet I work with installers of controlled access systems. When setting up a new system, it is necessary to calculate how many power supplies are needed in order to power the equipment for the site. We use a formula for calculating the voltage drop at each device. The calculation involves the input voltage, the current draw of the device plus the current draw of any devices after it, multiplied by the resistance of the length of wire to the device. The basic formula is Vout = Vin – I(DR) where Vin is the input voltage, I is the current draw of the device and any devices after it, D is the length of wire (in feet), and R is the resistance of the wire per foot. Let's set up a sheet to help us calculate the voltage at each device. Start with the label “Start Volts” in cell A1. In cells A2:E2 put the following column headings: Device, Amps, Distance, Ohms/Foot, and Voltage. In cell B1 put 13.2 for your starting voltage. In A3:A5 put 1, 2, 3 for the devices. B3:B5 are the current draw for each device. Use 0.3, 0.25, and 0.5. The three distances for the wire are 75, 110, and 120. For the Ohms/Foot, use 0.00639 for all three. This is the approximate Ohms per foot for 16 AWG (US). Leave the Voltage column blank. This is where we will enter our formulas.
Function Wizard The function wizard is the most complete method for entering formulas with functions. It is also the slowest because of the many options. The wizard is a great way to work through the set up of a complicated formula by allowing you to deal with individual pieces of information one at a time. We will use the wizard to create the voltage formula for the first device. There are three ways to access the Function Wizard. Select cell E3 and do one of the following: • Click the Function Wizard button on the formula toolbar. • Insert > Function… • CTRL + F2 The Function Wizard displays a function list box to the left. The Category drop-down list allows you to narrow the functions in the list to the selected category. There is also a Last Used category for selecting recently used functions. If you single-click on a function name, it displays a short description of the function to the right. When you double-click on a function, it inserts the function into the formula text box on the bottom right. Notice that the wizard has already inserted the equals sign for you. The right center displays text boxes for entering the arguments for the function. Above the argument boxes, it displays the short description and a list of the arguments and their type. The top right shows the results of the current formula and the formula result is displayed above the formula edit box. Our formula starts with the voltage coming into the device. For the first device this is 13.2 from cell B1, so click into the formula text box at the bottom and type “B1-”. From the category drop down list select Mathematical. Scroll down the list and double-click PRODUCT. The PRODUCT function is added to the formula.
Click into the Number 1 text box. The first argument is the sum of all the amps for all the devices. Click on the Function Wizard button to the left of the Number 1 text box. This gives you a blank function wizard screen. Note that you now have Back and Next buttons at the bottom. Select Mathematical from the category list, but this time double-click the SUM function. Click into the Number 1 text box. Use your mouse to select cells B3:B5. The range is added to the Number 1 text box for SUM. Click Back twice to return to the PRODUCT function we started with. Notice that the SUM function is now in the Number 1 text box. Select the Number 2 text box. Double-click on PRODUCT again. In the Number 1 text box for the second PRODUCT function, enter or select the range C3:D3. We are now finished with our formula. The final formula should look like =B1-PRODUCT(SUM(B3:B5),PRODUCT(C3:D3)) Click OK to finish the wizard. NOTE: I could have used the multiplication operator (*) to accomplish the same thing in the PRODUCT function, but I used the function in order to illustrate the ability to use functions as arguments to other functions.
Function List The Function List is the wizard without the bells and whistles. In fact, it is just the function list portion of the wizard. The idea behind it is to help you in adding functions directly into the cells. You can bring up the Function List by using the menus, Insert > Function List, or by clicking the Functions icon in the sidebar. When you select a function name in the list, a short description appears at the bottom of the list. The Function List also has a category item called Last Used, containing a list of the functions you have used recently. Let's use it to create the formula for the second device. Select the cell E4. Select the Input Line text box on the function toolbar. This is the best place to enter functions using the function list. For this device, we need the ending voltage of the previous one, so start the formula with “=E3-”. You should see PRODUCT listed on your Last Used list. Double-click PRODUCT to add it to the formula. With the cursor between the parenthesis, double-click SUM, which should also show in the Last Used list. Select cell range B4:B5. Click into the formula and use the arrow keys to move the cursor outside SUM's parenthesis. Type a comma then double-click PRODUCT again. Type in or select the range C4:D4. Press Enter. The final result should look like =E3-PRODUCT(SUM(B4:B5),PRODUCT(C4:D4))
Manual Entry Manual entry is just that, typing the formula directly into the cell from memory. The formula for the last device is the easiest because you no longer need the sum of device currents because it is the only one left. Select cell E5 and type in =E4-PRODUCT(B5, PRODUCT(C5:D5)) and press Enter. While you are typing in the functions, Calc will show you a hint balloon of the function and its arguments. Calc gives you three different methods for entering functions into a cell. Use the Function Wizard when you need as much guidance as possible, or when entering a complicated formula for the first time. The Function List gives you a list and short descriptions to aid you in using the correct arguments and functions in your formula. The manual method is great for entering short formulas, using functions you are familiar with, or repeating a formula you have used before.