Laboratory work № 11

A hierarchical structure of the electronic table.

Objective:Learn how to structure, classify data, delete the structure, to be able to transpose the table.

Structuring the table – is the management level of detail to display data by creating a structure. Due to the structure of the data, you can hide, but you can at any time to redisplay.

Usually hidden parts of the table on the screen remain totals records, called subtotals.

In structuring the field names to be used, and recording. To create a login in the menu Data – Group and Structure – Creating structure.

Document Structure – a tool that allows you to manage concealment or display parts of the worksheet. Left of the table with the results of the structure are the symbols – the "+" and "–".

Button "–" (symbol hiding details) hides data group. "+" Button (the symbol show the details), which appears after hiding data, allows them to recover.
Buttons picture numbers ("1", "2", ...) denote the levels of the structure. If you click with the level number, it will be hidden detailed data at this level and all lower.

In most cases, MS Excel is able to understand itself in your custom list and offer a specific embodiment of the structure. However, there are cases where the user must select the rows and columns that will be included in the structure.
To create the structure manually, you need to select rows (columns) to be included in a structure that is to be phased out, and only then execute the menu command Data – Group and Structure – Group. A structure created to parse, you must choose another team: Data – Group and Structure – Ungroup.

JOB 1.

1. Make a copy of the lab number 10; name the new workbook «Laboratory work № 11", leaving only the task sheets with number 3. Be the data and results for the three months and in general for the quarter on one sheet (Figure 11).
Check formulas and rewrite them if necessary (in the cells where there is a question mark is necessary to do the calculations):
1.1. Value of the initial balance of power end balance for the previous month: Enter in cell C2: =B13 in D2: =C13 in E2: =D13
1.2. For the summation of revenues, expenditures and performance for the quarter, use AutoSum.

1.3. Values in a row now with final balance using the formula:
Ending Balance = Starting Balance + Total revenue - Total expenses.
2. Make a copy of this table to Sheet2 and Sheet3.

Figure 11

3. Create on Sheet2 hierarchical table using the automatic structuring, for this:
3.1. Move the mouse pointer inside the table

3.2. Execute Data – Group then Structure – Creating structure.

3.3. Hide one of the group levels, with a single click on the appropriate button with the sign "-".

3.4. Demonstrate in the table only totals.

3.5. Demonstrate the values in the table only the grand total (final balance).
3.6. Demonstrate the values in the table only total for the quarter.

4. Provide a table to its original form by executing Data – Group and Outline – Remove structure

5. Liste3 Create a hierarchical structure of a table manually:

5.1. Select a block of cells A3:A5;

5.2. Execute Data – Group and Outline – Group, in the Grouping activate the option "Strings";

5.3. Similarly, group the data for the block of cells A7:A11;
5.4. Display on the screen only the totals;

5.5. Remove the structure by running Data - Group and Outline – Ungroup after selecting table.

6. Click the Sheet1. Source table to transpose Sheet4:

6.1. Copy the table to the clipboard after selecting table (Edit – Copy), go to Sheet4 and choose Edit–Paste Special–"transpose"

7. Create on Sheet4 a hierarchical structure of the table under item 3

Making diagram

1. For each month and for the quarter as a whole build circular (annular) diagram (choose only indicators):

a) The proportion of items of income in total income

b) The share of expenditure in the overall consumption

2. For a table (Sheet1) build:

2.1. Schedule monthly savings balance (final balance);

2.2. Histogram cash balance, showing for each month of funds available at the beginning and end of the month (starting and ending balance)

JOB 2.

1. On a blank worksheet, create an electronic version of Table 37; please complete the table cell data. Enter formulas for the summary rows. Call Sheet «JOB_2.1»

2. Create a copy of this sheet, the sheet name «JOB_2.2»

3. Create a sheet «JOB_2.2» hierarchical table using the automatic structuring command Data – Group and Outline – Structuring

4. Demonstrate in the table only totals

5. Demonstrate the values in the table only the grand total (Total)

6. Duplicate sheet «JOB_2.1», call sheet «JOB_2.3» and create it on the hierarchical structure of the table manually using Data – Group and Outline – Group.

7. Display screen only totals

8. Remove the structure using the command Data – Group and Outline – Ungroup

9. Insert a blank worksheet, name it «JOB_2.4». Original table «JOB_2.1» transposes the worksheet «JOB_2.4».

10. Create a «JOB_2.4» hierarchical table structure similar to item 3 (follow all the action on the item.)

Table 37 – Auction Results

Date: 2015-12-24; view: 1888