Complete the task in excel. An example of completing a task using

Recently I faced the problem of choosing tasks for entry level studying spreadsheets. There are a lot of tasks, but somehow everything doesn’t touch the soul - price lists, salaries - at school it’s all this boring. Well, I googled a little and found myself. I am not publishing the full texts of the problems here - they can be varied depending on the age of the students and the desired complexity. But I publish the stories. I hope that my colleagues themselves will cope with adapting the puzzles for a specific audience. I've used them to make diagrams - it's simple, but it takes understanding where and what type of diagrams to use.

Lipstick flavor?

The statistical agency surveyed Russians with questionnaires and found out that girls under 17 years old paint their lips 16 times a day, from this age up to 21 years old - 12 times, up to 27 years old - 6 times, 35 years old - 2 times, up to 45 years old - 4 times, up to 55 years old - 3 times, up to 65 years old - 1 time. It would be appropriate to say that according to another questionnaire, men admitted how pleasant women’s lipstick tasted to them. It turned out that 93% of the men surveyed don’t care at all, 5% even like the lipstick, and only the remaining 2% are disgusted by it.

What is a first kiss?

The statistical agency surveyed Russians with questionnaires and found out that 98% of the city women surveyed like to kiss eyes closed. But 63% of men prefer to do this with with open eyes. In many questionnaires, readers, on their own initiative, tried to explain their preferences for a blind or sighted kiss. It turned out that most girls and women's eyes close by themselves. Men love to spy on their partner’s reaction to see if it’s time to move on to more serious things.

With the same survey, the agency found out how actively Russians kiss. It turned out that average kisses up to 14 years old last about 40 seconds, from this age up to 17 years old - 1 minute 25 seconds, up to 21 years old - 14 minutes, up to 23 years old - 19 minutes, up to 27 years old - 9 minutes, up to 32 years old - 8 minutes, up to 35 years old - 7 minutes, up to 40 years old - 1 minute, up to 45 years old - 20 seconds, up to 50 years old - 4 seconds, up to 60 years old - 2 seconds.

Time for first love?

The statistical agency surveyed Russians with questionnaires and found out that 30 percent of Russian women fell in love for the first time at the age of 5, 15% first became interested in boys in elementary school, 45% lost their heads at 12, 5% at 14, and the last 5 % - fell in love for the first time at the age of 15-17.

It's different for men. They fall in love for the first time at the age of 11 - 60% of Russians, the rest meet their first happiness - at 14-17 years old.

What unusual work do women do around the house?

It turned out that they hammer nails - 92 percent of the women surveyed do repairs household appliances- 4 percent, move furniture - 57, clean clogged water pipes - 17, take out trash - 64, beat out carpets - 9, seduce husband - 4, watch hockey, football and boxing on TV - 9, do renovations in the apartment - 44 percent of number of respondents.

What unusual work do men do around the house?

It turned out that they wipe dust from furniture - 8 percent of the men surveyed; wash floors - 6 percent, do laundry - 21, embroider - 1, sew - 3, cook food - 18, iron clothes - 4, wash windows - 0. 5 grow indoor flowers - 3, go shopping - 14, knit - 20, darn linen - 2, sew buttons - 8 percent of the respondents.

The result is something like this diagram:

Visual, fun and useful.

Well, and “to the heap” there is also this picture - also as a problem, but only for use graphic objects in Word:

You may also be interested in information on the following: keywords, which is usually searched for on my website
.

Excel users have long and successfully used the program to solve various types tasks in different areas.

Excel is the most popular program in every office around the world. Its capabilities allow you to quickly find effective solutions in the most different areas activities. The program is capable of solving various types of problems: financial, economic, mathematical, logical, optimization and many others. For clarity, we provide each of the above-described solutions to problems in Excel and examples of its implementation.

Solving optimization problems in Excel

Optimization models are used in the economic and technical spheres. Their goal is to select a balanced solution that is optimal under specific conditions (number of sales to generate a certain revenue, the best menu, number of flights, etc.).

In Excel, the following commands are used to solve optimization problems:

To solve the simplest problems, use the “Parameter Selection” command. The most difficult ones are “Scenario Manager”. Let's look at an example solution optimization problem using the “Solution Search” add-on.

Condition. The company produces several types of yogurt. Conventionally – “1”, “2” and “3”. Having sold 100 jars of yogurt “1”, the company receives 200 rubles. “2” - 250 rubles. “3” - 300 rubles. Sales have been established, but the amount of available raw materials is limited. You need to find what kind of yogurt and in what volume you need to make in order to get maximum income from sales.

We enter the known data (including raw material consumption rates) into the table:

Based on this data, we will create a worksheet:

1. We do not yet know the number of products. These are the variables.
2. The following formulas are entered in the “Profit” column: =200*B11, =250*B12, =300*B13.
3. The consumption of raw materials is limited (these are limitations). The following formulas are entered into the cells: =16*B11+13*B12+10*B13 (“milk”); =3*B11+3*B12+3*B13 (“leaven”); =0*B11+5*B12+3*B13 (“shock absorber”) and =0*B11+8*B12+6*B13 (“sugar”). That is, we multiplied the consumption rate by the quantity.
4. The goal is to find the highest possible profit. This is cell C14.

Activate the “Search for a solution” command and enter the parameters.

After clicking the “Run” button, the program displays its solution.

The best option is to concentrate on producing yogurt “3” and “1”. Yogurt “2” should not be produced.

Solving financial problems in Excel

Most often, financial functions are used for this purpose. Let's look at an example.

Let's format the initial data in the form of a table:

Because interest rate does not change throughout the entire period, we use the PS function (RATE, NPER, PMT, BS, TYPE).

Filling the arguments:

1. The rate is 20%/4, because interest is calculated quarterly.
2. Nper – 4*4 (total deposit term * number of accrual periods per year).
3. PMT – 0. We don’t write anything, because... the deposit will not be replenished.
4. Type – 0.
5. BS is the amount we want to receive at the end of the deposit period.

The investor needs to invest this money, so the result is negative.

To check the correctness of the solution, we use the formula: PS = BS / (1 + bet) nper. Let's substitute the values: PS = 400,000 / (1 + 0.05) 16 = 183245.

Econometrics solution in Excel

To establish quantitative and qualitative relationships, mathematical and statistical methods and models.

There are 2 ranges of values:

X values ​​will play the role of a factor characteristic, Y – an effective one. The task is to find the correlation coefficient.

To solve this problem, the CORREL function (array 1; array 2) is provided.

Solving logical problems in Excel

IN table processor there are built-in logical functions. Any of them must contain at least one comparison operator that will determine the relationship between the elements (=, >, =,

The students took the test. Each of them received a mark. If more than 4 points, the test is passed. Less – not passed.

1. Place the cursor in cell C1. Click the functions icon. Select "IF".
2. Fill in the arguments. Logical expression – B1>=4. This is the condition under which boolean value– TRUE.
3. If TRUE – “Passed the test.” FALSE – “I didn’t pass the test.”

Solving math problems in Excel

Using the program's tools, you can solve both the simplest mathematical problems and more complex ones (operations with functions, matrices, linear equations etc.).

Conditions of the learning task. Find inverse matrix B for matrix A.

1. We make a table with the values ​​of matrix A.
2. Select an area on the same sheet for the inverse matrix.
3. Click the “Insert Function” button. Category – “Mathematical”. Type – “MOBR”.
4. In the “Array” argument field we enter the range of matrix A.
5. Press Shift+Ctrl+Enter at the same time - this prerequisite for entering arrays.

Excel's possibilities are not limitless. But the program can handle many tasks. Moreover, the features that can be expanded using macros and user settings are not described here.

Create a sheet Sorting
We want to sort puppies by cost to find out which breed of puppies are the most expensive and which are the cheapest.
To do this, you need to select all the data (WITHOUT AFFECTING the column headers!) and in the menu Data select item Sorting.
In the dialog box that appears, you specify which column to sort the values ​​by. You can also sort by multiple values, for example, first by breed, and then (within each breed) by date of birth.

Task 1: Sort puppies by cost.

Filter

Create a sheet Autofilter
More convenient tool AutoFilter is used for selecting and sorting data. With it, you can not only sort data, but also make selections.
To do this, you need to select all the data TOGETHER with the column headers and in the menu Data select item Filter, and in it there is a subparagraph Autofilter.
The cells in your header bar will no longer be normal, but will have drop-down list buttons. From these drop-down lists you can select different conditions sorting or selection.

Task 2a: Select all Dalmatians.

To remove the AutoFilter, you need to uncheck the AutoFilter menu bar.

You can set more complex selection conditions, for example, select all setters. English and Irish setters are participating in the exhibition. This means that we need to select all dogs whose breed name CONTAINS the word “setter”.

Task 2b: Select all dogs that belong to the Setter group.

Results

Create a sheet Results
Now we are interested to know how many representatives of different breeds came to the exhibition, and what is the average cost of a puppy of each breed.
For all these actions, in which we first combine puppies into groups (by breed), and then in EACH of them we find either the quantity, or the average value, or another parameter, we will need such Excel operation How summing up.

Summarizing is carried out in three steps.
1. It is MANDATORY to sort the puppies ACCORDING to the SIGN by which we want to combine them into groups (using Sorting). IN in this case they need to be sorted by breed.
2. Select all data TOGETHER with column headers and in the menu Data select an item Results, a dialog box opens Subtotals.

3. In the dialog box you specify:
a) on what basis to group records (in the field With every change in...)
b) and what parameter is in each group (field Add results for...) …
c) we want to calculate: find the sum, average, maximum, etc. (field Operation)…
In this case, we want to count how many puppies of each breed there are.
Then
a) With every change in... Breed
b) Add totals for... Nickname (i.e. how many different nicknames are in each group)
c) Operation: Quantity.

Task 3: Count with Results number of puppies of each breed.

Diagram

It is convenient to present numerical data visually using charts.
Let us display on the diagram the composition (by breed) of exhibitors. To do this, we use the data obtained in the previous task on the number of representatives of different breeds.

Step 1. Data preparation
Let's collapse the table, leaving only the rows with the totals. On the left in the margin opposite the table with the results you can see frames with “pluses”. These boxes mark the boundaries of groups. If you click on the plus sign, the group will collapse and only the line with the total will remain. Like this:
was:

So we've collapsed the entire table. Now let's move on to the next step.

Step 2. Inserting a diagram.
Just like in Word, inserting a chart in Excel is done through the menu Insert(paragraph Diagram). In the dialog box that opens, you will be prompted to select a chart type. For different tasks different diagrams are used. In our case, a circular one is best suited: it displays the share different meanings in total.

After you select the chart type and click OK, it will launch Chart Wizard, which will help you enter data and configure chart parameters.

The data entry stage is the most important! A small inaccuracy may cause the chart to not display correctly.
1.In the field Range on the tab Data range you specify cells with data (numbers + signatures). You can simply select them with the mouse on the work field, and they will automatically be entered into the cell.

2. Now be careful! On the next tab Rows you need to fill in three fields: 1) in the field Name you say what the diagram will be called; 2) in the field Values you insert cells (selecting them with the mouse on the worksheet) with NUMERIC VALUES, according to which the chart is drawn; 3) finally, in the field Category labels you specify the cells with labels that will go into the chart legend.

Finish inserting the diagram. Place it on the same sheet Results.

Step 3. Chart setup.
Now you need to configure appearance diagrams. If you click on different elements diagrams (title, legend, sectors, plotting area, etc.), then a selection rectangle will appear around them.

Just like in Word, the item will appear in the context menu Format…(Legend Format, Title Format, Plot Area Format, Data Signature Format, etc.). IN dialog boxes format, you can customize the color, fill and line type, font format, signatures. In other words, polish the appearance of your diagram.
For example, like this:

Addition: you can add percentages next to sectors in the window Data Series Format(when all colored sectors are selected).

Task 4a: Draw pie chart, which shows how many representatives of different breeds came to the exhibition.
If you are confident in your abilities, and this task seems too simple to you, then you can do task 4b instead.

Task 4b*: Draw a bar graph that shows the AVERAGE cost of each breed of puppy. To do this you will need to first use Results count average cost by breed, and then insert a histogram. In the histogram, add data labels (average cost for each column).

Pivot tables

Sheet Pivot table
At the show, judges give puppies scores for conformation (appearance) and training.
Each judge evaluates each dog. All assessments are entered in order into one table. But when looking at this table it is difficult to assess who won!

Repetition. IF function

Determine the champions and super champion of the exhibition. If the dog’s total score is greater than or equal to 20, then the dog is a champion, and if it is the maximum of all participants in the exhibition, then it is a super champion.

Task 1.

In Excel calculate the total cost of equipment.

1. Data entry.

1.1. Rename Sheet 1. To do this, click on the sheet label right click, select the command from the context menuRenameand enter a new name"Task 1".

1.2. Starting in cell A1, enter sequentially in spreadsheet data, indicated in Fig. 10.

Rice. 1.10. Initial view of the table

1.3. Adjust the width of the columns.

This can be done automatically by doing double click mouse on the border of the columns (the cursor will turn into a double-sided arrow) or manually, placing the cursor on the border between the columns and dragging the column to the desired width. Word wrapping can be done using the panel Cell Format by selecting it from the context menu and bookmarking it Alignment check the box Wrap according to words.

Rice. 1.11. Cell Format Window

1.4. In cell A2, enter the first type of equipment - Plow.

In other cells, starting from A3 to A11, enter other types of agricultural machinery:

Cultivator;

Harrow;

Peeler;

Seeder;

Sprayer;

Ice rink;

Milling cutter;

Mineral fertilizer spreader;

Mower.

Rice. 1.12. Intermediate table view

2. Creating formulas.

2.1. Enter the number of agricultural machinery and prices in dollars ($) into the table in accordance with the figure, and also add additional lines as indicated in the figure below.

2.2. Calculate the total purchase cost (in $) using the manual formula entry method:

– place the cursor in cell D2;

– Enter an equal sign (=) and then manually type the formula:

B2*C2, please note that all actions are repeated above in the formula bar.

or a button on the formula bar. Make sure the number 6500 appears in cell D2.

2.3. Let's consider a more rational way of entering formulas, which we recommend using in the future - the method of entering formulas by specifying cells.

Calculate the total purchase cost (in $). To do this:

– place the cursor in cell D3;

– Click in the formula bar and enter an equal sign (=);

– click on cell B3. Make sure that an active frame appears around cell B3, and the address of cell B3 is displayed in the formula bar

Rice. 1.13. Entering a formula by specifying cells

– continue entering the formula by typing the multiplication sign (*) from the keyboard;

– click on cell C3, make sure that its address is also reflected in the formula bar.

– to complete entering the formula, press the key or a button on the formula bar. Make sure the number 8000 appears in cell D3.

3. Addressing cells.

To automate similar calculations in spreadsheets, a mechanism for copying and moving formulas is used, in which automatic setting references to cells with source data.

3.1. Calculate the total purchase cost (in $) for the remaining types of agricultural machinery using the autofill marker. To do this:

– click on cell D3;

– place the cursor on the autofill marker;

– click left button mouse and, without pressing, drag the formula down to the end of the list and release the left button;

– make sure that in each line the program has changed the cell references in accordance with the new position of the formula (in the cell D11 selected in Fig. 8, the formula looks =B11*C11) and that all cells are filled with the corresponding numerical values.

Rice. 1.14. Intermediate table view

Calculate the price of agricultural machinery in rubles using the dollar to ruble exchange rate indicated in the table for what:

– place the cursor in cell E2;

– enter the formula =C2*B27;

– make sure that the resulting numerical value is 78260;

– try extending the formula down to the entire list using the autocomplete marker. Make sure you get it everywhere zeros! This happened because when copying the formula relative link to the dollar exchange rate in cell B27 automatically changed to B28, B29, etc. And since these cells are empty, when multiplied by them, the result is 0. Thus, the original formula for converting prices from dollars to rubles should be changed so that the reference to cell B27 does not change when copied.

For this there is absolute reference to a cell that does not change when copied and transferred.

Recalculate column E:

– delete all contents of the range of cells E2:E11, enter the formula = C2*$B$27 into cell E2;

– Using the autocomplete marker, extend the formula down to the entire list. Review the formulas and verify that the relative references have changed, but the absolute reference to cell B27 remains the same. Make sure the price is calculated correctly.

3.3. Knowing the price of a type of agricultural machinery in rubles and its quantity, independently calculate the last column: the total purchase amount in rubles.

4. Using functions.

Functions are entered using the usual typing from the keyboard or in a more preferable way - usingFunction Wizards. Let's look at both of these methods with examples.

4.1. Calculate the total for the “Quantity” column using the SUM function (function for finding the sum) using the method of manually entering functions.

The manual function entry method involves manually entering the function name and the list of its arguments using the keyboard. Sometimes this method turns out to be the most effective. When entering functions, please note that the functions are named in English.

To calculate the total for the “Quantity” column:

– place the cursor in cell B13;

– type the formula =SUM(B2:B11) from the keyboard;

– press the key and make sure that the number 75 appears in cell B13.

4.2. Calculate the total for the “Price, $” column using the toolFunction Wizard.

The tool is designed to enter a function and its arguments in semi-automatic modeFunction Wizard (fx ), which provides correct spelling functions, maintaining the required number of arguments and their correct sequence.

To open it use:

– Tab Formulas, where the library of functions is indicated;

– Function Wizard button on the formula bar (Fig. 1.15).

Rice. 1.15. Function Wizard button on the formula bar

To calculate the total for the “Price, $” column:

– place the cursor in cell C13;

– call the Function Wizard dialog box using one of the above methods;

– in the Function field, find SUM;

– in the Number 1 field you can enter the entire summation range C2:C11 at once (the range can be entered from the keyboard, or you can select it on the sheet with the left mouse button, and then it will be displayed in the formula automatically) (Fig. 1.16);

Rice. 1.16. Calculation of the amount through the Function Wizard

– pay attention to the button for collapsing the dialog box located on the right side of the Number 1 field. This will temporarily minimize the window, resulting in the entire worksheet being visible;

– click on the OK button, make sure that the numerical value 11185 appears in cell C13.

4.3. Similarly, calculate the total for the remaining columns.

4.4. Calculate additional options indicated in the table (average prices, minimum and maximum). These functions are in the category Statistical. To do this, use the appropriate functions in the specified cells.

Table 1.2

Cell addresses and corresponding calculation functions

5. Data formatting.

Numeric values ​​that are entered into cells are usually not formatted in any way. In other words, they consist of a sequence of numbers. It is best to format numbers so that they are easy to read and consistent in terms of number of decimal places.

If you move the cursor to a cell with a formatted numeric value, the formula bar displays the numeric value unformatted. When working with a cell, always pay attention to the formula bar! Some formatting operations Excel performs automatically.

For example, if you enter 10% in a cell, the program will know that you want to use the percentage format and will apply it automatically. Likewise, if you use a space to separate thousands from hundreds (for example, 123,456), Excel will apply formatting with that separator automatically. If you place a default currency sign after a numeric value, such as “RUB,” then the currency format will be applied to that cell.

To set cell formats, use the Format Cells dialog box.

There are several ways to open the Format Cells window. First of all, you need to select the cells that need to be formatted and then select the command Format / Cells or right-click on the selected cells and select Format Cells from the context menu.

Next, on the Number tab of the Format Cells dialog box, you can select from the presented categories required format. When selecting the appropriate category from the list right side panel changes to display the appropriate options.

In addition, the Format Cells dialog box contains several tabs that provide the user with various formatting options: Font, Font Effects, Alignment, Border, Background, Cell Protect.

5.1. Change the format of the cell range C2:C13 to Monetary:

– select the range of cells C2:C13;

– right-click inside the range;

– select the Format / Cells command;

– on the Number tab, select the Cash category;

– in the Format list, select USD $ English (USA);

– set the Fractional part parameter to 0;

– click OK (Fig. 1.17).

Rice. 1.17. Setting the “Cash” cell format

5.2. Similarly, change the format for the columns “Total purchase cost, $”, “Price, rub.”, “Total purchase cost, rub.”. Also change the format for the total cells with average, minimum and maximum prices. For ruble data, use the RUB format. Russian and fractional part specify equal to 1.

Please note that if, after changing the format, a row of symbols (hash ##########) is shown in a cell instead of a number, this means that the column is not wide enough to display the number in the selected format, which means you need to increase the column width .

6. Design of tables.

To elements worksheet You can also apply stylistic formatting methods, which are done using the Home tab. A full range of formatting options are available in the Format Cells dialog box. It's important to remember that formatting attributes only apply to the selected cells or group of cells. Therefore, before formatting, you need to select a cell or range of cells.

6.1. Add a title to the table:

– right-click on the number 1 next to the first line;

– select the Insert Rows command;

– select the range of cells A1:F1 and run the command

– enter the name “Report on the purchase of agricultural equipment” in the combined cells;

– by right-clicking, call context menu Cell format. In the window that opens, in the font tab, set following parameters: Font - Calibi , style – bold italic, size – 14; Font effects – color blue; Alignment – ​​center; Framing – position of lines on all sides, style – solid line 2.5 pt., color – green; Background – yellow 2;

– click OK.

6.2. Format the table contents:

– apply a bold style to the data in the cell ranges A2:F2, A3:A28;

– set the Background and Border for the cell ranges: A14:F14; A16:C16; A18:E18; A20:C20; A22:E22; A24:C24; A26:E26;

– highlight the dollar exchange rate in bold and red;

– decorate the range of cells A2:F12 with a Frame: an outer frame and lines inside.

6.3. Adjust the width of the columns if, during the formatting process, the data in the cells has increased and does not fit within the cell boundaries (Fig. 1.18).

Rice. 1.18. Final table view

6.4. Install horizontal orientation Sheet: Home / Print / Landscape orientation.

6.5. Save the spreadsheet in a personal folder named Job 1.

Task 1.

1. Enter the initial data, decorate the table using a border, add a title, placing it in the center of the table, and fill the table header. To format text, use Format Cell/Alignment.
2. Add additional cells to the table to enter formulas and get the result.
3. Functions used when performing work:

Mathematical:

• SUM - sum of arguments;
• PRODUCT - product of arguments;
• SUMPRODUCT - the sum of the products of the corresponding arrays.

Statistical:

• AVERAGE - arithmetic mean of the arguments;
• MAX - maximum value from the argument list;
• MIN - minimum value from the argument list;
• COUNTIF - Counts the number of non-blank cells in the range that satisfy a given condition.

1. Fill out the table (5-7 lines). Enter the data available in the table header (years, months, days of the week) using autofill.
2. Design the table using a border, add a title, placing it in the center of the table. The table header should be in color (font and background), in bold.
3. Rename a worksheet sheet based on the information entered.
4. Add a column “P\n No.” to the beginning of the table and fill it in automatically.
5. Perform the appropriate calculations.

1. Plan your gasoline costs for daily trips from Polovinka to Urai by car. If known:
- distance m/d settlements in km. (30 km one way)
- gasoline consumption (8 liters per 100 km.)
- the number of trips per month is different (because the number of working days is different.)
- price of 1 liter of gasoline (n rubles per liter)
- monthly projected increase in gasoline prices - k% per month
Calculate your monthly and annual gasoline costs. Construct a graph of changes in the price of gasoline and a graph of monthly expenses.

2. Imagine that you are the director of a restaurant. Total monthly fund wages- $10,000. At the shareholders' council it was established that:
- a waiter earns 1.5 times more than a dishwasher;
- cook - 3 times more than a dishwasher;
- chef - $30 more...

1. Calculate the zoo’s weekly revenue if you know:
- number of tickets sold every day
- adult ticket price - 15 rubles.
- the price for children is 30% cheaper than for adults. Construct a diagram (graph) of the zoo's daily revenue.

2. Prepare an order form for the store if you know:
- products (bread, flour, pasta, etc., at least 10 items)
- price of each product
- quantity of each product ordered
Calculate the amount of products ordered. Improve the order form by adding a discount (for example, 10%) if the cost of the purchased products is more than 5,000 rubles. Construct a chart (histogram) of the cost...

1. Find a solution to an equation of the form kx + b = 0, where k, b are arbitrary constants.

2. Sugarcane contains 9% sugar. How much sugar will be obtained from 20 tons of sugar cane?

3. The schoolchildren had to plant 200 trees. They exceeded the landing plan by 23%. How many trees did they plant?

4. From 50 kg. of the seeds collected by the students, 17% were maple seeds, 15% were linden seeds, 25% were acacia seeds, and steel were oak seeds. How many kilograms...