Capable of movement. Instructions for completing the test

Detailed solution part 1 (page) 3 on the world around us workbook for 4th grade students, authors N.F. Vinogradova, G.S. Kalinova. 2016

• Gdz on the surrounding world for grade 4 can be found

1. Task. Look at the pictures. What important object of living nature has not been drawn by man? Draw this object.

Answer. This object is a person

2. Task. Complete the diagram.

3. Task. Write what substances the human body exchanges with the environment.

Answer. Nutrients enter the human body with food - proteins, fats, carbohydrates, mineral salts, vitamins, water. During the process of breathing, oxygen enters the body, and oxygen is also partially absorbed by the skin.

The following are excreted from the body: undigested remains food, urine, which is formed in the kidneys; in the process of respiration - carbon dioxide and water; the skin secretes sweat and oil; The lacrimal gland secretes tear fluid, which moistens the eye; salivary glands- saliva.

4. Assignment. Cross out the names of those bodies that do not belong to nervous system.

Answer: heart (cross out), trachea (cross out), muscles (cross out).

5. Assignment. Fill out the chart.

6. Assignment. Write down the numbers that indicate in the picture: brain, spinal cord, nerves.

Answer. Brain – 1, spinal cord – 2, nerves – 3.

7. Assignment. Explain why nerves are compared to electrical wires. Prepare an oral history.

Answer. In the human body, information is transmitted along nerves. Nerve impulse represents nothing more than an electrical discharge. The peculiarity of the transmission is that this discharge is transmitted from nerve to nerve not directly, but through chemical substances, located on the border between the nerves.

Exercise. Have your say. From the head and spinal cord signals are transmitted to organs at very high speed. What does this mean for a person?

Answer. Signals are transmitted at high speed in order to enable the body to respond to any stimuli in a timely manner. For example, a person touched a hot object and immediately pulled his hand away. The eye saw a flying speck and immediately closed. They told you something and you immediately responded. In this way, we protect ourselves from any danger, navigate environment, we lead a certain lifestyle.

8. Assignment. Label the parts of the skeleton indicated by numbers in the picture.

2. Spine

3. Ribs that make up the chest

4. Upper free limb (shoulder, forearm, hand)

5. Lower free limb (thigh, lower leg, foot)

9. Assignment. Answer the questions. Discuss the answers.

How do you understand the expression: “He has good posture”?

Asya that's it free time spends time in front of the TV or at the computer, and Alyosha loves to play football. Explain which of the children will be physically developed.

Good posture means correct location parts of the skeleton relative to each other and in space. There is no curvature of the spine or defects in individual skeletal bones. This is achieved through training physical culture and sports, constant care for one’s physical fitness, adherence to work culture, and the ability to choose a working position.

Alyosha is certainly better physically developed. This is explained by the fact that for the development of the skeleton and muscles ( musculoskeletal system) regular physical education and sports are required. When a person plays sports, his muscles and bones regularly expand blood vessels, through which building substances (beans, fats, carbohydrates, mineral salts) arrive, as well as oxygen, which ensures metabolism. Consequently, bones and muscles will grow. When engaging in physical education, the nervous system sends signals that satisfy the needs of muscles for development. That is, the whole body is tuned to development.

Asya does not exercise physically, so her development will lag behind Aleshin's.

10. Assignment. Mark the correct answers to the question: “What contributes to the development of the human skeleton and muscles?”

Exercise and sport games(Right).

Eating right (right).

Question. How was task 10 completed? Mark only one statement.

11. Assignment. Explain how you understand the words of the ancient Greek scientist Aristotle: “Nothing depletes and destroys a person like prolonged physical inactivity.”

Answer. In order for the human body to be in good physical fitness, long time could maintain performance, cope with various diseases It is necessary to constantly engage in physical education and sports. Exercises allow the muscles to be in the right tone, the nervous system to be ready for quick response on external manifestations, performing large volumes physical work. In trained muscles muscle fibers, the blood vessels are elastic, the heart muscle is strong, the vital capacity of the lungs is significant.

If you do not engage in physical education, muscles become flabby, more cells die than are formed, blood vessels become brittle and fragile. The vital volume of the lungs is constantly decreasing. Even a slight load causes shortness of breath, increased heart rate, and rapid fatigue.

12. Assignment. Underline the name of the products that need to be included in the menu in order to receive daily required amount squirrel.

Answer. Fish, meat, eggs, cheese, milk.

13. Assignment. Fill out the table, arranging the names of the listed products in accordance with what vitamin they contain in large quantities.

14. Assignment. Write down the numbers that indicate in the picture: stomach, esophagus, colon.

1. Esophagus

2. Stomach

3. Large intestine

Question. What other digestive organs are shown in the diagram? Write their names.

Answer. Oral cavity (contains teeth, tongue, salivary glands), pharynx, pancreas, small intestine, liver.

Question. How was task 14 completed? Mark only one statement.

Quickly, correctly, independently.

15. Assignment. Prepare an answer to the question: “Prepare an answer to the question: “Why is it not recommended to read, watch TV, or talk while eating?”

Answer. While eating, it is not recommended to read or watch TV, because when performing these actions, the brain receives information that becomes the main one, and this leads to the inhibition of the secretion of saliva, gastric juice, digestive juices of the pancreas and liver. The walls of the stomach and intestines work more slowly.

If you talk while eating, food can get into the larynx or even trachea, which is very dangerous.

Exercise. Let's work on the project.

Project topics

Definitely not. The thing is that the human body does not synthesize vitamins itself, but receives them from food. The amount of a particular vitamin can significantly affect a person’s health and mood. You can buy vitamins at the pharmacy, but many scientists believe that this puts a strain on the liver. Ideally, you should eat the right balanced foods. For example, citrus fruits are rich in vitamin C, fish contains a lot of vitamin D, carrots contain a lot of vitamin A, and so on. A lack of these substances in the body leads to diseases such as scurvy and rickets.

Scurvy is a disease caused by acute deficiency of vitamin C ( ascorbic acid). Lack of vitamin C leads to disruption of collagen synthesis, connective tissue loses its strength. Symptoms: lethargy, fast fatiguability, weakening of muscle tone, rheumatoid pain in the sacrum and extremities (especially the lower ones), loosening and loss of teeth; fragility of blood vessels leads to bleeding gums and hemorrhages in the form of dark red spots on the skin. Treatment and prevention - normal supply of vitamin C to the body.

There is also information that sailors often suffered from scurvy due to a lack of table salt.

Saturation of food with vitamins is one of the conditions healthy diet, allowing you to maintain physical and mental activity. Vitamins are substances that have certain similar properties:

– occupy important place in metabolism;

- are produced in human body in small quantities, which necessitates their targeted administration;

– show their role in microscopic quantities.

The importance of vitamins for optimal human life is evidenced by the fact that when they are deficient in the body, diseases called avitaminosis and hypovitaminosis develop.

Causes of vitamin deficiency in humans:

1. The presence of diseases of the digestive system, as a result of which vitamins in food are poorly absorbed, partially destroyed, and are also synthesized by the intestines in low quantities. Eg, helminthic diseases- This is a serious obstacle to the absorption of vitamins. Some medications inhibit the activity of vitamins.

2. Vitamin deficiency in the diet caused by:

Wrong set of products. A lack of fruits and vegetables leads to a deficiency of vitamin C. If you adhere to only a vegetarian diet, a deficiency of vitamin B12 appears. If priority is given to refined products (products made from premium flour, refined rice, sugar), there is a high probability of B vitamin deficiency.

Seasonal changes in the content of vitamins in products. In spring and winter, the level of vitamin C in fruits decreases, and the range of this product group itself also decreases. During this same period, eggs and milk are poor in vitamins A and D.

Improper culinary processing and storage of dishes, leading to a decrease in vitamins B, C, A in food. For example, with prolonged heat treatment berries during the preparation of jam, the amount of vitamin C decreases significantly.

Imbalanced diet. Vitamins in food may be present in sufficient quantities, but their absorption will be difficult due to improper amounts (both excess and deficiency) of other vitamins, as well as due to long-term shortage complete proteins.

Special measures to prevent vitamin deficiency in food. In order to enhance the value of some food products They are specially fortified. This is how vitamins are enriched in many products for baby food: porridge, puree, nutritional mixtures, drinks. For example, in milk for child use vitamin D2 is administered so that half a liter of the drink contains daily dose. The need for fortification of products also arises if they are intended for use in special conditions(on expeditions, during wintering). Special fortification of food with vitamin C is carried out in sanatoriums, maternity hospitals, hospitals, dietary canteens, as well as canteens of educational institutions.

16. Assignment. Underline the name of the organs of the digestive system.

Answer. Stomach, esophagus, teeth, small intestine.

17. Assignment. Mark the correct statements.

Caries is a dental disease. (Right)

Caries occurs in people who do not take good care of their teeth. (Right)

18. Assignment. Mark the correct statement.

During the digestion process, proteins, fats and carbohydrates are broken down (broken down) into simpler substances. (Right)

19. Assignment. Finish the sentence.

Answer. In addition to proteins, fats and carbohydrates, our body needs water, vitamins, and minerals.

20. Question. In 1860, a drill for dental treatment appeared. What century was this in? Could teeth be treated with a drill in the 16th century?

Answer. 1860 is the 19th century, so in the 16th century they could not treat teeth with a drill.

21. Assignment. Mark the correct statements. Prepare explanations for your answers.

The liver cleanses the blood from harmful substances. (Blood filtration occurs in the liver; here almost all blood is purified from harmful substances). (Right)

Diseased teeth are a source of infection. (with food pathogens enter the esophagus and then the stomach and intestines infectious diseases). (Right)

22. Assignment. Finish the sentence.

Answer. In the nasal cavity, the air is warmed and purified. When you breathe, oxygen is absorbed and carbon dioxide is released.

23. Assignment. Note the rules for respiratory protection.

You need to breathe through your nose. (Right)

No smoking. (Right)

Must be done wet cleaning premises. (Right)

You cannot stay in an unventilated area for a long time. (Right)

24. Assignment. Cross out the names of the organs respiratory system. Label them on the drawing.

Answer: larynx, lungs, nasal cavity, trachea, bronchi.

On the image:

1. Nasal cavity

2. Larynx

Question. How was task 24 completed? Mark only one statement.

Quickly, correctly, independently. (+)

25. Assignment. Mark the correct answers to the questions.

How does tobacco smoke affect the respiratory system?

Reduces protective properties.

Why is it necessary to cover your nose with a tissue when sneezing and coughing?

To avoid infecting others.

What gas is absorbed during respiration?

Oxygen.

Where is the air warmed and cleared of dust and bacteria?

In the nasal cavity.

26. Assignment. Prepare a leaflet “How to take care of the respiratory system.”

1. You need to breathe through your nose.

2. Cover your nose with a tissue when coughing or sneezing.

3. Systematically engage in physical education and sports.

4. Ventilate the premises.

5. Do not smoke yourself or be in a room with people who smoke.

Exercise. Let's work on the project.

Project topics.

Oxygen consumption and output carbon dioxide How by-product called the process of breathing. The main respiratory organs of fish are gills.

Fish have two sets of gills - one on each side of the body behind the head. These delicate organs are protected by hard plates called operculum.

Each set of gills includes four bony arches. Each of these arches supports two rows of feather-shaped gill fibers called primary lamellae (petals).

Each primary lamina is in turn covered with tiny lamellae (secondary lobes) through which narrow blood capillaries pass.

It is through the thin membrane of the secondary petals that gas exchange occurs between the blood and external environment. The blood in the secondary petals flows in the opposite direction to the direction of movement of water passing along the surfaces of the lamellae.

As a result, a large diffusion gradient of oxygen and carbon dioxide occurs between these two liquids. This “counter-flow” system greatly increases the efficiency of gas exchange.

The respiratory system of amphibians is represented by lungs and skin, through which they are also able to breathe. The lungs are paired hollow sacs with a cellular inner surface dotted with capillaries. This is where gas exchange occurs. The breathing mechanism of frogs is a forced one and cannot be called perfect. The frog draws air into the oropharyngeal cavity, which is achieved by lowering the bottom oral cavity and opening of the nostrils. Then the floor of the mouth rises, and the nostrils close with valves again, and air is forced into the lungs.

Let's look at the example of a whale.

The skull of whales is adapted to allow breathing to occur when the nostrils are raised out of the water without bending the neck (the nostrils are shifted to the crown of the head).

The maxillary, premaxillary and mandibular bones are elongated due to the development of the fining apparatus (whalebone) or numerous single-vertex teeth. The nasal bones are reduced, the parietal bones are shifted to the sides so that the superoccipital bone is in contact with the frontal bones.

The blowhole - one or two external nasal openings - is located at the top of the head and opens only at the moment of a short respiratory act of exhalation - an inhalation performed immediately after surfacing. In cool weather, when exhaling, condensed steam flies up, forming a so-called fountain, by which whalers distinguish the type of whale.

Sometimes sprayed splashes of water also fly up with this steam. The rest of the time while it lasts breathing pause and the animal dives, the nostrils are tightly closed with valves that do not let water into Airways. Due to the special structure of the larynx airway separated from food. This allows you to breathe safely if water or food is in the mouth. The nasal canal of most species is connected to special air sacs and, together with them, plays the role of a sound signaling organ.

The lungs of cetaceans are very elastic and elastic, adapted to rapid compression and expansion, which provides a very short respiratory act and allows air to be renewed by 80-90% in one breath (in humans only by 15%). In the lungs, the muscles of the alveoli and cartilaginous rings are highly developed, even in the small bronchi, and in dolphins - in the bronchioles.

Cetaceans can stay under water for a long time (sperm whales and bottlenose whales up to 1.5 hours) with the same supply of air: large capacity lungs and the rich content of muscle hemoglobin allow them to be carried away from the surface increased amount oxygen, which is consumed very sparingly: during diving, the activity of the heart (pulse) slows down by more than half and the blood flow is redistributed so that the brain and heart muscle are primarily supplied with oxygen. During prolonged immersion, these organs also receive oxygen from arterial blood from the reserves of the “wonderful network” - the finest branching of blood vessels.

Less sensitive to oxygen starvation tissues (especially the muscles of the body) are transferred to starvation rations. Muscle hemoglobin, which gives muscle dark color, supplies the muscles with oxygen during the respiratory pause.

Air enters the open tracheal system through spiracles, the number of which varies from one to two pairs to eight to ten pairs. The number and location of spiracles reflect the adaptation of insects to habitat conditions. Each spiracle leads into the atrial cavity, the walls of which form the closing apparatus and an air filtration system. Tracheas branch and entangle everything internal organs. The terminal branches of the trachea end in a stellate tracheal cell, from which the smallest branches extend, having a diameter of 1-2 microns (tracheoles). Their tips lie on the cell membranes or penetrate into the cells. Many well-flying insects have air sacs, which are extensions of the longitudinal tracheal trunks. Their cavity is not permanent and can collapse when air escapes. Air sacs take part in the ventilation of the wing muscles and perform an aerostatic function, reducing the specific gravity of flying insects.

27. Assignment. Label the name of the organs of the circulatory system in the picture. Using a drawing, describe how blood moves through the body. Explain why the heart is compared to a pump?

1. Arteries

Blood moves through the body within the circulatory system. The human circulatory system is closed. It consists of the heart and blood vessels. Blood vessels are divided into arteries, veins and capillaries. Arteries move blood away from the heart. Blood moves through the veins to the heart. Inside organs, muscles, and skin, blood moves through capillaries. There are two circles of blood circulation - small and large.

The heart is compared to a pump, because the speed at which blood moves through the body and pressure depend on its operation. The heart has muscular walls and when it contracts, blood is released into the blood vessels. The heart beats about 100,000 times per day. Throughout life, the heart works and pumps tons of blood. That's why it's called a "pump".

28. Assignment. Finish the sentence.

Answer. The circulatory system consists of the heart and blood vessels - arteries, veins, capillaries.

Practical work

29. Assignment. Underline the names of the organs of the circulatory system.

Answer: heart, blood vessels.

30. Assignment. In 1908, Russian scientist I.I. Mechnikov decided that white blood cells protect the human body from pathogenic microbes. What century was this?

Answer. This was in the 20th (20th) century.

31. Assignment. Connect the name of the organ and its function with a line.

32. Assignment. Mark the correct statements.

What is main functions circulatory system?

Transfer of substances and gases. (+)

What should you do to stop bleeding from a cut?

Press a bandage or a clean handkerchief onto the wound. (+)

33. Assignment. Write down what function these organs perform.

The heart performs the work of a “pump” of the circulatory system, pumping blood throughout the body.

Stomach - produces gastric juice, digests food.

The brain processes information coming from the senses and “manages” the work of internal organs.

34. Assignment. Make a plan for a story on the topic “The human circulatory system.”

Answer. Plan:

1. What is the importance of the circulatory system?

2. What organs make up circulatory system person?

3. In what direction does blood flow through the blood vessels?

4. How does blood differ in composition?

5. What circulation circles are there in the circulatory system?

6. How blood moves through the circulation.

7. What is the role of the heart in blood circulation?

8. What are the rules of circulatory hygiene?

35. Assignment. Cross out the name of the excretory organs.

Answer: kidneys, ureters, bladder.

36. Assignment. Mark the correct statements.

What is the role of the kidneys in the body?

Removed from the body liquid products life activity. (+)

Which organ produces urine?

In the kidneys. (+)

37. Assignment.

1). The microscope was invented in Holland in 1590. Do you think Peter I could work with a microscope?

2) Famous Russian surgeon N.I. Pirogov was the first to use a plaster cast for fractures, as well as iodine and alcohol to treat wounds. This was in 1855. In what century did N.I. live? Pirogov?

Answer. N.I. Pirogov lived in a century.

38. Assignment. Mark the correct statement.

The skin does not allow pathogenic bacteria into the body. (+)

39. Assignment. Write down in the tables the methods of hardening the body and the functions of the skin that you know.

Exercise. Make a drawing “Skin structure”. Look at the diagram on p. 31 textbooks.

Creation of tables and all calculations test work should be performed on a computer using MS Excel. The report on the completion of the test work must be prepared in the word processor MS Word in accordance with the existing documentation standard. A printed report and a file with calculations in Excel (on a floppy disk or other medium) must be submitted to the teacher in accordance with the laboratory work schedule.

The option number corresponds to the number formed by the last two digits of the grade book. If this number is greater than 30, then 30 should be subtracted from it, perhaps more than once, in order to obtain a remainder in the range from 1 to 30 inclusive. For example, the number 76 corresponds to option No. 16 (76-30-30=16). If the last two digits are zeros, then for the calculation you need to take three digits. For example, 100 corresponds to the 10th option (100-30-30-30=10).

The test report includes:

1) title page;

2) task;

3) brief explanations of the solution to the problem;

4) a table of calculations in formula form with a border (row and column headings);

5) counting results in numerical form without borders;

6) graphics;

7) list of sources used.

Test work must be protected. During the defense, the student must open the file in Excel and explain the calculations.

2.2 Instructions for completing the test

The first two points of the task from each option should be completed using arithmetic expressions or the logical functions IF, AND and OR (unless other functions are specified).

When calculating 3-5 points in the tasks of each option, the following functions can be used: SUMIF, COUNTIF, VLOOKUP, and also tabular view formulas with the functions SUM, COUNT, MAX, MIN and IF, or using Database functions.

To perform intermediate calculations, you should, if necessary, enter auxiliary columns.

In paragraph 6 of some options, table sorting and automatic output of subtotals are required. In this case, you need to copy the table to new leaf via the clipboard and run the command first Data-Sorting, and then the command Data-Results. In the dialog box, you must specify the name of the field, when changing which you want to display totals, select the resulting function (sum, maximum, average, etc.) and indicate the field to which the selected function will be applied. The calculation option with sorting and totals is considered as an example in Section 3.

Each option requires constructing two diagrams. To obtain charts, you must first select the desired data ranges, capturing the headers. Then call the Chart Wizard with the button on the toolbar or with the command Insert-Diagram.

2.3 Test tasks

Option 1

The three lower warehouses of the timber processing enterprise receive logs, each of which is characterized by the data presented in Table 2. The logs are cut into logs of the same length (4 meters) starting from the butt. Residues from the logs, having a length less than the length of the log, go to waste.

Filling out the table

Fill in columns A and B with arbitrary repeating ones various combinations values ​​(at least 10 records).

Fill in the “Whip length, m” column with numbers in the range from 4 to 20.

Fill in the column “Butt diameter, m” with numbers from 0.4 to 1.2.

The column “Top diameter, m” ranges from 10% to 20% of the butt diameter.

Table 2 – Stick data

Exercise

Copy table 2 to a new sheet and complete the tasks.

Considering the whip to be a truncated cone, add columns:

“Whip volume, m3” and calculate the volume for each whip;

“Number of logs, pcs” and calculate the number of logs obtained from each log;

Enter the column “Indicator 1” and mark with units all the lengths of waste from which exceed 3 meters. Mark the rest with zeros.

Note. The following tasks must be completed under the edited table 2, indenting one line.

Calculate the total volume of logs for the Pine species.

How many logs produced more than three logs?

Find the maximum length of the whip for the Larch breed.

Copy the table to Sheet 3 and complete the following tasks:

sort the table by warehouse number;

obtain summary data for each warehouse on the number of logs received;

Option 2

There are logs in the timber yard. For each log, the data given in Table 3 is known.

Filling out the table

Fill in column A with arbitrary repeating values ​​(at least 10 records).

Fill in the “Diameter, cm” column with numbers in the range from 16 to 60.

Fill in the “Length, m” column with numbers from 3 to 6.

Table 3 – Timber warehouse

Exercise

Copy table 3 to a new sheet and complete the tasks.

(0,87* L+0,01* L 2 )*(D+0,001* L 2 ) 2 +0,0002* L 3 ,

Where D– log diameter, m;

L– log length, m.

Logs must be distributed by diameter into four size groups: from 16 to 24, from 24 to 36, from 36 to 52 and from 52 to 60 cm. Enter the “Size group” column and indicate in it the number of the size group for each log (1, 2, 3 or 4).

Note. The following tasks must be completed under the edited table 3, indenting one line.

Calculate the total volume of wood for the “Pine” and “Fir” species.

Find the average volume of a log for the Spruce species.

How many logs fall into size groups 2 and 3?

Copy table 3 to Sheet 3 and complete the tasks:

sort the table by breed;

obtain final data on the total volume of logs for each species;

hide the entries in the original list, leaving only the summary data;

build a histogram and a pie chart based on the final data.

Option 3

During the taxation of a forest area, a number of trees of three species were examined. For each tree, the data presented in Table 4 was obtained.

Filling out the table

Fill columns A with arbitrary integers (at least 15 entries)

Fill in column B with repeating values ​​(pine, fir, birch).

Fill in the “Trunk diameter” column with numbers in the range from 16 to 60.

Fill in the column “Trunk volume, m3” with numbers from 0.2 to 0.9.

Fill in the column “Age, years” with numbers from 1 to 200.

Fill in the “Condition” column with the following labels: 0 – healthy, 1 – damaged.

Table 4 – Taxation data

Exercise

Copy table 4 to a new sheet and complete the tasks.

Enter a new column “Age Group”. Fill it in with age group numbers from 1 to 4. Age groups are defined by the following ranges: from 1 to 40, from 40 to 90, from 90 to 150 and from 150 to 200 years.

Enter the column “Indicator 1” and mark “1” all healthy trees of the older age group. The rest should be marked “-”.

Note. The following tasks must be completed under the edited table, indenting one line.

What quantity healthy trees falls into the 3rd age group?

Determine the condition of the youngest tree.

What is the total volume of damaged trunks with a trunk diameter greater than 50 cm?

Copy table 4 to Sheet 3 and complete the tasks:

sort the table by age group;

calculate the number of trees for each age group;

hide the entries in the original list, leaving only the summary data;

build a histogram based on the final data and. pie chart.

Option 4

Several suppliers, having a known quantity of wood (m3) in stock, supply cargo to three consumers. The cost of transporting a unit of cargo from each supplier to each consumer is known. These data are presented in Table 5. The need of each consumer is known (Table 5). There is a plan for transporting goods from suppliers to consumers (Table 7).

Filling tables

Fill in Table 5 with arbitrary values ​​(at least 5 entries)

Table 6 should also be filled in with arbitrary values ​​(3 entries).

Table 7 must contain at least 15 records. The provider and consumer code is repeated.

Table 5 - Supplier inventories and transportation costs

Table 6 - Consumer needs

Table 7 – Supply plan

Exercise

Copy table 7 to a new sheet and complete the tasks.

Add a new column “Name of consumer” to the table and transfer data from table 6 to it using the VLOOKUP function.

Add the column “Transportation cost” to table 7 and calculate it using the formula “Transportation cost per m3” * “Volume of delivery”. Use the VLOOKUP and MATCH functions.

Note. The following tasks must be completed under the edited table 7, indenting one line.

Calculate the total amount of cargo to be transported from the supplier “Boguchanles”. Is there enough log stock at the enterprise?

Calculate the total amount of cargo received by the Krasnoyarsk Dock consumer. Will the needs of this consumer be satisfied?

Find the average delivery volume from the Boguchanles supplier.

Construct a histogram and a pie chart using columns A and B of the original table 5.

Option 5

Several suppliers, having a known amount of wood (m3) in stock, supply cargo to several consumers. The cost of one cubic meter of wood from each supplier is known. These data are presented in Table 8. There is a plan for the delivery of cargo from suppliers to consumers (Table 9).

Filling tables

Tables must be placed on different sheets.

Fill in Table 8 with arbitrary values ​​(at least 5 entries).

Table 9 must contain at least 15 records.

Table 8 - Supplier inventories and their prices

Exercise

Copy table 9 to a new sheet and complete the tasks.

Add a new column “Name of supplier” to the table and transfer data from table 8 to it using the VLOOKUP function.

Add a column “Delivery cost, rub.” and calculate it using the formula “Cost of 1 m3” * “Volume of supply”. Use the VLOOKUP function.

Note. The following tasks must be completed under the edited table 9, indenting one line.

How much wood should the Kezmales supplier send? Compare with the value of its inventory.

How many suppliers does Krasnoyarsky Dock have?

Determine the minimum supply volume from the supplier “Kezmales”.

Using columns B and C of the original table 8, construct a histogram and a pie chart.

Option 6

Table 10 contains information about flights operated from Krasnoyarsk airport. Table 11 provides information on passenger check-in for upcoming flights.

Filling tables

Place all tables on different sheets.

Table 10

Fill out the table randomly (at least 6 flights and 3 airlines, flight numbers are not repeated).

Table 11

The “Passenger's full name” column is filled in randomly.

The “Flight number” column is filled in with the flight numbers given in Table 9 (the numbers must be repeated).

The “Class” column is filled in randomly with letters E And B, denoting economy class and business class, respectively.

The column “Baggage weight, kg” is filled with numbers in the range from 0 to 100.

Column “Number of things, pcs.” filled with integers in the range from 1 to 5.

Table 10 – Flight information

Table 11 – Registration information

Exercise

Copy table 11 to a new sheet and complete the tasks.

Add new columns to the table:

- “Average weight of one thing”, calculated by the formula: “Weight of luggage” / “Number of things”);

- “Destination”, “Airline”, “Ticket cost” by transferring data from table 10 using the VLOOKUP function.

A business class passenger has the right to carry 30 kg of baggage free of charge, and an economy class passenger - 20 kg. Add a new column “Additional payment” and fill it in according next rule:

if the weight of the passenger's luggage does not exceed the norm, then issue 0, in otherwise You must pay for excess baggage weight in the amount of 40 rubles for each extra kilogram. To calculate, use the IF function and arithmetic operations.

Note. The following tasks must be completed under the edited table 11, indenting one line.

Define average cost economy class tickets to Moscow on all flights.

Calculate the total weight of baggage for flights 148 and 150.

What is the maximum baggage weight on flight 148?

Using columns A and E of the original table 10, construct a histogram and a pie chart.

Option 7

The results of testing in computer science in two groups, divided into subgroups, are shown in Table 12. Table 13 contains information about the teachers conducting laboratory classes in these subgroups.

Filling tables

Place all tables on different sheets.

Table 12

The column “Student's full name” is filled in randomly (at least 15 names).

The “Group (subgroup) number” column is filled with repeating values ​​of at least two groups for the first and second subgroups.

The “Type of training” column is filled in randomly with letters To And b, indicating that the student is studying on a commercial or budget basis, respectively.

The “Sum of Points” column is filled with real numbers in the range from 20 to 100 with two decimal places.

Table 13

Fill in the “Group (subgroup) number” column with the values ​​used in Table 11.

The columns “Name of teacher” and “Date of retaking the test” are filled in arbitrarily.

The “Work experience” column is filled with integers in the range from 1 to 35.

Table 12 – Test results

Table 13 - Teachers

Exercise

Copy table 12 to a new sheet and complete the tasks.

Add columns to the table:

- “Group (subgroup) number”, “Teacher’s full name”, “Work experience” and transfer the relevant data from table 12 using the VLOOKUP function;

Fill out the new column “Academic grade” according to the following rule:

If the student scored less than 55 points, then display “retake”;

If the score is from 55 to 70, then print the number 3;

If the score is from 70 to 85, then print the number 4;

If the number of points is at least 85, then - 5. To calculate, use the IF function.

Add a column “Test retake date” and fill it out like this:

If the student did not pass the test, then display the retake date for his group, which is given in Table 13;

Otherwise, display a dash (“-”). To calculate, use the IF and VLOOKUP functions.

Note. The following tasks must be completed under the edited table 12, indenting one line.

Find GPA on testing of public sector students in group 41-1.

What is the length of service of the teacher of the group in which the student who showed the best result is studying?

Using columns B and C of the original table 13, construct a histogram and a pie chart.

Option 8

Construction sand is mined in three quarries and delivered to construction sites in the city. Data are available on the productivity and costs of sand extraction for each quarry (Table 14). Information on the daily demand of construction sites for sand is contained in Table 15. Table 16 provides data on the actual supply of construction sand to construction sites from different quarries during one working day.

Filling tables

Place all tables on different sheets.

Table 14

The table must contain 3 rows.

The column “Name of sand quarry” is filled in randomly with the names of the quarries.

The “Daily Productivity” column is filled with integers ranging from 30 to 120.

The column “Sand mining costs” is filled with real numbers in the range from 500 to 800 with two decimal places.

Table 15

The table must contain at least 4 rows.

Fill in the “Construction Site” column randomly with the addresses of construction projects.

The “Daily Sand Requirement” column is filled with integers ranging from 5 to 35.

Table 16

The table must contain at least 12 rows.

The columns “Construction site” and “Name of sand quarry” are filled in with values ​​from tables 14 and 15, respectively, in various combinations.

The “Received per day” column is filled with integers in the range from 3 to 12.

The “Transportation costs” column is filled with real numbers in the range from 300 to 600 with two decimal places.

Table 14 - Quarries

Table 15 - Construction sites

Table 16 – Sand supply

Exercise

Copy table 16 to a new sheet and complete the tasks.

Add columns:

- “Costs for sand extraction, rub./t”, transfer the data from table 14 using the VLOOKUP function;

- “Cost of received sand, rub.” calculate using the formula “Sand mining costs, rub./t” * “Received per day, t”;

- “Total costs, rub.” (“Cost of incoming sand” + “Transportation costs”).

Add the column “Indicator 1” and fill it in according to the following rule:

If less than 5 tons of sand were received per day, then print “*”;

If there was a receipt from 5 to 9 tons, then display “**”;

If the receipt was more than 9 tons, then display “***”. For calculations, use the IF and AND functions.

Note. The following tasks must be completed under the edited table 16, indenting one line.

How much unclaimed construction sand is left in the Berezovsky quarry? Instructions: Calculate the amount of sand coming from this quarry, and compare with performance.

How many times have the transportation costs of delivering sand from the Peschanka quarry exceeded 450 rubles?

Calculate the amount of sand received at the construction site “Vzletnaya, 12” from all quarries. Does the received amount of sand meet the daily needs of the construction site “Vzlyotnaya, 12”?

Using columns A and B of the original table 15, construct a histogram and a pie chart.

Option 9

There are 4 enterprises that produce 3 types of products and use some raw materials in their production. The names of enterprises are presented in Table 17, the types of products and the cost of raw materials for the production of a unit of product are given in Table 18. Data on the daily productivity of enterprises for each product (number of products per day) are given in Table 19. The cost of 1 kg of raw materials is 200 rubles.

Filling tables

Place all tables on different sheets.

Table 17.

The table contains 4 rows and is filled in randomly.

Table 18.

The table contains 3 rows and is filled in randomly.

Table 19.

The table contains 12 rows. Each enterprise produces all types of products.

Column “Productivity, pcs.” fill in with integers in the range from 5 to 30.

Table 17 – Enterprises

Table 19 - Data on the work of enterprises

Exercise

Copy table 19 to a new sheet and complete the tasks.

Add columns:

- “Name of the enterprise” and “Name of the product”, transfer the data using the VLOOKUP function from tables 17 and 18;

- “Quantity of raw materials, kg” is calculated using the formula “Raw materials costs, kg/piece.” * “Productivity, pcs.”;

- “Cost of raw materials, rubles” is calculated using the formula “Quantity of raw materials, kg” * 200 rubles.

Add the column “Indicator1” and fill it in according to the following rule: if the company produces less than 10 doors per day, then output 1, otherwise - 0.

Note. The following tasks must be completed under the edited table 19, indenting one line.

How many cabinets do all factories produce per day?

Determine the size of the loan that must be provided to the Mekran enterprise for the purchase of raw materials for 30 days of work.

How much raw material is required for the production of doors at the Mekran and Nils enterprises?

Using columns B and C of the original table 18, construct a histogram and a pie chart.

Option 10

To produce beams of three sizes: 0.6 m, 1.5 m and 2.5 m, logs 3 m long are supplied for cutting. Table 20 shows various methods for cutting a three-meter log, indicating the number of beams of the corresponding sizes obtained. Table 21 shows the dates of receipt of the batch of logs and the methods for cutting them.

Filling tables

Place all tables on different sheets.

Table 20

Table 21

The table must contain at least 15 rows.

Table 20 - Methods for cutting logs

Table 21 – Log processing data

Exercise

Copy table 21 to a new sheet and complete the tasks.

Add columns to the table: “Number of beams of size 0.6”, “Number of beams of size 1.5”, “Number of beams of size 2.5”, “ Total bars" and fill them in according to the formulas using the VLOOKUP function and the data in Table 20;

Add a column “Indicator 1” and mark with units the cutting methods in which beams measuring 1.5 m cannot be obtained. The remaining methods are marked with zeros.

Note. The following tasks must be completed under the edited table 21, indenting one line.

How many times have you used the first method to cut logs?

What date did it arrive? maximum amount logs?

Find the total number of beams sawn using the 2nd and 3rd methods.

Copy the original table 20 to a new sheet. Add column E to it “Total beams, pcs.”, make calculations. Using columns A and E of the resulting table, construct a histogram and a pie chart.

Option 11

5-meter logs are cut into beams measuring 1.5 m, 2.4 m and 3.2 m. This produces a certain amount of waste. Table 22 shows all possible methods for cutting a five-meter log, indicating the number of resulting beams of the corresponding sizes. Table 23 shows the dates of receipt of the batch of logs and the methods for cutting them.

Filling tables

Place all tables on different sheets.

Table 22

There are 4 cutting methods for these sizes. Three methods are already indicated in the table, the fourth is to fill in yourself.

Table 23

The table must contain at least 15 rows.

Fill in the “Cutting method” column with repeating integers from 1 to 4.

Columns B and C should be filled in as desired. The date may be repeated.

Table 22 - Methods for cutting logs

Table 23 – Data on cutting logs

Exercise

Copy both tables to new sheets and complete the tasks.

Add column E “Waste” to Table 22 and calculate it.

Add columns to table 23: “Number of beams of size 1.5”, “Number of beams of size 2.4”, “Number of beams of size 3.2”, “Quantity of waste” and fill them in according to the formulas using the VLOOKUP function and the data in table 22 .

Note. The following tasks must be completed under the edited table 23, indenting one line.

Find the total amount of waste for the 4th cutting method.

Find the maximum amount of waste when processing logs received in quantities of no more than 6.

How many times have you received less than 5 beams of size 1.5?

Using columns A and E of the original table 22, construct a histogram and a pie chart.

Option 12

The company receives semi-finished products in the form of plywood sheets. Incoming plywood sheets are cut in two ways into three types of parts. Table 24 shows the number of parts of each type that is obtained when cutting one sheet of plywood using one or another cutting method. Table 25 contains data on the daily cutting of incoming semi-finished products.

Filling tables

Place all tables on different sheets.

Table 24

The table should have three rows.

Table 25

The table must contain at least 15 rows.

Fill in the “Cutting method” column with repeating integers from 1 to 2.

Columns B and C should be filled in as desired. The cutting date must be repeated.

Table 24 - Methods for cutting plywood sheets

Table 25 – Data on cutting several batches of plywood

Exercise

Copy table 25 to a new sheet and complete the tasks.

Add new columns to the table: “Number of parts No. 1”, “Number of parts No. 2”, “Number of parts No. 3” and “Total number of parts”” and fill them in according to the formulas using the VLOOKUP function and the data in Table 24.

Add a column “Indicator 1” and place 1 in it if the number of received plywood sheets is more than 20, and 0 in other cases.

Note. The following tasks must be completed under the edited table 25, indenting one line.

From the received plywood sheets it is necessary to make sets, including 4 parts No. 1, 3 parts No. 2 and 2 parts No. 3. How many sets can be made from all the received plywood? (Instructions: count how many sets of each type of parts are enough and select the minimum number of sets).

How many parts No. 1 were obtained using the first cutting method?

How many batches of plywood of more than 30 sheets were received for processing?

Using columns A, B and C of the original table 24, construct a histogram, and using columns A and B, construct a pie chart.

Option 13

To manufacture several types of products, the company uses steel and non-ferrous metals, which are available in limited quantities, as raw materials. Lathes and milling machines are used to manufacture products. Table 26 shows the rates of resource consumption for one product, and Table 27 contains the current reserves of resources at the enterprise. Table 28 provides data on the production of products.

Filling tables

Place all tables on different sheets.

Table 26

The table must contain at least 5 types of products. The type of product is not repeated.

Table 27

The table contains four rows. Fill in column B with arbitrary numbers.

Table 28

The table must contain at least 15 rows.

Fill in the “Product Type” column with randomly repeated values ​​from Table 25.

Columns B and C should be filled in as desired. The date of manufacture must be repeated.

Table 26 – Resource consumption rates for one product

Table 27 – Resource reserve

Table 28 – Product release data

Exercise

Copy table 28 to a new sheet and complete the tasks.

Add new columns to the table: “Consumption of steel, kg”, “Consumption of non-ferrous metals, kg”, “Costs of lathes, machine-hours”, “Costs of milling machines, machine-hours” and “Amount of profit, thousand rubles.” . Fill in the formulas using the data from Table 26 and the VLOOKUP function.

Under the table, display a message indicating whether the supply of non-ferrous metals is sufficient to manufacture all products.

Note. The following tasks must be completed under the edited table 28, indenting one line.

Calculate the consumption of non-ferrous metals for the manufacture of products 1.

On what day was the largest batch of products made?

What profit did those batches of products produce that required more than 100 kg of steel and more than 400 milling machine hours to produce?

Based on the data in the original table 27, construct a histogram and a pie chart.

Option 14

The enterprise has the resources of raw materials, labor and equipment necessary to produce several types of goods. The resource costs for producing a unit of a given type of product, the profit received by the enterprise, resource reserves and data on the actual production of goods are given in tables 29-31.

Filling tables

Place all tables on different sheets.

Table 29

The table must contain at least 5 types of goods. The type of product is not repeated.

Table 30

The table contains three rows. Fill in column B with arbitrary numbers.

Table 31

The table must contain at least 15 rows.

Fill in the “Type of product” column with randomly repeated values ​​from table 29.

Columns B and C should be filled in as desired. The release date must be repeated.

Table 29 – Resource costs per unit of product

Table 30 – Volume of resources

Table 31 – Release of goods

Exercise

Copy table 31 to a new sheet and complete the tasks.

Add new columns to the table: “Raw materials consumption, kg”, “Labor costs, h”, “Equipment costs, machine-hour” and “Profit amount, rub.”. Fill in the formulas using the data from Table 29 and the VLOOKUP function.

Under the table, display a message about whether the given amount of equipment is enough to produce all types of goods.

Note. The following tasks must be completed under the edited table 31, indenting one line.

How many times does lot A occur in quantities of more than 20 pieces?

What is the greatest profit from producing product C?

What consumption of raw materials was required to produce goods A and B?

Using columns A and B of the original table 29, construct a histogram and a pie chart.

Option 15

The refinery has reserves of grade A and grade B oil shown in Table 32. Refining oil produces gasoline and fuel oil. In this case, three processing methods are known, listed in table 33. Table 34 contains data on the operation of the plant over several days.

Filling tables

Place all tables on different sheets.

Table 32

The table should have 2 records. Column B should be filled in with larger numbers.

Table 33

Table 34

Data in the “Date of Processing” column must repeat;

Column “Amount of grade A oil, units.” fill in with arbitrary numbers, and the column “Amount of grade B oil, units.” fill in in accordance with the processing method and data from table 32.

Table 32 – Oil reserves

Table 33 – Methods of oil refining

Table 34 – Data on plant operation

Exercise

Copy table 34 to a new sheet and complete the tasks.

Add new columns to the table: “Amount of fuel oil, units,” “Amount of gasoline, units.” and “Quantity of petroleum products, units.” Fill in the formulas using the data from Table 33 and the VLOOKUP function.

If the numbers in the “Amount of fuel oil” column are greater than 100, then use conditional formatting to display them in red, otherwise – in blue.

Note. The following tasks must be completed under the edited table 34, indenting one line.

How much total grade A and grade B oil was used for refining? Were the initial supplies sufficient?

How much gasoline was obtained using the 1st method?

How many times after 08/20/2006 was the 3rd processing method used?

Based on the data in the original table 32, construct a histogram and a pie chart.

Option 16

An oil refinery processes grade A and grade B crude oil. Refining crude oil produces gasoline and fuel oil. In this case, three processing methods are known, listed in Table 35. Table 36 shows prices for fuel oil and gasoline. Table 37 contains data on the operation of the plant for several days.

Filling tables

Place all tables on different sheets.

Table 35

The table contains three records. Fill in according to the sample.

Table 36

The table should have 2 records. Fill in column B with arbitrary numbers.

Table 37

The table must contain at least 15 records

The data in the Processing Date column may be repeated;

The “Method number” column is filled with numbers 1, 2, 3, in any order;

Column “Amount of grade A oil, units.” fill in with arbitrary numbers, and the column “Amount of grade B oil, units.” fill in in accordance with the processing method and data from table 34.

Table 35 – Methods of oil refining

Table 36 – Prices for petroleum products

Table 37 – Data on plant operation

Exercise

Copy table 37 to a new sheet and complete the tasks.

Add new columns to the table: “Amount of fuel oil, units,” “Amount of gasoline, units,” “Price of fuel oil, rub.”, “Price of gasoline, rub.” and “Price of petroleum products, rub.” Fill in the formulas using data from tables 35 and 36 and the VLOOKUP function.

By conditional formatting, the number indicating the processing method in the “Method Number” column is displayed in a different color.

Note. The following tasks must be completed under the edited table 37, indenting one line.

How many times was the 1st processing method used?

What is the price of petroleum products obtained by the 2nd and 3rd processing methods?

Find the maximum price of fuel oil obtained by the 1st processing method.

Based on the data in Table 36, construct a histogram and a pie chart.

Option 17

To service several types of machines, several brands of technical oil are required. The codes of the machines, their names and the quantity of each type are known (Table 38). The names of brands of technical oils, the cost of one liter and stocks in the fuel and lubricants warehouse for each brand are also known (Table 39). For one machine of each type, the planned consumption rate is known technical oil each brand and actual consumption in liters (Table 40).

Filling tables

Place all tables on different sheets.

Table 38

The table contains five records. Fill in freely. Codes and names of machines are not repeated.

Table 39

The table must have at least 4 records. Brands are not repeated. Fill in columns B and C with arbitrary numbers.

Table 40

Table 38 - Machines

Table 39 – Brands of technical oils

Table 40 – Technical oil consumption

Exercise

Copy table 40 to a new sheet and complete the tasks.

Add new columns to the table: “Name of machine”, “Number of machines, pcs.”, “Cost of 1 liter, rub.”, “Consumption for all machines (plan), l”, “Consumption for all machines (actual), l ", "Cost of expense (plan), rub.", "Cost of expense (actual), rub." Fill in the formulas using data from tables 38-39 and the VLOOKUP function.

Enter a new column in which to display 1 if “Consumption on all machines (actual)” exceeds “Consumption on all machines (plan)”. Otherwise, output 0.

Note. The following tasks must be completed under the edited table 40, indenting one line.

What is the remaining amount of oil of one of the brands in the warehouse? If the oil supply is not enough, then display a message and calculate the cost of purchasing it, otherwise calculate the cost of the remainder.

What is the actual consumption for lathes of those oils whose cost per liter does not exceed 14 rubles?

How many times in the table “Cost of expense (actual), rub.” exceeds “Cost of expenditure (plan), rub.”?

Using columns B and C of Table 38, construct a histogram and a pie chart.

Option 18

To service several types of machines, several brands of technical oil are required. The codes of the machines, their names and the quantity of each type are known (Table 41). The names of brands of technical oils, the cost of one liter and stocks in the fuel and lubricants warehouse for each brand are also known (Table 42). For one machine of each type, the planned consumption rate of technical oil of each brand and the actual consumption in liters are known (Table 43).

Filling tables

Place all tables on different sheets.

Table 41

The table contains four records. Fill in freely. Codes and names of machines are not repeated.

Table 42

The table must have at least 5 records. Brands are not repeated. Fill in columns B and C with arbitrary numbers.

Table 43

The table must contain at least 15 records. List machine codes and oil brands in different combinations.

Table 41 - Machines

Table 42 – Brands of technical oils

Table 43 – Technical oil consumption

Exercise

Copy table 43 to a new sheet and complete the tasks.

Add new columns to the table: “Name of machine”, “Number of machines, pcs”, “Cost of 1 liter, rub.”, “Consumption for all machines (plan), l”, “Consumption for all machines (actual), l” , “Cost of expense (plan), rub.”, “Cost of expense (actual), rub.” Fill in the formulas using data from tables 41 and 42 and the VLOOKUP function.

In the “Oil Brand” column, use conditional formatting to highlight one of the brands in red, considering it to be in short supply.

Note. The following tasks must be completed under the edited table 43, indenting one line.

How many liters of oil are used on lathes and milling machines? Display a message indicating which machines spent more and by how much.

What is the average consumption of technical oil according to plan for one lathe?

Find the maximum cost of actual oil consumption of grade 12-234 per machine.

Use columns A and C of Table 42 to construct a histogram and a pie chart.

Option 19

The timber industry enterprise has three types of farms: coniferous, hard-leaved and soft-leaved. Coniferous farming includes the following species: pine, larch, spruce, fir, cedar. Hard-leaved ones include oak and beech, and soft-leaved ones include aspen and birch. For each rock, the reserve in m 3 is known (Table 44). There are regulatory data on the percentage yield of industrial wood by species. This information is contained in Table 45. The total yield percentage for each breed is 100%.

Filling tables

Place all tables on different sheets.

Table 44

The table should list all breeds without repetition. Column B can be filled in arbitrarily.

Table 45

The number of table entries is the same as table 43, but the breeds are listed in a different order. The remaining columns are filled with arbitrary numbers, the sum of which along the line is 100.

Table 44 – Stock of trunks in the timber industry enterprise

Table 45 – Commercial timber yield by species

Exercise

Copy table 45 to a new sheet and complete the tasks.

Add new columns to the table: “Total stock, m3”, “Coarse output, m3”, “Medium output, m3” and “Small output, m3”. Fill in the formulas using data from table 44 and the VLOOKUP function.

Enter a new column “Indicator 1” and fill it in as follows: if the percentage of output of large wood is greater than the percentage of output of small wood, then output 1, otherwise - 0.

Note. The following tasks must be completed under the edited table 45, indenting one line.

What is the yield of large timber for each farm? For which economy is it the largest?

Find the maximum supply of one species for coniferous farming.

How many species have a yield of small wood that is no greater than that of medium wood?

Using Table 44, construct a histogram and a pie chart.

Option 20

The timber industry enterprise has three types of farms: coniferous, hard-leaved and soft-leaved. Coniferous farming includes the following species: pine, larch, spruce, fir, cedar. Hard-leaved ones include oak and beech, and soft-leaved ones include aspen and birch. For each rock, the reserve in m3 is known (Table 46). There are regulatory data on the percentage yield of industrial wood by species. This information is contained in Table 47. The total yield percentage for each breed is 100%.

Filling tables

Place all tables on different sheets.

Table 46

The table should list all breeds without repetition. Column C can be filled in arbitrarily.

Table 47

The number of table entries is the same as table 46, but the breeds are listed in a different order. The remaining columns are filled with arbitrary numbers, the sum of which along the line is 100.

Table 46 – Stock of trunks in the timber industry enterprise

Table 47 – Commercial timber yield by species

Exercise

Copy table 47 to a new sheet and complete the tasks.

Add new columns to the table: “Farm type”, “Total stock, m3”, “Large output, m3”, “Medium output, m3” and “Small output, m3”. Fill in the formulas using data from table 45 and the VLOOKUP function.

In the column “Large yield, %”, highlight numbers exceeding 50% in red using conditional formatting, and the rest in blue.

Note. The following tasks must be completed under the edited table 47, indenting one line.

What is the total stock for the two types of farms (soft-leaved and coniferous)?

What is the yield of small wood for the species whose supply is the largest?

Calculate the average timber supply by species for one of the farms.

Use columns A and C of Table 46 to construct a histogram and a pie chart.

Option 21

Several consumers receive auto parts from one supplier. Information about sending goods is contained in table 48. The names of auto parts are given in table 49.

Filling tables

Place all tables on different sheets.

Table 48

Columns A, C, E, F are filled with arbitrary values ​​(at least 15 records), and the values ​​of the fields “Auto Part Code”, “Recipient” and “Destination” must be repeated.

Column B is filled with numbers ranging from 40 to 100.

Column D is filled with numbers ranging from 100 to 300.

Table 49

The table must contain at least 6 records.

Table 48 – Sending auto parts

Table 49 - Auto parts

Exercise

Copy table 48 to a new sheet and complete the tasks.

Add a column “Name of auto parts”, transfer names from table 49 using the VLOOKUP function.

At the end of the table, enter a new column “Indicator 1”. Fill it out as follows: if the number of auto parts is more than 150, but less than 200, then 1, otherwise - 0.

Enter another column “Indicator 2” and fill it in as follows: if the Recipient is “Your Choice” or “Bear” and the insured amount is more than 50, then 1, otherwise - 0.

Note. The following tasks must be completed under the edited table 48, indenting one line.

What is the number of auto parts with an insured amount from 50 to 80 traveling to the destination Novosibirsk?

What date is the minimum batch of auto parts sent?

Using columns D and F of table 48, construct a histogram and a pie chart.

Option 22

Several suppliers ship technical equipment to downstream warehouses. Information about the dispatch of goods is contained in table 50. Table 51 contains information about the available technical equipment.

Filling tables

Place all tables on different sheets.

Table 50

Columns A, B, D, F are filled with arbitrary values ​​(at least 15 records), and the values ​​of the fields “Product code”, “Supplier”, “Recipient” and “Destination” must be repeated.

Column C is filled with numbers ranging from 60 to 150.

Column E is filled with numbers ranging from 70 to 200.

Table 51

The table contains at least 6 records.

Table 50 – Shipping of goods

Table 51 – Product information

Exercise

Copy table 50 to a new sheet and complete the tasks.

Add new columns to the table: “Product name”, “Product unit cost, rub.” and “Cost of goods”. Fill in the formulas using data from table 51 and the VLOOKUP function.

Enter another column “Indicator 2” and fill it in as follows: if the destination is Boguchany and the goods are sent from Chelyabinsk, then 1, otherwise – 0.

Note. The following tasks must be completed under the edited table 50, indenting one line.

How many times does the product name “Wire” appear for the recipient “Angarles”?

Using columns B and C of Table 51, construct a histogram and a pie chart.

Option 23

Table 52 contains information on reprinting of classic literature. The list of writers is given in table 53.

Filling tables

Place all tables on different sheets.

Table 52

Columns E and F are filled with numbers ranging from 40 to 100.

Column C is filled with numbers in the range from 100 to 300.

Table 53

The table contains at least 5 records.

Table 52 – Information about the publication of classical literature

Exercise

Copy table 52 to a new sheet and complete the tasks.

Add new columns: “Author”, “Cost of the entire circulation”, “Royals”. The last column is calculated as 5% of the product of the number of books published and the selling price of one copy. To transfer data about the author from table 53, use the VLOOKUP function.

Enter another column “Indicator 2” and fill it in as follows: if the title of the book is “Sunday” or “War and Peace” and the number of copies is more than 200, then 1, otherwise – 0.

Note. The following tasks must be completed under the edited table 52, indenting one line.

How many times have Nekrasov’s books been published since 1980?

Copy table 53 to a new sheet. Add column C “Total number of copies, thousand copies.” Fill it using the function Summesli. Use columns B and C of Table 53 to construct a histogram and a pie chart.

Option 24

Table 54 contains information about the publication of some books on computer science. The list of authors is given in table 55.

Filling tables

Place all tables on different sheets.

Table 54

Columns A, B, D are filled with arbitrary values ​​(at least 15 records), and the values ​​of the fields “Author code”, “Book title”, “Publish date” must be repeated.

Columns E and F are filled with numbers ranging from 50 to 150.

Column C is filled with numbers in the range from 200 to 500.

Table 55

Table 54 – Information on the publication of books on computer science

Exercise

Copy table 54 to a new sheet and complete the tasks.

Add new columns: “Author”, “Cost of the entire circulation”, “Profit”. The last column is calculated as (“Sale price of one copy” - “Cost of one copy”) * “Number of copies”. To transfer data about the author from table 55, use the VLOOKUP function.

Enter another column “Indicator 2” and fill it in as follows: if the author is D. Lowe or P. Norton, and the cost of circulation is more than 120, then 1, otherwise - 0.

Note. The following tasks must be completed under the edited table 54, indenting one line.

What is the maximum number of copies of MS Word Secrets?

Copy table 55 to a new sheet. Add column C “Profit, total.” Fill it using the function Summesli. Using columns B and C of Table 55, construct a histogram and a pie chart.

Option 25

Table 56 contains information on wood species. Table 57 contains data on wood processing enterprises. Table 58 provides data on the performance of enterprises in different areas.

Filling tables

Place all tables on different sheets.

Table 56

Table 57

The table is filled with non-repeating codes and names of enterprises (at least 6 records).

Table 58

Columns A, B, C are filled with arbitrary values ​​(at least 15 records), and the values ​​of the fields “Plot number”, “Breed code” and “Enterprise code” must be repeated.

Columns D, E are filled with numbers in the range from 30 to 100.

Column F is filled with numbers in the range from 10 to 30.

Table 56 – List of breeds

Table 58 - Information on the work of enterprises at the sites

Exercise

Copy table 58 to a new sheet and complete the tasks.

Add new columns: “Species”, “Name of enterprise” and “Wood yield”. The last column is calculated as the sum of columns D, E and F. To transfer data from tables 56 and 57, use the VLOOKUP function.

Enter another column “Indicator 1” and fill it in as follows: if the species is pine or fir, and the name of the enterprise is Angarles, then 1, otherwise – 0.

Note. The following tasks must be completed under the edited table 58, indenting one line.

What is the total amount of waste for the spruce species at the Zaledeevskoye and Irkineevo enterprises?

Copy table 57 to a new sheet. Add Column C "Waste Amount". Fill it using the function Summesli. Using columns B and C of Table 57, construct a histogram and a pie chart.

Option 26

Table 59 contains information on wood species. Table 60 contains data on the heads of all sections. Table 61 provides data on the performance of enterprises in different areas.

Filling tables

Place all tables on different sheets.

Table 59

The table is filled with non-repeating values ​​(at least 7 records).

Table 60

The table is filled with non-repeating site numbers and the names of the chiefs (at least 4 entries).

Table 61

Columns A, B, C are filled with arbitrary values ​​(at least 15 records), and the values ​​of the fields “Plot number”, “Breed code” and “Name of enterprise” must be repeated.

Columns D, E are filled with numbers in the range from 50 to 120.

Column F is filled with numbers in the range from 25 to 40.

Table 59 – List of breeds

Table 61 – Information on the work of enterprises at the sites

Exercise

Copy table 61 to a new sheet and complete the tasks.

Add new columns: “Breed”, “Head of Section” and “Bonus”. To transfer data from tables 59 and 60, use the VLOOKUP function. A bonus to site managers is awarded in the amount of 1,000 rubles if the volume of waste is no more than 30% of the total volume for each type.

Enter another column “Indicator 2” and fill it in as follows: if the name of the enterprise is Gremuchiy or Krasnogoryevskoe, then display “ZAO”, otherwise – “LLC.

Note. The following tasks must be completed under the edited table 61, indenting one line.

What is the size of the bonus for the site manager A.S. Ivanov?

What is the maximum amount of waste for the pine species?

How many rocks have a yield of technical raw materials greater than 30 m3?

Copy table 59 to a new sheet. Add column C “Total, technical raw materials, m3”. Fill it using the function Summesli. Use columns B and C of Table 59 to construct a histogram and a pie chart.

Option 27

Table 62 lists all the reasons for marriage. Table 63 provides a list of products manufactured by the company. Information on defective products manufactured in different workshops is contained in Table 64.

Filling tables

Place all tables on different sheets.

Table 62

Table 63

Table 64

Columns A, B, C are filled with arbitrary values ​​(at least 15 records), and the values ​​of the fields “Workshop code”, “Defective reason code” and “Product code” must be repeated.

Column D is filled with numbers ranging from 10 to 30.

Table 62 – Directory of reasons for marriage

Table 63 - Product Directory

Table 64 – Release of defective products

Exercise

Copy table 64 to a new sheet and complete the tasks.

Add new columns: “Reason for defectiveness”, “Product name”, “Product unit price, rub.” and “Cost of Loss.” The last column is calculated as (“Unit price” * “Number of defective products”). To transfer data from tables 62 and 63, use the VLOOKUP function.

Enter another column “Indicator 1” and fill it in as follows: if for reasons of defects “Carelessness of workers” and “Technological reasons” the cost of losses is more than 5000, then 1, otherwise – 0.

Note. The following tasks must be completed under the edited table 64, indenting one line.

How many rows of the table contain information about workshop number 25?

Find the maximum cost of losses in the first workshop.

Use columns B and C of Table 63 to construct a histogram and a pie chart.

Option 28

Table 65 lists all the reasons for marriage. Table 66 provides a list of products manufactured by the company. Information on the number of products produced and defects allowed in different workshops is contained in Table 67.

Filling tables

Place all tables on different sheets.

Table 65

The table is filled with non-repeating values ​​(at least 4 records).

Table 66

The table is filled with various products (at least 6 entries).

Table 67

Columns A, B, C are filled in randomly (at least 15 entries), and the values ​​of the fields “Workshop code”, “Defective reason code” and “Product code” must be repeated.

Column D is filled with numbers ranging from 25 to 100.

Column E is filled with numbers ranging from 1 to 20.

Table 65 – Directory of reasons for marriage

Table 66 – Product Directory

Table 67 – Product release

Exercise

Copy table 67 to a new sheet and complete the tasks.

Add new columns: “Reason of defectiveness”, “Name of product”, and “Number of defective products, %”. To calculate and transfer data from tables 65 and 66, use arithmetic operations and the VLOOKUP function.

Enter another column “Indicator1” and fill it in as follows: if the number of defective products is more than 10, then display “fine”, otherwise – “-”.

Note. The following tasks must be completed under the edited table 67, indenting one line.

Calculate the number of defective products in workshop 01 with the cause of defect “Equipment malfunction”.

What is the maximum number of bolts in a batch of rejected products?

How many times are there workshops with a defect rate of 30 to 50?

Copy table 65 to a new sheet. Add column C “Number of defective products, pcs.” Fill it using the function Summesli. Using columns B and C of Table 65, construct a histogram and a pie chart.

Option 29

There is information on the movement of materials in three warehouses (Table 69). The names of materials in accordance with their codes are given in Table 68.

Filling tables

Place all tables on different sheets.

Table 68

Table 69

Column C is filled with numbers ranging from 300 to 400.

Columns D and E are filled in random numbers in the range from 10 to 150.

Table 68 – Materials Directory

Table 69 - Information on the movement of materials

Exercise

Copy table 69 to a new sheet and complete the tasks.

Enter new columns: “Name of material” and “Remainder”. Fill in the “Balance” column as follows: “Receipt” - (“Expense” + “Write off”).

Enter another column “Indicator 1” and fill it in as follows: if the material consumption in warehouses 2 and 3 is more than 70, then 1, otherwise – 0.

Note. The following tasks must be completed under the edited table 69, indenting one line.

Indicate the date of receipt of the largest amount of material at the warehouse.

Copy table 68 to a new sheet. Add column C "Total written off." Fill it using the function Summesli. Using columns B and C of Table 68, construct a histogram and a pie chart.

Option 30

There is information on the movement of materials in three warehouses (Table 72). The names of materials in accordance with their codes are given in Table 70. Information about warehouses is contained in Table 71.

Filling tables

Place all tables on different sheets.

Table 69

The table is filled randomly (at least 6 records).

Table 70

The table contains three records.

Table 71

Columns A, B, F are filled with arbitrary values ​​(at least 15 records), and the values ​​of the “Warehouse code” and “Material code” fields must be repeated.

Column C is filled with numbers ranging from 100 to 300.

Columns D and E are filled with numbers ranging from 10 to 50.

Table 70 – Materials Directory

Table 71 – Information about warehouses

Table 72 - Information on the movement of materials

Exercise

Copy table 72 to a new sheet and complete the tasks.

Enter new columns: “Name of material” and “Warehouse type”. Fill them with values ​​from tables 70 and 71 using the VLOOKUP function.

Enter another column “Indicator 1” and fill it in as follows: if in unheated warehouses of material with the name “Paint” less than 40 are written off, and more than 30 are consumed, then 1, otherwise – 0.

Note. The following tasks must be completed under the edited table 72, indenting one line.

What material is written off in the largest quantity?

How much “Lime” material arrived at warehouses 002 and 004 after 02/14/2006?

Copy table 71 to a new sheet. Add column C “Receipt, total”. Fill it using the function Summesli. Using columns B and C of Table 71, construct a histogram and a pie chart.

Fill out the table:

• Fill out the table:

Blood clot formation

• Blood clot formation

Fill out the table:

• Fill out the table:

• Pinocytosis is the absorption of liquid droplets by a cell.

• Phagocytosis is the absorption of solid particles by a cell (perhaps the role of particles is the performance of bacteria and viruses)

• Transport – transfer of substances (gases, nutrients).

• Protective – neutralization of foreign bodies.

• Connective – the connection of all parts of the body, organ systems into a single whole.

• Cells similar in structure and functions form:

• 1. Fabrics

• 2. Organs

• 3. Organ systems

• 4. Single organism

2. Platelets are involved in:

• 2. Platelets are involved in:

• Blood clotting

• Oxygen transfer

• Destruction of bacteria

• Nutrient transfer

• 3. If formed elements are removed from the blood, what remains is:

• Serum

• Lymph

• Plasma

• 4. Which of the following functions is not typical for blood?

• Support

• Protective

• Transport

• Connective

• IN 1. Establish a correspondence between the characteristics of human blood cells and their belonging to a certain group.

• Do not have constant A. Red blood cells

• forms

• 3. Biconcave shape

• 4. Capable of movement

• 6. Capable of phagocytosis

• AT 2. Match blood cells with their characteristics.

• 1.Responsible for immunity A.Platelets

• 2. Contain the protein hemoglobin B. Leukocytes

• 3. Capable of phagocytosis B. Erythrocytes

• 4. Responsible for clotting

• 5. Carries oxygen and carbon dioxide