Task execution technology:

1. Run Microsoft program Excel. Take a close look at the program window.
One of the cells is highlighted (framed with a black frame). How to select another cell? Just click on it with the mouse, and the mouse pointer at this time should look like a light cross. Try highlighting different table cells. Use the scroll bars to move around the table.

2.In order to enter text into one of the table cells, you need to select it and immediately (without waiting for the appearance of the much needed text in the Word processor). text cursor) "write".

Enter today's day of the week into any selected (active) cell, for example: Wednesday.
Select the entire table row that contains the name of the day of the week.
Click on the row title (its number).
Select the entire table column that contains the name of the day of the week.
Click on the column header (its name).

3.The main difference between the operation of spreadsheets and word processor is that after entering data into a cell, it must be recorded, i.e. let the program know that you have finished entering information in that particular cell.

You can record data in one of the following ways:

1. • press the (Enter) key;
   • click on another cell;
   • use the cursor buttons on the keyboard (move to another cell).

Record the information you entered. Select the table cell containing the day of the week and use the paragraph alignment buttons.

4.You have already noticed that the table consists of columns and rows, and each column has its own heading (A, B, C...), and all rows are numbered (1, 2, 3...). In order to select an entire column, just click on its header; to select an entire row, you need to click on its header.

Select the entire table column in which the name of the day of the week you entered is located.
What is the title of this column?
Select the entire row of the table in which the name of the day of the week you entered is located.
What title does this line have?
Determine how many rows and columns there are in the table?
Use the scroll bars to determine how many rows the table has and what the last column name is.
Attention!!! To quickly reach the end of the table horizontally or vertically, you need to press the key combinations: Ctrl+→ - end of columns or Ctrl+↓ - end of rows. Quick return to the beginning of the table - Ctrl+Home.
Select the entire table.
Use the empty button.

5.Select the table cell that is located in column C and row 4.
Please note that the address of the selected cell C4 has appeared in the Name field located above the header of column A. Select another cell and you will see that the address in the Name field has changed.

6.Select cell D5; F2; A16 .
What is the address of the cell containing the day of the week?

7.Determine the number of sheets in Book1.

Insert two additional sheets through the context menu Add–Sheet. To do this, go to the Sheet 3 sheet shortcut and click on it right click, a context menu will open, select the Add option and select Insert Sheet in the window. Added Sheet 4. Add Sheet 5 in the same way. Attention! Pay attention to the names of the new sheets and where they are placed.
Change the order of the sheets in the book. Click Sheet 4 and hold left button, move the sheet to the desired location.

8.Set the default number of worksheets in a new workbook to 3. To do this, run the command Tools–Options–General.

Report:

1. In a cell A3 Specify the address of the last column of the table.
2. How many rows are there in the table? Please enter your address last line in a cell B3.
3. Enter your name in cell N35, center it in the cell, and make it bold.
4. Enter the current year in cell C5.
5. Rename Sheet 1

Tikhomirova A.A.MS Excel . Workshop

PRACTICAL TASK No. 1 Building a table

To complete the task, use the table (Fig. 1) as a sample.

Figure 1 - Visit record form

Enter the text “Statement” in cell A1

Enter in cell A2 the text “recording of visits to a clinic (outpatient clinic), dispensary, consultation at home”

Enter the text “Name and specialty of the doctor” in cell A3

Enter the text “for” in cell A4

Enter the text “Plot: territorial No.” in cell A5

Enter the text “workshop number” in cell E5

Create a table header:

Enter the text “Days of the month” in cell A7

Enter in cell B7 the text “Inspection accepted at the clinic - total”

enter the text “Including regarding diseases” in cell C7

enter the text “Home visits made” in cell E7

enter the text “Including children under the age of 14 inclusive” in cell F7

enter the text “adults and teenagers” in cell C8

enter the text “children under the age of 14 inclusive” in cell D8

enter the text “about diseases” in cell F8

enter the text “preventive and patronage” in cell G8

enter the text “A” in cell A9

number the remaining columns of the table

Format the table header as shown

PRACTICAL TASK No. 2

Calculations in tables. Autosum.

In the table built in the previous task, fill the columns with arbitrary data

In line 15, form the line TOTAL:(in cells B15, C15, D15, E15, F15 and G15) use AutoSum.

PRACTICAL TASK No. 3

Calculations in tables. Formulas

Construct and format the table according to the example shown in Fig. 2, leaving cells I6:J9 blank in columns 9 and 10 of the table.

Figure 2- Payroll calculation using formulas

Enter the calculation formula in cell J6 Amounts to be issued excluding tax: =G6+H6

Enter the formula for calculation Tax(column 9) : =$E$3*(G6+H6)

Copy the formula to cells in the range I7:I14, pay attention to the automatic changes in the formulas that occur when copying

Change the formula in cell J6: = G6+H6-I6

Copy the formula into the cells of the range J7:J14, pay attention to the automatic changes in the formulas that occur when copying

Calculate the totals in cells G16, I16, J16 using Autosum

Calculate the average of the Salary column in cell G18 using Function Wizard and the AVERAGE function (category Statistical). Formula: = AVERAGE (G6:G14)

Copy the formula to cells I18 and J18, pay attention to the automatic changes in the formulas that occur when copying

PRACTICAL TASK No. 4

Building charts

Construct and format the table according to the example shown in Fig. 3.

Figure 3- Table for plotting diagrams

Create diagrams based on the table data:

pie chart of the primary incidence of socially significant diseases in St. Petersburg in 2010;

histogram of the dynamics of changes in the primary incidence of socially significant diseases in the population of St. Petersburg in the period 2006-2010.

graph of the dynamics of changes in the primary incidence of dysentery in the population in St. Petersburg in the period 2006-2010.

PRACTICAL TASK No. 5

Logical function IF

Convert the table from task No. 3 to the form in Fig. 4 by creating and filling out the column “Percentage of Plan Fulfillment”, and also set the bonus amount to 15% in cell H3.

Calculate the values ​​in the Premium column using the built-in logic IF function, based on the following condition:

“employees who exceed the plan receive a bonus of 15% of their salary.”

Recalculate the columns “Tax”, “Amount to be issued”, total and average values ​​in accordance with the changes in the table.

Compare the results obtained with the table in Fig. 5.

Figure 4 - Changes to the table of task No. 3

Figure 5 - Result of task 5

PRACTICAL TASK No.6

Calculations in tables. Formulas.

Using formulas that contain nested functions

Construct and format the table according to the example shown in Fig. 6.

Figure 6 – Table for determining student testing results

Using logical functions, create a formula to automatically determine student grades in accordance with the points scored, based on the following conditions:

Each student is asked to answer 100 questions. One point is awarded for each answer.

Based on the test results, grades are given according to the following criterion: from 90 before 100 points - score " Great", from 75 before 89 - « Fine", from 60 before 74 – « satisfied.", from 50 before 59 - « unsatisfactory." , before 49 - « unit", less 35 - « zero" In other cases, the message “ error».

Before performing calculations, draw up an algorithm for solving the problem in graphical form.

3. Calculate your GPA by setting it to be output as an integer.

4. Sort the data contained in the table in descending order of points scored.

5. Compare the results obtained with the table in Fig. 7.

Figure 7 - Result of task 6

Practical work VMS E xcel

Laboratory workshop created for practical learning section, calculations in “SpreadsheetsMSExcel- 2007" within the discipline " Information Technology V professional activity» second-year students of various specialties of the State Budgetary Educational Institution of Secondary Professional Education Polytechnic College No. 42, Moscow.

The workshop consists of four practical works on the main topics of applicationMS E xcelin calculations, is aimed mainly at students studying in the specialties “Economics and Accounting (by Industry)”, “Operational activities in logistics" and "Installation and technical operation industrial equipment (by industry)". Some topics of practical work can be used in training by students of other specialties.

Each practical work contains theoretical information on the topic of work, detailed analysis test case and 3 options for tasks on the topic.

1. Practical work

Subject:"Organization of settlements inMSExcel»

Purpose This practical work is to master the technology of organizing tables inMSExcelnamely copying, formatting cells, creating borders, presenting data, and organizing simple formulas calculations. Figure 1 shows a table in whichcolumn A organized by copying cell contentsA4 (date 04/01/13) down to the required cell,columns B And C filled with source data, also using copying and subsequent editing of values,column D , created by organizing a formula into a cellD4 (in the formula line, the type of formula is shown) and then copying it down.

Fig.1

The table in Fig. 2 is similar in creation to the previous table, with the addition of generating totals by column and row. The formula can be written differently.

Fig.2

Task optionson this topic« Organization of settlements inMSExcel»

Exercise 1 . Create a table according to task 1. ColumnMonth organize by copying cells the next three columns with the original data, fill and format the data in these columns. ColumnSupplement amount , create via formula.

Task 2 . Create a table according to task 2. Organize the column by copying cells.

Task 3 . Create a table according to task 3. Organize the column as follows, from the beginning fill in the value 1.0 in the cellI 4 and 1.1 per cell I5, then select a range of cells consisting of cellsI 4, I5 and copy the selected range down.

1. Practical work

Subject:"Statistical functions"

Purpose This practical work is to become familiar with the built-in statistical functions.

When processing statistical data quite often there is a need to determine various statistical characteristics. For such calculations inMSExcelA number of statistical functions are built in, for example:

AVERAGE(x 1 ,…,x n)

arithmetic mean (x 1 +…+x n )/n.

MAX(x 1 ,…,x n )

maximum value from the set of arguments (x 1 ,…,x n )

MIN(x 1,…,x n)

the minimum value from the set of arguments (x 1 ,…,x n )

COUNT(x 1,…,x n)

number of numbers in argument list

COUNTA(x 1 ,…,x n )

the number of values ​​in the argument list and not empty cells

An example of completing a task using

statistical functions

Figure 4 shows a table of product sales in a store.

Fig.4

Note . An empty cell in the Sales Quantity column means that the item was not sold.

Guidelines to complete the task:

Calculate:

• revenue from sales of each product;

  total, average, maximum, minimum revenue from sales of all goods;

  determine the total number of types of goods in the store,

  how many types of goods were sold.

An example of completing an assignment on the topic “Statistical functions”

enter in cell D2(in the first cell of the “Sales Revenue” column) formula: =B2*C2 (“Sales revenue” = “Price” * “Number of sales”);

copy the formula to the entire column;

enter formulas:

in D5 =SUM(D2:D4) - total revenue

in D6 = AVERAGE(D2:D4) - average revenue

in D7 =MAX(D2:D4) - maximum revenue

in D8 =MIN(D2:D4) - minimum revenue

in D9 =COUNT(A2:A4) - number of types of goods

(counting the number of non-blank values)

in D10 = COUNT(C2:C4) - the number of types of goods sold (counting the number numerical values)

Task optionson this topic"Statistical functions"

Exercise 1 . Organize the table “Rivers of Eurasia”.

Fig.5

Task 2 . The ages of ten people applying for vacancies in the company are known. Determine the maximum, minimum, average age applicants?

Task 3 . The table contains information about the company's employees: last name, length of service. Determine the average, maximum, minimum length of service. How many employees are there in total?

1. Practical work

Subject:"Logical functionIF…»

Purpose This practical work is to become familiar with the most common function in logical expressions.

In practice, Boolean expressions are used to developbranching algorithm :

Algorithmic language

If condition ( logical expression)

action 1

otherwise

action 2

all-if;

condition

action 1

action 2

Block diagram

To build a branch inMSExcel exists logical function IF, its structure is as follows:

IF value of logical expressionTRUE ,

THAT statement 1 is executed ,

OTHERWISE statement 2 is executed .

Rice. 5 .

Example of specifying arguments to the IF function

(finding maximum value of two numbers)

To call a functionIF , you have to press the buttonf x "Insert Function" located in the formula bar. will appearFunction Wizard in a cell Category you need to select a linebrain teaser and then select the functionIF , fill in three cells:

Logical expression

Value_if_true

Value_if_false

Figure 7 shows an example of using the functionIF Fig 7.

Task optionson this topic« Logical function IF...»

Exercise 1. In a cell D8 set the value to 800, that is, make Plan = Fact for V.V. Serov. Explain why the result has not changed?

Task 2 . Column A arbitrary number with a value of about 1000, column B is 2% of the number, column C (result), a logical IF function, provided that if the number is greater than or equal to 1000, then the result will be = number + 2%, otherwise = number – 2%. Figure 8 shows the table.

Fig 8_1.

Task 3 . Column E – first number, columnF– second number, columnG(result), is formed as follows: if number1 is greater than number2, then the result will be their sum, otherwise the result will be their difference. Figure 8_2 shows the original table with the result.

Fig 8_2.

1. Practical work

Subject:"Histograms, graphs"

Purpose This practical work is to master the technology of presenting data in the form of diagrams inMSExcel. To generate histograms, initial data is required, then depending on the versionMSOffice, select the Insert menu and the right type histograms (graphics). Before inserting a chart, it is recommended to be in any cell of the source table with data. Fig 9_1.

The following figure is Fig 9_2. a diagram has been generated - a graph of functions

y= sin(x), y= cos(x), y= x 2 (parabola). To generate graphs, a column of values ​​is requiredX. Values ​​are formed from -6.28 to 6.28 in increments of 0.1 Columns for formationsin(x), cos(x) are selected via function insertion. The column for the parabola is organized by formula. Fig 9_2.

Task optionson this topic« Histograms, graphs"

Exercise 1 . Organize a pie chart according to the data in Fig. 9_1.

Task 2 . Organize the graph of a functiony= x^3 (cubic parabola).

Fig 9_3

Task 2 . Organize changes in the dollar exchange rate against the ruble.

Goal of the work: development of practical skills in working with MS Excel 2010 spreadsheets

• Educational: generalization and consolidation of knowledge and practical skills in creating and designing tables, diagrams, and organizing calculations.
• Educational: development of attention and independence when working with a software product.
• Cognitive: development of interest in solving problems and interdisciplinary connections, strengthening cognitive motivation.

Equipment: computer class, software– MS Excel 2010.

Practical work No. 1
"Creating and formatting a table"

Purpose of work: Learn to format a table using the Format Cell command.

• Create new file. Give the first sheet a name Earth and make a table according to the example (Arial font, size 14):

2. Set the data format. Select cell C2 (place the cursor in it) with the right mouse button and select from the context menu Cell format... In the tab Number select format Numerical , the number of decimal places is 0. Click OK. In cell C2, type 149600000.

Similarly, select cell C3 and set the format Numerical , the number of decimal places is 0. In cell C3, type 384400.

Select cell C4 and set the format Time . In cell C4, type 23:56:04.

Select cell C5 and set the format Numerical , the number of decimal places is 3. In cell C5, type 365.256.

Select cell C6 and set the format Numerical , the number of decimal places is 1. In cell C6, type 29.8.

3. Format the table.

Merging cells. Merge the range of cells A1:C1 (cells A1, B1, C1). To do this, select the indicated cells with the left mouse button and context menu select Cell format... tab Alignment . Check the box in the line merging cells .

Alignment in a cell. Select in line horizontally in the dropdown list - horizontally meaning in the center .

In line vertically in the dropdown list - in the center .

Change the width and height of cell A1 using the left mouse button.

Write in several lines. Select cells B2:B6 with the left mouse button and select from the context menu Cell format... tab Alignment . Check the box in the line translate according to words . Nothing has changed in the table, because All information fits within the width of the cell. Reduce the width of Column B so that the text appears as shown in the example below. (If not all of the text in a cell is visible, it means that it is located outside the cell border - you need to increase the height of the cell using the left mouse button.)

The table will look like

Setting cell borders. Select cells A2:C6. From the context menu, select Cell Format tab Borders . Set external and internal boundaries.

The finished table will look like this:

4. Save the table in your folder under the name tables .

Practical work No. 2
"Building diagrams"

Goal of the work: Learn to create and format charts

Exercise 1.

1. Open the file tables . Give the second sheet the name Fur and make a table according to the example given.

2. Select the data range A2:Bll, including row and column headers

3. Select the Insert tab, the Chart command group, select the chart type - Histogram – Volume Histogram – Clustered Histogram.

4. Changing the name of the diagram.

After selecting the chart, the Working with Charts command line will be activated. Select in the Layout tab – Chart Title – Above Chart. Click on the chart title, erase the old title and type a new one Durability of the best quality furs. The diagram will look like

5. Chart formatting.

Right click on free space area of ​​the diagram and select the command from the context menu Chart area format. Set the design options you like. Approximate result

Task 2.

1. Go to the next sheet and give it a name Caves. Create a table using the following example:

2. Make a diagram. Approximate view of the diagram

Task 3.

1. Go to the next sheet and name it Graph

2. Construct a type diagram Schedule and format the chart (to create axis labels and data labels, use the Chart Tools - Layout tab). Approximate view of the diagram

Practical work No. 3
"Table Autofill"

Goal of the work: learn how to fill table cells using the AutoFill function.

The AutoFill function allows you to automatically continue a series of cells if the filling of the latter follows a certain principle (arithmetic progression, days of the week, months). MS Excel searches for the filling rule, the entered data in order to determine the values ​​of empty cells. If one initial value of the filling sample is entered, then one cell is selected, if the list has a data change interval, then two cells filled with the corresponding data must be selected.

Exercise 1.

1. Open the file tables autocomplete.

2.Autofill numbers. In cell A2, type the number 1, and in cell A3, the number 2. Select cells A2 and A3. Drag the fill handle with the left mouse button to cell A7.

3.Fill the cells with the days of the week. In cell B1, type Monday. Drag the fill handle with the left mouse button to cell F1.

4. Fill in the remaining cells and format the table as shown.

Task 2.

1. Open the file tables. Go to a new sheet and give it a name temperature.

2. Using the function Autocomplete, create a table based on the example.

3. Save the file.

Practical work No. 4
on this topic"Computing inMSExcel 2010"

Goal of the work: develop the skills of creating, editing, formatting and performing simple calculations in spreadsheets.

Calculations in MS Excel tables are carried out using formulas. The formula always starts with an = sign. The formula can contain numbers, cell addresses, mathematical symbols, and built-in functions. Parentheses allow you to change the standard order of actions. If a cell contains a formula, the worksheet displays the current result of that formula. If you make a cell current, the formula itself is displayed in the formula bar.

The rule of using formulas in MS Excel is that if the value of a cell really depends on other cells in the table, you should always use a formula, even if the operation can easily be performed in your head. This ensures that subsequent editing of the table will not violate its integrity and the correctness of the calculations performed in it.

Exercise 1.

1. Open the file tables. Create a new sheet and give it a name square.

2. Prepare a sheet for calculating the area of ​​a rectangle according to the sample

3. Set cells B2, B3, B4 to a number format (one decimal place).

4. In cell B2 enter the number 6, in cell B3 enter the number 7.

5. The area of ​​the rectangle is calculated in cell B4. Place the cursor in it. In order to calculate the area of ​​a square, you need to multiply the length of the first side of the rectangle by the value of the second side of the rectangle, i.e. multiply the value of cell B2 by the value of cell B3. Enter a formula in cell B4. For this

• print the sign = ;
• left-click on cell B2;
• print the multiplication sign *;
• left-click on cell B3;
• click Enter key.

The cell will display the result of the calculation using the formula =B2*B3, the number 42.0.

6. Change the value in cell B2, see what has changed. Change the value in cell B3 and see what has changed.

Task 2.

perimeter of a square.

2. Draw up a sheet for calculating the perimeter of a square according to the sample

3. Enter any number in cell B2

4. Enter the formula for calculating the perimeter in cell B3.

5. See the result.

Task 3.

1. Create a new sheet and give it a name amount of information.

2. The amount of information in bytes is known. Draw up a sheet to calculate the amount of information in other units of measurement of information.

Task 4.

1. Create a new sheet and give it a name geography.

2. Draw out a sheet for calculation according to the sample and fill in the empty cells of the table.

Literature.

1. Vasiliev A.V. Working in spreadsheets: workshop / A.V. Vasilyev, O.B. Bogomolova. – M.: BINOM. Knowledge Laboratory, 2007. – 160 p.
2. Zlatopolsky D.M. 1700 tasks for Microsoft Excel/ – St. Petersburg: BHV-Petersburg, 2003 – 544 p.