Goal of the work

· learn to work with relative and absolute links

· learn to transfer data from MS Excel to MS Word

· be able to create formulas and work with various MS Excel functions

· master various techniques for formatting text and data in MS Excel tables

· learn to prepare various kinds of illustrative material using charting and graphing tools

· master the techniques of constructing updated tables using detailed information as initial data

Task 1: Creating relative and absolute links

1. Create a MS Excel document and save it as Lab_work_2.xcls. Name the first sheet "Links". Enter the data as shown in Fig. 1.

Notes: To name a sheet, you need to select its current name with the mouse, right-click and select Rename. In column B2, to get 100 rubles, you need to dial 100 and select the monetary format (right mouse button - cell format...).

2. Calculate Ivanov's salary by creating a formula containing a relative reference. To do this, select cell C4 and go to the formula bar. Enter the formula = B2*B4(Fig. 2) and press Enter.

Rice. 2 Entered expression in Formula Bar

Note: When entering a formula, use Latin letters or use the mouse to select the desired cells.

3. Copy the formula into cells C5 and C6 by dragging the fill handle. In this case, replicating the formula this example with relative references an error message will appear in cell C5 (#VALUE!), since the relative address of cell B2 has changed, and the formula = has been copied to cell C5 B3*B5.

Rice. 3. Error message (#VALUE!) in cell C5.

4. Set an absolute reference to cell B2. To do this, select cell C4. Place the cursor in the formula bar on B2 and press the F4 key, which converts a relative reference to an absolute one and vice versa (Fig. 4). Sign ( $ ) will appear before both a column reference and a row reference. The formula in cell C4 will look like = $B$2*B4.

5. Press F4 successively, which will add or remove the $ sign before the column or row number. (B$2 or $B2 are so-called mixed links).

7. Copy the formula into cells C5 and C6 by dragging the fill handle. The result should be a table with correctly displayed data (Fig. 5).

Length of service" href="/text/category/visluga_let/" rel="bookmark">length of service using data generated in Excel, using Paste Special to link data.

1. Go to a new sheet and name it “Paste Special” and enter the data shown in Figure 6. Select the filled cells and copy to the clipboard.

Note: To ensure that the words in a cell do not go beyond the borders and line up one below the other, you need to select the cell, right-click, select Format Cells..., go to the Alignment tab and check the box next to “Wrap by words”.

Rice. 6. Data on the "Paste Special" sheet.

2. Create text file in MS Word, save it as Order.docs. Arrange it arbitrarily in the way that, in your opinion, an order assigning wages to employees might look like, leaving an empty space where, logically, you can insert a plate with the calculated salary.

3. Place the cursor where you want to insert the table. Run the command shown in the figure below:

Rice. 7. Paste Special command

A dialog box will appear Special insert(Fig.8)

Microsoft" href="/text/category/microsoft/" rel="bookmark">Microsoft Excel (an object).

5. Check the radio button To tie and click OK.

6. As a result, on the page text document The object associated with the Excel document appears.

7. Return to the Excel document and change the cells of the “premium” column to the “Cash” format (select the range of cells E2:E10, right-click and select Format Cells...) (Fig. 9). On the Number tab, select Monetary. Click OK.

https://pandia.ru/text/78/392/images/image010_15.jpg" width="497" height="358 src=">

Rice. 10. The data in the Premium column is displayed in monetary format.

8. Go to Word document. Select the table object. Right-click a specific menu and select the line listed Update connection(Fig. 11).

https://pandia.ru/text/78/392/images/image012_13.jpg" width="627" height="396 src=">

Rice. 12. Setting a radio button in the Paste Special dialog box

Task 3. Use the VLOOKUP function to automatically substitute data from one table to another

1. Go to a new sheet and rename it to VPR. Create two tables as shown in Fig. 13.

Rice. 13 VLOOKUP sheet data

2. Transfer amounts from the table Return details from column Returned(in rubles) to the table Debt repayment automatically, based on Full name so that you can then calculate Remaining debt. To do this, give the range of cells Return details own name by selecting everything except the “header” (G2:H22) and then right-clicking and selecting from the list that appears Range name.

3. In the dialog box that opens Creating a name enter a name (no spaces) remainder. In the future, use this name to refer to the table Return details.

Rice. 14. Create Name Dialog Box

4. Select cell D3 where the formula will be entered and open Function Master, Pressing fx near the formula bar (Fig. 15).

Rice. 15 Calling the Function Wizard

Rice. 16 Function Wizard dialog box

5. In the dialog box that appears for entering arguments for the function (Fig. 17):

Rice. 17. Function Arguments Dialog Box

Fill them out one by one:

· Search_value- cells B3

· Column_number- serial number (not a letter!) of the column from which the sum value should be taken - 2

· Time-lapse - enter the value FALSE, this means that the search only exact match.

6. Click OK and copy the entered function to the entire column.

7. Enter the calculation formula in cell E3 Balance of debt (=C3-D3). Copy the entered formula to the entire column to automatically calculate Remaining debt.(Fig. 18).

https://pandia.ru/text/78/392/images/image019_7.jpg" width="633" height="491">

Rice. 19 Text Wizard dialog box

4. At the first step of the Wizard, select Source data format, i.e., a character that separates the contents of future individual columns (delimited) from each other. Click Further.

5. At the second step of the Wizard, you must specify which character is the delimiter. Check space(Fig. 20). Click Further.

Rice. 20 Text Wizard dialog box. Setting separators

6. In the third step, for each of the resulting columns, selecting them first in the Wizard window, select the format Text(Fig. 21). Click Finish after answering Yes to the question about replacing end cells that Excel asks.

As a result, the text will be divided into 3 columns, which is what was required in the task (Fig. 22).

Rice. 21. Text Wizard dialog box. Setting the Column Data Format

­

Rice. 22. Result of division by columns.

Task 5 Automatically merge text from several cells using a formula and the & sign.

1. Create a new sheet. Give it a name and.

2. Enter in cells A1, B1, C1 - , respectively.

3. Select cell D1. In the formula bar, enter the following formula: = A1&" "& B1&" "& C1 , then press Enter.

As a result, the last name, first name and patronymic with the necessary spaces will be combined in cell D1 (Fig. 23).

Rice. 23. The result of combining the full name into one cell.

Task 6. Automatically merge text from several cells using the function Extracting the first letters of LEFT from text.

1. Create a new sheet. Enter in cells A1, B1, C1 - , respectively.

2. Select cell D1. In the formula bar, enter the following formula: = A1&" "&LEFT(B1;1)&"."&LEFT(C1;1)&"."

3 Press Enter (Figure 24).

Rice. 24 The result of gluing text located in different cells.

Task 7. Transpose table data using an array formula and the TRANSPOSE function

1. Create a new sheet and name it TRANSPOSE. Enter the data as shown in Fig. 25.

Rice. 25 TRANSP sheet data

2. Select a range of cells to place the transposed table. Since the original cell array was 10 rows and 2 columns, we need to select a range of empty cells measuring 2 rows and 10 columns.

3. Enter the transpose function =TRANSP into the formula bar

4. As an argument to the function, select your cell array A1:B10 and close the bracket.

Please note that you are dealing with an array, and therefore to enter a formula, you don’t just need to clickEnter!!!

5. Click Ctrl+ Shift+ Enter. In the formula bar, Excel automatically encloses the formula you created in curly braces. The result was an "inverted array" (Fig. 26).

Rice. 26. Result of data transposition

Task 8. Select data in a table that is repeated more than once using Conditional Formatting

1. Create a new sheet and name it Conditional Formatting.

2. Copy cells B3:B22 of the VLOOKUP sheet into it.

3. Select the entire list. Select Main - Conditional Formatting - Create Rule from the menu.

4. Select Rule Type - Use a formula to determine which cells to format. In the appropriate line, enter the formula:

COUNTIF($A:$A;A2)>1

This simple function looks for how many times the contents of the current cell appear in column A. If this number of repetitions is greater than 1, that is, the element has duplicates, then cell fill is triggered.

5. To select the highlight color in the window Conditional Formatting press the button Format... and go to the deposit View. Select yellow color and click OK.

Rice. 27. Conditional Formatting Dialog Box

As a result, data repeated more than once will be marked yellow in the table.

Task 9: Create a report using a PivotTable

1. Create a new sheet and name it Pivot table. Fill it in as shown in Fig. 28.

Rice. 28. Sheet data Pivot table

2. Select the active cell in the table with data (any list field) and click on the menu Insert - PivotTable - PivotTable

3. In the window that appears, fill in everything as shown in the figure. 29.

Rice. 29 Pivot Table Wizard

4. Click OK. The following window will appear:

Histogram" href="/text/category/gistogramma/" rel="bookmark">histogram chart.

1. Open a Microsoft Excel 2007 spreadsheet and create a workbook named Laboratory work №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 Salaries of 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

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 ensure 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 links, data located in different parts of the worksheet 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 the exact location of the cell 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 placed on a sheet it is very large quantity(up to several thousand) complex formulas, 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, 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.

x i	1	2	…	n	S	Y
0,1
0,2
.
.
1

Table 20.

i x	0,1	0,2	…	1
1
2
.
.
n
S
Y

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

18.1 Theoretical information

One of the main purposes Microsoft Excel is to perform various calculations using formulas and built-in functions. Formula begins with an equal sign and is an expression that can consist of numbers, references (cell addresses), or functions combined with arithmetic signs. IN Excel formulas The following arithmetic operations are used: exponentiation (^); multiplication (*); division(/); addition (+); subtraction (-).

Function is a ready-made formula that consists of a function name and an argument or several arguments, for example SUM(A3,C8). The function name specifies the actions, and the arguments specify values ​​or cells and are specified in parentheses. Moreover, there are no spaces between the function name and parentheses. For calculations using functions, use the Function Wizard - step 1 of 2, which is called up by clicking on the fx icon in the formula entry line or executing the Insert/Formula command. Before calling a function, you must place the cursor in the cell in which you want to enter the function.

To visually represent the relationships between values ​​in spreadsheets in Excel, charts are used.

Using Excel, you can create complex charts for spreadsheet data. Diagrams are built using the Chart Wizard, which can be called by clicking on the Chart Wizard button on the standard toolbar, or by executing the Insert/Diagram command. You can also create a diagram in one step by clicking on the F11 key. Before you open the Chart Wizard, you must select the spreadsheet or part of a table for which you want to create a chart.

For example, to plot the function Y = 2sin 3 (5px) + 7cos(3px) 2

in Microsoft Excel, it must be presented (using the arithmetic operations used in Excel) in a form convenient for performing calculations. After transforming the function, it will look like: Y = 2*(sin(5*PI()*x)^3 + 7*cos(3*PI()*x)^2.

Then, in the cells that are defined for the argument "x", you must enter numbers with a certain step (for example, from -2 to +2 in increments of 0.1), and in the cells intended to accommodate the function Y, you must enter the formula = 2*(sin (5*PI()*x)^3 + 7*cos(3*PI()*x)^2. In this case, instead of the “x” argument, you must enter into the formula references to the cells in which their values ​​are located. After execution calculations, you need to select the results of calculations and call the diagramming wizard in one of the ways, and then build a graph of the function in four steps using the diagramming wizard.

To complete this laboratory work, you must familiarize yourself with the functions, formulas, and charts of Microsoft Excel, which are presented in the lecture course or in recommended sources of information.

18.2 Purpose of work

Acquiring practical skills in working with formulas, functions and charts in Microsoft Excel.

18.3 Problem statement

Compute functions:

• Y=2sin(nx)cos 5 (2nx) + sin(5 nx) 2, where n is the number PI = 3, 14;
• Z=cos(2пx) sin 3 (пx) - 3cos(4пx);
• range of change of argument X start = -3, X end =3, step - 0.1.

Based on the results of the calculations performed (functions Y and Z), construct graphs of functions in a unified coordinate system. Place the function graphs on a separate sheet and enter X-axis Labels.

18.4 Step-by-step execution of work

18.4.1 Turn on PC

Click the button Power on system unit PC.

18.4.2 Launch Microsoft Excel

18.4.2.1 Launch Microsoft Excel using the Main Menu command.

After the OS has fully loaded, launch Microsoft Excel by clicking on the Start button and selecting Programs/ from the main menu Microsoft Office, Microsoft Office Excel 2003 . As a result, the application window will open Microsoft Excel, which displays a blank Workbook 1 workbook with three worksheets.

18.4.2.2 Save the Excel workbook.

To save the workbook in Excel, use the File/Save command, and in the Save Document dialog box, enter the file name: Graphs of Functions. Click OK, saving the Excel workbook to the My Documents folder.

18.4.3 Function evaluation

18.4.3.1 Assign names to columns A, B, C (Argument X, Function Y, Function Z).

Do the following:

• in cell A1, enter the name Argument X;
• in cell B1, enter the name Function Y;
• In cell C1, enter the name Function Z.

18.4.3.2 Populating Column A with X values.

Do the following:

• in cell A2 enter the initial value (equal to -3);
• select cell A2 and execute the command Edit/Fill/Progression... ;
• fill in the fields: Location - by columns; Step - 0.1; Type - arithmetic Limit value - final value 3;

18.4.3.3 Introduce the formula for calculating the Y function in column B.

Enter the formula for calculating the Y function in column B using the Function Wizard or by entering it from the keyboard:

18.4.3.4 Introduce the formula for calculating the Z function in column C.

Enter the formula for calculating the Z function in Column C using the Function Wizard or by entering it from the keyboard. Entering a formula for calculating function Z is similar to entering a formula for calculating function Y, described in paragraph 18.4.3.3. The x argument to the formula placed in cell C2 is the address of cell A2.

18.4.3.5 Filling in the remaining cells of columns B and C with formulas.

To fill cells in columns B and C with formulas, it is advisable to use the autofill method:

• select cells B2 and C2 at the same time;
• Position the mouse pointer over the fill handle and, holding down the left mouse button, drag the selection down over the remaining cells. The cells will be filled with formulas.

18.4.4 Plotting graphs

18.4.4.1 Based on the calculation results, construct a graph of the Y function.

To plot a graph of the Y function on a separate sheet, do the following:

• highlight the calculation results along with the heading Function Y;
• run the Insert/Chart command;
• Selecting the required settings at each of the four steps, draw a graph on a separate sheet.

18.4.4.2 Based on the calculation results, construct a graph of function Z on the same diagram as the graph of function Y.

To plot a graph of function Z on the same diagram as a graph of function Y, do:

• highlight the calculation results along with the title Function Z;
• execute the Edit/Copy command;
• open the sheet with the graph of function Y;
• select the diagram by left-clicking on it;
• paste data from the clipboard using the Edit/Paste command.

18.4.5 Enter X-Axis Labels on a Chart

To enter an X-axis label on a chart, do the following:

• select the diagram;
• execute the menu command Diagram/Input data...;
• open the Row tab;
• click on the collapse button located on the right side of the X-Axis Labels text box;
• go to the sheet with functions, highlight the values ​​of the X argument, except for the header, and press Enter;
• To close the Source Data window, click the OK button.

Save the changes to the file.

18.4.6 Shutdown

Tell your teacher about your completed work. After allowing shutdown, close the application program Microsoft Excel, after which you can begin taking tests on the completed work.

Test task 1. Using formulas and functions in calculations.

Given: a, b, c, h, l, m, x - any numbers.

Calculate:

Execution result:

V=1/3*PI()*B1*(B2*B2+B2*B3+B3*B3)

Test 2. Using relative and absolute references in formulas. Creating a table “Purchasing goods with a pre-holiday discount.”

Answers to security questions

1. What is a formula in Excel? What is its structure? What elements can a formula include? What are the rules for entering and editing formulas in Excel?

A formula in Excel is a sequence of characters starting with an equal sign “=”. This sequence of characters can include constant values, cell references, names, functions, or operators.

You must enter the formula starting with the equal sign. This is necessary so that Excel understands that it is a formula and not data that is being entered into the cell.

3. How can you copy and move formulas?

When you move a cell with a formula, the references contained in the formula do not change. When you copy a formula, cell references may change depending on their type.

4. How do formulas autofill cells?

As well as autofilling cells with data. If you need to make the same calculations in a table, use the autocomplete feature to avoid having to enter the same formulas over and over again.

5. How are formulas edited?

Making changes to the worksheet, as well as eliminating errors, is done by editing formulas in cells. Cell editing mode can be activated in several ways.

1. Editing a formula in the formula bar:

Select the cell with the formula you want to edit

Click the mouse, placing the cursor in the formula bar.

2. Editing a formula directly in a cell:

Double-click the mouse, placing the cursor inside the cell. This method Editing a formula only works if the Edit directly in cell option on the Edit tab of the Tools menu Options dialog box is enabled.

Enable “edit mode”:

Select a cell and press a key .

After finishing editing the formula, “edit mode” in the cell must be turned off - press the key or .

6. What is a function in Excel? What is its structure?

Functions in Excel are used to perform standard calculations in workbooks. The values ​​that are used to evaluate functions are called arguments. The values ​​returned by functions as a response are called results. In addition to built-in functions, you can use custom functions in calculations that are created using Excel tools. Function arguments are written in parentheses immediately after the function name and separated from each other by the semicolon “;”. Parentheses allow Excel to determine where the argument list begins and ends. Arguments must be placed inside the parentheses. Remember that when writing a function, there must be opening and closing brackets, and you should not insert spaces between the function name and the brackets.

Arguments can be numbers, text, booleans, arrays, error values, or references. Arguments can be either constants or formulas. In turn, these formulas may contain other functions. Functions that are an argument to another function are called nested. Excel formulas can use up to seven levels of nested functions.

The specified input parameters must have valid values ​​for the given argument. Some functions may have optional arguments that may not be present when the function's value is calculated.

For ease of use, functions in Excel are divided into categories: database and list management functions, date and time functions, DDE/External functions, engineering functions, financial, information, logical, view and link functions. In addition, the following categories of functions are present: statistical, text and mathematical.

Using text functions, it is possible to process text: extract characters, find the ones you need, write characters to a strictly defined place in the text, and much more.

Using the date and time functions, you can solve almost any problem related to date or time (for example, determining age, calculating work experience, determining the number of working days in any period of time).

Logical functions help create complex formulas that, depending on the fulfillment of certain conditions, will perform various types of data processing.

8. What is the difference between formulas and functions? How to generate function text in a dialog?

Functions can be part of formulas.

The dialog has two list windows and several buttons. For convenience, the built-in functions are divided into categories. The window called "Category" contains a list of function categories. And in the window called “Function”, a list of functions of the selected category is presented in alphabetical order.

Below the list of functions is a very condensed help about the highlighted function. But you can only read this help using a JAWS cursor. If you click on the help button, a new dialog box will open with detailed help about the selected Excel function.

And clicking the "OK" button activates the second step of the function wizard - entering input parameters or function arguments. Typically, here you need to list the cell addresses and ranges that are involved in the calculations of this function. After entering the next argument, you need to press tab. At the end, you need to press "Enter" to activate the "Ok" button. The parameter entry dialog also contains help information, available only for the JAWS cursor.

After specifying the input parameters of the built-in function, Excel will generate the text of the formula and place it in the cell where the cursor was located when calling the Function Wizard.

9. How to use the Function Wizard?

To find the built-in Excel function we need, we need to go to the "Insert" menu and activate the "Function" item. A dialog called “Function Wizard” will open.

Conclusions about the laboratory work done

In the process of performing laboratory work, I mastered the technique of working with formulas and functions in the Microsoft Office Excel spreadsheet processor.