Ministry of Education, Science and Youth Policy

Krasnodar Territory

State budgetary vocational education

institution

Krasnodar Territory

Armavir College of Technology and Service

Guidelines for the implementation of practical work on OUD (P.) 13 Informatics of activities for students by profession 09.01.03 Processing master digital information

MSEXCEL

Compiled by the teacher

Bylenko M.I.

Armavir, 2016

Provided guidelines for the implementation of practical work on OUD (P.) 13 Informatics for students by profession 09.01.03 Master in the processing of digital information.

Guidelines for performing extracurricular independent work developed taking into account the GEF average general education (order of the Ministry of Education and Science of Russia dated May 17, 2012 No. 413) and the requirements of the Federal State Educational Standard of secondary vocational education by profession 09.01.03 Digital Information Processing Master, approved by order of the Ministry of Education and Science No. 798 dated August 02, 2013, registered by the Ministry of Justice (registration No. 29749 dated August 20, 2013)and technical profile vocational education.

The workshop is designed to acquire practical skills in spreadsheet editor MSEXCEL 2007.

Contains theoretical material, equipped with drawings for clarity, practical tasks to consolidate theoretical knowledge.

For SPO students. It may be useful for those who wish to learn how to work in a spreadsheet editor MSexcel.

Reviewed and approved at the meeting of the UMO of natural sciences

"" 2016

Protocol No.

Chairman of the UMO M.M. Kryshtaleva

Practical work No. 2 Using the built-in functions and operations of ET………...

Practical work No. 3 Using logical functions ………………………….

Practical work No. 4 Construction of charts and graphs……………………………..

Practical work No. 5 Sorting and filtering data……………………………

Test on this topic:

Complex practical work on the topic: "Creating tables in EXCEL".

Option 1………………………………………………………………………………………

Option 2………………………………………………………………………………………

Option 3………………………………………………………………………………………

Option 4………………………………………………………………………………………

Subject: Creating and editing spreadsheets, entering formulas into a table, saving a table to disk.

Target: Get practical skills in creating and editing spreadsheets, entering formulas into a table, saving a table on disk.

Working process:

Create a price list like this:

"HORNS AND HOOVES"

28.09.11

dollar exchange rate

4.6 UAH

Name of product

Price in USD

Price in UAH

Checkered notebook

$0,20

0.92 UAH

Lined notebook

$0,20

0.92 UAH

Pencil case

$2,00

9.20 UAH

Pen

$0,50

2.30 UAH

Pencil

$0,20

0.92 UAH

Ruler

$0,30

1.38 UAH

Elastic

$0,40

1.84 UAH

Stages of the task:

Highlight a cell IN 1 and enter a table title in itPrice list of the store "HORN AND HOOVES"

To cell C2 enter functionTODAY(Put a sign «=»   Click the button < f x > on the toolbar. In fieldCATEGORY select Date and time . Select a function in the bottom boxToday ).

To cell IN 3 enter the words " dollar exchange rate ", in C3 the dollar exchange rate today.

To cell C3 apply the currency format (Format  Cell Format ek  Number tab  Numeric format  Monetary  Designation you can choose any).

into cells A5:B5 enter the table column headings.

Select them and apply a bold style and a larger font.

into cells A6:A12 and Q6:Q12 enter data.

To cell C6 enter the formula:= B6*$ C $3 . ($ means that an absolute reference is used).

Highlight a cell C6 and drag the fill handle down to the cellC13 .

Select a range of cellsC6:C13 and apply the currency format to them.

Highlight Header - CellsB1:C1 and run the commandCell Format , tab alignment and set the radio buttonCenter on Selection » (Horizontal Alignment), «Move by word ". Increase the title font.

On the left side of the price list, insert a picture of your choice.

Change the nameSHEET1 on the Price list .

2 . Calculate the statement of the implementation of the turnover plan for kiosk No. 5 in the form:

Filling a columnMi can be done by dragging a marker.

Column valuesVi andOi calculated by the formulas:Vi=Fi / Pi; Oi=Fi-Pi

RenameSHEET 2 inVedomosti .

Save the table in your folder under the namePractical work 1

Show your work to the teacher.

Subject: Using Built-in ET Functions and Operations

Target

enter and edit standard features THIS

Working process:

Task number 1

tab function

on the interval with a step of 0.2.

Arrange the calculations in the form of a table, format it using autoformat and make a heading for the table.

name the worksheetFunction .

Task number 2

Go to new worksheet and name itAge .

Create a list of 10 last names and initials.

Enter it in the table using autocomplete.

Enter the date of birth in the table.

In column Age calculate the age of these people using the functionsTODAY and YEAR

Format the table.

Make a header for the tableAge calculation »

Open file with Practical work 1 , go to sheetVedomosti .

In this table, add the cells below according to the model and perform the appropriate calculations. (Use statistical functionsMAX and AVERAGE )

Reporting year

Deviation from the plan

plan, r.

in fact, r.

performance, %

January

$7,800.00

$8,500.00

February

$3,560.00

$2,700.00

March

$8,900.00

$7,800.00

April

$5,460.00

$4,590.00

May

$6,570.00

$7,650.00

June

$6,540.00

$5,670.00

July

$4,900.00

$5,430.00

August

$7,890.00

RUB 8,700.00

September

$6,540.00

RUB 6,500.00

October

$6,540.00

$6,570.00

November

$6,540.00

RUB 6,520.00

December

$8,900.00

$10,000.00

Maximum

The average

Show your work to the teacher.

Task number 1

Working with the Year and Today functions

Cells that have been filled in gray, must contain formulas!

Create and format a table according to the sample (Enter last names from the list using autocomplete)

Calculate the length of service of the company's employees using the formula:

=YEAR(TODAY()-Date hired)-1900

( The result obtained may not match the values ​​in the task.Why?)

Rename Sheet1 in Information about the experience of employees

to work

Experience

Ivanov I.I.

Director

Petrov P.P.

Driver

Sidorov S.S.

Engineer

Koshkin K.K.

Ch. boo.

Myshkin M.M.

Security guard

Moshkin M.M.

Engineer

Sobakin S.S.

Technician

Losev L.L.

Psychologist

Gusev G.G.

Technician

Volkov V.V.

Supplier

Task number 2

Working with the IF function

1. Copy the table from task No. 1 toSheet2 and rename it to Tariff rates

2. Change the table header

3. Add a columnTariff rates and calculate them like this:

1- if the experience is less than 5 years, 2- if the experience is more than or equal to 5 years

Tariff rates for employees of the company "Horns and Hooves"

to work

Experience

Tariff rates

Ivanov I.I.

Director

Petrov P.P.

Driver

Sidorov S.S.

Engineer

Koshkin K.K.

Ch. boo.

Myshkin M.M.

Security guard

Moshkin M.M.

Engineer

Sobakin S.S.

Technician

Losev L.L.

Psychologist

Gusev G.G.

Technician

Volkov V.V.

Supplier

Task number 3

Working with nested IF functions

1. Copy the table from task No. 2 toSheet3 and rename it totaxes .

2. Change the table header.

3. Add columnsBid , Accrued , Tax , Wage and fill them like this:

Bet = arbitrary number from 500 to ...

Accrued = Rate * Tariff rates

Tax = 0, if Accrued smaller 1000, 12%, if Accrued more 1000 , but less 3000 , and 20 %, if Accrued more or equal3000

5. Show the work to the teacher.

The salary of employees of the company "Horns and Hooves"

Subject: Building charts and graphs

Target : get practical skills in working with MS Excel,

Learn to build, format and edit charts and graphs.

Working process:

Task number 1

1.Open file Practical work 2 , leaf Function .

2.Construct a graph of the function according to the data in the table..

Task number 2

1.Open a new workbook.

2. Enter information into the table according to the sample.

3.Perform the appropriate calculations (use an absolute reference for the dollar exchange rate).

4.Format the table.

5.Build a comparative pie chart of prices for goods and a chart of any other type by the quantity of goods sold.

6.Diagrams are beautifully designed, make headings and captions for data.

7.Sheet1 rename to Cost. Save to file Practice 4

Calculation of the cost of goods sold

Price

in dollars

Price

in rubles

Quantity

Price

Shampoo

$4 ,00

shower set

$5 ,00

Deodorant

$ 2,00

Toothpaste

$ 1,70

Soap

$ 0,40

Dollar exchange rate.

Purchase cost

Task number 3

1.Go to Sheet2. Rename it to Achievement.

2. Enter information into the table.

academic performance

3.Calculate the averages for the performance of each student and for the subjects.

4. Build a histogram of progress in subjects.

5. Build a pyramid chart based on the average performance of each student

6. Construct a pie chart on the average performance in subjects. Add percentages in data labels to this chart.

7. Beautifully arrange all the diagrams.

8.Show the work to the teacher.

Subject: Sorting and filtering data

Target : get practical skills in working with MS Excel,

Learn how to use sorting, data search and apply filters.

Working process:

Task number 1

Open filepriceproduct.xls,which is stored on disk _____ in the folder"Assignments for EXCEL»

Find in it information about the proposed AMD processors (use the command EDIT TO FIND).

Find and replace all occurrences of characters in this table DV D?R on the DV D-RW

Display information about products manufactured by the company ASUS(use autofilter).

Task number 2

Open file Films.xls , which is stored on disk _____ in the folder "Assignments forEXCEL » . Save it in your folder. Do the following with the contents of the file:

On a new sheet with the appropriate name, arrange the information in the table first by stores, then by genre, then by movie.

On a new sheet with the appropriate name, place all the films of the genre Drama that are in the store Style.

On a new sheet with the appropriate title, place information about the results of sales in different stores horror movies and build a comparison chart based on this data.

On a new sheet with the appropriate name, place information about films of the genre Fantasy that were sold for more than 10,000 rubles.

On a new sheet with the appropriate name, place information about films that are sold in stores Our cinema, Film lover, Style.

Find out which stores have the movie on sale Blue velvet.

On a new sheet with the appropriate name, place information about all films, price per unit of which exceeds the average unit price of all films listed in the table.

Show your work to the teacher.

Control work on the topic:

« Spreadsheets. Entering, editing and formatting data. Standard Functions.

Theoretical information:

Data types.

Cell formatting.

Function insertion.

Option number 1

Task number 1

Task number 2

Number of athletes among

student youth.

Girls

Youths

Who is bigger

Italy

Girls

Russia

Youths

Denmark

Girls

Ukraine

Youths

Sweden

Girls

Poland

Youths

Minimum

Maximum

Task number 3

Create a table and format it according to the sample.

Column "Number of days of stay » is calculated using the DAY function and the values ​​in the Arrival Date and Departure Date columns

Column "Price" calculated according to the condition: from 1 to 10 days - 100% of the cost, from 11 to 20 days - 80% of the cost, and more than 20 - 60% of the total cost of the room for this number of days.

Register of residents

at the Horns and Hooves Hotel.

Number

Room rate per night

Arrival date

Departure date

Number of days of stay

Price

Ivanov I.I.

10 UAH

2.09.2004

2.10.2004

Petrov P.P.

20 UAH

3.09.2004

10.09.2004

Sidorov S.S.

30 UAH

1.09.2004

25.09.2004

Koshkin K.K.

40 UAH

30.09.2004

3.10.2004

Myshkin M.M.

100 UAH

25.09.2004

20.10.2004

total cost

Task number 4

Make a multiplication table

Multiplication table

Control work on the topic:

“Spreadsheets. Entering, editing and formatting data. Standard Functions.

Theoretical information:

Rules of safety and behavior in the COT.

Operations with cells and ranges.

Data types.

Cell formatting.

Function insertion.

Option number 2

Task number 1

Construct a table of function values ​​on the interval [-2, 2] with a step of 0.4:

Apply one of the autoformat types to the table.

Task number 2

Create a table and format it according to the sample.

Calculations in a column are performed according to the formula:

Reporting year, tons / Previous year, tons,

And in the column Fulfillment of deliveries using the IF function (greater than or equal to 100% - done, otherwise - no)

Fulfillment of the contract for the supply of fruits and vegetables

for the needs of children's institutions of the Solomensky district

Previous year, tons

Reporting year, tons

Reporting year in % to the previous one

Fulfillment of deliveries

cucumbers

9,7

10,2

105,15

Done

Apples

13,4

15,3

114,18

Done

plums

5,7

2,8

49,12

Not done

Carrot

15,6

14,6

93,59

Not done

Onion

20,5

21

102,44

Done

Total

64,9

63,9

98,46

Not done

Task number 3

Create a table for calculating the optimal weight and format it according to the sample.

Column "Optimal weight » is calculated by the formula:

Optimal weight \u003d Height- 100

If the weight of a person is optimal, then in the column"Adviсe" opposite his name should appear the entry "Optimal weight". If the weight is less than optimal - “You need to recover by”, indicating in the next cell the number of missing kilograms. If the weight is more than optimal - “You need to lose weight by” indicating in the next cell the number of extra pounds.

How much do we weigh?

Task number 4

Make a multiplication table

Formulas and absolute references are used to fill the table.

Multiplication table

Comprehensive practical work on the topic:

"Creating tables in EXCEL ».

Option number 1

In the folder MY DOCUMENTS create a folder KR EXCEL and save all tables in it.

Values ​​in shaded cells are calculated using formulas!

Exercise 1.

2. Format the table.

3. Build a comparative chart (histogram) by the levels of sales of different products in the regions and a pie chart by the average number of products.

Sale of goods for winter sports.

Task 2

1. Create a table according to the sample. Perform the necessary calculations.

Total Cost = Total Mileage * Cost Rate

2. Format the table.

3. Construct a pie chart "Total car mileage" indicating the percentage of each and a bar chart "Car repair costs".

4. Use the Filter tool to identify the brands of cars whose mileage exceeds 40,000 km and the brands of cars that have costs for Maintenance exceed the average.

“Accounting for the cost of maintenance and current repairs of vehicles”

car model

Total mileage

thousand km

Cost rate

on the 1 000 4

1. Create a table and format it according to the sample.

2. The data in the Age column is calculated using the functions TODAY and YEAR

3. Sort the data in the table by age.

4. Build a comparative histogram by age and use employee positions as labels on the X axis.

5. Using the filter, display information only about military service employees (Gender - m, age from 18 to 45 years).

>

base data background

Design, set the task for the automated receipt of the document "Statement of the implementation of the retail trade turnover plan for ____ month 20 __ for ___ district consumer union of ___ district", including the details: name of the district consumer union, name of the district, order number (or organization code), organization name, date , sales plan for the month, actual implementation of the plan, percentage of completion, deviations, total for the organization. Additional tasks: 1. Classify the details on the grounds and signs; 2. Description of the directory "List of goods by groups of goods"; 3. Build codes of District Consumer Unions (Organizations); 4. Describe the list of PD and possible changes in the document "Consignment note",

Attributes-attributes characterize the qualitative properties of the reflected entities. Requisites-bases - quantitative values ​​that characterize this entity.

Details classification

Directory "List of goods by groups of goods"

Directory "List of product groups"

Sample Fill

Raypotrebsoyuz codes

Consignment note - a contract for the carriage of goods by road. For international transportation, it is drawn up in 4 original copies. The first copy remains with the consignor, the second copy follows with the cargo, the third and fourth copies remain with the carrier.

A waybill is required when the parties hire a transport organization to transport goods. The seller, on the basis of the TTN, writes off the goods from the balance at the time of the transfer of ownership established by the supply (purchase and sale) agreement. The shipper writes off the goods from his warehouse, the recipient of the goods receives inventory items. The transport organization calculates the cost of its services. For the driver of the freight carrier, the bill of lading is the basis for the carriage of goods, which is necessary for presentation to employees of the internal affairs bodies.

Primary documents: supply contract, bill of lading, act (carrier's report) on the provision of services for the delivery of goods, powers of attorney.

When concluding a contract, the following information is added to the "Commodity Section" section: Name of the consignor (automatically entered under the contract), Name of the consignee (entered automatically according to the contract), Date of compilation, Name of the payer Payer (selected from the directory), Product name (selected from the directory) , Quantity, Price.

The following fields are automatically calculated: Amount (Quantity x Price), total payable (total for the Amount field).

Ministry of Education, Science and Youth Policy of the Krasnodar Territory GBPOU KK "Armavir Machine-Building College" METHODOLOGICAL DEVELOPMENT Guidelines for the implementation of individual tasks on the topic: "EXCEL ELECTRONIC TABLES" in the disciplines: "Computer Science" and "Information Technology in Professional Activities" for students of all specialties :

REVIEWED by the cyclic methodological commission of the "Natural Science Disciplines" Protocol No. dated "" ____ 20__. Chairman of the CMC E.A. Strukov _____________ I APPROVE Deputy. director for SD _____________ L.A. Taranenko "____" ____________ 20 ___ Author: teacher Strukova E.A. Reviewers: teacher of GBPOU KK "Armavir Engineering College" Nadezhdina OV

Introduction These guidelines for conducting individual practical classes in the disciplines of "Informatics" and are intended for students of all specialties "Information technology in professional activities." Knowledge in the field of economic informatics and computer systems, organization and maintenance of accounting and economic analysis in computer environment are necessary in the professional activities of an accountant, manager, merchandiser. The works are of a reproductive nature, since when performing them, students use detailed instructions that indicate: the purpose of the work, brief theoretical information, the order of work, test questions, educational and special literature. Practical classes are devoted to an in-depth study of the basic information technologies of spreadsheets. Microsoft Excel used in the work of specialists of various profiles (economists, financiers, accountants ... etc.). Spreadsheets are an ideal environment for performing calculations of varying complexity. Microsoft Excel includes a developed mathematical apparatus, specialized information technologies for statistical analysis, are available graphic aids presentation and analysis of data. Microsoft Excel is widely used to automate accounting. Calculation documents (development and calculation tables) can be converted into electronic form, saved as templates for multiple use. Microsoft Excel supports electronic document management, providing the creation, description, search and storage of documents of a complex structure, the relationship of settlement documents. After practical classes, students should: Know: basic methods of working with Microsoft Excel. be able to: enter information into spreadsheets; enter formulas; edit formulas; use functions; work with autofilter; work with the form use a set of built-in Microsoft features excel; solve problems of modeling and data analysis; 4

1. Make a price list according to the model: Task 16 Price list of the store "HORN AND HOOVES" 09/28/11 4.6 hryvnia. Dollar exchange rate Price in c.u. Price in UAH 0.92 UAH 0.92 UAH 9.20 UAH 2.30 UAH 0.92 UAH 1.38 UAH 1.84 UAH $0.20 $0.20 $2.00 $0.50 $0.20 $0.30 $0.40 Product name Checkered notebook Ruled notebook Pencil case Pen Pencil Ruler Elastic band Steps to complete the task: 1. Select cell B1 and enter the heading of the Pricelist table into it store "HORN AND HOOVES" 2. In cell C2, enter the function TODAY (Put the sign "="  Press the button on the toolbar. In the CATEGORY field, select Date and Time. In the lower field, select the function Today). 3. In cell B3, enter the words "Dollar rate", in C3 - the dollar rate for today. 4. Apply the currency format to cell C3 (Format  Cell format  Number tab  Numeric format  Monetary  You can choose any designation). In cells A5:B5, enter the table column headings. 5. 6. Select them and apply a bold style and a larger font. 7. Enter the data in cells A6:A12 and B6:B12. 8. In cell C6, enter the formula: = B6*$C$3. ($ means that an absolute reference is used). 9. Select cell C6 and drag the fill handle down to cell C13. 10. Select the range of cells C6:C13 and apply the currency format to them. 11. Select the header - cells B1:C1 and execute the Cell Format command, the Alignment tab and set the switch "Center on selection" (Horizontal alignment), "Wrap by words". Increase the title font. 12. On the left side of the price list, insert a picture of your choice. 13. Change the name of SHEET1 to Pricelist. 5

2. Calculate the statement of the implementation of the sales plan of the kiosk No. 5 in the form: Task 17 Deviation from the plan execution,% Vi Oi No. Month i Mi 1 January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11 November 12 December Reporting year actually Fi 8,500.00 rub. $2,700.00 $7,800.00 $4,590.00 $7,650.00 $5,670.00 $5,430.00 RUB 8,700.00 RUB 6,500.00 $6,570.00 RUB 6,520.00 $10,000.00 plan Pi 7 800,00 r. $3,560.00 $8,900.00 $5,460.00 $6,570.00 $6,540.00 $4,900.00 $7,890.00 $6,540.00 $6,540.00 $6,540.00 $8,900.00 1. Filling in the Mi column can be done by dragging the marker. 2. The values ​​of columns Vi and Oi are calculated by the formulas: Vi=Fi / Pi; Oi=Fi - Pi 3. Rename SHEET2 to Sheet. 4. Save the table in your folder under the name Practical work 1 Assignment No. 18 1. Go to a new worksheet and name it Age. 2. Create a list of 10 last names and initials. 3. Enter it into the table using autocomplete. 4. Enter dates of birth in the table. 5. In the Age column, calculate the age of these people using the functions TODAY and YEAR 6. Format the table. 7. Make a heading for the table “Calculation of age” Full name Date of birth Age No. 1 2 3 ... 10 Ivanov I.I. Petrov P.P. Sidorov S.S. Myshkin M.M. 6

Task number 19 Cells filled with gray must contain formulas! 1. Create and format a table according to the model (Enter last names from the list using autocomplete) Calculate the length of service of the company's employees using the formula: 2. = YEAR (TODAY (Date of employment) 1900 (The result may not match the values ​​in the task. Why?) 3. Rename Sheet1 to Information about the experience of employees Information about the experience of employees of the company "Horns and Hooves" OI F Ivanov I.I. Petrov P.P. Sidorov S.S. Koshkin K.K. Myshkin M.M. Moshkin M.M. Sobakin S.S. Losev L.L. Gusev G.G. Volkov V.V. t s o n g l o D Director Driver Engineer Chief. boo. Security guard Engineer Technician Psychologist Technician Procurement officer a m e i r p u a t t o a b D a r a n 01 January 2003 02 February 2002 03 June 2001 05 September 2006 01 August 2008 04 December 2005 November 06, 2007 April 14, 2005 July 25, 2004 May 2, 2001 5 6 7 1 0 2 0 3 4 7 Task No. 20 Working with the IF function 1. Copy the table from assignment No. 1 on Sheet2 and rename it to Tariff rates 2. Change the heading of the table 3. Add a column Tariff rates and calculate them in this way: 1 if the experience is less than 5 years, 2 if the experience is greater than or equal to 5 years Tariff rates for employees of the firm "Roga and hooves" OI F Ivanov I.I. Petrov P.P. Sidorov S.S. Koshkin K.K. Myshkin M.M. Moshkin M.M. t s o n g l o D Director Driver Engineer Chief. boo. Guard Engineer a m e ir p u a t to a b D a r a n January 01, 2003 February 2, 2002 June 3, 2001 September 5, 2006 August 1, 2008 December 4, 2005 e y n f i ra T ikvat s 2 2 2 1 1 1 zh a t S 5 6 7 1 0 2 7

Sobakin S.S. Losev L.L. Gusev G.G. Volkov V.V. Technician Psychologist Technician Procurement 06 November 2007 14 April 2005 25 July 2004 02 May 2001 0 3 4 7 1 1 1 2 8

Working with Nested IF Functions Task #21 1. Copy the table from Task #2 to Sheet3 and rename it to Taxes. 2. Change the table header. 3. Add columns Rate, Accrued, Tax, Salary and fill them in this way: Rate = arbitrary number from 500 to ... Accrued = Rate * Tariff rates Tax = 0 if Accrued less than 1000, 12% if Accrued more than 1000, but less than 3000, and 20% if Accrued more than or equal to 3000 4. Save the document in your folder. 5. Show the work to the teacher. Wages of employees of the company "Horns and hooves" Petrov P.P. Driver Sidorov S.S. Engineer Koshkin K.K. Ch. boo. Myshkin M.M. Security guard Moshkin M.M. Engineer Sobakin S.S. Losev L.L. Gusev G.G. Volkov V.V. Technician Psychologist Technician Procurement n a m e irp a ta D utobar January 01, 2003 February 2, 2002 June 3, 2001 September 5, 2006 August 1, 2008 December 4, 2005 November 06, 2007 April 14, 2005 July 25, 2004 May 02, 2001 1 2 5000 1000 3000 4000 3000 4000 2000 3000 500 3500 ONEL S and HA HA 10000 2000 240 6000 1200 800 3000 360 800 4000 24000 3000 360 0 500 7000 1400 I AN N T 8000 1760 4800 3200 2640 3200 1760 2640 500 5600 5 6 7 1 0 2 0 3 4 7 Task No. 22 Calculation of the cost of goods sold Price in rubles Quantity Cost Price in dollars . $4.00 $5.00 $2.00 $1.70 $0.40 Product Shampoo Shower set Deodorant Toothpaste Soap Dollar rate. Purchase cost 9

Task number 23 1 .. Rename foxes in Progress. 2. Enter information into the table. Progress Full name Mathematics Informatics and Physics Secondary Ivanov I.I. Petrov P.P. Sidorov S.S. Koshkin K.K. Myshkin M.M. Moshkin M.M. Sobakin S.S. Losev L.L. Gusev G.G. Volkov V.V. Average for the subject 3. Calculate the average values ​​for the progress of each student and for the subjects. 4. Build a histogram of progress in subjects. 5.Construct a pyramid chart for the average performance of each student 6.Construct a pie chart for the average performance in subjects. Add percentages in data labels to this chart. 7. Beautifully arrange all the diagrams. ten

Examination on the topic: “Spreadsheets. Entering, editing and formatting data. Standard Functions. Task number 1 Create a table and format it according to the model. Fill in the contents of the column "Who is more" using the IF function. The number of athletes among young students. a n a r t S Italy Russia Denmark Ukraine Sweden Poland 28% 34% 21% 36% from Cash lob Girls Boys Girls Boys Girls Boys Task #2 Create a table and format it according to the model.  The column "Number of days of stay" is calculated using the DAY function and the values ​​in the columns "Date of arrival" and "Date of departure"  The column "Cost" is calculated according to the condition: from 1 to 10 days - 100% of the cost, from 11 to 20 days - 80% of the cost, and more than 20 - 60% of the total cost of the room for this number of days. Registration sheet for residents of the hotel "Horns and hooves". OI F Ivanov I.I. Petrov P.P. Sidorov S.S. Koshkin K.K. Myshkin M.M. R e m o N 1 2 4 8 13 Ik t u s w a r e m o n t so m i o t C UAH 10 UAH 20 UAH 30 UAH 40 UAH 100 h i l o C a i n a vi jor p t so m i o t C a i t b u a t a D i a t y b a t a t a D 2.10.2004 2.09 .2004 10.09.2004 3.09.2004 25.09.2004 1.09.2004 30.09.2004 3.10.2004 25.09.2004 20.10.2004 Total cost 11

Task № 3 Formulas and absolute references are used to fill the table. Make a multiplication table Multiplication table 1 2 3 ... 9 1 1 2 3 9 2 2 4 6 3 3 6 9 18 27 4 4 8 12 36 5 5 10 15 45 6 6 12 18 54 7 7 14 21 63 8 8 16 24 72 9 9 18 27 81 12

Task number 1 Create a table and format it according to the model. Calculations in the column Reporting year in % of the previous one are performed according to the formula: Reporting year, tons / Previous year, tons, And in the column Fulfillment of deliveries using the IF function (greater than or equal to 100% - completed, otherwise - no) Fulfillment of the contract for the supply of vegetables and fruits for the needs of children's institutions in the Solomensky district ya and tskud o r P Cucumbers Apples Plums Carrots Onions Total, n o t u t s d s h o g d y er i P s ch 9.7 13.4 5.7 15.6 20.5 64.9 s n e c t o n n o t, d o g 10.2 15.3 2.8 14.6 21 63.9 s derpk % w o g y n e c t O 105.15 114.18 49.12 93.59 102.44 98.46 ат соп Completed Completed Not completed Not completed Completed Not completed Task No. 2 Create a table for calculating the optimal weight and format it according to the sample.  The column "Optimal weight" is calculated by the formula: Optimal weight = Height 100  If the weight of a person is optimal, then in the column "Tips" opposite his name should appear the entry "Optimal weight". If the weight is less than optimal - “You need to recover by”, indicating in the next cell the number of missing kilograms. If the weight is more than optimal - “You need to lose weight by” indicating in the next cell the number of extra pounds. How much do we weigh? Full name Weight, kg Height, cm Optimal weight, kg Tips Weight difference, kg Ivanov I.I. Petrov P.P. Sidorov S.S. Koshkin K.K. Myshkin M.M. 65 55 64 70 78 160 155 164 170 180 60 55 64 70 80 You need to lose weight Optimal weight Optimal weight Optimal weight You need to gain weight 5 2 13

Formulas and absolute references are used to fill the table. 2 2 4 6 3 3 6 9 1 2 3 … 9 1 1 2 3 9 18 27 36 45 54 16 24 72 9 9 18 27 81 Complex practical work on the topic: "Creating tables in EXCEL". Option number 1 In the folder MY DOCUMENTS, create a folder EXCEL KR and save all the tables in it. Values ​​in shaded cells are calculated using formulas! Task 1. 1. Create a table according to the model. Perform the necessary calculations. 2. Format the table. 3. Build a comparative chart (histogram) by the levels of sales of different products in the regions and a pie chart by the average number of products. Sale of goods for winter sports. Region Skiing Skates Kyiv Zhytomyr Kharkiv Dnepropetrovsk Odessa Simferopol Average Task 2 3000 200 400 500 30 40 7000 600 400 3000 1000 500 Total Sledge 200 700 500 400 300 266 1. Create a table according to the sample. Perform the necessary calculations. Total Costs = Total Mileage * Cost Rate 2. Format the table. 3. Construct a pie chart "Total car mileage" indicating the percentage of each and a bar chart "Car repair costs". 4. Use the Filter tool to identify vehicle brands with more than 40,000 km and vehicle brands with higher than average maintenance costs. “Accounting for the cost of maintenance and current repairs of cars” 14

No. Car brand Total mileage thousand km Cost rate per 1,000 km, UAH. Total costs, thousand UAH. 1. Zhiguli 2 Moskvich 3 Mercedes 4 Opel Average 12 50 25 45 2000 1800 3000 2500 15

Task 3 1. Create a table and format it according to the model. 2. The data in the Age column is calculated using the functions TODAY and YEAR 3. Sort the data in the table by age. 4. Build a comparative histogram by age and use employee positions as labels on the X axis. 5. Using the filter, display information only about military service employees (Gender m, age from 18 to 45 years). Information about the employees of the company "Horns and Hooves" Name Arnoldov Taras Bulbovich Golubkov Lenya Mavrodievich Barabulya Sam Jonovich Simeonenko Zhorzh Zhorikovich Rybak Karp Karpovich Grafchenko Drakul Drakulovich KaraMurza Lev Filippovich Sidorov Petr Ivanovich Beautiful Vasilisa Ivanovna Poppins Mary Jonovna Position Date of birth. Gender Director m m Driver m Procurement Chief. boo. m m Engineer m Manager Security guard m m Technician w Secretary Psychologist w

Filling a column Mi can be done by dragging a marker.

Column values Vi and Oi calculated by the formulas: Vi=Fi / Pi; Oi=Fi-Pi

Rename SHEET2 in Vedomosti.

Save the table in your folder under the name Practical work 1

Show your work to the teacher.

Subject: Using Built-in ET Functions and Operations

Target: get practical skills in working with MS Excel,

enter and edit standard ET functions

Working process:

Task number 1

tab function

on the interval with a step of 0.2.

Arrange the calculations in the form of a table, format it using autoformat and make a heading for the table.

name the worksheet Function.

Task number 2

Go to new worksheet and name it Age.

Create a list of 10 last names and initials.

Enter it in the table using autocomplete.

Enter the date of birth in the table.

In column Age calculate the age of these people using the functions TODAY and YEAR

Format the table.

Make a header for the table Age calculation»

Task number 3

Open file with Practical work 1, go to sheet Vedomosti.

In this table, add the cells below according to the model and perform the appropriate calculations. (Use statistical functions MAX and AVERAGE)

Show your work to the teacher.

Topic: Using Boolean Functions

Task number 1

Working with the Year and Today functions

Cells filled with gray must contain formulas!

Create and format a table according to the sample (Enter last names from the list using autocomplete)

Calculate the length of service of the company's employees using the formula:

=YEAR(TODAY()-Date hired)-1900

( The result obtained may not match the values ​​in the task. Why?)

Rename Sheet1 in Information about the experience of employees

Task number 2

Working with the IF function

1. Copy the table from task No. 1 to Sheet2 and rename it to Tariff rates

2. Change the table header

3. Add a column Tariff rates and calculate them like this:

1- if the experience is less than 5 years, 2- if the experience is more than or equal to 5 years