Home Random Page



Laboratory work 5.

AutoComplete. AutoCompletion. Working with lists. Create custom lists.

Using cell names in formulas.


Objective: Become familiar with the names of ranges of cells; gain skills automation fill the same type of data spreadsheets, get skills making lists, use cell names in the calculations.

To enter repetitive or similar data MS Excel spreadsheet offers the following tools for automating input: AutoComplete and AutoCompletion numbers and formulas. This tool allows you to automate MS Excel user input lists of frequently repeated text fragments, so these lists are called custom lists.

1. Autocompletion applied to automate text data. In the course of entering text in another cell line, the program checks the entered characters lines available in this column above. If found unambiguous coincidence entered text is automatically completed. Pressing the Enter key confirms the completion operation; otherwise you can continue entering, without paying attention to the proposed option.

2. AutoComplete numbers used when working with numbers. In the lower right corner of the frame of the current cell has a black box it's the fill handle. When you hover the mouse pointer takes the form of a thin black cross. Drag the fill handle treated as a transaction "reproduction" of the cell contents in a horizontal or vertical direction. To accurately formulate the conditions of filling cells should use the command Edit-Fill-progression. In the dialog box, select the type of progression, and a step of utmost importance.

3. Formula AutoComplete is performed as well as AutoComplete figures. Its peculiarity lies in the need to copy links to other cells. In the AutoComplete is taken into account the nature of links in the formula: relative links vary according to the relative location of the original and copies, and absolute references remain unchanged.

JOB 1.

1. In cell A1 type the name of the table

2. Develop project budget trading company Alatau for this:

3. Assign worksheet Sheet1 name Budget. Before you fill out the names of columns and rows , create a new data list :

a) Choose: Servis, Parametres, tab lists;

b) Click to list elements in the opening window and type the list items, completing each element of the input by pressing Enter:


Costs of goods

Gross revenue

Expenditure items


Rental of premises

Taxes and fees

Interest on loans


c) click on the option to Add, then click OK.

Enter the names of the months using AutoFill by entering the name of the first month of the list, select the cell and drag the mouse down.

4. The appropriate box, put in B9 word Coming , and then press ENTER key.

5. Again select the cell and "pull" the fill handle down to nine lines to a cell, as your list consists of 10 items.

6. Enter the initial numerical data in a table cell. Summarize the string to determine the values in the column total.

7. For profit (C19), enter the formula =C10-C18 (the difference between the values of income and expenditure lines total) and copy this formula to the other cells in the row Profit

8. Enter the current date in cell B3: = (). In cell K8 type (), and K10 determine the average cost of goods: = (C11:H11)

9. Create names for the rows in the table by names as the names of columns and rows

10. The cell type L8 Maximum value, and in cell M8 Minimum value. Find the maximum and minimum values for a number of data items costs, using the names of the blocks in their respective formulas. When the correct naming and use of library functions Function Wizard, you should get the following formula = MAX(Costs_of_goods) = MIN (Costs_of_goods)

11. Set the cell format. To do this, use the Format Cells, Number tab, select the number format all formats, to enter the type # ##00"thousand tenge''

12. In cell B4 enter the current date using the today() (())

13. Try to calculate the sum of the rows in the Total column, note that this is not the correct operation after naming cells.


Table 21 Shopping Budget: Fiscal Year


Company ALATAU Shopping Budget
  Growth rate                            
    Sales growth 1,50%                          
    Rise in price of goods 0,9                          
Report   March April May June July August Total
    Costs of goods                  
    Gross revenue                  
    Expenditure items :                              
    Rental of premises                  
    Taxes and fees                  
    Interest on loans                  
    Total expenses                              

JOB 2.

Creation and editing of the diagram (Figure 2). Construct a bar chart for the project budget trading firm ALATAU

Add a data series Profit

remove data series Total expenses



Figure 2

JOB 3.

1. Create a table modeled on Table 22. Name of the column start from the third line.
2. Enter the source data. Column Period fills operation using AutoFill. Next three months between each insert two blank lines.

3. Format the table in accordance with the above sample

4. Settlements in cells in which there is a sign X cannot be held. Algorithms for blank cells create and define the names for cell ranges:
4.1. Select the cells with volumes in the first three months (B4:B6) and by using the Insert-Name, set the selected range name Quarter_1. Determine the appropriate names for other cell ranges.

4.2. For cells with totals volumes create names by using the information from the first column of the table. Assign names to the rows Total_for_the_1st_quarter and On_average_for_quarter_1 in all quarters (A7:B7, A8:B8, A12:B12, A13:B13, A17:B17, A18:B18, A22:B22, A23:B23), the command Insert-Name
5. Enter the formula. In cells where there is a sign "X" calculations do not produce. Names for cell ranges that are used as arguments set command Insert-Name ().
5.1. In cell B7 enter the formula =SUM(Quarter_1) in B8: =AVERAGE(Quarter_1)
5.2. Input similar formulas for the other quarters.

5.3. In cell C8 enter the formula = SUM(C4:C6)/COUNT(C4:C6) ((C4:C6)/(C4:C6)). Then copy it to the clipboard and paste into the appropriate cells in all other quarters.

5.4. To determine the values of the summary rows, enter the formula:
in cell B24: =SUM(Total_ for_ the_1_ quarter; Total_ for_ the_2_ quarter; Total_ for_the_3_quarter;Total_for_the_4_quarter), in B25: =AVERAGE(On_average_for_the_1_quarter; On_average_for_the_2_quarter; On_average_for_the_3_quarter; On_average_for_the_4_quarter) in C25:

= AVERAGE (C8, C13, C18, C23).

6. In cell A27 enter the text Minimum price and C27 in the formula:

=MIN (C4:C6, C9:C11, C14:C16, C19:C21).
7. During the last row of the table, record the same information to determine the maximum price.

8. Using the capabilities change the spreadsheet display formulas in cells

9. Set a custom format for the table:

9.1. Select the entire table

9.2. Select a color display table borders

9.3. Place the name of the table over the entire table by combining the appropriate cells
9.4. Deselect the entire table, select the column names in bold, the name of the table, place them in the middle


Table 22 Data on volumes and selling prices of milk in Akmola region


Period Volume, t Sales price
January 17,8
February 18,1
March 17,5
Total for the 1st quarter  
On average for the 1st quarter    
April 17,1
May 16,8
June 14,9
Total for the 2nd quarter  
On average for the 2nd quarter    
September 15,4
Total for the 3d quarter  
On average for the 3d quarter    
November 16,5
Desember 16,7
Total for the 4th quarter  
On average for the 4th quarter    
On average for the all quarter    

Date: 2015-12-24; view: 2270

<== previous page | next page ==>
Table 16 Data on the volume of sales | Sort and list processing. AutoFilter and Advanced Filter.
doclecture.net - lectures - 2014-2024 year. Copyright infringement or personal data (0.007 sec.)