Laboratory work №7-8

№1-2

Introduction to spreadsheet Microsoft Excel. Data input, autofilling

Aims: to get acquainted with the interface of the program, to be able to enter data in a cell, to work with autofilling

Methodical instructions for laboratory work

· For use of types of alignments and orientations execute the commands The main - to Unite and place in the center or in the context menu Format of Cells - Alignment.

· To insert the text in some lines execute the command in the context menu Format of Cells - Alignment - Transfer by words. To break text in a certain place it is necessary execute a combination of keys ALT+ENTER.

· Тo insert a line execute the commands The main - to insert a line or in the context menu to insert a line.

Task 1: Create a spreadsheet with calculations

1. Start the program Microsoft Excel.

2. Before filling the table, select cells A1, B1, C1, D1, E1, execute the commands The main - to Unite and place in the center or in the context menu Format of Cells - Alignment - Transfer by words.

3. Fill the table without values of columns D and E.

4. Into a cell of D2 insert a formula: =B2-C2. With marker of autofilling fill a range of cells D3: D13.

5. With similar way fill a column E on a formula: = C2-B2.

6. For adding the title of the table it is necessary to insert a line. For this purpose put the cursor for the first line, execute the Main - to Insert.

7. Unite the range of cells of A1: E1, execute the commands The main - to Unite and place in the center or in the context menu Format of Cells - Alignment - Association of cells, insert the title of the table Balance of Labour.

8. Select the table, include the table borders by command the Main – Border.

9. Add 3 lines (minimum, maximum, total).

10. Into a cell of B15 insert a formula: =MIN(B3: В14).

11. Into a cell of B16 we will insert function maximum by commands Formulas - To insert function - category Statistical – MAX (a maximum). In the appeared dialog window insert the range of cells B3: В14 and click OK.

12. For calculation total use the Main - the Autosum. Selectthe range of cells B3: В14 and click Enter.

13. Click the cell B15, with marker of autofilling extend to the right to Е15.

14. With similar way fill maximum and total functions.

15. Rename the work sheet.

Task 2: Create the table with use of types of alignments and orientations:

Task 3: Insert the text in some lines in one cell:

№3-4

Work with logical functions. Creation macros

Aim: learn to use the logical functions, to create macros

The Condition of the problem: Form the table " Examination bordereau". The Students will deliver the exams on 3-m subject: physicist, chemistries and informatics. Determine the average score of each student, if the average score of > =4, 5 then scholarships size increased by 30%.

Technology of the work

1. Open its worker a book. Move to new sheet and fill the table of the following form:

2. Enter the information about the students of the group, fill up to 12 rows.

3. Then enter the formula:

In cell F5=IF (D5=5; 1; 0)

In cell G5=IF (D5=4; 1; 0)

In cell H5= IF (D5=3; 1; 0)

In cell I5= IF (D5=2; 1; 0)

In cell J5= IF (D5= «didn’t come»; 1; 0)

4. Fill with the marker (+) cells F6: F12, G6: G12, H6: H12, I6: I12, J6: J12.

5. In cell A12, type fine, A13 well, etc to A16. In A17 type didn’t come, in A18 type Whole.

6. Enter the formula:

In cell C13=SUM (F5: F11)

In cell C14= SUM (G5: G11)

In cell C15= SUM (H5: H11)

In cell C16= SUM (J5: J11)

In cell C17= SUM (I5: I11)

In cell C18= SUM (C12: C16)

7. Rename the worksheet in which you work: PHYSICS.

8. To create the examination list in another subject: CHEMISTRY, PHYSICS copy this sheet. Select all the contents of the page (cells from A1: J17)

Right-click the pop-up menu, select Copy dynamic.

Continue to the next page. Stand on the cell A1, right-click in the dynamic menu that appears, select Paste. Then should see a copy of the contents of the worksheet Physics.

Change the text, changing the disciplines of physics to chemistry, change in column MARK marks. Rename the sheet name: CHEMISTRY. Thus, you should have two sheets: PHYSICS and CHEMISTRY.

Similarly on the next sheet to create: Examination register of subject: MATHS.

After this in your book must be present three sheets: PHYSICS, CHEMISTRY, MATHS.

9. On the next sheet create next table:

10. Then for the calculation of the average score of type formulas:

Stand on the cell C6, click on the button the function wizard select on the category of Statistics, choose the function Average.

Stand in the section on number 1:

Then go on to a sheet of PHYSICS, in it click on the cell D5.

Stand in the section on number 2:

Then go on to a sheet of CHEMISTRY, in it click on the cell D5.

Stand in the section on number 3:

Then go to a sheet of MATHS, in it click on the cell D5.

Click OK.

11. In the cell D6 enter next formula:

= IF(C6> =4, 5; $E$3*30%+$E$3; $E$3)

In the cell D13 enter formula: =SUM(D6: D12)

Creation macros

- Open its worker a book

- Give the name new sheet, having given him name MENU

- On sheet MENU add the object Word Art take the text a MENU

Include the panel an instrument Forms to draw the button (the Type - a Panels instrument - Forms).

Using instrument, draw on sheet button necessary sizes. Close the opened dialogue window to Fix object macros, having pressed button Cancel. Enter on button text PHYSICS.

Similarly way create three buttons: MATHEMATICS, CHEMISTRY, SCHOLARSHIP.

- Press the mouse on sheet MENU, choose the command: Service - Macros - Start record. In opened dialogue window assign the name Macros PHYSICS.

- Go on the sheet physics and execute the command: Service - Macros - Stop record.

- Return on sheet MENU and right button press mouse on button PHYSICS.

- In opened dynamic menu choose to Fix macros. In dialogue window choose Macros Physics (having snapped by name) and press OK, but then press the mouse on any other cell.

- Check functioning (working) the button PHYSICS, having pressed on her mouse, You should move to sheet PHYSICS.

- On each sheet PHYSICS, CHEMISTRY, MATHEMATICS, SCHOLARSHIP create the buttons for return on sheet MENU

№5-6

Creation of schedule and chart of function

Aim: know how to construct charts of function

Task 1: Construction of charts of function

for xÎ [-0, 5; 0, 5] with a step 0, 1.

1. Create a table like that (values of x fill with an autofilling marker):

2. Values of function y fill with an autofilling marker.

3. Select the range of cells of A2: B13, execute the commands The insert - Сharts - The schedule. Choose a point chart.

4. To enter the chart headings (click Chart): Designer-Chart layouts

5. Enter the chart headings: Charts of function y.

Task 2: Personality construct charts of functions y and z:

Task 3: Creation of the chart of functions y and z with conditions

for xÎ [-0, 5; 0, 5] with a step 0, 1.

1. Insert the values of x.

2. Into a cell of B3 insert a formula: =если(A3< 0, 5; 0, 2-A3; A3^(1/3))

3. Into a cell of C3: =если(A3< 0, 2; 1+A3; если(A3> 0, 8; A3^2; A3^(1/2)))

4. Construct charts of functions y, z

Task 4: Personality construct the charts of functions y and z:

Variant	xÎ	step	y	z
	[-2; 2]	0, 2
	[-5; 5]	0, 5
	[-1; 1]	0, 1
	[-3; 3]	0, 3
	[-4; 4]	0, 4

Task 5: On the basis of data construct the charts

Task 6: On the basis of the chart construct the table of data

Laboratory work №7-8

Using a spreadsheet as a simple database

Aim: know the commands of sorting and filtering of data, work with matrixes

Task 1: Example of creation of a database. Sorting and filtration of data

1. Create a table

2. For a full name arrangement alphabetically at first select the column Full name, execute the commands The main - Sorting and filter or in the context menu Sorting.

3. Select the range of cells of A2: G2, execute the commands The main - Sorting and filter – The filter. For example, we will bring out of a database only teachers. In a column Position put a tick at a position of the teacher. You will see that from the general database teachers are selected only. To open back a database it is enough to open the filter of the Position column and to execute the command Select all.

4. Using the filter we will choose data with an experience higher than 10. For this purpose in the column filter an experience choose Numerical filters - More - enter number 10.

5. To refuse a filtration execute The main - Sorting and filter – Filter

6. Independently execute the following filtrations:

ü Bring out of a surname database beginning with a letter C.

ü Output data with a salary less or equally 65000.

ü Data with an experience between 10 and 20.

ü Phones beginning on 4.

Task 2: Work with matrixes:

1. Find matrix determinant: А=

· Insert values of a matrix as it is shown in drawing:

· In the cellof I2 insert Determinant.

· Put the cursor in the cellof I3, execute the command Formula - To insert function or in a line of formulas . In the category Mathematical – МОПРЕД ( matrix determinant function ). In the appeared dialog window insert the range of cellsB2: G7and click OK.

2. Multiplication of a matrix A to number 14.

· Select the range of cells of B9: G14 where elements of a total matrix B will settle down.

· Into a line of formulas insert: =14*B2: G7 also press a combination of keys Shift+Ctrl+Enter.

3. Addition of matrixes.

· Select the range of cells of B16: G21 where elements of a total matrix C will settle down.

· Into a line of formulas insert: =B2: G7+B9: G14, for a result conclusion press a combination of keys Shift+Ctrl+Enter

4. By similar way find the return matrix A, which is brought by the function MOBR.

Answer the questions:

1. What types of alignments do you know?

2. How can you create diagrams?

3. What kind of categories of functions do you know?

4. What is absolute address of cell?

5. How can you find the determinant of matrix?

6. What kind of data can we enter to the cell?

7. How insert the text in some lines in one cell?

8. What are macros?