Complete practical tasks in Excel for economists. Excel assignments

Practical work inMS E xcel

The laboratory workshop is intended for practical study of the section, calculations in “Spreadsheets”MSExcel- 2007" within the discipline " Information Technology V professional activities» second-year students of various specialties of the State Budgetary Educational Institution of Secondary Professional Education Polytechnic College No. 42, Moscow.

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

Each practical work contains theoretical information on the topic of work, detailed analysis test example and 3 variants of tasks on the topic.

1. Practical work

Subject:"Organization of settlements inMSExcel»

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

Fig.1

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

Fig.2

Task optionson topic« Organization of settlements inMSExcel»

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

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

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

1. Practical work

Subject:"Statistical functions"

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

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

AVERAGE(x 1 ,…,x n)

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

MAX(x 1 ,…,x n )

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

MIN(x 1,…,x n)

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

COUNT(x 1 ,…,x n)

number of numbers in argument list

COUNTA(x 1 ,…,x n )

number of values ​​in the argument list and non-empty cells

An example of completing a task using

statistical functions

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

Fig.4

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

Guidelines for completing the task:

Calculate:

• revenue from sales of each product;

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

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

  how many types of goods were sold.

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

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

copy the formula to the entire column;

enter formulas:

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

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

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

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

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

(counting the number of non-empty values)

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

Task optionson topic"Statistical functions"

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

Fig.5

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

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

1. Practical work

Subject:"Logical functionIF…»

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

In practice, Boolean expressions are used to developbranching algorithm :

Algorithmic language

If condition (logical expression)

action 1

otherwise

action 2

all-if;

condition

action 1

action 2

Block diagram

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

IF value of logical expressionTRUE ,

THAT statement 1 is executed ,

OTHERWISE statement 2 is executed .

Rice. 5 .

Example of specifying arguments to the IF function

(finding the maximum value of two numbers)

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

Logical expression

Value_if_true

Value_if_false

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

Task optionson topic« Logical function IF...»

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

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

Fig 8_1.

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

Fig 8_2.

1. Practical work

Subject:"Histograms, graphs"

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

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

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

Task optionson topic« Histograms, graphs"

Task 1 . Organize pie chart, according to Fig. 9_1.

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

Fig 9_3

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

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 with fill. 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 list of arguments;
• MIN - minimum value from the list of arguments;
• 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. Decorate 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 between populated areas in km. (30 km one way)
- gasoline consumption (8 liters per 100 km.)
- the number of trips per month is different (since different quantities working days.)
- 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. The total monthly payroll is $10,000. At the shareholders' council it was established that:
- a waiter receives 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...

Preview:

1. Lesson 1. Purpose of the program. Screen view. Entering data into a table
2. Lesson 2. Formatting a table
3. Lesson 3. Calculation using formulas
4. Lesson 4. Presentation of data from a table in graphical form
5. Lesson 5. Working with built-in functions
6. Lesson 6. Working with templates
7. Lesson 7. Actions with the worksheet
8. Lesson 8. Creating databases, or working with lists
9. Lesson 9. Creating databases, or working with lists (continued)
10. Lesson 10. Macros
11. Practical tasks for self-control and test classes

Lesson 1. PURPOSE OF THE PROGRAM.
SCREEN VIEW. ENTERING DATA INTO A TABLE

Program Microsoft Excel belongs to a class of programs calledspreadsheets. Spreadsheets are focused primarily on solving economic and engineering problems; they allow you to systematize data from any field of activity. There are next versions of this program – Microsoft Excel 4.0, 5.0, 7.0, 97, 2000.

Microsoft Excel allows you to:

1. generate data in the form of tables;
2. calculate the contents of cells using formulas, while using more than 150 built-in functions is possible;
3. present data from tables in graphical form;
4. organize data into structures that are similar in capabilities to a database.

Practice 1

Task: create a table and enter the following data into it:

Operating procedure

1. First, let's determine the sizes of the columns; To do this, hover the mouse cursor over the boundaries of the columns on the coordinate line and move it to the right until the columns take the size you need.
2. Make a table title. To do this, click on cell A1 and type the text “The largest rivers in Africa” in it, then select the cell with the mouse and select the font size you need. The title is ready. Learn more about creating headings in Lesson 2.
3. Click on cell A2 and enter the word “Title” in it, then go to the adjacent cell or press the Enter key to exit input mode. Perform similar actions with other table cells.
4. Make sure that the name, length and basin of the river are located in separate cells.
5. Let's frame the table. Select all filled cells with the mouse, find the “borders” icon on the right side of the toolbar (a smaller image of the table with a dotted line) and click on the arrow button to the right of it. From the list provided, select the framing option you need. The table is ready. More details about selecting and formatting a table will be discussed below.
6. Save the table.

Lesson 2. FORMATTING A TABLE

Selecting table fragments

To perform any action on a group of cells, you must first select them. In this case, the background of all cells except the first one will be painted black. But the unshaded cell will also be highlighted.

1. To select one row, place the mouse pointer on the row number on the coordinate column. To select several lines, move along the coordinate column without releasing the left key.
2. To select one column, place the mouse pointer on a letter on the coordinate line. To select several columns, move along the coordinate line without releasing the left key.
3. To select several cells, move around the table while pressing the left key.
4. A selection can be removed by clicking anywhere on the screen.

Resizing Cells

In real tables, as a rule, all columns have different widths. There are several ways to change cell sizes.

If you need to resize several cells at once, you must first select them.

1. Place the mouse pointer on the coordinate line or column (they are highlighted gray and are located at the top and left); Without releasing the left mouse button, move the cell border in the desired direction. The mouse cursor will change its appearance.
2. Team Format – Line – Height and team Format – Column – Widthallow you to determine the cell dimensions very accurately. If dimensions are determined in points, then 1pt = 0.33255 mm.
3. Double-clicking the cell border will determine optimal sizes cells according to their contents.

Formatting cell contents

Command Format – Cell is designed to perform basic operations with cells. The action will be performed on the active cell or on a group of selected cells. The command contains the following submodes:

NUMBER - allows you to explicitly define the data type in a cell and the form of representation of this type. For example, for a number or currency format, you can specify the number of decimal places.

ALIGNMENT – defines the way data is arranged relative to the cell boundaries. If the “WORD Wrap” mode is enabled, the text in the cell is split into several lines. The mode allows you to position text in a cell vertically or even at a selected angle.

FONT – defines the font parameters in the cell (name, size, writing style).

BORDER – frames the selected cells, and you can determine the thickness of the line, its color and location.

VIEW – paints over background cells using a highlighted color or pattern.

PROTECTION – protection for changes is established.

The command is applied to the selected or active one in present moment cell.

Practice 2

Create a table like the following on the first worksheet.

When creating a table, apply the following settings:

1. The main text of the table is in Courier font, size 12;
2. the text is centered relative to the cell boundaries;
3. To make text span several lines in a cell, use the modeFormat – Cell – Alignment;
4. Frame the table in blue, for this use the modeFormat – Cell – Border.

Save the finished table in Users folder in the file fossils.xls.

Table title

To center the header relative to the table borders, there is a special icon called “Merge and center.” A group of cells above the table is first selected; when you click on this icon, they are combined into one and the text typed in it is centered.

Practical task 2.1

Above the created table, type the heading “Minerals” in size 14, in bold italics.

Lesson 3. CALCULATION BY FORMULAS

Rules for working with formulas

1. the formula always begins with the = sign;
2. the formula may contain signs arithmetic operations+ – * / (addition, subtraction, multiplication and division);
3. if the formula contains cell addresses, then the contents of the cell are involved in the calculation;
4. Click to get the result.

If you need to calculate data in a column using a formula of the same type, in which only the cell addresses change when moving to the next row of the table, then such a formula can be copied or multiplied across all cells of a given column.

For example:

Calculation of the amount in last column occurs by multiplying the data from the “Price of one copy” column and the data from the “Quantity” column, the formula does not change when moving to the next row in the table, only the cell addresses change.

Copying cell contents

Select the source cell, place the mouse pointer on the edge of the frame and, while holding down the key and the left mouse button, move the frame to a new location. This copies the contents of the cell, including the formula.

Autofill cells

Select the source cell, there is a fill marker in the lower right corner, place the mouse cursor on it, it will look like + ; With the left key pressed, we stretch the border of the frame to a group of cells. In this case, all selected cells are filled with the contents of the first cell. In this case, when copying and autofilling, the cell addresses in the formulas change accordingly. For example, the formula = A1 + B1 will change to = A2 + B2.

For example: = $A$5 * A6

When you copy this formula to the next row, the first cell reference will remain unchanged, but the second address in the formula will change.

Calculation of totals by columns

In tables, you often need to calculate column totals. There is a special icon for thisAutosummation. First, you need to select the cells with the source data; to do this, click the icon; the amount will be located in the free cell under the column.

Practice 3

Create a table like this:

Below the table, use the formula to calculate the average length of rivers.

Lesson 4. PRESENTING DATA FROM A TABLE IN GRAPHICAL VIEW

Microsoft Excel provides the user with ample opportunities for visualizing numerical data from tables. The two-dimensional image is called diagram, volume – histogram . Number series can be represented as graphs No matter what form of data presentation you choose, the procedure will be the same. In this case, a program calledChart Master. The user only needs to define the image parameters in the dialog window.

How to build a diagram:

1. Select the fragments of the table on the basis of which the diagram will be built. Cells containing column names are also highlighted and will be used as labels on the chart. If it is necessary to select non-adjacent fragments of the table, then the second fragment is selected when the key is pressed.

2. Select a teamInsert – Diagramor click the corresponding icon on the toolbar. The first of the Diagram Wizard dialog windows will appear on the screen.

3. In each window, select one of the proposed options with a mouse click. You can use the tabs at the top of the windows to switch between submodes. To go to the next window, click the “Next” button; the “Back” button allows you to return to the previous step. The “Finish” button will allow you to complete the process of creating a diagram.

Window 1: Determine the type of chart. At the same time, we select it in standard or non-standard diagrams. This window is shown in Fig. 4.

Window 2: A chart of the type you have chosen will be presented, built on the basis of the selected data. If the diagram does not work out, check that the source data in the table is selected correctly or select a different type of diagram.

Window 3: You can determine the chart title, data labels, the presence and location of a legend (a legend is an explanation of the chart: which color corresponds to which type of data).

Window 4: Determines the location of the diagram. It can be placed on the same sheet as the table with the source data, or on a separate sheet.

Rice. 4. First window of the Chart Wizard to determine the chart type

For example, let's build a diagram based on the following table.

Lakes

The diagram will be built based on the “Name” and “Deepest” columns. These columns need to be highlighted.

Click the icon and the diagram image. In the first window, select the chart type – pie. The second window will display the result of constructing the diagram; move on to the next window. In the third window we define the name – “Depths of the lakes”. Near each sector we will set the depth value. Place the legend below the diagram. Below is the result of our work:

Changing the formatting parameters of an already created diagram.

If you need to change the presentation form of any component of the diagram. By clicking the mouse, select a fragment of the diagram, a frame with resizing markers will appear around it, while the fragment of the diagram is considered selected and we edit it. Next, click the right mouse button to open the context menu, which will contain a list of actions. To change formatting options, select the modeData signature formatand change the options from values ​​to fractions.

Rice. 5. Context menu for formatting the constructed diagram

Actions with a diagram

You can do the following with the diagram, as with the inserted picture.

1. To resize the entire diagram or any of its fragments, you must first select the desired area. A frame with resizing markers will appear around it. By moving these markers while holding down the mouse button, we change the size.

2. To move a diagram around the worksheet, first select it, place the mouse pointer anywhere on the diagram and, while holding down the left key, move the diagram to a new location.

3. To delete a diagram, first select it, then press the Del key or select the “Delete” command in context menu diagrams.

Lesson 5. WORKING WITH FUNCTIONS

The program contains more than 150 built-in functions for data processing. For ease of searching, all functions are divided into categories, within each category they are sorted into alphabetical order. In addition, there are two categories - “10 recently used” and “Full alphabetical list”, in which all built-in functions are arranged in alphabetical order.

To insert a function into a formula, you can use the Function Wizard, and functions can be nested within each other, but no more than 8 times. The main tasks when using a function are to define the function itself and the argument. Typically, the arguments are cell addresses. If you need to specify a range of cells, then the first and last addresses are separated by a colon, for example A12:C20.

How to use functions

1. Let's make active the cell in which we want to place the result.
2. Choosing a teamInsert - Functionor click the F(x) icon.
3. In the first window of the Function Wizard that appears, we determine the category and name of the specific function (Fig. 6).
4. In the second window you need to define the arguments for the function. To do this, by clicking the button to the right of the first range of cells (see Fig. 7), close the window, select the cells on the basis of which the calculation will be carried out, and press the button. If the argument is several ranges of cells, then repeat the action.
5. Then press the key to complete the work. The original cell will contain the result of the calculation.

Let's look at working with functions using an examplelogical functions.When solving some problems, the value of a cell must be calculated in one of several ways - depending on the satisfaction or failure of one or more conditions.

To solve such problems, use the conditional IF function:

IF(,).

If the logical expression has the value “True” (1), IF takes the value of expression 1, and if “False” - the value of expression 2. You can write a nested IF function as expression 1 or expression 2. The number of nested IF functions should not exceed seven. For example, if the function IF(C5=1,D5*E5,D5-E5) is written in any cell, then if C5=1 the function will have the value “True” and the current cell will take the value D5*E5 if C5 =1 will have the value “False”, then the value of the function will be D5-E5.

Rice. 6. Function Wizards

If there are many conditions, writing nested IF functions becomes inconvenient. In this case, in place of the logical expression, you can specify one of two logical functions: AND (and) or OR (or).

The function format is the same:

AND(,..),

OR(,..).

The AND function evaluates to “True” if all logical expressions specified as arguments to this function are true at the same time. In other cases, the value of AND is “False”. You can specify up to 30 logical expressions in brackets.

The OR function evaluates to “True” if at least one of the logical expressions specified as arguments to this function is true. In other cases, the OR value is “False”.

Let's look at how logical functions work with an example.

Let’s create a table with the heading “Calculation results”:

The value of the last column may change depending on the value of the score. Let the applicant be considered accepted if the score is 21; if the score is lower, the applicant will not be accepted. Then the formula for entering in the last column is as follows:

IF(C2

Practice 5

Create a table for payroll calculation:

The first three columns are calculated in free form, the tax is calculated depending on the amount in the second column. The tax is calculated according to the following rule: if the amount of accruals from the beginning of the year for an employee is less than 20,000 rubles, then 12% of the taxable amount is taken. If the amount of accruals from the beginning of the year is more than 20,000 rubles, then 20% of the taxable amount is taken. To enter a tax calculation formula, use the Function Wizard.

Lesson 6. WORKING WITH TEMPLATES

To minimize actions when creating standard documents, it is convenient to use ready-made templates. To use them, you need to call the command File – New ; in the dialog box that appears, select the tab Solutions and determine required document. Fill in the fields of the document. Save the created document using the commandFile - Save As.

The user has the opportunity to create a form for any document, saving it as a template. This creates a template for the table without source data. You can pre-define the data format for empty cells. Cells are first selected and then with the command Format – Cell formatting parameters are determined.

Let's look at working with a template using an example.

Data from each geological station is entered into the same type of table. It makes sense to create a template containing a data table template.

1. Create an empty table like this:

Register of geological stations made by the laboratory
marine geoacoustics and petrophysics in the Baltic Sea

2. Save the table template as a template; To do this, in the window for saving the table in a file, in the “Type” field, select the TEMPLATE option or explicitly specify the file extension as. xlt.

3. After this, close the file.

4. To fill the template with data for a specific station, open the template file (if the name of this file is not in the folder window, then in the file opening window change the file type to TEMPLATE).

5. Fill out the table with specific information.

6. Save the file under a different name, and either explicitly specify the extension. xls , or set the file type as “ Microsoft Book Excel".

Practical task 6.

Create a template like this:

Square No.________

Latitude__________

Longitude __________

2. Enter the names of the months and depths in the table (0, 5,10,15,20, 30, 40, 50, 60, 80, 100, 150).

3. Format the table as you wish.

4. Save the table to a file squares.xlt, close the template file.

5. Open the file squares.xlt . Enter the temperature, salinity and density values ​​into it. Enter data about the square in the table header.

6. Save the file as square1.xls.

Lesson 7. ACTIONS WITH THE WORKSHEET

The workbook consists of several worksheets. You can do the following with the worksheet.

To add a new worksheet to a workbook, use the command Insert – Sheet. The new sheet will receive the next available number. The maximum number of sheets is 256.

To delete a worksheet with all its contents, select the commandEdit – Delete sheet.The worksheet is deleted with all its contents and cannot be restored.

Team Format – Sheet – RenameAllows you to assign a new name to the worksheet. At the same time, a cursor appears near the old name on the spine of the sheet. You need to delete the old name, enter a new one and press the key.

To remove the spines of worksheets from the screen, use the commandFormat – Sheet – Hide.The reverse action is performed by the commandFormat – Sheet – Show.

Copying or transferring a worksheet is performed with the commandEdit – Move/copy sheet.In this case, actions can be performed both in the current workbook (file) and in another open file. To perform a copy operation, you must enable the corresponding checkbox in the dialog box. The copy will have the same name as the original file. (2), (3), etc. will be indicated in parentheses.

Exercise 6.1

1. Rename the first worksheet “Source Data”.
2. Move it to the end workbook.
3. Make a copy of it in the same workbook.
4. Add to open book two more new worksheets.
5. Hide the spine of the 3rd worksheet and then show it again.

Practice 7

On the first worksheet, create a table that looks like this:

Basic morphometric characteristics of individual seas

1. Label the first worksheet “The Seas of the Atalantic Ocean.”
2. Make a copy of this worksheet and place it at the end of the file.
3. Make the remaining worksheets (Sheet2 and Sheet3) invisible.
4. Highlight the spine of the worksheet with number 3 again.

Lesson 8. CREATION OF DATABASES, OR WORKING WITH LISTS

In Microsoft Excel as databases can be used list.

List is a way of presenting data in which the data in the table is interrelated and the structure of the table is determined in advance. When performing normal data operations, such as searching, sorting, or manipulating data, lists are automatically recognized as databases, and the cursor must be located anywhere within the table.

If the database is considered to be a data table, then:

1. list columns become database fields;
2. column headings become database field names;
3. each line of the list is converted to data recording.

All actions with lists (database) are performed by the main menu command DATA.

1. List size and layout

1. You should not place more than one list on a sheet. Some list processing features, such as filters, do not allow you to process multiple lists at the same time.
2. You must leave at least one empty row and one empty column between the list and other worksheet data. This allows Microsoft Excel is faster detect and highlight the list when you sort, apply a filter, or insert automatically calculated totals.
3. The list itself should not contain empty rows or columns. This makes the list easier to identify and highlight.
4. Important data should not be placed at the left or right edge of the list; After applying the filter, they may be hidden.

2. Column headings

1. Column headings must be in the first column of the list. They are used by Microsoft Excel for reporting, searching, and organizing data.
2. The font, alignment, format, pattern, border, and uppercase and lowercase format assigned to list column headings must be different from the format assigned to data rows.
3. Use cell borders rather than blank lines or broken lines to separate headings from underlying data.

1. The list must be organized so that all rows contain the same type of data in the same columns.
2. You should not enter before the data in a cell. extra spaces, as they affect sorting.
3. You should not put a blank line between the headers and the first row of data.

Command DATA FORM

Form - it is a way of presenting data from a table where the contents of only one record are presented on the screen. The form window is shown in Fig. 8.

Using the form you can:

1. enter data into a table;
2. view or correct data;
3. delete data;
4. select records based on criteria.

Rice. 8. Form window for entering, viewing, deleting and searching records

Inserting records using a form

1. Specify the list cell from which to add entries.
2. Select a team Form in the Data menu.
3. Click the Add button.
4. Enter fields new entry, using the TAB key to move to the next field. To move to the previous field, use the SHIFT+TAB key combination.

To add an entry to the list, press ENTER. When you have completed typing the last entry, press the button Close to add the typed entry and exit the form.

Note

If the list box contains a formula, the form displays its result. You cannot change this field on the form. If you add a record that contains a formula, the result of the formula will not be calculated until you press ENTER or press Close . To cancel adding an entry, click the button Return before pressing the ENTER key or button Close . Microsoft Excel automatically adds a record when you move to another record or close a form.

Searching for entries in a list using a form

To move one entry, click on the scroll bar arrows in the dialog box. To move 10 entries, click the scroll bar between the arrows.

To set search terms or comparison terms, click the button Criteria . Enter the criteria in the form. To find records that match the criteria, click the buttons Next or Back . To return to editing the form, click the button Edit.

Practice 8

1. On the first line of the new worksheet, type the table head with the following names graph:

1. student number,
2. surname, first name,
3. speciality,
4. well,
5. home address,
6. year of birth.

1. Via the Data – Form command enter information about 10 students.
2. Learn to view, edit, and delete entries from the table.
3. Select entries from the list that meet the following criteria:

1. students with a certain year of birth,
2. students of a certain course.

1. Save the created database in a file Students.xls in the catalog specified by the teacher.

Lesson 9. CREATION OF DATABASES, OR WORKING WITH LISTS (CONTINUED)

Command DATA – SORT

This command allows you to sort data from a list by one or more fields. In this case, the table rows are sorted as database records. If sorting by several fields is specified, then the first field is considered the main one. In this case, all records are sorted by the first field specified for sorting. Fields where the first sort parameter is same value, within this group are sorted by the second parameter, etc.

To sort a list, make any cell inside the list active, then select the “Data - sort” command, define the field for sorting and its order. There are two sorting options available:ascending and descending. For a text field, this means alphabetical order and vice versa. The “Data-Sorting” command window is shown in Fig. 9.

Rice. 9. Window for sorting data in the list

Exercise 9.1

Re-sort the list of students in alphabetical order by specialty and student surnames.

Team DATA - RESULTS

The command allows you to calculate subtotals and grand totals. In this case, the field that will be summed is determined (of course, the operation makes sense only for numerical information). In the command window, we define the operation (“Sum”) and the field under which the subtotal values ​​will be located. At the bottom of the list will be the total value for the selected field.

Fig. 10. Command window Data-Filter-AutoFilter

Command DATA – FILTER

The “Data-Filter” command (Fig. 10) is convenient tool to create queries based on one or more criteria. The “Autofilter” submode is especially convenient and visual. When turned on this mode(when calling it, any cell inside the list must be active) a drop-down button with an arrow will appear to the right of the list field names, which contains a list of all values ​​for this field. When selecting a value from this list Only records that satisfy the given search criterion remain on the screen. The remaining entries are hidden. You can work with the query result as with a regular table - print it, save it in separate file, move to another worksheet, etc. To return to the original view of the table, select the “all” option in the list to the right of the field name.

Exercise 9.2

In the list of students created in the previous lesson, run the following queries:

1. determine the list of students with a certain year of birth;
2. display the details of the student with the specified last name;
3. determine the list of students of one course of a certain specialty.

Practice 9

Create a table as a database with the following field names:

1. book inventory number,
2. author,
3. Name,
4. publishing house,
5. year of publication,
6. price of one book,
7. number of copies.

Determine the total number of books and their total cost.

Run the following queries:

1. determine a list of books by a specific author;
2. determine the list of books of the same year of publication;
3. identify books of the same edition and year of release.

Lesson 10. MACROS

How to create macros

1. Select the command in the main menu of the programTools – Macro – Start Recording. A window will appear on the screen for defining the parameters of this macro, which is shown in Fig. 11.

Rice. 11. Window for defining macro parameters

2. Enter the name of the macro in the appropriate fields, assign the macro a key combination for quick launch(the letter must be Latin), in the description field you can briefly indicate the purpose of this macro. Determine where to save the macro - this file or “Personal Macro Book” (file personal.xls).

3. Next, perform the sequence of actions that you want to record in the macro. When finished, click the end recording button on the macro toolbar or select the commandTools – Macro – Stop recording.

4. To launch an already recorded macro, just press the combination assigned to it function keys or select in the list of macros the name of the macro that will be available when you select the commandTools – Macro – Macros.

5. The sequence of recorded actions is automatically converted into built-in language statements Visual Basic. For a user with programming skills, it is possible to create more complex programmable macros. To do this you can use the commandTools – Macro – Visual Basic Editor.

Practice 10

Create a macro named “Template” that would work within this workbook. Assign this macro a combination of functional Ctrl keys+ q. The macro must contain a sequence of actions 1 – 5 (see below):

Create an empty table like this on the first worksheet:

Vertical attenuation index values
for open ocean areas

1. Frame the table.
2. Define the font inside the table as 14, regular.
3. Finish recording the macro.
4. Go to the second worksheet. Execute the “Template” macro.
5. Fill out the table with the following data:

1. Sargasso Sea – 100-200, 0.040;
2. 400-500, 0,038.
3. North Atlantic Ocean - 1000-350, 0.031.
4. Northern Indian Ocean - 200-800, 0.022-10.033.
5. Pacific Ocean (near Tahiti) – 100-400, 0.034.
6. The world ocean as a whole is 0.03-0.04.

PRACTICAL TASKS
FOR SELF-CONTROL AND TEST ACTIVITY

Task 1

Create a table that looks like this. Determine the totals. Formatt the table as you wish.

Cost estimate for May 1999

Task 2

Create the following table as a database. Fill out the information using the form. Determine a list of films from a particular year.

List of videotapes

Task 3

Create a table that looks like this. Sort the data in the table in ascending order of product quantity.

List of goods in warehouse No. 1

Task 4

Create a table that looks like this. Calculate the data in the last column using the formula.

Task 5

Create a table of the following type and build 4 diagrams for all types of trees and the resulting data.

Data on Svetlogorsk forestry (conifers, thousand units)

Task 6

Create a table that looks like this, keeping the formatting settings. Calculate the data in the last column using the formula.

Cost estimate

Task 7

Create a table that looks like this. Calculate the data in the second and third columns using the formulas. Take the tax percentage equal to 12. Determine the totals for the columns.

Task 8

Create a schedule template. Save it as a template. Use this template to create your class schedule this semester.

SCHEDULE

Autumn term 2010/2011 academic year. year

Task 9

Create a table that looks like this. Re-sort the data by delivery date. Determine the total income.

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 that 98% of the city women surveyed like to kiss with their 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 fell in love with boys at primary school school, 45% lost their heads at the age of 12, 5% at the age of 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 the age of 14-17.

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
.

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
A more convenient tool for selecting and sorting data is AutoFilter. 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 ordinary, 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 of the diagram (title, legend, sectors, plotting area, etc.), 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 the format dialog boxes you can configure the color, fill and line type, font format, and captions. 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 a pie chart that shows how many different breeds came to the show.
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 calculate the average cost for the 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.