MINISTRY OF AGRICULTURE OF THE RUSSIAN FEDERATION

FSBEI HE "VYATSK STATE

AGRICULTURAL ACADEMY"

Department of Information Technologies and Statistics

Livanov R.V.

Workshop on working in electronic Microsoft table Office Excel 2007

for students of the Faculty of Economics

KIROV Vyatka State Agricultural Academy

Copyright by Livanov Roman, 2002-2014

Practical part

Laboratory work No. 1.

General acquaintance with Microsoft Excel

Launch Microsoft Excel: “Start” “All Programs” “ Microsoft Office» "Microsoft Office Excel 2007" or using the corresponding shortcut on the desktop. This will open a new workbook containing several worksheets.

In the window that opens, find the following elements:

Control buttons

Tool Ribbon

Scroll bars

Title bar

Active cell address

Sheet work area

Quick panel

Active cell

13. Heading area

columns

Office button

Row header area

Formula bar

Tabs on the ribbon

10. Worker Labels

Insert button

Copyright by Livanov Roman, 2002-2014

Exercise 1. Basics of working with spreadsheets.

1. Rename the worksheet title.

Right-click on the “Sheet1” shortcut at the bottom of the worksheet and select the command from the context menu "Rename".

Delete the old worksheet name, enter the new name “Printers” from the keyboard and press Enter.

2. Prepare table cells for input of initial data.

Select the cell range A1:D1 and set the context menu command

"Cell Format".

"translate according to words" and select the type of horizontal and vertical alignment - center.

On the Font tab of the dialog box, select the font style type -

bold italic and press the button"OK" .

3. Fill out the table with data according to the sample below.

		Names	Quantity,
		Laser printer, b/w
		Laser printer, color.
		Inkjet printer, b/w
		Inkjet printer, color.
		Matrix printer, b/w

4. Calculate sales volume as the product of quantity and price.

Select cell D2 and enter the = sign from the keyboard.

Click LMB on cell B2, enter the sign * from the keyboard and click LMB on cell C2. If everything is done correctly, then the following formula will appear in the formula bar: = B2 * C2.

Click Enter key– the result of the calculation using the formula will appear in the cell:

450000.

Copyright by Livanov Roman, 2002-2014

5. Copy the formula into the remaining cells of the column.

Select cell D2, which contains the result of the calculation.

Place your mouse cursor over the fill marker (the small square in the lower right corner of the selected cell).

Press LMB and, holding it, drag the cursor up to the 6th line inclusive. If everything is done correctly, then all cells of the “Sales Volume” column will be filled with calculated values.

6. Set for numbers in columns“Price” and “Sales Volume” monetary format.

Select the range of cells C2:D6 and set the context menu command

"Cell Format".

monetary, number of decimal places –0, designation –р. and click OK.

7. Insert a new column into the table.

LMB select any cell of the first column (for example A2

or A3).

"Insert columns into sheet"– as a result to the left of the table a new column will appear.

In cell A1, enter the title of the new column No. and set the horizontal and vertical alignment– in the center,

font type – bold italic.

8. Fill in the column“Item No.” using auto-completion.

In cell A2 enter number 1, in cell A3 - number 2.

Select the range of cells A2:A3.

Hover the mouse cursor over the fill marker in the lower right corner of the selected cells, click LMB and, while holding it, drag the cursor to the 6th

lines inclusive. As a result, numbers from 1 to 5 will appear in the column.

Copyright by Livanov Roman, 2002-2014

9. Insert into table new line to design the table title.

Select any cell of the first row by clicking LMB (for example B1

or C1).

On the Home tab, click the Insert button and select the command from the drop-down list "Insert rows into sheet"– as a result above the table a new line will appear.

Select the range of cells A1:E1 and set the context menu command

"Cell Format".

In the dialog box that appears, on the Alignment tab, select the checkbox "merging cells", select the horizontal alignment type

– in the center.

On the “Font” tab, select the font type – bold,

font color is red and click OK.

In the merged cell, enter the table title: Printer sales volume.

10. Set the border for table cells.

Select all table cells except the header (range

A2:E7) and set the context menu command “Format Cells”.

In the dialog box that appears, on the “Border” tab, select the type

11. Set the fill for table cells.

Select the table header (range A2:E2) and set the context menu command “Format Cells”.

In the dialog box that appears, on the “Fill” tab, select a cell fill color and click “OK”.

Fill the rest of the table in the same way using different colors.

Copyright by Livanov Roman, 2002-2014

12. Build a Column Chart "Product names" And

"Quantity".

Select the range of cells B2:C7, enter the command "Insert" "Histogram" and select the histogram type from the drop-down list –

histogram with grouping(first pattern in first line) – in

As a result, a diagram will be constructed.

On the Design tab, click the button "Row column"–

V As a result, the histogram will change the way the data series are displayed.

On the Layout tab, click "Chart title", V

in the drop-down list, select the placement of the name "Above the diagram"

and enter a title for the diagram Printers.

Using the button "Data signatures" on the "Layout" tab, set

V diagram numerical labels of data series with placement"In the center" .

On the Design tab, click the Move Chart button.

V In the dialog box that appears, select the placement of the charton a separate sheet and click the "OK" button - as a result, workbook A new worksheet will appear called “Diagram1”, on which the diagram will be placed.

13. Calculate string“Total” for the “Sales Volume” column.

Go to the Printers worksheet, which contains a table with data.

Enter Total in cell B8, and put dashes in cells C8 and D8.

Place the cursor in cell E8 and click on the AutoSum button on the “Home” tab - as a result, a formula will appear in the cell

SUM(E3:E7).

Press Enter – resulting in the contents of the cells covered dotted frame, will be summed.

Set the “Total” line to a border and your own fill color.

Copyright by Livanov Roman, 2002-2014

14. Change data in a table by column"Quantity" .

Select cell C3 and enter the value 30 in it - after pressing the key

Enter will automatically recalculate the values ​​in the “Sales Volume” column.

Select cell C7, enter the value 7 in it and press Enter.

Make sure that due to changes in data in the table, the chart is rebuilt to take into account the new values.

As a result of all the above actions, formatted

the table should look like this:

		Printer sales volume
		Names	Quantity,
		Laser printer, b/w
		Laser printer, color.
		Inkjet printer, b/w
		Inkjet printer, color.
		Matrix printer, b/w

Task 2. Using conditional formatting in calculations.

1. Go to the new worksheet “Sheet2” and give it a name

"Finance".

2. Select and merge the range of cells A1:E1, set the horizontal alignment to center and enter a table title:

Flow of funds.

3. Select the range of cells A2:E9 and set external and internal borders for the selected cells using the button and template “All borders” on the “Home” tab.

Copyright by Livanov Roman, 2002-2014

4. Design the table header.

In cell A2, enter Month.

In cell B2, enter At the beginning of the period.

In cell C2, enter Revenue.

In cell D2, enter Expenses.

In cell E2, enter End of period.

Select the range of cells A2:E2 and set the display for them -

translate according to words, horizontal and vertical alignment –

centered, font style is italic.

5. Fill in the column with data"Month" using autocomplete.

In cell A3, enter the name of the month January.

Move the mouse cursor over the fill marker of cell A3 and, while holding LMB,

drag the cursor up to the 8th line inclusive. As a result, the names of the months from January to June will appear in the column.

In cell A9, enter Total for the half year and set this cell to word wrap.

6. Fill the table cells with the original numerical data.

In cell B3, enter the value 1000.

Fill in the data in the “Income” and “Expenses” columns according to the sample below.

And enter the following formula into it: =B3+C3–D3 Press the Enter key - the result of the calculation using the formula will appear in the cell: 980. Select cell B4 and enter the formula in it: = E3 Copy the formula into the remaining cells of this column. 8. Set the number cells in the table to a currency format. Select the range of cells B3:E8 and set the context menu command "Cell Format". In the dialog box, on the “Number” tab, select the number format – currency, number of decimal places –0, symbol –$ and click “OK”. 9. Calculate total income and expenses for the six months. Select cell C9, click on the AutoSum button on the tab "Home" and press Enter. Copy the resulting function to the right along the row into cell D9. 10. Calculate the financial result of the activity. Select the range of cells A12:D12, merge them and set the horizontal alignment to right. Enter into the resulting cell: Financial result: profit (+), loss (–). In cell E12, enter the calculation formula yourself financial result as the difference between total income and total expenses for the six months.

Practical work inMS 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 given 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 )

number of values ​​in the argument list and non-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 has a logical IF function, 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.

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 empty cells I6:J9 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 for automatically determining 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

Task execution technology:

1. Launch Microsoft 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 work spreadsheets from 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.

Paste through context menu Add–Sheet two additional sheets. 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