Laboratory work № 10

Calculating totals. Data consolidation.

Objective:Learn how to calculate totals, use the data consolidation, data structure.

For summary information in Excel, use the command Data/Results, which allows you to apply one of the aggregate functions to a certain field or fields. When this outcome may be formed not only to the entire list, but also to separate groups of records having the same value in one of the fields.
Before executing the Results list must be sorted by the field, which provides for grouping records. After sorting, we can summarize the list as a whole and totals for a group of records.

Team Data/Results... opens a dialog box subtotals containing three input fields. In the input field «At each change in» states (selected from the drop-down list) is the field on which to group the records for subtotals.
In the list of input fields «Operation» is selected function calculating totals. To summarize numerical fields use the following functions: Sum, Average, Max, Min, Multiplication, Quantity of numbers and functions that calculate the deviation from the mean.

To summarize the results for non-numeric fields is the default operation number of values, calculates the number of records. In the input field «Add up the results for» stand (left mouse button) columns that are summed up.
Besides the summary rows command Data/Results... forms the so-called structure that corresponds to the grouping of data to calculate the subtotals and grand totals.
Data Consolidation – tool that allows combining data from source areas and bringing them to the destination. When you consolidate data may have different functions: summations, the arithmetic mean, determine the maximum and minimum values, etc.

Consolidation can be performed without creating links to the source data or the creation of links. In the second case MS Excel automatically adjusts the totals when they change. To do this, select the first cell range to consolidate on the sheet in the spreadsheet window click the Add button Consolidation. Then repeat the same steps for the other intervals cells that you want to consolidate

JOB 1.Calculating totals

1. Open the file Full assortment created in the Laboratory work № 9

2. With automatic calculation of totals, calculate the total number of copiers, faxes, and the total amount of each product:

A) Sort the data in the table in the form of goods command Data – Sort

B) Activate one of the cells in the table and choose Data – Results dialog box and select the operation Amount

C) In the "Add totals" field select the number and amount, in complete, click on OK

D) Display screen only summary data.

E) Restore displays the initial values.

3. Using the command Data – Results, answer how many different models for each product?

4. Find the number of product models, pre-sorted data by Goods.

5. Determine the total amount for each type of goods; pre-sort the data by column Name.

6. Which sum is necessary to have to buy the most expensive copier or fax machine?
7. Organize data by product and get the maximum amounts of goods.

8. What amount you need to have to buy the cheapest copier or fax machine? Find the minimum value of the sum of the goods.
9. How many different kinds of copiers and faxes? Find the number of product names.
10. Which average price of a copier or fax? Find the mean price of the goods.

11. Consider combining multiple outcomes to 2.3 points:

A) Follow step 2;

B) Re-open dialog box «Interim» and follow step 3 Mode Off «Replace current results»;

B) Displayed on the screen only summary data.

JOB 2.

1. Create a new worksheet and enter the data in the table (Table 35):

2. With automatic calculation of totals Data – Results turnover determine each salesperson for a specified period of time. As a result, the table will be supplemented by strings containing the totals for each salesperson:

2.1. Display on the screen only the totals;

2.2. Redisplay the original values.

3. Consider combining multiple outcomes. Add the table is another indicator – the number of cars sold by each seller (3 lines) and the total of the cabin (1 line):

3.1. Display on the screen only the totals;

3.2. Remove the line to the totals.

Table 35 – Company «Lucky Wheel»

3. On a clean sheet of the electronic version of the table 36, this is a report card on the payroll, and enters the data into the table arbitrarily (№, Name, Department, Job Title, Charge)

Table 36 – Payroll

Make payroll summary information indicating the departments and across the enterprise as a whole, retaining 10% of each employee.

JOB 3.

Data Consolidation

1. On a blank worksheet place the data in the following sequence:

• Rename a clean sheet on «January», enter the data for January (Figure 3);

• To calculate the total revenues, total expenses, use AutoSum;

• The final line in the balance now using the formula: Final Balance = Balance + Total revenue - Total expenses.

• Make a copy of the sheet (Rename/copy, check the box Create a copy)

• Rename the copy of the resulting sheet to «February,» and by removing only the previous data (formula should be retained), enter data for February in column B (Figure 4). Value of the initial balance sheet in February equals ending balance for January (for the previous month)

• Similarly, create lists of «March», just a block away from the Figure 5,6

My budget for the quarter (1 way )

1.In the sheet "Total for the quarter" pinch general revenues and expenses of various tables ( sheets January, February, March) , using the summation formula references to sheets.

My budget for the quarter (2 way)

2. For consolidated data, enter a new worksheet. Rename this sheet on Consolidation_1. The worksheet activate cell A1.

2.1. Execute Data Consolidation. In the dialog box «Consolidation» create a list of ranges consolidation (data sheets in January, February, March) and enable the option «in the left column»

2.2. Establish communication between the consolidated and source data. To do this, enable the option "create links to source data"

3. Change the data in the source tables and observe the changes on the sheet Consolidation_1

4. Enter a new leaf. Rename this sheet on Consolidation_2.

5. Perform consolidation worksheet data Consolidation_2. For a consolidated table include the options «in the left column» and «signature top line».

6. Compare the two consolidated table.

Figure 3 Figure 4

Figure 5 Figure 6

JOB 4.

1. On a blank worksheet, create a spreadsheet and enter the data shown in Figure 7, the next clean worksheet, create a table and enter the data shown in Figure 8, and the third worksheet, create a spreadsheet and enter the data shown in Figure 9:

2. Rename sheets: first –Karaganda, the second –Pavlodar and third –Taraz. To consolidate data pull the book Sheet 4. Rename this sheet consolidation. The worksheets activate the cell A4.

3. Execute Data Consolidation. In the dialog box, enable the following option. Keep in mind that in the dialog box «Link» is necessary to go consistently indicate the range of cells filled with data, completing the selection command «Add» button

4. After you click OK, activate that worksheet in which the results should be consolidated.

5. Save the file.

6. Establish communication between consolidated and source data. To do this, turn on the option to create a link with the original data.

7. Change the data in the source tables and watch the changes in the Consolidation sheet.

Figure 7 Figure 8 Figure 9

Date: 2015-12-24; view: 2082