Laboratory work No. 6

Lab 6: Excel Calculation Basics Creating Formulas Using the Function Wizard

Functions allow you to simplify formulas, especially if they are long or complex. Functions are used not only for direct calculations, but also for converting numbers, for example, for rounding, searching for values, comparing, etc.

To create formulas with functions, you usually use the group Function Library tabs Formulas.

Select the cell in which you want to enter the formula.

Click the button for the desired function category in the group Function Library and select the desired function.

In the window Function Arguments In the appropriate field(s), enter the function arguments. Cell references can be entered using the keyboard, but it is more convenient to select cells with the mouse. To do this, place the cursor in the appropriate field and select the required cell or range of cells on the sheet. To make it easier to select cells, the window Function Arguments can be moved or collapsed. As a tooltip, the window displays the purpose of the function, and at the bottom of the window a description of the argument in whose field the cursor is currently located is displayed. Please note that some functions have no arguments.

In the window Function Arguments click the button OK.

To insert a function, it is not necessary to use the function category buttons in the group Function Library. You can use the Function Wizard to select the desired function. Moreover, this can be done when working in any tab.

Function names can be entered from the keyboard when creating formulas. To simplify the creation process and reduce the number of typos, use formula autocompletion.

In a cell or formula bar, enter an "=" (equal sign) followed by the first letters of the function you are using. As you type, a scrolling list of possible items displays the closest values.

Select the desired function by double-clicking on it with the mouse.

Using the keyboard and mouse, enter the function arguments. Confirm entering the formula.

Creating formulas using the Sum button

This button, in addition to the group Function Library tabs Formulas(it's called there Autosum), also available in the group Editing tabs home.

To calculate the sum of numbers in cells located continuously in one column or one row, just select the cell below or to the right of the summed range and click the button Sum.

To confirm entering the formula, press the key Enter or press the button again Sum.

To calculate the sum of randomly located cells, select the cell in which the sum should be calculated, click on the button Sum, and then select the cells and/or ranges of cells to be summed on the worksheet. To confirm entering the formula, press the key Enter or press the button again Sum.

Function syntax

SUM),

where A is a list of 1 to 30 elements that need to be summed. The element can be a cell, a range of cells, a number, or a formula. References to empty cells, text or boolean values ​​are ignored.

In the new book, enter your academic performance data.

Calculate the total score for each student.

In addition to calculating the amount, the button Sum can be used in calculating the average value, determining the number of numerical values, finding the maximum and minimum values. In this case, you need to click on the button arrow and select the required action:

Average- calculation of the arithmetic mean;

Number- determination of the number of numerical values;

Maximum- finding the maximum value;

Minimum- finding the minimum value.

The purpose of the laboratory work is to study and consolidate skills in data entry and use of formulas in Microsoft Excel 2007 .

Entering data into a spreadsheet

Spreadsheet cells can contain three types of data: numeric values ​​(including time and date), text, and formulas. The worksheet, but in a "graphics layer" on top of the sheet, can also contain pictures, charts, images, buttons, and other objects.

Entering numbers

Numbers are entered using the top row of the keyboard or the numeric keypad. A comma or period is used as a decimal separator; you can enter currency symbols. If you enter a minus or parentheses before a number, it is considered negative. Zeros typed before the number are ignored by the program. If you need to get a value with leading zeros, it must be interpreted as text.

Excel uses 15 digits to represent numbers; when you enter a 16-digit number, it will be automatically saved to 15 digits. Numeric values ​​are automatically aligned to the right edge of the cell.

Entering Dates and Times

Excel uses an internal date numbering system to represent dates. (So, the earliest date that the program can recognize is January 1, 1900, this date is assigned serial number 1, the next date is assigned serial number 2, etc.). Dates are entered in a format familiar to the user and are recognized automatically. Time values ​​are also entered in one of the recognized time formats. The presentation of date and time directly on the worksheet is controlled by setting the cell's display format.

Entering text

All entered data that is not recognized as numbers or formulas is treated as text values. Text values ​​are aligned to the left edge of the table. If the text does not fit in one cell, then it is placed on top of adjacent cells if they are free. Parameters for placing text in a cell are set using the cell format.

Entering a formula

A formula is any mathematical expression. The formula always begins with the “=” sign and can include, in addition to operators and cell references, built-in Excel functions.

Data formats

After entering data into a cell, Excel automatically tries to determine its type and assign the cell the appropriate format - the form of data presentation. It is important to assign the correct cell format so that, for example, the cell can participate in calculations (not be text).

Excel has a set of standard cell formats that can be used in all workbooks (Figure 2.2.17). You can activate it by selecting Home – Number – Number Format, or using the context menu for the selected cell on the Number tab of the Cell Format window.

Figure 2.2.17. Standard formats

Initially, all table cells have the General format. The use of formats affects how the contents in the cells will be displayed: general - numbers are displayed as integers, decimal fractions, if the number is too large, then as exponential; numeric – standard numeric format; financial and monetary – the number is rounded to 2 decimal places, the currency sign is placed after the number, the monetary format allows you to display negative amounts without the minus sign and in a different color; short date and long date format – allows you to select one of the date formats; time – provides several time formats to choose from; - percentage – the number (from 0 to 1) in the cell is multiplied by 100, rounded to the nearest whole number and written with the % sign; fractional – used to display numbers in the form of an ordinary fraction rather than a decimal; exponential – designed to display numbers as the product of two components: a number from 0 to 10 and a power of 10 (positive or negative); text – when setting this format, any entered value will be perceived as text; additional – includes the formats Postal code, Zip code+4, Telephone number, Personnel number; all formats – allows you to create new formats as a custom template.

Using tools to speed up data entry

When entering data into table sheets, you can use some techniques to speed up their entry.

1) Autocomplete as you type. When you enter the same values ​​in multiple cells, you can use the autofill marker (the cross in the lower right corner of the active cell) to copy the values ​​to adjacent cells. Using the context menu that opens by right-clicking after dragging, you can set additional autofill parameters (for example, by entering the numbers 1 and 3 into the cells, you can get a sequence of numbers in increments of 2 for the selected range of cells).

2) Use of progression. If a cell contains a number, date or time period that may be part of a series, then when copied, its value is incremented (an arithmetic or geometric progression, a list of dates is obtained). To set a progression, you need to select the Fill button on the Editing panel of the Home tab and in the Progression dialog box that appears, set parameters for an arithmetic or geometric progression.

3) Auto-completion as you type. This feature allows you to automatically enter repetitive text data. After entering text into a cell, Excel remembers it and the next time you enter it, after typing the first letters of the word, it offers an option to complete the entry. To complete the entry, press “Enter”. This command can also be accessed by selecting Select from drop-down list from the context menu using the right mouse button. The AutoComplete feature only works on a continuous sequence of cells.

4) Using autocorrect as you type. AutoCorrect is designed to automatically replace some specified combinations of characters with others as you type. For example, you can specify that you enter a single character instead of entering multiple words. The command is available via the Office button – Excel Options. In Spelling - AutoCorrect Options you need to set the text and its abbreviation.

5) Using the Ctrl+Enter keyboard shortcut to enter repeating values. To enter the same values ​​in several cells, you can select them, enter the value in one cell and press Ctrl+Enter. As a result, the same data will be entered into all selected cells.

Validation of data as you enter

If you want to be sure that the correct data is entered into a worksheet, you can specify criteria that are valid for individual cells or ranges of cells. To set up a check, run the command Data – Working with Data – Checking Data. In the window that appears (Figure 2.2.18), set the verification criteria on the Parameters tab, the text of the prompt message for the user to enter on the Input Message tab, and the text of the error message on the Error Message tab.

After using the command Data – Working with Data – Circle Invalid Data, all incorrect data will be circled in red.

Figure 2.2.18. Window for setting data verification parameters

Using Formulas

A formula in Excel is a mathematical expression based on which the value of a certain cell is calculated. Formulas can use: numerical values; cell addresses (relative, absolute and mixed references); operators: mathematical (+, -, *, /, %, ^), comparisons (=,<, >, >=, <=, < >), text operator & (to combine several text strings into one), range relation operators (colon (:) - range, comma (,) - to combine ranges, space - intersection of ranges); functions.

Entering a formula always begins with the “=” sign. The result of the formula is displayed in the cell, and the formula itself is displayed in the formula bar. Cell addresses in the formula can be entered manually, or simply by clicking on the desired cells.

After calculation, the resulting result is displayed in the cell, and the created formula is displayed in the formula bar in the input window.

Cell Addressing Methods

The cell address consists of the column name and row number of the worksheet (for example A1, BM55). In formulas, addresses are indicated using links - relative, absolute or mixed. Thanks to the links, the data located in different parts sheet, can be used in several formulas at the same time.

A relative reference indicates the location of the desired cell relative to the active (i.e., current) cell. When copying formulas, these links are automatically changed in accordance with the new position of the formula (Example of link entry: A2, C10).

An absolute reference points to exact location cells included in the formula. When you copy formulas, these links do not change. To create an absolute cell reference, place a dollar sign ($) in front of the column and row designations (Example reference notation: $A$2, $C$10). To fix part of the cell address from changes (by column or row) when copying formulas, a mixed reference is used with fixation of the desired parameter. (Example link entry: $A2, C$10).

Notes

· To avoid manually typing dollar signs when writing links, you can use the F4 key, which allows you to “sort through” all types of links for a cell.

Built-in Excel functions

Each function has its own syntax and order of operation that must be followed in order for the calculations to be correct. Function arguments are written in parentheses, and functions may or may not have arguments; when using them, you must take into account the type of arguments. A function can act as an argument to another function, in which case it is called a nested function. In this case, up to several levels of function nesting can be used in formulas.

Excel 2007 includes math, logical, financial, statistical, text, and other functions. The name of the function in the formula can be entered manually from the keyboard (this activates the AutoComplete formulas tool, which allows you to select the desired function based on the first letters entered (Figure 2.2.19)), or you can select it in the Function Wizard window, activated by the button on the Function Library panel of the Formulas tab or from function groups on the same panel, or using the Edit panel button on the Home tab.

Figure 2.2.19. Autocomplete formulas

Formulas can be edited just like the contents of any other cell. To edit the contents of a formula: double-click the cell with the formula, or press F2, or edit the contents in the formula input line.

Naming and using cell names

Excel 2007 provides a useful feature for naming cells or ranges. This can be especially convenient when composing formulas. For example, by specifying the name Total_for_year for a cell, you can use this name instead of the cell address in all formulas.

The cell name can be valid within one sheet or one workbook; it must be unique and not duplicate cell names. To name cells, you need to select the cell or range and enter a new name in the title bar. Or use the Assign a name button to the Defined Names panel of the Formulas tab and call up the dialog box (Figure 2.2.20) to set the required parameters.

Figure 2.2.20. Name creation window

To view all assigned names, use the Name Manager command. You can also get a list of all names with cell addresses on the sheet using the command Use in formula - Insert names of the Defined names panel.

To insert a name into a formula, you can use the Use in Formula command and select the required cell name from the list.

Comment. A name can be assigned not only to cell ranges, but also to a formula. This is convenient when using nested formulas.

Showing dependencies in formulas

To help identify errors when creating formulas, you can display cell dependencies. Dependencies are used to view relationships in a table box between cells with formulas and cells with values ​​that were involved in these formulas. Dependencies are only displayed within one open book. When creating a dependency, you use influencing cells and dependent cells.

An influencing cell is a cell that references a formula in another cell.

A dependent cell is a cell that contains a formula.

To display cell relationships, you must select the Influencing Cells or Dependent Cells commands in the Formula Dependencies pane of the Formulas tab. To not display dependencies, use the Remove arrows command from the same panel.

Figure 2.2.21. Displaying Influencing Cells

Modes for working with formulas

Excel has an automatic calculation mode, thanks to which formulas on sheets are recalculated instantly. When placing a very large number (up to several thousand) of complex formulas on a sheet, the speed of work may decrease noticeably due to the recalculation of all formulas on the sheet. To control the calculation process using formulas, you need to set manual mode calculations by using the command Formulas – Calculation – Calculation parameters – Manual. After making changes, you need to call the Calculate command (to recalculate data on a worksheet sheet) or Recalculate (to recalculate the entire workbook) of the Calculation panel.

A useful feature for working with formulas is to display all formulas on a sheet. This can be done using the command Formulas – Formula Dependencies – Show Formulas. After this, the written formulas will be shown in the cells instead of the calculated values. To return to normal mode You need to click the Show formulas button again.

If a formula returns an incorrect value, Excel can help you identify the cell that is causing the error. To do this, you need to activate the command Formulas – Formula Dependencies – Check for Errors – Source of Errors. The Check for Errors command helps identify all erroneous formula entries.

To debug formulas, there is a formula evaluator called Formulas - Formula Dependencies - Evaluate Formula, which shows step-by-step calculations in complex formulas

Workshop:.

1. Create a table for calculating the sum of a series (options of tasks for calculating the sum of a series - see below). When creating a table, use built-in functions, absolute and relative addressing, and autofilling of cells.

2. Depending on the number of terms n, arrange the table as follows:

Table 19.

Table 20.

3. Using conditional formatting, highlight negative numbers in blue, numbers greater than 1.5 in red.

4. Create a table. A sample design is below. The step of change x depending on the variant of the task is 0.1 (or Pi/*).

5. Construct graphs s=f(x) and y=f(x) in one coordinate grid (on one diagram).

6. Study the possibilities of using functions (for a list of functions, see the task option), give an example of how each function works.

Table 21. Task options

1. Open a Microsoft Excel 2007 spreadsheet and create a workbook named Laboratory work No. 1.

2. It is necessary to create a table for calculating wages for company employees.

3. To simplify data entry into the table, create a drop-down list (Fig. 22) containing the full names of the company’s employees.

Rice. 22. Dropdown list

4. Insert another sheet into the Excel workbook using the shortcut in the row Sheet Label.

5. On a new sheet, create a list of employees (Fig. 23).

Rice. 23. List of company employees

6. To sort names alphabetically, run the command: ribbon tab Data group Sorting and Filter button .

7. Select the cell range A1:A10 and click the box Name at the left edge of the formula bar. Enter a name for the cells, for example Employees. Press the key Enter.

8. To prevent other users from viewing or changing the resulting list, protect and hide the sheet on which it is located.

9. Right-click the sheet tab. Select the command from the context menu.

10. In the dialog box Sheet protection(Fig. 24) enter a password to disable sheet protection. In chapter Allow all users of this sheet Uncheck all items. Click the button OK.

Rice. 24. Sheet Protection dialog box

11. In the dialog box Password confirmation enter the password again.

12. Right-click on the sheet shortcut and select the command from the context menu Hide.

13. Go to Sheet 1 and create a table Payroll preparation
(Fig. 25). Column Full name fill in using the drop down list.

Rice. 25. Table structure

14. Select the range of cells in which you want to place the drop-down list.

15. On the tab Data in Group Working with data select team Data checking.

16. In the dialog box Data checking indicate the data type and source (Fig. 26).

17. Open a tab Message to be entered(Fig. 27). Fill in the blank fields.

Rice. 26. Data Validation Dialog Box

Rice. 27. Message when entering data

18. Go to the tab Error message(Fig. 28). Fill in the fields View, Heading And Message.

Rice. 28. Message in case of data entry error

19. For table headers, set text wrapping(button located on the toolbar Alignment ribbon tabs home).

20. Freeze the first two columns and the table header row. To do this, select a range of cells C5:I20 and run the command: ribbon tab View group Window button .

21. Column Salary Fill with arbitrary data and set the currency format of the cells using the command:

ribbon tab home toolbar Number From the format drop-down list, select Currency format.

22. Let’s create a formula for calculating the bonus, which is 20% of the salary. Any formula begins with the sign = , so let's go to the cell F5 and enter the formula = E5*20%(or = E5*0.2).

23. Using the autofill marker (black cross near the lower right corner of the selected cell), copy the formula into the area
F6:F11.

24. Between the columns Prize And Income tax insert column Total accrued, in which calculate the amount Salary + Prize.

25. Fill in the remaining columns of the table, taking into account that income tax is 13% of the accrued amount.

26. Calculate the amount to be issued in dollars, to do this, set the current dollar exchange rate, for example 32, and in the cell J5 enter the formula: = I5/$C$14. Sign $ is used in the formula so that when copying using the autofill marker, the cell addressing does not change.

27. For cells containing monetary data, set the appropriate format.

28. Using the function SUM, calculate the total amount of income tax. For this:

· place the cursor in a cell H12;

· put the sign =;

· in the formula bar, click the button;

· in the function wizard dialog box that appears (Fig. 29), select a category Mathematical, function SUM;

· as a function argument SUM select the summation range H5:H11;

· press the button OK.

29. Similarly, calculate the total amount to be issued in dollars and the total amount to be issued in rubles.

Rice. 29. Function Wizard

30. Find the average ( AVERAGE), minimum ( MIN) and maximum ( MAX) wages.

31. Using conditional formatting, highlight in red Amounts to be issued, less than 5,500 rub. Run the command: ribbon tab home group Styles drop-down list Conditional Formatting Cell selection rules.

32. Build a diagram Wage company employees
(Fig. 30). Select columns at once FULL NAME. And Amount to be issued(hold down the Ctrl key), and on the ribbon tab Insert on the toolbar Diagrams select view bar chart.

33. Using the Ribbon Tab Layout, insert axis labels and chart title.

Rice. 30. Example of diagram design

34. Construct a pie chart showing the relationship between the total amount to be issued and the total income tax (Fig. 31).

Rice. 31. Example of a pie chart design