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:

Coming

Costs of goods

Gross revenue

Expenditure items

Advertisement

Rental of premises

Taxes and fees

Interest on loans

Profit

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

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

Date: 2015-12-24; view: 2272