Home Random Page



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.


  A B D E
Personal Budget January February March Total for the quarter
Starting balance     ?
Salary ?
Premium     ?
Additional earnings ?
Total revenue ? ? ? ?
Housing and utilities ?
Food ?
Clothing   ?
Vacation     ?
Other expenses ?
Total expenses ? ? ? ?
Ending balance ? ? ? ?

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 EditPaste 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


Auction Results (Kazakhstan)
City Date Total Buyers Sales of books
Karaganda 17 923 000,00
Karaganda 19 1 002 000,00
Almaty 7 1 180000,00
Almaty 15 932 000,00
Almaty 1 1 635 000,00
Almaty 7 794 000,00
Astana 12 821 000,00
Almaty 30 745 000,00

Date: 2015-12-24; view: 67

<== previous page | next page ==>
Laboratory work 10 | JOB 1. Data analysis
doclecture.net - lectures - 2014-2017 year. (0.007 sec.)